📌 테이블 생성 및 관리

이번 단계에서는 DDL(Data Definition Language, 데이터 정의어)에 대해 설명하겠습니다.

DDL은 데이터베이스 구조(테이블, 인덱스, 뷰 등)를 정의하고 관리하는 명령어입니다.

DDL의 대표적인 명령어는 다음과 같습니다.

• CREATE : 데이터베이스 객체(테이블, 인덱스 등) 생성
• ALTER : 기존 객체 수정
• DROP : 객체 삭제
• TRUNCATE : 테이블의 모든 데이터 삭제

---

3-1. 테이블 생성 (CREATE TABLE)

데이터를 저장하려면 먼저 테이블(Table)을 만들어야 합니다.

✅ 기본적인 테이블 생성

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT CHECK (age >= 18),
    department VARCHAR(50),
    salary DECIMAL(10,2) DEFAULT 0.00,
    hire_date DATE NOT NULL
    CONSTRAINT chk_age CHECK (age >= 18) -- CHECK 제약 조건 (MariaDB 10.2+이상에서 지원)
);

설명

• id : 기본 키(PK), AUTO_INCREMENT를 사용하여 자동 증가
• name : VARCHAR(50)(최대 50자), NOT NULL(NULL 값 허용 안 함)
• age : CHECK 제약 조건을 사용하여 나이가 18 이상이어야 함
• department : 부서 정보
• salary : DECIMAL(10,2)(소수점 2자리까지 허용), 기본값 0.00
• hire_date : 고용 날짜 (NULL 허용 안 함)

📌 AUTO_INCREMENT가 적용된 PRIMARY KEY

✅ PRIMARY KEY

• 테이블에서 각 행을 유일하게 식별하는 컬럼
• 중복된 값과 NULL을 허용하지 않음
• 자동으로 인덱스가 생성됨

✅ AUTO_INCREMENT

• 새 행이 추가될 때마다 자동으로 숫자가 증가
• 기본적으로 1부터 시작하며, ALTER TABLE로 변경 가능
• DELETE된 값은 기본적으로 재사용되지 않음
• TRUNCATE TABLE을 실행하면 자동 증가 값이 1부터 다시 시작

✅ 실무에서 많이 사용되는 패턴

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,  -- 사용자 ID 자동 증가
    username VARCHAR(50) NOT NULL UNIQUE,  -- 중복 불가
    email VARCHAR(100) NOT NULL UNIQUE,  -- 중복 불가
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP  -- 현재 시간 자동 입력
);

📌 사용자 정보를 저장하는 테이블에서 user_id를 AUTO_INCREMENT PRIMARY KEY로 설정하여 자동 증가

📌 추가적으로 UNIQUE 제약 조건을 적용하여 중복된 사용자명이 입력되지 않도록 설정

---

3-2. 제약 조건(Constraints)

제약 조건은 데이터 무결성을 보장하는 중요한 요소입니다.

주요 제약 조건은 다음과 같습니다.

제약 조건	설명
PRIMARY KEY	테이블의 기본 키, 고유한 값 유지
FOREIGN KEY	다른 테이블과 관계를 맺는 외래 키
UNIQUE	중복 값을 허용하지 않는 컬럼
NOT NULL	NULL 값을 허용하지 않음
CHECK	특정 조건을 만족해야 저장 가능
DEFAULT	값이 없을 경우 기본값 설정

✅ 제약 조건을 적용한 테이블 생성

CREATE TABLE departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) UNIQUE NOT NULL
);
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL
);

설명

• departments 테이블의 id는 기본 키이며, name은 유일해야 함 (UNIQUE NOT NULL)
• employees 테이블의 department_id는 departments.id를 참조하는 외래 키(FK)
• ON DELETE SET NULL : 참조하는 departments 값이 삭제되면 department_id를 NULL로 설정

🚨 ON DELETE 옵션 설명 보강

✅ 추가된 설명 및 예제

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(id)
    ON DELETE SET NULL -- 상위 테이블의 값이 삭제되면 NULL로 변경
    ON UPDATE CASCADE  -- 상위 테이블 값이 변경되면 자동 변경
);

📌 다른 ON DELETE 옵션 설명 추가

옵션	설명
ON DELETE CASCADE	부모 데이터 삭제 시 자식 데이터도 삭제
ON DELETE SET NULL	부모 데이터 삭제 시 자식 컬럼을 NULL로 변경
ON DELETE RESTRICT	부모 데이터 삭제를 제한 (오류 발생)
ON DELETE NO ACTION	RESTRICT와 동일 (SQL 표준 유지)

