데이터 타입 변경/ MySQL 내장함수

 데이터 형의 경우에는 숫자, 문자, 날짜 및 시간, 대용량 파일 등 다양한 카테고리가 존재한다. 단지 데이터베이스에 입력하거나 결과물을 출력할 때 알맞은 데이터 형을 매번 찾아서 사용하면 된다. 이번 게시글에서는 데이터 형식들을 쿼리에서 데이터 타입을 어떻게 변경하는지에 대한 간단한 예시를 남기고, MySQL에 존재하는 내장함수들을 알아보고자 한다.

 

데이터 타입 변경법

-- 데이터 형 변환에는 CAST와 CONVERT가 사용된다

USE sqldb ;
SELECT AVG(amount) AS '평균 구매 개수' FROM buytbl ; 
-- 평균값이 소수점을 가진 실수로 나왔으므로 정수로 보기 위해서 변환을 해준다

SELECT CAST(AVG(amount) as SIGNED INTEGER) AS '평균 구매 개수' FROM buytbl ;
	-- OR --
SELECT CONVERT(AVG(amount) , SIGNED INTEGER) AS '평균 구매 개수' FROM buytbl ;

-- 쿼리 결과를 보기좋게 변경할 수도 있다

SELECT num, 
		CONCAT(CAST(price AS CHAR(10)), 'X', CAST(amount AS CHAR(4)), '=') AS '단가X수량',
		price*amount AS '구매액'
    FROM buytbl ; 		
-- CONCAT(데이터 값, 데이터 값)은 어떤 형태의 데이터를 넣든 문자열로 변환하여 나열해준다

 CAST와 CONVERT를 통해 원래의 데이터 형식에서 다른 데이터 형으로 변경하여 출력값을 더 간결하게 보이도록 하는 것이다.

 

내장함수 - 문자열 함수 모음

 

 문자열 함수는 문자열, 즉 글과 같은 데이터를 조작하는 함수로 사용된다. 활용도가 높아 처음에 배치했다.

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

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

SELECT CONCAT_WS('/', '2025', '01', '01') ;

SELECT ELT(2, '하나', '둘', '셋'), FIELD('둘', '하나', '둘', '셋'), 
		FIND_IN_SET('둘', '하나,둘,셋'), INSTR('하나둘셋', '둘'), LOCATE('둘', '하나둘셋') ; -- LOCATE와 POSITION은 동일한 함수다
        
SELECT FORMAT(123456.123456, 4) ;

SELECT BIN(31), HEX(31), OCT(31) ;

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

SELECT LEFT('abcdefghi', 3), RIGHT('abcdefghi', 3) ;

SELECT LOWER('abcdEFGH'), UPPER('abcdEFGH') ; -- LOWER은 LCASE, UPPER은 UCASE와 동일한 함수이다

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

SELECT LTRIM('     이것이'), RTRIM('이것이      ') ;

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

SELECT REPEAT ('이것이', 3) ;

SELECT REPLACE('이것이 MySQL이다.', '이것이', 'This is') ;

SELECT REVERSE ('MySQL') ;

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

SELECT SUBSTRING('대한민국만세', 3, 2);
SELECT SUBSTRING('대한민국만세' FROM 3 FOR 2) AS 'Substring 테스트';

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

 

내장함수 - 제어 흐름 함수

 

 제어 흐름 함수는 프로그램의 흐름을 제어하는 용도로 사용된다.

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

SELECT IFNULL (NULL, '널이군요'), IFNULL (100, '널이군요') ;

SELECT NULLIF (100,100), NULLIF (200,100) ;

-- CASE는 내장 함수는 아니지만 연산자로 분류 
SELECT CASE 10
			WHEN 1 THEN '일'
            WHEN 5 THEN '오'
            WHEN 10 THEN '십'
            ELSE '모름'
		END AS 'CASE연습';

 

내장함수 - 수학 함수

 

 내장함수 중에서 수학적인 계산 및 기능을 담당하는 수학 함수도 있다.

SELECT ABS(-100) ;

SELECT COS(73) ; -- COS, SIN, TAN 등 삼각함수 기능 제공

SELECT CEILING(4.7), FLOOR(4.7), ROUND(4.7) ;

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

SELECT DEGREES(PI()), RADIANS(180) ;

SELECT LOG(2,16), LOG2(8) ; -- EXP, LN, LOG 등과 같은 지수, 로그 함수 제공

SELECT MOD(157,10), 157 % 10, 157 MOD 10 ; -- 나눗셈 후 나머지 값

SELECT POW(2,3), SQRT(9) ; -- 제곱 근, 루트 

SELECT RAND(), FLOOR(1 + (RAND() * (7-1)) ) ; -- 0과 1 사이에서 랜덤한 숫자, 특정 범위 내 랜덤 숫자

SELECT SIGN(100), SIGN(0), SIGN(-100.2314) ;

SELECT TRUNCATE(12345.12345, 3), TRUNCATE(12345.12345, -2) ;

 

내장함수 - 날짜 및 시간

SELECT ADDDATE('2025-01-01', INTERVAL 30 DAY), ADDDATE('2025-01-01', INTERVAL 1 MONTH) ;
SELECT SUBDATE('2025-01-01', INTERVAL 30 DAY), SUBDATE('2025-01-01', INTERVAL 1 MONTH) ;

SELECT ADDTIME('2025-01-01 23:59:59', '1:1:1'), ADDTIME('15:00:00', '2:10:10') ;
SELECT SUBTIME('2025-01-01 23:59:59', '1:1:1'), SUBTIME('15:00:00', '2:10:10') ;

SELECT CURDATE() ;
SELECT CURTIME() ;
SELECT NOW() ;
SELECT SYSDATE() ;

SELECT DATEDIFF('2025-01-01', NOW()), TIMEDIFF('23:59: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) ;

SELECT QUARTER('2025-07-07') ;

SELECT TIME_TO_SEC('12:11:10') ;

 

내장함수 - 시스템 정보 함수

 

 시스템 내부 정보를 추출하는 함수이다.

SELECT CURRENT_USER(), DATABASE() ;

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

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

SELECT VERSION() ;

SELECT SLEEP(5);
SELECT '5초 후에 이게 보여요' ;

 

 MySQL은 수많은 다양한 기능에 맞게 함수를 제공한다. 이곳에 다 나열하는 것은 너무 비효율적이므로 참조할 수 있는 공식 사이트의 링크를 남기겠다. 위에서 나열한 MySQL 내장함수는 본인이 카테고리 별로 분류된 함수들을 실습하고 SQL문을 기록으로 남긴 것이다. (필자의 MySQL 워크밴치 버전은 8.0.17이다.)

 

MySQL 내장 함수