[DB] SQL 명령어 정리: DDL, DML, DQL, DCL, TCL

📌 SQL

Structured Query Language(구조적 쿼리 언어)의 줄임말로,

관계형 데이터베이스 관리 시스템(RDBMS)의 데이터를 관리하기 위해 설계된 프로그래밍 언어이다.

데이터베이스와 상호작용하기 위한 언어로, 데이터 저장이나 조회, 삭제, 수정, 권한 설정 등의 역할을 수행할 수 있다.
SQL 명령어에는 DDL, DML, DCL, DQL, TCL이 있고 이 포스팅에서는 각 명령어를 살펴본다.

✅ DDL

DDL은 Data Definition Language로 데이터 정의어라고 부른다.
테이블, 인덱스, 스키마와 같은 "데이터베이스 구조"를 정의, 변경, 삭제하는 명령어들이 포함된다.

• CREATE
  새로운 데이터베이스 구조를 생성할 때 쓴다.

새로운 데이터베이스, 관계(테이블), 뷰(가상 테이블), 인덱스 등을 만들 수 있다.

-- 데이터베이스 생성
CREATE DATABASE company;

-- 테이블 생성
CREATE TABLE company.employees (
    id INT PRIMARY KEY,
    ...
);

 

CREATE로 만들 수 있는 테이블은 2가지 종류가 있다.

• Base tables(base relations)
  • 실제 데이터를 저장하는 테이블로 데이터가 DBMS에 의해 물리적으로 파일 형태로 저장된다.
• Virtual relations(views)
  • 실제 데이터를 저장하지 않고, 기존 테이블을 기반으로 만든 가상의 테이블이다.
  • 물리적 파일이 존재하지 않고, 기존 테이블의 데이터를 기반으로 동작한다.

-- employees 테이블의 특정 컬럼들의 데이터만 가져옴
CREATE VIEW employee_view AS
SELECT id, name FROM employees;

-- employees 테이블에서 id와 name만 볼 수 있음
SELECT * FROM employee_view;

• ALTER

이미 생성된 데이터베이스 객체(테이블, 뷰)의 구조를 변경할 때 사용한다.
주로 테이블의 컬럼 추가, 삭제, 수정을 할 때 사용한다.

-- ADD COLUMN: 테이블에 컬럼 추가
ALTER TABLE employees ADD COLUMN salary INT;

-- DROP COLUMN: 테이블에 컬럼 삭제
ALTER TABLE employees DROP COLUMN salary;

-- RENAME COLUMN: 테이블 컬럼명 변경
ALTER TABLE employees RENAME COLUMN name TO full_name;

-- 컬럼 데이터 타입 변경
-- MySQL: MODIFY
-- PostgreSQL: ALTER TABLE
ALTER TABLE employees ALTER COLUMN age TYPE BIGINT;

• DROP

데이터베이스 객체를 데이터베이스에서 완전히 삭제할 때 사용한다.
⚠️ 삭제된 데이터와 데이터베이스 구조는 복구될 수 없다.

-- 테이블 삭제
DROP TABLE employees;

이때 만약 employees 테이블이 없다면 오류가 발생할 수 있으므로 IF EXISTS와 함께 쓰면 좋다.

DROP TABLE IF EXISTS employees;

• RENAME

이미 존재하는 데이터베이스 객체의 이름을 변경할 때 사용한다.
ALTER의 RENAME과 같은 역할이다.

-- 테이블 이름 변경
RENAME TABLE old_table TO new_table;

• TRUNCATE

테이블의 모든 데이터를 삭제하는 명령어이다.
DROP과 다르게 테이블 구조가 유지되며 오직 데이터들만 삭제된다.
"전체 데이터"가 한 번에 삭제된다.

-- 테이블의 모든 데이터 삭제
TRUNCATE TABLE employees;

 

DELETE 때처럼 만약 employees 테이블이 존재하지 않으면 오류가 발생하므로 IF EXISTS 옵션과 함께 쓰면 안전하다.

TRUNCATE TABLE IF EXISTS employees;

✅ DML

