[02] 데이터 타입, 내장함수

[1] MySQL 의 데이터 형식

◈ 숫자 데이터 형식

데이터 형식	바이트 수	숫자 범위	설명
BIT(N)	N/8		1~64big 표현, b'0000' 형식으로 표현함
TINYINT	1	-128~127	정수
SMALLINT	2	-32,768~32,767	정수
MEDIUMINT	3	-8,388,608~8,388,607	정수
INT INTEGER	4	약 -21억~21억	정수
BIGINT	8	약 -900경~+900경	정수
FLOAT	4	-3.40E+38~-1.17E-38	소수점 아래 7자리까지
DOUBLE REAL	8	-1.22E-308~1.79E+308	소수점 아래 15자리까지
DECIMAL(m, [d]) NUMERIC(m, [d])	5~17	-10^38+1~+10^38-1	전체 자릿수(m)과 소수점 이하 자릿수(d) 가진 숫자형 ex) decimal(5,2) : 전체 자릿수는 5자리, 그 중 소수점 이하 2자리 차지

 

◈ 문자 데이터 형식

데이터 형식		바이트 수	설명
CHAR(n)		1~255	고정길이 문자형, n : 1~255 지정,  n 생략시 1
VARCHAR(n)		1~65535	가변길이 문자형 n : 1~65535 지정
BINARY(n)		1~255	고정길이 이진 데이터
VARBINARY(n)		1~255	가변길이 이진 데이터
TEXT 형식	TINYTEXT	1~255	255크기 TEXT 데이터
	TEXT	1~65535	N 크기 TEXT 데이터
	MIDUMTEXT	1~16777215	16777215 크기 TEXT 데이터
	LONGTEXT	1~4294967295	최대 4GB TEXT 데이터
BLOB 형식	TINYBLOB	1~255	255 크기 BLOB 데이터
	BLOB	1~65535	N 크기 BLOB 데이터
	MEDIUMBLOB	1~16777215	16777215 크기 BLOB 데이터
	LONGBLOB	1~4294967295	최대 4GB BLOB 데이터
ENUM(값들..)		1 또는 2	최대 65535개 열거형 데이터
SET(값들..)		1, 2, 3, 4, 8	최대 64개 서로 다른 데이터

 

◈ 날짜 및 시간 데이터 형식

데이터 형식	바이트수	설명
DATE	3	YYYY-MM-DD 형식, 1001-01-01~9999-12-31
TIME	3	HH:MM:SS 형식
DATETIME	8	YYYY-MM-DD HH:MM:SS 형식
TIMESTAMP	4	YYYY-MM-DD HH:MM:SS 형식
YEAR	1	YYYY 형식, 1901~2155까지

 

◈ 기타 데이터 형식

데이터 형식	바이트수	설명
GEOMETRY	N/A	공간 데이터 형식(선, 점 및 다각형 등)
JSON	8	JSON 문서 저장

 

◈ 데이터 형식 변환

- CAST() 또는 CONVERT() 이용

sql

닫기 줄바꿈

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

- 데이터 형식 : BINARY, CHAR, DATE, DATETIME, DECIMAL, JSON, SIGNED INTEGER, TIME, UNSIGNED INTEGER

ex) sqlDB의 구매테이블(buytbl)에서 평균 구매개수 구하는 쿼리문

sql

닫기 줄바꿈

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

---

[2] MySQL 내장 함수 

◈ 제어 흐름 함수

- 프로그램 흐름 제어

• IF (수식, 참, 거짓) : 수식의 참/거짓에 따른 실행문 실행
• IFNULL(수식1, 수식2) : 수식1이 NULL이 아니면 수식1 반환, NULL이면 수식2 반환
• NULLIF(수식1, 수식2) : 수식1 == 수식2 면 NULL 반환, 다르면 수식1 반환
• CASE ~ WHEN ~ ELSE ~ END : 내장 함수는 아니고 연산자(Operator)
• sql

  닫기 줄바꿈

  SELECT CASE 10
  WHEN 1 THEN '일'
  WHEN 5 THEN '오'
  WHEN 10 THEN '십' -- 결과는 '십' 반환
  END AS 'CASE예제'; -- 해당 사항이 없이면 END 반환

