SQL 기초 정리본

데이터 조회하기

SELECT / FROM

SQL은 코드가 아니라 데이터베이스에 요청하는 언어
따라서 어떤 요청을 할 지를 생각해봐야한다.

어느 테이블에서 데이터를 가져올까?

select
데이터를 가져오는 기본 명령어, 데이터를 조회하는 모든 쿼리에 사용
from
데이터를 가져올 테이블을 특정해주는 문법
*
모든 컬럼을 가져와 준다는 의미

예시

select *
from food_orders

필요한 항목만 뽑아서 사용하기

테이블의 모든 컬럼이 필요하지 않을 때, 필요한 것만 선택하여 조회 가능
예시

select 컬럼1, 컬럼2
from 테이블

컬럼에 별명(alias) 주기

방법1

컬럼 1 as 별명 1

방법2

컬럼2 별명2

영문 언더바 별명만 적는다
특수문자 한글 큰 따옴표 안에 적어준다 / "별명"

조건에 맞는 데이터로 필터링하기

WHERE

데이터 중 특정 조건을 필터링할 때, where 문법을 잉요해 전체 데이터 중 원하는 데이터만 필터링이 가능하다.
예시

select *
from customers
where age=21

숫자가 아닌 문자도 가능하다. 문자를 사용할 때는 '작은 따옴표'를 씌운다.

필터링을 할 때 유용한 표현

같음, 큼, 작음의 조건 지정

다양한 조건의 종류

1. between a and b

select *
from food_orders
where price between 20000 and 30000

2. in (a, b, c)

select *
from customers
where age in (15,21,31)

3. like '%'
   • 시작문자%
   • %포함문자%
   • %끝나는문자

select *
from food_orders
where restaurant_name like 'B%'

여러 개의 조건으로 필터링하기 (논리연산)

논리연산이란

여러 조건을 적용할 때 사용되는 연산

예시

select *
from food_orders
where cuisine_type='Korean' and price>=30000

---

데이터 계산하기

SQL을 이용해 숫자 계산 값을 조회해보기

: 데이터를 조회하는 SQL 기본 구조를 활용해 숫자 계산 및 정렬 사용해보기

SUM, AVERAGE, COUNT, MIN, MAX

✅ 기본 연산
연산자 종류 : +(더하기) -(빼기) *(곱하기) /(나누기)
함수 종류 : SUM, AVG
사용할 때는 데이터의 범위가 아닌 계산할 컬럼을 적어야 한다.

select sum(food_preparation_time) total_food_preparation_time,
	avg(delivery_time) avg_food_preparation_time
from food_orders

✅ 전체 데이터의 갯수 구하기
함수종류 : COUNT(컬럼 / 1 / *)
몇 개의 값을 가지고 있는 지 구할 때에는 distinct 를 사용한다.

select count(1) count_of_orders,
	count(distinct customer_id) 
    count_of_customers
from food_orders

✅ 데이터의 범위, 최솟값과 최댓값 구하기
함수종류: MIN, MAX (컬럼)
조회하는 데이터의 최솟값, 최댓값으로 범위도 구할 수 있다 !

select min(price) min_price,
	max(price) max_price
from food_orders

범주별 연산 : GROUP BY

✅ 카테고리 별 숫자 연산을 간단하게.
기본구조 : 카테고리 컬럼 지정, GROUP BY 작성

select cuisine_type, sum(price) sum_of_price
from food_orders
group by cuisine_type

정렬 연산 : ORDER BY

✅ 조회한 결과를 원하는 순서대로 정렬하기
정렬 종류 : 오름차순(생략), 내림차순 desc
기본구조 : 카테고리 컬럼 지정, ORDER BY 작성

select cuisine_type, sum(price) sum_of_price
from food_orders
group by cuisine_type
order by sum(price)

Query 작성 전 흐름 정리

1. 어떤 테이블에서 데이터를 뽑을 것인가 FROM
2. 어떤 컬럼을 이용할 것인가 SELECT
3. 어떤 조건을 지정해야 하는가 WHERE
4. 어떤 함수(수식)를 이용해야하는가

---

문자 포맷, SQL로 가공하기

Query 결과를 바로 사용할 수 있도록 문자 데이터의 형태를 바꾸기

사용 함수 : REPLACE, SUBSTRING, CONCAT

특정 문자를 다른 문자로 바꿀 때