Data Manipulation Language로 데이터 조작어라고 부른다.
데이터베이스 내 데이터를 추가, 수정, 삭제하는 명령어들이 포함된다.
데이터베이스 구조가 아니라 "데이터"만 다루는 명령어다.

• INSERT

새로운 데이터(행, row)를 테이블에 추가(저장)한다.

테이블에 잇는 모든 컬럼에 값을 추가하고, 컬럼 순서와 데이터 타입이 일치하는 경우에는 컬럼명을 반드시 명시할 필요 없다.

-- 하나의 행 추가
INSERT INTO employees (name, department, salary)
VALUES ('Alice', 'IT', 5000);
-- VALUES: 여러 개의 행 한 번에 추가
INSERT INTO employees (name, department, salary) 
VALUES 
    ('Bob', 'HR', 4000), 
    ('Charlie', 'Sales', 4500);

• UPDATE

테이블에 존재하는 데이터를 업데이트한다.
WHERE 키워드를 이용해 특정 데이터를 지정할 수 있다.

UPDATE employees 
SET salary = 5500 
WHERE name = 'Alice';

 

⚠️ WHERE 키워드가 없으면 테이블 내 모든 데이터가 변경될 수 있으므로 주의하자.

 -- 모든 직원의 급여가 7000으로 변경됨
UPDATE employees SET salary = 7000; 

• DELETE

저장된 데이터(행, row)를 삭제한다.

DELETE FROM employees WHERE name = 'Bob';

 

⚠️ WHERE 키워드가 없으면 테이블 내 모든 데이터가 삭제되므로 반드시!! 주의해야 한다.
TRUNCATE와 똑같아 보이지만, DELETE는 롤백으로 삭제된 데이터 복구가 가능하다는 점에서 차이가 있다.

-- employess의 모든 데이터 삭제
DELETE FROM employees; 

• (SELECT)

사람에 따라 데이터를 검색, 조회하는 SELECT도 조작어로 묶어 부르기도 한다.
이 포스팅에서는 주로 참고한 [Fundamentals of Database Systems] 교재를 따라 다음에 올 DQL로 분류하였다.

✅ DQL

Data Query Language로 데이터를 검색(조회)하는 명령어로
SELECT가 해당된다.

DML과 달리 데이터를 변경하지 않고 조회하는 역할만 한다.

WHERE, ORDER BY, GROUP BY 등의 키워드를 추가하여 더 자세한 조건으로 필터링, 정렬 기준 등의 조건을 걸 수 있다. 얘들은 언젠가 다른 포스팅에서 다루고 싶다.

• SELECT

-- employees 테이블의 모든 데이터 조회
SELECT * FROM employees;

-- 특정 컬럼 조회
SELECT name, salary FROM employees;

✅ DCL

Data Control Language로 데이터 제어 언어라고 부른다.

데이터베이스의 보안을 위해
데이터의 사용 및 접근 권한을 제어할 수 있는 명령어이다.

관리자같이 최고 권한을 가진 사용자만 실행 가능한 명령어이기도 하다.

• GRANT

사용자에게 특정 권한을 부여한다.
특정 유저에게 특정 데이터베이스 객체에 접근할 권리 또는 액션을 할 수 있는 권리를 부여한다.

-- user1에게 employees 테이블에 대한 SELECT, INSERT 권한 부여
GRANT SELECT, INSERT ON employees TO user1;

-- user1에게 my_datebase의 모든 테이블에 대한 모든 권리(즉, 관리 권한) 부여
GRANT ALL PRIVILEGES ON my_database.* TO user1;

• REVOKE

사용자에게 부여된 권한을 제거한다.

-- user1의 employees 테이블에 대한 INSERT 권한 제거
REVOKE INSERT ON employees FROM user1;

-- 모든 권한 제거
REVOKE ALL PRIVILEGES ON employees FROM user1;

✅ TCL

Transaction Control Language, 트랜잭션 제어 언어로 트랜잭션을 관리하는 명령어다.

 

📌 트랜잭션이란, 데이터베이스에서 수행되는 하나의 작업 단위로 결과는 반드시 성공(전체 수행) 또는 실패(아무것도 수행되지 않음)이다. 중간까지만 수행되는 경우는 없다.

