4-表约束和表设计-Toy模板网

这篇具有很好参考价值的文章主要介绍了4-表约束和表设计。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

1.表约束

1.1.什么是表约束？

1.2.为什么需要表约束？

1.3.常见的表约束

1.3.1.非空约束 not null

--->a.查看非空约束

--->b.验证非空约束

1.3.2.唯一约束 unique

--->a.查看唯一约束

--->b.验证唯一约束

--->PS：给某个字段只创建了唯一约束，没有指定非空约束。问：①这个字段能否插入NULL值？②若能插入NULL值，能否重复插入多个NULL值？③这个字段能否插入空值/空串（''）？④若能插入空值，能否重复插入多个空值？

1.3.3.主键约束 primary key

--->语法1：单个字段-独立主键

--->语法2：单个字段/多个字段-联合主键

--->PS：（常见面试题）主键约束 VS 唯一约束

1.3.4.外键约束 foreign key

1.3.5.检查约束 check（了解）

1.3.6.自增约束 auto_increment

--->注：

--->a.查看自增值

--->b.手动设置自增值（创建表时）

--->c.修改自增值

--->d.delete 和 truncate 重置自增验证（默认引擎InnoDB下）

1.3.7.默认约束 default

--->PS：时间类型的数据插入与查询：

--->PS：一种特殊的插入方法：insert......select......

2.表设计

2.1.数据库设计三范式

①第一范式（确保每列保持原子性）

②第二范式（确保表中的每列都和主键相关）

③第三范式（确保每列都和主键列直接相关，而不是间接相关）

2.2.表关系

①⼀对⼀：⼀个学⽣对应⼀个学号。

②一对多：一个班级包含多个学生（用两张表表示）。

③多对多：一张表中的一条数据可以对应到另一张表的多条数据，反之也是这样的，这样的关系叫做多对多，⼀般⽤三个表表示。

1.表约束

1.1.什么是表约束？

表约束是在创建表的时候，设计⼀些表的约束条件，⽤来保证数据的合法性和数据的正确性。

例如：

⼀个账号只能绑定⼀个⼿机号，那么⼿机号就不能重复被多个账号绑定使⽤，所以它应该设置唯⼀约束。

主键是⽤来标识数据的，因此它不能为 NULL，也不能为空，所以它需要设置⾮空约束或主键约束，主键约束也不能为 NULL。

1.2.为什么需要表约束？

就像法律是⽤来规范⼈们的正确⾏为的⼀样，表约束也是为了规范程序员正确使⽤表的，但表约束是预先设置的，设置之后就对所有插⼊和修改⽴即⽣效，⽐如⾮空约束设置好之后，如果添加和修改为 NULL 值就会报错，这点是和法律有细微的不同。

如果没有表约束，会导致数据不正确，从⽽导致程序或现实中的业务⽆法推进和执⾏。

4-表约束和表设计

1.3.常见的表约束

4-表约束和表设计

PS：

在MySQL中通过指令查看当前时间，并把当前时间设为默认约束，这样在后面添加数据时，不指定createtime也没关系，系统会按照当前时间自动插入一个时间值。

create table 表名 (字段名 数据类型 [约束] [,字段名 数据类型 [约束]] ...);

1.3.1.非空约束 not null

字段名 数据类型 not null

创建的字段默认是可以为空的：

4-表约束和表设计

添加了⾮空约束之后，设置的字段就不能为空了，此约束可以⽤于⼀张表中的任意0到n个字段：

4-表约束和表设计

--->a.查看非空约束

desc 表名;

--->b.验证非空约束

4-表约束和表设计

1.3.2.唯一约束 unique

字段名 数据类型 unique

唯⼀约束就是对应字段的值是唯⼀的，不能重复，此约束可以⽤于⼀张表中的任意0到n个字段：

4-表约束和表设计

--->a.查看唯一约束

查看索引详情：

-- 以行的方式/表的方式展示
show keys from 表名;

4-表约束和表设计

-- 以列的方式/key: value的方式展示
show keys from 表名\G;

4-表约束和表设计

--->b.验证唯一约束

4-表约束和表设计

