-SQL 문법정리-

1. Select문

select * from users

users라는 테이블에서 *(데이터 전체)을 select 한다는 문장이며, * 대신 특정 필드명이 들어가면 해당 필드만 추출해주는 코드이다.

 

2. Where절

select * from users
# 특정 값을 직접 지정 해주는 것이며 >,<와 같은 부등호도 가능
where name = '용빈' and id = 'kim'
# like는 특정 단어가 들어간 데이터를 모두 출력해주는 코드
where name like '%용%'
# between은 사잇값을 출력해주는 코드
where date between '2020-08-08' and '2020-08-18'
# in은 해당 데이터만 추출해주는 코드
where date in (2020,2021)

where절은 다양하게 활용할 수 있으며, 추출한 테이블에 조건을 걸어주는 파트라고 볼 수 있다.

 

3. 기타 기능

#orders의 데이터 개수(행의 개수)를 세줌
select count(*) from orders

#users에서 중복을 제거하고 name의 개수를 세줌
SELECT COUNT(DISTINCT(name)) FROM users
limit 5

limit은 출력되는 데이터의 행의 수를 조절해주는 기능이다.

 

4. Group by

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

group by는 지정한 필드의 같은 데이터로 그룹화 하는 것을 말한다. 예를 들어 name이라는 필드에 김**, 이**이라는 데이터가 있을 경우 같은 데이터끼리 묶는 것을 말한다. 

 

5. Order by

order by는 정렬을 해주는 기능으로, 위에서 다른 조건을 설정한 뒤 마지막에 사용하는 기능이라고 기억하면 편하다. 

order by는 그냥 사용할 때 오름차순으로 정렬되며, order by x desc와 같이 뒤에 desc를 붙여주면 내림차순으로 정렬된다.

 

6. 산술 연산

#1. sum
SELECT course_id , sum(likes) FROM checkins c 
group by course_id

#2. avg
SELECT course_id , avg(likes) FROM checkins c 
group by course_id

#3. round를 활용한 avg
SELECT course_id , round(avg(likes),2) FROM checkins c 
group by course_id

추가적으로 곱하기는 *을 사용하면 되고, 나누기는 /를 사용하면 된다.

 

7. Join

SELECT c.title, ch.week, COUNT(*) as cnt  FROM  courses c 
inner join checkins ch on c.course_id = ch.course_id

join은 두 개의 테이블을 결합해주는 코드이다. 여기서 중요한 점은 두 테이블에 공통으로 존재하는 필드가 있어야 하며, 이 부분은 on을 활용하여 맞춰주면 된다. 또한 각각의 필드 뒤에 알파벳이 있는데 이는 예명(alias)이며, 이 부분을 명확하게 해야 ambiguous 오류를 피할 수 있다.

 

8. Union

(
select '7월' as month, 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
)
UNION ALL 
(
select '8월' as month, 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
)

union은 두 개의 결과물을 결합해주는 코드이다. union을 사용할 때 주의해야 할 점은 결합하고자 하는 두 테이블의 필드명들이 모두 같이야 한다는 점이다.

 

9. Subquery

 1) Where절에 들어가는 Subquery

#전체 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기
select * from point_users pu 
where pu.point > (select avg(pu2.point) from point_users pu2)

where절에 들어가는 subquery를 활용할 때 주의해야 할 점은 subquery 부분을 괄호()로 감싸줘야 한다는 점이며, 추가적으로 예명을 명확하게 표기해야 한다는 점이다.

 

2) Select에 들어가는 Subquery

#checkins 테이블에 course_id별 평균 likes수 필드 우측에 붙여보기
SELECT checkin_id , course_id , user_id , likes,
(SELECT round(AVG(likes),1)  from checkins c2
where c.course_id = c2.course_id)
from checkins c

마찬가지로 select에 들어가는 부분에 select문을 집어 넣기만 하면 되며, 예명을 명확하게 하고, 괄호()를 사용하는 것이 중요하다.

 

 3) From에 들어가는 Subquery와 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

from절도 다른 subquery와 마찬가지이며 with절을 사용하면 위에 나와 있는 것처럼 간략하게 select 절을 표현할 수 있다.

 

Subquery를 사용할 때의 팁은 먼저 사용하고자 하는 select문을 보기좋게 만들어 논 뒤, 새로운 select문을 활용하여 가독성이 좋게 하는 것이라고 생각한다.

 

10. 문자열 쪼개기

# 특정 문자를 기준으로 분할하기
select user_id, email, SUBSTRING_INDEX(email, '@', 1) from users
# 정해진 문자열 길이만큼 출력하기
select order_no, created_at, substring(created_at,1,10) as date from orders

substring_index는 (필드, 기준이 되는 문자열, 위치)의 형식을 가지고 있고, 위치가 1이면 기준이 되는 문자열의 왼쪽 부분을, -1이면 오른쪽 부분을 출력해준다.

substring은 (필드, 문자열의 시작위치, 시작위치부터 끝나는 위치)의 형식을 가지고 있고, 위의 코드를 해석하면 create_at 필드의 첫 번째 문자열부터 10번째까지 출력한다는 것을 의미한다.

 

11.  Case

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

case는 select절에서 사용하는 코드이며, 파이썬의 if문과 유사한 형태를 가지고 있다. 따라서 여러개의 when절이 나올 수 있으며, 마무리는 꼭 END로 해야한다.