SQL 기초 완전정복

-- 모든 직원 정보를 조회합니다
SELECT * FROM employees;

# MySQL 서버 설치
sudo apt update
sudo apt install mysql-server

# 서비스 시작
sudo systemctl start mysql

# 보안 설정 마법사
sudo mysql_secure_installation

# MySQL 접속
mysql -u root -p

-- MySQL Installer를 공식 사이트에서 다운로드 후 설치
-- 설치 후 MySQL Command Line Client 실행
mysql -u root -p

# 설치
sudo apt install sqlite3

# 데이터베이스 생성 및 접속
sqlite3 tutorial.db

-- 데이터베이스 생성
CREATE DATABASE shop_db DEFAULT CHARACTER SET utf8mb4;
USE shop_db;

-- 고객 테이블
CREATE TABLE customers (
    customer_id   INT          PRIMARY KEY AUTO_INCREMENT,
    name          VARCHAR(50)  NOT NULL,
    email         VARCHAR(100) UNIQUE NOT NULL,
    phone         VARCHAR(20),
    city          VARCHAR(30),
    join_date     DATE         DEFAULT (CURDATE()),
    grade         VARCHAR(10)  DEFAULT 'BRONZE'
);

-- 상품 테이블
CREATE TABLE products (
    product_id    INT          PRIMARY KEY AUTO_INCREMENT,
    product_name  VARCHAR(100) NOT NULL,
    category      VARCHAR(30),
    price         DECIMAL(10,2) NOT NULL,
    stock         INT          DEFAULT 0,
    created_at    DATETIME     DEFAULT NOW()
);

