[SQL 기초] 7. 고급 문법, MySQL의 데이터 형식

테이블을 만들 때 데이터 형식 설정은 필수이다.

여기서 데이터 형식에는 크게 숫자형, 문자형, 날짜형이 있는데, 이렇게 다양한 데이터 형식이 존재하는 이유는 실제로 저장될 데이터의 형태가 다양하기 때문에 효율성을 높이기 위해서이다.

 

1. 정수형

:  소수점이 없는 숫자의 저장형

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

 

(1) 사용 예시

member_num (멤버 수)-> TINYINT

height_avg (키 평균)-> SMALLINT

 

(2) 예약어 UNSIGNED 

height_avg는 SMALLINT로 지정해서 -32,768~32,767까지 저장할 수 있지만,

키가 3만까지 클 수가 없으므로 TINYINT를 고려해볼 수 있다. 

그러나 TINYINT는 -128~127로 키가 200cm인 사람을 고려할 수 없으므로 범위가 부족하다.

따라서 범위가 0부터 시작되게 만드는 UNSIGNED 예약어를 사용할 수있다.

**TINYINT UNSIGNED 범위: 0~255

 

(3) 정수형으로 사용될 때

- 더하기/빼기 등의 연산에 의미가 있음

- 크다/작다 또는 순서에 의미가 있음

 

 

2. 문자형

: 문자형은 글자를 저장하기위해 사용하며 입력할 글자의 갯수를 지정해야 한다

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

 

CHAR는 고정길이형 문자형으로, 자릿수가 고정되어있다.

VARCHAR는 가변길이형 문자열로 VARCHAR(10)에 '가나다' 3글자를 저장할 경우

3자리만 사용되며 나머지 7자리는 사라진다.

 

VARCHAR가 CHAR보다 공간을 효율적으로 운영할 수있지만, MySQL 내부적으로 성능(속도)면에선 CHAR로 설정하는 것이 조금 더 좋다.

 

(1) MySQL 코드 예시

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

 

phone1,2가 정수형으로 저장이 되면 첫번째 자리에 0이 온 전화번호는 0이 삭제되고 저장된다. 따라서 CHAR로 저장하는 것이 저장/조회하기에 효율적이다.

 

 

(2) LONGTEXT, LONGBLOB

거대한 양의 문자가 저장되어야 하는 상황엔?

예를 들어 넷플릭스의 자막과 동영상 이진수 정보가 MySQL에 통채로 저장되어야 하는 상황엔

VARCHAR의 16383 범위가 턱도 없이 부족할 것이다.

그럴때 사용되는 것이 LONGTEXT와 LONGBLOB이다.

 

**실습 쿼리

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
); 

 

 

3. 실수형 

실수형은 소수점이 있는 숫자를 저장할 때 사용한다.

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

 

 

4. 날짜형

날짜형은 날짜 및 시간을 저장할 때 사용합니다.

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

 

 

 

5. 변수의 사용

: SQL도 다른 프로그래밍 언어처럼 변수를 선언하고 사용할 수 있다. 변수의 선언과 값의 대입은 다음 형식을 따릅니다.

: 그러나 변수는 해당 사용자의 현재 쿼리창에서만 임시로 존재한다.

 

(1) 변수의 선언

SET @변수이름 = 변수의 값;

 

(2) 변수의 값 출력

SELECT @변수이름;

 

(3) 실습 예시

USE market_db;  -- market_db를 사용하여 실습할 것

SET @txt = '가수 이름==> ' ;
SET @height = 166;
SELECT @txt , mem_name FROM member WHERE height > @height ;

출력 결과

 

 

(4) PREPARE, EXECUTE

SET @count = 3;
SELECT mem_name, height FROM member ORDER BY height LIMIT @count;

 

: 해당 쿼리는 오류를 일으키는데, 그 이유는 'Prepared Statement에서 바인딩 변수를 사용하였을 때 쿼리의 문법 처리과정이 미리 선 수행되기 때문에 바인딩 데이터는 SQL문법적인 의미를 가질 수 없다' 라고 한다. (출저:https://webstone.tistory.com/50)

: 따라서 PREPARE, EXECUTE 문법을 통해 변수를 사용하도록 한다

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

 

: 정상작동되는 형태이다.

 

 

 

6. 데이터 형 변환

: 문자형을 정수형으로 바꾸거나, 정수형을 문자형으로 바꾸는 것을 데이터의 형 변환이라고 부른다. 

: 명시적인 변환/ 암시적인 변환

 

(1) 명시적인 변환

: 직접 함수를 사용해서 변환함

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

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

 

**실습 쿼리

  [1] 평균 가격을 조회한다

조회 결과

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

  해당 값의 소숫점을 없애기 위해서 실수형 -> 정수형 형 변환이 필요함.

 

  [2] CAST, CONVERT 쿼리 사용

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

  여기서 SIGNED는 양의 부호를 가진 정수형을 의미한다.

형변환 결과

 

(2) 암시적인 변환

: 별도의 지시 없이 자연스럽게 변환됨

 

**실습 쿼리

SELECT '100' + '200' ; -- 문자와 문자를 더함 (정수로 변환되서 연산됨)
SELECT CONCAT('100', '200'); -- 문자와 문자를 연결 (문자로 처리)
SELECT CONCAT(100, '200'); -- 정수와 문자를 연결 (정수가 문자로 변환되서 처리)