[스파르타코딩클럽] 엑셀보다 쉬운 SQL, 4주차 (마지막)

DK·2022년 9월 26일
SQL자격증sql개발자스파르타코딩클럽엑셀보다쉬운sql자기개발코딩공부
0

[스파르타코딩클럽] 엑셀보다 쉬운 SQL

목록 보기
5/5

어언 4주(?)가 흘렀다... 정확하게는 시작하고 9일차를 맞이하게 되었는데 "엑셀보다 쉬운 SQL"수업이 모두 끝났다. 생각보다 시간을 많이 투자하지 않고 수강을 완료한 것같다. 다른 수업들보다 SQL수업이 쉬워서 그런지 몰라도 빠르게 끝낼 수 있었다.

나는 애초에 SQL 개발자 자격증을 따기 위해 수업을 내일배움카드를 통해 수강신청을 한것이었는데 실전을 이론 배우기 전부터 해서 그런지 몰라도 빠르게 습득 한것 같다. 첫 수업에서 백번의 이론 공부보다는 한번의 실전이 더 도움된다는 것에 백만번 동감한다.

마지막 수업의 숙제는 이제까지 배운 SQL 문법들을 정리하는 것이다. 배웠던 것들을 아래와 같이 정리해보려한다.

<1주차>

■ show : 데이터에 있는 목록들 불러오기 

show tables;

■ select : 특정 테이블에 있는 데이터를 가져오기 

select * from orders;

■ 특정 필드 가져오기 : 

select created_at, course_title, payment_method, email from orders;

■ where : Select 쿼리문으로 가져올 데이터에 조건을 걸어주는것

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

■ [where 절과 자주 쓰는 문법]

  • 같지 않음 : !=
select * from orders
where course_title != "웹개발 종합반";
👉 잠깐 상식! '!=' 에서 ! (느낌표)는 부정 (not)을 의미합니다. '='는 같음을 의미하니, '!='는 같지 않음이겠죠!
  • 범위 : between ... and ...
select * from orders
where created_at between "2020-07-13" and "2020-07-15";
  • 포함 : in
select * from checkins 
where week in (1, 3);
  • 패턴 : like
select * from users 
where email like '%daum.net';
👉 Like는 패턴으로 조건을 거는 문법으로, 사용법이 아주 다양!
  • where email like 'a%': email 필드값이 a로 시작하는 모든 데이터
  • where email like '%a' email 필드값이 a로 끝나는 모든 데이터
  • where email like '%co%' email 필드값에 co를 포함하는 모든 데이터
  • where email like 'a%o' email 필드값이 a로 시작하고 o로 끝나는 모든 데이터
👉 이외에도 여러 문법이 있는데, 그때그때 필요한 것을 찾아서 쓰면 됨! ('how to use like in sql' 구글링!)

■ limit : 모든 데이터를 불러오면 시간이 걸릴 수 있으니 조금의 데이터만 불러오기

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

■ distinct : 중복 데이터는 제외하고 가져오기

select distinct(payment_method) from orders;

■ count : 몇개인지 숫자 세보기

select count(*) from orders

<2주차>

■ group by : 동일한 범주를 갖는 데이터를 하나로 묶어서, 범주별 통계를 내주는 것

select name, count(*) from users
group by name;
  • 최소값 구하기
select week, min(likes) from checkins
group by week;
  • 최대값 구하기
select week, max(likes) from checkins
group by week;
  • 평균값 구하기
select week, avg(likes) from checkins
group by week;
  • 합계 구하기
select week, sum(likes) from checkins
group by week;

■ order by : 정렬 기능 (desc = 내림차순, 디폴트는 오름차순)

select * from checkins
order by likes desc;
  • 규칙:
select * from 테이블명
order by 정렬의 기준이 될 필드명;
  • SQL 쿼리가 실행되는 순서 : 
select name, count(*) from users
group by name
order by count(*);
👉 위 쿼리가 실행되는 순서: from → group by → select → order by

<3주차>

