[Mysql] SQL 고득점을 위한 MYSQL 내장 함수 정리

프로젝트와 DB를 사용하면서 DB의 데이터는 Spring Java로 가공했다.

하지만 SQL 고득점 kit 프로그래머스 문제를 풀다 보니 MYSQL의 내장 함수들로 그 값을 바로 출력, 처리할 수 있었다. 

함수들을 구글링을 통하여 이해하고 사용했었고, 그 내용을 포스팅하면 모두에게도 도움이 될 것 같아서 정리해보았다.

 

1. 수학 함수 

• ABS, MOD, POW, SQRT (절댓값, 나머지, 제곱, 제곱근)

SELECT ABS(-10); -- 10
SELECT MOD(12, 3) -- 14 % 3;
SELECT POW(3, 2), SQRT(16);

 

• CEIL, FLOOR, ROUND, TRUNK (올림, 내림, 반올림, 버림)

SELECT CEIL(3.7), FLOOR(3.7), ROUND(3.7); -- 올림, 버림, 반올림
SELECT CEIL(3.789, 2), FLOOR(3.789, 2), ROUND(3.789, 2); -- 소수점(2) 자리에서 %%
SELECT TRUNCATE(1234.6789, 2), TRUNCATE(1234.6789, -2); -- 버림

 

• RAND (랜덤)

SELECT RAND();  // 0.45156489
SELECT FLOOR(RAND() * 100); // 45

 

2. 문자열 함수

• 문자 길이 확인(LEN, DATALENGTH)

SELECT LEN( 'Hello' ) -- 5

 

• 문자열 자르기(LEFT, RIGHT, SUBSTRING)

SELECT LEFT( '123456', 3 ) -- '123'

SELECT RIGHT( '123456', 3 ) -- '456'

SELECT SUBSTRING( '123456', 3, 2 ) -- '34'

 

• 문자열 찾기 (CHARINDEX, PATINDEX)

SELECT CHARINDEX( '123', '123456789123' ) -- 1
SELECT CHARINDEX( '123', '123456789123', 2) -- 10

SELECT PATINDEX( '%123%', '123456789123' ) -- 1
SELECT PATINDEX( '123', '123456789123' ) --  0

SELECT PATINDEX('%[0-9]%', '(123)4A56-7B89c0') -- 2 
-- 숫자 1을 찾고 위치 2를 호출

 

• 문자열 안에 특정 문자 자릿수만큼 넣기(LPAD, RPAD)

SELECT LPAD('ABC',10,'0') FROM DUAL; -- 0000000ABC

SELECT RPAD('ABC',10,'0')  FROM DUAL; -- ABC0000000

 

• 문자열 끝의 공백 지우기(LTRIM, RTRIM)

SELECT LTRIM( '   he llo   ') / return 'he llo   '
 
SELECT RTRIM( '   he llo   ') / return '   he llo')

 

• 문자 반복(REPLICATE)

SELECT REPLICATE( '0', 4 ) -- '0000'

 

• 대소문자 변환(UPPER, LOWER)

SELECT UPPER( 'AbcdE' ) -- 'ABCDE'

SELECT LOWER( 'AbcdE' ) -- 'abcde'

 

• 문자열을 반대로(REVERSE)

SELECT REVERSE( 'HelloWorld' ) -- 'dlroWolleH'

 

3. 날짜 함수

• 시간 표현 (YEAR, MONTH, DAYOFMONTH, HOUR, MINUTE, SECOND, MICROSECOND)

ex) 2023-03-03 12:30:00
SELECT YEAR(NOW()), MONTH(NOW()), DAYOFMONTH(NOW()),
       HOUR(NOW()), MINUTE(NOW()), SECOND(NOW())
       
       -- 2023, 03, 03, 12, 30 ,00

 

• 시간 비교 (DATE, TIME, DATEDIFF, TIMEDIFF)

SELECT DATEDIFF('2020-1-5', '2020-1-1'); -- 4일 날짜 차이
SELECT TIMEDIFF('14:30:00', '06:30:00'); -- 08시간 시간 차이

SELECT DAYOFWEEK(NOW()), MONTHNAME(NOW()), DAYOFYEAR(NOW());

SELECT LAST_DAY('2023-03-04'); -- 해당월의 마지막 날짜  31

 

• 정규시간표현 DATE_FORMAT, CONVERT_TZ

ex) 2023-03-03 00:00:00
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d') FROM DUAL; -- 2023-03-03
SELECT DATE_FORMAT(NOW(),'%H:%i:%S') FROM DUAL; -- 00:00:00

SELECT @@GLOBAL.TIME_ZONE, @@SESSION.TIME_ZONE;
SELECT CONCAT_WS(' ', DATE_FORMAT(NOW(),'%H:%i:%S'), @@SESSION.TIME_ZONE) FROM DUAL;

SELECT DATE_FORMAT(CONVERT_TZ(NOW(), 'UTC', 'Asia/Seoul'),'%H:%i:%S') FROM DUAL;

 

4. 통계 함수

• COUNT, AVG, SUM, MIN, MAX

SELECT COUNT(column), AVG(column), SUM(column), MIN(column), MAX(column);

 

5. 논리 함수

• IF ~ Else (seq가 3보다 작거나 같은 면 A, 아닐 경우에는 B)

SELECT A.seq, 
	IF(A.seq <= 3, 'A', 'B') AS if_result 
FROM Table A

 

• Case ~ When (seq 값이 1~3이면 A, 4~6 이면 B, 둘 다 아닐 경우 c)

SELECT seq, 
	CASE
		WHEN (u.seq BETWEEN 1 AND 3) THEN 'A'
		WHEN (u.seq BETWEEN 4 AND 6) THEN 'B'
        ELSE 'C'
	END AS case_result
FROM Table u

 

• IFNULL 

SELECT IF(a>1, 'A', 'B'); -- 조건식, 참일때 반환값, 거짓일때 반환값
SELECT IFNULL(a, 'ERR'); -- 첫번째가 NULL 이면 두번째 값 반환

 

• NULL 값 변경(COALESCE) -- null 값인 것 No name으로 변경

SELECT COALESCE(name,'No name');