数据库应用技术教程 课件 第5章 SQL 2022中视图和索引的应用

知识回顾查询没有选修的1号课程的学生的学号和姓名。第5章SQL2022中视图和索引的应用学习目标理解视图的作用；掌握视图的概念、特点和类型；掌握创建视图、修改视图和删除视图的方法；掌握查看和加密视图定义文本；掌握通过视图修改基表中的数据；掌握使用图形工具管理视图口。理解索引的优点和缺点；了解聚集索引和非聚集索引的特点；掌握索引与约束的关系；掌握使用CREATEINDEX语句创建索引的方式；掌握查看、删除和修改索引；掌握分析和维护索引。5.1.1视图概述

视图的定义：视图是一种常用的数据库对象，可以把它看成从一个或几个基本表导出的虚表或存储在数据库中的查询。视图概述-视图的作用

简化操作提高数据安全性屏蔽数据库的复杂性数据即时更新说明：视图一经定义后，就可以像基本表一样可以被查询、删除。视图为查看和存取数据提供了另外一种途径。5.1.2创建视图使用ManagementStudio使用CreateView视图设计器关系图窗格条件窗格SQL窗格结果窗格

创建视图--使用ManagementStudio【例5.1】创建视图Stu_sc1，要求显示学生的学号、姓名、性别和选课的课程号、成绩。

创建视图--使用CreateView语法格式：CREATEVIEW视图名[(column[,...n])][WITHENCRYPTION]ASselect_statement[WITHCHECKOPTION]参数说明如下｡Column：表示视图中的列名。WITHENCRYPTION：对包含CREATEVIEW语句文本的条目进行加密。AS：表示视图要执行的操作。select_statement：定义视图的SELECT语句。WITHCHECKOPTION：强制针对视图执行的所有数据修改语句都必须符合在select_statement中设置的条件。创建视图--使用CreateView（续）【例5.2】在学生选课数据库中，建立信息系学生的的学号、姓名、性别和年龄视图。CREATEVIEWIS_Student AS SELECTSno,Sname,Ssex,Sage FROMStudentWHERESdept=‘信息系'创建视图--使用CreateView（续）【例5.3】在学生选课数据库中，创建学生选课视图stu_sc2，视图包含学生学号、姓名、课程号、成绩，并对创建视图文本进行加密。代码如下：Createviewstu_sc2(学号,姓名,课程号,成绩)WithencryptionasSelectStudent.sno,sname,cno,gradeFromstudentinnerjoinscOnStudent.sno=sc.sno练习1创建一个视图，使其统计每个学生的考试平均成绩和修课总门数。练习2创建一个包含每门课程的课程号，课程名，选课人数，平均成绩的视图。练习3创建每个系的平均年龄的视图sdept_avg_sage。在创建或使用视图时的限制情况如果视图中某一列是函数、数学表达式、常量或来自多个表的列名相同，则必须为列定义名字。当通过视图操作数据时，SQLServer不仅要检查视图引用的表是否存在，是否有效，而且还要验证对数据的修改是否违反了数据的完整性约束。5.1.3视图的管理修改视图删除视图查看视图应用视图修改视图

通过ManagementStudio

使用ALTERVIEW语句修改视图语法格式如下。ALTERVIEW视图名[(column[,...n])][WITHENCRYPTION]ASselect_statement[WITHCHECKOPTION]修改视图（续）【例5.4】将例5.1中的视图Stu_sc1，修改为男学生选课视图。代码如下：AlterviewStu_sc1AsSELECTstudent.Sno学号,Sname姓名,Ssex性别,Cno课程号,Grade成绩FROMscINNERJOINstudentONsc.Sno=student.SnoWhereSsex='男'删除视图使用Managementstudio使用DROPVIEW语句语法格式如下。DROPVIEW视图名[,…n]【例5.5】删除视图sc_count视图。代码如下：

DROPVIEWsc_count查看视图系统存储过程sp_help

用来返回有关数据库对象的详细信息，如果不针对某一特定对象，则返回数据库中所有对象信息。系统存储过程sp_depends

