혼자 공부하는 SQL 3주차 - MySQL의 데이터 형식

MySQL의 데이터 형식

데이터 형식이 필요한 경우
  ☞ 테이블 만들 때 ( 칼럼의 형식을 정해준다 )
  ☞ 데이터 insert할 때 ( insert 시 형식을 맞추지 않게되면 오류가 발생한다. )
  ☞ 데이터 update할 때 ( update 시 형식을 맞추지 않게되면 오류가 발생한다. )

정수형

• 인원 수, 가격, 수량 등에 주로 사용

데이터 형식	바이트 수	숫자 범위
TINYINT	1	-128 ~ 127
SMALLINT	2	-32.769 ~ 32.767
INT	4	약 -21억 ~ +21억
BIGINT	8	약 -900경 ~ +900경

• 정수형 타입 뒤에 UNSIGNED를 붙여주면 범위가 0부터로 지정된다.

데이터 입력가능한 범위 확인하기

market_db에서 hongong4 TABLE 생성하기

-- market_db DATABASE 사용하기
USE market_db;

-- hongong4 TABLE 생성하기
CREATE TABLE hongong4 (
	  tinyint_col	TINYINT
        , smallint_col	SMALLINT
    	, int_col	INT
    	, bigint_col	BIGINT
);

hongong4 table에 데이터 INSERT하기 _ 1

-- 데이터 INSERT하기
INSERT INTO hongong4 VALUES(127, 32767, 2147483647, 9000000000000000000);

hongong4 table에 데이터 INSERT하기 _ 2

-- 데이터 INSERT하기 ( 범위 안 맞게 )
INSERT INTO hongong4 VALUES(128, 32768, 2147483648, 90000000000000000000);

Error Code: 1264. Out of range value for column 'tinyint_col' at row 1

• Out of range value Error가 발생한다.
• Out of range value :: 입력값의 범위를 벗어남 → Table 생성 시 정한 타입의 입력값에 맞춰서 insert해줘야 한다!! 

---

문자형

• 글자(TEXT) 저장 시 사용
• 입력할 최대 글자의 개수 지정 필요

데이터 형식	바이트 수
CHAR(개수)	1 ~ 255
VARCHAR(개수)	1 ~ 16383

주의할 점 - CHAR()
  ☞ 자릿수가 고정되어 있음.
  ☞ 즉, 정확한 자릿수를 지정해주는 것이 중요함.

주의할 점 - VARCHAR()
  ☞ CHAR와 다르게 자릿수가 유동적임.
  ☞ 입력할 데이터들 중 최대 자릿수를 지정하는 것이 중요.

VARCHAR보다 많은 데이터 입력이 필요할 때!

big_table TABLE 생성하기

-- big_table TABLE 생성하기
CREATE TABLE big_table(
	  data1 CHAR(256)
    	, data2	VARCHAR(16384)
);

Error Code: 1074. Column length too big for column 'data1'(max = 255); use BLOB or TEXT instead

• Column length too big Error가 발생한다.
• 칼럼의 형식에 따른 최대 자릿수를 벗어남 → 타입에 맞춘 자릿수 지정이 필요함.
• 이 때 필요한 형식이 아래에서 설명할 대량의 데이터 형식이다.

---

대량의 데이터 형식

• 최대 16383자를 넘어가는 TEXT 저장 시 사용

데이터 형식		바이트 수
TEXT 형식	TEXT	1 ~ 65535
	LONGTEXT	1 ~ 4294967295
BLOB 형식	BLOB (Binary Long Object)	1 ~ 65535
	LONGBLOB	1 ~ 4294967295(약 42억__)

넷플릭스 데이터베이스 테이블 만들기

넷플릭스 데이터베이스 형태 예측하기

id	제목	감독	주연 배우	자막(LONGTEXT)	동영상(LONGBOLOB)
0001	기묘한이야기	더퍼 형제	위노나 라이더	#####	#####
0002	지정생존자	유종선	지진희	#####	#####
0003	블랙리스트	블랙맨	제임스 스페이더	#####	#####

 netflix_db DATABASE에서 movie TABLE 생성하기

-- DATABASE 생성하기
CREATE DATABASE netflix_db;
-- netflix_db DATABASE 사용하기
USE netflix_db;

-- movie TABLE 생성하기
CREATE TABLE movie (
	  movie_id			INT
        , movie_title		VARCHAR(30)
        , movie_director		VARCHAR(20)
        , movie_star		VARCHAR(20)
        , movie_script		LONGTEXT
        , movie_film		LONGBLOB
);

