聯(lián)接查詢及分組查詢

1、第十一章第十一章聯(lián)接查詢和分組查詢聯(lián)接查詢和分組查詢?nèi)缦碌娜缦碌腡-SQL語句含義是什么？語句含義是什么？Temp表中表中A列為商品名稱，請(qǐng)說出查詢以大寫字列為商品名稱，請(qǐng)說出查詢以大寫字母開頭的所有商品名稱的母開頭的所有商品名稱的T-SQL語句語句聚合函數(shù)聚合函數(shù)COUNT()和和SUM()各代表什么意思？各代表什么意思？回顧和作業(yè)點(diǎn)評(píng)回顧和作業(yè)點(diǎn)評(píng)SELECT Telephone FROM stuWHERE Telephone NOT LIKE 6%SELECT A FROM TempWHERE A LIKE A-Z% 預(yù)習(xí)檢查預(yù)習(xí)檢查分組查詢主要解決什么問題？分組查詢主要解決什么問題？多

2、表聯(lián)接主要解決什么問題？用在什么場合？多表聯(lián)接主要解決什么問題？用在什么場合？統(tǒng)計(jì)學(xué)生考試信息統(tǒng)計(jì)學(xué)生考試信息 從多個(gè)數(shù)據(jù)表中取得學(xué)生考試成績單從多個(gè)數(shù)據(jù)表中取得學(xué)生考試成績單本章任務(wù)本章任務(wù)使用使用GROUP BY進(jìn)行分組查詢進(jìn)行分組查詢掌握多表聯(lián)接查詢掌握多表聯(lián)接查詢本章目標(biāo)本章目標(biāo)學(xué)號(hào)學(xué)號(hào)姓名姓名地址地址所屬年級(jí)所屬年級(jí)001王明全王明全湖南長沙湖南長沙1002張菲張菲湖北宜昌湖北宜昌1003于寄謙于寄謙甘肅天水甘肅天水1004劉國正劉國正山東荷澤山東荷澤1005周接輪周接輪臺(tái)灣新竹臺(tái)灣新竹2006鞏小妹鞏小妹香港龍灣香港龍灣2007鞏大妹鞏大妹香港龍灣香港龍灣2008張明敏張明敏北京順

3、義北京順義3009矛十八矛十八四川棉陽四川棉陽3010張林光張林光陜西臨潼陜西臨潼3需求需求1每年級(jí)的學(xué)生人數(shù)各是多少？每年級(jí)的學(xué)生人數(shù)各是多少？分組查詢?cè)矸纸M查詢?cè)淼谝粚W(xué)期人數(shù)第一學(xué)期人數(shù)第二學(xué)期人數(shù)第二學(xué)期人數(shù)第三學(xué)期人數(shù)第三學(xué)期人數(shù)學(xué)號(hào)學(xué)號(hào)姓名姓名地址地址所屬年級(jí)所屬年級(jí)001王明全王明全湖南長沙湖南長沙1002張菲張菲湖北宜昌湖北宜昌1003于寄謙于寄謙甘肅天水甘肅天水1004劉國正劉國正山東荷澤山東荷澤1005周接輪周接輪臺(tái)灣新竹臺(tái)灣新竹2006鞏小妹鞏小妹香港龍灣香港龍灣2007鞏大妹鞏大妹香港龍灣香港龍灣2008張明敏張明敏北京順義北京順義3009矛十八矛十八四川棉陽四川棉陽

4、3010張林光張林光陜西臨潼陜西臨潼3三三組組分組查詢用法分組查詢用法SELECT FROM WHERE GROUP BY 分組查詢語句分組查詢語句SELECT COUNT(*) AS 人數(shù)人數(shù), SGrade AS 年級(jí)年級(jí)FROM Students GROUP BY SGrade演示示例演示示例1：分組查詢：分組查詢SELECT CourseID, AVG(Score) AS 課程平均成績課程平均成績FROM ScoreGROUP BY CourseID分組查詢解析分組查詢解析2-1對(duì)分組后的每個(gè)組內(nèi)的記錄進(jìn)行一個(gè)聚集，通常用到聚合函數(shù)對(duì)分組后的每個(gè)組內(nèi)的記錄進(jìn)行一個(gè)聚集，通常用到聚合函數(shù)

