第五章 高級查詢

1、12目標(biāo)使用使用LIKE、BETWEEN、IN進(jìn)行模糊查詢進(jìn)行模糊查詢在查詢中使用聚合函數(shù)在查詢中使用聚合函數(shù)使用使用GROUP BY進(jìn)行分組查詢進(jìn)行分組查詢進(jìn)行多表聯(lián)結(jié)查詢進(jìn)行多表聯(lián)結(jié)查詢3模糊查詢LIKE查詢時，字段中的內(nèi)容并不一定與查詢內(nèi)容完全查詢時，字段中的內(nèi)容并不一定與查詢內(nèi)容完全匹配，只要字段中匹配，只要字段中含有含有這些內(nèi)容這些內(nèi)容SELECT SName AS 姓名 FROM Students WHERE SName LIKE 張%張果老張果老張飛張飛張揚(yáng)出去張揚(yáng)出去思考：以下的SQL語句：SELECT * FROM 數(shù)據(jù)表 WHERE 編號 LIKE 008%A,C%可能會查

2、詢出的編號值為( )。A、9890ACDB、007_AFFC、008&DCGD、KK8C4通配符通配符解釋示例_一個字符一個字符A Like C_%任意長度的字符串任意長度的字符串B Like CO_% 括號中所指定范圍內(nèi)的一個字符括號中所指定范圍內(nèi)的一個字符C Like 9W01-2不在括號中所指定范圍內(nèi)的一個字符不在括號中所指定范圍內(nèi)的一個字符D Like %A-D1-25數(shù)據(jù)庫表記錄6模糊查詢IS NULL把某一字段中內(nèi)容為把某一字段中內(nèi)容為空空的記錄查詢出來的記錄查詢出來SELECT SName As 姓名，SAddress AS 地址 FROM Student WHERE S

3、Address IS NULL張果老NULL李尋歡NULL令狐沖NULL程靈素NULL7模糊查詢BETWEEN把某一字段中內(nèi)容把某一字段中內(nèi)容在特定范圍內(nèi)在特定范圍內(nèi)的記錄查詢出來的記錄查詢出來SELECT Sno, degree FROM SCore WHERE degree BETWEEN 60 AND 80101641039210392105888模糊查詢IN把某一字段中內(nèi)容與所列出的把某一字段中內(nèi)容與所列出的查詢內(nèi)容列表查詢內(nèi)容列表匹配匹配的記錄查詢出來的記錄查詢出來SELECT SName AS 學(xué)員姓名,SAddress As 地址 FROM Student WHERE SAddr

4、ess IN (北京,廣州,上海)李揚(yáng)廣州廣州于紫電上海上海李青霜北京北京司馬弓上海上海9問題成績表中存儲了所有學(xué)員的成績，我想知道：學(xué)員的總成績、平均成績怎么辦？10聚合函數(shù)-1SUMAVGSELECT SUM(degree ) FROM Score WHERE sno = 105SELECT SUM(degree ) ,sno FROM Score WHERE sno = 105SELECT AVG(degree ) AS 平均成績 From Score WHERE degree =6011聚合函數(shù)-2MAX、MINCOUNTselect avg(degree) as 平均成績, max(

5、degree) as 最高分, min (degree) as 最低分 From Score Where degree =60SELECT COUNT (*) AS 及格人數(shù) From Score WHERE degree=6012問題如果不是統(tǒng)計(jì)所有人所有課程的總成績而是想求每一門課的平均績或者某個人的所有課的總成績怎么辦？13分組匯總這三個數(shù)取平均值第4-6分?jǐn)?shù)取平均值最后三個數(shù)取平均值有一個學(xué)員參加考試1415分組查詢GROUP BYSELECT cno, AVG(degree) AS 課程平均成績FROM ScoreGROUP BY cno16利用HAVING篩選結(jié)果表 在實(shí)際使用中，

6、往往還要對分組后的結(jié)果按某種條在實(shí)際使用中，往往還要對分組后的結(jié)果按某種條件再進(jìn)行篩選，而只輸出滿足用戶指定條件的記錄。在件再進(jìn)行篩選，而只輸出滿足用戶指定條件的記錄。在SQL中，中，HAVING子句能完成此功能。子句能完成此功能。 “WHERE”子句與子句與“HAVING”子句的區(qū)別在于作用對子句的區(qū)別在于作用對象不同：象不同：1、WHERE子句的作用對象是表，是從表中選擇出滿子句的作用對象是表，是從表中選擇出滿足篩選條件的記錄。足篩選條件的記錄。2、HAVING子句的作用對象是組，是從組中選擇出滿子句的作用對象是組，是從組中選擇出滿足篩選條件的記錄。足篩選條件的記錄。17【例【例5】查詢】

