데이터베이스(쉬운코드)

3. SQL로 DB 구조 정의하기

youbing 2024. 11. 14. 13:17
본 내용은 유튜버 쉬운코드의 강의 "데이터베이스"를 참고하여 작성하였습니다.

 

SQL 기본 개념

SQL 뜻

  • Structured Query Language
  • 현업에서 쓰이는 relation DBMS의 표준 언어
  • 종합적인 DB 언어 : DDL + DML + VDL

 

SQL 주요 용어

relational data model -> SQL

  • relation -> table
  • attribute -> column
  • tuple -> row
  • domain -> domain

 

SQL에서 relation이란?

  • multiset(=bag) of tuples
    • 중복된 tuple을 허용한다.

 

SQL & RDBMS

  •  SQL은 RDBMS의 표준 언어지만 실제 구현에 강제가 없기 때문에 RDBMS마다 제공하는 SQL의 스펙이 조금씩 다르다.

MySQL로 정의할 예제 DB 소개

IT 회사 관련 RDB 만들기

  • 부서, 사원, 프로젝트 관련 정보들을 저장할 수 있는 관계형 DB를 만들자.
  • 사용할 RDBMS는 MySQL(1nnoDB)

DB 만들기

SHOW DATABASES; MySQL에 어떤 DB가 있는지 확인하고 싶을 때 사용

 

CREATE DATABASE (DB 이름); DB 생성

  • company라는 이름의 DB를 만들었더니, SHOW DATABASES; 에서 DB가 하나 추가됨.

 

SELECT database(); 활성화시킨 DB (어떤 DB를 사용할 건지 지정했는가?)

 

USE (DB 이름); 활성화시킬 DB 선택

DROP DATABASE (DB 이름); 비활성화시킬 DB 선택

 

DB vs Schema

  • MySQL에서는 DB와 Schema가 같은 의미
  • CREATE DATABASE company = CREATE SCHEMA company
  • 다른 RDBMS에서는 의미가 다르게 쓰임
    • i.g. PostgreSQL에서는 Schema가 DB의 namespace를 의미
      • 그래서 하나의DB가 여러 개의 Schema를 가질 수 있음.
      • DB 안에서 Schema가 정의되고 그 안에서 table이 정의됨.
    • MySQL은 DB 안에 table이 정의됨.

table 정의하기

IT 회사 RDM 만들기

  • 부서, 사원, 프로젝트 관련 정보들을 저장할 수 있는 관계형 DB

  • Department의 leader_id는 Employee의 id를 참조하고, Employee의 dept_id는 Department의 id를 참조함.
  • Project의 leader_id가 Employee의 id를 참조함.
  • Works_On의 empl_id는 Employee의 id를 참조하고, Works_On의 proj_id는 Project의 id를 참조함.

 

Department Table 만들기

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

 

attribute data type 소개

attribute data type : 숫자

SMALLINT, INT or INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL or NUMERIC

 

attribute data type : 문자열

CHAR, VARCHAR, MEDIUMTEXT, LONGTEXT

 

attribute data type : 날짜와 시간

DATE, TIME, DATETIME, TIMESTAMP

 

attribute data type : 그 외

 BINARY, TINYINT, GEOMETRY, JSON

primary key 적용하기

Key constraints : PRIMARY KEY

  • primary key : table의 tuple을 식별하기 위해 사용, 하나 이상의 attribute(s)로 구성
  • primary key는 중복된 값을 가질 수 없으며, Null도 값으로 가질 수 없다.

노란색은 중복이라 안됨. 빨간색은 Null이 포함되어서 안됨.

 

primary key 선언하는 방법

unique constraint 적용하기

  • unique로 지정된 attribute(s)는 중복된 값을 가질 수 없다.
  • 단, Null은 중복을 허용할 수도 있다. (RDBMS마다 다름)

 

unique 위반
unique 선언하는 방법

