mysql之数据类型

一 数值类型

　1 整数类型：应用场景，存放年龄，等级，id或者各种号码等等

　　TINYINT：1个字节存放，有符号范围：-128到127，没有符号范围：0，255 。

　　SMALLINT：2个字节存放，有符号范围：-32768到32767，没有符号范围：0到65535 。

　　MEDIUMINT：3个字节存放，有符号范围：-8388608到8388607，没有符号的范围：0到16777215 。

　　INT：4个字节存放，有符号范围：-2147483648到2147483647，没有符号的范围：0到4294967295 。

　　BIGINT：5个字节存放，有符号范围：-9233372036854775808到9233372036854775807，没有符号范围：0到18446744073709551615 。

　整型的宽度指的是显示宽度，并不是存储宽度。限制的显示宽度默认是11位。

mysql> create table t4(id int);
Query OK, 0 rows affected (0.46 sec)

mysql> desc t4;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.02 sec)

mysql> insert into t4 values(1111111111111111111111111111111111111111111);
Query OK, 1 row affected, 2 warnings (0.17 sec)

mysql> select * from t4;
+------------+
| id         |
+------------+
| 2147483647 |
+------------+
1 row in set (0.00 sec)

　　注意：如果在5.7中超过了该范围就会报错　　

什么是显示宽度：查看的时候显示的结果。

mysql> desc t3;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| id    | int(3) | YES  |     | NULL    |       |
+-------+--------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into t3(id)values(555555555);
Query OK, 1 row affected (0.04 sec)

mysql> select * from t3;
+-----------+
| id        |
+-----------+
| 555555555 |
+-----------+
1 row in set (0.00 sec)

　如果存放的值超过了最大的范围，就会显示该整型的最大值，默认的都是有符号的。

　unsiged：按照无符号的范围，将默认值变成无符号。

mysql> create table t4(id int);
Query OK, 0 rows affected (0.46 sec)

mysql> desc t4;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.02 sec)

mysql> insert into t4 values(1111111111111111111111111111111111111111111);
Query OK, 1 row affected, 2 warnings (0.17 sec)

mysql> select * from t4;
+------------+
| id         |
+------------+
| 2147483647 |
+------------+
1 row in set (0.00 sec)




mysql> create table t5(id int unsigned);
Query OK, 0 rows affected (0.45 sec)

mysql> desc t5;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> insert into  t5 values(11111111111111111111111111111111111111111);
Query OK, 1 row affected, 2 warnings (0.05 sec)

mysql> select * from t5;
+------------+
| id         |
+------------+
| 4294967295 |
+------------+
1 row in set (0.00 sec)

zerofill：长度不够定义的长度，就用0补全

mysql> create table t2(id int zerofill);
Query OK, 0 rows affected (0.23 sec)

mysql> insert into t2(id)values(333);
Query OK, 1 row affected (0.05 sec)

mysql> select * from t2;
+------------+
| id         |
+------------+
| 0000000333 |
+------------+
1 row in set (0.00 sec)

　2 浮点类型：应用场景，身高，价钱，水电费等等。

　　FLOAT：单精度，精确度最低，定义该类型时需要传入两个参数，第一个参数是数字的总个数；第二个参数传入的是小数点后面的数字的个数。第一个参数的最大值是255，第二个参数的最大值是30.。随着小数的增多，精确度会越来越不准确。

mysql> create table t5(x float(255,30));
Query OK, 0 rows affected (0.38 sec)

mysql> insert into t5 values(1.1111111111111111111111111111111111111);
Query OK, 1 row affected (0.04 sec)

mysql> select * from t5;
+----------------------------------+
| x                                |
+----------------------------------+
| 1.111111164093017600000000000000 |
+----------------------------------+
1 row in set (0.00 sec)

　　DOUBLE：双精度，浮点数值，定义该类型时还是需要传入两个参数，第一个参数时数字的总个数，第二个参数传入的是小数点后面数字的个数。第一个参数最大值也是255，第二个参数的最大值还是30，随着小数的增多，精确度会越来越低，但是比float类型要高一些。

mysql> create table t6(x double(255,30));
Query OK, 0 rows affected (0.22 sec)

mysql> desc t6;
+-------+----------------+------+-----+---------+-------+
| Field | Type           | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| x     | double(255,30) | YES  |     | NULL    |       |
+-------+----------------+------+-----+---------+-------+
1 row in set (0.03 sec)

