SQL (Structured Query Language)

요약 비교표

카테고리	주요 키워드	역할
DDL	CREATE, ALTER, DROP	데이터베이스 구조 정의 및 변경
DML	SELECT, INSERT, UPDATE, DELETE	데이터 조회 및 조작
DCL	GRANT, REVOKE	사용자 권한 관리
TCL	COMMIT, ROLLBACK, SAVEPOINT	트랜잭션 관리 및 데이터 무결성 유지

실무 팁
- DDL은 데이터베이스 설계 단계에서 주로 사용되며 자주 변경되지 않는다.
- DML은 애플리케이션 개발 중 가장 많이 사용되며 트랜잭션 처리와 함께 사용해야 안전성을 보장할 수 있다.
- DCL은 보안이 중요한 환경에서 필수적으로 사용된다.
- TCL은 대규모 데이터 변경 작업이나 복잡한 트랜잭션 처리 시 유용하게 활용된다.

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

DDL은 데이터베이스의 구조를 정의하거나 변경하는 데 사용된다.
테이블, 뷰, 인덱스 등의 데이터베이스 객체를 생성, 수정, 삭제하는 역할을 한다.

특징:

즉시 실행: DDL 명령어는 실행 즉시 데이터베이스에 적용되며, 자동으로 커밋(COMMIT)된다.
롤백 불가: 한 번 실행된 DDL은 되돌릴 수 없으므로, 실행 전 데이터베이스 백업이 권장된다.

주요 키워드와 구문

키워드	개념 및 역할
`CREATE`	새로운 데이터베이스 객체(테이블, 뷰 등)를 생성합니다.
`ALTER`	기존 데이터베이스 객체의 구조를 수정합니다.
`DROP`	데이터베이스 객체를 삭제합니다.
`TRUNCATE`	테이블의 모든 데이터를 삭제하지만 테이블 구조는 유지합니다.
`RENAME`	데이터베이스 객체의 이름을 변경합니다.

예제

CREATE

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
-- 데이터베이스 생성
-- CHARACTER SET은 저장될 문자의 인코딩을 지정
-- COLLATE는 문자열 비교 시 사용할 규칙을 지정
CREATE DATABASE school_management
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_unicode_ci;

-- 테이블 생성
-- 각 컬럼의 데이터 타입과 제약조건을 지정
CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,  -- 자동 증가하는 기본키
    first_name VARCHAR(50) NOT NULL,           -- NULL을 허용하지 않는 문자열
    last_name VARCHAR(50) NOT NULL,
    birth_date DATE,                           -- 날짜 형식
    enrollment_date TIMESTAMP                  -- 시간 정보 포함
        DEFAULT CURRENT_TIMESTAMP,             -- 현재 시간을 기본값으로
    grade DECIMAL(4,2)                         -- 소수점 포함 숫자
        CHECK (grade >= 0 AND grade <= 100),   -- 값의 범위 제한
    class_id INT,                              
    FOREIGN KEY (class_id)                     -- 외래키 설정
        REFERENCES classes(class_id)
        ON DELETE CASCADE                      -- 참조 레코드 삭제 시 동작 정의
);

-- 인덱스 생성
-- 데이터 검색 속도를 향상시키기 위한 구조
CREATE INDEX idx_student_name 
ON students(first_name, last_name);

-- 뷰 생성
-- 자주 사용하는 쿼리를 저장하여 재사용
CREATE VIEW student_summary AS
SELECT 
    s.student_id,
    CONCAT(s.first_name, ' ', s.last_name) AS full_name,
    c.class_name
FROM students s
JOIN classes c ON s.class_id = c.class_id;

ALTER

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 테이블 구조 수정
-- 새로운 컬럼 추가
ALTER TABLE students
ADD COLUMN email VARCHAR(100) UNIQUE;

-- 컬럼 수정
ALTER TABLE students
MODIFY COLUMN first_name VARCHAR(100) NOT NULL;

-- 컬럼 이름 변경
ALTER TABLE students
CHANGE COLUMN grade student_grade DECIMAL(4,2);

-- 제약조건 추가
ALTER TABLE students
ADD CONSTRAINT check_age 
    CHECK (YEAR(CURRENT_DATE) - YEAR(birth_date) >= 5);

-- 외래키 추가
ALTER TABLE students
ADD CONSTRAINT fk_student_class
    FOREIGN KEY (class_id) 
    REFERENCES classes(class_id);

