Select
1) 어떤 테이블에서 2) 어떤 필드의 데이터를 가져올지
Where
select로 가져올 데이터에 조건을 걸어줌
select count(distinct(payment_method)) from orders
where email like '%naver.com'
and course_title = 'SQL 종합반'
and created_at between "2020-07-12" and "2020-07-14"
limit 5;
distinct: 중복 제거 limit: 데이터 제한
Group by
특정 중복데이터를 묶어줌
Order by
데이터를 오름차순 및 내림차순으로 정렬
Alias
쿼리에 별칭을 붙임
select name, count(*) as cnt from users
group by name
order by count(*) desc;
Left Join
두 테이블에서 기준이 되는 필드를 가지고 한쪽 테이블의 전체 데이터에 공통 데이터를 붙임
Inner Join
두 테이블에서 기준이 되는 필드를 가지고 겹치는 데이터만 쿼리
select * from users u
left(inner) join point_users pu on u.user_id = pu.user_id
Union
데이터를 이어붙이고 싶을 때 사용 (정렬 X)
(
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
)
Subquery
쿼리 안의 쿼리로서, 쿼리를 하나로 묶어서 사용 가능
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 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
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
Substring
문자열 쪼개기
select SUBSTRING(created_at,1,10) from orders
// SUBSTRING(문자열, 출력을 하고싶은 첫 글자의 위치, 몇개의 글자를 출력하고 싶은지)
select user_id, email, SUBSTRING_INDEX(email, '@', -1) from users
// SUBSTRING_INDEX(문자열, 기준, 위치) (1: 첫번째 ,-1: 마지막)
Case
경우에 따라 원하는 값을 새 필드에 출력
select pu.point_user_id, pu.point,
case
when pu.point >= 10000 then '1만 이상'
when pu.point >= 5000 then '5천 이상'
else '5천 미만'
END as level
from point_users pu