[MySQL] 데이터 형식

· 데이터베이스 테이블을 만들 때는 데이터 형식을 설정해야 한다.

  테이터 형식에는 크게 숫자형, 문자형, 날짜형이 있고, 세부적으로 더 나뉘기도 한다.

 

다양한 데이터 형식이 존재하는 이유

· 저장될 데이터의 형태가 다양하기 때문이다. 각 테이터에 맞는 데이터를 지정함으로써 효율적인 저장이 가능하다. 

· 예를 들어 이름을 저장하는 컬럼에 100 글자를 저장할 칸을 준비하는 것을 상당한 낭비다.

 

데이터 형식

정수형

· 소수점이 없는 숫자 

· 사용 예: 인원 수, 가격, 수량

데이터 형식	바이트 수	숫자 범위	사용 예
TINYINT	1	-128 ~ 127	가수 그룹
SMALLINT	2	-32,768 ~ 32,767	평균 키
INT	4	약 -21억 ~ +21억	인원 수
BIGINT	8	약 -900경 ~ +900경

· 추가적으로 BIT, MEDIUMINT도 있으나 잘 사용하지 않는다.

· 정수형 데이터 형식을 갖는 컬럼에 숫자 범위를 초과하는 값을 넣으면 Out of range 오류가 발생한다.

 

· 데이터 형식의 양수 범위를 늘리기 위해서 값의 범위가 0부터 시작되는 USIGNED 예약어를 사용할 수 있다.

  예: TINYINT와 TINYINT UNSIGNED는 모두 1 바이트 크기를 갖지만 각각 -128~+128, 0~255의 값 범위를 표현한다.

CREATE TABLE member -- 회원 테이블
(
	mem_id		CHAR(8) NOT NULL PRIMARY KEY, -- 회원 아이디(PK)
    mem_name 	VARCHAR(10) NOT NULL, -- 이름
    mem_number	TYNYINT NOT NULL, -- 인원수
    addr 		CHAR(2) NOT NULL, -- 주소(경기, 서울, 경남 식으로 2글자만 입력)
    phone1 		CHAR(3), -- 연락처의 국번(02, 031, 055 등)
    phone2 		CHAR(8), -- 역락처의 나머지 전화번호(하이픈 제외)
    height		TINYINT UNSIGNED, -- 평균키
    debut_date DATE -- 데뷔 일자
);

 

문자형

· 문자형은 글자를 저장하기 위해 사용하며, 입력할 최대 글자의 개수를 지정해야 한다.

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

· 데이터 형식에 (개수) 부분을 생략하면, CHAR(1), VARCHAR(1)과 동일하다.

 

· CHAR는 문자를 의미하는 Character의 약어로, 고정길이 문자형이라고 부른다.

   - 자릿수가 고정되어 있다.

   - ex) CHAR(10)에 '가나다' 3글자만 저장해도 10자리를 모두 확보한 후 앞에 3자리를 사용한 뒤 7자리는 낭비한다.

· VARCHAR는 Variable Character의 약어로, 가변길이 문자형이다. 

   - 자릿수가 가변한다.

   - VARCHAR(10)에 '가나다' 3글자를 저장할 경우 3자리만 사용한다.

 

· VARCHAR가 CHAR보다 공간을 효율적으로 운영하지만, MySQL 내부적으로 성능(빠른 속도)은 CHAR 설정이 더 좋다.

   - CHAR는 글자의 개수가 고정된 경우, VARCHAR는 글자의 개수가 변동될 경우 사용하는 것이 좋다.

 

사용 예시

· 거주 지역을 서울/부산/경기와 같이 시도만 저장할 경우 모두 2글자로 일정하므로, 이때는 CHAR(2)로 설정하는 게 좋다.

· 가수 그룹 이름을 저장할 경우 '잇지'처럼 2글자도 있지만, '방탄소년단'처럼 좀 더 긴 글자도 있으므로, VARCHAR로 설정하는 게 좋다.

 

CREATE TABLE member -- 회원 테이블
(
	mem_id		CHAR(8) NOT NULL PRIMARY KEY, -- 회원 아이디(PK)
    mem_name 	VARCHAR(10) NOT NULL, -- 이름
    mem_number	TYNYINT NOT NULL, -- 인원수
    addr 		CHAR(2) NOT NULL, -- 주소(경기, 서울, 경남 식으로 2글자만 입력)
    phone1 		CHAR(3), -- 연락처의 국번(02, 031, 055 등)
    phone2 		CHAR(8), -- 역락처의 나머지 전화번호(하이픈 제외)
    height		TINYINT UNSIGNED, -- 평균키
    debut_date DATE -- 데뷔 일자
);

 

