[개념 콕] DDL, DML, DCL, TCL

내일배움캠프 수료생이 개발에 꼭 필요한 핵심 개념만 콕 집어 드립니다.

Jun 03, 2024

Contents

1. DDL (Data Definition Language, 데이터 정의어)2. DML (Data Manipulation Language, 데이터 조작어)4. TCL (Transaction Control Language , 트랜잭션 제어어)내일배움캠프는 개발에 필요한 핵심만 배웁니다

✍🏼

개발을 시작하시는 여러분, 정보가 너무 많고 배워야 할 것도 산더미라 어디서부터 시작해야 할지 막막하신가요? 내일배움캠프 수료생들이 4개월 동안 배운 엄선된 핵심 개념을 직접 정리해서 알려 드립니다. 공부하다 막히거나 헷갈리는 개념이 있다면 개념 콕으로 정리해보세요.

DDL, DML, DCL, TCL은 SQL 쿼리문의 종류로 관계형 데이터베이스에서 데이터를 정의, 조작, 제어하는 언어입니다. SQL 쿼리문의 네 가지 종류인 DDL, DML, DCL, TCL을 실제 SQL 쿼리문을 통해 알아봅시다.

1. DDL (Data Definition Language, 데이터 정의어)

DDL은 데이터베이스 객체(테이블, 인덱스, 뷰, 스키마 등)의 구조를 정의하고 관리하는 명령어의 집합입니다. DDL의 주요 키워드로는 CREATE, ALTER, DROP, TRUNCATE 등이 있습니다. 데이터베이스 스키마를 정의하고 수정하는 역할을 합니다.

DDL 명령어를 사용하여 데이터베이스와 테이블을 생성, 수정, 삭제까지 진행해 보겠습니다.

1) CREATE DATABASE: 데이터베이스 생성


CREATE DATABASE sparta;

CREATE는 새로운 데이터베이스 객체를 생성하는 SQL 명령어입니다. CREATE DATABASE 명령어를 사용하여 'sparta'라는 이름의 새로운 데이터베이스를 생성하였습니다.

2) SHOW DATABASES: 데이터베이스 표시


SHOW DATABASES;

SHOW DATABASES 명령어를 사용하여 현재 존재하는 데이터베이스의 목록을 조회할 수 있습니다. 이를 통해 'sparta' 데이터베이스가 성공적으로 생성되었는지 확인할 수 있습니다. 이제 sparta 라는 데이터베이스 안에 학생 테이블을 생성해 보겠습니다.

3) CREATE TABLE: 테이블 생성


CREATE TABLE
--이렇게 TABLE만 적은 쿼리문의 경우엔 빈 테이블이 생성됩니다.--

CREATE TABLE 학생 (
		휴대폰_번호 VARCHAR(15) PRIMARY KEY,
		커리큘럼_id INT,
		FOREIGN KEY (커리큘럼_id) REFERENCES 커리큘럼(id)
);


CREATE TABLE 커리큘럼 (
		id INT AUTO_INCREMENT PRIMARY KEY,
		name VARCHAR(50) NOT NULL
);

CREATE TABLE 명령어를 사용하여 'sparta' 데이터베이스 내에 '학생'과 '커리큘럼' 테이블을 생성하였습니다. 학생 테이블에서는 휴대폰 번호를 기본 키로 사용하며, JOIN문을 사용하여 커리큘럼 ID를 외래키(FOREIGN KEY)로 받은 후 학생이 속한 커리큘럼을 테이블에 표시하였습니다.

4) ALTER TABLE: 테이블 수정

학생 테이블에 학생 이름 컬럼이 빠져있습니다. ALTER는 기존의 데이터베이스 객체의 구조를 수정하는 SQL 명령어입니다. ALTER를 사용하여 학생 테이블에 학생의 이름, 나이 컬럼을 추가해 보겠습니다.


ALTER TABLE 학생
ADD COLUMN 이름 VARCHAR(50) NOT NULL,
ADD COLUMN 나이 TINYINT UNSIGNED NOT NULL;

5) DROP TABLE, TRUNCATE TABLE: 테이블의 모든 데이터를 삭제

DROP과 TRUNCATE는 모두 테이블의 데이터를 삭제하는 SQL 명령어입니다.


DROP TABLE 학생;

TRUNCATE TABLE 학생;

📌 DROP과 TRUNCATE 비교

