SQL 에디터 - DBeaver
Ctrl + Enter = 실행
1) show - 테이블 보기
show tables - 현재 DB 안에 table 리스트 전체 보기 2) select - 테이블안에 있는 Column Data 보기
ex. select * from table - 'table' 테이블 안에 있는 모든 data 보기
3) where - 조건절
ex. select * from orders
where payment_method = "kakaopay"
- orders 테이블에서 payment_method가 "kakaopay"인 Data 전체보기
3-1) where와 같이 쓰는 문법
i) != - '같지 않음'
select * from orders
where course_title != "앱개발 종합반"
ii) between - 범위 조건
select * from orders
where created_at between "2020-07-13" and "2020-07-15"
iii) in - 포함 조건
select * from checkins
where week in (1,3)
iV) like - 문자열 패턴 조건
select * from users
where email like '%daum.net'
4) Limit - 일부 데이터만 가져오기
select * from orders
where payment_method="kakaopay"
limit 5 - 5개만 보여줘 5) Distinct - 중복데이터 제외하고 가져오기
ex. select distinct(payment_method) from orders
6) Count - 숫자 세기
ex. select count(*) from orders
7) Group by - 동일 범주 데이터를 하나로 묶기
ex. select name, count(*) from users
group by name
8-1) min - 최소값 / max - 최대값
avg - 평균 / sum - 합계
8) Order by - 정렬 (기본 오름차순)
ex. select name, count() from users
group by name
order by count() - 오름차순
order by count(*) desc - 내림차순
- 퀴리 실행 순서 = from → group by → select → order by
9) Alias - 별칭 기능
ex. select payment_method, count(*) as cnt from orders o
where o.course_title = '앱개발 종합반'
group by payment_method
10) Inner Join - 테이블 합치기 (교집합)
ex. select * from users u
inner join point_users p
on u.user_id = p.user_id
- 기준이 되는 테이블을 from 절에, 붙이고 싶은 테이블을 Join 절에
11) Left Join - 테이블 합치기 (합집합)
ex. select name, count(*) from users u
left join point_users pu on u.user_id = pu.user_id
where pu.point_userid is Null
group by name
12) Union - 위아래 합치기 (colum 명 같아야 함)
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
)
* union 사용시 내부정렬 먹지 않는다 → Subquery 이용
13) Subquery(서브 쿼리) - 쿼리 안의 쿼리
ex1. select * from users u - where 절
where u.user_id in (select o.user_id from orders o
where o.payment_method = 'kakaopay');
i) from 실행: users 데이터를 가져와줌
ii) Subquery 실행: 해당되는 user_id의 명단을 뽑아줌
iii) where .. in 절에서 subquery의 결과에 해당되는 'user_id의 명단' 조건으로 필터링 해
iv) 조건에 맞는 결과 출력
ex2. select c.checkin_id, c.user_id, c.likes, - select 절
(
select avg(likes) from checkins c2
where c2.user_id = c.user_id) as avg_like_user
from checkins c;
i) 밖의 select * from 문에서 데이터를 한줄한줄 출력하는 과정에서
ii) select 안의 subquery가 매 데이터 한줄마다 실행되는데
iii) 그 데이터 한 줄의 user_id를 갖는 데이터의 평균 좋아요 값을 subquery에서 계산해서
iv) 함께 출력해준다! ex3. 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
i) 먼저 서브쿼리의 select가 실행되고,
ii) 이것을 테이블처럼 여기고 밖의 select가 실행!14) With - 새로 만든 테이블을 정의해버리기
ex. 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
15) SUBSTRING_INDEX - 기준 문자로부터 추출하기
ex1. select user_id, email, SUBSTRING_INDEX(email, '@', 1) from users
@를 기준으로 텍스트를 쪼개고, 그 중 첫 번째 조각을 가져오라는 뜻!
ex2. select user_id, email, SUBSTRING_INDEX(email, '@', -1) from users
@를 기준으로 텍스트를 쪼개고, 그 중 마지막 조각을 가져오라는 뜻!
16) SUBSTRING - 문자열, 출력하고 싶은 첫글자 위치, 개수 (like mid() in excel)
ex. select order_no, created_at, substring(created_at,1,10) as date from orders
17) Case - 경우에 따라 원하는 값 (like if else in excel)
ex. 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 lv
from point_users pu
)
select lv, count(*) as cnt from table1
group by lv
