第五讲 利用视图进行查询优化.

Presentation on theme: "第五讲 利用视图进行查询优化."— Presentation transcript:

1 第五讲 利用视图进行查询优化

2 第五讲 利用视图进行查询优化

3 学习情境引入 在前几讲中，我们针对商品采购管理系统相关查询功能进行了讲解。 为了简化数据查询操作和保护数据安全，数据库提供了视图工具。
数据库中视图是一个虚拟表，其内容由查询定义。 同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在；行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。

4 基本表1 基本表2 视图 视图结构示意图

5 从用户角度来看，一个视图是从一个特定的角度 来查看数据库中的数据。
从数据库系统内部来看，一个视图是由SELECT语句组成的查询定义的虚拟表。 从数据库系统外部来看，视图就如同一张表一样，对表能够进行的一般操作都可以应用于视图，例如查询，插入，修改，删除操作等。

6 视图是存储在数据库中的查询的SQL 语句，它主要出于两种原因：
安全原因： 视图可以隐藏一些数据，如：社会保险基金表，可以用视图只显示姓名，地址，而不显示社会保险号和工资数等； 另一原因：可使复杂的查询易于理解和使用。

7 视图的作用如下： 　* 简单性。看到的就是需要的。视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。 　* 安全性。通过视图用户只能查询和修改他们所能见到的数据。数据库中的其它数据则既看不见也取不到。数据库授权命令可以使每个用户对数据库的检索限制到特定的数据库对象上，但不能授权到数据库特定行和特定的列上。 　* 逻辑数据独立性。视图可帮助用户屏蔽真实表结构变化带来的影响。

8 视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。视图实际上就是给查询语句指定一个名字，将查询语句定义为一个独立的对象保存。

9 视图是个虚表，是从一个或者多个表或视图中导出的表，其结构和数据是建立在对表的查询基础上的。
它表示一张表的部分数据或多张表的综合数据，其结构和数据是建立在对表的查询基础上 视图中并不存放数据，而是存放在视图所引用的原始表（基表）中同一张原始表，根据不同用户的不同需求，可以创建不同的视图。

10 视图（View）：是由SELECT语句定义的用于集中、简化和定制显示数据库中数据的数据库对象。
1. 视图与表的关系： 视图（除索引视图）并不存储任何数据 2. 视图的数据源 一个基表（视图）中行的子集——水平视图 一个基表（视图）中列的子集——投影视图 多个基表（视图）连接操作的结果集——连接视图 基表（视图）进行运算汇总的结果集——计算视图

11 视图举例 商品信息表 商品类别表 商品单位表 视图的创建 教员讲笑话，活跃气氛：
大家都知道，我们国家现在“神六”上天了。从美国的月球登月开始，人类上天不再是神话。 听说，在美国，你只要出几十万美元，您就可以上一次月球进行太空旅行，所以，我们相信： 在不久的将来，上天旅行将走进我们百姓的生活，那是一件多么新鲜神奇的事情啊。 好多美国人上天后，他们用望远镜观看我们的地球，结果看到的地球形状各不相同。 有的说象绿色的橘子  ，有的说象白色的大鸭梨，有的说象蓝色的大西瓜。 提问：为什么呢？ 大多学员肯定会说：那是因为从不同的角度（视角），看到的不同形状而也。 鼓励学员：大家真聪明，确实如此。 既然同一个地球，从不同视角看到不同的结果。 从不同的视角，将看到不同的“图形”，这就是我们马上讲到的视图。 我们的学员信息也是如此：对于学员的信息和成绩 而教员比较关心学员成绩以及是否参加考试，包括姓名、学号、笔试、机试、是否通过等。 班主任比较关心学生档案，包括姓名、学号、性别和年龄， 我们来看看幻灯片所示的例子。

12 演示使用T-SQL管理平台创建视图 【视图dbo-View1】标签页 显示条件窗格 显示SQL窗格 显示结果窗格 显示关系图窗格
参见学生用书：第五章5.5.2节 如何创建视图 部分。

