MY_SQL 많이 쓰는 구문 정리 - 1) 날짜 / 시간 정보 / IF / CASE문 / IFNULL()

* MY SQL 사용 시 빈번하게 사용하는 구문을 정리함.

( 해당 코드 진행은 : https://www.w3schools.com/mysql/trymysql.asp?filename=trysql_select_all 링크에서 사용함)

해당 내용은 유튜브 https://www.youtube.com/channel/UC2nkWbaJt1KQDi2r2XclzTQ 보면서 공부한 내용을 바탕으로 정리함)

 

1) 날짜를 다룰 때 많이 사용하는 구문

1-1) 특정 DATE 형식에서 YEAR, MONTH,DAY, WEEKDAY 가져오기

SELECT BirthDate,
YEAR(BirthDate),
MONTH(BirthDate),
DAY(BirthDate),
WEEKDAY(BirthDate)
FROM Employees;

위의 값처럼 SQL 내에 있는 YEAR / MONTH /DAY / WEEKDAY (월요일:0) 을 사용해서 가져오는 방법이 있으며

문자열을 통해 가져오는 방법도 있음.

SELECT BirthDate,
SUBSTR(BirthDate,1,4) AS STR_YEAR,
SUBSTR(BirthDate,6,2) AS STR_MONTH,
SUBSTR(BirthDate,-2,2) AS STR_DAY,
LEFT(BirthDate,4) AS LEFT_YEAR,
RIGHT(BirthDate,2) AS RIGHT_DAY
FROM Employees;

 

 

 

1-2) 날짜 형태를 임의로 CONCAT_WS하여 만들기

-> CONCAT_WS("합칠기호", 합칠내용1, 합칠내용2) 를 합쳐주며 이를 이용하여 임의의 날짜 형태로 만들수 있음.

SELECT BirthDate,
CONCAT_WS("-",SUBSTR(BirthDate,1,4),SUBSTR(BirthDate,6,2),SUBSTR(BirthDate,-2,2)) AS CONCAT_WS_DATE
FROM Employees;

 

 

1-2) 문자열 -> DATE 형식으로 형식변환

->  STR_TO_DATE("STR형식의 날짜", "변환할포맷") , "%Y-%m-%d %H" 로 변환함.

SELECT BirthDate,
STR_TO_DATE(CONCAT_WS("-",SUBSTR(BirthDate,1,4),SUBSTR(BirthDate,6,2),SUBSTR(BirthDate,-2,2)), "%Y-%m-%d %H") AS STR_TO_DATE
FROM Employees;

 

 

1-3) 변환된 문자열 -> DATE 형식을 -> DATE_FORMAT을 사용하여 형식 변경

->  DATE_FORMAT("DATE 형식의 날짜 / STR형식의 날짜", "변환할포맷") , "%Y-%m-%d" 로 변환함.

SELECT BirthDate,
STR_TO_DATE(CONCAT_WS("-",SUBSTR(BirthDate,1,4),SUBSTR(BirthDate,6,2),SUBSTR(BirthDate,-2,2)), "%Y-%m-%d %H") AS STR_TO_DATE,
DATE_FORMAT(STR_TO_DATE(CONCAT_WS("-",SUBSTR(BirthDate,1,4),SUBSTR(BirthDate,6,2),SUBSTR(BirthDate,-2,2)), "%Y-%m-%d %H"), "%Y-%m-%d") AS DATE_FORMAT_DATE,
DATE_FORMAT(CONCAT_WS("-",SUBSTR(BirthDate,1,4),SUBSTR(BirthDate,6,2),SUBSTR(BirthDate,-2,2)), "%Y-%m-%d") AS DATE_FORMAT_STR
FROM Employees;

 

 

1-4) ADDDATE를 사용하여 DATE에 일정값이상의 정보를 더하기

->  ADDDATE("DATE형식 날짜", INTERVAL 1 YEAR, 1 MONTH, 1 WEEK, 1 DAY, 1 HOUR, 1,MINUTE, 1 SECOND) 다 가능함

SELECT BirthDate,
STR_TO_DATE(CONCAT_WS("-",SUBSTR(BirthDate,1,4),SUBSTR(BirthDate,6,2),SUBSTR(BirthDate,-2,2)), "%Y-%m-%d %H") AS STR_TO_DATE,
DATE_FORMAT(STR_TO_DATE(CONCAT_WS("-",SUBSTR(BirthDate,1,4),SUBSTR(BirthDate,6,2),SUBSTR(BirthDate,-2,2)), "%Y-%m-%d %H"), "%Y-%m-%d") AS DATE_FORMAT_DATE,
DATE_FORMAT(CONCAT_WS("-",SUBSTR(BirthDate,1,4),SUBSTR(BirthDate,6,2),SUBSTR(BirthDate,-2,2)), "%Y-%m-%d") AS DATE_FORMAT_STR,
ADDDATE(DATE_FORMAT(STR_TO_DATE(CONCAT_WS("-",SUBSTR(BirthDate,1,4),SUBSTR(BirthDate,6,2),SUBSTR(BirthDate,-2,2)), "%Y-%m-%d %H"), "%Y-%m-%d"),
INTERVAL +9 HOUR) AS ADD_DATE_DATE,
ADDDATE(DATE_FORMAT(CONCAT_WS("-",SUBSTR(BirthDate,1,4),SUBSTR(BirthDate,6,2),SUBSTR(BirthDate,-2,2)), "%Y-%m-%d"), INTERVAL +9 HOUR) AS ADD_DATE_STR
FROM Employees;