5、查詢每門課程的平均分查詢每門課程的平均分按照什么進(jìn)行分組？按照什么進(jìn)行分組？分組查詢解析分組查詢解析2-2SELECT CourseID, AVG(Score) AS 課程平均成績課程平均成績FROM ScoreGROUP BY CourseIDORDER BY AVG(Score) 查詢每門課程的平均分，并且按照分?jǐn)?shù)由查詢每門課程的平均分，并且按照分?jǐn)?shù)由低到高的順序排列顯示低到高的順序排列顯示SELECT StudentID, CourseID, AVG(Score) AS 課程平均成績課程平均成績FROM ScoreGROUP BY CourseID思考思考分組查詢所查詢的列分組查詢所查詢

6、的列1、分組列、分組列2、聚合函數(shù)計(jì)算出的列、聚合函數(shù)計(jì)算出的列分析以下的分析以下的T-SQL，結(jié)果會(huì)怎樣？，結(jié)果會(huì)怎樣？練習(xí)練習(xí) 使用分組查詢學(xué)生相關(guān)信息使用分組查詢學(xué)生相關(guān)信息 需求說明需求說明查詢每個(gè)年級(jí)的總學(xué)時(shí)數(shù)，并按照升序排列查詢每個(gè)年級(jí)的總學(xué)時(shí)數(shù)，并按照升序排列查詢每個(gè)參加考試的學(xué)員的平均分查詢每個(gè)參加考試的學(xué)員的平均分查詢每門課程的平均分，并按照降序排列查詢每門課程的平均分，并按照降序排列查詢每個(gè)學(xué)生參加的所有考試的總分，并按照降序查詢每個(gè)學(xué)生參加的所有考試的總分，并按照降序排列排列完成時(shí)間：完成時(shí)間：15分鐘分鐘共性問題集中講解共性問題集中講解n常見調(diào)試問題及解決辦法常見調(diào)試問

7、題及解決辦法n代碼規(guī)范問題代碼規(guī)范問題共性問題集中講解共性問題集中講解需求需求2統(tǒng)計(jì)每學(xué)期男女同學(xué)的人數(shù)，該怎么辦？統(tǒng)計(jì)每學(xué)期男女同學(xué)的人數(shù)，該怎么辦？ 對(duì)學(xué)生進(jìn)行分對(duì)學(xué)生進(jìn)行分組組計(jì)算每組學(xué)生計(jì)算每組學(xué)生的總?cè)藬?shù)的總?cè)藬?shù) 多列分組多列分組2-1年級(jí)分組年級(jí)分組性別分組性別分組多列分組多列分組2-2SELECT COUNT(*) AS 人數(shù)人數(shù),SGrade AS 年級(jí)年級(jí),SSex AS 性別性別 FROM StudentS GROUP BY SGrade,SSex ORDER BY SGrade演示示例演示示例2：多列分組：多列分組ORDER BY 子句在這里的作用？子句在這里的作用？需求

8、需求3如何獲得總?cè)藬?shù)超過如何獲得總?cè)藬?shù)超過15人的年級(jí)？人的年級(jí)？條件限定條件限定分組篩選分組篩選SELECT FROM WHERE GROUP BY HAVINGSELECT COUNT(*) AS 人數(shù)人數(shù),SGrade AS 年級(jí)年級(jí)FROM Students GROUP BY SGradeHAVING COUNT(*)15 分組篩選語句分組篩選語句演示示例演示示例3：分組篩選：分組篩選WHEREGROUP BYHAVINGWHERE子句：子句：用來篩選用來篩選 FROM 子句中指定的操作所產(chǎn)生的行子句中指定的操作所產(chǎn)生的行 GROUP BY子句：子句：用來分組用來分組 WHERE 子句

9、的輸出子句的輸出 HAVING子句：子句：用來從分組的結(jié)果中篩選行用來從分組的結(jié)果中篩選行 WHERE與與HAVING對(duì)比對(duì)比SELECT 部門編號(hào)部門編號(hào), COUNT(*)FROM 員工信息表員工信息表WHERE 工資工資 = 2000GROUP BY 部門編號(hào)部門編號(hào)HAVING COUNT(*) 1思考思考查詢有多個(gè)員工的工資不低于查詢有多個(gè)員工的工資不低于2000 2000 的部門編號(hào)的部門編號(hào) 分析以下的分析以下的T-SQL，其作用是什么？，其作用是什么？指導(dǎo)指導(dǎo)限定條件的分組查詢限定條件的分組查詢2-1 訓(xùn)練要點(diǎn)訓(xùn)練要點(diǎn)GROUP BY子句子句HAVING子句子句聚合函數(shù)聚合函數(shù)