· 위 코드에서 전화번호는 모두 숫자로 이루어져 정수형으로 지정해야 할 것 같지만, CHAR로 지정되어 있다.

  전화번호가 숫자로서 의미가 없기 때문이다. 숫자로서 의미를 가지려면 다음 중 1가지는 충족해야한다.

   1. 더하기/빼기 등의 연산에 의미가 있다.

   2. 크다/작다 또는 순서에 의미가 있다.

 

· 전화번호는 문자형이 더 효과적이기는 하지만, 정수형으로 지정했다고 반드시 틀렸다고 할 수 있 없다.

  효율성이나 타당성 면에서 좀 더 비효율적인 뿐이다.

 

대량의 데이터 형식

· 문자형인 CHAR는 최대 255자, VARCHAR는 최대 16383자까지 지정이 가능하다.

  이를 초과하는 번위로 컬럼을 생성하려하면, "Column length too big" 오류가 발생한다.

 

· 오류가 발생하는 예

CREATE TABLE big_table(
	data1 CHAR(256),
    data2 VARCHAR(16384);
)

 

· 더 큰 데이터를 저장하려면 다음 형식을 사용한다.

데이터 형식		바이트 수	사용예시
TEXT 형식	TEXT	1~65535	소설, 영화 대본 등
	LONGTEXT	1~4294967295 (약 42억)
BLOB 형식	BLOB	1~65535	사진, 동영상 등 이진 데이터
	LONGBLOB	1~4294967295 (약 42억)

· 추가로 TINYTEXT, MEDIUNTEXT, TINYBLOB, MEDIUMBLOB 등도 있지만 잘 사용하지 않는다.

 

· BLOB는 Binary Long Object의 약자로 글자가 아닌 이미지, 동영상 등의 데이터를 저장한다.

   - 이런 것을 이진(Binary) 데이터라고 부른다.

 

· 예를 들어, 넷플릭스와 같은 동영상 사이트는 다음과 비슷한 테이블을 운영할 수 있다.

CREATE DATABASE netflix_db;
USE netflix_db;
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	4	소수점 아래 7자리까지 표현	시력 ex)2.0, 1.5, 0.7
DOUBLE	8	소수점 아래 15자리까지 표현	정밀함이 요구되는 과학 데이터

 

· FLOAT와 DOUBLE은 소수점 아래를 어디까지 정밀하게 표현하는지 차이가 있다.

   - 과학 기술용 데이터가 아닌 이상 FLOAT면 충분하다. ex) 시력 2.0, 1.5, 0.7

 

날짜형

· 날짜 및 시간을 저장할 때 사용

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

· 날짜 또는 시간을 입력할 때는 문자와 마찬가지로 작은따옴표로 묶어줘야 한다.

 

변수의 사용

· SQL도 다른 일반적인 프로그래밍 언어처럼 변수를 선언하고 사용할 수 있다.

 

변수의 선언과 값 대입 형식

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

 

· 변수는 MySQL 워크 벤치를 재시작할 때까지는 유지되지만, 종료하면 없어진다. 

  임시로 사용한다고 생각하면 된다.

 

USER market_db;
SET @myVar1 = 5 ;		-- 변수를 선언하고 정수 또는 실수를 대입
SET @myVar2 = 4.25 ;

SELECT @myVar1 ;		-- 변수의 내용을 출력
SELECT @myVar1 + @myVar2 ;	-- 변수끼리 연산한 후에 출력

SET @txt = '가수 이름==> ' ;	-- 변수를 선언하고 문자열 또는 정수를 대입
SET @height = 166 ; 	
SELECT @txt , mem_name FROM member WHERE height > @height ; -- 테이블을 조회하면서 변수를 활용

 

· 주의! SELECT 문에서 행의 개수를 제한하는 LIMIT에는 변수를 사용할 수 없다. 이것은 문법상 오류 처리 된다.

   - 이를 해결하기 위해 PREPARE와 EXECUTE 예약어를 사용할 수 있다.

 

