필사 모드: SQL 실무 치트시트 — 매일 쓰는 명령어 총정리

-- 기본 조회
SELECT * FROM users WHERE age >= 20 ORDER BY created_at DESC LIMIT 10;

-- 특정 컬럼만
SELECT id, name, email FROM users WHERE status = 'active';

-- 별칭 (Alias)
SELECT
    u.name AS user_name,
    COUNT(o.id) AS order_count,
    SUM(o.amount) AS total_spent
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.name;

-- DISTINCT (중복 제거)
SELECT DISTINCT department FROM employees;

-- BETWEEN, IN, LIKE
SELECT * FROM products
WHERE price BETWEEN 10000 AND 50000
  AND category IN ('electronics', 'books')
  AND name LIKE '%갤럭시%';

-- NULL 처리
SELECT name, COALESCE(phone, '미등록') AS phone
FROM users
WHERE email IS NOT NULL;

-- 단건 삽입
INSERT INTO users (name, email, age) VALUES ('김영주', 'yj@example.com', 30);

-- 다건 삽입
INSERT INTO users (name, email, age) VALUES
    ('홍길동', 'hong@example.com', 25),
    ('이순신', 'lee@example.com', 35),
    ('세종대왕', 'sejong@example.com', 45);

-- SELECT 결과를 INSERT (테이블 복사)
INSERT INTO users_backup (name, email, age)
SELECT name, email, age FROM users WHERE status = 'active';

-- UPSERT (있으면 UPDATE, 없으면 INSERT)
-- PostgreSQL
INSERT INTO users (email, name, login_count)
VALUES ('yj@example.com', '김영주', 1)
ON CONFLICT (email)
DO UPDATE SET
    login_count = users.login_count + 1,
    last_login = NOW();

-- MySQL
INSERT INTO users (email, name, login_count)
VALUES ('yj@example.com', '김영주', 1)
ON DUPLICATE KEY UPDATE
    login_count = login_count + 1,
    last_login = NOW();

-- 기본 수정
UPDATE users SET status = 'inactive' WHERE last_login < '2025-01-01';

-- 여러 컬럼 동시 수정
UPDATE products
SET price = price * 1.1,           -- 10% 인상
    updated_at = NOW()
WHERE category = 'electronics';

-- JOIN UPDATE (다른 테이블 참조하여 수정)
-- PostgreSQL
UPDATE orders o
SET status = 'cancelled'
FROM users u
WHERE o.user_id = u.id
  AND u.status = 'banned';

-- MySQL
UPDATE orders o
JOIN users u ON o.user_id = u.id
SET o.status = 'cancelled'
WHERE u.status = 'banned';

-- CASE를 사용한 조건부 UPDATE
UPDATE employees
SET salary = CASE
    WHEN department = 'engineering' THEN salary * 1.15
    WHEN department = 'sales' THEN salary * 1.10
    ELSE salary * 1.05
END
WHERE hire_date < '2024-01-01';

-- ⚠️ 실수 방지: WHERE 없이 UPDATE하면 전체 수정!
-- 항상 SELECT로 먼저 확인!
SELECT * FROM users WHERE last_login < '2025-01-01';  -- 먼저 확인
UPDATE users SET status = 'inactive' WHERE last_login < '2025-01-01';

-- 기본 삭제
DELETE FROM sessions WHERE expired_at < NOW();

-- JOIN DELETE
-- PostgreSQL
DELETE FROM orders
USING users
WHERE orders.user_id = users.id AND users.status = 'deleted';

-- MySQL
DELETE o FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.status = 'deleted';

-- TRUNCATE (전체 삭제, 빠름, AUTO_INCREMENT 리셋)
TRUNCATE TABLE logs;

-- ⚠️ 소프트 삭제 패턴 (권장)
UPDATE users SET deleted_at = NOW() WHERE id = 123;
-- 조회 시:
SELECT * FROM users WHERE deleted_at IS NULL;

-- INNER JOIN (양쪽 모두 있는 것만)
SELECT u.name, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN (왼쪽 전부 + 오른쪽 매칭)
SELECT u.name, COALESCE(COUNT(o.id), 0) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.name;
-- → 주문이 없는 사용자도 포함 (order_count = 0)

-- RIGHT JOIN (오른쪽 전부 + 왼쪽 매칭)
-- 잘 안 씀, LEFT JOIN을 뒤집는 게 가독성 좋음

-- FULL OUTER JOIN (양쪽 전부)
SELECT u.name, o.amount
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

