《数据库系统原理》实验报告 下载本文

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