-- 주문 테이블
CREATE TABLE orders (
    order_id      INT          PRIMARY KEY AUTO_INCREMENT,
    customer_id   INT          NOT NULL,
    order_date    DATETIME     DEFAULT NOW(),
    total_amount  DECIMAL(12,2),
    status        VARCHAR(20)  DEFAULT 'PENDING',
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- 주문 상세 테이블
CREATE TABLE order_items (
    item_id       INT          PRIMARY KEY AUTO_INCREMENT,
    order_id      INT          NOT NULL,
    product_id    INT          NOT NULL,
    quantity      INT          NOT NULL,
    unit_price    DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id)   REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- 직원 테이블
CREATE TABLE employees (
    emp_id        INT          PRIMARY KEY AUTO_INCREMENT,
    name          VARCHAR(50)  NOT NULL,
    department    VARCHAR(30),
    position      VARCHAR(30),
    salary        DECIMAL(10,2),
    hire_date     DATE,
    manager_id    INT,
    FOREIGN KEY (manager_id) REFERENCES employees(emp_id)
);

-- 고객 데이터
INSERT INTO customers (name, email, phone, city, join_date, grade) VALUES
('김민수', 'minsu@mail.com',   '010-1234-5678', '서울',  '2023-01-15', 'GOLD'),
('이영희', 'younghee@mail.com', '010-2345-6789', '부산',  '2023-03-22', 'SILVER'),
('박철수', 'cheolsu@mail.com',  '010-3456-7890', '대전',  '2023-05-10', 'BRONZE'),
('정수진', 'sujin@mail.com',    '010-4567-8901', '서울',  '2023-07-04', 'GOLD'),
('최동현', 'donghyun@mail.com', '010-5678-9012', '인천',  '2023-09-18', 'SILVER'),
('한지은', 'jieun@mail.com',    '010-6789-0123', '서울',  '2024-01-05', 'BRONZE'),
('윤서준', 'seojun@mail.com',   '010-7890-1234', '대구',  '2024-02-14', 'GOLD'),
('강예린', 'yerin@mail.com',    '010-8901-2345', '부산',  '2024-04-20', 'BRONZE');

-- 상품 데이터
INSERT INTO products (product_name, category, price, stock) VALUES
('무선 키보드',       '전자기기',   45000,   120),
('무선 마우스',       '전자기기',   32000,   200),
('27인치 모니터',     '전자기기',   350000,  45),
('프로그래밍 입문서', '도서',       28000,   300),
('SQL 완전정복',      '도서',       32000,   250),
('노트북 파우치',     '액세서리',   25000,   180),
('USB-C 허브',        '액세서리',   55000,   90),
('기계식 키보드',     '전자기기',   89000,   60),
('데스크 매트',       '액세서리',   18000,   400),
('웹캠 HD',           '전자기기',   65000,   75);

-- 주문 데이터
INSERT INTO orders (customer_id, order_date, total_amount, status) VALUES
(1, '2024-01-10 14:30:00', 77000,  'COMPLETED'),
(1, '2024-02-15 09:00:00', 350000, 'COMPLETED'),
(2, '2024-01-22 11:20:00', 60000,  'COMPLETED'),
(3, '2024-03-05 16:45:00', 28000,  'COMPLETED'),
(4, '2024-03-18 10:10:00', 144000, 'SHIPPED'),
(5, '2024-04-01 13:55:00', 55000,  'SHIPPED'),
(6, '2024-04-12 08:30:00', 89000,  'PENDING'),
(7, '2024-04-20 17:00:00', 415000, 'PENDING'),
(2, '2024-05-03 12:00:00', 32000,  'CANCELLED'),
(1, '2024-05-15 15:30:00', 83000,  'COMPLETED');

-- 주문 상세 데이터
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
(1, 1, 1, 45000),
(1, 2, 1, 32000),
(2, 3, 1, 350000),
(3, 4, 1, 28000),
(3, 5, 1, 32000),
(4, 4, 1, 28000),
(5, 8, 1, 89000),
(5, 7, 1, 55000),
(6, 7, 1, 55000),
(7, 8, 1, 89000),
(8, 3, 1, 350000),
(8, 10,1, 65000),
(9, 5, 1, 32000),
(10,7, 1, 55000),
(10,4, 1, 28000);

-- 직원 데이터
INSERT INTO employees (name, department, position, salary, hire_date, manager_id) VALUES
('김대표', '경영',   'CEO',     12000000, '2020-01-01', NULL),
('이팀장', '개발',   '팀장',    7000000,  '2020-03-15', 1),
('박사원', '개발',   '사원',    4000000,  '2022-06-01', 2),
('최대리', '개발',   '대리',    5000000,  '2021-09-10', 2),
('정팀장', '마케팅', '팀장',    6500000,  '2020-07-20', 1),
('한사원', '마케팅', '사원',    3800000,  '2023-01-10', 5),
('윤과장', '영업',   '과장',    5500000,  '2021-02-28', 1),
('강사원', '영업',   '사원',    3500000,  '2023-08-15', 7);

-- 데이터베이스 목록 확인
SHOW DATABASES;

-- 데이터베이스 생성
CREATE DATABASE my_app DEFAULT CHARACTER SET utf8mb4;

-- 이미 존재하면 무시
CREATE DATABASE IF NOT EXISTS my_app;

-- 데이터베이스 선택 (사용)
USE my_app;

-- 데이터베이스 삭제
DROP DATABASE my_app;
DROP DATABASE IF EXISTS my_app;

CREATE TABLE students (
    id        INT         PRIMARY KEY AUTO_INCREMENT,
    name      VARCHAR(50) NOT NULL,
    age       INT         CHECK (age >= 0 AND age <= 150),
    major     VARCHAR(30) DEFAULT '미정',
    gpa       DECIMAL(3,2),
    enrolled  DATE        DEFAULT (CURDATE())
);

-- 테이블 목록
SHOW TABLES;

-- 테이블 구조 확인
DESC students;
-- 또는
DESCRIBE students;

-- 테이블 생성 SQL 보기
SHOW CREATE TABLE students;

-- 열 추가
ALTER TABLE students ADD COLUMN email VARCHAR(100);

-- 열 이름 및 타입 변경
ALTER TABLE students CHANGE COLUMN major department VARCHAR(50);

-- 열 타입만 변경
ALTER TABLE students MODIFY COLUMN name VARCHAR(100) NOT NULL;

-- 열 삭제
ALTER TABLE students DROP COLUMN email;

-- 테이블 이름 변경
ALTER TABLE students RENAME TO learners;

-- 테이블 완전 삭제 (구조 + 데이터)
DROP TABLE students;
DROP TABLE IF EXISTS students;

-- 데이터만 삭제 (구조 유지, 복구 불가)
TRUNCATE TABLE students;

-- BOOLEAN (MySQL에서는 TINYINT(1)의 별칭)
is_active BOOLEAN DEFAULT TRUE,

-- ENUM : 정해진 값만 입력 가능
gender ENUM('M', 'F', 'Other'),

-- JSON (MySQL 5.7+)
metadata JSON,

-- BLOB : 바이너리 데이터 (이미지 등)
profile_img BLOB

-- 기본 문법
SELECT 컬럼1, 컬럼2, ...
FROM 테이블명;

-- 고객 테이블의 모든 데이터 조회
SELECT * FROM customers;

-- 이름과 이메일만 조회
SELECT name, email
FROM customers;

-- 컬럼 별칭 (AS 키워드)
SELECT
    name       AS '고객명',
    email      AS '이메일',
    city       AS '도시',
    grade      AS '등급'
FROM customers;

-- 테이블 별칭
SELECT c.name, c.email
FROM customers AS c;

-- 어떤 도시의 고객이 있는지 (중복 제거)
SELECT DISTINCT city
FROM customers;

-- 여러 컬럼의 조합에서 중복 제거
SELECT DISTINCT city, grade
FROM customers;

-- 가격에 10% 할인 적용한 가격 계산
SELECT
    product_name AS '상품명',
    price        AS '원가',
    price * 0.9  AS '할인가',
    price - (price * 0.9) AS '할인액'
FROM products;

-- 이름과 도시를 합쳐서 표시
SELECT
    CONCAT(name, ' (', city, ')') AS '고객정보'
FROM customers;

-- 서울에 사는 고객 조회
SELECT * FROM customers
WHERE city = '서울';

-- 가격이 50000원 이상인 상품
SELECT product_name, price
FROM products
WHERE price >= 50000;

-- 급여가 500만원 이상인 직원
SELECT name, department, salary
FROM employees
WHERE salary >= 5000000;

-- AND : 두 조건 모두 만족
SELECT * FROM customers
WHERE city = '서울' AND grade = 'GOLD';

-- OR : 둘 중 하나만 만족
SELECT * FROM customers
WHERE city = '서울' OR city = '부산';

-- NOT : 조건의 반대
SELECT * FROM customers
WHERE NOT grade = 'BRONZE';

-- 복합 조건 (괄호로 우선순위 지정)
SELECT * FROM products
WHERE (category = '전자기기' OR category = '액세서리')
  AND price < 50000;

-- 가격이 30000 ~ 60000 사이인 상품
SELECT product_name, price
FROM products
WHERE price BETWEEN 30000 AND 60000;

-- 날짜 범위
SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31';

-- 서울, 부산, 대구에 사는 고객
SELECT * FROM customers
WHERE city IN ('서울', '부산', '대구');

-- NOT IN : 목록에 없는 것
SELECT * FROM orders
WHERE status NOT IN ('CANCELLED', 'PENDING');

-- '김'으로 시작하는 이름
SELECT * FROM customers
WHERE name LIKE '김%';

-- 이메일에 'mail'이 포함된 고객
SELECT * FROM customers
WHERE email LIKE '%mail%';

-- 이름이 정확히 3글자인 고객
SELECT * FROM customers
WHERE name LIKE '___';

-- '무선'으로 시작하는 상품
SELECT * FROM products
WHERE product_name LIKE '무선%';

-- 매니저가 없는 직원 (최상위 관리자)
SELECT * FROM employees
WHERE manager_id IS NULL;

-- 전화번호가 있는 고객
SELECT * FROM customers
WHERE phone IS NOT NULL;

-- 가격 오름차순 (기본값: ASC)
SELECT product_name, price
FROM products
ORDER BY price ASC;

-- 가격 내림차순
SELECT product_name, price
FROM products
ORDER BY price DESC;

-- 여러 컬럼으로 정렬 (1차: 도시 오름차순, 2차: 이름 오름차순)
SELECT name, city, grade
FROM customers
ORDER BY city ASC, name ASC;

-- 별칭이나 컬럼 번호로 정렬
SELECT product_name, price * 0.9 AS discount_price
FROM products
ORDER BY discount_price DESC;
-- 또는 ORDER BY 2 DESC; (2번째 컬럼)

-- 상위 5개 비싼 상품
SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 5;

-- 가장 최근 주문 3개
SELECT * FROM orders
ORDER BY order_date DESC
LIMIT 3;

-- 페이지당 5개씩 조회
-- 1페이지 (1~5번째)
SELECT * FROM products ORDER BY product_id LIMIT 5 OFFSET 0;

-- 2페이지 (6~10번째)
SELECT * FROM products ORDER BY product_id LIMIT 5 OFFSET 5;

-- 축약형: LIMIT offset, count
SELECT * FROM products ORDER BY product_id LIMIT 5, 5;

-- 모든 컬럼에 값 지정
INSERT INTO customers (name, email, phone, city, join_date, grade)
VALUES ('송하나', 'hana@mail.com', '010-1111-2222', '광주', '2024-06-01', 'BRONZE');

-- DEFAULT 값이 있는 컬럼은 생략 가능
INSERT INTO customers (name, email, city)
VALUES ('오둘이', 'dooli@mail.com', '제주');

INSERT INTO products (product_name, category, price, stock) VALUES
('무선 이어폰',   '전자기기', 79000,  150),
('태블릿 거치대', '액세서리', 22000,  300),
('마우스 패드',   '액세서리', 12000,  500);

-- VIP 고객 테이블에 GOLD 등급 고객 복사
CREATE TABLE vip_customers LIKE customers;

INSERT INTO vip_customers
SELECT * FROM customers
WHERE grade = 'GOLD';

-- 중복 키 에러 무시 (삽입 건너뛰기)
INSERT IGNORE INTO customers (name, email, city)
VALUES ('김민수', 'minsu@mail.com', '서울');

-- 중복 시 업데이트 (UPSERT)
INSERT INTO products (product_id, product_name, price, stock)
VALUES (1, '무선 키보드', 43000, 130)
ON DUPLICATE KEY UPDATE
    price = VALUES(price),
    stock = VALUES(stock);

-- 특정 고객의 등급 변경
UPDATE customers
SET grade = 'GOLD'
WHERE customer_id = 3;

-- 여러 컬럼 동시 수정
UPDATE customers
SET
    city = '세종',
    phone = '010-9999-0000'
WHERE customer_id = 3;

-- 모든 상품 가격 10% 인상
UPDATE products
SET price = price * 1.1;

-- 전자기기 카테고리만 5% 할인
UPDATE products
SET price = price * 0.95
WHERE category = '전자기기';

-- 재고가 50 이하인 상품 재고 100 추가
UPDATE products
SET stock = stock + 100
WHERE stock <= 50;

-- 1단계: 먼저 SELECT로 대상 확인
SELECT * FROM products
WHERE category = '전자기기' AND stock <= 50;

-- 2단계: 확인 후 UPDATE 실행
UPDATE products
SET stock = stock + 100
WHERE category = '전자기기' AND stock <= 50;

-- 특정 주문 삭제
DELETE FROM orders
WHERE order_id = 9;

-- 취소된 주문 모두 삭제
DELETE FROM orders
WHERE status = 'CANCELLED';

-- 전체 고객 수
SELECT COUNT(*) AS total_customers
FROM customers;

-- 상품 가격 통계
SELECT
    COUNT(*) AS '상품수',
    SUM(price) AS '가격합계',
    AVG(price) AS '평균가격',
    MAX(price) AS '최고가',
    MIN(price) AS '최저가'
FROM products;

-- 완료된 주문의 총 매출
SELECT
    COUNT(*) AS '주문건수',
    SUM(total_amount) AS '총매출'
FROM orders
WHERE status = 'COMPLETED';

-- 직원 급여 통계
SELECT
    AVG(salary) AS '평균급여',
    MAX(salary) AS '최고급여',
    MIN(salary) AS '최저급여',
    MAX(salary) - MIN(salary) AS '급여차이'
FROM employees;

-- COUNT(*) vs COUNT(column) 차이
SELECT
    COUNT(*)          AS '전체행수',      -- NULL 포함
    COUNT(manager_id) AS '매니저있는직원'  -- NULL 제외
FROM employees;

-- DISTINCT와 함께: 고유 값의 개수
SELECT
    COUNT(DISTINCT city) AS '도시수',
    COUNT(DISTINCT grade) AS '등급수'
FROM customers;

-- 도시별 고객 수
SELECT
    city      AS '도시',
    COUNT(*) AS '고객수'
FROM customers
GROUP BY city;

-- 카테고리별 상품 통계
SELECT
    category      AS '카테고리',
    COUNT(*)     AS '상품수',
    AVG(price)   AS '평균가격',
    SUM(stock)   AS '총재고'
FROM products
GROUP BY category;

-- 부서별 직원 수와 평균 급여
SELECT
    department    AS '부서',
    COUNT(*)     AS '직원수',
    AVG(salary)  AS '평균급여',
    MAX(salary)  AS '최고급여'
FROM employees
GROUP BY department;

-- 도시별, 등급별 고객 수
SELECT
    city, grade,
    COUNT(*) AS cnt
FROM customers
GROUP BY city, grade
ORDER BY city, cnt DESC;

-- 월별, 상태별 주문 통계
SELECT
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    status,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_sales
FROM orders
GROUP BY month, status
ORDER BY month;

-- 고객이 2명 이상인 도시만
SELECT
    city,
    COUNT(*) AS cnt
FROM customers
GROUP BY city
HAVING cnt >= 2;

-- 평균 급여가 500만원 이상인 부서
SELECT
    department,
    AVG(salary) AS avg_sal
FROM employees
GROUP BY department
HAVING avg_sal >= 5000000;

-- 2건 이상 주문한 고객
SELECT
    customer_id,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_spent
FROM orders
WHERE status != 'CANCELLED'
GROUP BY customer_id
HAVING order_count >= 2
ORDER BY total_spent DESC;

-- 등급별 고객 수와 비율
SELECT
    grade,
    COUNT(*) AS cnt,
    ROUND(
        COUNT(*) * 100.0 / (SELECT COUNT(*) FROM customers), 1
    ) AS percentage
FROM customers
GROUP BY grade
ORDER BY cnt DESC;

-- 고객별 첫 주문과 마지막 주문
SELECT
    customer_id,
    MIN(order_date) AS first_order,
    MAX(order_date) AS last_order,
    DATEDIFF(MAX(order_date), MIN(order_date)) AS days_between
FROM orders
GROUP BY customer_id;

-- 기본 문법
SELECT 컬럼목록
FROM 테이블A
INNER JOIN 테이블B ON 테이블A.키 = 테이블B.키;

-- 주문 정보에 고객 이름 포함하여 조회
SELECT
    o.order_id,
    c.name       AS customer_name,
    c.city,
    o.order_date,
    o.total_amount,
    o.status
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
ORDER BY o.order_date DESC;

-- 주문 상세 정보: 고객명 + 상품명 + 수량 + 금액
SELECT
    o.order_id       AS '주문번호',
    c.name           AS '고객명',
    p.product_name   AS '상품명',
    p.category       AS '카테고리',
    oi.quantity      AS '수량',
    oi.unit_price    AS '단가',
    oi.quantity * oi.unit_price AS '소계'
FROM order_items oi
INNER JOIN orders o    ON oi.order_id = o.order_id
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN products p  ON oi.product_id = p.product_id
ORDER BY o.order_id, oi.item_id;

-- 모든 고객 + 주문 정보 (주문이 없는 고객도 포함)
SELECT
    c.name,
    c.email,
    o.order_id,
    o.total_amount,
    o.status
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
ORDER BY c.customer_id;

-- ★ 실무 활용: 주문을 한 번도 하지 않은 고객 찾기
SELECT c.name, c.email, c.join_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

-- ★ 팔린 적 없는 상품 찾기
SELECT p.product_name, p.price, p.stock
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
WHERE oi.item_id IS NULL;

-- RIGHT JOIN : 오른쪽 테이블 기준 전체 표시
SELECT
    o.order_id,
    c.name,
    o.total_amount
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

-- 직원과 그 직원의 매니저 이름 함께 조회
SELECT
    e.name        AS '직원명',
    e.department  AS '부서',
    e.position    AS '직급',
    e.salary      AS '급여',
    m.name        AS '상사명',
    m.position    AS '상사직급'
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;

-- 모든 고객 × 모든 상품 조합
SELECT
    c.name   AS '고객명',
    p.product_name AS '상품명',
    p.price  AS '가격'
FROM customers c
CROSS JOIN products p
LIMIT 10;

-- 실무 활용: 모든 월 × 모든 카테고리 조합 (빈 데이터 포함 리포트)

-- 조인 컬럼 이름이 동일할 때 USING 사용 가능
SELECT o.order_id, c.name, o.total_amount
FROM orders o
INNER JOIN customers c USING (customer_id);

-- ON 절과 동일한 결과:
-- INNER JOIN customers c ON o.customer_id = c.customer_id

-- FULL OUTER JOIN 대체
SELECT c.name, o.order_id, o.total_amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id

UNION

SELECT c.name, o.order_id, o.total_amount
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

-- ★ 고객별 총 구매액과 구매 상품 수 (취소 제외)
SELECT
    c.name                            AS '고객명',
    c.grade                           AS '등급',
    COUNT(DISTINCT o.order_id)       AS '주문횟수',
    SUM(oi.quantity)                 AS '총상품수',
    COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS '총구매액'
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
    AND o.status != 'CANCELLED'
LEFT JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.customer_id, c.name, c.grade
ORDER BY '총구매액' DESC;

-- 평균 가격보다 비싼 상품
SELECT product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- 가장 비싼 상품 조회
SELECT * FROM products
WHERE price = (SELECT MAX(price) FROM products);

-- 가장 최근 주문의 상세 정보
SELECT * FROM orders
WHERE order_date = (
    SELECT MAX(order_date) FROM orders
);

-- 개발부서 평균 급여보다 높은 급여를 받는 직원
SELECT name, department, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department = '개발'
);

