IE盒子

搜索
查看: 119|回复: 1

MySQL数据库从入门到精通—数据的完整性

[复制链接]

1

主题

5

帖子

4

积分

新手上路

Rank: 1

积分
4
发表于 2022-12-2 12:13:26 | 显示全部楼层 |阅读模式
实体完整性
实体完整性是对关系中的记录进行约束,即对行的约束。此处主要讲解主键约束、唯一约束和自动增长列。


https://www.zhihu.com/video/1565269051558809600

主键约束
主键(PRIMARY KEY)用于唯一地标识表中的某一条记录。在两个表的关系中,主键用来在一个表中引用来自于另一个表中的特定记录。一个表的主键可以由多个关键字共同组成,并且主键的列不能包含空值。主键的值能唯一标识表中的每一行,这就好比所有人都有身份证,每个人的身份证号是不同的,能唯一标识每一个人。
接下来通过一个案例演示未设置主键会出现的问题。首先,创建一张订单表orders。


表中列出了订单表的字段、字段类型和说明。创建订单表的SQL语句及执行结果如下。


然后向订单表中插入一条数据。


从以上执行结果证明插入数据完成。为了进一步验证,使用SELECT语句查看orders表中的数据。


从以上执行结果可以看出,orders表中的数据成功插入。此时再次向表中插入数据,新插入数据的oid仍然为1。


从以上执行结果证明插入数据完成。为了进一步验证,使用SELECT语句查看orders表中的数据。


从以上执行结果可看出,orders表中的数据成功插入,该表中此时有两条订单数据,且两个订单的oid都为1。但订单号相同,商品的付款、送货等流程都可能出现问题。为了避免这种问题,在此处可以为表添加主键约束,为已经存在的表设置主键的语法格式如下。


以上执行结果证明删除数据完成。为了进一步验证,使用SELECT语句查看orders表中的数据。


从以上执行结果可看出,orders表中只有一条数据,不存在oid重复的数据。然后继续设置主键。


以上执行结果证明主键添加完成。为了进一步验证,使用DESC查看表结构。


从以上执行结果可以看出,oid字段的Key值为PRI,代表主键。然后将前面删除的第二条数据再次添加进去。


从以上执行结果可以看出,插入数据失败。这是因为主键对其进行了约束,新插入的数据主键不能重复。此处将oid的值改为2,再次插入。


以上执行结果证明插入数据完成。为了进一步验证,使用SELECT语句查看orders表中的数据。


从以上执行结果可以看出,当oid的值不重复时插入成功。另外,主键的值不能为NULL,此处继续验证,向表中插入一条数据,将oid设置为NULL。


以上执行结果证明定义为主键的字段值不能为NULL,否则会报错。以上是为已经存在的表添加主键约束,实际上,在创建表时同样可以添加主键约束。


在以上语法格式中,字段名表示需要设置为主键的列名,数据类型为该列的数据类型,PRIMARY KEY代表主键。
在前面的案例中讲解了添加单字段的主键约束,但随着业务的复杂,可能会需要多字段的主键约束,例如手机接收信息,这时通过手机号就不能够唯一确定一条记录,可能一个手机号在一天中接收了很多的信息。解决这个问题可以添加主键约束为手机号和时间戳两个列,根据两个列的数据,能够唯一确定一条记录。添加多字段的主键约束语法格式如下。


以上语法格式中,PRIMARY KEY中的参数表示构成主键的多个字段的名称。
唯一约束
唯一约束用于保证数据表中字段值的唯一性,在MySQL中使用UNIQUE关键字添加唯一约束。在创建表时为某个字段添加唯一约束的具体语法格式如下。


在以上语法格式中,字段名表示需要添加唯一约束的列名,列名后跟着数据类型和UNIQUE关键字,两者之间用空格隔开。
同样,唯一约束也可以添加到已经创建完成的表中,语法格式如下。


自动增长列
在前面的学习中,数据表中的id字段一般从1开始插入,不断增加,这种做法存在一些问题,即在添加数据时比较烦琐,每次都要添加一个id字段的值,而且容易出错。为了解决这个问题,可以将id字段的值设置为自动增加。在MySQL中使用AUTO_INCREMENT关键字设置表字段值自动增加。在创建表时将某个字段的值设置为自动增长,语法格式如下。


在以上语法格式中,字段名表示需要设置字段值自动增加的列名,列名后跟着数据类型和AUTO_INCREMENT关键字,两者之间用空格隔开。


