SQL_2

5. Union

• 실습환경 만들기

USE zerobase;

SELECT * FROM zerobase;

# 테스트용 테이블 생성
CREATE TABLE test1
(
	no int
);

CREATE TABLE test2
(
	no int
);

# 테스트 데이터 추가
INSERT INTO test1 VALUES (1);
INSERT INTO test1 VALUES (2);
INSERT INTO test1 VALUES (3);
INSERT INTO test2 VALUES (5);
INSERT INTO test2 VALUES (6);
INSERT INTO test2 VALUES (3);

# 데이터 확인
SELECT * FROM test1;
SELECT * FROM test2;

• UNION : 여러 개의 SQL문을 합쳐서 하나의 SQL 문으로 만들어주는 방법
  (주의. 칼럼의 개수가 같아야함.)
  • UNION : 중복된 값을 제거하여 알려준다.
  • UNION ALL : 중복된 값도 모두 보여준다.

# test1의 모든 데이터와 test2의 모든 데이터를 중복된 값을 포함하여 검색
SELECT * FROM test1
UNION ALL
SELECT * FROM test2;

# test1의 모든 데이터와 test2의 모든 데이터를 중복된 값을 제거하여 검색
SELECT * FROM test1
UNION
SELECT * FROM test2;

# 성별이 여자인 데이터를 검색하는 쿼리와
# 소속사가 YG엔터테이먼트인 데이터를 검색하는 쿼리를 UNION ALL로 실행
SELECT name, sex, agency FROM celeb WHERE sex = 'F'
UNION ALL
SELECT name, sex, agency FROM celeb WHERE agency = 'YG엔터테이먼트';

# 가수가 직업인 연예인의 이름, 직업을 검색하는 쿼리와
# 1980년대에 태어난 연예인의 이름, 생년월일, 나이를 검색하는 쿼리를 UNION으로 실행
# 에러 발생 (가져오는 칼럼의 개수가 같아야 함, 종류는 상관 없음.)
SELECT name, job_title FROM celeb # 칼럼 2개
WHERE job_title LIKE '%가수%'
UNION
SELECT name, birthday, age FROM celeb # 칼럼 3개
WHERE birthday BETWEEN '1980-01-01' AND '1989-12-31';

# 직업이 가수인 (가수를 포함하는) 데이터를 검색하는 쿼리와
# 직업이 텔런트인 (텔런트를 포함하는) 데이터를 검색하는 쿼리를 중복을 제거하여 합쳐서 실행
SELECT * FROM celeb 
WHERE job_title LIKE '%가수%'
UNION
SELECT * FROM celeb 
WHERE job_title LIKE '%텔렌트%';

# 성이 이씨인 데이터를 검색하는 쿼리와 
# 1970년대생을 검색하는 쿼리를 중복을 포함하여 합쳐서 실행
SELECT * FROM celeb 
WHERE name LIKE '이%'
UNION ALL
SELECT * FROM celeb 
WHERE BETWEEN birthday '1970-01-01' AND '1979-12-31';

5. Union

• 실습환경 만들기

USE zerobase;

# 테이블 추가
CREATE TABLE snl_show
(
	ID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    SEASON int NOT NULL,
    EPISODE int NOT NULL,
    BROADCAST_DATE date,
    HOST varchar(32) NOT NULL
);

DESC snl_show;

# 데이터 추가
INSERT INTO snl_show VALUES (1, 8, 7, '2020-09-05', '강동원');
INSERT INTO snl_show VALUES (2, 8, 8, '2020-09-12', '유재석');
INSERT INTO snl_show VALUES (3, 8, 9, '2020-09-19', '차승원');
INSERT INTO snl_show VALUES (4, 8, 10, '2020-09-26', '이수현');
INSERT INTO Snl_show VALUES (5, 9, 1, '2021-09-04', '이병헌');
INSERT INTO Snl_show VALUES (6, 9, 2, '2021-09-11', '하지원');
INSERT INTO snl_show VALUES (7, 9, 3, '2021-09-18', '제시');
INSERT INTO snl_show VALUES (8, 9, 4, '2021-09-25', '조정석');
INSERT INTO snl_show VALUES (9, 9, 5, '2021-10-02', '조여정');
INSERT INTO snl_show VALUES (10, 9, 6, '2021-10-09', '옥주현');

