SQL 기본 문법

1. DDL (Data Definition Language)

데이터베이스 객체(테이블, 뷰, 인덱스 등)를 정의·변경·삭제할 때 사용하는 문법입니다.

주로 데이터베이스 구조를 설계하거나 수정할 때 사용됩니다.

1.1. CREATE 문

• 용도: 새로운 데이터베이스 객체(테이블, 뷰, 인덱스, 데이터베이스 등)를 생성
• 주요 소분류:
  1. CREATE DATABASE
     • 설명: 새로운 데이터베이스를 생성
     • 예시:
     • sql CREATE DATABASE sample_db;
  2. CREATE SCHEMA
     • 설명: 데이터베이스 내 논리적 객체 그룹(스키마)을 생성 (DBMS마다 지원 여부 상이)
     • 예시 (PostgreSQL):
     • sql CREATE SCHEMA sales_schema;
  3. CREATE TABLE
     • 설명: 새로운 테이블을 생성. 컬럼, 자료형, 제약조건 등을 정의
     • 예시:
     • sql CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) NOT NULL UNIQUE, email VARCHAR(100), created_at DATETIME DEFAULT CURRENT_TIMESTAMP );
  4. CREATE INDEX
     • 설명: 특정 컬럼에 인덱스를 생성하여 검색 성능을 향상
     • 예시 (MySQL):
     • sql CREATE INDEX idx_users_username ON users(username);
  5. CREATE VIEW
     • 설명: 하나 이상의 테이블을 조합하거나 가공하여 가상의 테이블(뷰) 생성
     • 예시:
     • sql CREATE VIEW active_users AS SELECT id, username, email FROM users WHERE is_active = 1;
  6. CREATE SEQUENCE
     • 설명: 일련번호(시퀀스) 객체를 생성 (주로 Oracle, PostgreSQL)
     • 예시 (PostgreSQL):
     • sql CREATE SEQUENCE order_seq START WITH 1000 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 20;

1.2. ALTER 문

• 용도: 이미 존재하는 객체(테이블, 컬럼, 제약조건 등)를 수정
• 주요 소분류:
  1. ALTER TABLE … ADD COLUMN
     • 설명: 기존 테이블에 새로운 컬럼 추가
     • 예시:
     • sql ALTER TABLE users ADD COLUMN last_login DATETIME;
  2. ALTER TABLE … MODIFY/ALTER COLUMN
     • 설명: 컬럼의 자료형, NULL/NOT NULL, 기본값 등을 변경
     • 예시 (MySQL):
     • sql ALTER TABLE users MODIFY COLUMN email VARCHAR(150) NOT NULL;
     • 예시 (PostgreSQL):
     • sql ALTER TABLE users ALTER COLUMN email SET NOT NULL;
  3. ALTER TABLE … DROP COLUMN
     • 설명: 테이블에서 특정 컬럼을 삭제
     • 예시:
     • sql ALTER TABLE users DROP COLUMN last_login;
  4. ALTER TABLE … ADD CONSTRAINT / DROP CONSTRAINT
     • 설명: 제약조건(Primary Key, Foreign Key, Unique 등)을 추가·삭제
     • 예시:
     • sql -- FK 제약조건 추가 ALTER TABLE orders ADD CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id); -- FK 제약조건 삭제 ALTER TABLE orders DROP FOREIGN KEY fk_orders_user;
  5. ALTER VIEW … AS
     • 설명: 기존 뷰의 정의를 변경
     • 예시 (MySQL/PostgreSQL 공통):
     • sql ALTER VIEW active_users AS SELECT id, username, email, created_at FROM users WHERE is_active = 1;

1.3. DROP 문

