sql 다시 정리

SQL

데이터베이스와 대화하기 위한 언어
(ex. 데이터베이스에게 A를 주겠니? 라고 이야기 할 때 사용하는 언어)’

SQL = Query → SQL이란 언어를 이용하여 데이터베이스에 요청하는 것을 질의하는 것을
Query라고 한다.
(SQL : 데이터베이스에게 A를 좀 줘라고 말하기 위한 문법이라면, Query : A를 주세요 말하는것 자체)

데이터베이스

데이터가 저장되어있는 큰 폴더. 데이터베이스라는 폴더가 있고, 그안에 테이블이라는 파일이 있다.

select, from

SELECT : 데이터를 가져오는 명령어
FROM : 데이터를 가져올 테이블을 특정해주는 문법
*: 모든 컬럼을 가져온다

select * from 조회할 데이터

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

select 옆에 컬럼명을 지정 → 전체컬럼이 아닌 필요한 컬럼만 불러올 수 있다.

1. 원하는 컬럼 선택하기

필요한 것만 선택하여 조회 가능하며, 모든것을 조회하는 * 대신 필요한 컬럼만 적어준다.

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

ex) select restaurant_name, addr from food_orders

2. 컬럼에 별명 alias 부여

컬럼 옆에 별명을 적어준다.

방법1 : 컬럼 as 별명
방법2 : 컬럼 별명

별명지을 때 유의사항

영문, 언더바 (별명만 적음) → ex) ord_no
특수문자, 한글 (’별명'으로 큰따옴표 안에 적어줌) → ex) “ord no”, “주문 번호"

where

전체 데이터중 원하는 데이터만 필터링

select * from 테이블 
where 필터링 조건 (age = 21)

필터링은 숫자가 아닌 문자에도 사용이 가능하다. 문자를 사용할 때는 작은 다옴표를 사용한다.
대문자와 소문자를 서로 다른 문자로 인식해서 대소문자를 구분해야 한다.

select * from 테이블
where 필터링 조건 (gender = 'male')

비교연산, between, in, like

1. 비교연산

비교연산자	의미
\=	같다
<>	다르다
>	크다
>=	크거나 같다
<	작다
<=	작거나 같다

2. between, in, like

between : a와 b 사이

    between a and b

    ex) 나이가 21~23 사이
    select * from customers 
    where age between 21 and 23

in : 포함하는 조건 주기

    in (a,b,c)

    ex) 나이가 21, 25, 27 뽑기
    select * from customers 
    where age in (21, 25, 27)

    ex) 김하호, 박민윤 뽑기
    select * from customers 
    where name in ('김하호', '박민윤')

like : 완전히 같지는 않지만 비슷한 값을 조건으로 주기

    1. 특정한 문자로 시작하는 경우 → like '시작문자%'
    ex) 김으로 시작하는 사람 → where name like '김%' 

    2. 특정한 문자를 포함하는 경우 → like '%포함문자%'
    ex) 식당이름에 Next를 포함하는 경우 → where restaurant_name like '%Next%'

    3. 특정한 문자로 끝나는 경우 → like '%시작문자'
    ex) 임으로 끝나는 이름 → where name in '%임'

논리연산

여러개의 필터링 조건을 하나의 쿼리문에서 적용가능하다.

and 그리고
or 또는
not 아닌

sum, avg, count, min, max

1. 합계와 평균 구하기

1. 합계 → sum(컬럼)
2. 평균 → avg(컬럼)

ex) select avg(age) average_of_age from customers

2. 데이터의 갯수 구하기

주문건은 10개, 주문한 사람은 다섯명 일때 count(1)을 하면 테이블의 개수안 10개가 모두 나오고, distinct 고객명을 적으면 몇명의 고객이 주문했는지에 대한 값이 나와서 5개가 나온다.

