SQL의 개념과 SQL로 데이터베이스를 정의하는 법. table생성, 각종 데이터 타입, constraints, 키(key)들을 활용해 DB구축

이 글은 다음 유튜브 영상을 참조해 작성했습니다.

https://youtu.be/c8WNbcxkRhY

SQL 기본 개념

SQL뜻

- Structured Query Language

- 현업에서 쓰이는 relational DBMS의 표준 언어

- 종합적인 database언어: DDL + DML+ VDL

SQL주요 용어

relational data model	SQL
relation	table
attribute	column
tuple	row
domain	domain

SQL에서 relation이란?

- multiset(= bag) of tuples @ SQL

- 중복된 tuple을 허용한다

SQL & RDBMS

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

 

예제를 통해 SQL로 DB 정의하기

IT회사 관련 RDB 만들기

- 부서, 사원, 프로젝트 관련 정보들을 저장할 수 있는 관계형 데이터베이스를 만들자

- 사용할 RDBMS는 MySQL(InnoDB)

 

※ 이를 만들기 위해서는 각자 컴퓨터에서 MySQL을 설치해 실습해야 하기 때문에 설치를 위한 글 2개를 링크합니다.

1. 맥 (M2칩)

https://rec-of-tech.tistory.com/3

맥 mysql server 다운로드 및 실행

2. 윈도우 10

https://hongong.hanbit.co.kr/mysql-%EB%8B%A4%EC%9A%B4%EB%A1%9C%EB%93%9C-%EB%B0%8F-%EC%84%A4%EC%B9%98%ED%95%98%EA%B8%B0mysql-community-8-0/

MySQL 다운로드 및 설치하기(MySQL Community 8.0)

저는 윈도우 10 환경 컴퓨터에 MySQL설치 후 Command Line Client를 통해 실습할 예정입니다.

 

database 정의하기

mySQL설치후 어떠한 데이터가 있는지 확인하기 위한 명령

SHOW DATABASES;

다음과 같은 결과를 얻을 수 있다. 초기에는 information_schema, mysql, performance_schema, sys 4개가 표시된다.

데이터베이스 구축을 위해 DATABASE를 생성한다.

CREATE DATABASE company;

명령 수행 후 SHOW DATABASES;를 출력하면 company라는 이름으로 데이터베이스가 생성됨을 알 수 있다.

지금은 아무 데이터베이스를 선택하지 않았기 때문에 선택된 데이터 베이스를 확인하는 명령이 존재한다.

SELECT database();

아무 데이터베이스를 선택한 적이 없기 때문에 NULL이 표시된다.

내가 사용하고 싶은 데이터베이스는 다음 명령을 통해 선택할 수 있다.

USE company;

명령을 통해 company라는 데이터베이스를 사용한다고 선택하고 SELECT를 통해 확인할 수 있다.

데이터 베이스를 삭제하기 위한 명령은 다음과 같다.

DROP DATABASE company;

 

 

DATABASE vs SCHEMA

- MySQL에서는 DATABASE와 SCHEMA가 같은 뜻을 의미

- CREATE DATABASE company = CREATE SCHEMA company

- 다른 RDBMS에서는 의미가 다르게 쓰임

- i.g. PostgreSQL에서는 SCHEMA가 DATABASE의 namespace를 의미

 

table 정의하기

IT회사 RDB만들기

부서, 사원, 프로젝트 관련 정보들을 저장할 수 있는 관계형 데이터베이스!

DEPARTMENT

id

name

leader_id

DEPARTMENT 테이블 생성하기

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

EMPLOYEE

id

name

birth_date

sex

position

salary

dept_id

 

EMPLOYEE 테이블 생성하기

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

PROJECT

id

name

leader_id

start_date

end_date

PROJECT 테이블 생성하기

mysql> 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

empl_id

proj_id

WORKS_ON 테이블 생성하기

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

TABLE을 생성하고 나서 FOREIGN KEY추가하기

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

attribute data type: 숫자

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

 

attribute data type: 문자열

종류	설명	MySQL
고정 크기 문자열	- 최대 몇 개의 '문자'를 가지는 문자열을 저장할지를 지정 - 저장될 문자열의 길이가 최대 길이보다 작으면 나머지를 space로 채워서 저장 - name char(4)일 때 다음과 같이 저장: 'a   ', '한국  ', '고고고고', 'wow '	CHAR(n) (0 <= n <= 255)
가변 크기 문자열	- 최대 몇 개의 '문자'를 가지는 문자열을 저장할지를 지정 - 저장될 문자열의 길이 만큼만 저장 - name varchar(4)일 때 다음과 같이 저장: 'a', '한국', '고고고고', 'wow'	VARCHAR(n) (0<= n <= 65,535)
사이즈가 큰 문자열	- 사이즈가 큰 문자열을 저장할 때 사용	TINYTEXT TEXT MEDIUMTEXT LONGTEXT

attribute data type: 날짜와 시간

