[mySQL5.5] 06장. Data Types

n  Numeric 타입

분류	Type	storage	Signed range	Unsigned Range
정수	TINYINT	1byte	-128 ~ 127	0 ~ 255
	SMALLINT	2bytes	-32,768 ~ 32,767	0 ~ 65,535
	MEDIUMINT	3 bytes	-8,388,608~8,388,607	0 ~ 16,777,215
	INT	4 bytes	-2,147,683,648~2,147,683,647	0 ~ 4,294,967,295
	BIGINT	8 bytes
소수 (Floating-Point)	FLOAT	4 bytes	-3.402823466E+38~-1.175494351E-38	0 과 1.175494351E-38 ~ 3.402823466E+38
	DOUBLE	8 bytes
Fixed-Point	DECIMAL	9digit당: 4bytes사용	DECIMAL(P,S) P :precision , <=65 S : scale, <=30	DECIMAL(5,2) -999.99 ~ +999.99
BIT	BIT(n)	»INT((n+7)/8)	0 ~ 2**n -1

 

 

n  실습 1

 

1. test데이터베이스를 사용합니다.

mysql> use test;

Database changed

 

2. my_table을 생성한 후, 테스트합니다.

mysql> create table my_table(n SMALLINT unsigned);

Query OK, 0 rows affected (0.00 sec)

 

mysql> insert into my_table values(5);

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from my_table;

+------+

| n    |

+------+

|    5 |

+------+

3. 이번에는 음수를 입력하지만, 오류가 발생하지 않음을 확인합니다.

mysql> insert into my_table values(-5);

Query OK, 1 row affected, 1 warning (0.00 sec)

 

mysql>  select * from my_table;

+------+

| n    |

+------+

|    5 |

|    0 |

+------+

2 rows in set (0.00 sec)

 

4. sql_mode를 restricted모드로 설정하면, 에러가 발생합니다.

mysql> set sql_mode='traditional';

 

mysql> insert into my_table values(-5);

ERROR 1264 (22003): Out of range value for column 'n' at row 1

 

mysql> insert into my_table values(70000);

ERROR 1264 (22003): Out of range value for column 'n' at row 1

 

5. 이번에는 고정소수와 유동 소수에 대해 테스트합니다.

mysql> create table floating(n float,n2 decimal(7,6));

 

6. 데이터를 insert 한 후 조회합니다.

mysql> insert into floating values(0.99,0.99);

 

mysql> select * from floating;

+------+----------+

| n    | n2       |

+------+----------+

| 0.99 | 0.990000 |

+------+----------+

 

7. 갱신작업도 수행해 봅니다.

mysql> update floating

    -> set n=n-0.00001,n2=n2-0.00001;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

 

mysql> select * from floating;

+---------+----------+

| n       | n2       |

+---------+----------+

| 0.98999 | 0.989990 |

+---------+----------+

 

8. floating을 조회하지만, empty set이 반환됩니다.(유동소수는 approximate값이기 때문)

mysql> select * from floating where n=0.98999;

Empty set (0.00 sec)

 

고정소수는 항상 정확하므로, 잘 조회됩니다.

mysql>  select * from floating where n2=0.98999;

+---------+----------+

| n       | n2       |

+---------+----------+

| 0.98999 | 0.989990 |

+---------+----------+

1 row in set (0.00 sec)

 

9. 유동 소수를 조회해 봅니다.

mysql>  select * from floating where n BETWEEN 0.98999 -0.00001 AND 0.98999 +0.00001;

+---------+----------+

| n       | n2       |

+---------+----------+

| 0.98999 | 0.989990 |

+---------+----------+

1 row in set (0.00 sec)

 

10. bit타입을 테스트합니다.

mysql> create table bits(b BIT(10));

Query OK, 0 rows affected (0.00 sec)

 

mysql> insert into bits values(b'101');

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from bits;

+------+

| b    |

+------+

|  ^E   |

+------+

1 row in set (0.00 sec)

 

mysql> update bits set b=b'1001';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

 

mysql>  select * from bits;

+------+

| b    |

+------+

|          |

+------+

1 row in set (0.00 sec)

 

11. 0을 더하면 이진수를 10진수로 변환해서 표시합니다.

mysql> select b+0 from bits;

+------+

| b+0  |

+------+

|    9 |

+------+

1 row in set (0.00 sec)

 

 

n  Character 타입

비교	Type	storage	특징	최대 길이
Text	CHAR(M)	M characters	fixed	255bytes
	VARCHAR(M)	M characters+1,2bytes	Variable-length	65,535bytes
	TEXT	#characters+2bytes	Variable-length	65,535bytes
정수	ENUM	255개 값당 : 1 byte	-2,147,683,648~2,147,683,647	65,535개 값
	SET	8개값당 : 1byte		64개

 

 

n  실습 2 : char, varchar, sets, ENUM테스트

 

