单元6 数据视图

1、单元六单元六数据视图项目知识要点与目标项目知识要点与目标项目知识要点项目知识要点知识能力目标知识能力目标学时学时6.1 6.1 任务任务1 1 创建视图创建视图 6.1.1 6.1.1 视图概念视图概念 5.1.2 5.1.2 创建视图创建视图 5.1.3 5.1.3 查询视图查询视图 6.2 6.2 任务任务2 2 操作视图操作视图 6.2.1 6.2.1 通过视图操作数据图通过视图操作数据图 6.2.2 6.2.2 修改视图定义修改视图定义 6.2.3 6.2.3 删除视图删除视图 6.36.3综合实例综合实例 PetStorePetStore数据视图数据视图 【情境情境】：Petstor

2、ePetstore数据视图数据视图【职业岗位职业岗位】：操作员、程序员：操作员、程序员【典型工作任务典型工作任务】：创建视图、操：创建视图、操作视图作视图【技能要求技能要求】：1 1、使用、使用MySQLMySQL命令创建和操作视图命令创建和操作视图 2 2、使用图形工具创建和操作视图、使用图形工具创建和操作视图【知识要求知识要求】：1 1、视图的作用与使用、视图的作用与使用2 2、修改及删除视图的、修改及删除视图的SQLSQL语句语句 2 2项目实训六项目实训六“员工管理系统员工管理系统”数据视图操作数据视图操作2 2基本表基本表 视图是数据库的用户使用数据库的观点。例如，对于一个学校，其学

3、视图是数据库的用户使用数据库的观点。例如，对于一个学校，其学生的情况存于数据库的一个或多个表中，而作为学校的不同职能部门，所生的情况存于数据库的一个或多个表中，而作为学校的不同职能部门，所关心的学生数据的内容是不同的。即使是同样的数据，也可能有不同的操关心的学生数据的内容是不同的。即使是同样的数据，也可能有不同的操作要求，于是就可以根据他们的不同需求，在物理的数据库上定义他们对作要求，于是就可以根据他们的不同需求，在物理的数据库上定义他们对数据库所要求的数据结构，这种根据用户观点所定义的数据结构就是视图。数据库所要求的数据结构，这种根据用户观点所定义的数据结构就是视图。 基本表基本表用户一视图

4、基本表基本表用户二视图优点优点视图与表（有时为与视图区别，也称表为基本表视图与表（有时为与视图区别，也称表为基本表Base TableBase Table）不）不同，视图是一个虚表，即视图所对应的数据不进行实际存储，数据库中只同，视图是一个虚表，即视图所对应的数据不进行实际存储，数据库中只存储视图的定义，对视图的数据进行操作时，系统根据视图的定义去操作存储视图的定义，对视图的数据进行操作时，系统根据视图的定义去操作与视图相关联的基本表。与视图相关联的基本表。视图是从一个或多个表（或视图）导出的表。视图一经定义以后，就视图是从一个或多个表（或视图）导出的表。视图一经定义以后，就可以像表一样被查询

5、、修改、删除和更新。使用视图有下列优点：可以像表一样被查询、修改、删除和更新。使用视图有下列优点：（1 1）为用户集中数据，简化用户的数据查询和处理。有时用户所需）为用户集中数据，简化用户的数据查询和处理。有时用户所需要的数据分散在多个表中，定义视图可将它们集中在一起，从而方便用户要的数据分散在多个表中，定义视图可将它们集中在一起，从而方便用户的数据查询和处理。的数据查询和处理。（2 2）屏蔽数据库的复杂性。用户不必了解复杂的数据库中的表结构，）屏蔽数据库的复杂性。用户不必了解复杂的数据库中的表结构，并且数据库表的更改也不影响用户对数据库的使用。并且数据库表的更改也不影响用户对数据库的使用。（

6、3 3）简化用户权限的管理。只需授予用户使用视图的权限，而不必）简化用户权限的管理。只需授予用户使用视图的权限，而不必指定用户只能使用表的特定列，也增加了安全性。指定用户只能使用表的特定列，也增加了安全性。（4 4）便于数据共享。各用户不必都定义和存储自己所需的数据，可）便于数据共享。各用户不必都定义和存储自己所需的数据，可共享数据库的数据，这样同样的数据只需存储一次。共享数据库的数据，这样同样的数据只需存储一次。（5 5）可以重新组织数据以便输出到其他应用程序中。）可以重新组织数据以便输出到其他应用程序中。MySQL MySQL 在在5.05.0版中已经实现了视图功能（包括可更新视图）。版中

7、已经实现了视图功能（包括可更新视图）。1. 1. 使用使用CREATE VIEWCREATE VIEW语句创建视图语句创建视图语法格式：语法格式： CREATE OR REPLACE VIEW 视图名视图名 (列名列表列名列表) AS select语句语句 WITH CASCADED | LOCAL CHECK OPTION 列列名列表名列表：要想为视图的列定义明确的名称，可使用可选的列名列：要想为视图的列定义明确的名称，可使用可选的列名列表子句，列出由逗号隔开的列名。列名列表中的名称数目必须等于表子句，列出由逗号隔开的列名。列名列表中的名称数目必须等于SELECTSELECT语句检索的列数。

