SECTION 1 데이터 형식의 종류
1. 데이터 형식의 개요
데이터 형식은 데이텨형, 자료형, 데이터 타입 등으로 불린다.
SELECT 문을 잘 활용하고 테이블 생성을 효율적으로 하려면 데이터 형식을 알고 이해해야 한다.
2. 데이터 형식의 종류
2.1 숫자 데이터 형식
숫자 데이터 형식은 정수, 실수 등의 숫자를 저장한다.
DECIMAL 형식은 저오학한 수치를 저장하고
FLOAT, REAL 형식은 근사치를 저장한다.
대신 FLOAT, REAL 형식은 큰 숫자를 저장할 수 있다.
소수점이 있는 실수는 되로록 DECIMAL 형식을 사용하여 저장하는 것이 바람직하다.
| 데이터형식 | 바이트수 | 숫자 범위 | 설명 |
|---|---|---|---|
| BIT(N) | N/B | 1~64bit 표현, b'0000'형식으로 저장 | |
| TINYINT | 1 | -128~127 | 정수 저장 |
| BOOL and BOOLEAN | 1 | -128~127 | 정수 저장, TINYINT(1)과 동일, 0= false, 1=true |
| SMALLINT | 2 | -32768~32767 | 정수저장 |
| MEDIUMINT | 3 | -8388608~8388607 | 정수저장 |
| INT / INTEGER | 4 | 약 -21억 ~ 21억 | 정수저장 |
| BIGINT | 8 | 약 -900경 ~ 900경 | 정수저장 |
| FLOAT | 4 | -3.40E+38~-1.17E-38 | 소수점 이하 7자리까지 저장 |
| DOBULE / REAL | 8 | -1.22E-308~-1.79E+308 | 소수점 이하 15자리까지 저장 |
| ---------------------- | ----------- | ---------------------- | ---------------------- |
| DECIMAL(m,[d]) | |||
| DEC(m,[d]) | 5~17 | -1038+1~1038-1 | 전체 자릿수(m)와 소수점 이하 자릿수(d)를 가진 숫자 저장 |
| FIXED(m,[d]) | 예) DECIMAL(5,2)는 전체 자릿수를 5자리로 하되, | ||
| NUMRIC(m,[d]) | 그중 소수점 이하를 2자리로 하겠다는 뜻 |
MySQL은 부호 없는 정수를 지원한다.
TINYINT는 0~255, SMALLINT는 0~65535, MEDIUMINT는 0~16777125, INT는 0~약 42억, BIGINT는 0~약 1800경을 표현 가능하다.
부호 없는 정수를 지정할 때는 뒤에 UNSIGNED 예약어를 붙인다.
2.2 문자 데이터 형식
문자 데이터 형식은 영문이나 한글 등의 문자를 저장한다.
CHAR 형식은 고정 길이 문자형을 저장하고 자릿수가 고정되어 있다.
CHAR(100)에 'ABC'를 저장하면 100자리를 모두 확보한 후 앞의 3자리를 사용하고 97자리는 비워둔다.
VARCHAR(100)에 'ABC'를 저장하면 내부적으로는 3자리만 사용한다. 따라서 저장공간을 효율적으로 운영할 수 있다.
하지만, 일반적으로 INSERT문과 UPDATE 문을 사용할 때는 CHAR 형식으로 데이터 형식을 설정하는 것이 더 좋은 성능을 발휘한다.
VARCHAR를 사용하는 경우 쿼리문에서 문자의 길이를 확인하는 연산이 들어가기때문에 성능에서 효율적이지 못하다.
| 데이터형식 | 바이트수 | 설명 |
|---|---|---|
| CHAR(n) | 1~255 | 고정 길이 문자형 저장, CHAR만 쓰면 CHAR(1)과 동일 |
| VARCHAR(n) | 1~65535 | 가변길이 문자형 저장 |
| BINARY(n) | 1~255 | 고정 길이의 이진 데이터 값 저장 |
| VARBINARY | 1~255 | 가변 길이의 이진 데이터 값 저장 |
| TINYTEXT | 1~255 | 255크기의 TEXT 데이터 값 저장 |
| TEXT | 1~65535 | N 크기의 TEXT 데이터 값 저장 |
| MEDIUMTEXT | 1~16777215 | 16777215 크기의 TEXT 데이터 값 저장 |
| LONGTEXT | 1~4294967295 | 최대 4GB 크기의 TEXT 데이터 값 저장 |
| TINYBOLOB | 1~255 | 255 크기의 BLOB 데이터 값 저장 |
| BLOB | 1~65535 | N 크기의 BLOB 데이터 값 저장 |
| MEDIUMBLOB | 1~16777215 | 16777215 크기의 BLOB 데이터 값 저장 |
| LONGBLOB | 1~4294967295 | 최대 4GB 크기의 BLOB 데이터 값 저장 |
| ENUM(값들...) | 1 또는 2 | 최대 65535개의 열거형 데이터 값 저장 |
| SET(값들...) | 1,2,3,4,8 | 최대 64개의 서로 다른 데이터 값 저장 |
BINARY, VARBINARY : 바이트 단위의 이진 데이터 값을 저장
TEXT 형식은 대용량 글자를 저장하기 위한 형식, 크기에 따라 TINYTEXT, TEXT, MEDIUTMTEXT, LONGTEXT 등을 사용
BLOB (Binary Large OBject) 형식은 사진, 동영상, 문서 파일 등의 대용량 이진 데이터를 저장하는데 사용
ENUM 형식은 열거형 데이터를 저장하는 데 사용,
SET 형식은 최대 64개의 데이터를 2세트씩 세트로 묶어 저장할 때 사용.
2.3 날짜와 시간 데이터 형식
| 데이터형식 | 바이트수 | 설명 |
|---|---|---|
| DATE | 3 | 'YYYY-MM-DD'형식으로 날짜 저장 |
| TIME | 3 | 'HH:MM:SS'형식으로 시간 저장 |
| DATETIME | 8 | 'YYYY-MM-DD HH:MM:SS'형식으로 날짜와 시간 저장 |
| TIMESTAMP | 4 | 'YYYY-MM-DD HH:MM:SSS'형식으로 저장, time_zone 시스템 변수와 관련이 있으며, UTC 시간대로 변환하여 저장 |
| YEAR | 1 | 'YYYYY'형식으로 연도 저장 |
CAST() 함수는 지정된 데이터 형식으로 값을 변경해준다.
SELECT CAST('2020-10-19 12:35:29.123' AS DATE) AS 'DATE';
SELECT CAST('2020-10-19 12:35:29.123' AS TIME) AS 'TIME';
SELECT CAST('2020-10-19 12:35:29.123' AS DATETIME) AS 'DATETIME';DATE : 2020-10-19
TIME : 12:35:29
DATETIME : 2020-10-19 12:35:29
2.4 기타 데이터 형식
| 데이터형식 | 바이트수 | 설명 |
|---|---|---|
| GEOMETRY | ||
| POINT | ||
| LINESTRING | N/A | 공간 데이터를 저장하는 형식으로 선, 점, 다각형과 같은 공간 데이터 개체를 저장 |
| POLYGON | ||
| ------ | ----- | ------------------------------------------------------------- |
| JSON | 8 | JSON 문서 저장 |
SECTION 2 변수와 형 변환
1. 변수의 선언과 활용
SQL도 다른 언어처럼 변수를 선언하고 사용할 수 있다.
SET @변수이름 = 변수 값; -- 변수 선언 및 값 대입
SELECT @변수이름 -- 변수 값 출력실습
SET @myVar1 = 5;
SET @myVar2 = 3;
SET @myVar3 = 4.25;
SET @myVar4 = 'MC 이름 ==>';변수 선언하기
SELECT @myVar1;
SELECT @myVar2 + @myVar3;변수 프린트하기
SELECT @myVar4 , userName FROM userTBL WHERE height > 180;
LIMIT 절에는 원칙적으로 변수를 사용할 수 없으나 PREPARE 문과 EXECUTE 문을 활용하면 가능하다.
SET @myVar1 = 3;
PREPARE myQuery
FROM 'SELECT userName, height FROM userTBL ORDER BY height LIMIT ?';
EXECUTE myQuery USING @myVar1;
PREPARE 쿼리이름 FROM '쿼리문'은 쿼리이름에 쿼리문을 준비만하고 실행하지 않는다.
EXECUTE 쿼리이름 구문을 만나는 순간 실행된다.
데이터 형식과 형 변환
2.1 데이터 형식 변환 함수
일반적으로 사용되는 데이터 형식 변환 함수는 CAST()와 CONVERT()이다.
CAST(expression AS 데이터형식 [(길이)])
CONVERT(expression, 데이터형식 [(길이)])아래와 같이 사용할 수 있다.
SELECT CAST(AVG(amount) AS SIGNED INTEGER) FROM buyTBL;
SELECT CONVERT(AVG(amount), SIGNED INTEGER) FROM buyTBL;CAST 함수를 사용해 ($,/,%,@)구분자를 날짜 형식으로 바꿀 수 있다.
ex)
SELECT CAST('2020$12$12' AS DATE);2.2 암시적 형 변환
데이터 형식을 변환하는 것을 형 변환이라고 한다.
형 변환 방식은 명시적과 암시적으로 나눈다.
명시적은 앞에 나온 CAST, CONVERT 같은 함수를 이용해 데이터 형 변환을 하는 것이고,
암시적 형 변환은 사용하지 않고 진행한다.
SELECT '100' + '200'; -- 문자와 문자를 더함(정수로 변환한 후 처리)
SELECT CONCAT('100', '200'); --문자와 문자를 연결 (문자열 그대로 처리)
SELECT CONCAT(100, '200'); -- 정수와 문자를 연결(정수를 문자로)
SELECT 1 > '3mega'; -- 정수인 3으로 변환후 비교
SELECT 4 < '3MEGA'; -- 정수인 3으로 변환후 비교
SELECT 0 = 'mega3'; -- 문자가 0으로 변환됨
