Day04-3. MySQL 내장 함수

제어 흐름 함수

• 프로그램의 흐름을 제어함

IF (수식 , 참 , 거짓)
–  수식이 참 또는 거짓인지 결과에 따라서 2 중 분기

 

• IFNULL(수식 1, 수식 2)
– 수식 1 이 NULL 이 아니면 수식 1 이 반환
– 수식 1 이 NULL 이면 수식 2 가 반환

 

• NULLIF(수식 1, 수식 2)
– 수식 1 과 수식 2 가 같으면 NULL 을 반환
– 다르면 수식 1 을 반환

 

-- 수식1이 NULL 이 아니면 수식1이 반환, 수식1이 NULL이면 수식2가 반환
SELECT IFNULL(NULL, '널이군요'), IFNULL(100, '널이군요'); -- 널이군요 / 100 출력

-- 수식1과 수식2가 같으면 NULL 반환, 다르면 수식1 반환
SELECT NULLIF(100, 100), NULLIF(200, 100); -- NULL / 200 출력

-- SWITCH - CASE가 CASE-WHEN으로 바뀐 거라고 생각하자.

 

• CASE ~ WHEN ~ ELSE ~ END
– CASE 는 내장 함수는 아니며 연산자 Operator 로 분류
– 다중 분기에 사용

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

 

문자열 함수

• 문자열 함수는 문자열을 조작 . 활용도 높음

 

• ASCII ( 아스키 코드 ), 숫자
– 문자의 아스키 코드값을 돌려주거나 숫자의 아스키 코드값에 해당하는 문자를 돌려줌

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

 

• BIT_LENGTH(문자열), CHAR_ 문자열), 문자열
– 할당된 Bit 크기 또는 문자 크기를 반환
– CHAR_LENGTH( ) 는 문자의 개수 반환
– LENGTH( ) 는 할당된 Byte 수 반환

SELECT BIT_LENGTH('abc'), CHAR_LENGTH('abc'), LENGTH('abc');
SELECT BIT_LENGTH('가나다'), CHAR_LENGTH('가나다'), LENGTH('가나다');

 

• CONCAT( 문자열 1, 문자열 2,…), CONCAT_WS( 문자열 1, 문자열 2,…)
– 문자열을 이어줌
– CONCAT_WS( ) 는 구분자와 함께 문자열을 이어주는 역할

 

• E LT( 위치 , 문자열 1, 문자열 2, …), FIELD( 찾을 문자열 , 문자열 1, 문자열
2, …), FIND_IN_SET ( 찾을 문자열 , 문자열 리스트 ), 기준 문자열
, 부분 문자열 ), 부분 문자열 , 기준 문자열

 

– ELT( ) 는 위치 번째에 해당하는 문자열 반환
– FIELD( ) 는 찾을 문자열의 위치를 찾아 반환  없으면 0
– FIND_IN_SET( ) 은 찾을 문자열을 문자열 리스트에서 찾아 위치 반환
   » 문자열 리스트는 콤마 로 구분되어 있고 공백이 없어야 함
– INSTR( ) 는 기준 문자열에서 부분 문자열 찾아 그 시작 위치 반환
– LOCATE( ) 는 INSTR( ) 와 동일하지만 파라미터의 순서가 반대

 

SELECT CONCAT_WS('/', '2025', '01', '01'); -- 2025/01/01
SELECT ELT(2, '하나', '둘', '셋'), FIELD('둘', '하나', '둘', '셋'), 
FIND_IN_SET('둘', '하나,둘,셋'), INSTR('하나둘셋', '둘'), LOCATE('둘', '하나둘셋');

 

 

• FORMAT( 숫자 , 소수점 자릿수)
– 숫자를 소수점 아래 자릿수까지 표현
– 1000 단위마다 콤마 표시해 줌

 

• BIN(숫자), HEX(숫자), OCT(숫자)
– 2 진수 , 16 진수 , 8 진수의 값을 반환

SELECT FORMAT(123456.123456, 4); -- 소숫점 넷째자리까지 출력
SELECT BIN(31), HEX(31), OCT(31);

 

 

 

