기초 SQL문

오랜만의 쿼리문:

 

[쿼리 작성 순서]

1) show tables로 어떤 테이블이 있는지 보기

2) 제일 원하는 정보가 있을 것 같은 테이블에 select * from 테이블명 limit 10 

3) 원하는 정보가 없으면 다른 테이블에도 2)를 해보기

4) 테이블을 찾았다! 범주를 나눠서 보고싶은 필드를 찾기 (where)

5) 범주별로 통계를 보고싶은 필드를 찾기

6) SQL 쿼리 작성하기!

SELECT * from orders o

where email like '%naver.com'

and course_title = 'SQL 기초'

and payment_method ='kakaopay'

 

 

SELECT * from orders o

limit 5(우선 어떻게 생겼나 보게 5개만 보여줘)

 

 SELECT * from orders o

where date BETWEEN '2023-03-15' and '2023-03-16'

 

SELECT count(distinct(course_title)) from orders o

where email like '%naver.com'

and payment_method !='kakaopay'

 

 

 

[별칭 기능: Alias]

select payment_method, count(*) as cnt from orders o
where o.course_title = '앱개발 종합반'
group by payment_method

 

 

SELECT payment_method, count(*) FROM orders o

where course_title = '앱개발 종합반'

group by payment_method;

SELECT name, count(*) FROM users u

where email LIKE '%gmail.com'

group BY name;

SELECT course_id, round(AVG(likes),1) from checkins c

group by course_id

 

SELECT payment_method, count(*) FROM orders o

where course_title = '앱개발 종합반'

group by payment_method;

 

SELECT name, count(*) FROM users u

where email LIKE '%gmail.com'

group BY name;

SELECT course_id, round(AVG(likes),1) from checkins c

group by course_id

 

SELECT payment_method as '결제수단', count(*) as '주문건수' FROM orders o

where email like '%naver.com'

and course_title = '앱개발 종합반'

group by payment_method

합집합 (포인트없는 유저도 보여줌)

select * from users u

left join point_users p

on u.user_id =p.user_id

 

 

교집합

select * from users u

inner join point_users p

on u.user_id =p.user_id

 

SELECT * FROM orders o

inner join users u

on o.user_id = u.user_id;

 

SELECT * FROM checkins c

inner join users u

on c.user_id = u.user_id ;

 

SELECT * FROM enrolleds e

inner join courses c

on e.course_id = c.course_id

 

SELECT u.name, count(*) as 주문횟수 FROM users u

inner join orders o on u.user_id =o.user_id

where u.email LIKE '%naver.com'

group by u.name

 

 

SELECT o.payment_method, AVG(p.`point`) FROM orders o

inner join point_users p on o.user_id = p.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.title, count(*) as 시작전유저수 FROM enrolleds e

inner join courses c on e.course_id = c.course_id

where e.is_registered =0

group by c.title

 

SELECT c2.title, c1.week, count(*) FROM checkins c1

inner join courses c2 on c1.course_id = c2.course_id

group by c1.week, c2.title

order by c1.week, c2.title

 

 

SELECT * FROM users u

left join point_users pu on u.user_id = pu.user_id

where pu.point_user_id is NULL

group by u.name

 

 

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'

 

 

(
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
)

 

- union 하면 내부정렬이 안 먹는다. 

해결책: 서브쿼리

SELECT * FROM users

where user_id in (

SELECT user_id from orders

where payment_method = 'kakaopay'

)

 

select * from users u
where u.user_id in (select o.user_id from orders o 
where o.payment_method = 'kakaopay');

 

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;

 

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

 

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 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

 

문자열 쪼개기

SUBSTRING_INDEX

 

select user_id, email, SUBSTRING_INDEX(email, '@', 1) from users

email을 @를 기준으로  쪼개서 그 중 첫 번째 조각을 보여줘.

 

 

select substring(created_at,1,10) as date, count(*) as cnt_date from orders
group by date

 

 

select pu.user_id, pu.point,

(case when pu.point >10000 then '굿'

else '에헤이~' end) as msg

from point_users pu

 

SELECT pu.user_id,

pu.point,

(case when point > (SELECT avg(point) from point_users pu ) then '잘 하고 있어요!'

else '열심히 합시다!' end) as msg

from point_users pu;

 

select substring_index(email,'@',-1) as domain,count(*) as cnt

FROM users u

group by domain

 

 

 substring까지 쓸 필요 없는 경우:

SELECT DISTINCT(comment) from checkins c

where comment LIKE '%화이팅%'

 

 

 

select a.enrolled_id, a.cnt as done_cnt, b.cnt as total_cnt

from (SELECT e.enrolled_id, count(*) cnt from enrolleds e

left join enrolleds_detail ed on e.enrolled_id = ed.enrolled_id

group by e.enrolled_id) a

inner join (SELECT e.enrolled_id, count(*) cnt from enrolleds e

left join enrolleds_detail ed on e.enrolled_id = ed.enrolled_id

where done = 1

group by e.enrolled_id) b

on a.enrolled_id = b.enrolled_id

 

똑같은 걸 with절을 쓰면:

with table1 as (

SELECT e.enrolled_id, count(*) cnt from enrolleds e

left join enrolleds_detail ed on e.enrolled_id = ed.enrolled_id

group by e.enrolled_id

), table2 as (

SELECT e.enrolled_id, count(*) cnt from enrolleds e

left join enrolleds_detail ed on e.enrolled_id = ed.enrolled_id

where done = 1

group by e.enrolled_id

)

select a.enrolled_id, a.cnt as done_cnt, b.cnt as total_cnt

from table1 a

inner join table2 b

on a.enrolled_id = b.enrolled_id

 

더 간단하게는

 

SELECT enrolled_id,

sum(done) as done_cnt,

count(*) as total_cnt,

round(sum(done)/count(*),2) as ratio

from enrolleds_detail

group by enrolled_id