第5章 索引及视图操作 数据库原理应用与实践 SQL SERver2014（第2版） 主编 贾铁军 科学出版社 编著 陈国秦 万程 邢一鸣

Presentation on theme: "第5章 索引及视图操作 数据库原理应用与实践 SQL SERver2014（第2版） 主编 贾铁军 科学出版社 编著 陈国秦 万程 邢一鸣"— Presentation transcript:

1 第5章 索引及视图操作 数据库原理应用与实践 SQL SERver2014（第2版） 主编 贾铁军 科学出版社 编著 陈国秦 万程 邢一鸣
高校计算机,电气与信息 管理与商务类规划教材 上海市高校精品课程 上海教育高地建设项目 数据库原理应用与实践 SQL SERver2014（第2版） 主编 贾铁军 副主编 俞小怡 沈学东 连志刚 编著 陈国秦 万程 邢一鸣 科学出版社 第5章 索引及视图操作 1

2 目 录 1 2 3 4 5 5.1 索引概述 5.2 索引的基本操作 5.3 视图及其应用 5.5 特殊类型视图的应用 上海市高校精品课程
上海市教育高地项目 目 录 5.1 索引概述 1 5.2 索引的基本操作 2 5.3 视图及其应用 3 5.4 视图的常用操作 4 5.5 特殊类型视图的应用 5

3 教学目标 教学目标 ● 理解索引的概念、作用、特点、种类。 ● 熟悉索引的创建、更新及删除等操作方法。 ● 掌握视图的概念、特点和类型。
上海市高校精品课程 上海市教育高地项目 教学目标 教学目标 ● 理解索引的概念、作用、特点、种类。 ● 熟悉索引的创建、更新及删除等操作方法。 ● 掌握视图的概念、特点和类型。 ● 熟练掌握视图常用的基本操作。 重点 重点

4 5.1 索引概述 5.1.1 索引的概念及特点 1.索引的概念 2.索引的作用与特点
上海市高校精品课程 上海市教育高地项目 5.1 索引概述 复习要点 5.1.1 索引的概念及特点 1.索引的概念 索引（Index）是数据表中一列或几列值排序的逻辑指针清单。在数据库中，是表中数据和相应存储位置的列表,是加快检索表中数据的方法。 类似目录(位置链接) 用于快速查询 2.索引的作用与特点 1）索引的作用和优点 （1）快速高效地提高数据检索，是创建索引的最重要的原因。 （2）通过创建唯一性索引,可保证数据库表中每行数据唯一性。 （3）加速表之间的连接,有利于提高实现数据的参照完整性。 （4）利用分组和排序子句进行数据检索，可显著减少查询中分组和排序的时间。 （5）在检索数据中,利用索引可使用优化隐藏器,提高系统性能。

5 5.1 索引概述 2）索引的特点 3) 索引的缺点及问题 (1)创建和维护索引耗费时间。
上海市高校精品课程 上海市教育高地项目 5.1 索引概述 2）索引的特点 （1）索引可以加快数据库的检索速度，以索引页面减少存储空间。 （2）索引只能创建在数据库的表上，不能创建在视图上。 （3）索引既可以直接创建，也可以间接创建。 （4）使用查询处理器执行SQL语句时，在一个表上，一次只能使用一个索引。 （5）可能降低数据库插入、修改、删除等维护任务的速度。 （6）可以在优化隐藏中使用各种索引。 3) 索引的缺点及问题 (1)创建和维护索引耗费时间。 (2)索引需要占据一定的物理空间。 (3)数据增删改时，降低数据的动态维护速度。

6 5.1 索引概述 *5.1.2 索引的结构及原理 1. 索引的结构 B-树。在SQLServer中，索引按Ｂ-树（平衡树）结构进行组织。
上海市高校精品课程 上海市教育高地项目 5.1 索引概述 *5.1.2 索引的结构及原理 1. 索引的结构 B-树。在SQLServer中，索引按Ｂ-树（平衡树）结构进行组织。 索引Ｂ-树中的每一页称为一个索引节点。 Ｂ-树顶端节点称为根节点。索引中的底层节点称为叶节点。根节点与叶节点之间任何索引级别统称为中间级。 Ｂ-树主要用于在查找特定信息时， 提供一致性并节省时间。Ｂ-树先从根节点 开始，每次索引都按照一半或一少半的 树枝进行。只有少量数据时，根节点可 直指数据的实际位置.

7 上海市高校精品课程 上海市教育高地项目 5.1 索引概述 * 索引的结构及原理 2.索引的原理 SQL检索方法有两种： （1）对表逐行扫描查询 （2）索引（快速检索） SQL采取哪种方法执行特定检索取决于可用的索引、所需的列、使用的连接和表的大小等. 一个表的存储由两部分组成,一部分用于存放表的数据页面,另一部分存放索引页面。如pubs示例数据库中,employee表在 emp_id列上有一个索引。如下图所示,显示索引存储每个emp_id值并指向表中含有各值的数据行。 数据页面 索引页面

