实验2数据定义和基本查询解析.docx
《实验2数据定义和基本查询解析.docx》由会员分享，可在线阅读，更多相关《实验2数据定义和基本查询解析.docx（14页珍藏版）》请在冰豆网上搜索。
实验2数据定义和基本查询解析
实验二数据定义和基本查询
1实验目的
（1）掌握SQL的数据定义语言DDL使用方法。
（2）掌握SQL的基本查询语句使用方法。
2实验内容
2.1掌握SQL的DDL使用方法
（1）创建名为TPCH的数据库。
（2）在TPCH数据库中创建TPCH数据库模式的8个表。
（3）建立每个表主码、外键等基本完整性约束和相应的用户定义的完整性约束。
2.2掌握SQL的基本查询语句使用方法
（1）单表查询
（2）单表单条件查询
（3）单表多条件查询
（4）两表连接查询
（5）单表自身连接查询
（6）三表连接查询
（7）四表连接查询
（8）五表连接查询
（9）六表连接查询
（10）七表连接查询
3实验要求
（1）深入复习教材第三章SQL有关数据库定义和基本查询SQL语句，理解关系代数表达式如何使用基本的SQL查询语句实现。
（2）请按照附件1生成TPCH数据库模式（注意：
故意在附件1中SQL语句中引入了若干错误，需要仔细调试才能运行），要深入理解该数据库模式的含义。
（3）通过企业管理器添加数据，或者通过其他的数据自动产生工具自动添加数据，以便进行查询。
如果是手动添加数据，请以你班同学当做顾客，国家和地区表的数据参照附件3表中的数据录入（全部或部分数据即可），请从网上搜集尽量真实的零件和供应商信息添加到相应的表中，最后根据购物常识添加订单及其明细信息。
（4）根据书上的例子，针对TPCH数据库模式设计各种基本查询语句，每种类型的基本查询至少要设计一个查询，描述清楚查询要求，运行你所设计的查询语句，并截图相应的实验结果，每幅截图并要有较为详细的描述。
也可以按照附2所列示例查询做实验。
（5）实验步骤和实验总结中要详细描述实验过程中出现的问题、原因和解决方法。
4实验步骤
4.1掌握SQL的DDL使用方法
（1）创建名为TPCH的数据库。
（2）在TPCH数据库中创建上述8个表。
（3）建立每个表主码、外键等基本完整性约束和相应的用户定义的完整性约束。
4.2掌握SQL的基本查询语句使用方法
（1）单表查询（实现投影操作）
查询供应商的名称、地址和联系电话。
selects.name,n.name
fromnationn,suppliers
wheres.nationkey=n.nationkey;
（2）单表单条件查询（实现选择操作）
查询零售价格不超过20元的零件编号、名称及其零售价格。
（3）单表多条件查询
查询订单状态为完成，订单总金额大于40000的订单号，客户号，日期,金额。
（4）两表连接查询（实现笛卡尔积操作）
查询供应商及其所在国家。
（5）两表连接查询（实现普通连接操作，任意两个列做大于、小于等连接操作）
查询供应价格小于零售价格的零件及其供应信息。
（6）单表自身复合连接查询
查询与“华硕”在同一个国家的供应商编号、名称和地址信息。
selectS1.suppkey,S1.name,S1.address
fromsupplierS1
whereS1.nationkeyin
（selectS2.nationkey
fromsupplierS2
whereS2.name='丰泰'）;
（7）三表连接查询
查询顾客“路飞”订购的订单编号、总价及其订购的零件编号、数量和实际价格。
selectl.orderkey,o.totalprice,l.partkey,l.quantity
fromlineiteml,orderso,customerc
wherec.name='路飞'
ando.orderkey=l.orderkey
ando.custkey=c.custkey;
（8）四表连接查询
查询顾客“路飞”订购的订单编号、总价及其订购的零件名称、数量和实际价格。
selectl.orderkey,o.totalprice,p.name,p.retailprice,l.quantity
fromlineiteml,orderso,partp,customerc
wherec.name='路飞'
ando.orderkey=l.orderkey
andl.partkey=p.partkey
ando.custkey=c.custkey;
（9）五表连接查询
查询顾客“路飞”订购的订单编号、总价及其订购的零件供应价格、数量和实际价格。
selecto.orderkey,o.totalprice,ps.supplycost,l.quantity,p.retailprice
fromlineiteml,orderso,partsuppps,partp,customerc
wherec.name='路飞'
ando.orderkey=l.orderkeyandl.partkey=p.partkey
ando.custkey=c.custkeyandps.partkey=p.partkey;
（10）六表连接查询
查询顾客“路飞”订购的订单编号、总价及其订购零件的供应商名称、零件名称、数量、实际价格。
selecto.orderkey,o.totalprice,s.name,p.name,p.retailprice,l.quantity
fromlineiteml,orderso,partsuppps,partp,customerc,suppliers
wherec.name='路飞'
ando.orderkey=l.orderkey
andl.partkey=p.partkey
ando.custkey=c.custkey
andps.partkey=p.partkey
ands.suppkey=ps.suppkey;
（11）七表连接查询
查询中国顾客订购的订单编号、总价及其订购零件的供应商名称、零件名称、数量、实际价格。
selecto.orderkey,o.totalprice,s.name,p.name,p.retailprice,l.quantity
fromlineiteml,orderso,partsuppps,partp,customerc,suppliers,nationn
wheren.name='中国'
ando.orderkey=l.orderkey
andl.partkey=p.partkey
ando.custkey=c.custkey
andps.partkey=p.partkey
ands.suppkey=ps.suppkey
andn.nationkey=c.nationkey;
（12）八表查询
查询亚洲顾客订购的订单编号、总价及其订购零件的供应商名称、零件名称、数量、实际价格。
selecto.orderkey,o.totalprice,s.name,p.name,p.retailprice,l.quantity
fromlineiteml,orderso,partsuppps,partp,customerc,suppliers,nationn,regionr
wherer.name='亚洲'
ando.orderkey=l.orderkey
andl.partkey=p.partkey
ando.custkey=c.custkey
andps.partkey=p.partkey
ands.suppkey=ps.suppkey
andn.nationkey=c.nationkey
andr.regionkey=n.regionkey;
5总结与体会
5.1实验中出现的问题及其解决方案
1.在建立了一个表格例如supplier之后，发现少建了主码，再修改supplier里的数据再次运行的时候发现supplier的DDL没法运行了。
后来发现，当初一直用的是CREATE语句企图再运行刷新数据库，但是SQL语言里规定只能用ALTER语句来修改表，而CREATE语句只能创建表，而且只能创建不同名表，不可以通过刷新来修改表。
2.一个表的主码在定义时就不可以设为空值。
3.在多表连接查询当中，如果要连接N个表，连接谓词至少要有N-1个
5.2总结
多表连接查询可以使查询变得简单快速。
5.3体会
首先要熟悉SQL语句再开始实验，否者容易出错。
另一方面在实验中熟悉SQL语句，进一步掌握SQL语句。
多表连接查询虽然简单，但是也要仔细捋清各个表之间的关系才能得到准确的结果。
附1：
TPC-H数据库模式SQL语句
（已调试）
CREATETABLEsupplier（
suppkeyINTEGERprimarykey,
nameCHAR（25）,
addressVARCHAR（40）,
nationkeyINTEGER,
phoneCHAR（15）,
acctbalREAL,
commentVARCHAR（101））;
CREATETABLEpart（
partkeyINTEGER,
nameVARCHAR（55）,
mfgrCHAR（25）,
brandCHAR（10）,
typeVARCHAR（25）,
sizeINTEGER,
containerCHAR（10）,
retailpriceREAL,
commentVARCHAR（23））;
CREATETABLEpartsupp（
partkeyINTEGER,
suppkeyINTEGER,
availqtyINTEGER,
supplycostREAL,
commentVARCHAR（199））;
CREATETABLEcustomer（
custkeyINTEGER,
nameVARCHAR（25）,
addressVARCHAR（40）,
nationkeyINTEGER,
phoneCHAR（15）,
acctbalREAL,
mktsegmentCHAR（10）,
commentVARCHAR（117））;
CREATETABLEorders（
orderkeyINTEGER,
custkeyINTEGER,
orderstatusCHAR
（1）,
totalpriceREAL,
orderDATEDATEtime,
orderpriorityCHAR（15）,
clerkCHAR（15）,
shippriorityINTEGER,
commentVARCHAR（79））;
CREATETABLElineitem（
orderkeyINTEGER,
partkeyINTEGER,
suppkeyINTEGER,
linenumberINTEGER,
quantityREAL,
extendedpriceREAL,
discountREAL,
taxREAL,
returnflagCHAR
（1）,
linestatusCHAR
（1）,
shipDATEDATEtime,
commitDATEDATEt
升级会员 