MySQL 내장함수

제어 흐름 함수

IF(수식, 참, 거짓)

SELECT IF(100>200, '참이다.', '거짓이다.');

수식이 거짓이므로 거짓이다.를 출력.

IFNULL(수식1, 수식2)

수식1이 NULL인 경우 수식2를 출력.
수식1이 NULL이 아닌 경우 수식1을 출력.

SELECT IFNULL(NULL, 200); -- 200
SELECT IFNULL(NULL, '널입니다.'); -- 널입니다.
SELECT IFNULL(100, 50); -- 100

NULLIF(수식1, 수식2)

수식1과 수식2가 같으면 NULL을 반환.
다르면 수식1을 반환.

SELECT NULLIF(2*5, 5*2); -- NULL
SELECT NULLIF(3*5, 5*2); -- 15

연산자 CASE ~ WHEN ~ ELSE ~ END

CASE는 내장함수는 아니며 연산자로 분류됨.
다중 분기에서 사용된다.

SELECT CASE 10
  WHEN 1 THEN '일'
  WHEN 5 THEN '오'
  WHEN 10 THEN '십'
  ELSE '모름'
END AS '케이스연습';
-- 십

문자열 함수

ASCII(문자), CHAR(숫자)

• ASCII(문자) : 파라미터로 주어지는 문자에 해당하는 아스키코드 숫자 반환
• CHAR(숫자) : 파라미터로 주어지는 숫자에 해당하는 아스키코드 문자 반환

SELECT ASCII('A'); -- 65
SELECT CHAR(65); -- A

CHAR_LENGTH(문자열), LENGTH(문자열), BIT_LENGTH(문자열)

• CHAR_LENGTH(문자열) : 문자 갯수
• LENGTH(문자열) : 할당된 Byte 수
• BIT_LENGTH(문자열) : 할당된 Bit 수

UTF-8에서 영문은 한 글자 당 1Byte, 한글은 한 글자 당 3Byte

select char_length('abc'); -- 3
select length('abc'); -- 3
select bit_length('abc'); -- 24

select char_length('가나다'); -- 3
select length('가나다'); -- 9
select bit_length('가나다'); -- 72

CONCAT(문자열1, 문자열2, ...)

문자열을 합치기 위해서 CONCAT()을 사용할 수 있다.

SELECT num,
 CONCAT(CAST(price AS CHAR(10)), 'x', CAST(amount AS CHAR(4)), '=') AS '단가*수량',
 price * amount AS '구매액'
 FROM buyTBL;

INT형 자료를 먼저 문자열로 변환한 후, CONCAT

CONCAT_WS(구분자, 문자열1, 문자열2, ...)

CONCAT_WS()는 첫번째 파라미터로 구분자를 입력받음. 이후 나머지 파라미터(문자열)를 이을 때 구분자를 삽입

SELECT CONCAT_WS('/', '2024', '07', '13'); -- 2024/07/13

위치 찾기

• ELT(위치, 문자열1, 문자열2, ...,) : 파라미터로 주어진 문자열들 중 위치 번째에 해당하는 문자열을 반환
• FIELD(찾을 문자열, 문자열1, 문자열2, ...,) : 파라미터로 주어진 문자열들 중 찾을 문자열과 일치하는 문자열의 위치를 반환. 없으면 0 반환
• FIND_IN_SET(찾을 문자열, 문자열리스트) : 문자열 리스트에서 찾을 문자열과 일치하는 문자열을 찾아 위치를 반환. 문자열 리스트는 ,로 구분되어 있어야 하며 공백이 없어야 한다.
• INSTR(기준 문자열, 부분 문자열) : 기준 문자열에서 부분 문자열을 찾아서 시작 위치를 반환
• LOCATE(부분 문자열, 기준 문자열) : INSTR()과 같은 기능을 하지만 파라미터 순서가 반대. POSITION(부분 문자열 IN 기준 문자열)과는 동일한 함수이다.

SELECT ELT(2, 'A', 'D', 'B', 'C'); -- D
SELECT FIELD('둘', '하나', '둘', '셋'); -- 2
SELECT FIND_IN_SET('하나', '셋,둘,하나'); -- 3
SELECT INSTR('감자감자', '감자'); -- 1
SELECT LOCATE('감자', '감자감자'); -- 1
SELECT POSITION('감자' IN '감자감자'); -- 1

INSERT(기준 문자열, 위치, 길이, 삽입할 문자열)

기준 문자열에서 위치로부터 길이만큼 문자열 삽입

