关系数据库与SQL-Server(2019版)教学课件U19

模块六创建与管理视图和索引学习内容

视图创建视图管理视图索引创建索引管理索引

学习目标知识目标了解视图的概念和作用了解索引的概念、作用、结构和类型能力目标掌握创建和管理视图掌握创建和管理索引模块六创建与管理视图和索引2创建和管理视图任务1：认识视图视图的含义是基于某个查询结果的虚表。是用户查看和修改数据表中数据的一种方式。每个视图都有若干被定义的列和行。视图与基表的关系视图中的数据列和行来源于是其引用的基表。视图所对应的数据并不实际存储在数据库中，而是仍存储在视图所引用的基表中。数据库中只存储视图的定义。模块六创建与管理视图和索引3创建和管理视图任务1：认识视图视图的作用聚焦特定数据：使用户只能看到和操作与他们有关的数据，提高了数据的安全性。定制用户数据：使不同水平的用户能以不同的方式看到不同的数据。合并分离数据：视图可以从水平和垂直方向上分割数据，但原数据库的结构保持不变。简化数据操作：使用户不必写复杂的查询语句就可对数据进行操作。模块六创建与管理视图和索引4创建和管理视图任务2：创建视图使用SSMS创建视图使用CREATEVIEW语句创建视图语法格式：

CREATEVIEW视图名[(列名1,列名2[,…n])][WITHENCRYPTION]

AS

SELECT语句

[WITHCHECKOPTION]模块六创建与管理视图和索引5强制所有通过视图修改的数据满足SELECT语句中指定的条件。对视图的定义进行加密。创建和管理视图任务2：创建视图实例1：创建一个名为“V_不及格学生信息”的视图，该视图包含所有有不及格记录的学生的学号、姓名和原始成绩。CREATEVIEWV_不及格学生信息(学号,姓名,原始成绩)ASSELECTDISTINCTXSB.学号,姓名,成绩FROMXSB,CJBWHEREXSB.学号=CJB.学号ANDCJB.成绩<60GOSELECT*FROMV_不及格学生信息模块六创建与管理视图和索引6通过视图查询基表中的数据。创建和管理视图任务3：修改视图使用SSMS修改视图使用ALTERVIEW语句修改视图语法格式：

ALTERVIEW视图名[(列名1,列名2[,…n])][WITHENCRYPTION]

AS

SELECT语句

[WITHCHECKOPTION]模块六创建与管理视图和索引7创建和管理视图任务3：修改视图实例2：修改“V_学生信息”视图，使该视图用于查询是女生的获奖情况，并强制检查指定条件。ALTERVIEWV_学生信息ASSELECT学号,姓名,性别,出生日期,所属院系,专业名称,备注AS获奖情况FROMXSBWHERE性别=0AND备注LIKE'%’WITHCHECKOPTIONGOSELECT*FROMV_学生信息模块六创建与管理视图和索引8提示：如果在创建视图时使用了WITHENCRYPTION或WITHCHECKOPTION子句，并且要保留选项提供的功能，则必须在ALTERVIEW命令中包含它，否则这些选项不再起作用。创建和管理视图任务4：通过视图管理表数据视图可以代替表完成从表中查询、插入、更新和删除数据的操作。实例3：通过“V_学生信息”视图添加一条记录。INSERTINTOV_学生信息

VALUES(‘1903030112’,‘李晴’,

0,‘2002-01-08’,‘通信工程学院’,‘移动通信技术’,‘2020年

获一等奖学金’)模块六创建与管理视图和索引9创建和管理视图任务4：通过视图管理表数据通过“V_学生信息”视图再添加一条记录。INSERTINTOV_学生信息

VALUES(‘1903030113’,‘陈鹏飞’,

1,‘2001-11-18’,‘通信工程学院’,‘移动通信技术’,NULL)模块六创建与管理视图和索引10请思考：上面的插入语句为什么添加数据失败？因为“V_学生信息”视图中的“WITHCHECKOPTION“选项，会检查插入的数据是不是满足视图中设置的条件，即：性别字段是否为0？备注字段是否为非空？显然，上面的插入数据不符合条件。创建和管理视图任务4：通过视图管理表数据插入数据的注意事项可通过视图向基表中插入数据，但插入的数据实际上存放在基表中，而不是存放在视图中。如果视图引用了多个表，使用INSERT语句插入的列必须属于同一个表。若创建视图时定义了”WITHCHECKOPTION”选项，则使用视图向基表中插入数据时，必须保证插入后的数据满足定义视图的限制条件。说明：如果不想让不满足视图定义的数据插入到基表中，可以在定义视图时，加上”WITHCHECKOPTION”。模块六创建与管理视图和索引11创建和管理视图任务4：通过视图管理表数据实例4：将“V_学生信息”视图中的“张婧”改为“张婧婧”。UPDATEV_学生信息

SET

姓名=‘张婧婧’

WHERE

姓名=‘张婧’

GO

SELECT*FROMV_学生信息模块六创建与管理视图和索引12创建和管理视图任务4：通过视图管理表数据实例5：通过“V_学生信息”视图删除姓名为“张婧婧”的记录。DELETEFROMV_学生信息WHERE

