SQL 문법 총정리

[Where절과 자주 같이 쓰는 문법]

: 조건 달기

- 같지 않음 (!=)

select * from orders
where payment_method != 'CARD';

- 범위 (between and )

select * from orders
where created_at between "2020-07-13" and "2020-07-15";

: 13일~14일 사이면 between "2020-07-13" and "2020-07-15" 라고 써야 함

- 포함 (in)

select * from checkins
where week in (1,3);

- 패턴 (like)

select * from users
where email like '%gmail.com'

- 일부 데이터만 가져오기 (limit) : 방대한 데이터를 확인할 때

select * from orders
where patment_method = 'kakaopay'
limit 5;

- 숫자 세기 (count)

select count(*) from users

- 중복데이터 제외 (distinct)

select distinct(payment_method) from orders;

- 함수 여러개 쓰기 (distinct와 count)

select count(distinct(payment_method)) from orders;

 

[Group by와 같이 쓰는 문법]

: 범주의 통계

- 최소(min)

select course_id, min(likes) from checkins
group by course_id;

select 범주가 담긴 필드명, min(최솟값을 알고 싶은 필드명) from 테이블명
group by 범주가 담긴 필드명;

- 최대(max)

select course_id, max(likes) from checkins
group by course_id;

select 범주가 담긴 필드명, max(최댓값을 알고 싶은 필드명) from 테이블명
group by 범주가 담긴 필드명;

- 평균(avg)

select course_id, avg(likes) from checkins
group by course_id;

select 범주가 담긴 필드명, avg(평균값을 알고 싶은 필드명) from 테이블명
group by 범주가 담긴 필드명;

- 합계(sum)

select course_id, sum(likes) from checkins
group by course_id;

select 범주가 담긴 필드명, sum(합계를 알고 싶은 필드명) from 테이블명
group by 범주가 담긴 필드명;

- order by: 정렬

> 오름차순

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

> 내림차순

select name, count(*) from users
group by name
order by count(*) desc;

> group by 를 안쓰고도 사용 가능!

select * from checkins
order by likes;

 

[Join]

: 공통 key값을 가지고 연결

- inner join(교집합)

select * from users u
inner join point_users p
on u.user_id = p.user_id;

- left join(왼쪽에다 붙이는 것)

select * from users u
left join point_users p
on u.user_id = p.user_id;

> NULL값에 대한 처리는? 

where 절로 '~ is NULL' 혹은 '~is not NULL'로 조건을 지정

- join 할 테이블이 여러개라면?

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

> inner join을 2번 쓰기

 

[Union]

: 모아보기

(
	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 all (아래 붙일 코드)

 

[Subquery]

: 하나의 SQL 쿼리 안에 또 다른 SQL 쿼리 넣기

: tab으로 줄 구분 잘하기

(1) where

> where 필드명 in (subquery)

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

(2) select

> select 필드명, 필드명, (subquery) from ..

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;

(3) from

> join에서 사용

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]

: 복잡한 쿼리문을 깔끔하게 정리 가능

: inner join 할 때 유용

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

 

[Case]

: 경우에 따라 값 출력
: with과 함께 하면 좋다

- SUBSTRING_INDEX: 문자열 쪼개기

(1) 쪼개는 문자 기준 앞

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

(2) 쪼개는 문자 기준 뒤

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

- SUBSTRING: 문자열 일부만 출력하기

select order_no, created_at, substring(created_at,1,10) as date from orders

- 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 lv
from point_users pu