Kim NaRae

배울때마다 기록해두기

1. 기본

-- table 목록 확인하기
show tables

-- table 열기 
select * from tbl1

-- 처음 몇 개의 행만 열기 
select * from tbl1 limit 10

2. Select

1) 필요한 필드 추출하기

테이블 tb1l에서 var1, var2를 추출하자.

select var1, var2 from tbl1

2) 통계

  • as 이름을 통해 통계낸 필드의 이름을 정할 수 있다.
  • 테이블 tbl1에서 course_title별 개수를 cnt 필드로 추출하자. 
    select course_title, count(course_title) as cnt from tbl1
함수 설명
count()/avg()/min()/max() 개수/평균/최소값/최대값
round(var1,n) var1을 n자리까지 나타낸다.
distinct(var1) var1의 종류를 나타낸다.
ex)distinct(payment_method), 결제수단 종류
substring_index(var1,’구분값’,1 or -1) var1에서 ‘구분값’을 기준으로 1(왼쪽)/-1(오른쪽) 문자를 추출한다.
substring(var1, n1, n2) var1에서 n1번째부터 n2번째까지의 문자만 추출한다.

3. Where

조건 설명
=/!= 같다/같지 않다.
>/>=/</<= 크다/크거나 같다/작다/작거나 같다
is NULL/is not NULL NULL이면 TRUE/NULL이 아니면 TRUE
between a and b a보다 크거나 같고 b보다 작다
like ‘패턴’ 패턴과 같으면 TRUE
ex)’%naver.com’ 이메일 주소가 네이버이면 TRUE
in (a, b, c) a이거나 b 또는 c이면 TRUE

4. Group by / Order by

1) Group by

  • where 문도 같이 쓸 경우 group by보다 선행되어야 한다.
  • 테이블 tbl1에서 grp별 개수를 구해보자. 
    select grp, count(*) as cnt from tbl1
group by grp

2) Order by

  • 정렬은 맨 마지막에 실행되어야 한다.
  • 기본값은 오름차순, 내림차순은 끝에 desc를 추가한다.
  • var1에 대해 내림차순으로 정렬해보자. 
    select * from tbl1
order by var1 desc

6. Inner Join / Left Join

1) Inner Join

  • 두 테이블에 모두 존재하는 key만 결합한다.
  • 테이블의 별칭을 이용하면 편리하다.
  • table1 : mykey, var1, var2
    table2 : mykey, var3, var4
    table1(별칭, t1)과 table2(별칭, t2)를 inner join해보자. 
    select * from table1 t1
inner join table2 t2 on t1.mykey = t2.mykey

2) Left Join

  • 왼쪽 테이블의 key에 맞춰 결합한다.
  • 왼쪽 테이블의 key에 맞는 오른쪽 테이블의 정보가 없으면 NULL로 표현된다.
  • table1 : mykey, var1, var2
    table2 : mykey, var3, var4
    table1(별칭, t1)과 table2(별칭, t2)를 left join해보자. 
    select * from table1 t1
left join table2 t2 on t1.mykey = t2.mykey

7. Union

  • 테이블을 위 아래로 합칠 때 사용한다.
  • table1 : mykey, var1, var2
    table2 : mykey, var1, var2
    table1(별칭, t1)과 table2(별칭, t2)를 union으로 합쳐보자. 
    (
  select * from table1 t1
)
union 
(
  select * from table2 t2
)

8. With

  • 쿼리로 이루어진 테이블들을 결합할 때 With를 이용하여 임시 테이블을 만들어 쿼리를 더 보기 쉽게 정리할 수 있다.
  • 쉽게 이해할 수 있도록 임의의 테이블, 필드명을 지정하자.
    table1: ID, course_ID, user_ID, course_title, email
    table2: ID, course_ID, user_ID, week, likes
  • course별로 강의를 등록한 사람 중 강의를 들은 사람의 비율을 구하자.
with tbl1 as (
    select course_ID, course_title, count(*) as cnt from table1
    group by course_ID
), tbl2 as (
    select course_ID, count(distinct(user_id)) as tot_cnt from table2
    group by course_ID
)

select t1.course_ID, t1.course_title, t1.cnt, t2.tot_cnt, 
       round((cnt/tot_cnt)*100,2) as perc
  from tbl1 t1
inner join tbl2 t2 on t1.course_ID = t2.course_ID

9. Case when

  • 쉽게 이해할 수 있도록 임의의 테이블, 필드명을 지정하자.
    table1: ID, name, age, email, points
  • 포인트를 5천, 1만을 기준으로 그룹을 나누자.
select ID, points, 
       case when t1.point < 5000 then '5천 미만'
            when t1.point >= 10000 then '1만 이상'
       else '5천 이상' end as lvl
  from table1

10. 복잡한 코드 정리 : Subquery

  • 쉽게 이해할 수 있도록 임의의 테이블, 필드명을 지정하자.
    table1: ID, name, age, email, points
    table2: ID, method, likes

1) Where + Subquery

  • method가 “A”인 유저의 ID, name, email만 추출하자.
select ID, name, email from table1 
 where ID in (
    select ID from table2
    where method = "A"
 )

2) Select + Subquery

  • table2에 ID에 대한 likes의 평균인 average 열을 추가하자. 이때 평균은 소수점 한자리까지 나타내자.
select t2.ID, t2.method, 
       (
        select round(avg(likes),1) from table2
        where ID = t2.ID 
       ) as average
  from table2 t2

3) From + Subquery

  • table1에 table2의 ID별 likes 평균 average를 결합하고, ID, name, points, average만 추출하자. 이때 평균은 소수점 한자리까지 나타내자.
select t1.ID, t1.points, t2.average from table1 t1
inner join (
    select ID, round(avg(likes),1) as average from table2
    group by ID
) t2 on t1.ID = t2.ID

댓글남기기

참고