• 용도: 데이터베이스 객체(테이블, 뷰, 인덱스, 데이터베이스 등)를 삭제
• 주요 소분류:
  1. DROP DATABASE
     • 설명: 데이터베이스 전체를 삭제 (주의: 복구 불가)
     • 예시:
     • sql DROP DATABASE sample_db;
  2. DROP TABLE
     • 설명: 테이블과 그 안의 데이터·구조를 완전히 삭제
     • 예시:
     • sql DROP TABLE IF EXISTS users;
  3. DROP INDEX
     • 설명: 인덱스를 삭제 (DBMS마다 문법이 약간 다름)
     • 예시 (MySQL):
     • sql DROP INDEX idx_users_username ON users;
     • 예시 (PostgreSQL):
     • sql DROP INDEX IF EXISTS idx_users_username;
  4. DROP VIEW
     • 설명: 뷰(가상 테이블)를 삭제
     • 예시:
     • sql DROP VIEW IF EXISTS active_users;
  5. DROP SCHEMA
     • 설명: 스키마(논리적 그룹) 및 포함된 모든 객체를 삭제 (지원 여부 상이)
     • 예시 (PostgreSQL):
     • sql DROP SCHEMA IF EXISTS sales_schema CASCADE;

1.4. TRUNCATE (테이블 초기화)

• 용도: 테이블 내 모든 행(row) 을 빠르게 삭제하고, 저장 공간을 초기화
• 특징:
  • DELETE FROM 테이블; 과 차이: 롤백 여부(DBMS마다 다름), 트랜잭션 로그 기록량 차이, 자동 커밋 등
  • 대부분의 DBMS에서 TRUNCATE 후 롤백이 불가하거나 제약조건에 제한이 있음
• 예시:
• sql TRUNCATE TABLE users;

---

2. DML (Data Manipulation Language)

저장된 데이터(테이블의 행)를 조회·삽입·수정·삭제할 때 사용하는 문법입니다.

실제로 데이터를 조작하는 용도로 주로 사용됩니다.

2.1. INSERT 문

• 용도: 테이블에 새로운 행(row)을 추가
• 주요 소분류:
  1. INSERT … VALUES
     • 설명: 직접 값을 지정하여 한 행 또는 다중 행을 삽입
     • 예시 (단일 행):
     • sql INSERT INTO users (username, email, is_active) VALUES ('ham_ham', 'ham@example.com', 1);
     • 예시 (다중 행):
     • sql INSERT INTO users (username, email, is_active) VALUES ('alice', 'alice@example.com', 1), ('bob', 'bob@example.com', 0);
  2. INSERT … SELECT
     • 설명: 다른 테이블의 결과(서브쿼리)를 삽입
     • 예시:
     • sql -- 예: orders_backup 테이블에 현재 orders 데이터를 복사 INSERT INTO orders_backup (order_id, user_id, total_amount) SELECT order_id, user_id, total_amount FROM orders WHERE order_date < '2025-01-01';
  3. INSERT … ON DUPLICATE KEY UPDATE (MySQL 전용)
     • 설명: 기본키나 유니크 키 충돌 시 행을 업데이트
     • 예시 (MySQL):
     • sql INSERT INTO users (id, username, email) VALUES (1, 'ham_ham', 'ham@example.com') ON DUPLICATE KEY UPDATE username = VALUES(username), email = VALUES(email);
  4. MERGE 문 (또는 UPSERT)
     • 설명: 대상 테이블에 행이 있으면 UPDATE, 없으면 INSERT (DBMS마다 문법 차이)
     • 예시 (Oracle/PostgreSQL):
     • sql MERGE INTO inventory tgt USING (SELECT product_id, quantity FROM staging_inventory) src ON (tgt.product_id = src.product_id) WHEN MATCHED THEN UPDATE SET tgt.quantity = src.quantity WHEN NOT MATCHED THEN INSERT (product_id, quantity) VALUES (src.product_id, src.quantity);

2.2. UPDATE 문

• 용도: 테이블 내 기존 행(row)의 값을 수정
• 주요 소분류:
  1. 단일 컬럼/다중 컬럼 업데이트
     • 설명: WHERE 조건에 맞는 행을 업데이트
     • 예시 (단일 컬럼):
     • sql UPDATE users SET email = 'new_email@example.com' WHERE id = 1;
     • 예시 (다중 컬럼):
     • sql UPDATE users SET username = 'updated_name', is_active = 0 WHERE id = 2;
  2. 서브쿼리 활용
     • 설명: 다른 테이블·서브쿼리 결과를 이용해 업데이트
     • 예시 (MySQL, PostgreSQL 공통):
     • sql UPDATE orders o SET o.status = 'COMPLETED' FROM payments p WHERE o.order_id = p.order_id AND p.paid = TRUE;

