03. DB/table 생성

Last updated - 2023년 04월 21일 Edit Source

유튜버 쉬운코드 님의 데이터베이스 강의를 정리한 내용

SQL (Structured Query Language)

• 현업에서 쓰이는 relational DBMS의 표준 언어
• 종합적인 database 언어 : DDL + DML + VDL

Database 구조를 정의할 때 중요한 점

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

주요 용어

SQL에서 relation이란?

• multiset (= bag) of tuples @ SQL
  • 중복된 tuple을 허용한다는 의미
  • SQL에서는 각 tuple이 중복될 수 있다

SQL & RDBMS

• SQL은 RDBMS의 표준 언어이지만 실제 구현에 강제가 없음
• 따라서, RDBMS마다 제공하는 SQL의 스펙이 조금씩 다름

• 데이터베이스 목록 확인

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

• 데이터베이스 생성

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

CREATE DATABASE db이름;

+--------------------+
| Database           |
+--------------------+
| company            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

• 지금 선택된 데이터베이스가 무엇인지

1
2
3
4
5
6
7

SELECT database();

+------------+
| database() |
+------------+
| NULL       |
+------------+

• 사용할 데이터베이스 지정

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

USE db이름;

Database changed

다시 선택된 데이터베이스 확인해보면

SELECT database();
+------------+
| database() |
+------------+
| company    |
+------------+

• 데이터베이스 삭제

1

DROP DATABASE db이름;

• MySQL 에서는 DATABASE와 SCHEMA가 같은 뜻을 의미
  • CREATE DATABASE company = CREATE SCHEMA company
• 그러나, 다른 RDBMS에서는 의미가 다르게 쓰임
  • ex) PostgreSQL에서는 SCHEMA가 DATABASE의 namespace를 의미
  • 그래서 PostgreSQL에서는 하나의 DATABASE가 여러 개의 SCHEMA를 가질 수 있음
  • 데이터베이스 안에서 스키마가 정의, 스키마 안에서 테이블이 정의

• PostgreSQL에서는 SMALLINT, INT or INTEGER, BIGINT 3종류만 있음
  • TINYINT, MEDIUMINT가 없음
• 고정 소수점 방식
  • DECIMAL(precision, scale)
  • precision : 자릿수, scale : 소숫점 이하 몇 째 자리까지
  • DECIMAL(5, 2) => [-999.99 ~ 999.99]

SQL 표준 스펙에서는 DECIMAL은 유연한 처리, NUMERIC은 엄격한 처리
하지만 MySQL에서는 DECIMAL, NUMERIC 둘 다 엄격하게 처리함
엄격한 처리는 (5, 2)에서 5자리를 넘어가도 5자리까지만 딱 저장한다는거
유연한 처리는 넘어가도 저장해주는거임

• VARCHAR가 CHAR보다 좋은 것 아니냐? 할 수 있지만, VARCHAR와 CHAR를 어떻게 구현했는지에 따라 조금씩 다르다. -> RDBMS마다 다르다.
  • PostgreSQL
    • VARCHAR 권장
  • MySQL : VARCHAR가 storage 상 이점은 있겠지만, 시간적 성능이 CHAR에 비해 안좋을 수 있음
    • Form Number같이 문자열 크기가 고정적 -> CHAR 권장
    • 문자열 크기가 고정적이지 않은 속성인 경우 -> VARCHAR 권장
• PostgreSQL 에서는 TEXT만 있음
• MySQL 에서는 TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT 있음
  • VARCHAR보다 긴건 MEDIUMTEXT, LONGTEXT
  • TINYTEXT, TEXT는 VARCHAR와 동일하거나 그보다 작은 길이를 저장

• 시간을 3자리 까지 표시할 수 있다는 것은(hhh) 일이 경과된 시간을 표시하기 위함임
• DATETIME과 TIMESTAMP의 차이
  • 범위의 차이
  • time-zone 반영 차이
