第五章續(xù)分組及連接查詢

數(shù)據(jù)查詢(2)1回顧數(shù)據(jù)查詢將按照一定的條件對表的的記錄進(jìn)行逐行篩選，然后把符合要求的組合成“記錄集”返回給查詢的用戶，記錄集的結(jié)構(gòu)類似于表結(jié)構(gòu)可以在查詢中使用AS子句或者=重命名列名判斷一行中的數(shù)據(jù)項(xiàng)是否為空，使用ISNULL使用TOP子句可以限制查詢返回的行數(shù)ORDERBY子句用來對查詢的結(jié)果進(jìn)行排序，缺省按照升序排列，也可以按降序（DESC）來排列，還可以按照多列來排序在查詢語句中，可以使用常量、表達(dá)式和運(yùn)算符在查詢中使用函數(shù)，能夠像在程序中那樣處理查詢得到的數(shù)據(jù)項(xiàng)24.4.4轉(zhuǎn)換函數(shù)程序代碼如下：PRINT'今天的日期是'+CONVERT(VARCHAR(12),GETDATE(),101)PRINT'今年是'+CONVERT(VARCHAR(12),Year(Getdate()))PRINT'本月是'+CONVERT(VARCHAR(12),Month(Getdate()))+'月'PRINT'今天是'+CONVERT(VARCHAR(12),day(Getdate()))+'號(hào)'PRINT'后天是'+CONVERT(VARCHAR(12),DATEADD(Dy,2,getdate()),101)PRINT'與2011年1月27號(hào)還差'+CONVERT(VARCHAR(12),DATEDIFF(DAy,getdate(),'01/27/2011'))+'天'PRINT'現(xiàn)在是星期'+CONVERT(VARCHAR(12),DATEPART(Dw,getdate()))日期和時(shí)間函數(shù)的使用示例。3目標(biāo)使用LIKE、BETWEEN、IN進(jìn)行模糊查詢在查詢中使用聚合函數(shù)使用GROUPBY進(jìn)行分組查詢進(jìn)行多表聯(lián)接查詢4模糊查詢—LIKE查詢時(shí)，字段中的內(nèi)容并不一定與查詢內(nèi)容完全匹配，只要字段中含有這些內(nèi)容SELECTSNameAS姓名FROMStudentsWHERESNameLIKE'張%'姓名張果老張飛張揚(yáng)出去思考：以下的SQL語句：SELECT*FROM數(shù)據(jù)表WHERE編號(hào)LIKE'00[^8]%[A,C]%‘可能會(huì)查詢出的編號(hào)值為()。A、9890ACDB、007_AFFC、008&DCGD、KK8C5模糊查詢-LIKE示例：查詢姓名叫”王**軍“的學(xué)生信息通配符：%：包含零個(gè)或多個(gè)字符的任意字符串_下劃線：任何單個(gè)字符[]：指定范圍[a-f]或集合[acdef]中的任意單個(gè)字符[^]：不屬于指定范圍([a-f])或集合([abcdef])的任何單個(gè)字符。提問：在author表中查找以de開始并且其后的字母不為l的所有作者的姓氏SELECT*FROMSTUDENTWHEREStuNameLIKE‘王_軍‘6模糊查詢—ISNULL把某一字段中內(nèi)容為空的記錄查詢出來SELECTSNameAs姓名SAddressAS地址FROMStudentsWHERESAddress

ISNULL姓名地址張果老NULL李尋歡NULL令狐沖NULL程靈素NULL……猜一猜：把Students表中某些行的SAddress字段值刪掉后：——使用ISNULL能查詢出來這些數(shù)據(jù)行嗎？——怎么查詢出這些行來？7模糊查詢—BETWEEN把某一字段中內(nèi)容在特定范圍內(nèi)的記錄查詢出來SELECTStudentID,ScoreFROMSCoreWHEREScore

BETWEEN60AND80StudentIDScore77786111761764……8模糊查詢—IN把某一字段中內(nèi)容與所列出的查詢內(nèi)容列表匹配的記錄查詢出來SELECTSNameAS學(xué)員姓名,SAddressAs地址FROMStudentsWHERESAddress

IN

('北京','廣州','上海')學(xué)員姓名地址李揚(yáng)廣州于紫電上海李青霜北京司馬弓上?！?問題成績表中存儲(chǔ)了所有學(xué)員的成績，我想知道：學(xué)員的總成績、平均成績、有成績的學(xué)員總共有多少名怎么辦？10聚合函數(shù)-1SUMAVGSELECTSUM(ytd_sales)FROMtitlesWHEREtype='business'SELECTSUM(ytd_sales),Price

FROMtitlesWHEREtype='business'×SELECTAVG(SCore)AS平均成績

FromScoreWHEREScore>=6011聚合函數(shù)-2MAX、MINCOUNTSELECTAVG(SCore)AS平均成績,MAX

(Score)AS最高分,MIN(Score)AS最低分FromScoreWHEREScore>=60SELECT

COUNT(*)

