[TIL] Week 1-3 SQL MOD(), REGEXP(), LEFT(), RIGHT(), UNION / UNION ALL, LENGTH(), JOIN, IS NULL, SUBSTR(), CONCAT(), REPLACE(), CASE, IF, DATE()

 

셋째날 시작!!

어제 늦게 잤더니 일어나는데 졸렸다. 눈뜨자마자 해야하는게 좀 빡세긴 한 것 같지만, 시간을 허투루 쓰지 않게 돼서 좋은 것 같다.

오늘은 해커랭크라는 사이트에서 SQL basic select 문제를 풀어봤다. 어제 solvesql의 select 문제를 다 풀어버렸기 때문이다. 아직까진 재밌게 하고 있다. 부디 더 어려운 문법도 즐겁게 공부할 수 있었으면 좋겠다.

 

원유선 튜터님의 직무 세션도 수강했다. 의료, 금융 쪽에서 데이터 사이언스, 데이터 엔지니어 경력이 있으시다.

의료는 생각지 못한 분야인데, 웨어러블 및 IoT 기기에 활용되는 데이터를 분석하는 것이 재밌을 것 같단 생각이 들었다. 캠프를 더 진행하면서 여러 산업 분야를 생각해보고 정해야겠다. 또한 직접 발품팔며 동종업계 사람들에게 컨택하는 것의 중요성을 강조하셨다. 컨퍼런스를 찾아보고, 만약 캠프가 끝나게 되면 스터디도 찾아봐야겠다. gpt도 초보단계에선 안 쓰는게 좋다고 한다! 

현재는 튜터님들과 자주 컨택하는게 좋을 것 같다.

 

---

[목차]
1. MOD(나눌 컬럼, 나눌 수)
2. REGEXP() (MYSQL, ORACLE)
3. LEFT(), RIGHT()
4. UNION / UNION ALL
5. LENGTH(컬럼명)
6. JOIN, ON
7. IS NULL, IS NOT NULL
8. 문자열 조작 함수
9. CASE, IF

---

 

1. MOD(나눌 컬럼, 나눌 수)

나누고 싶은 컬럼/나눌 수의 나머지를 반환해주는 함수.

SELECT DISTINCT CITY
FROM STATION
WHERE MOD(ID,2)=0; -- ID/2의 나머지=0

 

2. REGEXP() (MYSQL, ORACLE)

LIKE 조건이 여러 개이면 OR나 AND로 코드를 작성해줘야한다. 하지만 조건이 아주 많을 경우엔 비효율적이다.

이처럼, 이전에 배웠던 LIKE가 여러 개 필요할 때, 오라클과 MYSQL에서는 REGEXP 함수를 활용하여 효율적으로 코드를 작성할 수 있다.

SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME REGEXP 'Lucy|Ella|Pickle|Rogan|Sabrina|Mitty' --OR 조건일 때 |를 사용.
ORDER BY ANIMAL_ID

 

3. LEFT(), RIGHT()

• LEFT(컬럼명, n) : 원하는 컬럼에서 왼쪽에서 n번째 자리에 해당하는 글자를 가져온다.
• RIGHT(컬럼명, n) : 원하는 컬럼에서 오른쪽에서 n번째 자리에 해당하는 글자를 가져온다.

SELECT DISTINCT CITY
FROM STATION
WHERE (LEFT(CITY,1) IN ('a','e','i','o','u'))
AND (RIGHT(CITY,1) IN ('a','e','i','o','u'));

 

4. UNION / UNION ALL

• UNION : 여러 쿼리문들을 합쳐서 하나의 쿼리문으로 만들어준다.
  중복 제거 (이 연산 때문에 속도가 UNION ALL보다 느리다.)
• UNION ALL : 여러 쿼리문들을 합쳐서 하나의 쿼리문으로 만들어준다. 중복을 포함한다.

(SELECT CITY, LENGTH(CITY)
FROM STATION
ORDER BY LENGTH(CITY) ASC, CITY
LIMIT 1)
UNION -- 위 아래 쿼리를 합쳐서 중복 제거하고 결과 반환
(SELECT CITY, LENGTH(CITY)
FROM STATION
ORDER BY LENGTH(CITY) DESC, CITY
LIMIT 1);

 

