* 이것이 MySQL이다 를 참고했습니다.
1. 숫자 데이터 형식
| 데이터 형식 | 바이트 수 | 숫자 범위 | 설명 |
| BIT(N) | N/8 | 1~64bit를 표현. b'000'형식으로 표현 | |
| 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)를 가진 숫자형 |
2. 문자 데이터 형식
| 데이터 형식 | 바이트 수 | 설명 | |
| ★ CHAR(n) | 1~255 | - 고정 길이 문자형 n을 1부터 255까지 지정 - character의 약자. - CHAR만 쓰면 CHAR(1)이 됨 |
|
| ★ VARCHAR(n) | 1~65535 | - 가변길이 문자형 - n을 사용하면 1부터 65535까지 지정 - Variable character의 약자 |
|
| BINARY(n) | 1~255 | 고정길이의 이진 데이터 값 | |
| VARBINARY(n) | 1~255 | 가변길이의 이진 데이터 값 | |
| TEXT 형식 | TINYTEXT | 1~255 | 255 크기의 TEXT 데이터 값 |
| TEXT | 1~65535 | N 크기의 TEXT 데이터 값 | |
| MEDIUMTEXT | 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', ...) | 1 또는 2 | 최대 65535개의 열거형 데이터 값 | |
| SET('값1', '값2', ...) | 1,2,3,4,8 | 최대 64개의 서로 다른 데이터 값 | |
3. 날짜와 시간 데이터 형식
| 데이터 형식 | 바이트 수 | 설명 |
| ★ DATE | 3 | 1001-01-01 ~ 9999-12-31까지 저장됨. YYYY-MM-DD 형식 |
| TIME | 3 | -838:59:59.000000 ~ 838:59:59.000000까지 저장됨. HH:MM:SS 형식 |
| ★ DATETIME | 8 | 1001-01-01 00:00:00 ~ 9999-12-31 23:59:59까지 저장됨 YYYY-MM-DD HH:MM:SS 형식 |
| TIMESTAMP | 4 | 1970-01-01 0:00:01 ~ 2038-01-19 03:14:07까지 저장됨 YYYY-MM-DD HH:MM:SS 형식 time_zone 시스템 변수와 관련됨. UTC 시간대 변환하여 저장 |
| YEAR | 1 | 1901~ 2155까지 저장됨. YYYY 형식 |
DATETIME은 명시적으로 값을 설정해줘야 값 설정이 가능한데 비해,
TIMESTAMP는 명시적으로 값을 설정하지 않아도 그 값이 저장되어야 하는 시점에서의 현재시간을 읽고 저장해줍니다.
TIMESTAMP는 또한 지역시간대 따라 변환하여 저장하는데, 내부적으로 숫자로 되어있어 다른 시간대로 보고싶을 때 쉽게 변환이 가능합니다.
4. 기타 데이터 형식
| 데이터 형식 | 바이트 수 | 설명 |
| ★ GEOMETRY | N/A | 공간 데이터 형식으로 선, 점, 다각형 같은 공간 데이터 개체를 저장하고 조작 |
| ★ JSON | 8 | JSON 문서를 저장 |
피벗(Pivot)
한 열에 포함된 여러값 출력, 이를 여러 열로 변환하여 테이블 반환식 회전.
필요하면 집계까지 수행 (GROUP BY)
데이터를 보다 직관적으로 분석/요약 가능
CREATE TABLE sales (
date DATE,
amount DECIMAL(10, 2)
);
INSERT INTO sales (date, amount) VALUES
('2024-01-01', 100.00),
('2024-01-01', 150.00),
('2024-01-02', 200.00),
('2024-01-02', 250.00),
('2024-01-03', 300.00);
SELECT
date, -- date 별로 그룹이 만들어짐
-- 그룹에 대해서 sum을 해줌
SUM(CASE WHEN date = '2024-01-01' THEN amount ELSE 0 END) AS '2024-01-01',
SUM(CASE WHEN date = '2024-01-02' THEN amount ELSE 0 END) AS '2024-01-02',
SUM(CASE WHEN date = '2024-01-03' THEN amount ELSE 0 END) AS '2024-01-03'
FROM sales
GROUP BY date;
date 별로 매출액을 합산하고 열로 변환하여 테이블을 반환합니다.
JSON 데이터
-> JSON?
- Key(속성)와 Value(값) 쌍으로 구성
- 자바스크립트에서 파생됨
-- **************************
-- *** MySQL에서 JSON 제어 ***
-- **************************
-- 대상: testdb.usertbl
-- JSON 형식으로 출력
SELECT JSON_OBJECT('name', name, 'addr', addr, 'height', height) AS 'JSON_데이터'
FROM testdb.usertbl WHERE height >= 180;
set @json = '{"usertbl":
[
{"addr": "서울", "name": "임재범", "height": 182} ,
{"addr": "서울", "name": "이승기", "height": 182} ,
{"addr": "서울", "name": "성시경", "height": 186}
]
}';
-- json이 유효한지 1/0 리턴
SELECT JSON_VALID(@json) AS JSON_VALID;
-- JSON_SEARCH(데이터, one/all, 찾을것)
-- 데이터 내에서 찾을거 첫번째 인덱스 반환(0부터 시작)
SELECT JSON_SEARCH(@json, 'one', '성시경') AS JSON_SEARCH;
-- 인덱스로 검색
SELECT JSON_EXTRACT(@json, '$.usertbl[2].name') AS JSON_EXTRACT;
-- 입력(Create)
SELECT JSON_INSERT(@json, '$.usertbl[0].mDate', '2009-09-09') AS JSON_INSERT;
-- 추가
SELECT JSON_ARRAY_APPEND(@json, '$.usertbl', JSON_OBJECT('addr', '부산', 'name', '김태희', 'height', 163)) AS JSON_ARRAY_APPEND;
-- 변경(Update)
SELECT JSON_REPLACE(@json, '$.usertbl[0].name', '홍길동') AS JSON_REPLACE;
-- 정보 변경
SELECT JSON_SET(@json, '$.usertbl[0].height', 175) AS JSON_SET;
-- 속성 추가
SELECT JSON_SET(@json, '$.usertbl[0].height', 70) AS JSON_SET;
-- 삭제(Delete)
SELECT JSON_REMOVE(@json, '$.usertbl[0]') AS JSON_REMOVE;
-- 속성 삭제
SELECT JSON_REMOVE(@json, '$.usertbl[0].height') AS JSON_REMOVE;
위의 쿼리는 MySQL에서 JSON 데이터를 다루는 다양한 함수 및 연산자들을 보여줍니다. 이러한 함수와 연산자들을 사용하여 JSON 데이터를 유효성 검사하거나, 원하는 정보를 찾거나, 수정, 추가, 삭제 등을 하여 JSON 형식 데이터를 유연하게 처리할 수 있습니다.
