[SQL] MySQL 고급 기능 마스터하기 - CHECK부터 VIEW까지

데이터베이스 관리에서 데이터의 정확성과 접근 효율성은 매우 중요하다고 할 수 있다.

이를 위해 MySQL은 다양한 고급 기능들을 제공하고 있는데 필자는 이러한 기능들을 정리하고자 한다.

---

CHECK 제약조건

CHECK 제약조건은 데이터가 특정 조건을 만족하는지 검증한다.

마치 입장 시 나이를 확인하는 것처럼, 데이터가 정해진 규칙을 준수하는지 확인한다.

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2),
    quantity INT,
    -- 가격은 0보다 커야 함
    CONSTRAINT chk_price CHECK (price > 0),
    -- 수량은 0 이상이어야 함
    CONSTRAINT chk_quantity CHECK (quantity >= 0)
);

-- 직원 급여 범위 설정
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10,2),
    department VARCHAR(50),
    -- 부서별 급여 범위 설정
    CONSTRAINT chk_salary 
    CHECK (
        (department = 'IT' AND salary BETWEEN 50000 AND 150000) OR
        (department = 'Sales' AND salary BETWEEN 40000 AND 100000)
    )
);

---

DEFAULT 값 설정

데이터 입력 시 값이 제공되지 않을 때 사용할 기본값을 설정한다.

이는 마치 스마트폰의 기본 설정값과 같은 역할을한다.

CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    -- 현재 시간을 기본값으로 설정
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -- 상태의 기본값 설정
    status VARCHAR(20) DEFAULT 'draft',
    -- 조회수 기본값
    views INT DEFAULT 0
);

---

인덱스 생성과 관리

인덱스는 데이터를 빠르게 찾기 위한 도구다.

책의 목차나 색인과 같은 역할을한다.

-- 단일 열 인덱스 생성
CREATE INDEX idx_email 
ON users(email);

-- 복합 인덱스 생성
CREATE INDEX idx_name_email 
ON users(last_name, first_name, email);

-- 고유 인덱스 생성
CREATE UNIQUE INDEX idx_unique_email 
ON users(email);

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

---

자동 증가(AUTO_INCREMENT) 설정

순차적으로 증가하는 값을 자동으로 생성할 때 사용한다.

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    -- 시작값을 1000으로 설정
    registration_number INT AUTO_INCREMENT,
    CONSTRAINT unique_reg UNIQUE (registration_number)
) AUTO_INCREMENT = 1000;

---

날짜 관련 기능

데이터베이스에서 날짜와 시간을 효과적으로 다루는 방법은 아래와 같다.

CREATE TABLE events (
    id INT PRIMARY KEY,
    event_name VARCHAR(100),
    -- 날짜만 저장
    event_date DATE,
    -- 날짜와 시간 저장
    start_time DATETIME,
    -- 타임스탬프 (시간대 변환 지원)
    created_at TIMESTAMP
);

-- 날짜 함수 사용 예시
SELECT 
    event_name,
    DATE_FORMAT(event_date, '%Y-%m-%d') as formatted_date,
    DATEDIFF(event_date, CURRENT_DATE) as days_until_event,
    TIMESTAMPDIFF(HOUR, created_at, NOW()) as hours_since_creation
FROM events;

---

뷰(Views) 생성과 활용

뷰는 자주 사용하는 복잡한 쿼리를 간단히 재사용할 수 있게 해주는 가상 테이블이다.

-- 직원 급여 통계 뷰 생성
CREATE VIEW employee_stats AS
SELECT 
    department,
    COUNT(*) as employee_count,
    AVG(salary) as avg_salary,
    MIN(salary) as min_salary,
    MAX(salary) as max_salary
FROM employees
GROUP BY department;

-- 뷰 사용
SELECT * FROM employee_stats 
WHERE avg_salary > 50000;

-- 고객 주문 정보 뷰
CREATE VIEW customer_orders AS
SELECT 
    c.name,
    c.email,
    COUNT(o.id) as total_orders,
    SUM(o.total_amount) as total_spent,
    MAX(o.order_date) as last_order_date
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name, c.email;