7、查詢3-105號課程的平均分。號課程的平均分。 本例主要學(xué)習(xí)有關(guān)本例主要學(xué)習(xí)有關(guān)GROUP BY子句的表達(dá)式書子句的表達(dá)式書寫格式。寫格式。 select cno,avg(degree) from score group by cno having cno=3-10518分組查詢WHEREGROUP BYHAVINGWHERE子句從數(shù)據(jù)源中去掉不符合其搜索條件的數(shù)據(jù)子句從數(shù)據(jù)源中去掉不符合其搜索條件的數(shù)據(jù)GROUP BY子句搜集數(shù)據(jù)行到各個組中，統(tǒng)計(jì)函數(shù)為各個子句搜集數(shù)據(jù)行到各個組中，統(tǒng)計(jì)函數(shù)為各個組計(jì)算統(tǒng)計(jì)值組計(jì)算統(tǒng)計(jì)值HAVING子句去掉不符合其組搜索條件的各組數(shù)據(jù)行子句去掉不符合其組搜索

8、條件的各組數(shù)據(jù)行19多表聯(lián)結(jié)查詢問題學(xué)員內(nèi)部測試成績查詢的每次顯示的都是學(xué)員的編號信息，因?yàn)樵摫碇兄淮鎯α藢W(xué)員的編號；實(shí)際上最好顯示學(xué)員的姓名，而姓名存儲在學(xué)員信息表；如何同時從這兩個表中取得數(shù)據(jù)？20多表聯(lián)結(jié)查詢分類內(nèi)聯(lián)結(jié)內(nèi)聯(lián)結(jié)(INNER JOIN)外聯(lián)結(jié)外聯(lián)結(jié)左外聯(lián)結(jié)左外聯(lián)結(jié) (LEFT JOIN)右外聯(lián)結(jié)右外聯(lián)結(jié) (RIGHT JOIN)完整外聯(lián)結(jié)完整外聯(lián)結(jié)(FULL JOIN)21SELECT S.SName,C.CNo,C.DegreeFrom Score AS CINNER JOIN Student AS SON C.SNo = S.SNoScoreSNodegree122300

9、100100200297896776300381猜一猜：這樣寫，返回的查詢結(jié)果是一樣的嗎？SELECT S.SName,C.CNo,C. degreeFrom Student AS SINNER JOIN Score AS CON C.SNo = S.SNo多表聯(lián)結(jié)查詢內(nèi)聯(lián)結(jié)-1Stundent梅超風(fēng)陳玄風(fēng)陸乘風(fēng)曲靈風(fēng)SNo1234查詢結(jié)果查詢結(jié)果梅超風(fēng)陳玄風(fēng)陳玄風(fēng)陸乘風(fēng)00100100200297896776陸乘風(fēng)0038122多表聯(lián)結(jié)查詢內(nèi)聯(lián)結(jié)-2SELECT Student.SName, Score.CNo, Score.DegreeFROM Student,ScoreWHERE Stu

10、dent.SNo = Score.SNo23多表聯(lián)結(jié)查詢?nèi)砺?lián)結(jié)SELECT S.SName AS 姓名, CS.CName AS 課程, C.Degree AS 成績FROM Student AS S INNER JOIN Score AS C ON (S.SNo = C.SNo)INNER JOIN Course AS CS ON (CS.CNo = C.CNo)24Score122300100100200297896776300381多表聯(lián)結(jié)查詢左外聯(lián)結(jié)Stundents梅超風(fēng)梅超風(fēng)陳玄風(fēng)陳玄風(fēng)陸乘風(fēng)陸乘風(fēng)曲靈風(fēng)曲靈風(fēng)1234查詢結(jié)果查詢結(jié)果梅超風(fēng)梅超風(fēng)陳玄風(fēng)陳玄風(fēng)陳玄風(fēng)陳玄風(fēng)陸乘風(fēng)陸乘

11、風(fēng)00100100200297896776陸乘風(fēng)陸乘風(fēng)00381曲靈風(fēng)曲靈風(fēng)NULLNULLSELECT S.SName,C.Cno,C.DegreeFrom Student AS SLEFT JOIN Score AS CON C.SNo = S.SNo猜一猜：這樣寫，返回的查詢結(jié)果是一樣的嗎？SELECT S.SName,C.CNo,C.Degree From Score AS CLEFT JOIN Student AS SON C.SNo = S.SNo25多表聯(lián)結(jié)查詢右外聯(lián)結(jié)SELECT S.SName,C.CNo,C.Degree From Score AS CRIGHT OUTER

