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

1、數(shù)據(jù)庫實(shí)驗(yàn)四：SQL數(shù)據(jù)查詢一、實(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、 一般簡(jiǎn)單查詢（1） 不帶條件的查詢指定字段（考慮去掉和不去掉重復(fù)值兩種情況）。（2） 查詢某個(gè)表中的所有記錄。（3） 使用單個(gè)條件的簡(jiǎn)單查詢。（4） 使用多個(gè)條件（AND關(guān)系）的查詢。（5） 使用多個(gè)條件（OR關(guān)系）的查詢。（6） 使用多個(gè)條件（混合AND和OR關(guān)系）的查詢。（7） 使用帶NOT運(yùn)算的查詢。（8） 使用BETWEEN···AND·

2、3;·的查詢。（9） 使用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）

3、 多個(gè)關(guān)系（三個(gè)以上）的連接查詢。（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é)果中包括教師姓名、職稱、

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

5、各自的效率）。（32） 設(shè)計(jì)兩個(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） 查詢所有課程的成績(jī)都大于60分的學(xué)生的平均分最高的學(xué)生信息。（39） 查詢數(shù)據(jù)庫課程的成績(jī)大于70分的、所有課程平均分最高的學(xué)生信息。（40） 查詢每個(gè)學(xué)生的平均成績(jī)。（41） 查詢每個(gè)學(xué)生的所有成績(jī)的最高成績(jī)、最低成績(jī)、平均成績(jī)和所考課程的門數(shù)。（42） 查詢至少有10門必修課程考試成績(jī)的每個(gè)學(xué)生的平均成

6、績(jī)。（43） 設(shè)計(jì)2個(gè)使用COMPUTE···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 &l

7、t;列名> ASC | 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、 一般簡(jiǎn)單查詢（1） 不帶條件的查詢指定字段（考慮去掉和不去掉重復(fù)值兩種情況）。select distinct 學(xué)號(hào) from 選課select 學(xué)號(hào) from 選課

8、(2) 查詢某個(gè)表中的所有記錄查詢教師表中的所有記錄(3) 使用單個(gè)條件的簡(jiǎn)單查詢查詢成績(jī)大于50的學(xué)生的學(xué)號(hào)select 學(xué)號(hào) from 選課where 成績(jī)>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

9、 (院系='6' and 生源='淮北')（7）使用帶NOT運(yùn)算的查詢select * from 學(xué)生where not (生源='合肥')（8）使用BETWEENAND的查詢select * from 選課where 成績(jī) between 86 and 93（9）使用NOTBETWEENAND的查詢select * from 選課where 成績(jī) not between 76 and 93（10）使用LIKE運(yùn)算符的字符串匹配查詢。select * from 課程where 課程性質(zhì) like '專業(yè)_'（11）使用LIKE運(yùn)算

10、符的模板匹配查詢。select * from 院系where 辦公地點(diǎn) like '4_1'（12）查詢空值和非空值的select * from 教師where 工資 is nullselect * from 教師where 工資 is not null（13）結(jié)果要求排序的查詢select * from 選課 -升序order by 成績(jī)select * from 選課 -降序order by 成績(jī) desc（14）查詢結(jié)果按多列排序，并分別要求升序和降序的查詢。select * from 選課order by 學(xué)號(hào), 成績(jī)select * from 選課order by 學(xué)

11、號(hào), 成績(jī) desc（15）使用TOP顯示前若干記錄的查詢select top 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),成績(jī)from 學(xué)生 join 選課 on 選課.學(xué)號(hào)= 學(xué)生.學(xué)號(hào)（19）多個(gè)關(guān)系（三個(gè)以上）的連接查詢select * from 學(xué)生,課程,選課w

12、here 學(xué)生.學(xué)號(hào)= 選課.學(xué)號(hào)and 課程.課程編號(hào)= 選課.課程編號(hào)（20）兩個(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),成績(jī)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）查詢教師-課程

13、信息，查詢結(jié)果中包括教師姓名、職稱、課程名稱和課程性質(zhì)等個(gè)字段，要求結(jié)果中列出所有教師信息（即包括不是任何課程責(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è)字段，要求

14、結(jié)果中既能夠反映目前不是責(zé)任教師的教師信息，又能反映目前沒有確定責(zé)任教師的課程信息select 姓名,職稱,課程名稱,課程性質(zhì)from 教師full join 課程on 課程.責(zé)任教師=教師.教師編號(hào)（26）使用IN運(yùn)算的簡(jiǎn)單嵌套查詢。select 教師.姓名from 教師where 教師.教師編號(hào) in (select 課程.責(zé)任教師from 課程where 課程性質(zhì)='專業(yè)基礎(chǔ)')（27）使用NOT IN運(yùn)算的簡(jiǎn)單嵌套查詢select 教師.姓名from 教師where 教師.教師編號(hào) not in (select 課程.責(zé)任教師from 課程where 課程性質(zhì)='

15、;專業(yè)基礎(chǔ)')（28）使用關(guān)系運(yùn)算（如等于）的簡(jiǎn)單嵌套查詢select 教師.姓名from 教師where 教師.教師編號(hào)=(select 課程.責(zé)任教師from 課程where 課程編號(hào)='02')（29）使用ANY或SOME的簡(jiǎn)單嵌套查詢select 姓名from 教師where 工資>= any( select 工資 from 教師 where 教師編號(hào)=20118)select * from 教師（30）使用ALL的簡(jiǎn)單嵌套查詢。select 姓名from 教師where 工資>= all (select 工資from 教師 where 教師編號(hào)=2

16、0115)（31）查詢?cè)合得Q含“計(jì)科系”、職稱為教授、所負(fù)責(zé)教程為專業(yè)基礎(chǔ)的教師姓名、職稱、課程名稱和課程學(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 教師 inner

17、a where out.教師編號(hào)= innera.教師編號(hào) )（33）使用EXISTS的嵌套查詢。select * 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 (

18、工資) from 教師（37）一次完成求和、計(jì)數(shù)、計(jì)算平均值的查詢。select * from 教師compute sum(工資),avg(工資)（38）查詢所有課程的成績(jī)都大于60分的學(xué)生的平均分最高的學(xué)生信息。select 學(xué)號(hào),avg(成績(jī)) 平均成績(jī)from 選課where 成績(jī)>60 group by 成績(jī),學(xué)號(hào)order by 成績(jī) desc（39）查詢數(shù)據(jù)庫課程的成績(jī)大于70分的、所有課程平均分最高的學(xué)生信息。select top(1) * from 學(xué)生where 學(xué)號(hào)in ( select 學(xué)號(hào)from 選課join 課程 on 選課.課程編號(hào)=課程.課程編號(hào) wher

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