SQL 총복습

SQL = Structure Query Language — DB에 쿼리를 날려서 원하는 데이터 가져옴 → Read하기에 매우 편함

DB 기초개념
C — create : 데이터의 생성 의미
R — read : 저장된 데이터를 읽음
U — update : 저장된 데이터를 변경함
D — delete : 저장된 데이터를 삭제

Select
데이터를 선택해서 가져옴
ex) select 필드 from 테이블명
(필드 대신 *를 입력하면 모든 필드)

Show tables
모든 테이블 보기
(근데 왼쪽탭에 이미 나옴)

Where
조건에 맞게 필터링함
ex)
select from 테이블명
where 필드명 = ‘x’
(!= 는 같지 않음을 나타냄 → ~이 아닌 데이터 추출)
and를 넣으면 여러 조건 추가 가능
ex)
select from orders
where course_title = “앱개발 종합반”
and payment_method = “kakaopay”;

In
포함하는 조건
ex)
select * from checkins
where week in (1, 3);

Between
범위를 나타내는 조건
ex)
select * from orders
where created_at
between “2020–07–13” and “2020–07–15”;
(13-14일이므로 15일 00시 00분까지로 나타냄)

Like
패턴(문자열규칙) 조건
ex)
select * from users
where email like ‘%daum.net’
(여기서 %는 앞 뒤 가운데로 올 수 있음)

Limit
x줄의 데이터만 가져옴 → 테이블에 어떤 데이터가 있는지 잠깐 볼 때 유용함
ex)
select * from orders
where payment_method = “kakaopay”
limit 5;

Distinct
중복데이터는 날려버리고 가져옴
ex)
select distinct(payment_method) from orders;
(이렇게 하면 고객이 결제한 수단이 어떤 건지 한 번에 확인 가능)

Count(필드명)
몇 개인지 숫자 세기
ex)
select count(*) from orders

Group by
같은 범주에 있는 데이터를 묶어서 범주별 통계를 내줌
~별 어쩌고 구하기 → group by임
ex)
select name, count(*) from users
group by name;

from으로 테이블을 가져오고
group by로 범주를 정해 묶은 후
**select로 원하는 데이터 추출

min(최소값 알고싶은 필드명)
max(최대값 알고싶은 필드명)
avg(평균값 알고싶은 필드명)
sum(합계값 알고싶은 필드명)
ex)
select week, sum(likes) from checkins
group by week;

Order by
정렬 (자동으로 오름차순, desc를 추가하면 내림차순)
ex)
select name, count() from users
group by name
order by count() desc;
쿼리 순서 : from → group by → select → order by

Join
두 테이블의 공통된 정보를 기준으로
두 테이블을 연결해서
한 테이블처럼 보게 함 (엑셀의 vlookup과 동일)

Inner join
공통된 필드에서 공통된 값에 해당하는 데이터만 나옴 (교집합)
ex)
select * from enrolleds e
inner join courses c
on e.course_id = c.course_id;

Left join
왼쪽 테이블에 오른쪽 테이블을 붙임
(공통된 필드에 한 쪽이 데이터가 부족한 경우 null값 뜸)
ex)
유저 중 포인트가 없는 유저 찾기→ null 값 찾기
select name, count() from users u
left join point_users pu on u.user_id = pu.user_id
where pu.point_user_id is NULL group by name
ex2)
유저 중 포인트가 있는 유저 찾기→ null 값 빼고 찾기
select name, count() from users u
left join point_users pu on u.user_id = pu.user_id
where pu.point_user_id is not NULL group by name

Union
select를 두 번 하지 않고 한 번에 모아서 봄
(두 테이블의 필드명이 같아야함, order by가 먹히지 않음)
ex)
(
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
)

Subquery
쿼리 안의 쿼리
where에 들어가는 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’);
(실행 순서 : from → subquery → where ~ in → select)

select에 들어가는 subquery
select 필드명, 필드명, (subquery) from 테이블명
ex)
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;

(실행순서 : select * from에서 데이터 한줄한줄 출력
→ select안의 subquery가 매 데이터 한줄마다 실행
→ 그 데이터 한 줄의 user_id를 갖는 데이터의 평균 좋아요 값을 subquery에서 계산
→ 출력)

From에 들어가는 subquery (가장 많이 사용)
→ 이미 있는 테이블에 내가 만든 select을 join할 때 사용
ex)
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

(먼저 subquery의 select를 실행 → 이걸 원래 테이블에 붙여서 원하는 데이터 뽑아냄)

with절
서브쿼리를 간편하게 쓰기 위함
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

(출력할 때 해당 모든 쿼리 묶어서 출력해야함)

SUBSTRING_INDEX
문자열 쪼개서 색인
ex)
select user_id, email, SUBSTRING_INDEX(email, ‘@’, 1) from users
→ email필드에서 문자 @ 를 기준으로 왼쪽 조각(이메일 아이디)을 가져옴
ex2)
select user_id, email, SUBSTRING_INDEX(email, ‘@’, -1) from users
→ email필드에서 문자 @ 를 기준으로 오른쪽 조각을 가져옴

SUBSTRING
문자열 일부만 출력 (날짜시간에서 시간을 빼거나 할 때)
ex)
select order_no, created_at, SUBSTRING(created_at,1,10) as date from orders
→ created_at 필드, 출력하고픈 첫 문자 위치, 출력하고픈 문자 수)

Case ~ when ~else ~ end
특정 조건에 따라 데이터를 구분해서 정리
ex)
select pu.point_user_id,
pu.point,
case
when pu.point > 10000 then ‘잘 하고 있어요!’
else ‘조금 더 달려주세요!’
END as ‘구분’
from point_users pu;