SQL

핵심 Key word 와 예시문으로 작성하였다.

* group by

select name, count(*) from users
group by name

(count, max, min, avg 등 사용)

* order by

select name, count() from users
group by name
order by count()

(desc 쓰면 내림차순)

* where

select payment_method, count() from orders
where course_title = '웹개발 종합반'
group by payment_method
order by count() desc

* join

select e.enrolled_id , e.user_id , count() as max_count from enrolleds e
join enrolleds_detail ed on e.enrolled_id = ed.enrolled_id
where ed.done = 1
group by e.enrolled_id
order by count() desc

(left join 은 합집합 개념)

* subquery

(where 절)
select * from point_users pu
where point >
(
select round(avg(pu.point),1) from point_users pu
inner join users u on pu.user_id = u.user_id
where u.name = '이**'
)

(select 절)
select c.checkin_id,
c.course_id,
c.user_id,
c.likes
(
select avg(likes) from checkins c
where course_id = c.course_id
)
from checkins c

(from 절)
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(course_id) as cnt_total from orders
group by course_id
) b on a.course_id = b.course_id

* with

* substring_index, substring (split)

* case when then else end