姓名=‘张婧婧’模块六创建与管理视图和索引13注意：要删除的数据必须包含在视图的结果集中。如果视图引用了多个表时，无法用DELETE命令删除数据。创建和管理视图任务5：删除视图语法格式：DROPVIEW视图名[,…n]模块六创建与管理视图和索引14引请思考:在一个数据表中检索数据时是如何进行的?如果采取逐行扫描的方式进行检索,可行吗?有什么方法可提高检索效率？模块六创建与管理视图和索引15对于小表来说，是可行的。但对于有成千上万行的表来说，其搜索的效率非常低。建立索引。创建和管理索引任务1：认识索引索引的含义索引是对表的一列或多个列的值进行排序的一种物理结构，它由该表的一列或多个列的值，以及指向这些列值对应记录存储位置的指针所组成。索引如同书的目录。索引的结构模块六创建与管理视图和索引16创建和管理索引任务1：认识索引建立索引的优点建立了索引的列作为查询条件时，数据的检索速度能大大地提高。增加索引的不利方面创建索引也要花费时间和占用物理空间。虽然索引加快了检索速度，但减慢了数据修改的速度(因为每执行一次数据修改，就需要对索引进行维护)。模块六创建与管理视图和索引17创建和管理索引任务1：认识索引何时创建索引？在经常用作查询条件的列上在经常要排序的列上在主键上在用于连接的列(外键)上模块六创建与管理视图和索引18创建和管理索引任务1：认识索引何时可不考虑建立索引？很少或从来不作为查询条件的列在小表中的任何列属于TEXT、IMAGE、长度较大的CHAR、VARCHAR、BINARY等数据类型的列当修改的性能需求远大于查询的性能需求时，不要创建索引模块六创建与管理视图和索引19创建和管理索引任务1：认识索引索引的类型：在SQLServer中，索引可以分为聚集索引、非聚集索引、唯一索引、包含性列索引、索引视图、全文索引和XML索引七种。按数据的物理存放顺序，索引可分为：聚集索引：会改变表记录的物理存储顺序，使之与索引列的顺序完全相同非聚集索引：不改变表记录的存放顺序模块六创建与管理视图和索引20创建和管理索引任务2：创建索引系统自动建立索引添加主键约束时系统会自动在表中生成一个唯一聚集索引。在XSB表的“学号”列上已创建了唯一聚集索引在CJB表的“学号和课程号”两列上已创建了唯一聚集索引在KCB表中的“课程号”列上已创建了唯一聚集索引添加唯一性约束时系统会自动在表中生成一个唯一非聚集索引。模块六创建与管理视图和索引21创建和管理索引任务2：创建索引手动建立索引使用SSMS创建索引使用CREATEINDEX语句创建索引模块六创建与管理视图和索引22创建和管理索引任务2：创建索引语法格式：CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEX索引名ON{表名|视图名}(列名[ASC|DESC][,...n])WITH<索引选项>模块六创建与管理视图和索引23说明：一个表中只能创建1个聚集索引。由于系统已自动在主键上创建了聚集索引，所以用户不能再创建，除非先删除已有的。一个表中可以创建若干个非聚集索引。创建和管理索引任务2：创建索引实例1：为CJB表创建非聚集索引IX_CJB_学号_课程号，该索引包括学号和课程号两个索引列，均按升序排列。CREATEINDEXIX_CJB_学号_课程号ONCJB(学号,课程号)实例2：对上面的索引作一点修改，使该索引变为唯一性的非聚集索引。CREATEUNIQUEINDEXIX_CJB_学号_课程号ONCJB(学号,课程号)WITHDROP_EXISTING模块六创建与管理视图和索引24删除同名的原索引，重建新索引创建和管理索引任务2：创建索引实例3：为“CJB”表创建一个唯一索引，索引字段为“课程号”、“学号”，其中“课程号”为降序排列，该索引的包含性列里面包含“成绩”、“学分”两个字段。指定索引填充，索引的填充因子为70，并允许在Tempdb数据库中存储临时排序的信息，可以忽略索引的重复键值，运行自动重新计算统计信息。CREATEUNIQUEINDEXIX_CJB_1ONCJB(课程号DESC,学号)INCLUDE(成绩,学分)WITH(PAD_INDEX=ON,FILLFACTOR=70,SORT_IN_TEMPDB=ON,IGNORE_DUP_KEY=ON,STATISTICS_NORECOMPUTE=OFF)模块六创建与管理视图和索引25包含性列指定索引填充指定填充因子在Tempdb数据库中存储临时排序的结果忽略重复的值自动重新计算统计信息创建和管理索引任务3：查看索引信息使用SSMS查看索引信息使用sp_helpindex存储过程查看索引信息语法格式：[EXEC]sp_helpindex

表名|视图名模块六创建与管理视图和索引26创建和管理索引任务4：删除索引使用SSMS删除索引使用DROPINDEX语句删除索引语法格式：

DROPINDEX{表名.|视图名.}索引名[,…n]实例4：删除XSB表中的索引IX_XSB_XM。

DROPINDEXIX_XSB_XM模块六创建与管理视图和索引27注意：SQLServer系统自动建立的索引不能用DROPINDEX删除，只能用ALTERTABLE语句中的DROPCONSTRAINT子句来解除加在该字段上的主键约束或唯一性约束，这些约束一解除，相关的索引也就被删除了。本课小结创建和管理视图视图的概念和作用