8 5.1 索引概述 5.1.3 索引的类型 1. 聚集索引 聚集索引也称为聚簇索引、群集索引或物理索引,
上海市高校精品课程 上海市教育高地项目 5.1 索引概述 5.1.3 索引的类型 1. 聚集索引 聚集索引也称为聚簇索引、群集索引或物理索引, 如同图书目录带有指针(位置链接)，对应（指向）数据存储位置按原定物理顺序(输入时自然顺序)排列，按照索引的字段（属性列）排列记录，并依排好的顺序将记录存储在表中。由于数据行本身只能按一个顺序存储,每个表只能建一个聚集索引。 按列排序 类似排序文件(物以类聚)

9 上海市高校精品课程 上海市教育高地项目 5.1 索引概述 2. 非聚集索引 非聚集索引具有完全独立于数据行的结构,使用此索引不用将物理数据页中的数据按列排序。按索引的字段排列记录，数据与索引分开存储，索引带有指针指向数据的存储位置。 索引无指定索引类型时，默认为非聚集索引，最好在唯一值较多的列创建非聚集索引,对经常需要联接和分组查询,应在联接和分组操作中使用的列上创建多个非聚集索引,在任何外键列上创建一聚集索引。如字典“偏旁部首”。 逻辑顺序 聚集索引和非聚集索引的区别.

10 5.1 索引概述 3. 其他类型索引 （1）唯一索引。若索引键值各不相同,可创建唯一索引。
上海市高校精品课程 上海市教育高地项目 5.1 索引概述 3. 其他类型索引 （1）唯一索引。若索引键值各不相同,可创建唯一索引。 （2）包含性列索引。多个列字符总数>索引列字符总数。 （3）视图索引。提高视图查询效率,将结果集永存索引中 （4）XML(Extensible markup language)索引。相对非结构化 数据索引。 （5）全文索引。基于标记的功能性索引。 可扩展标记语言

11 上海市高校精品课程 上海市教育高地项目 5.1 索引概述 各种索引类型比较

12 5.1 索引概述 *5.1.4 设计索引的策略 1. 建立索引的查询策略 上海市高校精品课程 上海市教育高地项目
（1）搜索符合特定搜索关键字值的行（精确匹配查询）。 （2）搜索其搜索关键字值为范围值的行（范围查询）。 （3）在前一表中搜索需要根据联接谓词与后一表中的某个行匹配的行。 （4）若不进行显式排序操作，按一种有序的顺序对行进行扫描，以允许基于顺序的操作，如合并联接。 （5）以优于表扫描的性能对表中所有的行进行扫描，性能提高是由于减少了要扫描的列集和数据总量。 （6）搜索插入和更新操作中重复的新搜索关键字值，实现PRIMARY KEY和UNIQUE约束。 （7）搜索已定义FOREIGN KEY约束的两个表之间匹配的行。 （8）使用LIKE比较进行查询时，若模式以特定字符串如“abc%”开头进行了索引，使用索引将提高效率。 常查询的关键字列,如产品名称,产地

13 5.1 索引概述 * 5.1.4 设计索引的策略 *2. 索引设计其他策略
上海市高校精品课程 上海市教育高地项目 5.1 索引概述 * 设计索引的策略 *2. 索引设计其他策略 （1）一个表若建有过多索引会影响INSERT、UPDATE和DELETE操作的性能 ； （2）应使用SQL事件探查器和索引优化向导帮助分析查询，确定要创建的索引。 （3）对小型表进行索引可能不会产生优化效果. （4）覆盖的查询可以提高性能。 （5）可以在视图上指定索引。 （6）可以在计算列上指定索引。

14 上海市高校精品课程 上海市教育高地项目 5.1 索引概述 *5.1.4 设计索引的策略 如对数据库表C（货物编号,货物名称,产地,生产企业,型号,颜色,单价,生产时间）的索引设计,如表4-3所示。 货物 列 名 聚集索引 唯一索引 非聚集索引 是否主键 货物编号 √ 货物名称 生产企业 产地代码

15 5.1 索引概述 * 5.1.4 设计索引的策略 *3. 适合索引的特征
上海市高校精品课程 上海市教育高地项目 5.1 索引概述 * 设计索引的策略 *3. 适合索引的特征 在确定某一索引所适合的项查询之后，可以选取最适合具体情况的索引类型特征：聚集还是非聚集,唯一还是普通,单列还是多列组合，索引中的列顺序为升序还是降序，覆盖还是非覆盖等。 还可选取索引的初始存储特征，通过设置填充因子优化其维护，并使用文件和文件组自定义其位置以优化性能。

16 5.1 索引概述 * 5.1.4 设计索引的策略 *4. 索引优化建议 建议考虑以下几个方面：
上海市高校精品课程 上海市教育高地项目 5.1 索引概述 * 设计索引的策略 *4. 索引优化建议 建议考虑以下几个方面： （1）将更新尽可能多的行的查询写入单个语句内，而不用多个查询更新相同的行。仅用一个语句，就可用优化的索引维护。 （2）使用索引优化向导分析查询并获得索引建议。 （3）对聚集索引使用整型键。另外，在唯一列、非空列或 IDENTITY 列上创建聚集索引，可以获得比较好的性能。 （4）在查询经常用到的各列上创建非聚集索引,可最大程度地利用隐蔽查询。 （5）物理创建索引所需时间很大程度上取决于磁盘子系统 。 （6）检查列的唯一性。 （7）在索引列中要注意检查数据的分布情况。 讨论思考 索引的类型有哪些？如何设计？

