數(shù)據(jù)庫實驗四SQL數(shù)據(jù)查詢

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

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

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

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

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

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

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

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

9、#39;)（7）使用帶NOT運算的查詢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運算符的字符串匹配查詢。select * from 課程where 課程性質(zhì) like '專業(yè)_'（11）使用LIKE運算符的模板匹配查詢。select*from院系where辦公地點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é)號, 成績select * from 選課order by 學(xué)號, 成績 desc（15）使用TOP顯示前若干記錄的查詢select to

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

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

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

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

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

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

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

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

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