1주차

select * from 테이블 where 필드 = '내용'

테이블에 있는 필드와 일치하는 내용을 찾은 후 테이블의 모든 필드를 표시해줌

select 필드 from 테이블 where 필드 = '내용'

테이블에 있는 필드와 일치하는 내용을 찾은 후 select 뒤에 적은 필드만 표시해줌

select * from 테이블 where 필드 point 등호 숫자

테이블에 있는 필드에서 숫자보다 등호한 숫자만 표시해줌
ex) where 필드 point >= 10000
필드의 10000이상인 숫자들만 표시해줌(숫자는 '' 안씀)

select * from 테이블 where 필드1 = '내용1' and/or 필드2 = '내용2'

and : 테이블에 있는 필드 1의 내용 1과 필드 2의 내용 2을 표시해줌 (둘다일치)
or : 테이블에 있는 필드 1의 내용 1 또는 필드 2의 내용 2을 표시해줌 (둘중 하나라도 일치)

select * from 테이블 where 필드 != '내용'

같지않음 : 테이블에 있는 필드와 내용이 일치하는 내용을 뺀 후 테이블의 모든 필드를 표시해줌

where 필드 between 숫자, 날짜 and 숫자, 날짜

범위 : 필드안의 숫자 날짜 사이를 표시해줌

where 필드 in ( A, B ......)

포함 : 필드 안에서 A,B......와 일치하는 값만 표시해줌

where 필드 like '내용'

패턴 : 내용에 조건을 걸어서 필드와 일치하는 내용을 찾아줌
내용에는 %(앞에 몇글자가 나와도 상관없음)이 자주쓰임
ex) where 필드 like 'A%Z'

select * from 테이블 where 필드 = '내용' limit 숫자

테이블에 있는 필드와 일치하는 내용을 찾은 후 테이블의 모든 필드를 숫자의 수만큼만 표시해줌

select (distinct 필드) from 테이블 where 필드 = '내용'

테이블에 있는 필드와 일치하는 내용을 찾은 후 distinct 뒤에 적은 필드 중 중복된 내용을 뺀 후 표시해줌

select count(*) from 테이블 where 필드 = '내용'

테이블에 있는 필드와 일치하는 내용을 찾은 후 결과값의 수를 세어줌

select count(distinct 필드) from 테이블 where 필드 = '내용'

테이블에 있는 필드와 일치하는 내용을 찾은 후 distinct 뒤에 적은 필드 중 중복된 내용을 뺀 다음 결과값의 수를 세어줌

2주차

그룹으로 묶어주자
안헷갈리게 적는 법
먼저 그룹지정할 전체적인 그림을 그려주고

select * from users
group by name

그 그림에서보면 name과 그 수를 세고싶으니까

select name, count(*) from users
group by name

적어주자

(1)users 테이블에서 '신' 씨를 가진 데이터만 불러와서 개수 살펴보기

select name, count(*) from users
where name = '신**'

(2)group by를 사용해서 '신'씨를 가진 데이터가 몇 개인지 살펴보기

select name, count(*) from users u
where name = '신**'
group by name

(3)네이버 이메일을 사용하는 사람들을 성씨별로 살펴보기(where, group by) (예상)

select name, count(*) from users
where email like '%naver.com'
group by name

Group by 사용해보기

select week, count(*) from checkins
group by week

(1) likes의 최소값, 최대값, 평균값, 반올림, 합계
(1)-1 최소값(min())

select week, min(likes) from checkins
group by week

(1)-2 최대값(max())

select week, max(likes) from checkins
group by week

(1)-3 평균값(avg())

select week, avg(likes) from checkins
group by week

(1)-4 반올림(round(값,자릿수))

select week, round(avg(likes),2) from checkins
group by week

(1)-5 합계(sum())

select week, sum(likes) from checkins
group by week

order by 사용해보기

(1) 오름차순 (asc 안써도됨)

select name, count() from users
group by name
order by count()

