파트 5-2. 데이터형 변환, 내장 함수

1. 데이터 형식과 형 변환


데이터 형식 변환 함수

가장 일반적으로 사용되는 데이터 형식 변환과 관련해서는 CAST(), CONVERT() 함수를 사용한다. 형식만 다를 뿐 거의 비슷한 기능을 한다.

-- 형식
CAST (expression AS 데이터형식[(길이)])
CONVERT (expression, 데이터형식 [(길이)])

-- 예시
select avg(amount) as '평균 구매 개수' from buytbl; -- 결과가 실수로 출력된다.

-- 둘 중 아무거나 사용해도 된다.
select cast(avg(amount) as signed integer) as '평균 구매 개수' from buytbl; -- avg(amount)를 정수값으로 변환하여 출력
select convert(avg(amount), signed integer) as '평균 구매 개수' from buytbl; -- avg(amount)를 정수값으로 변환하여 출력

-- 날짜로 변환하기
select cast('2020%12%12' as date);

-- 쿼리 결과를 보기 좋게 처리할때도 사용
-- concat 함수는 문자열을 콤마기준으로 합쳐준다.
-- as char(10)의 의미는 문자열 10글자로 바꿔준다.
select num, concat(cast(price as char(10)), 'X',cast(amount as char(4)),'=') as '단가X수량', price*amount as '구매액' from buytbl;

그림1


암시적인 형 변환

형 변환 방식에는 명시적인 변환과 암시적인 변환 두 가지가 있다. 명시적인 변환이란 cast, convert 함수를 이용해서 데이터 형식을 변환하는 것을 말한다. 암시적인 변환이란 cast, convert 함수를 사용하지 않고 형 변환되는 것을 말한다.

select '100' + '200'; -- 문자와 문자열을 더함(정수로 변환되어 연산된다.) -> 300 출력
select concat('100','200'); -- 문자와 문자를 연결(문자로 처리) -> 100200
select concat(100,'200'); -- 정수와 문자를 연결(정수가 문자로 변환되서 처리) ->100200
select 1 > '2mega'; -- 정수인 2로 변환되어서 비교 -> 0
select 3 > '2mega'; -- 정수인 2로 변환되어서 비교 -> 1
select 0 = 'mega2'; -- 문자는 0으로 변환됨  -> 1


2. 제어 흐름 함수


if(수식,참,거짓)

select if (100>200, '참이다','거짓이다'); -- 거짓이다


ifnull(수식1,수식2)

수식1이 널이면 수식2가 반환되고, 수식1이 널이 아니면 수식1이 반환된다.

select ifnull (null,'수식1이널이니까이게반환'), ifnull ('널이아니니까이게반환','hello'); 


nullif(수식1,수식2)

수식1과 수식2가 같으면 널 반환, 다르면 수식1을 반환

select nullif(100,100), nullif(200,100); -- 널, 200


case ~ when ~ else ~ end

case는 내장 함수는 아니며 연산자로 분류된다. 다중 분기에 사용될 수 있으므로 내장 함수와 함께 알아두자.

select case 10
    when 1 then '일'
    when 10 then '십'
    else '모름'
end as 'case연습';


3. 문자열 함수


ascii, char

문자의 아스키 코드값을 돌려주거나 숫자의 아스키 코드값에 해당하는 문자를 돌려준다.

select ascii('A'), char(65); -- 65 , A


bit_length(문자열), char_length(문자열), length(문자열)

할당된 bit 크기 또는 문자 크기를 반환한다. char_length는 문자의 개수를 반환하며, length는 할당된 byte수를 반환한다. MySQL은 기본적으로 UTF-8 코드를 사용하기 때문에 영문은 3바이트, 한글은 9바이트를 할당한다.

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


concat(문자열1,문자열2,…), concat_ws(구분자,문자열1,문자열2,…)

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

select concat_ws('/','2025','01','01'); -- 2025/01/01