1. test 데이터베이스를 사용합니다.

mysql> use test;

Database changed

 

2. varchar타입의 테이블을 생성합니다.

mysql> create table chars(c varchar(5));

Query OK, 0 rows affected (0.00 sec)

 

3. 데이터 insert 후에 조회합니다. VARCHAR는 trailing-space가 저장되지 않습니다.

mysql> insert into chars values('abc');

mysql> select * from chars;

+------+

| c    |

+------+

| abc  |

+------+

1 row in set (0.00 sec)

 

4. ENUM 타입을 가진 테이블을 생성합니다.내부적으로 정수로 변환되어 저장되며, 컬럼 선언에 따라 순서대로 인덱스가 부여됩니다.

mysql> create table t(numbers ENUM('0','1','2'));

Query OK, 0 rows affected (0.00 sec)

 

mysql> select * from t;

Empty set (0.00 sec)

 

5.  인덱스 2인 즉 ‘1’ 값을 저장 한 후 조회합니다.

mysql> insert into t (numbers) values(2);

Query OK, 1 row affected (0.00 sec)

 

mysql>  select * from t;

+---------+

| numbers |

+---------+

| 1       |

+---------+

1 row in set (0.00 sec)

 

6. ‘2’를 저장하면, ENUM에 명시된 값이므로 잘 저장됩니다.

mysql> insert into t (numbers) values('2');

mysql> select * from t;

+---------+

| numbers |

+---------+

| 1       |

| 2       |

+---------+

2 rows in set (0.00 sec)

 

7. ‘3’은 ENUM에 명시된 값이 아니므로, 무시되고 index 3인 값 ‘2’가 저장됩니다.

mysql> insert into t (numbers) values('3');

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from t;

+---------+

| numbers |

+---------+

| 1       |

| 2       |

| 2       |

+---------+

3 rows in set (0.00 sec)

 

8. sql_mode를 설정합니다.

mysql> set sql_mode='traditional';

 

9. sets 타입을 가진 테이블을 생성합니다.

mysql> create table sets(name varchar(20),colors set('red','blue','green'));

 

10.  sets 타입을 가진 데이터를 insert후 조회합니다.

mysql> insert into sets values('Sarah','red,blue');

mysql> select * from sets;

+-------+----------+

| name  | colors   |

+-------+----------+

| Sarah | red,blue |

+-------+----------+

1 row in set (0.00 sec)

 

11. 10번과 순서를 달리 하여 sets데이터를 입력하지만, sets는 unordered특성을 가지므로, 동일한 결과로 조회된다.

mysql> insert into sets values('Sarah','blue,red');

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from sets;

+-------+----------+

| name  | colors   |

+-------+----------+

| Sarah | red,blue |

| Sarah | red,blue |

+-------+----------+

2 rows in set (0.00 sec)

 

12. sets에 등록안된 데이터 orange는 저장되지 않습니다.

mysql> insert into sets values('Sarah','orange');

ERROR 1265 (01000): Data truncated for column 'colors' at row 1

mysql> select * from sets;

+-------+----------+

| name  | colors   |

+-------+----------+

| Sarah | red,blue |

| Sarah | red,blue |

+-------+----------+

2 rows in set (0.00 sec)

n  Temporal 타입

데이터	Type	storage	Display Format	Range
시간	TIME	3 bytes	HH:MM:SS	‘-838:59:59’ ~ ‘838:59:59’
년도	YEAR[(2|4)]	1byte	YYYY	1901 ~2155,0000
날짜	DATE	3 bytes	YYYY-MM-DD	‘1000-01-01’ ~ ‘9999-12-31’
날짜+시간	DATETIME	8bytes	YYYY-MM-DD HH:MM:SS	‘1000-01-01 00:00:00’ ~ ‘9999-12-31 23:59:59’
날짜+시간	TIMESTAMP	4 bytes	YYYY-MM-DD HH:MM:SS	‘1970-01-01 00:00:00’ ~ ‘2037’

 

 

n  실습

mysql> use test;

Database changed

mysql> drop table my_table;

 

1. datetime 과 Timestamp타입을 비교해봅니다.

mysql> create table my_table(bdate datetime,bstamp TIMESTAMP);

 

mysql> insert into my_table values('1950-02-15',current_timestamp());

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from my_table;

+---------------------+---------------------+

| bdate               | bstamp              |

+---------------------+---------------------+

| 1950-02-15 00:00:00 | 2011-12-10 14:40:27 |

+---------------------+---------------------+

1 row in set (0.00 sec)

 

2. time_zone정보에 따라 Timestamp타입은 영향을 받습니다.

현재 time_zone을 확인합니다.

mysql> select @@global.time_zone,@@session.time_zone;

+--------------------+---------------------+

| @@global.time_zone | @@session.time_zone |

+--------------------+---------------------+

| SYSTEM             | SYSTEM              |

+--------------------+---------------------+

 

