# Table of Contents
# DML
DML(Data Manipulation Lauguage)은 데이터를 조작하는 연산이다.
- INSERT
- UPDATE
- DELETE
- SELECT
# INSERT
다음과 같이 Row를 삽입할 수 있다.
INSERT INTO member(name, age, nation, gender)
VALUES('paul', 35, 'england', 'man');
여러 Row를 한꺼번에 삽입할 수 있다.
INSERT INTO member(name, age, nation, gender) VALUES
('monica', 23, 'usa', 'woman'),
('ross', 28, 'australia', 'man'),
('chandler', 29, 'england', 'woman'),
('chuck', 15, 'usa', 'man'),
('john', 35, 'australia', 'man');
SELECT의 결과값을 삽입할 수도 있다.
INSERT INTO employee (name, team, nation)
SELECT name, team, nation
FROM person
WHERE job IS NOT NULL;
# UPDATE
다음과 같이 Column을 업데이트할 수 있다.
UPDATE member SET name = 'paul' WHERE id = 1;
여러 Column을 동시에 업데이트할 수 있다.
UPDATE member SET name = 'paul', age = 33 WHERE id = 1;
# DELETE
다음과 같이 Row를 삭제할 수 있다.
DELETE FROM member WHERE age = 29;
# SELECT
다음과 같이 Row를 조회할 수 있다.
SELECT * FROM member;
원하는 Column만 조회할 수 있다.
SELECT name, age FROM member;
# DISTINCT
중복된 결과를 제거할 수 있다.
SELECT DISTINCT gender FROM member;
SELECT COUNT(DISTINCT gender) FROM member;
# WHERE
SELECT * FROM member WHERE id = 5;
SELECT * FROM member WHERE id != 5;
SELECT * FROM member age >= 20;
# AND
SELECT * FROM member WHERE age >= 25 AND age < 30;
# OR
SELECT * FROM member WHERE age <= 25 OR age > 30;
# IN
SELECT * FROM member WHERE id IN (6, 7);
# NOT IN
SELECT * FROM member WHERE id NOT IN (6, 7);
# IS
SELECT * FROM member WHERE job IS NULL;
# IS NOT
SELECT * FROM member WHERE job IS NOT NULL;
# ALL
SELECT * FROM member WHERE id > ALL(6, 7);
// SELECT * FROM member WHERE id > 6 AND id > 7;
# ANY
SELECT * FROM member WHERE id < ANY(6, 7);
// SELECT * FROM member WHERE id < 6 OR id < 7;
SELECT * FROM member WHERE id = ANY(6, 7);
// SELECT * FROM member WHERE id = 6 OR id = 7;
# LIKE
SELECT * FROM member;
+----+----------+------+-----------+--------+
| id | name | age | nation | gender |
+----+----------+------+-----------+--------+
| 1 | paul | 35 | england | man |
| 2 | monica | 23 | usa | woman |
| 3 | ross | 28 | australia | man |
| 4 | chandler | 29 | england | woman |
| 5 | chuck | 15 | usa | man |
| 6 | john | 35 | australia | man |
| 7 | silvia | 23 | usa | NULL |
| 8 | boss | 28 | australia | NULL |
| 9 | corona | 29 | usa | NULL |
| 10 | julia | 15 | england | NULL |
| 11 | joy | 30 | england | NULL |
| 12 | kane | 38 | england | NULL |
+----+----------+------+-----------+--------+
C로 시작하는 데이터 조회
SELECT * FROM member WHERE name LIKE 'C%';
+----+----------+------+---------+--------+
| id | name | age | nation | gender |
+----+----------+------+---------+--------+
| 4 | chandler | 29 | england | woman |
| 5 | chuck | 15 | usa | man |
| 9 | corona | 29 | usa | NULL |
+----+----------+------+---------+--------+
ia로 끝나는 데이터 조회
SELECT * FROM member WHERE name LIKE '%ia';
+----+--------+------+---------+--------+
| id | name | age | nation | gender |
+----+--------+------+---------+--------+
| 7 | silvia | 23 | usa | NULL |
| 10 | julia | 15 | england | NULL |
+----+--------+------+---------+--------+
on을 포함하는 데이터 조회
SELECT * FROM member WHERE name LIKE '%on%';
+----+--------+------+--------+--------+
| id | name | age | nation | gender |
+----+--------+------+--------+--------+
| 2 | monica | 23 | usa | woman |
| 9 | corona | 29 | usa | NULL |
+----+--------+------+--------+--------+
문자열 길이가 4, 두 번째 문자가 o인 데이터 조회
SELECT * FROM member WHERE name LIKE '_o__';
+----+------+------+-----------+--------+
| id | name | age | nation | gender |
+----+------+------+-----------+--------+
| 3 | ross | 28 | australia | man |
| 6 | john | 35 | australia | man |
| 8 | boss | 28 | australia | NULL |
+----+------+------+-----------+--------+
# EXISTS
게시글이 있는 사용자의 이메일 조회
SELECT email
FROM member
WHERE EXISTS (SELECT id FROM post);
# AS
SELECT m.name as member_name FROM member m WHERE age <= 25 OR age > 30;
# Grouping
SELECT * FROM member;
+----+----------+------+-----------+--------+
| id | name | age | nation | gender |
+----+----------+------+-----------+--------+
| 1 | paul | 35 | england | man |
| 2 | monica | 23 | usa | woman |
| 3 | ross | 28 | australia | man |
| 4 | chandler | 29 | england | woman |
| 5 | chuck | 15 | usa | man |
| 6 | john | 35 | australia | man |
| 7 | silvia | 23 | usa | NULL |
| 8 | boss | 28 | australia | NULL |
| 9 | corona | 29 | usa | NULL |
| 10 | julia | 15 | england | NULL |
| 11 | joy | 30 | england | NULL |
| 12 | kane | 38 | england | NULL |
+----+----------+------+-----------+--------+
GROUP BY로 그룹핑하는 컬럼은 프로젝션 대상에 반드시 포함되어야한다.
SELECT nation
FROM member
GROUP BY nation;
+-----------+
| nation |
+-----------+
| england |
| usa |
| australia |
+-----------+
GROUP BY에 포함되지 않은 컬럼을 프로젝션하려면 Grouping Function을 사용해야한다.
SELECT nation, COUNT(name)
FROM member
GROUP BY nation;
+-----------+-------------+
| nation | COUNT(name) |
+-----------+-------------+
| england | 5 |
| usa | 4 |
| australia | 3 |
+-----------+-------------+
HAVING절로 GROUPING에 조건을 추가할 수 있다.
SELECT nation, COUNT(name)
FROM member
GROUP BY nation
HAVING COUNT(name) > 3;
+---------+-------------+
| nation | COUNT(name) |
+---------+-------------+
| england | 5 |
| usa | 4 |
+---------+-------------+
WHERE 절은 GROUPING하기 전, HAVING 절은 GROUPING한 후 필터링한다.
SELECT nation, COUNT(name)
FROM member
WHERE age >= 20
GROUP BY nation
HAVING COUNT(name) > 3;
+---------+-------------+
| nation | COUNT(name) |
+---------+-------------+
| england | 4 |
+---------+-------------+
WITH ROLLUP으로 부분 합계도 계산할 수 있다.
SELECT nation, job, count(name)
FROM person
GROUP BY nation, job WITH ROLLUP;
+-----------+-------------------+-------------+
| nation | job | count(name) |
+-----------+-------------------+-------------+
| argentina | backend engineer | 1 |
| argentina | devops engineer | 1 |
| argentina | frontend engineer | 1 |
| argentina | NULL | 3 |
| brazil | backend engineer | 1 |
| brazil | designer | 3 |
| brazil | frontend engineer | 1 |
| brazil | NULL | 5 |
| uk | backend engineer | 1 |
| uk | frontend engineer | 1 |
| uk | NULL | 2 |
| usa | backend engineer | 2 |
| usa | frontend engineer | 2 |
| usa | NULL | 4 |
| NULL | NULL | 14 |
+-----------+-------------------+-------------+
COALESCE() 함수로 원하는 텍스트를 넣을 수도 있다.
SELECT COALESCE(nation, "총 합계"), COALESCE(job, "합계"), count(name)
FROM person
GROUP BY nation, job WITH ROLLUP;
+-------------------------------+----------------------+-------------+
| COALESCE(nation, "Total") | COALESCE(job, "Sum") | count(name) |
+-------------------------------+----------------------+-------------+
| argentina | backend engineer | 1 |
| argentina | devops engineer | 1 |
| argentina | frontend engineer | 1 |
| argentina | Sum | 3 |
| brazil | backend engineer | 1 |
| brazil | designer | 3 |
| brazil | frontend engineer | 1 |
| brazil | Sum | 5 |
| uk | backend engineer | 1 |
| uk | frontend engineer | 1 |
| uk | Sum | 2 |
| usa | backend engineer | 2 |
| usa | frontend engineer | 2 |
| usa | Sum | 4 |
| Total | Sum | 14 |
+-------------------------------+----------------------+-------------+
# ORDER BY
SELECT *
FROM member
ORDER BY age ASC, nation DESC;
+----+----------+------+-----------+--------+
| id | name | age | nation | gender |
+----+----------+------+-----------+--------+
| 5 | chuck | 15 | usa | man |
| 10 | julia | 15 | england | NULL |
| 2 | monica | 23 | usa | woman |
| 7 | silvia | 23 | usa | NULL |
| 3 | ross | 28 | australia | man |
| 8 | boss | 28 | australia | NULL |
| 9 | corona | 29 | usa | NULL |
| 4 | chandler | 29 | england | woman |
| 11 | joy | 30 | england | NULL |
| 1 | paul | 35 | england | man |
| 6 | john | 35 | australia | man |
| 12 | kane | 38 | england | NULL |
+----+----------+------+-----------+--------+
# SubQuery
SELECT name
FROM (
SELECT name, age, gender
FROM member
);
SELECT * FROM post WHERE writer_id IN (
SELECT id FROM member WHERE age > 30
);
SELECT * FROM post WHERE writer_id NOT IN (
SELECT id FROM member WHERE age > 30
);
SELECT email
FROM member
WHERE EXISTS (SELECT id FROM post);
SELECT *
FROM member
WHERE age > ALL(
SELECT age
FROM member
WHERE nation = 'usa'
);
+----+------+------+-----------+--------+
| id | name | age | nation | gender |
+----+------+------+-----------+--------+
| 1 | paul | 35 | england | man |
| 6 | john | 35 | australia | man |
| 11 | joy | 30 | england | NULL |
| 12 | kane | 38 | england | NULL |
+----+------+------+-----------+--------+
SELECT *
FROM member
WHERE age > ANY(
SELECT age
FROM member
WHERE nation = 'usa'
);
+----+----------+------+-----------+--------+
| id | name | age | nation | gender |
+----+----------+------+-----------+--------+
| 1 | paul | 35 | england | man |
| 2 | monica | 23 | usa | woman |
| 3 | ross | 28 | australia | man |
| 4 | chandler | 29 | england | woman |
| 6 | john | 35 | australia | man |
| 7 | silvia | 23 | usa | NULL |
| 8 | boss | 28 | australia | NULL |
| 9 | corona | 29 | usa | NULL |
| 11 | joy | 30 | england | NULL |
| 12 | kane | 38 | england | NULL |
+----+----------+------+-----------+--------+
# WITH
WITH 구문을 사용하면 쿼리의 결과값을 임시로 저장할 수 있다.
WITH
programmer AS (SELECT * FROM person WHERE job = 'programmer')
SELECT email, name, job FROM programmer;
다음과 같이 여러 테이블을 대상으로 임시 값을 저장할 수 있다.
WITH
frontend_engineer AS (SELECT * FROM person WHERE job = 'frontend engineer'),
backend_engineer AS (SELECT * FROM person WHERE job = 'backend engineer')
SELECT email, name, job FROM frontend_engineer
UNION
SELECT email, name, job FROM backend_engineer;
WITH programmer AS (
SELECT * FROM person WHERE job = 'frontend engineer'
UNION ALL
SELECT * FROM person WHERE job = 'backend engineer'
)
SELECT email, name, job FROM programmer;
# 수학 연산
SELECT age+3 FROM member;
SELECT salary*2 FROM member;
# 집합 연산
# UNION
UNION은 중복을 제거한다.
SELECT * FROM post WHERE writer_id=3 or id = 4
UNION
SELECT * FROM post WHERE writer_id=4 or id = 5;
+----+----------+-----------+-----------+
| id | content | writer_id | violating |
+----+----------+-----------+-----------+
| 4 | content4 | 3 | 0 |
| 5 | content5 | 3 | 0 |
| 6 | content6 | 4 | 0 |
| 7 | content7 | 4 | 0 |
+----+----------+-----------+-----------+
# UNION ALL
UNION은 중복을 제거하지 않는다.
SELECT * FROM post WHERE writer_id=3 or id = 4
UNION ALL
SELECT * FROM post WHERE writer_id=4 or id = 5;
+----+----------+-----------+-----------+
| id | content | writer_id | violating |
+----+----------+-----------+-----------+
| 4 | content4 | 3 | 0 |
| 5 | content5 | 3 | 0 |
| 5 | content5 | 3 | 0 |
| 6 | content6 | 4 | 0 |
| 7 | content7 | 4 | 0 |
+----+----------+-----------+-----------+
# JOIN
SELECT * FROM member;
+----+----------+------+-----------+--------+
| id | name | age | nation | gender |
+----+----------+------+-----------+--------+
| 1 | paul | 35 | england | man |
| 2 | monica | 23 | usa | woman |
| 3 | ross | 28 | australia | man |
| 4 | chandler | 29 | england | woman |
| 5 | chuck | 15 | usa | man |
| 6 | john | 35 | australia | man |
| 7 | silvia | 23 | usa | NULL |
| 8 | boss | 28 | australia | NULL |
| 9 | corona | 29 | usa | NULL |
| 10 | julia | 15 | england | NULL |
| 11 | joy | 30 | england | NULL |
| 12 | kane | 38 | england | NULL |
+----+----------+------+-----------+--------+
SELECT * FROM post;
+----+-----------+-----------+-----------+
| id | content | writer_id | violating |
+----+-----------+-----------+-----------+
| 1 | content1 | 2 | 0 |
| 2 | content2 | 2 | 0 |
| 3 | content3 | 2 | 0 |
| 4 | content4 | 3 | 0 |
| 5 | content5 | 3 | 0 |
| 6 | content6 | 4 | 0 |
| 7 | content7 | 4 | 0 |
| 8 | content8 | 7 | 1 |
| 9 | content9 | 7 | 1 |
| 10 | content10 | 8 | 1 |
| 11 | content11 | 8 | 1 |
| 12 | content12 | 9 | 1 |
| 13 | content13 | 9 | 1 |
| 14 | content14 | 9 | 1 |
| 15 | content15 | NULL | 0 |
| 16 | content16 | NULL | 0 |
+----+-----------+-----------+-----------+
# CROSS JOIN
Cartesian Product라고도 한다.
SELECT m.id as member_id, m.name, m.age, p.id as post_id, p.content, p.writer_id
FROM member m
CROSS JOIN post p;
+-----------+----------+------+---------+-----------+-----------+
| member_id | name | age | post_id | content | writer_id |
+-----------+----------+------+---------+-----------+-----------+
| 12 | kane | 38 | 1 | content1 | 2 |
| 11 | joy | 30 | 1 | content1 | 2 |
| 10 | julia | 15 | 1 | content1 | 2 |
| 9 | corona | 29 | 1 | content1 | 2 |
| 8 | boss | 28 | 1 | content1 | 2 |
| 7 | silvia | 23 | 1 | content1 | 2 |
| 6 | john | 35 | 1 | content1 | 2 |
| 5 | chuck | 15 | 1 | content1 | 2 |
| 4 | chandler | 29 | 1 | content1 | 2 |
| 3 | ross | 28 | 1 | content1 | 2 |
| 2 | monica | 23 | 1 | content1 | 2 |
| 1 | paul | 35 | 1 | content1 | 2 |
| 12 | kane | 38 | 2 | content2 | 2 |
| 11 | joy | 30 | 2 | content2 | 2 |
| 10 | julia | 15 | 2 | content2 | 2 |
| 9 | corona | 29 | 2 | content2 | 2 |
| 8 | boss | 28 | 2 | content2 | 2 |
| 7 | silvia | 23 | 2 | content2 | 2 |
| 6 | john | 35 | 2 | content2 | 2 |
| 5 | chuck | 15 | 2 | content2 | 2 |
| 4 | chandler | 29 | 2 | content2 | 2 |
| 3 | ross | 28 | 2 | content2 | 2 |
| 2 | monica | 23 | 2 | content2 | 2 |
| 1 | paul | 35 | 2 | content2 | 2 |
| 12 | kane | 38 | 3 | content3 | 2 |
| 11 | joy | 30 | 3 | content3 | 2 |
| 10 | julia | 15 | 3 | content3 | 2 |
| 9 | corona | 29 | 3 | content3 | 2 |
| 8 | boss | 28 | 3 | content3 | 2 |
| 7 | silvia | 23 | 3 | content3 | 2 |
| 6 | john | 35 | 3 | content3 | 2 |
| 5 | chuck | 15 | 3 | content3 | 2 |
| 4 | chandler | 29 | 3 | content3 | 2 |
| 3 | ross | 28 | 3 | content3 | 2 |
| 2 | monica | 23 | 3 | content3 | 2 |
| 1 | paul | 35 | 3 | content3 | 2 |
| 12 | kane | 38 | 4 | content4 | 3 |
| 11 | joy | 30 | 4 | content4 | 3 |
| 10 | julia | 15 | 4 | content4 | 3 |
| 9 | corona | 29 | 4 | content4 | 3 |
| 8 | boss | 28 | 4 | content4 | 3 |
| 7 | silvia | 23 | 4 | content4 | 3 |
| 6 | john | 35 | 4 | content4 | 3 |
| 5 | chuck | 15 | 4 | content4 | 3 |
| 4 | chandler | 29 | 4 | content4 | 3 |
| 3 | ross | 28 | 4 | content4 | 3 |
| 2 | monica | 23 | 4 | content4 | 3 |
| 1 | paul | 35 | 4 | content4 | 3 |
| 12 | kane | 38 | 5 | content5 | 3 |
| 11 | joy | 30 | 5 | content5 | 3 |
| 10 | julia | 15 | 5 | content5 | 3 |
| 9 | corona | 29 | 5 | content5 | 3 |
| 8 | boss | 28 | 5 | content5 | 3 |
| 7 | silvia | 23 | 5 | content5 | 3 |
| 6 | john | 35 | 5 | content5 | 3 |
| 5 | chuck | 15 | 5 | content5 | 3 |
| 4 | chandler | 29 | 5 | content5 | 3 |
| 3 | ross | 28 | 5 | content5 | 3 |
| 2 | monica | 23 | 5 | content5 | 3 |
| 1 | paul | 35 | 5 | content5 | 3 |
| 12 | kane | 38 | 6 | content6 | 4 |
| 11 | joy | 30 | 6 | content6 | 4 |
| 10 | julia | 15 | 6 | content6 | 4 |
| 9 | corona | 29 | 6 | content6 | 4 |
| 8 | boss | 28 | 6 | content6 | 4 |
| 7 | silvia | 23 | 6 | content6 | 4 |
| 6 | john | 35 | 6 | content6 | 4 |
| 5 | chuck | 15 | 6 | content6 | 4 |
| 4 | chandler | 29 | 6 | content6 | 4 |
| 3 | ross | 28 | 6 | content6 | 4 |
| 2 | monica | 23 | 6 | content6 | 4 |
| 1 | paul | 35 | 6 | content6 | 4 |
| 12 | kane | 38 | 7 | content7 | 4 |
| 11 | joy | 30 | 7 | content7 | 4 |
| 10 | julia | 15 | 7 | content7 | 4 |
| 9 | corona | 29 | 7 | content7 | 4 |
| 8 | boss | 28 | 7 | content7 | 4 |
| 7 | silvia | 23 | 7 | content7 | 4 |
| 6 | john | 35 | 7 | content7 | 4 |
| 5 | chuck | 15 | 7 | content7 | 4 |
| 4 | chandler | 29 | 7 | content7 | 4 |
| 3 | ross | 28 | 7 | content7 | 4 |
| 2 | monica | 23 | 7 | content7 | 4 |
| 1 | paul | 35 | 7 | content7 | 4 |
| 12 | kane | 38 | 8 | content8 | 7 |
| 11 | joy | 30 | 8 | content8 | 7 |
| 10 | julia | 15 | 8 | content8 | 7 |
| 9 | corona | 29 | 8 | content8 | 7 |
| 8 | boss | 28 | 8 | content8 | 7 |
| 7 | silvia | 23 | 8 | content8 | 7 |
| 6 | john | 35 | 8 | content8 | 7 |
| 5 | chuck | 15 | 8 | content8 | 7 |
| 4 | chandler | 29 | 8 | content8 | 7 |
| 3 | ross | 28 | 8 | content8 | 7 |
| 2 | monica | 23 | 8 | content8 | 7 |
| 1 | paul | 35 | 8 | content8 | 7 |
| 12 | kane | 38 | 9 | content9 | 7 |
| 11 | joy | 30 | 9 | content9 | 7 |
| 10 | julia | 15 | 9 | content9 | 7 |
| 9 | corona | 29 | 9 | content9 | 7 |
| 8 | boss | 28 | 9 | content9 | 7 |
| 7 | silvia | 23 | 9 | content9 | 7 |
| 6 | john | 35 | 9 | content9 | 7 |
| 5 | chuck | 15 | 9 | content9 | 7 |
| 4 | chandler | 29 | 9 | content9 | 7 |
| 3 | ross | 28 | 9 | content9 | 7 |
| 2 | monica | 23 | 9 | content9 | 7 |
| 1 | paul | 35 | 9 | content9 | 7 |
| 12 | kane | 38 | 10 | content10 | 8 |
| 11 | joy | 30 | 10 | content10 | 8 |
| 10 | julia | 15 | 10 | content10 | 8 |
| 9 | corona | 29 | 10 | content10 | 8 |
| 8 | boss | 28 | 10 | content10 | 8 |
| 7 | silvia | 23 | 10 | content10 | 8 |
| 6 | john | 35 | 10 | content10 | 8 |
| 5 | chuck | 15 | 10 | content10 | 8 |
| 4 | chandler | 29 | 10 | content10 | 8 |
| 3 | ross | 28 | 10 | content10 | 8 |
| 2 | monica | 23 | 10 | content10 | 8 |
| 1 | paul | 35 | 10 | content10 | 8 |
| 12 | kane | 38 | 11 | content11 | 8 |
| 11 | joy | 30 | 11 | content11 | 8 |
| 10 | julia | 15 | 11 | content11 | 8 |
| 9 | corona | 29 | 11 | content11 | 8 |
| 8 | boss | 28 | 11 | content11 | 8 |
| 7 | silvia | 23 | 11 | content11 | 8 |
| 6 | john | 35 | 11 | content11 | 8 |
| 5 | chuck | 15 | 11 | content11 | 8 |
| 4 | chandler | 29 | 11 | content11 | 8 |
| 3 | ross | 28 | 11 | content11 | 8 |
| 2 | monica | 23 | 11 | content11 | 8 |
| 1 | paul | 35 | 11 | content11 | 8 |
| 12 | kane | 38 | 12 | content12 | 9 |
| 11 | joy | 30 | 12 | content12 | 9 |
| 10 | julia | 15 | 12 | content12 | 9 |
| 9 | corona | 29 | 12 | content12 | 9 |
| 8 | boss | 28 | 12 | content12 | 9 |
| 7 | silvia | 23 | 12 | content12 | 9 |
| 6 | john | 35 | 12 | content12 | 9 |
| 5 | chuck | 15 | 12 | content12 | 9 |
| 4 | chandler | 29 | 12 | content12 | 9 |
| 3 | ross | 28 | 12 | content12 | 9 |
| 2 | monica | 23 | 12 | content12 | 9 |
| 1 | paul | 35 | 12 | content12 | 9 |
| 12 | kane | 38 | 13 | content13 | 9 |
| 11 | joy | 30 | 13 | content13 | 9 |
| 10 | julia | 15 | 13 | content13 | 9 |
| 9 | corona | 29 | 13 | content13 | 9 |
| 8 | boss | 28 | 13 | content13 | 9 |
| 7 | silvia | 23 | 13 | content13 | 9 |
| 6 | john | 35 | 13 | content13 | 9 |
| 5 | chuck | 15 | 13 | content13 | 9 |
| 4 | chandler | 29 | 13 | content13 | 9 |
| 3 | ross | 28 | 13 | content13 | 9 |
| 2 | monica | 23 | 13 | content13 | 9 |
| 1 | paul | 35 | 13 | content13 | 9 |
| 12 | kane | 38 | 14 | content14 | 9 |
| 11 | joy | 30 | 14 | content14 | 9 |
| 10 | julia | 15 | 14 | content14 | 9 |
| 9 | corona | 29 | 14 | content14 | 9 |
| 8 | boss | 28 | 14 | content14 | 9 |
| 7 | silvia | 23 | 14 | content14 | 9 |
| 6 | john | 35 | 14 | content14 | 9 |
| 5 | chuck | 15 | 14 | content14 | 9 |
| 4 | chandler | 29 | 14 | content14 | 9 |
| 3 | ross | 28 | 14 | content14 | 9 |
| 2 | monica | 23 | 14 | content14 | 9 |
| 1 | paul | 35 | 14 | content14 | 9 |
| 12 | kane | 38 | 15 | content15 | NULL |
| 11 | joy | 30 | 15 | content15 | NULL |
| 10 | julia | 15 | 15 | content15 | NULL |
| 9 | corona | 29 | 15 | content15 | NULL |
| 8 | boss | 28 | 15 | content15 | NULL |
| 7 | silvia | 23 | 15 | content15 | NULL |
| 6 | john | 35 | 15 | content15 | NULL |
| 5 | chuck | 15 | 15 | content15 | NULL |
| 4 | chandler | 29 | 15 | content15 | NULL |
| 3 | ross | 28 | 15 | content15 | NULL |
| 2 | monica | 23 | 15 | content15 | NULL |
| 1 | paul | 35 | 15 | content15 | NULL |
| 12 | kane | 38 | 16 | content16 | NULL |
| 11 | joy | 30 | 16 | content16 | NULL |
| 10 | julia | 15 | 16 | content16 | NULL |
| 9 | corona | 29 | 16 | content16 | NULL |
| 8 | boss | 28 | 16 | content16 | NULL |
| 7 | silvia | 23 | 16 | content16 | NULL |
| 6 | john | 35 | 16 | content16 | NULL |
| 5 | chuck | 15 | 16 | content16 | NULL |
| 4 | chandler | 29 | 16 | content16 | NULL |
| 3 | ross | 28 | 16 | content16 | NULL |
| 2 | monica | 23 | 16 | content16 | NULL |
| 1 | paul | 35 | 16 | content16 | NULL |
+-----------+----------+------+---------+-----------+-----------+
# INNER JOIN
ON절의 조건을 만족하는 컬럼만 출력한다.
SELECT m.id as member_id, m.name, m.age, p.id as post_id, p.content, p.writer_id
FROM member m
INNER JOIN post p
ON m.id = p.writer_id;
+-----------+----------+------+---------+-----------+-----------+
| member_id | name | age | post_id | content | writer_id |
+-----------+----------+------+---------+-----------+-----------+
| 2 | monica | 23 | 1 | content1 | 2 |
| 2 | monica | 23 | 2 | content2 | 2 |
| 2 | monica | 23 | 3 | content3 | 2 |
| 3 | ross | 28 | 4 | content4 | 3 |
| 3 | ross | 28 | 5 | content5 | 3 |
| 4 | chandler | 29 | 6 | content6 | 4 |
| 4 | chandler | 29 | 7 | content7 | 4 |
| 7 | silvia | 23 | 8 | content8 | 7 |
| 7 | silvia | 23 | 9 | content9 | 7 |
| 8 | boss | 28 | 10 | content10 | 8 |
| 8 | boss | 28 | 11 | content11 | 8 |
| 9 | corona | 29 | 12 | content12 | 9 |
| 9 | corona | 29 | 13 | content13 | 9 |
| 9 | corona | 29 | 14 | content14 | 9 |
+-----------+----------+------+---------+-----------+-----------+
# LEFT OUTER JOIN
ON절의 조건을 만족하는 컬럼 + 왼쪽 테이블 컬럼
SELECT m.id as member_id, m.name, m.age, p.id as post_id, p.content, p.writer_id
FROM member m
LEFT OUTER JOIN post p
ON m.id = p.writer_id;
+-----------+----------+------+---------+-----------+-----------+
| member_id | name | age | post_id | content | writer_id |
+-----------+----------+------+---------+-----------+-----------+
| 1 | paul | 35 | NULL | NULL | NULL |
| 2 | monica | 23 | 1 | content1 | 2 |
| 2 | monica | 23 | 2 | content2 | 2 |
| 2 | monica | 23 | 3 | content3 | 2 |
| 3 | ross | 28 | 4 | content4 | 3 |
| 3 | ross | 28 | 5 | content5 | 3 |
| 4 | chandler | 29 | 6 | content6 | 4 |
| 4 | chandler | 29 | 7 | content7 | 4 |
| 5 | chuck | 15 | NULL | NULL | NULL |
| 6 | john | 35 | NULL | NULL | NULL |
| 7 | silvia | 23 | 8 | content8 | 7 |
| 7 | silvia | 23 | 9 | content9 | 7 |
| 8 | boss | 28 | 10 | content10 | 8 |
| 8 | boss | 28 | 11 | content11 | 8 |
| 9 | corona | 29 | 12 | content12 | 9 |
| 9 | corona | 29 | 13 | content13 | 9 |
| 9 | corona | 29 | 14 | content14 | 9 |
| 10 | julia | 15 | NULL | NULL | NULL |
| 11 | joy | 30 | NULL | NULL | NULL |
| 12 | kane | 38 | NULL | NULL | NULL |
+-----------+----------+------+---------+-----------+-----------+
# RIGHT OUTER JOIN
ON절의 조건을 만족하는 컬럼 + 오른쪽 테이블 컬럼
SELECT m.id as member_id, m.name, m.age, p.id as post_id, p.content, p.writer_id
FROM member m
RIGHT OUTER JOIN post p
ON m.id = p.writer_id;
+-----------+----------+------+---------+-----------+-----------+
| member_id | name | age | post_id | content | writer_id |
+-----------+----------+------+---------+-----------+-----------+
| 2 | monica | 23 | 1 | content1 | 2 |
| 2 | monica | 23 | 2 | content2 | 2 |
| 2 | monica | 23 | 3 | content3 | 2 |
| 3 | ross | 28 | 4 | content4 | 3 |
| 3 | ross | 28 | 5 | content5 | 3 |
| 4 | chandler | 29 | 6 | content6 | 4 |
| 4 | chandler | 29 | 7 | content7 | 4 |
| 7 | silvia | 23 | 8 | content8 | 7 |
| 7 | silvia | 23 | 9 | content9 | 7 |
| 8 | boss | 28 | 10 | content10 | 8 |
| 8 | boss | 28 | 11 | content11 | 8 |
| 9 | corona | 29 | 12 | content12 | 9 |
| 9 | corona | 29 | 13 | content13 | 9 |
| 9 | corona | 29 | 14 | content14 | 9 |
| NULL | NULL | NULL | 15 | content15 | NULL |
| NULL | NULL | NULL | 16 | content16 | NULL |
+-----------+----------+------+---------+-----------+-----------+
# FULL OUTER JOIN
MySQL은 FULL OUTER JOIN을 지원하지 않는다. 따라서 UNION으로 흉내낼 수 있다.
(
SELECT m.id as member_id, m.name, m.age, p.id as post_id, p.content, p.writer_id
FROM post p
LEFT OUTER JOIN member m
ON m.id = p.writer_id
)
UNION
(
SELECT m.id as member_id, m.name, m.age, p.id as post_id, p.content, p.writer_id
FROM member m
LEFT OUTER JOIN post p
ON m.id = p.writer_id
);
+-----------+----------+------+---------+-----------+-----------+
| member_id | name | age | post_id | content | writer_id |
+-----------+----------+------+---------+-----------+-----------+
| 2 | monica | 23 | 1 | content1 | 2 |
| 2 | monica | 23 | 2 | content2 | 2 |
| 2 | monica | 23 | 3 | content3 | 2 |
| 3 | ross | 28 | 4 | content4 | 3 |
| 3 | ross | 28 | 5 | content5 | 3 |
| 4 | chandler | 29 | 6 | content6 | 4 |
| 4 | chandler | 29 | 7 | content7 | 4 |
| 7 | silvia | 23 | 8 | content8 | 7 |
| 7 | silvia | 23 | 9 | content9 | 7 |
| 8 | boss | 28 | 10 | content10 | 8 |
| 8 | boss | 28 | 11 | content11 | 8 |
| 9 | corona | 29 | 12 | content12 | 9 |
| 9 | corona | 29 | 13 | content13 | 9 |
| 9 | corona | 29 | 14 | content14 | 9 |
| NULL | NULL | NULL | 15 | content15 | NULL |
| NULL | NULL | NULL | 16 | content16 | NULL |
| 1 | paul | 35 | NULL | NULL | NULL |
| 5 | chuck | 15 | NULL | NULL | NULL |
| 6 | john | 35 | NULL | NULL | NULL |
| 10 | julia | 15 | NULL | NULL | NULL |
| 11 | joy | 30 | NULL | NULL | NULL |
| 12 | kane | 38 | NULL | NULL | NULL |
+-----------+----------+------+---------+-----------+-----------+
# 조건문
# IF
IF(조건문, 참일 때 값, 거짓일 때 값)
SELECT 3;
+---+
| 3 |
+---+
| 3 |
+---+
SELECT IF(3>1, 'larger than', 'smaller than');
+----------------------------------------+
| IF(3>1, 'Larger than', 'Smaller than') |
+----------------------------------------+
| Larger than |
+----------------------------------------+
SELECT IF(3>1, 'Larger than', 'Smaller than') AS result;
+-------------+
| result |
+-------------+
| Larger than |
+-------------+
SELECT name, IF(age>20, 'adult', 'minor') AS result;
SELECT IF(team IS NULL, 'Empty', 'Not Empty') AS result;
# IFNULL
SELECT IFNULL(name, "unknown") FROM user;
# NVL
SELECT NVL(team, "No team") FROM players;
# CASE WHEN
SELECT
name,
CASE
WHEN (m.score > 85) THEN 'A'
WHEN (m.score > 75) THEN 'B'
WHEN (m.score > 65) THEN 'C'
ELSE 'F'
END
AS grade
FROM member m;
+---------------+
| name | grade |
+---------------+
| smith | A |
| paul | C |
| jane | B |
+---------------+