數(shù)據(jù)庫實(shí)驗(yàn)四SQL數(shù)據(jù)查詢

1、一、實(shí)驗(yàn)?zāi)康呐c要求：1實(shí)驗(yàn)?zāi)康氖炀氄莆誗QL SELECT語句，能運(yùn)用查詢語句完成各種查詢。實(shí)驗(yàn)內(nèi)容 ：在實(shí)驗(yàn)三和實(shí)驗(yàn)四的基礎(chǔ)上完成查詢操作，將題目中的x, y等具體化：1、 一般簡單查詢（1） 不帶條件的查詢指定字段（考慮去掉和不去掉重復(fù)值兩種情況）。（2） 查詢某個(gè)表中的所有記錄。（3） 使用單個(gè)條件的簡單查詢。（4） 使用多個(gè)條件（AND關(guān)系）的查詢。（5） 使用多個(gè)條件（OR關(guān)系）的查詢。（6） 使用多個(gè)條件（混合AND和OR關(guān)系）的查詢。（7） 使用帶NOT運(yùn)算的查詢。（8） 使用BETWEEN···AND···的查詢。（9

2、） 使用NOT···BETWEEN···AND···的查詢。（10） 使用LIKE運(yùn)算符的字符串匹配查詢。（11） 使用LIKE運(yùn)算符的模板匹配查詢。（12） 查詢空值和非空值的查詢（13） 結(jié)果要求排序的查詢（14） 查詢結(jié)果按多列排序，并分別要求升序和降序的查詢。（15） 使用TOP顯示前若干記錄的查詢。（16） 使用TOP顯示前若干記錄的查詢，如果有滿足條件的并列記錄一并顯示。2、連接查詢（17） 兩個(gè)關(guān)系的連接查詢。（18） 帶其他查詢條件的兩個(gè)關(guān)系的連接查詢。（19） 多個(gè)關(guān)系（三個(gè)以上）的連接

3、查詢。（20） 兩個(gè)關(guān)系的廣義笛卡爾積運(yùn)算結(jié)果。（21） 根據(jù)兩個(gè)關(guān)系的廣義笛卡爾積運(yùn)算結(jié)果得到兩個(gè)關(guān)系進(jìn)行自然連接的結(jié)果。（22） 查詢教師-課程信息，查詢結(jié)果中包括教師姓名、職稱、課程名稱和課程性質(zhì)等4個(gè)字段。（23） 查詢教師-課程信息，查詢結(jié)果中包括教師姓名、職稱、課程名稱和課程性質(zhì)等4個(gè)字段，要求結(jié)果中列出所有教師信息（即包括不是任何課程責(zé)任教師的教師信息）。（24） 查詢教師-課程信息，查詢結(jié)果中包括教師姓名、職稱、課程名稱和課程性質(zhì)等4個(gè)字段，要求結(jié)果中能夠反映目前沒有確定責(zé)任教師的課程信息。（25） 查詢教師-課程信息，查詢結(jié)果中包括教師姓名、職稱、課程名稱和課程性質(zhì)等4個(gè)字段

4、，要求結(jié)果中既能夠反映目前不是責(zé)任教師的教師信息，又能反映目前沒有確定責(zé)任教師的課程信息。提示：（22）（25）分別用內(nèi)連接、左連接、右連接完成，分析它們的效果。3、嵌套查詢（注意使用ANY、ALL和EXISTS）（26） 使用IN運(yùn)算的簡單嵌套查詢。（27） 使用NOT IN運(yùn)算的簡單嵌套查詢。（28） 使用關(guān)系運(yùn)算（如等于）的簡單嵌套查詢。（29） 使用ANY或SOME的簡單嵌套查詢。（30） 使用ALL的簡單嵌套查詢。（31） 查詢院系名稱含“計(jì)算機(jī)”、職稱為教授、所負(fù)責(zé)教程為必修課的教師姓名、職稱、課程名稱和課程學(xué)時(shí)等信息（分別用嵌套查詢和連接查詢完成，分析各自的效率）。（32） 設(shè)計(jì)

5、兩個(gè)內(nèi)外層互相關(guān)的嵌套查詢。（33） 使用EXISTS的嵌套查詢。（34） 使用NOT EXISTS的嵌套查詢。4、匯總和分組查詢（35） 使用COUNT統(tǒng)計(jì)數(shù)目的查詢。（36） 使用SUM計(jì)算合計(jì)的查詢。（37） 一次完成求和、計(jì)數(shù)、計(jì)算平均值的查詢。（38） 查詢所有課程的成績都大于60分的學(xué)生的平均分最高的學(xué)生信息。（39） 查詢數(shù)據(jù)庫課程的成績大于70分的、所有課程平均分最高的學(xué)生信息。（40） 查詢每個(gè)學(xué)生的平均成績。（41） 查詢每個(gè)學(xué)生的所有成績的最高成績、最低成績、平均成績和所考課程的門數(shù)。（42） 查詢至少有10門必修課程考試成績的每個(gè)學(xué)生的平均成績。（43） 設(shè)計(jì)2個(gè)使用C

