[MySQL] 내장함수

내장함수

- 흐름 함수, 문자열 함수, 수학 함수, 날짜/시간 함수, 전체 텍스트 검색 함수, 형 변환 함수, XML 함수, 비트 함수, 보안/압축 함수, 정보 함수, 공간 분석 함수, 기타 함수 등 이 있다.

 

---

제어 흐름함수

- 프로그램의 흐름을 제어하는 함수이다.

- IF(수식, 참, 거짓)

• 수식의 결과가 참인지 거짓인지에 따라 2중으로 분기된다.

-- IF(수식, 참, 거짓)
select if(100>200, '참', '거짓');

- IFNULL(수식1, 수식2)

• 수식1이 NULL이 아니면 수식1이 반환되고 수식1이 NULL이면 수식2 가반환된다.
• NULL값을 판단할 때 사용된다.

-- IFNULL(수식1, 수식2)
select ifnull(NULL,'null');

- NULLIF(수식1, 수식2)

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

-- NULLIF(수식1, 수식2)
select nullif(100,200),nullif(100, 100);

- CASE ~ WHEN ~ ELSE ~ END : 다중분기

• CASE는 내장함수는 아니며 연산자(Operator)로 분류된다.
• 다중분기에 사용된다.
• 자바의 switch와 비슷하다.

-- CASE ~ WHEN ~ ELSE ~ END : 다중분기
select case 10
	when 1 then '일'
    when 5 then '오'
    when 10 then '십'
    else '모르는 값'
end as 'case 예시';

 

문자열 함수

- 문자열 조작시 사용되며 활용도가 높다.

- ASCII(아스키 코드)

• 문자의 아스키 코드 값을 반환한다.

- CHAR(숫자)

• 숫자의 아스키 코드값에 해당하는 문자를 반환한다.
• Workbench에서 실행시 BLOB으로 뜨면 해당 칸 선택 후 오른쪽 마우스 클릭 - Open Value in Viewer 클릭 - Text 탭에서 해당 값의 문자를 확인할 수 있다.

-- ASCII(아스키 코드), CHAR(숫자)
select ascii('A'), char(65);

- BIT_LENGTH(문자열)

• 할당된 Bit 크기 또는 문자 크기를 반환한다.

-  CHAR_LENGTH(문자열)

• 문자의 개수를 반환한다.

- LENGTH(문자열)

• 할당된 Byte수를 반환한다.

-- BIT_LENGTH(문자열), CHAR_LENGTH(문자열), LENGTH(문자열)
select bit_length('abc'), char_length('abc'), length('abc'); -- 영어 1개 = 1byte = 8bit
select bit_length('가나다'), char_length('가나다'), length('가나다'); -- 한글 1개 = 3byte = 72bit
/* UTF-8 코드에서 한글은 3byte로 한글자를 표현 한다.*/

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

문자열을 이어준다.

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

구분자와 함께 문자열을 이어준다.

-- CONCAT(문자열1, 문자열2, ...), CONCAT_WS(구분자, 문자열1, 문자열2, ...)
select concat('2023','02','07'),concat_ws('-','2023','02','07');

 

- ELT(위치, 문자열1, 문자열2, …)

• 위치 번째에 해당하는 문자열을 반환한다.

- FIELD(찾을 문자열, 문자열1, 문자열2, …)

• 찾을 문자열의 위치를 찾아 반환하며 없으면 0을 반환한다.

- FIND_IN_SET (찾을 문자열, 문자열 리스트)

• 찾을 문자열을 문자열 리스트에서 찾아 위치를 반환한다.
• 문자열 리스트는 콤마(,)로 구분되어 있고 공백이 없어야 한다.

- INSTR(기준 문자열, 부분 문자열)

• 기준 문자열에서 부분 문자열을 찾아 그 시작 위치를 반환한다.

- LOCATE(부분 문자열, 기준 문자열)

• INSTR()와 동일하지만 파라미터의 순서가 반대이다.

-- ELT(위치, 문자열1, 문자열2, …), FIELD(찾을 문자열, 문자열1, 문자열2, …), 
-- FIND_IN_SET (찾을 문자열, 문자열 리스트), INSTR(기준 문자열, 부분 문자열), LOCATE(부분 문자열, 기준 문자열)
select elt(1, '하나', '둘', '셋'), field('둘','하나','둘','셋'),
	   find_in_set('셋', '하나,둘,셋'),instr('하나둘셋','하'),locate('나','하나둘셋');

 

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

• 1000단뒤 구분기호(,)를 표시하고 소숫점자리수 만큼 표시

