[MySQL] 내장 함수 정리

※ 자주 사용하는 함수

현재 날짜 조회

SELECT current_timestamp; #2021-03-07 오후 9:27:21
SELECT NOW(); #2021-03-07 오후 9:27:21
SELECT SYSDATE(); #2021-03-07 오후 9:27:21
SELECT CURDATE(); #2021-03-07 오전 12:00:00

날짜 및 시간 더하기/빼기

#기준날짜로부터 하루 뒤 날짜 조회
SELECT DATE_ADD("2021-03-07 23:59:59", INTERVAL 1 DAY);

#한달 전 
#customer 테이블에서 한달 전 ~ 오늘까지 등록된 데이터 조회
SELECT * from customer where created >= date_add(now(), interval -1 month); 

#하루 전 
#customer 테이블에서 하루 전 ~ 오늘까지 등록된 데이터 조회
SELECT * from customer where created >= date_add(now(), interval -1 day); 

#한시간 전 
#customer 테이블에서 한시간 전 ~ 오늘까지 등록된 데이터 조회
SELECT * from customer where created >= date_add(now(), interval -1 hour);

입력된 날짜(date)를 특정 형식(format)의 문자열로 변환하여 반환하기 DATE_FORMAT(date,format)

SELECT DATE_FORMAT(CURDATE(),'%Y-%m-%d');

💬 format 형식

%M 영문 달 이름(January...December)

%W 요일명(Sunday...Saterday)

%D 날짜의 서수표기(1st,2nd,3rd....)
%Y 4자리 년 표기
%y 2자리 년 표기
%X 일요일부터 시작하는 주수를 4자리로 표기
%x 월요일부터 시작하는 주수를 4자리로 표기

%a 단축된 요일명 표기(Sun...Sat)
%d 월 중 날짜를 숫자로 표기(00...31)
%e 월 중 날짜를 숫자로 표기(0...31)
%m 숫자로 표기된 월(01...12)
%c 숫자로 표기된 월(1...12)
%b 단축된 달 이름 표기(Jan...Dec)
%j 년 중 일수 표기(001...366)
%H 24시간 표기(00...23)
%k 24시간 표기(0...23)
%h 12시간 표기(01...12)
%I 12시간 표기(01...12)
%l 12시간 표기(1...12)
%i 분의 숫자 표기(00...59)
%r 12시간 표기(hh:mm:ss [AP]M)
%T 24시간 표기(hh:mm:ss)
%S 초 표기(00...59)
%s 초 표기(00...59)
%p AM 또는 PM
%w 요일을 숫자로 표기(0=일요일...6=토요일)
%U 일요일부터 시작하는 수준 표기(0...53)
%u 월요일부터 시작하는 수준 표기(0...53)
%V 일요일부터 시작하는 수준 표기(1...53)
%v 월요일부터 시작하는 수준 표기(1...53)
%% % 문자 표기

가장 최근에 성공적으로 수행된 INSERT 구문의 첫 번째 AUTO_INCREMENT column의 값을 반환 (없을 경우 0을 반환)

SELECT LAST_INSERT_ID();

두 날짜 사이의 차이 datediff(enddate, startdate)

SELECT datediff(now(), date_format('2021-03-01', '%Y-%m-%d'));

문자열 연결

오라클에서는 '||'로 문자열을 연결하지만 MySQL에서는 '||'을 사용할 수 없고 CONCAT() 함수를 이용해 문자열을 연결
결과는 '서울특별시 강남구' 라는 문자열이 출력

SELECT CONCAT('서울특별시', ' ', '강남구'); 

String을 Date 형으로 변환 STR_TO_DATE(str,format)

SELECT STR_TO_DATE('20210307', '%Y%m%d');
SELECT STR_TO_DATE('20210307235959', '%Y%m%d%H%i%s');

 

1. 숫자 관련 함수

• ABS(숫자) - 절대값 출력
• CEILING(숫자) - 값보다 큰 정수 중 가장 작은 수
• FLOOR(숫자) - 값보다 작은 정수 중 가장 큰 수[실수를 무조건 버림(음수일 경우는 제외)]
• ROUND(숫자,자릿수) - 숫자를 소수점 이하 자릿수에서 반올림(자릿수는 양수,0,음수) 
• TRUNCATE(숫자,자릿수) - 숫자를 소수점 이하 자릿수에서 버림
• POW(X,Y) or POWER(X,Y) - X의 Y승
• MOD (분자, 분모) - 분자를 분모로 나눈 나머지를 구한다.(연산자 %와 같다)
• GREATEST(숫자1,숫자2,숫자3...) - 주어진 수 중 제일 큰 수 리턴
• LEAST(숫자1,숫자2,숫자3...) - 주어진 수 중 제일 작은 수 리턴
• INTERVAL(a,b,c,d.....) - a(숫자)의 위치 반환

