본문 바로가기
카테고리 없음

나만 보려고 정리하는 기본 SQL 쿼리문 정리 ! #스파르타코딩클럽 4주차

by 케마 2022. 1. 24.

데이터베이스 테이블 보기

show tables;

orders 테이블의 데이터 가져와보기

select * from orders;

orders 테이블의 특정 필드만 가져와보기

select created_at, course_title, payment_method, email from orders;

orders 테이블에서 payment_method가 kakaopay인 것만 가져와줘

select * from orders
where payment_method = "kakaopay";

여러 조건을 걸어주기

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

포인트가 20000점보다 많은 유저만 뽑아보기!

select * from point_users
where point > 20000;

성이 황씨인 유저만 뽑아보기

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

웹개발 종합반이면서 결제수단이 CARD인 주문건만 뽑아보기

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

'같지 않음' 조건 걸어보기('!=' 에서 ! (느낌표)는 부정 (not)을 의미합니다. '='는 같음을 의미하니, '!='는 같지 않음이겠죠!)

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

'범위' 조건 걸어보기

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

'포함' 조건 걸어보기

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

'패턴' (문자열 규칙) 조건 걸어보기

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

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로 끝나는 모든 데이터

결제수단이 CARD가 아닌 주문데이터만 추출해보기

select * from orders
where payment_method != 'CARD';

20000~30000 포인트 보유하고 있는 유저만 추출해보기

select * from point_users
where point between 20000 and 30000

이메일이 s로 시작하고 com로 끝나는 유저만 추출해보기

select * from users
where email like 's%com';

이메일이 s로 시작하고 com로 끝나면서 성이 이씨인 유저만 추출해보기

select * from users
where email like 's%com' and name = "이**";

일부 데이터만 가져오기: Limit

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

중복 데이터는 제외하고 가져오기: Distinct

select distinct(payment_method) from orders;

몇 개인지 숫자 세보기: Count

select count(*) from orders

[응용] Distinct와 Count를 같이 써보기

select distinct(name) from users;
SELECT count(distinct(name)) from users;

Select 쿼리문, Where 절 연습하기 -> 성이 남씨인 유저의 이메일만 추출하기

select email from users 
where name = "남**";

Where 절과 자주 같이쓰는 문법 연습하기 -> Gmail을 사용하는 2020/07/12~13에 가입한 유저를 추출하기

select * from users
where created_at between "2020-07-12" and "2020-07-14"
and email like "%gmail.com";

이외 유용한 문법 연습하기 -> Gmail을 사용하는 2020/07/12~13에 가입한 유저의 수를 세기

select count(*) from users
where created_at between "2020-07-12" and "2020-07-14"
and email like "%gmail.com";

naver 이메일을 사용하면서, 웹개발 종합반을 신청했고 결제는 kakaopay로 이뤄진 주문데이터 추출하기

select * from orders
where email like '%naver.com'
and course_title = '웹개발 종합반'
and payment_method = 'kakaopay'

성씨별 회원수를 Group by로 쉽게 구해보기

select name, count(*) from users
group by name;

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

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

주차별 '오늘의 다짐'의 좋아요 최솟값 구하기

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

주차별 '오늘의 다짐'의 좋아요 최댓값 구하기

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

주차별 '오늘의 다짐'의 좋아요 평균값 구하기

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

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

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

결과의 개수 오름차순으로 정렬해보기

select name, count(*) from users
group by name
order by count(*);

결과의 개수 내림차순으로 정렬해보기

select name, count(*) from users
group by name
order by count(*) desc;

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

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

문자열을 기준으로 정렬해보기

select * from users
order by name;

시간을 기준으로 정렬해보기

select * from users
order by created_at desc;

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

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, avg(likes) from checkins
group by course_id;

이외 유용한 문법 -> 별칭 기능: Alias

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

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

select payment_method, count(*) from orders
where email like '%naver.com' and course_title = '앱개발 종합반'
group by payment_method

Join을 사용해서 Key값으로 두 테이블 연결해보기

select * from point_users
left join users
on point_users.user_id = users.user_id

유저 데이터로 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;

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;

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

select co.title, count(co.title) as checkin_count from checkins ci
inner join courses co
on ci.course_id = co.course_id 
group by co.title

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

select * from point_users p
inner join users u 
on p.user_id = u.user_id
order by p.point desc

네이버 이메일 사용하는 유저의 성씨별 주문건수 세어보기

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

결제 수단 별 유저 포인트의 평균값 구해보기

