数据库技术 课件 项目8　视图、索引与数据优化

数据库技术8.1视图的创建与使用8.1视图的创建与使用MySQL视图是一种虚拟表，基于SQL查询结果集创建，不存储实际数据，仅保存查询定义。通过视图，可以简化复杂查询、增强数据安全性及实现数据逻辑独立性。创建视图使用CREATEVIEW语句，并指定视图名称和查询语句。视图的使用与表类似，可执行SELECT、UPDATE等操作，但需视具体权限和视图的可更新性而定。视图的定义与优点8.1.18.1.1视图的定义与优点

视图是基于SQL查询结果的虚拟表，不存储数据，仅保存查询定义，用于简化查询和增强数据安全。

使用视图有以下优点：

简化复杂查询：视图可以简化复杂查询，使用户无需关心底层数据结构的复杂性，只需通过视图即可获取所需的数据。

增强数据安全性：视图可以限制用户对基础表的访问权限，确保用户只能访问其被允许查询的结果集，从而保护敏感数据不被非法访问或修改。

提高数据独立性：视图提供了一种逻辑层的数据抽象，当基础表的结构发生变化时，视图可以屏蔽这些变化对用户的影响，从而保持数据的独立性。

实现数据重用：视图可以被多个查询或应用程序共享，避免了重复编写相同的查询语句，提高了代码的可重用性和可维护性。视图的创建与修改8.1.28.1.2视图的创建与修改1.创建视图在MySQL中，可以使用CREATEVIEW语句来创建视图。创建视图时，需要指定视图的名称和用于生成视图数据的SQL查询语句。语法格式如下：CREATE[ORREPLACE]VIEWview_name[(column_list)]ASSELECT_statement[WITH[CASCADED|LOCAL]CHECKOPTION];8.1.2视图的创建与修改参数说明：

ORREPLACE（可选）：如果指定的视图已经存在，则替换它。如果不存在，则创建一个新的视图。

view_name：表示要创建的视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。

column_list（可选）：表示属性清单，即视图中各个属性的名称。如果指定了此子句，则视图的列名将按照此清单中的顺序和名称来定义。默认情况下，如果不指定此子句，则视图的列名将与SELECT语句中查询的属性名称相同。

SELECT_statement：是一个完整的查询语句，用于从某个表或视图中查出某些满足条件的记录，并将这些记录导入视图中。这个查询语句可以包含SELECT子句、FROM子句、WHERE子句、GROUPBY子句、HAVING子句等，用于筛选、排序和连接数据。

WITH[CASCADED|LOCAL]CHECKOPTION（可选）：表示视图在更新时保证在视图的权限范围之内。CASCADED：表示更新视图时要满足所有相关视图和表的条件。这是默认值。LOCAL：表示更新视图时只要满足该视图本身定义的条件即可。8.1.2视图的创建与修改注意：创建视图时，用户必须具有创建视图的权限。查询语句不能包含子查询、系统或用户变量、预处理语句参数等。视图定义中引用的表或视图必须存在。但是，创建完视图后，可以删除定义引用的表或视图（可能会导致视图变得无效）。视图定义中允许使用ORDERBY语句，但是若从特定视图进行选择，而该视图使用了自己的ORDERBY语句，则视图定义中的ORDERBY将被忽略。8.1.2视图的创建与修改任务1：在“学生信息管理系统”数据库中，创建一个名称为“学生成绩”的视图，使用这个视图可以按学号的升序显示学生的学号、姓名、课程名、成绩。操作步骤如下，代码如下：CREATEVIEW学生成绩ASSELECTstudents.stu_id,stu_name,crs_name,sc_gradeFROMstudents,courses,scoresWHEREstudents.stu_id=scores.stu_idANDcourses.crs_id=scores.crs_idORDERBYstudents.stu_idASC;视图创建成功，可以通过SHOWTABLES命令查看。运行结果如图8.1所示：图8.1查看数据库中的表、视图