📌 외래 키를 정의할 때 ON DELETE 및 ON UPDATE 옵션을 명확히 이해

---

3-3. 테이블 변경 (ALTER TABLE)

기존 테이블을 변경하려면 ALTER TABLE을 사용합니다.

✅ 컬럼 추가

ALTER TABLE employees ADD COLUMN email VARCHAR(100);

✅ 컬럼 삭제

ALTER TABLE employees DROP COLUMN email;

✅ 컬럼 데이터 타입 변경

ALTER TABLE employees MODIFY COLUMN salary FLOAT;

✅ 기본값 변경

ALTER TABLE employees ALTER COLUMN salary SET DEFAULT 50000;

---

3-4. 테이블 삭제 (DROP TABLE)

테이블을 완전히 삭제하려면 DROP TABLE을 사용합니다.

⚠ 주의: DROP TABLE을 실행하면 테이블과 데이터가 완전히 삭제됩니다.

✅ 테이블 삭제

DROP TABLE employees;

---

3-5. 테이블 데이터 초기화 (TRUNCATE)

TRUNCATE는 테이블의 모든 데이터를 삭제하지만 테이블 구조는 유지합니다.

✅ 모든 데이터 삭제 (초기화)

TRUNCATE TABLE employees;

⚠ DELETE FROM employees; 와 다르게 TRUNCATE는 롤백이 불가능합니다.

---

3-6. 데이터베이스 관리

데이터베이스 자체를 생성, 수정, 삭제하는 방법도 중요합니다.

✅ 데이터베이스 생성

CREATE DATABASE company;

✅ 데이터베이스 확인

SHOW DATABASES;

✅ 특정 데이터베이스 사용

USE company;

✅ 데이터베이스 삭제

DROP DATABASE company;

제목 없음

3-7. 테이블 데이터 초기화 (TRUNCATE)

TRUNCATE는 테이블의 모든 데이터를 삭제하지만 테이블 구조는 유지합니다.

✅ 모든 데이터 삭제 (초기화)

-- 외래 키가 설정된 경우, TRUNCATE 실행 불가 (오류 발생 가능)
TRUNCATE TABLE employees;

• 외래 키(FK)가 존재하는 테이블에서는 TRUNCATE가 기본적으로 실행되지 않음.
• TRUNCATE 실행 시 FOREIGN_KEY_CHECKS 설정을 고려해야 함.

📌 해결 방법: FOREIGN_KEY_CHECKS를 비활성화 후 실행

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE employees;
SET FOREIGN_KEY_CHECKS = 1;

📌 FOREIGN_KEY_CHECKS를 0으로 설정하면 외래 키 무결성 검사를 일시적으로 해제하여 TRUNCATE 실행 가능.

TRUNCATE vs DELETE 차이점

차이점	DELETE	TRUNCATE
실행 속도	느림 (각 행을 개별 삭제)	빠름 (테이블 전체 초기화)
트랜잭션 롤백	✅ 가능 (TRANSACTION 내에서)	❌ 불가능 (즉시 반영)
AUTO_INCREMENT 초기화	❌ 유지됨	✅ 초기화됨
외래 키 제한 (FK)	✅ 제한 없음	❌ FK 설정 시 실행 불가 (우회 필요)

📌 대부분의 경우 TRUNCATE가 더 빠르지만, 외래 키 제약 조건이 있는 경우 실행이 제한될 수 있음.

---

3-8. 인덱스(Index)와 성능 최적화

인덱스는 검색 속도를 향상시키기 위해 테이블의 특정 컬럼에 대해 생성됩니다.

✅ 기본 인덱스 생성

-- 기본 인덱스 생성 (검색 속도 향상)
CREATE INDEX idx_department ON employees(department_id);

✅ UNIQUE 인덱스 생성

-- 기본 인덱스 생성 (검색 속도 향상)
CREATE INDEX idx_department ON employees(department_id);

📌 idx_email 인덱스를 생성하면 email 컬럼에서 중복 값 입력을 방지할 수 있음.

✅ PRIMARY KEY와 UNIQUE INDEX 차이점

구분	PRIMARY KEY	UNIQUE INDEX
중복 허용	❌ (유일해야 함)	✅ (중복 가능, NULL 값 허용)
NULL 허용	❌ (NULL 불가)	✅ (NULL 가능)
한 테이블 내 개수	1개만 가능	여러 개 가능

📌 PRIMARY KEY는 테이블당 하나만 존재할 수 있으며, NULL 값을 허용하지 않음.