• INSERT( 기준 문자열 , 위치 , 길이 , 삽입할 문자열
– 기준 문자열의 위치부터 길이만큼 지우고 삽입할 문자열 끼워 넣음

 

• LEFT( 문자열 , 길이 ), 문자열 , 길이
– 왼쪽 또는 오른쪽에서 문자열의 길이만큼 반환

SELECT INSERT('abcdefghi', 3, 4, '@@@@'), INSERT('abcdefghi', 3, 2, '@@@@');
SELECT LEFT('abcdefghi', 3), RIGHT('abcdefghi', 3);

 

 

• UCASE(문자열), LCASE(문자열)
– 소문자를 대문자로 // 대문자를 소문자로 변경
• UPPER(문자열), LOWER(문자열)
– 소문자를 대문자로 // 대문자를 소문자로 변경

SELECT LOWER('abcdEFGH'), UPPER('abcdEHGH');

 

 

• LPAD(문자열 , 길이 , 채울 문자열), RPAD(문자열 , 길이 , 채울 문자열)
– 문자열을 길이만큼 늘린 후에 빈 곳을 채울 문자열로 채움

SELECT LPAD('이것이', 5, '##'), RPAD('이것이', 5, '##');

 

• LTRIM(문자열), RTRIM(문자열)
– 문자열의 왼쪽 오른쪽 공백을 제거
– 중간의 공백은 제거되지 않음

SELECT TRIM('     이것이     '), TRIM(BOTH 'ㅋ' FROM 'ㅋㅋㅋ재밌어요.ㅋㅋㅋ');

 

• TRIM( 문자열 ), TRIM(방향 자를_문자열  FROM 문자열)
– TRIM( 문자열 은 문자열의 앞뒤 공백을 모두 없앰
– TRIM( 방향 자를 문자열 FROM 문자열 ) 에서 방향은 LEADING(앞), BOTH(양쪽), TRAILING(뒤) (으)로 표시

 

 

 

• REPEAT(문자열 , 횟수)
– 문자열을 횟수만큼 반복

 

• REPLACE(문자열 , 원래 문자열 , 바꿀 문자열)
– 문자열에서 원래 문자열을 찾아서 바꿀 문자열로 바꿈

 

• REVERSE(문자열)
– 문자열의 순서를 거꾸로 바꿈

 

• SPACE(길이)
– 길이만큼의 공백을 반환

 

• SUBSTRING(문자열 , 시작위치 , 길이) 또는 SUBSTRING(문자열 FROM 시작위치 FOR 길이)
– 시작위치부터 길이만큼 문자를 반환
– 길이가 생략되면 문자열의 끝까지 반환

 

• SUBSTRING_INDEX(문자열 , 구분자 , 횟수)
– 문자열에서 구분자가 왼쪽부터 횟수 번째까지 나오면 그 이후의 오른쪽은 버림
– 횟수가 음수면 오른쪽부터 세고 왼쪽을 버림

SELECT REPEAT('이것이', 3);
SELECT REPLACE('이것이 MySQL이다', '이것이', 'THIS IS');

SELECT REVERSE('MySQL');

SELECT CONCAT('이것이', SPACE(10), 'MySQL이다');

SELECT SUBSTRING('대한민국만세', 3, 2);

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

 

 

 

 

수학 함수

• ABS(숫자)
– 숫자의 절댓값 계산

 

• ACOS(숫자), ASIN(숫자), ATAN(숫자), ATAN2(숫자 1, 숫자 2), SIN(숫자), COS(숫자), TAN(숫자)
– 삼각 함수와 관련된 함수 제공

 

• CEILING(숫자), FLOOR(숫자), ROUND(숫자)
– 올림 , 내림 , 반올림 계산

 

 

• CONV(숫자 , 원래 진수 , 변환할 진수)
– 숫자를 원래 진수에서 변환할 진수로 계산

 

• DEGREES( 숫자 ), 숫자 ), PI ( )
– 라디안 값을 각도값으로 , 각도값을 라디안 값으로 변환

 

• EXP(X), LN(숫자), LOG(숫자), LOG(밑수, 숫자), LOG2(숫자), LOG10(숫자)
– 지수 , 로그와 관련된 함수 제공

 

• MOD(숫자 1, 숫자 2)  또는   숫자 1 % 숫자 2   또는   숫자 1 MOD 숫자 2
– 숫자 1 을 숫자 2 로 나눈 나머지 값을 구함

 

• POW(숫자 1, 숫자 2), SQRT( 숫자)
– 거듭제곱값 및 제곱근을 구함

SELECT ABS(-100); -- 100 출력
SELECT CEILING(4.3), FLOOR(4.7), ROUND(4.7);
SELECT CONV('AA', 16, 2), CONV(100, 10, 8);
SELECT DEGREES(PI()), RADIANS(180);
SELECT MOD(150, 10), 157 % 10, 157 MOD 10;
SELECT POW(2,3), SQRT(9);

 

 

• RAND( )
– RAND( ) 는 0 이상 1 미만의 실수 구함
– m<= 임의의 정수 < 를 구하고 싶다면 FLOOR(m + (RAND( ) * (n m) ) 사용

 

• SIGN( 숫자)
– 숫자가 양수 , 0, 음수인지 판별
– 결과는 1, 0, 1 셋 중에 하나 반환

 

• TRUNCATE(숫자 , 정수)
– 숫자를 소수점을 기준으로 정수 위치까지 구하고 나머지는 버림

SELECT RAND(), FLOOR( 1 + (RAND() * 5 ) );
SELECT SIGN(100), SIGN(0), SIGN(-100.123);
SELECT TRUNCATE(12345.12345, 2), TRUNCATE(12345.12345, -2);

 

 

날짜 및 시간 함수

• ADDDATE(날짜 , 차이), SUBDATE(날짜 , 차이)
– 날짜를 기준으로 차이를 더하거나 뺀 날짜 구함

 

• ADDTIME(날짜 시간 , 시간), SUBTIME(날짜 시간 , 시간)
– 날짜 시간을 기준으로 시간을 더하거나 뺀 결과를 구함

 

• CURDATE( ), CURTIME( ), NOW( ), SYSDATE( )
– CURDATE( ) 는 현재 연-월-일
– CURTIME( ) 은 현재 시:분:초
– NOW( ) 와 SYSDATE( ) 는 현재 ‘연-월-일 시:분:초'

 

• YEAR(날짜), MONTH(날짜), DAY(날짜), HOUR(시간), MINUTE(시간), SECOND(시간), MICROSECOND(시간)
– 날짜 또는 시간에서 연 , 월 , 일 , 시 , 분 , 초 , 밀리 초 구함

 

SELECT ADDDATE('2023-03-13', INTERVAL 31 DAY), ADDDATE('2023-08-08', INTERVAL 5 MONTH);
SELECT YEAR(CURDATE()), MONTH(CURDATE()), DAYOFMONTH(CURDATE());
SELECT HOUR(CURTIME()), MINUTE(CURRENT_TIME()), SECOND(CURRENT_TIME), MICROSECOND(CURRENT_TIME);

 

• DATE( ), TIME( )
– DATETIME 형식에서 연-월-일 및 시:분:초만 추출

 

• DATEDIFF( 날짜 1, 날짜 2), TIMEDIFF( 날짜 1 또는 시간 1, 날짜 1 또는 시간 2)
– DATEDIFF( ) 는 날짜 1 - 날짜 2 의 일수를 결과로 구함

 

• DAYOFWEEK( 날짜 ), MONTHNAME( ), 날짜
– 요일 (일 , 월 ~ 토) 및 1 년 중 몇 번째 날짜인지 구함

 

 

• LAST_DAY( 날짜)
– 주어진 날짜의 달의 마지막 날짜를 구함 ( 그 달이 며칠까지?)

 

• MAKEDATE(연도 , 정수)
– 연도에서 정수만큼 지난 날짜 구함

 

• MAKETIME(시 , 분 , 초)
– 시 , 분 , 초를 이용해서 ‘시 분 초’의 TIME 형식 만듦

SELECT DATE(NOW()), TIME(NOW());
SELECT DATEDIFF('2025-01-01', NOW()), TIMEDIFF('23:23:59', '12:11:10');
SELECT DAYOFWEEK(CURDATE()), MONTHNAME(CURDATE()), DAYOFYEAR(CURDATE());
SELECT LAST_DAY('2025-02-01');
SELECT MAKEDATE(2025, 32);
SELECT MAKETIME(12, 11, 10);
SELECT PERIOD_ADD(202501, 11), PERIOD_DIFF(202501, 202312);

 

• PERIOD_ADD(연월 , 개월수), PERIOD_DIFF(연월 1, 연월 2)
– PERIOD_ADD( ) 는 연월에서 개월만큼의 개월이 지난 연월 구함
– PERIOD_DIFF( ) 는 연월 1 연월 2 의 개월수 구함

 

• QUARTER(날짜)
– 날짜가 4 분기 중에서 몇 분기인지를 구함

 

• TIME_TO_SEC(시간)
– 시간을 초 단위로 구함

SELECT PERIOD_ADD(202501, 11), PERIOD_DIFF(202501, 202312);
SELECT QUARTER('2025-07-07');
SELECT TIME_TO_SEC('12:11:10');

 

시스템 정보 함수

• 시스템의 정보를 출력하는 함수 제공

 

• USER( ), DATABASE( )
– 현재 사용자 및 현재 선택된 데이터베이스 출력

 

• FOUND_ROWS( )
– 바로 앞의 SELECT 문에서 조회된 행의 개수 구함

 

• VERSION( )
– 현재 MySQL 의 버전

 

• SLEEP(초)
– 쿼리의 실행을 잠깐 멈춤

 

-- 시스템 정보 함수
SELECT CURRENT_USER(), DATABASE();

USE sqldb;
SELECT * FROM usertbl;
SELECT FOUND_ROWS();

USE sqldb;
UPDATE buytbl SET price = price*2;
SELECT ROW_COUNT();

-- 두 개 한꺼번에 실행.
SELECT SLEEP(5);
SELECT '5초후에 이게 보여요';