SQL 명령어 모음

테이블에서 원하는 컬럼만 선택

SELECT * (모든 쿼리) / query 중에 아무꺼나 원하는 거
from food_orders fo (테이블 명)_

 

---

컬럼에 별명(alias)을 주기

SELECT restaurant_name '레스토랑 이름', // 퀘리 옆에 ' ' 붙여서 이름 변경 가능 
price as '가격' // as를 붙여주어도 된다 
order_id ididid // 영어는 ' ' 굳이 안 붙여도 됨 
from food_orders fo

두 가지 방법 중 아무것이나 써도 된다.

---

WHERE 절 : 필터링의 기초 문법

select * 
from 테이블
where 필터링 조건 (eg. 20살 이상) 

select * 
from customers 
where age=21

21살인 사람들만 필터

---

sum : 덧셈 함수

select food_preparation_time, delivery_time, food_preparation_time + delivery_time as total_time 
from food_orders

---

count :숫자를 세는 함수

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

---

max min :최대값 최솟값

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

 

---

avg : 평균값

select avg(price) as average_price
from food_orders
where cuisine_type='Korean'

 

---

group by : 같은 것 끼리 묶기

​

select restaurant_name,
max(price) "최대 주문금액"
from food_orders
group by restaurant_name

---

order by : 오름차순 내림차순

select restaurant_name,
max(price) "최대 주문금액"
from food_orders
group by restaurant_name
order by max(price) desc

---

 

replace : 문자 바꾸기

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

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

select addr "원래 주소",
replace(addr, '문곡리', '문가리') "바뀐 주소"
from food_orders
where addr like '%문곡리%'

---

substr : 원하는 문자만 남기기

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

---

concat : 여러 컬럼의 문자를 합치기

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

---

if : 조건을 지정해주는 가장 기초 문법

if(조건, 조건을 충족할 때, 조건을 충족하지 못할 때)

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

select addr "원래 주소",
if(addr like '%평택군%', replace(addr, '문곡리', '문가리'), addr) "바뀐 주소"
from food_orders
where addr like '%문곡리%'

select substring(if(email like '%gmail%', replace(email, 'gmail', '@gmail'), email), 10) "이메일 도메인",
count(customer_id) "고객 수",
avg(age) "평균 연령"
from customers
group by 1

 

---

Case 문 : 조건을 여러가지 지정하고 싶을 때 - 기초

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

 

select order_id,
price,
quantity,
case when quantity=1 then price
when quantity>=2 then price/quantity end "음식 단가"
from food_orders

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

---

User Segmentation : 조건문과 수식을 이용하여 간단한

select name,
age,
gender,
case when (age between 10 and 19) and gender='male' then "10대 남자"
when (age between 10 and 19) and gender='female' then "10대 여자"
when (age between 20 and 29) and gender='male' then "20대 남자"
when (age between 20 and 29) and gender='female' then "20대 여자" end "그룹"
from customers
where age between 10 and 29

---

Subquery : 여러번의 연산을 한 번의 SQL 문으로 수행하기

select order_id, restaurant_name, food_preparation_time
from
(
select order_id, restaurant_name, food_preparation_time
from food_orders
) a

select order_id, restaurant_name, if(over_time>=0, over_time, 0) over_time
from
(
select order_id, restaurant_name, food_preparation_time-25 over_time
from food_orders
) a

---

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

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

---

조회한 데이터에 아무 값이 없다면 어떻게 해야 할까?

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

 

---

Pivot Table 예시

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

---

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

​

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, restaurant_name) cumulative_sum
from
(
select cuisine_type, restaurant_name, count(1) order_count
from food_orders
group by 1, 2
) a

---

date type : date_format 을 이용하여 년, 월, 일, 주 로 조회해 보기

1. 년 : Y (4자리), y(2자리)
2. 월 : M, m
3. 일 : d, e
4. 요일 : w

 

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

 

 

서브쿼리문의 사용방법

 
처음에 강의로 배웠던 방법으로는 FROM테이블을 서브쿼리에서 사용을 했지만
칼럼을 서브쿼리로도 사용할 수 있다는 것을 알았습니다.
 
예시 )
TABLE 방식

-- 코드를 입력하세요

SELECT NAME
FROM
(
SELECT NAME, DATETIME, MIN(DATETIME) MIN_D
FROM ANIMAL_INS 
) A
WHERE DATETIME = MIN_D

 
컬럼 방식

-- 코드를 입력하세요
SELECT NAME
FROM ANIMAL_INS 
WHERE DATETIME = (SELECT MIN(DATETIME) FROM ANIMAL_INS)

 
 

NULL 처리하기

 
처음에 내가 생각했던 방법으로는 REPLACE를 써서 바꾸어 주려고 했으나 NULL이라는 값은 글자가 아니라 말뜻 자체로 아무것도 가지고 있지 않은 값이기 때문에 REPLACE의 방법이 통하지 않았다.
 
그리하여 다음 방법으로 IF문을 이용하여 적용하는 방법이 였는데.