-- CROSS JOIN (모든 조합, 카테시안 곱)
SELECT s.size, c.color
FROM sizes s CROSS JOIN colors c;
-- 3 sizes × 4 colors = 12 combinations

-- SELF JOIN (자기 자신과 결합)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

JOIN 다이어그램:

INNER JOIN:    A ∩ B (교집합만)
LEFT JOIN:     A + (A ∩ B)
RIGHT JOIN:    (A ∩ B) + B
FULL OUTER:    A ∪ B (합집합)

-- 기본 집계
SELECT
    department,
    COUNT(*) AS emp_count,
    AVG(salary) AS avg_salary,
    MAX(salary) AS max_salary,
    MIN(salary) AS min_salary,
    SUM(salary) AS total_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000000  -- 평균 연봉 5천만 이상 부서만
ORDER BY avg_salary DESC;

-- ROLLUP (소계 + 총계)
SELECT
    COALESCE(department, '=== 전체 ===') AS department,
    COALESCE(position, '--- 소계 ---') AS position,
    COUNT(*) AS count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY ROLLUP(department, position);

-- WHERE 서브쿼리
SELECT * FROM users
WHERE id IN (
    SELECT user_id FROM orders
    WHERE amount > 1000000
);

-- FROM 서브쿼리 (인라인 뷰)
SELECT dept_name, avg_salary
FROM (
    SELECT department AS dept_name, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) sub
WHERE avg_salary > 60000000;

-- EXISTS (존재 여부 확인, IN보다 대량 데이터에서 빠름)
SELECT u.name
FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id AND o.status = 'completed'
);

-- 스칼라 서브쿼리 (SELECT 절)
SELECT
    name,
    salary,
    salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees;

-- 기본 CTE
WITH active_users AS (
    SELECT id, name, email
    FROM users
    WHERE status = 'active' AND last_login > NOW() - INTERVAL '30 days'
),
user_orders AS (
    SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total
    FROM orders
    WHERE created_at > NOW() - INTERVAL '30 days'
    GROUP BY user_id
)
SELECT
    au.name,
    au.email,
    COALESCE(uo.order_count, 0) AS orders,
    COALESCE(uo.total, 0) AS total_spent
FROM active_users au
LEFT JOIN user_orders uo ON au.id = uo.user_id
ORDER BY total_spent DESC;

-- 재귀 CTE (조직도, 카테고리 트리)
WITH RECURSIVE org_tree AS (
    -- 기저: CEO (manager_id가 NULL)
    SELECT id, name, manager_id, 1 AS level
    FROM employees WHERE manager_id IS NULL

    UNION ALL

    -- 재귀: 부하 직원 탐색
    SELECT e.id, e.name, e.manager_id, ot.level + 1
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT REPEAT('  ', level - 1) || name AS org_chart, level
FROM org_tree
ORDER BY level, name;

-- ROW_NUMBER (순번)
SELECT
    name, department, salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank_all,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_dept
FROM employees;

-- RANK vs DENSE_RANK
-- RANK: 1, 2, 2, 4 (공동 2등 후 4등)
-- DENSE_RANK: 1, 2, 2, 3 (공동 2등 후 3등)

-- LAG / LEAD (이전/다음 행 참조)
SELECT
    date,
    revenue,
    LAG(revenue) OVER (ORDER BY date) AS prev_day,
    revenue - LAG(revenue) OVER (ORDER BY date) AS daily_change,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY date))
        / LAG(revenue) OVER (ORDER BY date) * 100, 1
    ) AS change_pct
FROM daily_sales;

-- 누적합 (Running Total)
SELECT
    date, amount,
    SUM(amount) OVER (ORDER BY date) AS running_total,
    AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM daily_sales;

-- NTILE (분위 나누기)
SELECT
    name, salary,
    NTILE(4) OVER (ORDER BY salary DESC) AS quartile
    -- 1=상위25%, 2=25~50%, 3=50~75%, 4=하위25%
FROM employees;

-- 인덱스 생성
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- 복합 인덱스 순서가 중요!
-- idx(a, b, c) 는:
--   WHERE a = 1                    ✅ 사용
--   WHERE a = 1 AND b = 2         ✅ 사용
--   WHERE a = 1 AND b = 2 AND c = 3 ✅ 사용
--   WHERE b = 2                    ❌ 미사용! (선두 컬럼 없음)
--   WHERE a = 1 AND c = 3         △ a만 사용 (b 건너뜀)

-- 부분 인덱스 (PostgreSQL)
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

