Search
Duplicate

2. SQL ๋ช…๋ น์–ด

Created
2022/02/21 07:15
Updated
2022/02/25 18:38
Category
Stack
Creator

SQL ๊ธฐ๋ณธ ๋ช…๋ น์–ด

1.
๋ฐ์ดํ„ฐ ์ •์˜์–ด(DDL) : ํ…Œ์ด๋ธ” ์ƒ์„ฑ, ๋ณ€๊ฒฝ, ์‚ญ์ œ
2.
๋ฐ์ดํ„ฐ ์กฐ์ž‘์–ด(DML) : ๋ฐ์ดํ„ฐ ์‚ฝ์ž…, ์กฐํšŒ, ์ˆ˜์ •, ์‚ญ์ œ โ‡’ ์ œ์ผ ๋งŽ์ด ์“ธ ๋ช…๋ น์–ด
3.
๋ฐ์ดํ„ฐ ์ œ์–ด์–ด(DCL) : ๋ฐ์ดํ„ฐ ์ ‘๊ทผ ๊ถŒํ•œ ๋ถ€์—ฌ, ์ œ๊ฑฐ
4.
ํŠธ๋žœ์ ์…˜ ์ œ์–ด์–ด(TCL) : ๋ฐ์ดํ„ฐ ์กฐ์ž‘์–ด(DML) ๋ช…๋ น์–ด ์‹คํ–‰, ์ทจ์†Œ, ์ž„์‹œ์ €์žฅ

๋ฐ์ดํ„ฐ ์ •์˜์–ด(DDL; Data Definition Language)

CREATE : ์Šคํ‚ค๋งˆ, ๋„๋ฉ”์ธ, ํ…Œ์ด๋ธ”, ๋ทฐ, ์ธ๋ฑ์Šค ์ƒ์„ฑ(์ •์˜)
ALTER : ํ…Œ์ด๋ธ”์— ๋Œ€ํ•œ ์ •์˜๋ฅผ ๋ณ€๊ฒฝํ•˜๋Š” ๋ฐ ์‚ฌ์šฉ
DROP : ์Šคํ‚ค๋งˆ, ๋„๋ฉ”์ธ, ํ…Œ์ด๋ธ”, ๋ทฐ, ์ธ๋ฑ์Šค ์‚ญ์ œ
1.
ํ…Œ์ด๋ธ” ์ƒ์„ฑ(CREATE)
2.
์—ด ์ถ”๊ฐ€(ALTER)
3.
์—ด ๋ฐ์ดํ„ฐ ํƒ€์ž… ๋ณ€๊ฒฝ(ALTER)
4.
ํ…Œ์ด๋ธ” ๋ช… ๋ณ€๊ฒฝ(ALTER)
5.
ํ…Œ์ด๋ธ” ์‚ญ์ œ(DROP)
์ปฌ๋Ÿผ์˜ ๋ฐ์ดํ„ฐ ํƒ€์ž…
์ปฌ๋Ÿผ์˜ ์ œ์•ฝ ์กฐ๊ฑด
/* Practice ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์ƒ์„ฑ */ CREATE DATABASE Practice; /* Practice ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์‚ฌ์šฉ */ USE Practice; /* ํ…Œ์ด๋ธ” ์ƒ์„ฑ (Create) */ CREATE TABLE ํšŒ์›ํ…Œ์ด๋ธ” ( ํšŒ์›๋ฒˆํ˜ธ INT PRIMARY KEY, ์ด๋ฆ„ VARCHAR(20), ๊ฐ€์ž…์ผ์ž DATE NOT NULL, ์ˆ˜์‹ ๋™์˜ BIT ); /* ํ…Œ์ด๋ธ” ์—ด ์ถ”๊ฐ€ */ ALTER TABLE ํšŒ์›ํ…Œ์ด๋ธ” ADD ์„ฑ๋ณ„ VARCHAR(2); /* ์—ด ๋ฐ์ดํ„ฐ ํƒ€์ž… ๋ณ€๊ฒฝ */ ALTER TABLE ํšŒ์›ํ…Œ์ด๋ธ” MODIFY ์„ฑ๋ณ„ VARCHAR(20); /* ์—ด ์ด๋ฆ„ ๋ณ€๊ฒฝ */ ALTER TABLE ํšŒ์›ํ…Œ์ด๋ธ” CHANGE ์„ฑ๋ณ„ ์„ฑ VARCHAR(2); /* ํ…Œ์ด๋ธ”๋ช… ๋ณ€๊ฒฝ */ ALTER TABLE ํšŒ์›ํ…Œ์ด๋ธ” RENAME ํšŒ์›์ •๋ณด; /* ํ…Œ์ด๋ธ” ์‚ญ์ œ */ DROP TABLE ํšŒ์›์ •๋ณด;
SQL
๋ณต์‚ฌ