■ join : 두 테이블의 공통된 정보 (key값)를 기준으로 테이블을 연결해서 한 테이블처럼 보는 것을 의미 (엑셀의 vlookup 기능이라고 생각하면 됨)

■ left join :

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

*어떤 데이터는 모든 필드가 채워져있지만, 어떤 데이터는 비어있는 필드가 있음.
꽉찬 데이터: 해당 데이터의 user_id 필드값이 point_users 테이블에 존재해서 연결한 경우
비어있는 데이터: 해당 데이터의 user_id 필드값이 point_users 테이블에 존재하지 않는 경우

■ inner join :

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

*여기서는 비어있는 필드가 있는 데이터가 없음.
그 이유는, 같은 user_id를 두 테이블에서 모두 가지고 있는 데이터만 출력했기 때문

  • SQL 쿼리가 실행되는 순서 : 
select * from enrolleds e
inner join courses c
on e.course_id = c.course_id;
👉 위 쿼리가 실행되는 순서: from → join → select 
select u.name, count(u.name) as count_name from orders o
inner join users u
on o.user_id = u.user_id 
where u.email like '%naver.com'
group by u.name
👉 위 쿼리가 실행되는 순서: from → join → where → group by → select

■ Union : Select를 두 번 할 게 아니라, 한번에 모아서 보고싶은 경우

(
	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
)
``````sql
(
	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주차>

■ subquery?
Subquery란? 쿼리 안의 쿼리라는 의미. 하위 쿼리의 결과를 상위 쿼리에서 사용하면, SQL 쿼리가 훨씬 간단해짐

■ where 에 들어가는 Subquery.
Where은 조건문! Subquery의 결과를 조건에 활용하는 방식으로 유용하게 사용.
where 필드명 in (subquery) 이런 방식으로...

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

*↑위 쿼리가 실행되는 순서
(1) from 실행: users 데이터를 가져와줌
(2) Subquery 실행: 해당되는 user_id의 명단을 뽑아줌
(3) where .. in 절에서 subquery의 결과에 해당되는 'user_id의 명단' 조건으로 필터링 해줌
(4) 조건에 맞는 결과 출력

■ Select 에 들어가는 Subquery
Select는 결과를 출력해주는 부분! 기존 테이블에 함께 보고싶은 통계 데이터를 손쉽게 붙이는 것에 사용
select 필드명, 필드명, (subquery) from .. 이렇게!

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;

*↑위 쿼리가 실행되는 순서
(1) 밖의 select * from 문에서 데이터를 한줄한줄 출력하는 과정에서
(2) select 안의 subquery가 매 데이터 한줄마다 실행되는데
(3) 그 데이터 한 줄의 user_id를 갖는 데이터의 평균 좋아요 값을 subquery에서 계산해서
(4) 함께 출력해준다!

■ From 에 들어가는 Subquery (가장 많이 사용되는 유형!)
내가 만든 Select와 이미 있는 테이블을 Join하고 싶을 때 사용

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

*↑쿼리가 실행되는 순서
(1) 먼저 서브쿼리의 select가 실행되고,
(2) 이것을 테이블처럼 여기고 밖의 select가 실행!

■ with : 복잡한 서브쿼리를 깔끔하게 정리해서 사용할 수 있는 문법

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를 사용한다면??

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

이렇게 쓸 수 있다

■ substring_index : 문자열 쪼개기

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

-> @를 기준으로 텍스트를 쪼개고, 그 중 첫 번째 조각을 가져오라는 뜻!_

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

-> @를 기준으로 텍스트를 쪼개고, 그 중 마지막 조각을 가져오라는 뜻!_

■ substring : 문자열 일부만 출력하기
_예시. 

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

--> SUBSTRING(문자열, 출력을 하고싶은 첫 글자의 위치, 몇개의 글자를 출력하고 싶은지)
`

Example

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

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

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

■ 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

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
profile
DK
개발자.. 가능할까?
이전 포스트

[스파르타코딩클럽] 엑셀보다 쉬운 SQL, 3주차

0개의 댓글