-- 커버링 인덱스 (테이블 접근 없이 인덱스만으로 조회)
CREATE INDEX idx_covering ON orders(user_id, status, amount);
SELECT status, SUM(amount) FROM orders WHERE user_id = 123 GROUP BY status;
-- → 인덱스만 읽으면 됨! (테이블 I/O 없음)

-- PostgreSQL
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
GROUP BY u.name;

-- 읽는 법:
-- Seq Scan: 풀 테이블 스캔 (인덱스 필요?)
-- Index Scan: 인덱스 사용 ✅
-- Index Only Scan: 커버링 인덱스 ✅✅
-- Nested Loop: 소량 데이터 JOIN
-- Hash Join: 대량 데이터 JOIN
-- Sort: ORDER BY (메모리 초과 시 디스크)
-- Bitmap Heap Scan: 여러 인덱스 조합

-- MySQL
EXPLAIN FORMAT=JSON
SELECT * FROM orders WHERE user_id = 123 AND status = 'completed';

-- 예시 출력: EXPLAIN (ANALYZE, BUFFERS) 결과
 Hash Join  (cost=1082.00..3418.55 rows=4821 width=48) (actual time=8.412..41.903 rows=4795.00 loops=1)
   Hash Cond: (o.user_id = u.id)
   Buffers: shared hit=1204 read=812
   ->  Seq Scan on orders o  (cost=0.00..2015.00 rows=100000 width=24) (actual time=0.011..12.204 rows=100000.00 loops=1)
         Buffers: shared hit=200 read=815
   ->  Hash  (cost=1021.00..1021.00 rows=4880 width=32) (actual time=8.301..8.302 rows=4795.00 loops=1)
         Buckets: 8192  Batches: 1  Memory Usage: 384kB
         ->  Seq Scan on users u  (cost=0.00..1021.00 rows=4880 width=32) (actual time=0.019..7.104 rows=4795.00 loops=1)
               Filter: (status = 'active'::text)
               Rows Removed by Filter: 45205
               Buffers: shared hit=1004
 Planning Time: 0.312 ms
 Execution Time: 43.115 ms

BEGIN;

EXPLAIN ANALYZE
UPDATE orders SET status = 'cancelled' WHERE created_at < '2025-01-01';

ROLLBACK;

-- 인덱스는 email 컬럼 위에 있는데
CREATE INDEX idx_users_email ON users(email);

-- 조건은 lower(email) 위에 걸립니다 → 인덱스 못 씀
SELECT * FROM users WHERE lower(email) = 'yj@example.com';

-- 날짜도 마찬가지. created_at 인덱스가 있어도 캐스트하면 못 씁니다
SELECT * FROM orders WHERE created_at::date = '2026-08-16';

-- 해결 1) 표현식 인덱스를 만든다
CREATE INDEX idx_users_email_lower ON users(lower(email));
-- 문서 예시: upper(col) 위의 인덱스가 있으면
-- WHERE upper(col) = 'JIM' 조건이 그 인덱스를 쓸 수 있습니다.

-- 해결 2) 조건 쪽을 범위로 바꿔서 컬럼을 벗겨 낸다
SELECT * FROM orders
WHERE created_at >= '2026-08-16' AND created_at < '2026-08-17';

-- 대량 적재 직후에는 수동으로 돌립니다
ANALYZE orders;

-- 이후 계획이 바뀌는지 다시 확인
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;

-- OFFSET 방식 (간단하지만 대량 데이터에서 느림)
SELECT * FROM posts ORDER BY id DESC LIMIT 20 OFFSET 40;

-- 커서 기반 (대량 데이터 추천!)
SELECT * FROM posts
WHERE id < 12345  -- 마지막으로 본 id
ORDER BY id DESC
LIMIT 20;

-- 중복 행 찾기
SELECT email, COUNT(*) as cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- 중복 중 최신만 남기고 삭제
DELETE FROM users
WHERE id NOT IN (
    SELECT MIN(id) FROM users GROUP BY email
);

-- 또는 CTE로
WITH ranked AS (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) AS rn
    FROM users
)
DELETE FROM users WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

-- 오늘/이번달/올해
SELECT * FROM orders WHERE created_at::date = CURRENT_DATE;
SELECT * FROM orders WHERE DATE_TRUNC('month', created_at) = DATE_TRUNC('month', NOW());

-- 최근 7일 일별 통계
SELECT
    DATE(created_at) AS date,
    COUNT(*) AS orders,
    SUM(amount) AS revenue
