[개발일지] SQL 입문_4주차_총정리

1.  SELECT(FROM), WHERE

    1) SELECT 쿼리문: '데이터를 선택해서 가져오겠다 ' → 추출하고 싶은 필드를 정하겠다

        -쿼리문의 구성: ① 어떤 테이블에서(FROM), ②어떤 필드의 데이터를 가져올지(SELECT)

    2) WHERE 쿼리문: SELECT 쿼리문으로 가져올 데이터의 조건을 걸어주는 것

       ⁕여러 조건은 and로 연결해준다!

        -예1) orders 테이블에서 결제수단이 카카오페이인 데이터만 가져와줘

        -예2) point_users 테이블에서 포인트가 5000점 이상인 데이터만 가져와줘

       ▶예1)

          SELECT * FROM orders
          where payment_method = 'kakaopay'

    3) WHERE 쿼리문 활용법

        -단일 값 조건만 추출: WHERE payment_method = 'kakaopay'

        -복수 값 조건 추출: WHERE payment_method in ('kakaopay', 'CARD')

        -복수 조건 추출: WHERE payment_method = 'kakaopay AND name = '황%'

          ⁕복수 조건은 and로 연결한다!

        -같지 않은 조건 추출: WHERE payment_method != 'kakaopay'

         →kakaopay가 아닌 값만 추출된다

          ⁕! 는 not의 의미

        -범위 조건: WHERE created_at between '2020-07-13' and '2020-07-15'

         →이렇게 하게되면, 13일 14일 데이터가 추출된다. 15일까지 추출하고 싶으면 and '2020-07-16'까지!

        -(문자열) 패턴 조건 걸어보기: WHERE name like '황%'

            ⁕ %는 like과 연결하여 앞뒤로 해당 글자가 들어간 데이터를 뽑아온다

 

     4) 이외 유용한 문법

       -Limit: 테이블 중 일부 데이터만 추출하는 함수

       -distinct: 중복된 데이터는 제외하고 추출하는 함수

       -count: 몇개인지 세어보는 함수

 

2. Group by, Order by

     1) Group by: 동일한 범주를 갖는 데이터를 하나로 묶어서, 범주별 통계를 내주는 것

         → Group by를 이용하면 ① 같은 성씨의 데이터를 하나로 묶고 ② 각 성씨의 회원수를 구할 수 있다

         → 스파르타 코딩클럽의 user 성씨는 총 54개, 만약 하나하나 구할려면 54개의 쿼리를 작성해야함

         ⁕Group by의 실행 순서 from → group by → select

           이때, count(*)은 group by를한 데이터의 개수를 세어주는 것! 

         -min: 최솟값을 알고싶을 때

            Ex. select week, min(likes) from checkins

                group by week

         -max: 최댓값을 알고싶을 때

         -avg: 평균을 알고싶을 때

         -sum: 합계를 알고싶을 때

     

     2) Order by: 정렬해주는 문법

         ⁕Order by의 실행 순서 from → group by → select → order by

         -order by 정렬할 필드 desc: 내림차순

         -order by 정렬할 필드 asc: 오름차순

 

     3) 이외 유용한 문법

         -as: select문에 사용되어 alias(별칭)을 지정할 수 있다 

     

2. Join(Left/Inner), Union

     1) Join

        : 두 테이블의 공통된 정보(key값)를 기준으로 테이블을 연결해서 한 테이블 처럼 보는 것

         →join의 실행 순서는 항상 from과 붙어다닌다!

         ⁕From에 들어간 데이터를 기준으로, join의 테이블이 붙는다!

         -Left join: A데이터에 B데이터를 붙이고 중복되지 않은 값도 그대로 살리는 것

         -Inner join:  A데이터와 B데이터의 교집합만 추출하는 것(NULL 미추출)

         -NULL

            .Where pu.point is NULL : NULL값만 추출

            .Where pu.point is not NULL : NULL이 아닌 값만 추출

     

     2) Union

        : 서로다른 select문을 이어주는 문법, (Select ~~) Union all (Select ~~)로 사용

 

