数据库实验报告
软件一班高曌学号2220102668
一、实验目的：
1.通过对某个商用数据库管理系统的使用，了解DBMS的工作原理和系统构架。熟悉使用DBMS的企业管理器进行数据库管理；
2.掌握SQL的DDL使用方法。
3.掌握SQL的基本查询语句使用方法。
4.掌握SQL的高级查询的使用方法，如分组统计、嵌套查询、集合查询等等。
5.掌握SQL数据更新语句的基本使用方法，如UPDATE、DELETE、INSERT。
6.掌握更新语句条件中的嵌套查询使用方法。
7.掌握SQL视图语句的基本使用方法，如CREATE VIEW、DROP VIEW。
8.掌握视图更新、WITH CHECK OPTION等高级功能的使用。
二、实验过程：
实验一：数据库定义
数据库模式的sql语言
CREATE TABLE supplier (
suppkey INTEGER PRIMARY KEY,
name CHAR(25),
address VARCHAR(40),
nationkey INTEGE R,
phone CHAR(15),
acctbal REAL,
c omment0 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,
comment0 VARCHAR(23));
CREATE TABLE partsupp (
partkey INTEGER,
suppkey INTEGER,
availqty INTEGER,
supplycost REAL,
comment0 VARCHAR(199),
PRIMARY KEY(partkey,suppkey),
foreign key(partkey)references part(partkey),
foreign key(suppkey)references supplier(suppkey));
CREATE TABLE customer (
custkey INTEGER primary key,
name VARCHAR(25),
address VARCHAR(40),
nationkey INTEGER,
phone CHAR(15),
acctbal REAL,
mktsegment CHAR(10),
comment0 VARCHAR(117)
Foreign key (nationkey)references nation(nationkey);); CREATE TABLE orders (
orderkey INTEGER primary key,
custkey INTEGER,
orderstatus CHAR(1),
totalprice REAL,
orderDATE DATE,
orderpriority CHAR(15),
clerk CHAR(15),
shippriority INTEGER,
comment0 VARCHAR(79),
foreign key(custkey)references customer(custkey));
CREATE TABLE lineitem (
orderkey INTEGER,
partkey INTEGER,
suppkey INTEGER,
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),
comment0 VARCHAR(44),
primary key(orderkey,linenumber),
foreign key(partkey)references part(partkey),
foreign key(suppkey)references supplier(suppkey),
foreign key(partkey,suppkey)references partsupp(partkey,suppkey)); CREATE TABLE nation (
nationkey INTEGER primary key,
name CHAR(25),
regionkey INTEGER,
comment0 VARCHAR(152),
foreign key(regionkey)references region(regionkey));
CREATE TABLE region (
regionkey INTEGER primary key,
name CHAR(25),
comment0 VARCHAR(152));
tpch数据库如下图所示：
实验二：数据库的基本查询
查询供应商的名称、地址和联系电话。
select name,address,phone from supplier;
查询零售价格不超过50元的零件编号、名称及其零售价格。
select partkey,name,retailprice from part where retailprice<=50;
查询最近一周内提交的总价大于1000元的订单的编号、顾客编号、订单日期和总价等信息。
select orderkey,custkey,orderdate,totalprice from orders where totalprice>1000;
查询供应商及其所在国家。
select supplier.*,nation.* from supplier,nation where
supplier.nationkey=nation.nationkey;
查询供应价格大于零售价格的零件及其供应信息。
select partsupp.* from partsupp,part where partsupp.supplycost>part.retailprice;
查询供应商及其所在国家。
select https://www.doczj.com/doc/872846243.html,,https://www.doczj.com/doc/872846243.html, from supplier,nation where
supplier.nationkey=nation.nationkey;
查询供应商及其所在国家。
select https://www.doczj.com/doc/872846243.html,,https://www.doczj.com/doc/872846243.html, from supplier,nation;
查询与“三剑”在同一个国家的供应商编号、名称和地址信息。
select s1.suppkey,https://www.doczj.com/doc/872846243.html,,s1.address from supplier s1,supplier s2 where
s1.nationkey=s2.nationkey and https://www.doczj.com/doc/872846243.html,='三剑';
查询顾客“奚圆圆”订购的订单编号、总价及其订购的零件编号、数量和实际价格。
select orders.orderkey,orders.totalprice,part.partkey,lineitem.quantity,part.retailprice
from orders,part,lineitem,customer where orders.custkey=customer.custkey and
https://www.doczj.com/doc/872846243.html,='奚圆圆'and orders.orderkey=lineitem.orderkey and
lineitem.partkey=part.partkey;
查询顾客“奚圆圆”订购的订单编号、总价及其订购的零件供应价格、数量和实际价格。
select
orders.orderkey,orders.totalprice,partsupp.supplycost,lineitem.quantity,part.retailprice
from orders,part,lineitem,customer,partsupp
Where orders.custkey=customer.custkey and https://www.doczj.com/doc/872846243.html,='奚圆圆'
and orders.orderkey=lineitem.orderkey
and lineitem.partkey=part.partkey and partsupp.partkey=part.partkey;
查询顾客“奚圆圆”订购的订单编号、总价及其订购的零件名称、数量和实际价格。
select orders.orderkey,orders.totalprice,https://www.doczj.com/doc/872846243.html,,lineitem.quantity,part.retailprice
from orders,part,lineitem,customer
where orders.custkey=customer.custkey and https://www.doczj.com/doc/872846243.html,='奚圆圆'
and orders.orderkey=lineitem.orderkey
and lineitem.partkey=part.partkey ;
查询顾客“奚圆圆”订购的订单编号、总价及其订购零件的供应商名称、零件名称、数量、实际价格。
select
orders.orderkey,orders.totalprice,https://www.doczj.com/doc/872846243.html,,https://www.doczj.com/doc/872846243.html,,lineitem.quantity,part.retailprie from orders,part,lineitem,customer,supplier
where orders.custkey=customer.custkey and https://www.doczj.com/doc/872846243.html,='奚圆圆'
and orders.orderkey=lineitem.orderkey
and lineitem.partkey=part.partkey and lineitem.suppkey=supplier.suppkey;
查询中国顾客订购的订单编号、总价及其订购零件的供应商名称、零件名称、数量、实际价格。
select
orders.orderkey,orders.totalprice,https://www.doczj.com/doc/872846243.html,,https://www.doczj.com/doc/872846243.html,,lineitem.quantity,part.retailprie from orders,part,lineitem,customer,supplier,nation
where orders.custkey=customer.custkey and customer.nationkey=nation.nationkey and
https://www.doczj.com/doc/872846243.html,='中国' and orders.orderkey=lineitem.orderkey and
lineitem.partkey=part.partkey and lineitem.suppkey=supplier.suppkey;
查询亚洲顾客订购的订单编号、总价及其订购零件的供应商名称、零件名称、数量、实际价格。
Select
orders.orderkey,orders.totalprice,https://www.doczj.com/doc/872846243.html,,https://www.doczj.com/doc/872846243.html,,lineitem.quantity,part.retailprie from orders,part,lineitem,customer,supplier,nation,region
where orders.custkey=customer.custkey and customer.nationkey=nation.nationkey
And nation.regionkey=region.regionkey and https://www.doczj.com/doc/872846243.html,='亚洲'
and orders.orderkey=lineitem.orderkey
and lineitem.partkey=part.partkey and lineitem.suppkey=supplier.suppkey;
实验三：高级SQL查询
（1）不带分组过滤条件的分组统计查询。
统计每个顾客订购金额。
select https://www.doczj.com/doc/872846243.html,,sum(orders.totalprice)
from customer,orders
where customer.custkey=orders.custkey
group by https://www.doczj.com/doc/872846243.html,;
这组实验我明白了在没有聚集函数的情况下，select语句中的信息应和group by中的信息一致。
（2）带分组过滤条件的分组统计查询。
查询平均每个订单金额超过1000元的顾客编号及其姓名。
select orders.custkey,https://www.doczj.com/doc/872846243.html,
from customer,orders
where customer.custkey=orders.custkey
group by https://www.doczj.com/doc/872846243.html,,orders.custkey
having sum(orders.totalprice)>100000;
（3）IN嵌套查询。
查询订购了“吉发”制造的“螺丝刀”的顾客。
select https://www.doczj.com/doc/872846243.html,
from customer
where customer.custkey in
(select custkey
from orders
where orderkey in(
select orderkey
from lineitem,part
where lineitem.partkey=part.partkey and https://www.doczj.com/doc/872846243.html,='螺丝刀'and part.mfgr='吉发'));
（4）单层EXISTS嵌套查询。
查询没有购买过“海大”制造的“船舶模拟驾驶舱”的顾客。
select https://www.doczj.com/doc/872846243.html,
from customer
where not exists
(select *
from lineitem,part,supplier,orders
where custkey=orders.custkey
and lineitem.partkey=part.partkey
and lineitem.suppkey=supplier.suppkey
and https://www.doczj.com/doc/872846243.html,='螺栓'and part.mfgr='三剑');
（5）双层EXISTS嵌套查询。
查询至少购买过顾客“张三”购买过的全部零件的顾客姓名。
select name
from customer cusx
where not exists
(select *
from customer cusy
where https://www.doczj.com/doc/872846243.html,='高曌' and not exists
(select *
from part,customer,orders,lineitem
where https://www.doczj.com/doc/872846243.html,=https://www.doczj.com/doc/872846243.html,
and orders.custkey=customer.custkey
and lineitem.orderkey=orders.orderkey
and part.partkey=lineitem.partkey));
（6）集合查询(交、并、差各设计一个)。
查询顾客“张三”和“李四”都订购过的全部零件的信息。
select https://www.doczj.com/doc/872846243.html,
from part,customer,orders,lineitem
where orders.custkey=customer.custkey
and lineitem.orderkey=orders.orderkey
and part.partkey=lineitem.partkey
and https://www.doczj.com/doc/872846243.html,='高曌'
INTERSECT
select https://www.doczj.com/doc/872846243.html,
from part,customer,orders,lineitem
where orders.custkey=customer.custkey
and lineitem.orderkey=orders.orderkey
and part.partkey=lineitem.partkey
and https://www.doczj.com/doc/872846243.html,='张欢';
查询顾客“张三”和“李四”订购的全部零件的信息。
select https://www.doczj.com/doc/872846243.html,
from part,customer,orders,lineitem
where orders.custkey=customer.custkey
and lineitem.orderkey=orders.orderkey
and part.partkey=lineitem.partkey
and https://www.doczj.com/doc/872846243.html,='高曌'
UNION
select https://www.doczj.com/doc/872846243.html,
from part,customer,orders,lineitem
where orders.custkey=customer.custkey
and lineitem.orderkey=orders.orderkey
and part.partkey=lineitem.partkey
and https://www.doczj.com/doc/872846243.html,='张欢';
顾客“张三”订购过，而“李四”没订购过的零件的信息。
select https://www.doczj.com/doc/872846243.html,
from part,customer,orders,lineitem
where orders.custkey=customer.custkey
and lineitem.orderkey=orders.orderkey
and part.partkey=lineitem.partkey
and https://www.doczj.com/doc/872846243.html,='高曌'
EXCEPT
select https://www.doczj.com/doc/872846243.html,
from part,customer,orders,lineitem
where orders.custkey=customer.custkey
and lineitem.orderkey=orders.orderkey
and part.partkey=lineitem.partkey
and https://www.doczj.com/doc/872846243.html,='张欢';
（7）FROM 子句中的嵌套查询
查询平均每个订单金额超过1万元的顾客中属于中国的顾客信息。
PB中不支持FROM子句的嵌套查询。
实验四、SQL数据更新语句
（1）INSERT基本语句（插入全部列的数据）。
插入一个新顾客的记录，要求每列都给一个合理的值
insert into customer(custkey,name,address,nationkey,phone,acctbal,mktsegment)
values(7,'赵三','得标街和平路2号',1,'189********','1000000','中东区');
插入一条订单明细记录，要求每列都给一个合理的值
insert into orders values(8,2,1,'9800','2011/8/25','高','刘婷',3,'附加');
（2）INSERT基本语句（插入部分列的数据，要求随机生成某些数字列或者字符列的数据）。
插入一条订单记录，可以只给出必要的几个字段的值，其中数值字段可以用RANDOM函数随机生成数值
insert into orders(orderkey,orderdate,clerk)
values(9,'2011/8/25','刘婷');
Random不会，但是通过这个语句我明白了，插入语句时，主键不可以为空！
（3）UPDATE基本语句（修改所有记录的某些列的值）。
修改所有零件的零售价，使其价格上浮10%
update part set retailprice=retailprice*(1+0.10);
（4）UPDATE基本语句（修改部分记录的某些列的值）。
修改顾客高曌的国籍
update customer set nationkey=2 where name='高曌';
（5）DELETE基本语句（删除所有记录）。
删除所有订单记录
delete from orders;
（6）DELETE基本语句（删除部分记录）。
删除2011年1月1日之前订单记录
delete from orders where orderdate<'2011/1/1';
（7）INSERT批量插入语句（把一个查询结果插入到另外一个表中）。
创建一个顾客购物统计表，记录每个顾客及其购物总数和总价等信息
create table cus_ord
(cusname char(5) primary key,
ordsum char(10),
ordprice char(10)
);
insert
into cus_ord(cusname,ordsum,ordprice)
select https://www.doczj.com/doc/872846243.html,,lineitem.quantity,lineitem.extendedprice
from lineitem,orders,customet
where orders.orderkey=lineitem.orderkey and orders.custkey=customer.custkey
group by https://www.doczj.com/doc/872846243.html,;
实验五视图
创建视图（省略视图列名）。
（1）创建一个查看所有中国地区顾客基本信息的视图。
CREATE VIEW B2C
AS
SELECT C_CUSTKEY,C_NAME,C_PHONE,C_NATIONKEY
FROM CUSTOMER
WHERE C_NATIONKEY='001';
（2）创建视图（不能省略列名的情况）。
创建一个视图，查看订单号，折扣，税率，折扣减去税率所得的总折扣率
CREATE VIEW LISTMES(L_ORDERKEY,L_DISCOUNT,L_TAX,DISCOUNT)
AS
SELECT L_ORDERKEY,L_DISCOUNT,L_TAX,L_DISCOUNT-L_TAX
FROM LINEITEM;
（3）删除视图（RESTRICT / CASCADE）。
删除名字为LISTMES的视图
DROP VIEW LISTMES;
三、实验心得：
1、此次实验，全部语句我是通过powerbuilder实现的，安装SQL总是不成功，所以没办法只好选择PB了，通过上机和在寝室做，我发现PB与SQL还是有不同之处的，有的语句PB可以实现但在SQL中便不作为关键字；
2、万事开头难，建表貌似简单，实则过程很繁琐，花了很多心思与功夫在上面；
3、通过写SQL语句，发现把教材上的东西转化为动手实验，效果真的是事半功倍，让我深有感触的是，数据库的学习应该强调动手能力，死看书肯定得不到知识。