在任务1中，因为学号（stu_id）、姓名（stu_name）来自students表，课程名（crs_name）来自courses表，成绩（sc_grade）来自scores表，要查询这些信息，需要建立多表查询。代码如下：SELECTstudents.stu_id,stu_name,crs_name,sc_gradeFROMstudents,courses,scoresWHEREstudents.stu_id=scores.stu_idANDcourses.crs_id=scores.crs_id;建议初学者在创建视图之前，先编写并验证一个查询语句，确保其返回的结果准确无误后，再依据该查询来构建视图：8.1.2视图的创建与修改任务2：在“学生信息管理系统”数据库中，创建一个名称为“学生成绩_高等数学”的视图，使用这个视图可以按学号的升序显示学生的学号、姓名、课程名、高等数学成绩。代码如下：CREATEVIEW学生成绩_高等数学ASSELECTstudents.stu_id,stu_name,crs_name,sc_gradeFROMstudents,courses,scoresWHEREstudents.stu_id=scores.stu_idANDcourses.crs_id=scores.crs_idANDcrs_name='高等数学'ORDERBYstudents.stu_idASCWITHCHECKOPTION;为了确保该视图在更新时，在视图的权限范围之内。需要使用参数WITHCHECKOPTION。8.1.2视图的创建与修改2.修改视图

若需要修改视图，可以使用ALTERVIEW语句，或者先删除视图再重新创建。使用ALTERVIEW语句时，需要指定要修改的视图名称和新的查询语句。代码如下：ALTERVIEW<视图名>ASSELECT_statement[WITH[CASCADED|LOCAL]CHECKOPTION];ALTERVIEW语句的语法与CREATEVIEW类似，这里不再详细介绍。注意：要实现视图的修改，用户需要具有针对该视图的CREATEVIEW和DROP权限，以及由SELECT语句选择的每一列上的某些权限。8.1.2视图的创建与修改任务3：修改“学生成绩_高等数学”视图，增加课程编号（crs_id）字段。代码如下：ALTERVIEW学生成绩_高等数学ASSELECTstudents.stu_id,stu_name,courses.crs_id,crs_name,sc_gradeFROMstudents,courses,scoresWHEREstudents.stu_id=scores.stu_idANDcourses.crs_id=scores.crs_idANDcrs_name='高等数学'ORDERBYstudents.stu_idASCWITHCHECKOPTION;8.1.2视图的创建与修改2.删除视图

若要删除视图，必须拥有DROP权限。如果没有足够的权限，将无法删除视图。语法格式如下：LOCAL]CHECKOPTION];参数说明：

view_name是要删除的视图的名称。可以一次性删除多个视图，各个视图名称之间用逗号隔开。

IFEXISTS是可选的，它用于在视图不存在时避免产生错误。任务4：删除学生成绩、学生成绩_高等数学视图。代码如下：dropVIEW学生成绩,学生成绩_高等数学;注意：使用DROPVIEW语句只能删除视图的定义，而不会删除视图所依赖的数据。视图中的数据仍然存在于基本表中。

视图的使用8.1.38.1.3视图的使用1.通过视图操作数据

可以通过视图修改基表中的数据，包括UPDATE、INSERT和DELETE操作，由于视图是虚表，本身并不保存数据，所以通过视图来修改数据实质上是修改视图引用的基表中的数据，只有在满足下列条件时，才可以通过视图修改基础基表的数据：

只能引用一个基表的列：如果视图是基于两个或更多基表创建的，那么通过该视图进行的修改将不被允许。这是因为MySQL无法确定修改应该应用于哪个基表。如果确实需要修改多个基表，那么必须分别对每个基表进行修改。

直接引用表列中的基础数据：视图中的列必须直接对应于基表中的列，而不能是通过任何计算或函数派生得到的。例如，如果视图中的一列是通过CONCAT函数将两个基表列连接起来的，那么这一列就不能被修改。

不能修改计算列：如果视图中的列是通过某种计算得到的（如使用+、-、*、/等运算符），那么这一列也不能被修改。

不受GROUPBY、HAVING或DISTINCT子句的影响：如果视图包含GROUPBY、HAVING或DISTINCT子句，那么这些子句所作用的列将不能被修改。这是因为这些子句改变了数据的聚合方式或去重方式，使得修改操作无法准确地定位到基表中的具体行。