• TIMESTAMP는 표준시간대 UTC를 반영하는데, MySQL이 설치되어있는 서버의 timezone이나 MySQL 내부적으로 설정된 timezone의 시간 대비 표준 시간대로 변환해서 표준 시간대로 저장하고 읽어 올때도 변환해서 읽어옴
  • 즉, MySQL의 TIMESTAMP는 time-zone에 영향을 받는다!!

• byte-string은 보안 관련해서 암호화하고 싶을 때 암호화 키를 byte-string으로 저장함
• boolean
  • PostgreSQL은 있음
  • MySQL은 따로 없음
    • TINYINT로 대체해서 사용 (0과 1)

attribute DEFAULT

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

DEFAULT 선언방법

ALTER TABLE

• table의 schema를 변경하고 싶을 때 사용

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

DROP TABLE

• table을 삭제할 때 사용
• DROP TABLE 테이블명;

Key Constraints : PRIMARY KEY

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

Primary key 선언방법

• attribute가 여러 개라면 반드시 오른쪽처럼 적어야함
• 그러나, attribute가 한개라면 PRIMARY KEY(id)처럼 해도 됨

Key Constraints : UNIQUE

• UNIQUE로 지정된 attribute(s)는 중복된 값을 가질 수 없음
• 단, NULL은 중복을 허용할 수도 있음 (RDBMS 마다 다름)
  • MySQL, PostgreSQL에서는 NULL 중복 허용해줌

UNIQUE 선언방법

NOT NULL constraint

• attribute가 NOT NULL로 지정되면 해당 attribute는 NULL을 값으로 가질 수 없음
• 보통 NOT NULL과 UNIQUE는 같이 많이 선언함
  • 왜냐하면, UNIQUE가 NULL의 경우에는 중복을 허용할 수 있기 때문에, attribute가 NULL을 허용하지 않아도 되는 조건이라면 이렇게 많이 씀

NOT NULL 선언방법

CHECK constraint

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

CHECK 선언방법

Referential integrity constraint : FOREIGN KEY

• 참조 무결성 제약조건
• attribute(s)가 다른 table의 primary key나 unique key를 참조할 때 사용

FOREIGN KEY 선언방법

1. FOREIGN KEY로 설정할 attribute 이름
2. 어떤 테이블을 참조하는지, 테이블의 어떤 attribute 참조하는지
3. 참조하고 있는 값이 삭제되거나 업데이트 될 때 FOREIGN KEY value를 어떻게 할 지 옵션
   • MySQL에서는 CASCADE, SET NULL, RESTRICT 3개가 있다고 이해하자
   • PostgreSQL에서는 5가지 전부 제대로 지원함

• CASCADE

• SET NULL

• RESTRICT : 참조하고 있는애 있느면 수정 불가

• NO ACTION

  • MySQL에서는 RESTRICT와 NO ACTION이 동일
  • SQL 표준 스펙에서는 거의 유사
    • SQL에서 NO ACTION은 한 트랜잭션 내의 여러 SQL이 실행되는 동안에는 참조값이 변경/삭제 되는 것은 허용하지만, 트랜잭션이 끝났을 때도 여전히 참조 무결성 제약을 위반하고 있다면 이것은 금지한다는 의미

• SET DEFAULT

  • MySQL은 SET DEFAULT를 잘 지원하지 않음

constraint 이름 명시하기

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

• test_chk_1이 무엇을 의미하는지 알고싶으면 MySQL에서는 show create table 테이블명;라고 하면 결과로 나오는 화면에서 알 수 있다.

IT 회사 관련 RDB를 만들어보자.

• 부서, 사원, 프로젝트 관련 정보들을 저장할 수 있는 관계형 데이터베이스를 만들자
• 사용할 RDBMS는 MySQL (InnoDB)
  • MySQL은 여러 DB 엔진을 제공하는데, 가장 많이 사용하는 InnoDB를 기준으로 설명

• Data model을 바탕으로 DB의 구조를 기술했음 -> 스키마 만들었다는 거

DEPARTMENT 테이블 만들 당시에는 leader_id에 대한 FOREIGN KEY 지정을 못했었는데, 이제 생겼으니 한번 수정해보자.