12、 JOIN Student AS SON C.SNo = S.SNo26子查詢子查詢 子查詢就是包含在另一個查詢中的查詢。子查詢就是包含在另一個查詢中的查詢。可以使用一個可以使用一個SELECT語句返回將由另一個語句返回將由另一個SELECT語句使語句使用的記錄，此時外部查詢稱為用的記錄，此時外部查詢稱為“父查詢父查詢”，內(nèi)部查詢稱為，內(nèi)部查詢稱為“子查詢子查詢”。子查詢包括兩種類型：子查詢包括兩種類型：q 嵌套查詢嵌套查詢q 相關(guān)查詢相關(guān)查詢27什么是子查詢 2-1 問題：問題：編寫T-SQL語句，查看年齡比“李軍”大的學(xué)員，要求顯示這些學(xué)員的信息 ？分析：分析： 第一步：求出“李軍”的年齡

13、（即生日比李軍早的學(xué)員,1975-10-21976-2-20）；第二步：利用WHERE語句，篩選年齡比“李軍”大的學(xué)員；學(xué)員信息表學(xué)員信息表28什么是子查詢 2-2采用子查詢實(shí)現(xiàn)采用子查詢實(shí)現(xiàn) SELECT * FROM studentWHERE sbirthday(子查詢子查詢) 外面的查詢稱為父查詢，括號中嵌入的查詢稱為子查詢外面的查詢稱為父查詢，括號中嵌入的查詢稱為子查詢 UPDATE、INSERT、DELETE一起使用，語法類似于一起使用，語法類似于SELECT語句語句 將子查詢和比較運(yùn)算符聯(lián)合使用，必須保證子查詢返回的值不能多于將子查詢和比較運(yùn)算符聯(lián)合使用，必須保證子查詢返回的值不能

14、多于一個一個 2930使用子查詢2-1 問題：問題：查詢成績=60分的學(xué)員。學(xué)員信息表和成績表學(xué)員信息表和成績表31使用子查詢2-2采用子查詢 SELECT sName FROM student WHERE sNo=(SELECT sNo FROM score WHERE degree=60)GO子查詢一般來說，表連接都可以用子查詢替換，但有的子查詢卻不能用表連接一般來說，表連接都可以用子查詢替換，但有的子查詢卻不能用表連接替換替換子查詢比較靈活、方便，常作為增刪改查的篩選條件，適合于操縱一個子查詢比較靈活、方便，常作為增刪改查的篩選條件，適合于操縱一個表的數(shù)據(jù)表的數(shù)據(jù)表連接更適合于查看多表的

15、數(shù)據(jù)表連接更適合于查看多表的數(shù)據(jù)32使用使用IN或或NOT IN的子查詢的子查詢IN 子查詢用于進(jìn)行一個給定值是否在子查詢結(jié)果集中的判斷，格子查詢用于進(jìn)行一個給定值是否在子查詢結(jié)果集中的判斷，格式為：式為： 表達(dá)式表達(dá)式 NOT IN (子查詢子查詢)當(dāng)表達(dá)式與子查詢的結(jié)果表中的某個值相等時，當(dāng)表達(dá)式與子查詢的結(jié)果表中的某個值相等時，IN謂詞返回謂詞返回TRUE，否則返回否則返回FALSE，若使用了，若使用了NOT，則返回的值剛好相反。，則返回的值剛好相反?！纠纠?】查詢選修了課程號為查詢選修了課程號為3-105的學(xué)生情況的學(xué)生情況。 select * from student where

16、sno in (select sno from score where cno=3-105)33EXISTS子查詢 4-2IF EXISTS （子查詢） 語句 EXISTS子查詢的語法：子查詢的語法：如果子查詢的結(jié)果非空，即記錄條數(shù)如果子查詢的結(jié)果非空，即記錄條數(shù)1條以上，則條以上，則EXISTS （子查詢）（子查詢）將返回真（將返回真（true），否則返回假），否則返回假(false) EXISTS也可以作為也可以作為WHERE 語句的子查詢，但一般都能用語句的子查詢，但一般都能用IN子查詢子查詢替換替換34EXISTS子查詢查找選修了查找選修了3-105這門課的學(xué)生姓名這門課的學(xué)生姓名 select sname from student where exists (select * from score where student.sno=score.sno and cno=3-105) 35總結(jié)使用使用LIKE、BETWEEN、IN關(guān)鍵字，能夠進(jìn)行模糊查關(guān)鍵字，能夠進(jìn)行模糊查詢詢 條件不明確的查詢條件不明確的查詢聚合函數(shù)能夠?qū)α猩梢粋€單一的值，對于分析和統(tǒng)計(jì)通聚