B17041622-洪宗涛-数据库实验报告

1、精选优质文档-倾情为你奉上精选优质文档-倾情为你奉上专心-专注-专业专心-专注-专业精选优质文档-倾情为你奉上专心-专注-专业实 验 报 告（1 8 / 1 9 学年 第 二 学期）课程名称数据库系统 与 设 计实验名称数据库系统程序设计与分析实验时间1 9年5，6月24/31,14/21日指导单位计算机学院/软件学院指导教师沈苏彬学生姓名洪宗涛班级学号B学院(系)计算机学院专 业软件工程（NIIT）实 验 报 告实验名称数据库系统程序设计与分析指导教师沈苏彬实验类型上机实验学时16实验时间2019-5-24/31, 6-14/21实验目的和要求（1）掌握数据库系统创建和数据添加的程序设计方法

2、（2）掌握采用SQL编程语言实现关系数据库查询的程序设计方法（3）掌握对于已有数据库进行有兴趣的数据查询设计方法（4）掌握对于复杂数据库查询的优化程序设计方法（5）掌握SQL编程性能监测和分析方法要求独立完成实验方案的设计、数据库的构建、数据录入、SQL程序的编制、调试和运行；要求独立完成实验报告的编写。 二、实验环境(实验设备)硬件：微机软件：SQL Server 2008三、实验原理及内容实验1、利用教材习题 2.3.1给出的关系模式和习题2.4.1给出的数据，建立包括Product、PC、Laptop、Printer四个关系模式的关系数据库，并且录入给出的数据。实验2、按照教材习题6.2

3、.2要求，编制查询语句，并且利用实验1已经建立的关系数据库，执行查询语句，得出查询结果。在编制查询语句时，建议参照习题2.4.1编制的相关查询的关系代数表达式。实验3、按照教材习题 6.3.1要求，参照习题2.4.1编制的相关查询的关系代数表达式，利用核心数据库查询语言（即核心SQL，不包括GROUP BY、SUM、AVG、MIN、MAX、和COUNT语句）编制查询语句，对在实验1中建立的关系数据库进行查询，并且记录查询结果。实验4、按照教材习题 6.4.6要求，利用完全数据库查询语言（完全SQL）编制查询语句，对在实验1中建立的关系数据库进行查询，并且记录查询结果。实验5、基于在实验1中建立

4、的关系数据库和录入的数据进行数据查询，查找“具有最流行配置的PC型号和制造商”，查找“具有较高性价比的Laptop型号和制造商”，以及至少针对自己设立的一项有兴趣的题目，进行数据查询，得出查询结果，并且监测数据查询所花费的时间。实验6、对实验5的数据查询的SQL程序进行优化，重新执行以上的数据查询操作，监测优化后SQL程序所花费的时间，比较优化前后的数据查询的性能差异，分析其中的优化原理。参考教材数据库系统基础教程（第三版）中译本，Jeffery D. Ullman, Jennifer Widon著，岳丽华，金培权，万寿红等译. 北京：机械工业出版社，2011.实验11.Product、PC、

5、Laptop、Printer四个关系模式的关系数据库的建立CREATE TABLE Product(maker CHAR(10), -制造商model INT PRIMARY KEY, -型号type CHAR(10) -产品类型);CREATE TABLE PC(model INT PRIMARY KEY, -型号speed FLOAT, -速度ram INT, -内存hd INT, -硬盘容量price INT -价格);CREATE TABLE Laptop(model INT PRIMARY KEY, -型号speed FLOAT, -速度ram INT, -内存hd INT, -硬盘

6、容量screen FLOAT, -屏幕尺寸price INT -价格);CREATE TABLE Printer(model INT PRIMARY KEY, -型号color CHAR(10), -色彩type CHAR(10), -处理类型price INT -价格);2.数据的录入及录入数据的查看Product表INSERT INTOProduct(maker,model,type)VALUES (A,1001,pc),(A,1002,pc),(A,1003,pc),(A,2004,laptop),(A,2005,laptop),(A,2006,laptop),(B,1004,pc),(

7、B,1005,pc),(B,1006,pc),(B,2007,laptop),(C,1007,pc),(D,1008,pc),(D,1009,pc),(D,1010,pc),(D,3004,printer),(D,3005,printer),(E,1011,pc),(E,1012,pc),(E,1013,pc), (E,2001,laptop), (E,2002,laptop), (E,2003,laptop),(E,3001,printer),(E,3002,printer),(E,3003,printer),(F,2008,laptop),(F,2009,laptop),(G,2010,l

8、aptop),(H,3006,printer),(H,3007,printer);查看Product表SELECT *FROM Product;PC表INSERT INTOPC(model,speed,ram,hd,price)VALUES (1001,2.66,1024,250,2114),(1002,2.10,512,250,995),(1003,1.42,512,80,478),(1004,2.80,1024,250,649),(1005,3.20,512,250,630),(1006,3.20,1024,320,1049),(1007,2.20,1024,200,510),(1008,

9、2.20,2048,250,770),(1009,2.00,1024,250,650),(1010,2.80,2048,300,770),(1011,1.86,2048,160,959),(1012,2.80,1024,160,649),(1013,3.06,512,80,529);查看PC表SELECT *FROM PC;Laptop表INSERT INTOLaptop(model,speed,ram,hd,screen,price)VALUES (2001,2.00,2048,240,20.1,3673),(2002,1.73,1024,80,17.0,949),(2003,1.80,51

10、2,60,15.4,549),(2004,2.00,512,60,13.3,1150),(2005,2.16,1024,120,17.0,2500),(2006,2.00,2048,80,15.4,1700),(2007,1.83,1024,120,13.3,1429),(2008,1.60,1024,100,15.4,900),(2009,1.60,512,80,14.1,680),(2010,2.00,2048,160,15.4,2300);查看Laptop表SELECT *FROM Laptop;Printer表INSERT INTOPrinter(model,color,type,pr

11、ice)VALUES (3001,true,ink-jet,99),(3002,false,laser,239),(3003,true,laser,899),(3004,true,ink-jet,120),(3005,false,laser,120),(3006,true,ink-jet,100),(3007,true,laser,200);查看Printer表SELECT *FROM Printer;实验2a.查询硬盘容量至少30G的笔记本电脑制造商及该电脑的速度SELECT maker,speedFROM Product,LaptopWHERE Product.model = Laptop