◈ 문자열 함수

- 문자열 조작 함수

함수	설명
ASCII(문자)	문자의 아스키코드값 반환
CHAR(숫자)	숫자의 아스키코드값 반환
BIT_LENGTH(문자열)	할당된 비트 크기 또는 문자 크기 반환
CHAR_LENGTH(문자열)	문자의 개수 반환
LENGTH(문자열)	할당된 Byte 수 반환
CONCAT(문자열1, 문자열2, ..)	문자열 하나로 이어줌
CONCAT_WS(구분자, 문자열1, 문자열2, ..)	구분자와 함께 문자열 이어줌
GROUP_CONCAT(문자열 SEPARATOR '구분자')	GROUP BY 항목의 개별 내용을 하나의 문자열로 연결
ELT(위치, 문자열1, 문자열2, ...)	위치번째에 해당하는 문자열 반환
FIELD(찾을 문자열, 문자열1, 문자열2)	찾을 문자열 위치 찾아 반환, 없으면 0 반환
FIND_IN_SET(찾을 문자열, 문자열 리스트)	찾을 문자열을 문자열 리스트에서 찾아 위치 반환, 없으면 0 반환 - 문자열 리스트는 콤마(,)로 구분, 공백 없어야
INSTR(기준 문자열, 부분 문자열)	기준 문자열에서 부분 문자열 찾아 시작 위치 반환
LOCATE(부분 문자열, 기준 문자열)	INSTR() 과 동일한 역할
FORMAT(숫자, 소수점자릿수)	숫자 소수점 아래 자릿수까지 표현, 1000단위마다 콤마
BIN(숫자), HEX(숫자), OCT(숫자)	2, 16, 8진수 값 반환
INSERT(기준 문자열, 위치, 길이, 삽입할 문자열)	기준 문자열 위치부터 길이만큼 지우고 삽입할 문자열 삽입
LEFT(문자열, 길이), RIGHT(문자열, 길이)	왼쪽/오른쪽에서 문자열 길이만큼 반환
UPPER(문자열), LOWER(문자열)	소문자 ->대문자 / 대문자 -> 소문자
LPAD(문자열, 길이, 채울문자열)	문자열 길이만큼 늘린 후 왼쪽부터 빈곳 채움
RPAD(문자열, 길이, 채울문자열)	문자열 길이만큼 늘린 후 오른쪽부터 빈곳 채움
LTRIM(문자열), RTRIM(문자열)	문자열 왼/오른쪽 공백 제거, 중간 공백은 제거 안됨
TRIM(문자열) TRIM(방향 자를문자열 FROM 문자열)	문자열 앞뒤 공백 제거 방향: LEADING(앞), BOTH(양쪽), TRAILING(뒤)
REPEAT(문자열, 횟수)	문자열 횟수만큼 반복
REPLACE(문자열, 원래문자열, 바꿀문자열)	문자열에서 원래 문자열 찾아서 바꿀문자열로 바꿈
SPACE(길이)	길이만큼 공백 반환
SUBSTRING(문자열, 시작위치, 길이)  SUBSTRING(문자열 FROM 시작위치 FOR 길이)	시작위치부터 길이만큼 문자 반환, 길이 생략되면 문자열 끝까지
SUBSTRING_INDEX(문자열, 구분자, 횟수)	문자열에서 구분자가 왼쪽부터 횟수번째 나오면 그 이후는 버림 횟수가 음수면 오른쪽부터 세고 왼쪽을 버림

- GROUP_CONCAT() 예제

sql

닫기 줄바꿈

