[TIL] MySQL 숫자 연산, GROUP BY, ORDER BY, 문자열 연산, 조건문 함수 학습 (24-12-16)

🤖 사전 캠프 6일차 진행 사항 🤖 

• 엑셀보다 쉽고 빠른 SQL 2주차 수업
  • 2-1 ~ 2-6
  • 숙제
• 엑셀보다 쉽고 빠른 SQL 3주차 수업
  
  • 3-1 ~ 3-7
  • 숙제

---

엑셀보다 쉽고 빠른 SQL 2주차 수업

1. SUM / AVERAGE

1-1. SUM(컬럼)

: 숫자 타입의 컬럼에 대해 합계를 출력

 

1-2. AVG(컬럼)

: 숫자 타입의 컬럼에 대해 평균을 출력

 

• food_orders 테이블에서 음식 준비 시간의 합계와 배달 시간의 평균을 출력.

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

> 음식 준비 시간의 합계는 컬럼명을 total_food_preparation_time로 출력

> 배달 시간의 평균은 컬럼명을  avg_food_delivery_time로 출력

2. COUNT

데이터의 개수 출력

 

2-1. 전체 개수

: COUNT(컬럼) 또는 COUNT(*) 또는 COUNT(1)

 

2-2. 중복 제외 개수

: COUNT(DISTINCT 컬럼)

 

• food_orders 테이블에서 전체 주문 개수와 주문한 고객의 수를 출력
  • 전체 주문 개수는 테이블의 모든 튜플의 수를 출력하면 됨. ==> count(*)
  • 주문한 고객의 수는, 한 고객이 주문을 1개 이상 했을 수 있으므로
    주문 테이블에서 주문한 고객만 중복 없이 개수를 새야 함. ==> count(distinct 고객아이디)

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

> 전체 주문 개수는 컬럼명을 count_of_orders로 출력

> 주문한 고객의 수는 컬럼명을 count_of_customers로 출력

3. MIN / MAX

3-1. MIN(컬럼)

: 해당 컬럼의 최솟값

 

3-2. MAX(컬럼)

: 해당 컬럼의 최댓값

 

• food_orders 테이블에서 주문 가격의 최솟값(min_price)과 최댓값(max_price) 출력

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

---

💥 쿼리를 작성하는 순서 💥

• 주문 금액이 30000원 이상인 주문건의 개수 구하기

1. 어떤 테이블? ==> FROM 음식주문테이블

2. 어떤 컬럼? ==> 개수 이므로 count(*)

3. 어떤 조건? ==> WHERE 주문금액 >= 30000

4. 쿼리 작성 ==> 컬럼명과 타입은 테이블 구조를 살펴보고 결정

food_orders 테이블의 컬럼 정보

SELECT COUNT(*)
FROM food_orders
WHERE price >= 30000

---

• 한국 음식의 주문 당 평균 음식가격 구하기

1. 어떤 테이블? ==> FROM 음식주문테이블

2. 어떤 컬럼? ==> 평균 음식가격이므로 avg(음식가격)

3. 어떤 조건? ==> 음식 타입이 한국 음식이어야 하므로 WHERE 음식타입 = 'Korean'

4. 쿼리 작성

SELECT AVG(price) as average_koreanFood_price
FROM food_orders
WHERE cuisine_type = 'Korean'

---

4. GROUP BY

GROUP BY 컬럼

컬럼 값 별로 그룹화하여 출력

select 컬럼, ...
from 테이블
where 조건
group by 컬럼

 

실습 테이블 : food_orders

food_orders 테이블의 컬럼 정보

• 음식 종류 별 주문 금액 합계 조회
  • 음식 종류 별 ==> group by cusine_type
  • 주문 금액 합계 ==> select cusine_type, sum(price)

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

---

• 음식점 별 주문 금액 최댓값 조회
  • 음식점 별 ==> group by restaurant_name
  • 주문 금액 최댓값 ==> select restaurant_name , max(price)

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

5. ORDER BY

ORDER BY 컬럼 [ASC/DESC]

원하는 컬럼 기준으로 오름차순(ASC) 혹은 내림차순(DESC)로 정렬하여 출력

ORDER BY 컬럼 ASC : 오름차순으로 정렬. ASC는 생략 가능

ORDER BY 컬럼 DESC : 내림차순으로 정렬

• 오름차순 : 숫자가 점점 커지는 순서로 정렬
• 내림차순 : 숫자가 점점 작아지는 순서로 정렬

여러 컬럼으로 정렬 가능, 정렬의 우선순위는 먼저 적힌 컬럼부터

ex) ORDER BY 이름, 나이 DESC ==> 이름을 오름차순으로 정렬하고, 나이를 내림차순으로 정렬

select 컬럼, ...
from 테이블
where 조건
group by 컬럼
order by 컬럼1 [,컬럼2, 컬럼3, ...]

 

• 음식 종류 별 주문 금액 합계 조회 - 합계 금액이 높은 순으로 정렬
  • 음식 종류 별 ==> group by cusine_type
  • 주문 금액 합계 ==> select cusine_type, sum(price)
  • 금액이 높은 순으로 정렬 ==> order by sum(price) desc

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

정렬 전 / 정렬 후

6. 숙제

음식 종류별 가장 높은 주문 금액과 가장 낮은 주문금액을 조회하고, 가장 낮은 주문금액 순으로 (내림차순) 정렬하기

1. 테이블 ==> from food_orders
2. 컬럼 ==> select cuisine_type, min (price) as min_price, max(price) as max_price
3. 음식 종류 별 ==>  group by cuisine_type
4. 가장 낮은 주문금액 순으로 (내림차순) 정렬 ==> order by min(price) desc
5. 쿼리 작성

select cuisine_type, min(price) as min_price, max(price) as max_price
from food_orders
group by cuisine_type
order by min(price) desc

---

엑셀보다 쉽고 빠른 SQL 3주차 수업

1. REPLACE

특정 문자를 다른 문자로 바꾸기

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

 

• 주소의 '문곡리'를 '문가리'로 변경

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

2. SUBSTR (SUBSTRING)

특정 문자만 추출

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

 

• 서울 음식점들의 주소를 전체가 아닌 ‘시도’ 만 나오도록 수정

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

3. CONCAT

문자 합치기

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

 

• 서울시에 있는 음식점은 ‘[서울] 음식점명’ 이라고 수정

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

4. 조건문

4-1. IF

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

 

• ‘문곡리’ 가 평택에만 해당될 때, 평택 ‘문곡리’ 만 ‘문가리’ 로 수정

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

 

 

4-2. CASE

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 substring(addr, 1, 5)
            else substring(addr, 1, 2) end "변경된 주소"
from food_orders

---

• 10세 이상, 30세 미만의 고객의 나이와 성별로 그룹 나누기 (이름도 같이 출력)

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

5. 숙제

다음의 조건으로 배달시간이 늦었는지 판단하는 값을 만들어주세요.

• 주중 : 25분 이상
• 주말 : 30분 이상

1. SQL 문의 기본 구조로 시작
2. 조건을 여러번 적용할 때 if, case 문 중 어떤 것을 이용할지 결정
3. 조건에 ‘주중, 주말’ 조건과 ‘배달시간’ 조건을 동시에 줄 때 사용 할 논리연산자 결정