스파르타 SQL - 문법 총정리

스파르타 SQL 강의 완료 후 사용한 문법 총 정리

1.Select 쿼리문

show tables;
--테이블 보기

select * from orders;
--테이블 데이터 가져오기

select * created_at, course_title, payment_method, email from orders;
-- orders 테이블의 특정 필드값 가져오기

2.Select, where절
where절 : Select 쿼리문으로 가져올 데이터 조건 걸어줌

SELECT * FROM point_users pu 
WHERE point > 20000

SELECT * FROM  users u
WHERE name = '황**'

SELECT * FROM orders o
WHERE course_title = '웹개발 종합반' AND payment_method = 'CARD'

SELECT * FROM orders o 
WHERE course_id !='웹개발 종합반' --같지않음 조건

SELECT * FROM orders o
WHERE created_at BETWEEN '2020-070-13' AND '2020-07-15' --범위 조건

SELECT * FROM checkins c 
WHERE week IN (1,3) -- 포함 조건

SELECT * FROM users u 
WHERE email LIKE '%@daum.net' --특정 패턴 가져오기

SELECT * FROM orders o
WHERE payment_method != 'CARD'

SELECT * FROM point_users pu 
WHERE point BETWEEN 20000 AND 30000

SELECT * FROM users u
WHERE email LIKE 's%com'

SELECT * FROM users u 
WHERE email LIKE 'S%com' AND name = '이**'

SELECT * FROM orders o 
WHERE payment_method = 'kakaopay'
LIMIT 5 -- 일부 데이터 가져오기

SELECT DISTINCT(payment_method) FROM orders o --중복 데이터 제외 가져오기

SELECT COUNT(DISTINCT(name)) FROM users u --중복 데이터 제외 숫자 세기

SELECT email FROM users u 
WHERE name ='남**'

SELECT COUNT(*) FROM users u 
WHERE email LIKE '%gmail.com'
AND created_at BETWEEN '2020-07-12' AND '2020-07-14'

--1주차 숙제 완
SELECT * FROM orders o
WHERE email LIKE '%naver.com'
AND course_title = '웹개발 종합반'
AND payment_method = 'kakaopay'

3. Group by, Order by

• Group by : 동일하 범주를 갖는 데이터를 하나로 묶어서 범주별 통계 내줌
  • 개수 구하기

    select 범주별로 세어주고 싶은 필드명, count(*) from 테이블명
    group by 범주별로 세어주고 싶은 필드명;

  • 최소값

    select 범주가 담긴 필드명, min(최솟값을 알고 싶은 필드명) from 테이블명
    group by 범주가 담긴 필드명;

  • 최대값

    select 범주가 담긴 필드명, max(최댓값을 알고 싶은 필드명) from 테이블명
    group by 범주가 담긴 필드명;

  • 평균

    select 범주가 담긴 필드명, avg(평균값을 알고 싶은 필드명) from 테이블명
    group by 범주가 담긴 필드명;

  • 합계

    select 범주가 담긴 필드명, sum(합계를 알고 싶은 필드명) from 테이블명
    group by 범주가 담긴 필드명;

• Order by : 그룹 통계 정렬

  select * from 테이블명
  order by 정렬의 기준이 될 필드명;

• Order by 정렬 (디폴트 ASC, 내림차순 : DESC)
• 쿼리가 실행되는 순서: from → where → group by → select → order by
• Alias 별칭 : 혼동을 최소화하고 원하는 이름으로 결과를 출력하기 위해 사용

4. join

• Join : 두 테이블의 공통된 정보 (key값)를 기준으로 테이블을 연결해서 한 테이블처럼 보는 것을 의미
• Join 종류 : left join, inner join
  • Left join : A와 B 테이블 사이 겹치는 부분 포함, B 존재하지 않는 정보까지 보여줌(NULL)
  • inner join : A와 B 테이블 교집합
• 쿼리가 실행되는 순서 : rom → join → where → group by → select
• Union : 두 개의 Join을 한번에 보고 싶을 때

(
	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 사용하면 내부 정렬이 깨지게 되는데, 그때 SubQuery 쓰면 됨

5. SubQuery

• where절 서브쿼리

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

• select절 서브쿼리

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;

• 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가 실행 → 2) 이것을 테이블처럼 여기고 밖의 select가 실행!
• 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

• CASE문 (경우에 따라 원하는 값 새 필드 통계)

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

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