2.3. DELETE 문

• 용도: 테이블 내 특정 행(row)을 삭제
• 주요 소분류:
  1. 조건에 따른 삭제
     • 설명: WHERE 절로 조건을 지정해 삭제
     • 예시:
     • sql DELETE FROM users WHERE is_active = 0;
  2. 전체 삭제 (WHERE 생략)
     • 설명: 테이블 내 모든 행을 삭제 (단, 테이블 자체는 남음)
     • 예시:
     • sql DELETE FROM logs;
  3. JOIN을 활용한 삭제 (DBMS마다 문법 차이)
     • 설명: 다른 테이블과 조인하여 관련 행을 삭제
     • 예시 (MySQL):
     • sql DELETE o FROM orders o JOIN users u ON o.user_id = u.id WHERE u.is_active = 0;
     • 예시 (PostgreSQL):
     • sql DELETE FROM orders USING users WHERE orders.user_id = users.id AND users.is_active = 0;

---

3. DQL (Data Query Language)

저장된 데이터를 **조회(검색)**할 때 사용하는 문법입니다. 일반적으로 SELECT 구문 및 이와 관련된 서브쿼리, 조인, 집계 함수, 윈도우 함수 등이 포함됩니다.

3.1. SELECT 문

• 용도: 테이블(또는 여러 테이블)의 데이터를 조회
• 주요 소분류:
  1. 기본 SELECT
     • 설명: 특정 컬럼 선택, 또는 모든 컬럼 조회
     • 예시:
     • sql SELECT id, username, email FROM users; -- 또는 SELECT * FROM users;
  2. DISTINCT (중복 제거)
     • 설명: 중복된 결과 행을 제거
     • 예시:
     • sql SELECT DISTINCT city FROM customers;
  3. WHERE 절 (조건 필터링)
     • 설명: 특정 조건에 부합하는 행만 조회
     • 예시:
     • sql SELECT * FROM orders WHERE total_amount > 100;
  4. JOIN (조인)
     • 설명: 두 개 이상의 테이블을 결합하여 결과 생성
     • 소분류:
       • INNER JOIN: 교집합 형태로 일치하는 행만 반환
       • sql SELECT o.order_id, u.username, o.total_amount FROM orders o INNER JOIN users u ON o.user_id = u.id;
       • LEFT (OUTER) JOIN: 왼쪽 테이블의 모든 행 + 일치하는 오른쪽 행
       • sql SELECT u.id, u.username, o.order_id FROM users u LEFT JOIN orders o ON u.id = o.user_id;
       • RIGHT (OUTER) JOIN: 오른쪽 테이블의 모든 행 + 일치하는 왼쪽 행 (MySQL, PostgreSQL 등)
       • FULL (OUTER) JOIN: 두 테이블의 모든 행 + 일치 여부 상관없이 반환 (PostgreSQL 등)
  5. GROUP BY / HAVING (집계)
     • 설명: 그룹별 집계(합계, 평균, 카운트 등)를 수행하고, 그룹 조건 필터링
     • 예시:
     • sql SELECT user_id, COUNT(*) AS order_count, SUM(total_amount) AS total_spent FROM orders GROUP BY user_id HAVING SUM(total_amount) > 500;
  6. ORDER BY (정렬)
     • 설명: 조회 결과를 특정 컬럼 순서대로 정렬
     • 예시:
     • sql SELECT id, username, created_at FROM users ORDER BY created_at DESC;
  7. LIMIT / OFFSET (페이징)
     • 설명: 조회할 행의 개수를 제한하거나 건너뜀 (MySQL, PostgreSQL, SQLite 등)
     • 예시 (MySQL/PostgreSQL):
     • sql SELECT * FROM products ORDER BY price DESC LIMIT 10 OFFSET 20;
  8. 서브쿼리 (Subquery)
     • 설명: SELECT, FROM, WHERE 절 등에 다른 SELECT 문을 중첩
     • 종류:
       1. 스칼라 서브쿼리 (하나의 값 반환)
       2. sql SELECT u.username, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count FROM users u;
       3. 인라인 뷰 (FROM 절 내 서브쿼리)
       4. sql SELECT t.user_id, t.max_amount FROM ( SELECT user_id, MAX(total_amount) AS max_amount FROM orders GROUP BY user_id ) t;
       5. WHERE 절 서브쿼리
       6. sql SELECT * FROM users WHERE id IN ( SELECT user_id FROM orders WHERE total_amount > 1000 );
  9. 집계 함수 (Aggregate Functions)
     • 용도: 그룹화된 데이터 또는 전체 데이터에서 통계량 계산
     • 주요 함수:
       • COUNT(*), COUNT(col)
       • SUM(col)
       • AVG(col)
       • MIN(col), MAX(col)
     • 예시:
     • sql SELECT COUNT(*) AS total_users, AVG(age) AS avg_age FROM users WHERE is_active = 1;
  10. 윈도우 함수 (Window Functions)
      • 용도: 테이블 내에서 행 단위로 이동하며 계산 (순위, 누적 합계 등)
      • 주요 소분류:
        • 순위 관련: ROW_NUMBER(), RANK(), DENSE_RANK()
        • 누적 합계/평균: SUM(...) OVER (...), AVG(...) OVER (...)
        • 예시:
        • sql SELECT user_id, order_date, total_amount, SUM(total_amount) OVER (PARTITION BY user_id ORDER BY order_date) AS cumulative_sum FROM orders;
  11. WITH (CTE: Common Table Expression)
      • 용도: 복잡한 쿼리를 가독성 좋게 작성하기 위해 임시 결과 집합 정의
      • 예시:
      • sql WITH recent_orders AS ( SELECT user_id, total_amount FROM orders WHERE order_date >= '2025-01-01' ) SELECT u.username, ro.total_amount FROM users u JOIN recent_orders ro ON u.id = ro.user_id;