6、OMPUTE···BY和COMPUTE的查詢。（44） 設(shè)計(jì)1個(gè)使用COMPUTE的查詢。（45） 設(shè)計(jì)1個(gè)使用COMPUTE···BY的查詢。二、實(shí)驗(yàn)內(nèi)容1、實(shí)驗(yàn)原理 SQL SELECT查詢語句的一般格式是：SELECT ALL | DISTINCT < 表達(dá)式>, <表達(dá)式>FROM <表名>,<表名>WHERE <邏輯表達(dá)式>GROUP BY <列名>, <列名>HAVING<謂詞>ORDER BY <列名> ASC |

7、 DESC, <列名> ASC | DESC 2、實(shí)驗(yàn)步驟與結(jié)果（1）調(diào)出SQL Server2005軟件的用戶界面，進(jìn)入SQL Server Management Studio。（2）輸入自己編好的程序。（3）檢查已輸入的程序正確與否。（4）運(yùn)行程序，并分析運(yùn)行結(jié)果是否合理和正確。在運(yùn)行時(shí)要注意當(dāng)輸入不同的數(shù)據(jù)時(shí)所得到的結(jié)果是否正確。（5）輸出程序清單和運(yùn)行結(jié)果。實(shí)驗(yàn)過程及相關(guān)程序：1、 一般簡單查詢（1） 不帶條件的查詢指定字段（考慮去掉和不去掉重復(fù)值兩種情況）。select distinct 學(xué)號(hào) from 選課select 學(xué)號(hào) from 選課(2) 查詢某個(gè)表中的所有記錄

