select, where, group by, order by, subquery, inner join, left join
테이블 목록 보는법
show tables 입력
-> ①마우스 오른쪽 버튼 클릭 -> 실행 -> SQL문 실행
or ② ctrl+엔터
특정 '테이블' 전체 정보 읽는법
select form (테이블명)
= 모든 필드
ex select * form orders
특정 '필드' 정보 읽는 법
selec (특정 필드명), (특정 필드명) form (테이블명)
특정 '데이터' 정보 읽는 법 <조건을 거는 Where 절 사용>
selec (특정 필드명), (특정 필드명) form (테이블명)
where (필드명) = '특정 데이터 텍스트'
ex) orders 테이블에서 결제수단이 카카오페이인 데이터만 가줘와줘
select * from orders
where payment_method = 'kakaopay'
ex)point_users 테이블에서 포인트가 5000점 이상인 데이터 가져와줘
select * from point_users
where point >= 5000
ex) orders 테이블에서 주문한 강의가 앱개발 종합반이면서, 결제수단이 카드인 데이터만 가져와줘
select * from orders
WHERE course_title = '앱개발 종합반' and payment_method ='CARD'
Where절과 자주 같이 쓰는 문법 써보기
같지 않음 조건 !=
select * from orders
WHERE course_title != '웹개발 종합반'
범위 조건 between
ex) 7월 13일, 7월 14일의 주문데이터만 보고 싶다
select * from orders
WHERE created_at BETWEEN '2020-07-13' and '2020-07-15'
포함 조건 in
ex) 1, 3주차 사람들의 오늘의 다짐 데이터만 보고 싶다
select * from checkins
WHERE week in (1,3)
패턴 (문자열 규칙) 조건 걸어보기 like
ex) 다음 (daum) 이메일을 사용하는 유저만 보고 싶어요
select * from users
WHERE email like '%daum.net'
이외 유용한 문법 배워보기
일부 데이터만 가져오기 : Limit
ex) Limit 5 (5개 열만 가져오기)
중복 데이터는 제외하고 가져오기 : Distinct
ex) select DISTINCT (payment_method) from orders
몇 개인지 숫자 세보기 : Count
ex) select count(*) from orders
WHERE payment_method = 'kakaopay'
distinc와 count를 같이 써보기
ex) SELECT count(distinct(name)) FROM user_s
Groyp by
동일한 범주를 갖는 데이터를 하나로 묶어서, 범주별 통계를 내주는 것
동일한 범주의 갯수 구하기
count()
ex)
SELECT name, count() FROM users
group by name
ex)
SELECT name, email, count(*) from users
WHERE email LIKE '%@naver.com'
group by name
ex)
SELECT week, count(*) FROM checkins
group by week
동일한 범주에서의 최솟값 구하기
min(필드명)
ex)
SELECT week, min(likes) FROM checkins
group by week
동일한 범주에서의 최대값 구하기
max(필드명)
동일한 범주에서의 평균 구하기
round
-> 값 반올림 하고 싶을 땐, round 사용
ex)
SELECT week, round(avg(likes),2) FROM checkins
group by week
동일한 범주의 합계 구하기
sum(필드명)
정렬
order by
ex) 내림차순 정렬 (안적어도 됌, 디폴트 값)
SELECT name, count() from users
group by name
order by count() asc
ex) 오름차순 정렬
SELECT name, count() from users
group by name
order by count() desc
ex) SELECT * from checkins
order by likes desc
join
left join, inner join
ex)
SELECT * from users u
left join point_users p on u.user_id = p.user_id
실습)
1.orders 테이블에 users 테이블 연결
SELECT * from orders o
inner join users u on o.user_id = u.user_id
2.checkins 테이블에 users 테이블 연결
SELECT * from checkins c
inner join users u on c.user_id = u.user_id
3.enrolleds 테이블에 courses 테이블 연결
select * from enrolleds e
inner join courses c on e.course_id = c.course_id
letf join
언제 사용하는지)
null과 관련된것, 없는 것들을 포함해서 통계낼때 사용
ex) NULL이 아닌것만 찾기
SELECT u.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 u.name
ex) NULL인 것만 찾기
SELECT u.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 u.name
결과물 합치기! union all
(
)
union all
(
)
ex) (
select '7월' as month, 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
)
union all
(
select '8월' as month, 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
)
서브 쿼리 (큰 쿼리문 안에 들어가는 쿼리문)
Where에 들어가는 Subquery
Where 필드명 in(Subquery)
ex)
SELECT * from users
where user_id in (
SELECT user_id from orders
where payment_method = 'kakaopay'
)
Select에 들어가는 Subquery
select 필드명, 필드명, subzuery)from..
ex)
select c.checkin_id,
c.user_id,
c.likes,
(
select avg(likes) from checkins
WHERE user_id = c.user_id
) as avg_likes_user
from checkins c
From에 들어가는 Subquery
ex)
select pu.user_id, pu.point, a.avg_likes from point_users pu
inner join (
select user_id, round(avg(likes),1) as avg_likes from checkins
group by user_id
) a on pu.user_id = a.user_id
문자열 데이터 다뤄보기
문자열 쪼개보기 (substring_index)
ex) SELECT user_id, email, SUBSTRING_INDEX(email,'@',-1) from users
SELECT user_id, email, SUBSTRING_INDEX(email,'@',1) from users
문자열 일부만 출력하기 (substring)
SELECT SUBSTRING(created_at,1,10) as date, count(*) from orders
group by date
Case : 경우에 따라 원하는 값을 새 필드에 출력해보기
ex) SELECT pu.user_id, pu.point,
(case when pu.point > 10000 then '잘 하고 있어요!'
else '조금만 더 파이팅!' end) as msg
FROM point_users pu
case when
ex)
with table1 as(
SELECT pu.user_id, pu.point,
(case when pu.point > 10000 then '1만 이상'
when pu.point > 5000 then '5천 이상'
else '5천 미만' end) as lv
FROM point_users pu
)
SELECT a.lv, count(*) as cnt from table1 a
group by a.lv
