DDL, DML, DCL, TCL

SQL 은 데이터베이스를 관리하기 위한 명령어 모음으로 구성됩니다.

 

SQL 은 데이터를 쉽게 관리하기 위해서 명령어를 지원해 줍니다.

이 명령어들은 크게 DDL, DML, DCL 세 가지로 나눕니다.

DDL

Data Definition Language

DDL 은 데이터베이스의 구조를 정의하거나 수정할 때 사용되는 명령어입니다.

즉, 테이블을 생성, 변경, 삭제하거나 데이터베이스의 구조를 설정합니다.

 

주요 명령어는 다음과 같습니다.

CREATE : 데이터베이스, 테이블, 인텍스, 뷰 등을 생성

ALTER : 테이블의 구조를 수정

DROP : 테이블, 데이터베이스, 뷰 등을 삭제

TRUNCATE : 테이블의 모든 데이터를 삭제

 

예시 코드

CREATE

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50),
    grade INT
);
-- 'students' 테이블을 생성합니다.
-- student_id: 정수, 기본 키 (Primary Key)
-- name: 최대 50자의 문자열
-- grade: 정수

 

ALTER

ALTER TABLE students ADD email VARCHAR(100);
-- 'students' 테이블에 email 열을 추가합니다.

 

DROP

DROP TABLE students;
-- 'students' 테이블을 삭제합니다.

 

TRUNCATE

TRUNCATE TABLE students;
-- 'students' 테이블의 모든 데이터를 삭제합니다.
-- 테이블 구조는 유지됩니다.

 

DML

Data Manipulation Language

DML 은 데이터베이스에 데이터를 조회, 삽입, 수정, 삭제하는 데 사용됩니다.

사용자와 데이터베이스 간의 직접적인 상호작용이 이루어집니다.

 

주요 명령어는 다음과 같습니다.

SELECT : 데이터를 조회

INSERT : 데이터를 삽입

UPDATE : 데이터를 수정

DELETE : 데이터를 삭제

 

예시 코드

SELECT

SELECT * FROM students;
-- 'students' 테이블의 모든 데이터를 조회합니다.

 

INSERT

INSERT INTO students (student_id, name, grade) VALUES (1, 'John Doe', 90);
-- 'students' 테이블에 새로운 학생 데이터를 삽입합니다.

 

UPDATE

UPDATE students SET grade = 95 WHERE student_id = 1;
-- student_id가 1인 학생의 grade를 95로 수정합니다.

 

DELETE

DELETE FROM students WHERE student_id = 1;
-- student_id가 1인 학생 데이터를 삭제합니다.

 

DCL

Data Control Language

DCL 은 데이터베이스에 대한 권한을 관리하고, 데이터의 보안을 제어하는 데 사용됩니다.

 

주요 명령어는 다음과 같습니다.

GRANT : 사용자에게 특정 권한을 부여

REVOKE : 사용자로부터 권한을 제거

 

예시 코드

GRANT

GRANT SELECT, INSERT ON students TO 'user1';
-- 'user1'에게 'students' 테이블에 대해 SELECT(조회)와 INSERT(삽입) 권한을 부여합니다.

 

REVOKE

REVOKE INSERT ON students FROM 'user1';
-- 'user1'로부터 'students' 테이블에 대한 INSERT(삽입) 권한을 제거합니다.

 

 

TCL

Transaction Control Language

TCL 은 데이터베이스에서 트랜잭션을 제어하는 데 사용되는 명령어들의 집합입니다.

공식 사이트에서는 트랜잭션은 데이터베이스의 일관성을 유지하기 위해
하나의 논리적인 작업 단위로 묶인 일련의 데이터 조작 작업이라고 설명합니다.

 

TCL 명령어를 통해 데이터의 변경 사항을 저장하거나 취소할 수 있으며, 데이터베이스의 안정성과 무결성을 보장합니다.

 

트랜잭션이란?

트랜잭션은 간단하게 데이터베이스에서 수행되는 일련의 작업들로, 모두 성공하거나 모두 실패해야 합니다.

예를 들어, 은행에서 계좌 이체를 생각해 보면 A 계좌에서 돈을 출금하고, B 계좌에서 돈을 입금하는 경우 이 두 작업은 하나의 트랜잭션으로 묶어야 합니다. 하나라도 실패하면 전체 작업을 취소해야 하기 때문입니다.

 

