DB관리툴(디비버)
쿼리 작성하는 순서
1) show tables로 어떤 테이블이 있는지 살펴보기
2) 제일 원하는 정보가 있을 것 같은 테이블에 select * from 테이블명 limit 10 쿼리 날려보기
3) 원하는 정보가 없으면 다른 테이블에도 2)를 해보기
4) 테이블을 찾았다! 범주를 나눠서 보고싶은 필드를 찾기
5) 범주별로 통계를 보고싶은 필드를 찾기
6) SQL 쿼리 작성하기!
SQL - 1주차
SQL 식
show tables = 테이블 보여달라
-
select *(filed) from orders
orders(테이블)부터 모든 걸 보여달라 -
select * from orders
where payment_method = 'kakaopay'
orders(테이블)에 있는 payment_method(필드에)
'kakaopay' 유저만 가져와라 -
select * from point_users
where point >= 5000
point_users(테이블)에 있는 point(필드) 5000점(값) 이상 -
select * from orders
where course_title = '앱개발 종합반'
and payment_method = 'CARD'
orders(테이블)에 있는 course_title(필드) 앱개발 종합반(값)이면서
payment_method(필드) CARD(값) 가져와라
where 조건식
-
select * from orders
where course_title != '웹개발 종합반'
웹개발 종합반이 아닌 것 -
select * from orders
where created_at between '2020-07-13' and '2020-07-15'
몇부터 몇까지 보여달라 beween(7.13 and 7.14) -
select * from checkins
where week in (1,3)
1또는 3을 보여달라, in에 해당되는 거를 보여달라 -
select * from users
where email like '%daum.net'
users(테이블), email(필드), like
%화이팅% 화이팅만 있으면 상관 없음.
a%t 맨앞에 a있고 맨뒤에 t만 있으면 상관x
%앞에 뭐가 있든 daum.net 보여달라 -
select * from users
where email like 's%com'
and name = '이**'
users에서 email필드에서 s로 시작하며 com 끝나고 성이 이 씨인 경우만 -
select * from orders
where payment_method = 'kakaopay' limit 5
'kakaopay'인 것이 5개만 보여달라 -
select payment_method from orders
orders(테이블)에서 payment_method(필드만)보여달라 -
select DISTINCT (payment_method) from orders
orders(테이블)에서 paymeyment_method(필드만에서) 뭐가 있는지 보여달라(필드 값 하나씩만 )
kakaopay,card,taxbill,money 하나씩만 -
select count(*) from orders
orders(테이블)에서 오더가 몇개 있을까? -
select count(*) from orders where payment_method = 'kakaopay'
orders(테이블)에서 payment_method(필드)에서 kakaopay가 몇개일까? -
select count(distinct(payment_method)) from orders
orders(테이블)에서 payment_method(필드값)들이 몇개인가?
SQL - 2주차~3주차
- select name, count(*) from users
group by name(name 하나로 묶이는 애들만)
users(테이블)에서 각 name 개수를 세줘라
-naver.com으로 끝나는 name들을 묶어서 보여달라
select name, count(*) from users
where email like '%naver.com'
group by name
-week별로 묶었으니 week선택, likes최소값
(min최소값, max최댓값, avg평균값, sum합계)
round(avg(likes), 2) -둘째자리까지 반올림
select week, min(likes) from checkins
group by week
-오름차순
select name, count() from users
group by name
order by count()
-내림차순
select name, count() from users
group by name
order by count() desc
-orders(테이블)에서 course_title(필드)에서
웹개발 종합반이면서, payment(필드)를 보여줄 것인데,
각 개수를 세줘라
select payment_method , count(*) from orders
where course_title = '웹개발 종합반'
group by payment_method
-
userid 오름차순
select * from orders
order by user_id -
gamil.com을 사용하는 name개수를 보여달라
select name, count(*) from users
where email like '%gmail.com'
group by name
-checkins(테이블)에서 coursr_id필드를 묶어
likes개수를 보여달라 반올림 평균으로
select course_id, round(avg(likes)) from checkins
group by course_id
-앞으로 orders테이블을 o로 짧게 부르겠다.
o.course_title= orders테이블에 course_title이다
select * from orders o
where o.course_title ='앱개발 종합반'
-count() as cnt = count()를 cnt로 부르겠다
select payment_method , count(*) as cnt from orders o
where o.course_title ='앱개발 종합반'
group by payment_method
-o테이블에서 email을 네이버쓰고 앱개발 종합반의
결제방식 개수를 보여달라
select payment_method , count(*) as '개수' from orders o
where email like '%naver.com' and course_title = '앱개발 종합반'
group by payment_method
-두 개의 테이블 합치기 위해서는 기준이 필요하다
user_id = user_id
join의 종류: Left join, inner join
Left join: (레프트를 기준으로) point없는 것은 Null
u.user_id와 pu.user_id를 합쳐라
select * from users u
left join point_users pu
on u.user_id = pu.user_id
inner join: (교집합)point있는 거만 나옴
-테이블을 합쳐도 두개가 나옴(뭐로 합칠 건지 선택)
select c1.course_id from checkins c1
inner join courses c2 on c1.course_id = c2.course_id
group by c1.course_id
-courst.id와 title의 개수
select c1.course_id , c2.title, count(*) as '개수' from checkins c1
inner join courses c2 on c1.course_id = c2.course_id
group by c1.course_id
-등록 안 한 name들을 count()내림차순으로
select u.name, COUNT() from users u
inner join enrolleds e on u.user_id = e.user_id
where e.is_registered = 0
group by u.name
order by count(*) desc
-2개씩 묶어서 콤마로 나누기 가능
select c1.title, c2.week, count(*) from courses c1
inner join checkins c2 on c1.course_id = c2.course_id
group by c1.title, c2.week
order by c1.title, c2.week
-c1,c2.o 테이블에서 생산일이 2020-08-01 이상인 것을 보여달라
select c1.title, c2.week, count(*) as '개수' 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
-누구의 테이블을 선택하느냐에 따라 값이 달라질 수도 있음
NULL인 사람
select * from users u
left join point_users pu on u.user_id = pu.user_id
where pu.point_user_id is NULL
-point_user_id가 NULL이 아닌 사람
select u.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 u.name
-조건에 맞는 필드에서 총(count) ,
null이 아닌 것만(count(pu.point_user_id))
count는 null을 세지 않음
select count(pu.point_user_id) as pnt_user_pnt,count() as tot_user_cnt, round(count(pu.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'
7-8월을 이은다
(
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
)
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
)
- done =1 이고, e.enrolled_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 ed.done = 1
group by e.enrolled_id, e.user_id
order by cnt desc
SQL - 4주차
서브쿼리
select 서브쿼리
from 서브쿼리
where 서브쿼리
-users(테이블)에서 in에 해당되는 걸 보여달라
select name, email from users
where user_id in (
select user_id from orders
where payment_method = 'kakaopay'
)
-
checkins(테이블)에서 c.3필드를 보여주고,
(괄호)안에 있는 것도 보여달라
select c.checkin_id ,
c.user_id ,
c.likes,
(
select avg(likes) from checkins
where user_id = c.user_id
) //user_id 와 c.user_id가 같은 것의 평균
from checkins c -
chekins을 a라 칭해주고 pu와 join해준 다음에
pu.user_id와 포인트와 a테이블의 avg_likes를 보여줘라
select pu.user_id, pu.point, a.avg_likes from point_users pu
inner join (
select c.user_id, round(avg(likes),1) as avg_likes from checkins c
group by c.user_id
) a on pu.user_id = a.user_id
-point평균 보다 point가 큰 거롤
select * from point_users pu
where point > (
select round(avg(pu.point)) from point_users pu
)
-
where 서브쿼리
point가 이 씨 성보다 큰 사람들
select * from point_users pu
where point > (
select round(avg(pu.point)) from users u
inner join point_users pu on u.user_id = pu.user_id
where u.name = '이**'
) -
pu테이블 point가 pu테이블 user_id필드와 성이 이 씨인 것보다 큰 point를 추출
select * from point_users pu
where point > (
select avg(pu.point) from point_users pu
where user_id in (
select user_id from users where name = '이**'
)
select 서브쿼리
-
c.들을 하나씩 나열하고 서브쿼리 안에를 실행하자
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 '평균'
from checkins c -
select c.checkin_id ,
c2.title ,
c.user_id ,
c.likes ,
(
select ROUND(avg(likes),1) from checkins
where course_id = c.course_id
) as '평균'
from checkins c
inner join courses c2 on c.course_id = c2.course_id
-
두개의 테이블에서 조건에 맞게 추출 후
distinct user_id 중복 x
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 -
with절 이용해서 테이블 미리 만들기
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 문자열
-
@기준으로 -1은 오른쪽, 1은 왼쪽
select user_id , email, SUBSTRING_INDEX(email, '@', -1) from users -
첫번째부터 10까지 자르기
select order_no, SUBSTRING(created_at,1,10) as date from orders
SQL 조건문 CASE
- if절
select pu.user_id, pu.point,
(case when point > 10000 then '잘 하고 있어요!'
else '조금만 더 파이팅!' end)
from point_users pu
연습
select pu.point_user_id,
pu.point,
(case when point > (select ROUND(avg(point)) from point_users) then '잘 하고 있어요'
else '열심히 합시다' end) as msg
from point_users pu
- email 뒷자리만 나오게 해서 도메인이라는 변수를 주고 도메인이라는 걸 그룹 바이해서 하나씩 나오게 만듦
select SUBSTRING_INDEX(email,'@', -1) as 도메인, count(*) as cnt from users
group by 도메인
같은 방법 서브쿼리 사용
// users 테이블에서 도메인을 불러오고 도메인을 그룹바이 해서
select 도메인, count(*) as cnt
from (select SUBSTRING_INDEX(email,'@', -1) as 도메인 from users) a
group by 도메인
-화이팅 앞 뒤로 상관 없음 화이팅만 오면
select * from checkins
where comment like '%화이팅%'
select a.enrolled_id, b.done_cnt, a.total_cnt from (
select enrolled_id , count() as total_cnt
from enrolleds_detail
group by enrolled_id
) a
inner join
(
select enrolled_id , COUNT() as done_cnt
from enrolleds_detail
where done = 1
group by enrolled_id
) b on a.enrolled_id = b.enrolled_id
위와 같음
select enrolled_id ,
count(*),
sum(done)
from enrolleds_detail
group by enrolled_id
