（本科）第8章 视图和索引ppt课件.pptx

《（本科）第8章 视图和索引ppt课件.pptx》由会员分享，可在线阅读，更多相关《（本科）第8章 视图和索引ppt课件.pptx（33页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、课程主讲人：第8章 视图和索引第第8章章 视图和索引视图和索引本章主要介绍视图和索引的基本概念及基本操作。本章内容:8.1 视图8.1.1 视图概述8.1.2 创建视图8.1.3 修改视图8.1.4 使用视图8.2 索引8.2.1 索引简介8.2.2 索引类型8.2.3 创建索引8.2.4 查看索引信息8.2.5 删除索引1. 视图的优点(1) 简单、方便性。(2)数据逻辑独立性。(3) 安全性。8.1 视图8.1.1 视图概述视图作为一种数据库对象，用户通过视图来浏览数据表中感兴趣的部分或全部数据。视图是从一个或多个表中使用SELECT语句导出的虚拟表，同基本表一样，它包含了一系列带有名称的

2、列和行数据，但它并不是以一组数据的形式存储在数据库中，数据库中只存储视图的定义，而不存储视图对应的数据（除非是索引视图），这些数据仍存储在导出视图的基本表中。当基本表中的数据发生变化时，从视图中查询出来的数据也随之改变。2. 视图的分类（1）标准视图标准视图是为了系统应用需求，用户自定义的视图。标准视图组合了一个或多个表中的数据，用户通过标准视图可以实现对数据库的数据进行查询、修改和删除等基本操作。标准视图是用户使用较多的一种视图。（2）索引视图索引视图是被具体化了的视图，即已经对视图定义进行了计算并且生成的数据像表一样存储。 可以为视图创建索引，即对视图创建一个唯一的聚集索引。 索引视图可以

3、显著提高某些类型查询的性能。 索引视图尤其适于聚合许多行的查询。 但它们不太适于经常更新的基本数据集。（3）分区视图分区视图在一台或多台服务器间水平连接一组成员中的分区数据。这样，数据看上去如同来自于一个表。分区视图分为本地分区视图和分布式分区视图，在本地分区视图中所有相关的表和视图都连接在同一个 SQL Server 实例上，在分布式分区视图中至少有一个相关表是连接在不同的（远程）服务器上的。（4）系统视图系统视图公开目录元数据。即可以使用系统视图返回与 SQL Server 实例或在该实例中定义的对象相关的信息。 例如，可以通过查询 sys.databases 目录视图以便返回与实例中提供

4、的用户定义数据库有关的信息。8.1.2 创建视图创建视图时必须遵循以下原则：(1)只能在当前数据库中创建视图。但是，如果使用分布式查询定义视图，则新视图所引用的表和视图可以存在于其他数据库中，甚至其他服务器上。(2) 视图最多可以包含1024列。(3) 视图名称必须遵循标识符的规则，且对每个用户必须唯一。此外，该名称不得与该用户拥有的任何表的名称相同。(4)用户不能创建临时视图，也不能引用临时表或表变量来创建视图，但可以在其他视图之上再建立视图。(5)如果视图中的某一列是一个算术表达式、内置函数或常量派生而来，而且视图中两个或者更多的不同列拥有一个相同的名字(这种情况通常是因为在视图的定义中有

5、一个连接，而且这两个或者多个来自不同表的列拥有相同的名字)，此时，用户需要为视图的每一列指定特定的名称。(6)视图定义的SELECT语句不可以包含ORDER BY子句，除非在 SELECT 语句的选择列表中也有一个 TOP 子句。ORDER BY 子句仅用于确定视图定义中的 TOP 或 OFFSET 子句返回的行。 ORDER BY 不保证在查询视图时得到有序结果，除非在查询本身中也指定了 ORDER BY。(7)视图定义中的 SELECT 子句不能包括INTO关键字、OPTION子句或COMPUTE（BY）子句。(8)如果某个视图依赖于已删除的表（或视图），则当有人试图使用视图时，数据库引擎

6、将产生错误消息。如果创建了新表或视图（该表的结构与以前的基表没有不同之处）以替换删除的表或视图，则视图将再次可用。如果新表或视图的结构发生更改，则必须删除并重新创建该视图。 (8)通过视图进行查询时，数据库引擎将进行检查以确保语句中任何位置被引用的所有数据库对象都存在，这些对象在语句的上下文中有效，以及数据修改语句没有违反任何数据完整性规则；如果检查失败，将返回错误消息；如果检查成功，则将操作转换为对基本表的操作。(10)创建视图后，有关视图的信息将存储在下列目录视图中：sys.views、sys.columns和sys.sql_expression_dependencies。CREATE V

