SQL 4주차 개발일지

◎ Subquery : 쿼리 안의 쿼리. 코드를 편하고 간단하게 하기 위해서 사용한다.

 

● WHERE절에 들어가는 서브쿼리

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

● SELECT절에 들어가는 서브쿼리

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

● FROM절에 들어가는 서브쿼리

 : inner join (서브쿼리) a on 

 

<연습해보기>

● where절

카카오페이로 결제한 유저정보 출력하기

- ① orders테이블과 users테이블을 묶어줌

  : select * from users u

   inner join orders o on u.user_id = o.user_id

  ② 결제수단이 카카오페이인 경우만 나오게하기

  : where payment_method = 'kakaopay'

합치면

select  u.user_id , u.name  ,u.email  from users u

 inner join orders o on u.user_id = o.user_id

where o.payment_method = 'kakaopay'

 

이걸 서브쿼리를 이용한다면

 ① 카카오페이로 결제한 user_id만 출력하기

: select user_id from orders

 where payment_method = 'kakaopay'

② 위에서 구한 필드 안에서 유저정보 출력하기

 : select u.user_id , u.name  ,u.email  from users u

  where user_id in ( 

    select user_id from orders

    where payment_method = 'kakaopay'

 )

 

● select절

ckeckins테이블에 course_id별 평균 likes수 붙이기

① 먼저 checkins테이블을 실행하여 원하는 필드명을 뽑아낸다.

② 평균 likes수를 구하기 위해 새로운 checkins테이블을 실행한다.

   여기서 먼저 특정 course_id의 평균likes수를 출력해본다.

 : select avg(likes)  from checkins

   where course_id = '5f0ae408765dae0006002817'

③ 위 서브쿼리를 select절에 붙여주는데, 모든 course_id별 평균값을 구해야하기 때문에

    ①에서 출력하고자하는 c.course_id로 바꿔준다.

   select avg(likes)  from checkins

   where course_id = 'c.course_id'

 

= SELECT c.checkin_id ,
   c.course_id ,
   c.user_id ,
   c.likes ,
   (
    SELECT AVG(likes) from checkins
    WHERE course_id = c.course_id 
   )
  FROM checkins c 

 

● from절

course_id별 유저의 checkin갯수 구하기 ( 중복은 제외하기)

① checkins테이블을 실행 후 course_id로 묶어준다.

② count(user_id)로 실행시 중복된 경우도 함께 계산되기 때문에

   distinct로 중복을 없앤다.

 : select course_id, count(distinct(user_id)) from checkins

   group by course_id

 

◎ With절 

: 서브쿼리문을 좀 더 깔끔하게 정리. 쿼리문을 ( )안에 넣어 테이블로 정리한다.

맨 윗줄에 

with table1 as (  ) , table2 as (  )

 

 

○ 실전에서 유용한 sql문법

1. 문자열 

 1) 문자열 쪼개기 - substring_index

   ex) 이메일에서 아이디만 가져오기

    ① users테이블을 실행한다. 

        이메일은 '아이디 @ 도메인' 으로 이루어져있으므로 @를 기준으로 분리한다.

    : substring_index(email,'@',1) → email을 @ 기준으로 나눈 앞부분 (뒤는 -1)

 2) 문자열 일부만 출력하기

   ex) orders테이블에서 created_at 날짜만 출력하기

    : substring_index(created_at,1,10) → created_at의 앞에서 10번째 문자까지 출력

 

2. CASE

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

  ex) 포인트 보유액에 따라 다르게 표시하기

   point > 10000일때 '잘 하고 있어요!' 그 외엔 '조금만 더 화이팅!' 으로 표시

select pu.user_id, pu.point ,

( case when pu.point > 10000 then '잘 하고 있어요!'

          else '조금만 더 화이팅!' end)

 from point_users