-- IN : 주문한 적이 있는 고객
SELECT name, email
FROM customers
WHERE customer_id IN (
    SELECT DISTINCT customer_id FROM orders
);

-- NOT IN : 주문한 적 없는 고객
SELECT name, email
FROM customers
WHERE customer_id NOT IN (
    SELECT DISTINCT customer_id FROM orders
);

-- ANY : 어떤 팀장의 급여보다 높은 사원
SELECT name, salary
FROM employees
WHERE salary > ANY (
    SELECT salary FROM employees WHERE position = '팀장'
);

-- ALL : 모든 팀장의 급여보다 높은 사람
SELECT name, salary
FROM employees
WHERE salary > ALL (
    SELECT salary FROM employees WHERE position = '팀장'
);

-- 고객별 주문 통계를 구한 뒤, 10만원 이상 구매자만 조회
SELECT *
FROM (
    SELECT
        c.name,
        c.grade,
        COUNT(o.order_id)    AS order_count,
        SUM(o.total_amount)  AS total_spent
    FROM customers c
    INNER JOIN orders o ON c.customer_id = o.customer_id
    WHERE o.status = 'COMPLETED'
    GROUP BY c.customer_id, c.name, c.grade
) AS stats
WHERE total_spent > 100000
ORDER BY total_spent DESC;

