1. Select 쿼리문 개념
1) 어떤 테이블에서 2) 어떤 필드의 데이터를 가져올지 로 구성
(예시1) orders 테이블 테이터 가져오기
select * from orders;(예시2) orders 테이블의 특정 필드만 가져와보기
select created_at, course_title, payment_method, email from orders;2. where 절의 개념
Select 쿼리문으로 가져올 데이터에 조건을 걸어주는 것
(예시1) Select 쿼리문에 Where 절 함께 써보기
orders 테이블에서 payment_method가 kakaopay인 것만 가져오기
select * from orders
where payment_method = "kakaopay"2-1. where절과 자주 같이쓰는 문법
- 같지 않음 조건 !=
(예시) '웹개발 종합반'을 제외하고 주문데이터를 보기
select * from orders
where course_title != "웹개발 종합반";- 범위 조건 between ~ and ~
(예시) 7월 13일, 7월 14일 주문데이터만 보기
select * from orders
where created_at between "2020-07-13" and "2020-07-15";- 포함 조건 in
(예시)1, 3주차 사람들의 '오늘의 다짐' 데이터만 보기
select * from checkins
where week in (1, 3);- 패턴 (문자열 규칙) 조건 like
(예시)다음(daum) 이메일을 사용하는 유저만 보기
select * from users
where email like '%daum.net';- 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로 끝나는 모든 데이터
3. Limit: 일부 데이터만 가져오기
where payment_method = "kakaopay"
limit 5;4. distinct 중복 데이터는 제외하고 가져오기
select distinct(payment_method)
from orders;5. count: 몇 개인지 숫자 세보기
select count(*) from orders6. Group by: 범주별 통계내기
select 범주별로 세어주고 싶은 필드명, count(*) from 테이블명
group by 범주별로 세어주고 싶은 필드명;(예시)성씨별 회원수를 Group by로 구해보기
select name, count(*) from users
group by name;6-1. min: 동일한 범주에서 최솟값 구하기
select 범주가 담긴 필드명, min(최솟값을 알고 싶은 필드명) from 테이블명
group by 범주가 담긴 필드명;(예시)주차별 '오늘의 다짐'의 좋아요 최솟값 구하기
select week, min(likes) from checkins
group by week;6-2. max: 동일한 범주에서 최댓값 구하기
select 범주가 담긴 필드명, max(최댓값을 알고 싶은 필드명) from 테이블명
group by 범주가 담긴 필드명;(예시) 주차별 '오늘의 다짐'의 좋아요 최댓값 구하기
select week, max(likes) from checkins
group by week;6-3. avg: 동일한 범주의 평균 구하기
select 범주가 담긴 필드명, avg(평균값을 알고 싶은 필드명) from 테이블명
group by 범주가 담긴 필드명;(예시)주차별 '오늘의 다짐'의 좋아요 평균값 구하기
group by week;6-4. sum:동일한 범주의 합계 구하기
select 범주가 담긴 필드명, sum(합계를 알고 싶은 필드명) from 테이블명
group by 범주가 담긴 필드명;(예시) 주차별 '오늘의 다짐'의 좋아요 합계 구하기
select week, sum(likes) from checkins
group by week;7. order by : 정렬하기
select * from 테이블명
order by 정렬의 기준이 될 필드명;- 오름차순 정렬
(예시)결과의 개수 오름차순으로 정렬해보기
select name, count(*) from users
group by name
order by count(*);- 내림차순 정렬 desc
(예시)결과의 개수 내림차순으로 정렬해보기
select name, count(*) from users
group by name
order by count(*) desc;8. Alias: 별칭 기능
select payment_method, count(*) **as** cnt from orders o
where o.course_title = '앱개발 종합반'
group by payment_method9. join: 여러 테이블 연결하기
9-1. Left join: A와 교집합 부분을 표시
(예시) users 테이블과 point_users 테이블을 left join
select * from users u
left join point_users pu on u.user_id = pu.user_id
9-2. inner join: 가장많이 사용하며, A와 B의 두 테이블 교집합 부분을 표시
두 테이블에서 모두 가지고 있는 데이터만 출력된다.
(예시) 유저 데이터로 Inner Join 이해해보기
select * from users u
inner join point_users p
on u.user_id = p.user_id;10. 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을 사용할 경우 정렬이 되질 않는다. 이때는 Sunquery를 사용한다!
11. Subquery: 쿼리 안에 쿼리, 원하는 데이터를 쉽게 얻을 때 쓰는 기능
11-1. where에 들어가는 Subquery : where 필드명 in (subquery)
(예시) 카카오페이로 결제한 주문건 유저들만, 유저 테이블에서 출력하기
select * from users u
where u.user_id in (select o.user_id from orders o
where o.payment_method = 'kakaopay');11-2. Select에 들어가는 Subquery : select 필드명, 필드명, (subquery) from ..
(예시) '오늘의 다짐'의 좋아요 수가 평소에 받았던 수에 비해 높고 낮은지 구해보기
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;11-3. From에 들어가는 Subquery (가장 많이 사용되는 유형)
내가 만든 Select와 이미 있는 테이블을 join하고 싶을 때 사용한다!
(예시)유저 별 좋아요 평균 구해보기 → checkins 테이블을 user_id로 group by
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_id12. 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이런식으로 서브쿼리가 계속 붙으면, inner join 안쪽이 헷갈릴 수 있다. 이럴 때 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_id13. 문자열
13-1. 문자열 쪼개보기: SUBSTRING_INDEX
(예시1) 이메일 주소에서 @앞의 아이디만 가져오거나, @뒤의 이메일 도메인을 가져오기
select user_id, email, SUBSTRING_INDEX(email, '@', 1) from users(예시2) 이메일에서 이메일 도메인만 가져와보기
select user_id, email, SUBSTRING_INDEX(email, '@', -1) from users13-2. 문자열 일부만 출력하기: SUBSTRING
문자열, 출력을 하고싶은 첫 글자의 위치, 몇개의 글자를 출력하고 싶은지
(예시1) orders 테이블에서 날짜까지 출력하기
select order_no, created_at, substring(created_at,1,10) as date from orders(예시2) 일별로 몇 개씩 주문이 일어났는지 살펴보기
select substring(created_at,1,10) as date, count(*) as cnt_date from orders
group by date14. CASE: 경우에 따라 원하는 값을 새 필드에 출력하기
(예시) 포인트 보유액에 따라 다르게 표시해주기
select pu.point_user_id, pu.point,
case
when pu.point > 10000 then '잘 하고 있어요!'
else '조금 더 달려주세요!'
END as '구분'
from point_users pu;