1. 데이터 갯수 → count(컬럼) *컬럼명 대신 1혹은 * 사용가능
ex) 데이터 갯수 : 주문 테이블의 전체 주문은 몇건인가요?

2. 몇개의 값을 가지고 있는지 구할때 → count(distinct 컬럼)
distinct : 전체 개수가 아니라 컬럼에 몇개의 값을 가지고 있는지 구해주는 명령어
ex) 몇개의 값을 가지고 있는지 구할 때 : 주문을 한 고객은 몇명인가요?

3. 최솟값과 최댓값 구하기

1. 최솟값 : min(컬럼)
2. 최댓값 : max(컬럼)

group by

타입별로 원하는 단위로 나누는 것
여러번의 Query 없이, 카테고리를 지정하여 수식 함수로 연산을 할 수 있다.

group by절에는 위에서 묶고 싶은 범주형 컬럼을 모두 적어준다.

select 범주형 컬럼, sum(계산컬럼)
from 테이블
group by 범주형 컬럼

food_orders라는 테이블에서 데이터를 가져옴

sum(price) → 주문 금액의 합계를 구함

select 뒤에 cuisine_type이라는 컬럼이 들어가 있는데, 타입별로 합계 값 또는 평균값을 구하고 싶다라고 했을 때 범주를 주고싶은 컬럼을 명명해주면 원하는 대로 (cuisine_type별로) 합계나 평균 구하기 가능
select 에만 적어주면 안되고, sql문에도 명명 해줘야 하는데 그 구문이 가장 아래에 있는 group by절이다.
→ select문에서 범주형 컬럼을 적어줬을 때 범주형 컬럼을 그대로 group by에도 적어주면 해당 타입별로 값을 묶어서 연산한다는 뜻으로 데이터베이스에 명령을 전달해준다.

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

order by

쿼리 결과를 본인의 용도에 맞춰서 정렬

기본값 : 오름차순, 내림차순 : desc

select 범주형 컬럼, sum(계산컬럼)
from 테이블
group by 범주형 컬럼
order by 정렬을 원하는 컬럼(카테고리 컬럼이나 계산컬럼 둘다 가능)

sql문의 기본 구조

주문 테이블에서 → from
주문 수량이 1건인 주문건의 → where
음식 가격의 평균을 음식 종류별로 조회하여 → avg, group by
음식 가격이 높은 순서대로 정렬하기 → order by

replace, substring, concat

1. replace

특정 문자를 다른 문자로 바꿔준다.

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

2. substring

특정한 문자만 뽑아주는 구문

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

addr → 주소 컬럼에서 뽑아줄거야, 1 → 어디에서 뽑아올거야. 첫번째 문자부터 뽑아올 것, 2 → 글자수. 주소컬럼에서 데이터를 뽑아올건데 첫번째 문자부터 두글자씩만 뽑아와줘

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

3. concat

원하는 형태로 문자를 포멧팅

concat(값1, 값2, 값3, ...)

붙일 수 있는 문자의 종류 : 컬럼, 한글, 영어, 숫자, 기타 특수문자

if, case

1. if

if(조건, 참, 거짓)

if → 만약에, cuisine_type = Korean이면 컬럼의 값을 한식으로, Korean이 아닌 다른 값이라면 조건에 부합하지 않기 때문에 기타로 지정

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

2. case

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

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

문자변경과 조건문

1. 문자변경

replace : 지정한 문자를 다른 문자로 변경
substr : 특정 문자만 추출
concat : 여러 문자를 합하여 포맷팅

2. 조건문

if
case

subquery

여러번의 연산을 한번의 sql문으로 수행 → query 결과를 query에 다시 활용하는 것
쿼리를 한번 해준 후 그 결과물을 다시 활용하고 싶을 때 사용

subquery가 필요한 경우

여러번의 연산을 수행해야 할 때
조건문에 연산 결과를 사용해야 할 때
조건에 query 결과를 사용하고 싶을 때