---

4. DCL (Data Control Language)

데이터베이스 객체 및 데이터에 대한 권한 관리(사용자 접근 제어, 권한 부여/회수 등)를 담당하는 문법입니다.

4.1. GRANT

• 용도: 사용자(또는 역할)에 대해 특정 객체(테이블, 뷰 등)에 권한을 부여
• 주요 소분류:
  1. 테이블 레벨 권한
     • 예시:
     • sql -- 사용자 'app_user'에게 users 테이블 SELECT, INSERT 권한 부여 GRANT SELECT, INSERT ON users TO 'app_user'@'localhost';
  2. 데이터베이스 레벨 권한
     • 예시 (MySQL):
     • sql -- sample_db 데이터베이스 내 모든 테이블에 대해 권한 부여 GRANT ALL PRIVILEGES ON sample_db.* TO 'db_admin'@'%';
  3. 시스템 레벨 권한 (예: CREATE USER, REPLICATION 등)
     • 예시 (MySQL):
     • sql GRANT REPLICATION SLAVE ON *.* TO 'replicant'@'%';
  4. 역할(Role) 사용 (DBMS마다 지원 여부 상이)
     • 설명: 여러 권한을 묶어 역할(Role)로 설정한 뒤, 사용자에게 역할을 부여
     • 예시 (PostgreSQL):
     • sql CREATE ROLE read_only; GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only; GRANT read_only TO app_user;

4.2. REVOKE

• 용도: 이미 부여된 권한을 회수(취소)
• 주요 소분류:
  1. 테이블/데이터베이스 권한 회수
     • 예시:
     • sql -- users 테이블에서 INSERT 권한 회수 REVOKE INSERT ON users FROM 'app_user'@'localhost';
  2. 시스템 권한 회수
     • 예시:
     • sql REVOKE SUPER ON *.* FROM 'admin_user'@'%';

---

5. TCL (Transaction Control Language)

트랜잭션의 시작·종료·롤백을 제어하는 문법입니다. 여러 DML 문을 하나의 원자적 단위로 묶어 실행하거나 취소할 때 사용합니다.

5.1. COMMIT

• 용도: 현재 트랜잭션 내에서 실행된 모든 변경사항을 영구적으로 반영(저장)
• 예시:
• sql START TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE user_id = 1; UPDATE accounts SET balance = balance + 100 WHERE user_id = 2; COMMIT; -- 두 UPDATE를 모두 확정

