My SQL 공부 Start
1.원하는 데이터를 더 쉽게: Subquery
💡 SubQuery란?
쿼리 안의 쿼리라는 의미로 하위 쿼리의 결과를 상위 쿼리에서 사용하면, SQL 쿼리가 훨씬 간단해진다.
👉즉, Subquery를 사용하지 않아도 원하는 데이터를 얻어낼 수 있겠지만, 더 편하고 간단하게 원하는 데이터를 얻기 위해 사용되는 파워풀한 기능
- ex) kakaopay로 결제한 유저들의 정보 보기
👉우선, 이렇게 볼 수 있다. users 와 orders 의 inner join을 사용.
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'
❗ 이 쿼리를 조금 더 직관적으로 바꿀 수 있다.
- 우선 kakaopay로 결제한 user_id를 모두 구해보기 -> K 라고 하기.
select user_id from orders
where payment_method = 'kakaopay'
- 그 후에, user_id가 K 에 있는 유저들만 골라보기
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'
)
👉이것을 바로 서브쿼리라고 한다.
📚 자주 쓰이는 Subquery 유형
- Where 에 들어가는 Subquery : Subquery의 결과를 조건에 활용하는 방식으로 유용하게 사용
👉where 필드명 in (subquery) 이런 방식
ex) 카카오페이로 결제한 주문건 유저들만, 유저 테이블에서 출력해주고 싶을 때는 아래와 같이 표현
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 에 들어가는 Subquer : Select는 결과를 출력해주는 부분이죠? 기존 테이블에 함께 보고싶은 통계 데이터를 손쉽게 붙이는 것에 사용
👉select 필드명, 필드명, (subquery) from .. 이런 방식
ex) 앞서 보았던것처럼, '오늘의 다짐' 데이터를 보고 싶은데 '오늘의 다짐' 좋아요의 수가, 본인이 평소에 받았던 좋아요 수에 비해 얼마나 높고 낮은지가 궁금할 때
- 평균을 먼저 구하고 user_id= '4b8a10e6'을 예시로
select avg(likes) from checkins c2
where c2.user_id = '4b8a10e6'- 이렇게 표현
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 (가장 많이 사용되는 유형!): From은 언제 사용하면 좋을까요? 내가 만든 Select와 이미 있는 테이블을 Join하고 싶을 때 사용
ex)
- 유저 별 좋아요 평균을 먼저 구하기
select user_id, round(avg(likes),1) as avg_like from checkins
group by user_id- 해당 유저 별 포인트를 구하기
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가 실행
2.실전에서 유용한 SQL 문법
(1)문자열 다루기
-SUBSTRING_INDEX(문자열 쪼개보기)
👉 예로 이메일 주소에서 @앞의 아이디만 가져오거나, @뒤의 이메일 도메인을 가져오고 싶을 때
ex 1)select user_id, email, SUBSTRING_INDEX(email, '@', 1) from users
:@를 기준으로 텍스트를 쪼개고, 그 중 첫 번째 조각을 가져오라는 뜻!
ex 2)select user_id, email, SUBSTRING_INDEX(email, '@', -1) from users
:@를 기준으로 텍스트를 쪼개고, 그 중 마지막 조각을 가져오라는 뜻!
-SUBSTRING(문자열 일부만 출력하기)
👉 사용방법 SUBSTRING(문자열, 출력을 하고싶은 첫 글자의 위치, 몇개의 글자를 출력하고 싶은지)
ex)select order_no, created_at, substring(created_at,1,10) as date from orders
:created_at의 1번째부터 10번째 까지 짤라서 가져오라는 뜻!
(2) CASE: 경우에 따라 원하는 값을 새 필드에 출력해보기(조건걸기)
👉 사용방법
CASE WHEN [조건절]
THEN [TRUE일 경우 값]
ELSE [FALSE일 경우 값]
END
ex)10000점보다 높은 포인트를 가지고 있으면 '잘 하고 있어요!', 평균보다 낮으면 '조금 더 달려주세요!' 라고 표시하라.
select pu.point_user_id, pu.point,
case
when pu.point > 10000 then '잘 하고 있어요!'
else '조금 더 달려주세요!'
END as '구분'
from point_users pu;
