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

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

IN

('北京','廣州','上海')學員姓名地址李揚廣州于紫電上海李青霜北京司馬弓上?！?問題成績表中存儲了所有學員的成績，我想知道：學員的總成績、平均成績、有成績的學員總共有多少名怎么辦？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)計所有人所有課程的總成績而是想求每一門課的平均績或者某個人的所有課的總成績怎么辦？13分組匯總這三個數(shù)取平均值第4-6分數(shù)取平均值最后三個數(shù)取平均值有一個學員參加考試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)部測試成績補考成績怎么樣來統(tǒng)計每次的內(nèi)部測試不同學員的成績？17分組查詢—再看看內(nèi)部測試成績還要把這個加上取平均StudentID和CourseID的組合存在重復……需要按照這兩個來進行分組，避免StudentID和CourseID同時一樣18分組查詢—多列分組SELECTStudentIDAS學員編號,CourseIDAS內(nèi)部測試,AVG(Score)AS內(nèi)部測試平均成績FROMScoreGROUPBYStudentID,CourseID19分組查詢—問題在以上統(tǒng)計內(nèi)部測試成績的基礎(chǔ)上，如果只想看補考的學員的成績，怎么辦？20分組查詢—再看看增加條件：要求該學員的CourseID在分組內(nèi)出現(xiàn)過一次以上……SELECTStudentIDAS學員編號,CourseIDAS內(nèi)部測試,AVG(Score)AS內(nèi)部測試平均成績FROMScoreGROUPBYStudentID,CourseID21分組查詢—HAVINGSELECTStudentIDAS學員編號,CourseIDAS內(nèi)部測試,AVG(Score)AS內(nèi)部測試平均成績FROMScoreGROUPBYStudentID,CourseIDHAVINGCOUNT(Score)>122分組查詢—對比WHEREGROUPBYHAVINGWHERE子句從數(shù)據(jù)源中去掉不符合其搜索條件的數(shù)據(jù)GROUPBY子句搜集數(shù)據(jù)行到各個組中，統(tǒng)計函數(shù)為各個組計算統(tǒng)計值HAVING子句去掉不符合其組搜索條件的各組數(shù)據(jù)行23分組查詢—思考SELECT

部門編號,COUNT(*)FROM

員工信息表WHERE

工資>=2000GROUPBY

部門編號HAVING COUNT(*)>1思考：分析以下T-SQL的含義24多表聯(lián)接查詢—問題學員內(nèi)部測試成績查詢的每次顯示的都是學員的編號信息，因為該表中只存儲了學員的編號；實際上最好顯示學員的姓名，而姓名存儲在學員信息表；如何同時從這兩個表中取得數(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梅超風陳玄風陸乘風曲靈風SCode1234查詢結(jié)果SName梅超風陳玄風陳玄風陸乘風CourseIDScore00100100200297896776陸乘風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梅超風陳玄風陸乘風曲靈風SCode1234查詢結(jié)果SName梅超風陳玄風陳玄風陸乘風CourseIDScore00100100200297896776陸乘風00381曲靈風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．自身連接

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

提問：查詢員工信息以及經(jīng)理的信息SELECTA.*,B.ENameASMgrNameFROMEmployeeAJOINEmployeeBONA.Mgr=B.EmpNo35案例分析1—要求在數(shù)據(jù)庫表中，數(shù)據(jù)行位置并不重要，但是一個單位中要根據(jù)奇數(shù)行和偶數(shù)行的數(shù)據(jù)來匯總，在這個匯總的基礎(chǔ)上再得到一個數(shù)值，因此，要查詢數(shù)據(jù)庫表的奇數(shù)行和偶數(shù)行的總數(shù)數(shù)據(jù)庫表名：TBL 字段名：A 主鍵字段：IDKEY（標識列，種子：1；增長量：1）36案例分析1—分析只能依靠標識列的值來進行判斷和選取因為數(shù)據(jù)行可能存在增加、修改和刪除，因此標識列的數(shù)據(jù)值并不“完全可靠”，例如標識列值為3，并不一定是第三行，因為如果第二行被刪除了，它就是第二行，實際上也是偶數(shù)行根據(jù)我們前面使用過的SELECT…INTO，可以創(chuàng)建一張新表，順便創(chuàng)建新的標識列，再在新的標識列上執(zhí)行奇偶判斷奇數(shù)判斷依據(jù)：標識列值%2不等于0；偶數(shù)判斷依據(jù)：標識列值%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ā)行了新的信用卡，剛開始的時候推廣得很好，但是逐漸廢卡也越來越多（卡上的余額少于2元，并且用戶長時間不使用該卡），因此銀行在二月份把這些少于2元的卡從都數(shù)據(jù)庫表中刪除了，但是很快問題就來了，用戶發(fā)現(xiàn)他的卡再也不能使用而投訴，因此只能再把這些卡恢復。編寫SQL，把CardID為23、30等在子表中沒有的記錄插入到子表中，插入的數(shù)據(jù)Score項值為2……39案例分析2—分析這是一個數(shù)據(jù)插入的操作，因此要使用INSERT語句來進行這種方式的數(shù)據(jù)插入，不是固定的數(shù)據(jù)項，而是從其它表中篩選數(shù)據(jù)再插入，因此要使用到INSERTINTO…SELECT結(jié)構(gòu)插入的數(shù)據(jù)項是子表中沒