8、查詢教師表中的所有記錄(3) 使用單個(gè)條件的簡單查詢查詢成績大于50的學(xué)生的學(xué)號(hào)select 學(xué)號(hào) from 選課where 成績>50（4） 使用多個(gè)條件（AND關(guān)系）的查詢select工資from教師where工資>200 and工資<500（5） 使用多個(gè)條件（OR關(guān)系）的查詢select工資from教師where工資<200 or工資>500（6） 使用多個(gè)條件（混合AND和OR關(guān)系）的查詢select*from學(xué)生where (院系='2'and生源='淮南')or(院系='6'and生源='淮北&

9、#39;)（7）使用帶NOT運(yùn)算的查詢select * from 學(xué)生where not (生源='合肥')（8）使用BETWEENAND的查詢select * from 選課where 成績 between 86 and 93（9）使用NOTBETWEENAND的查詢select * from 選課where 成績 not between 76 and 93（10）使用LIKE運(yùn)算符的字符串匹配查詢。select * from 課程where 課程性質(zhì) like '專業(yè)_'（11）使用LIKE運(yùn)算符的模板匹配查詢。select*from院系where辦公地點(diǎn)l

10、ike'4_1'（12）查詢空值和非空值的select * from 教師where 工資 is nullselect * from 教師where 工資 is not null（13）結(jié)果要求排序的查詢select * from 選課 -升序order by 成績select * from 選課 -降序order by 成績 desc（14）查詢結(jié)果按多列排序，并分別要求升序和降序的查詢。select * from 選課order by 學(xué)號(hào), 成績select * from 選課order by 學(xué)號(hào), 成績 desc（15）使用TOP顯示前若干記錄的查詢select to

11、p 3 * from 學(xué)生（16）使用TOP顯示前若干記錄的查詢，如果有滿足條件的并列記錄一并顯示select top 3 * from 學(xué)生where 生源='淮南'（17）兩個(gè)關(guān)系的連接查詢。select * from 學(xué)生 cross join 選課（18）帶其他查詢條件的兩個(gè)關(guān)系的連接查詢select 學(xué)生.學(xué)號(hào),院系,姓名,性別,生源,課程編號(hào),成績from 學(xué)生 join 選課on 選課.學(xué)號(hào)= 學(xué)生.學(xué)號(hào)（19）多個(gè)關(guān)系（三個(gè)以上）的連接查詢select * from 學(xué)生,課程,選課where 學(xué)生.學(xué)號(hào)= 選課.學(xué)號(hào)and 課程.課程編號(hào)= 選課.課程編號(hào)（2

12、0）兩個(gè)關(guān)系的廣義笛卡爾積運(yùn)算結(jié)果。select * from 學(xué)生 cross join 選課（21）根據(jù)兩個(gè)關(guān)系的廣義笛卡爾積運(yùn)算結(jié)果得到兩個(gè)關(guān)系進(jìn)行自連接的結(jié)果select e.學(xué)號(hào),院系,姓名,性別,生源,課程編號(hào),成績from 學(xué)生 e join 選課 w on e.學(xué)號(hào)= w.學(xué)號(hào)（22）查詢教師-課程信息，查詢結(jié)果中包括教師姓名、職稱、課程名稱和課程性質(zhì)等個(gè)字段select 姓名,職稱,課程名稱,課程性質(zhì)from 教師 inner join 課程on 教師.教師編號(hào)=課程.責(zé)任教師（23）查詢教師-課程信息，查詢結(jié)果中包括教師姓名、職稱、課程名稱和課程性質(zhì)等個(gè)字段，要求結(jié)果中列出

13、所有教師信息（即包括不是任何課程責(zé)任教師的教師信息）select 姓名,職稱,課程名稱,課程性質(zhì)from 教師 left join 課程on 教師.教師編號(hào)=課程.責(zé)任教師（24）查詢教師-課程信息，查詢結(jié)果中包括教師姓名、職稱、課程名稱和課程性質(zhì)等個(gè)字段，要求結(jié)果中能夠反映目前沒有確定責(zé)任教師的課程信息。select 姓名,職稱,課程名稱,課程性質(zhì)from 教師 right join 課程on 教師.教師編號(hào)=課程.責(zé)任教師（25）查詢教師-課程信息，查詢結(jié)果中包括教師姓名、職稱、課程名稱和課程性質(zhì)等4個(gè)字段，要求結(jié)果中既能夠反映目前不是責(zé)任教師的教師信息，又能反映目前沒有確定責(zé)任教師的課程

14、信息select 姓名,職稱,課程名稱,課程性質(zhì)from 教師fulljoin 課程on 課程.責(zé)任教師=教師.教師編號(hào)（26）使用IN運(yùn)算的簡單嵌套查詢。select 教師.姓名from 教師where 教師.教師編號(hào) in (select 課程.責(zé)任教師from 課程where 課程性質(zhì)='專業(yè)基礎(chǔ)')（27）使用NOT IN運(yùn)算的簡單嵌套查詢select 教師.姓名from 教師where 教師.教師編號(hào) not in (select 課程.責(zé)任教師from 課程where 課程性質(zhì)='專業(yè)基礎(chǔ)')（28）使用關(guān)系運(yùn)算（如等于）的簡單嵌套查詢select

15、教師.姓名from 教師where 教師.教師編號(hào)=(select 課程.責(zé)任教師from 課程where 課程編號(hào)='02')（29）使用ANY或SOME的簡單嵌套查詢select姓名from教師where工資>=any(select工資from教師where教師編號(hào)=20118)select*from教師（30）使用ALL的簡單嵌套查詢。select 姓名from 教師where 工資>= all (select 工資from 教師 where 教師編號(hào)=20115)（31）查詢院系名稱含“計(jì)科系”、職稱為教授、所負(fù)責(zé)教程為專業(yè)基礎(chǔ)的教師姓名、職稱、課程名稱和課

16、程學(xué)時(shí)等信息（分別用嵌套查詢和連接查詢完成，分析各自的效率）select 姓名,課程名稱,學(xué)時(shí)from 教師join 課程on 責(zé)任教師=教師編號(hào) join 院系on 院系.編號(hào)=教師.院系where 名稱='計(jì)科系'and 職稱='教授'（32）設(shè)計(jì)兩個(gè)內(nèi)外層互相關(guān)的嵌套查詢。select * from 教師select 教師編號(hào),姓名,性別,職稱,工資from 教師 out where 工資=(select max(工資) from 教師 innera where out.教師編號(hào)= innera.教師編號(hào) )（33）使用EXISTS的嵌套查詢。select

17、 * from 教師where exists(select * from 課程 where 課程.責(zé)任教師=教師.教師編號(hào))（34）使用NOT EXISTS的嵌套查詢。select * from 教師where not exists(select * from 課程 where 課程.責(zé)任教師=教師.教師編號(hào))（35）使用COUNT統(tǒng)計(jì)數(shù)目的查詢。select * from 教師select count (工資) from 教師（36）使用SUM計(jì)算合計(jì)的查詢。select sum (工資) from 教師（37）一次完成求和、計(jì)數(shù)、計(jì)算平均值的查詢。select * from 教師compu

18、te sum(工資),avg(工資)（38）查詢所有課程的成績都大于60分的學(xué)生的平均分最高的學(xué)生信息。select 學(xué)號(hào),avg(成績) 平均成績from 選課where 成績>60 groupby 成績,學(xué)號(hào)orderby 成績desc（39）查詢數(shù)據(jù)庫課程的成績大于70分的、所有課程平均分最高的學(xué)生信息。selecttop(1)*from 學(xué)生where 學(xué)號(hào)in(select 學(xué)號(hào)from 選課join 課程on 選課.課程編號(hào)=課程.課程編號(hào)where 成績>70 and 課程名稱='數(shù)據(jù)庫')（40）查詢每個(gè)學(xué)生的平均成績。select學(xué)號(hào),avg(成績

19、)as平均成績from選課groupby學(xué)號(hào)（41）查詢每個(gè)學(xué)生的所有成績的最高成績、最低成績、平均成績和所考課程的門數(shù)。select 學(xué)號(hào),count(*)'所選課程數(shù)',max(成績)'最高成績',avg(成績)'平均成績',min(成績)'最低成績'from 選課groupby 學(xué)號(hào)（42）查詢至少有1門必修課程考試成績的每個(gè)學(xué)生的平均成績。selectavg(成績)from 選課where 課程編號(hào)in(select 課程編號(hào)from 課程where 課程性質(zhì)like'%基礎(chǔ)')groupby 學(xué)號(hào)（43）設(shè)計(jì)1個(gè)使用COMPUTEBY和COMPUTE的查詢。s