SET @count = 3;
PREPARE mySQL FROM 'SELECT mem_name, height FROM member ORDER BY height LIMIT ?'	
EXECUTE mySQL USING @count;

 

   - PREPARE는 'SELECT ~~ LIMIT ?'문을 실행하지 않고 mySQL이라는 이름으로 준비만 한다.

      LIMIT 다음에 오는 물음표(?)는 '현재는 모르지만 나중에 채워짐'이라는 뜻이다.

   - EXECUTE로 mySQL에 저장된 SELECT 문을 실행할 때, USING으로 물음표(?)에 @count 변수의 값을 대입한다.

   - 결론적으로 다음과 같은 SQL이 실행된다.

SELECT mem_name, height FROM member ORDER BY height LIMIT 3;

 

데이터 형 변환

· 자료형을 다른 형태로 변경하는 것 ex) 문자형을 정수형으로 바꾸거나, 반대로 정수형을 문자형으로 바꾸거나

· 직접 함수를 사용해서 변환하는 명시적인 변환(explicit conversion)과

  별도의 지시 없이 자연스럽게 변환되는 암시적인 변환(implicit conversion)이 있다.

 

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

· CAST(), CONVERT() 함수를 사용하여 데이터 형식을 변환할 수 있다.

   - 두 함수는 형식만 다를 뿐 동일한 기능을 제공한다.

 

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

 

▶ 예시 -  구매 테이블(buy)에서 평균 가격을 구하는 SQL 

 

SELECT AVG(price) AS '평균 가격' FROM buy;

 

· 실행 결과: 142.9167

 

·  함수를 이용하여 평균 가격을 정수로 표현할 수 있다.

 

SELECT CAST(AVG(price) AS SIGNED) AS '평균 가격' FROM buy;
-- 또는
SELECT CONVERT(AVG(price), SIGNED) '평균 가격' FROM buy;

 

· 실행 결과: 143

 

· CAST()나 CONVERT() 함수 안에 올 수 있는 데이터 형식은 CHAR, SIGNED, UNSIGNED, DATE, TIME, DATETIME 등이다.

   - SIGNED는 부호가 있는 정수, UNSIGNED는 부호가 없는 정수를 의미한다.

   - SIGNED는 SIGNED INTEGER, UNSIGNED는 UNSIGNED INTEGER라고 써도 된다.

 

▶ 예시 -  다양한 구분자를 날짜형으로 변경하는 SQL

SELECT CAST('2022$12$12' AS DATE);
SELECT CAST('2022/12/12' AS DATE);
SELECT CAST('2022%12%12' AS DATE);
SELECT CAST('2022@12@12' AS DATE);

 

· 실행 결과: 2020-12-12

 

▶ 예시 -  가격(price)과 수량(amount)을 곱한 실제 구매액을 표시하는 SQL

 

SELECT num, CONCAT(CAST(price AS CHAR), 'X', CAST(amount AS CHAR), '=') 
		'가격X수량', price*amount '구매액'
    FROM buy ;

 

· 실행 결과: 

num	가격X수량	구매액
1	30X2=	60
2	1000X1=	1000
3	200X1=	200

· 가격(price)과 수량(amount)은 정수지만, CAST() 함수를 통해 문자로 바뀌었다.

  CONCAT() 함수는 문자를 이어주는 역할을 하며, 여기서는 '30X2='과 같은 형태의 문자로 만들어 출력한다.

 

암시적인 변환

· 암시적인 변환은 CAST()나 CONVERT() 함수를 사용하지 않고도 자연스럽게 형이 변환된다.

 

▶ 예시 

 

SELECT '100' + '200' ;

· 실행 결과: 300

· 문자는 더할 수 없으므로 '100'과 '200'을 자동으로 숫자 100과 200으로 변환해서 덧셈을 수행한다.

 

▶ 예시

 

SELECT CONCAT('100', '200');

· 실행 결과: 100200

· 문자 '100'과 '200'을 연결한 '100200'으로 만들려면 CONCAT() 함수를 사용한다.

 

▶ 예시 

 

SELECT CONCAT(100, '200');
SELECT 100 + '200' ;

· 실행 결과:

100200

300

 

· 숫자와 문자를 CONCAT() 함수로 연결하면, 숫자가 문자로 변환되어 연결된다.

· CONCAT() 함수를 사용하지 않고 숫자 100과 문자 '200'을 더하면, 뒤의 문자가 숫자 200으로 자동 변환되어 300으로 출력된다.

 

출처

혼자 공부하는 SQL