[Database] SQL의 기본 개념(MySQL)

데이터베이스

SQL의 기본 개념

---

 

SQL(Structured Query Language)은 관계형 데이터베이스 관리 시스템(RDBMS)에서 데이터를 관리하고 조작하기 위해 사용되는 언어이다. SQL은 데이터베이스에 쿼리를 보내거나 데이터를 추가, 수정, 삭제하는 등 다양한 작업을 수행하는 데 사용된다.  

 

SQL의 주요 용어

Realtional Data Model	SQL
relation	table
attribute	column
tuple	row
domain	domain

 

Relation Data Model에서 relation이라 불리는 것은 SQL에서 table이라 불린다, 마찬가지로 attribute = column, tuple = row, domain = domain으로 불리게 된다. 하지만 필자의 경험으로 현업에서는 저 둘을 명확하게 구분지어서 부르진 않는다. 예를 들어, table이라 부르면서 그 테이블의 행은 tuple이라고 부르는 경우도 있으며 attribute 대신 column이라 부르는 경우도 있다. 따라서, 저 둘을 명확하게 구분 짓기보단 서로 같은 의미로 해석하면 될 거 같다. 하지만 SQL에서 relation이라 불리는 것이 있는데 relation은 RDM(Relational Data Model)에서의 realtion과 SQL에서의 reation의 의미는 조금 다를 수 있다.

 

SQL에서 relation이란?

SQL에서 불리는 relation의 의미는 multiset of tuples이다. set은 기본적으로 중복을 허용하지 않지만 말 그대로 multiset이기 때문에 중복을 허용한다는 의미다, 즉 tuple(행)의 중복을 허용한다.

 

SQL & RDBMS

SQL은 RDBMS의 표준 언어이지만 실제 구현에 강제가 없기 때문에 RDBMS마다 제공하는 SQL의 문법이 조금씩 다를 수 있다. 예를 들어, MySQL의 문법과 Oracle의 문법이 조금씩 다르다는 점이 있다. 따라서 현재 사용하고 있는 RDBMS(예 : MySQL, Oracle 등)의 사용 문법을 자세히 알고 있는 게 중요하다.

 

이제는 아래에서 예제를 통해 SQL로 간단한 DB를 한 번 정의해 보자.

 

SQL로 DB 정의하기

이번 예제로 사용할 RDBMS는 MySQL로, 부서, 사원, 프로젝트 관련 정보들을 저장할 수 있는 관계형 데이터베이스를 만들어보겠다.

 

데이터베이스 생성

CREATE DATABASE company;

위의 쿼리를 생성하면 company라는 데이터베이스가 생성된다. 

 

SHOW DATABASE;

SHOW DATABASE 명령어로 생성된 데이터베이스를 확인할 수 있다.

 

SELECT database();

데이터베이스를 만들었으면 해당 데이터베이스를 선택해서 사용해야 한다. 위의 쿼리를 실행하여 현재 선택된 데이터베이스를 확인할 수 있다. 현재는 아무 데이터베이스도 선택하지 않았기 때문에 출력은 null이 될 것이다.

 

USE company;

사용할 데이터베이스를 방금 생성한 데이터베이스인 company로 지정해 준다. 위의 쿼리를 실행하면 현재 company 데이터베이스를 지정하여 사용한다.

 

DATABASE와 SCHEMA

MySQL에서는 DATABASE와 SCHEMA가 같은 뜻을 의미한다. 예를 들어, CREATE DATABASE company와 CREATE SCHEMA company는 같은 의미이다. 하지만 이는 MySQL에 해당되는 사항이고 다른 RDBMS에서는 의미가 다를 수 있다. (PostgreSQL에서는 SCHEMA는 DATABASE의 namespace를 의미한다.)

 

테이블을 생성하기에 앞서 데이터베이스에는 어떤 데이터 타입이 있는지 한 번 살펴보고 넘어가자.

 

데이터 타입

첫 번째로 간단하게 DEPARTMENT 테이블을 생성해 보았는데 위의 쿼리를 참고하면서 데이터베이스에는 어떤 데이터 타입을 사용하는지 궁금증이 생겨 아래에 정리해 보았으니 참고하면 좋을 거 같다.(MySQL을 기준으로 작성된 것이기 때문에 다른 RDBMS와 차이가 있을 수 있다는 점은 참고 바란다.)

 

숫자 

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

