SQLD_ 엑셀보다 쉬운, SQL 2-4주차 완강

* tistory로만 개발일지 작성을 위해 네이버 블로그에 이전에 작성했던 글들을 긁어왔습니다.

* 강의들으면서 끄적이는 글 (일목묘연하지 않고, 이글을 보고 학습하기 적절하지 않을 것으로 사료됨, 틀린 부분 지적 환영)

**문법 총 정리**

1.where

조건문 subquery의 조건에 활용방식으로 사용

where 필드명 in -

​

2.select

결과를 출력해주는것 .

select 필드명 from 테이블

​

3.from

가장 많이 쓰는 서브쿼리

select와 테이블을 join 할때 사용함.

​

4.with

서브쿼리를 정리하는 문법

with 테이블명1 as (서브쿼리1), 테이블명2 as (서브쿼리2)

​

5.substtring_index

필드에서 기준값 설정하고 그 앞 뒤를 추출한다.

substtring_index (필드명, '기준이 되는 텍스트', 1)

1은 앞 -1은 뒤 텍스트'

​

6.substring

텍스트로 된 필드에서 기준값 설정하고, 기준값으로 설정된 수 뒷자리 까지 추출

substring (필드명, 텍스트자리수, 몇번째까지 추출할것인지)

​

7.case

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

case when ~ then '원하는 값'

else '원하는값' end

​

​

[2주차 목차]

- 통계: 최대/최소/평균/갯수 (쌓여있는 날것의 데이터-> 의미를 갖는 '정보'로의 변환)

- 통계구하기: 기존 방법의 한계

- 동일한 범주의 데이터를 묶어주는 Grop by

- 깔끔하게 데이터를 정렬하는 Order by

+ where 문으로 결과가 동일한지 검증해보기

​

 

---

users 테이블에서 성 별로 몇명인지 나타내기

-> SELECT name, count(*) FROM users

group by name

​

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

select week, count(*) from checkins

group by week

​

주차별로 '오늘의 다짐'의 최솟값 구하기

select week, min(likes) from checkins

group by week;

​

주차별로 '오늘의 다짐'의 최솟값 구하기

select week, max(likes) from checkins c

group by week

​

주차별로 '오늘의 다짐'의 평균값 구하기

select week, avg(likes) from checkins

group by week;

+ 소수점이 길게 나올때 반올림하려면 ROUND ( ~~ ,2) =소숫자리 2개까지

​

주차별로 '오늘의 다짐'의 좋아요 합ㄱㅖ 구하기

select week, SUM(likes) from checkins

group by week

​

---

깔끔한 정렬이 필요할 땐? ORDER BY -> 할거 다하고 마지막에 하기

오름차순 order by 필드명 (asc)

내림차순 order by 필드명 desc

​

​

웹개발 종합반의 결제수단별 주문건수 세어보기 (오름차순으로)

select payment_method, count(*)FROM orders

WHERE course_title ='웹개발 종합반'

group by payment_method

​

---

삽질하기

에러메시지 읽기

원하는 결과가 나오지 않았는지 생각 및 수정하기

​

---

연습하기

​

문자열대로 정렬하기 =숫자와 동일

​

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

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, ROUND(AVG(likes),2) FROM checkins

group by course_id

​

 

---

**꿀팁! 이렇게 쿼리 작성하면 편함**

1) show tables로 어떤 테이블이 있는지 살펴보기

2) 제일 원하는 정보가 있을 것 같은 테이블에 select * from 테이블명 limit 10 쿼리 날려보기

3) 원하는 정보가 없으면 다른 테이블에도 2)를 해보기

4) 테이ㅡㄹ을 찾았다! 범주를 나눠서 보고싶은 필드를 찾기

5) 범주별로 통계를 보고싶은 필드를 찾기

6) SQL 쿼리 작성하기

​

** 이외 유용한 문법 배우기

• 별칭기능: Alias
• 쿼리가 점점 길어지면서 종종 헷갈리는 일이 생길 수 있음. 그래서 SQL은 Alias 라는 별칭 기능을 지원함

​

​

​

​

SELECT payment_method,count(*)as cnt FROM orders o

WHERE o.course_title ='앱개발 종합반'

group by payment_method

​

숙제

네이버 이메일을 사용하여 앱개발 종합반을 신청한 주문의 결제 수단별 주문건수 세어보기

