MariaDB : SQL 주요함수에 대해서

SQL에서는 데이터베이스에서 자주 사용되는 기능을 내장 함수로 제공하고 있는데, 함수들을 통해서 데이터를 조작하거나 분석하는 등의 작업을 수행할 수 있게 도와준다.

 

숫자함수

함수	설명
ABS(숫자)	절대 값을 반환
FLOOR(숫자)	내림
CEILING(숫자)	올림
ROUND(숫자)	반올림
MOD(숫자, 나눌 숫자)	나눈 후 나머지 값 ('숫자%나눌 숫자' 와 동일)
POW(숫자, 거듭제곱 숫자)	첫 번째 숫자의 거듭제곱을 계산
SQRT(숫자)	주어진 숫자의 제곱근을 반환
RAND()	0이상 1미만의 난수
SIGN(숫자)	숫자가 양수면 1, 0이면 0, 음수면 -1을 반환
TRUNCATE(숫자, 정수)	숫자를 소수점 기준으로 나머지를 버림

SELECT 
ABS(-10), #10, 절대값
FLOOR(1.52), #1, 내림
CEILING(1.52), #2, 올림
ROUND(1.52), #2, 반올림
MOD(5,2), #1, 나머지
POW(2,3), #8, 2의 3제곱
SQRT(9), #3, 루트9 
RAND(), #0.93303459, 0~1 사이의 난수
SIGN(-10), #-1, 음수면 -1 반환
TRUNCATE(1.23127123123, 4); # 1.2312, 소수점 4자리에서 버림

 

문자함수

함수	설명
CHAR_LENGTH(문자열)	해당 문자열의 문자 크기를 반환
LENGTH(문자열)	해당 문자열의 바이트 크기를 반환
CONCAT(문자열, 문자열)	문자열을 이어줌
CONCAT_WS(연결문자,문자열,문자열)	문자열이 이어질 때 연결문자 사용
INSERT(문자열, 위치, 길이, 삽입할 문자열)	기준 문자열의 위치부터 길이만큼 지우고 문자열 삽입
LEFT | RIGHT(문자열, 길이)	왼쪽 또는 오른쪽에서 문자열의 길이만큼 반환
UCASE |  LCASE(문자열)	소문자를 대문자로, 대문자를 소문자로 변경
LPAD | RPAD (문자열, 길이, 채울문자열)	문자열을 길이만큼 늘린 후에 문자열을 통해 채움
TRIM() | TRIM(BOTH 제거할문자 FROM 문자열)	문자열의 앞 뒤 공백을 제거 | 문자열에서 앞 뒤 문자 제거
REPEAT(문자열, 횟수)	문자열을 횟수만큼 반복
REPLACE(문자열, 원래문자열, 바꿀문자열)	문자열에서 원래문자열을 찾아 바꿀문자열로 바꿈
SUBSTRING(문자열, 위치, 길이)	문자열의 위치부터 길이만큼 문자 반환
SUBSTRING_INDEX(문자열, 구분자, 위치 )	문자열에서 특정 구분자를 기준으로 지정된 위치까지 문자열 반환
FIELD(찾을문자, 문자, 문자)	열거되어있는 문자 중 찾을 문자의 인덱스 반환
FIND_IN_SET(찾을문자, '문자,문자,문자')	열거되어있는 문자(구분자로 구분) 중 찾을 문자의 인덱스 반환
INSTR(문자열, 찾을문자)	전체 문자열 중 찾을 문자의 인덱스 반환
LOCATE(찾을문자, 문자열)	전체 문자열 중 찾을 문자의 인덱스 반환

SELECT
CHAR_LENGTH('안녕하세요'), #5
LENGTH('안녕하세요'), #15, 한글의 경우 한글자가 3바이트이다.
CONCAT('안녕', '하세요'), #안녕하세요
CONCAT_WS('&', '코딩쥐', 'CODINGJI'), #코딩쥐&CODINGJI
INSERT('반갑습니다. 티스토리입니다.', 8, 4, '코딩쥐'), #반갑습니다. 코딩쥐입니다.
RIGHT('안녕하세요. 코딩쥐 티스토리입니다.', 13), # 코딩쥐 티스토리입니다.
LCASE('HELLO'), #hello
TRIM('          안녕하세요    '), #안녕하세요
REPEAT('안녕',3), #안녕안녕안녕
REPLACE('반갑습니다. 티스토리입니다.', '티스토리', '코딩쥐'), #반갑습니다. 코딩쥐입니다.
SUBSTRING('안녕하세요. 코딩쥐 티스토리입니다.', 8, 3), #코딩쥐
FIELD('HOLA', '안녕', 'HELLO', 'HOLA', 'CHAO'), #3
FIND_IN_SET('HOLA', '안녕,HELLO,HOLA,CHAO'), #3
INSTR('안녕하세요. 코딩쥐 티스토리입니다.', '코딩쥐'), #8
LOCATE('코딩쥐', '안녕하세요. 코딩쥐 티스토리입니다.'); #8

 

날짜 및 시간 함수