elt(위치,문자열1,문자열2,…), field(찾을 문자열, 문자열1,문자열2,..) find_in_set(찾을 문자열,문자열 리스트),instr(기준 문자열,부분 문자열), locate(부분 문자열,기준 문자열)

  • elt는 위치 번째에 해당하는 문자열을 반환한다.
  • field는 찾을 문자열의 위치를 찾아서 반환한다. 없다면 0을 반환한다.
  • find_in_set은 찾을 문자열을 문자열 리스트에서 찾아 위치를 반환한다. 문자열 리스트는 공백이 없어야하고 콤마로 구분되어있어야 한다.
  • instr은 기준 문자열에서 부분 문자열을 찾아서 그 시작 위치를 반환한다.
  • locate는 instr과 동일하지만 파라미터의 순서가 반대로 되어있다.
select elt(2,'하나','둘','셋'), field('둘','하나','둘','셋'), 
find_in_set('둘','하나,둘,셋'), instr('하나둘셋','둘'), 
locate('둘','하나둘셋');
-- 둘 , 2, 2, 3, 3


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

숫자를 소수점 아래 자릿수까지 표현한다. 1000단위마다 콤마로 표시해준다.

select format(123456.123456,4);  -- 123,456.1235


bin(숫자), hex(숫자), oct(숫자)

2진수, 16진수, 8진수의 값을 반환한다.

select bin(31), hex(31),oct(31); -- 11111, 1F, 37


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

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

-- ab@@@@ghi , ab@@@@efghi
select insert('abcdefghi',3,4,'@@@@'), insert('abcdefghi',3,2,'@@@@');


left(문자열, 길이) right(문자열, 길이)

왼쪽 또는 오른쪽에서 문자열의 길이만큼을 반환한다.

-- abc , ghi
select left('abcdefghi',3). right('abcdefghi',3);


upper(문자열), lower(문자열)

소문자를 대문자로, 대문자를 소문자로 변경

select lower('abcdEFG'), upper('abcdefg');


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(문자열,원래문자열,바꿀문자열)

문자열에서 원래 문자열을 찾아서 바꿀 문자열로 바꿔준다.

-- this is mysql이다
select replace ('이것이 mysql이다','이것이','this is');


reverse(문자열)

문자열의 순서를 거꾸로 만든다

-- lqsym
select reverse('mysql');


space(길이)

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

-- 이것이          mysql이다
select concat('이것이',space(10),'mysql이다');


substring(문자열, 시작위치, 길이), substring(문자열 from 시작위치 for 길이)

시작 위치부터 길이만큼 문자를 반환한다. 길이가 생략되면 끝까지 반환한다. mid, substr과 같은 함수이다.

-- 민국
select substring('대한민국만세',3,2);


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

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

select substring_index('cafe.naver.com','.',2); -- cafe.naver
select substring_index('cafe.naver.com','.',-2) ;-- naver.com


4. 수학 함수


abs(숫자)

절댓값을 계산한다.

select abs(-100); -- 100


ceiling(숫자), floor(숫자), round(숫자)

올림, 내림, 반올림

-- 5,4,5
select ceiling(4.7) , floor(4.7), round(4.7);


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

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

select conv(100,10,8) -- 10진수 100을 8진수로 변환 -> 144


pow(숫자1,숫자2), sqrt(숫자)

거듭제곱값 및 제곱근

select pow(2,3), sqrt(9); -- 2의 3제곱, 루트9


rand()

0이상 1미만의 실수를 구한다. 만약 m<= 임의의 정수 <n 를 구하고 싶다면 floor(m + rand() * (n-m))을 사용하면 된다.

-- 0이상 1미만 실수, 주사위 숫자
select rand(), floor(1+rand()*(7-1));


sign(숫자)

숫자가 양수, 0, 음수인지 확인한다.

-- 1,0,-1
select sign(100), sign(0), sign(-100);


truncate(숫자,정수)

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

-- 12345.12 , 12300
select truncate(12345.12345,2), truncate(12345.12345,-2);


5. 날짜 및 시간 함수


