MySQL과 SQL

MySQL 서버를 설치하고 CLI/GUI 클라이언트 사용법을 익힙니다. MySQL의 주요 개념들에 대해 공부하고 실제 모델링을 해봅니다.
MySQL, SQL, DB, 무결성, CRUD, JOIN, Index


MySQL 설치

MySQL Community ServerMySQL Community Server

오픈소스 RDBMS 중 MySQL을 이용해 DBMS에 대해서 배워보겠습니다. MySQL 다운로드 링크 에서 OS에 맞는 MySQL Community Server, MySQL Shell (CLI Client), MySQL Workbench (GUI Client)를 설치합니다.

MySQL Community Server는 DBMS 서버 프로그램이고, MySQL Shell은 CLI 클라이언트 프로그램입니다. 또 MySQL Workbench는 GUI 클라이언트 프로그램으로, GUI를 통해 용이하게 DB를 설계하고 관리 할 수 있게 해줍니다.

Mysql CLI 프로그램을 실행

로컬에서 mysql 서버가 실행중이라면, Shell에서 아래와 같이 mysql CLI 클라이언트 프로그램으로 로컬 mysql 서버에 접속 할 수 있습니다. 셸에서 직접 mysql CLI 클라이언트를 실행 할 수 없다면 (Windows의 경우), 위에서 설치한 MySQL Shell을 실행합니다.

~$ mysql -uroot -p
Enter password:

MySQL의 DB

DB HierachyDB Hierachy

DBMS는 프로젝트 단위로 구분되는 다수의 DB를 갖습니다. 또한 하나의 DB는 모델 단위로 구분되는 다수의 Table을 갖습니다. 그리고 하나의 Table은 데이터 단위로 구분되는 다수의 Record를 갖습니다. 마지막으로 하나의 Record는 키와 값의 집합으로 이루어지며, 이 때 이 키를 Field라고 합니다.

DB 조회

서버에 존재하는 모든 DB의 이름을 아래와 같이 출력 할 수 있습니다.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| blog               |
| iad                |
| mysql              |
| performance_schema |
| study              |
| sys                |
| todo               |
| workshop           |
| workshop2          |
+--------------------+
10 rows in set (0.01 sec)

DB 생성

DB를 설계하기 위해서 프로젝트에서 사용 할 DB를 생성합니다.

mysql> create database name_of_db;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| blog               |
| iad                |
| mysql              |
| name_of_db         |
| performance_schema |
| study              |
| sys                |
| todo               |
| workshop           |
| workshop2          |
+--------------------+
11 rows in set (0.00 sec)

DB 삭제

아래처럼 특정 DB를 삭제 할 수 있습니다. 백업되지 않은 데이터는 복원 할 수 없으니, 사용에 주의해야 할 쿼리입니다.

mysql> drop database name_of_db;
Query OK, 0 rows affected (0.00 sec)

쿼리의 종류

쿼리의 문법 체계, SQL에 빠르게 익숙해지는 것은 쉽지 않습니다. 하지만 대부분의 DBMS들이 비슷한 체계의 SQL을 제공하기에 한번 SQL 문법에 익숙해지면 다른 DBMS를 사용하더라도 큰 어려움이 없습니다. DB를 설계하는 과정을 계속하기 전에, 쿼리의 종류에 대해서 먼저 정리해보도록 하겠습니다.

쿼리비고
DDL (Data Definition Language)데이터 정의
createDB나 테이블을 생성
dropDB나 테이블을 삭제
alter테이블을 수정
truncate테이블을 초기화
DML (Data Manipulation Language)데이터 조작
select테이블의 레코드를 조회
insert테이블에 레코드를 추가
delete테이블의 레코드를 삭제
update테이블의 레코드를 수정
DCL (Data Control Language)데이터 접근 제어
grant(관리자가) 사용자에게 DB 제어의 권한을 부여
revoke(관리자가) 사용자에게 DB 제어의 권한을 박탈
start transaction트랙잭션의 시작
commit트랜잭션 승인
rollback트랜잭션 복원

DB와 Table

테이블테이블

프로젝트의 모델을 표현하고 그 데이터를 보관하기 위한 테이블을 생성합니다. 이 때 테이블의 이름을 설정하고, 필드들의 이름과 데이터 타입, 기본값, 인덱스 여부, PK, FK등을 설정합니다.

DB 선택

먼저 작업 할 DB를 선택합니다.

mysql> create database name_of_db;
Query OK, 1 row affected (0.00 sec)

mysql> use name_of_db;
Database changed

mysql> show tables;
Empty set (0.00 sec)

테이블의 생성, 변경, 삭제

테이블을 생성, 변경, 삭제해봅니다.

