SQL

SQL이란?

Structured Query Language의 약자.

데이터베이스에 query를 해서 원하는 데이터를 가져오는 것을 도와주는 언어!

 

SQL를 사용할 수 있는 도구로 DBeaver를 사용.

 

---

 

 

Show tables (테이블 보기)

 

 

Select * from orders (테이블 데이터 가져오기)

Ex. Select * from orders

      Select distinct(payment_method) from orders (중복 데이터 제외)

      Select count(*) from orders (개수)

      Select count(distinct(name)) from orders

      Select count(*) as cnt from orders (Alias – as 별칭)

      Select user_id, email, SUBSTRING_INDEX(email, ‘@’, 1/(-1)) from users  (@를 기준으로 텍스트를 쪼개고, 그 중 첫 번째(1)/마지막(-1) 조각을 가져오라는 뜻)

      select order_no, created_at, substring(created_at,1,10) as date from orders (문자열, 출력 원하는 첫 글자의 위치, 출력 글자 수)

      select pu.point_user_id, pu.point,

            case (특정 조건)

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

            else '조금 더 달려주세요!'

            END as '구분'

      from point_users pu;

 

 

Where (조건)

Ex. Select * from orders

      Where payment_method = ‘kakaopay’(‘ ’가 없을 경우, 컬럼(필드)로 인식하므로, ‘ ‘로 문자열로 인식시켜주기)

      And point > 20000 (수식 조건도 가능)

      And name != ‘김**’ (같지 않음은 != 으로 기입)

      And create_at between ‘2020-07-14’ and ‘2021-01-21’ (범위 조건도 가능)

      And week in (1,3) (포함 조건도 가능)

      And email like ‘%daum.net’ (패턴-문자열 규칙 조건도 가능, ‘a%’, ‘%a’, ‘%co%’, ‘a%o’)

      + limit 10 (출력할 숫자)

 

 

Group by (범주별)

Ex. Select name, count(*)(③) from(①) users

      Group by name(②)

      Select week, min/avg/max/sum(llikes) from checkins

      Group by week

 

 

Order by (정렬)

Ex. Select name, count(*)(③) from(①) users

    Group by name(②)

    Order by count(*)(④) (count(*) 값을 기준으로 정렬)

    Order by count(*) desc (내림차순으로 정렬)

 

 

Join (key값 기준으로 테이블 합치기)

Ex. Select(③) * from(①) point_users

      left join(②) users on point_users.user_id = users.user_id (한쪽으로 합치기)

 

      select * from users u

      inner join point_users p on u.user_id=p.user_id (교집합)

 

      Select(⑤) u.name, count(u.name) as count_name from(①) orders o

      Inner join(②) users u on o.user_id = u.user_id

      Where(③) u.email like ‘ %naver.com’

      Group by(④ u. name

 

      select c1.title, c2.week, count(*) as cnt from checkins c2

      inner join courses c1 on c2.course_id = c1.course_id

      group by c1.title, c2.week (콤마(,)로 이어서 두 개의 필드 걸기)

      order by c1.title, c2.week

 

      select 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

 

      select count(point_user_id) as pnt_user_cnt,

               count(*) as tot_user_cnt,

                round(count(point_user_id)/count(*),2) as ratio

      from users u

      left join point_users pu on u.user_id = pu.user_id

      where u.created_at between '2020-07-10' and '2020-07-20'

 

 

Union all (결과물 합치기)

Ex. (

      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

      )

 

 

Subquery (더 세부적인 데이터; 쿼리 안의 쿼리)

Ex.  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'

            → 테이블을 합친 후 값들을 필터링하여 조건에 해당하는 값을 가져오는 방식.

 

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

      where u.user_id in (

      select user_id from orders

      where payment_method = 'kakaopay'

      )

            → 테이블을 합치기 전 값들을 필터링하여 테이블로 합치는 방식.

 

 

      Where 필드명 in (subquery)

            select * from users u

           where u.user_id in (select o.user_id from orders o

            where o.payment_method = 'kakaopay');

 

 

      Select 필드명, 필드명, (subquery) 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;

 

 

      from 절 subquery

            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

 

 

      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

 

 

 

---