多表連接查詢

1、第五章數(shù)據(jù)查詢(二)1本章任務(wù)學(xué)生考試信息匯總學(xué)生考試信息統(tǒng)計(jì)從多個(gè)數(shù)據(jù)表中取得考試信息列表2 使用LIKE、BETWEEN、IN進(jìn)行模糊查詢 在查詢中使用聚合函數(shù) 使用GROUP BY進(jìn)行分組查詢 進(jìn)行多表聯(lián)接查詢本章目標(biāo)3一、模糊查詢有時(shí)查詢者對(duì)查詢條件是模糊的、大概的、不特別明確的。例如，查詢姓李的學(xué)生信息，查詢分?jǐn)?shù)在90分以上的考試成績(jī)，查詢男生的姓名。查詢的結(jié)果不一定是確切的。因此屬于模糊查詢。模糊查詢可以使用前面介紹的LIKE、通配符來(lái)進(jìn)行，還可以使用BETWEEN、IN進(jìn)行查詢。4模糊查詢LIKE 查詢時(shí)，字段中的內(nèi)容并不一定與查詢內(nèi)容完全匹配，只要字段中含有這些內(nèi)容即可。SEL

2、ECT Name AS 姓名 FROM Students WHERE Name LIKE 張%5思考：以下的SQL語(yǔ)句：SELECT * FROM 數(shù)據(jù)表 WHERE 編號(hào) LIKE 008%AC%可能會(huì)查詢出的編號(hào)值為( )。A、9890ACDB、007_AFFC、008&DCGD、KK8CB6模糊查詢IS NULL把某一字段中內(nèi)容為空的記錄查詢出來(lái)SELECT Name As 姓名，Address AS 地址 FROM Students WHERE Address IS NULL姓名地址張果老NULL李尋歡NULL令狐沖NULL程靈素NULL7模糊查詢BETWEEN把某一字段中內(nèi)容在特定范

3、圍內(nèi)的記錄查詢出來(lái)例如：查詢考試成績(jī)?cè)?0至80之間的學(xué)生學(xué)號(hào)和姓名。SELECT Sno, Score FROM Scores WHERE Score BETWEEN 60 AND 80SELECT Sno, Score FROM Scores WHERE Score=60 AND Score=60WHERE lno=113聚合函數(shù)-2MAX、MINSELECT AVG(Score) AS 平均成績(jī), MAX (Score) AS 最高分, MIN (Score) AS 最低分 FROM ScoresWHERE Score =60COUNTSELECT COUNT (*) AS 及格人次 F

4、ROM Scores WHERE Score=60例如：統(tǒng)計(jì)所有考試及格人次。14問(wèn)題： 如果不是統(tǒng)計(jì)所有人所有課程的總成績(jī)，而是想求每一門課的平均成績(jī)，或者每個(gè)人的所有課的總成績(jī)?cè)趺崔k？三、分組查詢15分組匯總第一門課6個(gè)成績(jī)?nèi)∑骄档诙T課6個(gè)成績(jī)?nèi)∑骄档谌T課5個(gè)成績(jī)?nèi)∑骄?6分組查詢GROUP BYSELECT lno, AVG(Score) AS 課程平均成績(jī)FROM ScoresGROUP BY lno例如：查詢每一門課程的平均成績(jī)17分組查詢思考SELECT Sno, lno, AVG(Score) AS 課程平均成績(jī)FROM ScoresGROUP BY lno思考：執(zhí)行以

5、下的T-SQL： 結(jié)果如何？18分組查詢多列分組怎么樣來(lái)統(tǒng)計(jì)每次的內(nèi)部測(cè)試不同學(xué)員的成績(jī)？第一次內(nèi)部測(cè)試不及格第二次內(nèi)部測(cè)試不及格第三次內(nèi)部測(cè)試不及格補(bǔ)考成績(jī)19分組查詢?cè)倏纯磧?nèi)部測(cè)試成績(jī)還要把這個(gè)加上取平均StudentID和CourseID的組合存在重復(fù)需要按照這兩個(gè)來(lái)進(jìn)行分組，避免StudentID和CourseID 同時(shí)一樣20分組查詢多列分組SELECT StudentID AS 學(xué)員編號(hào),CourseID AS 內(nèi)部測(cè)試, AVG(Score) AS 內(nèi)部測(cè)試平均成績(jī)FROM ScoreGROUP BY StudentID,CourseID21分組查詢問(wèn)題 在以上統(tǒng)計(jì)內(nèi)部測(cè)試成績(jī)的