10、需求說明需求說明查詢學(xué)生相關(guān)信息查詢學(xué)生相關(guān)信息查詢考試相關(guān)信息查詢考試相關(guān)信息講解需求說明講解需求說明指導(dǎo)指導(dǎo)限定條件的分組查詢限定條件的分組查詢2-2難點(diǎn)分析難點(diǎn)分析計(jì)算學(xué)生的年齡計(jì)算學(xué)生的年齡 至少一次不及格的信息至少一次不及格的信息 DATEDIFF(dd,BornDate,GETDATE()/365 WHERE StudentResult60GROUP BY StudentNo完成時(shí)間：完成時(shí)間：25分鐘分鐘小結(jié)小結(jié)如果查詢語句如果查詢語句將得到以下的查詢結(jié)果，那么以下的查詢語句將得到以下的查詢結(jié)果，那么以下的查詢語句將輸出哪些信息？將輸出哪些信息？SELECT * FROM ASE

11、LECT A1,B1,MAX(C1) FROM A需求需求4學(xué)號(hào)學(xué)號(hào)姓名姓名年級(jí)年級(jí)001張青裁張青裁1002陳剛陳剛1003蘇三東蘇三東1學(xué)號(hào)學(xué)號(hào)科目科目分?jǐn)?shù)分?jǐn)?shù)001260002270003480如何同時(shí)從這兩個(gè)表中取得數(shù)據(jù)？如何同時(shí)從這兩個(gè)表中取得數(shù)據(jù)？內(nèi)聯(lián)接內(nèi)聯(lián)接(INNER JOIN)外聯(lián)接外聯(lián)接左外聯(lián)接左外聯(lián)接 (LEFT JOIN)右外聯(lián)接右外聯(lián)接 (RIGHT JOIN)常用的多表聯(lián)接查詢常用的多表聯(lián)接查詢內(nèi)聯(lián)接內(nèi)聯(lián)接3-1梅超風(fēng)梅超風(fēng)1陳玄風(fēng)陳玄風(fēng)2陸乘風(fēng)陸乘風(fēng)3曲靈風(fēng)曲靈風(fēng)4100197200189200267300276300381Students Score 梅超風(fēng)梅超

12、風(fēng)00197陳玄風(fēng)陳玄風(fēng)00189陳玄風(fēng)陳玄風(fēng)00267陸乘風(fēng)陸乘風(fēng)00276陸乘風(fēng)陸乘風(fēng)00381查詢結(jié)果查詢結(jié)果 內(nèi)聯(lián)接使用比較運(yùn)算符根據(jù)每個(gè)表的通用列中的內(nèi)聯(lián)接使用比較運(yùn)算符根據(jù)每個(gè)表的通用列中的值匹配兩個(gè)表中的行值匹配兩個(gè)表中的行 內(nèi)聯(lián)接內(nèi)聯(lián)接3-2SELECT FROM 表表1INNER JOIN 表表2ON SELECT FROM 表表1，表，表2WHERE SELECT S.SName,C.CourseID,C.Score FROM Score AS CINNER JOIN Students AS SON C.StudentID = S.SCodeSELECT Students.

13、SName, Score.CourseID, Score.ScoreFROM Students,ScoreWHERE Students.SCode = Score.StudentID等等價(jià)價(jià)演示示例演示示例4：內(nèi)聯(lián)接：內(nèi)聯(lián)接內(nèi)聯(lián)接語句內(nèi)聯(lián)接語句內(nèi)聯(lián)接內(nèi)聯(lián)接3-3SELECT S.SName,C.CourseID,C.Score FROM Students AS SINNER JOIN Score AS CON C.StudentID S.SCodeSELECT S.SName,C.CourseID,C.Score FROM Students AS SINNER JOIN Score AS CO

14、N C.StudentID = S.SCode15行行下面的查詢語句返回的查詢結(jié)果是一樣的嗎？下面的查詢語句返回的查詢結(jié)果是一樣的嗎？根據(jù)前面表中數(shù)據(jù)，下面的查詢語句將會(huì)返根據(jù)前面表中數(shù)據(jù)，下面的查詢語句將會(huì)返回多少行記錄？回多少行記錄？SELECT Students.SName, Score.CourseID, Score.ScoreFROM Students,ScoreWHERE Students.SCode = Score.StudentID兩個(gè)語句的執(zhí)兩個(gè)語句的執(zhí)行結(jié)果相同行結(jié)果相同指導(dǎo)指導(dǎo)兩表內(nèi)聯(lián)接查詢信息兩表內(nèi)聯(lián)接查詢信息2-1 訓(xùn)練要點(diǎn)訓(xùn)練要點(diǎn)兩表內(nèi)聯(lián)接查詢兩表內(nèi)聯(lián)接查詢INNE