주요 명령어는 다음과 같습니다.

COMMIT : 트랜잭션에서 수행된 변경 사항을 영구적으로 데이터베이스에 반영합니다.

ROLLBACK : 트랜잭션에서 수행된 변경 사항을 취소하고, 데이터베이스를 트랜잭션 시작 이전상태로 되돌립니다.

SAVEPOINT : 트랜잭션 내에서 특정 지점을 저장점으로 설정하여, 해당 지점까지 롤백할 수 있습니다.

SET TRANSACTION : 트랜잭션의 속성을 설정합니다.

 

예시 코드

COMMIT

현재 트랜잭션에서 수행된 모든 DML 작업의 변경사항을 영구적으로 저장합니다.

BEGIN; -- 트랜잭션 시작

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

COMMIT; -- 트랜잭션 완료 및 변경 사항 저장

 

ROLLBACK 

현재 트랜잭션에서 수행된 모든 변경사항을 취소하고, 데이터베이스를 트랜잭션 시작 이전 상태로 되돌립니다.

BEGIN; -- 트랜잭션 시작

UPDATE products SET stock = stock - 10 WHERE product_id = 100;

-- 재고가 음수가 되면 트랜잭션을 취소
IF (SELECT stock FROM products WHERE product_id = 100) < 0 THEN
    ROLLBACK; -- 변경 사항 취소
ELSE
    COMMIT; -- 변경 사항 저장
END IF;

 

SAVEPOINT

트랜잭션 내에서 부분적으로 롤백할 수 있도록 특정 지점을 설정합니다.

BEGIN; -- 트랜잭션 시작

INSERT INTO orders (order_id, customer_id) VALUES (1, 101);
SAVEPOINT sp1; -- 저장점 설정

INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 201, 2);
SAVEPOINT sp2; -- 저장점 설정

-- 오류 발생 시 sp2로 롤백
IF (에러 발생) THEN
    ROLLBACK TO sp2; -- sp2 이후의 변경 사항 취소
END IF;

COMMIT; -- 트랜잭션 완료 및 변경 사항 저장

 

SET TRANSACTION

트랜잭션의 격리 수준등 속성을 설정합니다.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN; -- 트랜잭션 시작

-- 데이터 조회 및 수정 작업

COMMIT; -- 트랜잭션 완료

 

은행 계좌 이체 예제

예를 들어 계좌 A에서 계좌 B 로 100만 원을 이체하려고 합니다.

이 작업은 다음과 같은 단계로 이루어집니다.

계좌 A 에서 100만 원 출금 -> 계좌 B 에서 100만원 입금

 

BEGIN; -- 트랜잭션 시작

-- 계좌 A에서 100만원 출금
UPDATE accounts SET balance = balance - 1000000 WHERE account_id = 'A';

-- 계좌 B에 100만원 입금
UPDATE accounts SET balance = balance + 1000000 WHERE account_id = 'B';

-- 계좌 잔액 확인
IF (SELECT balance FROM accounts WHERE account_id = 'A') < 0 THEN
    -- 계좌 A의 잔액이 음수이면 롤백
    ROLLBACK; -- 변경 사항 취소
    SELECT '잔액 부족으로 거래가 취소되었습니다.' AS message;
ELSE
    -- 정상 처리되면 커밋
    COMMIT; -- 변경 사항 저장
    SELECT '거래가 완료되었습니다.' AS message;
END IF;

 

TCL , DML의 관계

DML 명령어는 데이터의 변경을 일으키지만, 실제 데이터베이스에 반영되려면 TCL 명령어(COMMIT) 이 필요합니다.

기본적으로 대부분의 데이터베이스는 각 DML 명령어를 자동으로 커밋하지만, 트랜잭션 블록(BEGIN;... COMMIT)을 사용하면 여러 DML 작업을 하나의 트랜잭션으로 묶어줄 수 있어 유용합니다.

 

중요

대부분의 데이터베이스는 기본적으로 자동 커밋(Auto-Commit) 모드로 동작합니다. 따라서 BEGIN을 명시적으로 사용하지 않으면, 각 DML 명령어(예: INSERT, UPDATE..)가 실행될 때마다 자동으로 COMMIT 이 수행됩니다.