8、若使用与源表或视图中相同的列名时可以省语句检索的列数。若使用与源表或视图中相同的列名时可以省略列名列表。略列名列表。 WITH CHECK OPTIONWITH CHECK OPTION：指出在可更新视图上所进行的修改都要符合：指出在可更新视图上所进行的修改都要符合selectselect语句所指定的限制条件，这样可以确保数据修改后，仍可通过语句所指定的限制条件，这样可以确保数据修改后，仍可通过视图看到修改的数据。视图看到修改的数据。创建视图举例创建视图举例【例例6.16.1】创建创建BookstoreBookstore数据库上的数据库上的jsj_selljsj_sell视图，包括计算机类图视

9、图，包括计算机类图书的销售的订单号，图书编号，书名，订购册数等情况。要保证对该书的销售的订单号，图书编号，书名，订购册数等情况。要保证对该视图的订单修改都要符合计算机类这个条件。视图的订单修改都要符合计算机类这个条件。 CREATE OR REPLACE VIEW jsj_sell CREATE OR REPLACE VIEW jsj_sell AS AS SELECT SELECT 订单号订单号,Sell.,Sell.图书编号图书编号, ,书名书名, ,订购册数订购册数 FROM Book, Sell FROM Book, Sell WHERE Book. WHERE Book.图书编号图书

10、编号=Sell. =Sell. 图书编号图书编号 AND Book. AND Book.图书类别图书类别= = 计算机计算机 WITH CHECK OPTION; WITH CHECK OPTION;【例例6.26.2】 创建创建BookstoreBookstore数据库中计算机类图书销售视图数据库中计算机类图书销售视图sale_AVGsale_AVG，包括书名（在视图中列名为包括书名（在视图中列名为namename）和该图书的平均订购册数（在视图中）和该图书的平均订购册数（在视图中列名为列名为sale_avgsale_avg）。）。 CREATE VIEW sale_avg (name, s

11、ale_avg)CREATE VIEW sale_avg (name, sale_avg) AS AS SELECT SELECT 书名书名,avg(,avg(订购册数订购册数) ) FROM jsj_sell FROM jsj_sell GROUP BY GROUP BY书名书名; ;例例6.16.1已经创建了计算机类图书销售视图已经创建了计算机类图书销售视图jsj_selljsj_sell，这里可以直接从，这里可以直接从jsj_selljsj_sell视图中查询信息生成新视图。视图中查询信息生成新视图。1 1、创建视图、创建视图Emp_view1,Emp_view1,包含所有男员工的员工号

12、码、姓名、工作包含所有男员工的员工号码、姓名、工作年限和学历，字段名用中文表示年限和学历，字段名用中文表示2 2、从、从Emp_view1Emp_view1查询工作年限在两年以上的员工信息。查询工作年限在两年以上的员工信息。3 3、创建创建视图、创建创建视图Emp_view 2Emp_view 2、包含员工号码、姓名、所在部门名、包含员工号码、姓名、所在部门名称和收入称和收入4 4、从、从Emp_view2Emp_view2查询研发部的员工号码、姓名和收入。查询研发部的员工号码、姓名和收入。任务二任务二 操作视图操作视图 1. 1. 可更新视图可更新视图要通过视图更新基本表数据，必须保证视图是

13、可更新视图，即可以在要通过视图更新基本表数据，必须保证视图是可更新视图，即可以在INSETINSET、UPDATEUPDATE或或DELETEDELETE等语句当中使用它们。对于可更新的视图，在视图中的行和基表等语句当中使用它们。对于可更新的视图，在视图中的行和基表中的行之间必须具有一对一的关系。还有一些特定的其他结构，这类结构会使得中的行之间必须具有一对一的关系。还有一些特定的其他结构，这类结构会使得视图不可更新。如果视图包含下述结构中的任何一种，那么它就是不可更新的：视图不可更新。如果视图包含下述结构中的任何一种，那么它就是不可更新的： （1 1）聚合函数；）聚合函数；（2 2）DISTI

14、NCTDISTINCT关键字；关键字；（3 3）GROUP BYGROUP BY子句；子句；（4 4）ORDER BYORDER BY子句；子句；（5 5）HAVINGHAVING子句；子句；（6 6）UNIONUNION运算符；运算符；（7 7）位于选择列表中的子查询；）位于选择列表中的子查询；（8 8）FROMFROM子句中包含多个表；子句中包含多个表；（9 9）SELECTSELECT语句中引用了不可更新视图；语句中引用了不可更新视图；通过视图插入数据通过视图插入数据2. 2. 插入数据插入数据使用使用INSERTINSERT语句通过视图向基本表插入数据语句通过视图向基本表插入数据【例例