DROP

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
-- 테이블 삭제
-- CASCADE 옵션은 관련된 모든 객체도 함께 삭제
DROP TABLE IF EXISTS students CASCADE;

-- 데이터베이스 삭제
DROP DATABASE IF EXISTS school_management;

-- 뷰 삭제
DROP VIEW IF EXISTS student_summary;

-- 인덱스 삭제
DROP INDEX idx_student_name ON students;

TRUNCATE

1
2
-- 테이블의 모든 데이터 삭제
TRUNCATE TABLE students;

RENAME

1
2
-- 테이블 이름 변경
RENAME TABLE students TO school_students;

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

DML은 데이터베이스에 저장된 데이터를 조회하거나 삽입, 수정, 삭제하는 데 사용된다.

특징:

비절차적 특성: DML은 사용자가 원하는 결과(WHAT)만을 명세하고, 데이터에 접근하는 방법(HOW)은 시스템이 결정한다.
트랜잭션 발생: DML 명령어 실행 시 트랜잭션이 발생하며, 실행 결과를 취소(ROLLBACK)하거나 확정(COMMIT)할 수 있다.
조건문 사용: WHERE 절을 사용하여 특정 조건에 맞는 데이터만 조작할 수 있다.
정렬 기능: ORDER BY 절을 사용하여 결과를 특정 열 기준으로 정렬할 수 있다.

주요 키워드와 구문

키워드	개념 및 역할
`SELECT`	데이터를 조회합니다.
`INSERT`	데이터를 삽입합니다.
`UPDATE`	기존 데이터를 수정합니다.
`DELETE`	데이터를 삭제합니다.

예제

SELECT

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-- 기본적인 데이터 조회
-- employees 테이블에서 직원의 기본 정보를 조회
SELECT 
    employee_id,
    first_name,
    last_name,
    salary
FROM employees
WHERE department_id = 10;

-- 조인을 사용한 복잡한 조회
-- 직원과 부서 정보를 함께 조회하면서 급여가 높은 순으로 정렬
SELECT 
    e.first_name,
    e.last_name,
    d.department_name,
    e.salary
FROM employees e
JOIN departments d 
    ON e.department_id = d.department_id
WHERE e.salary > 50000
ORDER BY e.salary DESC;

-- 그룹화와 집계 함수를 사용한 조회
-- 부서별 평균 급여와 직원 수를 계산
SELECT 
    d.department_name,
    COUNT(*) as employee_count,
    AVG(salary) as avg_salary,
    MAX(salary) as max_salary
FROM employees e
JOIN departments d 
    ON e.department_id = d.department_id
GROUP BY d.department_name
HAVING AVG(salary) > 60000;

INSERT

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- 단일 행 삽입
-- 새로운 직원 정보를 추가
INSERT INTO employees (
    first_name,
    last_name,
    email,
    hire_date,
    job_id,
    salary,
    department_id
) VALUES (
    '길동',
    '홍',
    'hong.gildong@company.com',
    CURRENT_DATE,
    'IT_PROG',
    50000,
    60
);

-- 다중 행 삽입
-- 여러 직원 정보를 한 번에 추가
INSERT INTO employees (
    first_name, last_name, department_id
) VALUES 
    ('철수', '김', 20),
    ('영희', '이', 20),
    ('민수', '박', 30);

-- 다른 테이블의 데이터를 이용한 삽입
-- 임시 테이블의 데이터를 이용해 새로운 직원 추가
INSERT INTO employees (
    first_name, last_name, department_id
)
SELECT 
    first_name,
    last_name,
    department_id
FROM temp_employees
WHERE hire_status = 'APPROVED';

UPDATE

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 단순 업데이트
-- 특정 부서 직원들의 급여를 인상
UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 20;

-- 다중 컬럼 업데이트
-- 직원의 여러 정보를 한 번에 수정
UPDATE employees
SET 
    salary = salary * 1.15,
    commission_pct = commission_pct + 0.05,
    last_update = CURRENT_TIMESTAMP
WHERE performance_rating > 4;

-- 조인을 사용한 업데이트
-- 부서 정보를 기반으로 급여 수정
UPDATE employees e
JOIN departments d 
    ON e.department_id = d.department_id
SET e.salary = e.salary * 1.2
WHERE d.department_name = '영업부';

DELETE

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
-- 조건부 삭제
-- 퇴사한 직원 정보 삭제
DELETE FROM employees
WHERE termination_date IS NOT NULL;

