스터디일지/MYSQL
[2023.08.04] SQL 3주차 & 4주차
똥쟁이핑크
2023. 8. 4. 21:05
오늘 배운 내용
3주차
- JOIN - 두 테이블의 공통된 정보(Key 값 - 필드명)를 기준으로 테이블을 연결해 준다. → 한 개의 테이블처럼 볼 수 있다.
- LEFT JOIN - 공통된 기준으로 묶되 값이 없으면 NULL이 들어간다. → 어디에 뭐를 붙일건지 순서가 중요하다.
- INNER JOIN - 교집합으로 묶어준다.
- 쿼리 실행 순서 - 1) FROM → 2) JOIN → 3) SELECT
# 사용 예시 - LEFT JOIN - point_users와 users의 2개의 테이블에서 LEFT JOIN 하였다.
SELECT * FROM point_users pu
LEFT JOIN users u
ON pu.user_id = u.user_id
# 사용 예시 - INNER JOIN - point_users와 users의 2개의 테이블에서 INNER JOIN 하였다.
SELECT * FROM point_users pu
INNER JOIN users u
ON pu.user_id = u.user_id
- 다른 문법과 사용해보기
- WHERE
- GROUP BY
- ORDER BY
- 쿼리 실행 순서 - 1) FROM → 2) JOIN → 3) WHERE → 4) GROUP BY → 5) SELECT
# 사용 예시 - WHERE
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
# 사용 예시 - GROUP BY
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
# 사용 예시 - ORDER BY
SELECT * FROM point_users p
INNER JOIN users u
ON p.user_id = u.user_id
ORDER BY p.point desc
- UNION
- 두 개의 테이블을 한번에 모아서 볼 수 있다.
- 두 테이블 간의 필드명이 같아야 한다.
- 내부 정렬이 되지 않는다 → 내부 정렬을 위해 서브 쿼리를 사용한다.
# 사용 예시 - 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주차
Subquery를 알아보자.
- Subquery
- 쿼리 안의 쿼리를 만한다.
- 하위 쿼리의 결과를 상위 쿼리에서 사용하면 훨씬 간단해진다.
# 사용 예시
SELECT u.user_id, u.name, u.email FROM users u
WHERE u.user_id IN (
SELECT user_id FROM orders
WHERE payment_method = 'kakaopay'
)
# 서브 쿼리
# SELECT user_id FROM orders
# WHERE payment_method = 'kakaopay'
- 자주 쓰이는 유형
- WHERE - Subquery의 결과를 조건에 활용하는 방식으로 유용하게 사용 가능
- SELECT - 기존 테이블에 함께 보고싶은 통계 데이터를 손쉽게 붙이는 것에 사용 가능
- FROM - SELECT와 이미 있는 테이블을 JOIN 하고 싶을 때 사용 가능
# 사용 예시 - WHERE
# WHERE 필드명 IN (Subquery)
SELECT * FROM users u
WHERE u.user_id IN (SELECT o.user_id FROM orders o
WHERE o.payment_method = 'kakaopay')
# 사용 예시 - 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
# 사용 예시 - FROM - 가장 많이 사용 됨
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
- 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
- 유용한 문법
- SUBSTRING_INDEX - 문자열 쪼개기
- SUBSTRING - 문자열 일부분 추출
- CASE - 특정 조건에 따라 데이터를 구분해서 정리한다.
# 사용 예시 - SUBSTRING_INDEX - @를 기준으로 텍스트를 쪼개고, 그 중 첫 번째 조각을 가져오기
SELECT user_id, email, SUBSTRING_INDEX(email, '@', 1) FROM users
# 사용 예시 - SUBSTRING - SUBSTRING(문자열, 출력을 하고싶은 첫 글자의 위치, 몇개의 글자를 출력하고 싶은지)
SELECT order_no, created_at, substring(created_at,1,10) as date FROM orders
# 사용 예시 - 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 level
FROM point_users pu
