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';

-- 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이면 → 다른 사람이 먼저 수정!