表的束缚（MySQL）

1. 表的束缚

真正束缚字段的是数据类型，但是数据类型束缚很单一，须要有一些额外的束缚，更好的保证数据的正当性，从业务逻辑角度保证数据的正确性。比如有一个字段是email，要求是唯一的。
表的束缚许多，这里主要先容如下几个： null/not null,default, comment, zerofill，primary key，auto_increment，unique key 。

null/not null	NULL是NULL，不是空串的意思
default	默认值
comment	分析：注释
zerofill	如果宽度小于设定的宽度，主动添补0
primary key	主键
auto_increment	自增长
unique key	唯一键

1.1 空属性

• 两个值：null（默认的）和not null(不为空)
  
• 数据库默认字段基本都是字段为空，但是现实开发时，尽大概保证字段不为空，因为数据为空没办法参与运算。
  

1. mysql> select null;
2. +------+
3. | NULL |
4. +------+
5. | NULL |
6. +------+
7. 1 row in set (0.00 sec)
9. mysql> select 1+null;
10. +--------+
11. | 1+null |
12. +--------+
13. |   NULL |
14. +--------+
15. 1 row in set (0.00 sec)

复制代码

案例：
创建一个班级表，包含班级名和班级所在的教室。
站在正常的业务逻辑中：

• 如果班级没有名字，你不知道你在哪个班级
  
• 如果教室名字可以为空，就不知道在哪上课
  

所以我们在设计数据库表的时间，一定要在表中进行限制，满足上面条件的数据就不能插入到表中。这就是“束缚”。

1. mysql> create table myclass(
2.   -> class_name varchar(20) not null,
3.   -> class_room varchar(10) not null);
4. Query OK, 0 rows affected (0.02 sec)
6. mysql> desc myclass;
7. +------------+-------------+------+-----+---------+-------+
8. | Field     | Type       | Null | Key | Default | Extra |
9. +------------+-------------+------+-----+---------+-------+
10. | class_name | varchar(20) | NO   |     | NULL   |       |
11. | class_room | varchar(10) | NO   |     | NULL   |       |
12. +------------+-------------+------+-----+---------+-------+
14. //插入数据时,没有给教室数据插入失败：
15. mysql> insert into myclass(class_name) values('class1');
16. ERROR 1364 (HY000): Field 'class_room' doesn't have a default value

复制代码

1.2 默认值

默认值：某一种数据会经常性的出现某个详细的值，可以在一开始就指定好，在须要真实数据的时间， 用户可以选择性的利用默认值。

1. mysql> create table tt10 (
2.   -> name varchar(20) not null,
3.   -> age tinyint unsigned default 0,
4.   -> sex char(2) default '男'
5.   -> );
6. Query OK, 0 rows affected (0.00 sec)
8. mysql> desc tt10;
9. +-------+---------------------+------+-----+---------+-------+
10. | Field | Type               | Null | Key | Default | Extra |
11. +-------+---------------------+------+-----+---------+-------+
12. | name | varchar(20)         | NO   |     | NULL   |       |
13. | age   | tinyint(3) unsigned | YES |     | 0       |       |
14. | sex   | char(2)             | YES |     | 男     |       |
15. +-------+---------------------+------+-----+---------+-------+

复制代码

默认值的生效：数据在插入的时间不给该字段赋值，就利用默认值

1. mysql> insert into tt10(name) values('zhangsan');
2. Query OK, 1 row affected (0.00 sec)
4. mysql> select * from tt10;
5. +----------+------+------+
6. | name     | age | sex |
7. +----------+------+------+
8. | zhangsan |    0 | 男   |
9. +----------+------+------+
10. --注意：只有设置了default的列，才可以在插入值的时候，对列进行省略

复制代码

1.3 列形貌

列形貌：comment，没有现实含义，专门用来形貌字段，会根据表创建语句生存，用来给步伐员或DBA 来进行相识。

1. mysql> create table tt12 (
2.   -> name varchar(20) not null comment '姓名',
3.   -> age tinyint unsigned default 0 comment '年龄',
4.   -> sex char(2) default '男' comment '性别'
5.   -> );
6.    
7. --注意：not null和defalut一般不需要同时出现，因为default本身有默认值，不会为空

复制代码

通过desc查看不到注释信息：

1. mysql> desc tt12;
2. +-------+---------------------+------+-----+---------+-------+
3. | Field | Type               | Null | Key | Default | Extra |
4. +-------+---------------------+------+-----+---------+-------+
5. | name | varchar(20)         | NO   |     | NULL   |       |
6. | age   | tinyint(3) unsigned | YES |     | 0       |       |
7. | sex   | char(2)             | YES |     | 男     |       |
8. +-------+---------------------+------+-----+---------+-------+

复制代码

通过show可以看到：