6、基礎(chǔ)上，如果只想看補(bǔ)考的學(xué)員的成績(jī)，怎么辦？22分組查詢?cè)倏纯丛黾訔l件：要求該學(xué)員的CourseID在分組內(nèi)出現(xiàn)過(guò)一次以上SELECT StudentID AS 學(xué)員編號(hào),CourseID AS 內(nèi)部測(cè)試, AVG(Score) AS 內(nèi)部測(cè)試平均成績(jī)FROM ScoreGROUP BY StudentID,CourseID23分組查詢HAVINGSELECT StudentID AS 學(xué)員編號(hào),CourseID AS 內(nèi)部測(cè)試, AVG(Score) AS 內(nèi)部測(cè)試平均成績(jī)FROM ScoreGROUP BY StudentID,CourseIDHAVING COUNT(Score) 124

7、分組查詢HAVINGSELECT Sno,AVG(Score) AS 平均成績(jī)FROM ScoresGROUP BY SnoHAVING AVG(Score) 60例：查詢選修課程平均成績(jī)?cè)?0分以上的學(xué)生學(xué)號(hào)和平均成績(jī)。25分組查詢對(duì)比WHEREGROUP BYHAVINGWHERE子句：從數(shù)據(jù)源中去掉不符合其搜索條件的數(shù)據(jù)GROUP BY子句：搜集數(shù)據(jù)行到各個(gè)組中，統(tǒng)計(jì)函數(shù)為各個(gè)組計(jì)算統(tǒng)計(jì)值HAVING子句：去掉不符合其組搜索條件的各組數(shù)據(jù)行26分組查詢思考SELECT 部門編號(hào), COUNT(*)FROM 員工信息表WHERE 工資 = 2000GROUP BY 部門編號(hào)HAVING C

8、OUNT(*) 1思考：在員工信息表中包含員工編號(hào)、員工姓名、部門編號(hào)、工資等字段，分析以下T-SQL的含義。分析：有多個(gè)員工的工資不低于2000的部門編號(hào)。27小結(jié)以下的查詢語(yǔ)句將輸出什么信息？SELECT lno,MAX(score)FROM scoresgroup by lno28四、多表聯(lián)結(jié)查詢問(wèn)題：查詢成績(jī)時(shí)每次顯示的都是學(xué)生的學(xué)號(hào)，因?yàn)閟cores表中只存儲(chǔ)了學(xué)生的學(xué)號(hào)，實(shí)際上最好顯示學(xué)生的姓名，而姓名存儲(chǔ)在students表中。那么，如何同時(shí)從這兩個(gè)表中取得數(shù)據(jù)呢？29多表聯(lián)接查詢分類內(nèi)聯(lián)接(INNER JOIN)外聯(lián)接左外聯(lián)接 (LEFT JOIN)右外聯(lián)接 (RIGHT JO

9、IN)完整外聯(lián)接(FULL JOIN)交叉聯(lián)接(CROSS JOIN)30多表聯(lián)接查詢內(nèi)聯(lián)接-1SELECT S.sno,S.name,C.lno,C.Score FROM Scores AS CINNER JOIN Students AS SON C.Sno = S.Sno例如：查詢所有學(xué)生的學(xué)號(hào)、姓名、選修的課程號(hào)及成績(jī)。SELECT Students.sno, S, scores.lno,scores.Score FROM ScoresINNER JOIN StudentsON students.Sno = Scores.Sno還可以給表定義別名：31還可以寫為：

10、SELECT Students.sno, S, scores.lno,scores.Score FROM Scores，StudentsWHERE students.Sno = Scores.Sno32再猜一猜：以下返回多少行？SELECT S.Name,C.lno,C.Score FROM Students AS SINNER JOIN Scores AS CON C.Sno S.Sno猜一猜：這樣寫，返回的查詢結(jié)果是一樣的嗎？SELECT S.Name,C.lno,C.Score FROM Students AS SINNER JOIN ScoreS AS CON

11、C.Sno = S.Sno33多表聯(lián)接查詢?nèi)砺?lián)接SELECT Students.Name AS 姓名, lessons.lName AS 課程, Scores.Score AS 成績(jī)FROM StudentsINNER JOIN Scores ON (Students.Sno = Scores.Sno)INNER JOIN lessons ON (Scores.lno = lessons.lno)例：查詢學(xué)生的姓名、選修的課程名及成績(jī)。34還可以寫為：SELECT Students.Name AS 姓名, lessons.lName AS 課程, Scores.Score AS 成績(jī)FROM

12、 Students, Scores , lessonsWHERE Students.Sno = Scores.Sno AND Scores.lno = lessons.lno35ScoresSnolnoScore122300100100200297896776300381多表聯(lián)接查詢左外聯(lián)接StundentsName梅超風(fēng)陳玄風(fēng)陸乘風(fēng)曲靈風(fēng)Sno1234查詢結(jié)果Name梅超風(fēng)陳玄風(fēng)陳玄風(fēng)陸乘風(fēng)lnoScore00100100200297896776陸乘風(fēng)00381曲靈風(fēng)NULLNULL36SELECT S.Name,C.lno,C.Score FROM Students AS SLEFT JO