📌 UNIQUE INDEX는 여러 개 생성 가능하며, NULL 값을 가질 수 있음.

✅ FULLTEXT INDEX 추가

-- 전체 텍스트 검색 인덱스 생성 (InnoDB 지원 가능)
CREATE FULLTEXT INDEX idx_description ON products(description);

📌 FULLTEXT INDEX를 사용하면 MATCH()와 AGAINST()를 활용한 고속 검색 가능.

📌 LIKE '%검색어%' 보다 성능이 우수한 검색을 수행할 수 있음.

SELECT * FROM products WHERE MATCH(description) AGAINST('fast query');

✅ 인덱스 삭제

DROP INDEX idx_department ON employees;

✅ 전체 인덱스 확인

SHOW INDEX FROM employees;

---

3-9. 파티셔닝 (Partitioning)

파티셔닝은 대용량 데이터를 여러 개의 작은 파티션으로 분할하여 관리하는 방법입니다.

MariaDB는 RANGE, LIST, HASH 등의 파티셔닝을 지원합니다.

✅ RANGE 파티셔닝 예제

-- RANGE 파티셔닝 (연도별 데이터 분할)
CREATE TABLE sales (
    id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2),
    PRIMARY KEY (id, sale_date)
) PARTITION BY RANGE(YEAR(sale_date)) (
    PARTITION p1 VALUES LESS THAN (2022),
    PARTITION p2 VALUES LESS THAN (2023),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

• 2022년 이전 데이터는 p1에 저장
• 2023년 이전 데이터는 p2에 저장
• 그 이후 데이터는 p3에 저장

📌 각 연도별로 데이터를 분할하여 저장하여 특정 연도 조회 시 속도가 향상됨.

✅ LIST 파티셔닝

-- LIST 파티셔닝 (지정된 값 기반 분할)
CREATE TABLE employees (
    id INT NOT NULL,
    name VARCHAR(100),
    department VARCHAR(50),
    PRIMARY KEY (id, department)
) PARTITION BY LIST COLUMNS(department) (
    PARTITION p1 VALUES IN ('HR', 'Finance'),
    PARTITION p2 VALUES IN ('IT', 'Marketing')
);

📌 LIST 파티셔닝은 특정 컬럼 값에 따라 데이터를 나누는 방식으로, 범위보다 명확한 그룹으로 분류할 때 유용.

✅ HASH 파티셔닝

-- HASH 파티셔닝 (균등 분배)
CREATE TABLE logs (
    id INT NOT NULL,
    log_message TEXT,
    PRIMARY KEY (id)
) PARTITION BY HASH(id) PARTITIONS 4;

📌 HASH 파티셔닝은 특정 값의 해시 값을 계산하여 균등하게 데이터를 배분함.

📌 특정 컬럼 값이 특정 범위에 편중되지 않도록 고르게 저장하는 데 유용.

✅ 파티션 확인

SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'sales';

📌 현재 데이터베이스 내 sales 테이블의 파티션 정보를 확인 가능.

✅ 파티션 확인

SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'sales';

---

3-10. 테이블스페이스(TableSpace) 관리

MariaDB는 데이터를 물리적으로 저장하는 테이블스페이스를 관리할 수 있습니다.

✅ InnoDB 테이블을 특정 테이블스페이스에 저장

-- 테이블을 특정 테이블스페이스에 저장 (InnoDB 전용)
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100)
) ENGINE=InnoDB
TABLESPACE ts1;

📌 테이블을 ts1 테이블스페이스에 저장할 수 있음.

📌 하지만, MariaDB에서는 대부분의 경우 기본 테이블스페이스(innodb_file_per_table=1)를 사용하며, 사용자 지정 테이블스페이스는 잘 사용되지 않음.

✅ 테이블스페이스 목록 조회

SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLESPACES;

📌 MariaDB의 모든 테이블스페이스 정보를 확인할 수 있음.

✅ 테이블스페이스 목록 조회

SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLESPACES;

---

✅ 정리

• 테이블 생성 (CREATE TABLE)
• 제약 조건 (PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE 등)
• 테이블 수정 (ALTER TABLE)
• 테이블 삭제 (DROP TABLE, TRUNCATE)
• 데이터베이스 생성 및 삭제
• 테이블 데이터 초기화 (TRUNCATE TABLE)
• 인덱스(Index)와 성능 최적화
• 파티셔닝(Partitioning)으로 대용량 데이터 관리
• 테이블스페이스(TableSpace) 관리

---