7、IEW语句的文本将存储在sys.sql_modules目录视图中。可以通过查询这些目录视图，查看有关的视图信息。在SQL Server 2014中创建视图主要有两种方式：1. 在SQL Server Management Studio中使用向导创建视图。【例8-1】创建所有在年龄大于22岁（包含22岁）的所有男生的学生信息视图，视图中只显示学号、姓名、性别和年龄，视图命名为view_s。2. 通过在查询窗口中执行T-SQL语句创建视图。在SQL Server 2014中创建视图的基本语法格式如下：CREATE VIEW schema_name . view_name (column ,.n )

8、 WITH ,.n AS select_statement WITH CHECK OPTION ; := ENCRYPTION SCHEMABINDING VIEW_METADATA 【例8-2】创建“S_C_SC”视图，包括“计算机”专业的学生的学号、姓名，和他们选修的课程号、课程名和成绩。【例8-3】创建视图名为view_sg，视图中只显示学号、姓名、性别和年龄。8.1.3 修改视图如果创建的视图不符合要求，需要对所创建的视图进行修改，则可以采用以下两种方法：1在SQL Server Management Studio中修改视图2使用T-SQL语句修改视图其基本语法格式如下：ALTER V

9、IEW schema_name . view_name ( column ,.n ) WITH ,.n AS select_statement WITH CHECK OPTION ; := ENCRYPTION SCHEMABINDING VIEW_METADATA 【例8-4】修改“S_C_SC”视图，要求包括每个学生的学号、姓名和选修的课程总数。8.1.4 使用视图1使用视图进行数据查询查询视图的方法和查询表的方法是一样的，可以在SQL Server Management Studio中选中要查询的视图并打开，浏览该视图查询的所有数据；也可以在查询窗口中执行T-SQL语句查询视图。【例8-

10、5】在查询窗口中查询view_sg视图，统计男女生的平均年龄。2使用视图修改基本表中数据修改视图的数据，其实就是对基本表的数据进行修改，因为视图中并不存储数据，数据还是存储在基本表中的。但是通过视图更新数据，并不是所有视图都可以更新，只有对满足可更新条件的视图才能进行更新。具体可更新条件参照教材。【例8-6】通过male_view视图向student表中插入一个“男”生。8.1.5 删除视图在不需要该视图的时候或想清除视图定义及与之相关联的权限时，可以删除该视图。视图的删除不会影响所依附的基本表的数据，定义在系统表sysahjects、syscolumns、syscomments、sysdep

11、ends和sysprotects中的视图信息也会被删除。1在SQL Server Management Studio删除视图在SQL Server Management Studio选中要删除的视图，单击右键,在弹出菜单中选择“删除”命令，进入“删除对象”对话框，单击“确定”按钮就能删除视图 。2在查询窗口中执行T-SQL语句删除视图T-SQL提供了视图删除语句DROP VIEW，其语法格式如下： DROP VIEW view_name【例8-7】删除例8-1创建的S_C_SC视图。8.2 索引8.2.1 索引简介数据库的索引就类似于书籍的目录，能加快查询速度。索引通过记录表中的关键值指向表中

12、的记录，这样数据库引擎就不用扫描整个表而定位到相关的记录。相反，如果没有索引，则会导致SQL Server搜索表中的所有记录，以获取匹配结果。8.2.2 索引类型索引根据不同的分类标准可有以下几种：1.按索引列值是否唯一分类：（1）唯一索引：索引列不允许有两行记录相同。例如，如果在表中的“姓名”列上创建了唯一索引，则以后输入的姓名将不能同名。（2）非唯一索引索引也可以是不唯一的，即索引列上可以有多行记录相同。2. 按索引列数分类：（1）单列索引：如果索引是根据单列创建的，这样的索引称为单列索引。（2）复合索引：根据多列组合创建的索引则称为复合索引。3. 按组织方式分类：（1） 聚集索引聚集索引

13、会对表和视图进行物理排序，所以这种索引对查询非常有效，在表和视图中只能有一个聚集索引。当建立主键约束时，如果表中没有聚集索引，SQL Server会用主键列作为聚集索引键。可以在表的任何列或列的组合上建立索引，实际应用中一般为定义成主键约束的列建立聚集索引。（2）非聚集索引非聚集索引不会对表和视图进行物理排序。如果表中不存在聚集索引，则表是未排序的。在表或视图中，最多可以建立250个非聚集索引，或者249个非聚集索引和1个聚集索引。聚集索引和非聚集索引都可以是唯一的索引。8.2.3 创建索引一般来说，适合建立索引的情况有：（1）在经常需要搜索的列上建立索引，可以加快搜索的速度。（2）对于主键和