--->PS：给某个字段只创建了唯一约束，没有指定非空约束。问：①这个字段能否插入NULL值？②若能插入NULL值，能否重复插入多个NULL值？③这个字段能否插入空值/空串（''）？④若能插入空值，能否重复插入多个空值？

①能插入NULL值：

②能重复插入多个NULL值：

为什么NULL可以重复插⼊到唯⼀列中呢？

因为NULL在mysql中⽐较特殊，这个NULL不是一个具体的值叫做"NULL"，而是表示某个字段是没有值的。

NULL不代表任何值，它表示该值尚未插⼊。

就像孙悟空⼀样，跳出三界外，不在五⾏中。

注意和带单引号的NULL（表示具体的一个值，值的内容为NULL，此时就不能重复插入多个了）区别：

③能插入空值（''）：

④不能重复插入多个空值：

小结：

在MySQL中：NULL、''、'NULL'这三个是完全不同的！

NULL：不存在。使用is null或is not null来判断。

''：存在的。使用=''或!=''来判断。

'NULL'：存在的。使用='NULL'或!='NULL'来判断。

1.3.3.主键约束 primary key

主键是可以⽤来表示⼀张表中某条数据的代表凭证，例如对于“⼈”这张表来说，唯⼀的身份证就可以作为主键来代表这个⼈，“姓名”不⾏，因为姓名有可能会重复。

主键的特征：

主键可以由单个字段或多个字段（联合主键）组成。
主键不能插入NULL，可以插入空串，必须唯⼀。（primary key = unique + not null）
⼀个表中只能有⼀个主键。

--->语法1：单个字段-独立主键

字段名 数据类型 primary key

4-表约束和表设计

--->语法2：单个字段/多个字段-联合主键

primary key(字段名 [,字段名]...)

4-表约束和表设计

联合主键只有当插入的多个字段都相同时，才会报错。

--->PS：（常见面试题）主键约束 VS 唯一约束

一个表中：主键约束只能有⼀个，⽽唯⼀约束可以有多个。

主键约束不能有 null 值，⽽唯⼀约束可以有 null，并且可以有多个 null。

1.3.4.外键约束 foreign key

外键⽤于关联其他表的主键或唯⼀键。

主表：主键在主表里。
次表：外键在次表里，外键关联到主表的主键上。

-- 在次表中写：
foreign key (外键字段名) references 主表(主键列名)

创建班级表class，id为主键：

4-表约束和表设计

创建学⽣表student，⼀个学⽣对应⼀个班级，⼀个班级对应多个学⽣。使⽤id为主键，classid 为外键，关联班级表id：

4-表约束和表设计

测试外键约束：

4-表约束和表设计

外键生效了！在次表进行数据插入时，会先去主表查询当前外键的有效性，若有效，才进行插入。

4-表约束和表设计

1.3.5.检查约束 check（了解）

检查约束是保证列中的值符合指定的条件，⽐如性别只能插⼊“男”或者“⼥”。

特殊说明：检查约束在 MySQL 8.0.15 以前约束不起作⽤，MySQL 8.0.16 才可以使⽤。

check(检查约束)

4-表约束和表设计

1.3.6.自增约束 auto_increment

字段名 数据类型 auto_increment

4-表约束和表设计

⾃增从 1 开始，每次递增 1。

auto_increment 列也可以显式指定值。

4-表约束和表设计

--->注：

①如果把一个NULL插入到一个auto_increment数据列里，MySQL将自动生成下一个序列编号。其执行逻辑和不设置自增列是一样的，会给自增列插入InnoDB里保存的自增最大值，而不会是NULL。

4-表约束和表设计

②当插入记录时，没有为auto_increment明确指定值，则等同插入NULL。

③一个表中只能有一个字段使用auto_increment约束，且该字段必须有唯一索引，以避免序号重复（即为主键或主键的一部分）。

4-表约束和表设计

④auto_increment列必须为一个key（primary key、foreign key、unique），但不是一定要为主键，如果没有key就会报错。

唯⼀列可以为⾃增列：

4-表约束和表设计

外键也可以为⾃增列：

4-表约束和表设计