-- FORMAT(숫자, 소수점 자릿수)
select format(1234567.123456,4);
select format(1234567.123456,2);

- BIN(숫자)

• 숫자 10진수 -> 숫자 2진수 로 변환 

- HEX(숫자)

• 숫자 10진수 -> 숫자 16진수 로 변환 (4bit)

- OCT(숫자)

• 숫자 10진수 -> 숫자 8진수 로 변환 (3bit)

-- BIN(숫자), HEX(숫자), OCT(숫자)
select bin(31), hex(31), oct(31);

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

• 기준 문자열의 위치부터 길이만큼 지우고 삽입할 문자열을 끼워 넣는다.

-- INSERT(기준문자열, 위치, 길이 ,삽입할 문자열)
select insert('abcdefghi', 3, 4, '@@@@'), insert('abcdefghi',5,2,'@@@@');

- LEFT(문자열, 길이)

• 왼쪽에서 문자열의 길이만큼 반환

- RIGHT(문자열, 길이)

• 오른쪽에서 문자열의 길이만큼 반환

select left('abcdefghi',3), right('abcdefghi',3);

- UPPER(문자열)

• 소문자를 대문자로 변경하여 반환한다.

- LOWER(문자열)

• 대문자를 소문자로 변경하여 반환한다.

-- UPPER(문자열), LOWER(문자열)
select upper('abcdEFG'), lower('abcdEFG');

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

• 문자열을 길이만큼 늘린 후 왼쪽의 빈공간에 채울문자열로 채운다.

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

• 문자열을 길이만큼 늘린 후 오른쪽의 빈공간에 채울문자열로 채운다.

-- LPAD(문자열, 길이, 채울 문자열), RPAD(문자열, 길이, 채울 문자열)
select lpad('LPAD',8,'#'),rpad('RPAD',8,'#');

- LTRIM(문자열)

• 문자열의 왼쪽 공백을 제거하며 중간의 공백은 제거되지 않는다.

- RTRIM(문자열)

• 문자열의 오른쪽 공백을 제거하며 중간의 공백은 제거되지 않는다.

- TRIM(문자열)

• 문자열의 앞, 뒤 공백을 모두 제거한다.

- TRIM(방향 자를_문자열 FROM 문자열)

• 방향은 LEADING(앞), BOTH(양쪽), TRAILING(뒤)로 표시한다.

-- LTRIM(문자열), RTRIM(문자열), TRIM(문자열), TRIM(방향 자를_문자열 FROM 문자열)
select ltrim('  LTRIM'), rtrim('RTRIM  '), trim('  TRIM  ');
select trim(leading '#' from '#TRIM#'),trim(both '#' from '#TRIM#'),trim(trailing '#' from '#TRIM#');

- REPEAT( 문자열, 횟수)

• 문자열을 횟수만큼 반복하여 출력한다.

- REPLACE(문자열, 원래 문자열, 바꿀 문자열)

• 문자열에서 원래 문자열을 찾아서 바꿀 문자열로 바꾼다.

- REVERSE(문자열)

• 문자열의 순서를 거꾸로 바꾼다.

-- REPEAT( 문자열, 횟수), REPLACE(문자열, 원래 문자열, 바꿀 문자열), REVERSE(문자열)
select repeat('반복',3), replace('바뀌기 전','전','후'), reverse('abcdefg');

- SPACE(길이)

• 길이 만큼의 공백을 반환한다.

-- SPACE(길이)
select concat('공백추가',space(10),'10칸 추가됨');

- SUBSTRING(문자열, 시작위치, 길이), SUBSTRING(문자열 FROM 시작위치 FOR 길이)

• 시작위치부터 길이만큼 문자를 반환한다.
• 길이가 생략되면 문자열의 끝까지 반환한다.

-- SUBSTRING(문자열, 시작위치, 길이), SUBSTRING(문자열 FROM 시작위치 FOR 길이)
select substring('가나다라마바사',3,2), substring('가나다라마바사' from 3 for 2);
/* substring(), substr(), mid()는 동일한 문자 추출 함수라고 볼 수 있다. */

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

• 문자열에서 구분자가 왼쪽부터 횟수 번째 까지 나오면 그 이후의 오른쪽은 버린다.
• 횟수가 음수이면 오른쪽부터 세고 왼쪽을 버린다.

-- SUBSTRING_INDEX(문자열, 구분자, 횟수)
select substring_index('cafe.naver.com','.',2); -- 횟수가 양수이면 구분자 오른쪽을 버린다.
select substring_index('cafe.naver.com','.',-2); -- 횟수가 음수이면 구분자의 왼쪽을 버린다.

 