(2) 내림차순 (desc)

select name, count() from users
group by name
order by count() desc

select * from checkins
order by likes desc

※마지막에 ;을 붙이는 이유

select name, count() from users
group by name
order by count();
한 쿼리가 여기서 끝난다는걸 알려줌
굳이 안써도 됨

Where와 Group by, Order by 함께 사용해보기

select payment_method ,count() from orders
where course_title = '웹개발 종합반'
group by payment_method
order by count()

자주발생하는 에러

1) select from orders
group by payment_method
라 하면 통계낼것을 지정하지 않아 대표적인것 하나씩만 보여준다

2) select count() from orders
group by payment_method
뭘 통계냈는지 모르고 숫자 센것만 보여준다.

별칭기능 : alias

(보통 알파벳 1, 2글자로 짧게 씀)
(1) 첫번째 별칭

select * from orders o
where o.course_title = '앱개발 종합반'
orders를 o로 별칭을 지정 후 orders에 있는 자료를 사용함

(2) 두번째 별칭

select payment_method , count(*) as cnt from orders o
where o.course_title = '앱개발 종합반'
group by payment_method
as를 사용해서 따로 열의 이름을 지어줄 수 있다.

3주차

Join

Left Join과 Inner Join 2가지가 있다.

1) Left Join

왼쪽을 기준으로 오른쪽을 붙인다.

select * from users u
left join point_users pu
on u.user_id = pu.user_id

2) Inner Join

둘이 중첩된 것을 나타낸다.

select * from users u
inner join point_users pu
on u.user_id = pu.user_id

실습 1)
orders 테이블에 users 테이블 연결해보기

select * from orders o
inner join users u
on o.user_id = u.user_id

※inner join이 조금 더 쉽다 처음 연습할때는 inner join으로 하자

실습 2)
checkins 테이블에 users 테이블 연결해보기

select * from checkins c
inner join users u
on c.user_id = u.user_id

실습 3)
enrolleds 테이블에 courses 테이블 연결해보기

select * from enrolleds e
inner join courses c
on e.course_id =c.course_id

배웠던 문법을 Join과 함께 사용해보기

1) checkins 테이블에 courses 테이블 연결해서 통계치 내보기
과목별 오늘의 다짐 갯수 세어보기

select c1.course_id, c2.title, count(*) as cnt from checkins c1
inner join courses c2
on c1.course_id = c2.course_id
group by c1.course_id

2) point_users 테이블에 users 테이블 연결해서 순서대로 정렬해보기
많은 포인트를 얻은 순서대로 유저 데이터 정렬해서 보기

select pu.user_id, u.name, u.email , pu.point from point_users pu
inner join users u
on pu.user_id = u.user_id
order by pu.point DESC

3) orders 테이블에 users 테이블 연결해서 통계치 내보기
네이버 이메일 사용하는 유저의 성씨별 주문건수 세어보기

select u.name , count(*) as cnt from orders o
inner join users u
on o.user_id = u.user_id
where o.email like '%naver.com'
group by u.name

퀴즈

Join 연습1
결제 수단 별 유저 포인트의 평균값 구해보기
join 할 테이블: point_users 에, orders 를 붙이기

select o.payment_method, round(avg(pu.point),0) as avg_point from point_users pu
inner join orders o
on pu.user_id = o.user_id
group BY o.payment_method

Join 연습2
결제하고 시작하지 않은 유저들을 성씨별로 세어보기
join 할 테이블: enrolleds 에, users 를 붙이기

select u.name, count(*) as cnt_name from enrolleds e
inner join users u on e.user_id = u.user_id
where e.is_registered = 0
group by u.name
order by cnt_name desc

Join 연습3
과목 별로 시작하지 않은 유저들을 세어보기
join 할 테이블: courses에, enrolleds 를 붙이기

select e.course_id , c.title , count(*) as cnt_notstart from courses c
inner join enrolleds e on c.course_id =e.course_id
where e.is_registered = 0
group by c.course_id