1. mysql> show create table tt12\G
2. *************************** 1. row ***************************
3.       Table: tt12
4. Create Table: CREATE TABLE `tt12` (
5. `name` varchar(20) NOT NULL COMMENT '姓名',
6. `age` tinyint(3) unsigned DEFAULT '0' COMMENT '年龄',
7. `sex` char(2) DEFAULT '男' COMMENT '性别'
8. ) ENGINE=MyISAM DEFAULT CHARSET=gbk
9. 1 row in set (0.00 sec)

复制代码

1.4 zerofill

刚开始学习数据库时，许多人对数字类型后面的长度很迷茫。通过show看看tt3表的建表语句：

1. mysql> show create table tt3\G
2. ***************** 1. row *****************
3.       Table: tt3
4. Create Table: CREATE TABLE `tt3` (
5. `a` int(10) unsigned DEFAULT NULL,
6. `b` int(10) unsigned DEFAULT NULL
7. ) ENGINE=MyISAM DEFAULT CHARSET=gbk
8. 1 row in set (0.00 sec)

复制代码

可以看到int(10),这个代表什么意思呢？整型不是4字节码？这个10又代表什么呢？着实没有zerofill这个 属性，括号内的数字是毫无意义的。a和b列就是前面插入的数据，如下：

1. mysql> insert into tt3 values(1,2);
2. Query OK, 1 row affected (0.00 sec)
3. mysql> select * from tt3;
4. +------+------+
5. | a   | b   |
6. +------+------+
7. |    1 |    2 |
8. +------+------+

复制代码

但是对列添加了zerofill属性后，显示的结果就有所不同了。修改tt3表的属性：

1. mysql> alter table tt3 change a a int(5) unsigned zerofill;
3. mysql> show create table tt3\G
4. *************************** 1. row ***************************
5.       Table: tt3
6. Create Table: CREATE TABLE `tt3` (
7. `a` int(5) unsigned zerofill DEFAULT NULL,  --具有了zerofill
8. `b` int(10) unsigned DEFAULT NULL
9. ) ENGINE=MyISAM DEFAULT CHARSET=gbk
10. 1 row in set (0.00 sec)

复制代码

对a列添加了zerofill属性，再进行查找，返回如下结果：

1. mysql> select * from tt3;
2. +-------+------+
3. | a     | b   |
4. +-------+------+
5. | 00001 |    2 |
6. +-------+------+

复制代码

这次可以看到a的值由原来的1变成00001，这就是zerofill属性的作用，如果宽度小于设定的宽度（这里 设置的是5），主动添补0。要注意的是，这只是最后显示的结果，在MySQL中现实存储的还是1。为什 么是如许呢？我们可以用hex函数来证明。

1. mysql> select a, hex(a) from tt3;
2. +-------+--------+
3. | a     | hex(a) |
4. +-------+--------+
5. | 00001 | 1     |
6. +-------+--------+

复制代码

可以看出数据库内部存储的还是1,00001只是设置了zerofill属性后的一种格式化输出而已。
1.5 主键

主键：primary key用来唯一的束缚该字段内里的数据，不能重复，不能为空，一张表中最多只能有一个 主键；主键所在的列通常是整数类型。
案例：

• 创建表的时间直接在字段上指定主键
  

1. mysql> create table tt13 (
2. -> id int unsigned primary key comment '学号不能为空',
3. -> name varchar(20) not null);
4. Query OK, 0 rows affected (0.00 sec)
6. mysql> desc tt13;
7. +-------+------------------+------+-----+---------+-------+
8. | Field | Type             | Null | Key | Default | Extra |
9. +-------+------------------+------+-----+---------+-------+
10. | id   | int(10) unsigned | NO   | PRI | NULL   |       | <= key 中 pri表示
11. 该字段是主键
12. | name | varchar(20)     | NO   |     | NULL   |       |
13. +-------+------------------+------+-----+---------+-------+

复制代码

• 主键束缚：主键对应的字段中不能重复，一旦重复，操作失败。
  

1. mysql> insert into tt13 values(1, 'aaa');
2. Query OK, 1 row affected (0.00 sec)

复制代码

1. mysql> insert into tt13 values(1, 'aaa');
2. ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

复制代码

• 当表创建好以后但是没有主键的时间，可以再次追加主键
  

1. alter table 表名 add primary key(字段列表)

复制代码

• 删除主键
  

1. alter table 表名 drop primary key;

复制代码

1. mysql> alter table tt13 drop primary key;
2. mysql> desc tt13;
3. +-------+------------------+------+-----+---------+-------+
4. | Field | Type             | Null | Key | Default | Extra |
5. +-------+------------------+------+-----+---------+-------+
6. | id   | int(10) unsigned | NO   |     | NULL   |       |
7. | name | varchar(20)     | NO   |     | NULL   |       |
8. +-------+------------------+------+-----+---------+-------+