5. LENGTH(컬럼명)

해당 컬럼의 데이터의 길이를 반환.

 

 

6. JOIN, ON

• JOIN : 여러 테이블을 연결해서 합쳐서 볼 수 있음
• LEFT JOIN : 왼쪽 테이블로 연결! 따라서 왼쪽 테이블의 모든 행을 결과에 포함. 연결 조건에 맞는 오른쪽 테이블 행이 없으면 NULL로 표시.
• INNER JOIN : 양쪽 모두 연결 조건에 부합하는 행만 포함.
• ON : 두 테이블을 어떤 조건으로 연결할 지 지정하는 절.

--구글 Bigquery 코드
SELECT T1.name, T1.email, T2.employee_id
FROM `intro_to_sql.employee` as T1
LEFT JOIN `intro_to_sql.mbtitest` as T2
ON T1.employee_id = T2.employee_id
WHERE T2.mbti IS NULL

 

7. IS NULL, IS NOT NULL

• IS NULL : 값이 NULL인지 확인. NULL은 특수값이기 때문에 '=' 을 쓰지 않음!
  NULL이면 TRUE, 아니면 FALSE 반환
• IS NOT NULL : 값이 NULL이 아님을 확인. NULL은 특수값이기 때문에 '!=' 을 쓰지 않음!
  NOT NULL이면 TRUE, 아니면 FALSE 반환

 

8. 문자열 조작 함수

• REPLACE(컬럼명, 바꿀 문자열, 바뀔 문자열)

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

• SUBSTRING() 또는 SUBSTR(컬럼명, 시작 위치, 자를 문자 개수) : 문자열을 자를 수 있는 함수이다.
  자를 대상인 컬럼명, 몇번째 자리에서 시작할지, 몇 개를 자를지 인자를 넣어주면 된다. 
  세번째 인자를 넣지 않으면 마지막까지 세어준다.

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

• CONCAT() : 문자열을 이어붙여주는 함수.
  괄호 안에 이어붙이고 싶은 컬럼명, 문자열을 넣어 [,] 콤마로 이어준다.
  문자열은 ' '로 감싼다.

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

 

 

9. CASE, IF

사용하는 경우

1. 새로운 카테고리를 만들 때

2. 연산식을 적용할 조건 지정하기

3. 다른 구문, 문법 안에서 적용하기

• IF(조건문, TRUE일 때 값, FALSE일 때 값)

select restaurant_name,
       cuisine_type "원래 음식 타입",
       if(cuisine_type='Korean', '한식', '기타') "음식 타입" -- 조건이 TRUE=한식, FALSE=기타
from food_orders

 

• CASE

[구조]

CASE WHEN 조건문 THEN 결과값

           WHEN 조건문 THEN 결과값

           ELSE 결과값 (그 외 조건 또는 경우일 때 결과값. 다른 조건이 없다면 생략 가능) END

 

시작은 CASE, 끝은 END

결과에 NULL은 불가함.

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

 

 

10. DATE()

DATETIME 데이터 타입에서 날짜만 추출하고 싶은 경우 사용.

DATE(컬럼명) -> YYYY-MM-DD HH:MM:SS 에서 YYYY-MM-DD만 남음.

 

SELECT DATE(MIN(order_purchase_timestamp)) "first_order_date",
       DATE(MAX(order_purchase_timestamp)) "last_order_date"
FROM olist_orders_dataset

 

 

 

[내일 공부할 내용]
JOIN, SUBQUERY, PIVOT TABLE
4일치 복습

---

 

참고 링크

https://velog.io/@ljs7463/MySQL-%ED%99%80%EC%88%98-%EC%A7%9D%EC%88%98-%EC%B6%9C%EB%A0%A5

[MySQL] 홀수, 짝수 출력

 

https://developevolvify.tistory.com/412

[해커랭크Easy] (2024) Weather Observation Station 5, 기상 관측소 5(설명/코드/정답)

 

https://silverji.tistory.com/49

UNION 과 UNION ALL 이란

https://joke00.tistory.com/103

[ORACLE] CASE WHEN ~ THEN ~ ELSE END

https://adjh54.tistory.com/277

[DB/Short] MySQL: DATETIME ↔ DATE 타입 간의 변환 방법