종류	설명	MySQL
날짜	- 년, 월, 일을 저장 - YYYY-MM-DD	DATE ('1000-01-01'~'9999-12-31')
시간	- 시, 분, 초를 저장 - hh:mm:ss or hhh:mm:ss	TIME ('-838:59:59' ~ '838:59:59')
날짜와 시간	- 날짜와 시간을 같이 표현 - YYYY-MM-DD hh:mm:ss - TIMESTAMP는 time-zone이 반영됨	DATETIME ('1000-01-01 00:00:00' to '9999-12-31 23:59:59')
		TIMESTAMP ('1970-01-01 00:00:01' UTC ~ '2038-01-19 03:14:07' UTC)

attribute data type: 그 외

종류	설명	MySQL
byte-string	(문자열이 아니라) byte string을 저장	BINARY VARBINARY BLOB type
boolean	- true, false를 저장 - MySQL에는 따로 없음	TINYINT로 대체해서 사용
위치관련	위치 관련 정보를 저장	GEOMETRY etc
JSON	- json 형태의 데이터를 저장 - e.g. {"name":"messi","age": 38}	JSON

 

Key constraints: PRIMARY KEY

- primary key: table의 tuple을 식별하기 위해 사용, 하나 이상의 attribute(s)로 구성

- primary key는 중복된 값을 가질 수 없으며, NULL도 값으로 가질 수 없다

- primary key를 선언하는 방법은 아래와 같다

▶attribute 하나로 구성될 때

create table PLAYER(
	id	INT	PRIMARY KEY
    	...
);

▶attribute 하나 이상으로 구성될 때

create table PLAYER(
	team_id	VARCHAR(12),
    	back_number	INT,
    	...
    	PRIMARY KEY(team_id, back_number)
);

 

Key constraints: UNIQUE

- UNIQUE로 지정된 attribute(s)는 중복된 값을 가질 수 없다

- 단, NULL은 중복을 허용할 수도 있다 (RDBMS 마다 다름)

- UNIQUE를 선언하는 방법은 아래와 같다

▶attribute 하나로 구성될 때

create table PLAYER(
	id	INT	UNIQUE
    	...
);

▶attribute 하나 이상으로 구성될 때

create table PLAYER(
	team_id	VARCHAR(12),
	back_number	INT,
	...
	UNIQUE(team_id, back_number)
);

 

NOT NULL constraint

- attribute가 NOT NULL로 지정되면 해당 attribute는 NULL을 값으로 가질 수 없다

- NOT NULL을 선언하는 방법은 아래와 같다

create table Student(
	...
	phone_number	INT	NOT NULL	UNIQUE,
	...
);

 

attribute DEFALUT

- attribute의 defalut값을 정의할 때 사용

- 새로운 tuple을 저장할 때 해당 attribute에 대한 값이 없다면 default 값으로 저장

- DEFAULT를 선언하는 방법은 아래와 같다

create table Orders(
	...
	menu	varchar(15)	DEFAULT '짜장면',
	...
);

 

CHECK constraint

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

- CHECK를 선언하는 방법은 아래와 같다

▶ attribute하나로 구성될 때

create table EMPLOYEE(
	...
	age	INT CHECK(age >= 20)
);

▶ attribute 하나 이상으로 구성될 때

create table PROJECT(
	start_date	DATE,
	end_date	DATE,
	...
	CHECK(start_date < end_date)
);

 

Referential integrity constraint : FOREIGN KEY

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

- FOREIGN KEY를 선언하는 방법은 아래와 같다

create table EMPLOYEE(
	...
	dept_id	INT,
	FOREIGN KEY (dept_id)
		references DEPARTMENT(id)
		on delete reference_option
		on update reference_option
);

reference_option	설명
CASCADE	참조값의 삭제/변경을 그대로 반영
SET NULL	참조값이 삭제/변경 시 NULL로 변경
RESTRICT	참조값이 삭제/변경되는 것을 금지
NO ACTION	RESTRICT와 유사
SET DEFAULT	참조값이 삭제/변경 시 default 값으로 변경

 

consratint 이름 명시하기

- 이름을 붙이면 어떤 constraint를 위반했는지 쉽게 파악할 수 있다

- constraint를 삭제하고 싶을 때 해당 이름으로 삭제 가능

create table TEST(
	age	INT CONSTRAINT age_over_20	CHECK (age > 20)
);

CONSTRAINT age_over_20 이름을 붙였을 때	CONSTRAINT age_over_20 이름을 생략했을 때
Check constraint 'age_over_20' is violated.	Check constraint 'test_chk_1' is violated.

 

ALTER TABLE

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

유형	MySQL 예제
attribute 추가	ALTER TABLE employee ADD blood VARCHAR(2);
attribute 이름 변경	ALTER TABLE employee RENAME COLUMN phone TO phnone_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를 변경하는 것이라면 변경 작업 때문에 서비스의 백엔드에 영향이 없을지 검토한 후에 변경하는 것이 중요

 

DROP TABLE

- table을 삭제할 때 사용

- DROP TABLE table_name;

 

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

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