-- 각 상품의 가격과 전체 평균 가격 비교
SELECT
    product_name,
    price,
    (SELECT ROUND(AVG(price)) FROM products) AS avg_price,
    price - (SELECT ROUND(AVG(price)) FROM products) AS diff_from_avg
FROM products
ORDER BY diff_from_avg DESC;

-- 각 직원이 속한 부서의 평균 급여 함께 조회
SELECT
    e.name,
    e.department,
    e.salary,
    (SELECT ROUND(AVG(salary))
     FROM employees e2
     WHERE e2.department = e.department
    ) AS dept_avg
FROM employees e;

-- 주문이 있는 고객만 조회 (EXISTS)
SELECT c.name, c.email
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.customer_id
);

-- 주문이 없는 고객 (NOT EXISTS)
SELECT c.name, c.email
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.customer_id
);

-- 10만원 이상 주문 이력이 있는 고객
SELECT c.name, c.grade
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.customer_id
    AND o.total_amount >= 100000
);

-- CONCAT : 문자열 연결
SELECT CONCAT('Hello', ' ', 'World');
-- 결과: Hello World

-- CONCAT_WS : 구분자(Separator)로 연결
SELECT CONCAT_WS('-', '2024', '01', '15');
-- 결과: 2024-01-15

-- UPPER / LOWER : 대소문자 변환
SELECT
    UPPER('hello sql') AS upper_case,  -- HELLO SQL
    LOWER('HELLO SQL') AS lower_case;  -- hello sql

-- LENGTH / CHAR_LENGTH
SELECT
    LENGTH('SQL 공부')      AS byte_len,  -- 바이트 수 (UTF8: 10)
    CHAR_LENGTH('SQL 공부') AS char_len;  -- 문자 수 (6)

-- SUBSTRING(str, start, length) : 부분 문자열
SELECT SUBSTRING('Hello World', 7, 5);
-- 결과: World

-- LEFT / RIGHT : 왼쪽/오른쪽에서 n글자
SELECT
    LEFT('Hello World', 5)  AS left_str,   -- Hello
    RIGHT('Hello World', 5) AS right_str;  -- World