mysql> insert into t6 values(1.111111111111111111111111111111111111111);
Query OK, 1 row affected (0.04 sec)

mysql> select * from t6;
+----------------------------------+
| x                                |
+----------------------------------+
| 1.111111111111111200000000000000 |
+----------------------------------+
1 row in set (0.00 sec)

　　DECIMAL：小数值，精确度最高，在定义该类型的时候还是需要传入两个参数，第一个参数传入的还是数字的总个数，第二个参数还是传入小数点后面数字的个数，第一个参数最大值是65，第二个参数最大值还是30。随着小数的增多，小数的精确度始终准确。对于精确值计算时需要用此类型。DECLMAL精确度一直准确的原因是因为在其内是用字符串类型存储的。

mysql> create table t7(x decimal(65,30));
Query OK, 0 rows affected (0.27 sec)

mysql> insert into t7 values(1.11111111111111111111111111111111111111111111111111111111111111);
Query OK, 1 row affected, 1 warning (0.04 sec)

mysql> select * from t7;
+----------------------------------+
| x                                |
+----------------------------------+
| 1.111111111111111111111111111111 |
+----------------------------------+
1 row in set (0.00 sec)

　3 bit类型：2进制显示；hex类型：8进制显示。

#了解：BIT
mysql> create table t9(x bit(1));
Query OK, 0 rows affected (0.34 sec)

mysql> insert into t9 values(2);
Query OK, 1 row affected, 1 warning (0.04 sec)

mysql> select * from t9;
+------+
| x    |
+------+
| 
    |
+------+
1 row in set (0.00 sec)

mysql> select bin(x) from t9;
+--------+
| bin(x) |
+--------+
| 1      |
+--------+
1 row in set (0.02 sec)

mysql> select hex(x) from t9;
+--------+
| hex(x) |
+--------+
| 1      |
+--------+
1 row in set (0.00 sec)

　　　　　　　　　　　　　　　　　　

除了整数类型以外，其他数据类型表示的都是存储上面的限制。

二 日期类型

　date：‘年-月-日’  表示年月日 ，该范围是（1001-01-01到9999-12-31）

　time：‘时：分：秒’  表示的是时分秒，该范围是（-838：59：59到838：59：59）

　datetime：‘年-月-日   时：分：秒’   前面两者的结合，该范围是（1001-01-01  00：00：00到9999-12-31   23：59：59）

　timestamp：‘年-月-日  时：分：秒’   和上面的限制功能是一样的，就是范围比上面的要小，该范围是（1970-01-01  00：00：00到2037 年的某一日）

　year：表示的是哪一年。该范围是（1901到2055）

mysql> create table t8(t time,
    ->                d date,
    ->                d_t datetime,
    ->                t_s timestamp,
    ->                y year);
Query OK, 0 rows affected (0.27 sec)

mysql> insert into t8 values('22:03:30','2017-10-23','1995-04-10  08:29:47','2008-08-08  20:08:08','1906');
Query OK, 1 row affected (0.03 sec)

mysql> select * from t8;
+----------+------------+---------------------+---------------------+------+
| t        | d          | d_t                 | t_s                 | y    |
+----------+------------+---------------------+---------------------+------+
| 22:03:30 | 2017-10-23 | 1995-04-10 08:29:47 | 2008-08-08 20:08:08 | 1906 |
+----------+------------+---------------------+---------------------+------+
1 row in set (0.00 sec)

应用场景：注册时间，文章发布会，生日等等。

datetime和timestamp的却别：1 datetime传入空时，默认为空；而timestamp传入的空时，默认为当前的时间戳。2 datetime的显示与时区有关；timestamp的显示与时区无关。

create table t11(
    x datetime,
    y timestamp
);

desc t11;

mysql> desc t11;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| x     | datetime  | YES  |     | NULL              |                             |
| y     | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
2 rows in set (0.01 sec)

mysql> insert into t11 values(null,null);
Query OK, 1 row affected (0.04 sec)

mysql> select * from t11;
+------+---------------------+
| x    | y                   |
+------+---------------------+
| NULL | 2017-10-23 10:25:07 |
+------+---------------------+
1 row in set (0.00 sec)



mysql> insert into t11 values('1011-11-11','1011-11-11');
Query OK, 1 row affected, 1 warning (0.05 sec)