复制代码

• 复合主键
  

在创建表的时间，在所有字段之后，利用primary key(主键字段列表)来创建主键，如果有多个字段 作为主键，可以利用复合主键。

1. mysql> create table tt14(
2. -> id int unsigned,
3. -> course char(10) comment '课程代码',
4. -> score tinyint unsigned default 60 comment '成绩',
5. -> primary key(id, course) -- id和course为复合主键
6. -> );
7. Query OK, 0 rows affected (0.01 sec)
9. mysql> desc tt14;
10. +--------+---------------------+------+-----+---------+-------+
11. | Field | Type               | Null | Key | Default | Extra |
12. +--------+---------------------+------+-----+---------+-------+
13. | id     | int(10) unsigned   | NO   | PRI | 0       |       | <= 这两列合成
14. 主键
15. | course | char(10)           | NO   | PRI |         |       |
16. | score | tinyint(3) unsigned | YES |     | 60     |       |
17. +--------+---------------------+------+-----+---------+-------+
19. mysql> insert into tt14 (id,course)values(1, '123');
20. Query OK, 1 row affected (0.02 sec)
22. mysql> insert into tt14 (id,course)values(1, '123');
23. ERROR 1062 (23000): Duplicate entry '1-123' for key 'PRIMARY' -- 主键冲突

复制代码

1.6 自增长

auto_increment：当对应的字段，不给值，会主动的被系统触发，系统会从当前字段中已经有的最大值 +1操作，得到一个新的不同的值。通常和主键搭配利用，作为逻辑主键。
自增长的特点:

• 任何一个字段要做自增长，前提是本身是一个索引（key一栏有值）
  
• 自增长字段必须是整数
  
• 一张表最多只能有一个自增长
  

案例：

1. mysql> create table tt21(
2.   -> id int unsigned primary key auto_increment,
3.   -> name varchar(10) not null default ''
4.   -> );
6. mysql> insert into tt21(name) values('a');
8. mysql> insert into tt21(name) values('b');
10. mysql> select * from tt21;
11. +----+------+
12. | id | name |
13. +----+------+
14. |  1 | a   |
15. |  2 | b   |
16. +----+------+

复制代码

在插入后获取前次插入的 AUTO_INCREMENT 的值（批量插入获取的是第一个值）

1. mysql > select last_insert_id();
2. +------------------+
3. | last_insert_id() |
4. +------------------+
5. |                1 |
6. +------------------+

复制代码

索引：
        在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结 构，它是某个表中一列或若干列值的聚集和相应的指向表中物理标识这些值的数据页的逻辑指针清单。 索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。
        索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序次序对这些指针排序。 数据库利用索引以找到特定值，然后顺指针找到包含该值的行。如许可以使对应于表的SQL语句执行得更快，可快速访问数据库表中的特定信息。
1.7 唯一键

一张表中有往往有许多字段须要唯一性，数据不能重复，但是一张表中只能有一个主键：唯一键就可以 办理表中有多个字段须要唯一性束缚的标题。
唯一键的本质和主键差不多，唯一键答应为空，而且可以多个为空，空字段不做唯一性比力。
关于唯一键和主键的区别：
我们可以简朴理解成，主键更多的是标识唯一性的。而唯一键更多的是保证在业务上，不要和别的信息 出现重复。乍一听似乎没啥区别，我们举一个例子

1. 假设一个场景(当然，具体可能并不是这样，仅仅为了帮助大家理解)
2. 比如在公司，我们需要一个员工管理系统，系统中有一个员工表，员工表中有两列信息，一个身份证号码，一
3. 个是员工工号，我们可以选择身份号码作为主键。
5. 而我们设计员工工号的时候，需要一种约束：而所有的员工工号都不能重复。
7. 具体指的是在公司的业务上不能重复，我们设计表的时候，需要这个约束，那么就可以将员工工号设计成为唯
8. 一键。
10. 一般而言，我们建议将主键设计成为和当前业务无关的字段，这样，当业务调整的时候，我们可以尽量不会对
11. 主键做过大的调整。

复制代码

案例：

1. mysql> create table student (
2.   -> id char(10) unique comment '学号，不能重复，但可以为空',
3.   -> name varchar(10)
4.   -> );
5. Query OK, 0 rows affected (0.01 sec)
7. mysql> insert into student(id, name) values('01', 'aaa');
8. Query OK, 1 row affected (0.00 sec)
10. mysql> insert into student(id, name) values('01', 'bbb'); --唯一约束不能重复
11. ERROR 1062 (23000): Duplicate entry '01' for key 'id'
13. mysql> insert into student(id, name) values(null, 'bbb'); -- 但可以为空
14. Query OK, 1 row affected (0.00 sec)
16. mysql> select * from student;
17. +------+------+
18. | id   | name |
19. +------+------+
20. | 01   | aaa |
21. | NULL | bbb |
22. +------+------+