SELECT u.userID, u.name, GROUP_CONCAT(b.prodName SEPARATOR ',') AS '상품목록'
FROM usertbl u LEFT OUTER JOIN buytbl b ON u.userID = b.userID
GROUP BY u.userID, u.name
ORDER BY u.userID;

◈ 수학 함수

함수	설명
ABS(숫자)	절댓값
ACOS(숫자) ASIN(숫자), ATAN(숫자), ATAN2(숫자1, 숫자2), SIN(숫자), COS(숫자), TAN(숫자)	삼각함수
CEILING(숫자), FLOOR(숫자), ROUND(숫자)	올림, 내림, 반올림
CONV(숫자, 원래 진수, 변환할 진수)	숫자의 진수 변환
DEGREES(숫자), RADIANS(숫자)	라디안 -> 각도, 각도 -> 라디안
PI()	3.141592 반환
EXP(숫자), LN(숫자), LOG(숫자) LOG(밑수, 숫자), LOG2(숫자), LOG10(숫자)	지수, 로그 함수
MOD(숫자1, 숫자2) 또는 숫자1 MOD 숫자2 또는  숫자1 % 숫자2	숫자1을 숫자2로 나눈 나머지
POW(숫자1, 숫자2)	숫자1의 숫자2 거듭제곱
SQRT(숫자)	제곱근
RAND()	0이상 1미만 랜덤 실수 m <= 임의의 정수 < n 구하고 싶으면 FLOOR(m + (RAND() * (n-m) )
SIGN(숫자)	숫자가 양수, 0, 음수인지 판별 결과는 1, 0, -1로 반환함
TRUNCATE(숫자, 정수)	숫자를 소수점 아래 정수 위치까지 구하고 나머지 버림

 

◈ 날짜 및 시간 함수

함수	설명
ADDDATE(날짜, 차이)	날짜를 기준으로 차이 더한 날짜 구함
SUBDATE(날짜, 차이)	날짜를 기준으로 차이 뺀 날짜 구함
ADDTIME(날짜/시간, 시간)	날짜/시간을 기준으로 차이 더한 날짜 구함
SUBTIME(날짜/시간, 시간)	날짜/시간을 기준으로 차이 뺀 날짜 구함
CURDATE()	현재 연-월-일
CURTIME()	현재 시:분:초
NOW(), SYSDATE()	현재 연-월-일 시:분:초
YEAR(날짜)	날짜에서 년도 추출
MONTH(날짜), MONTHNAME(날짜)	날짜에서 월 추출
DAY(날짜)	날짜에서 일 추출
HOUR(시간)	시간에서 시간 추출
MINUTE(시간)	시간에서 분 추출
SECOND(시간)	시간에서 초 추출
MICROSECOND(시간)	시간에서 밀리초 추출
DATE()	DATETIME 형식에서 연-월-일만 추출
TIME()	DATETIME 형식에서 시:분:초만 추출
DATEDIFF(날짜1, 날짜2) TIMEDIFF(날짜1/시간1, 날짜2/시간2)	날짜1(시간1) - 날짜2(시간2) 의 일수/시간 추출
DAYOFWEEK(날짜)	날짜의 요일(1:일, 2:월~7:토) 구함
DAYOFYEAR(날짜)	1년중 몇번째 날짜인지 구함
LAST_DAY(날짜)	주어진 날짜의 마지막 날짜 구함
MAKEDATE(년도, 정수)	년도에서 정수만큼 지난 날짜 구함
MAKETIME(시, 분, 초)	시, 분, 초 이용해서 시:분:초 의 TIME 형식 만듦
PERIOD_ADD(년월, 개월수)	년월에서 개월수만큼 지났을 때의 년월 구함
PERIOD_DIFF(년월1, 년월2)	년월1-년월2 의 개월수
QUARTER(날짜)	날짜가 4분기 중 몇분기인지 구함
TIME_TO_SEC(시간)	시간을 초 단위로 구함