发布时间 : 星期一 文章《数据库系统原理》实验报告更新完毕开始阅读1f1142d8376baf1ffd4fadbb
FROM Customer C,Orders O,Lineitem L,PartSupp PS,Part P WHERE C.custkey=O.custkey AND O.orderkey=L.orderkey AND L.suppkey=PS.suppkey AND L.partkey=PS.partkey AND PS.partkey=P.partkey AND C.name='李四'; INTERSECTION SELECT P.*
FROM Customer C,Orders O,Lineitem L,PartSupp PS,Part P WHERE C.cuskey=O.custkey AND O.orderkey=L.orderkey AND L.partkey=PS.partkey AND L.suppkey=PS.suppkey AND PS.partkey=P.partkey AND C.name='李四'; （6）集合查询（并）
查询顾客“张三”和“李四”订购的全部零件的信息。 SELECT P.*
FROM Customer C,Orders O,Lineitem L,PartSupp PS,Part P WHERE C.cuskey=O.custkey AND O.orderkey=L.orderkey AND L.partkey=PS.partkey AND L.suppkey=PS.suppkey AND PS.partkey=P.partkey AND C.name='张三'; UNION SELECT P.*
FROM Customer C,Orders O,Lineitem L,PartSupp PS,Part P WHERE C.cuskey=O.custkey AND O.orderkey=L.orderkey AND L.partkey=PS.partkey AND L.suppkey=PS.suppkey AND PS.partkey=P.partkey AND C.name='李四'; （7）集合查询（差）
顾客“张三”订购过而“李四”没订购过的零件的信息。 SELECT P.*
FROM Customer C,Orders O,Lineitem L,PartSupp PS,Part P WHERE C.cuskey=O.custkey AND O.orderkey=L.orderkey AND L.partkey=PS.partkey AND L.suppkey=PS.suppkey AND PS.partkey=P.partkey AND C.name='张三'; EXCEPT SELECT P.*
FROM Customer C,Orders O,Lineitem L,PartSupp PS,Part P WHERE C.cuskey=O.custkey AND O.orderkey=L.orderkey AND L.partkey=PS.partkey AND L.suppkey=PS.suppkey AND PS.partkey=P.partkey AND C.name='李四'; 4、数据更新实验
（1）INSERT基本语句(插入全部列的数据)
插入一条顾客记录，要求每列都给一个合理的值。 INSERT INTO Customer
VALUES (30,'张三','北京市',40,'010-51001199',0.00,'Northeast','VIP Customer'); （2）INSERT基本语句（插入部分列的数据） 插入一条订单记录，给出必要的几个字段值。
INSERT INTO Lineitem(orderkey,Linenumber,partkey,suppkey,quantity,shipdate) VALUES(862,ROUND(RANDOM()*100,0,479,1,10,'2012-3-6');
/*RANDOM()函数为随机小数生成函数，ROUND()为四舍五入函数*/ （3）批量数据INSERT语句
① 创建一个新的顾客表，把所有中国籍顾客插入到新的顾客表中。
CREATE TABLE NewCustmoer AS SELECT * FROM Customer WITH NO DATA; /*WITH NO DATA子句使得SELECT查询只生成一个结果模式，不查询出实际数据*/
INSERT INTO NewCustomer /*批量插入SELECT 语句查询结果到NewCustomer表中*/ SELECT C.*
FROM Costomer C,Nation N
WHERE C.nationkey=N.nationkey AND N.name='中国';
② 创建一个顾客购物统计表，记录每个顾客及其购物总数和总价等信息。 CREATE TABLE ShoppingStat (custkey INTEGER, quantity REAL, totalprice REAL);
INSERT INTO ShoppingStat
SELECT C.custkey,Sum(L.quantity),Sum(O.totalprice) /*对分组后的数据求总和*/
FROM Customer C,Order O,Lineitem L
WHERE C.custkey=O.custkey AND O.orderkey=L.orderkey GROUP BY C.custkey
③ 倍增零件表的数据，多次重复执行，直到总记录数达到50万为止。 INSERT INTO Part
SELECT partkey+(SELECT COUNT(*) FROM Part), name,mfgr,brand,type,size,container,retailprice,comment FROM Part;
（4）UPDATE语句（插入部分记录的部分列值）
“金仓集团”供应的所有零件的供应成本价下降10%。 UPDATE PartSupp
SET supplycost=supplycost*0.9
WHERE suppkey=(SELECT suppkey /*找出要修改的那些记录*/ FROM Supplier
WHERE name='金仓集团');
（5）UPDATE语句（利用一个表中的数据修改另外一个表中的数据）
利用Part表中的零售价格来修改Lineitem中的extendedprice,其中extendedprice=Part.retailprice*quantity。 UPDATE Lineitem L
SET L.extendedprice=P.retailprice*L.quantity FROM Part P
WHERE L.partkey=P.partkey;
/*Lineitem表也可以直接与Part表相连接，而不需通过PartSupp连接*/ （6）DELETE基本语句（删除给定条件的所有记录） 删除顾客张三的所有订单记录。 DELECT FROM Lineitem /*先删除张三的订单明细记录*/ WHERE orderkey IN(SELECT orderkey
FROM Order O,Customer C
WHERE O.custkey=C.custkey AND C.name='张三'); DELECT FROM Order /*再删除张三的订单记录*/ WHERE custkey=(SELECT custkey FROM Customer
WHERE name='张三'); 5、 视图
（1）创建视图（省略视图列名）
创建一个“海大汽配”供应商供应的零件视图V_DLMU_PartSupp1，要求列出供应零件的编号、零件名称、可用数量、零售价格、供应价格和备注等信息。 CREATE VIEW V_DLMU_PARTSUPP1 AS /*由SELECT子句目标列组成视图属性*/ SELECT
P.partkey,P.name,PS.availqty,P.retailprice,PS.supplycost,P.comment FROM Part P,PartSupp PS,Supplier S
WHERE P.partkey=PS.partkey AND S.suppkey=PS.suppkey AND S.name='海大汽配';
（2）创建视图（不能省略列名的情况）
创建一个视图V_CustAvgOrder,按顾客统计平均每个订单的购买金额和零件数量，要求输出 顾客编号、姓名，平均购买金额和平均购买零件数量。 CREATE VIEW V_CustAvgOrder(custkey,cname,avgprice,avgquantity) AS SELECT C.custkey,MAX(C.name),AVG(O.totalprice),AVG(L.quantity) FROM Customer C,Orders O,Lineitem L
WHERE C.custkey=O.custkey AND L.orderkey=O.orderkey GROUP BY C.custkey;
（3）创建视图（WITH CHECK OPTION）
使用WITH CHECK OPTION，创建一个“海大汽配”供应商供应的零件视图V_DLMU_PartSupp2，要求列出供应零件的编号、可用数量和供应价格等信息。然后通过该视图分别增加、删除和修改一条“海大汽配”零件供应记录，验证WITH CHECK OPTION是否起作用。 CREATE VIEW V_DLMU_PartSupp2 AS
SELECT partkey,suppkey,availqty,supplycost FROM PartSupp
WHERE suppkey=(SELECT suppkey FROM Supplier
WHERE name='海大汽配') WITH CHECK OPTION;
INSERT INTO V_DLMU_PartSupp2 VALUES (58889,5048,704,77760); UPADTE V_DLMU_PartSupp2 SET supplycost=12 WHERE suppkey=58889;
DELETE FROM V_DLMU_PartSupp2 WHERE suppkey=58889;
（4）可更新的视图（行列子集视图）
使用WITH CHECK OPTION，创建一个“海大汽配”供应商供应的零件视图V_DLMU_PartSupp4，要求列出供应零件的编号、可用数量和供应价格等信息。然后通过该视图分别增加、删除和修改一条“海大汽配”零件供应记录，验证该视图是否是可更新的，并比较上述“（3）创建视图”实验任务与本任务结果有何异同。
CREATE VIEW V_DLMU_PartSupp3 AS
SELECT partkey,suppkey,availqty,supplycost FROM PartSupp
WHERE suppkey=(SELECT suppkey FROM Supplier
WHERE name='海大汽配');
INSERT INTO V_DLUM_PartSupp3 VALUES(58889,5048,704,77760);
UPDATE V_DLMU_PartSupp3 SET supplycost=12 WHERE suppkey=58889;
DELETE FROM V_DLMU_PartSupp3 WHERE suppkey=58889; （5）可更新的视图
INSERT INTO V_CustAvgOrder