[데브코스][데이터 분석] 다양한 데이터 타입 다루기
< 3주차 SQL 쿼리 문법을 활용하여 데이터 분석 진행하기 (3) >
5. 다양한 데이터 타입 다루기
5-1. 숫자
BIT(M)
: 0과 1로만 구성. 컴퓨터가 데이터를 저장하는 기본 단위 (예> b’111’ = 7)
M은 몇 자리 비트를 사용할 것인지 의미. 1<=M<=64 (기본값 : M=1)
TINYINT
: 매우 작은 정수. 끝 값이 명확한 경우. 기존 INT 타입에 비해 훨씬 적은 저장용량
-> 8비트까지 사용 가능
- Signed 범위 : -127 ~ 127
- Unsigned 범위 : 0 ~ 255(2의 8승)
BOOL, BOOLEAN
: True/False
- TINYINT(1) 과 같음 (길이 1짜리 tinyint)
- 0이면 False, 1이면 True
SMALLINT
: 작은 정수(대략 6만개의 정수)
-> 16비트까지 사용가능
-32768 ~ 32767
0 ~ 65535
MEDIUMINT
: 중간 크기 정수 (대략 1670만개의 정수)
-> 24비트까지 사용가능
-8388608 ~ 8388607
0 ~ 16777215
INT, INTEGER
: 정수(일반적으로 사용하는 정수타입)
-> 32비트까지 사용 가능
-2147483648 ~ 2147483647
0 ~ 4294967295
BIGINT, SERIAL
: INT 타입보다 2배 많은 비트를 사용하는 정수 (64개의 비트)
(Bigint 중 부호가 없는 bigint를 SERIAL이라고 부름)
-9223372036854775808 ~
9223372036854775807
0 ~ 18446744073709551615.
DECIMAL(M,D), DEC, FIXED
: 고정소수점 타입 (M = 숫자 전체 자리 수의 길이, D = 소수점 이하 자리수)
M<=65
0<=D<=30
FLOAT
: 부동소수점 타입 (32개의 비트)
4 bytes
DOUBLE
: 부동소수점 타입 (64개의 비트)
8 bytes
현업에서는 주로 boolean, int, double 등을 사용함!
실습 예제
SELECT IF(0, 'true', 'false')
-- 결과 : false
SELECT IF(1, 'true', 'false')
-- 결과 : true
SELECT IF(3=TRUE, 'true', 'false')
-- 결과 : true
5-2. 문자
CHAR
고정된 길이의 문자열
길이 0~255
선언된 값보다 짧은 문자열이 들어오면, 빈 문자열로 나머지 길이를 채움
VARCHAR
변동 가능한 길이의 문자열(한 행이 가질 수 있는 총 길이와 동일), 기본값 지정 가능
길이 0~65535
TEXT
변동 가능한 길이의 문자열
길이 최대 65535 (길이 설정 불가) -> 최대 길이만 넘지 않으면 됨
Non-binary strings (=character strings)
기본값 지정 불가
TINYTEXT
더 짧은, 작은 TEXT
최대 길이 255 바이트
MEDIUMTEXT
중간 크기 TEXT
길이 최대 16777215 바이트
LONGTEXT
큰 크기 TEXT
길이 최대 4294967295
ENUM
: 제한된 값 리스트를 미리 저장해두고, 그 안에 있는 값만 삽입할 수 있도록 하는 타입
최초에 지정해 둔 리스트에 포함되는 값만 저장
효율적인 데이터 저장(컬럼에 들어갈 값이 절대 바뀌지 않을 때 효율적) / 유연성, 확장성 낮음
미리 지정한 리스트에 없는 값을 삽입하면 에러 발생(DB마다 다름)
SET
최초에 지정해 둔 리스트에 포함되는 값들을 중복으로 저장.
순서를 무시
enum과 동일하게 미리 가능한 값 리스트를 지정함. euum과 달리 0개 혹은 여러 개의 값 가능(가능한 모든 조합)
64개까지
주로 char, varchar가 많이 사용됨
5-3. 이진
: 데이터를 저장하거나 활용하기 위해 0과 1의 이진 형식으로 인코딩한 파일
: 다른 데이터 타입을 포함하는 타입임
(ex. 텍스트 타입은 이진 타입이지만, 이진타입이라고 꼭 텍스트 타입은 아님)
BLOB
: Binary Large Object (무슨 데이터든 바이너리 타입으로 최대 길이 내에서 집어 넣을 수 있음)
길이 최대 65535 (길이 설정 불가)
기본값 지정 불가
TINYBLOB
: 작은 BLOB
길이 최대 255
MEDIUMBLOB
: 중간 크기 BLOB
길이 최대 16777215
LONGBLOB
: 큰 BLOB
길이 최대 4294967295
BINARY
: Binary strings (고정된 길이)
(char의 이진 버전)
길이 0~255(바이트 기준. 한글은 한 글자에 3바이트임)
선언된 값보다 짧은 문자열이 들어오면, 빈 문자열로 나머지 길이를 채움
VARBINARY
: Binary strings (변동 가능한 길이) 길이 0~255
(varchar의 이진버전)
-- CAST 함수 : 타입 변경 함수 (바이너리 문자를 char 문자로 바꿈)
SELECT CAST(name as char) as str_name
FROM products
5-4. Array
1. Array (배열) : 데이터가 저장된 리스트
ex) [‘a’, ‘b’, ‘c’] / [1, 2, 3]
2. Element (원소) : Array에 저장된 각 데이터
3. JSON 타입으로 배열을 저장(JSON ARRAY라고도 부름)
4. 기본값 설정 불가
JSON_ARRAY
: 입력을 JSON 배열로 반환하는 함수
(INSERT 구문 or SELECT 구문에서 주로 사용)
-- JSON_ARRAY 함수에 배열의 원소가 될 값들을 넣어줌
INSERT INTO 'products' ('name', 'options') VALUES
('어린이칫솔', JSON_ARRAY('빨강', '파랑')),
('밥그릇', JSON_ARRAY('소', '중', '대')),
('마우스', NULL)
JSON_TYPE
: JSON 데이터의 타입을 반환하는 함수
(MySQL 5.7.8 버전부터 지원시작)
실습 : https://www.programiz.com/sql/online-compiler/
Online SQL Editor
www.programiz.com
ARRAY 타입으로 데이터를 넣을 때 JSON_ARRAY말고 단순 배열 형식도 가능함
-- 아래 두 가지 구문은 같은 결과임!
INSERT INTO 'products' ('name', 'options') VALUES
('어린이칫솔', JSON_ARRAY('빨강', '파랑')),
('밥그릇', JSON_ARRAY('소', '중', '대')),
('마우스', NULL)
INSERT INTO 'products' ('name', 'options') VALUES
('어린이칫솔', '["빨강", "파랑"]'),
('밥그릇', JSON_ARRAY('소', '중', '대')),
('마우스', NULL)
-- 즉, JSON_ARRAY('빨강', '파랑')는 '["빨강", "파랑"]' 와 같음
NESTED_ARRAY :ARRAY안의 원소가 ARRAY로 들어감
-- Nested Array
INSERT INTO 'products' ('name', 'options') VALUES
('어린이칫솔', JSON_ARRAY('빨강', '파랑')),
('밥그릇', JSON_ARRAY('소', '중', '대')),
('마우스', JSON_ARRAY(JSON_ARRAY("흰", "파"), JSON_ARRAY("초", "보")));
JSON_EXTRACT
: ARRAY 내부의 데이터를 접근함
$는 키를 지정하는 것이기 때문에, key-value 형식에서 많이 쓰임
-- JSON 타입의 options 컬럼에서 모든 값을 뽑아옴
SELECT JSON_EXTRACT(options, '$') as all_elements
FROM products
-- JSON 타입의 options 컬럼에서 첫 번째 값을 뽑아옴
SELECT JSON_EXTRACT(options, '$[0]') as first_elements
FROM products
5-5. Key-value
Key-value : Key와 Value로 이루어진 데이터
: key는 기준이 되는 값, value는 대응되는 값
: Key를 통해 Value에 접근할 수 있음
<예>
1. {‘이름’ : ‘홍길동’, ‘부서’ : ‘개발팀’, ‘직책’ : ‘팀장’, ‘근무지’ : ‘판교’}
2. {‘색상’ : [‘빨강’, ‘파랑’], ‘사이즈’ : [’S’, ‘M’, ‘L’]}
- JSON 타입으로 key-value를 저장
JSON_OBJECT
: key-value 입력값을 JSON 객체로 반환하는 함수
(JSON ARRAY와 유사함)
: Key-value 순으로 입력함
INSERT INTO 'managers_v2' ('id', 'name', 'managing', 'info') VALUES
(0, '영희', '스포츠', JSON_OBJECT('off', JSON_ARRAY('일', '월'), 'substitute', '민수')),
(1, '철수', '주방용품', JSON_OBJECT('off', JSON_ARRAY('화', '수'), 'substitute', '길순')),
(2, '민수', '디지털', JSON_OBJECT('off', JSON_ARRAY('목', '금'), 'substitute', '철수')),
(3, '길순', '키즈', JSON_OBJECT('off', JSON_ARRAY('금', '토'), 'substitute', '영희'));
JSON_OBJECT의 첫 번째 인자 : off --> 첫 번째 키
두 번째 인자 : JSON_ARRAY('일', '월') --> 첫 번째 키에 대응되는 value
세 번째 인자 : substitute--> 두 번째 키
네 번째 인자 : '민수' --> 두 번째 키에 대응되는 value
JSON_EXTRACT
: ARRAY에서 인덱싱을 할 때는 $ 뒤에 대괄호와 인덱스 번호를 씀.
Key-value 에서는 $ 뒤에 .을 찍고 원하는 키 이름을 써줌
-- off key에 대응되는 value가 도출됨
SELECT JSON_EXTRACT(info, '$.off') as off
FROM managers_v2
/*
off key에 대응되는 value가 도출됨
info 컬럼에서 off key에 대응되는 값을 가져오고, 해당 배열에서 첫 번째 값을 가져옴
*/
SELECT JSON_EXTRACT(info, '$.off[0]') as off_first
FROM managers_v2
/*
출력 :
일
화
목
금
*/
JSON_INSERT
: key-value 쌍을 삽입할 때 사용
-- new라는 키를 만들고, [1, 2, 3, 4] 값의 배열을 value로 집어넣음
UPDATE managers_v2 set info = JSON_INSERT(info, '$.new', JSON_ARRAY(1, 2, 3, 4));
JSON_REPLACE
: 이미 있는 컬럼값을 바꾸는 함수
-- new 키에 대한 모든 값을 모두 1로 바꾼다는 의미
UPDATE managers_v2 set info = JSON_INSERT(info, '$.new', 1);
-- Nested Key Value
-- value 값에 key-value를 또 넣은 형태
INSERT INTO 'managers_v2' ('id', 'name', 'managing', 'info') VALUES
(0, '영희', '스포츠', JSON_OBJECT('off', JSON_ARRAY('일', '월'), 'substitute', '민수', 'education_time', JSON_OBJECT('데이터', 3, '소방', 2) )),
(1, '철수', '주방용품', JSON_OBJECT('off', JSON_ARRAY('화', '수'), 'substitute', '길순', 'education_time', JSON_OBJECT('인사', 2, '데이터', 3) )),
(2, '민수', '디지털', JSON_OBJECT('off', JSON_ARRAY('목', '금'), 'substitute', '철수', 'education_time', JSON_OBJECT('보안', 3, '회계', 1 ))),
(3, '길순', '키즈', JSON_OBJECT('off', JSON_ARRAY('금', '토'), 'substitute', '영희', 'education_time', JSON_OBJECT('인공지능', 1, '소방', 1)));
🥵 2024년 3월 6일 수요일 TIL
SQL의 데이터 타입들은 잘 몰랐던 내용이라 색달랐던 강의였다.
이론만들으면 어려운데 실습을 같이 하니 다른 언어에서 봤던 데이터 타입들과 비슷해보인다.
* 오늘은 날씨가 너무 좋았다ㅠㅠ
정말 봄이 왔나보다. 봄이 오니 공부하기 어렵다 집중도 안되고,,