✨Order by, Group by 같이 연습해보기
-
Order by 연습하기
-
문자열을 기준으로 정렬해보기
select * from users order by email;select * from users order by name; -
시간을 기준으로 정렬해보기
select * from users order by created_at desc;
-
-
Group by 연습하기
- (1) 원하는 테이블과
(2) 범주로 사용할 필드
(3) 범주에 따라 통계를 계산하고 싶은 필드 (개수의 경우 제외)
💡 이 세 가지만 기억하면 됩니다!
- 앱개발 종합반의 결제수단별 주문건수 세어보기
select payment_method, count(*) from orders where course_title = "앱개발 종합반" group by payment_method; - Gmail 을 사용하는 성씨별 회원수 세어보기
select name, count(*) from users where email like '%gmail.com' group by name; - course_id별 '오늘의 다짐'에 달린 평균 like 개수 구해보기
select course_id, avg(likes) from checkins group by course_id;
- (1) 원하는 테이블과
-
📌 꿀팁
1) show tables로 어떤 테이블이 있는지 살펴보기
2) 제일 원하는 정보가 있을 것 같은 테이블에 select * from 테이블명 limit 10 쿼리 날려보기
3) 원하는 정보가 없으면 다른 테이블에도 2)를 해보기
4) 테이블을 찾았다! 범주를 나눠서 보고싶은 필드를 찾기
5) 범주별로 통계를 보고싶은 필드를 찾기
6) SQL 쿼리 작성하기!
✨이외 유용한 문법 배워보기
- 별칭 기능: Alias
- 쿼리가 점점 길어지면서 종종 헷갈리는 일이 생길 수 있습니다.
그래서 SQL은 Alias라는 별칭 기능을 지원합니다.
- 테이블명 뒤에 as를 붙여서 별칭을 추가하는 것도 가능하고,
select * from orders o where o.course_title = '앱개발 종합반' - 출력될 필드에 별칭을 붙이는 것도 가능해요!
select payment_method, count(*) as cnt from orders o where o.course_title = '앱개발 종합반' group by payment_method
- 쿼리가 점점 길어지면서 종종 헷갈리는 일이 생길 수 있습니다.
✨끝 & 숙제 설명
👉 숙제
네이버 이메일을 사용하여 앱개발 종합반을 신청한 주문의 결제수단별 주문건수 세어보기
- 💻 출력
select payment_method, count(*) from orders where email like '%naver.com' and course_title = '앱개발 종합반' group by payment_method
✨여러 테이블을 연결해보자: Join 이란?
-
Join: 여러 정보를 한 눈에 보고 싶다면
👉 오늘의 미션!'오늘의 다짐' 이벤트 당첨자를 선정하여 스타벅스 기프티콘을 지급해야 해요.
우선, 배운 내용을 사용해서 '오늘의 다짐' 테이블을 불러와 볼까요?오늘의 다짐 이벤트: 오늘의 다짐을 남겨준 10명 추첨해서 기프티콘 지급하는 이벤트.
💡 그런데 문제가 생겼어요!
'오늘의 다짐' 이벤트 당첨자를 추첨하기 위해서는, 이름과 연락처 등의 정보를 알아야 하는데 여기에는 user_id라는 정보만 있어요.
📌 기존의 방식대로 하면
회원 정보가 필요하니 users 테이블을 한번 살펴볼까요?
똑같은 이름의 필드를 발견했어요. users 테이블의 user_id 필드와, checkins 테이블의 user_id 필드의 이름이 같아요. 이걸 잘 연결시키면 될 것 같지 않나요? -
실제로 맞는지 확인해 봅시다! where를 잘 쓰면 되겠죠?
-
👉 [오늘의 꿀팁!]
한 테이블에 모든 정보를 담을 수도 있겠지만, 불필요하게 테이블의 크기가 커져 불편해집니다.
그래서, 데이터를 종류별로 쪼개 다른 테이블에 담아놓고 연결이 필요 한 경우 연결할 수 있도록 만들어놓습니다.예를 들면, users와 checkins 테이블에 동시에 존재하는 user_id 처럼요.
이런 필드를 두 테이블을 연결시켜주는 열쇠라는 의미로 'key'라고 부릅니다. -
Join을 사용하면?
-
-
Join 이란?
Join이란?
두 테이블의 공통된 정보 (key값)를 기준으로 테이블을 연결해서 한 테이블처럼 보는 것을 의미해요.예) user_id 필드를 기준으로 users 테이블과 orders 테이블을 연결해서 한 눈에 보고 싶어요!
그럴 때를 대비해서 무언가 연결된 정보가 있을 때, user_id 처럼 동일한 이름과 정보가 담긴 필드를 두 테이블에 똑같이 담아놓는답니다. 이런 필드를 두 테이블을 연결시켜주는 열쇠라는 의미로 'key'라고 불러요.
-
아래 테이블에서 (checkins 테이블) key값에 해당하는 필드를 찾아보세요.
💡 [오늘의 꿀팁!]
병원에서 의사선생님이 '환자번호 101번님 진료받으러 들어오세요' 라고 불렀는데 같은 환자번호를 가진 사람이 여러명이 있으면 누가 들어와야 할지 환자번호만으로 알 수 없겠죠?SQL에서의 Join도 마찬가지에요.
key값을 사용해 연결하고 싶은 테이블에 찾아가서 똑같은 값을 가지는 key를 찾게 되는데, 똑같은 key를 가지는 데이터가 여러개 있으면 어느 데이터를 가져와서 연결해야 할지 알 수 없어요.
-
Join을 사용해서 Key값으로 두 테이블 연결해보기
select * from point_users left join users on point_users.user_id = users.user_id
-
-
Join의 종류: Left Join, Inner Join
-
Left Join: 유저 데이터로 Left Join 이해해보기
SQL에서의 Join은 두 집합 사이의 관계와 같답니다.
-
유저 데이터로 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: 유저 데이터로 Inner Join 이해해보기
-
유저 데이터로 Inner Join 이해해보기
select * from users u inner join point_users p on u.user_id = p.user_id;
-
-
Join 함께 연습해보기
-
orders 테이블에 users 테이블 연결해보기
👉 inner Join을 사용해서 주문 정보에, 유저 정보를 붙여서 볼까요?
select * from orders o inner join users u on o.user_id = u.user_id;
-
checkins 테이블에 users 테이블 연결해보기
👉 Inner Join을 사용해서 '오늘의 다짐' 테이블에, 유저 테이블을 붙여서 볼까요?select * from checkins c inner join users u on c.user_id = u.user_id;
-
enrolleds 테이블에 courses 테이블 연결해보기
👉 Inner Join을 사용해서 '수강 등록' 테이블에, 과목 테이블을 붙여서 볼까요?select * from enrolleds e inner join courses c on e.course_id = c.course_id;
-
-
SQL 쿼리가 실행되는 순서
select * from enrolleds e inner join courses c on e.course_id = c.course_id;👉 위 쿼리가 실행되는 순서: from → join → select
- from enrolleds: enrolleds 테이블 데이터 전체를 가져옵니다.
- inner join courses on e.course_id = c.course_id: courses를 enrolleds 테이블에 붙이는데, enrolleds 테이블의 course_id와 동일한 course_id를 갖는 courses의 테이블을 붙입니다.
- select * : 붙여진 모든 데이터를 출력합니다.
-
배웠던 문법 Join과 함께 연습해보기
-
checkins 테이블에 courses 테이블 연결해서 통계치 내보기
👉 과목별 오늘의 다짐 갯수 세어보기select co.title, count(co.title) as checkin_count from checkins ci inner join courses co on ci.course_id = co.course_id group by co.title -
point_users 테이블에 users 테이블 연결해서 순서대로 정렬해보기
👉 유저의 포인트 정보가 담긴 테이블에 유저 정보를 연결해서, 많은 포인트를 얻은 순서대로 유저의 데이터를 뽑아보자!
select * from point_users p inner join users u on p.user_id = u.user_id order by p.point desc -
orders 테이블에 users 테이블 연결해서 통계치 내보기
👉 주문 정보에 유저 정보를 연결해 네이버 이메일을 사용하는 유저 중, 성씨별 주문건수를 세어보자!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
-
-
SQL 쿼리가 실행되는 순서
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- from orders o: orders 테이블 데이터 전체를 가져오고 o라는 별칭을 붙입니다.
- inner join users u on o.user_id = u.user_id : users 테이블을 orders 테이블에 붙이는데, orders 테이블의 user_id와 동일한 user_id를 갖는 users 테이블 데이터를 붙입니다. (*users 테이블에 u라는 별칭을 붙입니다)
- where u.email like '%naver.com': users 테이블 email 필드값이 naver.com으로 끝나는 값만 가져옵니다.
- group by u.name: users 테이블의 name값이 같은 값들을 뭉쳐줍니다.
- select u.name, count(u.name) as count_name : users 테이블의 name필드와 name 필드를 기준으로 뭉쳐진 갯수를 세어서 출력해줍니다.
✨이제는 실전! 본격 쿼리 작성해보기
-
Join 연습1
- 결제 수단 별 유저 포인트의 평균값 구해보기
join 할 테이블:point_users에,orders를 붙이기select o.payment_method, round(AVG(p.point)) from point_users p inner join orders o on p.user_id = o.user_id group by o.payment_method
- 결제 수단 별 유저 포인트의 평균값 구해보기
-
Join 연습2
- 결제하고 시작하지 않은 유저들을 성씨별로 세어보기
join 할 테이블:enrolleds에,users를 붙이기select name, count(*) as cnt_name from enrolleds e inner join users u on e.user_id = u.user_id where is_registered = 0 group by name order by cnt_name desc
- 결제하고 시작하지 않은 유저들을 성씨별로 세어보기
-
Join 연습3
- 과목 별로 시작하지 않은 유저들을 세어보기
join 할 테이블:courses에,enrolleds를 붙이기select c.course_id, c.title, count(*) as cnt_notstart from courses c inner join enrolleds e on c.course_id = e.course_id where is_registered = 0 group by c.course_id
- 과목 별로 시작하지 않은 유저들을 세어보기
-
Join 연습4
- 웹개발, 앱개발 종합반의 week 별 체크인 수를 세어보기
join 할 테이블:courses에,checkins를 붙이기select c1.title, c2.week, count(*) as cnt from checkins c2 inner join courses c1 on c2.course_id = c1.course_id group by c1.title c2.week order by c1.title, c2.week
- 웹개발, 앱개발 종합반의 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
- 연습4번에서, 8월 1일 이후에 구매한 고객들만 구해보기
.
