[MySQL] 데이터 형식 및 MySQL 내장함수

1. MySQL의 데이터 형식

1.1 MySQL에서 지원하는 데이터 형식의 종류

1. 숫자 데이터 형식
2. 문자 데이터 형식
3. 날짜와 시간 데이터 형식
4. 기타 데이터 형식(GEOMETRY, JSON, LOB) 등

1.2 데이터 형식 변환 함수

형식 변환이 가능한 것은 BINARY, CHAR, DATA, DATATIME, DECIMAL, JSON, SIGNED INTEGER, TIME, UNSIGNED INTEGER이다. 형 변환 함수는 아래 2가지를 주로 사용한다.

• cast는 단순 형변환 ( 데이터 as 데이터 형식 [  ] )
• convert는 형식을 지정해서 형변환 ( 데이터, 데이터 형식 [  ] )

1.3 암시적인 형 변환

CAST( )나 CONVERT( ) 함수를 사용하지 않고 형 변환이 가능한 경우가 있다. 

select '홍' + '길동'; -- 자동형변환
select '20' + '30'; -- 자동형변환 더하기 연산이 있으니까 일단 숫자로 바꿔본다. 
select concat('10','20'); -- 문자와 문자를 연결 (문자로 처리)
select concat(10,'20'); -- 정수와 문자를 연결 (정수가 문자로 변환돼서 처리)
select 1 > '11st'; -- 정수인 11로 변환 false :0, true :1 / 결과값 0
select 30 > '11st'; -- 정수인 11로 변환 / 결과값 1
select 0 = 'st11'; -- 문자는 0으로 변환됨 / 결과값 1

2. 내장함수

2.1 제어 흐름 함수

2.1.1 IF문

select gender, if(gender ="M",'남자','여자') from userDB;

2.1.1 IFNULL

groupName에 null값이 있다면 '미분류'라는 값을 넣어준다.

-- 구매내역 중 제품의 카테고리명이 없는 경우는 '미분류'라는 카테고리로 표시하세요
select prodName, ifnull(groupName,'미분류') as groupName
from buytbl
group by prodName, groupName;

2.1.2 CASE ~ WHEN ~ ELSE ~ END

when ~ then 안에 조건을 여러 개 추가할 수 있다. end 다음에 오는 값은 column의 제목이 된다. 

select gender, case when gender = 'M' then '남자' else '여자' end '성별' from employees; -- if else 조건처럼 사용 
select gender, case gender when 'M' then '남자' else '여자' end '성별' from employees; -- switch case문의 라벨 같이 사용

2.2 문자열 함수

2.2.1 BIT_LENGTH(문자열), CHAR_LENGTH(문자열), LENGTH(문자열)

select bit_length('abc'), char_length('abc'), length('abc'); -- 24, 3, 3 1byte가 8bit라서 24
select bit_length('가나다'), char_length('가나다'), length('가나다'); -- 72, 3, 9 한글은 한 글자가 3byte

2.2.2 Concat(), Concat_ws

문자열을 이어준다. CONCAT_WS( )는 구분자를 지정해서 이어준다.

select concat('2021','-','09','-','10')
select concat_ws('-','2021','09','10'); -- 2021-09-10

2.2.3 Format(숫자, 소수점 자릿수)

숫자를 소수점 아래 자릿수까지 표현

select format(12345.123456,1); //12345.1

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

-- 기준 문자열의 위치부터 길이만큼 지우고 삽입할 문자열 끼워 넣음
select insert('abcdefghi',3,4,'@'); -- 3번째 문자열부터 4개의 문자를 '@'로 바꾸기
-- 왼쪽 또는 오른쪽에서 문자열의 길이만큼 반환
select left('abcdefghi',3), right('abcdefghi',3), mid('abcdefghi',3,3); //abc ghi cde

 

2.2.5 대소문자 변경

select UCASE("ABCdef"), upper("ABDdef");
select LCASE("ABCdef"), LOWER("ABDdef");

2.2.6 LPAD(문자열, 길이, 채울 문자열), RPAD(문자열, 길이, 채울 문자열)

-- 주문번호 00015, 15000
select lpad(15,5,'0'),rpad(15,5,'0') ;

2.2.7 TRIM(문자열), REPEAT(문자열, 횟수), REPLACE(문자열, 원래 문자열, 바꿀 문자열), REVERSE(문자열)

-- 제일 앞, 제일 뒤에서 부터 해당 문자를 찾아서 바꾼다, 다른 문자를 만나면 멈춤
select TRIM(BOTH "ㅋ" from "ㅋㅋㅋㅋ재ㅋㅋ밌어요. ㅋㅋㅋ"); // 재ㅋㅋ밌어요.
-- 모든 문자열에서 해당 문자를 바꿈
select replace("ㅋㅋㅋㅋ재ㅋㅋㅋㅋㅋㅋ밌어요. ㅋㅋㅋ",'ㅋ','');  //재밌어요.
select repeat('ㅋ',5); // ㅋㅋㅋㅋㅋ
select reverse('abcdef'); //fedcba

2.3 수학 함수

select ceiling(51/10), floor(57/10), round(57/10); -- 6,5,6 올림, 내림, 반올림

select 52%10, mod(52, 10), 52 mod 10; -- 나머지
select rand(), rand()*5, (1+rand()*5),floor(1 +rand()*5); -- rand() 는 0부터 1보다 작은 실수가 나온다. 
-- floor(시작숫자 + rand()*마지막숫자) 로 하면 시작숫자부터 마지막 숫자까지의 값이 랜덤으로 출력된다.

2.4 날짜 및 시간 함수

select CURDATE( ), CURTIME(), NOW( ), SYSDATE( ); -- 2021-09-16	/ 15:13:38	/2021-09-16 15:13:38 / 2021-09-16 15:13:38

-- 특정 날짜부터 몇년, 몇달, 몇주, 몇일과 같은 날짜 함수이다. interval을 사용
select adddate('2021-01-01', interval 1 month),adddate('2021-01-01', interval 1 day);

-- date, time, year, month, day만 가져오기
select date(now()), time(now()),year(now()),month(now()),day(now());

select datediff('2020-08-17',now()); -- 날짜 차이 구하기
select timediff('2020-08-17',now()); -- 시간 차이 구하기

-- 지정한 날짜가 몇 요일인지를 알 수 있다. 일요일부터 1로 표시된다.
select dayofweek(curdate());