ㅤ	DROP	TRUNCATE
공통점	테이블의 모든 데이터를 삭제합니다.	테이블의 모든 데이터를 삭제합니다.
차이점	- 테이블의 구조와 데이터뿐만 아니라 관련된 인덱스, 제약 조건, 트리거 등도 함께 삭제됩니다. - 삭제된 테이블은 복구할 수 없습니다. (백업에서 복원하는 방법은 별도로 존재) - 테이블에 대한 모든 정보가 데이터 딕셔너리에서 제거됩니다.	- 테이블의 모든 데이터를 삭제하지만, 테이블 구조 자체는 유지합니다. - 인덱스, 제약 조건, 트리거 등의 테이블 관련 객체는 그대로 유지됩니다. - 테이블의 데이터만 제거되고, 테이블 구조에 대한 정보는 데이터 딕셔너리에 유지됩니다. - 빠른 데이터 삭제를 위해 사용되며, 삭제된 데이터는 복구할 수 없습니다. (백업에서 복원하는 방법은 별도로 존재)

6) DROP DATABASE: 데이터베이스 삭제

sparta 데이터베이스를 삭제합니다.


DROP DATABASE sparta

DDL에서 자주 쓰이는 SQL 키워드

아래 키워드들은 DDL에서 자주 쓰이는 SQL 키워드입니다. 위 예시에 사용되었으니 참고해 보세요.

스네이크 케이스 (snake case)

SQL은 단어 사이를 공백이 아닌 언더바(_) 를 사용해서 연결합니다. 마치 이 모양이 뱀 같다고 하여 스네이크 케이스라고 부릅니다.

VARCHAR

가변길이 문자열 타입이며 괄호안의 숫자는 들어갈 수 있는 최대 길이입니다. 가변길이 문자열 타입은 크기만큼의 데이터가 들어오지 않더라도 나머지 공간을 공백으로 채우지 않습니다.

반대 타입으로 고정길이 문자열 타입인 CHAR가 있습니다. 고정길이 문자열은 크기만큼의 데이터가 들어오지 않으면 나머지 공간을 공백으로 채워 넣습니다.

VARCHAR는 CHAR보다 공간을 효율적으로 사용할 수 있지만, 성능면에서는 CHAR로 설정하는 것이 속도가 더 빠릅니다.

INT

SQL에서 사용되는 정수형 중 하나로, 데이터 형식의 종류는 수십 가지이지만 자주 사용하는 것은 아래와 같습니다.

데이터 형식	바이트	숫자 범위	UNSIGNED
TINYINT	1	-128 ~ 127	0 ~ 255
SMALLINT	2	-32768 ~ 32767	0 ~ 65535
INT	4	약 -21억 ~ 21억	0 ~ 약 43억
BIGINT	8	약 -900경 ~ 900경	0 ~ 약 18해 4467경

참고) 데이터 형식의 앞에 UNSIGNED를 붙이면 범위가 음수 ~ 양수가 아닌 0부터 시작하게 됩니다.

PRIMARY KEY (PK, 기본 키)

기본키는 테이블의 각 행을 유일하게 식별할 수 있습니다. 기본 키가 되기 위해선 몇 가지 조건이 필요합니다.

<기본 키 조건>

1) 유일성: 각 테이블에는 하나의 기본 키만 존재 가능

2) NOT NULL 제약 조건: 기본 키는 NULL 값을 가질 수 없습니다.

3) 최소성: 각 행을 유일하게 식별할 수 있도록 최소한의 속성으로만 구성

FOREIGN KEY (FK, 외래 키)

외래 키는 다른 테이블의 기본 키를 참조하는 속성 또는 속성의 집합을 의미합니다.

<외래 키의 특징>

1) 외래 키는 참조하는 테이블의 기본 키와 대응되어 테이블 간의 참조 관계를 표현합니다.

2) 외래 키는 NULL 값을 가질 수 있습니다. NULL 값인 경우 참조하는 테이블의 특정 행과의 관계 없음을 의미합니다.

3) 외래 키로 설정된 값은 참조하는 테이블의 기본 키 중 하나와 일치해야 합니다. 즉, 외래 키가 NULL 이 아닌 경우에는 무조건 참조하는 테이블의 기본 키 값 중 하나와 일치해야 합니다.

NOT NULL

테이블 설계 시 사용되는 제약 조건 중 하나이며 데이터의 정확성과 무결성을 보장하는 장치입니다. 해당 컬럼에 NULL 값이 들어갈 수 없음을 의미하며, 데이터 입력 시 해당 필드는 반드시 값을 가져야 함을 의미합니다.

AUTO_INCREMENT

이 속성을 설정하면 데이터가 테이블에 추가될 때 해당 컬럼의 값이 자동으로 증가합니다. 주로 기본 키에 사용되어, 각 행이 고유한 값을 가지도록 보장합니다.

2. DML (Data Manipulation Language, 데이터 조작어)

DML은 데이터베이스에 저장된 데이터를 조회, 삽입, 수정, 삭제하는 데 사용되는 SQL 명령어 집합입니다. SELECT, INSERT, UPDATE, DELETE 등의 명령어가 이에 해당합니다.

