[혼자공부하는SQL] 9강

04-1. MySQL의 데이터 형식

0. 시작하기 전에

• 테이블을 만들 때는 테이터 형식을 설정해야 한다.
  • 데이터 형식 : 숫자형, 문자형, 날짜형
  • 실제로 저장될 데이터의 형태가 다양하기 때문에, 다양한 데이터 형식이 존재한다.

1. 데이터 형식

1) 정수형

• 정수형은 소수점이 없는 숫자를 의미한다.
  • 이름만 들어도 대략 길이가 올 것이다.

• 형태
• USE market_db; CREATE TABLE hongong4 ( tinyint_col TINYINT, smallint_col SMALLINT, int_Col INT, bigint_col BIGINT );
• 예시
• -- 정상 범위 INSERT INTO hongong4 VALUES (127, 32767, 2147483647, 9000000000000000000); -- 오류 범위 (벗어남) INSERT INTO hongong4 VALUES (128, 32768, 2147483648, 90000000000000000000);

(1) 정수형을 사용하는 예시

: 기존에 만들었던 member 테이블의 구조

-- 문제점 : 그룹 인원 수를 나타내는 mem_number, 평균 키를 나타내는 height의 범위가 너무 길다.
CREATE TABLE member -- 회원 테이블
( mem_id          CHAR(8) NOT NULL PRIMARY KEY, -- 사용자 아이디(PK)
  mem_name        VARCHAR(10) NOT NULL, -- 이름
  mem_number    INT NOT NULL,  -- 인원수
  addr              CHAR(2) NOT NULL, -- 지역(경기,서울,경남 식으로 2글자만입력)
  phone1        CHAR(3), -- 연락처의 국번(02, 031, 055 등)
  phone2        CHAR(8), -- 연락처의 나머지 전화번호(하이픈제외)
  height        SMALLINT,  -- 평균 키
  debut_date    DATE  -- 데뷔 일자
);

• mem_number : INT → TINYINT
• height : TINYINT → TINYINT UNSIGNED
  • UNSIGNED 예약어 : 값의 범위가 0부터 시작된다.

2) 문자형

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

(1) 종류

데이터 형식	바이트 수	특징
CHAR(개수)	1~255	고정형 문자형
VARCHAR(개수)	1~16383	가변형 문자형
- BINARY, VARBINARY도 있지만, 잘 사용하지 않는다.

(2) CHAR의 공간 낭비

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

(3) 연락처의 경우 정수형? 문자형?

• 모두 숫자로 이루어져 있어서, 정수형으로 지정해야 할 것 같지만, CHAR로 지정되어 있다.
• 전화번호가 숫자로서 의미가 없기 때문이다.
  • 전화번호는 문자형이 더 효과적이기는 하지만, 정수형으로 지정했다고 해서 반드시 틀렸다고 할 수는 없다. 효율성이나 타당성 면에서 좀 더 비효율적일 뿐이다.
• 숫자로서 의미를 가지려면 아래의 두 가지 중 하나는 충족해야 한다.
  1. 더하기/빼기 등의 연산에 의미가 있다.
  2. 크다/작다 또는 순서에 의미가 있다.

3) 대량의 데이터 형식

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

(1) 오류의 경우

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

(2) 해결책

• LONGTEXT : 최대 4GB
  • 대량의 텍스트
• LONGBLOB : 최대 4GB
  • 대량의 이진 데이터

(3) 궁금증

Q. VARCHAR는 16383까지 가능하다고 했는데, 왜 안 되는건지 궁금해.

A. VARCHAR(16383) 자체는 가능하지만, 한 행의 최대 크기(65535바이트)를 초과할 가능성이 있음. CHAR(255)가 255바이트를 차지하고, VARCHAR(16383)는 16385바이트(길이 정보 포함)를 차지하여 합계가 커지기 때문. 해결 방법은 CHAR(255)를 VARCHAR(255)로 변경, VARCHAR(16383) 크기 조정, 또는 ROW_FORMAT=DYNAMIC 사용.

(4) 예시

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

4) 실수형

(1) 형식

데이터 형식	바이트 수	설명
FLOAT	4	소수점 아래 7자리까지 표현
DOUBLE	8	소수점 아래 15자리까지 표현
- 과학 기술용 데이터가 아닌 이상 FLOAT이면 충분하다.

5) 날짜형

데이터 형식	바이트 수	설명
DATE	3	날짜만 저장, YYYY-MM-DD 형식으로 사용
TIME	3	시간만 저장, HH:MM:SS 형식으로 저장
DATETIME	8	날짜 및 시간을 저장, YYYY-MM-DD HH:MM:SS 형식으로 저장
- 내부적으로 데이터양이 다르기 떄문에, 필요에 따라 적절한 데이터 형식을 사용하는 것이 중요하다.

2. 변수의 사용

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

• 사용자가 다르면, 임시로된 변수들을 찾을 수 없게 되는 문제가 발생할 수 있다.

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

 

(2) 사용 예시

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

-- 문법 상 안 되는 경우
SET @count = 3;
SELECT mem_name, height FROm member ORDER BY height LIMIT @count;

 

(3) PREPARE / EXECUTE

• 변수를 물음표(?)를 사용하여 지정하고, 필요할 때 EXECUTE로 사용하고 USING으로 변수를 할당한다.

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

 

3. 데이터 형 변환

• 데이터의 형 변환 : 문자형을 정수형으로 바꾸거나, 정수형을 문자형으로 바꾸는 것
• 종류
  • 명시적인 변환(explicit conversion) : 직접 함수를 사용해서 변환하는 방법
  • 암시적인 변환(implicit conversion) : 별도의 지시 없이 자연스럽게 변환되는 방법

1) 함수를 이용한 명시적인 변홤

(1) 데이터 형식을 변환하는 함수

(1-1) 형식

• CAST()
• CONVERT()

(1-2) 형식

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

 

(3) 예시 

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

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

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

  -- CONCAT : 문자열을 잇는다.
  SELECT NUM,  CONCAT(CAST(price AS CHAR), 'X', CAST(amount AS CHAR), '=') '가격X수량', price * amount '구매액' FROM buy;

2) 암시적인 형 변환

(1) 예시

-- 문자와 문자를 더함 (쩡수로 변환되서 연산됨)
SELECT '100' + '200';
-- 문자와 문자를 연결 (문자로 처리)
SELECT CONCAT('100', '200');
-- 정수와 문자를 연결 (정수가 문자로 변환되서 처리)
SELECT CONCAT(100, '200');
-- 정수인 2로 변환되어서 비교
SELECT 1 > '2mega';
-- 정수인 2로 변환되어서 비교
SELECT 3 > '2MEGA';
-- 문자는 0으로 변환됨
SELECT 0 = 'mega2';