SQL 기초2

• replace 함수 : replace(바꿀 컬럼, 현재 값, 바꿀 값).

select restaurant_name "원래 상점명",

           replace(restaurant_name, 'Blue', 'Pink') "바뀐 상점명"

from food_orders

where restaurant_name like '%Blue Ribbon%'

->

restaurant_name을 가져오며  "원래 상점명"이라고 별명 주기.
restaurant_name 칼럼에서  'Blue'를 'Pink'로 바꾸고 그 데이터를  "바뀐 상점명"이라고 별명 주기.

food_orders에서 데이터를 가져오기

데이터에  'Blue Ribbon'이 포함된 조건에서만 필터링하여 가져오기 

 

 

 

• substing 함수 : substr(조회 할 컬럼, 시작 위치, 글자 수). 글자 수를 적지 않으면 마지막까지 전부 불러온다.
  substr(addr, 1, 2) "특정 문자"     ->   addr 칼럼에서 1번째 문자부터 2개의 문자를 가져온다. ex) addr: 서울특별시 -> 특정 문자: 서울, addr: 제주도 -> 특정 문자: 제주
  
  
  
•  
• concat 함수 : concat(붙이고 싶은 값1, 붙이고 싶은 값2, 붙이고 싶은 값3, ...)
  concat('[', substring(addr, 1, 2), '] ', restaurant_name) "바뀐 이름" 
  -> 붙이고 싶은 값은 총 4개이며 순서대로 1. [,  2.  substring(addr, 1, 2),  3. ],  4.  restaurant_name 
  
  

 

• if 함수 : if(조건, 조건을 충족할 때, 조건을 충족하지 못할 때)
  if(cuisine_type='Korean', '한식', '기타') "음식 타입"
  -> cuisine_type의 값이 'Korean' 일 때, '한식', 그렇지 못하면  '기타' '라고  "음식 타입" 에 불러온다.

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

 

 

• case 함수 : case when 조건1 then 값(수식)1
                              when 조건2 then 값(수식)2
                              else 값(수식)3 end                                       
  
  *조건 1, 2에서 모든 케이스가 부합되면 else 생략 가능

select case when cuisine_type='Korean' then '한식'

                   when cuisine_type in ('Japanese', 'Chinese') then '아시아'

                   else '기타' end "음식 타입",

from food_orders

 

 

 

• 칼럼의 '숫자형' -> '문자형', '문자형' -> '숫자형'  // *문자: char(character) , 숫자: decimal
  -문자로 변경 concat(restaurant_name, '-', cast(order_id as char))
  -숫자로 변경 cast(if(rating='Not given', '1', rating) as decimal)
  
  

 

• Subquery문 : Query 안에 sub 로 들어간 구문
  select column1, special_column
  from (
  select column1, column2 special_column
  from table1
  ) a  
  
  

 

• left join : 
  select 조회 할 컬럼
  from 테이블1 a left join 테이블2 b on 테이블1.공통컬럼명=테이블2.공통컬럼명
  -> 
  테이블1의 모든 데이터 + 공통칼럼명으로 묶인 테이블2의 데이터
  *테이블1에만 데이터값이 존재하고 테이블2에는 데이터값이 없다면, 데이터 값이 없음을 나타내는 [NULL]이 나옴.
  
  

 

• inner join :  
  select 조회 할 컬럼
  from 테이블1 a inner join 테이블2 b on a.공통컬럼명=b.공통컬럼명
  ->
  테이블1에도 존재하고 테이블2에도 존재하는 같은 데이터 값으로(공통컬럼명으로) 묶어 데이터를 가져옴.
  값이 없는 경우는 가져오지 않음.
  테이블1의 별명 a, 테이블2의 별명 b
  *두 테이블의 칼럼명이 달라고 괜찮다. 예를 들어 a.name=b.이름
  
  

 