15、6.56.5】 创建视图创建视图jsj_bookjsj_book，视图中包含计算机类图书的信息，并向，视图中包含计算机类图书的信息，并向jsj_bookjsj_book视图中插入一条记录：（视图中插入一条记录：（ISBN7-165-12683-7ISBN7-165-12683-7， 计算机计算机 ，OfficeOffice应用实例应用实例 ， 张瑜海张瑜海, , 人民邮电出版社人民邮电出版社 ，2011-10-212011-10-21，34.534.5，NULLNULL，NULLNULL，NULLNULL）。）。首先创建视图首先创建视图jsj_bookjsj_book： CREATE OR R

16、EPLACE VIEW CREATE OR REPLACE VIEW jsj_bookjsj_book AS AS SELECT SELECT * * FROM Book FROM Book WHERE WHERE 图书类别图书类别 = = 计算机计算机 WITH CHECK OPTION; WITH CHECK OPTION;接下来插入记录：接下来插入记录：INSERT INTO INSERT INTO jsj_bookjsj_book VALUES(ISBN7-165-12683-7, VALUES(ISBN7-165-12683-7,计算机计算机,Office,Office应用实例应用实

17、例, 张瑜海张瑜海,人民邮电出版社人民邮电出版社,2011-10-21,34.5,NULL,NULL,NULL,2011-10-21,34.5,NULL,NULL,NULL ); );通过视图修改数据通过视图修改数据当视图所依赖的基本表有多个时，不能向该视图插入数据，因为当视图所依赖的基本表有多个时，不能向该视图插入数据，因为这将会影响多个基本表。这将会影响多个基本表。对对INSERTINSERT语句还有一个限制：语句还有一个限制：SELECTSELECT语句中必须包含语句中必须包含FROMFROM子句中子句中指定表的所有不能为空的列。指定表的所有不能为空的列。3. 3. 修改数据修改数据使用

18、使用UPDATEUPDATE语句可以通过视图修改基本表的数据语句可以通过视图修改基本表的数据【例例6.66.6】 将将jsj_bookjsj_book视图中所有单价降低视图中所有单价降低% %。 UPDATE UPDATE jsj_bookjsj_book SET SET 单价单价 = = 单价单价* *(1-0.05);(1-0.05);该语句实际上是将该语句实际上是将jsj_bookjsj_book视图所依赖的基本表视图所依赖的基本表BookBook中所有计算机类中所有计算机类图书的单价也都降低了图书的单价也都降低了5%5%。若一个视图依赖于多个基本表，则一次修改该视图只能变动一个基若一个

19、视图依赖于多个基本表，则一次修改该视图只能变动一个基本表的数据。本表的数据。【例例6.76.7】 将将jsj_selljsj_sell视图中图书编号为视图中图书编号为ISBN 8-5006-6625-XISBN 8-5006-6625-X的书名改的书名改为为“ASP.NETASP.NET网站制作网站制作”，订单号为，订单号为6 6的订购册数改为的订购册数改为8 8。 UPDATE UPDATE jsj_selljsj_sell SET SET 书名书名=ASP.NET=ASP.NET网站制作网站制作 WHERE WHERE 图书编号图书编号=ISBN 8-5006-6625-X;=ISBN 8

20、-5006-6625-X;和和 UPDATE UPDATE jsj_selljsj_sell SET SET 订购册数订购册数=8=8 WHERE WHERE 订单号订单号=6;=6;视图修改数据举例视图修改数据举例通过视图删除数据通过视图删除数据4. 4. 删除数据删除数据使用使用DELETEDELETE语句可以通过视图删除基本表的数据语句可以通过视图删除基本表的数据【例例6.86.8】 删除删除jsj_bookjsj_book中中“中国青年出版社中国青年出版社”的记录。的记录。 DELETE FROM DELETE FROM jsj_bookjsj_book WHERE WHERE 出版社

21、出版社 = =中国青年出版社中国青年出版社;对依赖于多个基本表的视图，不能使用对依赖于多个基本表的视图，不能使用DELETEDELETE语句。例如，不能通语句。例如，不能通过对过对jsj_selljsj_sell视图执行视图执行DELETEDELETE语句而删除与之相关的基本表语句而删除与之相关的基本表BookBook及及SellSell表的数据。表的数据。1 1、创建视图、创建视图Emp_view3,Emp_view3,包含所有工作年限包含所有工作年限2 2年以上的员工的员工号年以上的员工的员工号码、姓名、学历、出生日期、性别和工作年限，所在部门编号。在创建码、姓名、学历、出生日期、性别和工作年限，所在部门编号。在创建视图的时候加上视图的时候加上WITH CHECK OPTIONWITH CHECK OPTION子句子句2 2、从、从Emp_view3Emp_view3插入一条记录：插入一条记录：（041110,041110,钟晓玲钟晓玲, ,博士博士,1973-12-01,1973-12-01,男男,3,4),3,4)。3 3、 修改修改Emp_view2Emp_view2中将中将“李丽李丽”的收入加的收入加200200元元4 4、删除视图、删除视图Emp_view3Emp_view3中中“本科本科”学历的员工学历的