유튜브 쉬운코드 님의 SQL의 개념과 SQL로 데이터베이스를 정의하는 법을 배웁니다. table 생성하기, 각종 데이터 타입, constraints, 키(key)들을 활용해서 DB를 구축해봅시다! 영상을 보고 정리한 글입니다.

SQL과 DDL

SQL이란?

Structured Query Language의 줄임말로 RDBMS의 표준 언어이다.
(RDBMS 마다 SQL 문법은 조금씩 다를 수 있다.)

관계형 데이터 모델과 SQL 용어 비교

• 관계형 데이터 모델 : relation, attribute, tuple, domain
• SQL : table, column, row,

(⚠️ SQL에서 relation은 multiset of tuples로 튜플의 중복을 허용한다.)

MySQL로 IT 회사 관련 RDB 정의하기

데이터베이스 정의하기

-- company 데이터베이스 생성
CREATE DATABASE company;

-- 현재 사용 중인 데이터베이스 확인
SELECT database();

-- company 데이터베이스 활성화
USE company;

-- company 데이터베이스 삭제
DROP company;

✅ DATABASE vs SCHEMA
MySQL에서는 DATABASE == SCHEMA
따라서 CREATE DATABASE company == CREATE SCHEMA company
다른 RDBMS에서는 다른 의미로 사용된다. DATABASE 안에서 SCHEMA가 정의되고, SCHEMA 안에서 테이블이 정의된다.

테이블 정의하기

스키마 정의

DEPARTMENT 테이블 정의

CREATE TABLE DEPARTMENT (
	id INT PRIMARY KEY,
	name VARCHAR(20) NOT NULL UNIQUE,
	leader_id INT
);

✅ UNIQUE 어트리뷰트는 중복된 값을 가질 수 없다. (NULL 중복을 허용하는 경우도 있는데 이는 RDBMS마다 다르다.)

EMPLOYEE 테이블 정의

CREATE TABLE EMPLOYEE (
	id INT,
	name VARCHAR(20) NOT NULL,
	birth_date DATE,
	sex CHAR(1) CHECK(sex in ('M', 'F')),
	position VARCHAR(10),
	salary INT DEFAULT 500000000,
	dept_id INT,
	PRIMARY KEY (id),
	FOREIGN KEY (dept_id) REFERENCES DEPARTMENT(id)
		ON DELETE SET NULL ON UPDATE CASCADE,
	CHECK(salary >= 500000000)
);

✅ DEFAULT : 어트리뷰트의 기본값 지정
✅ CHECK : 어트리뷰트의 값을 조건에 맞게 제한

FOREIGN KEY 적용하기

FOREIGN KEY (dept_id) REFERENCES DEPARTMENT(id)
		ON DELETE SET NULL 
		ON UPDATE CASCADE
;

✅ REFERENCE OPTIONS

• CASCADE
  • 참조하는 값이 삭제/변경되면 그 결과를 참조하고 있던 테이블에도 반영
• SET NULL
  • 참조하는 값이 삭제/변경되면 참조하고 있던 테이블에는 NULL로 변경
• RESTRICT
  • 참조하는 값이 삭제/변경되는 것을 금지
• NO ACTION
  • RESTRICT 유사
  • 트랜잭션 동안에는 삭제/변경 허용 → 해당 트랜잭션이 종료되면 RESTRICT
• SET DEFAULT
  • 참조하는 값이 삭제/변경되면 참조하고 있던 테이블에는 디폴트값으로 변경
  • MySQL에서는 지원X

Constraints 이름 명시하기

age INT CONSTRAINT age_over_20 CHECK(age > 20)

이름을 붙이면 어떤 constraint를 위반했는지 쉽게 파악이 가능해진다.

PROJECT 테이블 정의

CREATE TABLE PROJECT (
	id INT PRIMARY KEY,
	name VARCHAR(20) NOT NULL UNIQUE,
	leader_id INT,
	start_date DATE,
	end_date DATE,
	FOREIGN KEY (leader_id) REFERENCES EMPLOYEE(id)
		ON DELETE SET NULL ON UPDATE CASCADE,
	CHECK(start_date < end_date)
);

WORKS_ON 테이블 정의

CREATE TABLE WORKS_ON (
	empl_id INT,
	proj_id INT,
	PRIMARY KEY (empl_id, proj_id),
	FOREIGN KEY (empl_id) REFERENCES EMPLOYEE(id)
		ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (proj_id) REFERENCES PROJECT(id)
		ON DELETE CASCADE ON UPDATE CASCADE,

스키마 변경하기

ALTER TABLE DEPARTMENT ADD FOREIGN KEY (leader_id)
	REFERENCES EMPLOYEE(id)
	ON UPDATE CASCADE
	ON DELETE SET NULL;

변경 작업 때문에 서비스의 백엔드에 영향이 없을지 검토한 후에 변경하는 것이 중요하다!

✅ ALTER TABLE로 스키마 변경하는 유형

• 어트리뷰트 추가
• 어트리뷰트 이름 변경
• 어트리뷰트 타입 변경
• 테이블 이름 변경
• PK 추가

테이블 삭제하기

DROP TABLE WORKS_ON;

테이블 삭제는 항상 모든 검토를 끝낸 뒤 최후에 이루어져야 한다.