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)
总结：如果插入的记录错误，自增的机制也会自动的加下去。
