Back to Blog
DBSQLDDL

0x05. SQL 데이터 정의어 (DDL)

CREATE, ALTER, DROP으로 테이블을 만들고, 바꾸고, 지우는 SQL DDL의 핵심을 정리한다.

SQL에서 데이터를 조회하거나 삽입하기 전에, 먼저 테이블 자체를 정의해야 한다. 이 역할을 담당하는 것이 DDL(Data Definition Language), 즉 데이터 정의어다.

DDL을 통해 다음과 같은 정보를 지정할 수 있다.

  • 각 릴레이션의 스키마 (테이블 구조)
  • 각 속성에 대응하는 데이터 타입
  • 데이터의 규칙을 명시하는 무결성 제약조건(Integrity Constraints)
  • 성능 향상을 위한 인덱스(Index)
  • 보안 및 권한 설정
  • 디스크 상의 물리적 저장 구조

이 글에서는 SQL의 데이터 타입부터 CREATE, DROP, ALTER까지 DDL의 핵심 문법을 살펴본다.

SQL의 데이터 타입

문자열 (String)

CHAR(n) 은 고정 길이 문자열이다. 지정한 n만큼 항상 공간을 차지하며, 최대 길이는 255이다.

VARCHAR(n) 은 가변 길이 문자열이다. 실제 저장된 문자 수만큼만 공간을 사용하고, 최대 길이는 65,535이다.

둘의 차이를 비유하면, CHAR는 정해진 크기의 상자에 물건을 넣는 것이고, VARCHAR는 물건 크기에 맞춰 상자가 줄어드는 것이다. 길이가 일정한 데이터(예: 국가 코드)에는 CHAR가, 길이가 제각각인 데이터(예: 이름, 주소)에는 VARCHAR가 적합하다.

VARCHAR 범위를 넘어서는 긴 문자열에는 TEXT 계열 타입을 사용한다.

타입최대 크기
TINYTEXT255 bytes
TEXT65,535 bytes (64KB)
MEDIUMTEXT16,777,215 bytes (16MB)
LONGTEXT4,294,967,295 bytes (4GB)

숫자 (Numeric)

정수 타입은 크기에 따라 여러 종류가 있다.

타입크기
SMALLINT16-bit
INT (INTEGER)32-bit
BIGINT64-bit

이 외에도 TINYINT, MEDIUMINT 등이 있다.

고정 소수점 타입으로는 NUMERIC(p, d) 가 있다. p는 전체 자릿수, d는 소수점 이하 자릿수를 의미한다. 예를 들어 NUMERIC(3,1)은 44.5는 저장할 수 있지만, 444.5나 0.32는 저장할 수 없다. MySQL에서는 DECIMAL이 NUMERIC과 동일하게 동작한다.

부동 소수점 타입으로는 FLOAT(32-bit 단정밀도)와 REAL/DOUBLE(64-bit 배정밀도)이 있다.

DECIMAL vs FLOAT/DOUBLE: FLOAT와 DOUBLE은 연산이 빠르지만 근사값을 저장한다. 반면 DECIMAL은 느리지만 정확한 값을 보장한다. 금액처럼 정밀도가 중요한 데이터에는 반드시 DECIMAL을 사용해야 한다.

날짜/시간 (Temporal)

타입형식범위
DATEYYYY-MM-DD1000-01-01 ~ 9999-12-31
TIMEHH:MM:SS-838:59:59 ~ 838:59:59
DATETIMEYYYY-MM-DD HH:MM:SS1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
YEARYYYY1901 ~ 2155 (잘못된 값은 0000으로 변환)
TIMESTAMP(n)Unix time 기반1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTC

TIMESTAMP는 32-bit 부호 있는 정수로 저장되기 때문에, 2038년까지만 표현 가능하다는 한계가 있다. 이른바 Y2K38 문제다. 크기 n에 따라 표시 형식이 달라진다.

대용량 객체 (Large Objects)

바이너리 데이터에는 BINARY(n) (고정 길이, 최대 255)과 VARBINARY(n) (가변 길이, 최대 65,535)을 사용한다.

더 큰 바이너리 데이터에는 BLOB(Binary Large OBject) 계열을 사용한다.

