📊

SQL & 데이터베이스 치트시트

핵심 쿼리 문법, 다채로운 JOIN 구문, 집계 및 윈도우 함수, 인덱스 최적화와 트랜잭션 격리

#sql#database#postgres#mysql#query#crud최종 업데이트: 2026-09-21

SQL & 데이터베이스 치트시트 핵심 명령어 및 실전 가이드

핵심 쿼리 문법, 다채로운 JOIN 구문, 집계 및 윈도우 함수, 인덱스 최적화와 트랜잭션 격리

## 1. 기본 조회 & 필터링 (SELECT & WHERE)
```sql
-- 조건 필터링 및 페이징
SELECT id, username, email, created_at
FROM users
WHERE status = 'ACTIVE' 
  AND created_at >= NOW() - INTERVAL '30 days'
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;

-- NULL 안전 검사 및 패턴 매칭
SELECT * FROM products
WHERE description IS NOT NULL
  AND name ILIKE '%keyboard%'; -- 대소문자 무시 검색 (Postgres)
```

## 2. 테이블 결합 (JOIN 마스터)
```sql
-- INNER JOIN: 양쪽에 매칭되는 행만 반환
SELECT u.username, o.total_amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN: 왼쪽(users) 테이블은 매칭이 없어도 NULL로 모두 보존
SELECT u.username, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

-- 주문 내역이 한 번도 없는 사용자 찾기 (Anti-Join)
SELECT u.id, u.username
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;
```

## 3. 집계 & 그룹화 (GROUP BY & HAVING)
```sql
-- 그룹화 집계 및 조건 필터 (WHERE vs HAVING)
-- HAVING은 집계된 결과(SUM, COUNT 등)에 대한 필터링
SELECT category_id, COUNT(*) AS item_count, AVG(price) AS avg_price
FROM products
WHERE is_deleted = false
GROUP BY category_id
HAVING COUNT(*) >= 5;
```

## 4. 윈도우 함수 (순위 및 누적합)
```sql
-- 부서별 급여 순위 (RANK / DENSE_RANK / ROW_NUMBER)
SELECT 
  name, department, salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rank_in_dept
FROM employees;

-- 월별 매출 및 누적 매출(Cumulative Sum) 계산
SELECT 
  order_date, daily_revenue,
  SUM(daily_revenue) OVER (ORDER BY order_date) AS running_total
FROM daily_sales;
```

## 5. UPSERT (삽입 또는 기존 데이터 수정)
```sql
-- PostgreSQL ON CONFLICT
INSERT INTO user_settings (user_id, theme, notifications)
VALUES (101, 'dark', true)
ON CONFLICT (user_id) 
DO UPDATE SET theme = EXCLUDED.theme, updated_at = NOW();

-- MySQL ON DUPLICATE KEY UPDATE
INSERT INTO user_settings (user_id, theme)
VALUES (101, 'dark')
ON DUPLICATE KEY UPDATE theme = VALUES(theme);
```

## 6. 인덱스 생성 및 실행 계획 분석 (EXPLAIN)
```sql
-- 자주 검색하는 복합 인덱스 생성
CREATE INDEX idx_orders_user_status ON orders (user_id, status);

-- 쿼리 실행 계획 및 실제 소요 시간 분석
EXPLAIN ANALYZE
SELECT * FROM orders 
WHERE user_id = 42 AND status = 'COMPLETED';
```
`Seq Scan`(전체 테이블 스캔) 대신 `Index Scan`이 적용되는지 확인하는 것이 성능 튜닝의 기본입니다.

## 7. 트랜잭션 제어 (ACID)
```sql
BEGIN; -- 또는 START TRANSACTION;

UPDATE accounts SET balance = balance - 5000 WHERE id = 1;
UPDATE accounts SET balance = balance + 5000 WHERE id = 2;

-- 모든 작업이 정상일 때 최종 반영
COMMIT;

-- 중간에 예외 발생 시 이전 상태로 복원
-- ROLLBACK;
```

💬

SQL & 데이터베이스 치트시트

의견 및 질문

댓글 수정

댓글 삭제