[스코클] 엑셀보다 쉬운 SQL - 문법 정리

#1. select 구문: select 뒤에 오는 것은 출력값
show tables #database에 있는 table들의 제목 출력
select * from orders  # #orders 에 있는 모든 칼럼 출력 (*은 모든 것을 의미)
select order_no, created_at, user_id, email from orders # orders테이블에 있는 내용 중 select 뒤에 나열한 내용 출력: ','로 구분해주기
select * from enrolleds e  뒤에 붙는 알파벳 'e'는 일종의 별칭. subquery 쓸 때 명령을 정확하게 내리기 위해 필요.
limit 5  # 쿼리 뒤에 붙이면: 5개만 불러와: 간단하게 형태를 확인하고 싶은데 데이터가 너무 많을 경우 활용
select distinct(payment_method) from orders #distinct: 중복필드 제외하고 불러오기
select count(*) from orders # 해당 내용의 숫자세기
select count(distinct(name)) from users # 해당 내용 중복없이 숫자세기
select week, min(likes) from checkins # 최소값
select week, MAX(likes) from checkins # 최대
select week, avg(likes) from checkins  # 평균
select week, sum(likes) from checkins  # 합계

select o.payment_method, round(avg(pu.point), 1) as avg from point_users pu  # 반올림, 자릿수


#2. 조건절 where - select에서 기본
select * from orders
where payment_method = 'kakaopay' # 필드의 문자열은 ''로 잡아주기
where point >= 5000  # 숫자의 크고작음을 표현 가능
where course_title = '앱개발 종합반' and payment_method = 'CARD' # 두가지 조건을 and, or로 걸어주기 가능.
where name = '황**' # 특정 값으로 찾기 가능
where payment_method != 'CARD' # 같지 않음을 나타내기
where created_at between '2020-07-13' and '2020-07-15' # 범위: 특정 내용의 범위를 지정 가능
where point between 20000 and 30000  # where 20000 <= point =<30000
where week in (1,3)  #1과 3의 경우로 조건걸기 (범위아님)
where email like 'a%t'  # 비슷한 패턴 찾을 때 (like와 % 함께 쓰기:  a로 시작해서 t로 끝남. 가운데는 뭐가 와도 노상관. %@%와 같이 사용가능
where email like 's%com' and name like '이%' # 이메일이 s로 시작하고 com으로 끝나면서 성이 이씨인 유저만 추출해보기
where pu.point_user_id is not NULL  # ~ 빼기 조건절


#3. group by: 특정 그룹의 통계 낼때 활용하기; ~별로 구하기 할때는 무조건 group by 를 넣어줘야 함
select name, count(*) from users
group by name  #group by 를 안하고 count(*) 을 하면 전체 숫자만 나옴. name별 수를 세고 싶다면 group by 로 그룹 지정을 해야 함. 

select * from users
group by name # group by name 하면 distinct한 내용만 남음.


select name, count(*) from users # group by 와 where도 함께 사용 가능
group by name 
where email like '%naver.com'


#4. order by: 정렬은 뭐가 다 되고 마지막에

select name, count(*) from users
group by name
order by count(*) desc  #정렬은 다 되고 마지막에, 기본은 오름차순 (asc); 내림차순 (desc)-알파벳, 숫자, 시간 다 됨

#웹개발 종합반의 결제수단 별 주문건수 세어보기 where, group by, order by 함께 사용하기
select payment_method, count(*) from orders
where course_title = '웹개발 종합반'
group by payment_method
order by count(*) DESC  #연산 순서: 1) orders 검색, 2) 웹개발 종합반만 추출, 3) payment_method 로 묶어서, 4) count, 5) 내림차순 정렬

#5. Join: 테이블 합치기
select * from users u  # users를 선택, 그 옆에 point_users 를 붙인다. 기준은 각각의 user_id
left join point_users p on u.user_id = p.user_id # left join은 메인 테이블(from 뒤)을 기준으로 다른 테이블을 갖다 붙임. - 해당없는 필드값은 "Null"로 출력

