B17041622-洪宗濤-數(shù)據(jù)庫實驗報告

1、實 驗 報 告（1 8 / 1 9 學年 第 二 學期）課程名稱數(shù)據(jù)庫系統(tǒng) 與 設 計實驗名稱數(shù)據(jù)庫系統(tǒng)程序設計與分析實驗時間1 9年5，6月24/31,14/21日指導單位計算機學院/軟件學院指導教師沈蘇彬學生姓名洪宗濤班級學號B學院(系)計算機學院專 業(yè)軟件工程（NIIT）實 驗 報 告實驗名稱數(shù)據(jù)庫系統(tǒng)程序設計與分析指導教師沈蘇彬實驗類型上機實驗學時16實驗時間2019-5-24/31, 6-14/21一、 實驗目的和要求（1）掌握數(shù)據(jù)庫系統(tǒng)創(chuàng)建和數(shù)據(jù)添加的程序設計方法（2）掌握采用SQL編程語言實現(xiàn)關系數(shù)據(jù)庫查詢的程序設計方法（3）掌握對于已有數(shù)據(jù)庫進行有興趣的數(shù)據(jù)查詢設計方法（4）掌

2、握對于復雜數(shù)據(jù)庫查詢的優(yōu)化程序設計方法（5）掌握SQL編程性能監(jiān)測和分析方法要求獨立完成實驗方案的設計、數(shù)據(jù)庫的構建、數(shù)據(jù)錄入、SQL程序的編制、調試和運行；要求獨立完成實驗報告的編寫。 二、實驗環(huán)境(實驗設備)硬件：微機軟件：SQL Server 2008三、實驗原理及內容實驗1、利用教材習題 2.3.1給出的關系模式和習題2.4.1給出的數(shù)據(jù)，建立包括Product、PC、Laptop、Printer四個關系模式的關系數(shù)據(jù)庫，并且錄入給出的數(shù)據(jù)。實驗2、按照教材習題6.2.2要求，編制查詢語句，并且利用實驗1已經建立的關系數(shù)據(jù)庫，執(zhí)行查詢語句，得出查詢結果。在編制查詢語句時，建議參照習題2

3、.4.1編制的相關查詢的關系代數(shù)表達式。實驗3、按照教材習題 6.3.1要求，參照習題2.4.1編制的相關查詢的關系代數(shù)表達式，利用核心數(shù)據(jù)庫查詢語言（即核心SQL，不包括GROUP BY、SUM、AVG、MIN、MAX、和COUNT語句）編制查詢語句，對在實驗1中建立的關系數(shù)據(jù)庫進行查詢，并且記錄查詢結果。實驗4、按照教材習題 6.4.6要求，利用完全數(shù)據(jù)庫查詢語言（完全SQL）編制查詢語句，對在實驗1中建立的關系數(shù)據(jù)庫進行查詢，并且記錄查詢結果。實驗5、基于在實驗1中建立的關系數(shù)據(jù)庫和錄入的數(shù)據(jù)進行數(shù)據(jù)查詢，查找“具有最流行配置的PC型號和制造商”，查找“具有較高性價比的Laptop型號和

4、制造商”，以及至少針對自己設立的一項有興趣的題目，進行數(shù)據(jù)查詢，得出查詢結果，并且監(jiān)測數(shù)據(jù)查詢所花費的時間。實驗6、對實驗5的數(shù)據(jù)查詢的SQL程序進行優(yōu)化，重新執(zhí)行以上的數(shù)據(jù)查詢操作，監(jiān)測優(yōu)化后SQL程序所花費的時間，比較優(yōu)化前后的數(shù)據(jù)查詢的性能差異，分析其中的優(yōu)化原理。參考教材數(shù)據(jù)庫系統(tǒng)基礎教程（第三版）中譯本，Jeffery D. Ullman, Jennifer Widon著，岳麗華，金培權，萬壽紅等譯. 北京：機械工業(yè)出版社，2011.實驗11.Product、PC、Laptop、Printer四個關系模式的關系數(shù)據(jù)庫的建立CREATE TABLE Product(maker CHAR

5、(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, -硬盤容量screen FLOAT, -屏幕尺寸price INT -價格);CREATE TABLE Printer(mod

6、el INT PRIMARY KEY, -型號color CHAR(10), -色彩type CHAR(10), -處理類型price INT -價格);2.數(shù)據(jù)的錄入及錄入數(shù)據(jù)的查看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),(B,1005,pc),(B,1006,pc),(B,2007,laptop),(C,1007,pc),(D,1008,p

7、c),(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,laptop),(H,3006,printer),(H,3007,printer);查看Product表SELECT *F