Join 연습4
웹개발, 앱개발 종합반의 week 별 체크인 수를 세어볼까요? 보기 좋게 정리해보기!
join 할 테이블: courses에, checkins 를 붙이기

select c1.title, c2.week, count(*) as cnt from courses c1
inner join checkins c2 on c1.course_id = c2.course_id
group by c1.title , c2.week
order by c1.title , c2.week

Join 연습5
연습4번에서, 8월 1일 이후에 구매한 고객들만 발라내어 보세요!
join 할 테이블: courses에, checkins 를 붙이고! + checkins 에, orders 를 한번 더 붙이기!

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

Left Join

left join은 어디에 → 뭐를 붙일건지, 순서가 중요하다
회원인데 포인트가 없는 사람
1) 포인트가 없는사람

select from users u
left join point_users pu on u.user_id = pu.user_id
where pu.point_user_id is NULL

2) 회원인데 포인트가 없는 사람

select u.name , count() as cnt from users u
left join point_users pu on u.user_id = pu.user_id
where pu.point_user_id is NULL
group by u.name

3) 포인트가 있는사람들만

select u.name , count(*) as cnt from users u
left join point_users pu on u.user_id = pu.user_id
where pu.point_user_id is not NULL
group by u.name

퀴즈
7월10일 ~ 7월19일에 가입한 고객 중,
포인트를 가진 고객의 숫자, 그리고 전체 숫자, 그리고 비율을 보고 싶어요!

select count(pu.point_user_id) as pnt_user_cnt,
count(u.user_id) as tot_user_cnt,
round(count(pu.point)/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 배우기

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

이렇게 하면 내부 order는 안먹히게 된다. 즉 빼도 같은 값이 나온다.
그러므로 order을 빼고 밖에서 다시 지정해주면 된다.

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

4주차

Subquery란? 쿼리 안의 쿼리라는 의미입니다.

즉, Subquery를 사용하지 않아도 원하는 데이터를 얻어낼 수 있겠지만, 더 편하고 간단하게 원하는 데이터를 얻기 위해 사용되는 파워풀한 기능입니다.
Subquery에 대한 이해도가 생기면, With구문을 이용해서 더 간단하게 만들어볼게요!

원하는 데이터를 더 쉽게: Subquery

1) Subquery 사용방법 익혀보기

2) Where 에 들어가는 Subquery
kakaopay로 결제한 유저들의 정보 보기

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 * from users u
where u.user_id in (.....)
in(.....)안의 내용만 내용전체를 추출할 수 있다.

예시)select * from users u
where u.user_id in ('3b3eac9f','afb35ce0','81bde520')

그 안에
select o.user_id from orders o
where payment_method = 'kakaopay'를 넣어서 추출하면 된다.

select * from users u
where u.user_id in (
select o.user_id from orders o
where payment_method = 'kakaopay'
)

저 줄만 실행하고 싶을땐 드래그 후 컨트롤 엔터 하면 된다.

subquery는 select절, from절, where절 어디든 들어갈 수 있다.

2) Select 에 들어가는 Subquery

select avg(likes) from checkins c
where user_id = '4b8a10e6'

select c.checkin_id ,
c.user_id ,
c.likes,
(
select avg(likes) from checkins
where user_id = c.user_id
) as avg_likes_user
from checkins c

subquery가 select 안에서 하나하나 다 실행이 된다.

3) From 에 들어가는 Subquery (가장 많이 사용되는 유형!)
우선 유저 별 좋아요 평균을 먼저 구해볼까요?

select user_id, round(avg(likes),1) as avg_likes from checkins c
group by user_id

from 뒤에 이미 있는 절처럼 사용해보자

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

연습 1 Where 절에 들어가는 Subquery 연습해보기

1-1) 전체 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기

먼저 전체유저의 포인트의 평균을 구한다

select avg(point) from point_users pu