SELECT * FROM snl_show;

• JOIN : 두 개 이상의 테이블을 결합하는 것
  
  
  

• INNER 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;

• LEFT JOIN : 두 개의 테이블에서 공통영역을 포함해 왼쪽 테이블의 다른 데이터를 포함하는 조인방식 (없는 값은 NULL로 표시)

# snl_show에 호스트로 출연한 celeb을 기준으로 
# celeb 테이블과 snl_show 테이블을 LEFT JOIN
SELECT celeb.id, celeb.name, snl_show.id, snl_show.host
FROM celeb # left table
LEFT JOIN snl_show # right table
ON celeb.name = snl_show.host;

• RIGHT JOIN : 두 개의 테이블에서 공통영역을 포함해 오른쪽 테이블의 다른 데이터를 포함하는 조인방식 (없는 값은 NULL로 표시)

# snl_show에 호스트로 출연한 celeb을 기준으로 
# celeb 테이블과 snl_show 테이블을 RIGHT JOIN
SELECT celeb.id, celeb.name, snl_show.id, snl_show.host
FROM celeb # left table
RIGHT JOIN snl_show # right table
ON celeb.name = snl_show.host;

• FULL OUTER JOIN : 두개의 테이블에서 공통영역을 포함하여 양쪽 테이블의 다른영역을 모두 포함하는 조인방식 (MySQL에서 지원하지 않음. 다른 DBMS에서 사용가능)

# MySQL 지원하지 않음.
SELECT celeb.id, celeb.name, snl_show.id, snl_show.host
FROM celeb # left table
FULL OUTER JOIN snl_show # right table
ON celeb.name = snl_show.host;

# MySQL에서 같은 결과 나타내는 쿼리
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; 

• SELF JOIN 문법 : INNER JOIN과 같은 기능

# << 예제 >> snl_show에 호스트로 출연한 celeb을 기준으로 
# celeb 테이블과 snl_show 테이블을 SELF JOIN
SELECT celeb.id, celeb.name, snl_show.id, snl_show.host
FROM celeb, snl_show # 테이블 나열, Left / Right 관계 없어짐.
WHERE celeb.name = snl_show.host; # ON

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

# STEP 1.
SELECT name, job_title FROM celeb WHERE agency='안테나';

# STEP 2.
SELECT celeb.name, snl_host.host
FROM celeb, snl_show
WHERE celeb.name = snl_show.host;

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

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

# STEP 1. celeb 테이블의 연예인 중, 영화배우는 아니면서
SELECT * FROM celeb
WHERE NOT job_title LIKE '%영화배우%';

# STEP 2. celeb 테이블의 연예인 중, 영화배우는 아니면서 YG엔터테이먼트 소속
SELECT * FROM celeb
WHERE NOT job_title LIKE '%영화배우%' AND agency = 'YG엔터테이먼트';

# STEP 3. celeb 테이블의 연예인 중, 40세 이상이면서
SELECT * FROM celeb
WHERE age >= 40;

# STEP 4. celeb 테이블의 연예인 중, 40세 이상이면서 YG엔터테이먼트 소속이 아닌
SELECT * FROM celeb
WHERE age >= 40 AND agency != 'YG엔터테이먼트';

# STEP 5. celeb 테이블의 연예인 중, snl_show에 host로 출연했고,
SELECT celeb.id, celeb.name, snl_show.host
FROM celeb, snl_show
WHERE celeb.name = snl_show.host;

