实验08 视图操作

实验目的

了解创建视图的目的和意义；学会使用DMSQL方式完成数据库视图的创建、修改、删除等操作，并通过视图查询数据，更新数据。

实验要求

（1） 使用DMSQL方式创建、查看视图；
（2） 通过视图更新数据；
（3） 使用DMSQL方式修改视图；
（4） 使用DMSQL方式删除视图；

实验内容

注：以下每个题目完成后，请通过“SELECT * FROM <视图名称>”进行查看。

创建一个视图名称为View_计算机商店，其数据包含有“计算机”或“电脑”的商店名称、负责人和客服电话。

create view View_计算机商店
as 
select 商店名称 ,负责人,客服电话
from 卖家表,商品表
where 卖家表.商店编号=商品表.店铺编号
and 商品名称 like '%计算机%' or 商品名称 like '%电脑%'

修改视图“View_计算机商店”，增加“地址”字段。

create or replace view View_计算机商店
as 
select 商店名称 ,负责人,客服电话,地址
from 卖家表,商品表
where 卖家表.商店编号=商品表.店铺编号
and 商品名称 like '%计算机%' or 商品名称 like '%电脑%'

对视图“View_计算机商店”插入一行数据“华海电脑,王东升,13976924921，南宝电脑城238” ，并对实验结果进行分析。

Insert into view_计算机商店
Values（‘华海电脑’,‘王东升’,‘13976924921’，‘南宝电脑城238’）
实验结果分析：
不能执行insert语句是因为视图所依赖的基本表有多个，即卖家表和商品表，如果插入新的数据，会对多个表产生影响

创建一个视图名称为View_李姓客户，其数据包含李姓客户编号、客户姓名、电话、性别，显示为客户编号、客户姓名、客户电话、客户性别。要求使用WITH CHECK OPTION短语，并通过视图把客户姓名“李亮”改成“李亮亮”，“李彦君”改成“张彦君”,对操作结果进行分析。

create view view_李姓客户
as
select 客户编号,姓名 客户姓名,电话 客户电话,性别 客户性别
from 客户表
where 姓名 like '李%'
with check option
 
update  view_李姓客户
set 客户姓名='李亮亮'
where 客户姓名='李亮'
 
update  view_李姓客户
set 客户姓名='张彦君'
where 客户姓名='李彦君'

实验结果分析：
With check option语句使得在视图增删改数据时，关系数据库管理系统会检查视图定义中的条件，若不满足条件则拒绝执行该操作

对视图“View_李姓客户”分别插入下面两行数据：990123,王永乐,13645210245,男；990312,李小妹,13876024569,女。请分析实验结果，并对比插入前后的数据变化。

insert 
into view_李姓客户
values('990123','王永乐','13645210245','男')
 
insert 
into view_李姓客户
values('990312','李小妹','13876024569','女')

实验结果分析：
With check option语句使得在视图增删改数据时，关系数据库管理系统会检查视图定义中的条件，若不满足条件则拒绝执行该操作

通过视图“View_客户列表”，将龙一禾的记录删除。是否成功，为什么？

create view View_客户列表
as 
select *
from 客户表
delete
from View_客户列表
where 姓名='龙一禾'
 
实验结果分析：
成功，因为能转换为对基本表的更新 delete from 客户表 where 姓名=‘龙一禾’

创建一个视图名称为View_无订单客户，包含没有订单的客户姓名、电话、地址。

create view view_无订单客户
as 
select distinct 姓名,电话,地址
from 客户表
where 客户编号 not in
(select 客户编号
from 购买记录表)

删除视图“View_无订单客户”。

drop view view_无订单客户

创建一个视图，把客户、商店、商品、订单四张表连接到一起，输出字段包含每张表中必要的字段。

create view view_总
as 
select 姓名,性别,出生日期,电话,客户表.地址 客户地址,商店名称,负责人,客服电话,卖家表.地址 店铺地址,商品名称,单价,订单时间,数量,折扣率,成交时间
from 客户表,商品表,卖家表,购买记录表
where 客户表.客户编号=购买记录表.客户编号
and 卖家表.商店编号=商品表.店铺编号
and 商品表.商品编号=购买记录表.商品编号

在上一题的基础上通过视频查询既购买了“计算机”又购买了“打印机”的客户姓名，查询方法不限。

select 姓名
from view_总
where 商品名称='计算机' 
intersect
select 姓名
from view_总
where 商品名称='打印机'

思考与总结

不能通过视图更新数据的情况有哪些？

（1）若视图的字段是来自字段表达式或常数，则不允许对此视图执行INSERT、UPDATE操作，允许执行DELETE操作；
（2）若视图的字段是来自库函数，则此视图不允许更新；
（3）若视图的定义中有GROUP BY子句或聚集函数时，则此视图不允许更新；
（4）若视图的定义中有DISTINCT任选项，则此视图不允许更新；
（5）若视图的定义中有嵌套查询，并且嵌套查询的FROM子句中涉及的表也是导出该视图的基表，则此视图不允许更新；
（6）若视图是由两个以上的基表导出的，此视图不允许更新；
（7）一个不允许更新的视图上定义的视图也不允许更新；
（8）由一个基表定义的视图，只含有基表的主键或候补键，并且视图中没有用表达式或函数定义的属性，才允许更新。

创建视图时，使用WITH CHECK OPTION短语主要解决什么问题？

with check option 子句可以保证只能在视图的条件之内对视图进行操作