14、外键列应建立索引，因为经常通过主键列查询数据，而外键用于表间的连接。（3）在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的。（4）在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，缩短排序查询时间。（5）经常使用在WHERE子句中的列上创建索引，加快条件的判断速度。（6）视图中如果包含聚集函数或连接，创建视图索引可以显著提升性能。一般来说，不适合建立索引的情况有：（1）对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，那么有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速

15、度，增大了空间需求。（2）对于那些只有很少数据值的列不应该创建索引。（3）当修改性能远远大于检索性能时，不应该创建索引。这是因为修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改远远大于检索性能时，不应该创建索引。（4）对于小型表（数据量较少）不应创建索引。因为查询优化器在遍历用于搜索数据的索引时，花费的时间可能比执行简单的表扫描还长，因此，小型表的索引可能再查询时从来不用，但仍必须在表中的数据更新时进行维护。在SQL Server 2014中，提供了两种方法建立索引：1通过SQL Server Manag

16、ement Studio创建索引2利用T-SQL语句创建索引使用T-SQL语句创建索引的语法格式如下：CREATE UNIQUE CLUSTERED | NONCLUSTERED INDEX index_name ON ( column_name ASC | DESC ,.n ) INCLUDE (column_name ,n )WHERE WITH ( ,.n) ON partition_scheme_name (column_name)|filegroup_name|default := PAD_INDEX | FILLFACTOR = fillfactor | IGNORE_DUP_KE

17、Y | DROP_EXISTING | STATISTICS_NORECOMPUTE := database_name.schema_name.|schema_name. table_or_view_name【例8-8】根据teaching数据库中学生表student的年龄列sage的降序创建一个名为“index_sage”的普通索引，索引名为index_sage。【例8-9】根据teaching数据库的student中，根据姓名sname列和专业specialty创建一个名为index_sname_specialty的唯一索引，要求姓名升序排序，专业降序排序。3间接创建索引在定义表结构或修改

18、表结构时，如果定义了主键约束（PRAMARY KEY）或者唯一性约束（UNIQUE），可以间接创建索引。【例8-10】创建一个“stu1”表，并定义了主键约束。【例8-11】创建一个“teacher”表，并定义了主键约束和唯一性约束。4创建索引视图在SQL Server Management Studio的对象资源管理器，展开相应数据库文件夹，展开“视图”选项，再展开要创建索引的视图，右击“索引”选项选择“新建索引”命令。例如，假定男生视图符合创建聚集索引的要求，按学号的升序为男生视图创建一个唯一的聚集索引，在“新建索引”对话框内输入索引名，设置索引类型，单击“确定”按钮即可新建一个索引视图。

19、 我们也可以使用T-SQL语句创建索引视图。【例8-12】创建一个“female_view”视图，并为该视图按“sno”升序创建一个具有唯一性的聚集索引。8.2.4 查看索引信息1. 在SQL Server Management Studio中查看索引信息2. 使用T_SQL语句查看索引信息(1) 使用系统存储过程sp_helpindex查看索引信息 EXEC sp_helpindex table_name (2) 使用系统存储过程sp_help查看索引信息 EXEC sp_help table_name8.2.5 删除索引1在SQL Server Management Studio中删除索引

20、与在SQL Server Management Studio中创建索引的步骤一样，选中要进行删除索引的表，选中“索引”选顶，展开“索引”选顶前面的“+”号，右键单击要删除的索引，选择“删除”按钮，弹出“删除对象”对话框，单击“确定”按钮即可。2使用T-SQL语句删除索引其T-SQL语句的语法格式为：DROP INDEX table_name.index_name【例8-13】删除student表中的“Index_姓名”索引。本章小结本章主要介绍了视图和索引的基本概念及应用。视图是关系数据库系统提供给用户以多角度查看数据库中数据的重要机制。从用户角度来看，一个视图是从一个特定角度来查看数据库中的

21、数据，不同的用户可以浏览数据库表中不同的数据，通常用了集中、简化和自定义每个用户对数据库的不同认识和需求。本章主要介绍了视图的分类：标准视图、索引视图和分区视图和系统视图；介绍了视图创建的两种常用方法：在SQL Server Management Studio中创建和使用T-SQL语句创建；介绍了视图的修改、使用和删除，通过对视图的操作可以体现到对表的操作。索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定数据。本章主要介绍了索引的类型，重点介绍了聚类索引和非聚类索引，通常我们在表中通过设置主键PRIMARY KEY来建立聚类索引，此时自动建立的索引也是唯一索引，一个表有且仅有一个聚类索引，而非聚类索引可以有多个，聚类索引和非聚类索引都可以是唯一索引也可以是非唯一索引；本章重点介绍了索引的创建和删除的方法，同样有两种方法可以快速创建、删除索引。