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

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

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

3、 实验日期： 5.19 实验成绩： 批改教师： 批改时间： 、实验目的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 数据库，证券分析用户查询要求， 设计各种

5、嵌套查询和集合查询。4、 针对 TPC-H 数据库设计单元主唱入、批量数据插入、修改数据和删除数据的 sql 语句。理解和掌握 insert、 update、 delete 语法结构的各个组成成分，结合嵌 套 sql 子查询，分别设计几个不同形式的插入、修改和删除数据的语句，并调试 成功。5、 针对给定的数据库模式，以及相应的应用要求，创建视图和带 WITH CHECK OPTION 的视图，并验证视图 WITH CHECK OPTION 选项的有效性。理解和掌 握试图消解执行原理，掌握可更新视图和不可更新视图的区别。6、 针对给定的数据库模式和具体应用需求，创建唯一索引、函数索引、复合索 引

6、等；修改索引；删除索引。设计相应的 sql 查询验证索引有效性，学习利用 EXPLAIN 命令分析 sql 查询是否使用了所创建的索引，并能够分析其原因，执 行 sql 查询并估算索引提高查询效率的百分比，要求实验数据达到 10 万条记录 以上的数据量，以便验证所以效果 .三、 实验过程1、数据库定义实验（1） 定义数据库采用中文字符集创建名为 TCHP 的数据库。CREATE DATABASE TPCH ENCODING= GBK;（2） 定义模式在数据库 TPCH 中创建名为 SALES 的模式。Create SCHEMA Sales;3)定义基本表在TPCH数据库的Sales模式中创建8

