[항해99 SQL] 4주차

Subquery : 쿼리 안의 쿼리라는 의미

 

inner join을 다음과 같이 subquery 로도 나타낼 수 있다.

 

tab을 하는 방법은 tab하고자 하는 것을 하이라이트 한 후, tab을 하면 된다. -> 오른쪽으로 이동

shift + tab을 하게 된다면 <- 왼쪽으로 이동

 

where 에서의 subquery

select * from users u

where user_id in (

select user_id from orders o

where payment_method = 'kakaopay'

)

 

select에 들어가는 subquery

select c.checkin_id,

c.user_id,

c.likes,

(

select avg(likes) from checkins

where user_id = c.user_id

) as avg_likes_user

from checkins c

 

이 부분은 c.user_id 에 대한 모든 user_id를 찾아서 평균 likes를 찾는 것과 동일하다.

즉, 모든 c.user_id에 대해서 likes를 찾아서 적용, 적용.,... 반복 하게 된다.

 

여기서 () 안에 있는 chekins에는 c를 alias로 똑같이 적용시켜 주면 안된다. (내가 했던 실수)

 

from에 들어가는 subquery

 

inner join () 안에 들어가는 쿼리를 만들고, inner join 안에 넣는다. 그리고 a 별칭을 붙여준 후, 

어떤 것으로 붙여줄 지도 적어준다. -> on pu.user_id = a.user_id

 

이씨 성을 가지고, 포인트가 이씨 평균보다 높은 사람들을 출력하라

예제1)

select * from point_users pu

where point > (

        select avg(point) from point_users pu

        inner join users u on pu.user_id = u.user_id

        where name = '이**'

)

 

예제2)

select * from point_users pu

where point > (

         select avg(point) from point_users pu

         where user_id in (

                select user_id from users

                where name = '이**'

          )

)

 

 

checkins 테이블에 과목명별 평균 likes수 필드 우측에 붙여보기

 

 

courses에서 title가져오기

 

이것의 경우, c.title마다 값을 비교해줄 필요가 없으므로, 위와 다르게

inner join을 해주엇다.

이후 c2.title을 출력해주었다.

여기서 tip 은 c2.*을 통해서, c2들이 잘 붙여졌는지 확인할 수 있다.

 

with절에 대해서 배워보자.

약간 대치 같은 느낌이다. with table1 as ( ~~~~~ )

을 넣고 본문에, table1을 ~~~~ 대신에 넣어준다.

 

문자열 데이터를 다루기

1) 문자열 쪼개기 -> SUBSTRING_INDEX 문법을 사용하면 된다.

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

문자열에서 @을 기준으로 자르고, 첫 번째 것만 가지고 와줘

 

2) SUBSTRING 이용 기준이 1부터 몇 번째 글자까지 자를 것인지

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

group by date

 

CASE: 경우에 따라 원하는 값을 새 필드에 출력해보기

 

case when 문

 

 

평균 이상 포인트를 가지고 있으면 '잘하고 있어요' 

낮으면 '열심히 합시다!' 표시하기

 

이메일 도메인별 유저의 수 세어보기

select domain, count(*) as cnt from (select SUBSTRING_INDEX(email,'@',-1) as domain from users) em

group by domain

 

substirng에 매몰되어서, like가 있다는 것을 잊지 말기

select * from checkins c

where comment like '%화이팅%'

 

 

수강등록정보별 전체 강의 수와 들은 강의 수, 그리고 진도율 출력해보기

1) table로 정리한 것

 

2) 정리하기 전