: 쿼리 안의 쿼리라는 의미, 하위 쿼리의 결과를 상위 쿼리에서 사용하면, SQL 쿼리가 훨씬 간단하고 편하게 데이터를 얻을 수 있음.

: 하나의 SQL 쿼리 안에 또다른 SQL 쿼리가 있는 것을 의미

: 안에 있는 쿼리 먼저 작동되고 그뒤에 큰 쿼리가 작동

1-1 where : 이미 뽑아놓은거 안에 이 쿼리 안에 있니?

+ Where은 조건문으로 Subquery의 결과를 조건에 활용하는 방식으로 유용하게 사용
+ where 필드명 in (subquery)

카카오페이로 결제한 주문건 유저들만, 유저 테이블에서 출력

SELECT user_id, name, email FROM users u 
WHERE user_id in (
    SELECT user_id FROM orders o 
    WHERE payment_method = 'kakaopay'
)

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

1-2 selet : 뽑을때마다 실행되게!

+ 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) 함께 출력해준다!

1-3 from:내가 만든 Select와 이미 있는 테이블을 Join하고 싶을 때

가장 많이 사용되는 유형!

1. 우선 유저 별 좋아요 평균
2. • 해당 유저 별 포인트 => 포인트와 like의 상관정도를 알 수 있음.

     select pu.user_id , pu.point, a.avg_likes from point_users pu
     inner join (
     select user_id, ROUND(AVG(likes),1) as avg_likes from checkins c 
     group by user_id 
     ) a on pu.user_id =a.user_id

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

이씨 성을 가진 유저들의 평균 포인트보다 더 많은 포인트를 가지고 있는 데이터를 추출

select * from point_users pu
where pu.point > (
    select avg(point) from users u
    inner join point_users pu on pu.user_id =u.user_id 
    where u.name = '이**'
)

둘은 동일하다 하위는 서브쿼리안에 서브쿼리가 또 들어갈 수 있음.

SELECT * from point_users pu 
WHERE pu.point > (
    SELECT avg(point) from point_users pu 
    WHERE user_id in (
        SELECT user_id from users u 
        WHERE name = '이**'
    )
)

2. with : 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를 사용해서 변경 VVVVVV

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

3. substring : 문자열 데이터를 원하는 형태로 한번 정리

3-1 substring_index : 문자열 쪼개보기

이메일에서 아이디만 가져와보기

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

이메일에서 도메인만 가져와보기

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

3-2 substring : 문자열 일부만 출력하기

orders 테이블에서 날짜까지 출력하게 해보기

select SUBSTRING(created_at,1,10) as date, count(*) from orders o 
group by date

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

SELECT created_at, SUBSTRING(created_at,12,8) from orders o 

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

특정 조건에 따라, 데이터를 구분해서 정리해주고 싶을 때, CASE 라는 문법이 사용

10000점보다 높은 포인트를 가지고 있으면 '잘 하고 있어요!', 평균보다 낮으면 '조금 더 달려주세요!' 라고 표시해 주려면 어떻게 해야할까?

select     pu.user_id, pu.point,
        (CASE when pu.point > 10000 then ' 잘 하고 있어요!'
        ELSE  '조금만 더 파이팅!' end) as msg
from    point_users pu 

포인트 보유액에 따라 다르게 표시해주기> 서브쿼리를 이용해서 group by로 통계 > with 절과 함께해 정리

with table1 as (
    select     pu.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 a.lv, COUNT(*) as cnt from table1 a 
group by a.lv

1. Join

: Join을 사용하면, 이렇게 한 눈에 두 개의 테이블을 연결할 수 있음. : SQL의 Join은 엑셀의 vlookup 기능과 동일함

Join이란? 두 테이블의 공통된 정보 (key값)를 기준으로 테이블을 연결해서 한 테이블처럼 보는 것을 의미해요 예) user_id 필드를 기준으로 users 테이블과 orders 테이블을 연결해서 한 눈에 보고 싶어요!

1-1 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 테이블에 존재하지 않는 경우

1-2 Inner Join

두 테이블의 교집합을 이야기

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

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

Join 연습

inner join이 좀더 쉬우니 inner join 실습 후 left join (순서가 중요) 하는게 좋음

연습1

 //orders 테이블에 users 테이블 연결해보기

SELECT * FROM orders o 
inner join users u on o.user_id = u.user_id 

연습2

//orders 테이블에 users 테이블 연결해보기

SELECT * FROM checkins c 
inner join users u on c.user_id = u.user_id 

연습3