返回系统表中存储的任何信息，该系统表指出该对象所依赖的对象。系统存储过程sp_helptext（若试图加密了，就无法看了）检索出视图､触发器､存储过程的文本。5.1.4视图的应用1、利用视图查询数据【例5.6】在学生选课数据库中，查询平均年龄超过22岁的系别和平均年龄。代码如下：USE学生选课GOSELECT系别名称,平均年龄Fromsdept_avg_sageWhere平均年龄>22视图的应用2、利用视图更新数据【例5.7】在学生选课数据库中，利用已有视图IS_Student(sno,sname,ssex,sage,sdept)，增加一个新的女同学“李娜”，年龄22，信息系，学号“95088”。Insertintois_studentvalues(‘95088’,‘李娜’,‘女’,22,‘信息系')5.2索引-索引的作用

索引是一种重要的数据对象，它由一行行的记录组成，而每一行记录都包括数据表中一列或若干列值的集合，而不是数据表中的所有记录，因而能够提高数据的查询效率。此外，索引还可以用来确保列的惟一性，从而保证数据的完整性。索引的分类

聚集索引非聚集索引惟一索引包含性列索引索引视图全文索引XML索引其中，聚集索引和非聚集索引是数据库引擎最基本的索引索引的分类1、聚集索引（也称簇索引或簇集索引）在聚集索引中，表中的行的物理存储顺序和索引顺序完全相同(类似于图书目录和正文内容之间的关系)。聚集索引对表的物理数据页，按列进行排序，然后再重新存储到磁盘上。2、非聚集索引（也称非簇索引或非簇集索引）非簇索引具有与表的数据行完全分离的结构，非聚集索引的叶节点存储了组成非聚集索引的关键字值和一个指针，指针指向数据页中的数据行，该行具有与索引键值相同的列值，非聚集索引不改变数据行的物理存储顺序，因而一个表可以有多个非聚集索引。索引的分类3、惟一索引如果为了保证表或视图的每一行在某种程度上是惟一的，可以使用惟一索引，也就是说索引值是惟一的。创建数据表时如果设置了主键，则SQLServer2022就会默认建立一个惟一索引。4、包含性列索引使用包含性列索引，可以通过将非键列添加到非聚集索引的叶级来扩展其功能，创建覆盖更多查询的非聚集索引。索引的分类5、视图索引视图索引是为视图创建的索引。其存储方法与带聚集索引的表的存储方法相同。6、全文索引全文索引是一种特殊类型的基于标记的功能性索引，由MicrosoftSQLServer全文引擎(MSFTESQL)服务创建和维护。7、XML索引

XML索引是XML数据关联的索引形式，是XML二进制BLOB的已拆分持久表示形式，可分为主索引和辅助索引。索引和约束的关系

对列定义PRIMARYKEY约束和UNIQUE约束时，会自动创建索引。1、PRIMARYKEY约束和索引如果创建表时，将一个特定列标识为主键，自动对该列创建PRIMARYKEY约束和惟一聚集索引。2、UNIQUE约束和索引默认情况下，创建UNIQUE约束，自动对该列创建惟一非聚集索引。当用户从表中删除主键约束或惟一约束时，创建在这些约束列上的索引也会被自动删除。3、独立索引使用CREATEINDEX语句或SQLServerManagementStudio对象资源管理器中的【新建索引】对话框创建独立于约束的索引创建索引使用ManagementStudio使用CREATEINDEX语句CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]/*索引的类型*/INDEX索引名ON{表名|视图名}列名[ASC|DESC][,...n])创建索引【例5.8】在学生表上创建学生学号的聚集索引。操作步骤如下。（1）启动ManagementStudio。（2）在【对象资源管理器】中，展开【学生选课】|【表】|dbo.Student】|【索引】。在【索引】节点下，可以发现系统已默认依据设置的主键自动产生了一个聚集索引“PK_student”。说明：当用户在Student表中创建主键约束，则SQLServer2022数据库引擎自动对该列创建PRIMARYKEY约束和惟一聚集索引。创建索引【例5.9】在学生选课数据库中，经常要使用学生的姓名进行查询，为提高查询效率，请创建姓名列为非聚集索引。代码如下：CREATEINDEXIX_snameONstudent(sname)删除索引使用ManagementStudio删除独立于约束的索引使用DROPINDEX语句删除独立于约束的索引【例5.10】删除student表的索引Sname_index。Dropindexstudent.Sname_index说明：由于PK_student聚集索引是由student表在创建主键约