SELECT INSERT('ABCDEFGHI', 3, 4, 'cdef'); -- ABcdefGHI
SELECT INSERT('ABCDEFGHI', 3, 2, 'cde'); -- ABcdeEFGHI
SELECT INSERT('ABCDEFGHI', 3, 4, 'cde'); -- ABcdeGHI

길이와 삽입할 문자열의 길이는 꼭 같지 않아도 됨.

• 길이보다 삽입 문자열 길이가 긴 경우에는 길이만큼만 삽입 문자열에서 가져와서 기준 문자열의 문자를 대체
• 길이보다 삽입 문자열 길이가 작은 경우에는 부족한 길이만큼 기준 문자열로부터 문자를 삭제

LEFT(문자열, 길이), RIGHT(문자열, 길이)

• LEFT() : 문자열에서 왼쪽으로부터 길이만큼의 부분 문자열 반환
• RIGHT() : 문자열에서 오른쪽으로부터 길이만큼의 부분 문자열 반환

SELECT LEFT('ABCDEFG', 3); -- ABC
SELECT RIGHT('ABCDEFG', 3); -- EFG

UPPER(문자열), LOWER(문자열)

• UPPER() : 문자열의 모든 문자를 대문자로
• LOWER() : 문자열의 모든 문자를 소문자로

SELECT UPPER('abcdefg'); -- ABCDEFG
SELECT LOWER('ABCDEFG'); -- abcdefg

LPAD(문자열, 길이, 채울 문자열), RPAD(문자열, 길이, 채울 문자열)

• LPAD() : 문자열을 길이만큼 늘린 후 채울 문자열을 반복하여 왼쪽 공백을 채움
• RPAD() : 문자열을 길이만큼 늘린 후 채울 문자열을 반복하여 오른쪽 공백을 채움

SELECT LPAD('순', 6, '두부'); -- 로나로나로메
SELECT RPAD('순', 6, '두부'); -- 메로나로나로

LTRIM(문자열), RTRIM(문자열)

• LTRIM() : 문자열의 왼쪽 공백 제거
• RTRIM() : 문자열의 오른쪽 공백 제거

SELECT LTRIM('   순두부'); -- 순두부
SELECT RTRIM('순두부   '); -- 순두부

TRIM()

• TRIM(문자열) : 문자열의 앞뒤 공백을 모두 제거
• TRIM(방향 자를문자열 FROM 문자열) : 문자열로부터 방향에 맞게 자를 문자열을 제거
  방향은 앞, 뒤, 양쪽
  • LEADING : 앞
  • TRAILING: 뒤
  • BOTH : 양쪽 모두

SELECT TRIM('  올 때 메로나    '); -- 올 때 메로나
SELECT TRIM(LEADING '!' FROM '!!!올 때 메로나!!!'); -- 올 때 메로나!!!
SELECT TRIM(TRAILING '.' FROM '...올 때 메로나...'); -- ...올 때 메로나
SELECT TRIM(BOTH 'ㅋ' FROM 'ㅋㅋㅋ올 때 메로나ㅋㅋㅋ'); -- 올 때 메로나

REVERSE(문자열)

문자열 순서를 반대로 반환

SELECT REVERSE('오랑우탄'); -- 탄우랑오

SPACE(길이)

길이만큼의 공백을 반환

SELECT CONCAT('안', SPACE(5), '녕하세요'); -- 안     녕하세요

SUBSTRING()

두가지 방식 가능
SUBSTRING(문자열, 시작위치, 길이)
SUBSTRING(문자열 FROM 시작위치 FOR 길이)

SELECT SUBSTRING('가나다라마바사', 3, 3); -- 다라마
SELECT SUBSTRING('가나다라마바사' FROM 3 FOR 3); -- 다라마

길이 파라미터가 문자열의 길이보다 긴 경우에는 문자열의 끝까지 반환

SUBSTRING_INDEX(문자열, 구분자, 횟수)

SELECT SUBSTRING_INDEX('www.naver.com', '.', 2); -- www.naver
SELECT SUBSTRING_INDEX('www.naver.com', '.', -2); -- naver.com

횟수만큼 구분자가 나오면, 그 이후의 문자열은 버림(이전까지의 문자열을 반환)
횟수가 양수이면 왼쪽부터 세고, 음수이면 오른쪽부터 셈

숫자, 수학 함수

FORMAT(숫자, 소수점 자릿수)

SELECT FORMAT(3.14159265, 3); -- 3.141

