《数据库系统原理》实验报告.docx
- 文档编号:7305532
- 上传时间:2023-01-22
- 格式:DOCX
- 页数:49
- 大小:72.38KB
《数据库系统原理》实验报告.docx
《《数据库系统原理》实验报告.docx》由会员分享,可在线阅读,更多相关《《数据库系统原理》实验报告.docx(49页珍藏版)》请在冰豆网上搜索。
《数据库系统原理》实验报告
学生实验报告
(理工类)
课程名称:
数据库系统原理专业班级:
14软件工程1班
学生学号:
**********学生姓名:
孟祥辉
所属院部:
软件工程学院指导教师:
麻春艳
2015——2016学年第二学期
金陵科技学院教务处制
实验报告书写要求
实验报告原则上要求学生手写,要求书写工整。
若因课程特点需打印的,要遵照以下字体、字号、间距等的具体要求。
纸张一律采用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、custkey
GROUPBYC、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='船舶模拟驾驶舱');
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,PartP
WHEREC、cuskey=O、custkeyAND
O、orderkey=L、orderkeyAND
L、partkey=PS、partkeyAND
L、suppkey=PS、suppkeyAND
PS、partkey=P、partkeyAND
p、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、orderkeyAND
LB、suppkey=LC、suppkeyAND
LB、partkey=LC、partkey));
(4)FROM子句中的嵌套查询
查询订单平均金额超过1万元的顾客中的中国籍顾客信息。
SELECTC、*
FROMCustomerC,(SELECTcustkey
FROMOrders
GROUPBYcustkey
HAVINGAVG(totalprice)>10000)B,NationN
WHEREC、custkey=B、custkeyAND
C、nationkey=N、nationkeyANDN、name='中国';
(5)集合查询(交)
查询顾客“张三”与“李四”都订购过的全部零件的信息。
SELECTP、*
FROMCustomerC,OrdersO,LineitemL,PartSuppPS,PartP
WHEREC、custkey=O、custkeyANDO、orderkey=L、orderkeyAND
L、suppkey=PS、suppkeyANDL、partkey=PS、partkeyAND
PS、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、custkeyAND
O、orderkey=L、orderkeyAND
L、partkey=PS、partkeyAND
L、suppkey=PS、suppkeyAND
PS、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语句
① 创建一个新的顾客表,把所有中国籍顾客插入到新的顾客表中。
CREATETABLENewCustmoerASSELECT*FROMCustomerWITHNODATA;
/*WITHNODATA子句使得SELECT查询只生成一个结果模式,不查询出实际数据*/
INSERTINTONewCustomer/*批量插入SELECT语句查询结果到NewCustomer表中*/
SELECTC、*
FROMCostomerC,NationN
WHEREC、nationkey=N、nationkeyANDN、name='中国';
② 创建一个顾客购物统计表,记录每个顾客及其购物总数与总价等信息。
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
③ 倍增零件表的数据,多次重复执行,直到总记录数达到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
FROMCustomer
WHEREname='张三');
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库系统原理 数据库 系统 原理 实验 报告