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

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