17 5.2 索引的基本操作 5.2.1 索引的创建及使用 1. 创建索引的方法 ①利用“对象资源管理器”直接创建索引。
上海市高校精品课程 上海市教育高地项目 5.2 索引的基本操作 5.2.1 索引的创建及使用 1. 创建索引的方法 ①利用“对象资源管理器”直接创建索引。 ②利用SQL语句的CREATE INDEX命令创建索引。 语法格式： CREATE [UNIQUE] [CLUSTER∣NONCLUSTER] INDEX <索引名>ON <表名|视图名>(<列名>[<次序>]， [,<列名>[<次序>]]…) 说明：UNIQUE表明建立唯一索引。选CLUSTER表示建立聚集索引，选NONCLUSTER或默认为非聚集索引,“次序”用于指定索引值的排列 顺序,可为ASC（升序)或DESC（降序）,缺省值默认为升序。 索引文件名

18 5.2 索引的基本操作 5.2.1 索引的创建及使用 1. 创建索引的方法 用“对象资源管理器”直接创建索引 上海市高校精品课程
上海市教育高地项目 5.2 索引的基本操作 5.2.1 索引的创建及使用 1. 创建索引的方法 用“对象资源管理器”直接创建索引

19 5.2 索引的基本操作 5.2.1 索引的创建及使用 1. 创建索引的方法 【案例5-2】商品表的属性列商品编号上创建一个（非聚集）索引。
上海市高校精品课程 上海市教育高地项目 5.2 索引的基本操作 5.2.1 索引的创建及使用 1. 创建索引的方法 【案例5-2】商品表的属性列商品编号上创建一个（非聚集）索引。 CREATE INDEX 商品_编号 ON 商品（商品编号SC）；

20 5.2 索引的基本操作 5.2.1 索引的创建及使用 *2. 索引的查看与使用 1）索引的查看
上海市高校精品课程 上海市教育高地项目 5.2 索引的基本操作 5.2.1 索引的创建及使用 *2. 索引的查看与使用 1）索引的查看 （1）用DBCC SHOW_STATISTICS命令查看指定表或视图中特定对象的统计信息。用该命令查看“BooksDateBase”系统中“Books”表中的“BooksBigClass”索引的统计信息，返回结果如右图所示。

21 5.2 索引的基本操作 5.2.1 索引的创建及使用 *2. 索引的查看与使用 （2）用SSMS图形化工具查看统计信息。 上海市高校精品课程
上海市教育高地项目 5.2 索引的基本操作 5.2.1 索引的创建及使用 *2. 索引的查看与使用 （2）用SSMS图形化工具查看统计信息。

22 5.2 索引的基本操作 5.2.1 索引的创建及使用 *2. 索引的查看与使用
上海市高校精品课程 上海市教育高地项目 5.2 索引的基本操作 5.2.1 索引的创建及使用 *2. 索引的查看与使用 （3）使用系统存储过程sp_helpindex查看特定表上的索引信息。 如查看数据库“BookDateBase”中“Books”表的索引信息，可使用如下语句： EXEC SP_HELPINDEX Books

23 5.2 索引的基本操作 5.2.1 索引的创建及使用 *2. 索引的查看与使用 2）查看查询执行计划及索引的比较
上海市高校精品课程 上海市教育高地项目 5.2 索引的基本操作 5.2.1 索引的创建及使用 *2. 索引的查看与使用 2）查看查询执行计划及索引的比较 【案例5-3】查看查询执行计划及索引和未索引的比较。 SELECT 学号, 姓名 FROM 学生表 ORDER BY 姓名 在“查询”菜单上选择“显示估计的执行计划”或按Ctrl+L，可显示此查询的执行计划。 对索引和未索引执行计划的比较，可查看实例。 （1）检验聚集索引 （2）检验非聚集索引

24 5.2 索引的基本操作 5.2.1 索引的创建及使用 *2. 索引的查看与使用 3）聚集索引的使用
上海市高校精品课程 上海市教育高地项目 5.2 索引的基本操作 5.2.1 索引的创建及使用 *2. 索引的查看与使用 3）聚集索引的使用 在聚集索引下，数据在物理上按顺序排在数据页上，重复值也排在一起,因而在那些包含范围检查(between、<、<=、>、>=)或使用group by、order by的查询时，一旦找到具有范围中第一个键值的行，具有后续索引值的行必然连在一起，不必进一步搜索，避免了大范围扫描，可极大地提高查询速度。 聚集索引的候选列是： ① 经常按范围存取的列， ② 经常在where子句中使用并插入随机的主键列； ③ 在group by或order by中使用的列； ④ 在连接操作中使用的列。

25 5.2 索引的基本操作 5.2.1 索引的创建及使用 *2. 索引的查看与使用 4）非聚集索引的使用
上海市高校精品课程 上海市教育高地项目 5.2 索引的基本操作 5.2.1 索引的创建及使用 *2. 索引的查看与使用 4）非聚集索引的使用 聚集索引检索效率较低,且一个表只能建一个聚集索引,当用户需要建立多个索引时就应使用非聚集索引。在建立非聚集索引时，应考虑索引对查询速度的加快与降低修改速度之间的影响。 通常，在下面情况中使用非聚集索引： ① 常用于聚合函数(如Sum,....)的列； ② 常用于join,order by,group by的列； ③ 查询出的数据不超过表中数据量的20%。

