SQL 문법 정리

1. Where

'같음' 조건 걸기

SELECT * FROM orders o
WHERE o.user_id = '1234'

'같지 않음' 조건 걸기

SELECT * FROM orders o
WHERE o.user_id != '1234'

'범위' 조건 걸기

SELECT * FROM orders o
WHERE o.created_at BETWEEN '2020-07-10' and '2020-07-20'

'포함' 조건 걸기

SELECT * FROM checkins c 
where c.week in (1,3)

'패턴' 조건 걸기

SELECT * FROM users
where email like '%naver.com'

'%1234'의 경우 1234앞에 어떠한 문자열이 와도 상관 없다 라는 뜻!!

2. 유용한 문법

전체 데이터를 다 불러올 경우, 양이 방대하기 때문에 'Limit' 사용!

SELECT * FROM orders o
WHERE o.user_id != '1234'
LIMIT 5

이 경우, 5줄만 출력

중복 데이터 제외하기 'Distinct'

SELECT distinct(payment_method) FROM orders o

이 경우, 중복되는 payment_method값은 제외하고 출력

숫자 세기 'Count'

SELECT Count(*) FROM orders o

이 경우, 전체의 개수를 출력

'Distinct' 'Count' 응용

SELECT count(distinct(payment_method)) FROM orders o

이 경우 결제 수단의 종류가 몇 개 인지 알 수 있다. (4개)

3. 범주 & 정렬

~별로 통계, ~별로 갯수 등에서 기준 점을 잡아주는 'Group by'

SELECT o.payment_method  , count(user_id) FROM orders o
group by o.payment_method 

이 경우, 각 결제 수단 별 유저 수를 알 수 있다.

정렬 시켜주는 'Order by'

SELECT o.payment_method  , count(user_id) FROM orders o
group by o.payment_method 
order by o.payment_method 

이 경우, o.payment_method이 문자열이기 때문에 알파뱃 오름 차순으로 정렬

SELECT o.payment_method  , count(user_id) FROM orders o
group by o.payment_method 
order by o.payment_method desc

뒤에 desc를 붙여주면 내림 차순으로 정렬 (문자열, 숫자 등등 상관 없음)

4.Join

서로 다른 테이블을 연결 시켜주는 'join'
'inner join'과 'left join'이 존재한다.
'inner join'은 교집합, 'left join'은 첫번째 SELECT한 원 안에 붙이는 것!

point_users 테이블에 orders 테이블을 'inner join'한 예시문

select o.payment_method, round(AVG(p.point)) from point_users p
inner join orders o 
on p.user_id = o.user_id 
group by o.payment_method

이 경우, 결제 수단 종류에 따른 유저들의 평균 포인트를 나타냅니다.

users 테이블에 point_users 테이블을 'left join'한 예시문

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

'left join'에는 NULL 값이 발생하는데 이를 통해서 특정 값이
존재하지 않는 자료들, 혹은 존재하는 자료들만 뽑아낼 수 있다.

select * from users u
left join point_users pu on u.user_id = pu.user_id
where pu.point is not NULL

이 경우, users 테이블과 point_users 테이블을 'left join'한 후
point_users 테이블의 point 값이 NULL 상태가 아닌 자료만 나타낸다.

5.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'을 사용 할 경우, 'order by'가 작동하지 않아 정렬이 풀린다.※

6.Subquery

Where에 들어가는 'Subquery' 예시

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

이 경우, 카카오 페이를 사용해서 결제한 유저들의 정보만 골라서 나타낸다.

Select에 들어가는 'Subquery' 예시

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

이 경우, 새로 avg_like_user 값, 즉 각 유저가 받은 좋아요의 평균값이 추가로 출력된다.

From에 들어가는 'Subquety' 예시

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

이 경우, 유저의 아이디 별 좋아요 평균, 그리고 포인트를 알 수 있다.

'Subquery' 사용 시에 내가 원하는 것을 나눠서 각각 구현해 본 후
합치는 개념으로 사용하면 조금 더 수월함!

'with'절 예시

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

더 간단하게 표기 가능해진다.
'Subquery'를 가상의 table1로 지정하여 사용하는 느낌

7. 실전에서 유용한 SQL 문법

'SUBSTRING_INDEX' 문자열 쪼개기

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

이 경우, users 테이블의 email 값에서 @을 기준으로 앞 부분을 가져온다.

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

이 경우, users 테이블의 email 값에서 @을 기준으로 뒷 부분을 가져온다.

'SUBSTRING' 문자열에서 일부만 추출하기

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

이 경우, orders 테이블의 created_at 값의 1번째 문자부터 10번째 문자열까지의 텍스트를 출력한다.

select order_no, created_at, substring(created_at,12,8) as date from orders

이 경우, orders 테이블의 created_at 값의 12번째 문자부터 8번째 문자열까지의 텍스트를 출력한다.

'Case' 경우에 따라 원하는 값을 출력(if같은 느낌?..)

select 	pu.point_user_id, 
		pu.point,
        (
          case 
          when pu.point > 10000 then '잘 하고 있어요!'
          else '조금 더 달려주세요!'
          END
        ) as '구분'
from point_users pu

이 경우, point_users 테이블에서 point 값 중 10000을 초과할 경우 '잘 하고 있어요!', 초과하지 못 할 경우, '조금 더 달려주세요!'를 출력한다.

(case when (조건식) then (내용기입)
when (조건식) then (내용기입)
else (내용기입)
END)