스파르타 코딩 SQL 문법 총정리 하기

show tables : 테이블을 다 보여줌

 

 

select ~ from ~:  from 에서 데이터 선택해서 보여줌 

select * from orders

*는 전체라는 뜻

 

select ~ from ~

where ~ : where에 넣은 조건에 맞는 데이터만 보여줌

조건을 여러개 넣을 때는  and

select * from orders
where course_title = "앱개발 종합반" and payment_method = "kakaopay";

 

where 과 같이 쓰는 문법들

><: 초과, 미만

>=:이상

select * from point_users 
where point > =5000

<=: 이하?

 !=: 같지 않음:

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

between '~' and '~' :범위 (뒤에 넣어준 숫자는 포함하지 않음)

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

in (~,~) : 포함

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

~ like ('%~'): 패턴 (문자열 규칙) 

select * from users 
where email like '%daum.net'

 

limit  숫자:  방대한 데이터 중 일부만 가져옴

select*from orders
where payment_method = 'kakaopay'
limit 5

 

distict(): 중복 데이터는 제외함

select distinct(payment_method) from orders

 

count(): 데이터 개수 세어줌

select count(*) from orders
where payment_method = 'kakaopay'

 

distinct & count 같이 쓸 때

select count(distinct(name)) from users

 

group by ~: ~를 기준으로 그룹 지어줌 

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

 

order by (desc): 정렬 시켜줌, 기본 오름차순,   desc 붙이면 내림차순.  

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

 

 

group by와 같이 쓰는 문법들

min(): 최소 개수

select week, min(likes) from checkins
group by week

max(): 최대 개수

select week, max(likes) from checkins
group by week

avg(): 평균 개수

select week, avg(likes) from checkins
group by week

round( , 숫자): 소수점으로 끊어주기

select week, round(avg(likes),2) from checkins
group by week

sum():  데이터 안의 숫자들을 다 더해줌

select week, sum(likes) from checkins
group by week

 

Alias  별칭 기능:

테이블 이름 뒤에 약자를 더 해줌. 필드명 앞에 붙여서 더욱 구체적인 정보를 넣을 때 유용

select * from orders o
where o.course_title = '앱개발 종합반'

 

as 를 붙여서 필드명을 원하는 이름으로 바꿔줄 수 있다. 

select payment_method, count(*) as cnt from orders o
where o.course_title = '앱개발 종합반'
group by payment_method

 

 

Join

select ~ from

~join ~ on ~=~

left join:  A, B 테이블이 있다면 A를 기준으로  B 테이블을 붙여주는 것

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

inner join: A,B 테이블이 있다면  A,B테이블 둘 다에 해당하는 데이터를 기준으로 테이블을 붙여준다.  

그래서 어떤 것을 테이블을 기준으로 하던지 결과가 같음.

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

 

 

union: 결과물 합쳐줌 - order by 정렬이 사라짐

( 완성 테이블)

union all

(완성 테이블) 

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

 

subquery: ()해주고 그 쿼리를 더 다른 쿼리 안에 넣어주는 것

종류

where에 넣기

select user_id, name, email from users
where user_id in (
	select user_id from orders
	where payment_method = 'kakaopay'
)

select에 넣기

select c.checkin_id, 
	   c.user_id, 
	   c.likes,
	   (
	     select avg(likes) from checkins
	     where user_id = c.user_id
	   ) as avg_likes_user
from checkins c

from에 넣기 (join 해주는 것도 포함)

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

 

subquery 해서 넣어줄 때 () a 이런식으로 이름을 지어줄 수 있다. 

 

with: 복잡한 서브쿼리를 간단한 이름으로 대체해줄 수 있는 문법

with 새명칭  as (subquery) , 새명칭 as (subquery)

근데 그냥 실행시켜주면 안 되고, 전체 선택해서 실행해야 오류없이 같은 결과가 나온다.

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:  어떠한 기준으로 문자열을 쪼개준다.

SUBSTRING_INDEX(필드명, '문자기준', 1or -1) 

1을 넣으면 기준 앞쪽이 나옴

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

-1을 넣으면 기준 뒤쪽이 나옴

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

 

SUBSTRING: 문자 일부만 출력

SUBSTRING(필드명, 시작 문자 자리 숫자, 끝날 문자 자리 숫자)

select SUBSTRING(created_at,1,10) as date, count(*) from orders o 
group by date

 

 

case: 경우에 따라 원하는 값을 새 필드에 출력

(case when~ then

else end ) as ~ 

// 괄호치고 이름 지어줄 수 있음

select pu.user_id, pu.point,
		(case when pu.point > 10000 then '잘 하고 있어요!'
		else '조금만 더 파이팅!' end) as msg
from point_users pu