13 视图的创建和应用 在SQL Server 2005中一般用以下两种方法创建视图： （1）用SQL SERVER管理平台创建视图；
（2）用Transact-SQL语句中的CREATE VIEW命令创建视图 创建视图时应该注意以下情况： （1）只能在当前数据库中创建视图，在视图中最多只能引用1024列，视图中记录的数目限制只由其基表中的记录数决定。 （2）如果视图引用的基表或者视图被删除，则该视图不能再被使用，直到创建新的基表或者视图。 （3）如果视图中某一列是函数、数学表达式、常量或者来自多个表的列名相同，则必须为列定义名称。

14 利用SQL SERVER管理平台创建视图 视图的创建和应用
（1）在SQL SERVER管理平台中，展开指定的服务器，打开要创建视图的数据库文件夹，选择指定的数据库，右击该数据库图标，从弹出的快捷菜单中依次选择“新建（New）→视图”选项，如图示。接着就出现添加表、视图、函数对话框。

15 创建视图的基本语法如下： CREATE VIEW <视图名> [(<视图列名表>)] [WITH ENCRYPTION] AS Select_语句 其中：WITH ENCRYPTION子句对视图进行加密。 此命令必须是GO指令后的第一条！

16 使用T-SQL语句创建视图 CREATE VIEW 视图名 AS <select语句> 任务实施
在数据库中，根据商品信息表（goods）创建一个只包含商品类别号为“P001”和“P002”视图，视图名为View_GoodsInfo_GoodsClass。视图包含的列为商品编号、商品类别号、商品名称和价格。

17 IF EXISTS (SELECT * FROM sysobjects
WHERE name = 'View_GoodsInfo_GoodsClass ') DROP VIEW View_GoodsInfo_GoodsClass GO CREATE VIEW View_GoodsInfo_GoodsClass AS SELECT goods_id 商品编号, classification_id 商品类别号, goods_name 商品名称,unit_price 价格 FROM goods WHERE classification_id =‘P001' OR classification_id =‘P002' 检测视图是否存在 删除视图 创建命令 创 建 命 令 AS关键字不能少 视图名 创建视图 Select语句 告诉学员： 说明：从一个或者多个表或视图中导出的虚拟表，其结构和数据是建立在对表的查询基础上的。 理论上它可以像普通的物理表一样使用，例如增、删、改、查等，修改视图中的数据实际上是修改 原始数据表。因为修改视图有许多限制，所以在实际开发中一般视图仅做查询使用 使用视图

18 创建有限制的视图操作 任务实施 在数据库中，根据商品信息表（goods）创建一个只包含商品类别号为“P001”和“P002”视图，视图名为View_GoodsInfo_GoodsClass2。视图包含的列为商品编号、商品类别号、商品名称和价格，列名为中文别名。 并要求通过该视图进行的更新操作只涉及商品类别号为“SPLB01”和“SPLB03”的商品信息。 CREATE VIEW View_GoodsInfo_GoodsClass2 AS SELECT goods_id 商品编号, classification_id 商品类别号, goods_name 商品名称,unit_price 价格 FROM goods WHERE classification_id =‘P001' OR classification_id =‘P002' WITH CHECK OPTION

19 基于多个基本表的视图 CREATE VIEW View_3 AS
任务实施 在employee表和department表中创建一视图名为View_3，其视图列名列表中含有的列为：部门号、部门名称、部门人员名称。 CREATE VIEW View_3 AS SELECT employee.department_id,department_name,employee_name FROM employee,department WHERE employee.department_id=department.department_id SELECT * FROM View_3

20 建立统计分组视图 CREATE VIEW View_PurchaseCount AS
任务实施 对于数据库的的商品表（goods），创建一个查询每种商品的进货数量的视图，视图名为View_ PurchaseCount。 CREATE VIEW View_PurchaseCount AS SELECT classification_id 商品编号,SUM(stock_quantity) 进货数量 FROM goods GROUP BY classification_id 必须给列SUM()指定列名