select문 두개 → 괄호 안에서 한번 select 문으로 쿼리를 조회해 주면 column 1, 2가 나온다.

이 결과를 다시 main query문에서 계산을 해주고 싶을 때, 괄호안의 결과문에서 column1만 뽑아 올려서 사용해주는 것이 subquery문.

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)

join

두 개의 테이블을 서로 묶어서 하나의 결과를 만들어 내는 것

공통컬럼은 묶어주기 위한 ‘공통 값’ 이기 때문에 두 테이블의 컬럼명은 달라도 괜찮다.

주문 정보에서 고객 이메일을 알기 위해서는, 고객 정보에서 동일한 고객 ID 의 이메일을 가져와야 한다
JOIN 은 동일한 원리를 가지는데, 각각 주문 정보와 고객 정보가 테이블이라고 할 때, 고객 ID 를 기준으로 필요한 값을 가져와 주는 것
이 때 중요한 것은, 두 테이블이 공통으로 갖고 있는 컬럼이 된다. 위의 예시에서는 ‘고객 ID’

공통 컬럼을 기준으로 묶은 형태는 아래와 같이 된다.
즉, 공통 컬럼을 기준으로 두 테이블을 합쳐서, 각각 테이블에서 필요한 데이터를 조회할 수 있도록 만들어주는 것

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.공통컬럼명

주문 테이블과 고객 테이블을 cusomer_id 를 기준으로 left join 으로 묶어보기

(조회 컬럼 : order_id, customer_id, restaurant_name, price, name, age, gender)

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

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

[방법1] 없는 값을 제외해주기

Mysql 에서는 사용할 수 없는 값일 때 해당 값을 연산에서 제외해준다. → 0으로 간주
즉, 평균 rating 을 구하는 쿼리를 아래와 같이 작성했을 때 실제 연산에 사용되는 데이터는 다음과 같다.

select restaurant_name,
       avg(rating) average_of_rating,
       avg(if(rating<>'Not given', rating, null)) average_of_rating2
from food_orders
group by 1

따라서, 명확하게 연산을 지정해주기 위해 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

[방법2] 다른 값을 대신 사용하기

사용할 수 없는 값 대신 다른 값을 대체해서 사용도 가능하다.
데이터 분석 시에는 평균값 혹은 중앙값 등 대표값을 이용하여 대체해주기 한다.
다른 값으로 변경하고 싶을 때, 다음 두 개의 문법을 이용할 수 있다.
- 다른 값이 있을 때 조건문 이용하기 : if(rating>=1, rating, 대체값)
- null 값일 때 : coalesce(age, 대체값)
null 을 다른 값으로 대체한 쿼리문을 실행하면 다음과 같다.

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

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

[방법] 조건문으로 값의 범위를 지정하기

조건문으로 가장 큰 값, 가장 작은 값의 범위를 지정해 줄 수 있다. → 상식적인 수준 안에서 범위를 지정

SQL 로 Pivot Table 만들어보기

Pivot table

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

[실습] 음식점별 시간별 주문건수 Pivot Table 뷰 만들기 (15~20시 사이, 20시 주문건수 기준 내림차순)

음식점별, 시간별 주문건수 집계하기

 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

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 은 각 행의 관계를 정의하기 위한 함수로 그룹 내의 연산을 쉽게 만들어준다.

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

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

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

Rank ‘특정 기준으로 순위를 매겨주는’ 기능
예를 들어, 주문 건수별 순위 매기기, 결제 시간이 빠른 순으로 순위 매기기 등이 가능하다.

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

음식 타입별, 음식점별 주문 건수 집계하기

 select cuisine_type, restaurant_name, count(1) order_count
 from food_orders
 group by 1, 2

Rank 함수 적용하기

 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

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

sum : 합계를 구하는 기능
누적합이 필요하거나 카테고리별 합계컬럼와 원본 컬럼을 함께 이용할 때 유용하게 사용할 수 있다.

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