select * from users u 
inner join point_users p on u.user_id = p.user_id # inner join은 point_users 값이 있는 애들만 붙어-교집합 (null은 없어짐)

select c.course_id, c2.title, count(*) as cnt from checkins c  # 테이블 합친 후, group by 를 활용하여 원하는 값/통계치 출력 가능
inner join courses c2 on c.course_id = c2.course_id  
group by c.course_id  #뭐의 course_id인지 명확하게 해줘야 함. 

select pu.user_id, u.name, u.email, pu.point from point_users pu  #- 유저의 포인트 정보가 담긴 테이블에 유저 정보를 연결해서, 많은 포인트를 얻은 순서대로 유저의 데이터를 뽑아보자!
inner join users u on pu.user_id = u.user_id 
order by pu.point DESC 

select u.name, count(*) as cnt from orders o  #- naver.com 이메일 사용하는 유저중 성씨 별 주문건수 세어보기
inner join users u on o.user_id = u.user_id 
where o.email like '%naver.com'
group by u.name #-> 위 쿼리가 실행되는 순서: from - join - where - group by - select

#6. 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 #union에서는 order by 가 안 먹는다. order 넣을 필요 없음 why? 합친거에서 다시 order를 해야 함. (서브퀄)
(
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
)


#7. sub query 큰 쿼리 안에 들어가는 작은 쿼리들.. (select, from, where 안에 들어감)
#7.1. WHERE 안에 들어가는 subquery : where는 조건문. subquery 결과를 조건에 활용하는 방식으로 유용하게 사용.

select user_id from orders o
where payment_method = 'kakaopay'

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


select * from checkins

select avg(likes) from checkins
where user_id = '4b8a10e6'

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 

select avg(likes) from checkins c 

#7.2. from 절에 들어가는 subquery - 내가 지금 만든 select문을 마치 원래부터 있었던 table처럼 사용하는게 from 절의 subquery!!: 내가만든 select 와 이미 있는 테이블을 join하고 싶을 때 사용하기 좋음.

select user_id, round(avg(likes),1) as avg_likes from checkins
group by user_id 

select pu.user_id, pu.point, a.avg_likes from point_users pu
inner join (
select user_id, round(avg(likes),1) as avg_likes from checkins
group by user_id 
) a on pu.user_id = a.user_id


select avg(point) from point_users pu 

select * from point_users pu 
where point > (select avg(point) from point_users pu 
)


select avg(point) from point_users pu 
inner join users u on pu.user_id = u.user_id 
where u.name like '이%'

select * from point_users pu 
where point > (
select avg(point) from point_users pu 
inner join users u on pu.user_id = u.user_id 
where u.name like '이%'
)
order by pu.point DESC 

# another way
select avg(point) from point_users pu 
where user_id in (
select user_id from users 
where name='이**'
) #name 이 '이**' 인 조건인 user_id 의 point 평균 -> point_users에는 name이 없기 때문에 users의 정보를 가져다 씀


#7.3. select에 subquery 넣기

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

select *,
(
select course_id, avg(likes) from checkins c 
group by course_id 
) as avg_likes
from checkins c 

select avg(likes) from checkins
where course_id = c.course_id

select c.checkin_id, 
   c.course_id, 
   c.user_id, 
   c.likes,  
   (
   select round(avg(likes),1) 
   from checkins
   where course_id = c.course_id
) as course_avg
from checkins c 


select c.checkin_id, 
   title, 
   c.user_id, 
   c.likes,  
   (
   select round(avg(likes),1) 
   from checkins
   where course_id = c.course_id
) as course_avg
from checkins c 
inner join courses c2 on c.course_id = c2.course_id 


#practice4. (from) 
#1. course_id 별로 한번이라도 로그인한 유저의 수

select c.title, 
   cnt_checkins, 
   cnt_total,  
   round(cnt_checkins/cnt_total, 2) as ratio
  from 