๋ฐ์ดํ„ฐ ์กฐ์ž‘์–ด(DML; Data Manipulation Language)

SELECT : ํ…Œ์ด๋ธ”์—์„œ ์กฐ๊ฑด์— ๋งž๋Š” ํ–‰์„ ๊ฒ€์ƒ‰ํ•œ๋‹ค
INSERT: ํ…Œ์ด๋ธ”์— ์ƒˆ๋กœ์šด ํ–‰์„ ์‚ฝ์ž…
DELETE : ํ…Œ์ด๋ธ”์—์„œ ์กฐ๊ฑด์— ๋งž๋Š” ํ–‰์„ ์‚ญ์ œ
UPDATE : ํ…Œ์ด๋ธ”์—์„œ ์กฐ๊ฑด์— ๋งž๋Š” ํ–‰์˜ ๋‚ด์šฉ์„ ๋ณ€๊ฒฝ
DELETE, UPDATE ์‹œ, WHERE ์กฐ๊ฑด์ ˆ์„ ์‚ฌ์šฉํ•ด์„œ ์›ํ•˜๋Š” ํ–‰๋งŒ ์‚ญ์ œ,๋ณ€๊ฒฝํ•  ์ˆ˜ ์žˆ๋‹ค.
/* Practice ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์‚ฌ์šฉ*/ USE Practice; /***************ํ…Œ์ด๋ธ” ์ƒ์„ฑ(Create)***************/ /* ํšŒ์›ํ…Œ์ด๋ธ” ์ƒ์„ฑ */ CREATE TABLE ํšŒ์›ํ…Œ์ด๋ธ” ( ํšŒ์›๋ฒˆํ˜ธ INT PRIMARY KEY, ์ด๋ฆ„ VARCHAR(20), ๊ฐ€์ž…์ผ์ž DATE NOT NULL, ์ˆ˜์‹ ๋™์˜ BIT ); /***************๋ฐ์ดํ„ฐ ์‚ฝ์ž…*******************/ INSERT INTO ํšŒ์›ํ…Œ์ด๋ธ” VALUES (1001, 'ํ™๊ธธ๋™', '2020-01-02', 1); INSERT INTO ํšŒ์›ํ…Œ์ด๋ธ” VALUES (1002, '์ด์ˆœ์‹ ', '2020-01-03', 0); INSERT INTO ํšŒ์›ํ…Œ์ด๋ธ” VALUES (1003, '์žฅ์˜์‹ค', '2020-01-04', 1); INSERT INTO ํšŒ์›ํ…Œ์ด๋ธ” VALUES (1004, '์œ ๊ด€์ˆœ', '2020-01-05', 0); /* ํšŒ์›ํ…Œ์ด๋ธ” ์กฐํšŒ */ SELECT * FROM ํšŒ์›ํ…Œ์ด๋ธ”; /***************์กฐ๊ฑด ์œ„๋ฐ˜*******************/ /* PRIMARY KEY ์ œ์•ฝ ์กฐ๊ฑด ์œ„๋ฐ˜ */ INSERT INTO ํšŒ์›ํ…Œ์ด๋ธ” VALUES (1004, '์žฅ๋ณด๊ณ ', '2020-01-06', 0); /* NOT NULL ์ œ์•ฝ ์กฐ๊ฑด ์œ„๋ฐ˜ */ INSERT INTO ํšŒ์›ํ…Œ์ด๋ธ” VALUES (1005, '์žฅ๋ณด๊ณ ', NULL, 0); /* ๋ฐ์ดํ„ฐ ํƒ€์ž… ์กฐ๊ฑด ์œ„๋ฐ˜ */ INSERT INTO ํšŒ์›ํ…Œ์ด๋ธ” VALUES (1005, '์žฅ๋ณด๊ณ ', 1, 0); /***************๋ฐ์ดํ„ฐ ์กฐํšŒ***************/ /* ๋ชจ๋“  ์—ด ์กฐํšŒ */ SELECT * FROM ํšŒ์›ํ…Œ์ด๋ธ”; /* ํŠน์ • ์—ด ์กฐํšŒ */ SELECT ํšŒ์›๋ฒˆํ˜ธ, ์ด๋ฆ„ FROM ํšŒ์›ํ…Œ์ด๋ธ”; /* ํŠน์ • ์—ด ์ด๋ฆ„ ๋ณ€๊ฒฝํ•˜์—ฌ ์กฐํšŒ (์กฐํšŒํ•  ๋•Œ๋งŒ ์ด๋ฆ„ ์—ด์„ ์„ฑ๋ช…์œผ๋กœ ๋ณ€๊ฒฝํ•ด์„œ ์กฐํšŒ) */ SELECT ํšŒ์›๋ฒˆํ˜ธ, ์ด๋ฆ„ AS ์„ฑ๋ช… FROM ํšŒ์›ํ…Œ์ด๋ธ”; /***************๋ฐ์ดํ„ฐ ์ˆ˜์ •*******************/ /* ๋ชจ๋“  ๋ฐ์ดํ„ฐ ์ˆ˜์ • */ UPDATE ํšŒ์›ํ…Œ์ด๋ธ” SET ์ˆ˜์‹ ๋™์˜ = 0; /* ํšŒ์›ํ…Œ์ด๋ธ” ์กฐํšŒ */ SELECT * FROM ํšŒ์›ํ…Œ์ด๋ธ”; /* ํŠน์ • ์กฐ๊ฑด ๋ฐ์ดํ„ฐ ์ˆ˜์ • */ UPDATE ํšŒ์›ํ…Œ์ด๋ธ” SET ์ˆ˜์‹ ๋™์˜ = 1 WHERE ์ด๋ฆ„ = 'ํ™๊ธธ๋™'; /* ํšŒ์›ํ…Œ์ด๋ธ” ์กฐํšŒ */ SELECT * FROM ํšŒ์›ํ…Œ์ด๋ธ”; /***************๋ฐ์ดํ„ฐ ์‚ญ์ œ*******************/ /* ํŠน์ • ๋ฐ์ดํ„ฐ ์‚ญ์ œ */ DELETE FROM ํšŒ์›ํ…Œ์ด๋ธ” WHERE ์ด๋ฆ„ = 'ํ™๊ธธ๋™'; /* ํšŒ์›ํ…Œ์ด๋ธ” ์กฐํšŒ */ SELECT * FROM ํšŒ์›ํ…Œ์ด๋ธ”; /* ๋ชจ๋“  ๋ฐ์ดํ„ฐ ์‚ญ์ œ */ DELETE FROM ํšŒ์›ํ…Œ์ด๋ธ”; /* ํšŒ์›ํ…Œ์ด๋ธ” ์กฐํšŒ */ SELECT * FROM ํšŒ์›ํ…Œ์ด๋ธ”;
SQL
๋ณต์‚ฌ