타입최대 크기
TINYBLOB255 bytes
BLOB65,535 bytes (65KB)
MEDIUMBLOB16,777,215 bytes (16MB)
LONGBLOB4,294,967,295 bytes (4GB)

CREATE

CREATE DATABASE

데이터베이스를 생성하려면 CREATE DATABASE를 사용한다. 생성 시 기본 문자 인코딩과 정렬 방식(collation)을 함께 지정할 수 있다.

CREATE DATABASE test
DEFAULT CHARACTER SET utf8
COLLATE utf8_unicode_ci;

여기서 collation이란 문자열을 비교하고 정렬하는 규칙을 의미한다. 데이터베이스를 생성한 뒤에는 USE 문으로 해당 데이터베이스를 선택한다.

USE test;

CREATE TABLE

테이블 생성은 CREATE TABLE 문으로 한다.

CREATE TABLE table_name (
  Col1_name data_type(size),
  Col2_name data_type(size),
  ...
);

무결성 제약조건 (Integrity Constraints)

SQL은 무결성 제약조건을 위반하는 모든 업데이트를 거부한다. 무결성 제약조건은 "어떤 데이터가 유효한지"를 명시하는 규칙이다.

주요 제약조건 종류는 다음과 같다.

제약조건문법설명
기본키PRIMARY KEY (A1, ..., An)행을 고유하게 식별
외래키FOREIGN KEY (Am, ..., An) REFERENCES r다른 테이블 참조
유일키UNIQUE (A1, ..., An)중복 값 불허
NOT NULLNOT NULLNULL 값 불허 (기본값은 NULL 허용)
값 제약CHECK (조건), DEFAULT값의 범위나 기본값 지정

PRIMARY KEY vs UNIQUE

PRIMARY KEY와 UNIQUE 모두 중복 값을 허용하지 않는다는 점에서 비슷하다. 둘 다 각 행의 식별자 역할을 할 수 있다. 하지만 중요한 차이가 있다.

PRIMARY KEYUNIQUE
NULL 허용불가가능
테이블당 개수1개만여러 개 가능
인덱스Clustered Index 자동 생성Non-clustered Index

예를 들어, PRIMARY KEY로 지정된 컬럼에 중복 값을 INSERT하면 해당 쿼리는 거부(rejected) 된다. 반면 UNIQUE 컬럼은 NULL 값으로의 변경은 허용된다.

DROP

테이블을 삭제하려면 DROP TABLE을 사용한다.

DROP TABLE time_slot_backup;

테이블 자체는 남기고 데이터만 삭제하고 싶다면 TRUNCATE를 사용한다.

TRUNCATE TABLE time_slot_backup;

데이터베이스 전체를 삭제할 때는 DROP DATABASE를 사용한다.

DROP DATABASE university;

ALTER

이미 생성된 테이블의 스키마를 변경할 때는 ALTER TABLE을 사용한다. 컬럼을 추가(ADD), 수정(MODIFY), 삭제(DROP)할 수 있다.

-- 컬럼 추가
ALTER TABLE time_slot_backup ADD remark VARCHAR(20);

-- 컬럼 타입 수정
ALTER TABLE time_slot_backup MODIFY remark CHAR(20);

-- 컬럼 삭제
ALTER TABLE time_slot_backup DROP remark;

그 외에도 다양한 ALTER 문이 가능하다.

-- 컬럼 삭제 (COLUMN 키워드 명시)
ALTER TABLE instructor DROP COLUMN salary;

-- PRIMARY KEY 제약조건 삭제
ALTER TABLE instructor DROP PRIMARY KEY;

-- FOREIGN KEY 제약조건 삭제
ALTER TABLE instructor DROP FOREIGN KEY instructor_ibfk_1;

-- DEFAULT 값 삭제
ALTER TABLE student ALTER tot_cred DROP DEFAULT;

HGU 전산전자공학부 홍참길 교수님의 23-1 Database System 수업을 듣고 작성한 포스트이며, 첨부한 모든 사진은 교수님 수업 PPT의 사진 원본에 필기를 한 수정본입니다.

데이터베이스 시스템 시리즈8편