FROM orders
WHERE created_at >= NOW() - INTERVAL '7 days'
GROUP BY DATE(created_at)
ORDER BY date;

-- 시간대별 분포
SELECT
    EXTRACT(HOUR FROM created_at) AS hour,
    COUNT(*) AS count
FROM orders
GROUP BY hour
ORDER BY hour;

-- SELECT FOR UPDATE (비관적 락)
BEGIN;
SELECT * FROM products WHERE id = 1 FOR UPDATE;  -- 다른 트랜잭션 대기
UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;

-- 낙관적 락 (version 컬럼)
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 5;  -- version이 맞을 때만 수정
-- 영향 받은 행 = 0이면 → 다른 사람이 먼저 수정!

-- OFFSET 방식: 5001페이지를 보려면 100020행을 만들어서 100000행을 버립니다
SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 100000;

-- 키셋(시크) 방식: 마지막으로 본 행의 값부터 이어서 읽습니다
SELECT id, title, created_at
FROM posts
WHERE (created_at, id) < ('2026-05-01 12:00:00', 84213)
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- 이 인덱스가 있어야 의미가 있습니다
CREATE INDEX idx_posts_created_id ON posts(created_at DESC, id DESC);

-- 예시 출력: OFFSET 방식
 Limit  (cost=8421.55..8423.24 rows=20 width=48) (actual time=182.401..182.408 rows=20.00 loops=1)
   ->  Index Scan Backward using idx_posts_created_id on posts
         (cost=0.42..84210.33 rows=1000000 width=48)
         (actual time=0.028..170.552 rows=100020.00 loops=1)
 Execution Time: 182.443 ms

-- 예시 출력: 키셋 방식
 Limit  (cost=0.42..2.11 rows=20 width=48) (actual time=0.031..0.052 rows=20.00 loops=1)
   ->  Index Scan Backward using idx_posts_created_id on posts
         (cost=0.42..84210.33 rows=899980 width=48)
         (actual time=0.029..0.047 rows=20.00 loops=1)
         Index Cond: (ROW(created_at, id) < ROW('2026-05-01 12:00:00'::timestamp, 84213))
 Execution Time: 0.081 ms

-- 현재 대기 중인 세션과 그 세션을 막고 있는 PID
SELECT
    a.pid,
    a.state,
    now() - a.xact_start AS xact_age,
    now() - a.query_start AS query_age,
    a.wait_event_type,
    a.wait_event,
    pg_blocking_pids(a.pid) AS blocked_by,
    left(a.query, 120) AS query
FROM pg_stat_activity a
WHERE a.backend_type = 'client backend'
ORDER BY xact_age DESC NULLS LAST;

-- 이건 SHARE 락을 잡습니다. 다른 트랜잭션은 읽을 수는 있지만
-- INSERT / UPDATE / DELETE 는 인덱스 생성이 끝날 때까지 블록됩니다.
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- 이건 SHARE UPDATE EXCLUSIVE 락을 잡습니다. 쓰기를 막지 않습니다.
CREATE INDEX CONCURRENTLY idx_orders_user_date ON orders(user_id, created_at DESC);

-- 실패하면 invalid 인덱스가 남습니다. psql 에서 \d 로 보면 INVALID 로 표시됩니다.
SELECT indexrelid::regclass AS index_name, indrelid::regclass AS table_name
FROM pg_index
WHERE NOT indisvalid;

-- 권장 복구 방법은 지우고 다시 만드는 것입니다.
DROP INDEX CONCURRENTLY idx_orders_user_date;

-- 나쁜 방법: 큰 테이블 전체를 스캔하는 동안 다른 갱신이 전부 잠깁니다
ALTER TABLE orders ADD CONSTRAINT orders_amount_positive CHECK (amount > 0);

-- 좋은 방법 1단계: NOT VALID 로 즉시 커밋 (테이블 스캔 없음)
ALTER TABLE orders ADD CONSTRAINT orders_amount_positive CHECK (amount > 0) NOT VALID;

-- 좋은 방법 2단계: 나중에 검증 (SHARE UPDATE EXCLUSIVE 락만 잡습니다)
ALTER TABLE orders VALIDATE CONSTRAINT orders_amount_positive;

-- 위험: orders.user_id 에 NULL 이 한 건이라도 있으면 결과가 0건입니다
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM orders);

-- 안전 1) NOT EXISTS 로 바꾼다 (권장)
SELECT * FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- 안전 2) NOT IN 을 유지해야 한다면 NULL 을 명시적으로 걸러 낸다
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL);