語言和sqlserver數(shù)據(jù)庫技術(shù)

第十一章連接查詢和分組查詢預(yù)習(xí)檢查分組查詢主要解決什么問題？請舉例說明請寫出T-SQL語句中實現(xiàn)分組查詢的關(guān)鍵字進(jìn)行分組篩選應(yīng)使用哪個關(guān)鍵字？多表連接主要解決什么問題？用在什么場合？多表連接查詢有哪幾種？集中測試2/38回顧和作業(yè)點評如下的T-SQL語句含義是什么？Temp表中A列為商品名稱，請說出查詢以大寫字母開頭的所有商品名稱的T-SQL語句聚合函數(shù)COUNT()和SUM()各代表什么意思？點評作業(yè)的提交情況和共性問題SELECTTelephoneFROMStudentsWHERETelephoneNOTLIKE'6%'SELECTAFROMTempWHEREALIKE'[A-Z]%'提問作業(yè)點評3/38本章任務(wù)統(tǒng)計學(xué)生考試信息從多個數(shù)據(jù)表中取得學(xué)生考試成績單4/38本章目標(biāo)使用GROUPBY進(jìn)行分組查詢掌握多表連接查詢5/38學(xué)號姓名地址所屬年級……001王明全湖南長沙1002張菲湖北宜昌1003于寄謙甘肅天水1004劉國正山東荷澤1005周接輪臺灣新竹2006鞏小妹香港龍灣2007鞏大妹香港龍灣2008張明敏北京順義3009矛十八四川棉陽3010張林光陜西臨潼3需求1每年級的學(xué)生人數(shù)各是多少？問題6/38學(xué)號姓名地址所屬年級001王明全湖南長沙1002張菲湖北宜昌1003于寄謙甘肅天水1004劉國正山東荷澤1005周接輪臺灣新竹2006鞏小妹香港龍灣2007鞏大妹香港龍灣2008張明敏北京順義3009矛十八四川棉陽3010張林光陜西臨潼3第一學(xué)期人數(shù)第二學(xué)期人數(shù)第三學(xué)期人數(shù)三組示例分組查詢原理7/38分組查詢用法分組查詢語句SELECT……FROM<表名>WHERE……GROUPBY……SELECTCOUNT(*)AS人數(shù),SGrade

AS

年級FROM

StudentsGROUPBYSGrade示例語法演示示例1：分組查詢SELECT列表中只能包含1.被分組的列2.為每個分組返回一個值的表達(dá)式，如聚合函數(shù)注意!8/38SELECTCourseID,AVG(Score)AS課程平均成績FROMScoreGROUPBYCourseID分組查詢解析2-1如何查詢每門課程的平均分？按照什么進(jìn)行分組？問題提問對分組后的每個組內(nèi)的記錄進(jìn)行一個聚集，通常用到聚合函數(shù)!9/38分組查詢解析2-2查詢每門課程的平均分，并且按照分?jǐn)?shù)由低到高的順序排列顯示SELECTCourseID,AVG(Score)AS課程平均成績FROMScoreGROUPBYCourseIDORDERBYAVG(Score)問題10/38需求2統(tǒng)計每學(xué)期男女同學(xué)的人數(shù)，該怎么辦？對學(xué)生進(jìn)行分組計算每組學(xué)生的總?cè)藬?shù)問題分析11/38多列分組2-1年級分組性別分組12/38SELECTCOUNT(*)AS人數(shù),SGradeAS年級,SSexAS性別

FROMStudentSGROUPBYSGrade,SSexORDERBYSGradeORDERBY子句在這里的作用？提問多列分組2-2演示示例2：多列分組13/38需求3如何獲得總?cè)藬?shù)超過15人的年級？條件限定問題14/38分組篩選語句SELECT……FROM<表名>WHERE……GROUPBY……HAVING……SELECTCOUNT(*)AS人數(shù),SGradeAS年級FROMStudentsGROUPBYSGradeHAVINGCOUNT(*)>15示例語法分組篩選演示示例3：分組篩選15/38WHERE與HAVING對比WHERE子句用來篩選FROM子句中指定的操作所產(chǎn)生的行GROUPBY子句用來分組WHERE子句的輸出HAVING子句用來從分組的結(jié)果中篩選行WHEREGROUPBYHAVING16/38SELECT 部門編號,COUNT(*)FROM 員工信息表WHERE 工資>=2000GROUPBY 部門編號HAVING COUNT(*)>1思考分析以下的T-SQL，其作用是什么？提問查詢有多個員工的工資不低于2000的部門編號!17/38學(xué)員操作—使用分組查詢學(xué)生相關(guān)信息需求說明查詢每個年級的總學(xué)時數(shù)，并按照升序排列查詢每個參加考試的學(xué)員的平均分查詢每門課程的平均分，并按照降序排列查詢每個學(xué)生參加的所有考試的總分，并按照降序排列練習(xí)完成時間：15分鐘18/38共性問題集中講解常見問題及解決辦法代碼規(guī)范問題調(diào)試技巧共性問題集中講解19/38學(xué)員操作—限定條件的分組查詢2-1訓(xùn)練要點GROUPBY子句HAVING子句聚合函數(shù)需求說明查詢學(xué)生相關(guān)信息查詢考試相關(guān)信息指導(dǎo)講解需求說明20/38學(xué)員操作—限定條件的分組查詢2-2難點分析計算學(xué)生的年齡至少一次不及格的信息DATEDIFF(dd,BornDate,GETDATE())/365WHEREStudentResult<60GROUPBYStudentNo指導(dǎo)完成時間：25分鐘21/38小結(jié)如果執(zhí)行查詢語句其查詢結(jié)果以下的查詢語句將輸出哪些信息？SELECT*FROMASELECTA1,B1,MAX(C1)FROMA提問22/38需求4如何同時從這兩個表中取得數(shù)據(jù)？問題學(xué)號姓名年級…001張青裁1002陳剛1003蘇三東1學(xué)號科目分?jǐn)?shù)00126000227000348023/38常用的多表連接查詢內(nèi)連接(INNERJOIN)外連接左外連接(LEFTJOIN)右外連接(RIGHTJOIN)24/38內(nèi)連接3-1內(nèi)連接使用比較運算符根據(jù)每個表的通用列中的值匹配兩個表中的行StudentsScore查詢結(jié)果SNameSCode梅超風(fēng)1陳玄風(fēng)2陸乘風(fēng)3StudentsIDCourseIDScore100197200189200267300276300381SNameCourseIDScore梅超風(fēng)00197陳玄風(fēng)00189陳玄風(fēng)00267陸乘風(fēng)00276陸乘風(fēng)0038125/38內(nèi)連接3-2內(nèi)連接語句SELECT ……

