USB迷 | 专注于互联网分享

    最新消息： USBMI致力于为网友们分享Windows、安卓、IOS等主流手机系统相关的资讯以及评测、同时提供相关教程、应用、软件下载等服务。
    你的位置： 首页 > 业界 > 数据库实验四：视图

    数据库实验四：视图

    业界 admin 7浏览 0评论

    数据库实验四

        • 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.supplycost 
 FROM supplier,part,partsupp 
  WHERE 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,lineitem
   WHERE 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_totalprice 
  FROM 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_quantity
    FROM 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

    数据库实验四

        • 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.supplycost 
 FROM supplier,part,partsupp 
  WHERE 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,lineitem
   WHERE 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_totalprice 
  FROM 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_quantity
    FROM 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

    继续浏览有关 的文章

    与本文相关的文章

    发布评论

    评论列表 (0)

    1. 暂无评论