-- 코드를 입력하세요
SELECT ANIMAL_TYPE, IF(NAME IS NULL, 'No name', NAME) NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS 
ORDER BY ANIMAL_ID

 
 

DATE 포맷 변경하기

 

-- 코드를 입력하세요
SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME,'%Y-%m-%d')
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

 
DATE_FORMAT에서 괄호 안에 데이터와 표기 방법을 넣어주면 된다.
% Y,%m,%d,%e 년, 월, 일, 요일로 맞추어 넣어줄 수 있다.
 

반올림을 하는 방법

 
반올림을 하는 방법이 두 가지가 있는데
하나는 숫자형식으로 반올림을 할 것인가
하나는 문자형식으로 반올림을 할 것 인가이다.
 
먼저 숫자형식의 반올림은 ROUND를 사용해서 만들어주고

-- 코드를 입력하세요
SELECT ROUND(AVG(DAILY_FEE),0) AVERAGE_FEE 
FROM CAR_RENTAL_COMPANY_CAR
WHERE CAR_TYPE = 'SUV'

 
문자형식의 반올림은 FORMAT을 이용하여 만들어 줄 수 있다.

-- 코드를 입력하세요
SELECT FORMAT(AVG(DAILY_FEE),0) AVERAGE_FEE 
FROM CAR_RENTAL_COMPANY_CAR
WHERE CAR_TYPE = 'SUV'

 

가격대별 정리방법

 

내가 아는 방법으로는 CASE문을 이용하여 각 상황마다 조건을 주어서 만들어 주는 방식을 이용하였는데

-- 코드를 입력하세요
SELECT CASE WHEN PRICE < 10000 THEN '0'
            WHEN PRICE < 20000 THEN '10000'
            WHEN PRICE < 30000 THEN '20000'
            WHEN PRICE < 40000 THEN '30000'
            WHEN PRICE < 50000 THEN '40000'
            WHEN PRICE < 60000 THEN '50000'
            WHEN PRICE < 70000 THEN '60000'
            WHEN PRICE < 80000 THEN '70000'
            WHEN PRICE < 90000 THEN '80000'
            ELSE '90000'
            END 'PRICE_GROUP',
            COUNT(1) 'PRODUCTS'
FROM PRODUCT 
GROUP BY 1
ORDER BY 1

 
같은 답을 찾을 수는 있지만 숫자가 많아진다면 무식하게 늘이는 방법 밖에 없었습니다.
하지만 다른 작성자의 내용을 보고 깨달은 것으로
TRUNCATE 또는 ROUND 문을 이용하는 것입니다.

SELECT TRUNCATE(PRICE/10000,0)*10000 PRICE_GROUP, COUNT(*) PRODUCTS
FROM PRODUCT
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP;

 

 

JOIN에 JOIN 넣기

 

3개의 테이블을 합치는 방법으로 JOIN 밑으로 다시 조인은 넣어주어서 합쳐줄 수 있으며 각 테이블의
공통된 내용을 찾아서 맞추어 주어야 한다,
 

-- 코드를 입력하세요
SELECT A.APNT_NO, B.PT_NAME, A.PT_NO, A.MCDP_CD, C.DR_NAME, A.APNT_YMD
FROM APPOINTMENT A 
        INNER JOIN PATIENT B ON A.PT_NO = B.PT_NO 
        INNER JOIN DOCTOR C ON A.MDDR_ID = C.DR_ID
WHERE C.MCDP_CD = 'CS' AND APNT_YMD LIKE '2022-04-13%' AND A.APNT_CNCL_YN = 'N'
ORDER BY APNT_YMD

 

DATE 차이 계산법

 

SELECT CAR_ID, AVERAGE_DURATION
FROM
(
SELECT CAR_ID, ROUND(AVG(DATEDIFF(END_DATE,START_DATE)+1), 1) AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
GROUP BY 1
ORDER BY 2 DESC, 1 DESC
)A
WHERE AVERAGE_DURATION >= 7

 
위에서 보다시피
DATEDIFF(END_DATE, START_DATE)+1
마지막에 +1을 붙여주는데 날짜계산을 예를 들어
1일에서 10일까지를 생각하면 1을 포함해야 하기에 총 10일로 계산을 하지만
공식으로 계산이 되면 10-1이 되어서 9일로 계산이 된다.
그렇기에 +1을 넣어주어 우리가 원하는 값을 얻을 수 있다.

 
 
 

HAVING 이용하여 필터링하기

 
일반적인 칼럼을 필터링하기 위해서는 WHERE BY 절을 이용하여 내가 원하는 값만 가져와서 사용할 수 있었지만
SUM, AVG, COUNT와 같이 합쳐서 그룹을 만들어준 값을 기준으로 필터링을 하고 싶을 때에는 HAVING을 사용하여 
내가 원하는 값에 사용 할 수 있다.

-- 코드를 입력하세요
SELECT ID, NAME, HOST_ID
FROM PLACES
WHERE HOST_ID IN ( SELECT HOST_ID
                  FROM PLACES 
                  GROUP BY 1
                  HAVING COUNT(1) >= 2
                 )
ORDER BY 1