3. sesson 의 time_zone을 변경합니다.

mysql> set time_zone='+07:00';

 

mysql> select @@global.time_zone,@@session.time_zone;

+--------------------+---------------------+

| @@global.time_zone | @@session.time_zone |

+--------------------+---------------------+

| SYSTEM             | +07:00              |

+--------------------+---------------------+

1 row in set (0.00 sec)

 

 

4. 데이터를 조회합니다.

mysql> select * from my_table;

+---------------------+---------------------+

| bdate               | bstamp              |

+---------------------+---------------------+

| 1950-02-15 00:00:00 | 2011-12-10 20:40:27 |

+---------------------+---------------------+

1 row in set (0.00 sec)

 

5. 이번에는 time_zone을 1시간 더 변경합니다. time_zone에 따라 btimestamp만 값이 변경됨을 볼수 있습니다.

mysql>  set time_zone='+06:00';

 

mysql> select * from my_table;

+---------------------+---------------------+

| bdate               | bstamp              |

+---------------------+---------------------+

| 1950-02-15 00:00:00 | 2011-12-10 19:40:27 |

+---------------------+---------------------+

1 row in set (0.00 sec)

 

6. 이번에는 date format을 변경해봅니다.

ysql> select sysdate();

+---------------------+

| sysdate()           |

+---------------------+

| 2011-12-10 20:42:17 |

+---------------------+

1 row in set (0.00 sec)

 

mysql> select date_format(sysdate(),'%Y-%m-%d %H:%i:%s');

+--------------------------------------------+

| date_format(sysdate(),'%Y-%m-%d %H:%i:%s') |

+--------------------------------------------+

| 2011-12-10 20:43:33                        |

+--------------------------------------------+

1 row in set (0.00 sec)

 

mysql>  select date_format(sysdate(),'%Y-%M-%d %h:%i:%s %p');

+-----------------------------------------------+

| date_format(sysdate(),'%Y-%M-%d %h:%i:%s %p') |

+-----------------------------------------------+

| 2011-December-10 08:44:38 PM                  |

+-----------------------------------------------+

 

mysql>  select date_format(sysdate(),'%Y-%m-%d %T');

+--------------------------------------+

| date_format(sysdate(),'%Y-%m-%d %T') |

+--------------------------------------+

| 2011-12-10 20:45:27                  |

+--------------------------------------+

1 row in set (0.00 sec)

 

 

n  Character Set and Collation

n  예제

예제 1:  Table, Column character set

CREATE TABLE t1

(

    c1 CHAR(10) CHARACTER SET latin1 COLLATE latin1_german1_ci

) DEFAULT CHARACTER SET latin2 COLLATE latin2_bin;

 

 

예제 2: 데이터베이스와 character set

 

CREATE DATABASE d1

    DEFAULT CHARACTER SET latin2 COLLATE latin2_czech_ci;

 

USE d1;

CREATE TABLE t1

(

    c1 CHAR(10)

);

 

 

n  AUTO-INCREMENT

-      순번 자동 생성기

-      1번부터 생성, 테이블당 1개만 가능

-      LAST_INSERT_ID() : 가장 최근에 실행한 insert문에서 처음 생성한 값을 반환

 

mysql> use test;

Database changed

 

1. id컬럼을 AUTO_INCREMENT로 생성합니다.

mysql> CREATE TABLE animals (

    ->      id MEDIUMINT NOT NULL AUTO_INCREMENT,

    ->      name CHAR(30) NOT NULL,

    ->      PRIMARY KEY (id)

    -> ) ENGINE=MyISAM;

 

2. 4건을 입력후, 조회합니다.

mysql> INSERT INTO animals (name) VALUES

    ->     ('dog'),('cat'),('penguin'),

    -> ('lax');

Query OK, 4 rows affected (0.00 sec)

 

mysql> select * from animals;

+----+---------+

| id | name    |

+----+---------+

|  1 | dog     |

|  2 | cat     |

|  3 | penguin |

|  4 | lax     |

+----+---------+

4 rows in set (0.00 sec)

 

 

최초에 생성된 1 값을 반환합니다.

mysql> SELECT LAST_INSERT_ID();

+------------------+

| LAST_INSERT_ID() |

+------------------+

|                1 |

+------------------+

1 row in set (0.00 sec)

 

3. 이번에는 1을 insert한 후, LAST_INSERT_ID 함수를 호출합니다.

mysql> INSERT INTO animals (name) VALUES('Dolphin');

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from animals;

+----+---------+

| id | name    |

+----+---------+

|  1 | dog     |

|  2 | cat     |

|  3 | penguin |

|  4 | lax     |

|  5 | Dolphin |

+----+---------+

5 rows in set (0.00 sec)

 

mysql> SELECT LAST_INSERT_ID();

+------------------+

| LAST_INSERT_ID() |

+------------------+

|                5 |

+------------------+

1 row in set (0.00 sec)