//enrolleds 테이블에 courses 테이블 연결해보기

SELECT * FROM enrolleds e 
inner join courses c on c.course_id = c.course_id 

위 쿼리가 실행되는 순서: from → join → select

1. from enrolleds: enrolleds 테이블 데이터 전체를 가져옴
2. inner join courses on e.course_id = c.course_id: courses를 enrolleds 테이블에 붙이는데, enrolleds 테이블의 course_id와 동일한 course_id를 갖는 courses의 테이블을 붙임.
3. select * : 붙여진 모든 데이터를 출력

항상 from에 들어간 테이블을 기준으로, 다른 테이블이 붙는다고 생각하자

2. Join + 이전문법 활용

- checkins 테이블에 courses 테이블 연결해서 통계치 내보기

SELECT c1.course_id, c2.title, COUNT(*) as cnt from checkins c1
inner join courses c2 on c1.course_id =c2.course_id 
group by c1.course_id 

- point_users 테이블에 users 테이블 연결해서 순서대로 정렬해보기

SELECT pu.user_id, u.name, u.email, pu.point FROM point_users pu 
inner join users u on pu.user_id = u.user_id 
order BY pu.point DESC 
코드를 입력하세요

- orders 테이블에 users 테이블 연결해서 통계치 내보기

select u.name , COUNT(*) as cnt from orders o 
inner join users u on o.user_id = u.user_id
WHERE o.email LIKE "%naver.com"
GROUP BY u.name

위 쿼리가 실행되는 순서: from → join → where → group by → select

1. from orders o: orders 테이블 데이터 전체를 가져오고 o라는 별칭
2. inner join users u on o.user_id = u.user_id : users 테이블을 orders 테이블에 붙이는데, orders 테이블의 user_id와 동일한 user_id를 갖는 users 테이블 데이터를 붙임.(users 테이블에 u라는 별칭)
3. where u.email like '%naver.com': users 테이블 email 필드값이 naver.com으로 끝나는 값만 가져옴.
4. group by u.name: users 테이블의 name값이 같은 값들을 뭉쳐줌.
5. select u.name, count(u.name) as count_name : users 테이블의 name필드와 name 필드를 기준으로 뭉쳐진 갯수를 세어서 출력.

Join의 실행 순서는 항상 from 과 붙어다닌다고 생각하자

3. 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

V

(
    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
)

union을 사용하면 내부 정렬이 먹지 않는다. 이 때 유용한 방법이 있음. 바로, SubQuery(서브쿼리) 다음주에 배움

코린이의 SQL 2주차

1. Group by

: 동일한 범주의 데이터를 묶어주어, 통계를 내주기

1-1 프로세스

문제 : 성씨별로 몇 명이 회원이 있는지 구하기

step1 : 전체데이터를 먼저 불러본다

 SELECT * FROM users
group by name 

step2 : 동일한 범주의 데이터를 어떻게 묶을건지 고려 후 데이터 선택 수정

 SELECT name, COUNT(*) FROM users
group by name 

쿼리가 실행되는 순서: from → group by → select

1-2 범주의 갯수 구하기 + 전주에 배운 where 과 값이 같은지 비교/확인

주차별 '오늘의 다짐' 개수 구하기

> 테이블로 값만 카운트 보여주기
SELECT week, COUNT(*) FROM checkins
group by week


> 조건의 해당 값 카운트 모두 보여주기
SELECT * FROM checkins
where week =1

1-3 최솟값/ 최대값/ 평균값/ 합계

최소값 : min(최솟값을 알고 싶은 필드명)

select week, min(likes) from checkins
group by week

최대값 : max(최대값을 알고 싶은 필드명)

select week, max(likes) from checkins
group by week

평균값 : avg(평균값을 알고 싶은 필드명)

select week, avg(likes) from checkins
group by week

+참고 소수점 반올림 : round(avg(평균값을 알고 싶은 필드명), 보여주고자 하는 소수점의 자리수)

합계 : sum (합계를 알고 싶은 필드명)

select week, sum(likes) from checkins
group by week

1-4 where 과 함께 쓰기

'신' 씨를 가진 데이터만 불러와서 개수 > group by를 사용해서 '신'씨를 가진 데이터가 몇 개인지

SELECT name, COUNT(*) FROM users
WHERE name = '신**'
GROUP by name 

*쿼리가 실행되는 순서: from → where → select → group by *

퀴즈

1. 앱개발 종합반의 결제수단별 주문건수 세어보기

   SELECT payment_method, count(*) FROM orders
   group by payment_method 