12、.model AND hd = 30;b.查询制造商B生产的任意类型的所有产品的型号和价格(SELECT Product.model AS model,priceFROM Product,PCWHERE Product.model = PC.model AND maker = B)UNION(SELECT Product.model AS model,priceFROM Product,LaptopWHERE Product.model = Laptop.model AND maker = B)UNION(SELECT Product.model AS model,priceFROM Prod

13、uct,PrinterWHERE Product.model = Printer.model AND maker = B)c.查询只卖笔记本电脑不卖PC的厂商(SELECT maker FROM ProductWHERE type = Laptop)EXCEPT(SELECT makerFROM ProductWHRE type = PC);d.查询出现在两种或两种以上PC中的硬盘的大小SELECT DISTINCT PC1.ram AS ramFROM PC PC1,PC PC2WHERE PC1.model PC2.model AND PC1.ram = PC2.ram;e. 查询每对具有

14、相同速度和RAM的PC的型号。每对只能列出一次；例如，若（i，j）已被列出，则（j，i）就不能再被列出SELECT PC1.model AS i,PC2.model AS jFROM PC PC1,PC PC2WHERE PC1.speed = PC2.speed AND PC1.ram = PC2.ram AND PC1.model PC2.model;f.查询生产至少两种速度至少为3.0的电脑（PC或笔记本电脑）的厂商SELECT makerFROM (SELECT maker,Product.model AS model FROM Product,PCWHERE Product.mode

15、l = PC.model AND speed = 3.0)UNION(SELECT maker,Product.model AS model FROM Product,LaptopWHERE Product.model = Laptop.model AND speed = 3.0)TempGROUP BY makerHAVING COUNT(model) = 2;实验3a.找出速度在3.0以上的PC制造商方法1SELECT DISTINCT maker FROM ProductWHERE model IN(SELECT model FROM PC WHERE speed = 3.0);方法2S