해당 표는 MySQL을 기준으로 작성된 것이므로 다른 RDBMS에서는 다를 수 있다는 점을 참고 바란다. 고정 소수점 방식의 DECIMAL 같은 경우 첫 번째 매개변수로는 총 몇 자리인지에 대한 자릿수를 설정하고 두 번째 파라미터로는 소수점 몇 자리 이하까지에 대한 설정이다. 

 

SQL 표준에서 DECIMAL과 NUMERIC은 약간의 차이가 있지만 MySQL에서는 차이가 없다, 표준에서의 차이점은 예를 들어 위의 표와 같이 5자리 수로 설정을 했을 때 NUMERIC 같은 경우는 5자리가 넘어가면 나머지 숫자들을 저장하지 않지만 DECIMAL 같은 경우는 5자리가 넘어가도 유연하게 저장한다.

 

문자열 

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

TINYTEXT나 TEXT는 사실 VARCHAR와 비슷한 크기의 텍스트를 저장하기 때문에 실제로 VARCHAR보다 더 큰 텍스트(예를 들어, 게시글 같은 경우)는 MEDIUMTEXT나 LONGTEXT를 사용하는 것이 권장된다.

 

날짜와 시간

종류	설명	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이 반영됨	TIMESTAMP ('1970-01-01 00:00:01' UTC ~ '2038-01-19 03:14:07' UTC)
		DATETIME ('1000-01-01 00:00:00' to '9999-12-31 23:59:59')

시간에서 hhh와 같이 3 자릿수를 표시할 때는 보통 얼마 큼의 시간이 걸렸는지 측정할 때 많이 사용된다. 그리고 날짜와 시간을 모두 표현하고 싶을 땐 TIMESTAMP와 DATETIME을 사용하는 데 이 둘의 차이점은 첫 번째로 위의 표에서 볼 수 있듯이 범위의 차이가 있다. 두 번째로 TIMESTAMP는 UTC(표준 시간)으로 저장한다. 저장된 time zone을 기준으로 저장한다는 의미이다. 

 

기타

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

byte-string은 보통 암호화 관련해서 문자열 그 자체로 저장할 순 없으니  많이 사용한다. boolean은 MySQL에서 제공되지 않기 때문에 TINYINT로 0과 1로 사용하게 된다.

 

 

TABLE 정의하기

위에서 앞서 언급했듯이 이번 시간에는 부서, 사원, 프로젝트 관련 정보들을 저장할 수 있는 관계형 데이터베이스를 설계하기로 했다. 이제 위의 정보들을 저장할 수 있는 테이블을 생성할 것이다. 생성에 앞서 각 테이블의 설계는 아래와 같다.

 

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

 

각 테이블에서 파란색으로 강조된 칼럼들은 PK(Primary Key)를 의미한다. PK는 테이블에서 각각의 튜플을 식별하기 위한 기본키이다.

 

 

DEPARTMENT TABLE 생성

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

가장 먼저 DEPARTMENT 테이블을 생성하는 쿼리이다. 먼저 각각의 라인은 각 속성을 설정한다고 보면 된다. 각 속성마다 칼럼 이름, 데이터 타입, 기타 옵션들 순서로 설정한다.

 

PRIMARY KEY

primary key는 table의 tuple을 식별하기 위해 사용하고 하나 이상의 attribute로 구성한다. 또한, 중복된 값을 가질 수 없으며, NULL도 값으로 가질 수 없다.

 

UNIQUE KEY

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

... UNIQUE(컬럼1, 컬럼2)

여러 개의 UNIQUE 키를 설정할 땐 위와 같이 할 수 있다.

 

NOT NULL

attribute가 NOT NULL로 지정되면 해당 attribute는 NULL을 가질 수 없다. 즉, 해당 속성에는 반드시 데이터가 비어있지 않고 들어가 있어야 한다는 의미다.

 

여기까지 살펴보았을 때 위에서 만들었던 department 테이블을 생성하는 쿼리에 대해 조금 더 이해가 갔을 거라 생각한다.

 

EMPLOYEE TABLE 생성

CREATE TABLE EMPLOYEE(
    id INT PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    birth_date DATE,
    sex CHAR(1) CHECK(sex in ('M','F')),
    position VARHCAR(10),
    salary INT DEFAULT 1000000,
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES DEPARTMENT(id)
    ON DELETE SET NULL ON UPDATE CASCADE,
    CHECK(salary >= 1000000)
);