26 上海市高校精品课程 上海市教育高地项目 5.2 索引的基本操作 5.2.1 索引的创建及使用 *2. 索引的查看与使用 索引使用情况分析表

27 5.2 索引的基本操作 5.2.1 索引的创建及使用 *2. 索引的查看与使用 5）创建索引应注意的问题 （1) 慎重选择作为聚集索引的列。
上海市高校精品课程 上海市教育高地项目 5.2 索引的基本操作 5.2.1 索引的创建及使用 *2. 索引的查看与使用 5）创建索引应注意的问题 （1) 慎重选择作为聚集索引的列。 【注意】：通常，数据库应用系统进行数据检索都离不开“用户名（代码）”、“货物名称”、“单价”、“生产日期”等常用字段，便于对常用数据进行快速检索。 （2）注重以多列创建的索引中列的顺序。多列索引中列的先后顺序，应当与实际应用中where、group by或order by等子句里列的放置位置相同，检索才能快。  注意

28 5.2 索引的基本操作 5.2.2 索引的更新与删除 *1. 索引的更新 * 在SQL Server的查询分析器中输入命令：
上海市高校精品课程 上海市教育高地项目 5.2 索引的基本操作 5.2.2 索引的更新与删除 *1. 索引的更新 * 在SQL Server的查询分析器中输入命令： use database_name int ('Employee') dbcc showcontig 在命令返回参数中Scan Density是索引性能的关键指示器其值越接近100％越好，通常低于90％时，就应更新（重建）索引。 由于数据更新

29 5.2 索引的基本操作 *1. 索引的更新 5.2.2 索引的更新与删除 命令格式：
上海市高校精品课程 上海市教育高地项目 5.2 索引的基本操作 5.2.2 索引的更新与删除 *1. 索引的更新 命令格式： dbcc dbreindex('表名', 索引名, 填充因子)    /*填充因子一般为90或100*/ 若更新（重建）后，Scan Density还没有达到100%，可更新（重建）该表的所有索引： dbcc dbreindex('表名', ' ' , 填充因子)

30 5.2 索引的基本操作 5.2.2 索引的更新与删除 2. 删除索引 （1）利用对象资源管理器删除索引 （2）利用SQL中的命令删除索引
上海市高校精品课程 上海市教育高地项目 5.2 索引的基本操作 5.2.2 索引的更新与删除 2. 删除索引 （1）利用对象资源管理器删除索引 （2）利用SQL中的命令删除索引 DROP INDEX <索引名> 【案例5-4】删除表商品的索引商品_编号。 DROP INDEX 商品_编号 讨论思考 （1）创建及查看索引的方法有哪几种？ （2）创建索引应注意的问题？ （3）索引的更新和删除如何操作？？

31 5.3 视图及其应用 5.3.1 视图的概念和作用 1. 视图的概念 视图(View)是从基本表/其他视图导出的
上海市高校精品课程 上海市教育高地项目 5.3 视图及其应用 5.3.1 视图的概念和作用 1. 视图的概念 视图(View)是从基本表/其他视图导出的 一种虚表.视图显示的数据来自一个/几个不同的 基表/其他视图,由其中的列和数据行构成。 对视图概念的理解，还包括： （1）视图是查看组织数据的一种方法。 （2）提供存储预定义的查询语句作为DB对象以备后用的功能。 （3）视图只是一种逻辑对象,并非物理对象,不占物理存储空间 （4）在视图中被引用的表称为视图的基表。 （5）视图的内容包括：基表的列或行的子集；多个基表的联合:多个基表的连接；基表的统计汇总；另一视图的子集；视图和基表的混合。

32 5.3 视图及其应用 5.3.1 视图的概念和作用 2. 视图的作用 （1）集中组织调用数据。 （2）提供数据保护。 （3）简化用户操作。
上海市高校精品课程 上海市教育高地项目 5.3 视图及其应用 5.3.1 视图的概念和作用 2. 视图的作用 （1）集中组织调用数据。 （2）提供数据保护。 （3）简化用户操作。 （4）为数据库重构提供一定程度的逻辑独立性。 （5）便于组织数据导出和对数据的管理与传输，视图将数据库设计的复杂性与用户分开，简化用户权限的管理,为向其他应用程序输出重新组织数据。 （6）视图使用户以多种角度看待同一数据。

33 5.3 视图及其应用 5.3.2 视图的种类和特点 1．视图的种类 1）标准视图：用户定义的视图(内容由查询定义).
上海市高校精品课程 上海市教育高地项目 5.3 视图及其应用 5.3.2 视图的种类和特点 1．视图的种类 1）标准视图：用户定义的视图(内容由查询定义). 2）索引视图：具体化的视图,可为此视图建索引. 3）分区视图：在服务器间连接一组表中分区数据. 4）系统视图：公开目录元数据（结构），可返回与SQL实例/实例中定义的对象有关的信息。 筛选 可存储索引-表结构

