MySQL CheatSheet

-- 데이터베이스 생성
CREATE DATABASE mydb;

-- 데이터베이스 선택
USE mydb;

-- 데이터베이스 목록 보기
SHOW DATABASES;

-- 데이터베이스 삭제
DROP DATABASE mydb;

-- 테이블 생성
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 테이블 구조 보기
DESCRIBE users;

-- 테이블 목록 보기
SHOW TABLES;

-- 데이터 삽입
INSERT INTO users (name, email)
VALUES ('홍길동', 'hong@example.com');

-- 데이터 조회
SELECT * FROM users;
SELECT name, email FROM users WHERE id = 1;

-- 데이터 수정
UPDATE users SET email = 'new@example.com' WHERE id = 1;

-- 데이터 삭제
DELETE FROM users WHERE id = 1;

-- 숫자 타입
INT, BIGINT, DECIMAL(10,2), FLOAT, DOUBLE

-- 문자열 타입
VARCHAR(255), CHAR(10), TEXT, LONGTEXT

-- 날짜 타입
DATE, TIME, DATETIME, TIMESTAMP

-- 기타 타입
BOOLEAN, JSON, BLOB

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

-- 서브쿼리 예제
SELECT name FROM users
WHERE id IN (
    SELECT user_id FROM orders
    WHERE order_date > '2024-01-01'
);

-- EXISTS 사용
SELECT name FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id
);

-- ROW_NUMBER, RANK, DENSE_RANK
SELECT name, salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) as rn,
    RANK() OVER (ORDER BY salary DESC) as rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rank
FROM employees;

-- PARTITION BY
SELECT name, department, salary,
    AVG(salary) OVER (PARTITION BY department) as avg_dept_salary
FROM employees;

-- 기본 CTE
WITH high_salary_emp AS (
    SELECT name, salary, department
    FROM employees
    WHERE salary > 50000
)
SELECT department, AVG(salary)
FROM high_salary_emp
GROUP BY department;

-- 재귀 CTE
WITH RECURSIVE hierarchy AS (
    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, h.level + 1
    FROM employees e
    JOIN hierarchy h ON e.manager_id = h.id
)
SELECT * FROM hierarchy;

-- 문자열 조작
CONCAT('Hello', ' ', 'World')
SUBSTRING('Hello World', 1, 5)
LENGTH('Hello World')
UPPER('hello'), LOWER('HELLO')
TRIM('  hello  ')
REPLACE('Hello World', 'World', 'MySQL')
LEFT('Hello', 3), RIGHT('Hello', 3)

-- 패턴 매칭
LIKE '%pattern%'
REGEXP '^[A-Za-z]+$'

-- 현재 날짜/시간
NOW(), CURDATE(), CURTIME()

-- 날짜 계산
DATE_ADD(NOW(), INTERVAL 1 DAY)
DATE_SUB(NOW(), INTERVAL 1 MONTH)
DATEDIFF('2024-12-31', '2024-01-01')

-- 날짜 포맷
DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s')
YEAR(NOW()), MONTH(NOW()), DAY(NOW())

-- 날짜 추출
EXTRACT(YEAR FROM NOW())

-- 기본 집계 함수
COUNT(*), COUNT(column)
SUM(column), AVG(column)
MIN(column), MAX(column)

-- 고급 집계
GROUP_CONCAT(column)
VARIANCE(column), STDDEV(column)

-- 조건부 집계
SUM(CASE WHEN condition THEN 1 ELSE 0 END)
COUNT(DISTINCT column)

-- 인덱스 생성
CREATE INDEX idx_name ON users(name);
CREATE INDEX idx_email ON users(email);

-- 복합 인덱스
CREATE INDEX idx_name_email ON users(name, email);

-- 유니크 인덱스
CREATE UNIQUE INDEX idx_unique_email ON users(email);

-- 인덱스 삭제
DROP INDEX idx_name ON users;

-- 인덱스 확인
SHOW INDEX FROM users;

-- 실행 계획 확인
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- 쿼리 분석
ANALYZE TABLE users;

-- 슬로우 쿼리 로그
SET global slow_query_log = 'ON';
SET global long_query_time = 2;

-- 쿼리 캐시
SELECT SQL_CACHE * FROM users;
SELECT SQL_NO_CACHE * FROM users;

-- 테이블 파티셔닝
CREATE TABLE orders (
    id INT,
    order_date DATE,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p0 VALUES LESS THAN (2023),
    PARTITION p1 VALUES LESS THAN (2024),
    PARTITION p2 VALUES LESS THAN (2025)
);

-- 테이블 최적화
OPTIMIZE TABLE users;

-- 통계 정보 업데이트
UPDATE TABLE users;