MySQL数据库-实验四 视图实验

一、实验目的

        熟悉 SQL 语言有关视图的操作，能够熟练使用 SQL 语句来创建需要的视图，定义数据库外模式，并能使用所创建的视图实现数据管理。

二、实验内容和要求

        针对自建数据库 test 创建视图和带 with check option 的视图，并验证 with checkoption 选项的有效性。理解和掌握视图消解执行原理，掌握可更新视图和不可更新视图的区别。

三、实验步骤

（1）创建一个“河北省华信集团”供应商供应的零件视图 Viewpart1,要求列出供应商

编号、供应商名称、零件编号、零件名称、可用数量、零售价格和供应价格。

create view Viewpart1 as

select supplier.suppkey,supplier.name as name1,part.partkey,part.name as name2,partsupp.availqty,part.retailprice,partsupp.supplycost

from supplier,part,partsupp

where supplier.name='河北省华信集团' and partsupp.suppkey=supplier.suppkey and part.partkey=partsupp.partkey;

（2）创建一个视图 ViewCust1,按顾客统计 2020 年订单的购买总金额和零件总数量，

要求输出顾客编号、姓名、购买总金额和购买零件总数量。

create view ViewCust1

as select cus.custkey,cus.name,sum(ord.totalprice)  sum_totalprice,lin.quantity

from customer cus,orders ord ,lineitem lin

where cus.custkey = ord.custkey and ord.orderkey = lin.orderkey and ord.orderdate like '2023%'

group by cus.custkey;

select  *from ViewCust1;

1）针对刚创建的视图，插入一条记录，看看是否能通过验证，并说明原因。

insert into ViewCust1 values (0,'程一一',0,0);  

不能执行原因：视图字段来自聚集函数，不能进行插入操作

2）针对刚创建的视图，查询购买总金额超过 10 万的顾客编号、姓名、购买总和

购买零件总数量。

select  cus.custkey,cus.name,ord.totalprice,lin.quantity

from customer cus , orders ord ,lineitem lin

where totalprice >100000;

（3）创建一个“河北钢铁集团有限公司”供应商供应的零件视图 Viewpart2,要求列出供应零件的编号、供应商编号、可用数量和供应价格。然后通过该视图分别增加、删除和修改一条“河北钢铁集团有限公司”零件供应记录。

create  view Viewpart2

as select *
from partsupp where suppkey in (select suppkey from  supplier WHERE name='河北钢铁集团有限公司');

SELECT * FROM Viewpart2;


insert into Viewpart2 values(0,753,20,105.00,null);

select *from Viewpart2;



UPDATE Viewpart2 SET supplycost=2023.00 WHERE partkey=4914;

SELECT * FROM Viewpart2;



DELETE FROM Viewpart2 WHERE supplycost=2023.00;

SELECT * FROM Viewpart2;

partkey和suppkey各自做外键：

Cannot add or update a child row: a foreign key constraint fails (test.partsupp, CONSTRAINT FK_partkey_in_partsupp FOREIGN KEY (partkey) REFERENCES part (partkey) ON DELETE RESTRICT ON UPDATE RESTRICT)不允许插入

partkey和suppkey联合被lineitem表做外键：

Cannot delete or update a parent row: a foreign key constraint fails (test.lineitem, CONSTRAINT FK_UNION FOREIGN KEY (partkey, suppkey) REFERENCES partsupp (partkey, suppkey))

不允许删除，可以更改supplycost。

（4）使用 with check option 创建一个“河北钢铁集团有限公司”供应商供应的零件

视图 Viewpart3,要求列出供应零件的编号、供应商编号、可用数量和供应价格。

然后通过该视图分别增加、删除和修改一条“河北钢铁集团有限公司”零件供应记录，

验证该视图是否可更新，并比较和“（3）”的实验结果有无异同。

CREATE VIEW Viewpart3 AS SELECT partkey,suppkey,availqty,supplycost

FROM partsupp WHERE suppkey

 IN(SELECT suppkey FROM supplier WHERE name='河北钢铁集团有限公司')

  WITH CHECK OPTION;



SELECT * FROM Viewpart3;



insert into Viewpart3 values(0,753,20,105.00,null);

select *from Viewpart3;

UPDATE Viewpart3 SET supplycost=1000 WHERE partkey=4914;

SELECT * FROM Viewpart3;



DELETE FROM Viewpart3 WHERE supplycost=1000;

SELECT * FROM Viewpart3;

和实验（3）的结果是一样的，插入和删除操作是不允许的但是允许更新其中的数据：

插入不允许：

ERROR 1369 (HY000): CHECK OPTION failed ‘test.viewpart3’

删除不允许：

Cannot delete or update a parent row: a foreign key constraint fails (test.lineitem, CONSTRAINT FK_UNION FOREIGN KEY (partkey, suppkey) REFERENCES partsupp (partkey, suppkey)

（5）创建顾客订购零件明细视图 Viewcust2,要求列出顾客编号、姓名、购买零件数、

金额。

1）然后在该视图的基础上再创建视图 Viewcust3,列出每个顾客的平均购买零件数和

平均金额，显示顾客编号、姓名、平均购买零件数、平均金额；

CREATE VIEW Viewpart3 AS SELECT partkey,suppkey,availqty,supplycost

FROM partsupp WHERE suppkey

 IN(SELECT suppkey FROM supplier WHERE name='河北钢铁集团有限公司')

  WITH CHECK OPTION;



SELECT * FROM Viewpart3;



insert into Viewpart3 values(0,753,20,105.00,null);

select *from Viewpart3;

UPDATE Viewpart3 SET supplycost=1000 WHERE partkey=4914;

SELECT * FROM Viewpart3;



DELETE FROM Viewpart3 WHERE supplycost=1000;

SELECT * FROM Viewpart3;

2）删除视图 Viewcust2。

DROP VIEW Viewcust2;

SELECT * FROM Viewcust3;

Viewcust3依赖于viewcust2创建，当viewcust2被删除后，无法再查询viewcust3