기초 실습환경

+----+--------+------------+------+------+------------------+------------------+
| ID | NAME   | BIRTHDAY   | AGE  | SEX  | JOB_TITLE        | AGENCY           |
+----+--------+------------+------+------+------------------+------------------+
|  1 | 아이유 | 1993-05-16 |   29 | F    | 가수, 텔런트     | EDAM엔터테이먼트 |
|  2 | 이미주 | 1994-09-23 |   28 | F    | 가수             | 울림엔터테이먼트 |
|  3 | 송강   | 1994-04-23 |   28 | M    | 텔런트           | 나무엑터스       |
|  4 | 강동원 | 1981-01-18 |   41 | M    | 영화배우, 텔런트 | YG엔터테이먼트   |
|  5 | 유재석 | 1972-08-14 |   50 | M    | MC, 개그맨       | 안테나           |
|  6 | 차승원 | 1970-06-07 |   48 | M    | 영화배우, 모델   | YG엔터테이먼트   |
|  7 | 이수현 | 1999-05-04 |   23 | F    | 가수             | YG엔터테이먼트   |
+----+--------+------------+------+------+------------------+------------------+
7 rows in set (0.00 sec)
+------+
| no   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)
mysql> select * from test2;
+------+
| no   |
+------+
|    3 |
|    5 |
|    6 |
+------+
3 rows in set (0.00 sec)

1. UNION

여러 개의 SQL문을 합쳐서 하나의 SQL 문으로 만들어주는 방법
(단, column의 개수가 같아야함)

🎯 UNION ALL

중복된 값도 모두 보여준다.

✍ 입력

SELECT * FROM test1
UNION ALL
SELECT * FROM test2;

💻 출력

+------+
| no   |
+------+
|    1 |
|    2 |
|    3 |
|    3 |
|    5 |
|    6 |
+------+
6 rows in set (0.00 sec)

🎯 UNION

중복된 값을 제거하여 알려준다

✍ 입력

SELECT * FROM test1
UNION
SELECT * FROM test2;

💻 출력

+------+
| no   |
+------+
|    1 |
|    2 |
|    3 |
|    5 |
|    6 |
+------+
5 rows in set (0.00 sec)

• 예제 1 : 성별이 여자인 데이터를 검색하는 쿼리와 소속사가 YG엔터테이먼트인 데이터를 검색하는 쿼리

1) UNION ALL

✍ 입력

SELECT name, sex, agency FROM celeb WHERE sex='F'
UNION ALL
SELECT name, sex, agency FROM agency='YG엔터테이먼트';

💻 출력

+--------+------+------------------+
| name   | sex  | agency           |
+--------+------+------------------+
| 아이유 | F    | EDAM엔터테이먼트 |
| 이미주 | F    | 울림엔터테이먼트 |
| 이수현 | F    | YG엔터테이먼트   |
| 강동원 | M    | YG엔터테이먼트   |
| 차승원 | M    | YG엔터테이먼트   |
| 이수현 | F    | YG엔터테이먼트   |
+--------+------+------------------+
6 rows in set (0.00 sec)

2) UNION / WHERE절 AND로 연결

✍ 입력

SELECT name, sex, agency FROM celeb WHERE sex='F'
UNION
SELECT name, sex, agency FROM celeb WHERE agency='YG엔터테이먼트';

=

SELECT name, sex, agency FROM celeb WHERE sex='F' OR agency='YG엔터테이먼트';

💻 출력

+--------+------+------------------+
| name   | sex  | agency           |
+--------+------+------------------+
| 아이유 | F    | EDAM엔터테이먼트 |
| 이미주 | F    | 울림엔터테이먼트 |
| 강동원 | M    | YG엔터테이먼트   |
| 차승원 | M    | YG엔터테이먼트   |
| 이수현 | F    | YG엔터테이먼트   |
+--------+------+------------------+
5 rows in set (0.00 sec)

2. JOIN

두개 이상의 테이블을 결합하는 명령문

기초 실습 환경

mysql> select* from snl_show order by id;
+----+--------+---------+----------------+--------+
| ID | SEASON | EPISODE | BROADCAST_DATE | HOST   |
+----+--------+---------+----------------+--------+
|  1 |      8 |       7 | 2020-09-05     | 강동원 |
|  2 |      8 |       8 | 2020-09-12     | 유재석 |
|  3 |      8 |       9 | 2020-09-19     | 차승원 |
|  4 |      8 |      10 | 2020-09-26     | 이수현 |
|  5 |      9 |       1 | 2021-09-04     | 이병헌 |
|  6 |      9 |       2 | 2021-09-11     | 하지원 |
|  7 |      9 |       3 | 2021-09-18     | 제시   |
|  8 |      9 |       4 | 2021-09-25     | 조정석 |
|  9 |      9 |       5 | 2021-10-02     | 조여정 |
| 10 |      9 |       6 | 2021-10-09     | 옥주현 |
+----+--------+---------+----------------+--------+
10 rows in set (0.00 sec)