-- 조인을 사용한 삭제
-- 폐쇄된 부서의 직원 정보 삭제
DELETE e
FROM employees e
JOIN departments d 
    ON e.department_id = d.department_id
WHERE d.department_status = 'CLOSED';

-- 서브쿼리를 사용한 삭제
-- 특정 조건의 직원 정보 삭제
DELETE FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location_id = 1700
);

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

DCL은 데이터베이스 사용자에게 권한을 부여하거나 회수하며, 보안과 접근 제어를 담당한다.

권한의 종류:
- 시스템 권한: 데이터베이스 전체에 대한 권한으로, 데이터베이스 연결, 테이블 생성 등과 같은 작업을 수행할 수 있다.
  1 2
  GRANT CREATE TABLE TO 'username'@'hostname'; -- 'username' 사용자에게 테이블을 생성할 수 있는 권한을 부여합니다.
- 객체 권한: 특정 데이터베이스 객체(예: 테이블, 뷰, 시퀀스 등)에 대한 권한으로, 해당 객체에 대한 SELECT, INSERT, UPDATE, DELETE 등의 작업을 수행할 수 있다.
  1 2
  GRANT SELECT, UPDATE ON Employees TO 'username'@'hostname'; -- 'username' 사용자가 'Employees' 테이블에 대해 SELECT와 UPDATE 권한을 갖도록 부여합니다.
WITH GRANT OPTION
- 데이터베이스에서 권한의 위임을 가능하게 하는 중요한 기능이다.
- 이것은 단순히 권한을 받는 것을 넘어서, 받은 권한을 다른 사용자에게 부여할 수 있는 능력을 제공한다.

주요 키워드와 구문

키워드	개념 및 역할
`GRANT`	특정 사용자에게 권한을 부여합니다.
`REVOKE`	특정 사용자에게 부여된 권한을 회수합니다.

예제

GRANT

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 기본 권한 부여
-- 특정 사용자에게 employees 테이블의 조회와 수정 권한을 부여
GRANT SELECT, UPDATE 
ON database_name.employees 
TO 'user_name'@'localhost';

-- 모든 권한 부여
-- 관리자 계정에 모든 데이터베이스 객체에 대한 모든 권한을 부여
GRANT ALL PRIVILEGES 
ON database_name.* 
TO 'admin_user'@'%';

-- 특정 컬럼에 대한 권한 부여
-- HR 담당자에게 급여 정보 조회 권한을 부여
GRANT SELECT (employee_id, salary, commission_pct)
ON employees 
TO 'hr_staff'@'localhost';

-- 권한 부여 권한 설정
-- 매니저가 다른 사용자에게 권한을 부여할 수 있도록 설정
GRANT SELECT, INSERT 
ON database_name.reports 
TO 'manager'@'localhost'
WITH GRANT OPTION;

REVOKE

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
-- 특정 권한 회수
-- 사용자로부터 데이터 수정과 삭제 권한을 회수
REVOKE UPDATE, DELETE 
ON database_name.employees 
FROM 'user_name'@'localhost';

-- 모든 권한 회수
-- 특정 사용자의 모든 권한을 회수
REVOKE ALL PRIVILEGES 
ON database_name.* 
FROM 'user_name'@'localhost';

-- GRANT OPTION 권한 회수
-- 권한을 부여할 수 있는 권한을 회수
REVOKE GRANT OPTION 
ON database_name.* 
FROM 'manager'@'localhost';

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

TCL은 트랜잭션 관리와 관련된 명령어로, 데이터의 일관성과 무결성을 유지하는 데 사용된다.

특징:

데이터의 일관성과 무결성을 유지한다.
영구적인 변경을 하기 전에 데이터의 변경 사항을 확인할 수 있다.
논리적으로 연관된 작업을 그룹화하여 처리할 수 있다.

트랜잭션은 데이터베이스의 신뢰성을 보장하기 위해 다음과 같은 ACID 특성을 가져야 한다:

Atomicity(원자성): 트랜잭션의 모든 연산은 모두 성공적으로 완료되거나, 전혀 수행되지 않은 것처럼 처리되어야 한다.
Consistency(일관성): 트랜잭션이 완료된 후에도 데이터베이스는 일관된 상태를 유지해야 한다.
Isolation(격리성): 동시에 실행되는 트랜잭션들은 서로 간섭하지 않고 독립적으로 수행되어야 한다.
Durability(지속성): 트랜잭션이 성공적으로 완료되면, 그 결과는 영구적으로 저장되어야 한다.