2개 이상 주문한 사람의 주문내역을 본다 같이 어떤 기준이 되는 값을 가지는 사람의 모든 데이터 값을 보기 위해서는 
그룹을 시켜 필터링을 하고 다시 그룹을 풀어주어야 하지만 한번 그룹이 되어버리면 풀어버릴 수 없기 때문에 
서브쿼리를 만들어주고 거기에서 내가 원하는 그룹만 HAVING으로 찾아내준다음
WHERE BY로 각 칼럼을 찾아내어 주면 된다.

 
 

UNION을 이용한 다른 테이블 속 컬럼 합치기

그림과 같이 테이블 명은 다르지만 동일한 컬럼을 사용할 때 같은 칼럼내용을 합쳐주기 위해서 UNION을 사용하여 합쳐줄 수 있다.

-- 코드를 입력하세요
SELECT FLAVOR
FROM 
(
SELECT *
FROM JULY
UNION
SELECT *
FROM FIRST_HALF    
)A
GROUP BY 1
ORDER BY SUM(TOTAL_ORDER) DESC
LIMIT 3

내용이 합쳐졌기 때문에 합을 계산하기에 편리해진다.

WITH문

 
서브쿼리랑 비슷한 느낌이지만 이것은 본문을 사용하기 전에 미리 선언을 하여서 다른 테이블을 하나 더 만들어준다고
생각을 하자 만들어두면 다른 곳에서 바로바로 꺼내서 사용을 할 수 있다.

-- 코드를 입력하세요
WITH TEMP1 AS(
    SELECT CAR_ID, MAX(END_DATE)
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
    GROUP BY 1
    HAVING YEAR(MAX(END_DATE)) <2023 AND MONTH(MAX(END_DATE)) < 11
),
TEMP2 AS(
    SELECT T1.CAR_ID, B.CAR_TYPE, B.DAILY_FEE
    FROM TEMP1 T1 INNER JOIN CAR_RENTAL_COMPANY_CAR B ON T1.CAR_ID = B.CAR_ID
    WHERE B.CAR_TYPE IN ('SUV', '세단')
),
TEMP3 AS(
    SELECT T2.CAR_ID, T2.CAR_TYPE,
    ROUND(T2.DAILY_FEE * 30 * (100 - C.DISCOUNT_RATE) / 100, 0) as FEE
    FROM TEMP2 T2 INNER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN C ON T2.CAR_TYPE = C.CAR_TYPE
    WHERE C.DURATION_TYPE = '30일 이상'
)

SELECT *
FROM TEMP3
WHERE FEE BETWEEN 500000 AND 1990000
ORDER BY FEE DESC, CAR_TYPE, CAR_ID DESC

 
 
또한 밑에서 나오는 재귀 쿼리에서도 이 WITH문을 이용하여 하는 방법이다.
 
 

재귀 쿼리

 
말로만 들어서는 조금 생소한 표현이지만 쉽게 표현을 하자면
한번 사용한 테이블 내용을 다시 가져와서 한 번 더 계산을 할 수 있기에 반복적으로 늘어나는 값이나
0~23시까지 하나하나 나누어주어야 하는 일이 필요할 때 유용하게 사용이 될 수 있다.
 

WITH RECURSIVE Numbers AS (
    -- 1. 초기 쿼리: 1부터 시작
    SELECT 1 AS NUM
    UNION ALL
    -- 2. 재귀 쿼리: 이전 숫자에 1을 더함
    SELECT NUM + 1
    FROM Numbers
    -- 3. 종료 조건: 10보다 작을 때만 재귀를 계속함
    WHERE NUM < 10
)
-- 생성된 숫자 출력
SELECT * FROM Numbers;

 
나는 이 방법을 이용해서 데이터상에는 나와있지 않은 값을 0으로 표현을 해야 했는데 공간이 있는 자리에 NULL로 표현이 되어 있다면 IF문이나 CASE문을 사용하여 조건식 변경을 해주면 가능하지만
지금 같은 경우에는 아예 쿼리 자체가 존재하지 않는 상황이기에 쿼리를 생성하여주어야 하는 상황이었다.
 

-- 코드를 입력하세요
WITH RECURSIVE CTE AS(
    SELECT 0 AS NUM
    UNION ALL
    SELECT NUM+1 FROM CTE
    WHERE NUM < 23
)

SELECT C.NUM HOUR, IFNULL(COUNT,0) COUNT
FROM CTE C LEFT JOIN (
    SELECT HOUR(DATETIME) HOUR, COUNT(1) COUNT
    FROM ANIMAL_OUTS 
    GROUP BY 1
) H ON C.NUM = H.HOUR

위에서 보다시피 재귀쿼리를 사용하기 위해서는 WITH문과 RECURSIVE를 사용하여 반복이 가능하도록 만들어주고
안쪽에서는 UNION문을 이용 하나하나 NUM을 추가하여 칼럼을 만들어주는 방식을 이용하였습니다.
 
또한 추가적인 방법으로 IF문에서 NULL을 바로 변경하는 명령문으로 IFNULL을 사용하면 된다는 것을 알았습니다