🎯 JOIN 기본

✍ SQL JOIN의 종류

🎯 INNER JOIN

두 개의 테이블에서 공통된 요소들을 통해 결합하는 join 방식

• 예제 : snl_show에 호스트로 출연한 celeb을 기준으로 celeb 테이블과 snl_show 테이블을 INNER JOIN

✍ 입력

SELECT celeb.id, celeb.name, snl_show.id, snl_show.host
FROM celeb
INNER JOIN snl_show
ON celeb.name = snl_show.host;

💻 출력

+----+--------+----+--------+
| id | name   | id | host   |
+----+--------+----+--------+
|  4 | 강동원 |  1 | 강동원 |
|  5 | 유재석 |  2 | 유재석 |
|  6 | 차승원 |  3 | 차승원 |
|  7 | 이수현 |  4 | 이수현 |
+----+--------+----+--------+
4 rows in set (0.01 sec)
==> 양쪽 테이블 공통된 데이터

🎯 LEFT JOIN

두 개의 테이블에서 공통된 요소들을 포함해 왼쪽 테이블의 다른 데이터를 포함하는 join 방식

• 예제 : snl_show에 호스트로 출연한 celeb을 기준으로 celeb 테이블과 snl_show 테이블을 LEFT JOIN

✍ 입력

SELECT celeb.id, celeb.name, snl_show.id, snl_show.host
FROM celeb
LEFT JOIN snl_show
ON celeb.name = snl_show.host

💻 출력

+----+--------+------+--------+
| id | name   | id   | host   |
+----+--------+------+--------+
|  1 | 아이유 | NULL | NULL   |    => snl_show(right table)에 정보 없음
|  2 | 이미주 | NULL | NULL   |    => snl_show(right table)에 정보 없음
|  3 | 송강   | NULL | NULL   |    => snl_show(right table)에 정보 없음
|  4 | 강동원 |    1 | 강동원 |
|  5 | 유재석 |    2 | 유재석 |
|  6 | 차승원 |    3 | 차승원 |
|  7 | 이수현 |    4 | 이수현 |
+----+--------+------+--------+
7 rows in set (0.00 sec)

🎯 RIGHT JOIN

두 개의 테이블에서 공통된 요소들을 포함해 오른쪽 테이블의 다른 데이터를 포함하는 join 방식

• 예제 : snl_show에 호스트로 출연한 celeb을 기준으로 celeb 테이블과 snl_show 테이블을 RIGHT JOIN

✍ 입력

SELECT celeb.id, celeb.name, snl_show.id, snl_show.host
FROM celeb
RIGHT JOIN snl_show
ON celeb.name = snl_show.host

💻 출력

+------+--------+----+--------+
| id   | name   | id | host   |
+------+--------+----+--------+
|    4 | 강동원 |  1 | 강동원 |
|    5 | 유재석 |  2 | 유재석 |
|    6 | 차승원 |  3 | 차승원 |
|    7 | 이수현 |  4 | 이수현 |
| NULL | NULL   |  5 | 이병헌 |    => celeb(left table)에 정보 없음
| NULL | NULL   |  6 | 하지원 |    => celeb(left table)에 정보 없음
| NULL | NULL   |  7 | 제시   |    => celeb(left table)에 정보 없음
| NULL | NULL   |  8 | 조정석 |    => celeb(left table)에 정보 없음
| NULL | NULL   |  9 | 조여정 |    => celeb(left table)에 정보 없음
| NULL | NULL   | 10 | 옥주현 |    => celeb(left table)에 정보 없음
+------+--------+----+--------+
10 rows in set (0.00 sec)

🎯 FULL OUTER JOIN

두 개의 테이블에서 공통된 요소들을 포함해 양쪽 테이블의 다른 데이터를 모두 포함하는 join 방식

• 예제 : snl_show에 호스트로 출연한 celeb을 기준으로 celeb 테이블과 snl_show 테이블을 RIGHT JOIN

✍ 입력