AS及格人數(shù)FromScoreWHEREScore>=6012問題如果不是統(tǒng)計(jì)所有人所有課程的總成績而是想求每一門課的平均績或者某個(gè)人的所有課的總成績怎么辦？13分組匯總這三個(gè)數(shù)取平均值第4-6分?jǐn)?shù)取平均值最后三個(gè)數(shù)取平均值有一個(gè)學(xué)員參加考試14分組查詢—GROUPBYSELECT

CourseID,AVG(Score)

AS課程平均成績FROMScoreGROUPBY

CourseID15分組查詢—思考SELECT

StudentID,CourseID,AVG(Score)

AS課程平均成績FROMScoreGROUPBY

CourseID思考：執(zhí)行以下的T-SQL：——結(jié)果如何？16分組查詢—多列分組第一次內(nèi)部測試成績第二次內(nèi)部測試成績第三次內(nèi)部測試成績補(bǔ)考成績怎么樣來統(tǒng)計(jì)每次的內(nèi)部測試不同學(xué)員的成績？17分組查詢—再看看內(nèi)部測試成績還要把這個(gè)加上取平均StudentID和CourseID的組合存在重復(fù)……需要按照這兩個(gè)來進(jìn)行分組，避免StudentID和CourseID同時(shí)一樣18分組查詢—多列分組SELECTStudentIDAS學(xué)員編號(hào),CourseIDAS內(nèi)部測試,AVG(Score)AS內(nèi)部測試平均成績FROMScoreGROUPBYStudentID,CourseID19分組查詢—問題在以上統(tǒng)計(jì)內(nèi)部測試成績的基礎(chǔ)上，如果只想看補(bǔ)考的學(xué)員的成績，怎么辦？20分組查詢—再看看增加條件：要求該學(xué)員的CourseID在分組內(nèi)出現(xiàn)過一次以上……SELECTStudentIDAS學(xué)員編號(hào),CourseIDAS內(nèi)部測試,AVG(Score)AS內(nèi)部測試平均成績FROMScoreGROUPBYStudentID,CourseID21分組查詢—HAVINGSELECTStudentIDAS學(xué)員編號(hào),CourseIDAS內(nèi)部測試,AVG(Score)AS內(nèi)部測試平均成績FROMScoreGROUPBYStudentID,CourseIDHAVINGCOUNT(Score)>122分組查詢—對比WHEREGROUPBYHAVINGWHERE子句從數(shù)據(jù)源中去掉不符合其搜索條件的數(shù)據(jù)GROUPBY子句搜集數(shù)據(jù)行到各個(gè)組中，統(tǒng)計(jì)函數(shù)為各個(gè)組計(jì)算統(tǒng)計(jì)值HAVING子句去掉不符合其組搜索條件的各組數(shù)據(jù)行23分組查詢—思考SELECT

部門編號(hào),COUNT(*)FROM

員工信息表WHERE

工資>=2000GROUPBY

部門編號(hào)HAVING COUNT(*)>1思考：分析以下T-SQL的含義24多表聯(lián)接查詢—問題學(xué)員內(nèi)部測試成績查詢的每次顯示的都是學(xué)員的編號(hào)信息，因?yàn)樵摫碇兄淮鎯?chǔ)了學(xué)員的編號(hào)；實(shí)際上最好顯示學(xué)員的姓名，而姓名存儲(chǔ)在學(xué)員信息表；如何同時(shí)從這兩個(gè)表中取得數(shù)據(jù)？25多表聯(lián)接查詢—分類內(nèi)聯(lián)接(INNERJOIN)外聯(lián)接——左外聯(lián)接(LEFTJOIN)——右外聯(lián)接(RIGHTJOIN)——完整外聯(lián)接(FULLJOIN)交叉聯(lián)接(CROSSJOIN)26SELECT S.SName,C.CourseID,C.ScoreFrom ScoreASCINNERJOIN StudentsASSON C.StudentID=S.SCodeScoreStudentsIDCourseIDScore122300100100200297896776300381猜一猜：這樣寫，返回的查詢結(jié)果是一樣的嗎？SELECT S.SName,C.CourseID,C.ScoreFrom StudentsASSINNERJOIN ScoreASCON C.StudentID=S.SCode再猜一猜：以下返回多少行？SELECT S.SName,C.CourseID,C.ScoreFrom StudentsASSINNERJOIN ScoreASCON C.StudentID<>S.SCode多表聯(lián)接查詢—內(nèi)聯(lián)接-1StundentsSName梅超風(fēng)陳玄風(fēng)陸乘風(fēng)曲靈風(fēng)SCode1234查詢結(jié)果SName梅超風(fēng)陳玄風(fēng)陳玄風(fēng)陸乘風(fēng)CourseIDScore00100100200297896776陸乘風(fēng)0038127多表聯(lián)接查詢—內(nèi)聯(lián)接-2SELECTStudents.SName,Score.CourseID,Score.ScoreFROM Students,ScoreWHERE

Students.SCode=Score.StudentID28多表聯(lián)接查詢—三表聯(lián)接SELECT

