여기 적혀 있는 모든 코드는 스파르타 강의에 있는 강의 노트를 참고했으며, 모든 코드는 예시 입니다.

1주차

1. SHOW 사용법

스파르타 데이터베이스의 테이블 보기

show tables;

2. SELECT 사용법

(1) orders 테이블의 데이터 가져와보기

select * from orders;

(2) orders 테이블의 특정 필드만 가져와보기

select created_at, course_title, payment_method, email from orders;

3. WHERE 사용법

(1) 기본적인 where 사용하기

select * from orders
where payment_method = "kakaopay";

(2) 다중 조건 사용하기

select * from orders
where course_title = "앱개발 종합반" and payment_method = "kakaopay";

(3) '같지 않음' 조건 걸어보기

select * from orders
where course_title != "웹개발 종합반";

(4) '범위' 조건 걸어보기

select * from orders
where created_at between "2020-07-13" and "2020-07-15";

(5) '포함' 조건 걸어보기

select * from checkins 
where week in (1, 3);

(6) '패턴' (문자열 규칙) 조건 걸어보기

select * from users 
where email like '%daum.net';

4. 특수 쿼리 사용법

(1) 일부 데이터만 가져오기

select * from orders 
where payment_method = "kakaopay"
limit 5;

(2) 중복 데이터는 제외하고 가져오기

select distinct(payment_method) from orders;

(3) 몇 개인지 숫자 세보기

select count(*) from orders;

(4) DISTINCT와 COUNT 함께 사용하기

select count(distinct(name)) from users;

2주차

1. GROUP BY 사용법

(1) 동일한 범주의 개수 구하기

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

(2) 동일한 범주에서의 최솟값 구하기

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

(3) 동일한 범주에서의 최댓값 구하기

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

(4) 동일한 범주의 평균 구하기

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

(5) 동일한 범주의 합계 구하기

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

2. ORDER BY 사용법

(1) 오름차순 정렬하기

select * from checkins
order by likes asc;

asc는 생략 가능

select * from checkins
order by likes;

(2) 내림차순 정렬하기

select * from checkins
order by likes desc;

3. 응용

(1) GROUP BY와 ORDER BY 같이 사용하기

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

(2) WHERE, GROUP BY, ORDER BY 같이 사용하기

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

(3) 별칭 기능 사용하기

select payment_method, count(*) as cnt from orders o
where o.course_title = '앱개발 종합반'
group by payment_method;

3주차

1. JOIN 사용법

(1) LEFT JOIN 사용하기

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

(2) INNER JOIN 사용하기

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

2. 응용

JOIN, GROUP BY, ORDER BY 모두 다중으로 사용하기

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;

3. UNION ALL 사용법

두 결과물을 하나로 합치기

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

4주차

1. SUBQUERY 사용법

(1) WHERE에 들어가는 SUBQUERY 사용하기

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

(2) SELECT에 들어가는 SUBQUERY 사용하기

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;

(3) FROM에 들어가는 SUBQUERY 사용하기

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

(4) INNER JOIN에 들어가는 SUBQUERY 사용하기

select a.course_id, b.cnt_checkins, a.cnt_total from
(
	select course_id, count(*) as cnt_total from orders
	group by course_id
) a
inner join (
	select course_id, count(distinct(user_id)) as cnt_checkins from checkins
	group by course_id
) b
on a.course_id = b.course_id;

(5) WITH절을 이용한 SUBQUERY 사용하기

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;

2. 문자열 사용법

(1) 문자열 쪼개보기

1) 이메일에서 아이디만 가져와보기

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

2) 이메일에서 이메일 도메인만 가져와보기

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

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

1) order 테이블에서 날짜까지 출력하게 해보기

select order_no, created_at, substring(created_at,1,10) as date from orders;

2) 일별로 몇 개씩 주문이 일어났는지 살펴보기

select substring(created_at,1,10) as date, count(*) as cnt_date from orders
group by date;

3. CASE 사용법

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

select pu.point_user_id, pu.point,
case 
when pu.point > 10000 then '잘 하고 있어요!'
else '조금 더 달려주세요!'
END as '구분'
from point_users pu;

(2) CASE 다중 사용하기

select pu.point_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;

(3) SUBQUERY 활용하기

select level, count(*) as cnt from (
	select pu.point_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 lv;

(4) WITH절 활용하기

with table1 as (
	select pu.point_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 level, count(*) as cnt from table1
group by lv;