数据库实验四1、创建一个“河北省华信集团”供应商供应的零件视图Viewpart1,要求列出供应商编号、供应商名称、零件编号、零件名称、可用数量、零售价格和供应价格。2、创建一个视图ViewCust1,按顾客统计2020 年订单的购买总金额和零件总数量,要求输出顾客编号、姓名、购买总金额和购买零件总数量。1) 针对刚创建的视图，插入一条记录，看看是否能通过验证，并说明原因2）针对刚创建的视图，查询购买总金额超过10万的顾客编号、姓名、购买总金额和购买零件总数量。 3、创建一个“河北钢铁集团有限公司”供应商供应的零件视图Viewpart2,要求列出供应零件的编号、供应商编号、可用数量和供应价格。1) 然后通过该视图分别增加、删除和修改一条“河北钢铁集团有限公司”零件供应记录。 4、使用with check option 创建一个“河北钢铁集团有限公司”供应商供应的零件视图Viewpart3,要求列出供应零件的编号、供应商编号、可用数量和供应价格。1）然后通过该视图分别增加、删除和修改一条“河北钢铁集团有限公司”零件供应记录，验证该视图是否可更新，并比较和“3”的实验结果有无异同。 5、创建顾客订购零件明细视图Viewcust2,要求列出顾客编号、姓名、购买零件数、金额；1） 然后在该视图的基础上再创建视图Viewcust3,列出每个顾客的平均购买零件数和平均金额，显示顾客编号、姓名、平均购买零件数、平均金额；2） 删除视图Viewcust2。 1、创建一个“河北省华信集团”供应商供应的零件视图Viewpart1,要求列出供应商编号、供应商名称、零件编号、零件名称、可用数量、零售价格和供应价格。 CREATE VIEW Viewpart1 AS SELECT supplier.suppkey,supplier.name AS supplier_name,part.partkey,part.name AS part_name,partsupp.availqty,part.retailprice,partsupp.supplycostFROM supplier,part,partsuppWHERE supplier.name='河北省华信集团' && partsupp.suppkey=supplier.suppkey && part.partkey=partsupp.partkey;SELECT * FROM Viewpart1; 
2、创建一个视图ViewCust1,按顾客统计2020 年订单的购买总金额和零件总数量,要求输出顾客编号、姓名、购买总金额和购买零件总数量。 CREATE VIEW ViewCust1 AS SELECT customer.custkey,customer.name, SUM(orders.totalprice) AS SUM_totalprice,SUM(lineitem.quantity) AS SUM_quantity FROM orders,customer,lineitemWHERE orders.custkey=customer.custkey && orders.orderkey=lineitem.orderkey && orders.orderdate LIKE '2020%' GROUP BY customer.custkey;SELECT * FROM ViewCust1; 
1) 针对刚创建的视图，插入一条记录，看看是否能通过验证，并说明原因 INSERT INTO ViewCust1 VALUES(999999,'河北工业大学',1903.00,2021); The target table ViewCust1 of the INSERT is not insertable-into 无法通过验证，无法执行，原因：视图的字段来自聚集函数，不允许更新
2）针对刚创建的视图，查询购买总金额超过10万的顾客编号、姓名、购买总金额和购买零件总数量。 SELECT * FROM ViewCust1 WHERE SUM_totalprice>100000; 
3、创建一个“河北钢铁集团有限公司”供应商供应的零件视图Viewpart2,要求列出供应零件的编号、供应商编号、可用数量和供应价格。 CREATE VIEW Viewpart2 AS SELECT partkey,suppkey,availqty,supplycost FROM partsupp WHERE suppkey IN (SELECT suppkey FROM supplier WHERE name='河北钢铁集团有限公司');SELECT * FROM Viewpart2; 
1) 然后通过该视图分别增加、删除和修改一条“河北钢铁集团有限公司”零件供应记录。 INSERT INTO Viewpart2 VALUES (999999,666666,195779,1903.00);SELECT * FROM Viewpart2;UPDATE Viewpart2 SET supplycost=2021.00 WHERE partkey=4914;SELECT * FROM Viewpart2;DELETE FROM Viewpart2 WHERE supplycost=2021.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,要求列出供应零件的编号、供应商编号、可用数量和供应价格。 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; 
1）然后通过该视图分别增加、删除和修改一条“河北钢铁集团有限公司”零件供应记录，验证该视图是否可更新，并比较和“3”的实验结果有无异同。 INSERT INTO Viewpart3 VALUES (999999,666666,195779,1903.00);SELECT * FROM Viewpart3;UPDATE Viewpart3 SET supplycost=1900.00 WHERE partkey=4914;SELECT * FROM Viewpart3;DELETE FROM Viewpart3 WHERE supplycost=1900.00;SELECT * FROM Viewpart3; 
依然。。。。。
插入 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,要求列出顾客编号、姓名、购买零件数、金额； CREATE VIEW Viewcust2 AS SELECT customer.custkey,customer.name, SUM(lineitem.quantity) AS SUM_quantity, SUM(orders.totalprice) AS SUM_totalpriceFROM orders,customer,lineitem WHERE customer.custkey=orders.custkey && orders.orderkey=lineitem.orderkey GROUP BY customer.custkey;SELECT * FROM Viewcust2; 
1） 然后在该视图的基础上再创建视图Viewcust3,列出每个顾客的平均购买零件数和平均金额，显示顾客编号、姓名、平均购买零件数、平均金额； CREATE VIEW Viewcust3 AS SELECT Viewcust2.custkey,Viewcust2.name, ( Viewcust2.SUM_totalprice/COUNT(orders.orderkey)) AS avg_price, (Viewcust2.SUM_quantity/COUNT(lineitem.orderkey)) AS avg_quantityFROM Viewcust2,orders,lineitem WHERE Viewcust2.custkey=orders.custkey && orders.orderkey=lineitem.orderkey GROUP BY Viewcust2.custkey;SELECT * FROM Viewcust3; 
2） 删除视图Viewcust2。 DROP VIEW Viewcust2;SELECT * FROM Viewcust3; 
ERROR 1356 (HY000): View ‘test.viewcust3’ references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them Viewcust3依赖于viewcust2创建，当viewcust2被删除后，无法再查询viewcust3