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

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

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

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

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

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

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

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

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

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

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

11、LECT A1,B1,MAX(C1) FROM A需求需求4學號學號姓名姓名年級年級001張青裁張青裁1002陳剛陳剛1003蘇三東蘇三東1學號學號科目科目分數(shù)分數(shù)001260002270003480如何同時從這兩個表中取得數(shù)據(jù)？如何同時從這兩個表中取得數(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梅超風梅超風1陳玄風陳玄風2陸乘風陸乘風3曲靈風曲靈風4100197200189200267300276300381Students Score 梅超風梅超

12、風00197陳玄風陳玄風00189陳玄風陳玄風00267陸乘風陸乘風00276陸乘風陸乘風00381查詢結果查詢結果 內(nèi)聯(lián)接使用比較運算符根據(jù)每個表的通用列中的內(nèi)聯(lián)接使用比較運算符根據(jù)每個表的通用列中的值匹配兩個表中的行值匹配兩個表中的行 內(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等等價價演示示例演示示例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行行下面的查詢語句返回的查詢結果是一樣的嗎？下面的查詢語句返回的查詢結果是一樣的嗎？根據(jù)前面表中數(shù)據(jù)，下面的查詢語句將會返根據(jù)前面表中數(shù)據(jù)，下面的查詢語句將會返回多少行記錄？回多少行記錄？SELECT Students.SName, Score.CourseID, Score.ScoreFROM Students,ScoreWHERE Students.SCode = Score.StudentID兩個語句的執(zhí)兩個語句的執(zhí)行結果相同行結果相同指導指導兩表內(nèi)聯(lián)接查詢信息兩表內(nèi)聯(lián)接查詢信息2-1 訓練要點訓練要點兩表內(nèi)聯(lián)接查詢兩表內(nèi)聯(lián)接查詢INNE

15、R JOINON的使用的使用 WHERE的使用的使用需求說明需求說明查詢學生相關信息查詢學生相關信息查詢考試相關信息查詢考試相關信息講解需求說明講解需求說明指導指導兩表內(nèi)聯(lián)接查詢信息兩表內(nèi)聯(lián)接查詢信息2-2難點分析難點分析查詢年級編號為查詢年級編號為1的年級名稱、科目名稱及學時的年級名稱、科目名稱及學時 SELECT FROM Subject AS J INNER JOIN Grade AS G ON J.GradeId=G.GradeIdWHERE G.GradeId=1完成時間：完成時間：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語句如何實現(xiàn)？語句如何實現(xiàn)？練習練習三表內(nèi)聯(lián)接查詢信息三表內(nèi)聯(lián)接查詢信息 需求說明需求說明查詢學生學號、姓名、考試科目名稱及成績查詢學生學號、姓名、考試科目名稱及成績查詢參加查詢參加“走進走進Java編程世界編程世界”考試的學生姓名、考試的學生姓名、成

17、績、考試日期成績、考試日期完成時間：完成時間：10分鐘分鐘左外聯(lián)接左外聯(lián)接梅超風梅超風1陳玄風陳玄風2陸乘風陸乘風3曲靈風曲靈風4100197200189200267300276300381StudentsScore梅超風梅超風00197陳玄風陳玄風00189陳玄風陳玄風00267陸乘風陸乘風00276陸乘風陸乘風00381曲靈風曲靈風NULLNULL查詢結果查詢結果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、匹配，返回到結果集、匹配，返回到結果集2、無匹配，、無匹配，NULL值返回值返回到結果集到結果集演示示例演示示例5：左外連接：左外連接猜一猜：這樣寫，返回的查詢結果是一樣的嗎？猜一猜：這樣寫，返回的查詢結果是一樣的嗎？SELECT 圖書編號圖書編號,圖書名稱圖書名稱,出版社名稱出版社名稱FROM 圖書表圖書表RIGHT OUTER JOIN 出版社表出版社表 ON 圖書表圖書表.出版社編號出版社編號 = 出版社表出版社表.出版社編號出版社編號右外聯(lián)接右外聯(lián)接圖書編號圖書編號圖書名稱圖書名稱出版社名稱出版社名稱1走進走進Java編程世界編程世界北大出版社北大出版社2HTML和和CSS網(wǎng)頁技術網(wǎng)頁技術清華出版社清華出版社NULLNULL新知出版社新知出版社也許很久也許很久沒出版書沒出版書籍了籍了右外聯(lián)接的原理與