1) SELECT

SELECT는 데이터베이스에서 특정 데이터를 조회할 때 사용합니다.


SELECT * FROM 학생 
JOIN 커리큘럼 ON 학생.커리큘럼_id = 커리큘럼.id
WHERE 커리큘럼.이름 = 'Spring';

학생 테이블에서 Spring 커리큘럼에 속한 데이터를 조회하는 SQL 문입니다. 실행 시 아래 표와 같은 결과가 출력됩니다.

휴대폰_번호	이름	나이	커리큘럼_이름
010-1111-2222	르탄이	20	Spring
010-3333-4444	스프링수강생	24	Spring
010-8888-9999	스프링수강생	25	Spring

2) INSERT

위에서 만든 sparta 데이터베이스의 학생 테이블과 커리큘럼 테이블에 데이터를 넣어보겠습니다. 테이블에 데이터를 추가하는 데 사용하는 SQL문은 INSERT ~ INTO 입니다.

아래 예시와 같이 INSERT는 INTO 키워드를 무조건 붙여서 사용합니다


INSERT INTO 커리큘럼 (name) VALUES
('Spring'),
('React'),
('Node'),
('Unity'),
('Kotlin');

/*
커리큘럼 테이블을 학생 테이블에서 JOIN하고 있기 때문에 
커리큘럼 테이블에 먼저 데이터를 넣어줘야 합니다.
*/


INSERT INTO 학생 (휴대폰_번호, 이름, 나이, 커리큘럼_id) VALUES
('010-1111-2222', '르탄이', 20, 1),
('010-2222-3333', '스파르타', 22, 2),
('010-3333-4444', '스프링수강생', 24, 1),
('010-4444-5555', '리액트수강생', 28, 2),
('010-5555-6666', '노드수강생', 30, 3),
('010-6666-7777', '코틀린수강생', 28, 5),
('010-7777-8888', '유니티수강생', 26, 4),
('010-8888-9999', '스프링수강생', 25, 1);

3) UPDATE

UPDATE는 데이터베이스 내의 기존 행을 수정하기 위해 사용되는 명령어입니다. 위의 INSERT ~ INTO와 같이 UPDATE 또한 SET과 함께 사용됩니다.

학생 테이블에서 이름이 스프링수강생인 학생이 2명이므로 뒷사람의 이름을 변경해 보겠습니다. 스프링수강생이라는 이름이 겹치기 때문에 해당 스프링수강생의 기본 키 (휴대폰_번호)를 찾은 후 해당 휴대폰_번호를 사용하여 update 문을 적용하여 이름을 변경합니다.


SELECT 휴대폰_번호
FROM 학생
WHERE 이름 = '스프링수강생'
ORDER BY 휴대폰_번호
LIMIT 1 OFFSET 1;


UPDATE 학생
SET 이름 = '스파르타스프링수강생'
WHERE 휴대폰_번호 = '010-8888-9999';

4) DELETE

DELETE는 테이블의 데이터를 삭제하는 데 사용됩니다. WHERE 절을 사용하여 조건을 지정하지 않으면 테이블의 모든 행이 삭제됩니다. 테이블 구조는 그대로 유지되며 데이터만 삭제됩니다.


DELETE FROM 학생;
--학생 테이블의 모든 데이터가 삭제됩니다.--

DELETE FROM 학생
WHERE 나이 = 28;
--나이가 28살인 모든 수강생의 데이터가 삭제됩니다.--

DELETE FROM 학생
WHERE 휴대폰_번호 LIKE '%5555%';
--휴대폰 번호에 5555가 들어가는 모든 수강생의 데이터가 삭제됩니다.--

*TRUNCATE, DROP과의 차이

DELETE문은 데이터 롤백이 가능합니다. 행 별로 삭제를 수행하며 삭제된 행은 트랜잭션 로그에 기록되기 때문입니다.

DML에서 자주 쓰이는 SQL 키워드

JOIN

JOIN 문은 SQL에서 두 개 이상의 테이블을 결합하여 데이터를 조회할 때 사용됩니다. 위의 테이블은 INNER JOIN을 사용하여 학생 테이블과 커리큘럼 테이블을 결합하여 조회했습니다.

WHERE

WHERE 문은 특정 조건을 지정할 때 사용하는 SQL 문입니다. SELECT, UPDATE, DELETE 등의 명령어와 함께 사용됩니다.

ORDER BY

ORDER BY는 SQL 문의 결과를 정렬하는 데 사용됩니다. 결과를 오름차순(ASC) 또는 내림차순(DESC)으로 정렬할 수 있으며, 기본값은 오름차순입니다.

LIMIT

SQL 문의 결과로 반환되는 행의 수를 제한할 때 사용합니다.

OFFSET

