Data Analysis/혼공SQL

[혼공SQL] 4-1 MySQL의 데이터 형식

알밤바 2022. 6. 23. 10:49
728x90
반응형

본 포스팅은 '한빛출판네트워크'의 '혼공SQL' 책을 기반으로 작성한 포스팅입니다.

1. 데이터 형식

1.1 정수형

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

 

  • 정수형 데이터를 확인하기 위해 테이블을 만들어보자.
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, 90000000000000000);

 

  • 최댓값에서 1을 더해보자.
insert into hongong4 values (128, 32768, 2147483648, 90000000000000001);

→ 입력값의 범위를 벗어났다는 오류가 발생함

 

📌 UNSIGNED 예약어

  • 값의 범위가 0부터 시작됨
  • 예시
    • TINYINT : -128 ~ 127 (1바이트 크기)
    • TINYINT UNSIGNED : 0 ~ 255 (1바이트 크기)

 

1.2 문자형

  • 문자형은 글자를 저장하기 위해 사용
  • 입력할 최대 글자의 개수를 지정

데이터 형식 바이트 수

데이터 형식 바이트 수
CHAR(개수) 1~255
VARCHAR(개수) 1~16383
  • CHAR
    • 고정길이 문자형 (자릿수가 고정)
    • MySQL 내부적으로 성능이 좋음 (속도가 빠름)

 

  • VARCHAR
    • 가변길이 문자형
    • CHAR보다 공간을 효율적으로 운영할 수 있음

 

  • CHAR은 글자의 개수가 고정된 경우, VARCHAR은 글자의 개수가 변동될 경우 사용하면 좋음
  • 전화번호의 경우, 모두 숫자로 이루어져 정수형으로 지정해야 할 것 같지만,
  • 숫자로서 의미가 없기에 문자형으로 지정
  • 숫자로서 의미를 가지기 위한 조건
    • 더하기/뺴기 등의 연산에 의미가 있음
    • 크다/작다 또는 순서에 의미가 있음

 

1.3 대량의 데이터 형식

create table big_table (
	data1 char(256),    --char은 255까지
    data2 varchar(16384));   --varchar은 16383까지

→ 열의 길이를 기준 이상으로 두어 에러가 발생함

 

  • 대량의 데이터 형식
  데이터 형식 바이트 수
TEXT 형식 TEXT 1 ~ 645535
  LONGTEXT 1 ~ 4294967295
BLOB 형식 BLOB 1 ~ 65535
  LONGBLOB 1 ~ 4294967295

 

  • TEXT : 글자 / LONGTEXT는 주로 소설이나 영화 대본과 같은 내용을 저장할 때 필요한 형식
  • BLOB : Binary Long Object / 이미지, 동영상 등의 데이터 (이진 데이터)

 

  • 대량의 데이터 테이블 예제
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,  -- 자막 (최대 4GB 입력)
  movie_film     longblob)  -- 동영상 (최대 4GB 입력)

 

1.4 실수형

  • 소수점이 있는 숫자를 저장할 때 사용
  • 과학 기술용 데이터가 아닌 이상 FLOAT면 충분함
  • 실수형 예시 : 시력 (2.0, 1.5, 0.7,...)
데이터 형식 바이트 수 설명
FLOAT 4 소수점 아래 7자리까지 표현
DOUBLE 8 소수점 아래 15자리까지 표현

 

1.5 날짜형

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

 

2. 변수의 사용

  • SQL도 다른 일반적인 프로그래밍 언어처럼 변수를 선언하고 사용할 수 있음
set @변수이름 = 변수의 값 ;     -- 변수의 선언 및 값 대입
select @변수이름;               -- 변수의 값 출력

 

  • 변수는 MySQL 워크벤치를 재시작할 때까지는 유지되지만, 종료하면 없어짐 (임의로 사용)
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;  -- 테이블 조회 시 변수 활용

 

  • LIMIT에는 변수를 사용할 수 없음
  • 이를 해결하는 것이 PREPARE과 EXECUTE임
  • PREPARE은 실행하지 않고 SQL만 준비해 놓고 EXECUTE에서 실행하는 방식
set @count = 3;       -- 변수에 값 대입
-- mySQL이라는 이름으로만 준비
-- ? : 현재는 모르지만 나중에 채워지는 수
prepare mySQL from 'select mem_name, height from member order by height limit ?' ;
-- mySQL에 저장된 select 문을 실행할 때 USING으로 물음표에 변수의 값을 대입
execute mySQL using @count ;

 

3. 데이터 형 변환

  • 명시적인 변환 (explicit conversion) : 직접 함수를 사용하여 변환
  • 암시적인 변환 (implicit conversion) : 별도의 지시 없이 자연스럽게 변환

 

3.1 명시적인 변환

  • 데이터 형식을 변환하는 함수 : CAST(), CONVERT()
  • (형식만 다를 뿐 동일한 기능을 함)
CAST ( 값 AS 데이터_형식 [ (길이) ] )
CONVERT (값, 데이터_형식 [ (길이) ] )

 

  • 평균가격이 실수로 표현됨
select avg(price) as '평균가격' from buy;

 

  • 가격이니 실수가 아닌 정수로 표현해보자.
    • CAST(), CONVERT() 함수 사용
    • 함수 안에 올 수 있는 데이터 형식 : CHAR, SIGNED, UNSIGNED, DATE, TIME, DATETIME 등
    • SIGNED : 부호가 있는 정수, UNSIGNED : 부호가 없는 정수
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('2020@12@12' as date);

 

  • 가격(price)과 수량(amount)을 곱한 실제 구매액 표시
select num, concat(cast(price as char), 'x', cast(amount as char), '=') '가격x수량',
	price*amount '구매액'
  from buy;

  • 가격과 수량은 정수지만 CAST()함수를 통해 문자로 바꿈
  • CONCAT() 함수는 문자를 어어주는 역할을 함

 

3.2 암시적인 변환

CAST()나 CONVERT() 함수를 사용하지 않고도 자연스럽게 형이 변환되는 것

 

  • 문자를 더할 수 없으므로 자동으로 숫자로 변환
select '100' + '200';

 

  • '100200' 만들기
select concat('100', '200');

 

  • 숫자형과 문자형 더하기
select concat (100, '200');

 

select 100 + '200';

▶ 숫자와 문자를 연산할 때 CONCAT()을 사용하면 숫자가 문자로 변하고, 더하기만 사용하면 문자가 숫자로 변한 후에 연산됨

728x90
반응형