주요 키워드와 구문

키워드	개념 및 역할
START TRANSACTION / BEGIN	- 트랜잭션의 시작을 알리는 명령어
`COMMIT`	- 트랜잭션을 완료하고 변경 내용을 영구적으로 저장한다. - 트랜잭션이 성공적으로 완료되었음을 나타낸다. - COMMIT 이후에는 변경사항을 취소할 수 없다.
`ROLLBACK`	- 트랜잭션을 취소하고 이전 상태로 되돌린다. - 오류가 발생하거나 의도치 않은 변경이 있을 때 사용한다.
`SAVEPOINT`	- 트랜잭션 내에서 특정 지점을 설정하고 해당 지점으로 롤백할 수 있다. - 복잡한 트랜잭션에서 부분적 롤백을 가능하게 한다. - ROLLBACK TO SAVEPOINT 명령어로 특정 저장점까지만 롤백할 수 있다.

예제

START TRANSACTION / BEGIN

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- 단순한 트랜잭션 시작
START TRANSACTION;

-- 계좌 이체 예시
START TRANSACTION;
    -- 출금 계좌에서 금액 차감
    UPDATE accounts 
    SET balance = balance - 1000000
    WHERE account_id = 'A001';

    -- 입금 계좌에 금액 추가
    UPDATE accounts 
    SET balance = balance + 1000000
    WHERE account_id = 'A002';

    -- 거래 기록 저장
    INSERT INTO transaction_history (
        from_account,
        to_account,
        amount,
        transaction_date
    ) VALUES (
        'A001',
        'A002',
        1000000,
        CURRENT_TIMESTAMP
    );
COMMIT;

COMMIT

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- 기본적인 커밋
START TRANSACTION;
    INSERT INTO orders (product_id, quantity)
    VALUES (1001, 5);

    UPDATE inventory
    SET stock = stock - 5
    WHERE product_id = 1001;
COMMIT;

-- 조건부 커밋
START TRANSACTION;
    UPDATE accounts 
    SET balance = balance - 50000
    WHERE account_id = 'A001';

    -- 잔액 확인
    SELECT @current_balance := balance 
    FROM accounts 
    WHERE account_id = 'A001';

    IF @current_balance >= 0 THEN
        COMMIT;
    ELSE
        ROLLBACK;
    END IF;

ROLLBACK

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- 기본적인 롤백
START TRANSACTION;
    DELETE FROM products 
    WHERE category = 'DISCONTINUED';

    -- 삭제된 제품 수 확인
    SELECT @deleted_count := COUNT(*) 
    FROM products;

    -- 너무 많은 제품이 삭제되었다면 롤백
    IF @deleted_count > 100 THEN
        ROLLBACK;
    ELSE
        COMMIT;
    END IF;

-- 에러 처리를 포함한 롤백
START TRANSACTION;
    BEGIN TRY
        -- 재고 업데이트
        UPDATE inventory
        SET stock = stock - @order_quantity
        WHERE product_id = @product_id;

        -- 주문 생성
        INSERT INTO orders (…)
        VALUES (…);

        COMMIT;
    END TRY
    BEGIN CATCH
        ROLLBACK;
        -- 에러 로그 기록
        INSERT INTO error_log (error_message, error_date)
        VALUES (ERROR_MESSAGE(), CURRENT_TIMESTAMP);
    END CATCH;

SAVEPOINT

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
-- 세이브포인트 사용 예시
START TRANSACTION;
    INSERT INTO customers (name, email)
    VALUES ('홍길동', 'hong@email.com');

    SAVEPOINT customer_created;

    INSERT INTO orders (customer_id, product_id)
    VALUES (LAST_INSERT_ID(), 1001);

    -- 주문 처리 중 문제 발생 시
    IF @error_occurred THEN
        ROLLBACK TO customer_created;
        -- 고객 정보는 유지하고 주문만 취소
    END IF;

    COMMIT;

-- 복잡한 트랜잭션에서의 세이브포인트 활용
START TRANSACTION;
    -- 제품 정보 업데이트
    UPDATE products
    SET price = price * 1.1
    WHERE category = 'Electronics';

    SAVEPOINT price_updated;

    -- 재고 정보 업데이트
    UPDATE inventory
    SET reorder_point = reorder_point + 10
    WHERE product_id IN (
        SELECT product_id 
        FROM products 
        WHERE category = 'Electronics'
    );

    -- 재고 업데이트에 문제가 있다면
    IF @inventory_error THEN
        ROLLBACK TO price_updated;
        -- 가격 변경은 유지하고 재고 업데이트만 취소
    END IF;

    COMMIT;