๋ฐ์ดํ„ฐ ์ œ์–ด์–ด(DCL; Data Control Language)

DBA(๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ๊ด€๋ฆฌ์ž)๊ฐ€ DB ๊ด€๋ฆฌ์™€ ๊ถŒํ•œ ๊ด€๋ฆฌ๋ฅผ ๋ชฉ์ ์œผ๋กœ ์‚ฌ์šฉ
GRANT : DB์˜ ์‚ฌ์šฉ์ž์—๊ฒŒ ํŠน์ • ์ž‘์—…์— ๋Œ€ํ•œ ๊ถŒํ•œ ๋ถ€์—ฌ
REVOKE : ์‚ฌ์šฉ์ž์—๊ฒŒ ํŠน์ • ์ž‘์—…์— ๋Œ€ํ•œ ๊ถŒํ•œ ๋ฐ•ํƒˆ
/***************์‚ฌ์šฉ์ž ํ™•์ธ***************/ /* MYSQL ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์‚ฌ์šฉ */ USE MYSQL; /* ์‚ฌ์šฉ์ž ํ™•์ธ */ SELECT * FROM USER; /***************์‚ฌ์šฉ์ž ์ถ”๊ฐ€***************/ /* ์‚ฌ์šฉ์ž ์•„์ด๋”” ๋ฐ ๋น„๋ฐ€๋ฒˆํ˜ธ ์ƒ์„ฑ */ CREATE USER 'TEST'@LOCALHOST IDENTIFIED BY 'PASSWORD'; /* ์‚ฌ์šฉ์ž ํ™•์ธ */ SELECT * FROM USER; /* ์‚ฌ์šฉ์ž ๋น„๋ฐ€๋ฒˆํ˜ธ ๋ณ€๊ฒฝ */ SET PASSWORD FOR 'TEST'@LOCALHOST = '1234'; /***************๊ถŒํ•œ ๋ถ€์—ฌ ๋ฐ ์ œ๊ฑฐ***************/ /** ๊ถŒํ•œ: CREATE, ALTER, DROP, INSERT, DELETE, UPDATE, SELECT ๋“ฑ **/ /* ํŠน์ • ๊ถŒํ•œ ๋ถ€์—ฌ */ GRANT SELECT, DELETE ON PRACTICE.ํšŒ์›ํ…Œ์ด๋ธ” TO 'TEST'@LOCALHOST; /* ํŠน์ • ๊ถŒํ•œ ์ œ๊ฑฐ */ REVOKE DELETE ON PRACTICE.ํšŒ์›ํ…Œ์ด๋ธ” FROM 'TEST'@LOCALHOST; /* ๋ชจ๋“  ๊ถŒํ•œ ๋ถ€์—ฌ */ GRANT ALL ON Practice.ํšŒ์›ํ…Œ์ด๋ธ” TO 'TEST'@LOCALHOST; /* ๋ชจ๋“  ๊ถŒํ•œ ์ œ๊ฑฐ */ REVOKE ALL ON Practice.ํšŒ์›ํ…Œ์ด๋ธ” FROM 'TEST'@LOCALHOST; /***************์‚ฌ์šฉ์ž ์‚ญ์ œ***************/ /* ์‚ฌ์šฉ์ž ์‚ญ์ œ */ DROP USER 'TEST'@LOCALHOST; /* ์‚ฌ์šฉ์ž ํ™•์ธ */ SELECT * FROM USER;
SQL
๋ณต์‚ฌ