34 5.3 视图及其应用 5.3.2 视图的种类和特点 2．视图的特点
上海市高校精品课程 上海市教育高地项目 5.3 视图及其应用 5.3.2 视图的种类和特点 2．视图的特点 （1）视图对应于三级模式中的外模式, 是外模式一级数据结构的基本单位,是提供给用户以多角度观察数据的重要机制和形式。 （2）虚表是由基表(实表)/其他视图导出的虚拟表,其本身不存储在数据库中。 （3）视图只存放其定义,而不存放其对应的数据.视图的列可来自不同表,是表的抽象和在逻辑意义上建立的新关系。 （4）创建视图后,可进行检索/删除等操作,也可再定义其他视图.视图建立/删除(结构)不影响基表，但对视图内容的更新(添加、删除和修改)直接影响基表.当视图来自多个基表时,不允许通过视图添加和删除数据。 用户模式如网页 其定义存放在数据字典中 讨论思考 （1）视图的概念是什么？有何作用？ （2）视图的种类有哪些？ （3）视图的特点是什么？

35 5.4 视图的常用操作 5.4.1 视图的创建和策划 1．视图的策划设计及创建 1）视图的策划设计 定义用户局部视图时可以主要考虑：
上海市高校精品课程 上海市教育高地项目 5.4 视图的常用操作 5.4.1 视图的创建和策划 1．视图的策划设计及创建 1）视图的策划设计 定义用户局部视图时可以主要考虑： （1）使用更符合用户习惯的别名。 （2）可对不同级别的用户定义不同的视图，以保证系统的安全性。 （3）简化用户对系统的使用。

36 5.4 视图的常用操作 5.4.1 视图的策划和创建 1．视图的策划设计及创建 功能：创建一个指定的视图。 2）视图的创建方法
上海市高校精品课程 上海市教育高地项目 5.4 视图的常用操作 5.4.1 视图的策划和创建 1．视图的策划设计及创建 2）视图的创建方法 （1）使用“资源对象管理器”创建视图。 （2）使用SQL 命令创建视图。 在SQL语言中，使用语句创建视图语法格式为： CREATE VIEW <视图名>[（<列名1>[，<列名2>]…）] [WITH ENCRYPTION] AS （子查询） [WITH CHECK OPTION] 功能：创建一个指定的视图。 加密 SELECT(…)

37 5.4 视图的常用操作 5.4.1 视图的创建和策划 1．视图的创建及策划设计 在创建视图时还应该注意： 上海市高校精品课程
上海市教育高地项目 5.4 视图的常用操作 5.4.1 视图的创建和策划 1．视图的创建及策划设计 在创建视图时还应该注意： （1）创建视图必须拥有其权限，否则无法进行。 （2）只能在当前数据库中创建视图。 （3）视图名不可与表重名。 （4）视图中列的名称需要所引用的基表的列名一致。 （5）可以将视图创建在其他视图上。 *（6）不应在视图上创建全文索引、规则、默认值和after触发器(特殊存储过程,执行由事件触发),也不能在规则、缺省、触发器的定义中引用视图。 *（7）不能创建临时视图，也不能在临时表上建立视图。 *（8）定义视图的查询语句不能包含COMPUTE或COMPUTE BY子句;不能包含ORDER BY子句，除非在SELECT语句选择列表中也有TOP子句;不能包含INTO关键字;不能引用临时表或表变量。 （9）若视图引用的表被删除,则当使用该视图时将返回一条错误信息,若创建具有相同表结构新表替代已删除的表视图则可使用,否则必须重新创建视图。

38 5.4 视图的常用操作 5.4.1 视图的创建和策划 1．视图的策划设计及创建
上海市高校精品课程 上海市教育高地项目 5.4 视图的常用操作 5.4.1 视图的创建和策划 1．视图的策划设计及创建 【案例5-5】对商品销售数据库中,经常要用到有关商品的信息:商品编号、商品名、价格等数据,请用SQL语句创建商品_价格视图。 CREATE VIEW 商品_价格 AS SELECT (商品编号,商品名，价格) FROM 商品 商品（商品编号，商品名，产地，价格，等级） 售货员（售货员编号，姓名，性别，年龄） 售货（商品编号,售货员编号,数量）

39 5.4 视图的常用操作 5.4.1 视图的创建和策划 1．视图的创建及策划设计
上海市高校精品课程 上海市教育高地项目 5.4 视图的常用操作 5.4.1 视图的创建和策划 1．视图的创建及策划设计 【案例5-6】对商品销售数据库,经常用到有关商品的信息:商品编号、商品名、销售数量等数据,请用SQL语句创建商品_销售量视图。 CREATE VIEW 商品_销售量(商品编号,商品名,销售数量) AS SELECT (商品.商品编号,商品名,COUNT(销售数量)) FROM 商品,售货 WHERE 商品.商品编号=售货.商品编号 GROUP BY 商品编号；

40 5.4 视图的常用操作 5.4.2 视图的重命名、修改及删除 1．视图的重命名及修改
上海市高校精品课程 上海市教育高地项目 5.4 视图的常用操作 5.4.2 视图的重命名、修改及删除 1．视图的重命名及修改 *1）视图重命名 （1）使用 SSMS操作方法 （2）使用 SQL语句操作 视图重命名操作使用的语句为： sp_rename old_name, new_name

