데이터베이스 기초 3. SQL로 데이터베이스, 테이블 정의(MySQL)

 

SHOW DATABASES;

CREATE DATABASE company;

USE <DATABSENAME>

 

SELECT DATABASE();

 

 

• MySQL 에서는 databse와 schema가 같은 뜻
  
  • 즉 CREATE DATABSE company = CREATE SCHEMA comapny
  • i.g.) PostgreSQL에서는 SCHEMA가 DATABASE의 namespace를 의미
  • 오라클에서는 유저가 소유한 논리적 데이터 구조,즉, 스키마 오브젝트(테이블,인덱스, 뷰 등)의 집합, 유저=SCHEMA라고 할 수 있다. 서로 다른 유저가 같은 스키마 오브젝트들을 공유할 수 있음.

 

DATA TYPE

숫자타입 종류	설명	사이즈	MySQL Type
정수	정수를 저장할 때 사용	1 byte	TINYINT
		2 byte	SMALLINT
		3 byte	MEDIUMINT
		4 byte	INT or INTEGER
		8 byte	BIGINT
부동 소수점 방식(floating-point)	- 실수(real number)를 저장할 때 사용 - 고정 소수점 방식에 비해 정확하지 않다.	4 byte	FLOAT
		8byte	DOUBLE or DOUBLE PRECISION
고정 소수점 방식(fixed-point)	- 실수를 정확하게 저장할 때 사용 (precision, scale) - DECIMAL(5, 2) => [-999.99 ~ 999.99]	variable	DECIMAL OR NUMERIC

• SQL 표준에서 DECIMAL과 NUMERIC의 차이
  • DECIMAL : 5자리 넘어서도 저장
  • NUMERIC : 5자리 넘어서면 저장하지 않음
  • 하지만 MYSQL은 둘 다 엄격하게 그 자릿수만 저장(표준의 NUMERIC 방식)

문자열 타입 종류	설명	MySQL
고정 크기 문자열	- 최대 몇 개의 문자를 가지는 문자열을 저장할 지를 지정 - 저장될 문자열 길이가 최대 길이보다 작으면 나머지는 space로 채움	CHAR(n) (0 <= n <= 255)
가변 크기 문자열	- 최대 몇 개의 문자를 가지는 문자열을 저장할 지를 지정 - 저장될 문자열의 길이 만큼만 저장	VARCHAR(n) (0 <= n <= 65,535)
사이즈가 큰 문자열	- 사이즈가 큰 문자열을 저장할 때 사용	TINYTEXT TEXT MEDIUMTEXT LONGTEXT

 

날짜, 시간 타입 종류	설명	MySQL
날짜	- 년, 월, 일을 저장	DATE '1000-01-01' ~ '9999-12-31'
시간	- 시,분,초를 저장	TIME '-838:59:59' ~ '838:59:59'
날짜와 시간	- 날짜와 시간을 같이 표현 - TIMESTAMP는 time-zone 반영됨	DATETIME TIMESTAMP

 

기타 타입	설명	MySQL
byte-string	byte string을 저장, 보안 용도 등으로 사용	BINARY VARBINARY BLOB type
boolean	- true, false를 저장 - MySQL에는 없음	TINYINT로 대체
위치	위치 관련 정보 저장	GEOMETRY etc
JSON	- json 형태의 데이터를 저장	JSON

 

Constraints(제약 조건)

• PRIMARY KEY : table의 tuple을 식별하기 위해 사용, 하나 이상의 attribute로 구성
  • Primary key는 중복된 값을 가질 수 없으며, NULL도 값으로 가질 수 없다.(PK가 가지는 속성 하나라도 NULL이면 안됨)
• UNIQUE : 중복된 값을 가질 수 없다.
  • 단, NULL은 중복을 허용할 수도 있다(RDBMS 마다 다름)
• NOT NULL
  • 해당 속성은 NULL 값을 가질 수 없다.
• DEAFULT : 기본값을 정의할 때 사용
  • 새로운 TUPLE 저장 시 해당 attribute 의 값이 없다면 이 기본값을 저장
• CHECK 
  • attribute 의 값을 제한할 때 사용
• FOREIGN KEY
  • attribute가 다른 table의 primary key나 unique key를 참조할 때 사용
  • Options
    • CASCADE : 참조값의 삭제/변경을 그대로 반영
    • SET NULL : 참조값이 삭제/변경 시 NULL로 변경
    • RESTRICT : 참조값이 삭제/변경되는 것을 금지
    • NO ACTION : RESTRICT와 유사 (MySQL 미지원)
    • SET DEFAULT : 참조값이 삭제/변경 시 DEFAULT값으로 변경 (MySQL 미지원)
• CONTRAINT 이름 명시
  • 이름을 붙이면 어떤 CONTRAINT를 위반했는 지 쉽게 파악 가능
  • CONSTRAINT를 삭제하고 싶을 때 해당 이름으로 삭제 가능
• ALTER TABLE로 스키마 변경하기
  • ADD, RENAME COLUMN, MODIFY COLUMN, RENAME TO, ADD PRIMARY KEY.. 등 여러 옵션있음
  • 이미 서비스 중인 TABLE의 SCHEMA를 변경하는 것이라면 변경 작업 때문에 서비스의 백엔드에 영향이 없을지 검토한 후에 변경하는 것이 중요.

 

CREATE TABLE
    DEPARTMENT (
        ID INT PRIMARY KEY,
        NAME VARCHAR(20) NOT NULL UNIQUE,
        LEADER_ID INT
    );
    
    
    CREATE TABLE EMPLOYEE (
    ID         INT PRIMARY KEY,
    NAME       VARCHAR(30) NOT NULL,
    BIRTH_DATE DATE,
    SEX        CHAR(1) CHECK(SEX IN ('M','F')),
    POSITION   VARCHAR(10),
    SALARY     INT DEFAULT 50000000,
    DEPT_ID    INT,
    
    FOREIGN KEY (DEPT_ID) REFERENCES DEPARTMENT(ID)
        ON DELETE SET NULL ON UPDATE CASCADE,
    CHECK (SALARY >= 50000000)
);


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

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의 LEADER_ID FOREIGN KEY 추가
ALTER TABLE DEPARTMENT ADD FOREIGN KEY (LEADER_ID)
    REFERENCES EMPLOYEE(ID)
        ON UPDATE CASCADE
        ON DELETE SET NULL;

 

 

* 데이터베이스 구조를 정의할 때는 구축하는 서비스의 스펙과 데이터 일관성, 편의성, 확장성 등을 종합적으로 고려하여 DB 스키마를 적절하게 정의해야 한다.