SELECT payment_method, count(*) FROM orders o

where email like '%naver.com' and course_title ='앱개발 종합반'

group by payment_method

​

---

 

3주차 목차

​

- JOIN: 여러정보(KEY 값)를 한눈에 보고싶다면 (실무에 굉장히 많이씀!) / 테이블들을 연결해서 한눈에 볼 수 있음

-> 테이블을 붙일 때 기준이 필요하다.

-> VLOOKUP이랑 비슷

​

- JOIN의 종류 : LEFT JOIN, INNER JOIN

​

LEFT JOIN

​

SELECT * FROM users u

LEFT JOIN point_users p

ON u.user_id = p.user_id

INNER JOIN

SELECT * FROM users u

inner JOIN point_users p

ON u.user_id = p.user_id

​

​

​

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

SELECT * FROM orders o

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

​

checkins 테이블에 users 테이블 연결해보기

SELECT * FROM checkins c

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

​

​

enrolleds 테이블에 courses 테이블 연결하기

SELECT * FROM enrolleds e

inner join courses c on e.course_id = c.course_id

​

​

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

​

​

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

​

​

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

​

​

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

->과목별 오늘의 다짐 갯수 세어보기

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_uses 테이블에 users 테이블 연결해서 순서대로 정렬해보기

-> 많은 포인트를 얻은 순서대로 유저 데이터 정렬해서 보기

select * from point_users p

inner join users u

on p.user_id = u.user_id

order by p.point desc

​

order 테이블에 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

​

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

​

​

​

결제 수단 별 유저 포인트의 평균값 구해보기(반올림)

SELECT o.payment_method, ROUND(avg(pu.point),2) FROM point_users pu

inner join orders o on pu.user_id = o.user_id

group by o.payment_method

​

​

결제하고 시작하지 않은 유저들을 성씨별로 세어보기

SELECT u.name , count(*) as cnt from enrolleds e

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

where e.is_registered = 0

GROUP by u.name

order by cnt desc

​

과목 별로 시작하지 않은 유저들을 세어보기

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

​

​

웹개발, 앱개발 종합반의 week 별 체크인 수를 세어보세요

• join 할 테이블: courses, chekins 붙이기

SELECT c1.title , c2.week , COUNT(*) FROM courses c1

inner join checkins c2 on c1.course_id =c2.course_id

group by c1.title , c2.week

order by c1.title ,c2.week

​

+

8월 1일이후에 구매한 고객들만~

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

​

​

left join 은 언제 사용하나?

회원가입은 했는데 포인트가 없는사람

SELECT u.name, COUNT(*) as cnt FROM users u

left join point_users pu on u.user_id =pu.user_id

where pu.point_user_id is NULL

group by u.name

​

count는 null을 세지않는다!

​

7월 10일 ~7월19일 가입한 고객중

포인트를 가진 고객의 숫자, 그리고 전체 숫자, 그리고 비율을 보여줘라

SELECT COUNT(pu.point_user_id) as pnt_user_cnt,

count(u.user_id) as tot_user_cnt,

round(count(pu.point_user_id)/count(u.user_id),2) as ratio

FROM users u

left join point_users pu on u.user_id =pu.user_id

where u.created_at BETWEEN '2020-07-10' and '2020-07-20'

​

union 에 대해서

​

​

숙제~

enrolled_id별 수강완료(done=1)한 강의 갯수를 세어보고, 완료한 강의 수가 많은 순서대로 정렬해보기 user_id도 같이 출력하기

​

​

SUBQUERY 연습해보기

Q1. 전체 유저의 포인트 평균보다 큰 유저들의 데이터 추출하기

SELECT * FROM point_users pu

WHERE POINT > (SELECT AVG(point) FROM point_users pu

)

Q2. 이씨 성을 가진 유저의 포인트 평균보다 큰 유저들의 데이터 추출하기

SELECT * FROM point_users pu

WHERE POINT > (SELECT AVG(pu.point) FROM point_users pu

INNER JOIN users u on pu.user_id = u.user_id

WHERE u.name = '이**')

​

Q3. checkins 테이블에 course_id 별 평균 likes 수 필드 우축에 붙여보기

SELECT c.checkin_id ,

c.course_id ,

c.user_id ,

c.likes,

(select AVG(likes) from checkins c

where course_id ='5f0ae408765dae0006002817'

) as course_avg

FROM checkins c

​

​