mysql> select * from t11;
+---------------------+---------------------+
| x                   | y                   |
+---------------------+---------------------+
| NULL                | 2017-10-23 10:25:07 |
| 1011-11-11 00:00:00 | 0000-00-00 00:00:00 |
+---------------------+---------------------+

三 字符串类型

　1 char类型：定长，简单粗暴，但是浪费空间，存取的速度快。0到255字节之间。

　　如果超过了定义的长度，就只保留定义长度的范围，如果没有达到定义的长度，就会利用空格补全。

mysql> create table t10(x char(255));
Query OK, 0 rows affected (0.22 sec)

mysql> desc t10;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| x     | char(255) | YES  |     | NULL    |       |
+-------+-----------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> create table t11(x char);
Query OK, 0 rows affected (0.86 sec)

mysql> desc t11;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| x     | char(1) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

# 默认长度为1

　2 varchar类型：变长，精准，节省空间，存取速度慢，0到65535字节之间（注意：在5.7版本中）。

　　不管定义的宽度，如果超出范围，就会按照定义的长度存储，如果没有超出范围，就会按照传入的长度存储。

mysql> create table t12(x varchar(21844));
Query OK, 0 rows affected (0.28 sec)

mysql> desc t12;
+-------+----------------+------+-----+---------+-------+
| Field | Type           | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| x     | varchar(21844) | YES  |     | NULL    |       |
+-------+----------------+------+-----+---------+-------+
1 row in set (0.02 sec)

　　如果存储长度的超过了这个的最大限制，mysql会给你其他的类型。

　　存储和读取时都需要对标记进行操作，而标记的长度最长是65535字节，而一个标记只占用一个字节，最多只能有两个标记字节

　总结：如果咋定义的长度等于或者小于传入的长度，char比较节省内存空间；varchar反而会浪费空间；如果定义的长度在大于传入的长度，varchar会节省空间，而char会浪费空间。　　　

　　　　　　　　　　　　　　　　　　

　　还有一些不常用的字符类型：text   blob   binary等等。

　length：查看字节的长度

　char_length：查看字符的长度，这个查看的都是处理后的结果。

　　set sql_mode='pad_char_to_full_length'：自动填满char定义的长度，不会私自的删除。

mysql> create table t13(x char(10),
    ->                  y varchar(10));
Query OK, 0 rows affected (0.51 sec)

mysql> desc t13;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| x     | char(10)    | YES  |     | NULL    |       |
| y     | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into t13 values('fang','fang');
Query OK, 1 row affected (0.05 sec)

mysql> select * from t13;
+------+------+
| x    | y    |
+------+------+
| fang | fang |
+------+------+
1 row in set (0.03 sec)

mysql> select char_length(x),char_length(y) from t13;
+----------------+----------------+
| char_length(x) | char_length(y) |
+----------------+----------------+
|              4 |              4 |
+----------------+----------------+
1 row in set (0.00 sec)

mysql> set sql_mode='pad_char_to_full_length';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select char_length(x),char_length(y) from t13;
+----------------+----------------+
| char_length(x) | char_length(y) |
+----------------+----------------+
|             10 |              4 |
+----------------+----------------+
1 row in set (0.00 sec)

四 枚举类型和集合类型

　enum：单选，可以插入多个值，传入时只能传入一个值。传入的值不是插入值的范围，就会传入一个空。

　set：可以插入多个值，传入值时剋有传入多个，如果传入的内容不在该范围，就会传入一个空。

mysql> create table t14(name char(10),
    ->                  sex enum('male','female','男','女'),
    ->                  honnies set('ect','paly','wark','le','lanqiu'));
Query OK, 0 rows affected (0.23 sec)

mysql> insert into t14 values('fang','male','ect,paly,wark')
    -> ;
Query OK, 1 row affected (0.03 sec)

mysql> select * from t14;
+------------+------+---------------+
| name       | sex  | honnies       |
+------------+------+---------------+
| fang       | male | ect,paly,wark |
+------------+------+---------------+
1 row in set (0.00 sec)

　　default：设置一个默认值。

mysql> create table t15(sex enum('male','female','男','女') not null default 'female',
    ->                  aa set('cet','wark','paly') not null default 'cet');
Query OK, 0 rows affected (0.24 sec)

mysql> insert into t15 value();
Query OK, 1 row affected (0.04 sec)

mysql> select * from t15;
+--------+-----+
| sex    | aa  |
+--------+-----+
| female | cet |
+--------+-----+
1 row in set (0.00 sec)