权限要求：用户需要对目标基表具有相应的UPDATE、INSERT或DELETE权限。8.1.3视图的使用任务5：在“学生信息管理系统”数据库中，创建一个名称为“学生成绩”的视图，使用这个视图可以显示学生的学号、姓名、课程编号、课程名、成绩。并通过这个视图将学号为23510102710101同学的大学英语修改为99分。首先创建视图学生成绩，代码如下：CREATEVIEW学生成绩ASSELECTstudents.stu_id,stu_name,courses.crs_id,crs_name,sc_gradeFROMstudents,courses,scoresWHEREstudents.stu_id=scores.stu_idANDcourses.crs_id=scores.crs_idWITHCHECKOPTION;图8.2视图学生成绩查询结果图8.3基本表scores查询结果接下来修改数据，代码如下：UPDATE学生成绩SETsc_grade=99WHEREstu_id='23510102710101'ANDcrs_name='大学英语'运行成功后，使用查询语句分别查询视图学生成绩，表scores,代码和运行结果如图8.2，图8.3所示。UPDATE修改数据实际上是将视图所依赖的基本表中的数据进行修改，如果一个视图依赖于多个基本表，则通过该视图修改数据，一次只能变动一个基本表的数据。8.1.3视图的使用任务6：在“学生信息管理系统”数据库中，创建一个名称为“学生信息”的视图，使用这个视图可以显示“51010271”班学生的学号、姓名、班级编号。通过该视图插入一条学生记录“23510102710121，李华,51010271”。首先创建视图学生信息，代码如下：CREATEVIEW学生信息ASSELECTstu_id,stu_name,cls_idFROMstudentsWHEREcls_id='51010271'WITHCHECKOPTION;图8.4视图学生信息查询结果图8.5基本表students查询结果接下来插入数据，代码如下：INSERTINTO学生信息VALUES('23510102710121','李华','51010271');运行成功后，使用查询语句分别查询视图学生信息，表students,代码和运行结果如图8.4，图8.5所示。从运行结果可知，记录插入成功。通过视图“学生信息”插入学生信息，只能插入班级编号为“51010271”的数据，如果插入其他班级编号的数据，系统将提示“1369-CHECKOPTIONfailed'sims.学生信息'”错误提示。因为“学生信息”视图中使用了WITHCHECKOPTION，则插入的数据必须符合视图定义中SELECT语句所设置的条件。使用INSERT语句时还需要注意，INSERT语句中必须包含FROM子句中指定表中所有不能为空的列。例如，通过“学生信息”视图插入数据时，如果“姓名”字段为空，则会出现插入错误。8.1.3视图的使用任务7：通过“学生信息”视图删除学号为“23510102710121”的学生记录。代码如下：DELETEFROM学生信息WHEREstu_id='23510102710121'如果视图来源于单个的基本表，可以使用DELETE语句通过视图来删除基本表中的数据，对于依赖多个基本表的视图，则不能使用DELETE语句。8.1.3视图的使用2.通过视图查询数据

可以像查询表一样查询视图。视图在逻辑上是一个虚拟表，它是基于一个或多个表的查询结果集。因此，你可以使用标准的SQL查询语句来查询视图，就像查询物理表一样。

在数据库的查询操作中，可以使用视图来简化查询，特别是当分析需要基于多个表或复杂计算时。可以创建一个包含所需分析数据的视图，然后在分析工具中查询该视图。8.1.3视图的使用（1）创建学生成绩信息视图。代码如下：CREATEVIEW学生成绩信息ASSELECTstudents.stu_id,stu_name,courses.crs_id,crs_name,sc_gradeFROMstudents,scores,coursesWHEREstudents.stu_id=scores.stu_idandcourses.crs_id=scores.crs_id;任务8：

查询选修了课程名为“数据库及应用”课程的学生学号、姓名、课程编号、课程名，成绩。

这个查询任务涉及students，courses，scores三个表的链接，我们可以将这些查询封装在一个视图中。这样，每次需要这些数据时，只需简单地查询视图即可，而无需重复编写复杂的查询语句。（2）查询选修了课程编号为“230101”课程的学生成绩信息。代码如下：SELECT*FROM学生成绩信息WHEREcrs_name='数据库及应用';8.1.3视图的使用任务9：查询选修了课程“网页制作技术”，且成绩高于该课程平均分的学生的学号、姓名和成绩。代码如下：SELECTstu_id,stu_name,sc_gradeFROM学生成绩信息WHEREcrs_name='网页制作技术'andsc_grade>(SELECTAVG(sc_grade)FROM学生成绩信息WHEREcrs_name='网页制作技术')；谢谢数据库技术8.2索引的创建与使用8.2索引的创建与使用