16、ELECT DISTINCT maker FROM ProductWHERE EXISTS(SELECT modelFROM PCWHERE PC.speed = 3.0 AND model =Product.model);b.找出价格最高的打印机方法1SELECT modelFROM PrinterWHERE price = ALL(SELECT price FROM Printer);方法2SELECT modelFROM PrinterWHERE NOT( price ANY(SELECT price FROM Printer);c.找出速度比任何一台PC都慢的笔记本电脑方法1SELEC

17、T model FROM LaptopWHERE speed ALL(SELECT speed FROM PC);方法2SELECT modelFROM LaptopWHERE NOT (speed = ALL(SELECT priceFROM (SELECT priceFROM PC)UNION(SELECT priceFROM Laptop)UNION(SELECT priceFROM Printer)Temp2);方法2SELECT model FROM(SELECT model,priceFROM PC)UNION(SELECT model,priceFROM Laptop)UNION

18、(SELECT model,priceFROM Printer)Temp1WHERE NOT(price ANY(SELECT priceFROM (SELECT priceFROM PC)UNION(SELECT priceFROM Laptop)UNION(SELECT priceFROM Printer)Temp2);e.找出价格最低的彩色打印机的制造商方法1SELECT makerFROM ProductWHERE model = (SELECT modelFROM PrinterWHERE color = true AND price ANY(SELECT priceFROM Pri

19、nter);f.找出RAM容量最小而PC中速度最快的制造商方法1SELECT DISTINCT makerFROM ProductWHERE model IN(SELECT modelFROM PCWHERE speed =ALL(SELECT speed FROM PC WHERE ram = ALL(SELECT ramFROM PC);方法2SELECT DISTINCT makerFROM ProductWHERE model IN(SELECT modelFROM PCWHERE NOT(speed ANY(SELECT ramFROM PC);实验4a.查询PC的平均速度SELEC

20、T AVG(speed) AS agvSpeedFROM PC;b.查询价格高于$1000的笔记本电脑的平均速度SELECT AVG(speed) AS agvSpeedFROM LaptopWHERE price 1000;c.查询厂商A生产的PC的平均价格SELECT AVG(price) AS avgPriceFROM Product,PCWHERE Product.model = PC.model AND maker = A;d.查询厂商D生产的PC和笔记本电脑的平均价格SELECT AVG(price) AS avgPriceFROM (SELECT priceFROM Produc

21、t,PCWHERE Product.model = PC.model AND maker = D)UNION ALL(SELECT priceFROM Product,LaptopWHERE Product.model = Laptop.model AND maker = D)Tempe.查询每种不同速度的PC的平均价格SELECT speed,AVG(price) AS avgPriceFROM PCGROUP BY speed;f.查询每家厂商生产的笔记本电脑的屏幕尺寸的平均值SELECT maker,AVG(screen) AS avgScreenFROM Product,LaptopW

22、HERE Product.model = Laptop.modelGROUP BY maker;g.查询至少生产三种不同型号PC的制造商SELECT makerFROM ProductWHERE type = pcGROUP BY makerHAVING count(model) = 3;h.查询每个销售PC的厂商的PC的最高价格SELECT maker,max(price) AS maxPriceFROM Product,PCWHERE Product.model = PC.model GROUP BY maker;i.查询每种高于2.0速度的PC的平均价格SELECT speed,AVG(

23、price) AS avgPriceFROM PCWHERE speed 2.0GROUP BY speed;j.查询所有生产打印机的厂商生产的PC的硬盘容量的平均大小SELECT maker,AVG(hd) AS avgHdFROM Product,PCWHERE Product.model = PC.model AND maker IN (SELECT maker FROM Product WHERE type = printer )GROUP BY maker;实验5查找“具有最流行配置的PC型号和制造商”对最流行配置的PC的个人定义：最流行配置不考虑价格，在厂家生产的PC中选出所有型号

24、中speed值出现的最多的型号，ram值出现最多的型号，hd值出现最多的型号，在这三者的基础上求交集，当然交集的结果可能为空集，但当考虑到实际以及对于数据量非常的大库而言，应该是不会出现空集的。SELECT maker,PC.model AS modelFROM Product,PCWHERE Product.model = PC.modelAND speed IN (SELECT speed FROM PC GROUP BY speedHAVING COUNT(model) = ALL(SELECT COUNT(model)FROM PCGROUP BY speed)AND ram IN (

25、SELECT ram FROM PC GROUP BY ramHAVING COUNT(model) = ALL(SELECT COUNT(model)FROM PCGROUP BY ram)AND hd IN (SELECT hd FROM PC GROUP BY hdHAVING COUNT(model) = ALL(SELECT COUNT(model)FROM PCGROUP BY hd);Datalog 描述：基本关系代数描述的每一个表达式都可以用Datalog表达，而扩展的关系代数中的操作，如分组和聚集，则不能用Datalog表达，在该SQL代码中涉及扩展关系代数中的操作，故无法用

26、Datalog表示。查找“具有较高性价比的Laptop型号和制造商”对较高性价比的Laptop的个人定义：ram = 512 AND hd 150 AND speed = 2.20 AND price 200 AND speed = 2.80 AND price = 250 AND speed = 3.00 AND price 150 AND speed = 2.20 AND price 200 AND speed = 2.80 AND price = 250 AND speed = 3.00 AND price = 3;Datalog 描述：基本关系代数描述的每一个表达式都可以用Datalo

27、g表达，而扩展的关系代数中的操作，如分组和聚集，则不能用Datalog表达，在该SQL代码中涉及扩展关系代数中的操作，故无法用Datalog表示。实验6对实验5中（1）的优化比较优化前后的数据查询的性能差异，分析其中的优化原理：将FROM子句中的一个两个表改为一个表结合子查询实现，FROM之后给出多个表相当于多个表之间进行笛卡儿积操作，非常耗费时间，而且会产生大量的冗余数据，特别是对于数据非常庞大的数据表。SELECT maker,modelFROM ProductWHERE model IN(SELECT model FROM PCWHERE speed IN (SELECT speed FROM PC GROUP BY speedHAVING COUNT(model) = ALL(SELECT COUNT(model)FROM PCGROUP BY speed)INTERSECT(SELECT model FROM PCWHERE ram IN(SELECT ram FROM PC GROUP BY ramHAVING COUNT(model) = ALL(SELECT COUN