[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()` 이용

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

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

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

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)

• 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() 예제

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(시간)`	시간을 초 단위로 구함