S.SNameAS姓名,CS.CourseNameAS課程,C.ScoreAS成績FROMStudentsASSINNERJOINScoreASCON(S.SCode=C.StudentID)INNERJOINCourseASCSON(CS.CourseID=C.CourseID)29ScoreStudentsIDCourseIDScore122300100100200297896776300381多表聯(lián)接查詢—左外聯(lián)接StundentsSName梅超風(fēng)陳玄風(fēng)陸乘風(fēng)曲靈風(fēng)SCode1234查詢結(jié)果SName梅超風(fēng)陳玄風(fēng)陳玄風(fēng)陸乘風(fēng)CourseIDScore00100100200297896776陸乘風(fēng)00381曲靈風(fēng)NULLNULLSELECT

S.SName,C.CourseID,C.ScoreFrom

StudentsASSLEFTJOIN ScoreASCON

C.StudentID=S.SCode猜一猜：這樣寫，返回的查詢結(jié)果是一樣的嗎？SELECT

S.SName,C.CourseID,C.ScoreFrom

ScoreASCLEFTJOIN

StudentsASSON

C.StudentID=S.SCode30多表聯(lián)接查詢—右外聯(lián)接SELECTTitles.Title_id,Titles.Title,Publishers.Pub_nameFROMtitlesRIGHTOUTERJOINPublishersONTitles.Pub_id=Publishers.Pub_id313．自身連接

連接操作不僅可以在兩個(gè)表之間操作，也可以是一個(gè)表與其自己進(jìn)行連接，這種操作稱為自身連接。32【例】查詢每個(gè)人的祖父姓名。假定個(gè)人情況表如表所示。表Person33要查出個(gè)人的祖父姓名，必須將這個(gè)表做自身連接。查詢語句為：SELECTFirst.Name，Second.F_nameFROMPersonFirst，PersonSecondWHEREFirst.F_name=Second.Name這里First和Second分別是第一個(gè)Person表和第二個(gè)Person表的別名。查詢結(jié)果：張成全張福全李向軍李福來34自聯(lián)接

提問：查詢員工信息以及經(jīng)理的信息SELECTA.*,B.ENameASMgrNameFROMEmployeeAJOINEmployeeBONA.Mgr=B.EmpNo35案例分析1—要求在數(shù)據(jù)庫表中，數(shù)據(jù)行位置并不重要，但是一個(gè)單位中要根據(jù)奇數(shù)行和偶數(shù)行的數(shù)據(jù)來匯總，在這個(gè)匯總的基礎(chǔ)上再得到一個(gè)數(shù)值，因此，要查詢數(shù)據(jù)庫表的奇數(shù)行和偶數(shù)行的總數(shù)數(shù)據(jù)庫表名：TBL 字段名：A 主鍵字段：IDKEY（標(biāo)識(shí)列，種子：1；增長量：1）36案例分析1—分析只能依靠標(biāo)識(shí)列的值來進(jìn)行判斷和選取因?yàn)閿?shù)據(jù)行可能存在增加、修改和刪除，因此標(biāo)識(shí)列的數(shù)據(jù)值并不“完全可靠”，例如標(biāo)識(shí)列值為3，并不一定是第三行，因?yàn)槿绻诙斜粍h除了，它就是第二行，實(shí)際上也是偶數(shù)行根據(jù)我們前面使用過的SELECT…INTO，可以創(chuàng)建一張新表，順便創(chuàng)建新的標(biāo)識(shí)列，再在新的標(biāo)識(shí)列上執(zhí)行奇偶判斷奇數(shù)判斷依據(jù)：標(biāo)識(shí)列值%2不等于0；偶數(shù)判斷依據(jù)：標(biāo)識(shí)列值%2等于037案例分析1—T-SQLSELECT A,IDENTITY(int,1,1)ASID

INTO TEMPTABLEFROM TBLSELECT

SUM(A)AS奇數(shù)列匯總FROM TEMPTABLEWHERE ID%2<>0SELECT

SUM(A)FROMAS偶數(shù)列匯總

FROM TEMPTABLEWHERE ID%2=038案例分析2—要求一家銀行發(fā)行了新的信用卡，剛開始的時(shí)候推廣得很好，但是逐漸廢卡也越來越多（卡上的余額少于2元，并且用戶長時(shí)間不使用該卡），因此銀行在二月份把這些少于2元的卡從都數(shù)據(jù)庫表中刪除了，但是很快問題就來了，用戶發(fā)現(xiàn)他的卡再也不能使用而投訴，因此只能再把這些卡恢復(fù)。編寫SQL，把CardID為23、30等在子表中沒有的記錄插入到子表中，插入的數(shù)據(jù)Score項(xiàng)值為2……39案例分析2—分析這是一個(gè)數(shù)據(jù)插入的操作，因此要使用INSERT語句來進(jìn)行這種方式的數(shù)據(jù)插入，不是固定的數(shù)據(jù)項(xiàng)，而是從其它表中篩選數(shù)據(jù)再插入，因此要使用到INSERTINTO…SELECT結(jié)構(gòu)插入的數(shù)據(jù)項(xiàng)是子表中沒