SELECT celeb.id, celeb.name, snl_show.id, snl_show.host
FROM celeb
FULL OUTER JOIN snl_show
ON celeb.name = snl_show.host;

💻 출력

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OUTER JOIN snl_show
ON celeb.name = snl_show.host' at line 3

📌 mySQL은 FULL OUTER JOIN을 지원하지 않음
- 기타 DBMS에서는 사용가능

• mySQL에서 FULL OUTER JOIN 기능을 활용하려면 다음과 같이 쿼리 구성

✍ 입력

SELECT celeb.id, celeb.name, snl_show.id, snl_show.host
FROM celeb
LEFT JOIN snl_show
ON celeb.name = snl_show.host
UNION
SELECT celeb.id, celeb.name, snl_show.id, snl_show.host
FROM celeb
RIGHT JOIN snl_show
ON celeb.name = snl_show.host;

💻 출력

+------+--------+------+--------+
| id   | name   | id   | host   |
+------+--------+------+--------+
|    1 | 아이유 | NULL | NULL   |
|    2 | 이미주 | NULL | NULL   |
|    3 | 송강   | NULL | NULL   |
|    4 | 강동원 |    1 | 강동원 |
|    5 | 유재석 |    2 | 유재석 |
|    6 | 차승원 |    3 | 차승원 |
|    7 | 이수현 |    4 | 이수현 |
| NULL | NULL   |    5 | 이병헌 |
| NULL | NULL   |    6 | 하지원 |
| NULL | NULL   |    7 | 제시   |
| NULL | NULL   |    8 | 조정석 |
| NULL | NULL   |    9 | 조여정 |
| NULL | NULL   |   10 | 옥주현 |
+------+--------+------+--------+
13 rows in set (0.00 sec)

🎯 SELF JOIN

JOIN 명령문 없이 INNER JOIN 기능을 실행

• 예제 1 : snl_show에 호스트로 출연한 celeb을 기준으로 celeb 테이블과 snl_show 테이블을 SELF JOIN

✍ 입력

SELECT celeb.id, celeb.name, snl_show.id, snl_show.host
FROM celeb, snl_show
WHERE celeb.name = snl_show.host;

💻 출력

+----+--------+----+--------+
| id | name   | id | host   |
+----+--------+----+--------+
|  4 | 강동원 |  1 | 강동원 |
|  5 | 유재석 |  2 | 유재석 |
|  6 | 차승원 |  3 | 차승원 |
|  7 | 이수현 |  4 | 이수현 |
+----+--------+----+--------+
4 rows in set (0.00 sec)

• 예제 2 : celeb 테이블의 연예인 중, snl_show에 host로 출연했고, 소속사가 안테나인 사람의 이름과 직업을 검색

✍ 입력

SELECT celeb.name, celeb.job_title
FROM celeb, snl_show
WHERE celeb.name = snl_show.host AND celeb.agency='안테나';

💻 출력

+--------+------------+
| name   | job_title  |
+--------+------------+
| 유재석 | MC, 개그맨 |
+--------+------------+
1 row in set (0.00 sec)

• 예제 3 : celeb 테이블의 연예인 중, snl_show에 host로 출연했고, 영화배우는 아니면서 YG 엔터테이먼트 소속이거나 40세 이상이면서 YG 엔터테이먼트 소속이 아닌 연예인의 이름과 나이, 직업, 소속사, 시즌, 에피소드 정보를 검색

✍ 입력

SELECT celeb.name, celeb.age, celeb.job_title, celeb.agency, snl_show.season, snl_show.episode
FROM celeb, snl_show
WHERE celeb.name = snl_show.host
  AND ((celeb.job_title NOT LIKE '%영화배우%' AND celeb.agency='YG엔터테이먼트')
   OR (celeb.age>=40 AND celeb.agency !='YG엔터테이먼트'));

💻 출력

+--------+------+------------+----------------+--------+---------+
| name   | age  | job_title  | agency         | season | episode |
+--------+------+------------+----------------+--------+---------+
| 유재석 |   50 | MC, 개그맨 | 안테나         |      8 |       8 |
| 이수현 |   23 | 가수       | YG엔터테이먼트 |      8 |      10 |
+--------+------+------------+----------------+--------+---------+
2 rows in set (0.00 sec)

• 예제 4 : snl_show에 출연한 연예인의 snl_show 아이디, 시즌, 에피소드, 이름, 직업 정보를 검색

✍ 입력

SELECT snl_show.id, snl_show.season, snl_show.episode, celeb.name, celeb.job_title
FROM snl_show, celeb
WHERE snl_show.host=celeb.name;