2. Gmail 을 사용하는 성씨별 회원수 세어보기

   SELECT name, count(*) FROM users
   where email LIKE '%@gmail.com'
   group by name

3. course_id별 '오늘의 다짐'에 달린 평균 like 개수 구해보기

   SELECT course_id, ROUND(avg(likes),1) FROM checkins
   group by course_id 

2. order by

: 깔끔한 정렬이 필요할때 사용

2-1 오름차순 정렬 (asc)

step1 : 전체데이터를 먼저 불러본다

 SELECT * FROM users
group by name 

step2 : 동일한 범주의 데이터를 어떻게 묶을건지 고려후 데이터 선택 수정

 SELECT name, COUNT(*) FROM users
group by name 

step3 : 결과값을 보기좋게 정렬

아무것도 적지 않으면 기본적으로 오름차순

 SELECT name, COUNT(*) FROM users
group by name 
order by count(*) 

쿼리가 실행되는 순서: from → group by → select → order by

2-2 내림차순 정렬 (desc)

step1 step2

step3 : 결과값을 내림차순 정렬

영단어 descending의 약자

 SELECT name, COUNT(*) FROM users
group by name 
order by count(*) desc

2-3 문자열 정렬

- 알파벳으로 정렬

select * from users
order by email

- 가나다순으로 정렬

select * from users
order by name;

- 시간기준으로 정렬

select * from users
order by created_at desc;

3. Where와 함께 group by + order by 쓰기

*Where절로 조건이 하나 추가되고, 그 이후에 Group by, Order by가 실행되는 것! *

step1 테이블 전체 불러보기

select*from oreders

step2 잘라서

select*from oreders
where course_title= '웹개발 종합반'

step3 묶어서

 select*from oreders
 where course_title= '웹개발 종합반'
 group by payment_method

step4 통계내고

select payment_method, count(*) from orders
 where course_title= '웹개발 종합반'
 group by payment_method

step5 정렬한다

 select payment_method, count(*) from orders
 where course_title= '웹개발 종합반'
 group by payment_method
 order by count(*)

4. Alias

: 별칭붙여주기 (길어지는 쿼리를 짧게)

orders 테이블을 o > 테이블 o 안에 코스타이틀(o.course_title) count(*) 를 as cnt > cnt 필드명으로 출력

SELECT payment_method, count(*) as cnt FROM orders o 
where o.course_title ='앱개발 종합반'
group by payment_method

1. select

: 데이터베이스에서 '데이터를 선택해서 가져오겠다'는 의미 ㄴ 1. 어떤 테이블에서 2. 어떤 필드의 데이터를 가져올지 로 구성

• 시작 전 show tables로 모든 데이터베이스 테이블을 확인

show tables

• 특정 테이블 데이터를 가져와보기

select * from orders;

2. where

: select 데이터에 가져올 데이터에 조건을 걸어주는 것

select * from orders
where payment_method = "kakaopay";

• ~이상인 데이터 뽑기

select * from point_users
where point > 20000;

• 특정 문자열의 데이터 뽑기

select * from users
where name = "황**";

• 여러 조건의 데이터 합성(?) 해서 뽑기

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

쿼리 작성하는 tip

1) show tables로 어떤 테이블이 있는지 살펴보기 2) 제일 원하는 정보가 있을 것 같은 테이블에 select * from 테이블명 쿼리 날려보기 3) 원하는 정보가 없으면 다른 테이블에도 2)를 해보기 4) 테이블을 찾았다! 조건을 걸 필드를 찾기 5) select * from 테이블명 where 조건 이렇게 쿼리 완성!

3. where 절과 자구 같이 쓰는 문법

3-1) 같지 않음 !=

select * from orders
where course_title != "웹개발 종합반";

3-2) 범위 조건 between ~ and ~

select * from orders
where created_at between "2020-07-13" and "2020-07-15";

3-3) 포함 조건 in (~, ~, )

select * from checkins 
where week in (1, 3);

3-4) 패턴 조건 like '%~~'

Like의 다양한 사용법 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' 구글링!)

select * from users 
where email like '%daum.net';

4. 이외 문법

4-1) 일부 데이터만 가져오기 Limit : 데이터를 모두 불러오기에는 너무 크고, 데이터 구성방식만 보고싶을때 사용하면 좋음

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

4-2) 중복 데이터 제외하고 가져오기 Distinct

select distinct(payment_method) from orders;

4-3) Count

select count(*) from orders

• 중복을 제외한 값 세기

select distinct(name) from users;