• COMMIT

트랜잭션을 올바르게 완료하고, 모든 변경 사항을 데이터베이스에 영구적으로 반영한다.

BEGIN; -- 트랜잭션 시작
UPDATE employees SET salary = 6000 WHERE name = 'Alice';
COMMIT;  -- 변경 사항을 데이터베이스에 반영

• ROLLBACK

트랜잭션 이전으로 모든 변경사항들을 되돌린다.(undo)

BEGIN;
UPDATE employees SET salary = 6000 WHERE name = 'Alice';
ROLLBACK;  -- 변경 사항을 취소하고 원래 상태로 되돌림

-- 롤백 후에는 Alice의 salary가 원래 상태로 돌아간다

• SAVEPOINT

트랜잭션 내에서 말 그대로 세이브 포인트, 중간 저장 지점을 만들 수 있다.
롤백이 되더라도 세이브 포인트 이후 작업만 취소되고, 세이브 포인트 이전 부분은 커밋될 수 있다.

BEGIN; -- 트랜잭션 시작
UPDATE employees SET salary = 5000 WHERE name = 'Bob';
SAVEPOINT point1;  -- 중간 지점 설정
UPDATE employees SET salary = 6000 WHERE name = 'Alice';
ROLLBACK TO point1;  -- point1 지점까지 롤백
COMMIT;  -- 최종 커밋

-- Bob의 급여는 5000으로 수정되고, Alice의 급여는 6000으로 변경되지만
-- 롤백 후 Alice 급여는 업데이트 전 원래 상태로 유지됨

💡 DDL, DML의 차이: 트랜잭션

DDL과 DML은
"데이터베이스 구조를 다루는가", "데이터를 다루는가" 에 대한 차이 말고도
트랜잭션을 어떻게 하느냐에 따른 차이가 있다.

 

TCL 챕터에서 이야기한 것처럼
DML 사용 시에는 COMMIT을 통해 데이터 변경 사항을 영구 저장하거나, ROLLBACK을 통해 변경을 취소할 수 있다.

 

즉, 트랜잭션을 지원하여 트랜잭션 내에서 롤백이나 커밋을 통해 데이터 변경 사항을 관리할 수 있다.

 

BEGIN; 

UPDATE employees SET salary = 6000 WHERE name = 'Bob';
COMMIT;
-- Bob의 연봉을 수정한 행동은 영구 반영됨

UPDATE employees SET salary = 5000 WHERE name = 'Alice';
ROLLBACK;
-- Alice의 연봉을 수정한 행동은 취소됨

 

이때 롤백의 적용 구간은 마지막 COMMIT~ROLLBACK 사이 구간이다.
위 예시에서는 Alice의 연봉을 업데이트하는 행동만 포함이 된다.

 

구글링을 하며 DDL은 트랜잭션을 지원하지 않는다는 글을 꽤 봤는데
MySQL 공식 문서에 따르면 DDL을 트랜잭션을 한다.

 

DDL statements automatically commit the current transaction; they cannot be rolled back.

 

다만 DDL 명령어는 사용 시 자동으로 커밋까지 바로 진행되므로
롤백을 하여 취소할 수가 없다.

 

롤백이 불가능하기 때문에 트랜잭션을 지원하지 않는다고 이해하는 경우가 많은 것 같다.
정확히는 자동으로 커밋되므로 롤백이 불가능하고, 트랜잭션을 직접 관리할 수 없고,
트랜잭션 내에서 롤백을 통한 수정이 불가능하다는 의미로 이해하는 게 옳다.

 

✅ 정리하자면

• DML은 수행 후 롤백이 가능하다.
• DDL은 수행 즉시 자동으로 커밋되므로 롤백이 불가능하다.

참고자료

📕 Fundamentals of Database Systems, 2017, Ramez Elmasri and Shamkant B.Navathe
MySQL 공식 문서
DDL과 트랜잭션, 그리고 Atomic DDL
SQL Commands | DDL, DQL, DML, DCL and TCL Commands