Sql语句使用

一、建立八张表

零件供应商子模式包含三个基本表零件表（Part）、供应商
表（Supplier）和零件供应商联系表（Partsupp）
和Region（地区表）、Nation（国家表）、Customer（顾客表）、
Orders（订单表）、Lineitem （订单明细表）共八张表

二、详细步骤

（1）创建数据库

create database PartsSupplier   

(2)创建表

表一 Part表

create table Part(partkey integer PRIMARY KEY,----实体完整性
name varchar(20), 
mfgr char(25),brand char(10),
type varchar(25),size integer, 
container char(10), 
retailprice real, 
comment  varchar(23) )

表二 Supplier表

create table Supplier(suppkey integer PRIMARY KEY,----实体完整性
name char（25）,
address varchar(40),
nationkey integer,
phone char(15),
acctbal real,
comment varchar(101))

表三 Partsupp表

create table Partsupp(
partkey integer,
suppkey integer,
PRIMARY KEY(partkey,suppkey),
availqty integer,
supplycost real,
comment varchar(20),
constraint partkey foreign key (partkey)references Part(partkey),
constraint suppkey foreign key (suppkey)references Supplier(suppkey)
)

-----参照完整性，表Part和表Supplier是被参照关系,表Partsupp是参照关系

表四 地区表

create table region(
regionkey  integer primary key ,
name char(25),
comment  varchar(152) )

表五 国家表

create table nation(
nationkey  integer primary key ,
name char(25),
comment  varchar(152) )

表六 顾客表

create table Customer(
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  char(15),
clerk  char(15),
shippriority  integer,
comment  varchar(79) )

表八 订单明细表

create table Lineitem(
orderkey  integer references orders(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),
shipdate  date,
commitdate  date,
receiptdate   date,
shipinstruct  char(25),
shipmode  char(10),
comment  varchar(44),
primary key(orderkey,linenumber),
foreign key(partkey,suppkey)references (partsupp(partkey,suppkey)))

(3)按要求查询
a. 查询供应商的名称、地址和联系电话

select name, address, phone from supplier

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

查询语句：select * from orders where totalprice > 1000

c. 统计每个顾客的订购金额

查询语句：select custkey, totalprice from orders

d. 查询订单平均金额超过1000元的顾客编号及其姓名

查询语句：select custkey, name from customer where custkey in (select custkey from orders where totalprice>1000)

e. 查询与“浙江干大林副食有限公司”在同一个国家的供应商编号、名称和地址信息

查询语句：select suppkey, name, address from supplier where nationkey in (select nationkey from supplier where name = '浙江干大林副食有限公司')

f. 查询供应商价格大于零售价格的零件名、制造商名、零售价格和供应价格

查询语句：select name, mfgr, retailprice, supplycost from part, partsupp where part.partkey=partsupp.partkey and part.retailprice>partsupp.supplycost

g. 查询顾客“xxx”订购的订单编号、总价及其订购的零件编号、数量和明细价格

查询语句：select orderkey, quantity, partkey, linenumber, extendedprice from  Lineitem where orderkey in (select orderkey from orders where custkey in (select custkey from customer where name='刘一'))

心得

通过实验二对于sql语句查询的练习，深刻的体会到理论知识的重要性，在没有参考课本的情况下基本是无法做下去的，很容易卡在某一点然后无法继续进行下去。比如第（4）题的嵌套查询，刚开始并没有立即想到，而是想着怎么直接使用‘.’访问符直接访问，一直卡着做不下去，直到翻开课本看到先建立多表连接再进行查询后才恍然大悟，下面做的就得心应手了。但看书的最终目的一定是摆脱课本故理论界和实践才是最有效、最高效的学习方法。

三、思考题

（1）不在group by子句出现的属性，是否可以出现在select子句中?

不在group by子句出现的属性，可以出现在select子句中；
而出现在group by子句出现的属性，不一定出现在select子句中。

(2)分组统计查询中的where和having的区别？

Where是对元组的约束；having是对分组的约束。
Where后面的条件表达式不能是函数；having后可以跟函数。
Having在某种情况下可由where替代，having常跟在group by后面

（3）如何提高连接查询速度？

优化语句，精确条件。实现同一个查询的请求可以有多种方法，不同的方法其执行效率可能会有差别，甚至会差别很大，这就需要我们对于聚进行优化，找出查询速度最快的语句。