그리고 그 내용을 subquery로 넣는다.

select * from point_users pu
where point > (
select avg(point) from point_users pu
)

1-2) 이씨 성을 가진 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기

(1) join으로 풀어보기

select avg(point) from point_users pu
inner join users u on pu.user_id = u.user_id
where u.name = '이**'

그대로 넣으면 된다.

select * from point_users pu
where point > (
select avg(point) from point_users pu
inner join users u on pu.user_id = u.user_id
where u.name = '이**'
)

(2) subquery로 풀어보기

select u.user_id from users u
where u.name = '이**'

이 자료를 포인트 평균을 구하려는 위치에 넣으면된다.

select avg(point) from point_users pu
where pu.user_id in(
select u.user_id from users u
where u.name = '이**'
)

이때 where in()을 조심하자
이 값을 비교하려는 포인트에 넣으면 된다.

select * from point_users pu
where point > (
select avg(point) from point_users pu
where pu.user_id in(
select u.user_id from users u
where u.name = '이**'
)
)

subquery 안에 subquery를 넣을 수 있다.

연습 2 Select 절에 들어가는 Subquery 연습해보기

2-1) checkins 테이블에 course_id별 평균 likes수 필드 우측에 붙여보기

먼저 유저 아이디 별 likes의 평균을 구해보자

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

그 후 값을 구하면 된다.

select c.checkin_id ,
c.course_id ,
c.user_id ,
c.likes ,
(
select round(avg(likes),1) from checkins
where course_id = c.course_id
) as course_avg
from checkins c

여기서 먼저 구했던 likes의 평균을 잘 짜야된다.

2-2) checkins 테이블에 과목명별 평균 likes수 필드 우측에 붙여보기(나한텐 조금 어려웠다)

2-1)에서 만들어진 자료를 토대로 밑에 inner를 만들고 그 값에서 title을 가져오면 된다.
select c.checkin_id ,
c2.title ,
c.user_id ,
c.likes ,
(
select round(avg(likes),1) from checkins
where course_id = c.course_id
) as course_avg
from checkins c
inner join courses c2 on c2.course_id = c.course_id

연습 3 From 절에 들어가는 Subquery 연습해보기

1) [준비1] course_id별 유저의 체크인 개수를 구해보기!

select course_id , count(DISTINCT(user_id)) as cnt_checkins from checkins c
group by course_id

distinct()로 겹치지 않는 유저 아이디를 count하면 된다.

2) [준비2] course_id별 인원을 구해보기!

select course_id , count(*) from orders o
group by course_id

3) [진짜 하고 싶은 것] course_id별 like 개수에 전체 인원을 붙이기

select a.course_id, a.cnt_checkins, b.cnt_total
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 o
group by course_id
) b
on a.course_id = b.course_id

4) [한 걸음 더] 퍼센트를 나타내기

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 o
group by course_id
) b
on a.course_id = b.course_id

5) [반 걸음 더] 앗, 강의 제목도 나타나면 좋겠네요!

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 c
group by course_id
) a
inner join (
select course_id , count(*) as cnt_total from orders o
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

subquery를 alias처럼 쓸수있다.

실전에서 유용한 SQL 문법 (문자열, Case)
1) 문자열 쪼개보기(SUBSTRING_INDEX())

select user_id, email, SUBSTRING_INDEX(email,'@',1) from users u

숫자 1의 의미 : email의 @를 기준으로 앞에걸 보여줘

숫자 -1의 의미 : email의 @를 기준으로 뒤에걸 보여줘

2) 문자열 일부만 출력하기(substring())

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

string()은 엑셀의 mid()랑 사용법이 같다

select SUBSTRING(created_at, 1, 10) as date, count(*) from orders o
group by date

이런식으로 날짜별 종합해볼 수도 있다.

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

select pu.user_id, pu.point,
(case when pu.point > 10000 then '잘 하고 있어요!'
else '조금만 더 파이팅!' end) as msg
from point_users pu

