METHOD: [REPLACE] [SUBSTRING] [CONCAT]

새로운 내용 배우기 전 복습

 

SQL 기본 구조와 순서 외우기

select -> from -> where -> group by -> order by 

 

조회 조건과 매칭하는 연습 많이 해보기

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

---

1. 업무 필요한 문자 포맷이 다를 때,  SQL로 가공하는 함수 배워보기

 

1.1 [REPLACE] - 특정문자를 다른 문자로 변경

바뀐 상점 이름, 지역 이름 한 번에 SQL 로 바꿀 수 있음

 

 

예시1. 식당 명의 ‘Blue Ribbon’ 을 ‘Pink Ribbon’ 으로 바꾸기

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


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

 

 

 

예시2. 주소의 ‘문곡리’ 를 ‘문가리’ 로 바꾸기

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


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

 

 

---

 

1-2. [SUBSTRING(substr)] - 원하는 문자만 추출

전체 데이터가 아닌 특정 문자만 필요할 때, SQL 로 필요한 부분만 조회할 수 있음

 

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

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




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

 

---

 

1-3 [CONCAT] - 여러 컬럼의 문자를 합하여 포맷팅하기

원하는 문자가 여러 컬럼에 있을 때, 하나로 합쳐서 업무에 필요한 형태로 만들 수 있음

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

 

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

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




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

 

 

---

 

 

2. 문자 데이터를 바꾸고 GROUP BY 사용하여 연습하기

 

문제1. 서울 지역의 음식 타입별 평균 음식 주문금액 구하기 (출력 : ‘서울’, ‘타입’, ‘평균 금액’)

• Query 를 적기 전에 흐름을 정리해보기 
  1. 어떤 테이블에서 데이터를 뽑을 것인가 → 주문 테이블
  2. 어떤 컬럼을 이용할 것인가 → 주문 금액, 음식 타입, 주소
  3. 어떤 조건을 지정해야 하는가 → 서울 지역
  4. 어떤 함수 (수식) 을 이용해야 하는가 → 평균 구하는 수식, 특정 문자만 뽑는 기능
• 구문으로 만들기 
  1. 어떤 테이블에서 데이터를 뽑을 것인가 → from food_orders
  2. 어떤 컬럼을 이용할 것인가 → price, cuisine_type, addr
  3. 어떤 조건을 지정해야 하는가 → where addr like ‘%서울%’
  4. 어떤 함수 (수식) 을 이용해야 하는가 → avg(price), substring(addr, 1, 2)

select substring(addr, 1, 2) "시도",
       cuisine_type "음식 종류",
       avg(price) "평균 금액"
from food_orders
where addr like '%서울%'
group by 1, 2

 

 

 

 

문제2. 이메일 도메인별 고객 수와 평균 연령 구하기

 

• Query 를 적기 전에 흐름을 정리해보기
  1. 어떤 테이블에서 데이터를 뽑을 것인가 → 고객 테이블
  2. 어떤 컬럼을 이용할 것인가 → 이메일 주소, 나이, 고객번호
  3. 어떤 조건을 지정해야 하는가 → 없음
  4. 어떤 함수 (수식) 을 이용해야 하는가 → 특정 문자만 뽑아내는 수식, 평균 구하는 수식, 갯수 구하는 수식
• 구문으로 만들기 
  1. 어떤 테이블에서 데이터를 뽑을 것인가 → from customers
  2. 어떤 컬럼을 이용할 것인가 → email, age, customer_id
  3. 어떤 조건을 지정해야 하는가 → X
  4. 어떤 함수 (수식) 을 이용해야 하는가 → avg(age), count(customer_id) or count(1), substring(email, 10)

select substring(email, 10) "도메인",
       count(customer_id) "고객 수",
       avg(age) "평균 연령"
from customers
group by 1

 

 

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

 

• Query 를 적기 전에 흐름을 정리해보기 
  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_type, ')') "바뀐이름",
       count(1) "주문건수"
from food_orders
group by 1