# RESULT.
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 ((NOT job_title LIKE '%영화배우%' AND agency = 'YG엔터테이먼트')
    OR (age >= 40 AND agency != 'YG엔터테이먼트'));

# << 예제 >> snl_show에 출연한 연예인의 snl_show 아이디, 시즌, 에피소드, 이름, 직업 정보를 검색
SELECT snl_show.id, season, episode, name , job_title # id는 두테이블에서 중복됨.
FROM celeb, snl_show
WHERE name = host; # 고유의 컬럼은 앞에 테이블명 없어도 되지만, 테이블명.사용하는 것을 권장

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

# STEP 1. snl_show에 출연한 celeb 중,
SELECT name, season, episode, broadcast_date, agency
FROM celeb, snl_show
WHERE name = host;

# STEP 2. snl_show에 출연한 celeb 중, 에피소드 7, 9, 10 중에 출연했거나
SELECT name, season, episode, broadcast_date, agency
FROM celeb, snl_show
WHERE name = host AND episode IN (7, 9, 10);

# STEP 3. snl_show에 출연한 celeb 중, 소속사가 YG로 시작하고 뒤에 6글자로 끝나는 사람
SELECT name, season, episode, broadcast_date, agency
FROM celeb, snl_show
WHERE name = host AND agency LIKE 'YG______';

# STEP 4. snl_show에 출연한 celeb 중, 작년 9월15일 이후에 출연했던 사람
SELECT name, season, episode, broadcast_date, agency
FROM celeb, snl_show
WHERE name = host AND broadcast_date > '2020-09-15';

# RESULT.
SELECT name, season, episode, broadcast_date, agency
FROM celeb, snl_show
WHERE name = host
	AND (episode IN (7, 9, 10) OR agency LIKE 'YG______')
    AND broadcast_date > '2020-09-15';

# << 예제 >> snl_show에 출연한 celeb 테이블의 연예인 중,
# 영화배우나 텔런트가 아닌 연예인의 아이디, 이름, 직업, 시즌, 에피소드 정보를 검색
SELECT celeb.id, celeb.name, celeb.job_title, snl_show.season, snl_show.episode
FROM celeb, snl_show
WHERE celeb.name = snl_show.host
	AND NOT (job_title LIKE '%영화배우%' OR job_title LIKE '%텔런트%');
    
# << 예제 >> snl_show에 출연한 celeb 중, 작년 9월 15일 이후에 출연했거나
# 소속사 이름이 '엔터테이먼트'로 끝나지 않으면서
# 영화배우나 개그맨이 아닌 연예인의 celeb 아이디, 이름, 직업, 소속사를 검색

SELECT celeb.id, celeb.name, celeb.job_title, celeb.agency
FROM celeb, snl_show
WHERE celeb.name = snl_show.host 
	AND (snl_show.broadcast_date > '2020-09-15' OR NOT celeb.agency LIKE '%엔터테이먼트')
	AND NOT (celeb.job_title LIKE '%영화배우%' OR celeb.job_title LIKE '%개그맨%');

6. CONCAT, ALIAS, DISTINCT, LIMIT

• 실습환경 만들기

USE zerobase;

# 데이터 확인
SELECT * FROM celeb;
SELECT * FROM snl_show;

• CONCAT 문법 : 여러 문자열을 하나로 합치거나 연결

SELECT CONCAT('concat', ' ', 'test');
SELECT CONCAT('이름:' , name) FROM celeb; # 이름: 아이유

• ALIAS 문법 : 칼럼이나 테이블 이름에 별칭 생성
  - AS 생략 가능

# name을 이름으로 별칭을 만들어서 검색
SELECT name AS '이름' FROM celeb;

# name은 이름으로, agency는 소속사로 별칭을 만들어서 검색
SELECT name AS '이름', agency AS '소속사' FROM celeb;