复制代码

1.8 外键

外键用于定义主表和从表之间的关系：外键束缚主要定义在从表上，主表则必须是有主键束缚或unique 束缚。当定义外键后，要求外键列数据必须在主表的主键列存在或为null。
语法：

1. foreign key (字段名) references 主表(列)

复制代码

案例：

对上面的示意图进行设计：

• 先创建主键表
  

1. create table myclass (
2. id int primary key,
3. name varchar(30) not null comment'班级名'
4. );

复制代码

• 再创建从表
  

1. create table stu (
2. id int primary key,
3. name varchar(30) not null comment '学生名',
4. class_id int,
5. foreign key (class_id) references myclass(id)
6. );

复制代码

• 正常插入数据
  

1. mysql> insert into myclass values(10, 'C++大牛班'),(20, 'java大神班');
2. Query OK, 2 rows affected (0.03 sec)
3. Records: 2 Duplicates: 0 Warnings: 0

复制代码

1. mysql> insert into stu values(100, '张三', 10),(101, '李四',20);
2. Query OK, 2 rows affected (0.01 sec)
3. Records: 2 Duplicates: 0 Warnings: 0

复制代码

• 插入一个班级号为30的门生，因为没有这个班级，所以插入不乐成
  

1. mysql> insert into stu values(102, 'wangwu',30);
2. ERROR 1452 (23000): Cannot add or update a child row:
3. a foreign key constraint fails (mytest.stu, CONSTRAINT stu_ibfk_1
4. FOREIGN KEY (class_id) REFERENCES myclass (id))

复制代码

• 插入班级id为null，比如来了一个门生，目前还没有分配班级
  

1. mysql> insert into stu values(102, 'wangwu', null);

复制代码

• 如何理解外键束缚
  

1. 首先我们承认，这个世界是数据很多都是相关性的。
3. 理论上，上面的例子，我们不创建外键约束，就正常建立学生表，以及班级表，该有的字段我们都有。
5. 此时，在实际使用的时候，可能会出现什么问题？
6. 有没有可能插入的学生信息中有具体的班级，但是该班级却没有在班级表中？
7. 比如比特只开了比特100班，比特101班，但是在上课的学生里面竟然有比特102班的学生(这个班目前并
8. 不存在)，这很明显是有问题的。
10. 因为此时两张表在业务上是有相关性的，但是在业务上没有建立约束关系，那么就可能出现问题。
12. 解决方案就是通过外键完成的。建立外键的本质其实就是把相关性交给mysql去审核了，提前告诉mysql
13. 表之间的约束关系，那么当用户插入不符合业务逻辑的数据的时候，mysql不允许你插入。

复制代码

1.9 综合案例 - 阅读

有一个商店的数据，记载客户及购物情况，有以下三个表构成：

• 商品goods(商品编号goods_id，商品名goods_name, 单价unitprice, 商品种别category, 供应商 provider)
  
• 客户customer(客户号customer_id,姓名name,住址address,邮箱email,性别sex，身份证card_id)
  
• 购买purchase(购买订单号order_id,客户号customer_id,商品号goods_id,购买数量nums)
  

要求：

• 每个表的主外键
  
• 客户的姓名不能为空值
  
• 邮箱不能重复
  
• 客户的性别(男，女)
  

SQL:

1. -- 创建数据库
2. create database if not exists bit32mall
3. default character set utf8 ;
5. -- 选择数据库
6. use bit32mall;
8. -- 创建数据库表
9. -- 商品
10. create table if not exists goods
11. (
12.   goods_id  int primary key auto_increment comment '商品编号',
13.   goods_name varchar(32) not null comment '商品名称',
14.   unitprice  int  not null default 0 comment '单价，单位分',
15.   category  varchar(12) comment '商品分类',
16.   provider  varchar(64) not null comment '供应商名称'
17. );
19. -- 客户
20. create table if not exists customer
21. (
22.   customer_id  int primary key auto_increment comment '客户编号',
23.   name varchar(32) not null comment '客户姓名',
24.   address  varchar(256) comment '客户地址',
25.   email  varchar(64) unique key comment '电子邮箱',
26.    sex  enum('男','女') not null comment '性别',
27.   card_id char(18) unique key comment '身份证'
28. );
30. -- 购买
31. create table if not exists purchase
32. (
33.   order_id  int primary key auto_increment comment '订单号',
34.   customer_id int comment '客户编号',
35.   goods_id  int comment '商品编号',
36.   nums  int default 0 comment '购买数量',
37.   foreign key (customer_id) references customer(customer_id),
38.   foreign key (goods_id) references goods(goods_id)
39. );

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。