SQL에서 결과의 시작점을 지정할 때 사용하며 주로 LIMIT와 함께 사용합니다.

UPDATE예시의 LIMIT 1 OFFSET 1은 검색 결과의 첫 번째를 건너뛰고 (OFFSET 1) 그다음 나오는 하나 (LIMIT 1) 를 조회하겠다는 의미입니다.

LIKE

SQL에서 문자열이 특정 패턴과 일치하는지 확인할 때 사용합니다. 주로 SELECT, UPDATE, DELETE의 WHERE 절에서 사용됩니다.

LIKE는 두 가지 와일드카드 문자를 사용할 수 있습니다.

1) % : 임의의 문자열을 나타냅니다. 0개 이상의 문자를 대체할 수 있습니다.

예를 들어 르% 이라고 입력한다면 르로 시작하는 모든 데이터를 찾습니다. 위의 쿼리문에서도 %5555% 를 입력했으므로 문자열에서 5555가 들어가는 모든 문자열을 찾습니다.

2) _ : 임의의 단일문자를 나타냅니다. 예를 들어 _프링수강생 이라고 입력한다면 프링수강생과 일치하는 모든 데이터를 찾습니다.

3. DCL (Data Control Language, 데이터 제어어)

데이터베이스에서 데이터의 접근과 사용을 제어하는데 사용되는 명령어입니다. 주로 데이터베이스 시스템의 보안과 관련된 작업을 수행하며, 사용자에게 권한을 부여하거나 취소하는 데 사용됩니다. GRANT, REVOKE 등의 키워드가 이에 해당합니다.

1) GRANT

데이터베이스 객체에 대한 특정 권한을 사용자에게 부여할 때 사용됩니다.


GRANT SELECT ON 학생 TO user1;
--user1에게 학생 테이블에 대한 SELECT 권한 부여--

GRANT SELECT, INSERT ON 학생 TO user2;
--user2에게 학생 테이블에 대한 SELECT, INSERT 권한 부여--

2) REVOKE

특정 사용자에게 부여한 권한을 취소할 때 사용됩니다.


REVOKE INSERT ON 학생 FROM user2;
--user2에게 부여한 권한 중 INSERT 권한 취소--

REVOKE SELECT ON 학생 FROM user1;
--user1에게 부여한 SELECT 권한 취소--

4. TCL (Transaction Control Language , 트랜잭션 제어어)

데이터베이스에서 트랜잭션의 시작, 종료, 제어를 위해 사용되는 명령어입니다. COMMIT, ROLLBACK, SAVEPOINT 등의 키워드가 이에 해당합니다.

1) COMMIT

트랜잭션 내에서 이루어진 모든 변경사항을 데이터베이스에 영구적으로 반영합니다. 트랜잭션이 성공적으로 완료되었음을 의미합니다.


START TRANSACTION;
INSERT INTO 학생 (휴대폰_번호, 이름, 나이, 커리큘럼_id) VALUES ('010-1111-2222', '르탄이', 20, 1);
COMMIT;

2) ROLLBACK: 트랜잭션 취소

트랜잭션 내에서 이루어진 모든 변경사항을 취소하고, 트랜잭션 이전의 상태로 되돌립니다. 트랜잭션이 실패하거나 중단되었을 때 사용합니다.


START TRANSACTION;
INSERT INTO 학생 (휴대폰_번호, 이름, 나이, 커리큘럼_id) VALUES ('010-1111-2222', '르탄이', 20, 1);
ROLLBACK;

3) SAVEPOINT: 트랜잭션 중간 저장점 설정

트랜잭션 내에서 특정 지점을 저장하고 이후에 ROLLBACK TO SAVEPOINT를 사용하여 저장한 지점으로 돌아갈 수 있습니다.


START TRANSACTION;
INSERT INTO 학생 (휴대폰_번호, 이름, 나이, 커리큘럼_id) VALUES ('010-1111-2222', '르탄이', 20, 1);
SAVEPOINT sp1;
UPDATE 학생 SET age = 24 WHERE 휴대폰_번호 = '010-1111-2222';
ROLLBACK TO sp1;
COMMIT;

내일배움캠프는 개발에 필요한 핵심만 배웁니다

지금까지 꼭 필요한 개발 지식에 대해 알아보았습니다. 내일배움캠프에서는 전문가들이 선별한 핵심 개발 지식으로 개발 공부도, 취업도 보다 효율적으로 할 수 있는데요. 국내 유수의 IT기업 출신 튜터님들과 실습 위주의 독보적인 커리큘럼으로 개발자 취업을 체계적으로 준비해보세요. 내일배움캠프 4개월, 여러분 인생의 가장 큰 터닝 포인트입니다.

CREDIT

글 | 박상신 내일배움캠프 수료생 편집 | 정효재 팀스파르타 에디터