Not Null constraint 적용하기

  • attribute가 Not Null로 지정되면 해당 attribute는 Null을 값으로 가질 수 없다.

Null이 있으면 안됨.

 

Not Null 선언 방법

  • 보통 Not Null과 unique를 같이 사용

 

EMPLOYEE table 만들기

 

Default 적용하기

  • attribute의 default 값을 정의할 때 사용
  • 새로운 tuple을 저장할 때 해당 attribute에 대한 값이 없다면 default 값으로 저장

 

default 선언하는 방법

 

 

Check constraint 적용하기

  • attribute의 값을 제한하고 싶을 때 사용

10,000,000이 조건인 50,000,000보다 작으므로 저장되지 않음.

 

Check 선언하는 방법

Foreign key 적용하기

  • attribute(s)가 다른 table의 primary key나 unique key를 참조할 때 사용

  • 만약 EMPLOYEE의 department_id에 1이라는 값이 들어가는데 DEPARTMENT의 id에 1이 없으면 entity integrity constraint에 따라 department_id에 1이 저장되지 않음.

 

Referential inegrity constraint : Foreign key

  • Employee라는 테이블은 dept_id라는 INT 값을 속성으로 가지는데 이 값은 Foreign key이다.
  • 이 FK는 DEPARTMENT 테이블의 id라는 값을 참조한다.
  • 값이 삭제/변경될 때는 FK가 어떻게 바뀌는지 정의한다.

 

reference option

  • CASCADE : 참조값의 삭제/변경을 그대로 반영
    • 참조값이 삭제/변경되면 그걸 참조하던 FK도 같이 삭제/변경
  • SET NULL : 참조값이 삭제/변경 시 NULL로 변경
    • 참조값이 삭제/변경되면 그걸 참조하던 FK가 Null로 바뀜.
  • RESTRICT : 참조값이 삭제/변경되는 것을 금지
  • NO ACTION : RESTRICT와 유사
  • SET DEFAULT : 참조값이 삭제/변경 시 default 값으로 변경

constraint에 이름 붙이기

  • 이름을 붙이면 어떤 constraint을 위반했는지 쉽게 파악할 수 있다.
  • constraint를 삭제하고 싶을 때 해당 이름으로 삭제 가능

test_chk_1이 뭔지 알고 싶으면 show create table (테이블 이름)

 

 

 

PROJECT table 만들기

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 table 만들기

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
);

 

  • 지금까지의 문제점 : DEPARTMENT table을 정의할 때는 EMPLOYEE table이 생성되어 있지 않아, DEPARTMENT의 leader_id가 FK 설정이 안되어 있음. -> 이를 해결하기 위해 ALTER TABLE을 실행

alter table로 schema 변경하기

ALTER TABLE : table이 생성되고 난 이후에 schema를 변경하고 싶을 때 사용

  • 이미 서비스 중인 table의 schema를 변경하는 것이라면 변경 작업 때문에 서비스의 백엔드에 영향이 없을지 검토한 후에 변경하는 것이 중요

 

ALTER TABLE department ADD FOREIGN KEY (leader_id)
  REFERENCES employee(id)
  on update CASCADE
  on delete SET NULL;

 

  • attribute 추가 : ALTER TABLE employee ADD blood VARCHAR(2);
  • attribute 이름 변경 : ALTER TABLE employee RENAME COLUMN phone TO phone_num;
  • attribute 타입 변경 : ALTER TABLE employee MODIFY COLUMN blood CHAR(2); 
  • table 이름 변경 : ALTER TABLE logs RENAME TO backend_logs;
  • primary key 추가 : ALTER TABLE logs ADD PRIMARY KEY (id);

table 삭제하기

  • DROP TABLE (테이블 이름) : table을 삭제할 때 사용

DB 구조 정의할 때 중요한 점

  • 만들려는 서비스의 스펙과 데이터 일관성, 편의성, 확장성 등등을 종합적으로 고려하여 DB 스키마를 적절하게 정의하는 것이 중요하다.