5.2. ROLLBACK

• 용도: 현재 트랜잭션 내에서 실행된 모든 변경사항을 취소(롤백)
• 예시:
• sql START TRANSACTION; UPDATE inventory SET stock = stock - 10 WHERE product_id = 100; -- 검증 중 오류 발견 ROLLBACK; -- UPDATE가 모두 취소됨

5.3. SAVEPOINT

• 용도: 트랜잭션 중간에 중간 지점(저장점) 을 설정하여, 일부만 롤백 가능
• 주요 소분류:
  1. SAVEPOINT 설정
     • 예시:
     • sql START TRANSACTION; INSERT INTO orders (user_id, total_amount) VALUES (1, 200); SAVEPOINT before_payment; UPDATE payments SET status = 'PAID' WHERE order_id = LAST_INSERT_ID();
  2. ROLLBACK TO SAVEPOINT
     • 설명: 지정한 저장점까지 롤백하고, 이후 작업은 유지
     • 예시:
     • sql ROLLBACK TO SAVEPOINT before_payment;
  3. RELEASE SAVEPOINT (일부 DBMS에서 지원)
     • 용도: 더 이상 필요 없는 저장점을 제거
     • 예시 (MySQL, PostgreSQL):
     • sql RELEASE SAVEPOINT before_payment;

5.4. SET TRANSACTION

• 용도: 트랜잭션의 격리 수준(TRANSACTION ISOLATION LEVEL) 등을 설정
• 예시 (MySQL, PostgreSQL 등):
• sql SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; START TRANSACTION; -- 트랜잭션 로직 COMMIT;

---

6. 기타 유틸리티 문법 및 명령어

SQL 표준 명령어 외에도 DBMS별로 제공하는 유틸리티 목적의 문법이 있습니다.

아래 예시는 MySQL, PostgreSQL, Oracle 등에서 자주 사용되는 명령어를 중심으로 설명합니다.

6.1. DESCRIBE / DESC

• 용도: 테이블의 컬럼 구조(컬럼명, 자료형, NULL 허용 여부, 키 등)를 확인
• 예시 (MySQL):
• sql DESCRIBE users; -- 또는 DESC orders;
• PostgreSQL: psql 콘솔에서 \\d 테이블명 사용

6.2. EXPLAIN / EXPLAIN ANALYZE

• 용도: 특정 쿼리에 대해 실행 계획(Execution Plan) 을 확인하여 성능 최적화
• 주요 소분류:
  1. EXPLAIN (MySQL)
     • 예시:
     • sql EXPLAIN SELECT * FROM users WHERE email = 'ham@example.com';
  2. EXPLAIN (PostgreSQL)
     • 예시:
     • sql EXPLAIN ANALYZE SELECT * FROM orders WHERE total_amount > 500;
     • ANALYZE 옵션을 붙이면 실제 실행 통계도 함께 출력

6.3. SHOW (MySQL 전용)

• 용도: DBMS 설정, 사용자, 테이블 목록, 인덱스 정보 등 메타데이터 조회
• 주요 예시:
  1. SHOW DATABASES
  2. sql SHOW DATABASES;
  3. SHOW TABLES
  4. sql USE sample_db; SHOW TABLES;
  5. SHOW COLUMNS / SHOW FIELDS
  6. sql SHOW COLUMNS FROM users; -- 또는 SHOW FIELDS FROM orders;
  7. SHOW INDEX
  8. sql SHOW INDEX FROM users;
  9. SHOW STATUS / SHOW VARIABLES
  10. sql SHOW STATUS LIKE 'Threads_connected'; SHOW VARIABLES LIKE 'max_connections';

6.4. COMMENT ON (Oracle, PostgreSQL)

• 용도: 테이블·컬럼 등 객체에 주석(설명) 추가
• 예시 (PostgreSQL):
• sql COMMENT ON TABLE users IS '회원 정보를 저장하는 테이블'; COMMENT ON COLUMN users.email IS '회원 이메일 주소';

6.5. ANALYZE (PostgreSQL, Oracle 등)

• 용도: 통계 정보를 수집하여 쿼리 최적화에 활용 (인덱스 선택, 실행 계획 개선 등)
• 예시 (PostgreSQL):
• sql ANALYZE users; ANALYZE VERBOSE orders;

