데이터 형식과 형 변환
데이터 형식 변환 함수
가장 일반적으로 사용되는 데이터 형식 변환과 관련해서는 CAST(), CONVERT() 함수를 사용한다. CAST(), CONVERT()는 형식만 다를 뿐 거의 비슷한 기능을 한다.
형식:
CAST ( expression AS 데이터형식 [(길이)] )
CONVERT ( expression , 데이터형식 [(길이)] )데이터 형식 중에서 가능한 것은 BINARY, CHAR, DATE, DATETIME, DECIMAL, JSON, SIGNED INTEGER, TIME, UNSIGNED INTEGER 등이다.
사용 예를 보면 좀 더 쉽게 이해될 것이다. 다음은 sqlDB의 구매 테이블(buyTbl)에서 평균 구매 개수를 구하는 쿼리문이다.
USE sqldb;
SELECT AVG(amount) AS '평균 구매 개수' FROM buytbl;결과는 2.9167개가 나왔다.
그런데, 개수이므로 정수로 보기 위해서 다음과 같이 CAST() 함수나 CONVERT()함수를 사용할 수 있다.
SELECT CAST(AVG(amount) AS SIGNED INTEGER) AS '평균 구매 개수' FROM buytbl ;
또는
SELECT CONVERT(AVG(amount) , SIGNED INTEGER) AS '평균 구매 개수' FROM buytbl ;쿼리 실행 결과 평균 구매 개수는 정수 3으로 나온다.
문자형을 DATE로도 바꿀 수 있음
SELECT CAST('2020$12$12' AS DATE);
SELECT CAST('2020/12/12' AS DATE);
SELECT CAST('2020%12%12' AS DATE);
SELECT CAST('2020@12@12' AS DATE);
쿼리 실행 결과 4개 모두 동일.
쿼리의 결과를 보기 좋도록 처리할 때도 사용된다. 단가(price)와 수량(amount)을 곱한 실제 입금액을 표시하는 쿼리는 다음과 같이 사용할 수 있다.
SELECT num, CONCAT(CAST(price AS CHAR(10)), 'X', CAST(amount AS CHAR(4)) , '=') AS '단가X수량',
price*amount AS '구매액'
FROM buytbl ;
암시적인 형 변환
형 변환 방식에는 명시적인 변환과 암시적인 변환, 두 가지가 있다. 명시적인 변환(Explicit conversion)이란 위에서 한 CAST() 또는 CONVERT() 함수를 이용해서 데이터 형식을 변환하는 것을 말한다. 암시적인 변환(Implicit conversion)이란 CAST()나 CONVERT()함수를 사용하지 않고 형이 변환되는 것을 말한다.
SELECT '100' + '200' ; -- 문자와 문자를 더함(정수로 변환되서 연산됨)
SELECT CONCAT('100', '200') ; -- 문자와 문자를 연결(문자로 처리)
SELECT CONCAT(100, '200') ; -- 정수와 문자를 연결(정수가 문자로 변환되서 처리)
SELECT 1 > '2mega' ; -- 정수인 2로 변환되어서 비교
SELECT 3 > '2MEGA' ; -- 정수인 2로 변환되어서 비교
SELECT 0 = 'mega2' ; -- 문자는 0으로 변환됨
*다른 DBMS에서는 암시적인 형 변환의 결과가 MySQL과 다를 수 있다. 예로 어떤 DBMS에서 SELECT '100'+'200'의 결과가 '100200'문자열로 처리되기도 한다.
MySQL 내장 함수
MySQL은 많은 내장 함수를 포함하고 있다. 내장 함수는 크게 제어 흐름 함수, 문자열 함수, 수학 함수, 날짜/시간 함수, 전체 텍스트 검색 함수, 형 변환 함수, XML 함수, 비트 함수, 보안/압축 함수, 정보 함수, 공간 분석 함수, 기타 함수 등으로 나눌 수 있다.
제어 흐름 함수
프로그램의 흐름을 제어한다.
-IF(수식, 참, 거짓)
수식이 참 또는 거짓인지 결과에 따라서 2중 분기한다.
SELECT IF (100>200, '참이다', '거짓이다');거짓이 출력된다.
-IFNull(수식1, 수식2)
수식1이 NULL이면 수식2가 반환되고, 수식1이 NULL이 아니면 수식1이 반환된다.
SELECT IFNULL(NULL, '널이군요'), IFNULL(100, '널이군요');첫 번째는 '널이군요'가 출력되고, 두 번째는 100이 출력된다.
-NULLIF(수식1, 수식2)
수식1과 수식2가 같으면 NULL을 반환하고, 다르면 수식1을 반환한다.
SELECT NULLIF(100,100), NULLIF(200,100);첫 번째는 NULL이, 두 번째는 200이 반환된다.
-CASE~WHEN~ELSE~END
CASE는 내장 함수는 아니며 연산자(Operator)로 분류된다. 다중 분기에 사용될 수 있으므로 내장 함수와 함께 알아두자.
SELECT CASE 10
WHEN 1 THEN '일'
WHEN 5 THEN '오'
WHEN 10 THEN '십'
ELSE '모름'
END AS 'CASE연습';
CASE 뒤의 값이 10이므로 세 번째 WHEN이 수행되어 '십'이 반환된다. 만약 해당하는 사항이 없다면 ELSE 부분이 반환된다. 마지막 END AS뒤에는 출력될 열의 별칭을 써주면 된다.
문자열 함수
문자열을 조작한다.
ASCII(아스키 코드), CHAR(숫자)
문자의 아스키 코드값을 돌려주거나 숫자의 아스키 코드값에 해당하는 문자를 돌려준다.
SELECT ASCII('A'), CHAR(65);65와 'A'를 돌려준다.
*Workbench의 약간의 버그로 CHAR(65)의 결과가 'BLOB'으로 보일 수 있다. 그러면 'BLOB'글자에서 마우스 오른쪽 버튼을 클릭한 후 [Open Value in Viewer]를 선택하고 위쪽의 [Text]탭을 클릭하면 된다. 일반 명령행 모드에서는 정상적으로 'A'로 출력된다.
BIT_LENGTH(문자열), CHAR_LENGTH(문자열), LENGTH(문자열)
할당된 Bit 크기 또는 문자 크기를 반환한다. CHAR_LENGTH()는 문자의 개수를 반환하며 LENGTH()는 할당된 Byte 수를 반환한다.
SELECT BIT_LENGTH('abc'), CHAR_LENGTH('abc'), LENGTH('abc');
SELECT BIT_LENGTH('가나다'), CHAR_LENGTH('가나다'), LENGTH('가나다');MySQL은 기본으로 UTF-8 코드를 사용하기 때문에 영문은 3Byte를, 한글은 3x3=9Byte를 할당한다.
출력결과는 24, 3, 3 / 72, 3, 9
CONCAT(문자열1, 문자열2, ...), CONCAT_WS(구분자, 문자열1, 문자열2, ...)
ELT(위치, 문자열1, 문자열2, ...), FIELD(찾을 문자열, 문자열1, 문자열2, ...), FIND_IN_SET(찾을 문자열, 문자열 리스트), INSTR(기존 문자열, 부분 문자열), LOCATE(부분 문자열, 기준 문자열)
ELT()는 위치 번째에 해당하는 문자열을 반환한다. FIELD()는 찾을 문자열의 위치를 찾아서 반환한다. FIELD()는 매치되는 문자열이 없으면 0을 반환한다. FIND_IN_SET()은 찾을 문자열을 문자열 리스트에서 찾아서 위치를 반환한다. 문자열 리스트는 콤마로 구분되어 있어야 하며 공백이 없어야 한다. INSTR()는 기준 문자열에서 부분 문자열을 찾아서 그 시작 위치를 반환한다. LOCATE()는 INSTR()와 동일하지만 파라미터의 순서가 반대로 되어 있다.
SELECT ELT(2, '하나', '둘', '셋'), FIELD('둘', '하나', '둘', '셋'), FIND_IN_SET('둘', '하나,둘,셋'), INSTR('하나둘셋', '둘'), LOCATE('둘', '하나둘셋');'둘',2,2,3,3을 반환한다.
*LOCATE()와 POSITION()은 동일한 함수다.
FORMAT(숫자, 소수점 자릿수)
숫자를 소수점 아래 자릿수까지 표현한다. 또한 1000 단위마다 콤마(,)를 표시해 준다.
SELECT FORMAT(123456.123456, 4);'123,456.1235'를 반환한다.
BIN(숫자), HEX(숫자), OCT(숫자)
2진수, 16진수, 8진수의 값을 반환한다.
SELECT BIN(31), HEX(31), OCT(31);2진수 11111, 16진수 1F, 8진수 37을 반환한다.
INSERT(기준 문자열, 위치, 길이, 삽입할 문자열)
기준 문자열의 위치부터 길이만큼을 지우고 삽입할 문자열을 끼워 넣는다.
SELECT INSERT('abcdefghi', 3, 4, '@@@@'), INSERT('abcdefghi', 3, 2, '@@@@');'ab@@@@ghi'와 'ab@@@@efghi'를 반환한다.
LEFT(문자열, 길이), RIGHT(문자열, 길이)
왼쪽 또는 오른쪽에서 문자열의 길이만큼 반환한다.
SELECT LEFT('abcdefghi', 3), RIGHT('abcdefghi', 3);'abc'와 'ghi'를 반환한다.
UPPER(문자열), LOWER(문자열)
소문자를 대문자로, 대문자를 소문자로 변경한다.
SELECT LOWER('abcdEFGH'), UPPER('abcdEFGH');'abcdefgh'와 'ABCDEFGH'를 반환한다.
LPAD(문자열, 길이, 채울 문자열), RPAD(문자열, 길이, 채울 문자열)
문자열을 길이만큼 늘린 후에, 빈 곳을 채울 문자열로 채운다.
SELECT LPAD('이것이', 5, '##'), RPAD('이것이', 5, '##');'##이것이'와 '이것이##'를 반환한다.
LTRIM(문자열), RTRIM(문자열)
문자열의 왼쪽/오른쪽 공백을 제거한다. 중간의 공백은 제거되지 않는다.
SELECT LTRIM(' 이것이'), RTRIM('이것이 ');둘다 공백이 제거된 '이것이'를 반환한다.
TRIM(문자열), TRIM(방향 자를_문자열 FROM 문자열)
TRIM(문자열)은 문자열의 앞뒤 공백을 모두 없앤다. TRIM(방향 자를_문자열 FROM 문자열)에서 방향은 LEADING(앞), BOTH(양쪽), TRAILING(뒤)가 나올 수 있다.
SELECT TRIM(' 이것이 '), TRIM(BOTH 'ㅋ' FROM 'ㅋㅋㅋ재밌어요.ㅋㅋㅋ');'이것이'와 '재밌어요.'를 반환한다.
REPEAT(문자열, 횟수)
문자열을 횟수만큼 반복한다.
SELECT REPEAT('이것이', 3);'이것이이것이이것이'를 반환한다.
REPLACE(문자열, 원래 문자열, 바꿀 문자열)
문자열에서 원래 문자열을 찾아서 바꿀 문자열로 바꿔준다.
SELECT REPLACE ('이것이 MySQL이다', '이것이', 'This is');'This is MySQL이다'를 반환한다.
REVERSE(문자열)
문자열의 순서를 거꾸로 만든다.
SELECT REVERSE('MySQL')'LQSyM'을 반환한다.
SPACE(길이)
길이만큼의 공백을 반환한다.
SELECT CONCAT('이것이', SPACE(10), 'MySQL이다');'이것이 MySQL이다'를 반환한다.
SUBSTRING(문자열, 시작위치, 길이) 또는 SUBSTRING(문자열 FROM 시작위치 FOR 길이)
시작 위치부터 길이만큼 문자를 반환한다. 길이가 생략되면 문자열의 끝까지 반환한다.
SELECT SUBSTRING('대한민국만세', 3, 2);'민국'을 반환한다.
SUBSTRING_INDEX(문자열, 구분자, 횟수)
문자열에서 구분자가 왼쪽부터 횟수 번째 나오면 그 이후의 오른쪽은 버린다. 횟수가 음수면 오른쪽부터 세고 왼쪽을 버린다.
SELECT SUBSTRING_INDEX('cafe.naver.com', '.', 2), SUBSTRING_INDEX('cafe.naver.com', '.', -2);'cafe.naver'와 'naver.com'을 반환한다.
수학 함수
ABS(숫자)
숫자의 절댓값을 계산한다.
SELECT ABS(-100);절댓값인 100을 반환한다.
ACOS(숫자),ASIN(숫자),ATAN(숫자),ATAN2(숫자1,숫자2),SIN(숫자),COS(숫자),TAN(숫자)
삼각 함수와 관련된 함수를 제공한다.
CEILING(숫자),FLOOR(숫자),ROUND(숫자)
올림,내림,반올림
SELECT CEILING(4.7), FLOOR(4.7), ROUND(4.7);5,4,5를 반환한다.
*CEILING()과 CEIL()은 동일한 함수다.
CONV(숫자, 원래 진수, 변환할 진수)
숫자를 원래 진수에서 변환할 진수로 계산한다.
SELECT CONV('AA', 16, 2), CONV(100, 10, 8);16진수 AA를 2진수로 변환한 10101010과 100을 8진수로 변환한 144가 반환된다.
DEGREES(숫자),RADIANS(숫자),PI()
라디안 값을 각도값으로, 각도값을 라디안 값으로 변환한다. PI()는 파이값인 3.141592를 반환한다.
SELECT DEGREES(PI()), RADIANS(180);파이의 각도값인 180과 180의 라디안 값이 출력된다.
EXP(X), LN(숫자), LOG(숫자), LOG(밑수, 숫자), LOG2(숫자), LOG10(숫자)
지수, 로그와 관련된 함수를 제공한다.
MOD(숫자1, 숫자2) 또는 숫자1 % 숫자2 또는 숫자1 MOD 숫자2
숫자1을 숫자2로 나눈 나머지 값을 구한다.
SELECT MOD(157, 10), 157 % 10, 157 MOD 10;모두 157을 10으로 나눈 나머지 값 7을 반환한다.
POW(숫자1, 숫자2), SQRT(숫자)
거듭제곱값 및 제곱근을 구한다.
SELECT POW(2,3), SQRT(9);2의 3제곱근과 루트9의 값을 반환한다.
*POW()와 POWER()는 동일한 함수다.
RAND()
RAND()는 0 이상 1 미만의 실수를 구한다. 만약 'm<= 임의의 정수 <n'를 구하고 싶다면 FLOOR (M + (RAND() * (N-M)))을 사용하면 된다.
SELECT RAND(), FLOOR(1 + RAND() * (7-1)) );SIGN(숫자)
숫자가 양수, 0, 음수인지를 구한다. 결과는 1, 0, -1 셋 중에 하나를 반환한다.
SELECT SIGN(100), SIGN(0), SIGN(-100.123);TRUNCATE(숫자,정수)
숫자를 소수점을 기준으로 정수 위치까지 구하고 나머지는 버린다.
SELECT TRUNCATE(12345.12345, 2), TRUNCATE(12345.12345, -2);12345.12와 12300을 반환한다.
날짜 및 시간 함수
ADDDATE(날짜, 차이), SUBDATE(날짜, 차이)
SELECT ADDDATE('2025-01-01', INTERVAL 31 DAY), ADDDATE('2025-01-01', INTERVAL 1 MONTH);
SELECT SUBDATE('2025-01-01', INTERVAL 31 DAY), SUBDATE('2025-01-01', INTERVAL 1 MONTH);31일 후 또는 한달 후인 '2025-02-01'과 31일 전 또는 한달 전인 '2024-12-01'을 반환한다.
*ADDDATE()와 DATE_ADD()는 동일한 함수이며 SUBDATE()와 DATE_SUB()도 동일한 함수다.
ADDTIME(날짜/시간, 시간), SUBTIME(날짜/시간, 시간)
날짜/시간을 기준으로 시간을 더하거나 뺀 결과를 구한다.
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');1시간 1분 1초 후인 '2025-01-02 01:01:00'과 2시간 10분 10초 후인 '17:10:10'을 반환한다. 또 1시간 1분 1초 전인 '2025-01-01 22:58:58'과 2시간 10분 10초 전인 '12:49:50'을 반환한다.
CURDATE(), CURTIME(), NOW(), SYSDATE()
CURDATE()는 현재 연-월-일을, CURTIME()은 현재 시:분:초를 구한다. NOW()와 SYSDATE()는 현재 '연-월-일 시:분:초'를 구한다.
*CURDATE(),CURRENT_DATE(),CURRENT_DATE는 모두 동일하며 CURTIME(),CURRENT_TIME(),CURRENT_TIME도 모두 동일하다. NOW(),LOCALTIME,LOCALTIME(),LOCALTIMESTAMP,LOCALTIMESTAMP()도 모두 동일하다.
YEAR(날짜),MONTH(날짜),DAY(날짜),HOUR(시간),MINUTE(시간),SECOND(시간),MICROSECOND(시간)
날짜 또는 시간에서 연,월,일,시,분,초,밀리초를 구한다.
DATE(), TIME()
DATETIME 형식에서 연-월-일 몇 시:분:초만 추출한다.
SELECT DATE(NOW()), TIME(NOW());현재 연-월-일 몇 시:분:초를 반환한다.
DATEDIFF(날짜1, 날짜2), TIMEDIFF(날짜1 또는 시간1, 날짜1 또는 시간2)
DATEDIFF()는 날짜1-날짜2의 일수를 결과로 구한다. 즉, 날짜2에서 날짜1까지 몇 일이 남았는지 구한다. TIMEDIFF()는 시간1-시간2의 결과를 구한다.
DAYOFWEEK(날짜), MONTHNAME(), DAYOFYEAR(날짜)
요일(1:일, 2:월~7:토) 및 1년 중 몇 번째 날짜인지를 구한다.
SELECT DAYOFWEEK(CURDATE()), MONTHNAME(CURDATE()), DAYOFYEAR(CURDATE());현재 요일과 월 이름 그리고 일년 중 몇 일이 지났는지를 반환한다.
LAST_DAY(날짜)
주어진 날짜의 마지막 날짜를 구한다. 주로 그 달이 몇 일까지 있는지 확인할 때 사용한다.
MAKEDATE(연도, 정수)
연도에서 정수만큼 지난 날짜를 구한다.
MAKETIME(시,분,초)
시,분,초를 이용해서 '시:분:초'의 TIME 형식을 만든다.
PERIOD_ADD(연월,개월수), PERIOD_DIFF(연월1,연월2)
PERIOD_ADD()는 연월에서 개월만큼의 개월이 지난 연월을 구한다. 연월은 YYYY 또는 YYYYMM형식을 사용한다. PERIOD_DIFF()는 연월1-연월2의 개월수를 구한다.
QUARTER(날짜)
날짜가 4분기 중에서 몇 분기인지를 구한다.
TIME_TO_SEC(시간)
시간을 초 단위로 구한다.
시스템 정보 함수
USER(),DATABASE()
현재 사용자 및 현재 선택된 데이터베이스를 구한다.
FOUND_ROWS()
바로 앞의 SELECT문에서 조회된 행의 개수를 구한다.
USE sqldb;
SELECT * FROM usertbl;
SELECT FOUND_ROWS();고객 테이블의 10개 행을 조회했으므로 10이 반환된다.
ROW_COUNT()
바로 앞의 INSERT,UPDATE,DELETE문에서 입력,수정,삭제된 행의 개수를 구한다.
CREATE,DROP문은 0을 반환하고, SELECT문은 -1을 반환한다.
USE sqldb;
UPDATE buytbl SET price=price*2;
SELECT ROW_COUNT();구매 테이블의 12개 행을 변경했으므로 12가 반환된다.
VERSION()
현재 MySQL의 버전을 구한다.
SLEEP(초)
쿼리의 실행을 잠깐 멈춘다.
SELECT SLEEP(5);
SELECT '5초후에 이게 보여요';그 외의 함수
비트 함수, 전체 텍스트 검색 함수, 보안 및 압축 함수, MySQL Enterprise암호화 함수, XML함수, 공간 분석 함수, JSON 함수 등이 있지만, 너무 많이 나열하는 것보다는 앞으로 필요할 때 소개하는 것으로 하겠다.