41 5.4 视图的常用操作 5.4.2 视图的重命名、修改及删除 1．视图的重命名及修改 2）视图修改 （1）利用菜单操作方法
上海市高校精品课程 上海市教育高地项目 5.4 视图的常用操作 5.4.2 视图的重命名、修改及删除 1．视图的重命名及修改 2）视图修改 （1）利用菜单操作方法 （2）利用SQL语句操作方法 ALTER VIEW <视图名> [WITH ENCRYPTION] AS （子查询） [WITH CHECK OPTION]

42 5.4 视图的常用操作 5.4.2 视图的重命名、修改及删除 2．视图的删除
上海市高校精品课程 上海市教育高地项目 5.4 视图的常用操作 5.4.2 视图的重命名、修改及删除 2．视图的删除 1）使用SSMS删除 2）使用Transact-SQL删除 在SQL语言中，使用DROP VIEW语句删除视图，其语法格式为： DROP VIEW <视图名> 功能为删除指定视图。 【案例5-7】删除商品_销售量视图。 DROP VIEW商品_销售量；

43 5.4 视图的常用操作 *5.4.3 查询视图及有关信息 【案例5-8】查找售出商品键盘的销售量。 SELECT 销售数量
上海市高校精品课程 上海市教育高地项目 5.4 视图的常用操作 *5.4.3 查询视图及有关信息 查询视图 在视图中查询方法： （1）利用SSMS通过菜单查看； （2）查询视图Information_schema.views； （3）查询系统表syscomments； （4）使用命令: SELECT <筛选列组> FROM 视图名 （sp_helptext <对象名>） 【案例5-8】查找售出商品键盘的销售量。 SELECT 销售数量 FROM 商品_销售量 WHERE 商品名='键盘'

44 5.4 视图的常用操作 *5.4.3 查询视图及有关信息 2. 获取有关视图的信息 *1）使用菜单获取视图属性
上海市高校精品课程 上海市教育高地项目 5.4 视图的常用操作 *5.4.3 查询视图及有关信息 2. 获取有关视图的信息 *1）使用菜单获取视图属性 （1）在“对象资源管理器”中，单击包含要查看属性的视图的数据库旁边的加号，然后单击加号以展开“视图”文件夹。 （2）右键单击要查看其属性的视图，然后选择“属性”。 *2）使用视图设计器工具获取视图属性 （1）在“对象资源管理器”中，展开包含要查看属性的视图的数据库，然后展开“视图”文件夹。 （2）右键单击要查看其属性的视图，然后选择“设计”。 （3）右键单击“关系图”窗格中的空白区域，再单击“属性”。 *3） 使用SQL语句方法 EXEC sp_helptext <对象名>

45 5.4 视图的常用操作 5.4.4 利用视图更新数据 1．使用SSMS操作方法 2．用SQL语句方法 （1）通过视图更新表数据的语句
上海市高校精品课程 上海市教育高地项目 5.4 视图的常用操作 5.4.4 利用视图更新数据 1．使用SSMS操作方法 2．用SQL语句方法 （1）通过视图更新表数据的语句 UPDATE <视图名> （2）通过视图插入表数据的语句 INSERT INTO <视图名> Values (对应值列表) （3）通过视图删除表数据的语句 DELETE FROM <视图名> WHERE <条件表达式>

46 5.4 视图的常用操作 5.4.4 利用视图更新数据 上海市高校精品课程 上海市教育高地项目
【案例5-9】 向视图商品_价格插入数据（‘G010’，’服装’，117） INSERT INTO 商品_价格 Values(‘G010’,’服装’,117) 执行时转换成所引用数据表的插入。 【案例5-10】 将视图商品_价格中商品编号为G001的商品价格改为55。 UPDATE 商品_价格 SET 价格 = 55 WHERE 商品编号 =‘G001’ 执行时转换成所引用数据表该记录的修改。 【案例5-11】在视图商品_价格中删除商品编号为G002的商品 DELETE FROM 商品_价格 WHERE商品编号 =‘G002’ 讨论思考 （1）如何创建和策划视图？ （2）视图重命名、修改和删除语句操作是什么? （3）查询视图及有关信息的方法有哪些？ （4）视图更新的具体的方法有哪些种？

47 *5.5 特殊类型视图的应用 *5.5.1 索引视图的概念和创建 1．索引视图的概念及作用 索引视图是指建立唯一聚集索引的视图。
上海市高校精品课程 上海市教育高地项目 *5.5 特殊类型视图的应用 *5.5.1 索引视图的概念和创建 1．索引视图的概念及作用 索引视图是指建立唯一聚集索引的视图。 标准视图是在执行引用了视图的查询时，SQL 才将相关的基本表中的数据合并成视图的逻辑结构。当查询所引用的视图包含大量的数据行或涉及到对大量数据行进行合计运算或连接操作时，动态地创建视图结果集将给系统带来沉重的负担，特别是经常引用大容量视图。 解决方法是为视图创建唯一聚集索引，即在视图上创建唯一聚集索引时生成该视图的结果集，并将结果集数据与有聚集索引的表的数据集一样存储在数据中的。

