SQL 기초와 데이터 분석
1. 다양한 데이터 타입 다루기
1-1 숫자
- 숫자형 타입을 int로 지정해두었으면, 데이터 삽입할 때 float형으로 넣어도 출력할 땐 int형으로 나옴
SELECT IF(-1,'true','false')➡️ 숫자 아무거나 와도 true로 반환, 0만 false로 반환
#TRUE=1,FALSE=0
SELECT(2=TRUE,'true','false')➡️ false반환 (2=TRUE이다가 2=1이다로 해석되기 때문에 false로 반환)
1-2 문자
- ENUM형태와 SET : 둘 다 정해진 값을 넣고 싶을 때 사용하는 데이터 타입
- 둘의 차이점은, 한 column에 데이터를 집어넣을 때 ENUM은 묶인 데이터 중 하나만 선택해서 넣을 수 있고 SET은 묶인 데이터라면 몇 개를 선택하든 상관이 없다.
- ENUM : 크기가 거의 변하지 않는 데이터를 ENUM 타입으로 해야한다. (ex : 옷 사이즈 S,M,L,XL 등)
- CHAR은 고정길이 문자열로 길이나 형태가 완전히 정해져 있을 때 사용
- VARCHAR 같은 경우에는 가변 길이 문자열로 문자열 길이가 고정되어 있지 않을 때 사용
1-3 이진
- char(5)는 문자 그대로 5
- binary(5)는 바이너리 형태의 이진값으로 5를 변환
- 일반적인 문자열로 표현할 수 없는 자료형(이미지, 오디오 같은 멀티미디어 파일 등)을 표현할 때도 쓰이기 때문에 binary타입 사용
- Lob : text, 그래픽, 이미지, 비디오 등 구조화되지 않은 대형 데이터를 저장하는 데 사용하는 자료형
- CLOB (문자 객체), BLOB(이진 객체) BFILE(OS에 저장되는 binary file의 이름과 위치를 저장)
1-4 Array
- Array(배열) : 데이터가 저장된 리스트 (ex : ['a','b','c'],[1,2,3])
- Element(원소) : Array에 저장된 각 데이터
- JSON 타입으로 배열을 저장
- 기본값 설정 불가
1-4-1 JSON_ARRAY : 입력을 JSON 배열로 반환하는 함수
CREATE TABLE IF NOT EXISTS 'products'(
'product_id' int(6) NOT NULL,
'category' varchar(40) NOT NULL,
'name' varchar(10) NOT NULL,
'price' int unsigned NOT NULL,
'options' JSON NULL,
PRIMARY KEY ('product_id')
);
INSERT INTO 'products' ('product_id','category','name','price','options')
VALUES (0,'키즈','어린이칫솔',1500,JSON_ARRAY('빨강','파랑')),
(1,'스포츠','손목보호대',10000,JSON_ARRAY('S','M','L')),
(2,'주방용품','밥그릇',2000,JSON_ARRAY('소','중','대')),
(3,'디지털','마우스',15000,NULL);➡️ JSON_ARRAY('빨강','파랑')) 이렇게 말고 '["빨강","파랑"]' 이렇게 넣어도 됨
- Nested array
INSERT INTO 'products' ('product_id','category','name','price','options')
VALUES (0,'키즈','어린이칫솔',1500,JSON_ARRAY('빨강','파랑')),
(1,'스포츠','손목보호대',10000,JSON_ARRAY('S','M','L')),
(2,'주방용품','밥그릇',2000,JSON_ARRAY('소','중','대')),
(3,'디지털','마우스',15000,JSON_ARRAY(JSON_ARRAY("흰색","파랑"),JSON_ARRAY("초록","보라"),JSON_ARRAY("갈색","검정")));
1-4-2 JSON_TYPE : JSON 데이터의 타입을 반환하는 함수
SELECT options, JSON_TYPE(options)
FROM products
1-4-3 JSON_EXTRACT
- Array 내부에 접근
SELECT JSON_EXTRACT(options,'$') as all_elements
FROM products
SELECT JSON_EXTRACT(options,'$[0]') as all_elements
FROM products
1-5 Key-value
- key-value : Key와 Value로 이루어진 데이터
- Key를 통해 Value에 접근할 수 있음
- ex : {'이름':'홍길동','부서':'개발팀','직책':'팀장','근무지':'판교'}
- JSON 타입으로 key-value를 저장
1-5-1 JSON_OBJECT
CREATE TABLE IF NOT EXISTS 'managers_v2' (
'id' int unsigned NOT NULL,
'name' varchar(40) NOT NULL,
'managing' varchar(50) NOT NULL,
'info' JSON NULL,
PRIMARY KEY ('id')
);
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', '영희'));
1-5-2 JSON_EXTRACT
SELECT JSON_EXTRACT(info,'$.off') as off
FROM managers_v2
--인덱싱--
SELECT JSON_EXTRACT(info,'$.off[0]') as off
FROM managers_v2
1-5-3 JSON_INSERT
UPDATE managers_v2 set info = JSON_INSERT(info, '$.new',JSON_ARRAY(1,2,3,4));
1-5-4 JSON_REPLACE
UPDATE managers_v2 set info = JSON_REPLACE(info, '$.new',1);
--인덱싱 이용해서 한개의 값만 바꾸기--
UPDATE managers_v2 set info = JSON_REPLACE(info, '$.new[1]',10);
1-5-5 Nested Key-Value
CREATE TABLE IF NOT EXISTS 'managers_v2' (
'id' int unsigned NOT NULL,
'name' varchar(40) NOT NULL,
'managing' varchar(50) NOT NULL,
'info' JSON NULL,
PRIMARY KEY ('id')
);
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)));
SELECT JSON_EXTRACT(info,'$.education_time')
FROM managers_v2
SELECT name,JSON_EXTRACT(JSON_EXTRACT(info,'$.education_time'),'$.데이터')
FROM managers_v2
위의 내용은 프로그래머스 데이터분석1기 하홍석 강사님의 강의 자료를 참고하였습니다.
참고 사이트
https://audgnssweet.tistory.com/66
