SELECT user_id, name, email from users u
where user_id in (
SELECT user_id from orders o
where payment_method = 'kakaopay'
)
select 절에 들어가는 subquery
SELECT c.checkin_id,
c.user_id,
c.likes,
(
SELECT avg(likes) from checkins c
WHERE user_id = c.user_id
) AS avg_likes_user
from checkins c;
from 절에 들어가는 subquery
SELECT pu.user_id, pu.point, a.avg_likes FROM point_users pu
inner join (
SELECT user_id, round(avg(likes),1) as avg_likes from checkins c
group by user_id
) a on pu.user_id = a.user_id;
[문제] 전체 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기
내가 쓴 쿼리 (작동 안 됨)
SELECT point_user_id, pu.point ROUND(avg(pu.point),1) FROM point_users pu
WHERE point > (
SELECT * FROM point_users pu
WHERE point
)
정답 쿼리
SELECT * FROM point_users pu
WHERE point > (
SELECT avg(point) FROM point_users
)
[문제] 이씨 성을 가진 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기
정답 쿼리
SELECT * from point_users pu
where point > (
SELECT round(avg(point),1) FROM point_users pu
inner join users u on pu.user_id = u.user_id
WHERE u.name = '이**'
)
[문제] checkins 테이블에 course_id별 평균 likes 수 필드 우측에 붙여보기
내가 쓴 쿼리 = 정답 쿼리
SELECT c.checkin_id,
c.course_id,
c.user_id,
c.likes,
(select round(avg(likes),1) from checkins
where course_id =c.course_id
) as course_avg
FROM checkins c
[문제] checkins 테이블에 과목명별 평균 likes 수 필드 우측에 붙여보기
정답 쿼리
SELECT c.checkin_id,
c1.title,
c.user_id,
c.likes,
(select round(avg(likes),1) from checkins
where course_id =c.course_id
) as course_avg
FROM checkins c
inner join courses c1 on c.course_id = c1.course_id;
[단계1] course_id별 유저의 체크인 개수를 구해보기
SELECT course_id, count(DISTINCT(user_id)) as cnt_checkins FROM checkins c
group by course_id
[단계2] course_id별 인원을 구해보기
SELECT course_id, count(*) as cnt_total FROM orders o
group by course_id
[최종 문제] course_id별 like 개수에 전체 인원을 붙이기
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 c
group by course_id
) a
inner join
(
SELECT course_id, count(*) as cnt_total FROM orders o
group by course_id
) b on a.course_id = b.course_id
inner join courses c on a.course_id = c.course_id
여러 정보를 한 눈에 보고 싶을 때 두 테이블의 공통된 정보(key 값)를 기준으로 연결해서 한 테이블처럼 보는 것.
ex) user_id 필드를 기준으로 users 테이블과 orders 테이블을 연결해서 보고 싶다.
SELECT * FROM users u
left join point_users p
on u.user_id = p.user_id
Left Join
A 데이터의 필드는 모두 채워져 있지만, B 데이터에는 비어있는 필드가 있다.
꽉찬 데이터: 해당 데이터의 user_id 필드값이 point_users 테이블에 존재해서 연결한 경우
비어있는 데이터: 해당 데이터의 user_id 필드값이 point_users 테이블에 존재하지 않는 경우
Inner Join
두 테이블의 교집합.
비어있는 필드가 있는 데이터가 없음.
그 이유는, 같은 user_id를 두 테이블이 모두 가지고 있는 데이터만! 출력했기 때문이다.
Union
select를 두 번 할 게 아니라, 한번에 모아보고 싶을 때
A 테이블과 B 테이블의 필드명이 같아야 함
union 안에 있으면 order by는 먹히지가 않음
[(A+order by)+(B+order by)] 가 아니라 [(A)+(B) order by] 는 됨
예시)
(
select '7월' as month, 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
)
UNION all
(
select '8월' as month, 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
)
[연습] orders 테이블에 users 테이블 연결하기
SELECT * FROM orders o
inner join users u
on o.user_id = u.user_id;
[연습] checkins 테이블에 users 테이블 연결하기
SELECT * FROM checkins c
inner join users u
on c.user_id = u.user_id;
[연습] enrolleds 테이블에 courses 테이블 연결하기
SELECT * FROM enrolleds e
inner join courses c
on e.course_id = c.course_id;
[연습] 과목별 오늘의 다짐 갯수 세어보기
SELECT c1.course_id, c2.title, COUNT(*) as cnt FROM checkins c1
inner join courses c2
on c1.course_id = c2.course_id
GROUP by c1.course_id
[연습] 많은 포인트를 얻은 순서대로 유저 데이터 정렬해서 보기
SELECT pu.user_id, u.name, u.email, pu.point FROM point_users pu
inner join users u
on pu.user_id = u.user_id
order by pu.point DESC
[연습] 네이버 이메일 사용하는 유저의 성씨별 주문건수 세어보기
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
[문제] 결제 수단 별 유저 포인트의 평균값 구해보기
내가 쓴 쿼리 = 정답 쿼리
SELECT o.payment_method, round(avg(pu.point),0) FROM point_users pu
inner join orders o on pu.user_id = o.user_id
group by o.payment_method;
[문제] 결제하고 시작하지 않은 유저들을 성씨별로 세어보기
내가 쓴 쿼리 = 정답 쿼리
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;
[문제] 과목 별로 시작하지 않은 유저들을 세어보기
내가 쓴 쿼리 = 정답 쿼리
select c.course_id, c.title, count(*) as cnt from courses c
inner join enrolleds e on c.course_id = e.course_id
WHERE e.is_registered = 0
group BY c.course_id;
[문제] 웹개발, 앱개발 종합반의 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 c2.week, c.title
order by c.title, c2.week;
[문제] 위 문제에서, 8월 1일 이후에 구매한 고객들만 발라내어 보기
내가 쓴 쿼리
select c.title, c2.week, o.created_at, count(*) 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 LIKE '2020-08%'
GROUP by c.title, c2.week
order by c.title, c2.week;
정답 쿼리
select c.title, c2.week, count(*) 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;
= 둘 다 나오는 값은 똑같음
[문제] 7월 10일 ~ 7월 19일에 가입한 고객 중, 포인트를 가진 고객의 숫자, 그리고 전체 숫자, 그리고 비율을 보기
정답 쿼리
SELECT count(pu.point_user_id) as pnt_user_cnt,
count(u.user_id) as tot_user_cnt,
round(count(pu.point_user_id)/count(u.user_id),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';
어려워서 가닥도 못 잡았음
[숙제] enrolled_id별 수강완료(done=1)한 강의 갯수를 세어보고, 완료한 강의 수가 많은 순서대로 정렬해보기.
user_id도 같이 출력되어야 한다.
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 done ='1'
group by e.enrolled_id, e.user_id
order by cnt desc;
SELECT week, min(likes) from checkins
group by week;
max(필드명)
: 동일한 범주에서 최댓값 구하기
예시) 주차별 '오늘의 다짐'의 좋아요 최댓값 구하기
SELECT week, max(likes) from checkins
group by week;
avg(필드명)
: 동일한 범주의 평균 구하기
예시) 주차별 '오늘의 다짐'의 좋아요 평균값 구하기
SELECT week, avg(likes) from checkins
group by week
sum(필드명)
: 동일한 범주의 합계 구하기
예시) 주차별 '오늘의 다짐' 좋아요 합계 구하기
SELECT week, sum(likes) from checkins
group by week
[문제] 앱개발 종합반의 결제수단별 주문건수 세어보기
내가 쓴 쿼리 = 정답 쿼리
SELECT payment_method, count(*) FROM orders
where course_title = '앱개발 종합반'
group by payment_method;
[문제] Gmail을 사용하는 성씨별 회원수 세어보기
내가 쓴 쿼리 = 정답 쿼리
SELECT name, count(*) FROM users
WHERE email LIKE '%gmail.com'
group by name;
[문제] course_id 별 '오늘의 다짐'에 달린 평균 like 갯수 구해보기
내가 쓴 쿼리 = 정답 쿼리
SELECT course_id , round(avg(likes),1) FROM checkins
group by course_id;
쿼리를 이렇게 정리하면 편하다!
(1) show tables로 어떤 테이블이 있는 지 살펴보기 (2) 제일 원하는 정보가 있을 것 같은 테이블에 select * from 테이블명 limit 10 쿼리 해 보기 (3) 원하는 정보가 없으면 다른 테이블에도 (2) 해 보기 (4) 테이블을 찾았으면 범주를 나눠서 보고싶은 필드를 찾기 (5) 범주별로 통계를 보고싶은 필드를 찾기 (6) SQL 쿼리 작성하기
select *from orders
where course_title ='앱개발 종합반' and payment_method='CARD';
※ 주의 ※
kakaopay, CARD의 경우 ' '나 " "안에 넣어야만 문자열로 인식이 된다. ' ', " "를 사용하지 않고 쓴다면 필드(칼럼)명이나 테이블명으로 인식되며 에러가 뜬다.
[문제] 포인트가 20000점보다 많은 유저만 뽑아보기
내가 쓴 쿼리 = 정답
select *from point_users
where point > 20000;
[문제]성이 황씨인 유저만 뽑아보기
내가 쓴 쿼리
select *from users
where name ='황';
정답 쿼리
select *from users
where name ='황**';
[문제]웹개발 종합반이면서 결제수단이 CARD인 주문건만 뽑아보기!
내가 쓴 쿼리 = 정답
select *from orders
where course_title ='웹개발 종합반' and payment_method ='CARD';
이렇게 작성하면 편하다! 꿀팁
1) show tables로 어떤 테이블이 있는지 살펴보기 2) 제일 원하는 정보가 있을 것 같은 테이블에 select * from 테이블명 쿼리 날려보기 3) 원하는 정보가 없으면 다른 테이블에도 2)를 해보기 4) 테이블을 찾았다! 조건을 걸 필드를 찾기 5) select * from 테이블명 where 조건
Where 절과 자주 같이쓰는 문법
'같지 않음' !=
예시) 웹개발 종합반을 제외하고 주문 데이터를 보기
select *from orders
where course_title !='웹개발 종합반';
'범위' between
예시) 7월 13일, 7월 14일의 주문데이터만 보기
select *from orders
where created_at between '2020-07-13' and '2020-07-15';
'포함' in
예시) 1,3주차 사람들의 '오늘의 다짐' 데이터만 보기
select *from checkins
where week in (1,3);
'패턴' like
예시) 다음 이메일을 사용한 유저만 보기
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로 끝나는 모든 데이터
[문제]결제수단이 CARD가 아닌 주문데이터만 추출해보기
내가 쓴 쿼리 = 정답
select *from orders
where payment_method =!'CARD';
[문제] 20000~30000 포인트 보유하고 있는 유저만 추출해보기
내가 쓴 쿼리 = 정답 쿼리
select *from point_users
where point between '20000' and '30000';
정답 쿼리
select *from point_users
where point between 20000 and 30000
[문제]이메일이 s로 시작하고 com로 끝나는 유저만 추출해보기
내가 쓴 쿼리 = 정답 쿼리
select *from users
where email like 's%com';
[문제]이메일이 s로 시작하고 com로 끝나면서 성이 이씨인 유저만 추출해보기
내가 쓴 쿼리
select *from users
where email like 's%com'; and name ='이**';
정답 쿼리
select *from users
where email like 's%com' and name='이**';
일부 데이터만 가져오기 Limit
테이블에 어떤 데이터가 들어있나 보려고 할 때, 데이터를 다 불러오느라 시간이 오래 걸릴 때 사용.
select *from orders
where payment_method = 'kakaopay'
limit 5;
중복 데이터는 제외하고 가져오기 Distinct
select distinct(payment_method) form orders;
몇 개인지 숫자 세보기 Count
테이블에 데이터가 몇 개 들어있는지 궁금하다면
select count(*) form orders
[응용] Distinct와 Count를 같이 써보기
회원들의 성씨가 몇개인지 궁금하다면?
select distinct(name) from users;
다음 단계
select count(distinct(name)) from users;
[퀴즈] 성이 '남'씨인 유저의 이메일만 추출하기
내가 쓴 쿼리
select *from users
where name ='남**' and email;
정답 쿼리
select email form users
where name ='남**';
[퀴즈] Gmail을 사용하는 2020/07/12~13에 가입한 유저 추출하기
내가 쓴 쿼리 = 정답 쿼리
select *from users
where created_at between '2020-07-12' and '2020-07-14' and email like '%gmail.com';
[퀴즈] Gmail을 사용하는 2020/07/12~13에 가입한 유저의 수 세기
내가 쓴 쿼리 = 정답 쿼리
select count(8) form users
where created_at between '2020-07-12' and '2020-07-14' and email like '%gmail.com';