4) case + subquery 로 통계를 낼 수 있다.

(1)
select pu.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

(2) 구간별 몇명인지 확인할 수 있다.
select a.lv, count(*) from (
select pu.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 a.lv

(3) with절로 만들어서 사용할 수 있다.
with table1 as (
select pu.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 a.lv, count(*) as cnt from table1 a
group by a.lv

초급퀴즈
퀴즈 1 평균 이상 포인트를 가지고 있으면 '잘 하고 있어요' / 낮으면 '열심히 합시다!' 표시하기!

select pu.user_id,
pu.point,
(case when pu.point > (select avg(point) from point_users pu2 ) then '잘 하고 있어요'
else '열심히 합시다!' end) as msg
from point_users pu

퀴즈 2 이메일 도메인별 유저의 수 세어보기

select domain, count() as cnt_domain
from (
select substring_index(email,'@',-1) as domain from users u2
) a
group by domain

select SUBSTRING_INDEX(email,'@',-1) as domain, count() as cnt_domain
from users u
group by SUBSTRING_INDEX(email,'@',-1)
이거로도 같은 값이 나온다

퀴즈 3 '화이팅'이 포함된 오늘의 다짐만 출력해보기

select * from checkins c
where comment like '%화이팅%'

중급 퀴즈

퀴즈 1 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수 출력해보기

select ed1.enrolled_id, count() as done_cnt, a.total_cnt from enrolleds_detail ed1
inner join (
select ed2.enrolled_id, count() as total_cnt from enrolleds_detail ed2
group by ed2.enrolled_id
) a on ed1.enrolled_id = a.enrolled_id
where ed1.done =1
group by ed1.enrolled_id

select a.enrolled_id, a.done_cnt, b.total_cnt from (
select ed1.enrolled_id, count() as done_cnt from enrolleds_detail ed1
where ed1.done =1
group by ed1.enrolled_id
) a
inner JOIN (
select ed2.enrolled_id, count() as total_cnt from enrolleds_detail ed2
group by ed2.enrolled_id
) b
on a.enrolled_id = b.enrolled_id

이렇게 해도 같다

퀴즈 2 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수, 그리고 진도율 출력해보기

select ed1.enrolled_id, count() as done_cnt, a.total_cnt, round((count()/a.total_cnt),2) as ratio
from enrolleds_detail ed1
inner join (
select ed2.enrolled_id, count(*) as total_cnt from enrolleds_detail ed2
group by ed2.enrolled_id
) a on ed1.enrolled_id = a.enrolled_id
where ed1.done =1
group by ed1.enrolled_id

select a.enrolled_id, a.done_cnt, b.total_cnt, round((a.done_cnt/b.total_cnt),2) as ratio from (
select ed1.enrolled_id, count() as done_cnt from enrolleds_detail ed1
where ed1.done =1
group by ed1.enrolled_id
) a
inner JOIN (
select ed2.enrolled_id, count() as total_cnt from enrolleds_detail ed2
group by ed2.enrolled_id
) b
on a.enrolled_id = b.enrolled_id

이렇게 해도 같다

추가) with절 사용

with table1 as (
select ed1.enrolled_id, count() as done_cnt from enrolleds_detail ed1
where ed1.done =1
group by ed1.enrolled_id
), table2 as (
select ed2.enrolled_id, count() as total_cnt from enrolleds_detail ed2
group by ed2.enrolled_id
)

select a.enrolled_id,
a.done_cnt,
b.total_cnt, round((a.done_cnt/b.total_cnt),2) as ratio
from table1 a
inner JOIN table2 b on a.enrolled_id = b.enrolled_id

with절이 위에 있어야 한다.

추가) 그러나, 더 간단하게 만들 수 있지 않을까!

select enrolled_id,
sum(done) as done_cnt,
count() as total_cnt,
round(sum(done)/count(),2) as ratio
from enrolleds_detail ed
group by enrolled_id