데이터 정의어 (DDL)

💡 이 내용 이후로 모든 SQL 관련 내용은 NomadCoder 강의를 듣고 정리한 글입니다.
아래의 링크에 SQL 학습에 필요한 데이터가 존재합니다.
https://nomadcoders.co/sql-masterclass

아래의 글과 함께 보시면 좋습니다.

2025.07.16 - [Programming/DataBase] - 데이터 정의어 (DDL)

개념

데이터를 저장할 테이블과 같은 구조를 정의하는 데 사용되는 명령어

---

기본 키 - Primary Key

기본 키는 데이터베이스 테이블의 각 행/레코드를 고유하게 구분하기 위한 테이블의 필드

특징

종류	설명
불변성	해당 값이 변경되지 않아야 함
고유함	해당 값은 유일해야 하며, 동일한 값을 가진 것이 존재하지 않아야 함

기본 키의 종류

종류	설명
자연 키 (Natural key)	테이블에서 자연적으로 파생됨, 테이블의 데이터와 논리적인 관계에 있음 ex) id INTEGER PRIMARY KEY AUTOINCREMENT
후보 키 (Surrogate Key)	인의적으로 만든 키, 오직 행의 추적에 의의를 둠 ex) title TEXT PRIMARY KEY UNIQUE NOT NULL

•  

---

외래키 - Foreign Key

외래키는 다른 Table의 column을 참조하는 column을 말한다.

FOREIGN KEY (column_name) REFERENCES {table_name}(참조할 column_name) {ON DELETE option}

옵션	설명
ON DELETE CASCADE	참조하고 있는 원래 데이터 삭제 시 해당 데이터도 같이 삭제
ON DELETE SET NULL	참조하고 있는 원래 데이터 삭제 시 해당 데이터의 외래키를 NULL로 변경
ON DELETE DEFAULT	참조하고 있는 원래 데이터 삭제 시 해당 데이터의 외래키를 설정헀던 DEFAULT 값으로 변경

FOREIGN KEY (breed_id) REFERENCES breeds(breed_id) ON DELETE SET NULL,
FOREIGN KEY (owner_id) REFERENCES owners(owner_id) ON DELETE CASCADE

---

테이블 생성 - CREATE TABLE

SQLite의 경우 다른 DB와 달리 컬럼을 생성할 때 유형 선언을 하지 않아도 된다.

CREATE TABLE {테이블 명} (컬럼 명);

---

SQLite

-- SQLite
CREATE TABLE movies (
	id INTEGER PRIMARY KEY AUTOINCREMENT, -- AUTOINCREMENT : 값을 1씩 자동으로 증가
	-- 이런식으로 해당 테이블과 관련이 없는 기본키를 후보키라고 한다.
    title TEXT UNIQUE NOT NULL, 
    -- title을 기본키로 할 경우 테이블과 논리적으로 연관이 있으므로 자연키라고 한다.
    released INTEGER NOT NULL CHECK (released > 0), -- INTEGER : 정수
    overview TEXT NOT NULL CHECK (LENGTH(overview) <= 100),
    rating REAL DEFAULT 1 CHECK (rating BETWEEN 0 AND 10), -- REAL : 실수
    director TEXT DEFAULT 'Unknown',
    adult INTEGER DEFAULT 0 CHECK (adult = 0 OR adult = 1), 
    -- SQLITE에는 Boolean 타입이 없으므로 0, 1만 입력하게 제한해야한다.
    -- poster BLOB 
    /* 
    이미지 같은 파일을 저장할 때 BLOB을 쓴다. 
	하지만 DB에 파일을 저장하는것은 권장하지 않는다.
	대신 저장된 이미지의 경로를 TEXT로 저장한다.
	*/
) STRICT; -- SQLite에서 데이터 타입을 엄격히 검사

 

SQLite 데이터 타입

SQlite의 데이터타입은 이렇게 5종류가 있다.

유형	설명
TEXT	텍스트(문자열)
INTEGER	정수(숫자)
REAL	소수점 숫자
BLOB	blob 데이터 (Big Large Of Byte)
NULL	NULL 값

---

제약 사항 - Constraint

제약 사항	설명	예시
UNIQUE	중복 값을 허용하지 않는다.
NOT NULL	NULL 값을 허용하지 않는다.
DEFAULT	값을 넣지 않을 경우 자동으로 입력할 기본 값을 설정	DEFAULT 0
CHECK (조건문)	조건문에 대해 참인 값만 입력	CHECK (Column = 0)

내장 스칼라 함수

SQLite 함수 - https://sqlite.org/lang_corefunc.html

Built-In Scalar SQL Functions

---

MYSQL

-- MySQL
CREATE TABLE users (
	-- 아래 처럼 Column과 함께 제약 조건을 작성한 것을 unnamed constraint라 한다.
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(15) NOT NULL,
    gender ENUM('male', 'female') NOT NULL,
    age INT NOT NULL,
    email VARCHAR(50) NOT NULL,

	-- 아래 처럼 제약 조건을 따로 작성한 것을 named constraint라고 한다.
    CONSTRAINT chk_age CHECK ( age < 150 ),
    CONSTRAINT uq UNIQUE (email)
);

