subquery
where, select,from, inner join
with 절
case, 문자열
select * from orders;
select created_at, course_title, payment_method, email from orders;
select * from users
where name = "황**";
select * from orders
where created_at between "2020-07-13" and "2020-07-15";
select * from checkins
where week in (1, 3);
select * from users
where email like '%daum.net';
select * from point_users
where point between 20000 and 30000
select * from users
where email like 's%com';
select * from users
where email like 's%com' and name = "이**";
select * from orders
where payment_method = "kakaopay"
limit 5;
select distinct(payment_method) from orders;
select count(*) from orders
select distinct(name) from users;
select email from users
where name = "남**";
select * from users
where created_at between "2020-07-12" and "2020-07-14"
and email like "%gmail.com";
select count(*) from users
where created_at between "2020-07-12" and "2020-07-14"
and email like "%gmail.com";
select * from orders
where email like '%naver.com'
and course_title = '웹개발 종합반'
and payment_method = 'kakaopay'
select name, count(*) from users
group by name;
select * from users
where name = "신**";
select name, count(*) from users
group by name;
select 범주별로 세어주고 싶은 필드명, count(*) from 테이블명
group by 범주별로 세어주고 싶은 필드명;
select 범주가 담긴 필드명, min(최솟값을 알고 싶은 필드명) from 테이블명
group by 범주가 담긴 필드명;
select 범주가 담긴 필드명, max(최댓값을 알고 싶은 필드명) from 테이블명
group by 범주가 담긴 필드명;
select 범주가 담긴 필드명, avg(평균값을 알고 싶은 필드명) from 테이블명
group by 범주가 담긴 필드명;
select 범주가 담긴 필드명, sum(합계를 알고 싶은 필드명) from 테이블명
group 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;
select * from checkins
order by likes desc;
select * from 테이블명
order by 정렬의 기준이 될 필드명;
select name, count(*) from users
group by name
order by count(*);
select payment_method, count(*) from orders
where course_title = "웹개발 종합반"
group by payment_method;
select * from users
order by email;
select * from users
order by name;
select * from users
order by created_at desc;
select name, count(*) from users
where email like '%gmail.com'
group by name;
select name, count(*) from users
where email like '%gmail.com'
group by name;
- 별칭 기능: Alias</aside>
- <aside> 👉 쿼리가 점점 길어지면서 종종 헷갈리는 일이 생길 수 있습니다. 그래서 SQL은 Alias라는 별칭 기능을 지원합니다.
select payment_method, count(*) from orders
where email like '%naver.com' and course_title = '앱개발 종합반'
group by payment_method
select * from enrolleds e
inner join courses c
on e.course_id = c.course_id;
select co.title, count(co.title) as checkin_count from checkins ci
inner join courses co
on ci.course_id = co.course_id
group by co.title
select * from point_users p
inner join users u
on p.user_id = u.user_id
order by p.point desc
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
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
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
select c1.title, c2.week, count(*) as cnt from checkins c2
inner join courses c1 on c2.course_id = c1.course_id
group by c1.title c2.week
order by c1.title, c2.week
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
select * from users u
left join point_users pu on u.user_id = pu.user_id
select name, count(*) from users u
left join point_users pu on u.user_id = pu.user_id
where pu.point_user_id is NULL
group by name
select 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 name
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'
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
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
(
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
)
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
select u.user_id, u.name, u.email from users u
inner join orders o on u.user_id = o.user_id
where o.payment_method = 'kakaopay'
select user_id from orders
where payment_method = 'kakaopay'
select u.user_id, u.name, u.email from users u
where u.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 avg(likes) from checkins c2
where c2.user_id = '4b8a10e6'
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 user_id, round(avg(likes),1) as avg_like from checkins
group by user_id
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 checkin_id, course_id, user_id, likes,
(select avg(c2.likes) from checkins c2
where c.course_id = c2.course_id)
from checkins c;
select checkin_id, c3.title, user_id, likes,
(select round(avg(c2.likes),1) from checkins c2
where c.course_id = c2.course_id) as course_avg
from checkins c
inner join courses c3
on c.course_id = c3.course_id;
select course_id, count(distinct(user_id)) as cnt_checkins from checkins
group by course_id
select course_id, count(*) as cnt_total from orders
group by course_id
select a.course_id, b.cnt_checkins, a.cnt_total from
(
select course_id, count(*) as cnt_total from orders
group by course_id
) a
inner join (
select course_id, count(distinct(user_id)) as cnt_checkins from checkins
group by course_id
) b
on a.course_id = b.course_id
select a.course_id, b.cnt_checkins, a.cnt_total, (b.cnt_checkins/a.cnt_total) as ratio from
(
select course_id, count(*) as cnt_total from orders
group by course_id
) a
inner join (
select course_id, count(distinct(user_id)) as cnt_checkins from checkins
group by course_id
) b
on a.course_id = b.course_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
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
select user_id, email, SUBSTRING_INDEX(email, '@', 1) from users
select user_id, email, SUBSTRING_INDEX(email, '@', -1) from users
select order_no, created_at, substring(created_at,1,10) as date from orders
select substring(created_at,1,10) as date, count(*) as cnt_date from orders
group by date
select pu.point_user_id, pu.point,
case
when pu.point > 10000 then '잘 하고 있어요!'
else '조금 더 달려주세요!'
END as '구분'
from point_users pu;
select pu.point_user_id, pu.point,
case
when pu.point > 10000 then '1만 이상'
when pu.point > 5000 then '5천 이상'
else '5천 미만'
END as lv
from point_users pu
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 lv
from point_users pu
) a
group by lv
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 lv
from point_users pu
)
select level, count(*) as cnt from table1
group by lv
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
select domain, count(*) as cnt from (
select SUBSTRING_INDEX(email,'@',-1) as domain from users
) a
group by domain
select * from checkins c
where c.comment like '%화이팅%'
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
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
select enrolled_id,
sum(done) as cnt_done,
count(*) as cnt_total
from enrolleds_detail ed
group by enrolled_id