ํŠธ๋žœ์žญ์…˜ ์ œ์–ด์–ด(TCL; Transaction Control Language)

โ€ข
ํŠธ๋žœ์žญ์…˜์ด๋ž€?
BEGIN ๋ช…๋ น์–ด๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ ๋งŒ๋“  ๋ถ„ํ• ํ•  ์ˆ˜ ์—†๋Š” SQL ์ž‘์—… ๋‹จ์œ„๋ฅผ ํŠธ๋žœ์žญ์…˜ ์ด๋ผ๊ณ  ํ•œ๋‹ค.
COMMIT : ๋ชจ๋“  ํŠธ๋žœ์žญ์…˜ ์ž‘์—…์„ ์ตœ์ข… ์‹คํ–‰
ROLLBACK : ๋ชจ๋“  ํŠธ๋žœ์žญ์…˜ ์ž‘์—…์„ ๋˜๋Œ๋ฆผ(์ทจ์†Œ)
SAVEPOINT : ํŠธ๋žœ์žญ์…˜ ํ•˜์œ„ ์ž‘์—… ๋‚ด์—์„œ ROLLBACK ์ €์žฅ์ ์„ ์ง€์ •ํ•˜๋Š” ๋ช…๋ น์–ด
ํŠธ๋žœ์žญ์…˜ ์ž‘์—…๊ณผ ์ผ๋ฐ˜ ์ž‘์—… ์ฐจ์ด์ ?
ํŠธ๋žœ์žญ์…˜ COMMIT ํ•˜๋Š” ์‹œ์ ์— ๋ชจ๋“  ์ž‘์—…๋“ค์ด ํ•œ๊บผ๋ฒˆ์— ์ˆ˜ํ–‰๋˜๋Š” ๊ฒƒ์ด ์•„๋‹ˆ๋ผ, ์ผ๋ฐ˜์ ์ธ ๋ช…๋ น์–ด์ฒ˜๋Ÿผ ๋ช…๋ น์–ด๊ฐ€ ์‹คํ–‰๋˜๋ฉด ์ž‘์—…์ด ์ˆ˜ํ–‰๋œ๋‹ค.
ํ•˜์ง€๋งŒ ROLLBACK ์„ ํ•  ์ˆ˜ ์žˆ๋‹ค๋Š” ์žฅ์ ์œผ๋กœ ์‚ฌ์šฉํ•˜๋Š” ๊ฒƒ ๊ฐ™๋‹ค.ย 
/* Practice ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์‚ฌ์šฉ*/ USE Practice; /***************ํ…Œ์ด๋ธ” ์ƒ์„ฑ(Create)***************/ /* (ํšŒ์›ํ…Œ์ด๋ธ” ์กด์žฌํ•  ์‹œ, ํšŒ์›ํ…Œ์ด๋ธ” ์‚ญ์ œ) */ DROP TABLE ํšŒ์›ํ…Œ์ด๋ธ”; /* ํšŒ์›ํ…Œ์ด๋ธ” ์ƒ์„ฑ */ CREATE TABLE ํšŒ์›ํ…Œ์ด๋ธ” ( ํšŒ์›๋ฒˆํ˜ธ INT PRIMARY KEY, ์ด๋ฆ„ VARCHAR(20), ๊ฐ€์ž…์ผ์ž DATE NOT NULL, ์ˆ˜์‹ ๋™์˜ BIT ); /* ํšŒ์›ํ…Œ์ด๋ธ” ์กฐํšŒ */ SELECT * FROM ํšŒ์›ํ…Œ์ด๋ธ”; /***************BEGIN + ์ทจ์†Œ(ROLLBACK)*******************/ /* ํŠธ๋žœ์ ์…˜ ์‹œ์ž‘ */ BEGIN; /* ๋ฐ์ดํ„ฐ ์‚ฝ์ž… */ INSERT INTO ํšŒ์›ํ…Œ์ด๋ธ” VALUES (1001, 'ํ™๊ธธ๋™', '2020-01-02', 1); /* ํšŒ์›ํ…Œ์ด๋ธ” ์กฐํšŒ */ SELECT * FROM ํšŒ์›ํ…Œ์ด๋ธ”; /* ์ทจ์†Œ */ ROLLBACK; /* ํšŒ์›ํ…Œ์ด๋ธ” ์กฐํšŒ */ SELECT * FROM ํšŒ์›ํ…Œ์ด๋ธ”; /***************BEGIN + ์‹คํ–‰(COMMIT)*******************/ /* ํŠธ๋žœ์ ์…˜ ์‹œ์ž‘ */ BEGIN; /* ๋ฐ์ดํ„ฐ ์‚ฝ์ž… */ INSERT INTO ํšŒ์›ํ…Œ์ด๋ธ” VALUES (1005, '์žฅ๋ณด๊ณ ', '2020-01-06', 1); /* ์‹คํ–‰ */ COMMIT; /* ํšŒ์›ํ…Œ์ด๋ธ” ์กฐํšŒ */ SELECT * FROM ํšŒ์›ํ…Œ์ด๋ธ”; /***************์ž„์‹œ ์ €์žฅ(SAVEPOINT)*******************/ /* (ํšŒ์›ํ…Œ์ด๋ธ”์— ๋ฐ์ดํ„ฐ ์กด์žฌํ•  ์‹œ, ๋ฐ์ดํ„ฐ ๋ชจ๋‘ ์‚ญ์ œ) */ DELETE FROM ํšŒ์›ํ…Œ์ด๋ธ”; /* ํšŒ์›ํ…Œ์ด๋ธ” ์กฐํšŒ */ SELECT * FROM ํšŒ์›ํ…Œ์ด๋ธ”; /* ํŠธ๋žœ์ ์…˜ ์‹œ์ž‘ */ BEGIN; /* ๋ฐ์ดํ„ฐ ์‚ฝ์ž… */ INSERT INTO ํšŒ์›ํ…Œ์ด๋ธ” VALUES (1005, '์žฅ๋ณด๊ณ ', '2020-01-06', 1); /* SAVEPOINT ์ง€์ • */ SAVEPOINT S1; /* 1005 ํšŒ์› ์ด๋ฆ„ ์ˆ˜์ • */ UPDATE ํšŒ์›ํ…Œ์ด๋ธ” SET ์ด๋ฆ„ = '์ด์ˆœ์‹ '; /* SAVEPOINT ์ง€์ • */ SAVEPOINT S2; /* 1005 ํšŒ์› ๋ฐ์ดํ„ฐ ์‚ญ์ œ */ DELETE FROM ํšŒ์›ํ…Œ์ด๋ธ”; /* SAVEPOINT ์ง€์ • */ SAVEPOINT S3; /* ํšŒ์›ํ…Œ์ด๋ธ” ์กฐํšŒ */ SELECT * FROM ํšŒ์›ํ…Œ์ด๋ธ”; /* SAVEPOINT S2 ์ €์žฅ์ ์œผ๋กœ ROLLBACK */ ROLLBACK TO S2; /* ํšŒ์›ํ…Œ์ด๋ธ” ์กฐํšŒ */ SELECT * FROM ํšŒ์›ํ…Œ์ด๋ธ”; /* ์‹คํ–‰ */ COMMIT; /* ํšŒ์›ํ…Œ์ด๋ธ” ์กฐํšŒ */ SELECT * FROM ํšŒ์›ํ…Œ์ด๋ธ”;
SQL
๋ณต์‚ฌ