　　这两个传入的参数最好都是字符串。

五 约束条件

　1 not null和null：null是为空，而not null是不为空。

　　default：设置默认值

　　unique：唯一

　2 key：加速数据查询

　　primary key：主键，一个表里面只能有一个主键，并且是不为空且唯一。

　　unique key：设置唯一，可以为空。

　　foreign key：建立表于表之间的关系。

　　auto_increment：自增。必须在定义key，一般在key后面使用，除foregin key以外。

　　　auto_increment_increment：定义长度

　　　auto_increment_offset：定义初始值

　　　这两个自带属性

　　　　set session：会话，一个会话等于一次连接

　　　　set global  ：表全局修改数据他们直接相互影响。

　　　　%：代替任意字符

　　　　根据自增有关的配置：初始值的那个值不能大于定义的步长。初始值一定要小于步长，步长默认为1.

　　　　详情：http://www.cnblogs.com/linhaifeng/articles/7238814.html

#基于会话级别
    set session auto_increment_increment=2 #修改会话级别的步长

    #基于全局级别的
    set global auto_increment_increment=2 #修改全局级别的步长（所有会话都生效）
    set global auto_increment_offset=2;

#初始值（auto_increment_offset）一定要 <= 步长（auto_increment_increment）

create table t4(id int primary key auto_increment,name char(10));

set global auto_increment_increment=3;
set global auto_increment_offset=2;


mysql> use day43;
Database changed
mysql> create table t5(id int primary key auto_increment,name char(10));
Query OK, 0 rows affected (0.35 sec)

mysql> desc t5;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(11)  | NO   | PRI | NULL    | auto_increment |
| name  | char(10) | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> show create table t5;
+-------+------------------------------------------------------------------------------------
| Table | Create Table
+-------+------------------------------------------------------------------------------------
| t5    | CREATE TABLE `t5` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------
1 row in set (0.00 sec)

mysql> insert into t5(name) values('aaaa');
Query OK, 1 row affected (0.08 sec)

mysql> select * from t5;
+----+------+
| id | name |
+----+------+
|  2 | aaaa |
+----+------+
1 row in set (0.00 sec)

mysql> insert into t5(name) values('bbbb');
Query OK, 1 row affected (0.06 sec)

mysql> select * from t5;
+----+------+
| id | name |
+----+------+
|  2 | aaaa |
|  5 | bbbb |
+----+------+
2 rows in set (0.00 sec)

　　constraint：定义约束条件，后面加上约束条件，括号里写入字段。

　　一张表只能够有一个主键，但是可以有多个非空且唯一的约束条件。而mysql将第一个非空且唯一的字段设置为主键。

　　在一张表中，not null unique可以有多个。

　　什么是联合唯一：在定义后，加上一个额约束条件，然后将需要约束的是字段添加到括号里面，多个字段同时在一起加上约束条件，就叫做联合唯一。

mysql> create table SIT(s_id int primary key auto_increment,
    -> s_name char(5) not null,
    -> gender enum('男','女') not null,
    -> caption char(10) not null,
    -> c_name CHAR(5) not null,
    -> number int not NULL,
    -> t_name char(5) not null,
    -> unique(s_name,caption,c_name,t_name));
Query OK, 0 rows affected (0.25 sec)

mysql> show tables;
+---------------+
| Tables_in_jie |
+---------------+
| class         |
| course        |
| fang          |
| fang1         |
| score         |
| sit           |
| student       |
| t1            |
| t2            |
| t3            |
| t4            |
| t5            |
| t7            |
| t8            |
| teacher       |
+---------------+
15 rows in set (0.00 sec)

mysql> desc sit;
+---------+-----------------+------+-----+---------+----------------+
| Field   | Type            | Null | Key | Default | Extra          |
+---------+-----------------+------+-----+---------+----------------+
| s_id    | int(11)         | NO   | PRI | NULL    | auto_increment |
| s_name  | char(5)         | NO   | MUL | NULL    |                |
| gender  | enum('男','女') | NO   |     | NULL    |                |
| caption | char(10)        | NO   |     | NULL    |                |
| c_name  | char(5)         | NO   |     | NULL    |                |
| number  | int(11)         | NO   |     | NULL    |                |
| t_name  | char(5)         | NO   |     | NULL    |                |
+---------+-----------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

总结：如果插入的记录错误，自增的机制也会自动的加下去。