참고 및 출처

keyword

Keyword SQL(Structured Query Language)는 데이터베이스를 관리하고 조작하기 위한 표준 언어로, 다양한 키워드를 통해 데이터 정의, 조작, 제어, 트랜잭션 관리 등을 수행한다. 데이터 조회 (Query) 키워드 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 -- SELECT: 데이터를 조회하는 기본 키워드 -- 지정된 컬럼의 데이터를 결과셋으로 반환 SELECT employee_id, first_name, salary FROM employees; -- FROM: 데이터를 가져올 테이블을 지정 -- 여러 테이블을 콤마로 구분하거나 JOIN을 사용할 수 있음 SELECT * FROM employees, departments; -- DISTINCT: 결과에서 중복된 행을 제거하는 데 사용 -- 기본 DISTINCT 사용 -- 부서별 unique한 직무 목록 조회 SELECT DISTINCT job_id FROM employees; -- 여러 컬럼에 DISTINCT 적용 -- 부서와 직무의 unique한 조합 조회 SELECT DISTINCT department_id, job_id FROM employees; -- COUNT와 함께 사용 -- 회사에 존재하는 직무 개수 조회 SELECT COUNT(DISTINCT job_id) as unique_jobs FROM employees; -- GROUP BY와 함께 사용 SELECT department_id, COUNT(DISTINCT job_id) as job_types FROM employees GROUP BY department_id; 결과 제한 1 2 3 4 5 6 7 8 9 10 11 12 13 14 -- LIMIT - 반환되는 결과의 최대 행 수를 제한합니다. SELECT * FROM employees LIMIT 10 -- 상위 10개 행만 반환 -- OFFSET - 결과의 시작 위치를 지정합니다. LIMIT와 함께 자주 사용됩니다. SELECT * FROM employees LIMIT 10 OFFSET 20 -- 21번째부터 30번째 행을 반환 -- FETCH - SQL 표준의 LIMIT와 유사한 기능을 합니다. SELECT * FROM employees FETCH FIRST 10 ROWS ONLY -- 페이지당 10개 항목, 3번째 페이지 조회 SELECT * FROM products ORDER BY name LIMIT 10 OFFSET 20; -- (페이지 번호 - 1) * 페이지 크기 = OFFSET 조건 연산자 키워드 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 -- WHERE: 조건절을 지정하여 특정 조건을 만족하는 데이터만 조회 -- AND, OR을 사용하여 여러 조건 조합 가능 SELECT * FROM employees WHERE salary > 50000 AND department_id = 10; -- IN: 값 목록 중 포함 여부 -- BETWEEN: 범위 조건 -- LIKE: 패턴 매칭 -- IS NULL: NULL 값 확인 SELECT * FROM employees WHERE department_id IN (10, 20, 30) AND salary BETWEEN 40000 AND 60000 AND first_name LIKE '김%' AND manager_id IS NOT NULL; -- CASE - 조건에 따라 다른 값을 반환합니다. -- WHEN - CASE 문에서 조건을 지정합니다. -- THEN - 조건이 참일 때 반환할 값을 지정합니다. -- ELSE - 모든 조건이 거짓일 때 반환할 값을 지정합니다. SELECT name, CASE WHEN age < 20 THEN 'Young' WHEN age < 60 THEN 'Adult' ELSE 'Senior' END as age_group FROM users; 정렬과 그룹화 키워드 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 -- GROUP BY: 지정된 컬럼을 기준으로 데이터를 그룹화 -- 주로 집계 함수와 함께 사용 SELECT department_id, AVG(salary) FROM employees GROUP BY department_id; -- ORDER BY: 결과를 정렬 -- ASC(오름차순), DESC(내림차순) 지정 가능 SELECT * FROM employees ORDER BY salary DESC, first_name ASC; -- HAVING: GROUP BY로 그룹화된 데이터에 대한 조건 지정 -- WHERE는 개별 행에 대한 조건, HAVING은 그룹에 대한 조건 SELECT department_id, AVG(salary) FROM employees GROUP BY department_id HAVING AVG(salary) > 50000; 조인(Join) 관련 키워드 JOIN은 두 개 이상의 테이블을 연결하여 데이터를 검색하는 방법이다. JOIN을 사용하면 여러 테이블의 데이터를 하나의 결과 집합으로 결합할 수 있다. ...