기본 CRUD
SELECT (조회)
-- 기본 조회
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 (삽입)
-- 단건 삽입
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 (수정)
-- 기본 수정
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 (삭제)
-- 기본 삭제
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;
JOIN (결합)
-- 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 (합집합)
GROUP BY + 집계 함수
-- 기본 집계
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);
서브쿼리 (Subquery)
-- 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 (Common Table Expression) — 가독성의 왕
-- 기본 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;
윈도우 함수 (Window Functions)
-- 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;
인덱스 (Index) 전략
-- 인덱스 생성
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 없음)
실행 계획 (EXPLAIN)
-- 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;
락(Lock) 주의
-- 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이면 → 다른 사람이 먼저 수정!
**Q1.** LEFT JOIN과 INNER JOIN의 차이는?
||LEFT JOIN: 왼쪽 테이블의 모든 행 포함, 오른쪽 매칭 없으면 NULL. INNER JOIN: 양쪽 모두 매칭되는 행만 반환||
**Q2.** UPSERT를 PostgreSQL과 MySQL에서 각각 어떻게 쓰나?
||PostgreSQL: INSERT ... ON CONFLICT (key) DO UPDATE SET ... MySQL: INSERT ... ON DUPLICATE KEY UPDATE ...||
**Q3.** 복합 인덱스 idx(a, b, c)에서 WHERE b = 2만 쓰면?
||인덱스를 사용하지 못함. 복합 인덱스는 왼쪽부터 순서대로 사용. 선두 컬럼(a) 없이는 작동하지 않음||
**Q4.** ROW_NUMBER와 DENSE_RANK의 차이는?
||ROW_NUMBER: 항상 연속 번호 (동점 없음). DENSE_RANK: 동점 시 같은 순위, 다음 순위는 바로 다음 번호 (1,2,2,3). RANK는 동점 후 건너뜀 (1,2,2,4)||
**Q5.** OFFSET 페이지네이션이 대량 데이터에서 느린 이유는?
||OFFSET N은 N개를 읽고 버림. OFFSET 100만이면 100만 행을 읽은 후 결과 반환. 커서 기반은 인덱스로 바로 시작점 접근||
**Q6.** 커버링 인덱스란?
||쿼리에 필요한 모든 컬럼이 인덱스에 포함되어 테이블 접근 없이 인덱스만으로 결과를 반환하는 것. Index Only Scan||
**Q7.** SELECT FOR UPDATE의 용도와 주의점은?
||비관적 락 — 선택한 행을 다른 트랜잭션이 수정하지 못하게 잠금. 주의: 트랜잭션이 길어지면 다른 트랜잭션이 대기하므로 데드락 위험||
**Q8.** WHERE 없이 UPDATE를 실행하면?
||테이블의 모든 행이 수정됨! 항상 SELECT로 대상을 먼저 확인한 후 UPDATE 실행. 프로덕션에서는 트랜잭션으로 감싸고 확인 후 COMMIT||
현재 단락 (1/340)
-- 기본 조회