《数据库系统原理》实验报告(2)

nationkey INTEGER REFERENCES Nation(nationkey), phone CHAR(15), acctbal REAL,

mktsegment CHAR(10), comment VARCHAR(117)); CREATE TABLE Orders(

orderkey INTEGER PRIMARY KEY,

custkey INTEGER REFERENCES Customer(custkey), orderstatus CHAR(1), totalprice REAL, orderdate DATE,

orderpriority INTEGER, comment VARCHAR(79)); CREATE TABLE Lineitem(

orderkey INTEGER REFERENCES Order(orderkey), partkey INTEGER REFERENCES Part(partkey),

suppkey INTEGER REFERENCES Supplier(suppkey), linenumber INTEGER, quantity REAL,

extendedprice REAL, discount REAL, tax REAL,

returnflag CHAR(1), linestatus CHAR(1), shipinstruct CHAR(25), shipmode CHAR(10), comment VARCHAR(44),

PRIMARY KEY(orderkey,linenumber),

FOREIGN KEY(Partkey,suppkey) REFERENCES PartSupp(partkey,suppkey)); 2、数据基本查询

（1）单表查询（实现投影操作）

查询供应商的名称、地址和联系电话。

SELECTE name,address,phone FROMSupplier; （2）单表查询（实现选择操作）

查询最近一周内提交的总价大于1000元的订单的编号、顾客编号等订单的所有信息。

SELECT *FROM Sales.Orders WHERE CURRENT_DATE-orderdata<7 AND totalprice >1000;

（3）不带分组过滤条件的分组统计查询 统计每个顾客的订购金额

SELECT C.custkey ,SUM(O.totalprice) FROM customer C,Orders O WHERE C.custkey=O.custkey GROUP BY C.custkey;

（4）带分组过滤条件的分组统计查询

查询订单平均金额超过1000元的顾客编号及其姓名 SELECT C.custkey，MAX（C.name） FROM Customer C,Orders O WHERE C.custkey=O.custkey GROUP BY C.custkey;

HAVING AVG（O.totalprice）>1000; （5）表单自身连接查询

查询与“金仓集团”在同一个国家的供应商编号、名称和地址信息。 SELECT F.suppkey,F.name，F.address FROM Supplier F,Supplier S

WHERE F.nationkey=S.nationkey AND S.name='金仓集团'; （6）两表连接查询（普通连接）

查询供应价格大于零售价格的零件名、制造商名、零售价格和供应价格。 SELECT P.name,P.mfgr,P.retailprice,PS.supplycost FROM Part P,Partsupp PS

WHERE P.retailprice>PS.supplycost; （7）两表连接查询（自然连接）

查询供应价格大于零售价格的零件名、制造商名、零售价格和供应价格。 SELECT P.name,P.mfgr,P.retailprice,PS.supplycost FROM Part P,Partsupp PS

WHERE P.partkey=PS.partkey AND P.retailprice>PS.supplycost; （8）三表连接查询

查询顾客“苏举库”订购的订单编号、总价及其订购的零件编号、数量和明细价格。

SELECT O.orderkey,O.totalprice,L.partkey,L.quantity,L.extendedprice FROM Custom C,Orders O,Lineitem L

WHERE C.custkey=O.custkey AND O.orderkey=L.orderkey AND C.name='苏举库'; 3、数据高级查询实验 （1）IN嵌套查询

查询订购了“海大”制造的“船舶模拟驾驶舱”的顾客。 SELECT custkey，name FROM Customer

WHERE custkey IN ( SELECT O.custkey FROM Orders O,Lineitme L，PartSupp PS,Part P WHERE O.orderkey=L.orderkey AND L.partkey=PS.partkey AND L.suppkey=PS.suppkey AND PS.partkey=P.partkey AND P.mfgr='海大' AND P.name='船舶模拟驾驶舱'); SELECT custkey,name FROM Customer

WHERE cuskey IN ( SELECT O.custkey

FROM Orders O,Lineitem L,Part P WHERE O.orderkey=L.orderkey AND L.partkey=P.partkey AND p.mfgr='海大' AND P.name='船舶模拟驾驶舱'); （2）单层EXISTS嵌套查询

查询没有购买过“海大”制造的“船舶模拟驾驶舱”的顾客。 SELECT custkey,name FROM Customer

WHERE NOT EXISTS( SELECT O.custkey FROM 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 p.mfgr='海大' AND P.name='船舶模拟驾驶舱'); （3）双层EXISTS嵌套查询

查询至少购买过顾客“张三”购买过的全部零件的顾客姓名。 SELECT CA.name FROM Customer CA WHERE NOT EXISTS （SELECT *

FROM Customer CB,Oders OB,Lineitem LB WHERE CB.custkey=OB.custkey AND OB.orderkey=LB.orderkey AND CB.name='张三' AND NOT EXISTS(SELECT * FROM Orders OC,Lineitem LC WHERE CA.custkey=LC.custkey AND OC.orderkey=LC.orderkey AND LB.suppkey=LC.suppkey AND LB.partkey=LC.partkey)); （4）FROM子句中的嵌套查询

查询订单平均金额超过1万元的顾客中的中国籍顾客信息。 SELECT C.*

FROM Customer C,(SELECT custkey FROM Orders GROUP BY custkey HAVING AVG(totalprice)>10000) B,Nation N WHERE C.custkey=B.custkey AND C.nationkey=N.nationkey AND N.name='中国'; （5）集合查询（交）

查询顾客“张三”和“李四”都订购过的全部零件的信息。 SELECT P.*

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语句（插入部分记录的部分列值）