-- TRIM : 공백 제거
SELECT
    TRIM('  Hello  ')  AS both_trim,   -- Hello
    LTRIM('  Hello  ') AS left_trim,   -- 'Hello  '
    RTRIM('  Hello  ') AS right_trim;  -- '  Hello'

-- SUBSTRING_INDEX : 구분자 기준으로 자르기
SELECT
    SUBSTRING_INDEX('minsu@mail.com', '@', 1)  AS username,  -- minsu
    SUBSTRING_INDEX('minsu@mail.com', '@', -1) AS domain;    -- mail.com

-- REPLACE : 문자열 치환
SELECT REPLACE('010-1234-5678', '-', '');
-- 결과: 01012345678

-- INSTR / LOCATE : 위치 찾기
SELECT
    INSTR('Hello World', 'World')    AS pos1,  -- 7
    LOCATE('World', 'Hello World')   AS pos2;  -- 7

-- LPAD / RPAD : 패딩(채우기)
SELECT
    LPAD('42', 5, '0')  AS left_pad,   -- 00042
    RPAD('Hi', 5, '.')  AS right_pad;  -- Hi...

-- REPEAT / REVERSE
SELECT
    REPEAT('★', 5) AS stars,     -- ★★★★★
    REVERSE('Hello') AS reversed;  -- olleH

-- ① 이메일에서 도메인 추출
SELECT
    name,
    email,
    SUBSTRING_INDEX(email, '@', -1) AS domain
FROM customers;

-- ② 전화번호 마스킹 처리
SELECT
    name,
    CONCAT(
        LEFT(phone, 3), '-****-', RIGHT(phone, 4)
    ) AS masked_phone
FROM customers;

-- ③ 이름 성씨 기준 통계 (한글 1글자 = 성씨)
SELECT
    LEFT(name, 1) AS family_name,
    COUNT(*)       AS cnt
FROM customers
GROUP BY family_name
ORDER BY cnt DESC;

-- ④ 상품명 검색 (대소문자 무시)
SELECT * FROM products
WHERE LOWER(product_name) LIKE '%usb%';

-- ⑤ 주문번호 형식 만들기: ORD-00001
SELECT
    CONCAT('ORD-', LPAD(order_id, 5, '0')) AS order_no,
    total_amount
FROM orders;

SELECT
    NOW()         AS '현재일시',      -- 2024-04-26 14:30:00
    CURDATE()     AS '오늘날짜',      -- 2024-04-26
    CURTIME()     AS '현재시간',      -- 14:30:00
    SYSDATE()     AS '시스템일시',    -- NOW()와 유사
    UTC_TIMESTAMP() AS 'UTC시간';

SELECT
    YEAR(NOW())       AS '연도',     -- 2024
    MONTH(NOW())      AS '월',       -- 4
    DAY(NOW())        AS '일',       -- 26
    HOUR(NOW())       AS '시',       -- 14
    MINUTE(NOW())     AS '분',       -- 30
    SECOND(NOW())     AS '초',       -- 0
    DAYOFWEEK(NOW()) AS '요일번호',  -- 1(일)~7(토)
    DAYNAME(NOW())   AS '요일명',    -- Friday
    QUARTER(NOW())   AS '분기',      -- 2
    WEEK(NOW())      AS '주차';      -- 17

-- DATE_ADD / DATE_SUB
SELECT
    DATE_ADD(NOW(), INTERVAL 7 DAY)     AS '7일후',
    DATE_ADD(NOW(), INTERVAL 1 MONTH)   AS '1달후',
    DATE_ADD(NOW(), INTERVAL 1 YEAR)    AS '1년후',
    DATE_ADD(NOW(), INTERVAL 2 HOUR)    AS '2시간후',
    DATE_SUB(NOW(), INTERVAL 30 DAY)    AS '30일전',
    DATE_SUB(NOW(), INTERVAL 3 MONTH)   AS '3달전';

-- DATEDIFF : 두 날짜 사이의 일수
SELECT
    name, join_date,
    DATEDIFF(CURDATE(), join_date) AS days_since_join
FROM customers;

-- TIMESTAMPDIFF : 특정 단위로 차이 계산
SELECT
    name, hire_date,
    TIMESTAMPDIFF(YEAR, hire_date, CURDATE())  AS '근속연수',
    TIMESTAMPDIFF(MONTH, hire_date, CURDATE()) AS '근속개월'
FROM employees;

SELECT
    order_date,
    DATE_FORMAT(order_date, '%Y년 %m월 %d일')  AS '한국식',
    DATE_FORMAT(order_date, '%Y-%m')            AS '년월',
    DATE_FORMAT(order_date, '%W')               AS '요일',
    DATE_FORMAT(order_date, '%p %h:%i:%s')     AS '시간'
FROM orders
LIMIT 3;

-- ① 월별 매출 집계
SELECT
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    COUNT(*) AS order_count,
    SUM(total_amount) AS monthly_sales
FROM orders
WHERE status = 'COMPLETED'
GROUP BY month
ORDER BY month;

-- ② 최근 30일 내 주문
SELECT * FROM orders
WHERE order_date >= DATE_SUB(NOW(), INTERVAL 30 DAY);

-- ③ 가입 후 1년이 지난 고객
SELECT name, join_date
FROM customers
WHERE join_date < DATE_SUB(CURDATE(), INTERVAL 1 YEAR);

-- ④ 요일별 주문 분포
SELECT
    DAYNAME(order_date) AS day_name,
    COUNT(*) AS cnt
FROM orders
GROUP BY day_name
ORDER BY FIELD(day_name, 'Monday','Tuesday','Wednesday',
    'Thursday','Friday','Saturday','Sunday');

-- ⑤ 분기별 매출
SELECT
    CONCAT(YEAR(order_date), ' Q', QUARTER(order_date)) AS quarter,
    SUM(total_amount) AS revenue
FROM orders
WHERE status != 'CANCELLED'
GROUP BY quarter
ORDER BY quarter;

-- 주문 상태를 한글로 변환
SELECT
    order_id,
    status,
    CASE status
        WHEN 'COMPLETED' THEN '완료'
        WHEN 'SHIPPED'   THEN '배송중'
        WHEN 'PENDING'   THEN '대기중'
        WHEN 'CANCELLED' THEN '취소됨'
        ELSE '알수없음'
    END AS '상태한글',
    total_amount
FROM orders;

-- 가격대별 분류
SELECT
    product_name,
    price,
    CASE
        WHEN price >= 100000 THEN '★★★ 고가'
        WHEN price >= 50000  THEN '★★ 중가'
        WHEN price >= 20000  THEN '★ 저가'
        ELSE '초저가'
    END AS '가격대'
