数据库系统原理实验报告.docx

1、数据库系统原理实验报告学 生 实 验 报 告（理工类）课程名称：数据库系统原理 专业班级： 14软件工程1班 学生学号： 55 学生姓名： 孟祥辉 所属院部： 软件工程学院 指导教师： 麻春艳 20 15 20 16 学年 第 二 学期 金陵科技学院教务处制实验报告书写要求实验报告原则上要求学生手写，要求书写工整。若因课程特点需打印的，要遵照以下字体、字号、间距等的具体要求。纸张一律采用A4的纸张。实验报告书写说明实验报告中一至四项内容为必填项，包括实验目的和要求；实验仪器和设备；实验内容与过程；实验结果与分析。各院部可根据学科特点和实验具体要求增加项目。填写注意事项（1）细致观察，及时、准确

2、、如实记录。（2）准确说明，层次清晰。（3）尽量采用专用术语来说明事物。（4）外文、符号、公式要准确，应使用统一规定的名词和符号。（5）应独立完成实验报告的书写，严禁抄袭、复印，一经发现，以零分论处。实验报告批改说明实验报告的批改要及时、认真、仔细，一律用红色笔批改。实验报告的批改成绩采用百分制，具体评分标准由各院部自行制定。实验报告装订要求实验批改完毕后，任课老师将每门课程的每个实验项目的实验报告以自然班为单位、按学号升序排列，装订成册，并附上一份该门课程的实验大纲。实验项目名称：数据库定义与操作语言 实验学时： 2 同组学生姓名： 孟陈、陈晓雪、季佰军 实验地点： 1318 实验日期： 实

3、验成绩： 批改教师： 批改时间： 一、实验目的1、理解和掌握数据库DDL语言，能够熟练地使用SQL DDL语句创建、修改和删除数据库、模式和基本表。2、掌握SQL册亨徐设计基本规范，熟练运用SQL语言实现数据基本查询，包括单表查询、分组统计查询和连接查询3、掌握SQL嵌套查询和集合查询等, 各种高级查询的设计方法等.4、熟悉数据库的数据更新操作，能够使用sql语句对数据库进行数据的插入、修改、删除操作。5、熟悉sql语言有关系图的操作，能够熟练使用sql语言来创建需要的视图，定义数据库外模式，并能使用所创建的视图实现数据管理。6、掌握所以设计原则和技巧，能够创建合适的索引以提高数据库查询、统计

4、分析效率。二、实验内容和要求1、理解和掌握SQL DDL语句的语法，特别是各种参数的具体含义和使用方法；使用sql语句创建、修改和删除数据库、模式和基本表。掌握sql语句常见语法错误的调试方法。2、针对TPC-H数据库设计各种单表查询sql语句、分组统计查询语句；设计单个表针对自身的连接查询，涉及多个表的连接查询。理解和掌握sql查询语句各个子句的特点和作用，按照sql程序设计规范写出具体的sql查询语句，并调试通过。3、针对TPC-H数据库，证券分析用户查询要求，设计各种嵌套查询和集合查询。4、针对TPC-H数据库设计单元主唱入、批量数据插入、修改数据和删除数据的sql语句。理解和掌握ins

5、ert、update、delete语法结构的各个组成成分，结合嵌套sql子查询，分别设计几个不同形式的插入、修改和删除数据的语句，并调试成功。5、针对给定的数据库模式，以及相应的应用要求，创建视图和带WITH CHECK OPTION的视图，并验证视图WITH CHECK OPTION选项的有效性。理解和掌握试图消解执行原理，掌握可更新视图和不可更新视图的区别。6、针对给定的数据库模式和具体应用需求，创建唯一索引、函数索引、复合索引等；修改索引；删除索引。设计相应的sql查询验证索引有效性，学习利用EXPLAIN命令分析sql查询是否使用了所创建的索引，并能够分析其原因，执行sql查询并估算索

6、引提高查询效率的百分比，要求实验数据达到10万条记录以上的数据量，以便验证所以效果.三、实验过程1、数据库定义实验（1）定义数据库采用中文字符集创建名为TCHP的数据库。CREATE DATABASE TPCH ENCODING=GBK;（2）定义模式在数据库TPCH中创建名为SALES的模式。Create SCHEMA Sales;（3）定义基本表在TPCH数据库的Sales模式中创建8个基本表。/*设置当前会话的搜索路径为sales模式、public模式，基本表就会自动创建在sales模式下。*/SET SEARCH_PATH TO Sales, Public;CREATE TABLE R