---

Generated Column (Computed Column)

• 다른 컬럼을 사용하여 값을 도출하는 컬럼
• MySQL뿐만 아니라 SQLite, PostgreSQL도 지원한다.

종류	설명
STORED	실제로 데이터를 DB에 저장 데이터 수정, 삽입 시 추가 연산이 일어남
VIRTUAL	데이터를 DB에 저장하지 않고 해당 컬럼을 조회할 때마다 연산을 수행 데이터 조회 시 추가 연산이 일어남

STORED

CREATE TABLE users_v2 (
    user_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(20),
    last_name VARCHAR(20),
    email VARCHAR(50),
    full_name VARCHAR(41) -- 20 + " " + 20 == 41
    -- 자동으로 first_name과 last_name를 붙인 문자열을 저장한다.
        GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) 
        STORED NOT NULL
);

VIRTUAL

ALTER TABLE users_v2
    ADD COLUMN domain
        VARCHAR(30)
        GENERATED ALWAYS AS
            (SUBSTRING_INDEX(email, '@', -1)) -- @ 이후의 문자열을 저장한다.
        VIRTUAL;

---

테이블 삭제 - DROP TABLE

DROP TABLE {테이블 명};

DROP TABLE movies;

---

쿼리 저장 - View

자주 사용하는 쿼리는 view를 통해 저장하고 언제 어디서든 사용할 수 있다.

CREATE VIEW {view_name} AS {쿼리문}

View 생성

-- 일반적으로 view의 이름을 정할 때 앞에 v_ 를 붙인다.
CREATE VIEW v_guy_ritchie AS SELECT 
	*
FROM
	movies
WHERE
	director = 'Guy Ritchie';

View 실행

-- 테이블이 아니라 view를 통해 이전에 저장한 쿼리를 간단히 실행할 수 있다.
SELECT * FROM v_guy_ritchie;

View 삭제

DROP VIEW v_guy_ritchie;

---

테이블 수정 - ALTER TABLE

Column 추가

ALTER TABLE {table_name} ADD COLUMN {column_name} {constraint};

-- NOT NULL을 추가할 경우 DEFAULT를 통해 초기에 채워넣을 기본값을 설정해야한다.
ALTER TABLE users ADD COLUMN email VARCHAR(50) NOT NULL DEFAULT '<EMAIL>';

Column 삭제

ALTER TABLE {table_name} DROP COLUMN {column_name};

ALTER TABLE users DROP COLUMN updated_at;

Column 변경

Column의 이름과 제약 조건 둘 다 변경

ALTER TABLE {table_name} CHANGE COLUMN {column_name} {change_name} {constraint};

ALTER TABLE users CHANGE COLUMN id user_id BIGINT UNSIGNED AUTO_INCREMENT;
ALTER TABLE users CHANGE COLUMN name name VARCHAR(20) NOT NULL;

Column 제약 조건 변경

Column의 제약 조건만 변경

ALTER TABLE {table_name} MODIFY COLUMN {column_name} {constraint};

• 해당 컬럼이 저장할 타입을 변경하는 것은 안됨
• ex) CHAR → INT

ALTER TABLE users MODIFY COLUMN name VARCHAR(15) NOT NULL;

Column 이름 변경

Column의 이름만 변경

ALTER TABLE {table_name} RENAME COLUMN {column_name} TO {change_name};

ALTER TABLE users RENAME COLUMN user_id TO id;

Column 제약 조건 삭제

ALTER TABLE {table_name} DROP CONSTRAINT {constraint_name};

• 제약 조건 이름이 없을 경우 (inline constraint) 해당 컬럼의 이름으로 제약 조건이 자동으로 생성을 한다.
• 자세한 것은 SHOW CREATE TABLE {table_name}을 통해 확인이 가능하다.

ALTER TABLE users DROP CONSTRAINT chk_age;

Column 제약 조건 추가

ALTER TABLE {table_name} ADD CONSTRAINT {constraint_name} {constraint};

ALTER TABLE users ADD CONSTRAINT chk_age CHECK ( age < 150 );

Foreign Key 제약 조건 변경

외래키의 제약 조건을 변경하려면 먼저 제약 조건을 삭제를 해야 한다.

ALTER TABLE {table_name} DROP FOREIGN KEY {constraint_name}
ADD CONSTRAINT {constraint_name} FOREIGN KEY (foreignKey_name)
REFERENCES {table_name} (column_name) ON DELETE {option}

ALTER TABLE dogs
DROP FOREIGN KEY owner_fk
ADD CONSTRAINT owner_fk FOREIGN KEY (owner_id) 
    REFERENCES owners (owner_id) ON DELETE SET NULL;