数据库中索引是一种高效获取数据的数据结构，它类似于书籍的目录，能够显著提升查询操作的效率。在MySQL数据库中，索引通常被创建在表的特定列上，这些索引充当了数据的快速检索路径，帮助MySQL迅速定位并访问所需的数据行，从而大大加快了针对这些列的查询速度，优化了数据库的整体性能。

索引的类型与结构8.2.18.2.1索引的类型与结构

主键索引：当表中的某个列被设为主键时，该列就是主键索引。主键索引具有唯一性和非空性。

唯一索引：索引列的值必须唯一，但允许为空值。唯一索引用于保证数据的唯一性。

普通索引：用表中的普通列构建的索引，没有任何限制。普通索引用于加速对该列的查询操作。

全文索引：主要用于文本数据的全文检索。01按功能分类

聚集索引：聚集索引要求表中数据存储的物理顺序与索引值的顺序一致。在InnoDB存储引擎中，主键索引默认为聚集索引。

二级索引（非聚集索引）：二级索引的叶子节点存储的是该字段值对应的主键值，而不是行数据本身。在查询时，需要先通过二级索引找到主键值，然后再通过主键值到聚集索引中找到行数据。02按存储形式分类

单列索引：一个索引只包含单个列。

组合索引：一个索引包含多个列。03按作用字段个数划分1.索引的类型8.2.1索引的类型与结构1.索引的结构

B-Tree是一种平衡树结构，其所有值都出现在叶子节点，且叶子节点形成一个单向链表。B-Tree索引具有查询效率高、支持范围查询和排序操作等优点，是MySQL中最常用的索引数据结构。哈希索引采用哈希算法，将键值换算成哈希值，并映射到对应的槽位上。哈希索引只能用于等值比较（=、IN），不支持范围查询。其查询效率通常很高，但在处理哈希冲突时可能需要扫描链表。

全文索引主要用于文本数据的全文检索，如文章的标题和内容。在MySQL中，全文索引通常用于MyISAM存储引擎，但在MySQL5.6及更高版本中，InnoDB存储引擎也支持全文索引。010203B-Tree索引

哈希索引全文索引索引的类型与结构8.2.28.2.2索引的特点1.索引的优点

加速查询：索引允许数据库系统直接跳到数据所在位置，避免了全表扫描，尤其是在大型数据表中，这种效果尤为明显。通过索引，数据库可以更快地定位到符合条件的数据，从而提高查询效率。

提高响应速度：由于查询可以快速定位数据，因此查询的响应时间通常会显著缩短。这对于需要快速响应的在线应用来说尤为重要。

保证数据记录的唯一性：通过创建唯一索引，可以确保表中的某一列或某几列的数据记录是唯一的，防止重复数据的插入。

实现表与表之间的参照性：索引可以用于外键约束，确保表与表之间的数据一致性。

减少排序和分组的时间：在使用ORDERBY或GROUPBY查询语句进行数据检索时，索引可以帮助减少排序和分组的时间，因为B-Tree结构的索引本身就是按照索引字段的值有序存储的。8.2.2索引的特点3.索引的优化

选择合适的列创建索引：对于经常用于WHERE查询条件、GROUPBY操作或ORDERBY操作的字段，可以创建索引以提高查询效率。同时，应尽量避免在经常更新的字段上创建索引。

创建联合索引：当查询条件涉及多个字段时，可以创建联合索引以进一步提高查询效率。在创建联合索引时，应将取值离散大的字段放在前面，以更有效地缩小结果集范围。

使用前缀索引：对于包含很长字符串描述的字段，如文章的内容摘要，可以只选取字符串的前几个字符来创建前缀索引，以减少索引字段所占用的存储空间并提高查询速度。但需要注意的是，前缀索引在某些情况下可能无法用于ORDERBY操作和覆盖索引。

主键索引最好是自增的：在InnoDB中，主键索引默认是聚簇索引。当使用自增主键时，每次插入新数据都会按顺序追加到当前索引节点的位置，这种追加式的插入操作效率极高。若使用非自增主键，则可能导致数据调整和页面变动，增加系统开销。8.2.2索引的特点1.索引的优点

加速查询：索引允许数据库系统直接跳到数据所在位置，避免了全表扫描，尤其是在大型数据表中，这种效果尤为明显。通过索引，数据库可以更快地定位到符合条件的数据，从而提高查询效率。

提高响应速度：由于查询可以快速定位数据，因此查询的响应时间通常会显著缩短。这对于需要快速响应的在线应用来说尤为重要。

