✍️ 필사 모드: 데이터베이스 기초 완전 가이드 — SQL, 정규화, 인덱스, 트랜잭션, NoSQL

-- 기본 조회
SELECT name, email, created_at
FROM users
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 10;

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

-- INNER JOIN: 양쪽 모두 일치하는 행만
SELECT u.name, o.order_date
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN: 왼쪽 테이블 전체 + 일치하는 오른쪽
SELECT u.name, o.order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- RIGHT JOIN: 오른쪽 테이블 전체 + 일치하는 왼쪽
SELECT u.name, o.order_date
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- FULL OUTER JOIN: 양쪽 모두 포함
SELECT u.name, o.order_date
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

-- 부서별 평균 급여가 500만원 이상인 부서
SELECT
    department,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) >= 5000000
ORDER BY avg_salary DESC;

-- 평균 급여보다 높은 급여를 받는 직원
SELECT name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary) FROM employees
);

-- EXISTS를 사용한 서브쿼리
SELECT u.name
FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id
    AND o.created_at >= '2026-01-01'
);

-- IN을 사용한 서브쿼리
SELECT name, department
FROM employees
WHERE department IN (
    SELECT department
    FROM employees
    GROUP BY department
    HAVING COUNT(*) > 10
);

-- 1NF 위반: phone_numbers에 여러 값 저장
-- name: '홍길동', phone_numbers: '010-1234-5678, 010-9876-5432'

-- 1NF 준수: 별도 테이블로 분리
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE phone_numbers (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    phone VARCHAR(20) NOT NULL
);

-- 2NF 위반: student_name은 student_id에만 종속
-- (student_id, course_id) -> grade  (OK)
-- student_id -> student_name  (부분 종속!)

-- 2NF 준수
CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL
);

CREATE TABLE enrollments (
    student_id INTEGER REFERENCES students(student_id),
    course_id INTEGER REFERENCES courses(course_id),
    grade CHAR(2),
    PRIMARY KEY (student_id, course_id)
);

-- 3NF 위반: department_name은 department_id를 통해 이행 종속
-- employee_id -> department_id -> department_name

-- 3NF 준수
CREATE TABLE departments (
    department_id SERIAL PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department_id INTEGER REFERENCES departments(department_id)
);

-- 반정규화 예: 주문 테이블에 사용자 이름을 중복 저장
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    user_name VARCHAR(100),  -- 반정규화: 조회 시 JOIN 제거
    total_amount DECIMAL(10, 2),
    created_at TIMESTAMP DEFAULT NOW()
);

-- 단일 컬럼 인덱스
CREATE INDEX idx_users_email ON users(email);

-- 인덱스를 사용하는 쿼리 예시
SELECT * FROM users WHERE email = 'test@example.com';  -- 인덱스 사용
SELECT * FROM users WHERE email LIKE 'test%';           -- 인덱스 사용
SELECT * FROM users WHERE email LIKE '%test';           -- 인덱스 미사용 (선행 와일드카드)

CREATE INDEX idx_users_email_hash ON users USING HASH (email);

-- JSONB 컬럼에 GIN 인덱스
CREATE INDEX idx_products_tags ON products USING GIN (tags);

-- 전문 검색용 GIN 인덱스
CREATE INDEX idx_articles_search ON articles USING GIN (
    to_tsvector('korean', title || ' ' || content)
);

-- 공간 데이터 인덱스
CREATE INDEX idx_locations_coords ON locations USING GIST (coordinates);

-- 복합 인덱스: 선행 컬럼부터 매칭
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- 이 인덱스가 효과적인 쿼리들:
SELECT * FROM orders WHERE user_id = 1;                          -- 사용
SELECT * FROM orders WHERE user_id = 1 AND created_at > '2026-01-01'; -- 사용
SELECT * FROM orders WHERE created_at > '2026-01-01';            -- 미사용 (선행 컬럼 누락)

-- 커버링 인덱스
CREATE INDEX idx_orders_covering ON orders(user_id, created_at, total_amount);

-- 인덱스만으로 처리 가능 (Index Only Scan)
SELECT user_id, created_at, total_amount
FROM orders
WHERE user_id = 1 AND created_at > '2026-01-01';