💻 출력

+----+--------+---------+--------+------------------+
| id | season | episode | name   | job_title        |
+----+--------+---------+--------+------------------+
|  1 |      8 |       7 | 강동원 | 영화배우, 텔런트 |
|  2 |      8 |       8 | 유재석 | MC, 개그맨       |
|  3 |      8 |       9 | 차승원 | 영화배우, 모델   |
|  4 |      8 |      10 | 이수현 | 가수             |
+----+--------+---------+--------+------------------+
4 rows in set (0.00 sec)

• 예제 5 : snl_show에 출연한 celeb 중, 에피소드 7,9,10 중에 출연했거나, 소속사가 YG로 시작하고 뒤에 6글자로 끝나는 사람 중 snl_show에 2020년 9월 15일 이후에 출연했던 사람을 검색

✍ 입력

SELECT celeb.name, celeb.agency, snl_show.episode, snl_show.broadcast_date
FROM celeb, snl_show
WHERE celeb.name = snl_show.host
  AND ((snl_show.episode IN (7,9,10) OR celeb.agency LIKE 'YG______') AND snl_show.broadcast_date > 20200915);

💻 출력

+--------+----------------+---------+----------------+
| name   | agency         | episode | broadcast_date |
+--------+----------------+---------+----------------+
| 차승원 | YG엔터테이먼트 |       9 | 2020-09-19     |
| 이수현 | YG엔터테이먼트 |      10 | 2020-09-26     |
+--------+----------------+---------+----------------+
2 rows in set (0.00 sec)

3. CONCAT

여러 문자열을 하나로 합치거나 연결

🎯 CONCAT

두 개의 테이블에서 공통된 요소들을 통해 결합하는 join 방식

• 예제 1

✍ 입력

SELECT CONCAT('concat',' ','test');

💻 출력

+-----------------------------+
| CONCAT('concat',' ','test') |
+-----------------------------+
| concat test                 |
+-----------------------------+
1 row in set (0.01 sec)

• 예제 2

✍ 입력

SELECT CONCAT('이름:',name) FROM celeb;

💻 출력

+----------------------+
| CONCAT('이름:',name) |
+----------------------+
| 이름:아이유          |
| 이름:이미주          |
| 이름:송강            |
| 이름:강동원          |
| 이름:유재석          |
| 이름:차승원          |
| 이름:이수현          |
+----------------------+
7 rows in set (0.00 sec)

🎯 ALIAS

column이나 table 이름에 별칭을 생성

• 예제 1 : column에 별칭 생성

✍ 입력

SELECT name as '이름' FROM celeb;

💻 출력

+--------+
| 이름   |
+--------+
| 아이유 |
| 이미주 |
| 송강   |
| 강동원 |
| 유재석 |
| 차승원 |
| 이수현 |
+--------+
7 rows in set (0.00 sec)

📌 별칭 생성 시 as 생략 가능
- SELECT columnname (as) nickname FROM table 도 가능

• 예제 2 : '이름'과 '소속사'로 별칭을 만들어서 해당 정보 검색

✍ 입력

SELECT name '이름', agency '소속사' FROM celeb;

💻 출력

+--------+------------------+
| 이름   | 소속사           |
+--------+------------------+
| 아이유 | EDAM엔터테이먼트 |
| 이미주 | 울림엔터테이먼트 |
| 송강   | 나무엑터스       |
| 강동원 | YG엔터테이먼트   |
| 유재석 | 안테나           |
| 차승원 | YG엔터테이먼트   |
| 이수현 | YG엔터테이먼트   |
+--------+------------------+
7 rows in set (0.00 sec)

• 예제 3 : name 과 job_title을 합쳐서 profile이라는 별칭을 만들어서 검색

✍ 입력

SELECT CONCAT(name,' : ',job_title) profile FROM celeb;

💻 출력

+---------------------------+
| profile                   |
+---------------------------+
| 아이유 : 가수, 텔런트     |
| 이미주 : 가수             |
| 송강 : 텔런트             |
| 강동원 : 영화배우, 텔런트 |
| 유재석 : MC, 개그맨       |
| 차승원 : 영화배우, 모델   |
| 이수현 : 가수             |
+---------------------------+
7 rows in set (0.00 sec)

• 예제 4 : snl_show에 출연한 celeb을 기준으로 두 테이블을 JOIN하여, celeb 테이블은 c, snl_show 테이블은 s라는 별칭을 만들어서 출연한 시즌과 에피소드, 이름, 직업을 검색

