[MySQL / SQL] - 주요 함수 ( 숫자, 문자 , 시간, 날짜)

MySQL을 사용하다보면 다른 데이터베이스와도 조금씩 다른 함수들이 존재한다.

따라서 나중에 한눈에 확인해서 사용할 수 있도록 조금씩 정리해두려고한다.

숫자

 
-- ABS : 절대값
SELECT ABS(-100); -- 100
 
-- CEILING : 올림 
SELECT CEILING(3.9); -- 4
 
-- FLOOR : 내림
SELECT FLOOR(3.7); -- 3
 
-- ROUND : 반올림
SELECT ROUND(3.4); -- 3
 
-- MOD : 나머지 값
SELECT MOD(14,3); -- 2
 
-- POW : 제곱
SELECT POW(3,4); -- 81
 
-- SQRT : 제곱근
SELECT SQRT(16); -- 4
 
-- RAND : 무작위 실수 
SELECT RAND();  -- 0 ~ 0.9999999
 
-- 숫자 6중 랜덤 하나
SELECT FLOOR( 1 + RAND() * 6); -- 1 ~ 6
 
-- TRUNCATE : 소숫점 기준으로 정수 위치까지만 구하고 나머지 버림.
SELECT TRUNCATE(1234.5678 , 2); -- 1234.56
SELECT TRUNCATE(1234.5678 , -2); -- 1200
 
-- CONV : 기존 진수의 숫자를 변환할 지수로 계산 후 반환,
SELECT CONV(100,10,2); -- 1100100
-- 위 코드는 10진수 100을 2진수로 변환한다. 

 

문자

-- ASCII - 문자를 아스키 코드로 반환
SELECT ASCII('A'); -- 65
 
-- CHAR - 아스키 코드를 문자로 반환.
SELECT CHAR(65); 
 
 
-- BIT_LENGTH는 할당된 Bit 크기이다.
SELECT BIT_LENGTH('ABC'); -- 24
 
-- CHAR_LENGTH 는 문자의 개수
SELECT CHAR_LENGTH('ABC'); -- 3
 
-- LENGTH는 할당된 BYTE 수.
SELECT LENGTH('ABC'); -- 3
 
-- CONCAT : 문자열 연결 
SELECT CONCAT('2023','07','27'); -- 20230727
 
-- CONCAT_WS : 구분자와 함께 문자열 연결
SELECT CONCAT_WS('/','2023','07','27'); -- 2023/07/27
 
-- ELT : 위치 번째의 문자 반환.
SELECT ELT(2,'A','B','C'); -- B
 
-- FIELD : 찾을 문자열의 위치 찾기. 없으면 0 반환
SELECT FIELD('B','A','B','C'); -- B
 
-- FIND_IN_SET : 찾을 문자열을 문자열 리스트에서 찾기. 문자열 리스트는 콤마로 되야하고, 공백 금지
SELECT FIND_IN_SET('B','A,B,C'); -- 2
 
-- INSTR : 기준 문자열에서 부분 문자열을 찾아 시작 위치 반환
SELECT INSTR('ABCD', 'B'); -- 2
 
-- LOCATE : INSTR과 동일하나 파라미터의 순서 반대
SELECT LOCATE('B', 'ABCD'); -- 2
 
-- FORMAT : 숫자를 소숫점 아래 자릿수까지 표현 , 1000단위마다 콤마 표시
SELECT FORMAT(1234.123456, 2); -- 1,234.12
 
-- BIN : 2진수 
SELECT BIN(32); -- 100000
 
-- HEX : 16진수
SELECT HEX(32); -- 20
 
-- OCT : 8진수
SELECT OCT(32); -- 40
 
-- INSERT : 기존 문자열 위치부터 길이 만큼 삽입할 문자열로 변경
SELECT INSERT('ABCD', 2, 1, '!'); -- A!CD
 
-- LEFT : 문자열의 왼쪽부터 길이 만큼 반환
SELECT LEFT('ABCD', 3); -- ABC
 
-- RIGHT : 문자열의 오른쪽부터 길이 만큼 반환
SELECT RIGHT('ABCD', 3); -- BCD
 
-- LCASE : 대문자를 소문자로 변경한 후 반환
SELECT LCASE('ABCD'); -- abcd
 
-- UCASE : 소문자를 대문자로 변경한 후 반환
SELECT UCASE('abcd'); -- ABCD
 
-- LPAD : 문자열의 길이 만큼 왼쪽에서 늘린 후 빈 곳을 채울 문자열로 채움
SELECT LPAD('ABCD', 10, '*'); -- ******ABCD
 
-- RPAD : 문자열의 길이 만큼 오른쪽에서 늘린 후 빈 곳을 채울 문자열로 채움
SELECT RPAD('ABCD', 10, '*'); -- ABCD******
 
-- LTRIM : 문자열의 왼쪽 공백 제거
SELECT LTRIM('   ABCD'); -- ABCD
 
-- RTRIM : 문자열의 오른쪽 공백 제거
SELECT RTRIM('ABCD    '); -- ABCD
 