수학함수

- ABS(숫자)

• 숫자의 절댓값을 계산한다.

- ACOS(숫자), ASIN(숫자), ATAN(숫자), ATAN2(숫자1, 숫자2), SIN(숫자), COS(숫자), TAN(숫자)

• 삼각 함수와 관련된 함수 제공한다.

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

• 각각 올림, 내림, 반올림을 계산한다.

-- CEILING(숫자), FLOOR(숫자), ROUND(숫자)
select ceiling(4.2), ceil(4.2), floor(4.7), round(4.3);

- CONV(숫자, 원래 진수, 변환할 진수)

• 숫자를 원래 진수에서 변환할 진수로 계산한다.

-- CONV(숫자, 원래 진수, 변환할 진수)
select conv('A4',16,2), conv(100,10,8);

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

• 라디안 값을 각도값으로, 각도값을 라디안 값으로 변환하며, PI( )는 3.141592를 반환한다.

-- DEGREES(숫자), RADIANS(숫자), PI ( )
select degrees(pi()), radians(180);

- EXP(X), LN(숫자), LOG(숫자), LOG(밑수, 숫자), LOG2(숫자), LOG10(숫자)

• 지수, 로그와 관련된 함수 제공한다.

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

• 숫자1을 숫자2로 나눈 나머지 값을 구한다.

-- DEGREES(숫자), RADIANS(숫자), PI ( )
select degrees(pi()), radians(180);

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

• 거듭제곱값 및 제곱근을 구한다.

-- POW(숫자1, 숫자2), SQRT(숫자)
select pow(2,3), sqrt(9);

- RAND( )