FROM products
ORDER BY price DESC;

-- 급여 등급 분류
SELECT
    name, department, salary,
    CASE
        WHEN salary >= 10000000 THEN 'S등급'
        WHEN salary >= 6000000  THEN 'A등급'
        WHEN salary >= 4000000  THEN 'B등급'
        ELSE 'C등급'
    END AS salary_grade
FROM employees
ORDER BY salary DESC;

-- 재고 상태 표시
SELECT
    product_name, stock,
    CASE
        WHEN stock = 0   THEN '품절'
        WHEN stock <= 30  THEN '부족'
        WHEN stock <= 100 THEN '보통'
        ELSE '충분'
    END AS stock_status
FROM products;

-- 주문 상태별 건수를 가로로 표시
SELECT
    COUNT(*) AS '전체',
    SUM(CASE WHEN status = 'COMPLETED' THEN 1 ELSE 0 END) AS '완료',
    SUM(CASE WHEN status = 'SHIPPED'   THEN 1 ELSE 0 END) AS '배송중',
    SUM(CASE WHEN status = 'PENDING'   THEN 1 ELSE 0 END) AS '대기',
    SUM(CASE WHEN status = 'CANCELLED' THEN 1 ELSE 0 END) AS '취소'
FROM orders;

-- 카테고리별 가격대 분포 피벗
SELECT
    category,
    SUM(CASE WHEN price >= 100000 THEN 1 ELSE 0 END) AS '10만원이상',
    SUM(CASE WHEN price >= 50000 AND price < 100000 THEN 1 ELSE 0 END) AS '5~10만원',
    SUM(CASE WHEN price < 50000 THEN 1 ELSE 0 END) AS '5만원미만'
FROM products
GROUP BY category;

-- 월별 상태별 매출 피벗
SELECT
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    SUM(CASE WHEN status = 'COMPLETED' THEN total_amount ELSE 0 END) AS completed_sales,
    SUM(CASE WHEN status = 'SHIPPED'   THEN total_amount ELSE 0 END) AS shipped_sales,
    SUM(total_amount) AS total_all
FROM orders
GROUP BY month
ORDER BY month;

-- ORDER BY에서 CASE 사용: 커스텀 정렬
SELECT name, grade
FROM customers
ORDER BY
    CASE grade
        WHEN 'GOLD'   THEN 1
        WHEN 'SILVER' THEN 2
        WHEN 'BRONZE' THEN 3
    END;

-- UPDATE에서 CASE 사용: 조건별 일괄 수정
UPDATE products
SET price = CASE
    WHEN category = '전자기기' THEN price * 0.9   -- 10% 할인
    WHEN category = '도서'     THEN price * 0.95  -- 5% 할인
    ELSE price
END;

-- 고객별 주문 요약 뷰
CREATE VIEW v_customer_summary AS
SELECT
    c.customer_id,
    c.name,
    c.grade,
    c.city,
    COUNT(o.order_id)                AS order_count,
    COALESCE(SUM(o.total_amount), 0) AS total_spent,
    MAX(o.order_date)                AS last_order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
    AND o.status != 'CANCELLED'
GROUP BY c.customer_id, c.name, c.grade, c.city;

-- 뷰를 일반 테이블처럼 조회!
SELECT * FROM v_customer_summary
WHERE total_spent > 100000
ORDER BY total_spent DESC;

-- 보안용 뷰: 급여 정보를 숨긴 직원 뷰
CREATE VIEW v_employees_public AS
SELECT emp_id, name, department, position, hire_date
FROM employees;

-- 상품 카테고리별 통계 뷰
CREATE VIEW v_category_stats AS
SELECT
    category,
    COUNT(*)         AS product_count,
    ROUND(AVG(price)) AS avg_price,
    SUM(stock)       AS total_stock,
    MIN(price)       AS min_price,
    MAX(price)       AS max_price
FROM products
GROUP BY category;

-- 뷰 수정 (또는 없으면 생성)
CREATE OR REPLACE VIEW v_category_stats AS
SELECT
    category,
    COUNT(*) AS product_count,
    ROUND(AVG(price)) AS avg_price
FROM products
GROUP BY category;

-- 뷰 목록 확인
SHOW FULL TABLES WHERE Table_type = 'VIEW';

-- 뷰 삭제
DROP VIEW IF EXISTS v_customer_summary;

-- 단일 컬럼 인덱스
CREATE INDEX idx_customers_city
ON customers(city);

-- 복합 인덱스 (여러 컬럼)
CREATE INDEX idx_orders_cust_date
ON orders(customer_id, order_date);

-- 유니크 인덱스
CREATE UNIQUE INDEX idx_customers_email
ON customers(email);

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

-- 인덱스 삭제
DROP INDEX idx_customers_city ON customers;

-- ALTER TABLE로 인덱스 추가/삭제
ALTER TABLE products ADD INDEX idx_category (category);
ALTER TABLE products DROP INDEX idx_category;

-- 인덱스 없이 조회
EXPLAIN SELECT * FROM customers WHERE city = '서울';
-- type: ALL (전체 스캔) → 느림

-- 인덱스 생성 후 조회
CREATE INDEX idx_city ON customers(city);
EXPLAIN SELECT * FROM customers WHERE city = '서울';
-- type: ref (인덱스 사용) → 빠름

-- 트랜잭션 시작
START TRANSACTION;

-- 작업 1: 주문 생성
INSERT INTO orders (customer_id, total_amount, status)
VALUES (1, 45000, 'PENDING');

-- 작업 2: 재고 차감
UPDATE products
SET stock = stock - 1
WHERE product_id = 1 AND stock > 0;

-- 작업 3: 주문 상세 추가
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (LAST_INSERT_ID(), 1, 1, 45000);

-- 모두 성공 → 확정
COMMIT;

-- 문제 발생 시 → 전체 취소
-- ROLLBACK;

START TRANSACTION;

INSERT INTO orders (customer_id, total_amount, status)
VALUES (2, 60000, 'PENDING');
SAVEPOINT sp_order_created;

UPDATE products SET stock = stock - 1 WHERE product_id = 99;
-- 상품 99번이 없어서 문제 발생!

-- sp_order_created 시점으로 되돌리기
ROLLBACK TO SAVEPOINT sp_order_created;

-- INSERT는 유지, UPDATE만 취소된 상태
-- 다시 올바른 작업 수행
UPDATE products SET stock = stock - 1 WHERE product_id = 2;

