데이터베이스와 SQL

🫵🏻 데이터 베이스 : 데이터가 저장되어 있는 큰 폴더
SQL : SQL은 '코드'가 아니라 데이터베이스에 요청을 하기 위한 '언어'
SQL 이용하여 데이터베이스에 요청하는 질의를 QUERY라고 함

---

데이터 조회

• SELECT : 데이터를 가져오는 기본 명령어
• FROM : 데이터를 가져올 테이블을 특정해주는 문법
• '*' : 모든 컬럼을 가져와준다는 의미

---

컬럼 명 바꾸기 (별명주기)

• 방법 1 : 컬럼1 as 별명1
• 방법 2 : 컬럼2 별명2

  🌸 영문, 언더바는 별명만 적어도 되지만, 특수문자 및 한글은 큰 따옴표 안에 별명 기재해야함 🌸

---

조건에 맞게 데이터 필터링

• WHERE : 필터링 조건 제시
• 비교 연산 ( = , <>, >, >=, < , <=)
• BETWEEN : A 이상 B 이하
• IN : 포함하는 조건
• LIKE : 완전히 똑같지는 않지만, 비슷한 값 조건으로 주기

🍀 IF ) 조건이 여러 개? -> 논리연산(and, or, not)으로 연결시키기!

---

엑셀 대신 SQL로 한번에 계산하기

• 숫자 연산 (덧셈+, 뺄셈-, 곱셈*, 나눗셈 / 모두 사용 가능)
• 합계 : SUM
• 평균 : AVG
• 행 개수 세기 : COUNT(컬럼) (컬럼명 대신 1, * 사용 가능)
• 최댓값 : MAX
• 최솟값 : MIN

  👅 IF ) 범주 별로 계산하고 싶다면? -> GROUP BY 이용!
  COUNT는 유의미한 계산으로 이용할려면 GROUP BY 필수적으로 필요함!

---

QUERY 결과 정렬하기

• 오름차순 : ORDER BY 컬럼명 ASC (ORDER BY의 기본형이 오름차순이라 ASC 생략 가능)
• 내림차순 : ORDER BY 컬럼명 DESC

---

SQL 작성 순서

select 
from 
where 
group by  
having 
order by

✋🏻 HAVING은 그룹 별로 연산한 다음 조건을 주고 싶을 때 사용

---

데이터 가공

• REPLACE(바꿀 컬럼, 현재 값, 바꿀 값) : 다른 값으로 바꾸어 버리기
• SUBSTRING(조회 할 컬럼, 시작 위치, 글자 수) : 원하는 문자만 남기고 자르기
• CONCAT(붙이고 싶은 값1, 붙이고 싶은 값2, 붙이고 싶은 값3) : 여러 컬럼의 문자 합치기

---

조건에 따라 다른 연산 하는법

• if(조건, 조건을 충족할 때, 조건을 충족하지 않을 때) : 단일 조건을 지정할 때
• case when 조건1 then 값(수식)1
  when 조건2 then 값(수식)2
  else 값(수식)3
  end : 두개 이상의 조건을 지정할 때

---

DATA TYPE 오류 해결 방법

👋🏻 cast 함수 이용

// 숫자로 변경
cast(if(rating = 'Not given', '1', rating) as decimal)

// 문자로 변경
concat(restaurant_name, '-', cast(order_id as char))

---

SUBQUERY

🤭 QUERY 안에 QUERY
여러 번의 연산을 수행해야할 때
조건문에 연산 결과를 사용해야 할 때
조건에 QUERY 결과를 사용하고 싶을 때
사용하면 유용함
작동 순서 : 안쪽 쿼리 -> 바깥쪽 쿼리

SUBQUERY 종류
- 중첩(일반) 서브쿼리 - WHERE 절에서 사용
- 스칼라 서브쿼리 - SELECT 절에서 사용, 하나의 컬럼처럼 사용
- 인라인 뷰(가장 많이 사용) - FROM 절에서 사용, 하나의 테이블처럼 사용, 명칭을 반드시 기재

---

서로 다른 테이블 붙이기(UNION, JOIN)

• UNION 함수 : 테이블 수직결합
  <기본 구조>

  # union/union all 기본구조
  # 컬럼 순서가 같고, 그 형식이 같아야 함
  select 컬럼1, 컬럼2, 컬럼3.. 
  from 테이블명1
  union (all) #수직결합 명시
  select 컬럼1, 컬럼2, 컬럼3..
  from 테이블명2 

  

• JOIN : 테이블 수평결합
  
  

• SELECT *
  FROM 테이블1 a LEFT JOIN 테이블2 b ON a.공통 컬럼 = b.공통 컬럼
  -> 공통 컬럼을 기준으로 하나의 테이블에 값이 없더라도 모두 조회

• SELECT *
  FROM 테이블1 a INNER JOIN 테이블2 b ON a.공통 컬럼 = b.공통 컬럼
  -> 공통 컬럼을 기준으로 두 테이블 모두에 있는 값만 조회

  🫡 공통 컬럼은 '공통 값'이기 때문에 두 테이블의 컬럼명은 달라도 괜찮음!
  공통컬럼이 없다면, 조인을 할 수 없다는 점을 기억!
  공통컬럼은 꼭 하나가 아닐 수 있음 -> 여러 개도 가능
  아래 ERD, MAPPING CARDINALITY로 확인

---

ERD & Mapping Cardinality

---

결측값 처리하기

• 없는 값 제외해주기
  if (rating<> 'Not given', rating, null)
• 다른 값 대신 사용하기
  1) 다른 값이 있을 때 조건문 이용 -> if (rating<> 'Not given', rating, 대체값)
  2) null 값일 때 -> coalesce(age, 대체값)

---

이상치 처리하기

like 2세, 150세
-> 조건문으로 상식적인 수준안에서 범위로 지정해버리기

---

PIVOT TABLE

• PIVOT TABLE : 2개 이상의 기준으로 데이터를 집계할 때, 보기 쉽게 배열하는 것
  <예시>
  음식점별, 시간별, 주문건 수 PIVOT TABLE 만들기
  여기서 max함수는 일단 이해하지 말고 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

---

WINDOW FUCTION

• 기본 구조
  window_function(argument) over (partition by 그룹 기준 컬럼 order by 정렬 기준)

  🫥 window_function과 over은 짝꿍!

<RANK 함수 예시>
음식 타입별로 주문 건수가 가장 많은 상점 3개씩 조회하기

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,
       cnt_order,
       sum(cnt_order) over (partition by cuisine_type) sum_cuisine,
       sum(cnt_order) over (partition by cuisine_type order by cnt_order) 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

---

날짜 포맷 & 조건

• yyyy-mm-dd 형식의 컬럼을 date type 으로 변경하기
  -> date(컬럼명) 사용
• date type을 년, 월, 일, 주 로 조회해보기
  ->

date_format(date(date), '%Y') "년", // 년도 조회 
date_format(date(date), '%m') "월", // 월 조회
date_format(date(date), '%d') "일", // 일 조회
date_format(date(date), '%w') "요일" // 요일 조회

---

REGEXP(정규식)

• REGEXP는 LIKE를 이용한 검색과 달리 Regular Expression(정규 표현식)를 이용해 검색함
  -> REGEXP를 사용하면 SQL에서 정규표현식을 활용하여 기본 연산자보다 복잡한 문자열 조건을 걸어 데이터를 검색 가능
  
  
  출처 : https://dadev.tistory.com/entry/MY-SQL-정규식-REGEXP