8、ROM 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,2.20,2048,250,770),(1009,2.00,1024,250,650),(1010,2.80,2048,

9、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,512,60,15.4,549),(2004,2.00,512,60,13.3,1150),(2005,2.16,1024,

10、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,price)VALUES (3001,true,ink-jet,99),(3002,false,laser,239),(30

11、03,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.model AND hd = 30;b.查詢制造商B生產的任意類型的所有產品的型號和價格(SELECT Product

12、.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 Product,PrinterWHERE Product.model = Printer.model AND maker = B

13、)c.查詢只賣筆記本電腦不賣PC的廠商(SELECT maker FROM ProductWHERE type = Laptop)EXCEPT(SELECT makerFROM ProductWHRE type = PC);d.查詢出現(xiàn)在兩種或兩種以上PC中的硬盤的大小SELECT DISTINCT PC1.ram AS ramFROM PC PC1,PC PC2WHERE PC1.model PC2.model AND PC1.ram = PC2.ram;e. 查詢每對具有相同速度和RAM的PC的型號。每對只能列出一次；例如，若（i，j）已被列出，則（j，i）就不能再被列出SELECT PC

14、1.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.model = PC.model AND speed = 3.0)UNION(SELECT maker,Product.mode

15、l 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);方法2SELECT DISTINCT maker FROM ProductWHERE EXISTS(SELECT modelFR

16、OM 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都慢的筆記本電腦方法1SELECT model FROM LaptopWHERE speed ALL(SELECT speed FROM PC);方法2

17、SELECT 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(SELECT model,priceFROM Printer)Temp1WHERE NOT(price ANY(SEL

18、ECT 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 Printer);f.找出RAM容量最小而PC中速度最快的制造商方法1SELECT DISTINCT makerFROM Pr

19、oductWHERE 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的平均速度SELECT AVG(speed) AS agvSpeedFROM PC;b.查詢價格高于$1000的筆記本電腦的平均速度SELE

20、CT 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 Product,PCWHERE Product.model = PC.model AND maker = D)UNION ALL(S

21、ELECT 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,LaptopWHERE Product.model = Laptop.modelGROUP BY maker;g.查詢至少生產三種不同

22、型號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(price) AS avgPriceFROM PCWHERE speed 2.0GROUP BY speed;j.查詢所

23、有生產打印機的廠商生產的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（1） 查找“具有最流行配置的PC型號和制造商”對最流行配置的PC的個人定義：最流行配置不考慮價格，在廠家生產的PC中選出所有型號中speed值出現(xiàn)的最多的型號，ram值出現(xiàn)最多的型號，hd值出現(xiàn)最多的型號，在這三者的基礎上求交集，當然交集的

24、結果可能為空集，但當考慮到實際以及對于數(shù)據(jù)量非常的大庫而言，應該是不會出現(xiàn)空集的。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 (SELECT ram FROM PC GROUP BY ramHAVING COUNT(model) = ALL

25、(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 描述：基本關系代數(shù)描述的每一個表達式都可以用Datalog表達，而擴展的關系代數(shù)中的操作，如分組和聚集，則不能用Datalog表達，在該SQL代碼中涉及擴展關系代數(shù)中的操作，故無法用Datalog表示。（2） 查找“具有較高性價比的Laptop型號和制造商”對較高性價比的Laptop的個人定義

26、：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 描述：基本關系代數(shù)描述的每一個表達式都可以用Datalog表達，而擴展的關系代數(shù)中的操作，如分組和聚集，則不能用Datalog表達，在該SQL代碼中涉及擴展關系

27、代數(shù)中的操作，故無法用Datalog表示。實驗61. 對實驗5中（1）的優(yōu)化比較優(yōu)化前后的數(shù)據(jù)查詢的性能差異，分析其中的優(yōu)化原理：將FROM子句中的一個兩個表改為一個表結合子查詢實現(xiàn)，F(xiàn)ROM之后給出多個表相當于多個表之間進行笛卡兒積操作，非常耗費時間，而且會產生大量的冗余數(shù)據(jù)，特別是對于數(shù)據(jù)非常龐大的數(shù)據(jù)表。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 COUNT(model)FROM PCGROUP