保证数据记录的唯一性：通过创建唯一索引，可以确保表中的某一列或某几列的数据记录是唯一的，防止重复数据的插入。

实现表与表之间的参照性：索引可以用于外键约束，确保表与表之间的数据一致性。

减少排序和分组的时间：在使用ORDERBY或GROUPBY查询语句进行数据检索时，索引可以帮助减少排序和分组的时间，因为B-Tree结构的索引本身就是按照索引字段的值有序存储的。索引的创建8.2.38.2.3索引的创建1.使用CREATEINDEX命令创建索引MySQL中允许用户在已存在的表上创建新的索引。语法格式如下：CREATEINDEXindex_nameONtable_name(column_name(length)[ASC|DESC],...);注意：

index_name：索引的名称，索引在一个表中

的名称必须是唯一的。

table_name：表的名称。

column_name(length)：表示创建索引的列名，如果是多列索引，则列名之间用逗号间隔。对于某些数据类型（如VARCHAR），可以指定索引的长度。如果省略length，则索引将基于整个列的值。

ASC|DESC:规定索引按升序（ASC）还是降序（DESC）排序，默认为升序（ASC）。8.2.3索引的创建任务9：根据courses表“crs_name”列上的前8个字符建立一个升序索引name_crs_。代码如下：CREATEINDEXname_crsONcourses(crs_name(8)ASC);说明：一个索引在定义时只包含单个列，这样的索引叫做单列索引。任务10：在scores表的“stu_id”列和“crs_id”列上建立一个复合索引stu_crs。代码如下：CREATEINDEXstu_crsONscores(stu_id,crs_id);说明：一个索引在定义时可以包含多个列，列与列之间用逗号间隔，这些列同属于同一个表，这样的索引叫做组合索引。8.2.3索引的创建2.在CREATETABLE时定义索引在创建数据表的同时定义索引是一种高效的索引创建方式，它将索引的创建和表的定义结合在一起。语法格式如下：CREATETABLEtable_name(column1datatype,column2datatype,...PRIMARYKEY(column_name,...)--主键索引|INDEX|KEY[index_name](column_name,...)--普通索引|UNIQUE[INDEX][index_name](column_name,...)--唯一索引|[FULLTEXT][INDEX][index_name](column_name,...)--全文索引);参数说明：

column_name:

需要创建索引的列的名称。对于复合索引，可以指定多个列名。

[index_name]:可选的索引名称。如果不指定，MySQL将自动生成一个名称。建议为索引指定一个描述性的名称，方便在后续的管理和调试过程中识别索引。8.2.3索引的创建任务11：创建scores_copy表,设置“stu_id”,“crs_id”列为组合主键，并在“sc_grade”列上创建普通索引。代码如下：CREATETABLEscores_copy(stu_idchar(14)notnull,crs_idchar(6)notnull,sc_gradeDECIMAL(5,2)null,sc_rebuildDECIMAL(5,2),PRIMARYKEY(stu_id,crs_id),--组合主键 INDEX(sc_grade)--普通索引);8.2.3索引的创建2.使用ALTERTABLE命令添加索引对于已经存在的表，如果需要在其中添加索引，可以使用ALTERTABLE命令。语法格式如下：ALTERTABLEtable_nameADDINDEXindex_name(column_name(length));参数说明：

ALTERTABLE:修改表结构。

table_name:修改的表的名称。ADDINDEX:向表中添加一个新的索引。任务12：在courses表“crs_name”列上创建一个普通索引。代码如下：ALTERTABLEcoursesADDINDEX(crs_name);

索引的查看与删除8.2.48.2.4索引的查看与删除1.查看索引MySQL提供了多种方法来查看表中的索引信息。（1）使用SHOWINDEX命令语法格式如下：SHOWINDEXFROMtable_name;参数说明：table_name：要查看索引的表的名称。执行该命令后，会显示表的索引名、字段名、索引类型等信息。任务13：查看在courses表中的索引。代码如下：SHOWINDEXFROMcourses;运行结果如图8.6所示：图8.6查看courses表中的索引8.2.4索引的查看与删除（1）使用INFORMATION_SCHEMA.STATISTICS表语法格式如下：SELECT*FROMINFORMATION_SCHEMA.STATISTICSWHERETABLE_SCHEMA='your_database'ANDTABLE_NAME='your_table';参数说明：

your_dat