《数据库系统原理》实验报告.docx
《《数据库系统原理》实验报告.docx》由会员分享，可在线阅读，更多相关《《数据库系统原理》实验报告.docx（40页珍藏版）》请在冰豆网上搜索。
《数据库系统原理》实验报告
学生实验
（理工类）
14软件工程1班
孟祥辉
麻春艳
第—_学期
课程名称：
数据库系统原理专业班级:
学生学号：
1412101055学生姓名:
所属院部：
软件工程学院指导教师:
2015——20_16—学年
金陵科技学院教务处制
实验报告书写要求
实验报告原则上要求学生手写，要求书写工整。
若因课程特点需打印的，要遵照以下字体、字号、间距等的具体要求。
纸张一律采用A4的纸张。
实验报告书写说明实验报告中一至四项内容为必填项，包括实验目的和要求；实验仪器和设备；实验内容与过程；实验结果与分析。
各院部可根据学科特点和实验具体要求增加项目。
填写注意事项
（1）细致观察，及时、准确、如实记录。
（2）准确说明，层次清晰。
（3）尽量采用专用术语来说明事物。
（4）外文、符号、公式要准确，应使用统一规定的名词和符号。
（5）应独立完成实验报告的书写，严禁抄袭、复印，一经发现，以零分论处。
实验报告批改说明实验报告的批改要及时、认真、仔细，一律用红色笔批改。
实验报告的批改成绩采用百分制，具体评分标准由各院部自行制定。
实验报告装订要求
实验批改完毕后，任课老师将每门课程的每个实验项目的实验报告以自然班为单位、按学号升序排列，装订成册，并附上一份该门课程的实验大纲。
实验项目名称：
数据库定义与操作语言实验学时：
2
同组学生姓名：
孟陈、陈晓雪、季佰军
实验地点：
1318
实验日期：
5.19实验成绩：
批改教师：
批改时间：
、实验目的
1、理解和掌握数据库DDL语言，能够熟练地使用SQLDDL语句创建、修改和删除数据库、模式和基本表。
2、掌握SQL册亨徐设计基本规范，熟练运用SQL语言实现数据基本查询，包括单表查询、分组统计查询和连接查询
3、掌握SQL嵌套查询和集合查询等,各种高级查询的设计方法等.
4、熟悉数据库的数据更新操作，能够使用sql语句对数据库进行数据的插入、
修改、删除操作。
5、熟悉sql语言有关系图的操作，能够熟练使用sql语言来创建需要的视图，定义数据库外模式，并能使用所创建的视图实现数据管理。
6、掌握所以设计原则和技巧，能够创建合适的索引以提高数据库查询、统计分析效率。
二、实验内容和要求
1、理解和掌握SQLDDL语句的语法，特别是各种参数的具体含义和使用方法；使用sql语句创建、修改和删除数据库、模式和基本表。
掌握sql语句常见语法错误的调试方法。
2、针对TPC-H数据库设计各种单表查询sql语句、分组统计查询语句；设计单个表针对自身的连接查询，涉及多个表的连接查询。
理解和掌握sql查询语句各个子句的特点和作用，按照sql程序设计规范写出具体的sql查询语句，并调试通过。
3、针对TPC-H数据库，证券分析用户查询要求，设计各种嵌套查询和集合查询。
4、针对TPC-H数据库设计单元主唱入、批量数据插入、修改数据和删除数据的sql语句。
理解和掌握insert、update、delete语法结构的各个组成成分，结合嵌套sql子查询，分别设计几个不同形式的插入、修改和删除数据的语句，并调试成功。
5、针对给定的数据库模式，以及相应的应用要求，创建视图和带WITHCHECKOPTION的视图，并验证视图WITHCHECKOPTION选项的有效性。
理解和掌握试图消解执行原理，掌握可更新视图和不可更新视图的区别。
6、针对给定的数据库模式和具体应用需求，创建唯一索引、函数索引、复合索引等；修改索引；删除索引。
设计相应的sql查询验证索引有效性，学习利用EXPLAIN命令分析sql查询是否使用了所创建的索引，并能够分析其原因，执行sql查询并估算索引提高查询效率的百分比，要求实验数据达到10万条记录以上的数据量，以便验证所以效果.
三、实验过程
1、数据库定义实验
（1）定义数据库
采用中文字符集创建名为TCHP的数据库。
CREATEDATABASETPCHENCODING='GBK';
（2）定义模式
在数据库TPCH中创建名为SALES的模式。
CreateSCHEMASales;
3）定义基本表
在TPCH数据库的Sales模式中创建8个基本表。
/*设置当前会话的搜索路径为sales模式、public模式，基本表就会自动创建在sales模式下。
*/
SETSEARCH_PATHTOSales,Public;
CREATETABLERegion（regionkeyINTEGERPRIMARYKEY,nameCHAR（25）,commentVARCHAR（152））;
CREATETABLENation（nationkeyINTEGERPRIMARYKEY,nameCHAR（25）,addressVARCHAR（40）,regionkeyINTEGERREFERENCESREGION（REGIONKEY）,commentVARCHAR（152））;
CREATETABLESupplier（suppkeyINTEGERPRIMARYKEY,nameCHAR（25）,addressVARCHAR（40）,nationkeyINTEGERREFERENCESNation（nationkey）,phoneCHAR（15）,acctbalREAL,commentVARCHAR（101））;
CREATETABLEPart（partkeyINTEGERPRIMARYKEY,nameVARCHAR（55）,mfgrCHAR（25）,/*制造厂*/
brandCHAR（10）,typeVARCHAR（25）,sizeINTEGER,containerCHAR（10）,retailpriceREAL,commentVARCHAR（23））;
CREATETABLEPartSupp（partkeyINTEGERREFERENCESPart（partkey）,suppkeyINTEGERREFERENCESSupplier（suppkey）,availqtyINTEGER,supplycostREAL,commentvarchar（199）,
PRIMARYKEY（parkey,suppkey））;
CREATETABLECostomer（custkeyINTEGERPRIMARYKEY,nameVARCHAR（25）,addressVARCHAR（40）,
nationkeyINTEGERREFERENCESNation（nationkey）,phoneCHAR（15）,acctbalREAL,mktsegmentCHAR（10）,commentVARCHAR（117））;
CREATETABLEOrders（orderkeyINTEGERPRIMARYKEY,custkeyINTEGERREFERENCESCustomer（custkey）,orderstatusCHAR
（1）,totalpriceREAL,orderdateDATE,orderpriorityINTEGER,commentVARCHAR（79））;
CREATETABLELineitem（orderkeyINTEGERREFERENCESOrder（orderkey）,partkeyINTEGERREFERENCESPart（partkey）,suppkeyINTEGERREFERENCESSupplier（suppkey）,linenumberINTEGER,quantityREAL,extendedpriceREAL,discountREAL,taxREAL,returnflagCHAR
（1）,linestatusCHAR
（1）,shipinstructCHAR（25）,shipmodeCHAR（10）,commentVARCHAR（44）,
PRIMARYKEY（orderkey,linenumber）,
FOREIGNKEY（Partkey,suppkey）REFERENCESPartSupp（partkey,suppkey））;
2、数据基本查询
（1）单表查询（实现投影操作）查询供应商的名称、地址和联系电话。
SELECTEname,address,phoneFROMSupplier;
（2）单表查询（实现选择操作）查询最近一周内提交的总价大于1000元的订单的编号、顾客编号等订单的所有信息。
SELECT*FROMSales.OrdersWHERECURRENT_DATE-orderdata<7ANDtotalprice>1000;
（3）不带分组过滤条件的分组统计查询统计每个顾客的订购金额
SELECTC.custkey,SUM（O.totalprice）FROMcustomerC,OrdersO
WHEREC.custkey=O.custkeyGROUPBYC.custkey;
（4）带分组过滤条件的分组统计查询查询订单平均金额超过1000元的顾客编号及其姓名SELECTC.custkey，MAX（C.name）
FROMCustomerC,OrdersO
WHEREC.custkey=O.custkey
GROUPBYC.custkey;
HAVINGAVG（O.totalprice）>1000;
（5）表单自身连接查询查询与“金仓集团”在同一个国家的供应商编号、名称和地址信息。
SELECTF.suppkey,F.name，F.address
FROMSupplierF,SupplierS
WHEREF.nationkey=S.nationkeyANDS.name=&仓集团';
（6）两表连接查询（普通连接）查询供应价格大于零售价格的零件名、制造商名、零售价格和供应价格。
SELECTP.name,P.mfgr,P.retailprice,PS.supplycost
FROMPartP,PartsuppPS
WHEREP.retailprice>PS.supplycost;
（7）两表连接查询（自然连接）查询供应价格大于零售价格的零件名、制造商名、零售价格和供应价格。
SELECTP.name,P.mfgr,P.retailprice,PS.supplycost
FROMPartP,PartsuppPS
WHEREP.partkey=PS.partkeyAND
P.retailprice>PS.supplycost;
（8）三表连接查询查询顾客“苏举库”订购的订单编号、总价及其订购的零件编号、数量和明细价格。
SELECTO.orderkey,O.totalprice,L.partkey,L.quantity,L.extendedprice
FROMCustomC,OrdersO,LineitemL
WHEREC.custkey=O.custkeyANDO.orderkey=L.orderkeyANDC.name='苏举库';
3、数据高级查询实验
（1）IN嵌套查询查询订购了“海大”制造的“船舶模拟驾驶舱”的顾客。
SELECTcustkey，name
FROMCustomer
WHEREcustkeyIN（SELECTO.custkey
FROMOrdersO,LineitmeL，PartSuppPS,PartP
WHEREO.orderkey=L.orderkeyAND
L.partkey=PS.partkeyAND
L.suppkey=PS.suppkeyAND
PS.partkey=P.partkeyAND
P.mfgr=海大'ANDP.name=M舶模拟驾驶舱'）;SELECTcustkey,name
FROMCustomer
WHEREcuskeyIN（SELECTO.custkey
FROMOrdersO,LineitemL,PartP
WHEREO.orderkey=L.orderkeyAND
L.partkey=P.partkeyAND
p.mfgr='海大'ANDP.name=船舶模拟驾驶舱'）;
（2）单层EXISTS嵌套查询查询没有购买过“海大”制造的“船舶模拟驾驶舱”的顾客。
SELECTcustkey,name
FROMCustomer
WHERENOTEXISTS（SELECTO.custkey
FROMOrdersO,LineitemL,PartSuppPS,PartPWHEREC.cuskey=O.custkeyAND
O.orderkey=L.orderkeyANDL.partkey=PS.partkeyANDL.suppkey=PS.suppkeyANDPS.partkey=P.partkeyANDp.mfgr='海大'ANDP.name=船舶模拟驾驶舱'）;
（3）双层EXISTS嵌套查询查询至少购买过顾客“张三”购买过的全部零件的顾客姓名。
SELECTCA.name
FROMCustomerCA
WHERENOTEXISTS
（SELECT*
FROMCustomerCB,OdersOB,LineitemLB
WHERECB.custkey=OB.custkeyAND
OB.orderkey=LB.orderkeyAND
CB.name=张三'AND
NOTEXISTS（SELECT*
FROMOrdersOC,LineitemLC
WHERECA.custkey=LC.custkeyAND
OC.orderkey=LC.orderkeyANDLB.suppkey=LC.suppkeyANDLB.partkey=LC.partkey））;
（4）FROM子句中的嵌套查询查询订单平均金额超过1万元的顾客中的中国籍顾客信息。
SELECTC.*
FROMCustomerC,（SELECTcustkey
FROMOrders
GROUPBYcustkeyHAVINGAVG（totalprice）>10000）B,NationNWHEREC.custkey=B.custkeyAND
C.nationkey=N.nationkeyANDN.name='中国'；
（5）集合查询（交）查询顾客“张三”和“李四”都订购过的全部零件的信息。
SELECTP.*
FROMCustomerC,OrdersO,LineitemL,PartSuppPS,PartP
WHEREC.custkey=O.custkeyANDO.orderkey=L.orderkeyANDL.suppkey=PS.suppkeyANDL.partkey=PS.partkeyANDPS.partkey=P.partkeyANDC.name#四'；
INTERSECTION
SELECTP.*
FROMCustomerC,OrdersO,LineitemL,PartSuppPS,PartP
WHEREC.cuskey=O.custkeyAND
O.orderkey=L.orderkeyAND
L.partkey=PS.partkeyAND
L.suppkey=PS.suppkeyAND
PS.partkey=P.partkeyAND
C.name=李四'；
（6）集合查询（并）查询顾客“张三”和“李四”订购的全部零件的信息。
SELECTP.*
FROMCustomerC,OrdersO,LineitemL,PartSuppPS,PartP
WHEREC.cuskey=O.custkeyAND
O.orderkey=L.orderkeyAND
L.partkey=PS.partkeyAND
L.suppkey=PS.suppkeyAND
PS.partkey=P.partkeyAND
C.name二张三';
UNION
SELECTP.*
FROMCustomerC,OrdersO,LineitemL,PartSuppPS,PartP
WHEREC.cuskey=O.custkeyAND
O.orderkey=L.orderkeyAND
L.partkey=PS.partkeyAND
L.suppkey=PS.suppkeyAND
PS.partkey=P.partkeyAND
C.name=李四'；
（7）集合查询（差）顾客“张三”订购过而“李四”没订购过的零件的信息。
SELECTP.*
FROMCustomerC,OrdersO,LineitemL,PartSuppPS,PartP
WHEREC.cuskey=O.custkeyAND
O.orderkey=L.orderkeyAND
L.partkey=PS.partkeyAND
L.suppkey=PS.suppkeyAND
PS.partkey=P.partkeyAND
C.name二张三';
EXCEPT
SELECTP.*
FROMCustomerC,OrdersO,LineitemL,PartSuppPS,PartP
WHEREC.cuskey=O.custkeyANDO.orderkey=L.orderkeyANDL.partkey=PS.partkeyAND
L.suppkey=PS.suppkeyANDPS.partkey=P.partkeyAND
C.name=李四'；
4、数据更新实验
（1）INSERT基本语句（插入全部列的数据）插入一条顾客记录，要求每列都给一个合理的值。
INSERTINTOCustomer
VALUES（30,'张三','北京市',40,”,0.00,'Northeast','VIPCustomer'）;
（2）INSERT基本语句（插入部分列的数据）插入一条订单记录，给出必要的几个字段值。
INSERTINTOLineitem（orderkey,Linenumber,partkey,suppkey,quantity,shipdate）VALUES（862,ROUND（RANDOM（）*100,0,479,1,10,'2012-3-6'）;
/*RANDOM（）函数为随机小数生成函数，ROUND（）为四舍五入函数*/
（3）批量数据INSERT语句
1创建一个新的顾客表，把所有中国籍顾客插入到新的顾客表中。
CREATETABLENewCustmoerASSELECT*FROMCustomerWITHNODATA;/*WITHNODATA子句使得SELECT查询只生成一个结果模式，不查询出实际数据*/
INSERTINTONewCustomer/*批量插入SELECT语句查询结果到NewCustomer表中*/
SELECTC.*
FROMCostomerC,NationN
WHEREC.nationkey=N.nationkeyANDN.name='中国'；
2创建一个顾客购物统计表，记录每个顾客及其购物总数和总价等信息。
CREATETABLEShoppingStat（custkeyINTEGER,
quantityREAL,totalpriceREAL）;
INSERTINTOShoppingStat
SELECTC.custkey,Sum（L.quantity）,Sum（O.totalprice）/*对分组后的数据求总和*/
FROMCustomerC,OrderO,LineitemL
WHEREC.custkey=O.custkeyANDO.orderkey=L.orderkey
GROUPBYC.custkey
3倍增零件表的数据，多次重复执行，直到总记录数达到50万为止。
INSERTINTOPart
SELECTpartkey+（SELECTCOUNT（*）FROMPart）,name,mfgr,brand,type,size,container,retailprice,comment
FROMPart;
（4）UPDATE语句（插入部分记录的部分列值）
“金仓集团”供应的所有零件的供应成本价下降10%。
UPDATEPartSupp
SETsupplycost=supplycost*0.9
WHEREsuppkey=（SELECTsuppkey/*找出要修改的那些记录*/
FROMSupplier
WHEREname=金仓集团'）;
（5）UPDATE语句（利用一个表中的数据修改另外一个表中的数据）
利用Part表中的零售价格来修改Lineitem中的extendedprice,其中extendedprice=Part.retailprice*quantity。
UPDATELineitemL
SETL.extendedprice=P.retailprice*L.quantity
FROMPartP
WHEREL.partkey=P.partkey;
/*Lineitem表也可以直接与Part表相连接，而不需通过PartSupp连接*/
（6）DELETE基本语句（删除给定条件的所有记录）删除顾客张三的所有订单记录。
DELECTFROMLineitem/*先删除张三的订单明细记录*/
WHEREorderkeyIN（SELECTorderkey
FROMOrderO,CustomerC
WHEREO.custkey=C.custkeyANDC.name=张三'）;
DELECTFROMOrder/*再删除张三的订单记录*/
WHEREcustkey=（SELECTcustkey
FROMCustomerWHEREname='张三'）;
5、视图
（1）创建视图（省略视图列名）创建一个“海大汽配”供应商供应的零件视图V_DLMU_PartSupp，1要求列出供
应零件的编号、零件名称、可用数量、零售价格、供应价格和备注等信息。
CREATEVIEWV_DLMU_PARTSUPP1AS/*由SELECT?
句目标
升级会员 