---

실수형

• 소수점이 있는 숫자 저장 시 사용
• FLOAT과 DOUBLE은 거의 유사하다고 볼 수 있음. 기본적으로는 FLOAT면 충분함.

데이터 형식	바이트 수	설명
FLOAT	4	소수점 아래 7자리까지 표현
DOUBLE	8	소수점 아래 15자리까지 표현

---

날짜형

• 날짜 및 시간 저장 시 사용

데이터 형식	바이트 수	설명
DATE	3	날짜만 저장. YYYY-MM-DD 형식으로 사용
TIME	3	시간만 저장. HH:MM:SS 형식으로 사용
DATETIME	8	날짜 및 시간을 저장. YYYY-MM-DD HH:MM:SS 형식으로 사용.

날짜, 시간 데이터 입력하기

date_time TABLE 생성하기

-- date_time TABLE 생성하기
CREATE TABLE date_time(
		  dt_date		DATE
		, dt_time		TIME
		, dt_datetime	DATETIME
);

현재 시간, 현재 날짜 구하기

-- 현재 날짜 구하기
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');
-- 현재 시간 구하기
SELECT DATE_FORMAT(NOW(), '%H:%m:%s');
-- 현재 날짜와 현재 시간 구하기
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%m:%s');

-- 한번에 SELECT 결과 확인하기
SELECT
	  DATE_FORMAT(NOW(), '%Y-%m-%d') 	  	  AS 오늘 날짜
	, DATE_FORMAT(NOW(), '%H:%m:%s') 	  	  AS 현재 시간
	, DATE_FORMAT(NOW(), '%Y-%m-%d %H:%m:%s') AS 오늘 날짜와 현재 시간;

NOW() 함수를 이용한 오늘 날짜와 현재 시간 가져오기

 date_time TABLE에 현재 날짜, 현재 시간 INSERT하기

INSERT INTO date_time VALUES(DATE_FORMAT(NOW(), '%Y-%m-%d'), DATE_FORMAT(NOW(), '%H:%m:%s'), DATE_FORMAT(NOW(), '%Y-%m-%d %H:%m:%s'));

INSERT 후 date_time TABLE SELECT 결과

---

변수의 사용

-- 변수의 선언 및 값 대입
SET @변수이름 = 변수값;

-- 변수값 출력하기
SELECT @변수이름;

-- @txt 변수 생성 및 값 대입
SET @txt = '가수 이름 ==> ';
-- @height 변수 생성 및 값 대입
SET @height = 166;

-- member TABLE에서 mem_name 출력 & @txt 변수 출력
-- WHERE 조건 이용하여 조건별 출력
SELECT
	   @txt
	 , mem_name 
FROM member 
WHERE height > @height;

-- 변수를 사용하지 않고 SELECT하게 된다면..?
SELECT
	  '가수 이름 ==> ' AS '가수명'
	, mem_name
FROM member
WHERE height > 166;

---

데이터 형 변환

• 문자형 → 정수형 // 정수형 → 문자형 등 형을 변환하는 것

함수를 이용한 명시적인 변환

• CAST(), CONVERT()

예시)
CAST( 값 AS 데이터_형식 [ (길이) ] )
CONVERT( 값, 데이터_형식 [ (길이) ] )

---

p.173 ~ 174 확인문제

1. 정수형 데이터

• TINYINT > SMALLINT > INT > BIGINT

2. TINYINT 형식의 칼럼에 128을 입력 시 생기는 오류는?

• Out of range

3. 정수형 데이터에 UNSIGNED를 붙이면?

• 데이터가 양수만 저장됨

4. 문자형 데이터에 대한 설명

• CHAR는 고정형 문자형
• CHAR는 최대 255자까지 저장 가능
• VARCHAR는 가변형 문자형
• VARCHAR는 최대 16383글자까지 저장

5. 숫자를 CHAR형으로 지정하기에 적합한 것

• 전화번호 국번
• 전화번호 뒷자리

6. 대량의 데이터 형식을 저장하는 방식

• 영화 테이블에서 자막을 저장하기에 적합한 데이터 형식은 LONGTEXT이며, 
• 동영상 파일을 저장하기에 적합한 데이터 형식은 LONGBLOB이다.

7. 데이터 형식의 변환에 사용되는 함수

• CAST()
• CONVERT()