SQL 정리 :: JOIN | Subquery | WITH절 | SUBSTRING | CASE문

1. 기초

1. 데이터베이스의 테이블 확인 : show tables  

2. 조회 : SELECT * FROM orders WHERE payment_method ='kakaopay'

* 실행 : [ctrl+enter]

3. 조건 : SELECT * FROM users u WHERE email like 's%com'

( 이메일이 s로 시작해서 com으로 끝나는)

 

2. 이외 유용한 문법

1. limit : 5개만 보겠음 ::select * from orders limit 5 

2. distinct : 특정 컬럼 중복제거 :: select distinct(payment_met1건문에 ''로 해줘야함

2. You have an error in your SQL syntax; :: 조건문 주변 확인하기

 

3. 범주의 통계

1. Group by : 이름별로 몇개씩인지 카운트 ::select name, count(*) from users u group by name 

1-1. min (최소값) ::select week, min(likes) from checkins group by week

1-2. round(avg(평균),1) :: 평균의 소수점 1째자리까지 :: select week, round(avg(likes),1) from checkins group by week

1-3. sum (합계) ::  select week, sum(likes) from checkins group by week

 

2. Order by : 정렬 :: select name, count(*) from users group by name order by name 

 

** 쿼리 실행 순서 : from > group by  > select > order by

 

4. Join

1. a left join b : a 테이블 기준으로 b 붙이기 (* a테이블에 null값도 나옴: null인데이터가 필요할 때 사용)

2. a inner join b : (교집합만 나옴)

** 쿼리 실행 순서 : from > join > where > group by > select

***count :  null을 세지 않음 

 

5. union all

- 테이블 합치기

(select * from table) union all (select * from table)  

6. Subquery

(1) where 필드명 in (서브쿼리)

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

(2) select 필드명,필드명,(서브쿼리) from ~

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

(3) from 절 (제일 많이 사용!)

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 c 
	group by user_id 
			) a 
on pu.user_id = a.user_id

7.With 절 

* with 테이블명 as (select ~)

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

select c.title  
      ,a.cnt_checkins 
      ,b.cnt_total 
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

8. 실전에서 유용한 SQL 문법 (문자열/ CASE )

1-1) 문자열 : SUBSTRING_INDEX(컬럼, '@', 1) :: 이메일 도메인만 확인

SELECT user_id , email, SUBSTRING_INDEX(email,'@',-1)  FROM users u

1-2) 문자열 : SUBSTRING(컬럼, 시작 포인트, 끝 포인트) 

SELECT order_no , created_at , SUBSTRING(created_at,1,10)  FROM orders o

2) CASE

SELECT pu.user_id
     , pu.point
     , (case when pu.point > 10000 
             then '잘하고있어요'
             else '파이팅' end
        ) as msg
FROM point_users pu

 

 

 

 

[출처]

1. 스파르타코딩 : 엑셀보다 쉬운 SQL 강의 0주차~4주차