13、IN Scores AS CON C.Sno = S.Sno猜一猜：這樣寫，返回的查詢結(jié)果是一樣的嗎？SELECT S.Name,C.lno,C.Score FROM Scores AS CLEFT JOIN Students AS SON C.Sno = S.Sno37多表聯(lián)接查詢右外聯(lián)接SELECT scores.lno, scores.score, lessons.lname,lessons.creditFROM scores RIGHT OUTER JOIN lessons ON scores.lno = lessons.lno38案例分析1要求在數(shù)據(jù)庫(kù)表中，數(shù)據(jù)行位置并不重要但是一個(gè)

14、單位中要根據(jù)奇數(shù)行和偶數(shù)行的數(shù)據(jù)來(lái)匯總,在這個(gè)匯總的基礎(chǔ)上再得到一個(gè)數(shù)值因此，要查詢數(shù)據(jù)庫(kù)表的奇數(shù)行和偶數(shù)行的總數(shù)數(shù)據(jù)庫(kù)表名：TBL字段名：A主鍵字段：IDKEY（標(biāo)識(shí)列，種子：1；增長(zhǎng)量：1）39案例分析1分析依靠標(biāo)識(shí)列的值來(lái)進(jìn)行判斷和選取數(shù)據(jù)行可能存在增加、修改和刪除，因此標(biāo)識(shí)列的數(shù)據(jù)值并不“完全可靠”：例如標(biāo)識(shí)列值為3，并不一定是第三行如果第二行被刪除了，它就是第二行，實(shí)際也是偶數(shù)行使用SELECTINTO，創(chuàng)建一張新表，同時(shí)創(chuàng)建新的標(biāo)識(shí)列，再在新的標(biāo)識(shí)列上執(zhí)行奇偶判斷奇數(shù)判斷依據(jù)：標(biāo)識(shí)列值%2不等于0偶數(shù)判斷依據(jù)：標(biāo)識(shí)列值%2等于040案例分析1T-SQLSELECT A, IDENT

15、ITY(int,1,1) AS ID INTO TEMPTABLE FROM TBLSELECT SUM(A) AS 奇數(shù)列匯總 FROM TEMPTABLE WHERE ID%2 0SELECT SUM(A) FROM AS 偶數(shù)列匯總 FROMTEMPTABLE WHERE ID%2 = 041案例分析2要求一家銀行發(fā)行了新的信用卡，剛開(kāi)始的時(shí)候推廣得很好，但是逐漸廢卡也越來(lái)越多（卡上的余額少于2元，并且用戶長(zhǎng)時(shí)間不使用該卡），因此銀行在二月份把這些少于2元的卡都從數(shù)據(jù)庫(kù)表中刪除了，但是很快問(wèn)題就來(lái)了，用戶發(fā)現(xiàn)他的卡再也不能使用而投訴，因此只能再把這些卡恢復(fù)。編寫SQL，把CardID為2

16、3、30等在子表中沒(méi)有的記錄插入到子表中，插入的數(shù)據(jù)Score項(xiàng)值為242案例分析2分析數(shù)據(jù)插入的操作使用INSERT語(yǔ)句從其它表中篩選數(shù)據(jù)再插入使用INSERT INTOSELECT結(jié)構(gòu)43插入的數(shù)據(jù)項(xiàng)子表中沒(méi)有，需要找到子表中沒(méi)有的數(shù)據(jù)：不考慮整個(gè)語(yǔ)句的INSERT（前半部分）先考慮SELECT（后半部分）SELECT的任務(wù)就是找出兩個(gè)表中不同的項(xiàng)使用INNER JOINON可以找出相同的項(xiàng)，編寫T-SQL：SELECT Users.CardID,2 FROM Users INNER JOIN Account ON (Account.CardID = Users.CardID)44案例分析

17、2分析把“=” 改為“”，找到的項(xiàng)比原來(lái)的要多很多：兩個(gè)表之間是進(jìn)行“交叉”對(duì)比的，而不是一一對(duì)比對(duì)比出很多“不同”的項(xiàng)來(lái)這種方法不可行45考慮左外連接查詢：能夠查詢出左表中存在而相關(guān)表不存在的數(shù)據(jù)項(xiàng)子表中不存在的數(shù)據(jù)項(xiàng)：加WHERE條件來(lái)進(jìn)行篩選S.CardID IS NULL可以判斷其是否在子表中存在46案例分析2T-SQLINSERT INTO Account (Account.CardID, Account.Score) SELECT Users.CardID,2 FROM Users LEFT JOIN Account ON (Account.CardID= Users.CardID)WHERE Account.CardID IS NULL47總結(jié)聚合函數(shù)都有哪些？在一個(gè)SQL語(yǔ)句中，Group By、Where、Having這三部分正確使用次序是什么？學(xué)生會(huì)參加多次考試，查詢每個(gè)學(xué)生的平均成績(jī)的SQL語(yǔ)句怎么寫？SELECT sno,