[TIL] SQL_5일차(replace,substr,concat,if/case)

이전 시간까지는 정렬과 조회에 대해 공부했는데요. 이번 시간부터는 SQL 데이터를 임의로 수정하는 방법에 대해 공부합니다.

✍🏻배운내용📚

1. 특정 문자를 다른 문자로 바꾸기 replace(바꿀 컬럼, 현재 값, 바꿀 값)
2. 특정 문자만 골라서 조회 substr(조회 할 컬럼, 시작 위치, 글자 수)

3. 여러 컬럼의 값을 하나로 합치기 concat(붙이고 싶은 값1, 값2, 값3, .....)

4. 조건에 따라 다른 연산을 하는 방법 IF & CASE

---

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

특정 문자를 다른 것으로 바꿀 수 있는 기능 : replace(바꿀 컬럼, 현재 값, 바꿀 값)

→ 문자라면 '따옴표' 붙이기

[실습]  

1. 최근에 상점 이름이 바뀌었지만 과거 데이터에는 옛날 이름으로 저장되어있어요.

식당 명의 ‘Blue Ribbon’ 을 ‘Pink Ribbon’ 으로 바꾸기.  replace(바꿀 컬럼, 현재 값, 바꿀 값)

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

 

1-2. 예전에 ‘문곡리’ 라는 지명이 ‘문가리’ 로 바뀌었어요. replace(바꿀 컬럼, 현재 값, 바꿀 값)

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

 

 

 

2. 특정 문자만 골라서 조회

특정 문자만 골라서 조회할 수 있는 기능: substr(조회 할 컬럼, 시작 위치, 글자 수)

 [substring = substr]

→ substr(조회 할 컬럼, 시작 위치, 글자 수) 여기에서 글자수를 정확히 모르고 마지막까지 조회하고 싶으면 글자 수를 안 쓰면 된다.

[실습]  

2-1. 서울 음식점들의 주소를 전체가 아닌 ‘시도’ 만 나오도록 수정.  substr(조회 할 컬럼, 시작 위치, 글자 수)

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

2-2. 서울 음식점들의 주소를 전체가 아닌 ‘시'까지만 나오도록 수정.  substr(조회 할 컬럼, 시작 위치, 글자 수)

select addr "원래 주소", substr(addr, 1, 5) "'~시'까지" from food_orders where addr like '%서울특별시%'

 

 

3. 여러 컬럼의 값을 하나로 합치기

여러 컬럼의 값을 하나로 합칠 수 있는 기능 : concat(붙이고 싶은 값1, 값2, 값3, .....)

→ 문자라면 '따옴표' 붙이기

[실습]  

3-1.서울시에 있는 음식점은 ‘[서울] 음식점명’ 이라고 수정. concat(붙이고 싶은 값1, 값2, 값3, .....)

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

3-2. 음식타입별 음식점 이름으로 수정 . concat(붙이고 싶은 값1, 값2, 값3, .....)

select restaurant_name "원래 이름", concat(restaurant_name,' : ',cuisine_type) "바꿘 이름" from food_orders

 

 

[ 실습 응용 ] 문자 데이터를 바꾸고, GROUP BY 사용하기

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

select substr(addr, 1, 2) "시도", cuisine_type "타입", avg(price) "평균 금액" from food_orders where addr like '%서울%' group by cuisine_typ

select substr(addr, 1, 2) "시도", cuisine_type "타입", avg(price) "평균 금액" from food_orders where addr like '%서울%' group by 1,2

 

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

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

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

 

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

select concat('[',substr(addr, 1, 2),']',restaurant_name,' (', cuisine_type, ')') "음식점", count(order_id) "주문간수" from food_orders group by 1

 

 

 

4. 조건에 따라 다른 연산을 하는 방법  IF & CASE

1) 조건에 따라 다른 방법을 적용하고 싶을 때 : If 문 기초

IF 문은 원하는 조건에 충족할 때 적용할 방법과 아닌 방법을 지정해 줄 수 있습니다.

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

[실습] 

1-1) 음식 타입을 ‘Korean’ 일 때는 ‘한식’, ‘Korean’ 이 아닌 경우에는 ‘기타’ 라고 지정하고 싶어요

select cuisine_type, if(cuisine_type='Korean', '한식','기타') from food_orders

 

 

1-2) ‘문곡리’ 가 평택에만 해당될 때, 평택 ‘문곡리’ 만 ‘문가리’ 로 수정

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

 

 

 

1-3) 실습 응용문제 2번에서 잘못된 이메일 주소 (gmail) 만 수정을 해서 사용. substr(조회 할 컬럼, 시작 위치, 글자 수)

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

 

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

조건별로 지정을 해주기 때문에 아래와 같이 if 문을 여러번 쓴 효과를 낼 수 있습니다.

if(조건1, 값1, if(조건2, 값2, 값3))

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

[실습] 

2-1)  음식 타입을 ‘Korean’ 일 때는 ‘한식’, ‘Japanese’ 혹은 ‘Chienese’ 일 때는 ‘아시아’, 그 외에는 ‘기타’ 라고 지정

 

select case when cuisine_type = 'Korean' then '한식'            when cuisine_type in ('Japanese', 'Chienese') then '아시아'            else '기타' end "음식타입", cuisine_type from food_orders

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

 

2-2) 음식 단가를 주문 수량이 1일 때는 음식 가격, 주문 수량이 2개 이상일 때는 음식가격/주문수량 으로 지정

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

 

2-3) 주소의 시도가 ‘경기도’ 일 때는 ‘경기도’, ‘특별시’ 혹은 ‘광역시’ 일 때는 붙여서(5글자까지), 아닐 때는 앞의 두 글자만 사용

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

 

 

 

---

💡[ Tip ] SQL생각 흐름 🧠 (수정할 수 있습니다.)

1. 기본구조를 적기 : select  from  where

2. 어떤 데이터를 가져올 테이블을 적기 : from 테이블

 → 만약 조건이 잘 파악이 안 되면 (전체) 테이블만 우선 실행하여 결과를 보고 조건 찾기

3. 어떤 컬럼을 이용할 것인지 선택 : select 컬럼

4. 어떤 조건을 지정할 지 적기 : where 조건, group by / order by

5. 어떤 함수(수식) 을 이용해야 하는가 → 갯수 구하는 수식

---

ADsP 시험 정보가 궁금하신 분들은 여기를 클릭 💨

이전 내용이 궁금하다면 여기를 클릭 💨

🌈 오늘 하루도 열심히 보낸 모두 좋은 하루 되세요🌆