데이터베이스(DB)
- 공유되어 사용될 목적으로 통합하여 관리되는 데이터의 집합
- Create, Read, Update, Delete
SQL(Structed Query Language)
- DB를 구축하고 활용하기위해 사용하는 언어
(DB에 요청(query)를 날려서 원하는 데이터를 가져오는 것을 도와주는 언어) - RDBMS를 관리할 때 유용한 프로그래밍 언어
RDBMS(Relational Database Management System)
관계형 데이터베이스 관리 시스템- 관계형 데이터 베이스 : 행과 열로 구조화된 형식의 데이터베이스
Query
DB에 명령을 내리는 것
Select
DB에서 데이터를 선택해 가져오기
show tabels;
select payment_method from orders;
#테이블을 보여줘
#orders에서 payment_method 필드만 가져와줘Where
select 쿼리문으로 가져올 데이터에 조건 걸기
#orders 테이블에서 payment_method가 kakaopay인 것만 가져와줘
select * from orders
where payment_method = 'kakaopay';
# 성이 황씨인 유저만 뽑기
select * from users
where name = "황**";
# 포함 조건 in // 1,3주차 사람들의 오늘의 다짐만 출력하기
select * from checkins
where week in (1, 3);
# 패턴 조건 like // 이메일이 다음인 유저만 고르기
select * from users
where email like '%daum.net';
limit 5; // 일부데이터만 가져오기 limit
# distinct 중복 데이터 제외하기
select distinct(payment_method) from orders;
// payment_method - kakaopay
CARD
MONEY 3가지 결제 수단이 있다는 사실 알수 있음
# count // 주문의 개수
select count(*) from orders
// count(*) - 286
Group by, Order by
group by : 동일한 범주를 갖는 데이터를 하나로 묶어서 범주별 통계를 내는 것
# users 테이블에서 name에 따라 그룹을 만들고
# 그것의 개수를 내림차순으로 정렬해줘
select name, count(*) from users
group by name
order by count(*) desc
// name count(*)
김** 100
이** 85
박** 77
# checkins 테이블에서 week 필드를 가져와 가장 낮은 좋아요 수 보여주기
select week, min(likes) from checkins
group by week
// min(likes), max(likes), avg(likes), sum(likes)
// week min(likes)
1 0 // 1주차 그룹 중에서 가장 낮은 좋아요 수
2 1
3 1Alias : 별칭기능
select * from orders 0
where o.course_title = '앱개발 종합반'
select payment_method, count(*) as cnt from ordersJoin
두 테이블의 공통된 정보(key 값)을 기준으로 테이블을 연결해서 한 테이블처럼 보이게 함.
left join, inner join
select * from users u // 기준이 되는 테이블
left join point_users p on u.user_id = p.user_id
Union
필드명이 같은 테이블을 모아보기
(
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에서는 내부 정렬x
Subquery
where, select, from에 사용
# 유저 별 평균 좋아요와, 해당 유저별 포인트를 보여줘
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
# checkins 테이블에 course_id별 avg(likes)를 우측에 붙이기
select avg(likes) from checkins c
where course_id = '5f0ae408765dae0006002817'
// course_id에 해당하는 것만 avg(likes) 표현, course_id 개수 맞춰놓기
select c2.checkin_id, c2.course_id, c2.user_id, c2.likes,
(select avg(likes) from checkins c
where course_id = c2.course_id) as course_avg
from checkins c2
# course_id 별 user_id 수와 course_id 별 인원 구해서 나타내기
# 추가로 강의 title 도
i) select course_id , count(distinct(user_id)) as cnt_checkins from checkins c
group by course_id
ii) select course_id, count(*) as cnt_total from orders o
group by course_id
iii) select * from courses c // 추가로 강의 title 가져오기
# 최종
select c2.title, a.course_id , a.cnt_checkins , b.cnt_total
from (select course_id , count(distinct(user_id)) as cnt_checkins from checkins c
group by course_id) a
inner join (select course_id, count(*) as cnt_total from orders o
group by course_id) b
on a.course_id = b.course_id
inner join courses c2 on a.course_id = c2.course_id
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문자열
i ) 쪼개보기
select email, substring_index(eamil, '@', 1) from users
// -> @로 쪼개고 그 중 첫번째 조각 (abc12@naver.com -> abc12)
select email, substring_index(email, '@', -1) from users
-> @로 쪼개고 그 중 마지막 조각 (abc12@naver.com -> naver.com)ii) 일부만 출력하기
select created_at, substring(created_at, 1 ,10) from orders
-> created_at에서 처음 글자부터 10번째 글자까지 출력
(2020-03-28 14:35:29 -> 2020-03-28)Case
경우에 따라 원하는 값을 새 필드에 출력해보기
select pu.point_user_id, pu.point,
case when pu.point > 10000 then '잘 하고 있어요!'
else '조금 더 달려주세요!'END as 'msg'
from point_users pu;
# 10000점보다 높은 포인트를 가지고 있으면 '잘 하고 있어요!',
# 평균보다 낮으면 '조금 더 달려주세요!'예제
i) 결제하고 시작하지 않은 유저들을 성씨별로 세어보기
join : enrolleds에 users 조인
is_registered = 0
select name, count(*) as cnt_name from ebrolleds e
inner join users u on e.user_id = u.user_id
where is_registered = 0
group by name
order cnt_name
ii) point에 따라 다른 필드로 나누고 필드별 개수 세기
# 다른 필드로 나누기
select point_user_id, point,
case when point > 10000 then '1만 이상'
when point > 5000 then '5천 이상'
else '5천 미만'
End as level
from point_users
# 각 필드별 개수 세기, subquery
select level, count(*) from
(select pu.point_user_id, pu.point,
case when pu.point > 10000 then '1만 이상'
when pu.point > 5000 then '5천 이상'
else '5천 미만'
End as level
from point_users pu) a
group by level
# with로 깔끔하게
with table1 as (
select pu.point_user_id, pu.point,
case when pu.point > 10000 then '1만 이상'
when pu.point > 5000 then '5천 이상'
else '5천 미만'
End as level
from point_users pu
)
select level, count(*) as cnt from table1
group by leveliii) enrolled_id 별 전체 강의 수와 들은 강의 수 출력하기
# 전체 강의 수
select enrolled_id, count(*) from enrolleds_details
group by enrolled_id
# 들은 강의 수
select enrolled_id, count(*) from enrolleds_details
where done = 1
group by enrolled_id
# join 하기
with table1 as (
select enrolled_id, count(*) as total_cnt from enrolleds_detail ed
group by enrolled_id
), table2 as (
select enrolled_id, count(*) as done_cnt from enrolleds_detail ed
where done = 1
group by enrolled_id
)
select b.enrolled_id , a.done_cnt , b.total_cnt from table1 b
inner join table2 a on b.enrolled_id = a.enrolled_id ;
# 진도율 추가하기
round((a.done_cnt/b.total_cnt),2) as ratio 추가하기
# 더욱 간단하게 만들기
select enrolled_id,
sum(done) as done_cnt, count(*) as total_cnt
from enrolleds_detail ed
group by enrolled_id
how to use like in sql 로 구글링
하루에 한 개념씩