1. SQL 기본 개념
1.1. 구조적 쿼리 언어 (structured query language)
•
줄여서 SQL
•
현업에서 쓰이는 relational DBMS의 표준 언어
•
종합적인 database 언어: DDL + DML + VDL
1.2. SQL 주요 용어
relational data model | SQL |
relation | table |
attribute | column |
tuple | row |
domain | domain |
•
SQL에서 relation이란?
◦
multiset(= bag) of tuples
◦
중복된 tuple을 허용한다
•
SQL은 RDBMS의 표준 언어이지만 실제 구현에 강제가 없기 때문에, RDBMS마다 제공하는 SQL의 스펙이 조금씩 다르다.
◦
MySQL ≠ PostgreSQL ≠ Oracle
2. MySQL 데이터 유형 (MySQL attribute data type)
2.1. 숫자 속성 데이터 유형 (numeric attribute data type)
종류 | 설명 | MySQL | 사이즈 |
정수 | 정수를 저장할 때 사용 | TINYINT | 1 byte |
SMALLINT | 2 byte | ||
MEDIUMINT | 3 byte | ||
INT or INTEGER | 4 byte | ||
BIGINT | 8 byte | ||
부동 소수점 방식
(floating-point) | - 실수(real number)를 저장할 때 사용
- 고정 소수점 방식에 비해 정확하지 않다 | FLOAT | 4 byte |
DOUBLE or
DOUBLE PRECISION | 8 byte | ||
고정 소수점 방식
(fixed-point) | - 실수를 정확하게 저장할 때 사용
- DECIMAL(5, 2) ⇒ [-999.99 ~ 999.99] | DECIMAL or NUMERIC
(널널 vs 엄격 차이) | variable |
2.2. 문자열 속성 데이터 유형 (string attribute data type)
종류 | 설명 | MySQL |
고정 크기 문자열 | - 최대 몇 개의 ‘문자’를 가지는 문자열을 저장할지를 지정
- 저장될 문자열 길이가 최대 길이보다 작으면 나머지를 space로 채워서 저장
- name char(4)일 때 다음과 같이 저장: a , 한국 | CHAR(n)
(0 ≤ n ≤ 255) |
가변 크기 문자열 | - 최대 몇 개의 ‘문자’를 가지는 문자열을 저장할지를 지정
- 저장될 문자열의 길이 만큼만 저장
- name varchar(4)일 때 다음과 같이 저장: a, 한국 | VARCHAR(n)
(0 ≤ n ≤ 65,535) |
사이즈가 큰 문자열 | - 사이즈가 큰 문자열을 저장할 때 사용 | TINYTEXT
TEXT
MEDIUM TEXT
LONGTEXT |
2.3. 날짜와 시간 속성 데이터 유형 (datetime attribute data type)
종류 | 설명 | MySQL |
날짜 | - 년, 월, 일을 저장
- YYYY-MM-DD | DATE
(’1000-01-01’ ~ ‘9999-12-31’) |
시간 | - 시, 분, 초를 저장
- hh:mm:ss or hhh:mm:ss
- hhh:mm:ss → 주로 경과 시간을 기록할 때 사용 | TIME
(’-838:59:59’ ~ ‘838:59:59’) |
날짜와 시간 | - 날짜와 시간을 같이 표현
- YYYY-MM-DD hh:mm:ss | DATETIME
(’1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’) |
- TIMESTAMP는 time-zone이 반영됨 | TIMESTAMP
(’1970-01-01 00:00:01’ UTC ~ ‘2038-01-19 03:14:07’ UTC) |
2.4. 그 외 속성 데이터 유형 (etc attribute data type)
종류 | 설명 | MySQL |
byte-string | - (문자열이 아니라) byte string을 저장
- BLOB type은 주로 암호화 키를 저장할 때 사용 | BINARY
VARBINARY
BLOB type |
boolean | - true, false를 저장
- MySQL에는 따로 없음 | TINYINT로 대체해서 사용 |
위치 관련 | - 위치 관련 정보를 저장 | GEOMETRY
etc |
JSON | - json 형태의 데이터를 저장
- 예) {”name”: “messi”, “age”: 38} | JSON |
3. 실습 - IT 회사 관련 RDB 만들기
•
부서, 사원, 프로젝트 관련 정보들을 저장할 수 있는 관계형 데이터베이스를 만들자
•
사용할 RDBMS는 MySQL (InnoDB) 기준
•
DATABASE vs SCHEMA
◦
MySQL에서는 DATABASE와 SCHEMA가 같은 뜻을 의미
▪
CREATE DATABASE company = CREATE SCHEMA company
◦
다른 RDBMS에서는 의미가 다르게 쓰임
▪
예) PostgreSQL에서는 SCHEMA가 DATABASE의 namespace를 의미
3.1. 스키마 설계
•
DEPARTMENT
id | name | leader_id |
•
EMPLOYEE
id | name | birth_date | sex | position | salary | dept_id |
•
PROJECT
id | name | leader_id | start_date | end_date |
•
WORKS_ON
empl_id | proj_id |
3.1. 데이터베이스 목록 (현재 계정이 접근 가능한 데이터베이스)
SHOW DATABASES;
SQL
복사
3.2. 데이터베이스 생성
CREATE DATABASE company;
SQL
복사
3.3. 현재 선택된 데이터베이스 확인
SELECT database();
SQL
복사
3.4. 사용할 데이터베이스 선택하기
USE company;
SQL
복사
3.5. 데이터베이스 삭제
DROP DATABASE company;
SQL
복사
3.6. 부서(department) 테이블 생성
create table DEPARTMENT (
id INT PRIMARY KEY,
name VARCHAR(20) NOT NULL UNIQUE,
leader_id INT
);
SQL
복사
PRIMARY KEY Key constraints
UNIQUE Key constraints
NOT NULL constraint
3.7. 직원(EMPLOYEE) 테이블 생성
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)
);
SQL
복사
DEFAULT attribute
CHECK constraint
FOREIGN KEY Referential integrity constraint
constraint 이름 명시하기
3.8. 프로젝트(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)
);
SQL
복사
3.9. 배치(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
);
SQL
복사
3.10. 부서(department) 테이블 외래키 추가
ALTER TABLE department ADD FOREIGN KEY (leader_id)
REFERENCES employee(id)
on update CASCADE
on delete SET NULL;
SQL
복사
•
ALTER TABLE 대표 유형
유형 | MySQL |
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 log ADD PRIMARY KEY (id); |
◦
이미 서비스 중인 table의 schema를 변경하는 것이라면 변경 작업 때문에 서비스의 백엔드에 영향이 없을지 검토한 후에 변경하는 것이 중요!!
3.11. 테이블 삭제
DROP TABLE table_name;
SQL
복사
4. 결론
•
database 구조를 정의할 때 중요한 점
◦
만들려는 서비스의 스펙과 데이터 일관성, 편의성, 확장성 등등을 종합적으로 고려하여 DB 스키마를 적절하게 정의하는 것이 중요