48 *5.5 特殊类型视图的应用 *5.5.1 索引视图的概念和创建 2. 索引视图的创建 1）创建索引视图的步骤 创建索引视图的步骤如下：
上海市高校精品课程 上海市教育高地项目 *5.5 特殊类型视图的应用 *5.5.1 索引视图的概念和创建 2. 索引视图的创建 1）创建索引视图的步骤 创建索引视图的步骤如下： ①视图中验证将引用的所有现有表的SET选项正确性。 ②在创建任何新表和视图之前，验证会话的SET选项设置。 ③验证视图定义的确定性。 ④用WITH SCHEMABINDING选项创建视图。 ⑤为视图创建唯一的聚集索引。

49 *5.5 特殊类型视图的应用 *5.5.1索引视图的概念和创建 上海市高校精品课程 上海市教育高地项目 2. 索引视图的创建
2）索引视图的SET设置 若执行查询时启用不同的SET选项，则在数据库引擎中对同一表达式求值会产生不同结果。 如，将SET选项CONCAT_NULL_YIELDS_NULL设置为ON，表达式  'abc' + NULL 返回值 NULL。  SET 选项 必需的值 默认服务器值 默认OLE DB和ODBC值 默认DB- Library值 ANSI_NULLS ON OFF ANSI_PADDING ANSI_WARNINGS* ARITHABORT CONCAT_NULL_YIELDS_NULL NUMERIC_ROUNDABORT QUOTED_IDENTIFIER

50 *5.5 特殊类型视图的应用 *5.5.1索引视图的概念和创建 2. 索引视图的创建 上海市高校精品课程 上海市教育高地项目 3）确定性视图
索引视图的定义应是确定性的。 若选择列表中的所有表达式、WHERE 和 GROUP BY 子句都具有确定性，则视图才具有确定性。 在使用特定的输入值集对确定性表达式求值时，应始终返回相同结果。 只有确定性函数可加入确定性表达式。

51 *5.5 特殊类型视图的应用 *5.5.1索引视图的概念和创建 2. 索引视图的创建 上海市高校精品课程 上海市教育高地项目 4）其他要求
除对 SET 选项和确定性函数的要求外，还必须满足要求： ①执行 CREATE INDEX 的用户必须是视图所有者。 ②创建索引时，IGNORE_DUP_KEY 选项必须设置为 OFF（默认设置）。 ③创建表时,基表应有正确的SET选项集,否则具有架构绑定的视图无法引用该表 ④在视图定义中，必须使用两部分名称（即 schema.tablename）引用表。 ⑤必须使用 WITH SCHEMABINDING 选项创建用户定义函数。 ⑥需要使用两部分名称 schema.function 引用用户定义函数。 ⑦应当使用 WITH SCHEMABINDING 选项创建视图。 ⑧视图必须仅引用同一数据库中的基表，而不引用其他视图中的基表。 ⑨视图定义必须包含以下各部分，如表5-8所示。

52 *5.5 特殊类型视图的应用 *5.5.1索引视图的概念和创建 2. 索引视图的创建 5）建议及应用
上海市高校精品课程 上海市教育高地项目 *5.5 特殊类型视图的应用 *5.5.1索引视图的概念和创建 2. 索引视图的创建 5）建议及应用 引用索引视图中的 datetime 和 smalldatetime 字符串文字时，建议使用确定性日期格式将文字显式转换为所需日期类型。将字符串隐式转换为 datetime 或 smalldatetime 所涉及的表达式具有不确定性，结果取决于服务器会话的LANGUAGE和DATEFORMAT设置。

53 *5.5 特殊类型视图的应用 *5.5.1索引视图的概念和创建 2. 索引视图的创建 上海市高校精品课程 上海市教育高地项目
【案例5-12】创建学生选课情况的汇总索引视图。 先进行SET设置，然后由于成绩字段中有NULL，索引视图不允许使用SUM对具有空值的列求和，因此使用ISNULL将空值变为0值。 SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON SET CONCAT_NULL_YIELDS_NULL ON SET NUMERIC_ROUNDABORT OFF SET QUOTED_IDENTIFIER ON SET ARITHABORT ON --SET设置可只进行一次。

54 *5.5 特殊类型视图的应用 *5.5.1索引视图的概念和创建 2. 索引视图的创建 上海市高校精品课程 上海市教育高地项目
USE 教学管理 GO CREATE VIEW V_选课汇总视图 WITH SCHEMABINDING AS SELECT 学号, SUM(ISNULL(成绩,0)) AS 总成绩, COUNT_BIG(*) AS 选修门数 FROM dbo.选课表GROUP BY 学号 CREATE UNIQUE CLUSTERED INDEX 选课表_学号_idx ON 选课汇总视图(学号)

55 *5.5 特殊类型视图的应用 *5.5.2 分区视图及更新数据方法 1．分区视图的概念及用法
上海市高校精品课程 上海市教育高地项目 *5.5 特殊类型视图的应用 *5.5.2 分区视图及更新数据方法 1．分区视图的概念及用法 分区视图是通过对具有相同结构的成员表使用UNION ALL 所定义的视图。 分区视图在一个或多个服务器间水平连接一组成员表中的分区数据，使数据看起来如同来自一个表。