15、R JOINON的使用的使用 WHERE的使用的使用需求說明需求說明查詢學(xué)生相關(guān)信息查詢學(xué)生相關(guān)信息查詢考試相關(guān)信息查詢考試相關(guān)信息講解需求說明講解需求說明指導(dǎo)指導(dǎo)兩表內(nèi)聯(lián)接查詢信息兩表內(nèi)聯(lián)接查詢信息2-2難點(diǎn)分析難點(diǎn)分析查詢年級(jí)編號(hào)為查詢年級(jí)編號(hào)為1的年級(jí)名稱、科目名稱及學(xué)時(shí)的年級(jí)名稱、科目名稱及學(xué)時(shí) SELECT FROM Subject AS J INNER JOIN Grade AS G ON J.GradeId=G.GradeIdWHERE G.GradeId=1完成時(shí)間：完成時(shí)間：20分鐘分鐘SELECT S.SName AS 姓名姓名, CS.CourseName AS 課程課程

16、, C.Score AS 成績成績FROM Students AS S INNER JOIN Score AS C ON (S.SCode = C.StudentID)INNER JOIN Course AS CS ON (CS.CourseID = C.CourseID)三表內(nèi)聯(lián)接三表內(nèi)聯(lián)接CourseIDCourseName使用使用WHERE語句如何實(shí)現(xiàn)？語句如何實(shí)現(xiàn)？練習(xí)練習(xí)三表內(nèi)聯(lián)接查詢信息三表內(nèi)聯(lián)接查詢信息 需求說明需求說明查詢學(xué)生學(xué)號(hào)、姓名、考試科目名稱及成績查詢學(xué)生學(xué)號(hào)、姓名、考試科目名稱及成績查詢參加查詢參加“走進(jìn)走進(jìn)Java編程世界編程世界”考試的學(xué)生姓名、考試的學(xué)生姓名、成

17、績、考試日期成績、考試日期完成時(shí)間：完成時(shí)間：10分鐘分鐘左外聯(lián)接左外聯(lián)接梅超風(fēng)梅超風(fēng)1陳玄風(fēng)陳玄風(fēng)2陸乘風(fēng)陸乘風(fēng)3曲靈風(fēng)曲靈風(fēng)4100197200189200267300276300381StudentsScore梅超風(fēng)梅超風(fēng)00197陳玄風(fēng)陳玄風(fēng)00189陳玄風(fēng)陳玄風(fēng)00267陸乘風(fēng)陸乘風(fēng)00276陸乘風(fēng)陸乘風(fēng)00381曲靈風(fēng)曲靈風(fēng)NULLNULL查詢結(jié)果查詢結(jié)果SELECT S.SName,C.CourseID,C.Score FROM Score AS CLEFT JOIN Students AS SON C.StudentID = S.SCode不一樣，主不一樣，主表和從表位表和從表

18、位置已互換置已互換SELECT S.SName,C.CourseID,C.Score FROM Students AS SLEFT JOIN Score AS CON C.StudentID = S.SCode主表（左表）主表（左表）Students中中的數(shù)據(jù)逐條匹配從表的數(shù)據(jù)逐條匹配從表Score中的數(shù)據(jù)中的數(shù)據(jù)1、匹配，返回到結(jié)果集、匹配，返回到結(jié)果集2、無匹配，、無匹配，NULL值返回值返回到結(jié)果集到結(jié)果集演示示例演示示例5：左外連接：左外連接猜一猜：這樣寫，返回的查詢結(jié)果是一樣的嗎？猜一猜：這樣寫，返回的查詢結(jié)果是一樣的嗎？SELECT 圖書編號(hào)圖書編號(hào),圖書名稱圖書名稱,出版社名稱出版社名稱FROM 圖書表圖書表RIGHT OUTER JOIN 出版社表出版社表 ON 圖書表圖書表.出版社編號(hào)出版社編號(hào) = 出版社表出版社表.出版社編號(hào)出版社編號(hào)右外聯(lián)接右外聯(lián)接圖書編號(hào)圖書編號(hào)圖書名稱圖書名稱出版社名稱出版社名稱1走進(jìn)走進(jìn)Java編程世界編程世界北大出版社北大出版社2HTML和和CSS網(wǎng)頁技術(shù)網(wǎng)頁技術(shù)清華出版社清華出版社NULLNULL新知出版社新知出版社也許很久也許很久沒出版書沒出版書籍了籍了右外聯(lián)接的原理與