2. 문자 관련 함수

• ASCII(문자) - 문자의 아스키 코드값 리턴
• CONCAT('문자열1','문자열2','문자열3'...) - 문자열들을 연결
• INSERT('문자열','시작위치','길이','새로운 문자열') - 문자열의 시작위치부터 길이만큼 새로운 문자열로 대치
• REPLACE('문자열','기존문자열','바뀔 문자열') - 문자열 중 기존 문자열을 바뀔 문자열로 바꾼다
• INSTR('문자열','찾는 문자열') - 문자열 중 찾는 문자열의 위치 값을 출력
• LEFT('문자열',개수) - 문자열 중 왼쪽에서 개수만큼을 추출
• RIGHT('문자열',개수) - 문자열 중 오른쪽에서 개수만큼을 추출
• MID('문자열',시작위치,개수) - 문자열 중 시작위치부터 개수만큼 출력
• SUBSTRING('문자열',시작위치,개수) - 문자열 중 시작위치부터 개수만큼 출력
• LTRIM('문자열') - 문자열 중 왼쪽의 공백을 없앤다
• RTRIM('문자열') - 문자열 중 오른쪽의 공백을 없앤다
• TRIM('문자열') - 양쪽 모두의 공백을 없앤다
• LCASE('문자열') or LOWER('문자열') - 소문자로 바꾼다
• UCASE('문자열') or UPPER('문자열') - 대문자로 바꾼다
• REVERSE('문자열') - 문자열을 반대로 나열한다

3. 논리 관련 함수

• IF(논리식,참일 때 값,거짓일 때 값) - 논리식이 참이면 참일 때 값을 출력하고, 논리식이 거짓이면 거짓일 때 값을 출력한다
• IFNULL(값1,값2) - 값1이 NULL 이면 값2로 대치하고 그렇지 않으면 값1을 출력 (Oracle의 NVL 과 같은 함수)

4. 집계 함수

• COUNT(필드명) - NULL 값이 아닌 레코드 수를 구한다
• SUM(필드명) - 필드명의 합계를 구한다
• AVG(필드명) - 각각의 그룹 안에서 필드명의 평균값을 구한다
• MAX(필드명) - 최대값을 구한다
• MIN(필드명) - 최소값을 구한다

5. 날짜 관련 함수

• NOW() or SYSDATE() or CURRENT_TIMESTAMP() - 현재 날짜와 시간 출력
• CURDATE() or CURRENT_DATE() - 현재 날짜 출력
• CURTIME() or CURRENT_TIME() - 현재 시간 출력
• DATE_ADD(날짜,INTERVAL 기준값) - 날짜에서 기준값 만큼 더함
• DATE_SUB(날짜,INTERVAL 기준값) - 날짜에서 기준값 만큼 뺀다
• YEAR(날짜) - 날짜의 연도 출력
• MONTH(날짜) - 날짜의 월 출력
• MONTHNAME(날짜) - 날짜의 월을 영어로 출력
• DAYNAME(날짜) - 날짜의 요일일 영어로 출력
• DAYOFMONTH(날짜) - 날짜의 월별 일자 출력
• DAYOFWEEK(날짜) - 날짜의 주별 일자 출력(일요일(1), ...토요일(7)) 
• WEEKDAY(날짜) - 날짜의 주별 일자 출력(월요일(0),화요일(1)...일요일(6)) 
• DAYOFYEAR(날짜) - 일년을 기준으로 한 날짜까지의 날 수
• WEEK(날짜) - 일년 중 몇 번째 주
• FROM_DAYS(날 수) - 00년 00월 00일부터 날 수 만큼 경과한 날의 날짜 출력
• TO_DAYS(날짜) - 00 년 00 월 00일 부터 날짜까지의 일자 수 출력
• DATE_FORMAT(날짜,'형식') - 날짜를 형식에 맞게 출력