<TIL> / SQL 문법 총 정리

1주차

쿼리문 : 쿼리는 질의를 뜻한다.(데이터베이스에 명령을 내리는 것을 의미!)
테이블 : 데이터가 담겨져 있는 표!(한 마디로, 엑셀의 시트명을 의미한다.)

show tables : 데이터베이스안에 있는 테이블들을 볼 수 있다.
                  * : 모든 필드라는 뜻.

     ex) select * from orders
     ->전체 테이블 중 orders 테이블을 가져와 주세요!

 

특정 필드를 가지고 오고 싶을 땐?
   -> ( *) 대신에 orders테이블에 있는 가지고오고 싶은 필드명을 넣으면 된다.
       (갖고 오고 싶은 필드가 2개 이상일 경우는 (,)를 이용해서 나열한다.)

     ex) select order_no, payment_method from orders

 

조건을 거는 where 문법.

명문고라는 '테이블'에서, 3학년이라는 '필드' 중에, '이과'반인 값만 보고싶을 때!
    ex) select * from 명문고
          where 3학년 = '이과'
    (이과에 작은따움표(')가 붙는 이유 : (')는 '문자열'이라는 뜻 !!

    (')이 없으면 프로그램이 너도 무엇을 지칭하니~? 라고 알아들음.
    ex) 명문고 = 테이블 지칭
    3학년 = 필드를 지칭
    이과 = 필드안에 있는 문자(데이터 값)이기 때문에 (')를 붙여서 쿼리문 작성!

 

and를 사용할 때 !
명문고 3학년 중에서 이과반의 여학생인 값을 보고 싶어요 ~!
    ex) select * from 명문고
          where 3학년 = '이과' and 성별 = '여'

 

between을 사용할 때 !
where 3학년 between 'a' and 'b' : 필드 데이터 중 'a' 와 'b' 사이에 있는 데이터만 출력

 

in을 사용할 때 !
where week in (1,3) : week 필드 중 데이터가 1 또는 3 인것들만 가지고 와라
  -> 1,3에 작은따움표(')붙지않은 이유 : 문자열이 숫자일 경우는 (')를 붙이지 않는다.
      문자일 경우에는 (')를 붙여준다.

= : 크거나 같다
<= : 작거나 같다
!= : 같지 않다

like의 뜻 : ~와 같은
  %의 뜻 : 앞 또는 뒤에 뭐가있던 간에~

    ex) select * from users
          where email like '%kakao.com'

  -> users테이블에서 email이 %(앞에 뭐가있던 간데) 뒤에가 kakao.com으로 끝나는
      데이터를 가져와라.

 

limit 숫자 : 해당 테이블의 많은 정보들 중에서 그 중 숫자만큼 보고싶을때 사용.

distinct : 나열되는 데이터의 값들의 중복을 제거할 때 사용.

count : 개수를 셀 때 사용.
     ex) select count(distinct(payment_method)) from orders
      -> 결과값(count의 값)이 중복을 제외하고 '4'라고 표시된다. (kakaopay, card, taxbill, money)

 

<**1주차 내용을 이용한 문제 및 답안**>

 

Q. 네이버 이메일을 사용하면서, 웹개발 종합반을 신청했고,

     결제는 카카오페이로 이뤄진주문데이터 추출하기

 

A. select * from orders
    where email like '%@naver.com' and course_title = '웹개발 종합반' and
    payment_method = 'kakaopay'

2주차

group by : 동일한 범주의 데이터를 필드별로 묶어주는. (~별 이 나오면 대개 group by)
order by : 깔끔하게 데이터를 정렬,나열 해주는.

 

max : 최대값
min : 최솟값
sum : 합계
avg : 평균

round : 소수점 정리

round를 이용해서 소숫점 정리를 해줄 때!
    ex) select week, round(avg(likes),2) from checkins

          group by week

 

order by로 정렬 후 보다 더 보기좋게 정렬시키는 단어들
desc : 내림차순
asc : 오름차순

 

<**2주차 내용을 이용한 문제 및 답안**>

 

Q. 네이버 이메일을 사용하여, 앱개발 종합반을 신청한 주문의 결제수단별 주문건수 세어보기

 