BIN(숫자), OCT(숫자), HEX(숫자)

• BIN(숫자) : 2진수로 변환
• OCT(숫자) : 8진수로 변환
• HEX(숫자) : 16진수로 변환

SELECT BIN(25), OCT(25), HEX(25); -- 11001 31 19

ABS(숫자)

절댓값 반환

SELECT ABS(-25); -- 25

삼각함수

사인, 코사인 탄젠트

• SIN(숫자)
• COS(숫자)
• TAN(숫자)

역사인, 역코사인, 역탄젠트

• ACOS(숫자)
• ASIN(숫자)
• ATAN(숫자) : [-π/2, π/2] 범위
• ATAN2(숫자1, 숫자2) : [-π, π] 범위

CEILING(숫자), FLOOR(숫자), ROUND(숫자)

올림, 내림, 반올림

SELECT CEILING(2.5), FLOOR(2.5), ROUND(2.5); -- 3, 2, 3

CONV(숫자, 현재 진수, 변환할 진수)

숫자를 변환할 진수로 변환

SELECT CONV('AA', 16, 2); -- 10101010
SELECT CONV(100, 10, 8); -- 144

DEGREES(숫자), RADIANS(숫자), PI()

• DEGREES(숫자) : 라디안 -> 각도 변환
• RADIANS(숫자) : 각도 -> 라디안 변환
• PI() : 파이 반환

SELECT DEGREES(PI()); -- 180
SELECT RADIANS(180); -- 3.141592653589793

지수 함수, 로그 함수

• EXP(숫자)
  밑이 e
• LN(숫자)
  밑이 e
• LOG(숫자)
  밑이 e
• LOG(밑, 진수)
• LOG2(숫자)
  밑이 2
• LOG10(숫자)
  밑이 10

모듈러

• MOD(숫자1, 숫자2)
• 숫자1 % 숫자2
• 숫자1 MOD 숫자2

거듭제곱, 제곱근

• POW(숫자1, 숫자2)
• SQRT(숫자)

난수

• RAND()
  [0, 1) 범위의 실수
• RAND(숫자)
  파라미터로 SEED를 주는 경우에는 여러 번 실행해도 같은 수를 반환

SIGN(숫자)

숫자가 양수, 0, 음수 중 어디에 속하는지 반환

• 양수 : 1 반환
• 0 : 0 반환
• 음수 : -1 반환

TRUNCATE(숫자, 정수)

숫자를 소수점 기준 정수 위치까지 구하고 나머지를 버림

• 정수 파라미터가 양수 : 소수점 뒤로 정수 위치까지
• 정수 파라미터가 0 : 소수점 이하를 버림
• 정수 파라미터가 음수 : 소수점 앞으로 정수 위치까지(자릿수는 유지)

SELECT TRUNCATE(12345.6789, 2); -- 12345.67
SELECT TRUNCATE(12345.6789, 0); -- 12345
SELECT TRUNCATE(12345.6789, -2); -- 12300

날짜 및 시간 함수

ADDDATE(날짜, 차이), SUBDATE(날짜, 차이)

• ADDDATE() : 날짜 기준 차이만큼 더함
• SUBDATE() : 날짜 기준 차이만큼 뺌

날짜는 ''로 감싸져 있어야 함
차이는 정수로만 주는 경우에는 DAY 단위로 계산되며,
MONTH나 YEAR 단위로 주고 싶은 경우에는 INTERVAL 정수 단위로 주면 됨.

SELECT ADDDATE('2024-07-14', 3); -- 2024-07-17
SELECT SUBDATE('2024-07-14', 3); -- 2024-07-11

SELECT ADDDATE('2024-07-14', INTERVAL 1 YEAR); -- 2025-07-14
SELECT SUBDATE('2024-07-14', INTERVAL 1 MONTH); -- 2024-06-14

ADDTIME(날짜 시간, 시간), SUBTIME(날짜 시간, 시간)

• 첫번째 파라미터로 날짜와 시간을 공백으로 구분하여 넣음
  yyyy-MM-dd HH:mm:ss 형식
• 두번째 파라미터로 더하거나 뺄 시간을 넣음
  HH:mm:ss 형식

역시 날짜, 시간은 ''로 감싸져 있어야 함

SELECT ADDTIME('2024-07-14 22:35:07', '2:2:2'); -- 2024-07-15 00:37:09
SELECT SUBTIME('2024-07-14 22:35:07', '2:2:2'); -- 2024-07-14 20:33:05