索引
在现实生活中,人们去图书馆查找一本感兴趣的书时,如果从第一本开始依次查找,则效率明显是低下的,如果按照图书的分类进行查找,则效率会有所提升。在MySQL中查找数据也有类似的问题,如果使用“SELECT * FROM表名 WHERE id=1000”,则数据库从第一条记录开始遍历,直到找到id等于1000的数据,这种做法的效率明显非常低,MySQL提供了索引来解决这个问题。
在关系数据库中,索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构,它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录,用户可以根据目录中的页码快速找到所需的内容。MySQL中的索引分为很多种,包括普通索引、唯一索引、全文索引、单列索引、多列索引、空间索引、组合索引等,接下来详细讲解其中常用的普通索引和唯一索引。
普通索引
普通索引是最基本的索引类型,它的唯一任务是加快对数据的访问速度,因此应该只为那些最经常出现在查询条件或排序条件中的数据列创建索引。尽可能选择一个数据最整齐、最紧凑的数据列来创建索引,例如一个整数数据类型的列。
在创建表时可以创建普通索引,语法格式如下所示。


在以上语法格式中,INDEX表示字段的索引,索引名是可选值,括号中的字段名是创建索引的字段,参数长度是可选的,用于表示索引的长度。
另外,对于已经创建完成的表,也可以为其某个字段创建普通索引,语法格式如下。


唯一索引
前面讲解了普通索引,它允许被索引的数据列包含重复的值,例如姓名可能出现重复的情况,但有些值是不能重复的,在为这个数据列创建索引的时候就应该用UNIQUE关键字把它定义为一个唯一索引。唯一索引可以保证数据记录的唯一性,这种做法的好处:一是简化了MySQL对这个索引的管理工作,这个索引也因此而变得更有效率,二是MySQL会在有新记录插入数据表时自动检查新记录的这个字段的值是否已经在某个记录的这个字段中出现,如果已经出现,MySQL将拒绝插入那条新记录。
在创建表时可以创建唯一索引,语法格式如下。


在以上语法格式中,UNIQUE INDEX关键字表示唯一索引,索引名是可选值,括号中的字段名是创建索引的字段,参数长度是可选的,用于表示索引的长度。
另外,对于已经创建完成的表,也可以为其某个字段创建唯一索引,语法格式如下。
在创建测试表test4后,为id字段创建唯一索引。
首先创建test4表。


接着为表test4的id字段创建唯一索引。


以上执行结果证明test4表的id字段的唯一索引创建完成。为了进一步验证,使用SHOW CREATE TABLE查看表的具体信息。


从以上执行结果可以看出,id字段成功创建了唯一索引,索引名称为test4_id。

域完整性
域完整性是对关系中的单元格进行约束,域代表单元格,也就是对列的约束。域完整性约束包括数据类型、非空约束、默认值约束和CHECK约束,数据类型在前面章节中已经讲解过,MySQL会忽略CHECK约束,因此本节只讲解非空约束和默认值约束。
非空约束
非空约束用于保证数据表中某个字段的值不为NULL,在MySQL中使用NOT NULL关键字添加非空约束。在创建表时,为某个字段添加非空约束的具体语法格式如下。


在以上语法格式中,字段名是需要添加非空约束的列名,列名后跟着数据类型和NOT NULL关键字,两者之间用空格隔开。
此外,非空约束也可以添加到已经创建完成的表中,语法格式如下。


创建测试表test6,创建完成后,为id字段添加非空约束。
首先创建test6表。


以上执行结果证明test6表创建完成。接着为表中的id列添加非空约束。


以上执行结果证明test6表中的id字段添加非空约束成功。为了进一步验证,使用DESC语句查看表结构。


从以上执行结果可以看出,test6表中id字段的NULL列的值为NO,证明成功添加了非空约束。

默认值约束
默认值约束用于为数据表中某个字段的值添加默认值,例如订单的创建时间,如果不进行手动填写,可以设置创建时间字段的默认值为当前时间。在MySQL中使用DEFAULT关键字添加默认值约束,为某个字段添加默认值约束的具体语法格式如下。



在以上语法格式中,字段名表示需要添加默认值约束的列名,列名后跟着数据类型和DEFAULT关键字,DEFAULT是添加的默认值。
此外,默认值约束也可以添加到已经创建完成的表中,语法格式如下。


创建测试test8表,在创建完成后为name字段添加默认值约束,默认值为lilei。
首先创建test8表。


以上执行结果证明test8表创建完成。然后为test8表中的name字段添加默认值约束。


以上执行结果证明test8表中的name字段添加默认值约束成功。为了进一步验证,使用DESC语句查看表结构。


从以上执行结果可以看出,test8表中name字段的Default列的值为lilei,证明成功添加了默认值约束。

引用完整性

域完整性
引用完整性是对实体之间关系的描述,是定义外关键字与主关键字之间的引用规则,也就是外键约束。如果要删除被引用的对象,那么也要删除引用它的所有对象,或者把引用值设置为空。接下来将详细讲解外键约束的有关的内容。
外键的概念