-- 계좌 이체 트랜잭션 예시
BEGIN;

UPDATE accounts SET balance = balance - 100000
WHERE account_id = 'A001';

UPDATE accounts SET balance = balance + 100000
WHERE account_id = 'B001';

-- 잔액 확인
SELECT balance FROM accounts WHERE account_id = 'A001';

COMMIT;  -- 또는 문제 발생 시 ROLLBACK;

-- 격리 수준 설정
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN;
SELECT balance FROM accounts WHERE account_id = 'A001';
-- 이 트랜잭션 동안 다른 트랜잭션의 변경이 보이지 않음
COMMIT;

-- 데드락 시나리오
-- 트랜잭션 1: A -> B 순서로 잠금
-- 트랜잭션 2: B -> A 순서로 잠금

-- 해결 방법:
-- 1. 잠금 순서를 통일한다 (항상 ID가 작은 것부터)
-- 2. 타임아웃을 설정한다
-- 3. 트랜잭션 범위를 최소화한다

SET lock_timeout = '5s';

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2026-01-01'
GROUP BY u.name
ORDER BY order_count DESC
LIMIT 10;

-- N+1 문제 발생
-- 1번: SELECT * FROM users LIMIT 100;
-- 100번: SELECT * FROM orders WHERE user_id = ?;  (각 유저마다)

-- 해결: JOIN으로 한 번에 조회
SELECT u.*, o.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.id IN (SELECT id FROM users LIMIT 100);

-- 또는 서브쿼리 활용
SELECT u.*, (
    SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id
) AS order_count
FROM users u
LIMIT 100;

-- 안 좋은 예: 인덱스 무효화
SELECT * FROM users WHERE YEAR(created_at) = 2026;

-- 좋은 예: 인덱스 활용 가능
SELECT * FROM users
WHERE created_at >= '2026-01-01'
  AND created_at < '2027-01-01';

-- JSONB 컬럼 생성 및 활용
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    attributes JSONB
);

-- 데이터 삽입
INSERT INTO products (name, attributes)
VALUES ('노트북', '{"brand": "Samsung", "ram": 16, "storage": "512GB", "tags": ["electronics", "portable"]}');

-- JSONB 쿼리
SELECT name, attributes->>'brand' AS brand
FROM products
WHERE attributes->>'ram' = '16';

-- JSONB 포함 연산자
SELECT * FROM products
WHERE attributes @> '{"brand": "Samsung"}';

-- JSONB 배열 조회
SELECT * FROM products
WHERE attributes->'tags' ? 'electronics';

-- 재귀 CTE: 조직도 탐색
WITH RECURSIVE org_tree AS (
    -- 시작점: 최상위 관리자
    SELECT id, name, manager_id, 1 AS depth
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- 재귀: 하위 직원 탐색
    SELECT e.id, e.name, e.manager_id, t.depth + 1
    FROM employees e
    INNER JOIN org_tree t ON e.manager_id = t.id
)
SELECT * FROM org_tree ORDER BY depth, name;

-- 부서별 급여 순위
SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept,
    RANK() OVER (ORDER BY salary DESC) AS overall_rank,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary,
    salary - LAG(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS diff_from_prev
FROM employees;

-- 누적 합계
SELECT
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date ROWS UNBOUNDED PRECEDING) AS running_total
FROM daily_sales;

-- 범위 파티셔닝
CREATE TABLE events (
    id BIGSERIAL,
    event_type VARCHAR(50),
    payload JSONB,
    created_at TIMESTAMP NOT NULL
) PARTITION BY RANGE (created_at);

-- 월별 파티션 생성
CREATE TABLE events_2026_01 PARTITION OF events
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE events_2026_02 PARTITION OF events
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

CREATE TABLE events_2026_03 PARTITION OF events
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

-- 파티션별 인덱스 자동 생성
CREATE INDEX idx_events_type ON events(event_type);

// MongoDB 쿼리 예시
db.users.insertOne({
  name: "홍길동",
  email: "hong@example.com",
  address: {
    city: "서울",
    district: "강남구"
  },
  tags: ["premium", "active"]
});