select o.payment_method, round(AVG(p.point)) from point_users p
inner join orders o 
on p.user_id = o.user_id 
group by o.payment_method

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

select name, count(*) as cnt_name from enrolleds e
inner join users u
on e.user_id = u.user_id 
where is_registered = 0
group by name
order by cnt_name 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 별 체크인 수

select c1.title, c2.week, count(*) as cnt from checkins c2
inner join courses c1 on c2.course_id = c1.course_id
group by c1.title c2.week
order by c1.title, c2.week

웹개발, 앱개발 종합반의 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

유저 중에, 포인트가 없는 사람

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

7월10일 ~ 7월19일에 가입한 고객 중, 포인트를 가진 고객의 숫자, 그리고 전체 숫자, 그리고 비율

count 는 NULL을 세지 않음

select count(point_user_id) as pnt_user_cnt,
       count(*) as tot_user_cnt,
       round(count(point_user_id)/count(*),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'

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

select e.enrolled_id,
	     e.user_id,
	     count(*) as cnt
  from enrolleds e
 inner join enrolleds_detail ed on e.enrolled_id = ed.enrolled_id
 where ed.done = 1
 group by e.enrolled_id, e.user_id
 order by cnt desc

Where 에 들어가는 Subquery

select * from users u
where u.user_id in (select o.user_id from orders o 
					where o.payment_method = 'kakaopay');

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;

From 에 들어가는 Subquery

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

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

select * from point_users pu 
where pu.point > (select avg(pu2.point) from point_users pu2);

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

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

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

select checkin_id, course_id, user_id, likes, 
(select avg(c2.likes) from checkins c2
where c.course_id = c2.course_id) 
from checkins c;

checkins 테이블에 과목명별 평균 likes수 필드 우측에 붙여보기

select checkin_id, c3.title, user_id, likes, 
(select round(avg(c2.likes),1) from checkins c2
where c.course_id = c2.course_id) as course_avg
from checkins c
inner join courses c3 
on c.course_id = c3.course_id;

course_id별 유저의 체크인 개수를 구해보기!

select course_id, count(distinct(user_id)) as cnt_checkins from checkins
group by course_id

course_id별 인원을 구해보기!

select course_id, count(*) as cnt_total from orders
group by course_id

course_id별 like 개수, 전체

select a.course_id, b.cnt_checkins, a.cnt_total from
(
	select course_id, count(*) as cnt_total from orders
	group by course_id
) a
inner join (
	select course_id, count(distinct(user_id)) as cnt_checkins from checkins
	group by course_id
) b
on a.course_id = b.course_id

코스제목별 like 개수, 전체, 비율

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로 정리

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

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

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

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

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

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

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

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

select substring(created_at,1,10) as date, count(*) as cnt_date from orders
group by date

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

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

평균 이상 포인트를 가지고 있으면 '잘 하고 있어요' / 낮으면 '열심히 합시다!' 표시하기!

select pu.point_user_id, pu.point,
case 
when pu.point > (select avg(pu2.point) from point_users pu2) then '잘 하고 있어요!'
else '열심히 합시다!'
end as 'msg'
from point_users pu

이메일 도메인별 유저의 수 세어보기

select domain, count(*) as cnt from (
	select SUBSTRING_INDEX(email,'@',-1) as domain from users
) a
group by domain

'화이팅'이 포함된 오늘의 다짐만 출력해보기

select * from checkins c
where c.comment like '%화이팅%'

수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수 출력해보기

with lecture_done as (
	select enrolled_id, count(*) as cnt_done from enrolleds_detail ed 
	where done = 1
	group by enrolled_id
), lecture_total as (
	select enrolled_id, count(*) as cnt_total from enrolleds_detail ed 
	group by enrolled_id
)


select a.enrolled_id, a.cnt_done, b.cnt_total from lecture_done a
inner join lecture_total b on a.enrolled_id = b.enrolled_id

수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수, 그리고 진도율 출력해보기

with table1 as (
	select enrolled_id, count(*) as done_cnt from enrolleds_detail
	where done = 1
	group by enrolled_id
), table2 as (
	select enrolled_id, count(*) as total_cnt from enrolleds_detail
	group by enrolled_id
)

select a.enrolled_id,
       a.done_cnt,
       b.total_cnt,
       round(a.done_cnt/b.total_cnt,2) as ratio
  from table1 a
 inner join table2 b on a.enrolled_id = b.enrolled_id

 

티스토리툴바