外键是指引用另一个表中的一列或多列,被引用的列应该具有主键约束或唯一约束。外键用于建立和加强两个表数据之间的连接,接下来通过两张表讲解什么是外键约束。
首先创建学科表subject,包含两个字段(专业编号sub_id和专业名称sub_name)。


然后创建学生表student,包含3个字段(学生编号stu_id、学生姓名stu_name和专业编号sub_id)。


在创建的subject表中sub_id为主键,在student表中也有sub_id字段,此处是引入了subject表的主键,那么student表中的sub_id字段就是外键。被引用的subject表是主表,引用外键的student表是从表,两个表是主从关系,student表可以通过sub_id连接subject表中的信息,从而建立两个表的数据之间的连接。
因为student表中的sub_id字段是外键,所以当外键字段引用了主表subject的数据时,subject表不允许单方面删除表或表中数据,需要先删除引用它的所有对象,或者把引用值设置为空。接下来验证这种情况,首先为主表subject添加数据。


以上执行结果证明了插入数据完成。为了进一步验证,使用SELECT语句查看subject表中的数据。


从以上执行结果可以看出,subject表中的数据成功插入。然后向student表中插入数据。


以上执行结果证明插入数据完成。为了进一步验证,使用SELECT语句查看student表中的数据。


从以上执行结果可以看出,student表中的数据成功插入,表中的sub_id为1,是引用了subject表中sub_id字段的值,然后删除主表subject中的数据。


从以上执行结果可以看出,subject表中的数据删除成功,这明显不符合外键约束的作用,数据被从表引用时,主表中的数据不应该被删除,这是因为此时还没有为sub_id字段添加外键约束,接下来将详细讲解如何添加外键约束。
添加外键约束
前面讲解了外键约束是什么以及为什么需要外键约束,若想真正连接两个表的数据,就需要为表添加外键约束,语法格式如下。


接下来仍然使用前面创建的student表和subject表,并清空两张表的数据,此处就不再演示。为student表中的sub_id字段添加外键约束。


以上执行结果证明了添加外键约束成功。此时,如果先为student表添加数据,则是无法添加的,因为subject表中还没有可以引用的数据。


从以上执行结果可看出,主表中没有数据,从表中无法插入数据。此时先为subject表插入数据。


以上执行结果证明插入数据完成。然后为student表插入数据。


以上执行结果证明插入数据完成。接下来仍然进行前面的实验,直接删除subject表中的数据。


从以上执行结果可以看出,因为subject表中的数据被student表引用,所以无法删除subject表中的数据。此时可以先删除从表中的数据,再删除主表中的数据。首先删除student表中的数据。


以上执行结果证明student表中的数据删除成功。然后删除主表subject中的数据。


以上执行结果证明subject表中的数据删除成功,这就是外键约束的基本使用。用户除了创建表后添加外键约束,还可以在创建表的同时添加外键约束。


接下来通过一个案例演示在创建表的同时添加外键约束,创建学生表student2和分数表score,其中学生表包含两个字段(学生编号stu_id和学生姓名stu_name),分数表包括3个字段(分数编号sco_id、分数score和学生编号stu_id)。
首先创建学生表。


以上执行结果证明student2表创建成功。然后创建分数表,在创建表的同时添加外键约束。


以上执行结果证明score表创建成功,在创建的同时添加了外键约束。为了进一步验证,使用SHOW CREATE TABLE语句查看score表。


从以上执行结果可看出,score表中的stu_id字段有外键约束,关联的主表为student2。

删除外键约束
前面讲解了添加外键约束的两种方式,在实际开发中可能会出现需要解除两个表之间的关联关系的情况,这就需要删除外键约束,语法格式如下。


接下来演示将student表中的外键约束删除,首先查看表中的外键名。


从以上执行结果可以看出,sub_id字段为外键,关联的主表是subject,外键名为student_ibfk_1。接着下来删除这个外键约束。


以上执行结果证明student表的外键约束删除成功。为了进一步验证,使用SHOW CREATE TABLE语句查看student表。


从以上执行结果可以看出,student表的外键约束删除成功。
小结:MySQL数据库从入门到精通—数据的完整性


https://www.zhihu.com/video/1565273480177881088
本文首先介绍了实体完整性,其中重点讲解了主键约束、唯一约束和自动增长列;其次介绍了索引,其中举例讲解了普通索引和唯一索引;再次讲解了域完整性,其中重点讲解了非空约束和默认值约束;最后讲解了引用完整性。对于本章,大家需要多理解引用完整性,以便于后面多表查询的学习。
回复

使用道具 举报

1

主题

8

帖子

13

积分

新手上路

Rank: 1

积分
13
发表于 2025-4-24 09:39:53 | 显示全部楼层
垃圾内容,路过为证。
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

快速回复 返回顶部 返回列表