发布时间 : 星期三 文章《数据库系统原理》实验报告更新完毕开始阅读1f1142d8376baf1ffd4fadbb
（3）定义基本表
在TPCH数据库的Sales模式中创建8个基本表。
/*设置当前会话的搜索路径为sales模式、public模式，基本表就会自动创建在sales模式下。*/
SET SEARCH_PATH TO Sales, Public; CREATE TABLE Region(
regionkey INTEGER PRIMARY KEY, name CHAR(25),
comment VARCHAR(152)); CREATE TABLE Nation(
nationkey INTEGER PRIMARY KEY, name CHAR(25),
address VARCHAR(40),
regionkey INTEGER REFERENCES REGION(REGIONKEY), comment VARCHAR(152)); CREATE TABLE Supplier(
suppkey INTEGER PRIMARY KEY, name CHAR(25),
address VARCHAR(40),
nationkey INTEGER REFERENCES Nation(nationkey), phone CHAR(15), acctbal REAL,
comment VARCHAR(101)); CREATE TABLE Part(
partkey INTEGER PRIMARY KEY, name VARCHAR(55), mfgr CHAR(25), /*制造厂*/ brand CHAR(10), type VARCHAR (25), size INTEGER,
container CHAR(10), retailprice REAL,
comment VARCHAR(23)); CREATE TABLE PartSupp(
partkey INTEGER REFERENCES Part(partkey),
suppkey INTEGER REFERENCES Supplier(suppkey), availqty INTEGER, supplycost REAL ,
comment varchar(199),
PRIMARY KEY (parkey,suppkey)); CREATE TABLE Costomer(
custkey INTEGER PRIMARY KEY, name VARCHAR(25), address VARCHAR(40),
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.*