SQL 기초 10~14

Union

• 실습 환경 만들기

use 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);

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

select * from test1 union all select * from 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엔터테이먼트';

• 성별이 여자인 데이터를 검색하는 쿼리와 소속사가 YG인 데이터를 검색하는 쿼리를 Union 로 실행

select name,sex,agency from celeb where sex='F'
union 
select name,sex,agency from celeb where agency='YG엔터테이먼트';

• 가수가 직업인 연예인의 이름,직업을 검색하는 쿼리와 1980년대 태어난 연예인의 이름 생년월일 나이를 검색하는 쿼리를 union 실행

select name,job_title from celeb where job_title='%가수%'
union
select name,birthday,age from celeb where birthday between 1980-01-01 and 1989-12-31;

첫번째 와 두번째의 컬럼의 수가 다르기 때문에 ERROR 발생

연습

1. 직업이 가수인(가수를 포함하는) 데이터를 검색하는 쿼리와 직업이 탤런트(포함) 데이터를 검색하는 쿼리를 중복을 제거하여 합쳐서 실행

select name,job_title from celeb where job_title like '%가수%'
union
select name,job_title from celeb where job_title like '%탤런트%';

2. 성이 이씨인 데이터를 검색하는 쿼리와 1970년대생을 검색하는 쿼리를 중복을 포함하여 합쳐서 실행 ( birthday 가 데이터타입을 바꿔야함)

select name,birthday from celeb where name like '이%'
union all
select name,birthday from celeb where birthday between '1970-01-01' and '1979-12-31';

#데이터 타입 바꾸기
alter thble celeb change column brithday birthday date;

join

• 실습환경 만들기

1. 새로운 테이블 추가

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;

2. 데이터 추가

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','옥주현');

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

1. insert 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; #조인할 기준

2. 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;  #출연한 적이 있는 정보 + 출연한 적이 없는 정보까지

3. 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;

4. full outer 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;

full outer join 은 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;

5. self join : from 에서 가져올 테이블이 여러개 일 시 여러개를 넣고 where 절에 조건을 주면 조인이 가능함.

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;

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

select name,job_title
from celeb,snl_show
where celeb.name=snl_show.host and celeb.agency='안테나';

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 ((not celeb.job_title like '%영화배우%' and celeb.agency='YG엔터테이먼트') or (celeb.age>=40 and not celeb.agency='YG엔터테이먼트'));

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;

컬럼명만 명시하기(동일한 컬럼의 경우 에러발생)

select id,season,episode,name,job_title
from celeb,snl_show
where name=host; #ID가 같은 컬럼 명이기때문에 에러

동일한 컬럼명이 존재하는 경우 테이블 명 기재

select snl_show.id,season,episode,name,job_title
from celeb,snl_show
where name=host;

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

select name,season,episode,broadcast_date,agency
from snl_show,celeb
where name=host and (episode in (7,9,10) or agency like 'YG______') and broadcast_date > '2020-09-15';

연습

1. snl_show에 출연한 celeb 테이블의 연예인 중,영화배우나 탤런트가 아닌 연예인의 아이디,이름,직업,시즌,에피소드 정보를 검색

select celeb.id,name,job_title,season,episode
from celeb,snl_show
where name=host and not(job_title like '%영화배우%' or job_title like '%탤런트%');

2. snl_show 에 출연한 celeb 중 2020년 9월 15 일 이후 출연했거나 소속사 이름이 엔터테이먼트로 끝나지 않으면서 영화배우나 개그맨이 아닌 연예인의 celeb id, 이름 직업 소속사 검색

select celeb.id,name,job_title,agency
from celeb,snl_show
where name=host and 
(broadcast_date>'2020-09-15' or not agency like '%엔터테이먼트') and not (job_title like '%영화배우%' or job_title like '%개그맨%');

Concat,alias,distinct,limit.

• 실습 환경 만들기

use zerobase;
select* from celeb;
select * from snl_show;

1. concat : 여러문자열을 하나로 합치거나 연결

select concat('concat',' ','test');

이름 데이터 연결하기

select concat('이름:',name) from celeb;

2. ALIAS : 칼럼이나 테이블 이름에 별칭 생성 (python as같은)

• 컬럼에 별칭 생성
  

select column as alias
from tablename;

• 테이블 이름에 별칭 생성

select column1,column2,...
from tablename as alias

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 season,episode,name,job_title 
from celeb as c,snl_show as s
where c.name=s.host;

snl_show 에 출연한 celeb 을 기준으로 두 테이블을 조인하여 다음과 같이 데이터의 별칭을 사용하여 검색

• 시즌,에피소드,방송일을 합쳐서 '방송정보'
• 이름, 직업을 합쳐서 '출연자 정보'

select concat (season,'-',episode,'(',broadcast_date,')') as '방송정보',
concat (name,':',job_title) as '출연자 정보'
from celeb as c,snl_show as s
where c.name=s.host;

as는 생략 할 수 있다.

select concat (season,'-',episode,'(',broadcast_date,')') as '방송정보',
concat (name,':',job_title) as '출연자 정보'
from celeb c,snl_show s
where c.name=s.host;

3. distinct : 검색한 결과의 중복 제거

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

select distinct agency from celeb;

가수 중 성별과 직업별 종류를 검색 -중복 제거

select distinct sex,job_title from celeb 
where job_title like '%가수%';

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

celeb 데이터 3개만 가져오기

select * from celeb limit 3;

나이가 가장 적은 연예인 4명

select * from celeb order by age limit 4;

연습

1. 이름이 3글자인 연예인 정보 검색하기

select concat('이름',':',name,',','소속사',':',agency) as '연예인 정보'
from celeb
where name like '___';

2. 앞글자가 2글자 이고, 엔터테이먼트로 끝나는 소속사 연예인 중 snl에 출연한 연예인의 나이,성별 과 시즌-에피소드,방송날짜, 소속사 정보를 방송날짜 최신순 으로 정렬

select agency as '소속사 성보',concat('나이 :',age,'(',sex,')') as '신상정보',
concat(season,'-',episode,',','방송날짜 :',broadcast_date) as '출연정보'
from celeb as c,snl_show as s
where c.name=s.host and agency like '__엔터테이먼트'
order by broadcast_date desc;

3. celeb 테이블에서 성별과 소속사 별 종류를 검색하여 성별,소속사 순으로 정렬하기

select distinct sex, agency 
from celeb
order by sex,agency;

4. 남자 연예인 중 나이가 가장 많은 2명

select *
from celeb
where sex='M'
order by age desc
limit 2;

snl에 출연한 연예인의 정보를 최신 방송 날짜 순으로 2개만 검색

select concat('SNL 시즌',season,'에피소드',episode,'호스트',host) as 'SNL 방송정보',age
from celeb,snl_show
where celeb.name=snl_show.host
order by broadcast_date asc
limit 2;