COMMIT;

-- 현재 autocommit 상태 확인
SELECT @@autocommit;   -- 1 = 활성화(기본값)

-- autocommit 비활성화 (모든 SQL을 수동으로 COMMIT해야 함)
SET autocommit = 0;

-- 다시 활성화
SET autocommit = 1;

CREATE TABLE members (
    -- PRIMARY KEY + AUTO_INCREMENT
    member_id   INT          PRIMARY KEY AUTO_INCREMENT,

    -- NOT NULL + UNIQUE
    username    VARCHAR(30)  NOT NULL UNIQUE,
    email       VARCHAR(100) NOT NULL UNIQUE,

    -- CHECK 제약
    age         INT          CHECK (age >= 14 AND age <= 120),

    -- DEFAULT 값
    status      ENUM('ACTIVE', 'INACTIVE', 'BANNED')
                DEFAULT 'ACTIVE',

    -- DEFAULT 현재 시간
    created_at  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
                ON UPDATE CURRENT_TIMESTAMP
);

-- 리뷰 테이블 : 외래키 + 참조 옵션
CREATE TABLE reviews (
    review_id    INT PRIMARY KEY AUTO_INCREMENT,
    product_id   INT NOT NULL,
    customer_id  INT NOT NULL,
    rating       INT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    title        VARCHAR(100),
    content      TEXT,
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    -- 상품 삭제 시 → 리뷰도 삭제 (CASCADE)
    FOREIGN KEY (product_id)
        REFERENCES products(product_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

    -- 고객 삭제 시 → 리뷰 삭제 거부 (RESTRICT)
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE RESTRICT
);

-- CHECK 제약 추가
ALTER TABLE products
ADD CONSTRAINT chk_price_positive CHECK (price > 0);

-- UNIQUE 제약 추가
ALTER TABLE products
ADD CONSTRAINT uq_product_name UNIQUE (product_name);

-- 외래키 추가
ALTER TABLE order_items
ADD CONSTRAINT fk_items_product
FOREIGN KEY (product_id) REFERENCES products(product_id);

-- CHECK 삭제
ALTER TABLE products DROP CHECK chk_price_positive;

-- 외래키 삭제
ALTER TABLE order_items DROP FOREIGN KEY fk_items_product;

-- UNIQUE 삭제
ALTER TABLE products DROP INDEX uq_product_name;

-- 전체 매출 요약 (취소 제외)
SELECT
    COUNT(DISTINCT o.order_id)   AS '총주문수',
    COUNT(DISTINCT o.customer_id) AS '구매고객수',
    SUM(o.total_amount)            AS '총매출',
    ROUND(AVG(o.total_amount))     AS '평균주문액',
    MAX(o.total_amount)            AS '최대주문액',
    MIN(o.total_amount)            AS '최소주문액'
FROM orders o
WHERE o.status != 'CANCELLED';

-- 월별 매출 추이 (완료 + 배송중)
SELECT
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    COUNT(*)                          AS orders,
    SUM(total_amount)                 AS revenue,
    ROUND(AVG(total_amount))          AS avg_order,
    COUNT(DISTINCT customer_id)       AS unique_buyers
FROM orders
WHERE status IN ('COMPLETED', 'SHIPPED')
GROUP BY month
ORDER BY month;

-- 판매 수량 기준 베스트셀러
SELECT
    p.product_name                       AS '상품명',
    p.category                           AS '카테고리',
    SUM(oi.quantity)                    AS '총판매수량',
    SUM(oi.quantity * oi.unit_price)    AS '총매출',
    p.stock                              AS '현재재고'
FROM order_items oi
INNER JOIN products p ON oi.product_id = p.product_id
INNER JOIN orders o  ON oi.order_id = o.order_id
WHERE o.status != 'CANCELLED'
GROUP BY p.product_id, p.product_name, p.category, p.stock
ORDER BY SUM(oi.quantity) DESC
LIMIT 5;

-- 고객별 구매 분석 + 활동 상태 판단
SELECT
    c.name                                   AS '고객명',
    c.email                                  AS '이메일',
    c.grade                                  AS '등급',
    c.city                                   AS '도시',
    COUNT(o.order_id)                       AS '주문횟수',
    SUM(o.total_amount)                     AS '총구매액',
    ROUND(AVG(o.total_amount))              AS '평균구매액',
    MAX(o.order_date)                       AS '마지막주문',
    DATEDIFF(CURDATE(), MAX(o.order_date)) AS '경과일',
    CASE
        WHEN DATEDIFF(CURDATE(), MAX(o.order_date)) <= 30
            THEN '활성'
        WHEN DATEDIFF(CURDATE(), MAX(o.order_date)) <= 90
            THEN '준활성'
        WHEN DATEDIFF(CURDATE(), MAX(o.order_date)) <= 180
            THEN '휴면위험'
        ELSE '휴면'
    END                                      AS '활동상태'
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status != 'CANCELLED'
GROUP BY c.customer_id, c.name, c.email, c.grade, c.city
ORDER BY SUM(o.total_amount) DESC;

SELECT
    p.category                                AS '카테고리',
    SUM(oi.quantity)                         AS '판매수량',
    SUM(oi.quantity * oi.unit_price)         AS '매출',
    ROUND(
        SUM(oi.quantity * oi.unit_price) * 100.0
        / (SELECT SUM(quantity * unit_price)
           FROM order_items), 1
    )                                         AS '매출비중(%)'
FROM order_items oi
INNER JOIN products p ON oi.product_id = p.product_id
GROUP BY p.category
ORDER BY SUM(oi.quantity * oi.unit_price) DESC;

-- 2번 이상 주문한 고객 비율
SELECT
    total_buyers,
    repeat_buyers,
    ROUND(repeat_buyers * 100.0 / total_buyers, 1)
        AS '재구매율(%)'
FROM (
    SELECT
        COUNT(DISTINCT customer_id) AS total_buyers,
        SUM(CASE WHEN cnt >= 2 THEN 1 ELSE 0 END)
            AS repeat_buyers
    FROM (
        SELECT customer_id, COUNT(*) AS cnt
        FROM orders
        WHERE status != 'CANCELLED'
        GROUP BY customer_id
    ) AS order_counts
) AS stats;

SELECT
    p.product_id,
    p.product_name,
    p.category,
    p.stock                            AS '현재재고',
    COALESCE(SUM(oi.quantity), 0)      AS '총판매량',
    CASE
        WHEN p.stock = 0   THEN '품절'
        WHEN p.stock <= 30  THEN '긴급보충'
        WHEN p.stock <= 50  THEN '보충권장'
        WHEN p.stock <= 100 THEN '보통'
        ELSE '충분'
    END                                 AS '재고상태'
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name, p.category, p.stock
ORDER BY p.stock ASC;

SELECT
    department                                 AS '부서',
    COUNT(*)                                  AS '인원',
    FORMAT(SUM(salary), 0)                    AS '급여합계',
    FORMAT(ROUND(AVG(salary)), 0)              AS '평균급여',
    FORMAT(MAX(salary), 0)                    AS '최고급여',
    FORMAT(MIN(salary), 0)                    AS '최저급여',
    ROUND(
        SUM(salary) * 100.0
        / (SELECT SUM(salary) FROM employees), 1
    )                                          AS '인건비비중(%)'
FROM employees
GROUP BY department
ORDER BY SUM(salary) DESC;

SELECT
    c.city AS '도시',
    COALESCE(SUM(
        CASE WHEN c.grade = 'GOLD'
        THEN o.total_amount END
    ), 0) AS 'GOLD매출',
    COALESCE(SUM(
        CASE WHEN c.grade = 'SILVER'
        THEN o.total_amount END
    ), 0) AS 'SILVER매출',
    COALESCE(SUM(
        CASE WHEN c.grade = 'BRONZE'
        THEN o.total_amount END
    ), 0) AS 'BRONZE매출',
    COALESCE(SUM(o.total_amount), 0) AS '합계'
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id
    AND o.status != 'CANCELLED'
GROUP BY c.city
ORDER BY COALESCE(SUM(o.total_amount), 0) DESC;

-- 구매 금액 기준으로 등급 일괄 업데이트
UPDATE customers c
INNER JOIN (
    SELECT
        customer_id,
        SUM(total_amount) AS total
    FROM orders
    WHERE status != 'CANCELLED'
    GROUP BY customer_id
) AS stats ON c.customer_id = stats.customer_id
SET c.grade = CASE
    WHEN stats.total >= 500000 THEN 'GOLD'
    WHEN stats.total >= 200000 THEN 'SILVER'
    ELSE 'BRONZE'
END;

/* =============================================
   SQL 치트시트 - 한눈에 보는 핵심 문법
   ============================================= */

/* ── 1. 데이터 조회 ── */
SELECT col1, col2           -- 조회할 컬럼
FROM table_name             -- 테이블
WHERE condition             -- 행 필터
GROUP BY col               -- 그룹핑
HAVING group_condition      -- 그룹 필터
ORDER BY col ASC|DESC     -- 정렬
LIMIT n OFFSET m;          -- 결과 제한

/* ── 2. 데이터 조작 ── */
INSERT INTO t (c1, c2) VALUES (v1, v2);
INSERT INTO t (c1) VALUES (v1), (v2), (v3);
UPDATE t SET c1 = v1 WHERE condition;
DELETE FROM t WHERE condition;

/* ── 3. 테이블 관리 ── */
CREATE TABLE t (col TYPE constraints);
ALTER TABLE t ADD COLUMN col TYPE;
ALTER TABLE t MODIFY COLUMN col NEW_TYPE;
ALTER TABLE t DROP COLUMN col;
DROP TABLE IF EXISTS t;
TRUNCATE TABLE t;

/* ── 4. JOIN ── */
FROM a INNER JOIN b ON a.id = b.a_id  -- 교집합
FROM a LEFT JOIN  b ON a.id = b.a_id  -- 왼쪽 전체
FROM a RIGHT JOIN b ON a.id = b.a_id  -- 오른쪽 전체
FROM a CROSS JOIN b                    -- 모든 조합

/* ── 5. 집계 함수 ── */
COUNT(*) | COUNT(col) | COUNT(DISTINCT col)
SUM(col) | AVG(col) | MAX(col) | MIN(col)

/* ── 6. WHERE 조건 연산자 ── */
=  !=  >  <  >=  <=
AND | OR | NOT
BETWEEN a AND b
IN (v1, v2, v3) | NOT IN (...)
LIKE '%패턴%'     -- %: 0개 이상 문자, _: 1개 문자
IS NULL | IS NOT NULL
EXISTS (subquery) | NOT EXISTS (subquery)

/* ── 7. 문자열 함수 ── */
CONCAT(s1, s2)  |  CONCAT_WS(sep, s1, s2)
SUBSTRING(str, start, len)
LEFT(str, n)  |  RIGHT(str, n)
UPPER(str)  |  LOWER(str)
TRIM(str)  |  LTRIM(str)  |  RTRIM(str)
REPLACE(str, from, to)
LPAD(str, len, pad)  |  RPAD(str, len, pad)
LENGTH(str)  |  CHAR_LENGTH(str)
SUBSTRING_INDEX(str, delim, count)

/* ── 8. 날짜 함수 ── */
NOW()  |  CURDATE()  |  CURTIME()
YEAR(d) | MONTH(d) | DAY(d) | HOUR(d)
DATE_ADD(date, INTERVAL n UNIT)
DATE_SUB(date, INTERVAL n UNIT)
DATEDIFF(date1, date2)
TIMESTAMPDIFF(UNIT, date1, date2)
DATE_FORMAT(date, '%Y-%m-%d')

/* ── 9. 숫자 함수 ── */
ROUND(n, d)  |  CEIL(n)  |  FLOOR(n)
ABS(n)  |  MOD(a, b)  |  POWER(a, b)
FORMAT(n, d)  -- 천 단위 쉼표 포맷

/* ── 10. NULL 처리 ── */
IFNULL(col, default_val)
COALESCE(v1, v2, v3, ...)  -- 첫 NOT NULL
NULLIF(a, b)  -- a=b이면 NULL

/* ── 11. CASE 표현식 ── */
CASE col
    WHEN val1 THEN result1
    WHEN val2 THEN result2
    ELSE default_result
END

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE default_result
END

/* ── 12. 트랜잭션 ── */
START TRANSACTION;
-- SQL 작업들 ...
COMMIT;      -- 확정
ROLLBACK;    -- 취소
SAVEPOINT sp1;
ROLLBACK TO SAVEPOINT sp1;

/* ── 13. 뷰 / 인덱스 ── */
CREATE VIEW v AS SELECT ...;
DROP VIEW IF EXISTS v;
CREATE INDEX idx ON t(col);
DROP INDEX idx ON t;
EXPLAIN SELECT ...;  -- 실행 계획