replace (바꿀 컬럼, 현재 값, 바꿀 값)

select restaurant_name "원래 상점명",
replace(restaurant_name, 'Blue', 'Pink') "바뀐 상점명"
from food_orders
where restaurant_name like '%Blue Ribbon%'

원하는 문자만 남기기

substring(substr) (조회할 컬럼, 시작위치, 글자 수)

select addr "원래 주소",
substr(addr, 1, 2) "시도"
from food_orders
where addr like '%서울특별시%'

여러 컬럼의 문자를 합치기
붙일 수 있는 문자의 종류 : 컬럼, 한글, 영어, 숫자, 특수문자

concat(붙이고 싶은 값 1, 2, 3, ....)

select restaurant_name "원래 이름",
		addr "원래 주소",
concat('[', substring(addr, 1, 2), '] ', restaurant_name) "바뀐 이름
from food_orders
where addr like '%서울%'

문자데이터를 바꾸고 GROUP BY 사용하기

흐름 정리
1. 어떤 테이블에서 데이터를 뽑을 것인가
2. 어떤 컬럼을 이용할 것인가
3. 어떤 조건을 지정해야 하는가
4. 어떤 함수(수식)를 이용해야 하는가

예시
[지역(시도)] 음식점이름 (음식종류) 컬럼을 만들고, 총 주문건수 구하기

구문
1.
어떤 테이블에서 데이터를 뽑을 것인가 → from food_orders
2.
어떤 컬럼을 이용할 것인가 → addr, restaurant_name, cuisine_type,order_id
3.
어떤 조건을 지정해야 하는가 → X
4.
어떤 함수 (수식) 을 이용해야 하는가 → count(1), substring(addr, 1,2),concat(’[’, 뽑은시도, ‘] ‘, restaurant_name, ‘ (’, cuisine_type, ‘)’)

합치기

