스파르타 코딩클럽 [ 엑셀보다 쉬운 SQL ] - 4주차 (문법 정리)

Select 쿼리문

    - 데이터베이스에서 '데이터를 선택해서 가져오겠다'는 의미

select * from orders

 

 

Where 절

    - Select 쿼리문으로 가져올 데이터에 조건을 걸어주는 것

select * from orders
where course_title = '웹개발 종합반'

 

 

Group by

    - 동일한 범주를 갖는 데이터를 하나로 묶어서, 범주별 통계를 내주는 것

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

이름 별로 묶고, 이름별 개수를 출력하기

 

 

Order by

    - 정렬 기능

    - DSEC : 내림차순 / ASC : 오름차순

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

개수를 기준으로 정렬하기 (오름차순)

 

 

 

Where, Group by, Order by 함꼐 사용해보기

select payment_method, count(*) from orders
where course_title = "웹개발 종합반"
group by payment_method
order by count(*) DESC;

orders 테이블에서 course_title이 '웹개발 종합반'인 요소에서 payment_method별 주문 건수를 내림차순으로 정렬

 

 

Left Join

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

👉 어떤 데이터는 모든 필드가 채워져있지만, 어떤 데이터는 비어있는 필드가 있다

꽉찬 데이터: 해당 데이터의 user_id 필드값이 point_users 테이블에 존재해서 연결한 경우

비어있는 데이터: 해당 데이터의 user_id 필드값이 point_users 테이블에 존재하지 않는 경우

 

 

 

Inner Join

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

👉 Left Join과는 다르게, 여기서는 비어있는 필드가 있는 데이터가 없다

그 이유는, 같은 user_id를 두 테이블에서 모두 가지고 있는 데이터만 출력했기 때문

 

 

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

👉 위 쿼리가 실행되는 순서: from → join → where → group by → select

 

 

 

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
)

7월, 8월 데이터를 합침

 

 

 

Subquery

    - 하나의 SQL 쿼리 안에 또다른 SQL 쿼리가 있는 것

    - Subquery는 where, select, from 절에서 유용하게 사용

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'
)

 

    

 

Where은 조건문이죠? Subquery의 결과를 조건에 활용하는 방식으로 유용하게 사용

where 필드명 in (subquery) 이런 방식

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

👉

(1) from 실행: users 데이터를 가져와줌

(2) Subquery 실행: 해당되는 user_id의 명단을 뽑아줌

(3) where .. in 절에서 subquery의 결과에 해당되는 'user_id의 명단' 조건으로 필터링 해줌

(4) 조건에 맞는 결과 출력

 

 

 

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;

👉

(1) 밖의 select * from 문에서 데이터를 한줄한줄 출력하는 과정에서

(2) select 안의 subquery가 매 데이터 한줄마다 실행되는데

(3) 그 데이터 한 줄의 user_id를 갖는 데이터의 평균 좋아요 값을 subquery에서 계산해서

(4) 함께 출력

 

From은 언제 사용하면 좋을까요? 내가 만든 Select와 이미 있는 테이블을 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

👉

(1) 먼저 서브쿼리의 select가 실행되고,

(2) 이것을 테이블처럼 여기고 밖의 select가 실행!

 

 

 

with

    - 서브쿼리 From절이 계속 붙으면 헷갈리기 때문에 깔끔히 하기위해 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_INDEX(문자열, 구분하고싶은 문자, 몇번째조각)

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

👉

이메일에서 @를 기준으로 텍스트를 쪼개고, 그 중 첫 번째 조각을 가져오라는 뜻!

ex) abc@naver.com -> abc 가져옮

 

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

👉

@를 기준으로 텍스트를 쪼개고, 그 중 마지막 조각을 가져오라는 뜻!

ex) abc@naver.com -> naver.com 가져옮

 

 

SUBSTRING(문자열, 출력을 하고싶은 첫 글자의 위치, 몇개의 글자를 출력하고 싶은지)

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

create_at에서 문자열 일부만 출력

 

 

 

CASE

    - 경우에 따라 원하는 값을 새 필드에 출력해보기

    - 특정 조건에 따라, 데이터를 구분해서 정리

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

👉

10000점보다 높은 포인트를 가지고 있으면 '잘 하고 있어요!',

평균보다 낮으면 '조금 더 달려주세요!' 라고 표시