함수	설명
SYSDATE(), NOW()	시스템에 저장된 현재 날짜를 반환
CURDATE() | CURTIME()	CURDATE는 현재 년-월-일 | CURTIME은 현재 시:분:초
DATE() | TIME()	DATETIME형식에서 연월일 | 시분초만 추출
YEAR | MONTH | DAY(날짜)	날짜에서 연, 월, 일을 반환
HOUR | MINUTE | SECOND | MICROSECOND(시간)	시간에서 시, 분, 초를 반환
LAST_DAY(월)	해당 달의 마지막 날짜를 반환
QUARTER(날짜)	날짜가 4분기 중 몇 분기인지 반환
ADDDATE | SUBDATE(날짜, 차이)	날짜를 기준으로 차이를 더하거나 뺀 날짜를 반환
ADDTIME | SUBTIME(날짜/시간, 시간)	날짜/시간을 기준으로 시간을 더하거나 뺀 날짜를 반환
ADD_MONTHS(날짜)	날짜에 개월 수를 더해 반환
DATEDIFF(날짜1, 날짜2)	날짜1-날짜2의 일수를 반환
TIMEDIFF(시간1, 시간2)	시간1-시간2의 결과를 반환
MAKEDATE(연도, 일수)	연도와 일수를 기반으로 날짜를 생성
MAKETIME(시,분,초)	'시:분:초'의 TIME형식을 만들어 반환
DAYOFWEEK(날짜)	요일을 반환 (1=일요일, 7=토요일)
DAYNAME | MONTHNAME(날짜)	요일 | 월을 문자로 반환
DAYOFYEAR(날짜)	주어진 날짜가 해당 연도의 몇 번째 날인지 반환
DATE_FORMAT(시간, '형식')	시간을 설정한 형식으로 출력

 

DATE_FORMAT의 형식

만약 2024-10-15 오후 3시 14분 24초 라고 한다면 아래와 같이 표기된다.

형식	설명	예시	형식	설명	예시
%Y	4자리 연도	2024	%p	AM 또는 PM	PM
%y	2자리 연도	24	%W	요일	TUESDAY
%M	전체 월 이름	October	%w	주의 일 (0=일요일, 6=토요일)	2
%m	2자리 월	10	%j	연중 일 수	288
%d	2자리 일	15	%U	연중 주 수 (일요일 시작)	42
%H	24시간제 시	15	%u	연중 주 수  (월요일 시작)	43
%h	12시간제 시	03
%i	분	14
%s	초	24

SELECT
#현재시간 : 2024-10-19 12:07:47
NOW(), # 2024-10-19 12:07:47
CURDATE(), CURTIME(), # 2024-10-19 , 12:07:47
DATE(NOW()),TIME(NOW()), # 2024-10-19 , 12:07:47
YEAR(NOW()), MONTH(NOW()), DAY(NOW()), # 2024, 10, 19
LAST_DAY(CURRENT_DATE), # 2024-10-31
QUARTER(NOW()), # 4 
ADDDATE('2024-10-15', INTERVAL 1 MONTH), # 2024-11-15
SUBDATE('2024-10-15', INTERVAL 1 MONTH), # 2024-09-15
ADDTIME('2024-10-15 14:11:00', '1:00:00'), # 2024-10-15 15:11:00
SUBTIME('2024-10-15 14:11:00', '1:00:00'), # 2024-10-15 13:11:00
DATEDIFF(NOW(), '2024-12-25'), # -67
TIMEDIFF(TIME(NOW()), '20:00'), # -07:52:13
MAKEDATE(2024, 210), MAKETIME(13,1,20), # 2024-07-28, # 13:01:20
DAYOFWEEK(NOW()), DAYNAME(NOW()), MONTHNAME(NOW()), # 7, Saturday, October
DATE_FORMAT('2024-10-15 14:11:23', '%y년%m월%d일 %W %h시%i분%s초%p'); # 24년10월15일 Tuesday 02시11분23초PM

 

제어 흐름 함수

함수	설명
IF(수식, 참, 거짓)	수식이 참 / 거짓인지에 따라 해당 값 반환
IFNULL(수식1, 수식2)	수식1이  NULL이면 수식2를 반환, 그렇지 않으면 수식1 반환
NULLIF(수식1, 수식2)	수식1과 수식2가 같다면 NULL을, 그렇지 않으면 수식1 반환
CASE ~ WHEN ~ ELSE ~ END	다중 분기에 사용

# userinfo에서 age가 30 이상일 경우 '30대 이상' 아닐경우 '30대 미만'
SELECT ename, if(age>=30, '30대이상', '30대 미만') AS 30대기준 FROM userinfo;

# null 값이 있을 경우 'NO INFORMATION'을 반환
SELECT ename, IFNULL(favorite, 'NO INFORMATION') AS 좋아하는것들  FROM userinfo;

# age를 25세 미만, 25~30세, 30세이상으로 나눠서 반환
SELECT ename, age,
case
	when age<25 then '25세 미만'
	when age<30 then '25세이상 30세미만'
	when age>=30 then '30세이상'
	END AS 나이범위
FROM userinfo;