SQL 3주차 단어/문법 정리
Key
: 데이터의 연결이 필요한 두 테이블에 공통으로 존재하며 연결해주는 역할을 할 수 있는 필드
+) 한 테이블에 모든 정보를 담을 수도 있겠지만, 불필요하게 테이블의 크기가 커지기 때문에,
데이터를 종류별로 쪼개 다른 테이블에 담아놓고 연결이 필요한 경우 활용이 가능하도록 만들어놓는다.
Join
: 두 테이블의 Key값을 기준으로 테이블을 연결해서 한 테이블처럼 보는 것을 의미한다.
+) 엑셀에서의 VLOOKUP과 같다.
- Join 문법 정리 -
Inner join / on ~
: A 테이블에 B 테이블을 Inner join할 경우, 조회 테이블에 NULL 데이터가 없다.
= Key 필드로 연결 후, A 테이블의 필드 데이터와 B 테이블의 필드 데이터를
inner join하여 조회 테이블에서 공백이 없는 데이터만 보여준다.
select * from 테이블명 Alias
inner join 테이블명2 Alias2
on Alias.Key필드명 = Alias2.Key필드명+) from 뒤엔 연결의 기준이 되는 테이블A를,
inner join 뒤엔 붙이고 싶은 테이블B를 적는다.
그 후 ' on A.Key필드 = B.Key필드 ' 로 이어주면 된다.
- enrolleds 테이블에 courses 테이블을 inner join 해보기 -
select * from enrolleds e
inner join courses c
on e.course_id = c.course_id> 쿼리 실행 순서
1) enrolleds 테이블 데이터를 가져온다.
( from enrolleds e )
2) courses 테이블을 가져와서 course_id라는 동일한 필드(Key)를 기준으로 enrolleds 테이블에 붙인다.
( inner join courses c
on e.course_id = c.course_id )
3) 모든 데이터를 조회한다.
( select * )
> 결과값
Left join / on ~
: A 테이블에 B 테이블을 Left join할 경우, 비어있는 데이터(NULL)가 생길 수 있다.
= Key 필드로 연결 후, A 테이블의 필드에 맞는 B 테이블의 필드 데이터의 유무에 따라
Left Join 후 조회 테이블의 데이터의 유무 차이가 생긴다.
select * from 테이블명 Alias
left join 테이블명2 Alias2
on Alias.Key필드명 = Alias2.Key필드명+) Left join은 from 테이블과 join 테이블의 위치가 중요하다.
from 테이블을 기준으로 join 테이블의 데이터를 붙이기 때문이다.
+) Count는 Null을 세지 않는다.
- users 테이블에 point_users 테이블을 left join하기 -
select * from users u
left join point_users pu
on u.user_id = pu.user_id
> 결과값
Left join
- is Null
: where절로 Null이 존재하는 필드에서 사용시 Null인 데이터만 조회된다.
select * from 테이블명 Alias
left join 테이블명2 Alias2
on Alias.Key필드명 = Alias2.Key필드명
where Alias.(Null이 존재하는 필드) is Null
- 유저 중 포인트를 보유하지 않은 사람의 통계 -
select name, count(*) from users u
left join point_users pu
on u.user_id = pu.user_id
where pu.point_user_id is NULL
group by name
> 결과값
Left join
- is not Null
: where절로 Null이 존재하는 필드에서 사용시 Null이 아닌 데이터만 조회된다.
select * from 테이블명 Alias
left join 테이블명2 Alias2
on Alias.Key필드명 = Alias2.Key필드명
where Alias.(Null이 존재하는 필드) is not Null
- 유저 중 포인트를 보유하고 있는 사람의 통계 -
select name, count(*) from users u
left join point_users pu
on u.user_id = pu.user_id
where pu.point_user_id is not NULL
group by name
> 결과값
- 이 외 유용한 문법 -
Round
: 소수를 반올림하여 출력해주는 명령어, Round(숫자,자릿수)로 적는다.
select round((필드명),출력할 소수점 자리의 갯수) from 테이블명
group by 필드명
- 결제수단별 유저 포인트의 평균값 구해보기 -
select o.payment_method, round(AVG(p.point)) from point_users p
inner join orders o
on p.user_id = o.user_id
group by o.payment_method- Avg
: 평균을 출력해준다.
Union // all
: Select 두 번의 결과를 한 번에 모아서 출력해준다.
+) Union으로 합칠 두 개의 Select 출력 필드명이 같아야한다.
- 8월에 웹개발 앱개발반을 수강시작한 유저의 수와 결제수단을 출력하기 -
SELECT '8월' as month,
o.course_title,
o.payment_method,
count(o.created_at) as start_user
from orders o
inner join users u
on o.user_id = u.user_id
where o.created_at >= '2020-08-01'
group by payment_method
- 7월에 웹개발 앱개발반을 수강시작한 유저의 수와 결제수단을 출력하기 -
SELECT '8월' as month,
o.course_title,
o.payment_method,
count(o.created_at) as start_user
from orders o
inner join users u
on o.user_id = u.user_id
where o.created_at >= '2020-08-01'
group by payment_method
- 7월과 8월, 각각 웹개발 앱개발반을 수강시작한 유저의 수와 결제수단을 한 번에 출력하기 -
(
SELECT '7월' as month,
o.course_title,
o.payment_method,
count(u.user_id) as start_user
from orders o
inner join users u
on o.user_id = u.user_id
where o.created_at < '2020-08-01'
group by payment_method
)
union all
(
SELECT '8월' as month,
o.course_title,
o.payment_method,
count(o.created_at) as start_user
from orders o
inner join users u
on o.user_id = u.user_id
where o.created_at >= '2020-08-01'
group by payment_method
)
+) Union을 사용하면 내부정렬(order by)이 적용되지 않는다.
그렇기 때문에 SubQuery(4주차)를 사용해야 한다.
- 복습하기 -
[ inner join ]
- '오늘의 다짐' 정보에 과목 정보를 연결해 과목별 '오늘의 다짐' 갯수를 세어보기 -
SELECT c2.title, count(*) as cnt from checkins c
inner join courses c2
on c.course_id = c2.course_id
group by c2.title
[ inner join, order by ]
- 많은 포인트를 얻은 순서대로 유저의 데이터를 뽑아보기 -
SELECT u.user_id, point from point_users pu
inner join users u
on pu.user_id = u.user_id
order by pu.point desc
[ inner join, like, group by, count ]
- 네이버 이메일을 사용하는 유저 중, 성씨별 주문건수를 세어보기 -
SELECT u.name, count(*) from orders o
inner join users u
on o.user_id = u.user_id
where o.email like '%naver.com'
group by u.name
[ inner join, =, group by, order by, count ]
- 결제하고 시작하지 않은 유저들을 성씨별, 많은 순으로 세어보기 -
SELECT u.name, count(*) as cnt from enrolleds e
inner join users u
on e.user_id = u.user_id
where e.is_registered = 0
group by u.name
order by cnt desc
[ inner join, =, group by, count ]
- 과목 별로 시작하지 않은 유저들을 세어보기 -
SELECT c.title, count(*) from courses c
inner join enrolleds e
on c.course_id = e.course_id
where e.is_registered = 0
group by c.title
[ inner join, group by, order by, count ]
- 웹개발, 앱개발 종합반의 week 별 체크인 수를 세고 보기 좋게 정리해보기 -
SELECT c.title, c2.week, count(*) as cnt from courses c
inner join checkins c2
on c.course_id = c2.course_id
group by c.title, c2.week
order by c.title, c2.week
+) group by와 order by에 다수의 필드를 사용해야 할 때는 콤마를 이용한다.
ex) group by c.title, c2.week
order by c.title, c2.week
[ inner join, >= ]
- 윗 문제에서 8월 1일 이후에 구매한 고객들만 골라내기 -
SELECT c.title, c2.week, count(*) as cnt from courses c
inner join checkins c2
on c.course_id = c2.course_id
inner join orders o
on c2.user_id = o.user_id
where o.created_at >= '2020-08-01'
group by c.title, c2.week
order by c.title, c2.week
+) 8월 1일 이후에 구매한 고객의 데이터는 orders 테이블에 저장되어 있기 때문에,
courses 테이블과 checkins 테이블 중 Key가 있는 checkins의 테이블에 orders의 테이블을 합친 후
orders의 테이블에 속해있던 주문데이터에 where절로 8월 1일과 같거나 그 이상(>=)의 조건을 걸어
출력값에 8월 1일 이후에 주문한 고객 데이터만 조회할 수 있도록 하였다.
[ left join, where, between, count, round, alias ]
- 7/10 ~ 7/19에 가입한 고객 중 포인트를 가진 고객의 수, 전체 수, 비율을 조회하기 -
SELECT count(point_user_id) as pnt_user,
count(*) as total_user,
round(count(point_user_id)/count(*),2) as ratio
from users u
left join point_users pu on u.user_id = pu.user_id
where u.created_at between '2020-07-10' and '2020-07-20'
+) Count는 Null을 세지 않는다.
예제처럼 round(필드값,2)를 해주면 소수점 둘째 자리까지 반올림이 되어 출력된다.