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

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

AS

年級(jí)FROM

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

FROMStudentSGROUPBYSGrade,SSexORDERBYSGradeORDERBY子句在這里的作用？提問(wèn)多列分組2-2演示示例2：多列分組13/38需求3如何獲得總?cè)藬?shù)超過(guò)15人的年級(jí)？條件限定問(wèn)題14/38分組篩選語(yǔ)句SELECT……FROM<表名>WHERE……GROUPBY……HAVING……SELECTCOUNT(*)AS人數(shù),SGradeAS年級(jí)FROMStudentsGROUPBYSGradeHAVINGCOUNT(*)>15示例語(yǔ)法分組篩選演示示例3：分組篩選15/38WHERE與HAVING對(duì)比WHERE子句用來(lái)篩選FROM子句中指定的操作所產(chǎn)生的行GROUPBY子句用來(lái)分組WHERE子句的輸出HAVING子句用來(lái)從分組的結(jié)果中篩選行WHEREGROUPBYHAVING16/38SELECT 部門編號(hào),COUNT(*)FROM 員工信息表WHERE 工資>=2000GROUPBY 部門編號(hào)HAVING COUNT(*)>1思考分析以下的T-SQL，其作用是什么？提問(wèn)查詢有多個(gè)員工的工資不低于2000的部門編號(hào)!17/38學(xué)員操作—使用分組查詢學(xué)生相關(guān)信息需求說(shuō)明查詢每個(gè)年級(jí)的總學(xué)時(shí)數(shù)，并按照升序排列查詢每個(gè)參加考試的學(xué)員的平均分查詢每門課程的平均分，并按照降序排列查詢每個(gè)學(xué)生參加的所有考試的總分，并按照降序排列練習(xí)完成時(shí)間：15分鐘18/38共性問(wèn)題集中講解常見(jiàn)問(wèn)題及解決辦法代碼規(guī)范問(wèn)題調(diào)試技巧共性問(wèn)題集中講解19/38學(xué)員操作—限定條件的分組查詢2-1訓(xùn)練要點(diǎn)GROUPBY子句HAVING子句聚合函數(shù)需求說(shuō)明查詢學(xué)生相關(guān)信息查詢考試相關(guān)信息指導(dǎo)講解需求說(shuō)明20/38學(xué)員操作—限定條件的分組查詢2-2難點(diǎn)分析計(jì)算學(xué)生的年齡至少一次不及格的信息DATEDIFF(dd,BornDate,GETDATE())/365WHEREStudentResult<60GROUPBYStudentNo指導(dǎo)完成時(shí)間：25分鐘21/38小結(jié)如果執(zhí)行查詢語(yǔ)句其查詢結(jié)果以下的查詢語(yǔ)句將輸出哪些信息？SELECT*FROMASELECTA1,B1,MAX(C1)FROMA提問(wèn)22/38需求4如何同時(shí)從這兩個(gè)表中取得數(shù)據(jù)？問(wèn)題學(xué)號(hào)姓名年級(jí)…001張青裁1002陳剛1003蘇三東1學(xué)號(hào)科目分?jǐn)?shù)00126000227000348023/38常用的多表連接查詢內(nèi)連接(INNERJOIN)外連接左外連接(LEFTJOIN)右外連接(RIGHTJOIN)24/38內(nèi)連接3-1內(nèi)連接使用比較運(yùn)算符根據(jù)每個(gè)表的通用列中的值匹配兩個(gè)表中的行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)連接語(yǔ)句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示例語(yǔ)法等價(jià)演示示例4：內(nèi)連接26/38內(nèi)連接3-3下面的查詢語(yǔ)句返回的查詢結(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ù)，下面的查詢語(yǔ)句將會(huì)返回多少行記錄？SELECTStudents.SName,Score.CourseID,Score.ScoreFROM Students,ScoreWHEREStudents.SCode=Score.StudentID兩個(gè)語(yǔ)句的執(zhí)行結(jié)果相同提問(wèn)27/38SELECTS.SNameAS姓名,CS.CourseNameAS課程,C.ScoreAS成績(jī)FROMStudentsASSINNERJOINScoreASCON(S.SCode=C.StudentID)INNERJOINCourseASCSON(CS.CourseID=C.CourseID)三表內(nèi)連接示例使用WHERE語(yǔ)句如何實(shí)現(xiàn)？提問(wèn)姓名課程成績(jī)………………SNameSCode…………StudentsIDCourseIDScore………………CourseIDCourseName…………28/38學(xué)員操作—兩表內(nèi)連接查詢信息2-1訓(xùn)練要點(diǎn)兩表內(nèi)連接查詢INNERJOIN…ON的使用WHERE的使用需求說(shuō)明查詢學(xué)生相關(guān)信息查詢考試相關(guān)信息指導(dǎo)講解需求說(shuō)明29/38學(xué)員操作—兩表內(nèi)連接查詢信息2-2難點(diǎn)分析查詢年級(jí)編號(hào)為1的年級(jí)名稱、科目名稱及學(xué)時(shí)SELECT……FROMSubjectASJINNERJOINGradeASGONJ.GradeId=G.GradeIdWHERE

G.GradeId=1指導(dǎo)完成時(shí)間：20分鐘30/38學(xué)員操作—三表內(nèi)連接查詢信息需求說(shuō)明查詢學(xué)生學(xué)號(hào)、姓名、考試科目名稱及成績(jī)查詢參加“走進(jìn)Java編程世界”考試的學(xué)生姓名、成績(jī)、考試日期練習(xí)完成時(shí)間：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猜一猜：這樣寫(xiě)，返回的查詢結(jié)果是一樣的嗎？示例提問(wèn)主表（左表）Students中的數(shù)據(jù)逐條匹配表Score中的數(shù)據(jù)1、匹配，返回到結(jié)果集2、無(wú)匹配，NULL值返回到結(jié)果集演示示例5：左外連接32/38圖書(shū)編號(hào)圖書(shū)名稱出版社名稱1走進(jìn)Java編程世界北大出版社2HTML和CSS網(wǎng)頁(yè)技術(shù)清華出版社………………NULLNULL新知出版社………………右外連接右外連接的原理與左外連接相同右表逐條去匹配記錄；否則NULL填充也許很久沒(méi)出版書(shū)籍了SELECT圖書(shū)編號(hào),圖書(shū)名稱,出版社名稱FROM圖書(shū)表RIGHTJOIN出版社表ON

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