[개발일지-4(1)] SQL 4주차 숙제

SQL 모든 문법 총 정리!!

SELECT와 WHERE절

1. Where 절은, Select 쿼리문으로 가져올 데이터에 조건을 걸어주는 것을 의미

select * from orders
where payment_method = "kakaopay";

• '같지 않음' 조건은 != 로 걸 수 있다.
• '범위' 조건은 between
• '포함' 조건은 in
• '패턴' 조건은 like

2. 일부 데이터만 가져올 때에는 Limit
3. 중복 데이터는 제외하고 가져오기: Distinct

select distinct(payment_method) from orders;

4. 몇 개인지 숫자 세보기: Count

SELECT count(distinct(name)) from users;
name 중에서 중복을 제외하고 세어준다.

Group by

Group by는 동일한 범주를 갖는 데이터를 하나로 묶어서, 범주별 통계를 내주는 것

1. 동일한 범주의 갯수 구하기

select 범주별로 세어주고 싶은 필드명, count(*) from 테이블명
group by 범주별로 세어주고 싶은 필드명;

2. 동일한 범주의 최댓값 구하기

select 범주가 담긴 필드명, max(최댓값을 알고 싶은 필드명) from 테이블명
group by 범주가 담긴 필드명;

3. 동일한 범주의 최솟값 구하기

select 범주가 담긴 필드명, min(최솟값을 알고 싶은 필드명) from 테이블명
group by 범주가 담긴 필드명;

4. 동일한 범주의 평균 구하기

select 범주가 담긴 필드명, avg(평균을 알고 싶은 필드명) from 테이블명
group by 범주가 담긴 필드명;

5. 동일한 범주의 합계 구하기

select 범주가 담긴 필드명, sum(합계를 알고 싶은 필드명) from 테이블명
group by 범주가 담긴 필드명;

Order by

asc는 오름차순, desc는 내림차순 정렬된다. 뒤에 아무것도 입력하지 않을 시 기본적으로 오름차순 정렬이 된다.

Join

두 테이블의 공통된 정보 (key값)를 기준으로 테이블을 연결해서 한 테이블처럼 보는 것을 의미
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 테이블에 존재하지 않는 경우
2. inner join

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

여기서는 비어있는 데이터가 없다..!

union

select 결과를 이어보고 싶을 때 사용. 두 필드 이름이 같아야 함!!

subquery

where절에 들어가는 subquery

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에 들어가는 subquery

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 (가장 많이 쓰인다!)

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

해당 유저별 포인트와 좋아요 평균과의 상관관계를 알 수 있음.

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

with를 사용하여 table 이라는 이름으로 정의하여 하나의 필드처럼 사용할 수 있게 함. 더 깔끔하게 쿼리 작성이 가능하다.

실전에 유용한 문법들 (문자열 갖고놀기, case)

1. 문자열 쪼개기

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

@를 기준으로 텍스트를 쪼개고, 그 중 첫 번째 조각을 가져오라는 뜻!

2. 문자열 일부만 출력하기

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

SUBSTRING(문자열, 출력을 하고싶은 첫 글자의 위치, 몇개의 글자를 출력하고 싶은지)

3. Case : 경우에 맞게 출력하기

select pu.point_user_id, pu.point,
case when pu.point > 10000 then '잘 하고 있어요!'
     else '조금 더 달려주세요!' END as '구분'
from point_users pu;