數(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)康?熟練掌握 SQL 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 的查詢。 （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è)以上）的連接查詢。 （20）兩個(gè)關(guān)系的廣義笛卡爾積運(yùn)算結(jié)果。 （21）根據(jù)兩個(gè)關(guān)系的廣義笛卡爾

3、積運(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è)字段，要求結(jié)果中既能夠反映目前不是責(zé)任教師的教

4、 師信息，又能反映目前沒有確定責(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í)等信息（分別用嵌套查詢和連接 查詢完成，分析各自的效率） 。 （32）

5、設(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 , FROM , WHERE GROUP BY , HAVING ORDER BY ASC| DESC, ASC | DESC 2、實(shí)驗(yàn)步驟與結(jié)果 (1) 調(diào)出 SQL Server2005軟件的用戶界面，進(jìn)入 SQL Server Management Studio。 (2) 輸入自己編好

7、的程序。 (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 選課 FQiqufryLMil (ldrnioitm f*4臚| X 3rlct discxTicc;牛j左串 A T L irr 口財(cái)L狙 可編輯 T DOT select 學(xué)號(hào)from 選課 fQLQiUtryl iq) (Bo.ininiitrato

8、r (M)* se-B- rrom 遲譚 * 1 1 1 1-00?! 2 CKO 3 D03 4 DO* 5 DOS 6 M6 7 OW 工鈣 fram 教挿 innera xbznjam,煢 J 幣編號(hào)： I 斗1 3 1 LL 7JL 3Dim 2 丁 帳 500陽 3 i EJtS. 200 03 首 4 W0W S S 300 W I E SOOOO T f ? 何丹 9D0即 8 a A 300冏 （33）使用EXISTS的嵌套查詢。 select * from教師 where exists （select * from課程 where課程.責(zé)任教師=教師.教師編號(hào)） 可編輯 機(jī)幣

9、嚼號(hào) 睛 虧1 1 1 】 1 李凡 畀 3Q0DQ 2 2 1 網(wǎng)工 500 00 3 3 2 膽 網(wǎng)工 額00 J A 2 畀 謳覧 5 5 3 曰RI 丸 網(wǎng)宜 300 DO E 3 網(wǎng)言 500 00 7 / 4 何丹 AS 5K00 3 Q 4 女 .怔 XKOO (34) 使用NOT EXISTS的嵌套查詢。 select * from教師 where not exists (select * from課程 where課程.責(zé)任教師=教師.教師編號(hào)) select * from 教師 select count （工資）from 教師 教！I詭SR 譏力I 姓名 性別 職稱 專業(yè) 工

10、姿 1 i 1 1 李凡 畀 網(wǎng)工 300.00 2 2 1 弓煖 男 網(wǎng)工 5M.D0 3 3 2 楚銳 卑 講師 HI 2M.W) 4 4 2 男 網(wǎng)工 5 5 3 自麗 女 軌授 300.00 & 3 女 G00.M 7 7 4 何丹 女 講1帀 5W,M) a a 斗 女 網(wǎng)管 3OT.MJ （36）使用SUM計(jì)算合計(jì)的查詢。 select sum 工資)from 教師 QH-20140719ANBT.ma5ter - dbm渾閔 QH-20140719ANBT.master - dbo SqLQueryUql - (1 select suit. 工資 from教師I rn C3結(jié)果|

11、暑 無列名） HyiS而! （37）次完成求和、計(jì)數(shù)、計(jì)算平均值的查詢。 select * from 教師 compute sum（工資）,avg（工資） 2孵石血 幻方碎（S.t赴皂f岡抽 *11丄魚 1 2 1 1 1 Tn 跟豆 300洌 SDO.DO 2 3 3 2 w: 200.00 4 4 2 晞硯 畑聞 5 3 tin 丈 30000 百 i Kt 女 BJtfr WQ.B 7 7 4 何丹 女 500.00 & g 4 300.1X9 ftUnjvg i pi勵(lì)就 wxnoo （38）查詢所有課程的成績(jī)都大于60分的學(xué)生的平均分最高的學(xué)生信息 select學(xué)號(hào)，avg（成績(jī)）平

12、均成績(jī) from選課 where 成績(jī) 60 group by成績(jī)學(xué)號(hào) order by 成績(jī) desc QLQuryl.Ml - （lfr.dminitrator （S4） 曰呂uluE學(xué)號(hào)宀（成績(jī)平島戍翕 from選課 where 成績(jī) ?roup by成績(jī)”學(xué)號(hào) I- o-dex by成績(jī)業(yè)胡 *rn_ 學(xué)號(hào)平均成緡 : _ Ji:. 1 i 008 I 觀 2 (M7_ 70 （39）查詢數(shù)據(jù)庫課程的成績(jī)大于70分的、所有課程平均分最高的學(xué)生信息。 selecttop（1） * from 學(xué)生 where 學(xué)號(hào) in （select學(xué)號(hào)from 選課join課程 on選課.課程編號(hào)=課

13、程.課程編號(hào) where 成績(jī)70 and課程名稱=數(shù)據(jù)庫） SQLQuerylql - （la.dministrator select （1 fr from 學(xué)生 Vere 學(xué)號(hào)廠巨 （select學(xué)號(hào)from選課join課程 on選課課程編號(hào)=課程課程編號(hào) 朋氏 成贛E 課程名粽數(shù)據(jù)庫） ,1 二結(jié)果出消息1 學(xué)號(hào)院系姓名性別生源狀態(tài)平均咸續(xù) （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ù),ma

14、x（成績(jī)）最高成績(jī),avg（成績(jī)）平均成績(jī),min （成績(jī)）最低成績(jī) from選課 group by 學(xué)號(hào) 字號(hào) 恥藤 1 j 2 10 ID 10 2 碩 2 2Q 酉 20 3 303 2 30 3D 349 4 004 ? 坤 40 4fl 5 DOS 2 M 50 90 6 ow 2 w GD 翎 r / 307 2 70 7D 70 e O 2 BQ （42）查詢至少有1門必修課程考試成績(jī)的每個(gè)學(xué)生的平均成績(jī)。 selectavg（成績(jī)） from選課 where課程編號(hào)in SQLQucryLfql - (Io dmini(tratcr (54)* （select課程編號(hào)from

15、課程 where課程性質(zhì)like %基礎(chǔ)） group by 學(xué)號(hào) select劉5成績(jī)） 仕cm選課 訕丸亡課程編號(hào)何 select塢程編號(hào)froir,課程Tiere課程性質(zhì)加V基礎(chǔ)， I- giaup by 學(xué)號(hào) 亙結(jié)果J消息 jiiirKiiiiiviiiiiiaHiiiiiTi| （43）設(shè)計(jì)1個(gè)使用 COMPUTE?BY和COMPUTE的查詢。 select* from選課order by學(xué)號(hào) compute avg（成績(jī)）,sum（成績(jī)）by 學(xué)號(hào) f*?5 rj :D01 I *1 10 1? 001 m TO 1 :16 1 2 om! e 而廠竝 f*?5 20 a e*g

16、sir- 1 ：20 ： *) 1 筒n 30 2 蚯 cn 1 avg ftrri ：30 ； SO 1 m； e 2 00402 細(xì) SJFl T jnWi 一少占V二銳：勺二 IkxiRTMt 0H- M14O71SUVBnAcfciiin irraster OOMHK) 1 If (44) 設(shè)計(jì)1個(gè)使用COMPUTE的查詢。 select * from 教師 compute sum(工資) SQLQuryl.ql (lo.dminitrator (54)* E select - from 迪.1 市 L corapute sun H跖I 錯(cuò)果匕消息 8WM號(hào) 娃茗 性別 職禰 工資 1

17、 ；i! 1 1 1 李凡 卑 網(wǎng)工 300 00 2 2 1 張?jiān)?助敎 網(wǎng)工 500.00 3 3 2 講師 網(wǎng)工 200 00 4 4 2 畀 助埶 網(wǎng)工 40000 5 3 白麗 smoo 6 3 助敎 網(wǎng)管 600 00 7 7 4 何丹 講師 網(wǎng)管 5D0M 8 8 4 湯H 文 教橙 網(wǎng)管 300 00 sum 1i IliodOTl hi iiliii mil ml (45) 設(shè)計(jì)個(gè)使用group?BY的查詢。 select教師編號(hào)，avg工資) from教師 group by教師編號(hào) - QLQueryl.ql - (lodiriiniOrator (54)* 3 select ：刻帀編號(hào)小回工資 土工um教師 -gioup by教師編號(hào)| T f 結(jié)聞竹幅 教師漏號(hào)Ct列名) 1 |l-1 300 000000 2 2500.000000 3 320QOT0QOT 4 440GW0DM ATiTi 6 石伽WWW 7 75M.W0DM 8 8300 000000 三、實(shí)驗(yàn)分析與小結(jié)： （實(shí)驗(yàn)過程中的問題分析、產(chǎn)生的原因以及解決方法；實(shí)驗(yàn)結(jié)果分析；有 待優(yōu)化思路） 實(shí)驗(yàn)過程中出現(xiàn)了很多的錯(cuò)誤，但經(jīng)過自己的不懈努力都解決的了， 也正是通過 這次的實(shí)驗(yàn)學(xué)到了很多，比如：group by