7、个基本表。/*设置当前会话的搜索路径为sales模式、public模式，基本表就会自动创建 在sales模式下。*/SET SEARCH_PATH TO Sales,Public;CREATE TABLE Region( 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 REGI

8、ON(REGIONKEY), comment VARCHAR(152);CREATE TABLE Supplier( suppkey INTEGER PRIMARY KEY, name CHAR(25), address VARCHAR(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(

9、25), /*制造厂 */brand CHAR(10), type VARCHAR (25), size INTEGER, container CHAR(10), retailprice REAL, comment VARCHAR(23);CREATE TABLE PartSupp( partkey INTEGER REFERENCES Part(partkey), suppkey INTEGER REFERENCES Supplier(suppkey), availqty INTEGER, supplycost REAL , comment varchar(199),PRIMARY KEY

10、(parkey,suppkey);CREATE TABLE Costomer( custkey INTEGER PRIMARY KEY , name VARCHAR(25), address VARCHAR(40),nationkey INTEGER REFERENCES Nation(nationkey), phone CHAR(15), acctbal REAL, mktsegment CHAR(10), comment VARCHAR(117);CREATE TABLE Orders( orderkey INTEGER PRIMARY KEY , custkey INTEGER REFE

11、RENCES Customer(custkey), orderstatus CHAR(1), totalprice REAL, orderdate DATE, orderpriority INTEGER, comment VARCHAR(79);CREATE TABLE Lineitem( orderkey INTEGER REFERENCES Order(orderkey), partkey INTEGER REFERENCES Part(partkey), suppkey INTEGER REFERENCES Supplier(suppkey), linenumber INTEGER, q

12、uantity REAL, extendedprice REAL, discount REAL, tax REAL, returnflag CHAR(1), linestatus CHAR(1), shipinstruct CHAR(25), shipmode CHAR(10), comment VARCHAR(44),PRIMARY KEY(orderkey,linenumber),FOREIGN KEY(Partkey,suppkey) REFERENCES PartSupp(partkey,suppkey);2、数据基本查询(1)单表查询(实现投影操作) 查询供应商的名称、地址和联系电话

13、。SELECTE name,address,phone FROMSupplier;(2)单表查询(实现选择操作) 查询最近一周内提交的总价大于 1000 元的订单的编号、顾客编号等订单的所有 信息。SELECT *FROM Sales.Orders WHERE CURRENT_DATE-orderdata1000;(3)不带分组过滤条件的分组统计查询 统计每个顾客的订购金额SELECT C.custkey ,SUM(O.totalprice) FROM customer C,Orders OWHERE C.custkey=O.custkey GROUP BY C.custkey;（4）带分组过

14、滤条件的分组统计查询 查询订单平均金额超过 1000 元的顾客编号及其姓名 SELECT C.custkey， MAX （C.name）FROM Customer C,Orders OWHERE C.custkey=O.custkeyGROUP BY C.custkey;HAVING AVG（ O.totalprice）1000;（ 5）表单自身连接查询 查询与“金仓集团”在同一个国家的供应商编号、名称和地址信息。SELECT F.suppkey,F.name，F.addressFROM Supplier F,Supplier SWHERE F.n ati on key=S. nati on

15、key AND S.n ame=& 仓集团;（6）两表连接查询（普通连接） 查询供应价格大于零售价格的零件名、制造商名、零售价格和供应价格。SELECT P.name,P.mfgr,P.retailprice,PS.supplycostFROM Part P,Partsupp PSWHERE P.retailpricePS.supplycost;（7）两表连接查询（自然连接） 查询供应价格大于零售价格的零件名、制造商名、零售价格和供应价格。SELECT P.name,P.mfgr,P.retailprice,PS.supplycostFROM Part P,Partsupp PSWHERE P

16、.partkey=PS.partkey ANDP.retailpricePS.supplycost;（8）三表连接查询 查询顾客“苏举库”订购的订单编号、总价及其订购的零件编号、数量和明细价 格。SELECT O.orderkey,O.totalprice,L.partkey,L.quantity,L.extendedpriceFROM Custom C,Orders O,Lineitem LWHERE C.custkey=O.custkey AND O.orderkey=L.orderkey AND C.n ame=苏举库;3、数据高级查询实验（1） IN 嵌套查询 查询订购了“海大”制造的

17、“船舶模拟驾驶舱”的顾客。SELECT custkey，nameFROM CustomerWHERE custkey IN （ SELECT O.custkeyFROM Orders O,Lineitme L，PartSupp PS,Part PWHERE O.orderkey=L.orderkey ANDL.partkey=PS.partkey ANDL.suppkey=PS.suppkey ANDPS.partkey=P.partkey ANDP.mfgr=海大AND P.name=M舶模拟驾驶舱）; SELECT custkey,nameFROM CustomerWHERE cuskey

18、 IN （ SELECT O.custkeyFROM Orders O,Lineitem L,Part PWHERE O.orderkey=L.orderkey ANDL.partkey=P.partkey ANDp.mfgr=海大AND P.name=船舶模拟驾驶舱);(2)单层 EXISTS 嵌套查询 查询没有购买过“海大”制造的“船舶模拟驾驶舱”的顾客。SELECT custkey,nameFROM CustomerWHERE NOT EXISTS( SELECT O.custkeyFROM Orders O,Lineitem L,PartSupp PS,Part P WHERE C.c

19、uskey=O.custkey ANDO.orderkey=L.orderkey AND L.partkey=PS.partkey AND L.suppkey=PS.suppkey AND PS.partkey=P.partkey AND p.mfgr=海大AND P.name=船舶模拟驾驶舱);(3)双层 EXISTS 嵌套查询 查询至少购买过顾客“张三”购买过的全部零件的顾客姓名。SELECT CA.nameFROM Customer CAWHERE NOT EXISTS(SELECT *FROM Customer CB,Oders OB,Lineitem LBWHERE CB.custk

20、ey=OB.custkey ANDOB.orderkey=LB.orderkey ANDCB. name=张三ANDNOT EXISTS(SELECT *FROM Orders OC,Lineitem LCWHERE CA.custkey=LC.custkey ANDOC.orderkey=LC.orderkey AND LB.suppkey=LC.suppkey AND LB.partkey=LC.partkey);( 4) FROM 子句中的嵌套查询 查询订单平均金额超过 1 万元的顾客中的中国籍顾客信息。SELECT C.*FROM Customer C,(SELECT custkeyF

21、ROM OrdersGROUP BY custkey HAVING AVG(totalprice)10000) B,Nation N WHERE C.custkey=B.custkey ANDC. natio nkey=N. natio nkey AND N.n ame=中国；(5)集合查询(交) 查询顾客“张三”和“李四”都订购过的全部零件的信息。SELECT P.*FROM Customer C,Orders O,Lineitem L,PartSupp PS,Part PWHERE C.custkey=O.custkey AND O.orderkey=L.orderkey AND L.su

22、ppkey=PS.suppkey AND L.partkey=PS.partkey AND PS.partkey=P.partkey AND C.n ame# 四；INTERSECTIONSELECT P.*FROM Customer C,Orders O,Lineitem L,PartSupp PS,Part PWHERE C.cuskey=O.custkey ANDO.orderkey=L.orderkey ANDL.partkey=PS.partkey ANDL.suppkey=PS.suppkey ANDPS.partkey=P.partkey ANDC.n ame=李四；（6）集合查

23、询（并） 查询顾客“张三”和“李四”订购的全部零件的信息。 SELECT P.*FROM Customer C,Orders O,Lineitem L,PartSupp PS,Part PWHERE C.cuskey=O.custkey ANDO.orderkey=L.orderkey ANDL.partkey=PS.partkey ANDL.suppkey=PS.suppkey ANDPS.partkey=P.partkey ANDC.n ame 二张三;UNIONSELECT P.*FROM Customer C,Orders O,Lineitem L,PartSupp PS,Part P

24、WHERE C.cuskey=O.custkey ANDO.orderkey=L.orderkey ANDL.partkey=PS.partkey ANDL.suppkey=PS.suppkey ANDPS.partkey=P.partkey ANDC.n ame=李四；（7）集合查询（差） 顾客“张三”订购过而“李四”没订购过的零件的信息。 SELECT P.*FROM Customer C,Orders O,Lineitem L,PartSupp PS,Part PWHERE C.cuskey=O.custkey ANDO.orderkey=L.orderkey ANDL.partkey=

25、PS.partkey ANDL.suppkey=PS.suppkey ANDPS.partkey=P.partkey ANDC.n ame 二张三;EXCEPTSELECT P.*FROM Customer C,Orders O,Lineitem L,PartSupp PS,Part PWHERE C.cuskey=O.custkey AND O.orderkey=L.orderkey AND L.partkey=PS.partkey ANDL.suppkey=PS.suppkey AND PS.partkey=P.partkey ANDC.n ame=李四；4、数据更新实验(1)INSERT

26、基本语句(插入全部列的数据) 插入一条顾客记录，要求每列都给一个合理的值。INSERT INTO CustomerVALUES (30,张三,北京市,40,”,0.00,Northeast,VIP Customer);(2)INSERT基本语句(插入部分列的数据) 插入一条订单记录，给出必要的几个字段值。INSERT INTO Lineitem(orderkey,Linenumber,partkey,suppkey,quantity,shipdate) VALUES(862,ROUND(RANDOM()*100,0,479,1,10,2012-3-6);/*RANDOM()函数为随机小数生成函

27、数，ROUND()为四舍五入函数*/(3)批量数据INSERT语句1创建一个新的顾客表，把所有中国籍顾客插入到新的顾客表中。CREATE TABLE NewCustmoer AS SELECT * FROM Customer WITH NO DATA; /*WITH NO DATA 子句使得 SELECT 查询只生成一个结果模式，不查询出实际 数据*/INSERT INTO NewCustomer /* 批 量 插 入 SELECT 语 句 查 询 结 果 到 NewCustomer 表中 */SELECT C.*FROM Costomer C,Nation NWHERE C.n atio n

28、key=N. natio nkey AND N. name=中国；2创建一个顾客购物统计表，记录每个顾客及其购物总数和总价等信息。CREATE TABLE ShoppingStat (custkey INTEGER,quantity REAL, totalprice REAL);INSERT INTO ShoppingStatSELECT C.custkey,Sum(L.quantity),Sum(O.totalprice) /*对分组后的数据求总 和*/FROM Customer C,Order O,Lineitem LWHERE C.custkey=O.custkey AND O.orde

29、rkey=L.orderkeyGROUP BY C.custkey3倍增零件表的数据，多次重复执行，直到总记录数达到 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*0.9WHERE suppkey

30、=（SELECT suppkey /* 找出要修改的那些记录 */FROM SupplierWHERE name=金仓集团）;（ 5） UPDATE 语句（利用一个表中的数据修改另外一个表中的数据）利用 Part表中的零售价格来修改 Lin eitem 中的 exte ndedprice,其中 extendedprice=Part.retailprice*quantity。UPDATE Lineitem LSET L.extendedprice=P.retailprice*L.quantityFROM Part PWHERE L.partkey=P.partkey;/*Lineitem表也可以

31、直接与 Part表相连接，而不需通过 PartSupp连接*/（6）DELETE 基本语句（删除给定条件的所有记录） 删除顾客张三的所有订单记录。DELECT FROM Lineitem /*先删除张三的订单明细记录 */WHERE orderkey IN（SELECT orderkeyFROM Order O,Customer CWHERE O.custkey=C.custkey AND C.n ame=张三）;DELECT FROM Order /*再删除张三的订单记录 */WHERE custkey=（SELECT custkeyFROM Customer WHERE name=张三）;5、 视图（1）创建视图（省略视图列名） 创建一个“海大汽配”供应商供应的零件视图 V_DLMU_PartSupp，1 要求列出供应零件的编号、零件名称、可用数量、零售价格、供应价格和备注等信息。CREATE VIEW V_DLMU_PARTSUPP1 AS /* 由 SELECT?句目标