第五章 高級(jí)查詢

1、12目標(biāo)使用使用LIKE、BETWEEN、IN進(jìn)行模糊查詢進(jìn)行模糊查詢?cè)诓樵冎惺褂镁酆虾瘮?shù)在查詢中使用聚合函數(shù)使用使用GROUP BY進(jìn)行分組查詢進(jìn)行分組查詢進(jìn)行多表聯(lián)結(jié)查詢進(jìn)行多表聯(lián)結(jié)查詢3模糊查詢LIKE查詢時(shí)，字段中的內(nèi)容并不一定與查詢內(nèi)容完全查詢時(shí)，字段中的內(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 編號(hào) LIKE 008%A,C%可能會(huì)查

2、詢出的編號(hào)值為( )。A、9890ACDB、007_AFFC、008&DCGD、KK8C4通配符通配符解釋示例_一個(gè)字符一個(gè)字符A Like C_%任意長度的字符串任意長度的字符串B Like CO_% 括號(hào)中所指定范圍內(nèi)的一個(gè)字符括號(hào)中所指定范圍內(nèi)的一個(gè)字符C Like 9W01-2不在括號(hào)中所指定范圍內(nèi)的一個(gè)字符不在括號(hào)中所指定范圍內(nèi)的一個(gè)字符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問題成績表中存儲(chǔ)了所有學(xué)員的成績，我想知道：學(xué)員的總成績、平均成績?cè)趺崔k？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ì)所有人所有課程的總成績而是想求每一門課的平均績或者某個(gè)人的所有課的總成績?cè)趺崔k？13分組匯總這三個(gè)數(shù)取平均值第4-6分?jǐn)?shù)取平均值最后三個(gè)數(shù)取平均值有一個(gè)學(xué)員參加考試1415分組查詢GROUP BYSELECT cno, AVG(degree) AS 課程平均成績FROM ScoreGROUP BY cno16利用HAVING篩選結(jié)果表 在實(shí)際使用中，

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

7、查詢3-105號(hào)課程的平均分。號(hào)課程的平均分。 本例主要學(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ù)行到各個(gè)組中，統(tǒng)計(jì)函數(shù)為各個(gè)子句搜集數(shù)據(jù)行到各個(gè)組中，統(tǒng)計(jì)函數(shù)為各個(gè)組計(jì)算統(tǒng)計(jì)值組計(jì)算統(tǒng)計(jì)值HAVING子句去掉不符合其組搜索條件的各組數(shù)據(jù)行子句去掉不符合其組搜索

8、條件的各組數(shù)據(jù)行19多表聯(lián)結(jié)查詢問題學(xué)員內(nèi)部測試成績查詢的每次顯示的都是學(xué)員的編號(hào)信息，因?yàn)樵摫碇兄淮鎯?chǔ)了學(xué)員的編號(hào)；實(shí)際上最好顯示學(xué)員的姓名，而姓名存儲(chǔ)在學(xué)員信息表；如何同時(shí)從這兩個(gè)表中取得數(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子查詢子查詢 子查詢就是包含在另一個(gè)查詢中的查詢。子查詢就是包含在另一個(gè)查詢中的查詢?？梢允褂靡粋€(gè)可以使用一個(gè)SELECT語句返回將由另一個(gè)語句返回將由另一個(gè)SELECT語句使語句使用的記錄，此時(shí)外部查詢稱為用的記錄，此時(shí)外部查詢稱為“父查詢父查詢”，內(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(子查詢子查詢) 外面的查詢稱為父查詢，括號(hào)中嵌入的查詢稱為子查詢外面的查詢稱為父查詢，括號(hào)中嵌入的查詢稱為子查詢 UPDATE、INSERT、DELETE一起使用，語法類似于一起使用，語法類似于SELECT語句語句 將子查詢和比較運(yùn)算符聯(lián)合使用，必須保證子查詢返回的值不能多于將子查詢和比較運(yùn)算符聯(lián)合使用，必須保證子查詢返回的值不能

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

15、數(shù)據(jù)表連接更適合于查看多表的數(shù)據(jù)32使用使用IN或或NOT IN的子查詢的子查詢IN 子查詢用于進(jìn)行一個(gè)給定值是否在子查詢結(jié)果集中的判斷，格子查詢用于進(jìn)行一個(gè)給定值是否在子查詢結(jié)果集中的判斷，格式為：式為： 表達(dá)式表達(dá)式 NOT IN (子查詢子查詢)當(dāng)表達(dá)式與子查詢的結(jié)果表中的某個(gè)值相等時(shí)，當(dāng)表達(dá)式與子查詢的結(jié)果表中的某個(gè)值相等時(shí)，IN謂詞返回謂詞返回TRUE，否則返回否則返回FALSE，若使用了，若使用了NOT，則返回的值剛好相反。，則返回的值剛好相反?！纠纠?】查詢選修了課程號(hào)為查詢選修了課程號(hào)為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)行模糊查詢?cè)?條件不明確的查詢條件不明確的查詢聚合函數(shù)能夠?qū)α猩梢粋€(gè)單一的值，對(duì)于分析和統(tǒng)計(jì)通聚