A. select payment_method, count(*) as cnt from orders
    where email like '%@naver.com' and course_title = '앱개발 종합반'
    group by payment_method

3주차

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

 

join을 사용할 때

     ex) users 테이블과 orders 테이블을 연결해서 user_id 필드를 기준으로 두 테이블을 한 눈에 보고 싶어요.

          (여러 테이블을 연결하는것도 가능)

 

join의 종류 : left join, inner join

left join : 합집합
inner join : 교집합 (주로 많이 사용됨.)

 

alias : 별칭이란 뜻.
as ~ : as 뒤에오는 문자를 as 앞의것의 별칭으로 만들어 줄 때 사용.
     ex) select count() as cnt from orders o

      -> as뒤에 붙은 cnt가 count()의 별칭이 된 것!  뿐만 아니라 orders 뒤의 o도 orders의 별칭이라는 뜻으로 표시.

 

alias(별칭) 언제쓰나요??

   ->orders라는 테이블에도 user_id라는 필드가 있고,
      users라는 다른 테이블에도 user_id라는 필드가 있을 때,
      어떤 테이블의 user_id인지 구분 시켜줄 때 사용.

 

    ex) select o.user_id, u.email, count(*) as cnt from orders o
          inner join users u on o.user_id = u.user_id
          group by o.user_id

 

union all : 쿼리문과 쿼리문을 이을때 사용.
union은 order by가 적용되지 않는다.
그래서 합칠 쿼리문에서 order by를 빼줘도 결과값이 달라지지 않는다.

 

order by 를 적용시킬려면?

    -> union all로 쿼리문을 합친 후 order by를 써야 적용이 됨.
ex)
(
select '7월' as month, 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
)
union all
(
select '8월' as month, 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
)

 

<**3주차 내용을 이용한 문제 및 답안**>

 

Q. enrolled_id별 수강완료(done=1)한 강의 갯수를 세어보고, 완료한 강의 수가 많은 순서대로 정렬해보기.

     user_id도 같이 출력되어야 한다.

 

A. select e.enrolled_id, user_id, count(done) as done from enrolleds e
    inner join enrolleds_detail ed on e.enrolled_id = ed.enrolled_id
    where done=1
    group by e.enrolled_id
    order by done desc

4주차

subquery란? : 쿼리 안의 쿼리 (쉽게 데이터를 뽑아내기 위해 사용)
subquery를 사용하지 않아도 자신이 원하는 데이터를 얻어낼 수 있다.

*subquery가 들어가는 쿼리문을 작성 시 항상 tab을 사용해서 줄을 잘 맞춰 주어야 한다.
->헷갈리지 않기 위함.

 

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'

 

위와 같은 쿼리문을 이번엔 subquery를 이용해서 만든 예시.

 

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

 

위 둘다 결과값은 같다.

 

with 구문

 

사용 전 예시.

 

ex)

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를 사용해서 괄호안의 서브쿼리들을 하나의 테이블(table1,2)로 만들어,

쿼리문 맨위에 with절로 정리한 예시.

 

ex)

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

 

-> alias로 별칭을 주면서 하나의 테이블로 사용해 with로 정리했더니
훨씬 더 한 눈에 보기 편한 쿼리문을 작성할 수 있었다.

 

SUBSTRING_INDEX(eamil,'@',1) : email을 @을 기준으로 쪼개는데, 첫번째것만 보여줘~
ex) inborn96

 

SUBSTRING_INDEX(eamil,'@',-1) : email을 @을 기준으로 쪼개는데, 뒤에것만 보여줘~
ex) daum.net

 

SUBSTRING(created_at,1,8) : created_at을 몇 번째부터 시작해서, 그 뒤로 몇 글자까지 자를건지~ (8은 개수를 뜻함.)

 

그 외 조건문

 

case when ~ then '잘 하고 있어요!'
else '힘내세요!' end

 

이렇게 1~4주차 SQL강의를 완강하면서, 일상에서 사용되는 대부분의 문법들을 다뤄보았다.

 

문법을 정리해두면 나중에 내가 필요할 때 바로바로 하나하나 꺼내쓰기에 좋을 것 같아서 총정리로 정리해본다 ㅎㅎㅎㅎㅎ