(
select course_id, count(distinct user_id) as cnt_checkins from checkins c
group by course_id) a
inner join
(
select o.course_id, count(*) as cnt_total from orders o
group by o.course_id
) b on b.course_id = a.course_id
inner join courses c on c.course_id = a.course_id



# 8. with 쿼리: 새로만든 테이블에 이름 붙이기. 

with table1 as (
select course_id, count(distinct user_id) as cnt_checkins from checkins c
group by course_id
), table2 as (
select o.course_id, count(*) as cnt_total from orders o
group by o.course_id
)

select c.title, 
   cnt_checkins, 
   cnt_total,  
   round(cnt_checkins/cnt_total, 2) as ratio
  from table1 a
inner join table2 b on b.course_id = a.course_id
inner join courses c on c.course_id = a.course_id


#9.1. 실전에서 유용한 SQL 문법 (문자열)

select user_id, email, SUBSTRING_INDEX(email, '@', 1), SUBSTRING_INDEX(email, '@', -1)  from users # 문자열 데이터의 일부만 잘라오기

select order_no, substring(created_at, 1, 10)as date, count(*) from orders o #문자열 일부만 출력 (e.g., 일자자별 통계 - 시간 지우기)
group by date
#일별로 주문 몇개?


#9.2. 실전에서 유용한 SQL 문법 (case) - 특정 조건에 따라 새로운 필드값 만들기
select pu.user_id, pu.point, 
(case when point > 10000 then '잘 하고 있어요!' 
else '조금만더 파이팅!' END) as msg
from point_users pu 

#포인트가 10000보다 크면 잘하고 있어요, 또는 그게 아니면 조금만 더 파이팅! 쓰고 끝내라.
select pu.user_id, pu.point, 
(case when point > 10000 then '1만 이상' 
when pu.point> 5000 then '5천 이상'
else '5천 미만' END) as lv
from point_users pu 


select a.lv, count(*) as cnt from 
(select pu.user_id, pu.point, 
(
case when point > 10000 then '1만 이상' 
when pu.point> 5000 then '5천 이상'
else '5천 미만' END) as lv
from point_users pu 
) a
group by a.lv

with table1 as (select pu.user_id, pu.point, 
(
case when point > 10000 then '1만 이상' 
when pu.point> 5000 then '5천 이상'
else '5천 미만' END) as lv
from point_users pu)

select a.lv, count(*) as cnt from table1 a
group by a.lv


# 꿀팁1. 점점길어지고 방대해지면 헷갈림: 줄을 잘 맞춰야 함 (눈에보기좋게)
# 꿀팁2. join을 통해서 여러개의 테이블을 붙일 수 있음.
# 꿀팁3. 연결한 테이블 이름은 지정 가능??
# 꿀팁: group by 와 order by 는 각각 두개 이상의 변수로 필터링을 할 수 있다.  e.g., group by c1.title, c2.week;  order by c1.title, c2.week
# 꿀팁: UNION 에서는 기존에 했던 orderby 가 적용이 안됨. 새로 order by 를 넣어 주어야 함 - 어떻게 넣나?
# 꿀팁: 원하는 데이털를 얻기 위한 방법은 다양. 다양하게 시도해보기. 단순한게 좋은 쿼리
# 꿀팁: subquery는 안에 들어가는 쿼리문 먼저 작성해주고, 바깥쿼리를 작성한 후, 적절한 위치에 서브쿼리를 넣어주기.
# 꿀팁: 성장을 위해서 한눈에 보기 좋은 쿼리를 짜기!! 줄 잘맞추기!!!)
# 꿀팁: subquery 짤 때는 항상 쪼개서 생각하기.
# 꿀팁: 평균값을 타이핑으로 치지 않고 섭쿼리로 하는이유: 데이터가 실시간으로 바뀌니깐..
# 꿀팁: 쿼리는 한번에 정답 맞추는 게임이 아니다!!