• 0 이상 1 미만의 실수 구한다.
• ‘m<= 임의의 정수 < n’를 구하고 싶다면 FLOOR(m + (RAND( ) * (n-m) ) 사용한다. (랜덤 수 구하기)

-- RAND( ) :랜던 값을 구할때 자주 사용된다.
select rand(), floor(1+(rand()*6));

- SIGN(숫자)

• 숫자가 양수, 0, 음수인지 판별하 결과는 1, 0, -1 셋 중에 하나 반환한다.

-- SIGN(숫자)
select sign(100), sign(0), sign(-23.4);

- TRUNCATE(숫자, 정수)

• 숫자를 소수점을 기준으로 정수 위치까지 구하고 나머지는 버린다.

-- TRUNCATE(숫자, 정수)
select truncate(12345.12345,2); -- 12345.12 출력
select truncate(12345.12345,-2); -- 12300 출력

 

날짜 및 시간 함수

- ADDDATE(날짜, 차이)

• 날짜를 기준으로 차이를 더한 날짜를 구한다.
• 날짜의 이후 날짜를 구할 때 사용한다.

-  SUBDATE(날짜, 차이)

• 날짜를 기준으로 차이를 뺀 날짜를 구한다.
• 날짜의 이전 날짜를 구할 때 사용한다.

-- ADDDATE(날짜, 차이), SUBDATE(날짜, 차이)
select adddate('2023-02-07', interval 31 day),adddate('2023-02-07', interval 1 month);
select subdate('2023-02-07', interval 31 day),subdate('2023-02-07', interval 1 month);
/* day, month 계산에 따라 결과다 다르게 나올 수 있다. */

- ADDTIME(날짜/시간, 시간)

• 날짜/시간을 기준으로 시간을 더한 결과를 구한다.

- SUBTIME(날짜/시간, 시간)

• 날짜/시간을 기준으로 시간을 뺀 결과를 구한다.

-- ADDTIME(날짜/시간, 시간), SUBTIME(날짜/시간, 시간) -- 날짜를 제외하고 시간만 계산할 수 도 있다.
select addtime('2023-02-07 11:22:59','1:1:1'),addtime('11:22:59','2:10:10'); 
select subtime('2023-02-07 11:22:59','1:1:1'),subtime('11:22:59','2:10:10');

- CURDATE( )

• 현재 연-월-일 을 반환한다.

- CURTIME( )

• 현재 시 : 분 : 초 를 반환한다.

- NOW( ), SYSDATE( )

• 현재 ‘연-월-일 시 : 분 : 초 를 반환한다.

-- CURDATE( ), CURTIME( ), NOW( ), SYSDATE( )
select curdate(),curtime(),now(),sysdate();

- YEAR(날짜), MONTH(날짜), DAY(날짜), HOUR(시간), MINUTE(시간), SECOND(시간), MICROSECOND(시간)

• 날짜 또는 시간에서 연, 월, 일, 시, 분, 초, 밀리초 구한다.

-- YEAR(날짜), MONTH(날짜), DAY(날짜), HOUR(시간), MINUTE(시간), SECOND(시간), MICROSECOND(시간)
select year(curdate()),month(curdate()),dayofmonth(curdate());
select hour(curtime()),minute(curtime()), second(curtime()), microsecond(curtime());

- DATE( ), TIME( )

• DATETIME 형식에서 연-월-일 및 시 : 분 : 초만 추출한다.

-- DATE( ), TIME( )
select date(now()), time(now());

- DATEDIFF(날짜1, 날짜2)

• 날짜1과 날짜2의 차이 일수를 구한다.

- TIMEDIFF(날짜1 또는 시간1, 날짜1 또는 시간2)

• 시간 차이를 구한다.

-- DATEDIFF(날짜1, 날짜2), TIMEDIFF(날짜1 또는 시간1, 날짜1 또는 시간2)
select datediff('2023-03-31',now());
select timediff('23:23:59','11:38:29');

- DAYOFWEEK(날짜)

• 요일(1:일, 2:월~7:토)을 숫자로 반환한다.

- MONTHNAME( )

• 현재 몇 월인지 달의 이름을 구한다.

- DAYOFYEAR(날짜)

• 1년 중 몇 번째 날짜인지 구한다.

-- DAYOFWEEK(날짜), MONTHNAME( ), DAYOFYEAR(날짜)
select dayofweek(curdate()),monthname(curdate()),dayofyear(curdate());

- LAST_DAY(날짜)

• 주어진 날짜의 해당 달에서 마지막 날짜를 구한다.

-- LAST_DAY(날짜)
select last_day('2024-02-02');

- MAKEDATE(연도, 정수)

• 연도에서 정수만큼 지난 날짜를 구한다.

-- MAKEDATE(연도, 정수)
select makedate(2025, 32); -- 2025-02-01 반환

- MAKETIME(시, 분, 초)

• 시, 분, 초를 이용해서 ‘시 : 분 : 초’의 TIME 형식 만듦

-- MAKETIME(시, 분, 초)
select maketime(11, 49, 59); -- 11:49:59 반환

- PERIOD_ADD(연월, 개월수)

• 연월에서 개월만큼의 개월이 지난 연월을 구한다.

- PERIOD_DIFF(연월1, 연월2)

• 연월1과 연월2의 차이 개월수를 구한다

-- PERIOD_ADD(연월, 개월수), PERIOD_DIFF(연월1, 연월2)
select period_add(202501,11), period_diff(202501, 202312); --202512 | 13 반환

- QUARTER(날짜)

• 날짜가 4분기 중에서 몇 분기인지를 구한다. (1분기:1~3월, 2분기:4~6월, 3분기:7~9월, 4분기:10~12월)

-- QUARTER(날짜)
select quarter('2025-07-07');

- TIME_TO_SEC(시간)

• 시간을 초 단위로 구함

-- TIME_TO_SEC(시간)
select time_to_sec('12:11:10'); -- 43870

 

 

시스템 정보 함수

- USER()

• 현재 접속한 사용자의 정보를 반환한다.

- DATABASE()

• 현재 선택된 데이터베이스를 반한다.

-- USER(), DATABASE()
select current_user(), database();

- FOUND_ROWS()

• 바로 앞의 select문을 실행하여 조회된 행의 개수를 구한다.

-- FOUND_ROWS()
select * from usertbl; -- 10개의 데이터 출력
select found_rows(); -- 10 반환

- ROW_COUNT()

• 바로 앞의 INSERT, UPDATE, DELETE 문에서 입력, 수정, 삭제된 행의 개수를 반환한다.
• CREATE, DROP문은 0을 반환하며, SELECT문은 -1을 반환한다.

-- ROW_COUNT()
update buytbl set price = price*2;
select row_count(); /* update문과 동시에 실행시켜야 원하는 결과를 얻을 수 있음 */

- VERSION()

현재 MySQL 의 버전을 반환한다.

-- VERSION()
select version();

- SLEEP(초)

입력한 초 만큼 일시정지를 유지한다.

-- SLEEP(초)
select sleep(5);
select '5초 후에 보여지는 항목'; /* sleep과 select을 동시에 실행시킬것 */

---

http://dev.mysql.com/doc/refman/8.0/en/functions.html 에서 함수를 더 자세히 확인해 볼 수 있다.