FROM

表1INNERJOIN

表2ON

……SELECT……

FROM

表1，表2WHERE……

SELECT

S.SName,C.CourseID,C.Score

FROM

ScoreASCINNERJOIN

StudentsASSON

C.StudentID=S.SCodeSELECT

Students.SName,Score.CourseID,Score.ScoreFROM Students,ScoreWHEREStudents.SCode=Score.StudentID示例語法等價演示示例4：內(nèi)連接26/38內(nèi)連接3-3下面的查詢語句返回的查詢結(jié)果是一樣的嗎？SELECT

S.SName,C.CourseID,C.Score

FROM

StudentsASSINNERJOIN

ScoreASCON

C.StudentID<>S.SCodeSELECT

S.SName,C.CourseID,C.Score

FROM

StudentsASSINNERJOIN

ScoreASCON

C.StudentID=S.SCode根據(jù)前面表中數(shù)據(jù)，下面的查詢語句將會返回多少行記錄？SELECTStudents.SName,Score.CourseID,Score.ScoreFROM Students,ScoreWHEREStudents.SCode=Score.StudentID兩個語句的執(zhí)行結(jié)果相同提問27/38SELECTS.SNameAS姓名,CS.CourseNameAS課程,C.ScoreAS成績FROMStudentsASSINNERJOINScoreASCON(S.SCode=C.StudentID)INNERJOINCourseASCSON(CS.CourseID=C.CourseID)三表內(nèi)連接示例使用WHERE語句如何實現(xiàn)？提問姓名課程成績………………SNameSCode…………StudentsIDCourseIDScore………………CourseIDCourseName…………28/38學(xué)員操作—兩表內(nèi)連接查詢信息2-1訓(xùn)練要點兩表內(nèi)連接查詢INNERJOIN…ON的使用WHERE的使用需求說明查詢學(xué)生相關(guān)信息查詢考試相關(guān)信息指導(dǎo)講解需求說明29/38學(xué)員操作—兩表內(nèi)連接查詢信息2-2難點分析查詢年級編號為1的年級名稱、科目名稱及學(xué)時SELECT……FROMSubjectASJINNERJOINGradeASGONJ.GradeId=G.GradeIdWHERE

G.GradeId=1指導(dǎo)完成時間：20分鐘30/38學(xué)員操作—三表內(nèi)連接查詢信息需求說明查詢學(xué)生學(xué)號、姓名、考試科目名稱及成績查詢參加“走進(jìn)Java編程世界”考試的學(xué)生姓名、成績、考試日期練習(xí)完成時間：10分鐘31/38StudentsIDCourseIDScore100197200189200267300276300381SNameSCode梅超風(fēng)1陳玄風(fēng)2陸乘風(fēng)3曲靈風(fēng)4左外連接SNameCourseIDScore梅超風(fēng)00197陳玄風(fēng)00189陳玄風(fēng)00267陸乘風(fēng)00276陸乘風(fēng)00381曲靈風(fēng)NULLNULLStudentsScore查詢結(jié)果SELECT

S.SName,C.CourseID,C.Score

FROM

ScoreASCLEFTJOIN

StudentsASSON

C.StudentID=S.SCode不一樣，主表和從表位置已互換SELECT

S.SName,C.CourseID,C.Score

FROM

StudentsASSLEFTJOIN

ScoreASCON

C.StudentID=S.SCode猜一猜：這樣寫，返回的查詢結(jié)果是一樣的嗎？示例提問主表（左表）Students中的數(shù)據(jù)逐條匹配表Score中的數(shù)據(jù)1、匹配，返回到結(jié)果集2、無匹配，NULL值返回到結(jié)果集演示示例5：左外連接32/38圖書編號圖書名稱出版社名稱1走進(jìn)Java編程世界北大出版社2HTML和CSS網(wǎng)頁技術(shù)清華出版社………………NULLNULL新知出版社………………右外連接右外連接的原理與左外連接相同右表逐條去匹配記錄；否則NULL填充也許很久沒出版書籍了SELECT圖書編號,圖書名稱,出版社名稱FROM圖書表RIGHTJOIN出版社表ON

圖書表.出版社編號=出版社表.出版社編號示例33/38學(xué)員操作—使用外連接查詢信息需求說明查詢所有科目的參考信息