河北联合大学2010SQLSERVER课件4.视图_索引_游标
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
wk.baidu.com
分区视图在一台或多台服务器间水 平连接一组成员表中的分区数据。 平连接一组成员表中的分区数据。这 数据看上去如同来自于一个表。 样，数据看上去如同来自于一个表。 联接同一个SQL Server实例中的成 联接同一个SQL Server实例中的成 员表的视图是一个本地分区视图。 员表的视图是一个本地分区视图。
9.1 视图 9.1.4 查询视图 视图创建完毕， 视图创建完毕，就可以如同查询基本表一样查询视图 可以在SQL Studio中选 了。可以在SQL Server Management Studio中选 中要查询的视图并打开， 中要查询的视图并打开，浏览该视图查询的所有数 也可以在查询窗口中执行T SQL语句查询视图 语句查询视图。 据。也可以在查询窗口中执行T-SQL语句查询视图。 【例9-3】查询学生_课程_分数视图，统计“数据库 查询学生_课程_分数视图，统计“ 应用”课程的总分和平均分。 应用”课程的总分和平均分。
9.1 视图 9.1.6 修改视图定义 修改视图定义，与修改基本表结构不一样。 修改视图定义，与修改基本表结构不一样。修改基本 表结构是指重新定义列名、属性、约束等， 表结构是指重新定义列名、属性、约束等，而修改 视图定义是指修改视图的指定列的列名、别名、 视图定义是指修改视图的指定列的列名、别名、表 是否输出、顺序类型等属性。 名、是否输出、顺序类型等属性。 T-SQL提供了视图创建语句ALTER VIEW。其语法格 SQL提供了视图创建语句 提供了视图创建语句ALTER VIEW。 式如下： 式如下：
第9章 视图、索引和游标 视图、
数据库的基本表是按照数据库设计人员的观点设 计的，并不一定符合用户的需求。 计的，并不一定符合用户的需求。SQL Server 2005可以根据用户需求重新定义表的数据结构 可以根据用户需求重新定义表的数据结构， 2005可以根据用户需求重新定义表的数据结构，这 种数据结构就是视图。 种数据结构就是视图。 在数据库系统中， 在数据库系统中，为了迅速地从庞大的数据库中 找到所需要的数据， 2005提供了类似 找到所需要的数据，SQL Server 2005提供了类似 书的目录作用的索引技术。 书的目录作用的索引技术。通过对数据库中表设置 索引，可以大大加快数据的检索速度。 索引，可以大大加快数据的检索速度。 2005还提供了一种称为游标的机制 还提供了一种称为游标的机制， SQL Server 2005还提供了一种称为游标的机制， 来处理数据。 来处理数据。
9.1 视图 9.1.3 创建视图,应考虑如下准则： 创建视图,应考虑如下准则： 1) 只能在当前数据库中创建视图。 只能在当前数据库中创建视图。 2) 视图名称必须遵循标识符的规则，且对每个架构都必 视图名称必须遵循标识符的规则， 须惟一。 须惟一。 3) 用户可以对其他视图创建视图。 用户可以对其他视图创建视图。 5) 不能将AFTER触发器与视图相关联，只有INSTEAD 不能将AFTER触发器与视图相关联 只有INSTEAD 触发器与视图相关联， OF触发器可以与之相关联 OF触发器可以与之相关联。 触发器可以与之相关联。 6) 定义视图的查询不能包含COMPUTE子句、 定义视图的查询不能包含COMPUTE子句 子句、 COMPUTE BY子句或INTO关键字。 BY子句或 子句或INTO关键字 关键字。 7) 定义视图的查询不能包含ORDER BY子句，除非在 定义视图的查询不能包含ORDER BY子句 子句， SELECT语句的选择列表中还有一个 SELECT语句的选择列表中还有一个TOP子句。 语句的选择列表中还有一个TOP子句 子句。 8) 定义视图的查询不能包含指定查询提示的OPTION子 定义视图的查询不能包含指定查询提示的OPTION子 句。 9) 定义视图的查询不能包含TABLESAMPLE子句。 定义视图的查询不能包含TABLESAMPLE子句 子句。
【例9-1】创建学生_课程_分数视图，包括计算 创建学生_课程_分数视图， 机学院的学生的学号、姓名， 机学院的学生的学号、姓名，和他们选修的课 程号、课程名、分数。 程号、课程名、分数。 CREATE VIEW 学生_课程_分数 学生_课程_ AS SELECT 学生表.学号,姓名,课程表.课程号,课程 学生表.学号,姓名,课程表.课程号, 名,分数 FROM 学生表,课程表,成绩表 学生表,课程表, WHERE 学生表.学号=成绩表.学号 学生表.学号=成绩表. AND 课程表.课程号=成绩表.课程号 课程表.课程号=成绩表. AND 院系名称='计算机学院' 院系名称='计算机学院 计算机学院' GO
1. 视图的优点,主要表现在： 视图的优点,主要表现在： 1) 为用户集中数据，简化用户的数据查 为用户集中数据， 询和处理。使得分散在多个表中的数据， 询和处理。使得分散在多个表中的数据， 通过视图定义在一起。 通过视图定义在一起。 2) 简化操作，屏蔽了数据库的复杂性。 简化操作，屏蔽了数据库的复杂性。 3) 重新定制数据，使得数据便于共享。 重新定制数据，使得数据便于共享。 3) 合并分割数据，有利于数据输出到应 合并分割数据， 用程序中。 用程序中。 4) 简化了用户权限的管理，增加了安全 简化了用户权限的管理， 性。
SELECT 总分=SUM(分数),平均分=AVG(分数) 总分=SUM(分数 平均分 分数),平均分=AVG(分数 分数) FROM 学生_课程_分数 学生_课程_ WHERE 课程名='数据库应用' 课程名='数据库应用 数据库应用' GO
【例9-4】查询姓“张”的职工进 查询姓“ 货的零件名称。 货的零件名称。 SELECT 零件名称 FROM 进货视图 WHERE 姓名LIKE '张%' 姓名LIKE '张 GO
【例9-2】创建进货视图，包括职工的姓名 创建进货视图， 和零件名称。 和零件名称。 CREATE VIEW 进货视图 AS SELECT 姓名,零件名称 姓名, FROM 工作人员表,库存零件表,出货单表 工作人员表,库存零件表, WHERE 工作人员表.人员编号=出货单表. 工作人员表.人员编号=出货单表.人员编号 AND 库存零件表.零件编号=出货单表.零件 库存零件表.零件编号=出货单表. 编号 GO
9.1 视图
在SQL Server 2005中创建标准视图主要有两种方式：一种 2005中创建标准视图主要有两种方式 中创建标准视图主要有两种方式： 方式是在SQL 方式是在SQL Server Management Studio中使用向导创 Studio中使用向导创 建视图，另一种方式是通过在查询窗口中执行T SQL语句创 建视图，另一种方式是通过在查询窗口中执行T-SQL语句创 建视图。 SQL提供了视图创建语句 建视图。T-SQL提供了视图创建语句CREATE VIEW。其语 提供了视图创建语句CREATE VIEW。 法格式如下： 法格式如下：
9.1 视图 10) 不能为视图定义全文索引定义。 不能为视图定义全文索引定义。 11) 不能创建临时视图，也不能对临时表创建视图。 不能创建临时视图，也不能对临时表创建视图。 12) 不能删除参与到使用SCHEMABINDING子句创 不能删除参与到使用SCHEMABINDING子句创 建的视图中的视图、表或函数， 建的视图中的视图、表或函数，除非该视图已被删 除或更改而不再具有架构绑定。另外， 除或更改而不再具有架构绑定。另外，如果对参与 具有架构绑定的视图的表执行ALTER TABLE语句 语句， 具有架构绑定的视图的表执行ALTER TABLE语句， 而这些语句又会影响该视图的定义， 而这些语句又会影响该视图的定义，则这些语句将 会失败。 会失败。 13) 下列情况下必须指定视图中每列的名称： 下列情况下必须指定视图中每列的名称： 视图中的任何列都是从算术表达式、 ① 视图中的任何列都是从算术表达式、内置函数或常 量派生而来。 量派生而来。 视图中有两列或多列原应具有相同名称。 ② 视图中有两列或多列原应具有相同名称。 希望为视图中的列指定一个与其源列不同的名称。 ③ 希望为视图中的列指定一个与其源列不同的名称。
3) 被修改的列不受GROUP BY、 被修改的列不受GROUP BY、 HAVING、DISTINCT或TOP子句的影响。 HAVING、DISTINCT或TOP子句的影响。 子句的影响
即使是可更新视图，也不能随意更新数据。 即使是可更新视图，也不能随意更新数据。 如果视图所依赖的基本表有多个时， 如果视图所依赖的基本表有多个时，不能向 该视图添加数据，因为这将影响多个基本表。 该视图添加数据，因为这将影响多个基本表。 修改数据时，若视图依赖于多个基本表， 修改数据时，若视图依赖于多个基本表， 那么一次修改只能修改一个基本表中的数据。 那么一次修改只能修改一个基本表中的数据。 删除数据时，若视图依赖于多个基本表， 删除数据时，若视图依赖于多个基本表， 那么不能通过视图删除数据。 那么不能通过视图删除数据。
9.1 视图 9.1.5 更新视图 通过更新视图数据（包括添加、修改和删除） 通过更新视图数据（包括添加、修改和删除）可以修改 基本表数据。 基本表数据。只有对满足可更新条件的视图才能进行 更新。 更新。 只要满足下列条件，即可通过视图修改基础基表的数据： 只要满足下列条件，即可通过视图修改基础基表的数据： 1) 任何修改（包括UPDATE、INSERT和 任何修改（包括UPDATE、INSERT和
9.1 视图
2. 视图的使用范围 视图通常用来集中、 视图通常用来集中、简化和自定义每个 用户对数据库的不同 认识。通常在以下情况下使用视图： 认识。通常在以下情况下使用视图： 1)着重于特定数据 1)着重于特定数据。 着重于特定数据。 2) 简化数据操作。 简化数据操作。 3) 自定义数据。 自定义数据。 4) 数据的导入与导出。 数据的导入与导出。 5) 跨服务器组合分区数据库
第9章 视图、索引和游标 视图、
9.1 视图 9.2 索引 9.3 游标
9.1 视图
9.1.1 视图概述 视图是从一个或多个表或视图中导出的表， 视图是从一个或多个表或视图中导出的表， 其结构和数据是建立在对表的查询基础 上的。 上的。 视图不是真实存在的基础表而是一张虚表， 视图不是真实存在的基础表而是一张虚表， 视图所对应的数据并不实际地以视图结 构存储在数据库中， 构存储在数据库中，而是存储在视图所 引用的表中。 引用的表中。
9.1 视图 9.1.2 视图的类型 2005中 在SQL Server 2005中，视图可以分为
标准视图、索引视图和分区视图。 标准视图、索引视图和分区视图。
标准视图组合了一个或多个表中的数据，用户可以 标准视图组合了一个或多个表中的数据， 使用标准视图对数据库进行查询、修改、 使用标准视图对数据库进行查询、修改、删除等基 本操作。 本操作。 索引视图是被具体化了的视图， 索引视图是被具体化了的视图，即它已经过计算并 存储。可以为视图创建索引， 存储。可以为视图创建索引，即对视图创建一个惟 一的聚集索引。 一的聚集索引。索引视图可以显著提高某些类型查 询的性能。索引视图尤其适于聚合许多行的查询。 询的性能。索引视图尤其适于聚合许多行的查询。 但它们不太适于经常更新的基本数据集 不太适于经常更新的基本数据集。 但它们不太适于经常更新的基本数据集。
CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ] [ WITH <view_attribute> [ ,...n ] ] AS select_statement [ ; ] [ WITH CHECK OPTION ]
9.1 视图
DELETE语句） 只能引用一个基表的列。 DELETE语句）都只能引用一个基表的列。 语句 2) 视图中被修改的列必须直接引用表列中的基础 视图中被修改的列必须直接引用表列中的基础 数据。 数据。不能通过任何其他方式对这些列进行派 如通过聚合函数、计算（如表达式计算）、 生，如通过聚合函数、计算（如表达式计算）、 集合运算等。 集合运算等。
分区视图在一台或多台服务器间水 平连接一组成员表中的分区数据。 平连接一组成员表中的分区数据。这 数据看上去如同来自于一个表。 样，数据看上去如同来自于一个表。 联接同一个SQL Server实例中的成 联接同一个SQL Server实例中的成 员表的视图是一个本地分区视图。 员表的视图是一个本地分区视图。
9.1 视图 9.1.4 查询视图 视图创建完毕， 视图创建完毕，就可以如同查询基本表一样查询视图 可以在SQL Studio中选 了。可以在SQL Server Management Studio中选 中要查询的视图并打开， 中要查询的视图并打开，浏览该视图查询的所有数 也可以在查询窗口中执行T SQL语句查询视图 语句查询视图。 据。也可以在查询窗口中执行T-SQL语句查询视图。 【例9-3】查询学生_课程_分数视图，统计“数据库 查询学生_课程_分数视图，统计“ 应用”课程的总分和平均分。 应用”课程的总分和平均分。
9.1 视图 9.1.6 修改视图定义 修改视图定义，与修改基本表结构不一样。 修改视图定义，与修改基本表结构不一样。修改基本 表结构是指重新定义列名、属性、约束等， 表结构是指重新定义列名、属性、约束等，而修改 视图定义是指修改视图的指定列的列名、别名、 视图定义是指修改视图的指定列的列名、别名、表 是否输出、顺序类型等属性。 名、是否输出、顺序类型等属性。 T-SQL提供了视图创建语句ALTER VIEW。其语法格 SQL提供了视图创建语句 提供了视图创建语句ALTER VIEW。 式如下： 式如下：
第9章 视图、索引和游标 视图、
数据库的基本表是按照数据库设计人员的观点设 计的，并不一定符合用户的需求。 计的，并不一定符合用户的需求。SQL Server 2005可以根据用户需求重新定义表的数据结构 可以根据用户需求重新定义表的数据结构， 2005可以根据用户需求重新定义表的数据结构，这 种数据结构就是视图。 种数据结构就是视图。 在数据库系统中， 在数据库系统中，为了迅速地从庞大的数据库中 找到所需要的数据， 2005提供了类似 找到所需要的数据，SQL Server 2005提供了类似 书的目录作用的索引技术。 书的目录作用的索引技术。通过对数据库中表设置 索引，可以大大加快数据的检索速度。 索引，可以大大加快数据的检索速度。 2005还提供了一种称为游标的机制 还提供了一种称为游标的机制， SQL Server 2005还提供了一种称为游标的机制， 来处理数据。 来处理数据。
9.1 视图 9.1.3 创建视图,应考虑如下准则： 创建视图,应考虑如下准则： 1) 只能在当前数据库中创建视图。 只能在当前数据库中创建视图。 2) 视图名称必须遵循标识符的规则，且对每个架构都必 视图名称必须遵循标识符的规则， 须惟一。 须惟一。 3) 用户可以对其他视图创建视图。 用户可以对其他视图创建视图。 5) 不能将AFTER触发器与视图相关联，只有INSTEAD 不能将AFTER触发器与视图相关联 只有INSTEAD 触发器与视图相关联， OF触发器可以与之相关联 OF触发器可以与之相关联。 触发器可以与之相关联。 6) 定义视图的查询不能包含COMPUTE子句、 定义视图的查询不能包含COMPUTE子句 子句、 COMPUTE BY子句或INTO关键字。 BY子句或 子句或INTO关键字 关键字。 7) 定义视图的查询不能包含ORDER BY子句，除非在 定义视图的查询不能包含ORDER BY子句 子句， SELECT语句的选择列表中还有一个 SELECT语句的选择列表中还有一个TOP子句。 语句的选择列表中还有一个TOP子句 子句。 8) 定义视图的查询不能包含指定查询提示的OPTION子 定义视图的查询不能包含指定查询提示的OPTION子 句。 9) 定义视图的查询不能包含TABLESAMPLE子句。 定义视图的查询不能包含TABLESAMPLE子句 子句。
【例9-1】创建学生_课程_分数视图，包括计算 创建学生_课程_分数视图， 机学院的学生的学号、姓名， 机学院的学生的学号、姓名，和他们选修的课 程号、课程名、分数。 程号、课程名、分数。 CREATE VIEW 学生_课程_分数 学生_课程_ AS SELECT 学生表.学号,姓名,课程表.课程号,课程 学生表.学号,姓名,课程表.课程号, 名,分数 FROM 学生表,课程表,成绩表 学生表,课程表, WHERE 学生表.学号=成绩表.学号 学生表.学号=成绩表. AND 课程表.课程号=成绩表.课程号 课程表.课程号=成绩表. AND 院系名称='计算机学院' 院系名称='计算机学院 计算机学院' GO
1. 视图的优点,主要表现在： 视图的优点,主要表现在： 1) 为用户集中数据，简化用户的数据查 为用户集中数据， 询和处理。使得分散在多个表中的数据， 询和处理。使得分散在多个表中的数据， 通过视图定义在一起。 通过视图定义在一起。 2) 简化操作，屏蔽了数据库的复杂性。 简化操作，屏蔽了数据库的复杂性。 3) 重新定制数据，使得数据便于共享。 重新定制数据，使得数据便于共享。 3) 合并分割数据，有利于数据输出到应 合并分割数据， 用程序中。 用程序中。 4) 简化了用户权限的管理，增加了安全 简化了用户权限的管理， 性。
SELECT 总分=SUM(分数),平均分=AVG(分数) 总分=SUM(分数 平均分 分数),平均分=AVG(分数 分数) FROM 学生_课程_分数 学生_课程_ WHERE 课程名='数据库应用' 课程名='数据库应用 数据库应用' GO
【例9-4】查询姓“张”的职工进 查询姓“ 货的零件名称。 货的零件名称。 SELECT 零件名称 FROM 进货视图 WHERE 姓名LIKE '张%' 姓名LIKE '张 GO
【例9-2】创建进货视图，包括职工的姓名 创建进货视图， 和零件名称。 和零件名称。 CREATE VIEW 进货视图 AS SELECT 姓名,零件名称 姓名, FROM 工作人员表,库存零件表,出货单表 工作人员表,库存零件表, WHERE 工作人员表.人员编号=出货单表. 工作人员表.人员编号=出货单表.人员编号 AND 库存零件表.零件编号=出货单表.零件 库存零件表.零件编号=出货单表. 编号 GO
9.1 视图
在SQL Server 2005中创建标准视图主要有两种方式：一种 2005中创建标准视图主要有两种方式 中创建标准视图主要有两种方式： 方式是在SQL 方式是在SQL Server Management Studio中使用向导创 Studio中使用向导创 建视图，另一种方式是通过在查询窗口中执行T SQL语句创 建视图，另一种方式是通过在查询窗口中执行T-SQL语句创 建视图。 SQL提供了视图创建语句 建视图。T-SQL提供了视图创建语句CREATE VIEW。其语 提供了视图创建语句CREATE VIEW。 法格式如下： 法格式如下：
9.1 视图 10) 不能为视图定义全文索引定义。 不能为视图定义全文索引定义。 11) 不能创建临时视图，也不能对临时表创建视图。 不能创建临时视图，也不能对临时表创建视图。 12) 不能删除参与到使用SCHEMABINDING子句创 不能删除参与到使用SCHEMABINDING子句创 建的视图中的视图、表或函数， 建的视图中的视图、表或函数，除非该视图已被删 除或更改而不再具有架构绑定。另外， 除或更改而不再具有架构绑定。另外，如果对参与 具有架构绑定的视图的表执行ALTER TABLE语句 语句， 具有架构绑定的视图的表执行ALTER TABLE语句， 而这些语句又会影响该视图的定义， 而这些语句又会影响该视图的定义，则这些语句将 会失败。 会失败。 13) 下列情况下必须指定视图中每列的名称： 下列情况下必须指定视图中每列的名称： 视图中的任何列都是从算术表达式、 ① 视图中的任何列都是从算术表达式、内置函数或常 量派生而来。 量派生而来。 视图中有两列或多列原应具有相同名称。 ② 视图中有两列或多列原应具有相同名称。 希望为视图中的列指定一个与其源列不同的名称。 ③ 希望为视图中的列指定一个与其源列不同的名称。
3) 被修改的列不受GROUP BY、 被修改的列不受GROUP BY、 HAVING、DISTINCT或TOP子句的影响。 HAVING、DISTINCT或TOP子句的影响。 子句的影响
即使是可更新视图，也不能随意更新数据。 即使是可更新视图，也不能随意更新数据。 如果视图所依赖的基本表有多个时， 如果视图所依赖的基本表有多个时，不能向 该视图添加数据，因为这将影响多个基本表。 该视图添加数据，因为这将影响多个基本表。 修改数据时，若视图依赖于多个基本表， 修改数据时，若视图依赖于多个基本表， 那么一次修改只能修改一个基本表中的数据。 那么一次修改只能修改一个基本表中的数据。 删除数据时，若视图依赖于多个基本表， 删除数据时，若视图依赖于多个基本表， 那么不能通过视图删除数据。 那么不能通过视图删除数据。
9.1 视图 9.1.5 更新视图 通过更新视图数据（包括添加、修改和删除） 通过更新视图数据（包括添加、修改和删除）可以修改 基本表数据。 基本表数据。只有对满足可更新条件的视图才能进行 更新。 更新。 只要满足下列条件，即可通过视图修改基础基表的数据： 只要满足下列条件，即可通过视图修改基础基表的数据： 1) 任何修改（包括UPDATE、INSERT和 任何修改（包括UPDATE、INSERT和
9.1 视图
2. 视图的使用范围 视图通常用来集中、 视图通常用来集中、简化和自定义每个 用户对数据库的不同 认识。通常在以下情况下使用视图： 认识。通常在以下情况下使用视图： 1)着重于特定数据 1)着重于特定数据。 着重于特定数据。 2) 简化数据操作。 简化数据操作。 3) 自定义数据。 自定义数据。 4) 数据的导入与导出。 数据的导入与导出。 5) 跨服务器组合分区数据库
第9章 视图、索引和游标 视图、
9.1 视图 9.2 索引 9.3 游标
9.1 视图
9.1.1 视图概述 视图是从一个或多个表或视图中导出的表， 视图是从一个或多个表或视图中导出的表， 其结构和数据是建立在对表的查询基础 上的。 上的。 视图不是真实存在的基础表而是一张虚表， 视图不是真实存在的基础表而是一张虚表， 视图所对应的数据并不实际地以视图结 构存储在数据库中， 构存储在数据库中，而是存储在视图所 引用的表中。 引用的表中。
9.1 视图 9.1.2 视图的类型 2005中 在SQL Server 2005中，视图可以分为
标准视图、索引视图和分区视图。 标准视图、索引视图和分区视图。
标准视图组合了一个或多个表中的数据，用户可以 标准视图组合了一个或多个表中的数据， 使用标准视图对数据库进行查询、修改、 使用标准视图对数据库进行查询、修改、删除等基 本操作。 本操作。 索引视图是被具体化了的视图， 索引视图是被具体化了的视图，即它已经过计算并 存储。可以为视图创建索引， 存储。可以为视图创建索引，即对视图创建一个惟 一的聚集索引。 一的聚集索引。索引视图可以显著提高某些类型查 询的性能。索引视图尤其适于聚合许多行的查询。 询的性能。索引视图尤其适于聚合许多行的查询。 但它们不太适于经常更新的基本数据集 不太适于经常更新的基本数据集。 但它们不太适于经常更新的基本数据集。
CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ] [ WITH <view_attribute> [ ,...n ] ] AS select_statement [ ; ] [ WITH CHECK OPTION ]
9.1 视图
DELETE语句） 只能引用一个基表的列。 DELETE语句）都只能引用一个基表的列。 语句 2) 视图中被修改的列必须直接引用表列中的基础 视图中被修改的列必须直接引用表列中的基础 数据。 数据。不能通过任何其他方式对这些列进行派 如通过聚合函数、计算（如表达式计算）、 生，如通过聚合函数、计算（如表达式计算）、 集合运算等。 集合运算等。