mysql> create table test(
    -> field1 int,
    -> field2 char
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> describe test;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| field1 | int(11) | YES  |     | NULL    |       |
| field2 | char(1) | YES  |     | NULL    |       |
+--------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table test add column(
    -> field3 int
    -> );

mysql> describe test;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| field1 | int(11) | YES  |     | NULL    |       |
| field2 | char(1) | YES  |     | NULL    |       |
| field3 | int(11) | YES  |     | NULL    |       |
+--------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table test add column( field4 int, field5 int );
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table test drop field3, drop field4;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table test add column(fieldx int), drop field5;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe test;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| field1 | int(11) | YES  |     | NULL    |       |
| field2 | char(1) | YES  |     | NULL    |       |
| fieldx | int(11) | YES  |     | NULL    |       |
+--------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

테이블을 생성하고 삭제하거나, 필드들을 추가, 삭제 할 수 있습니다. 이 때 테이블에 레코드가 존재하는 경우엔 필드를 삭제하는 과정에서 데이터의 손실이 발생 할 수 있습니다.

필드의 데이터 타입

위에서 빈 테이블을 생성하고 필드를 변경하는 중에 field1 int, field2 char와 같이 필드의 이름과 데이터 타입을 명시했었습니다. int는 Integer, char는 Character로 각각 정수와 문자열 타입을 의미합니다. DBMS에서는 다양한 타입의 데이터를 저장하고 또 적합하지 않은 데이터를 방지 할 수 있도록 지원합니다.

데이터 타입설명
int4 Byte로 저장되는 정수
tinyint1 Byte로 저장되는 정수
bigint8 Byte로 저장되는 정수
float(M,D)4 Byte로 저장되는 최대 M자리, 소수점 아래 최대 D자리의 실수 (부정확)
double(M,D)8 Byte로 저장되는 최대 M자리, 소수점 아래 최대 D자리의 실수 (부정확)
decimal(M,D)가변 크기로 저장되는 최대 M자리, 소수점 아래 최대 D자리의 실수 (정확)
char(N)정해진 인코딩을 따르는 N자리의 문자열
varchar(N)정해진 인코딩을 따르는 최대 N자리의 가변 길이 문자열
text정해진 인코딩을 따르는 최대 64KB의 가변 길이 문자열
mediumtext정해진 인코딩을 따르는 최대 16MB의 가변 길이 문자열
longtext정해진 인코딩을 따르는 최대 4GB의 가변 길이 문자열
blob최대 64KB의 가변 길이 바이너리
enum('abc’, '123’, …)지정된 값들 중에서만 입력 할 수 있음
date년-월-일의 날짜
datetime날짜에 시:분:초까지
timestamp1970년 0초 부터 특정 시각까지의 시간을 초로 환산 (Unix Timestamp라고 함)

MySQL의 전체 데이터 타입

CRUD

CRUD (Create, Read, Update, Delete)는 응용프로그램에서 데이터를 조작하는 방식을 표현하는 키워드입니다. 응용프로그램은 사용자의 요청에 따라 데이터를 CRUD하고 그 결과를 화면에 반영해주는 방식으로 구현됩니다. DBMS에서는 위에서 봤던 DML(Data Manipulation Language) Query를 통해 DB의 데이터를 CRUD합니다.

Create

mysql> create table users( id int, name varchar(32), created_at datetime );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into users values(1, 'admin', '2017-11-11');
Query OK, 1 row affected (0.00 sec)

mysql> insert into users values(2, 'guest', now());
Query OK, 1 row affected (0.00 sec)

마지막 쿼리에서 현재의 시간을 반환하는 now() 처럼 SQL들은 특정한 기능을 수행하는 함수를 제공합니다.

Read

mysql> select name, created_at from users;
+-------+---------------------+
| name  | created_at          |
+-------+---------------------+
| admin | 2017-11-11 00:00:00 |
| guest | 2017-12-08 18:57:40 |
+-------+---------------------+
2 rows in set (0.00 sec)

# 모든 필드는 as를 통해 조회시의 필드명을 다르게 나타 낼 수 있습니다. 
mysql> select id as 번호, name as 이름, created_at as 가입일 from users;
+--------+--------+---------------------+
| 번호   | 이름   | 가입일              |
+--------+--------+---------------------+
|      1 | admin  | 2017-11-11 00:00:00 |
|      2 | guest  | 2017-12-08 18:57:40 |
+--------+--------+---------------------+
2 rows in set (0.00 sec)

# as 키워드는 생략 가능합니다.
mysql> select id 번호, name 이름, created_at 가입일 from users;
+--------+--------+---------------------+
| 번호   | 이름   | 가입일              |
+--------+--------+---------------------+
|      1 | admin  | 2017-11-11 00:00:00 |
|      2 | guest  | 2017-12-08 18:57:40 |
+--------+--------+---------------------+
2 rows in set (0.00 sec)

# * 은 모든 필드를 나타내는 특수 문자입니다.
mysql> select * from users;
+------+-------+---------------------+
| id   | name  | created_at          |
+------+-------+---------------------+
|    1 | admin | 2017-11-11 00:00:00 |
|    2 | guest | 2017-12-08 18:57:40 |
+------+-------+---------------------+
2 rows in set (0.00 sec)

mysql> select users.id from users;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

# 테이블명에도 as를 사용 할 수 있습니다.
mysql> select users.id from users as u;
ERROR 1054 (42S22): Unknown column 'users.id' in 'field list'

mysql> select u.id, u.name 이름 from users u;
+------+--------+
| id   | 이름   |
+------+--------+
|    1 | admin  |
|    2 | guest  |
+------+--------+
2 rows in set (0.00 sec)

데이터를 Read하는 쿼리는 상당히 복잡한 로직을 동반할 수 있습니다. 이에 대해서는 뒤에서 더 다룹니다.

Update

mysql> select * from users;
+------+-------+---------------------+
| id   | name  | created_at          |
+------+-------+---------------------+
|    1 | admin | 2017-11-11 00:00:00 |
|    2 | guest | 2017-12-08 18:57:40 |
+------+-------+---------------------+
2 rows in set (0.00 sec)

mysql> update users set name='dumb';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from users;
+------+------+---------------------+
| id   | name | created_at          |
+------+------+---------------------+
|    1 | dumb | 2017-11-11 00:00:00 |
|    2 | dumb | 2017-12-08 18:57:40 |
+------+------+---------------------+
2 rows in set (0.00 sec)


# update 쿼리는 기본적으로 테이블의 모든 레코드를 대상으로 적용됩니다.
# 이때 update 쿼리의 뒷 부분에 where절을 추가해서 특정한 레코드를 대상으로 쿼리를 실행할 수 있습니다.
# where절은 boolean으로 평가되는 표현식입니다.

mysql> update users set name='smart' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from users;
+------+-------+---------------------+
| id   | name  | created_at          |
+------+-------+---------------------+
|    1 | smart | 2017-11-11 00:00:00 |
|    2 | dumb  | 2017-12-08 18:57:40 |
+------+-------+---------------------+
2 rows in set (0.00 sec)

# update 쿼리의 set 구문에 기존 필드명을 참조할 수 있습니다.
# 또 where의 표현식을 다양하게 조합 할 수 있습니다.

mysql> update users set name=concat(name, '_hi') where name like 'sm%';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from users;
+------+----------+---------------------+
| id   | name     | created_at          |
+------+----------+---------------------+
|    1 | smart_hi | 2017-11-11 00:00:00 |
|    2 | dumb     | 2017-12-08 18:57:40 |
+------+----------+---------------------+
2 rows in set (0.00 sec)

# set 구문의 콤마(,)를 통해 한 쿼리로 여러 필드를 수정 할 수 있습니다.

mysql> update users set id=id+10, name='smart_11' where id != 2 and created_at > '2017-01-01';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from users;
+------+----------+---------------------+
| id   | name     | created_at          |
+------+----------+---------------------+
|   11 | smart_11 | 2017-11-11 00:00:00 |
|    2 | dumb     | 2017-12-08 18:57:40 |
+------+----------+---------------------+
2 rows in set (0.00 sec)

눈치 채셨겠지만 where 구문은 insert 쿼리를 제외한 select, update, delete 쿼리에 모두 적용 할 수 있습니다.

Delete

mysql> delete from users;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from users;
Empty set (0.00 sec)

delete 쿼리는 where 조건이 없이 실행되면 테이블의 모든 레코드를 삭제하므로 각별한 주의가 필요합니다.

무결성 제약

도메인 무결성

필드에 타입을 지정하는 이유는 적합하지 않은 데이터를 방지하기 위해서입니다. 이렇게 데이터 타입을 통한 제약 조건을 도메인 무결성 제약 (Domain Integrity Constraint)이라고 합니다.

mysql> insert into users values('id', 'name', now());
ERROR 1366 (HY000): Incorrect integer value: 'id' for column 'id' at row 1

개체 무결성

뿐만 아니라 모든 테이블은 PK라고 하는 고유한 레코드를 대표하는 필드(들)을 지정 할 수 있습니다. 이를 통해서 PK 필드는 테이블 내에서 빈 값을 갖지 않으며, 고유한 값으로 유지될 수 있습니다. 이러한 제약 조건을 개체 무결성 제약 (Entity Integrity Constraint)이라고 합니다.
테이블에 PK를 설정하고 이 고유한 PK 값을 기반으로 행을 필터링하면서 read, update, delete 작업을 수행 할 수 있습니다.

mysql> drop table users;
Query OK, 0 rows affected (0.00 sec)

mysql> create table users(
    -> id int primary key,
    -> name varchar(32)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into users values(1, 'user1');
Query OK, 1 row affected (0.00 sec)

mysql> insert into users values(1, 'user2');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

mysql> insert into users values(null, 'user2');
ERROR 1048 (23000): Column 'id' cannot be null

참조 무결성 제약

이전 챕터에서 배웠던 테이블간의 관계를 적용해보겠습니다. users 테이블과 1:M 관계인 orders 테이블을 생성합니다.

mysql> create table orders(
    -> id int primary key,
    -> user_id int,
    -> product varchar(32)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into orders values(1, 1, '어떤 상품');
Query OK, 1 row affected (0.00 sec)

mysql> insert into orders values(2, 10, '어떤 상품2');
Query OK, 1 row affected (0.00 sec)

mysql> select * from orders;
+----+---------+---------------+
| id | user_id | product       |
+----+---------+---------------+
|  1 |       1 | 어떤 상품     |
|  2 |      10 | 어떤 상품2     |
+----+---------+---------------+
2 rows in set (0.00 sec)

orders.user_idusers.id를 나타내도록 설계하였습니다. 이렇게 관계된 레코드의 PK 값을 통해서 레코드와 레코드간의 관계를 설정합니다. orders.id=1인 첫번째 레코드는 users.id=1user가 '어떤 상품’을 주문했다는 데이터를 표현합니다.
적절합니다만, orders.id=2인 둘째 레코드는 존재하지 않는 users.id=10인 user와의 관계를 표현하고 있습니다. 또한 orders.user_id=1인 주문 데이터가 존재하는 상태에서, users.id=1인 사용자 데이터가 삭제 될 경우도 전체 데이터의 무결성이 무너집니다.

mysql> create table orders(
    -> id int,
    -> user_id int,
    -> product varchar(32),
    -> primary key(id),
    -> foreign key(user_id) references users(id)
    -> );
Query OK, 0 rows affected (0.01 sec)

# PK를 primary key(필드 [, ...])로 정의 할 수 있습니다.
# FK는 foreign key(필드) references 참조테이블(참조필드 [, ...])로 정의 할 수 있습니다.

mysql> insert into orders values(1, 1, '상품1');
Query OK, 1 row affected (0.00 sec)

mysql> insert into orders values(2, 10, '상품2');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`name_of_db`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`))

mysql> delete from users where id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`name_of_db`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`))

FK를 설정하면 존재하지 않는 레코드의 PK를 참조 할 수 없습니다. 또한 1:1이나 1:M 관계에서 자식 레코드가 존재할 때 부모 레코드를 삭제하거나, 부모 레코드의 PK를 업데이트 할 수 없도록 할 수 있습니다. 이러한 제약 조건을 참조 무결성 제약 (Referential Integrity Constraint)이라고 합니다.

참조 무결성은 기본적으로 제약을 위반하는 경우, 위의 예시에서 알 수 있듯 그 질의가 제한(restrict)됩니다. 하지만 update, delete의 두 경우에 대해서 restrict, cascade, set null, no action을 설정 할 수도 있습니다. 예를 들어 user가 삭제되는 경우 모든 자식 order를 삭제하려면 on delete cascade, user의 PK가 업데이트 되는 경우 모든 자식 order의 연결된 FK를 업데이트하려면 on update cascade, user가 삭제되더라도 order는 남겨두되, 연결된 FK를 빈 값으로 지정하려면 on delete set null을 지정 하면 됩니다.

mysql> create table orders(
    -> id int,
    -> user_id int,
    -> product varchar(32),
    -> primary key(id),
    -> foreign key(user_id) references users(id) on delete cascade on update cascade
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into orders values(1, 1, "상품1");
Query OK, 1 row affected (0.00 sec)

mysql> delete from users where id = 1;
Query OK, 1 row affected (0.00 sec)

mysql> select * from orders;
Empty set (0.00 sec)

필드 특성

nullable

기본적으로 PK 제외한 모든 필드에는 null 값을 입력할 수 있습니다.

mysql> describe users;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(32) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into users values(1, null);
Query OK, 1 row affected (0.00 sec)

mysql> insert into users(id) values(2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from users;
+----+------+
| id | name |
+----+------+
|  1 | NULL |
|  2 | NULL |
+----+------+
2 rows in set (0.00 sec)

not null, default

users 테이블의 name 필드에 빈값을 허용하지 않으려면 not null 속성을 추가합니다. 참고로 PK 필드는 개체 무결성을 위해서 기본적으로 not null 속성을 갖습니다.

mysql> drop table orders;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table users;
Query OK, 0 rows affected (0.00 sec)

mysql> create table users(
    -> id int primary key,
    -> name varchar(32) not null default '이름없음'
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into users values(1, null);
ERROR 1048 (23000): Column 'name' cannot be null

mysql> insert into users values(1, '일번');
Query OK, 1 row affected (0.00 sec)

# 테이블명 뒤에 필드명을 나열해서 values 구문과 매치될 필드를 명시 할 수 있습니다.
mysql> insert users(id, name) values(2, '이번');
Query OK, 1 row affected (0.00 sec)

mysql> insert users(name, id) values('삼번', 3);
Query OK, 1 row affected (0.00 sec)

# 이때 기본값이 지정된 필드는 생략 할 수 있습니다.
mysql> insert users(id) values(4);
Query OK, 1 row affected (0.00 sec)

mysql> select * from users;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 일번         |
|  2 | 이번         |
|  3 | 삼번         |
|  4 | 이름없음     |
+----+--------------+
4 rows in set (0.00 sec)

auto_increment

이 때 insert 구문에서 PK 필드를 생략하고 싶다면 어떻게 해야 할까요? 프로그램에서 테이블의 id 최대값을 기억해두면서 매번 그 id 값을 통해 insert 시킬 수도 있겠습니다. 또는 insert 쿼리에 select 쿼리를 연결 할 수 도 있습니다. 하지만 이러한 방식보단 MySQL에서는 AI (auto_increment) 속성을 이용 할 수 있습니다.

mysql> insert into users (select max(id)+1, '삼식이' from users);
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from users;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 일번         |
|  2 | 이번         |
|  3 | 삼번         |
|  4 | 이름없음      |
|  5 | 삼식이       |
+----+--------------+
5 rows in set (0.00 sec)

mysql> drop table users;
Query OK, 0 rows affected (0.00 sec)

mysql> create table users(
    -> id int primary key auto_increment,
    -> name varchar(32) not null default '이름 없음'
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> insert into users(name) values('이름1');
Query OK, 1 row affected (0.00 sec)

mysql> insert into users() values();
Query OK, 1 row affected (0.00 sec)

mysql> select * from users;
+----+---------------+
| id | name          |
+----+---------------+
|  1 | 이름1         |
|  2 | 이름 없음     |
+----+---------------+
2 rows in set (0.00 sec)

unique

마지막으로 1:1 관계와 1:M 관계에 대해서 생각해 보겠습니다. userprofile과 1:M 관계라면 profileuser_id 필드를 두고, FK를 통해 제약을 줄 수 있습니다. 하지만 1:1 관계의 경우엔 user에 profile_id 필드를 두거나, profile에 user_id 필드를 두고 FK로 제약을 주는 것만으로는 부족합니다.

mysql> create table orders(
    -> id int primary key auto_increment,
    -> user_id int not null,
    -> foreign key(user_id) references users(id) on delete cascade on update cascade
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into profiles(user_id) values(1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into profiles(user_id) values(1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from profiles;
+----+---------+
| id | user_id |
+----+---------+
|  1 |       1 |
|  2 |       1 |
+----+---------+
2 rows in set (0.00 sec)

# truncate는 테이블을 초기화하는 쿼리입니다.
mysql> truncate profiles;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from profiles;
Empty set (0.00 sec)

# profiles.user_id 컬럼에 unique 속성을 추가합니다.
mysql> alter table profiles modify user_id int not null unique;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into profiles(user_id) values(1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into profiles(user_id) values(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'user_id'

unique 속성은 위처럼 1:1 관계를 설정하거나, 회원의 로그인 아이디와 같이 테이블 내에서 고유해야하는 필드에 설정 할 수 있는 속성입니다.

조건 절

subquery in selectsubquery in select

위에서 다뤘던 select 쿼리에 대해서 좀 더 자세히 알아보겠습니다. 응용프로그램에서는 일반적으로 복잡한 insert, update, delete 쿼리를 필요로하지 않습니다. 하지만 데이터를 조회하는 select 쿼리의 경우에는 응용프로그램의 요구에 따라 많게는 수십여줄에 이를 수도 있습니다.

todo DB 생성

select 쿼리를 공부하기 전에 다음 챕터에서 계속 이용 할 todo 데이터베이스를 생성합니다. todo DB는 회원(user)이 여러 목록(list)을 가질 수 있으며, 목록은 여러 항목(item)을 가질 수 있습니다. 이번에는 설치한 Workbench GUI 클라이언트 프로그램으로 DB를 모델링하고 더미 데이터를 생성해봅니다. 이 todo DB는 다음 챕터에서 웹 서버를 작성하면서 다시 이용하게 됩니다.

mysql> describe users;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| uid      | varchar(45) | NO   | UNI | NULL    |                |
| password | varchar(45) | NO   |     | NULL    |                |
| name     | varchar(45) | NO   |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

mysql> select * from users;
+----+-------+----------+-----------+
| id | uid   | password | name      |
+----+-------+----------+-----------+
|  1 | admin | 1234     | 관리자    |
|  2 | test  | 1234     | 손님      |
+----+-------+----------+-----------+
2 rows in set (0.00 sec)

mysql> describe lists;
+---------+-------------------------------------+------+-----+---------+----------------+
| Field   | Type                                | Null | Key | Default | Extra          |
+---------+-------------------------------------+------+-----+---------+----------------+
| id      | int(11)                             | NO   | PRI | NULL    | auto_increment |
| name    | varchar(45)                         | NO   |     | NULL    |                |
| icon    | enum('work','other','shop','study') | NO   |     | NULL    |                |
| user_id | int(11)                             | NO   | MUL | NULL    |                |
+---------+-------------------------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> select * from lists;
+----+--------------+-------+---------+
| id | name         | icon  | user_id |
+----+--------------+-------+---------+
|  1 | 공부하기     | study |       1 |
|  2 | 일하기       | work  |       1 |
|  3 | 빈리스트     | other |       1 |
|  4 | aa           | other |       1 |
+----+--------------+-------+---------+
4 rows in set (0.00 sec)

mysql> describe items;
+---------+-------------+------+-----+-------------------+----------------+
| Field   | Type        | Null | Key | Default           | Extra          |
+---------+-------------+------+-----+-------------------+----------------+
| id      | int(11)     | NO   | PRI | NULL              | auto_increment |
| name    | varchar(45) | NO   |     | NULL              |                |
| checked | tinyint(1)  | NO   |     | 0                 |                |
| date    | datetime    | NO   |     | CURRENT_TIMESTAMP |                |
| user_id | int(11)     | NO   | MUL | NULL              |                |
| list_id | int(11)     | NO   | MUL | NULL              |                |
+---------+-------------+------+-----+-------------------+----------------+
6 rows in set (0.00 sec)

mysql> select * from items;
+----+------------------------------+---------+---------------------+---------+---------+
| id | name                         | checked | date                | user_id | list_id |
+----+------------------------------+---------+---------------------+---------+---------+
|  5 | 안드로이드 공부하기               |       0 | 2017-11-07 22:28:36 |       1 |       1 |
|  6 | Rx 공부하기                    |       1 | 2017-11-07 22:28:36 |       1 |       1 |
|  7 | Angular 2.0 공부하기           |       1 | 2017-11-07 22:28:36 |       1 |       1 |
| 10 | aa                           |       1 | 2017-11-09 23:20:24 |       1 |       1 |
| 11 | asdsd                        |       1 | 2017-11-09 23:20:45 |       1 |       1 |
| 12 | asdasd                       |       1 | 2017-11-09 23:22:19 |       1 |       1 |
| 13 | asd                          |       1 | 2017-11-09 23:22:19 |       1 |       1 |
| 38 | asda                         |       0 | 2017-11-14 22:38:48 |       1 |       2 |
| 42 | asasdasdasd                  |       1 | 2017-11-14 22:40:49 |       1 |       2 |
| 43 | fs                           |       0 | 2017-11-14 22:40:51 |       1 |       2 |
+----+------------------------------+---------+---------------------+---------+---------+
10 rows in set (0.00 sec)

비교 연산자

우선 select 뿐만 아니라 update, delete 쿼리에서도 이용 할 수 있는 where 구문에 대해 자세히 알아보겠습니다. where 구문은 boolean으로 평가되는 표현식을 받아 쿼리가 적용될 레코드를 필터링하는데 이용됩니다.

표현식설명
X = Y같다
X != Y 또는 X <> Y다르다
X > Y, <, >=, <=대소를 비교하는 연산자는 숫자 뿐만 아니라 문자열 및 날짜 타입에도 적용 가능
X is null빈 값이다
X is not null빈 값이 아니다
X between Y and ZX >= Y and X <= Z
X not between Y and ZX < Y or X > Z
X like YX(문자열)이 Y와 같다, 이 때 Y에서 %를 와일드카드로 이용 가능
X in (subquery or values)X가 목록 내에 존재한다
X not in (subquery or values)X가 목록 내에 존재하지 않는다
exists (subquery)subquery의 결과 레코드가 한개 이상 존재한다
not exists (subquery)subquery의 결과 레코드가 존재하지 않는다
not (expr) 또는 !(expr)expr가 거짓이다
((expr1) and (expr2)) or not (expr3)and (또는 &&), or (또는 ||), not (또는 !)을 조합해 복잡한 조건을 표현 가능

where

위의 연산자들을 통해 where 절에 다양한 조건을 적용 할 수 있습니다.

mysql> select * from users;
+----+-------+----------+-----------+
| id | uid   | password | name      |
+----+-------+----------+-----------+
|  1 | admin | 1234     | 관리자      |
|  2 | test  | 1234     | 손님      |
+----+-------+----------+-----------+
2 rows in set (0.00 sec)


mysql> select count(*) as total from users;
+-------+
| total |
+-------+
|     2 |
+-------+
1 row in set (0.00 sec)


mysql> select * from users where id in (1,2) and name like '%리자';
+----+-------+----------+-----------+
| id | uid   | password | name      |
+----+-------+----------+-----------+
|  1 | admin | 1234     | 관리자    |
+----+-------+----------+-----------+
1 row in set (0.00 sec)


# 리스트가 있는 유저만 불러오기
# subquery를 사용 할 때 필드명이 중첩되는 경우 필드명 앞에 테이블명을 표기해줍니다.

mysql> select * from users where exists (select * from lists where lists.user_id = users.id);
+----+-------+----------+-----------+
| id | uid   | password | name      |
+----+-------+----------+-----------+
|  1 | admin | 1234     | 관리자    |
+----+-------+----------+-----------+
1 row in set (0.00 sec)

집계 함수

데이터에 대한 통계적인 결과를 출력 할 때는 위의 count()와 같은 집 계함수를 이용합니다. 집계함수는 레코드들을 그룹으로 묶고서 통계적인 연산을 수행합니다.

함수설명
count(*)null인 레코드를 포함한 갯수를 셈
count([distinct ] expr)expr가 null인 레코드를 제외한 갯수를 셈, distinct로 중복값을 하나로 처리할 수 있음
sum([distinct ] expr)expr의 합계
max(expr), min(expr)expr의 최대값, 최소값

이외에도 다양한 함수가 있습니다.

mysql> select * from users;
+----+-------+----------+-----------+
| id | uid   | password | name      |
+----+-------+----------+-----------+
|  1 | admin | 1234     | 관리자    |
|  2 | test  | 1234     | 손님      |
+----+-------+----------+-----------+
2 rows in set (0.00 sec)

mysql> select count(*) from users;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

mysql> select sum(id) from users;
+---------+
| sum(id) |
+---------+
|       3 |
+---------+
1 row in set (0.00 sec)

# 이 때 집계함수와 일반 필드를 같이 select 하려면 오류가 납니다.
mysql> select sum(password), name from users;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'todo.users.name'; this is incompatible with sql_mode=only_full_group_by

group by, having

위의 마지막 쿼리에서 sum(password), name을 같이 조회하려 했습니다만 오류가 발생했습니다. 집계함수를 이용하면, 기본적으로 전체 테이블을 하나의 그룹으로 취급하기 때문에 어떤 name을 조회해야 할지 명확하지 않기 때문입니다. 집계함수를 적용하기에 앞서서 전체 테이블을 group by를 이용해 여러개의 그룹으로 분리 할 수 있습니다. group by는 항상 select 쿼리, 집계함수와 함께 쓰입니다.

mysql> select * from items;
+----+------------------------------+---------+---------------------+---------+---------+
| id | name                         | checked | date                | user_id | list_id |
+----+------------------------------+---------+---------------------+---------+---------+
|  5 | 안드로이드 공부하기          |       0 | 2017-11-07 22:28:36 |       1 |       1 |
|  6 | Rx 공부하기                  |       1 | 2017-11-07 22:28:36 |       1 |       1 |
|  7 | Angular 2.0 공부하기         |       1 | 2017-11-07 22:28:36 |       1 |       1 |
| 10 | aa                           |       1 | 2017-11-09 23:20:24 |       1 |       1 |
| 11 | asdsd                        |       1 | 2017-11-09 23:20:45 |       1 |       1 |
| 12 | asdasd                       |       1 | 2017-11-09 23:22:19 |       1 |       1 |
| 13 | asd                          |       1 | 2017-11-09 23:22:19 |       1 |       1 |
| 38 | asda                         |       0 | 2017-11-14 22:38:48 |       1 |       2 |
| 42 | asasdasdasd                  |       1 | 2017-11-14 22:40:49 |       1 |       2 |
| 43 | fs                           |       0 | 2017-11-14 22:40:51 |       1 |       2 |
+----+------------------------------+---------+---------------------+---------+---------+
10 rows in set (0.00 sec)


# 항목이 완료되었는지(checked)를 그룹으로 집계 
mysql> select count(*), checked from items group by checked;
+----------+---------+
| count(*) | checked |
+----------+---------+
|        3 |       0 |
|        7 |       1 |
+----------+---------+
2 rows in set (0.00 sec)


# where절은 group by 앞에 옵니다.
mysql> select count(*), checked, list_id from items where list_id=1 group by checked;
+----------+---------+---------+
| count(*) | checked | list_id |
+----------+---------+---------+
|        1 |       0 |       1 |
|        6 |       1 |       1 |
+----------+---------+---------+
2 rows in set (0.00 sec)


# group by를 여러번 적용 할 수도 있습니다.
mysql> select count(*), checked, list_id from items group by checked, list_id;
+----------+---------+---------+
| count(*) | checked | list_id |
+----------+---------+---------+
|        1 |       0 |       1 |
|        2 |       0 |       2 |
|        6 |       1 |       1 |
|        1 |       1 |       2 |
+----------+---------+---------+
4 rows in set (0.00 sec)


# group by에 표현식을 적용 할 수도 있습니다.
mysql> select count(*), checked, list_id > 1 from items group by checked, list_id > 1;
+----------+---------+-------------+
| count(*) | checked | list_id > 1 |
+----------+---------+-------------+
|        1 |       0 |           0 |
|        2 |       0 |           1 |
|        6 |       1 |           0 |
|        1 |       1 |           1 |
+----------+---------+-------------+
4 rows in set (0.01 sec)


# having절로 group by로 집계된 결과를 다시 필터링 할 수 있습니다.

mysql> select count(*) as cnt, checked from items group by checked;
+-----+---------+
| cnt | checked |
+-----+---------+
|   3 |       0 |
|   7 |       1 |
+-----+---------+
2 rows in set (0.00 sec)

mysql> select count(*) as cnt, checked from items group by checked having cnt > 5;
+-----+---------+
| cnt | checked |
+-----+---------+
|   7 |       1 |
+-----+---------+
1 row in set (0.00 sec)

order by, limit

조회시 조건을 통해 결과를 필터링하는 방법, 또 결과를 그룹으로 나누고 집계함수를 이용하는 방법을 알아봤습니다. 이제 order by절로 결과를 정렬하고, limit절로 출력 갯수를 제한하는 방법을 알아보겠습니다.

# desc(descending): 내림차순
mysql> select * from users order by uid desc;
+----+-------+----------+-----------+
| id | uid   | password | name      |
+----+-------+----------+-----------+
|  2 | test  | 1234     | 손님      |
|  1 | admin | 1234     | 관리자    |
+----+-------+----------+-----------+
2 rows in set (0.00 sec)

# asc(ascending): 오름차순
# order by를 순서대로 여러차례 적용할 수 있습니다.
mysql> select * from items order by checked asc, id desc;
+----+------------------------------+---------+---------------------+---------+---------+
| id | name                         | checked | date                | user_id | list_id |
+----+------------------------------+---------+---------------------+---------+---------+
| 43 | fs                           |       0 | 2017-11-14 22:40:51 |       1 |       2 |
| 38 | asda                         |       0 | 2017-11-14 22:38:48 |       1 |       2 |
|  5 | 안드로이드 공부하기          |       0 | 2017-11-07 22:28:36 |       1 |       1 |
| 42 | asasdasdasd                  |       1 | 2017-11-14 22:40:49 |       1 |       2 |
| 13 | asd                          |       1 | 2017-11-09 23:22:19 |       1 |       1 |
| 12 | asdasd                       |       1 | 2017-11-09 23:22:19 |       1 |       1 |
| 11 | asdsd                        |       1 | 2017-11-09 23:20:45 |       1 |       1 |
| 10 | aa                           |       1 | 2017-11-09 23:20:24 |       1 |       1 |
|  7 | Angular 2.0 공부하기         |       1 | 2017-11-07 22:28:36 |       1 |       1 |
|  6 | Rx 공부하기                  |       1 | 2017-11-07 22:28:36 |       1 |       1 |
+----+------------------------------+---------+---------------------+---------+---------+
10 rows in set (0.00 sec)


# order by절에도 표현식을 적용 할 수 있습니다.
mysql> select id,checked,id*checked from items order by id*checked asc;
+----+---------+------------+
| id | checked | id*checked |
+----+---------+------------+
|  5 |       0 |          0 |
| 38 |       0 |          0 |
| 43 |       0 |          0 |
|  6 |       1 |          6 |
|  7 |       1 |          7 |
| 10 |       1 |         10 |
| 11 |       1 |         11 |
| 12 |       1 |         12 |
| 13 |       1 |         13 |
| 42 |       1 |         42 |
+----+---------+------------+
10 rows in set (0.00 sec)

# 난수를 발생시키는 rand() 함수로 랜덤으로 정렬 할 수 있습니다.
mysql> select * from items order by rand();
+----+------------------------------+---------+---------------------+---------+---------+
| id | name                         | checked | date                | user_id | list_id |
+----+------------------------------+---------+---------------------+---------+---------+
| 38 | asda                         |       0 | 2017-11-14 22:38:48 |       1 |       2 |
|  6 | Rx 공부하기                  |       1 | 2017-11-07 22:28:36 |       1 |       1 |
| 13 | asd                          |       1 | 2017-11-09 23:22:19 |       1 |       1 |
| 42 | asasdasdasd                  |       1 | 2017-11-14 22:40:49 |       1 |       2 |
| 10 | aa                           |       1 | 2017-11-09 23:20:24 |       1 |       1 |
| 43 | fs                           |       0 | 2017-11-14 22:40:51 |       1 |       2 |
| 11 | asdsd                        |       1 | 2017-11-09 23:20:45 |       1 |       1 |
|  5 | 안드로이드 공부하기          |       0 | 2017-11-07 22:28:36 |       1 |       1 |
| 12 | asdasd                       |       1 | 2017-11-09 23:22:19 |       1 |       1 |
|  7 | Angular 2.0 공부하기         |       1 | 2017-11-07 22:28:36 |       1 |       1 |
+----+------------------------------+---------+---------------------+---------+---------+
10 rows in set (0.00 sec)


# limit절은 select 쿼리의 가장 마지막에서 결과의 갯수를 제한합니다.
mysql> select * from items where checked order by id desc limit 5;
+----+-------------+---------+---------------------+---------+---------+
| id | name        | checked | date                | user_id | list_id |
+----+-------------+---------+---------------------+---------+---------+
| 42 | asasdasdasd |       1 | 2017-11-14 22:40:49 |       1 |       2 |
| 13 | asd         |       1 | 2017-11-09 23:22:19 |       1 |       1 |
| 12 | asdasd      |       1 | 2017-11-09 23:22:19 |       1 |       1 |
| 11 | asdsd       |       1 | 2017-11-09 23:20:45 |       1 |       1 |
| 10 | aa          |       1 | 2017-11-09 23:20:24 |       1 |       1 |
+----+-------------+---------+---------------------+---------+---------+
5 rows in set (0.00 sec)

# limit [offset, ] number에서 offset은 출력을 스킵 할 레코드의 갯수를 나타냅니다.
mysql> select * from items where checked order by id desc limit 0, 5;
+----+-------------+---------+---------------------+---------+---------+
| id | name        | checked | date                | user_id | list_id |
+----+-------------+---------+---------------------+---------+---------+
| 42 | asasdasdasd |       1 | 2017-11-14 22:40:49 |       1 |       2 |
| 13 | asd         |       1 | 2017-11-09 23:22:19 |       1 |       1 |
| 12 | asdasd      |       1 | 2017-11-09 23:22:19 |       1 |       1 |
| 11 | asdsd       |       1 | 2017-11-09 23:20:45 |       1 |       1 |
| 10 | aa          |       1 | 2017-11-09 23:20:24 |       1 |       1 |
+----+-------------+---------+---------------------+---------+---------+
5 rows in set (0.00 sec)

mysql> select * from items where checked order by id desc limit 2, 5;
+----+--------------------------+---------+---------------------+---------+---------+
| id | name                     | checked | date                | user_id | list_id |
+----+--------------------------+---------+---------------------+---------+---------+
| 12 | asdasd                   |       1 | 2017-11-09 23:22:19 |       1 |       1 |
| 11 | asdsd                    |       1 | 2017-11-09 23:20:45 |       1 |       1 |
| 10 | aa                       |       1 | 2017-11-09 23:20:24 |       1 |       1 |
|  7 | Angular 2.0 공부하기     |       1 | 2017-11-07 22:28:36 |       1 |       1 |
|  6 | Rx 공부하기              |       1 | 2017-11-07 22:28:36 |       1 |       1 |
+----+--------------------------+---------+---------------------+---------+---------+
5 rows in set (0.00 sec)

order by와 limit절을 이용해서 게시판 등의 프로그램에서 게시물들을 정렬하고, 페이지 번호 등을 기반으로 데이터의 출력을 제한 할 수 있습니다.

JOIN 절

SQL 구문의 처리 순서

SQL 처리 순서SQL 처리 순서

  1. FROM, JOIN 목표 데이터셋을 정의
  2. WHERE 조건에 따라 필터링
  3. GROUP BY 결과를 그룹으로 나눔
  4. HAVING 결과를 다시 조건에 따라 필터링
  5. SELECT 결과에서 필드를 추출
  6. ORDER BY 결과를 정렬
  7. LIMIT 결과의 스킵, 출력 갯수를 제한

복잡한 쿼리의 구문들은 대략적으로 위의 순서로 실행됩니다. from과 함께 첫째로 처리되는 join 절에 대해서 알아보겠습니다.

join

앞서 배웠듯이 RDB에서는 하나의 모델을 하나의 테이블로 독립적으로 표현하면서, 모델간의 관계는 FK를 통해 설정합니다. 때문에 잘 분리된 모델에서는 item과 그 주인인 user를 한번의 쿼리로 출력할 수 없습니다.

mysql> select * from items where id=5;
+----+------------------------------+---------+---------------------+---------+---------+
| id | name                         | checked | date                | user_id | list_id |
+----+------------------------------+---------+---------------------+---------+---------+
|  5 | 안드로이드 공부하기          |       0 | 2017-11-07 22:28:36 |       1 |       1 |
+----+------------------------------+---------+---------------------+---------+---------+
1 row in set (0.00 sec)

mysql> select * from users where id=1;
+----+-------+----------+-----------+
| id | uid   | password | name      |
+----+-------+----------+-----------+
|  1 | admin | 1234     | 관리자    |
+----+-------+----------+-----------+
1 row in set (0.00 sec)

join절은 위처럼 분리된 테이블을 조합하여 데이터를 조회하는 데 사용됩니다.

# ... from 테이블 join 테이블2 on 조건식으로 테이블을 조합합니다.
mysql> select * from users join items on users.id=items.user_id limit 5;
+----+-------+----------+-----------+----+------------------------------+---------+---------------------+---------+---------+
| id | uid   | password | name      | id | name                         | checked | date                | user_id | list_id |
+----+-------+----------+-----------+----+------------------------------+---------+---------------------+---------+---------+
|  1 | admin | 1234     | 관리자    |  5 | 안드로이드 공부하기          |       0 | 2017-11-07 22:28:36 |       1 |       1 |
|  1 | admin | 1234     | 관리자    |  6 | Rx 공부하기                  |       1 | 2017-11-07 22:28:36 |       1 |       1 |
|  1 | admin | 1234     | 관리자    |  7 | Angular 2.0 공부하기         |       1 | 2017-11-07 22:28:36 |       1 |       1 |
|  1 | admin | 1234     | 관리자    | 10 | aa                           |       1 | 2017-11-09 23:20:24 |       1 |       1 |
|  1 | admin | 1234     | 관리자    | 11 | asdsd                        |       1 | 2017-11-09 23:20:45 |       1 |       1 |
+----+-------+----------+-----------+----+------------------------------+---------+---------------------+---------+---------+
5 rows in set (0.00 sec)

# 꼭 FK 관계가 아니여도 join을 사용 할 수 있습니다.
# 이 예시는 유의미한 결과는 아닙니다.
mysql> select * from users join items on length(users.uid) < length(items.name) limit 5;
+----+-------+----------+-----------+----+------------------------------+---------+---------------------+---------+---------+
| id | uid   | password | name      | id | name                         | checked | date                | user_id | list_id |
+----+-------+----------+-----------+----+------------------------------+---------+---------------------+---------+---------+
|  1 | admin | 1234     | 관리자    |  5 | 안드로이드 공부하기          |       0 | 2017-11-07 22:28:36 |       1 |       1 |
|  2 | test  | 1234     | 손님      |  5 | 안드로이드 공부하기          |       0 | 2017-11-07 22:28:36 |       1 |       1 |
|  1 | admin | 1234     | 관리자    |  6 | Rx 공부하기                  |       1 | 2017-11-07 22:28:36 |       1 |       1 |
|  2 | test  | 1234     | 손님      |  6 | Rx 공부하기                  |       1 | 2017-11-07 22:28:36 |       1 |       1 |
|  1 | admin | 1234     | 관리자    |  7 | Angular 2.0 공부하기         |       1 | 2017-11-07 22:28:36 |       1 |       1 |
+----+-------+----------+-----------+----+------------------------------+---------+---------------------+---------+---------+
5 rows in set (0.01 sec)

# join을 여러 테이블에 걸쳐 적용 할 수 있습니다.
# 중첩되는 필드명이 있을 땐 as를 이용해서 구분하여 출력합니다.
mysql> select users.name user_name, lists.name as list_name, items.name as item_name, checked  from users join lists on users.id=lists.user_id join items on lists.id=items.list_id limit 5;
+-----------+--------------+------------------------------+---------+
| user_name | list_name    | item_name                    | checked |
+-----------+--------------+------------------------------+---------+
| 관리자    | 공부하기     | 안드로이드 공부하기          |       0 |
| 관리자    | 공부하기     | Rx 공부하기                  |       1 |
| 관리자    | 공부하기     | Angular 2.0 공부하기         |       1 |
| 관리자    | 공부하기     | aa                           |       1 |
| 관리자    | 공부하기     | asdsd                        |       1 |
+-----------+--------------+------------------------------+---------+
5 rows in set (0.00 sec)

join은 NoSQL과 구분되는 RDB의 핵심적인 기능이라고 할 수 있습니다. 위처럼 독립적인 테이블들을 조합하여 응용프로그램에서 원하는 데이터를 추출하는 쿼리를 작성할 수 있는 능력이 필요합니다.

위처럼 join절은 select, where 등의 연산을 처리하기에 앞서서 from과 함께 목표 데이터셋을 정의하는 역할을 합니다. 이때 각각의 테이블을 하나의 집합으로 보면 아래 그림처럼 다양한 join이 가능합니다.

JOIN의 종류JOIN의 종류

cross join

from A join B처럼 on 구문이 제외된 조인을 크로스 조인이라고 합니다. cartesian product라고도 불리는 cross join은 단순히 테이블 A와 테이블 B의 모든 레코드들에 대해서 가능한 순서쌍을 생성합니다. 일반적인 비지니스 로직에선 쓰이지 않지만, 모든 조인의 기본이된다고 볼 수 있습니다.

inner join

from A [inner] join B on X는 조건 X를 만족하는 데이터쌍만을 조합하는 inner 조인을 실행합니다.

# items 테이블의 list_id를 nullable하게 수정합니다.
# 그리고 몇개의 더미 데이터의 list_id를 null로 수정했습니다.

mysql> alter table items modify column list_id int null;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> update items set list_id = null where id > 10;
ERROR 1048 (23000): Column 'list_id' cannot be null
mysql> alter table items modify column list_id int null;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from items;
+----+------------------------------+---------+---------------------+---------+---------+
| id | name                         | checked | date                | user_id | list_id |
+----+------------------------------+---------+---------------------+---------+---------+
|  5 | 안드로이드 공부하기          |       0 | 2017-11-07 22:28:36 |       1 |       1 |
|  6 | Rx 공부하기                  |       1 | 2017-11-07 22:28:36 |       1 |       1 |
|  7 | Angular 2.0 공부하기         |       1 | 2017-11-07 22:28:36 |       1 |       1 |
| 10 | aa                           |       1 | 2017-11-09 23:20:24 |       1 |       1 |
| 11 | asdsd                        |       1 | 2017-11-09 23:20:45 |       1 |    NULL |
| 12 | asdasd                       |       1 | 2017-11-09 23:22:19 |       1 |    NULL |
| 13 | asd                          |       1 | 2017-11-09 23:22:19 |       1 |    NULL |
| 38 | asda                         |       0 | 2017-11-14 22:38:48 |       1 |    NULL |
| 42 | asasdasdasd                  |       1 | 2017-11-14 22:40:49 |       1 |    NULL |
| 43 | fs                           |       0 | 2017-11-14 22:40:51 |       1 |    NULL |
+----+------------------------------+---------+---------------------+---------+---------+
10 rows in set (0.00 sec)


# 이제 join을 실행한 결과 items.list_id가 null인 데이터쌍은 추출되지 않았습니다.
mysql> select * from items join lists on items.list_id=lists.id;
+----+------------------------------+---------+---------------------+---------+---------+----+--------------+-------+---------+
| id | name                         | checked | date                | user_id | list_id | id | name         | icon  | user_id |
+----+------------------------------+---------+---------------------+---------+---------+----+--------------+-------+---------+
|  5 | 안드로이드 공부하기          |       0 | 2017-11-07 22:28:36 |       1 |       1 |  1 | 공부하기     | study |       1 |
|  6 | Rx 공부하기                  |       1 | 2017-11-07 22:28:36 |       1 |       1 |  1 | 공부하기     | study |       1 |
|  7 | Angular 2.0 공부하기         |       1 | 2017-11-07 22:28:36 |       1 |       1 |  1 | 공부하기     | study |       1 |
| 10 | aa                           |       1 | 2017-11-09 23:20:24 |       1 |       1 |  1 | 공부하기     | study |       1 |
+----+------------------------------+---------+---------------------+---------+---------+----+--------------+-------+---------+
4 rows in set (0.00 sec)

left join

다음으로 from A left join B on X는 A의 데이터 전체를 추출하면서 X를 만족시키는 B를 조합합니다.

# X를 만족시키는 쌍이 없는 A의 레코드는 B의 필드를 모두 null로 갖는 결과를 냅니다.
mysql> select * from items left join lists on items.list_id=lists.id;
+----+------------------------------+---------+---------------------+---------+---------+------+--------------+-------+---------+
| id | name                         | checked | date                | user_id | list_id | id   | name         | icon  | user_id |
+----+------------------------------+---------+---------------------+---------+---------+------+--------------+-------+---------+
|  5 | 안드로이드 공부하기          |       0 | 2017-11-07 22:28:36 |       1 |       1 |    1 | 공부하기     | study |       1 |
|  6 | Rx 공부하기                  |       1 | 2017-11-07 22:28:36 |       1 |       1 |    1 | 공부하기     | study |       1 |
|  7 | Angular 2.0 공부하기         |       1 | 2017-11-07 22:28:36 |       1 |       1 |    1 | 공부하기     | study |       1 |
| 10 | aa                           |       1 | 2017-11-09 23:20:24 |       1 |       1 |    1 | 공부하기     | study |       1 |
| 11 | asdsd                        |       1 | 2017-11-09 23:20:45 |       1 |    NULL | NULL | NULL         | NULL  |    NULL |
| 12 | asdasd                       |       1 | 2017-11-09 23:22:19 |       1 |    NULL | NULL | NULL         | NULL  |    NULL |
| 13 | asd                          |       1 | 2017-11-09 23:22:19 |       1 |    NULL | NULL | NULL         | NULL  |    NULL |
| 38 | asda                         |       0 | 2017-11-14 22:38:48 |       1 |    NULL | NULL | NULL         | NULL  |    NULL |
| 42 | asasdasdasd                  |       1 | 2017-11-14 22:40:49 |       1 |    NULL | NULL | NULL         | NULL  |    NULL |
| 43 | fs                           |       0 | 2017-11-14 22:40:51 |       1 |    NULL | NULL | NULL         | NULL  |    NULL |
+----+------------------------------+---------+---------------------+---------+---------+------+--------------+-------+---------+
10 rows in set (0.00 sec)
==inner joinleft join으로 일반적인 비지니스 로직을 모두 수행== 할 수 있습니다. right joinleft join을 뒤집은 구문에 불과하며, full joinleft joinright join의 합에 지나지 않습니다.

## right join, full join
```sql
mysql> select * from items left join lists on items.list_id=lists.id;
+----+------------------------------+---------+---------------------+---------+---------+------+--------------+-------+---------+
| id | name                         | checked | date                | user_id | list_id | id   | name         | icon  | user_id |
+----+------------------------------+---------+---------------------+---------+---------+------+--------------+-------+---------+
|  5 | 안드로이드 공부하기          |       0 | 2017-11-07 22:28:36 |       1 |       1 |    1 | 공부하기     | study |       1 |
|  6 | Rx 공부하기                  |       1 | 2017-11-07 22:28:36 |       1 |       1 |    1 | 공부하기     | study |       1 |
|  7 | Angular 2.0 공부하기         |       1 | 2017-11-07 22:28:36 |       1 |       1 |    1 | 공부하기     | study |       1 |
| 10 | aa                           |       1 | 2017-11-09 23:20:24 |       1 |       1 |    1 | 공부하기     | study |       1 |
| 11 | asdsd                        |       1 | 2017-11-09 23:20:45 |       1 |    NULL | NULL | NULL         | NULL  |    NULL |
| 12 | asdasd                       |       1 | 2017-11-09 23:22:19 |       1 |    NULL | NULL | NULL         | NULL  |    NULL |
| 13 | asd                          |       1 | 2017-11-09 23:22:19 |       1 |    NULL | NULL | NULL         | NULL  |    NULL |
| 38 | asda                         |       0 | 2017-11-14 22:38:48 |       1 |    NULL | NULL | NULL         | NULL  |    NULL |
| 42 | asasdasdasd                  |       1 | 2017-11-14 22:40:49 |       1 |    NULL | NULL | NULL         | NULL  |    NULL |
| 43 | fs                           |       0 | 2017-11-14 22:40:51 |       1 |    NULL | NULL | NULL         | NULL  |    NULL |
+----+------------------------------+---------+---------------------+---------+---------+------+--------------+-------+---------+
10 rows in set (0.00 sec)

mysql> select * from items right join lists on items.list_id=lists.id;
+------+------------------------------+---------+---------------------+---------+---------+----+--------------+-------+---------+
| id   | name                         | checked | date                | user_id | list_id | id | name         | icon  | user_id |
+------+------------------------------+---------+---------------------+---------+---------+----+--------------+-------+---------+
|    5 | 안드로이드 공부하기          |       0 | 2017-11-07 22:28:36 |       1 |       1 |  1 | 공부하기     | study |       1 |
|    6 | Rx 공부하기                  |       1 | 2017-11-07 22:28:36 |       1 |       1 |  1 | 공부하기     | study |       1 |
|    7 | Angular 2.0 공부하기         |       1 | 2017-11-07 22:28:36 |       1 |       1 |  1 | 공부하기     | study |       1 |
|   10 | aa                           |       1 | 2017-11-09 23:20:24 |       1 |       1 |  1 | 공부하기     | study |       1 |
| NULL | NULL                         |    NULL | NULL                |    NULL |    NULL |  2 | 일하기       | work  |       1 |
| NULL | NULL                         |    NULL | NULL                |    NULL |    NULL |  3 | 빈리스트     | other |       1 |
| NULL | NULL                         |    NULL | NULL                |    NULL |    NULL |  4 | aa           | other |       1 |
+------+------------------------------+---------+---------------------+---------+---------+----+--------------+-------+---------+
7 rows in set (0.00 sec)

## MySQL은 full join을 지원하지 않습니다.
mysql> select * from items full join lists on items.list_id=lists.id;
ERROR 1054 (42S22): Unknown column 'items.list_id' in 'on clause'

union [distinct], union all

마지막으로 MySQL에서 지원하지 않는 full join을 union 연산을 통해 구현해보겠습니다. union 연산은 여러 select 쿼리의 결과를 하나의 결과로 합치는 데 이용됩니다. 이 때 union 할 레코드들은 모두 동일한 필드를 가져야 합니다.

mysql> (select * from items right join lists on items.list_id=lists.id) union (select * from items left join lists on items.list_id=lists.id);
+------+------------------------------+---------+---------------------+---------+---------+------+--------------+-------+---------+
| id   | name                         | checked | date                | user_id | list_id | id   | name         | icon  | user_id |
+------+------------------------------+---------+---------------------+---------+---------+------+--------------+-------+---------+
|    5 | 안드로이드 공부하기          |       0 | 2017-11-07 22:28:36 |       1 |       1 |    1 | 공부하기     | study |       1 |
|    6 | Rx 공부하기                  |       1 | 2017-11-07 22:28:36 |       1 |       1 |    1 | 공부하기     | study |       1 |
|    7 | Angular 2.0 공부하기         |       1 | 2017-11-07 22:28:36 |       1 |       1 |    1 | 공부하기     | study |       1 |
|   10 | aa                           |       1 | 2017-11-09 23:20:24 |       1 |       1 |    1 | 공부하기     | study |       1 |
| NULL | NULL                         |    NULL | NULL                |    NULL |    NULL |    2 | 일하기       | work  |       1 |
| NULL | NULL                         |    NULL | NULL                |    NULL |    NULL |    3 | 빈리스트     | other |       1 |
| NULL | NULL                         |    NULL | NULL                |    NULL |    NULL |    4 | aa           | other |       1 |
|   11 | asdsd                        |       1 | 2017-11-09 23:20:45 |       1 |    NULL | NULL | NULL         | NULL  |    NULL |
|   12 | asdasd                       |       1 | 2017-11-09 23:22:19 |       1 |    NULL | NULL | NULL         | NULL  |    NULL |
|   13 | asd                          |       1 | 2017-11-09 23:22:19 |       1 |    NULL | NULL | NULL         | NULL  |    NULL |
|   38 | asda                         |       0 | 2017-11-14 22:38:48 |       1 |    NULL | NULL | NULL         | NULL  |    NULL |
|   42 | asasdasdasd                  |       1 | 2017-11-14 22:40:49 |       1 |    NULL | NULL | NULL         | NULL  |    NULL |
|   43 | fs                           |       0 | 2017-11-14 22:40:51 |       1 |    NULL | NULL | NULL         | NULL  |    NULL |
+------+------------------------------+---------+---------------------+---------+---------+------+--------------+-------+---------+
13 rows in set (0.00 sec)


# union 연산은 union dinstinct 연산으로 처리되며, 이는 결과 중에서 중복되는 레코드를 제거해줍니다.
# 중복되는 레코드를 제거할 필요가 없거나, 비용을 줄이려면 union all 연산을 이용합니다.

mysql> (select * from items right join lists on items.list_id=lists.id) union all (select * from items left join lists on items.list_id=lists.id);
+------+------------------------------+---------+---------------------+---------+---------+------+--------------+-------+---------+
| id   | name                         | checked | date                | user_id | list_id | id   | name         | icon  | user_id |
+------+------------------------------+---------+---------------------+---------+---------+------+--------------+-------+---------+
|    5 | 안드로이드 공부하기          |       0 | 2017-11-07 22:28:36 |       1 |       1 |    1 | 공부하기     | study |       1 |
|    6 | Rx 공부하기                  |       1 | 2017-11-07 22:28:36 |       1 |       1 |    1 | 공부하기     | study |       1 |
|    7 | Angular 2.0 공부하기         |       1 | 2017-11-07 22:28:36 |       1 |       1 |    1 | 공부하기     | study |       1 |
|   10 | aa                           |       1 | 2017-11-09 23:20:24 |       1 |       1 |    1 | 공부하기     | study |       1 |
| NULL | NULL                         |    NULL | NULL                |    NULL |    NULL |    2 | 일하기       | work  |       1 |
| NULL | NULL                         |    NULL | NULL                |    NULL |    NULL |    3 | 빈리스트     | other |       1 |
| NULL | NULL                         |    NULL | NULL                |    NULL |    NULL |    4 | aa           | other |       1 |
|    5 | 안드로이드 공부하기          |       0 | 2017-11-07 22:28:36 |       1 |       1 |    1 | 공부하기     | study |       1 |
|    6 | Rx 공부하기                  |       1 | 2017-11-07 22:28:36 |       1 |       1 |    1 | 공부하기     | study |       1 |
|    7 | Angular 2.0 공부하기         |       1 | 2017-11-07 22:28:36 |       1 |       1 |    1 | 공부하기     | study |       1 |
|   10 | aa                           |       1 | 2017-11-09 23:20:24 |       1 |       1 |    1 | 공부하기     | study |       1 |
|   11 | asdsd                        |       1 | 2017-11-09 23:20:45 |       1 |    NULL | NULL | NULL         | NULL  |    NULL |
|   12 | asdasd                       |       1 | 2017-11-09 23:22:19 |       1 |    NULL | NULL | NULL         | NULL  |    NULL |
|   13 | asd                          |       1 | 2017-11-09 23:22:19 |       1 |    NULL | NULL | NULL         | NULL  |    NULL |
|   38 | asda                         |       0 | 2017-11-14 22:38:48 |       1 |    NULL | NULL | NULL         | NULL  |    NULL |
|   42 | asasdasdasd                  |       1 | 2017-11-14 22:40:49 |       1 |    NULL | NULL | NULL         | NULL  |    NULL |
|   43 | fs                           |       0 | 2017-11-14 22:40:51 |       1 |    NULL | NULL | NULL         | NULL  |    NULL |
+------+------------------------------+---------+---------------------+---------+---------+------+--------------+-------+---------+
17 rows in set (0.00 sec)

MySQL 함수 목록 , W3School SQL 튜토리얼

인덱스, 뷰, 프로시저, 트리거

DBMS에서 제공하는 추가적인 기능에 대해서 알아봅니다. 인덱스에 대한 개념을 정리하고 뷰, 프로시저, 트리거에 대해서 간단히 소개하겠습니다.

인덱스

인덱스 (색인)인덱스 (색인)

특정 레코드를 조작하는 query를 실행하면 테이블의 첫째 열에서부터 전체 테이블을 순차적으로 검색하기 때문에 테이블이 클 수록 검색 비용이 높아집니다. 이 때 백과사전의 색인처럼 검색 속도를 높히기 위해서, 테이블의 특정 컬럼을 추출해 검색에 효율적인 방식으로 구성한 보조 테이블을 인덱스(Index)라고 합니다.

인덱스 타입설명
primary keyPK로 설정된 컬럼은 자동으로 primary key 인덱스를 생성
uniqueunique로 설정된 컬럼은 자동으로 unqiue 인덱스를 생성
index일반적인 필드에 대한 인덱스, FK로 설정되면 자동으로 index 인덱스를 생성
fulltextTEXT, VARCHAR, CHAR 등 대용량 텍스트에 특화된 인덱스

자주 사용되는 쿼리의 비용을 절감할 수 있도록 적절한 인덱스를 생성하면, 대규모의 데이터를 관리할 때 최적화를 꾀 할 수 있습니다. 참고로 인덱스를 생성하면 select 쿼리의 비용을 줄어들지만, insert, update 쿼리의 비용은 색인을 업데이트하는 과정 때문에 늘어날 수 있습니다.

mysql> create table index_test(
    -> id int primary key auto_increment,
    -> some_value int(128),
    -> some_value2 text,
    -> index(some_value),
    -> fulltext(some_value2)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> show index from index_test;
+------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| index_test |          0 | PRIMARY     |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| index_test |          1 | some_value  |            1 | some_value  | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| index_test |          1 | some_value2 |            1 | some_value2 | NULL      |           0 |     NULL | NULL   | YES  | FULLTEXT   |         |               |
+------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

뷰(View)는 가상의 테이블입니다. join 등으로 복잡해진 쿼리를 단순화 시키고, 응용프로그램에서 이용 할 데이터셋을 추상화하며, 실제 테이블은 은닉하여 보안을 꾀할 수 있는 장점이 있습니다.

mysql> select users.id user_id, users.name user_name, lists.id list_id, lists.name list_name, items.id item_id, items.name item_name  from users join lists on users.id=lists.user_id join items on lists.id=items.list_id;
+---------+-----------+---------+--------------+---------+------------------------------+
| user_id | user_name | list_id | list_name    | item_id | item_name                    |
+---------+-----------+---------+--------------+---------+------------------------------+
|       1 | 관리자    |       1 | 공부하기     |       5 | 안드로이드 공부하기          |
|       1 | 관리자    |       1 | 공부하기     |       6 | Rx 공부하기                  |
|       1 | 관리자    |       1 | 공부하기     |       7 | Angular 2.0 공부하기         |
|       1 | 관리자    |       1 | 공부하기     |      10 | aa                           |
+---------+-----------+---------+--------------+---------+------------------------------+
4 rows in set (0.00 sec)

mysql> create view user_list_items as select users.id user_id, users.name user_name, lists.id list_id, lists.name list_name, items.id item_id, items.name item_name  from users join lists on users.id=lists.user_id join items on lists.id=items.list_id;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from user_list_items where item_id > 5;;
+---------+-----------+---------+--------------+---------+--------------------------+
| user_id | user_name | list_id | list_name    | item_id | item_name                |
+---------+-----------+---------+--------------+---------+--------------------------+
|       1 | 관리자    |       1 | 공부하기     |       6 | Rx 공부하기              |
|       1 | 관리자    |       1 | 공부하기     |       7 | Angular 2.0 공부하기     |
|       1 | 관리자    |       1 | 공부하기     |      10 | aa                       |
+---------+-----------+---------+--------------+---------+--------------------------+
3 rows in set (0.01 sec)

DBMS의 종류에 따라서 view의 인덱스를 생성하거나, insert, update, delete에 여러 제약사항이 따릅니다. 기본적으로 view는 select 쿼리의 편의를 위한 가상 테이블로 이해 할 수 있겠습니다.

프로시저

프로시저(Procedure)는 DB에 정의하는 일종의 함수입니다. 프로시저는 일반적인 프로그래밍 언어의 함수처럼 인자를 받을 수 있으며, 실행시 일련의 query를 실행합니다.

트리거

트리거(Trigger)는 DB에 정의하는 일종의 이벤트 핸들러(함수)입니다. 트리거는 insert, update, delete 쿼리의 실행 전이나 후에 일련의 query를 실행합니다.

이 강의를 포함한 커리큘럼
저자

김동욱

개발 경력 약 10년, 전문 분야는 웹 및 각종 응용 소프트웨어 플랫폼입니다. Codeflow를 운영하고 있습니다.

2018년 04월 10일 업데이트

지원되지 않는 웹 브라우저거나 예기치 않은 오류가 발생했습니다.