# name과 job_title을 합쳐서 profile이라는 별칭을 만들어서 검색
SELECT CONCAT(name, ' : ', job_title) AS profile FROM celeb; # 아이유 : 가수, 텔렌트

# snl_show에 출연한 celeb을 기준으로 두 테이블을 조인하여,
# celeb 테이블은 c, snl_show 테이블은 s라는 별칭을 만들어서
# 출연한 시즌과 에피소드, 이름, 직업을 검색
SELECT s.season, s.episode, c.name, c.job_title
FROM celeb AS c, snl_show AS s
WHERE c.name = s.host;

# snl_show에 출연한 celeb을 기준으로 두 테이블을 조인하여,
# 다음과 같이 각 데이터의 별칭을 사용하여 검색
# - 시즌, 에피소드, 방송일을 합쳐서 '방송정보'
# - 이름, 직업을 합쳐서 '출연자정보'
SELECT CONCAT(s.season, '-', s.episode, '(', s.broadcast_date, ')') AS '방송정보',
	CONCAT(c.name, '(', c.job_title, ')') AS '출연자정보'
FROM celeb AS c, snl_show AS s
WHERE c.name = s.host;

# 이름이 3글자인 연예인 정보를 검색하여 다음과 같이 출력
SELECT CONCAT('이름 :', c.name, ', ', '소속사 :', c.agency) AS '연예인 정보'
FROM celeb AS c
WHERE c.name LIKE '___';

# 앞글자가 2글자이고, '엔터테이먼트'로 끝나는 소속사 연예인 중
# SNL에 출연한 연예인의 신상정보(나이, 성별)와 출연정보(시즌-에피소드, 방송날짜), 소속사 정보를 
# 방송날짜 최신순으로 정렬하여 다음과 같이 검색
SELECT c.agency AS '소속사 정보',
	CONCAT('나이 :', c.age, '(', c.sex, ')') AS '신상정보',
    CONCAT(s.season, '-', s.episode, ', 방송날짜 :', s.broadcast_date) AS '출연정보'
FROM celeb AS c, snl_show AS s
WHERE c.agency LIKE '__엔터테이먼트' AND c.name = s.host
ORDER BY s.broadcast_date DESC;

• DISTINCT 문법 : 검색한 결과의 중복 제거

# 연예인 소속사 종류를 검색 - 중복 포함
SELECT agency FROM celeb;

# 연예인 소속사 종류를 검색 - 중복 제거 (DISTINCT)
SELECT DISTINCT agency FROM celeb;

# 가수 중에서, 성별과 직업별 종류를 검색 - 중복 포함
SELECT sex, job_title FROM celeb WHERE job_title LIKE '%가수%';

# 가수 중에서, 성별과 직업별 종류를 검색 - 중복 제거 (DISTINCT)
SELECT DISTINCT sex, job_title FROM celeb WHERE job_title LIKE '%가수%';

# celeb 테이블에서 성별과 소속사별 종류를 검색하여 성별, 소속사 순으로 정렬
SELECT DISTINCT sex, agency FROM celeb ORDER BY sex, agency;

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

# celeb 데이터 3개만 가져오기
SELECT * FROM celeb LIMIT 3;

# 나이가 가장 적은 연예인 4명을 검색
SELECT * FROM celeb ORDER BY age LIMIT 4;

# celeb 테이블에서 남자 연예인 중 나이가 가장 많은 2명을 조회
SELECT * FROM celeb 
WHERE sex='M' 
ORDER BY age DESC 
LIMIT 2; 

# SNL에 출연한 연예인의 정보를 최신 방송날짜 순으로 2개만 검색하여 출력
SELECT CONCAT('SNL 시즌 ', s.season, ' 에피소드 ', s.episode, ' 호스트 ', c.name) AS 'SNL 방송정보',
	c.age
FROM celeb AS c, snl_show AS s
WHERE c.name = s.host
ORDER BY c.age DESC
LIMIT 2;