[Select, Where 절]
- Where 절의 개념: Select 쿼리문으로 가져올 데이터에 조건을 걸어주는 것
- Select 쿼리문에 Where 절 함께 써보기
ex) orders 테이블에서 payment_method가 kakaopay인 것만 가져와줘!
→ select * from orders where payment_method = "kakaopay"
[Where 절과 자주 같이쓰는 문법]
- '같지 않음' 조건 걸어보기: '같지 않음' 조건은 != 로 걸 수 있습니다.
- '범위' 조건 걸어보기
ex) 7월 13일, 7월 14일의 주문데이터만 보고 싶어졌어요. 어떻게 해야 할까요?
→ select * from orders
where created_at between "2020-07-13" and "2020-07-15"
- '포함' 조건 걸어보기
ex) 1, 3주차 사람들의 '오늘의 다짐' 데이터만 보고 싶어졌어요.
→ select * from checkins
where week in (1, 3)
- '패턴' (문자열 규칙) 조건 걸어보기
ex) 다음 (daum) 이메일을 사용하는 유저만 보고 싶어졌어요. 어떻게 하죠?
→ select * from users
where email like '%daum.net'
<Like의 다양한 사용법>
☞ where email like 'a%': email 필드값이 a로 시작하는 모든 데이터
☞ where email like '%a' email 필드값이 a로 끝나는 모든 데이터
☞ where email like '%co%' email 필드값에 co를 포함하는 모든 데이터
☞ where email like 'a%o' email 필드값이 a로 시작하고 o로 끝나는 모든 데이터
[일부 데이터만 가져오기: Limit]
ex) 5개의 데이터만 출력
→ select * from orders
where payment_method = "kakaopay"
limit 5
[중복 데이터는 제외하고 가져오기: Distinct]
ex) 고객이 사용한 결제수단이 어떤 종류가 있을지 알고 싶어요.
→ select distinct(payment_method) from orders
[몇 개인지 숫자 세보기: Count]
ex) Orders 테이블에 데이터가 몇 개 들어있는지 궁금해요!
→ select count(*) from orders
[범주의 통계를 내주는 Group by]
ex) 성씨별 회원수를 Group by로 쉽게 구해보기
→ select name, count(*) from users
group by name
ex) group by를 사용해서 '신'씨를 가진 데이터가 몇 개인지 살펴보기
→ select name, count(*) from users
group by name
★Group by 기능 알아보기★
- 동일한 범주의 개수 구하기
ex) 주차별 '오늘의 다짐' 개수 구하기
→ select 범주별로 세어주고 싶은 필드명, count(*) from 테이블명
group by 범주별로 세어주고 싶은 필드명
- 동일한 범주에서의 최솟값 구하기
ex) 주차별 '오늘의 다짐'의 좋아요 최솟값 구하기
→ select 범주가 담긴 필드명, min(최솟값을 알고 싶은 필드명) from 테이블명
group by 범주가 담긴 필드명
- 동일한 범주에서의 최댓값 구하기
ex) 주차별 '오늘의 다짐'의 좋아요 최댓값 구하기
→ select 범주가 담긴 필드명, max(최댓값을 알고 싶은 필드명) from 테이블명
group by 범주가 담긴 필드명
- 동일한 범주의 평균 구하기
ex) 주차별 '오늘의 다짐'의 좋아요 평균값 구하기
→ select 범주가 담긴 필드명, avg(평균값을 알고 싶은 필드명) from 테이블명
group by 범주가 담긴 필드명
[Order by로 앞의 결과를 정렬]
- 원본 쿼리 살펴보기
→ select name, count(*) from users
group by name
- 결과의 개수 오름차순으로 정렬
→ select name, count(*) from users
group by name
order by count(*)
- 결과의 개수 내림차순으로 정렬
→ select name, count(*) from users
group by name
order by count(*) desc
※ Order by 제대로 알아보기: SQL 쿼리가 실행되는 순서
→ select name, count(*) from users
group by name
order by count(*)
※ Where와 Group by, Order by 함께 사용해보기]
ex) 웹개발 종합반의 결제수단별 주문건수 세어보기
→ select payment_method, count(*) from orders
where course_title = "웹개발 종합반"
group by payment_method
[여러 테이블을 연결해보자: Join]
ex) Join을 사용해서 Key값으로 두 테이블 연결
→ select * from point_users
left join users
on point_users.user_id = users.user_id
ex) 유저 데이터로 Inner Join 이해
→ select * from users u
inner join point_users p
on u.user_id = p.user_id;
ex) orders 테이블에 users 테이블 연결
→ select * from orders o
inner join users u
on o.user_id = u.user_id
ex) checkins 테이블에 users 테이블 연결
→ select * from checkins c
inner join users u
on c.user_id = u.user_id
ex) enrolleds 테이블에 courses 테이블 연결
→ select * from enrolleds e
inner join courses c
on e.course_id = c.course_id
▶ 배웠던 문법 join과 함께 연습해보기
ex) 과목별 오늘의 다짐 갯수 세어보기
→ select co.title, count(*) as checkin_count from checkins ci
inner join courses co
on ci.course_id = co.course_id
group by co.title
ex) 많은 포인트를 얻은 순서대로 유저 데이터 정렬
→ select * from point_users p
inner join users u
on p.user_id = u.user_id
order by p.point desc
ex) 네이버 이메일 사용하는 유저의 성씨별 주문건수 세어보기
→ select u.name, count(u.name) as count_name from orders o
inner join users u
on o.user_id = u.user_id
where u.email like '%naver.com'
group by u.name
[결과물 합치기! Union]
ex) 월별로 합치기
→ (
select '7월' as month, c.title, c2.week, count(*) as cnt from checkins c2
inner join courses c on c2.course_id = c.course_id
inner join orders o on o.user_id = c2.user_id
where o.created_at < '2020-08-01'
group by c2.course_id, c2.week
order by c2.course_id, c2.week
)
union all
(
select '8월' as month, c.title, c2.week, count(*) as cnt from checkins c2
inner join courses c on c2.course_id = c.course_id
inner join orders o on o.user_id = c2.user_id
where o.created_at >= '2020-08-01'
group by c2.course_id, c2.week
order by c2.course_id, c2.week
)
[원하는 데이터를 더 쉽게: Subquery]
▶ Where 에 들어가는 Subquery
ex) 카카오페이로 결제한 주문건 유저들만, 유저 테이블에서 출력
→ select * from users u
where u.user_id in (select o.user_id from orders o
where o.payment_method = 'kakaopay')
▶ Select 에 들어가는 Subquery
ex) '오늘의 다짐' 좋아요의 수가, 본인이 평소에 받았던 좋아요 수에 비해 얼마나 높고 낮은지 평균 구하기
→ select c.checkin_id, c.user_id, c.likes,
(select avg(likes) from checkins c2
where c2.user_id = c.user_id) as avg_like_user
from checkins c
▶ From 에 들어가는 Subquery (가장 많이 사용되는 유형!)
ex) 유저 별 좋아요 평균을 구한 후 해당 유저 별 포인트 보기
→ select pu.user_id, a.avg_like, pu.point from point_users pu
inner join (
select user_id, round(avg(likes),1) as avg_like from checkins
group by user_id
) a on pu.user_id = a.user_id
[With 절로 더 깔끔하게 쿼리문을 정리]
ex) 코스제목별 like 개수, 전체, 비율
before ☞ select c.title,
a.cnt_checkins,
b.cnt_total,
(a.cnt_checkins/b.cnt_total) as ratio
from
(
select course_id, count(distinct(user_id)) as cnt_checkins from checkins
group by course_id
) a
inner join
(
select course_id, count(*) as cnt_total from orders
group by course_id
) b on a.course_id = b.course_id
inner join courses c on a.course_id = c.course_id
after ☞ with table1 as (
select course_id, count(distinct(user_id)) as cnt_checkins from checkins
group by course_id
), table2 as (
select course_id, count(*) as cnt_total from orders
group by course_id
)
select c.title,
a.cnt_checkins,
b.cnt_total,
(a.cnt_checkins/b.cnt_total) as ratio
from table1 a inner join table2 b on a.course_id = b.course_id
inner join courses c on a.course_id = c.course_id
[실전에서 유용한 SQL 문법 : 문자열, Case]
- 문자열 데이터 다뤄보기
1) 문자열 쪼개보기
ex) 이메일에서 아이디만 가져와보기
→ select user_id, email, SUBSTRING_INDEX(email, '@', 1) from users
ex) 이메일에서 이메일 도메인만 가져와보기
→ select user_id, email, SUBSTRING_INDEX(email, '@', -1) from users
2) 문자열 일부만 출력하기
ex) orders 테이블에서 날짜까지 출력하게 해보기
→ select order_no, created_at, substring(created_at,1,10) as date from orders
ex) 일별로 몇 개씩 주문이 일어났는지 살펴보기
→ select substring(created_at,1,10) as date, count(*) as cnt_date from orders
group by date
- CASE: 경우에 따라 원하는 값을 새 필드에 출력해보기
ex) 포인트 보유액에 따라 다르게 표시해주기
→ select pu.point_user_id, pu.point,
case
when pu.point > 10000 then '잘 하고 있어요!'
else '조금 더 달려주세요!'
END as '구분'
from point_users pu
- CASE: 실전을 위한 트릭!
1. 우선 몇 가지로 구분을 나누고,
→ select pu.point_user_id, pu.point,
case
when pu.point >= 10000 then '1만 이상'
when pu.point >= 5000 then '5천 이상'
else '5천 미만'
END as level
from point_users pu
2. 서브쿼리를 이용해서 group by로 통계를 낼 수 있습니다.
→ select level, count(*) as cnt from (
select pu.point_user_id, pu.point,
case
when pu.point >= 10000 then '1만 이상'
when pu.point >= 5000 then '5천 이상'
else '5천 미만'
END as level
from point_users pu
) a
group by level
3. with 절과 함께
→ with table1 as (
select pu.point_user_id, pu.point,
case
when pu.point >= 10000 then '1만 이상'
when pu.point >= 5000 then '5천 이상'
else '5천 미만'
END as level
from point_users pu
)
select level, count(*) as cnt from table1
group by level
[연습문제]
Q. 성이 남씨인 유저의 이메일만 추출하기
A. select email from users
where name = "남**"
Q. Gmail을 사용하는 2020/07/12~13에 가입한 유저를 추출하기
A. select * from users
where created_at between "2020-07-12" and "2020-07-14"
and email like "%gmail.com"
Q. Gmail을 사용하는 2020/07/12~13에 가입한 유저의 수를 세기
A. select count(*) from users
where created_at between "2020-07-12" and "2020-07-14"
and email like "%gmail.com"
Q. naver 이메일을 사용하면서, 웹개발 종합반을 신청했고 결제는 kakaopay로 이뤄진 주문데이터 추출하기
A. select * from orders
where email like '%naver.com'
and course_title = '웹개발 종합반'
and payment_method = 'kakaopay'
Q. 네이버 이메일을 사용하여 앱개발 종합반을 신청한 주문의 결제수단별 주문건수 세어보기
A. select payment_method, count(*) from orders
where email like '%naver.com' and course_title = '앱개발 종합반'
group by payment_method
Q. 결제 수단 별 유저 포인트의 평균값 구해보기
A. 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
Q. 결제하고 시작하지 않은 유저들을 성씨별로 세어보기
A. select name, count(*) as cnt_name from enrolleds e
inner join users u
on e.user_id = u.user_id
where is_registered = 0
group by name
order by cnt_name desc
Q. 과목 별로 시작하지 않은 유저들을 세어보기
A. select c.course_id, c.title, count(*) as cnt_notstart from courses c
inner join enrolleds e
on c.course_id = e.course_id
where is_registered = 0
group by c.course_id
Q. 웹개발, 앱개발 종합반의 week 별 체크인 수를 세어보기
A. select c1.title, c2.week, count(*) as cnt from courses c1
inner join checkins c2 on c1.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 c1.title, c2.week
order by c1.title, c2.week
Q. 7월10일 ~ 7월19일에 가입한 고객 중, 포인트를 가진 고객의 숫자, 그리고 전체 숫자, 그리고 비율을 보고 싶어요!
A. select count(point_user_id) as pnt_user_cnt,
count(*) as tot_user_cnt,
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'
Q. enrolled_id별 수강완료(done=1)한 강의 갯수를 세어보고, 완료한 강의 수가 많은 순서대로 정렬해보기.(user_id도 같이 출력되어야 한다.)
A. select e.enrolled_id,
e.user_id,
count(*) as cnt
from enrolleds e
inner join enrolleds_detail ed on e.enrolled_id = ed.enrolled_id
where ed.done = 1
group by e.enrolled_id, e.user_id
order by cnt desc
Q. 평균 이상 포인트를 가지고 있으면 '잘 하고 있어요' / 낮으면 '열심히 합시다!' 표시하기!
A. select pu.point_user_id, pu.point,
case
when pu.point > (select avg(pu2.point) from point_users pu2) then '잘 하고 있어요!'
else '열심히 합시다!'
end as 'msg'
from point_users pu
Q. 이메일 도메인별 유저의 수 세어보기
A. select domain, count(*) as cnt from (
select SUBSTRING_INDEX(email,'@',-1) as domain from users
) a
group by domain
Q. '화이팅'이 포함된 오늘의 다짐만 출력해보기
A. select * from checkins c
where c.comment like '%화이팅%'
Q. 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수 출력해보기
A. with lecture_done as (
select enrolled_id, count(*) as cnt_done from enrolleds_detail ed
where done = 1
group by enrolled_id
), lecture_total as (
select enrolled_id, count(*) as cnt_total from enrolleds_detail ed
group by enrolled_id
)
select a.enrolled_id, a.cnt_done, b.cnt_total from lecture_done a
inner join lecture_total b on a.enrolled_id = b.enrolled_id
Q. 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수, 그리고 진도율 출력해보기
A. with table1 as (
select enrolled_id, count(*) as done_cnt from enrolleds_detail
where done = 1
group by enrolled_id
), table2 as (
select enrolled_id, count(*) as total_cnt from enrolleds_detail
group by enrolled_id
)
select a.enrolled_id,
a.done_cnt,
b.total_cnt,
round(a.done_cnt/b.total_cnt,2) as ratio
from table1 a
inner join table2 b on a.enrolled_id = b.enrolled_id