• NULL : '값이 없음'을 의미. 값이 0이 아니라, 아예 데이터가 존재하지 않는다는 의미다.
  
  if(rating<>'not given', rating, null)             ->  만약 rating이 'not given' 아니라면 rating을 그대로 가져오고,  rating이 'not given'이라면 null을 가져온다.
  
  where customer_id is not null                   ->   customer_id의 데이터가 not null인 것만 즉, null이 아닌 것만 불러온다. 데이터가 있는 애들만 불러온다.
  
  *null 값을 제외하는 것이 아니라 대체값을 넣고 싶을 때,
  coalesce(age, 20) "null 제거"                    ->  age에 있는 null이 20 으로 대체되어 불러오고, 그 범주칼럼의 별명이 "null 제거".
  
  

 

• Window 함수 : Window Function 은 각 행의 관계를 정의하기 위한 함수로 그룹 내의 연산을 쉽게 만들어준다.
                          window_function 과 over는 한 쌍으로 생략 불가.
  기본 구조:
  window_function(argument) over (partition by 그룹 기준 컬럼 order by 정렬 기준)
  • window_function : 기능 명을 사용해줍니다. (sum, avg 와 같이 기능명이 있습니다)
  • argument : 함수에 따라 작성하거나 생략합니다.
  • partition by : 그룹을 나누기 위한 기준입니다. group by 절과 유사하다고 생각해주시면 됩니다.
  • order by : window function 을 적용할 때 정렬 할 컬럼 기준을 적어줍니다.
  rank() over (partition by cuisine_type order by cnt desc)
  -> rank() over : 순위를 구한다. partition by cuisine_type : cuisine_type 별로 구한다. order by cnt desc : cnt의 내림차순(큰 값을 1위)으로 정렬한다.
  
  sum(cnt_order) over (partition by cuisine_type) sum_cuisine
  -> cnt_order 의 합계를 구한다,  cuisine_type 별로. 음식종류별 주문 수의 총합이 나옴.
  
  sum(cnt_order) over (partition by cuisine_type order by cnt_order, restaurant_name) cum_cuisine
  -> cnt_order 의 합계를 구한다,  cuisine_type 별로,  cnt_order, restaurant_name로 정렬한다.
       음식종류별 주문 수의 누적합이 정렬되어 나옴.

 

select cuisine_type,

restaurant_name,

cnt_order,

sum(cnt_order) over (partition by cuisine_type) sum_cuisine,

sum(cnt_order) over (partition by cuisine_type order by cnt_order, restaurant_name) cum_cuisine

from

(

select cuisine_type,

restaurant_name,

count(1) cnt_order

from food_orders

group by 1, 2

) a

order by cuisine_type , cnt_order, cum_cuisine

• date 함수 : date(칼럼명) "별명" -> 칼럼명의 데이터를 날짜 형식으로 바꾼다.
                     date_format(칼럼명, '가져올 값') "별명" -> 칼럼명의 날짜 형식의 데이터 칼럼을 
  date_format(date(dt), '%Y') "년" -> date(dt) : dt칼럼을 date함수를 써서 날짜 형식으로 바꿈. date_format(date(dt), '%Y') : 연도만 남기고 가져온다.
  
  

  select date(date) date_type,

  date_format(date(date), '%Y') "년",

  date_format(date(date), '%m') "월",

  date_format(date(date), '%d') "일",

  date_format(date(date), '%w') "요일"
  date_format(date(date), '%y%m') "년월"
  

  from payments
  
  요일 데이터값 표시: 일요일은 0, 월요일은 1... 토요일은 6
  
  + 나는 24, 23 처럼 뒤의 두 숫자만 나왔는데 2024, 2023 처럼 4자리 모두 나오게 하는 방법은 뭘까?
  
  

  %Y , %y  전자는 2024처럼 4자리, 후자는 24처럼 2자리
  %M%m 전자는 March처럼 영문, 후자는 08처럼 숫자 등