6.6. VACUUM (PostgreSQL)

• 용도: 사용하지 않는 공간을 회수하고 테이블·인덱스 통계 업데이트
• 예시:
• sql VACUUM FULL; VACUUM ANALYZE users;

6.7. SET (세션/환경 설정)

• 용도: 현재 세션 또는 시스템 전반 설정 변경 (문자셋, 타임존, SQL 모드 등)
• 예시 (MySQL):
• sql SET NAMES 'utf8mb4'; SET time_zone = '+09:00'; SET sql_mode = 'STRICT_TRANS_TABLES';
• 예시 (PostgreSQL):
• sql SET search_path TO public, sales_schema; SET work_mem = '64MB';

---

요약 정리

plaintext

1. DDL (데이터 정의어)
   ├─ CREATE
   │   ├─ CREATE DATABASE
   │   ├─ CREATE SCHEMA
   │   ├─ CREATE TABLE
   │   ├─ CREATE INDEX
   │   ├─ CREATE VIEW
   │   └─ CREATE SEQUENCE
   ├─ ALTER
   │   ├─ ALTER TABLE … ADD/MODIFY/DROP COLUMN
   │   ├─ ALTER TABLE … ADD/DROP CONSTRAINT
   │   └─ ALTER VIEW … AS
   ├─ DROP
   │   ├─ DROP DATABASE
   │   ├─ DROP TABLE
   │   ├─ DROP INDEX
   │   └─ DROP VIEW
   └─ TRUNCATE TABLE

2. DML (데이터 조작어)
   ├─ INSERT
   │   ├─ INSERT … VALUES
   │   ├─ INSERT … SELECT
   │   ├─ INSERT … ON DUPLICATE KEY UPDATE (MySQL)
   │   └─ MERGE (UPSERT)
   ├─ UPDATE
   │   ├─ UPDATE … SET … WHERE …
   │   └─ UPDATE … FROM (JOIN)
   └─ DELETE
       ├─ DELETE FROM … WHERE …
       ├─ DELETE FROM … (전체 삭제)
       └─ DELETE … USING / JOIN 기반 삭제

3. DQL (데이터 조회어)
   └─ SELECT
       ├─ 기본 SELECT (컬럼/전체 조회)
       ├─ DISTINCT
       ├─ WHERE
       ├─ JOIN (INNER, LEFT, RIGHT, FULL)
       ├─ GROUP BY / HAVING
       ├─ ORDER BY
       ├─ LIMIT / OFFSET
       ├─ 서브쿼리 (스칼라, 인라인 뷰, WHERE 절)
       ├─ 집계 함수 (COUNT, SUM, AVG, MIN, MAX)
       ├─ 윈도우 함수 (ROW_NUMBER, SUM() OVER … 등)
       └─ WITH (CTE)

4. DCL (데이터 제어어)
   ├─ GRANT
   │   ├─ 테이블 레벨 권한
   │   ├─ 데이터베이스 레벨 권한
   │   ├─ 시스템 레벨 권한
   │   └─ 역할(Role) 부여
   └─ REVOKE
       ├─ 테이블/DB 권한 회수
       └─ 시스템 권한 회수

5. TCL (트랜잭션 제어어)
   ├─ COMMIT
   ├─ ROLLBACK
   ├─ SAVEPOINT
   │   ├─ SAVEPOINT 설정
   │   ├─ ROLLBACK TO SAVEPOINT
   │   └─ RELEASE SAVEPOINT
   └─ SET TRANSACTION (격리 수준 등 설정)

6. 기타 유틸리티 문법
   ├─ DESCRIBE / DESC (테이블 구조 확인)
   ├─ EXPLAIN / EXPLAIN ANALYZE (실행 계획 확인)
   ├─ SHOW (MySQL 전용 메타데이터 조회)
   ├─ COMMENT ON (Oracle/PostgreSQL: 객체 주석)
   ├─ ANALYZE (PostgreSQL: 통계 수집)
   ├─ VACUUM (PostgreSQL: 공간 회수·통계 업데이트)
   └─ SET (세션/시스템 설정 변경)