위에서 DATE_FORMAT의 함수를 사용하는 수간 STR_TO_DATE를 안해줘도 DATE 형식으로 변경이 되는 것 같음.

CONCAT_WS를 수행하고 DATE_FORMAT으로 변경하고 바로 ADDDATE를 사용해주면 동작한

*순서 : SUBSTR() , CONCAT_WS(), DATE_FORMAT(), ADDDATE()

 

 

1-5) 시간차 구하기 (일수차 : DATE_DIFF, 시간차 : TIME_DIFF)

-- DATE_FORMAT / DATE_DIFF / TIME_DIFF 사용
SELECT BirthDate,
LAST_DAY(BirthDate),
DATEDIFF(LAST_DAY(BirthDate),BirthDate) AS DATE_DIFF_LASTDAY,
DATE_FORMAT(BirthDate,"%Y-%m-%d %H:00:00") AS UTC_DATE_FORMAT,
ADDDATE(DATE_FORMAT(BirthDate,"%Y-%m-%d %H:00:00"),INTERVAL +9 HOUR) AS KST_DATE_FORMAT,
TIMEDIFF(ADDDATE(DATE_FORMAT(BirthDate,"%Y-%m-%d %H:00:00"),INTERVAL +9 HOUR),DATE_FORMAT(BirthDate,"%Y-%m-%d %H:00:00")) AS TIME_DIFF_KST_UTC
FROM Employees;

ADDDATE(DATE_FORMAT(BirthDate,"%Y-%m-%d %H:%m:00"),INTERVAL +9 MINUTE) AS KST_DATE_FORMAT,

-- 분까지 나타낼때에는 아래와 같이 %m을 사용한다 --> PANDAS에서는 %Y-%m-%d %H-%M-%S
-- %Y-%m-%d %H:%m:00
-- %Y-%m-%d %H:00:00

import datetime

cur_time = datetime.datetime.now()
cur_time_str = cur_time.strftime("%Y-%m-%d %H:%M:%S")
print(cur_time,"\n",cur_time_str)

 

 

 

2) IF 문 

-- IF문 기본
SELECT IF(5>4, "참일 때 실행문장", "거짓일 때 실행문장")

 

--> 위와 같이 기본적으로 다른 언어들에서 쓰는 것처럼 참일 때/ 거짓일 때 값을 가져오게 나타나 있음.

* 예시

-- Category tabale에서 description 내에 coffees, Breads가 들었으면 good 아니면 bad로 입력함
SELECT Description,
IF((Description LIKE "%coffees%") or (Description LIKE "%Breads%"),"GOOD","BAD") AS IF_CONDITION
FROM Categories

 

조건문으로 Description 칼럼에 LIKE를 사용하여 해당하는 문자열이 있는지를 확인함.

-- Category tabale에서 description 내에 coffees, Breads가 들었으면 good 아니면 bad로 입력함
SELECT Description,
IF((Description LIKE "%coffees%") or (Description LIKE "%Breads%"), Description,"BAD FOOD") AS IF_CONDITION
FROM Categories;

위와 같이 원래 있는 값을 RETURN 하는 것도 가능함.

 

 

3) SWITHCH 문

-- SWITCH문 기본 형태 , 이 때 여러개의 CASE가 참일 때에는 가장위에 있는 CASE를 먼저 RETURN 한다
SELECT
CASE
  WHEN 3 < 0 THEN '1번의 조건'
  WHEN -1 != 0 THEN '2번의 조건'
  ELSE 'ELSE의 조건'
END;

 

* 예시

SELECT Description,
CASE 
 WHEN (Description LIKE "%Soft%") or (Description LIKE "%teas%") THEN Description 
 ELSE "BAD FOOD"
END
FROM Categories;

 

위에서 CASE의 조건이 여러가지가 참일 경우에는 가장 먼저 CASE에 걸리는 부분을 RETURN 한다.

 

 

4) IFNULL 구문

-> IFNULL("NULL이 아닐때 RETURN" , "NULL 일때 RETURN" )  : 일종의 NULL인 상황의 보안이라고 생각

 

** 위 총정리 에시

-- Employees 에서 BirthDate를 last_day 기준으로 하여 그달의 말일 까지 
-- DATE_DIFF가 5일 이내인 사람에는 "SOON" , 10일 이내의 사람에게는 "LONG", 그외 사람한테는 NULL을 보내고, NULL일 경우는 ISNULL을 사용하여 
-- "NULL TEST"로 작성함


SELECT BirthDate, LAST_DAY(BirthDate), DATEDIFF(LAST_DAY(BirthDate),BirthDate) AS DATE_DIFF,
CASE
 WHEN DATEDIFF(LAST_DAY(BirthDate),BirthDate) <= 5 THEN 'SOON'
 WHEN DATEDIFF(LAST_DAY(BirthDate),BirthDate) <= 10 THEN 'LONG'
 ELSE NULL
END AS RESULT,
IFNULL(
CASE
 WHEN DATEDIFF(LAST_DAY(BirthDate),BirthDate) <=5 THEN "SOON"
 WHEN DATEDIFF(LAST_DAY(BirthDate),BirthDate) <=10 THEN "LONG"
 ELSE NULL
END, 
"NULL TEST"
) AS RESULT_NULL_TEST
FROM Employees;

 

위의 코드를 실행하면 REULST의 경우는 NULL값을 다른 문자열로 처리를 하지않고 있으며,

IFNULL을 사용하면 NULL인 경우에도 백업으로 RETURN할 값을 지정할 수 있음.