auto_increment不能只和not null一起使用：

4-表约束和表设计

⑤auto_increment约束的字段类型只能为整数类型（TINYINT、SMALLINT、INT、BIGINT 等）。

4-表约束和表设计

--->a.查看自增值

4-表约束和表设计

--->b.手动设置自增值（创建表时）

4-表约束和表设计

--->c.修改自增值

alter table 表名 auto_increment = 值;

4-表约束和表设计

注：auto_increment 的值只能设置⽐⽬前存储的最⼤值⼤，否则设置不会⽣效：

4-表约束和表设计

--->d.delete 和 truncate 重置自增验证（默认引擎InnoDB下）

delete 不重置⾃增：

4-表约束和表设计

truncate 重置⾃增：

4-表约束和表设计

1.3.7.默认约束 default

默认约束是给没有给列赋值时的默认值。

字段名 数据类型 default '默认值'

--->PS：时间类型的数据插入与查询：

MySQL中任何数据类型都可以使用英文单引号括起来，so在不确定是什么数据类型时，可以加上单引号。

插入：

查询：

查询book图书表中，作者author列不为空，或者满足条件：价格price在50元以上且出版日期public_date在2019年之后的图书信息：

select * from book where author is not null or(price>50 and public_date>'2019-01-01 00:00:00');

--->PS：一种特殊的插入方法：insert......select......

insert into插入时，是根据列的顺序匹配查询结果的，不关注列名。

2.表设计

2.1.数据库设计三范式

①第一范式（确保每列保持原子性）

第⼀范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满⾜了第⼀范式。第⼀范式的合理遵循需要根据系统的实际需求来定。

优点：

减少了数据冗余。
更利于数据的维护和更新。

⽐如某些数据库系统中需要⽤到“地址”这个属性，本来直接将“地址”属性设计成⼀个数据库表的字段就⾏。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就⾮要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进⾏存储，这样在对地址中某⼀部分操作的时候将⾮常⽅便。这样设计才算满⾜了数据库的第⼀范式：

上表所示的⽤户信息遵循了第⼀范式的要求，这样在对⽤户使⽤城市进⾏分类的时候就⾮常⽅便，也提⾼了数据库的性能。

②第二范式（确保表中的每列都和主键相关）

第⼆范式在第⼀范式的基础之上更进⼀层。第⼆范式需要确保数据库表中的每⼀列都和主键相关，⽽不能只与主键的某⼀部分相关（主要针对联合主键⽽⾔）。也就是说在⼀个数据库表中，⼀个表中只能保存⼀种数据，不可以把多种数据保存在同⼀张数据库表中。

优点：

更利于数据的维护和修改。
更利于数据的完整展示。

⽐如要设计⼀个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键：

这样就产⽣⼀个问题：这个表中是以订单编号和商品编号作为联合主键。这样在该表中商品名称、单位、商品价格等信息不与该表的主键相关，⽽仅仅是与商品编号相关。所以在这⾥违反了第⼆范式的设计原则。⽽如果把这个订单信息表进⾏拆分，把商品信息分离到另⼀个表中，把订单数量表也分离到另⼀个表中，就⾮常完美了：

这样设计，在很⼤程度上减⼩了数据库的冗余。如果要获取订单的商品信息，使⽤商品编号到商品信息表中查询即可。

③第三范式（确保每列都和主键列直接相关，而不是间接相关）

第三范式需要在满足第一范式和第二范式的基础上，所有字段不能出现对非主键的依赖。确保数据表中的每⼀列数据都和主键直接相关，而不能间接相关。

优点：

实现列和非主键列的解耦（一个优秀的设计理念：修改了一个字段，不会影响其他字段）。

⽐如在设计⼀个订单数据表的时候，可以将客户编号作为⼀个外键和订单表建⽴相应的关系，⽽不可以在订单表中添加关于客户其它信息（⽐如姓名、所属公司等）的字段。下表不符合第三范式：

正确的表结构：

这样在查询订单信息的时候，就可以使⽤客户编号来引⽤客户信息表中的记录，也不必在订单信息表中多次输⼊客户信息的内容，减⼩了数据冗余。