4th Week Homework "SQL_Grammar List up"

[SQL 문법 정리] 스파르타 코딩클럽 4주차 완료, 마지막 과제

SQL이란? Structured Query Language(구조적 질의 언어)
데이터베이스에서 정보를 얻거나 갱신하기 위해 표준 대화식으로 구성된 프로그래밍 언어.
<네이버 국어사전>

표 하나의 전체 단위를 테이블, 그 안에 있는 열 단위를 필드라고 한다.

테이블= 표 단위 ex) orders, users..

필드= 표 안에 각각의 열 단위 ex) order_no, crearted_at..

​1) 테이블 불러오기

전체 테이블 보기 > show tables

특정 테이블 전체 가져오기 > select * from orders

특정 테이블의 특정 필드 가져오기 > select created_at, course_title, email from orders

​

2) 조건 붙여서 가져오기

where

포함조건

where name in (‘신’, ‘이’)

where week in (1,3)

같지 않음 조건

where course_title != “웹개발 종합반”

범위조건

where created_at between “2020-07-13” and “2020-07-15”

where point <= 5000

​특정 문자만 포함된 조건

where email like ‘%daum.net’

갯수 세기 조건

select name, count(*) from users

​최소값 조건

select week, min(likes) from checkins

​최댓값 조건

select week, max(likes) from checkins

평균값 조건

select week, avg(likes) from checkins

반올림 조건 (소수점)

select week, round(avg(likes),1) from checkins

3)그룹으로 묶어서 보기, 정렬하기

group by, order by

이름별로 카운트 내어서 정렬하기

select name, count(*) from users

group by name

order by count(*) desc ; 내림차순 / 안 쓰면 자동 오름차순

​
4) 필드명 대체하기

Alias (별칭)

select payment_method, count(*) as cnt from orders o

where o.course_title = '앱개발 종합반'

group by payment_method

• as 뒤에 문자를 붙이면 그 문자대로 필드명에 반영된다.

• 테이블 이름 뒤에 알파벳을 붙이면 반복해서 쓸 때 알파벳으로 바꿔서 쓸 수 있다.

5) 테이블 2개 연결하기

Left join

select * from A a

Left join B b on a.공통필드 = b.공통필드

2개의 테이블 중 왼쪽 테이블에 해당된 것과 공통된 데이터를 함께 가져온다. (NULL 값을 포함) 어디에 무엇을 붙일지 순서가 중요! 엑셀의 VLOOKUP과 비슷한 원리다.

inner join

select * from A a

Inner join B b on a.공통필드 = b.공통필드

2개의 테이블 중 공통된 교집합 데이터만 가져온다. 순서는 중요치 않음. left join 보다 더 많이 쓴다.

6) 추출한 데이터 하나로 붙이기

Union

추출한 데이터 결과 2개 사이에 넣어주면 한 표로 붙일 수 있다.

order 정렬을 해놓은 것을 합쳐주진 못해서, 가져온 후에 정렬을 할 수 있다.

(A 테이블) union all (B 테이블)

(

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

order 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

order by c1.title, c2.week

)

​

7) 쿼리 조건 붙여서 가져오기

subquery

큰 쿼리 안에 들어가는 작은 쿼리.

원하는 데이터를 한 번에 추출할 수 없을 때, 실시간으로 반영해서 가져올 수 있게 된다.

​

1. where 절에 들어가는 subquery

Q. 포인트가 평균보다 많은 사람들의 데이터 추출해보기

= 전체 유저 테이블을 먼저 가져온 후, 거기서 맞는 조건을 지정해서 가져오는 원리

​

select * from point_users pu

where point > (

         select avg(point) from point_users

)

​

2. select 절에 들어가는 subquery

Q. checkins 테이블에 course_id 별 평균 likes 수 필드 우측에 붙여보기

= 전체 체크인 테이블에서 조건에 맞는 좋아요 수를 가져오는 원리

​

select c.checkin_id, c.course_id, c.user_id, c.likes,

(

select avg(likes) from checkins

where course_id = c.course_id

) as course_avg → 필드명이 길어서 바꿔줌

from checkins c

​

3. from 절에 들어가는 subquery (가장 많이 사용)

Q. checkins 테이블에 course_id별 토탈 like 개수와, 전체 인원 대비 비율 붙이기

= 체크인 테이블에서 조건에 맞는 좋아요 수를 구하고, 원하는 필드만 가져오는 원리

​

select a.course_id, 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 c

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

​

​

8) 테이블 이름 정리하기

with

테이블 이름이 길어져서 복잡할 때 보기 편하도록 정리해주는 것으로, 맨 윗줄에 작성한다.

table1, table2 형식으로 표시해서 대체할 수 있으며, 주로 inner join과 사용한다.

with table1 as( ), table2 as()

select ~~~ from table1 a

inner join table2 b on a.조건 = b.조건

​

9) 문자열 정리하기

SUBSTRING_INDEX > 문자열 쪼개기

SUBSTRING_INDEX(email, ‘@’, 1) 이메일의 @을 기준으로 앞 문자 가져오기 ( 앞이 1, 뒤가 -1 이다.)

​

SUBSTRING > 문자열 일부만 출력하기

SUBSTRING(create_at,1,10) = 1번째 자리부터 10자리만큼 가져온다.

​

CASE > 조건에 해당하면 문자열에 구간별로 표시하기

ex) 포인트가 10000점 이상이면 ‘잘 하고 있어요!’, 이하면 ‘조금만 더 파이팅’ 표시할 때

select pu.user_id, pu.point

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

        else ‘조금만 더 파이팅!’ end) as msg

   from point_users pu

​

​

10) 과제 정리

● 1주차 과제

Q. 이메일이 네이버이면서, 웹개발 종합반이면서, 카카오페이 결제자인 데이터 추출하기

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

● 2주차 과제

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

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

​
● 3주차 과제

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

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