select concat('[', substring(addr, 1, 2), '] ', restaurant_name, ' (', cuisine
count(1) "주문건수"
from food_orders
group by 1

조건에 따라 포맷을 다르게

사용함수 : if, case

내가 원하는 범주를 조건으로 주고, 해당 범주에 적용하고 싶은 것을 지정한다.

원하는 조건에 충족할 때 적용할 방법과 아닌 방법을 지정하기

IF(조건, 조건을 충족할 때, 조건을 미충족할 때)

select restaurant_name,
cuisine_type "원래 음식 타입",
if(cuisine_type='Korean', '한식', '기타') "음식 타입"
from food_orders

조건을 여러가지 지정하고 싶을 때

CASE when 조건 1 then 값(수식)1
when 조건 2 then 값(수식)2
else 값(수식) 3
end

select restaurant_name,
addr,
case when addr like '%경기도%' then '경기도'
     when addr like '%특별%' or addr like '%광역%' then substrin
     else substring(addr, 1, 2)
end "변경된 주소"
from food_orders

조건을 사용할 수 있는 경우들
1. 새로운 카테고리 만들기
2. 연산식을 적용할 조건 지정하기
3. 다른 문법 안에서 적용하기

데이터 타입이 다를 때 변경법

--숫자로 변경
cast(if(rating='Not given', '1', rating) as decimal) 

--문자로 변경
concat(restaurant_name, '-', cast(order_id as char))

---

Subquery

언제 쓰나요

• 여러 번의 연산을 수행해야 할 때
• 조건문에 연산 결과를 사용해야 할 때
• 조건에 Query 결과를 사용해야 할 때

기본 구조

select column1, special_column
from
( /* subquery */
select column1, column2 special_column
from table1
) a

select column1, column2
from table1
where column1 = (select col1 from table2)

조건문과 결합하기 - user segmentation

실습
음식점의 평균 단가별 segmentation을 진행하고, 그룹에 따라 수수료 연산하기

흐름 정리
1.어떤 테이블에서 데이터를 뽑을 것인가 → 주문 테이블
2.어떤 컬럼을 이용할 것인가 → 식당 이름, 주문 금액, 주문 수량
3.어떤 조건을 지정해야 하는가 → X
4.어떤 함수 (수식) 을 이용해야 하는가 → 평균 구하는 식, 카테고리에 따라 연산, 조건문

쿼리 작성

select restaurant_name,
price_per_plate*ratio_of_add "수수료"
from
(
select restaurant_name,
case when price_per_plate<5000 then 0.005
when price_per_plate between 5000 and 19999 then 0.01
when price_per_plate between 20000 and 29999 then 0.02
else 0.03 end ratio_of_add,
price_per_plate
from
(
select restaurant_name, avg(price/quantity) price_per_plate
from food_orders
group by 1
) a
) b

복잡한 연산 수행하기

실습
음식점의 총 주문수량과 주문 금액을 연산하고, 주문 수량을 기반으로 수수료 할인율 구하기

✅ 할인 조건

• 수량 5개 이하 10%
• 수량 15개 초과, 총 주문금액 300,000원 이상 0.5%
• 이 외 일괄 1%

흐름 정리
1. 어떤 테이블에서 데이터를 뽑을 것인가 → 주문 테이블
2. 어떤 컬럼을 이용할 것인가 → 음식점 이름, 주문 수량, 주문 금액
3. 어떤 조건을 지정해야 하는가 → X
4. 어떤 함수 (수식) 을 이용해야 하는가 → 합계를 구하는 기능, 조건문

쿼리 작성

select restaurant_name,
case when sum_of_quantity<=5 then 0.1
when sum_of_quantity>15 and sum_of_price>=300000 then 0.0
else 0.01 end ratio_of_add
from
(
select restaurant_name,
sum(quantity) sum_of_quantity,
sum(price) sum_of_price
from food_orders
group by 1
) a

---

JOIN

언제 쓰나요

필요한 데이터가 서로 다른 테이블에 있을 때

기본 원리와 종류

두 테이블이 공통으로 갖고 있는 컬럼을 기준으로 테이블을 합쳐 필요한 데이터를 조회할 수 있도록 해준다.

LEFT JOIN
공통 컬럼을 기준으로, 하나의 테이블에 값이 없더라도 모두 조회되는 경우

INNER JOIN
공통 컬럼을 기준으로, 두 테이블 모두에 있는 값만 조회되는 경우

기본 구조

-- LEFT JOIN
select 조회 할 컬럼
from 테이블1 a left join 테이블2 b on a.공통컬럼명=b.공통컬럼명

-- INNER JOIN
select 조회 할 컬럼
from 테이블1 a inner join 테이블2 b on a.공통컬럼명=b.공통컬럼명

실습
한국 음식의 주문별 결제 수단과 수수료율을 조회하기
(조회 컬럼 : 주문 번호,식당 이름, 주문 가격, 결제 수단, 수수료율)
결제 정보가 없는 경우도 포함하여 조회

흐름 작성
1. 어떤 테이블에서 데이터를 뽑을 것인가 → from food_orders, payments
2. 어떤 컬럼을 이용할 것인가 → order_id, restaurant_name, price, pay_type, vat
3. 어떤 조건을 지정해야 하는가 → where cuisine_type=’Korean’
4. 어떤 함수 (수식) 을 이용해야 하는가 → left join payments (on order_id)

쿼리 작성

select a.order_id,
a.restaurant_name,
a.price,
b.pay_type,
b.vat
from food_orders a left join payments b on a.order_id=b.order_id
where cuisine_type='Korean'

---

조회한 데이터에 아무런 값이 없을 때

사용할 수 없는 데이터가 들어있거나, 값이 없는 경우에 사용한다.

값의 제외 null 이용

예시

select a.order_id,
a.customer_id,
a.restaurant_name,
a.price,
b.name,
b.age,
b.gender
from food_orders a left join customers b on a.customer_id=b.customer_id
where b.customer_id is not null

값의 변경 coalesce(컬럼, 대체값)

예시

select a.order_id,
a.customer_id,
a.restaurant_name,
a.price,
b.name,
b.age,
coalesce(b.age, 20) "null 제거",
b.gender
from food_orders a left join customers b on a.customer_id=b.customer_id
where b.age is null

조회한 데이터가 상식적이지 않은 값을 갖고 있을 때

예를 들어, 주문고객의 나이가 2세라거나, 결제일자가 1970년도 등일 때

조건문으로 값의 범위를 지정한다

예시

select customer_id, name, email, gendor, age,
case when age<15 then 15
when age>80 then 80
else age end "범위를 지정해준 age"
from customers

15세 미만은 15세로, 80세 초과는 80세로 대체된 것을 확인할 수 있다.

SQL로 Pivot table 작성

max() 활용

pivot table

2개 이상의 기준으로 데이터를 집계할 때, 보기 쉽게 배열하여 보여주는 것

실습
음식점 별 시간 별 주문건 수 확인하기 (15-20시 사이, 20시 주문건 수 기준 내림차순)

1. 음식점 별, 시간 별 주문건수를 먼저 집계한다

select a.restaurant_name,
substring(b.time, 1, 2) hh,
count(1) cnt_order
from food_orders a inner join payments b on a.order_id=b.order_id
where substring(b.time, 1, 2) between 15 and 20
group by 1, 2

2. pivot view 구조를 작성한다

select restaurant_name,
max(if(hh='15', cnt_order, 0)) "15",
max(if(hh='16', cnt_order, 0)) "16",
max(if(hh='17', cnt_order, 0)) "17",
max(if(hh='18', cnt_order, 0)) "18",
max(if(hh='19', cnt_order, 0)) "19",
max(if(hh='20', cnt_order, 0)) "20"
from
(
select a.restaurant_name,
substring(b.time, 1, 2) hh,
count(1) cnt_order
from food_orders a inner join payments b on a.order_id=b.order_id
where substring(b.time, 1, 2) between 15 and 20
group by 1, 2
) a
group by 1
order by 7 desc

window function - rank, sum

window function

각 행의 관계를 정의하기 위한 함수, 그룹 내의 연산을 쉽게 만든다.
예시
한식 식당 중, 주문 건수가 많은 순으로 순위 매기기
한식 식당 전체 주문 건수 중에서 A식당이 차지하는 비율

기본 구조

window_function(argument) over (partition by 그룹 기준 컬럼 order by 정렬 기준)

window_function : 기능 명을 사용해줍니다. (sum, avg 와 같이 기능명이 있습니다)
argument : 함수에 따라 작성하거나 생략합니다.
partition by : 그룹을 나누기 위한 기준입니다. group by 절과 유사하다고 생각해주시면 됩니다.
order by : window function 을 적용할 때 정렬 할 컬럼 기준을 적어줍니다.

rank

N번째까지의 대상을 조회하고 싶을 때

특정 기준으로 순위를 매겨주는 기능!

실습
음식 타입 별로 주문 건수가 가장 많은 상점 3개씩 조회하기

select cuisine_type,
restaurant_name,
order_count,
rn "순위"
from
(
select cuisine_type,
restaurant_name,
rank() over (partition by cuisine_type order by order_count desc) rn,
order_count
from
(
select cuisine_type, restaurant_name, count(1) order_count
from food_orders
group by 1, 2
) a
) b
where rn<=3
order by 1, 4

sum

전체에서 차지하는 비율, 누적 합을 구할 때

합계를 구하는 기능이지만, 누적 합을 구하거나 카테고리 별 합계 컬럼과 원본 컬럼을 함께 이용할 때 유용하다 !

실습
각 음식점의 주문건이 해당 음식 타입에서 차지하는 비율을 구하고, 주문 건이 낮은 순으로 정렬했을 때 누적합 구하기

select cuisine_type,
restaurant_name,
order_count,
sum(order_count) over (partition by cuisine_type) sum_cuisine_type,
sum(order_count) over (partition by cuisine_type order by order_count, restau
from
(
select cuisine_type, restaurant_name, count(1) order_count
from food_orders
group by 1, 2
) a

날짜 포맷과 조건까지 SQL로

sql의 연산은 날짜도 가능하다. 데이터에 날짜를 지정하거나 조건에 날짜를 사용해야 할 때 활용해보자.

1. yyyy-mm-dd 형식의 컬럼을 date type으로 변경하기

select date(date) date_type,
date
from payments

2. date type을 date_format을 이용하여 조회하기
   년 : YYYY / yy
   월 : M / m
   일 : D / d
   요일 : w (월요일이 1)

select date(date) date_type,
date_format(date(date), '%Y') "년",
date_format(date(date), '%m') "월",
date_format(date(date), '%d') "일",
date_format(date(date), '%w') "요일"
from payments

실습
년도, 월 별 주문건수를 구해 3월 조건으로 지정하고, 년도별로 정렬하기

select date_format(date(date), '%Y') y,
date_format(date(date), '%m') m,
count(1) order_count
from food_orders a inner join payments b on a.order_id=b.order_id
where date_format(date(date), '%m')='03'
group by 1, 2
order by 1