系统学习----数据类型介绍

数值类型

tinyint 1bytes
smallint 2bytes
mediumint 3bytes
int|intergint 4bytes
bigint 8bytes
float   4bytes  单精度
double 8bytes   双精度

示例1： int数据类型 （int宽度并不能限制）

create table test(id int(4));
insert test VALUES(11111111);
select * from test;
alter table test change a a int(5);

#创建表一个是默认宽度的int，一个是指定宽度的int(5) mysql> create table t1 (id1 int,id2 int(5)); Query OK, 0 rows affected (0.02 sec)
#像t1中插入数据1，1 mysql> insert into t1 values (1,1); Query OK, 1 row affected (0.01 sec)
#可以看出结果上并没有异常 mysql> select * from t1;

+‐‐‐‐‐‐+‐‐‐‐‐‐+  
| id1  | id2  |  
+‐‐‐‐‐‐+‐‐‐‐‐‐+  
|    1 |    1 |  
+‐‐‐‐‐‐+‐‐‐‐‐‐+  
row in set (0.00 sec) 

#那么当我们插入了比宽度更大的值，会不会发生报错呢？ mysql> insert into t1 values (111111,111111); Query OK, 1 row affected (0.00 sec)
#答案是否定的，id2仍然显示了正确的数值，没有受到宽度限制的影响

mysql> select * from t1;  
+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+  
| id1        | id2    |  
+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+  
| 0000000001 |  00001 |  
| 0000111111 | 111111 |  
+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+  
rows in set (0.00 sec) 

示例2：小数示例类型; 小数位会被限制，并且遵循四舍五入

#创建表的三个字段分别为float，double和decimal参数表示一共显示5位，小数部分占2位 mysql> create table t2 (id1 float(5,2),id2 double(5,2),id3 decimal(5,2)); Query OK, 0 rows affected (0.02 sec)
#向表中插入1.23，结果正常 mysql> insert into t2 values (1.23,1.23,1.23); Query OK, 1 row affected (0.00 sec)

mysql> select * from t2;  
+‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐+  
| id1  | id2  | id3  |  
+‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐+  
| 1.23 | 1.23 | 1.23 | 
+‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐+  
row in set (0.00 sec) 

#向表中插入1.234，会发现4都被截断了 mysql> insert into t2 values (1.234,1.234,1.234); Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t2;  
+‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐+  
| id1  | id2  | id3  |  
+‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐+  
| 1.23 | 1.23 | 1.23 |  
| 1.23 | 1.23 | 1.23 |  
+‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐+  
rows in set (0.00 sec) 

#向表中插入1.235发现数据虽然被截断，但是遵循了四舍五入的规则 mysql> insert into t2 values (1.235,1.235,1.235); Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t2;  
+‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐+  
| id1  | id2  | id3  |  
+‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐+  
| 1.23 | 1.23 | 1.23 |  
| 1.23 | 1.23 | 1.23 |  
| 1.24 | 1.24 | 1.24 |  
+‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐+  
rows in set (0.00 sec) 

实例3：时间数据类型

mysql> create table t4 (d date,t time,dt datetime);  
Query OK, 0 rows affected (0.02 sec) 
mysql> desc t4;  
+‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+  
| Field | Type     | Null | Key | Default | Extra |  
+‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+  
| d     | date     | YES  |     | NULL    |       |  
| t     | time     | YES  |     | NULL    |       |  
| dt    | datetime | YES  |     | NULL    |       |  
+‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+  
rows in set (0.01 sec) 
mysql> insert into t4 values (now(),now(),now());  
Query OK, 1 row affected, 1 warning (0.01 sec) 
mysql> select * from t4;  
+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+  
| d          | t        | dt                  |  
+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+  
| 2018‐09‐21 | 14:51:51 | 2018‐09‐21 14:51:51 |  
+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+  
row in set (0.00 sec) 
mysql> insert into t4 values (null,null,null);  
Query OK, 1 row affected (0.01 sec) 
mysql> select * from t4;  
+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+  
| d          | t        | dt                  |  
+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+  
| 2018‐09‐21 | 14:51:51 | 2018‐09‐21 14:51:51 |  
| NULL       | NULL     | NULL                |  
+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+  
rows in set (0.00 sec)

当插入NULL值时，TIMESTAMP数据类型会将当前数据插入

示例4：字符串数据类型：超过指定宽度的字符串内容会被截取

varchar数据类型在定义宽度的时候：不管字符串长度为多少，总是分配宽度值所对应的长度。
char数据类型在定义宽度的时候；字符串存储长度为多少，就分配多长的空间。

mysql> create table t9 (v varchar(4),c char(4));  
Query OK, 0 rows affected (0.01 sec) 
mysql> insert into t9 values ('ab  ','ab  ');  
Query OK, 1 row affected (0.00 sec) 
# 在检索的时候char数据类型会去掉空格  
mysql> select * from t9;  
+‐‐‐‐‐‐+‐‐‐‐‐‐+  
| v    | c    |  
+‐‐‐‐‐‐+‐‐‐‐‐‐+  
| ab   | ab   |  
+‐‐‐‐‐‐+‐‐‐‐‐‐+  
row in set (0.00 sec) 
# 来看看对查询结果计算的长度  
mysql> select length(v),length(c) from t9;  
+‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐+  
| length(v) | length(c) |  
+‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐+  
|         4 |         2 |  
+‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐+  
row in set (0.00 sec) 

# 当存储的长度超出定义的长度，会截断  
mysql> insert into t9 values ('abcd  ','abcd  ');  
Query OK, 1 row affected, 1 warning (0.01 sec) 
mysql> select * from t9;  
+‐‐‐‐‐‐+‐‐‐‐‐‐+  
| v    | c    |  
+‐‐‐‐‐‐+‐‐‐‐‐‐+  
| ab   | ab   |  
| abcd | abcd |  
+‐‐‐‐‐‐+‐‐‐‐‐‐+  
rows in set (0.00 sec)

示例5：set数据类型和enum数据类型

mysql> create table t10 (name char(20),gender enum('female','male'));  
Query OK, 0 rows affected (0.01 sec) 

# 选择enum('female','male')中的一项作为gender的值，可以正常插入  
mysql> insert into t10 values ('nezha','male');  
Query OK, 1 row affected (0.00 sec) 

# 不能同时插入'male,female'两个值，也不能插入不属于'male,female'的值  
mysql> insert into t10 values ('nezha','male,female');  
ERROR 1265 (01000): Data truncated for column 'gender' at row 1 

mysql> create table t11 (name char(20),hobby set('抽烟','喝酒','烫头','翻车'));  
Query OK, 0 rows affected (0.01 sec) 

# 可以任意选择set('抽烟','喝酒','烫头','翻车')中的项，并自带去重功能  
mysql> insert into t11 values ('yuan','烫头,喝酒,烫头');  
Query OK, 1 row affected (0.01 sec) 

mysql> select * from t11;  
+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+  
| name | hobby        |  
+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+  
| yuan | 喝酒,烫头     |  
+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+  
row in set (0.00 sec) 

# 不能选择不属于set('抽烟','喝酒','烫头','翻车')中的项，  
mysql> insert into t11 values ('alex','烫头,翻车,看妹子');  
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1