數(shù)據(jù)庫：數(shù)據(jù)庫上的查詢-實驗報告.doc

應用數(shù)學學院 學院 信息安全 專業(yè) 班_組、學號 姓名 協(xié)作者 教師評定 實驗題目 數(shù)據(jù)庫的查詢 一、實驗目的與要求實驗目的：掌握SQL Server查詢分析器的使用方法，加深對SQL 和Transact-SQL語言的查詢語句的理解。熟練掌握簡單表的數(shù)據(jù)查詢、數(shù)據(jù)排序和數(shù)據(jù)聯(lián)結(jié)查詢的操作方法；嵌套查詢語句；熟練掌握數(shù)據(jù)查詢中的分組、統(tǒng)計、組合的操作方法。實驗要求：1、簡單查詢操作。該實驗包括投影、選擇條件表達，數(shù)據(jù)排序，使用臨時表等；2、連接查詢操作；3、嵌套查詢。在SQL Server查詢分析器中使用IN、比較符、ANY或ALL和EXISTS操作符進行嵌套查詢操作；4、分組查詢實驗。包括分組條件表達、選擇組條件的表達方法；5、使用函數(shù)查詢。包括統(tǒng)計函數(shù)和分組統(tǒng)計函數(shù)的使用方法；6、組合查詢實驗。二、實驗方案將查詢需求用Transact-SQL語言表示；在SQL Server查詢分析器的輸入?yún)^(qū)中輸入Transact-SQL查詢語句；設置查詢分析器的結(jié)果區(qū)為標準執(zhí)行或網(wǎng)格執(zhí)行方式；發(fā)布執(zhí)行命令，并在結(jié)果區(qū)中查看結(jié)果。三、實驗結(jié)果和數(shù)據(jù)處理首先，我創(chuàng)建了兩個數(shù)據(jù)庫，以供實驗的完成。具體如下：1、學生課程庫（1）表結(jié)構(gòu)（2）表信息（3）關系圖2、圖書借閱表（1）表結(jié)構(gòu)（2）表信息（3）關系圖實驗步驟：1、簡單查詢：（l）用Transact-SQL語句表示下列操作。在學生選課庫中實現(xiàn)其數(shù)據(jù)查詢操作。求數(shù)學系學生的學號和姓名。SELECT 學號,姓名FROM 學生WHERE 所在系=數(shù)學系;求選修了課程的學生學號。SELECT DISTINCT 學號FROM 選課;求選修C1課程的學生學號和成績，并要求對查詢結(jié)果按成績降序排列，如果成績相同則按學號升序排列。SELECT 學號,成績FROM 選課WHERE 課程號=C1ORDER BY 成績 DESC,學號 ASC;獲選修課程C1且成績在8090分之間的學生學號和成績，并將成績乘以系數(shù)0.8 輸出。SELECT 學號,成績*0.8FROM 選課WHERE 課程號=C1 AND 成績 BETWEEN 80 AND 90;求數(shù)學系或計算機系姓張的學生的信息。SELECT *FROM 學生WHERE 所在系 IN (數(shù)學系,計算機系) AND 姓名 LIKE 張%;求缺少了成績的學生的學號和課程號。SELECT 學號,課程號FROM 選課WHERE 成績 IS NULL;（2）在圖書借閱庫中實現(xiàn)其查詢操作：將計算機類的書存入永久的計算機圖書表中，將借書日期在1999年以前的借閱記錄存入臨時的超期借閱表。SELECT *INTO 計算機圖書FROM 圖書WHERE 類別=計算機GOSELECT *INTO #超期借閱FROM 借閱WHERE 借閱日期90;查詢每一門課的間接先行課（即先行課的先行課）。SELECT A.課程號,A.課程名,B.先行課FROM 課程 A,課程 BWHERE A.先行課=B.課程號3、嵌套查詢：用Transact-SQL語句表示，在學生選課庫中實現(xiàn)其數(shù)據(jù)嵌套查詢操作。（1）求選修了高等數(shù)學的學生學號和姓名。SELECT 學號,姓名FROM 學生WHERE 學號 IN (SELECT 學號 FROM 選課 WHERE 課程號 IN (SELECT 課程號 FROM 課程 WHERE 課程名=高等數(shù)學);（2）求C1課程的成績高于張立的學生學號和成績。SELECT 學號,成績FROM 選課WHERE 課程號=C1 AND 成績(SELECT 成績 FROM 選課 WHERE 課程號=C1AND 學號=(SELECT 學號 FROM 學生 WHERE 姓名=張立);（3）求其他系中年齡小于計算機系年齡最大者的學生。SELECT *FROM 學生WHERE 年齡 ANY (SELECT 年齡 FROM 學生 WHERE 所在系=計算機系) AND 所在系計算機系;（4）求其他系中比計算機系學生年齡都小的學生。SELECT *FROM 學生WHERE 年齡 ALL (SELECT 年齡 FROM 學生 WHERE 所在系=計算機系) AND 所在系計算機系;（5）求選修了C1課程的學生姓名。SELECT 姓名FROM 學生WHERE EXISTS (SELECT * FROM 選課 WHERE 學生.學號=學號 AND 課程號=C1);（6）求沒有選修C1課程的學生姓名。SELECT 姓名FROM 學生WHERE NOT EXISTS (SELECT * FROM 選課 WHERE 學生.學號=學號 AND 課程號=C1);（7）查詢選修了全部課程的學生的姓名。SELECT 姓名FROM 學生WHERE NOT EXISTS (SELECT * FROM 課程 WHERE NOT EXISTS (SELECT * FROM 選課 WHERE 學生.學號=學號 AND 課程.課程號=課程號);（8）求選修了學號為“S2”的學生所選修的全部課程的學生學號和姓名。SELECT 學號,姓名FROM 學生WHERE NOT EXISTS (SELECT * FROM 選課 選課1 WHERE 選課1.學號=S2 AND NOT EXISTS (SELECT * FROM 選課 選課2 WHERE 學生.學號=選課2.學號 AND 選課2.課程號=選課1.課程號);4、組合查詢和統(tǒng)計查詢：在圖書借閱庫中實現(xiàn)其查詢操作。（1）查找這樣的圖書類別：要求類別中最高的圖書定價不低于全部按類別分組的圖書平均定價的2倍。SELECT A.*FROM 圖書 AGROUP BY A. 類別HAVING MAX(A.定價)=ALL(SELECT 2 * AVG(B. 定價) FROM 圖書 B GROUP BY B. 類別)（2）求機械工業(yè)出版社出版的各類圖書的平均定價，用GROUP BY表示。SELECT 類別,AVG(定價)平均價FROM 圖書WHERE 出版社=機械工業(yè)出版社GROUP BY 類別ORDER BY 類別 ASC（3）列出計算機類圖書的書號、名稱及價格。最后求出冊數(shù)和總價格。SELECT 書號,書名,定價FROM 圖書WHERE 類別=計算機SELECT COUNT(*) ,SUM(定價)FROM 圖書WHERE 類別=計算機（4）列出計算機類圖書的書號、名稱及價格，并求出各出版社這類書的總價格。最后求出全部冊數(shù)和總價格。select 書號,書名,定價from 圖書where 類別=計算機select count(*) as 冊數(shù) ,sum(定價) as 總價格from 圖書where 類別=計算機group by 出版社order by 出版社 ascselect count(*) as 全部冊數(shù) ,sum(定價) as 總價格from 圖書（5）查詢計算機類和機械工業(yè)出版社出版的圖書。SELECT *FROM 圖書WHERE 類別=計算機UNION ALLSELECT *FROM 圖書WHERE 出版社=機械工業(yè)出版社四、結(jié)論這次的實驗主要應用了數(shù)據(jù)庫的建立以及數(shù)據(jù)庫的查詢等操作，通過對兩個數(shù)據(jù)庫的操作，我對建立數(shù)據(jù)庫和Transact-SQL語句更加熟悉了。實驗過程中有遇到過一些問題，比較低級的錯誤就是標點符號的使用錯誤，有時確實會使用到中文符號，導致無法執(zhí)行命令。還有以下幾個問題我想總結(jié)一下，以便以后能注意一下這幾個方面：第一，在學生選課庫中，學生表已經(jīng)在選課表中有了外鍵，突然我發(fā)現(xiàn)學號信息需要修改，以便能夠有更好的實驗結(jié)果輸出，由于在選課表中已經(jīng)添加了信息，學生表此時是無法修改的，所以我的做法是現(xiàn)在選課表中把外鍵刪掉，然后再在學生表中修改學號，最后再添加外鍵。不過我接著想，如果數(shù)據(jù)庫龐大，涉及的關聯(lián)比較多的話，用這種辦法修改起來豈不是很麻煩？于是我在網(wǎng)上尋找其他更好的解決辦法。有網(wǎng)友說可以直接級聯(lián)更新，還有一邏輯就是保存數(shù)據(jù)到臨時表，然后刪掉原表，再重建表，再復制數(shù)據(jù)到新表。我都在嘗試著。第二，關于臨時表的使用，INTO子句具有創(chuàng)建表的功能，當創(chuàng)建臨時表時，在表名前加“#”代表創(chuàng)建局部臨時表，在表名前加“#”代表創(chuàng)建全局臨時表。局部臨時表只對當前連接有效，僅在當前會話中可見，當前連接斷開時自動刪除；全局臨時表對其它連接也有效，在所有會話中都可見，在當前連接和其他訪問過它的連接都會在斷開時自動刪除。所以關機后，臨時表就不存在了。臨時表缺省使用硬盤，一般來說速度比較慢，但在數(shù)據(jù)量比較大的時候，如果使用表變量，會把內(nèi)存耗盡，反而會降低速度。所以大數(shù)據(jù)量時適合用臨時表。第三，掌握了幾個很常用的語句。ORDER BY是排序子句，INTO是創(chuàng)建表子句，GROUP BY是分組子句，COUNT 是統(tǒng)計記錄數(shù)，“%”是多字符通配，“_”是單字符通配，CHAR是定長字符串，VarCHAR是變長字符串，ASC是升序，DESC是降序，DISTINCT可以使結(jié)果集中，不出現(xiàn)重復項，等等。我覺得只有通過上機實驗操作，才能更好的理解和掌握書上的知識點，并且在解決遇到的問題的過程中，能夠?qū)W到更多更實用的書上沒有提到的知識。我相信在以后對SQL Server的使用和不斷摸索中，我會把它掌握的更好。五、問題與討論（回答思考題）1、在刪除數(shù)據(jù)操作中，對于存在相互牽制關系的兩個表，刪除操作的先后次序有無關系？答：當然有關系。比如在上述的學生選課表中，要刪除年齡為19的學生及選課記錄，需要使用兩條刪除語句，一條用于刪除選課表中的記錄，另一條用于刪除學生表中的學生信息：1、刪除選課表中的記錄：DELETEFROM 選課WHERE 學號 IN （SELECT 學號FROM 學生WHERE 年齡 =19）2、刪除學生表中的學生信息：DELETEFROM 學生WHERE 年齡 =19由此可以看出，由于在刪除選課表是需