데이터 조작 (INSERT, UPDATE, DELETE)

-- 단일 행 삽입
INSERT INTO employees (name, department, salary, hire_date)
VALUES ('김철수', '개발팀', 55000, '2024-01-15');

-- 다중 행 삽입
INSERT INTO employees (name, department, salary, hire_date)
VALUES
    ('이영희', '디자인팀', 48000, '2024-02-01'),
    ('박민수', '기획팀', 52000, '2024-02-15'),
    ('최지은', '개발팀', 60000, '2024-03-01');

-- SELECT 결과를 다른 테이블에 삽입
INSERT INTO employee_backup (name, department, salary)
SELECT name, department, salary
FROM employees
WHERE department = '개발팀';

-- 특정 행 수정
UPDATE employees
SET salary = 58000
WHERE name = '김철수';

-- 여러 열 동시 수정
UPDATE employees
SET salary = salary * 1.1,
    department = '시니어개발팀'
WHERE department = '개발팀'
  AND hire_date < '2023-01-01';

-- 조건부 수정 (CASE 활용)
UPDATE employees
SET salary = CASE
    WHEN department = '개발팀' THEN salary * 1.15
    WHEN department = '디자인팀' THEN salary * 1.10
    ELSE salary * 1.05
END;

-- 조건에 맞는 행 삭제
DELETE FROM employees
WHERE department = '퇴사';

-- 서브쿼리로 삭제 대상 지정
DELETE FROM employees
WHERE id NOT IN (
    SELECT employee_id FROM active_projects
);

-- TRUNCATE — 테이블 전체 데이터 삭제 (빠름, 롤백 불가)
TRUNCATE TABLE temp_logs;

-- 테이블 생성
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) DEFAULT 0.00,
    category VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 테이블 수정
ALTER TABLE products
ADD COLUMN stock INT DEFAULT 0;

ALTER TABLE products
MODIFY COLUMN name VARCHAR(200) NOT NULL;

ALTER TABLE products
DROP COLUMN category;

-- 테이블 삭제
DROP TABLE IF EXISTS temp_products;