河北工业大学数据库原理及应用实验实验报告

《数据库原理及应用实验》实验报告班级：网络151姓名：徐毅民学号：153299实验1数据库定义与操作语言实验实验1.4数据更新实验1、实验目的熟悉数据库的数据更新操作，能够使用SQL语句对数据库进行数据的插入、删除、修改操作。2、实验内容和要求针对TPC-H数据库设计数据单元组插入、批量数据插入、修改数据和删除数据等SQL语句。理解和掌握INSERTT、UPDATE、和DELETE语法结构的各个组成成分，结合嵌套SQL子查询，分别设计几种不同形式的插入、修改和删除数据的语句，并调试成功。3、实验重点和难点实验重点：插入、修改和删除数据的SQL实验难点：与嵌套SQL子查询相结合的插入、修改和删除数据的SQL语句；利用一个表的数据来插入、修改和删除另一个表的数据。4、实验报告示例（1）INSERT基本语句(插入全部列的数据)插入一条顾客记录，要求每列都给一个合理的值。INSERTINTOCustomerVALUES(30,'张三','北京市',40,'010-51001199',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语句①创建一个新的顾客表，把所有中国籍顾客插入到新的顾客表中。INSERTINTONewCustomer/*批量插入SELECT语句查询结果到NewCustomer表中*/SELECTC.*FROMCostomerC,NationNWHEREC.nationkey=N.nationkeyANDN.name='中国';②创建一个顾客购物统计表，记录每个顾客及其购物总数和总价等信息。CREATETABLEShoppingStat(custkeyINTEGER,quantityREAL,totalpriceREAL);INSERTINTOShoppingStatSELECTC.custkey,Sum(L.quantity),Sum(O.totalprice)/*对分组后的数据求总和*/FROMCustomerC,OrderO,LineitemLWHEREC.custkey=O.custkeyANDO.orderkey=L.orderkeyGROUPBYC.custkey③倍增零件表的数据，多次重复执行，直到总记录数达到50万为止。INSERTINTOPartSELECTpartkey+(SELECTCOUNT(*)FROMPart),name,mfgr,brand,type,size,container,retailprice,commentFROMPart;（4）UPDATE语句（插入部分记录的部分列值）“金仓集团”供应的所有零件的供应成本价下降10%。UPDATEPartSuppSETsupplycost=supplycost*0.9WHEREsuppkey=(SELECTsuppkey/*找出要修改的那些记录*/FROMSupplierWHEREname='金仓集团');（5）UPDATE语句（利用一个表中的数据修改另外一个表中的数据）利用Part表中的零售价格来修改Lineitem中的extendedprice,其中extendedprice=Part.retailprice*quantity。UPDATELineitemLSETL.extendedprice=P.retailprice*L.quantityFROMPartPWHEREL.partkey=P.partkey;/*Lineitem表也可以直接与Part表相连接，而不需通过PartSupp连接*/（6）DELETE基本语句（删除给定条件的所有记录）删除顾客张三的所有订单记录。DELECTFROMLineitem/*先删除张三的订单明细记录*/WHEREorderkeyIN(SELECTorderkeyFROMOrderO,CustomerCWHEREO.custkey=C.custkeyANDC.name='张三');DELECTFROMOrder/*再删除张三的订单记录*/WHEREcustkey=(SELECTcustkeyFROMCustomerWHEREname='张三');实验1.5视图实验1、实验目的熟悉SQL语言有关视图的操作，能够熟练使用SQL语句来创建需要的视图，定义数据库外模式，并能使用所创建的视图实现数据管理。2、实验内容和要求针对给定的数据库模式，以及相应的应用需求，创建视图和带WITHCHECKOPTION的视图，并验证视图WITHCHECKOPTION选项的有效性。理解和掌握视图消除执行原理，掌握可更新视图和不可更新视图的区别。3、实验重点和难点实验重点：创建视图。实验难点：可更新的视图和不可更新的视图之区别，WITHCHECKOPTION的验证。4、实验报告示例（1）创建视图（省略视图列名）创建一个“海大汽配”供应商供应的零件视图V_DLMU_PartSupp1，要求列出供应零件的编号、零件名称、可用数量、零售价格、供应价格和备注等信息。CREATEVIEWV_DLMU_PARTSUPP1AS/*由SELECT子句目标列组成视图属性*/SELECTP.partkey,P.name,PS.availqty,P.retailprice,PS.supplycost,P.commentFROMPartP,PartSuppPS,SupplierSWHEREP.partkey=PS.partkeyANDS.suppkey=PS.suppkeyANDS.name='海大汽配';（2）创建视图（不能省略列名的情况）创建一个视图V_CustAvgOrder,按顾客统计平均每个订单的购买金额和零件数量，要求输出顾客编号、姓名，平均购买金额和平均购买零件数量。CREATEVIEWV_CustAvgOrder(custkey,cname,avgprice,avgquantity)ASSELECTC.custkey,MAX(C.name),AVG(O.totalprice),AVG(L.quantity)FROMCustomerC,OrdersO,LineitemLWHEREC.custkey=O.custkeyANDL.orderkey=O.orderkeyGROUPBYC.custkey;（3）创建视图（WITHCHECKOPTION）使用WITHCHECKOPTION，创建一个“海大汽配”供应商供应的零件视图V_DLMU_PartSupp2，要求列出供应零件的编号、可用数量和供应价格等信息。然后通过该视图分别增加、删除和修改一条“海大汽配”零件供应记录，验证WITHCHECKOPTION是否起作用。CREATEVIEWV_DLMU_PartSupp2ASSELECTpartkey,suppkey,availqty,supplycostFROMPartSuppWHEREsuppkey=(SELECTsuppkeyFROMSupplierWHEREname='海大汽配')WITHCHECKOPTION;INSERTINTOV_DLMU_PartSupp2VALUES(58889,5048,704,77760);UPADTEV_DLMU_PartSupp2SETsupplycost=12WHEREsuppkey=58889;DELETEFROMV_DLMU_PartSupp2WHEREsuppkey=58889;（4）可更新的视图（行列子集视图）使用WITHCHECKOPTION，创建一个“海大汽配”供应商供应的零件视图V_DLMU_PartSupp4，要求列出供应零件的编号、可用数量和供应价格等信息。然后通过该视图分别增加、删除和修改一条“海大汽配”零件供应记录，验证该视图是否是可更新的，并比较上述“（3）创建视图”实验任务与本任务结果有何异同。CREATEVIEWV_DLMU_PartSupp3ASSELECTpartkey,suppkey,availqty,supplycostFROMPartSuppWHEREsuppkey=(SELECTsuppkeyFROMSupplierWHEREname='海大汽配');INSERTINTOV_DLUM_PartSupp3VALUES(58889,5048,704,77760);UPDATEV_DLMU_PartSupp3SETsupplycost=12WHEREsuppkey=58889;DELETEFROMV_DLMU_PartSupp3WHEREsuppkey=58889;（5）可更新的视图INSERTINTOV_CustAvgOrderVALUES(100000,NULL,20,2000);（6）删除视图（RESTRICT/CASCADE）创建顾客订购零件明细视图V_CustOrd,要求列出顾客编号、姓名、购买零件数、金额，然后在该视图的基础上，在创建（2）的视图V_CustAvgOrder,然后使用RESTRICT选项和CASCADE选项删除视图V_CustOrd。CREATEVIEWV_CustOrd(custkey,cname,qty,extprice)ASSELECTC.custkey,C.name,L.quantity,L.extendedpriceFROMCustomerC,OrderO,LineitemLWHEREC.custkey=O.custkeyANDO.orderkey=L.orderkey;CREATEVIEWV_CustAvgOrder(custkey,cname,avgqty,avgprice)ASSELECTcustkey,MAX(cname),AVG(qty),AVG(extprice)FROMV_CustOrd/*在视图V_CustOrd上再创建视图*/GROUPBYcustkey;DROPVIEWV_CustOrdRESTRICT;DROPVIEWV_CustOrdCASCADE;实验1.6索引实验1、实验目的掌握索引设计原则和技巧，能够创建合适的索引以提高数据库查询、统计分析效率。2、实验内容和要求针对给定的数据库模式和具体应用要求，创建唯一索引、函数索引、复合索引等；修改索引；删除索引。设计相应的SQL查询验证索引有效性。学习利用EXPLAIN命令分析SQL查询是否使用了所创建的索引，并能够分析其原因，执行SQL查询并估算索引提高查询效率的百分比。要求实验数据集达到10万条记录以上的数据量，以便验证索引效果。3、实验重点和难点实验重点：创建索引。实验难点：设计SQL查询验证索引有效性。4、实验报告示例（1）创建唯一索引在零件表的零件名称字段上创建唯一索引。CREATEUNIQUEINDEXIdx_part_nameONPart(name);（3）创建复合索引（对两个及两个以上的属性创建索引，称为复合索引）在零件表的制造商和品牌两个字段上创建一个复合索引。CREATEUNIQUEINDEXIdx_part_mfgr_brandONPart(mfgr,brand);（4）修改索引名称修改零件表的名称字段上的索引名。ALTERINDEXIdx_part_name_hashRENAMETOIdx_part_name_hash_new;（5）*验证索引效率创建一个函数TestIndex,自动计算sql查询执行的时间。CREATEFUNCTIONTestIndex(p_part_nameCHAR(55))RETURNINTEGERAS/*自定义函数TestIndex()：输入参数为零件名称，返