현재 날짜, 시간

• CURDATE(), CURRENT_DATE
  현재 날짜 yyyy-MM-dd
• CURTIME(), CURRENT_TIME
  현재 시간 HH:mm:ss
  파라미터를 주는 경우에는 소숫점 자리수까지 표현. 범위 1~6
• NOW(), SYSDATE(), LOCALTIME(), LOCALTIMESTAMP(), LOCALTIME, LOCALTIMESTAMP,
  현재 날짜 시간 yyyy-MM-dd HH:mm:ss
  파라미터를 주는 경우에는 소숫점 자리수까지 표현. 범위 1~6

SELECT CURDATE(); -- 2024-07-14
SELECT CURTIME(); -- 22:41:34
SELECT NOW(); -- 2024-07-14 22:41:29
SELECT SYSDATE(); -- 2024-07-14 22:41:24

연, 월, 일, 시, 분, 초, 밀리초

• YEAR(날짜)
  파라미터로 준 날짜의 연 정보
• MONTH(날짜)
  파라미터로 준 날짜의 월 정보
• DAY(날짜)
  파라미터로 준 날짜의 일 정보
• HOUR(시간)
  파라미터로 준 시간의 시 정보
• MINUTE(시간)
  파라미터로 준 시간의 분 정보
• SECOND(시간)
  파라미터로 준 시간의 초 정보
• MICROSECOND(밀리초가 포함된 시간)
  파라미터로 준 시간의 밀리초 정보

DATE(DATETIME), TIME(DATETIME)

• DATE() : 날짜 시간 정보로부터 날짜만 반환
• TIME() : 날짜 시간 정보로부터 시간만 반환

날짜, 시간 차 계산

• DATEDIFF(날짜1, 날짜2) : 두 날짜 간의 차이
  날짜1 - 날짜2. 일 수를 계산
  정수 형태
• TIMEDIFF(시간1, 시간2) : 두 시간 간의 차이
  시간1 - 시간2. 시간을 계산
  HH:mm:ss 형태

날짜의 순서

• DAYOFWEEK(날짜) : 해당 날짜가 몇 번째 요일인지
  1:일, 2:월 ~ 7:토
• MONTHNAME(날짜) : 해당 날짜의 달 이름
  January ~ December
• DAYOFYEAR(날짜) : 해당 날짜의 해에서 몇 번째 날인지
  1 ~ 365

LAST_DAY(날짜)

해당 날짜의 달에서 마지막 날
윤달 계산을 할 때 편리하게 사용 가능

SELECT LAST_DAY('2021-11-1'); -- 2021-11-30

MAKEDATE(연도, 정수)

해당 연도에서 정수 일만큼 지난 날짜

SELECT MAKEDATE(2025, 1); -- 2025-01-01
SELECT MAKEDATE(2025, 365); -- 2025-12-31

MAKETIME(시, 분, 초)

시, 분, 초를 사용해 HH:mm:ss 포맷으로 변경

SELECT MAKETIME(15,2,3);  -- 15:02:03

PERIOD_ADD(연월, 개월수), PERIOD_DIFF(연월1, 연월2)

• PERIOD_ADD() : 연월에서 개월수만큼 지난 연월을 반환
• PERIOD_DIFF() : 연월1 - 연월2 개월수를 반환

SELECT PERIOD_ADD(202503, 3); -- 202506
SELECT PERIOD_DIFF(202503, 202512); -- -9

QUARTER(날짜)

해당 날짜가 4분기 중 몇 분기에 속하는 지 반환

SELECT QUARTER(CURDATE()); -- 3

TIME_TO_SEC(시간)

시간을 초단위로 변환

SELECT TIME_TO_SEC('11:25:01'); -- 41101

시스템 정보 함수

USER(), DATABASE()

현재 사용자, 데이터베이스

SELECT USER(), DATABASE(); -- root@localhost sqldb

FOUND_ROWS()

바로 앞의 SELECT문에서 조회된 행의 갯수를 반환

SELECT * FROM buyTBL; -- 10개 행이 조회됨
SELECT FOUND_ROWS(); -- 10

ROW_COUNT()

바로 앞의 INSERT, UPDATE, DELETE문에서 입력, 수정, 삭제된 행의 갯수를 반환

CREATE, DROP문은 0을 반환
SELECT문은 -1을 반환

VERSION()

현재 MySQL 버전

SLEEP(초)

쿼리의 실행을 주어진 초만큼 멈춤