-- TRIM : 문자열의 양쪽 공백 모두 제거 문자열 사이는 안됨.
SELECT TRIM('  AB CD   '); -- AB CD
 
-- REPEAT : 문자열을 횟수만큼 반복.
SELECT REPEAT('ABCD ', 2);  -- ABCD ABCD
 
-- REPLACE : 문자열에서 원래 문자열을 찾아 대체할 문자열로 바꿈. 
SELECT REPLACE('MY NAME IS PARKJOOHONG', 'PARK', 'KIM'); -- MY NAME IS KIMJOOHONG
 
-- REVERSE : 문자열을 거꾸로 만든다.
SELECT REVERSE('ABC'); -- CBA
 
-- SPACE : 길이만큼 공백을 반환한다.
SELECT CONCAT('AB',SPACE(5),'CD'); -- AB     CD
 
-- SUBSTRING : 시작 위치부터 길이만큼 문자 반환
SELECT SUBSTRING('ABCD', 3, 1); -- C
 
-- SUBSTRING_INDEX : 문자열에서 시작부터 구분자 횟수까지 나오면 그 이후 버림
SELECT SUBSTRING_INDEX('ABCD.EFG', '.', 1);  -- ABCD

 

시간 및 날짜

-- SYSDATE : 현재 날짜, 시간
SELECT SYSDATE();
SELECT NOW();
 
-- ADDDATE : 지정한 날짜 + 일자 
SELECT ADDDATE(NOW(), 31);
 
-- SUBDATE  : 지정한 날짜 - 일자
SELECT SUBDATE(NOW(), INTERVAL 31 MONTH);
-- INTERVAL 을 통해 년,월,일을 지정할 수 있다.
 
-- ADDTIME : 지정한 날짜 + 시간
SELECT ADDTIME(NOW(), '3:0:0');
 
-- SUBTIME : 지정한 날짜 - 시간
SELECT SUBTIME(NOW(), '9:0:0');
 
-- 년 월 일 시간 분 초
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());
 
-- DATE : 년 월 일
SELECT DATE(NOW()); -- 2023-07-27
 
-- TIME : 시 분 초
SELECT TIME(NOW());
 
-- DATEDIFF : 날짜 빼기
SELECT DATEDIFF('2020-12-5' , '2020-12-3' ); -- 2
 
-- TIMEDIFF : 시간 빼기
SELECT TIMEDIFF('2:0:0', '0:20:0'); -- 01:40:00
 
-- DAYOFWEEK : 1주일 중 오늘 몇일째인지
SELECT DAYOFWEEK(NOW()); 
 
-- DAYOFMONTH : 1달 중 오늘 몇일째인지
SELECT DAYOFMONTH(NOW());
 
-- DAYOFYEAR : 1년 중 오늘 몇일째인지
SELECT DAYOFYEAR(NOW()); 
 
-- DAYNAME : 현재 일 이름 영어로
SELECT DAYNAME(NOW());
 
-- MONTHNAME : 현재 달 이름 영어로
SELECT MONTHNAME(NOW());
 
-- LAST_DAT : 현재 월의 마지막 일 수
SELECT LAST_DAY(NOW());
 
-- DATE_FORMAT : 날짜 형식 문자형태로 바꿈
SELECT DATE_FORMAT(NOW(), '%Y-%M-%D');  -- 2023-July-27th
SELECT DATE_FORMAT(NOW(), '%y-%m-%d');  -- 23-07-27
 
-- STR_TO_DATE : 포멧 형식의 문자열을 날짜로 바꿈
SELECT STR_TO_DATE('2013-09-30', '%Y-%m-%d'); -- 2013-09-30
-- 년도만 대문자 Y,  나머지 소문자 해야함.

select now() 현재시간, 
date_add(now(), interval 5 second) 5초후,
date_add(now(), interval 5 hour) 5시간후, 
date_add(now(), interval 5 day) 5일후
from dual;
 
 
select now(), -- 2023-08-01 16:33:57
date_format(now(), '%Y %M %e %p %l %i %S'), -- 2023 August 1 PM 4 33 57
date_format(now(), '%y-%m-%d %H:%i:%s'), -- 23-08-01 16:33:57
date_format(now(), '%y.%m.%d %W'),  -- 23.08.01 Tuesday
date_format(now(), '%H시%i분%s초') -- 16시33분57초
from dual;
 
-- if(논리식, 값1, 값2)논리식이 참이면 값1이 리턴, 거짓이면 값2 리턴
--     ifnull(값1, 값2) 값1이 NULL이면 값2로 대치, NULL이 아니면 값1리턴.
--     nullif(값1, 값2) 값1 = 값2이 TRUE이면 NULL이 그렇지 않으면 값1이 리턴.
 
select if(3 > 2, '크다', '작다'), if(3 > 5, '크다', '작다'), -- 크다, 작다
nullif(3, 3), nullif(3, 5), -- null, 3
ifnull(null, 'b'), ifnull('a', 'b') -- b, a
from dual;