56 *5.5 特殊类型视图的应用 *5.5.2 分区视图及更新数据方法 1．分区视图的概念及用法 上海市高校精品课程 上海市教育高地项目
【案例5-12】 将一个顾客信息 customer 表分区成三个表： On Server1: CREATE TABLE customer_33 (customerid INTEGER PRIMARY KEY CHECK (customerid BETWEEN 1 AND 32999) On Server2: CREATE TABLE customer_66 (customerid INTEGER PRIMARY KEY CHECK (customerid BETWEEN AND 65999) On Server3: CREATE TABLE customer_99 CHECK (customerid BETWEEN AND 99999)

57 *5.5 特殊类型视图的应用 *5.5.2 分区视图及更新数据方法 1．分区视图的概念及用法 上海市高校精品课程 上海市教育高地项目
【案例5-13】 将一个顾客信息 customer 表分区成三个表： 在Server1上为上例创建分布式分区视图。 CREATE VIEW customers AS SELECT * FROM CompanyDatabase.TableOwner.customers_33 UNION ALL Server2.CompanyDatabase.TableOwner.customers_66 Server3.CompanyDatabase.TableOwner.customers_99

58 *5.5 特殊类型视图的应用 *5.5.2 分区视图及更新数据方法 2. 用分区视图更新数据的方法
上海市高校精品课程 上海市教育高地项目 *5.5 特殊类型视图的应用 *5.5.2 分区视图及更新数据方法 2. 用分区视图更新数据的方法 通常，SQL Server更新视图的方法有两种： （1）INSTEAD OF触发器。可在视图上创建INSTEAD OF触发器，修改数据时执行此触发器，但不执行定义触发器的数据修改语句。 （2）分区视图。

59 *5.5 特殊类型视图的应用 *5.5.2 分区视图及更新数据方法 2. 用分区视图更新数据的方法 上海市高校精品课程 上海市教育高地项目
在分区视图上修改数据应满足的条件： ① INSERT语句必须为分区视图中的所有列提供数据，并且不允许在INSERT, UPDATE语句内使用DEFAULT关键字； ② 插入的分区列值应满足基表约束条件； ③ 若分区视图的某个成员包含TIMESTAMP列,则不能用INSERT,UPDATE修改视图; ④ 若一个成员表中包含IDENTITY列，则不能用INSERT语句插入数据，也不能用UPDATE语句修改IDENTITY列，而用UPDATE语句可修改表内其他列； ⑤ 若存在具有同一视图或成员表的自链接，则不能使用INSERT, UPDATE, DELETE语句对成员表进行插入、修改和删除操作； ⑥ 若列中包含TEXT, NTEXT或IMAGE列数据，则不能使用UPDATE语句修改PRIAMARY KEY列。

60 *5.5 特殊类型视图的应用 5.5.2 分区视图及更新数据方法 2. 用分区视图更新数据的方法 上海市高校精品课程 上海市教育高地项目
若视图无INSTEAD OF触发器/不是分区视图,则视图须满足下列条件才可更新: (1) 当视图引用多表时，无法用DELETE命令删除数据，若使用UPDATE，则应与INSERT操作一样，被更新的列必须属于同一个表。 (2) 定义视图的SELECT语句在选择列表中无聚合函数,也不包含TOP, GROUP BY, UNION（除非视图是本主题稍后要描述的分区视图）或DISTINTCT子句。聚合函数可用在 FROM子句的子查询中，只要不修改函数返回的值即可。 (3) 定义视图的SELECT语句的选择列表中没有派生列。派生列是由任何非简单列表达式（使用函数、加法或减法运算符等）所构成的结果集列。 (4)一UPDATE或INSERT语句只修改视图的FROM子句引用的一个基表中的数据 (5) 只有当视图在FROM子句中只引用一个表时，DELETE语句才能引用可更新视图。 讨论思考 （1）什么是索引视图？有什么作用？ （2）如何创建学生选课情况的汇总索引视图？ （3）用分区视图更新数据的方法是什么？

61 上海市高校精品课程 上海市教育高地项目 本章小结 索引是某表中一列或几列值的集合及相应的指向表中物理标识其值的数据页的逻辑指针清单，是加快检索表中数据的方法。在数据库中，索引就是表中数据和相应存储位置的列表。使用索引可以快速查询。本章主要在介绍索引概念、作用、特点、种类基础上，通过大量的典型案例介绍索引的创建、更新及删除等操作方法。同时，介绍了策划设计索引的策略、注意事项和建议。 视图是从基本表或其他视图导出的一种虚表。视图的数据来自一个或几个不同的基表或其他视图。是一种数据库对象，当视图创建后，系统将视图的定义存放在数据字典中，视图对应数据存储在所引用的数据表中。 结合视图的概念、特点和类型等叙述，通过应用案例介绍了视图的创建、重命名、更新、查询及删除等基本操作，以及视图创建前的策划设计和注意问题。在对常用的标准视图介绍的同时,还对特殊类型视图进行了概述。最后，以综合应用案例对视图应用进行了综合实例分析。

62 诚挚谢意！ 上海市高校精品课程 上海市教育高地项目 数据库原理应用与实践