21 视图的创建和应用 修改视图 （1）在SQL SERVER管理平台中，右击要修改的视图，从弹出的快捷菜单中选择“设计视图”选项，出现视图修改对话框。该对话框与创建视图时的对话框相同，可以按照创建视图的方法修改视图。 （2）使用ALTER VIEW语句修改视图，但首先必须拥有使用视图的权限，然后才能使用ALTER VIEW语句，该语句的语法形式如下： ALTER VIEW view_name [（column[,...n]）] [WITH ENCRYPTION] AS select_statement [WITH CHECK OPTION ]

22 视图的创建和应用 任务实施 对View_GoodsInfo_GoodsClass 视图 进行修改。
alter view View_GoodsInfo_GoodsClass as SELECT GoodsID 商品编号,ClassID 商品类别号 FROM GoodsInfo WHERE ClassID='SPLB01' OR ClassID='SPLB03‘

23 视图的创建和应用 重命名视图 （1）在SQL SERVER管理平台中，选择要修改名称的视图，并右击该视图，从弹出的快捷菜单中选择“重命名”选项。或者在视图上再次单击，也可以修改视图的名称。接着该视图的名称变成可输入状态，可以直接输入新的视图名称。 　（2）使用系统存储过程sp_rename来修改视图的名称，该过程的语法形式如下： exec sp_rename old_name,new_name

24 对View_GoodsInfo_GoodsClass 视图 进行重命名。
视图的创建和应用 对View_GoodsInfo_GoodsClass 视图 进行重命名。 exec sp_rename View_GoodsInfo_GoodsClass , View3‘ 注意： SP_rename 后 紧跟 旧名称，后面是新名称 任务实施

25 对于不再使用的视图，可以使用SQL SERVER管理平台或者Transact-SQL语句中的DROP VIEW命令删除它。
删除视图 对于不再使用的视图，可以使用SQL SERVER管理平台或者Transact-SQL语句中的DROP VIEW命令删除它。 使用Transact-SQL语句DROP VIEW删除视图，其语法形式如下： DROP VIEW {view_name} [,…n] 可以使用该命令同时删除多个视图，只需在要删除的各视图名称之间用逗号隔开即可。

26 使用视图操作数据表 除了在SELECT中使用视图作为数据源进行查询以外，还可以通过视图对数据表的数据进行添加、修改和删除的操作。
使用视图对数据表的记录进行操作时，所创建的视图必须满足的要求： 1) 修改视图中的数据时，不能同时修改两个或者多个基本表，当对基于两个或多个表创建的视图进行修改时，每次的修改只能影响一张基本表。 2) 视图的字段中不能包含计算列，计算列是不能更新的。

27 使用视图操作数据表 3) 如果在创建视图时指定了WITH CHECK OPTION选项，那么使用视图修改数据库时，必须保证修改后的数据满足视图定义的要求。 4) 如果在视图定义中使用了GROUP BY、UNION、DISTINCT或TOP子句，则视图不允许更新。 5) 如果在视图定义中有嵌套查询，并且内层查询的FROM子句涉及的表也是导出该视图的基本表，则视图不允许更新。

28 INSERT INTO department_view VALUES(‘G004’,’总经理室’)
通过视图插入数据 1.插入(INSERT) 条件：视图来自单表，且非空项均需在视图中。 【案例】创建视图department_view CREATE VIEW department_view AS SELECT department_id, department_name FROM department 【案例】 向视图department_view中插入一个部门(‘ G004 ’，‘总经理室’)。 INSERT INTO department_view VALUES(‘G004’,’总经理室’)

29 通过视图更新数据 2. 修改(UPDATE)和删除(DELETE) update department_view
set department_name=‘董事长室' where department_id='G004'Go Delete from department_view where department_id='G004'

30 视图可以屏蔽数据的复杂性，简化用户对数据库的操作,还可以使用视图重新组织数据。
总 结 　　　视图可以屏蔽数据的复杂性，简化用户对数据库的操作,还可以使用视图重新组织数据。 　　　视图可以让不同的用户以不同的方式看到不同或者相同的数据集。 　　　视图可以定制不同用户对数据的访问权限。 　　　因此，视图是在数据库系统开始时进行查询优化的常用方法。