[데브코스][데이터 분석] 다양한 데이터 타입 다루기

< 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

 

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의 데이터 타입들은 잘 몰랐던 내용이라 색달랐던 강의였다. 
이론만들으면 어려운데 실습을 같이 하니 다른 언어에서 봤던 데이터 타입들과 비슷해보인다. 

* 오늘은 날씨가 너무 좋았다ㅠㅠ
정말 봄이 왔나보다. 봄이 오니 공부하기 어렵다 집중도 안되고,,