MySQL数据库单元6数据视图笔记

数据视图一、创建和查询视图1.视图概述视图是从一个或多个表或视图中导出的虚拟表。视图是一个虚表即视图所对应的数据不进行实际存储数据库中只存储视图的定义对视图中的数据进行操作时系统根据视图的定义去操作与视图相关联的基本表。视图一经定义就可以像表一样被查询、修改、删除和更新。使用视图有下列优点。为用户集中数据简化用户的数据查询和处理操作。有时用户需要的数据分散在多个表中定义视图可将它们集中在一起从而方便用户查询和处理数据。屏蔽数据库的复杂性。用户不必了解复杂的数据库中的表结构并且数据库表的更改也不影响用户对数据库的使用。简化用户权限的管理。只需授予用户使用视图的权限而不必指定用户只能使用表中的特定列这样也增强了数据的安全性。便于数据共享。同样的数据只需存储一次各用户不必定义和存储已有的数据使用视图可以共享数据库的数据。可以重新组织数据以便输出到其他应用程序中使用。2.创建视图create [or replace] view 视图名 [(列名列表)] as select 语句 [with [cascaded | local] check option]使用视图时要注意下列事项。在默认情况下将在当前数据库中创建新视图。要想在给定数据库中明确创建视图创建时应将其名称指定为 “库名。视图名”。视图的命名必须遵循标识符命名规则不能与表同名。对于每个用户视图名必须是唯一的对于不同用户即使是定义相同的视图也必须使用不同的名字。不能把规则、默认值或触发器与视图相关联。不能在视图上建立任何索引包括全文索引。在视图中使用 select 语句有以下限制。定义视图的用户必须对所参照的表或视图有查询权限即可执行 select 语句的权限在定义中引用的表或视图必须存在。不能包含 from 子句中的子查询不能引用系统变量或用户变量不能引用预处理语句中的参数。在视图定义中允许使用 order by 子句但是如果从一个已经定义了 order by 子句的视图中选择数据时视图定义中的 order by 子句将被忽略。例创建 bookstore 数据库上的 jsj_sell 视图该视图中包括计算机类图书的订单号、图书编号、书名、订购册数要保证对该视图的订单修改符合计算机类这个条件。create or replace view jsj_sell as select 订单号,sell.图书编号,书名,订购册数 from book, sell where book.图书编号 sell.图书编号 and book.图书类别 计算机 with check option;因为订单号、订购册数来自sell表而图书编号、书名来自book表要查询这些信息需要建立多表查询。select 订单号,sell.图书编号,书名,订购册数 from book,sell where book.图书编号 sell.图书编号 and book.图书类别 计算机;例创建 bookstore 数据库中计算机类图书的销售视图 sale_avg其中包括书名在视图中列名为 “name”和该类图书的平均订购册数在视图中列名为 “sale_avg”。create view sale_avg (name, sale_avg) as select 书名,avg(订购册数) from jsj_sell group by 书名;3.查询视图例在视图 jsj_sell 中查找计算机类图书的订单号和订购册数。select 订单号,订购册数 from jsj_sell;例查找平均订购册数大于 5 本的会员的用户号和平均订购册数。 先创建会员平均订购视图 kh_avg其中包括会员的用户号在视图中列名为 “userid”和平均订购册数在视图中列名为 “order_avg”再对 kh_avg 视图进行查询。 创建会员平均订购视图 kh_avg。create view kh_avg (userid, order_avg) as select 用户号, avg(订购册数) from sell group by 用户号;再对 kh_avg 视图进行查询。select * from kh_avg where order_avg 5;注意 进行视图查询时若其关联的基本表中添加了新字段该视图将不包含新字段如果与视图相关联的表或视图被删除则该视图将不能再使用。例如假设视图 ls_sell 中的列关联了 sell 表中的所有列若 sell 表新增了 “送货地址” 字段那么 ls_sell 视图中将查询不到 “送货地址” 字段的数据。二、操作视图1.通过视图操作数据1.可更新视图如果视图包含下述结构中的任何一种那么它就是不可更新的。聚合函数。distinct 关键字。group by 子句。order by 子句。having 子句。union 运算符。位于选择列表中的子查询。from 子句中包含多个表。select 语句中引用了不可更新视图。where 子句中的子查询引用 from 子句中的表。2.插入数据当使用视图插入数据时如果在创建视图时加上了 with check option 子句with check option 子句会在插入数据的时候检查新数据是否符合视图定义中 where 子句的条件。 with check option 子句只能和可更新视图一起使用。例创建视图 jsj_book视图中包含计算机类图书的信息并向 jsj_book 视图中插入一条记录 “tp.0837计算机office 应用实例张怡怡人民邮电出版社2023‑10‑2134.5nullnullnull”。首先创建视图 jsj_book。create or replace view jsj_book as select * from book where 图书类别 计算机 with check option;接下来插入记录。insert into jsj_book values(tp.0837,计算机,office应用实例,张怡怡, 人民邮电出版社,2023‑10‑21,34.5,null,null,null);3.修改数据使用 update 语句可以实现通过视图修改基本表中的数据。例将 jsj_book 视图中的所有图书的单价降低 5%。update jsj_book set 单价 单价*(1‑0.05);该语句实际上是将 jsj_book 视图所依赖的基本表 book 中所有计算机类图书的单价降低 5%。若一个视图依赖多个基本表则修改一次该视图只能改变一个基本表中的数据。例将 jsj_sell 视图中图书编号为 “tp.2525” 的图书的书名改为 “php 网站制作”将订单号为 5 的订单的订购册数改为 100。改书名update jsj_sell set 书名 php网站制作 where 图书编号 tp.2525;改订购册数update jsj_sell set 订购册数 100 where 订单号 5;本例中视图 jsj_sell 依赖 book 和 sell 两个基本表对 jsj_sell 视图的一次修改只能改变一个基本表中的数据即书名源于 book 表或订购册数源于 sell 表。所以以下修改是错误的。update jsj_sell set 书名 php网站制作,订购册数 100 where 订单号 5 and 图书编号 tp.2525;4.删除数据如果视图数据来源于单个基本表可以使用 delete 语句通过视图删除基本表中的数据。例删除 jsj_book 中 “人民邮电出版社” 的记录。delete from jsj_book where 出版社 人民邮电出版社;对依赖多个基本表的视图不能使用 delete 语句。例如不能通过对 jsj_sell 视图执行 delete 语句删除与之相关联的基本表 book 和 sell 中的数据。2.修改视图定义使用 alter view 语句可以对已有视图的定义进行修改。alter view 视图名 [(列名列表)] as select 语句 [with [cascaded | local] check option]alter view 语句的语法和 create view 的类似这里不再赘述。例将 jsj_book 视图修改为只包含计算机类图书的图书编号、书名和单价。alter view jsj_book as select 图书编号,书名,单价 from book where 图书类别 计算机;3.删除视图drop view [if exists] 视图名1 [,视图名2] ...如果声明了 if exists就算视图不存在也不会出现错误信息。使用 drop view 可以一次删除多个视图示例如下。drop view jsj_book, jsj_sell;上面的语句一次删除了视图 jsj_book 和 jsj_sell。