// 집계 파이프라인
db.orders.aggregate([
  { $match: { status: "completed" } },
  { $group: {
      _id: "$user_id",
      totalSpent: { $sum: "$amount" },
      orderCount: { $sum: 1 }
  }},
  { $sort: { totalSpent: -1 } },
  { $limit: 10 }
]);

-- Cassandra CQL
CREATE TABLE sensor_data (
    sensor_id UUID,
    event_time TIMESTAMP,
    temperature DOUBLE,
    humidity DOUBLE,
    PRIMARY KEY (sensor_id, event_time)
) WITH CLUSTERING ORDER BY (event_time DESC);

// Neo4j Cypher 쿼리
// 친구의 친구 찾기 (2단계 관계)
MATCH (me:Person)-[:FRIEND]->(friend)-[:FRIEND]->(fof)
WHERE me.name = '홍길동'
  AND NOT (me)-[:FRIEND]->(fof)
  AND fof <> me
RETURN DISTINCT fof.name AS recommendation
LIMIT 10;

# 캐시 설정 (TTL 3600초)
SET user:1001:profile '{"name":"홍길동","email":"hong@example.com"}' EX 3600

# 캐시 조회
GET user:1001:profile

# 캐시 무효화
DEL user:1001:profile

# 세션 저장 (30분 만료)
HSET session:abc123 user_id 1001 role admin login_time "2026-04-12T10:00:00"
EXPIRE session:abc123 1800

# 세션 조회
HGETALL session:abc123

# 점수 추가
ZADD game:leaderboard 1500 "player:001"
ZADD game:leaderboard 2300 "player:002"
ZADD game:leaderboard 1800 "player:003"

# 상위 10명 조회 (높은 점수순)
ZREVRANGE game:leaderboard 0 9 WITHSCORES

# 특정 플레이어 순위 조회
ZREVRANK game:leaderboard "player:001"

# 채널 구독
SUBSCRIBE notifications:user:1001

# 메시지 발행
PUBLISH notifications:user:1001 '{"type":"order","message":"주문이 완료되었습니다"}'

# 락 획득 (NX: 존재하지 않을 때만, EX: 만료 시간)
SET lock:order:process "worker-1" NX EX 30

# 락 해제 (Lua 스크립트로 원자적 처리)
# 자신이 설정한 락만 해제하도록 값을 확인
EVAL "if redis.call('get', KEYS[1]) == ARGV[1] then return redis.call('del', KEYS[1]) else return 0 end" 1 lock:order:process "worker-1"

-- 사용자와 프로필: 1:1
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE user_profiles (
    user_id INTEGER PRIMARY KEY REFERENCES users(id),
    bio TEXT,
    avatar_url VARCHAR(500)
);

-- 사용자와 게시글: 1:N
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    title VARCHAR(200) NOT NULL,
    content TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 게시글과 태그: M:N (중간 테이블 필요)
CREATE TABLE tags (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE post_tags (
    post_id INTEGER REFERENCES posts(id),
    tag_id INTEGER REFERENCES tags(id),
    PRIMARY KEY (post_id, tag_id)
);

-- V1__create_users_table.sql
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- V2__add_status_to_users.sql
ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';
CREATE INDEX idx_users_status ON users(status);

-- V3__create_orders_table.sql
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES users(id),
    total_amount DECIMAL(10, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

# alembic/versions/001_create_users.py
from alembic import op
import sqlalchemy as sa

def upgrade():
    op.create_table(
        'users',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('email', sa.String(255), nullable=False, unique=True),
        sa.Column('name', sa.String(100), nullable=False),
        sa.Column('created_at', sa.DateTime(), server_default=sa.func.now()),
    )

def downgrade():
    op.drop_table('users')

-- NOT NULL 없이 추가 (기존 행에 영향 없음)
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

ALTER TABLE users DROP COLUMN phone;

-- CONCURRENTLY 옵션: 테이블 잠금 없이 인덱스 생성
CREATE INDEX CONCURRENTLY idx_orders_user ON orders(user_id);