4. Subquery

   ⁕Subquery란 쿼리안에 쿼리!

     Q. kakaopay로 결제한 유저들의 정보 보기

     A-1. inner join 사용

           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'

 

    A-2. Subquery 사용

        SELECT u.user_id, u.name, u.email from users u 
        WHERE u.user_id in (SELECT user_id from orders o
                                   WHERE o.payment_method = 'kakaopay'
                                   )

        →(1) kakaopay로 결제한 user_id를 추출하는 쿼리를 K라고 하면,

            SELECT user_id from orders o
            WHERE o.payment_method = 'kakaopay'

 

          (2) 그 후에, user_id가 K에 있는 유저들만 골라보기
            SELECT u.user_id, u.name, u.email from users u 
            WHERE u.user_id in (SELECT user_id from orders o
                                       WHERE o.payment_method = 'kakaopay'
                                      )

    1) Where절에 들어가는 Subquery: Subquery의 결과를 조건에 활용하는 방식으로 사용

        → Where 필드명 in (Subquery)

        ⁕Where은 조건문!

 

        Q. 카카오페이로 결제한 주문건 유저들만, 유저 테이블에서 출력해라!

        A.

        SELECT * from users u 
        WHERE u.user_id in (SELECT user_id from orders o
                                   WHERE o.payment_method = 'kakaopay'
                                  )

        ▶쿼리 실행 순서

           (1) from 실행: users의 테이블을 가져와줌

           (2) Subquery실행: 해당되는 user_id의 명단을 뽑아줌

           (3) where .. in 절에서 Subquery의 결과에 해당하는 'u.user_id의 명단' 조건으로 필터링 해줌

           (4) 조건에 맞는 결과 출력

    2) Select절에 들어가는 Subquery: 기존 테이블에 함께 보고싶은 통계 테이터를 손쉽게 붙이는데 활용

       → Select 필드명, 필드명, (Subquery) from .. 

       ⁕ Select는 결과를 추출해주는 문법!

 

        Q. '오늘의 다짐' 데이터를 보고 싶은데, '오늘의 다짐' 좋아요의 수가, 본인이 평소에 받았던 좋아요의 수에 비해

           얼마나 높고 낮은지 궁금해!

        A.

            SELECT c.checkin_id,

                      c.user_id,

                      round((SELECT avg(likes) from checkins c2
                               WHERE c.user_id = c2.user_id 
                                ),1) as avg_like_user
            FROM checkins c 

        ▶쿼리 실행 순서

           (1) 밖의 select * from 문에서 데이터를 한줄한줄 출력하는 과정에서

           (2) select 안의 subquery가 매 데이터 한줄마다 실행되는데

           (3) 그 데이터 한 줄의 user_id를 갖는 데이터의 평균 좋아요 값을 subquery에서 계산해서

           (4) 함께 출력해준다!

 

    3) (가장 많이 활용) From에 들어가는 Subquery: 내가 만든 Select와 이미 있는 테이블을 Join하고 싶을 때 사용

 

        Q-1. 유저 별 좋아요 평균 

        A.

          SELECT c.user_id, round(avg(c.likes),1) as avg_like FROM checkins c 
          group by c.user_id 

 

        Q-2. Q-1에서 추출한 유저별 포인트가 궁금해

        A.

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

        ▶쿼리 실행 순서

           (1) 먼저 서브쿼리의 select가 실행되고,

           (2) 이것을 테이블처럼 여기고 밖의 select가 실행!

 

    4) with절로 더 깔끔하게 쿼리문 작성하기!

       : 보기어려운 inner join 중첩은 with로 깔끔하게 정리가 가능하다

         → with는 임시 테이블을 생성해주는 문법 : with 테이블명 as (select .. )

 

       (1) 아래와 같이 inner 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

 

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

 

    5) 문자열 데이터 다뤄보기

         ▶SUBSTRING_INDEX문법 활용

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

          select user_id, email, SUBSTRING_INDEX(email, '@', 1) from users
          → @ 기준으로 텍스트를 쪼개고 1번째 조각을 가져오라는 뜻

 

          (2) 이메일에서 도메인만 가져오기
          select user_id, email, SUBSTRING_INDEX(email, '@', -1) from users

          → @ 기준으로 텍스트를 쪼개고 그 중 마지막 조각을 가져오라는 뜻

 

         ▶SUBSTRING 문법 활용    

          (1) orders 테이블에서 날짜까지만 출력하기

          select order_no, created_at, substring(created_at,1,10) as date from orders

 

          (2) 일별로 몇개씩 주문이 일어났는지 추출하기

          select substring(created_at,1,10) as date, count(*) as cnt_date from orders
          group by date

 

    5) CASE: 경우에 따라 원하는 값을 새 필드에 출력해보기

        → case when 조건 the 출력값 else 출력값 END as 별칭

          Q. 10000점보다 높은 포인트를 가지고 있으면 '잘 하고 있어요!', 평균보다 낮으면 '조금 더 달려주세요!'

              라고 표시해 주려면 어떻게 해야할까요?

          A. 

            select pu.point_user_id, pu.point,
            case 
            when pu.point > 10000 then '잘 하고 있어요!'
            else '조금 더 달려주세요!'
            END as '구분'
            from point_users pu

 

        ▶ Subquery와 함께 CASE 사용하기!

            select lv, count(*) as cnt from (
            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
            ) a
            group by lv

 

        ▶ Subquery와 함께 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 lv, count(*) as cnt from table1
            group by lv