2023.09.14.<SQL>MySQL_문법정리

스파르타 코딩클럽_엑셀보다 쉬운 SQL

1. 정의

• 쿼리문이란 
• subquery란 : 쿼리문 안에 쿼리가 있는 것을 의미한다.

 

---

2. 문법

2-1. Database의 모든 Table 보기

Show tables;

2-2. Where

• where +  in → 1,3주차 사람들의 '오늘의 다짐' 데이터만 보고 싶을 경우

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

• where +  like → % 값 % 가능

2-3. Limit

• 원하는 개수만 가져오기

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

2-4. Distinct

• 중복 제거

select distinct(payment_method) from orders;

2-5. Group by

• Group by name ; name값이 같은 필드끼리 묶어서 합쳐준다.
• 함께 사용할 때 select절에 min, max, sum, avg 등 사용가능
•  콤마를 통해서 두가지 묶기 가능

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

 

2-5. Order by

• Order by point ; point순으로 정렬
• 콤마를 통해서 두가지 묶기 가능
• 자동 오름차순, 내림차순 정렬 원할 시 Order절 마지막에 desc 붙이기

select * from 테이블명
order by 정렬의 기준이 될 필드명;

2-6. As(Alias)

 

2-7.  ( inner) Join

• 자동으로 탐

 

 2-8. Left Join

• 값이 없는 경우 Null값 반환
•  wherewjf + is NULL, is not NULL을 통해서 통계내기 가능
•  name별로 is Null인 사람을 count해라

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

 

2-9. Union

• 7월과 8월을 붙여주고 싶을 경우 → (  ) union all ( ) 

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

 

 

2-10. Substring_index, Substring 

• 문자열 데이터 추출

1.SUBSTRING_INDEX
- email 필드의 값 → @을 기준으로 앞쪽의 문자 (1,-1 사용가능)
select user_id, email, SUBSTRING_INDEX(email, '@', 1) from users

1.SUBSTRING
- created_at 필드의 값 → 1번째부터 10번째까지의 문자를 가져와라.
select order_no, created_at, substring(created_at,1,10) as date from orders

•  활용 : 일별로 주문이 몇개씩인지 알아보기

 

substring을 사용하지 않을 경우 시간,분,초 단위까지 나오기 때문에 일별 데이터 뽑기 곤란 select created_at, count(*) from orders o  group by created_at
substring을 사용하여 시간, 분, 초 단위를 잘라서 사용할 경우 일별 데이터 뽑기 가능 select substring(created_at,1,10) as date,  count(*) as cnt_date from orders group by date

 

2-11. Case

• 기본 형식 
• 타 언어 IF절이라 생각하면 편함

select 필드1, 필드2,
case
when 조건1 then '출력할 문장1'
when 조건2 then '출력할 문장2'
else '출력할 문장3'
End as 별명
from 테이블명

---

3. 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');

 

•  Select 필드명, 필드명, (Subquery) from ...

예를들어

1.'4b8a10e6'의 평균 좋아요의 개수
 
select avg(likes) from checkins c2
where c2.user_id = '4b8a10e6'

2. 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;

 

• Select 필드명, 필드명, (Subquery) from ...

예를 들어

1. 유저별 평균 좋아요의 수
select user_id, round(avg(likes),1) as avg_like from checkins
group by user_id

2. subquery를 통한 포인트와 like의 상관관계 출력
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

---

4. With

• 쿼리문 제일 위쪽에 with table1(테이블명 지정) as (서브쿼리문) 을 통해 별명을 붙여서 사용할 수 있다.
• with문까지 스크롤해서 실행시켜야 오류가 나질 않는다!

with table1 as (서브쿼리문1), table2 as (서브쿼리문2)

예를 들어 
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