앞서 department 테이블을 생성했을 때 살펴보았던 키워드들은 이해가 갈 것이다. 하지만 아직 살펴보지 않은 키워드들은 어떤 의미인지 알 수가 없기 때문에 아래에서 각 키워드들을 살펴보자.

 

DEFAULT

attribute(s)의 default 값을 정의할 때 사용한다. 새로운 tupe을 저장할 때 해당 attribute에 대한 값이 없다면 default 값으로 저장.

CREATE TABLE ORDERS(
    ...
    menu VARCHAR(15) DEFAULT '라면'
    ...
);

 

 

CHECK

attribute(s)의 값을 제한하고 싶을 때 사용한다.

CREATE TABLE PROJECT(
    start_date DATE,
    end_date DATE
    ...
    CHECK(start_date < end_date)
);

1개의 attribute에 대해서 제약을 걸고 싶을 땐 칼럼 옆에 바로 선언해 줘도 되지만 위의 예제처럼 2개 이상의 칼럼에 대해 CHECK를 걸고 싶을 땐 위와 같이 아래쪽에 선언을 해줘야 한다.

 

FOREIGN KEY

attribute(s)가 다른 table의 primary key나 unique key를 참조할 때 사용한다. foreign key로 참조를 할 땐 참조할 다른 table의 primary 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 값으로 변경

FOREIGNKEY(외래키로 지정할 칼럼) REFERENCES 참조할_테이블(참조할 테이블의 기본 키)과 같은 형식으로 외래키를 지정할 수 있고 참조 옵션을 설정할 수 있다.

 

ON_DELETE 참조옵션

참조하고 있는 값이 삭제가 될 경우 옵션 지정

 

ON_UPDATE 참조옵션

참조하고 있는 값이 업데이트될 경우 옵션 지정

 

(옵션의 종류는 위의 표를 참고하면 된다._

 

이제 나머지 테이블들도 생성을 한 번 해보자.

 

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 CASCASE,
    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 테이블

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

가장 처음 DEPARTMENT 테이블을 선언하였을 때 LEADER_ID를 EMPLOYEE 테이블에 외래키로 걸어줬어야 했다. 하지만 위에 쿼리에서 외래키를 지정하지 않은 이유는 DEPARTMENT 테이블을 선언했을 때는 EMPLOYEE 테이블이 없었기 때문이다. 이제 테이블 수정을 통해 LEADER_ID를 EMPLOYEE 테이블에 외래키를 걸어주자.

 

ALERT TABLE department ADD FOREIGN KEY (leader_id)
REFERENCES employee(id)
ON UPDATE CASCADE
ON DELETE SET NULL;

ALTER TABLE 변경하려는 테이블 이름 ADD FOREIGN KEY (지정할 외래 키) REFERENCES 참조할 테이블(참조할 테이블의 기본 키)과 같은 형식으로 지정해 줄 수 있다.

 

ALTER TABLE의 유형

유형	MySQL
attribute 추가	ALTER TABLE 테이블이름 ADD 속성이름타입 예 : ALETER TABLE employee ADD blood VARCHAR(2);
attribute 이름 변경	ALTER TABLE 테이블이름 RENAME COLUMN 기존속성이름 TO 새로운속성이름 예 : ALTER TABLE employee RENAME COLUMN phone TO phone_num;
attribute 타입 변경	ALTER TABLE 테이블이름 MODIFY COLUMN 속성 타입 예 : ALTER TABLE employee MODIFY COLUMN blood CHAR(2);
table 이름 변경	ALTER TABLE 테이블이름 RENAME TO 새로운이름 예 : ALTER TABLE logs RENAME TO backend_logs;
primary key 추가	ALTER TABLE 테이블이름 ADD PRIMARY KEY(컬럼) 예 : ALTER TABLE log ADD PRIMARY KEY(id);
...	...

ALTER TABLE를 사용할 때 주의할 점은 이미 서비스 중인 TABLE의 SCHEMA를 변경하는 것이라면 변경 작업 때문에 서비스의 백엔드에 영향이 없을지 검토한 후에 변경하는 것이 중요하다.

 

테이블 삭제

DROP TABLE 테이블이름;

위의 쿼리를 실행하면 데이터를 포함한 테이블 자체가 삭제되기 때문에 주의해서 사용해야 한다.

 

 

데이터베이스 구조를 정의할 때 중요한 점

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