✍ 입력

SELECT s.season, s.episode, c.name, c.job_title
FROM celeb c, snl_show s
WHERE c.name = s.host;

💻 출력

+--------+---------+--------+------------------+
| season | episode | name   | job_title        |
+--------+---------+--------+------------------+
|      8 |       7 | 강동원 | 영화배우, 텔런트 |
|      8 |       8 | 유재석 | MC, 개그맨       |
|      8 |       9 | 차승원 | 영화배우, 모델   |
|      8 |      10 | 이수현 | 가수             |
+--------+---------+--------+------------------+
4 rows in set (0.00 sec)

• 예제 5 : snl_show에 출연한 celeb을 기준으로 두 테이블을 JOIN하여 다음과 같이 각 데이터의 별칭을 사용하여 검색
  - 시즌, 에피소드, 방송일을 합쳐서 '방송정보'
  • 이름, 직업을 합쳐서 '출연자정보'

✍ 입력

SELECT CONCAT(s.season,'-',s.episode,'(',s.broadcast_date,')') '방송정보',
	   CONCAT(c.name,' ','(',c.job_title,')') '출연자정보'
FROM celeb c, snl_show s
WHERE c.name = s.host;

💻 출력

+------------------+---------------------------+
| 방송정보         | 출연자정보                |
+------------------+---------------------------+
| 8-7(2020-09-05)  | 강동원 (영화배우, 텔런트) |
| 8-8(2020-09-12)  | 유재석 (MC, 개그맨)       |
| 8-9(2020-09-19)  | 차승원 (영화배우, 모델)   |
| 8-10(2020-09-26) | 이수현 (가수)             |
+------------------+---------------------------+
4 rows in set (0.00 sec)

🎯 DISTINCT

검색한 결과의 중복 제거

• 예제 1-1 : 연예인 소속사 종류를 검색 - 중복 포함

✍ 입력

SELECT agency FROM celeb;

💻 출력

+------------------+
| agency           |
+------------------+
| EDAM엔터테이먼트 |
| 울림엔터테이먼트 |
| 나무엑터스       |
| YG엔터테이먼트   |
| 안테나           |
| YG엔터테이먼트   |
| YG엔터테이먼트   |
+------------------+
7 rows in set (0.00 sec)

• 예제 1-2 : 연예인 소속사 종류를 검색 - 중복 제거

✍ 입력

SELECT DISTINCT agency FROM celeb;

💻 출력

+------------------+
| agency           |
+------------------+
| EDAM엔터테이먼트 |
| 울림엔터테이먼트 |
| 나무엑터스       |
| YG엔터테이먼트   |
| 안테나           |
+------------------+
5 rows in set (0.00 sec)    => 중복된 agency 2개 제거

• 예제 2-1 : 가수 중에서, 성별과 직업별 종류를 검색 - 중복 포함

✍ 입력

SELECT sex, job_title FROM celeb WHERE job_title LIKE '%가수%';

💻 출력

+------+--------------+
| sex  | job_title    |
+------+--------------+
| F    | 가수, 텔런트 |
| F    | 가수         |
| F    | 가수         |
+------+--------------+
3 rows in set (0.00 sec)

• 예제 2-2 : 가수 중에서, 성별과 직업별 종류를 검색 - 중복 제거

✍ 입력

SELECT DISTINCT sex, job_title FROM celeb WHERE job_title LIKE '%가수%';

💻 출력

+------+--------------+
| sex  | job_title    |
+------+--------------+
| F    | 가수, 텔런트 |
| F    | 가수         |
+------+--------------+
2 rows in set (0.00 sec)

🎯 LIMIT

검색결과를 정렬된 순으로 주어진 숫자만큼만 조회

✍ 입력

SELECT * FROM celeb LIMIT 3;

💻 출력

+----+--------+------------+------+------+--------------+------------------+
| ID | NAME   | BIRTHDAY   | AGE  | SEX  | JOB_TITLE    | AGENCY           |
+----+--------+------------+------+------+--------------+------------------+
|  1 | 아이유 | 1993-05-16 |   29 | F    | 가수, 텔런트 | EDAM엔터테이먼트 |
|  2 | 이미주 | 1994-09-23 |   28 | F    | 가수         | 울림엔터테이먼트 |
|  3 | 송강   | 1994-04-23 |   28 | M    | 텔런트       | 나무엑터스       |
+----+--------+------------+------+------+--------------+------------------+
3 rows in set (0.00 sec)