adddate(날짜,차이), subdate(날짜,차이)

날짜를 기준으로 차이를 더하거나 뺀 날짜를 구한다.

-- 31일 후 또는 한달 후인 2025-02-01을 반환
select adddate('2025-01-01',interval 31 day), adddate('2025-01-01',interval 1 month);

-- 31일전 또는 한달 전인 2024-12-01을 반환
select subdate('2025-01-01',interval 31 day), subdate('2025-01-01',interval 1 month);


addtime(날짜/시간,시간), subtime(날짜/시간, 시간)

날짜/시간을 기준으로 시간을 더하거나 뺀 결과를 구한다.

select addtime('2025-01-01 23:59:59', '1:1:1'); -- 2025-01-02 01:01:00
select subtime('2025-01-01 23:59:59', '1:1:1'); -- 2025-01-01 22:58:58


curdate(), curtime(), now(), sysdate()

curdate는 현재 연-월-일, curtime은 현재 시:분:초를 구한다. now와 sysdate는 현재 연-월-일 시:분:초를 구한다.

year(날짜), month(날짜), day(날짜), hour(시간), minute(시간), second(시간), microsecond(시간)

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

select year(curdate()), month(curdate()), day(curdate()); -- 현재 연 월 일
select hour(curtime()), minute(curtime()), second(curtime()), microsecond(curtime()); -- 시 분 초 밀리초


date(), time()

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

select date(now()), time(now())


datediff(날짜1,날짜2), timediff(날짜1 또는 시간1, 날짜1 또는 시간2)

datediff는 날짜1 - 날짜2의 일수를 결과로 구한다. 즉 날짜2에서 날짜1까지 며칠이 남았는지 구한다. timediff는 시간1-시간2의 결과를 구한다.

select datediff('2025-01-01',now()), timediff('23:23:59','12:11:10');


dayofweek(날짜), monthname(), dayofyear(날짜)

요일, 1년 중 몇 번째 날짜인지를 구한다. 요일은 숫자로 표현되는데 1:일요일, 2:월 ~ 7:토요일로 인식하면 된다.

-- 현재 요일과 월이름 일년 중 며칠이 지났는지를 반환
select dayofweek(curdate()), monthname(curdate()), dayofyear(curdate());


last_day(날짜)

주어진 날짜의 마지막 날짜를 구한다. 주로 그 달이 며칠까지 있는지 확인할 때 사용

-- 2025-02-09 
select last_day('2025-02-01');


makedate(연도,정수)

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

-- 2025-02-01
select makedate(2025,32);


maketime(시,분,초)

시,분,초를 이용해서 시:분:초의 time형식을 만든다.

-- 12:11:10
select maketime(12,11,10);


period_add(연월,개월수), period_diff(연월1,연월2)

period_add는 연월에서 개월만큼의 개월이 지난 연월을 구한다. 연월은 yyyy또는 yyyymm형식을 사용한다. period_diff는 연월1-연월2의 개월수를 구한다.

-- 202512, 13
select period_add(202501,11), period_diff(202501,202312);


quarter(날짜)

4분기 중에서 몇 분기인지 확인

-- 3분기 반환
select quarter('2025-07-07');


time_to_sec(시간)

시간을 초 단위로 구한다

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


6. 시스템 정보 함수


user(), database()

현재 사용자 및 현재 선택된 데이터베이스를 구한다.

select current_user(), database();


found_rows()

바로 앞의 select문에서 조회한 행의 개수를 구한다.

use sqldb;
select * from usertbl; -- 10개 조회
select found_rows(); -- 10을 반환


row_count()

바로 앞의 insert, update, delete문에서 입력, 수정, 삭제된 행의 개수를 구한다. create, drop 문이었을 때는 0을 반환하고, select문이었을 때는 -1을 반환한다.

use sqldb;
update buytbl set price= price*2; -- 10
select row_count();



본 포스팅은 ‘이것이 MySQL이다’를 읽고 공부한 내용을 바탕으로 작성하였습니다.


© 2021. By Backtony