7、egion(regionkey INTEGER PRIMARY KEY,name CHAR(25),comment VARCHAR(152);CREATE TABLE Nation(nationkey INTEGER PRIMARY KEY,name CHAR(25),address VARCHAR(40),regionkey INTEGER REFERENCES REGION(REGIONKEY),comment VARCHAR(152);CREATE TABLE Supplier(suppkey INTEGER PRIMARY KEY,name CHAR(25),address VARCH

8、AR(40),nationkey INTEGER REFERENCES Nation(nationkey),phone CHAR(15),acctbal REAL,comment 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,comment VARCHAR(23);CREATE TAB

9、LE PartSupp(partkey INTEGER REFERENCES Part(partkey),suppkey INTEGER REFERENCES Supplier(suppkey),availqty INTEGER,supplycost REAL ,comment varchar(199),PRIMARY KEY (parkey,suppkey);CREATE TABLE Costomer(custkey INTEGER PRIMARY KEY,name VARCHAR(25),address VARCHAR(40),nationkey INTEGER REFERENCES Na

10、tion(nationkey),phone CHAR(15),acctbal REAL,mktsegment CHAR(10),comment VARCHAR(117);CREATE TABLE Orders(orderkey INTEGER PRIMARY KEY,custkey INTEGER REFERENCES Customer(custkey),orderstatus CHAR(1),totalprice REAL,orderdate DATE,orderpriority INTEGER,comment VARCHAR(79);CREATE TABLE Lineitem(orderk

11、ey INTEGER REFERENCES Order(orderkey),partkey INTEGER REFERENCES Part(partkey),suppkey INTEGER REFERENCES Supplier(suppkey),linenumber INTEGER,quantity REAL,extendedprice REAL,discount REAL,tax REAL,returnflag CHAR(1),linestatus CHAR(1),shipinstruct CHAR(25),shipmode CHAR(10),comment VARCHAR(44),PRI

12、MARY KEY(orderkey,linenumber),FOREIGN KEY(Partkey,suppkey) REFERENCES PartSupp(partkey,suppkey);2、数据基本查询（1）单表查询（实现投影操作）查询供应商的名称、地址和联系电话。SELECTE name,address,phone FROMSupplier;（2）单表查询（实现选择操作）查询最近一周内提交的总价大于1000元的订单的编号、顾客编号等订单的所有信息。SELECT *FROM WHERE CURRENT_DATE-orderdata1000;（3）不带分组过滤条件的分组统计查询统计每个顾客

13、的订购金额SELECT ,SUMFROM customer C,Orders OWHERE =GROUP BY ;（4）带分组过滤条件的分组统计查询查询订单平均金额超过1000元的顾客编号及其姓名SELECT ，MAX（）FROM Customer C,Orders OWHERE =GROUP BY ;HAVING AVG（）1000;（5）表单自身连接查询查询与“金仓集团”在同一个国家的供应商编号、名称和地址信息。SELECT ,，FROM Supplier F,Supplier S WHERE = AND =金仓集团;（6）两表连接查询（普通连接）查询供应价格大于零售价格的零件名、制造商名

14、、零售价格和供应价格。SELECT ,FROM Part P,Partsupp PSWHERE ;（7）两表连接查询（自然连接）查询供应价格大于零售价格的零件名、制造商名、零售价格和供应价格。SELECT ,FROM Part P,Partsupp PSWHERE = AND ;（8）三表连接查询查询顾客“苏举库”订购的订单编号、总价及其订购的零件编号、数量和明细价格。SELECT ,FROM Custom C,Orders O,Lineitem LWHERE = AND = AND =苏举库;3、数据高级查询实验（1）IN嵌套查询查询订购了“海大”制造的“船舶模拟驾驶舱”的顾客。SELECT

15、 custkey，nameFROM CustomerWHERE custkey IN ( SELECT FROM Orders O,Lineitme L，PartSupp PS,Part P WHERE = AND = AND = AND = AND =海大 AND =船舶模拟驾驶舱);SELECT custkey,nameFROM CustomerWHERE cuskey IN ( SELECT FROM Orders O,Lineitem L,Part P WHERE = AND = AND =海大 AND =船舶模拟驾驶舱);（2）单层EXISTS嵌套查询查询没有购买过“海大”制造的“船

16、舶模拟驾驶舱”的顾客。SELECT custkey,nameFROM CustomerWHERE NOT EXISTS( SELECT FROM Orders O,Lineitem L,PartSupp PS,Part P WHERE = AND = AND = AND = AND = AND =海大 AND =船舶模拟驾驶舱);（3）双层EXISTS嵌套查询查询至少购买过顾客“张三”购买过的全部零件的顾客姓名。SELECT FROM Customer CAWHERE NOT EXISTS （SELECT * FROM Customer CB,Oders OB,Lineitem LB WHER

17、E = AND = AND =张三 AND NOT EXISTS(SELECT * FROM Orders OC,Lineitem LC WHERE = AND = AND = AND =);（4）FROM子句中的嵌套查询查询订单平均金额超过1万元的顾客中的中国籍顾客信息。SELECT C.*FROM Customer C,(SELECT custkey FROM Orders GROUP BY custkey HAVING AVG(totalprice)10000) B,Nation N WHERE = AND = AND =中国;（5）集合查询（交）查询顾客“张三”和“李四”都订购过的全部

18、零件的信息。SELECT P.*FROM Customer C,Orders O,Lineitem L,PartSupp PS,Part P WHERE = AND = AND = AND = AND = AND =李四;INTERSECTION SELECT P.*FROM Customer C,Orders O,Lineitem L,PartSupp PS,Part P WHERE = AND = AND = AND = AND = AND =李四;（6）集合查询（并）查询顾客“张三”和“李四”订购的全部零件的信息。SELECT P.*FROM Customer C,Orders O,Li

19、neitem L,PartSupp PS,Part P WHERE = AND = AND = AND = AND = AND =张三;UNIONSELECT P.*FROM Customer C,Orders O,Lineitem L,PartSupp PS,Part P WHERE = AND = AND = AND = AND = AND =李四;（7）集合查询（差）顾客“张三”订购过而“李四”没订购过的零件的信息。SELECT P.*FROM Customer C,Orders O,Lineitem L,PartSupp PS,Part P WHERE = AND = AND = AN

20、D = AND = AND =张三;EXCEPTSELECT P.*FROM Customer C,Orders O,Lineitem L,PartSupp PS,Part P WHERE = AND = AND = AND = AND = AND =李四;4、数据更新实验（1）INSERT基本语句(插入全部列的数据)插入一条顾客记录，要求每列都给一个合理的值。INSERT INTO CustomerVALUES (30,张三,北京市,40,0,Northeast,VIP Customer);（2）INSERT基本语句（插入部分列的数据） 插入一条订单记录，给出必要的几个字段值。INSERT

21、INTO Lineitem(orderkey,Linenumber,partkey,suppkey,quantity,shipdate)VALUES(862,ROUND(RANDOM()*100,0,479,1,10,2012-3-6);/*RANDOM()函数为随机小数生成函数，ROUND()为四舍五入函数*/（3）批量数据INSERT语句 创建一个新的顾客表，把所有中国籍顾客插入到新的顾客表中。CREATE TABLE NewCustmoer AS SELECT * FROM Customer WITH NO DATA;/*WITH NO DATA子句使得SELECT查询只生成一个结果模式

22、，不查询出实际数据*/INSERT INTO NewCustomer /*批量插入SELECT 语句查询结果到NewCustomer表中*/SELECT C.*FROM Costomer C,Nation NWHERE = AND =中国; 创建一个顾客购物统计表，记录每个顾客及其购物总数和总价等信息。CREATE TABLE ShoppingStat(custkey INTEGER, quantity REAL, totalprice REAL);INSERT INTO ShoppingStatSELECT ,Sum,Sum /*对分组后的数据求总和*/FROM Customer C,Ord

23、er O,Lineitem LWHERE = AND =GROUP BY 倍增零件表的数据，多次重复执行，直到总记录数达到50万为止。INSERT INTO PartSELECT partkey+(SELECT COUNT(*) FROM Part),name,mfgr,brand,type,size,container,retailprice,commentFROM Part;（4）UPDATE语句（插入部分记录的部分列值） “金仓集团”供应的所有零件的供应成本价下降10%。UPDATE PartSuppSET supplycost=supplycost*WHERE suppkey=(SEL

24、ECT suppkey /*找出要修改的那些记录*/ FROM Supplier WHERE name=金仓集团);（5）UPDATE语句（利用一个表中的数据修改另外一个表中的数据） 利用Part表中的零售价格来修改Lineitem中的extendedprice,其中extendedprice=*quantity。UPDATE Lineitem LSET =*FROM Part PWHERE =;/*Lineitem表也可以直接与Part表相连接，而不需通过PartSupp连接*/（6）DELETE基本语句（删除给定条件的所有记录） 删除顾客张三的所有订单记录。DELECT FROM Line

25、item /*先删除张三的订单明细记录*/WHERE orderkey IN(SELECT orderkey FROM Order O,Customer C WHERE = AND =张三);DELECT FROM Order /*再删除张三的订单记录*/WHERE custkey=(SELECT custkey FROM Customer WHERE name=张三);5、 视图（1）创建视图（省略视图列名） 创建一个“海大汽配”供应商供应的零件视图V_DLMU_PartSupp1，要求列出供应零件的编号、零件名称、可用数量、零售价格、供应价格和备注等信息。CREATE VIEW V_DLM

26、U_PARTSUPP1 AS /*由SELECT子句目标列组成视图属性*/SELECT ,FROM Part P,PartSupp PS,Supplier SWHERE = AND = AND =海大汽配;（2）创建视图（不能省略列名的情况） 创建一个视图V_CustAvgOrder,按顾客统计平均每个订单的购买金额和零件数量，要求输出 顾客编号、姓名，平均购买金额和平均购买零件数量。CREATE VIEW V_CustAvgOrder(custkey,cname,avgprice,avgquantity) ASSELECT ,MAX,AVG,AVGFROM Customer C,Orders

27、 O,Lineitem LWHERE = AND =GROUP BY ;（3）创建视图（WITH CHECK OPTION） 使用WITH CHECK OPTION，创建一个“海大汽配”供应商供应的零件视图V_DLMU_PartSupp2，要求列出供应零件的编号、可用数量和供应价格等信息。然后通过该视图分别增加、删除和修改一条“海大汽配”零件供应记录，验证WITH CHECK OPTION是否起作用。CREATE VIEW V_DLMU_PartSupp2ASSELECT partkey,suppkey,availqty,supplycostFROM PartSuppWHERE suppkey

28、=(SELECT suppkey FROM Supplier WHERE name=海大汽配)WITH CHECK OPTION;INSERT INTO V_DLMU_PartSupp2VALUES (58889,5048,704,77760);UPADTE V_DLMU_PartSupp2SET supplycost=12WHERE suppkey=58889;DELETE FROM V_DLMU_PartSupp2WHERE suppkey=58889;（4）可更新的视图（行列子集视图） 使用WITH CHECK OPTION，创建一个“海大汽配”供应商供应的零件视图V_DLMU_Part

29、Supp4，要求列出供应零件的编号、可用数量和供应价格等信息。然后通过该视图分别增加、删除和修改一条“海大汽配”零件供应记录，验证该视图是否是可更新的，并比较上述“（3）创建视图”实验任务与本任务结果有何异同。CREATE VIEW V_DLMU_PartSupp3AS SELECT partkey,suppkey,availqty,supplycostFROM PartSuppWHERE suppkey=(SELECT suppkeyFROM SupplierWHERE name=海大汽配); INSERT INTO V_DLUM_PartSupp3VALUES(58889,5048,704,77760);UPDATE V_DLMU_PartSupp3SET supplycost=12WHERE suppkey=58889;DELETE FROM V_DLMU_PartSupp3WHERE suppkey=58889;（5）可更新的视图INSERT INTO V_CustAvgOrderVALUES(100000,NULL,20,2000);（6）删除视图（RESTRICT/CASCADE） 创建顾客订购零件明细视图V_CustOrd,要求列出顾客编号、姓名、购买零件数、金额，然后在该视图的基础上，在