第4章數(shù)據(jù)庫的基本應(yīng)用(42-43)

1、韓山師范學(xué)院任課人：任課人：cws第第4 4章章數(shù)據(jù)庫的基本應(yīng)用數(shù)據(jù)庫的基本應(yīng)用數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server章節(jié)內(nèi)容章節(jié)內(nèi)容4.1 管理表管理表4.2 數(shù)據(jù)查詢數(shù)據(jù)查詢4.3 數(shù)據(jù)查詢?nèi)蝿?wù)實現(xiàn)數(shù)據(jù)查詢?nèi)蝿?wù)實現(xiàn)4.4 數(shù)據(jù)更新數(shù)據(jù)更新4.5 數(shù)據(jù)更新任務(wù)實現(xiàn)數(shù)據(jù)更新任務(wù)實現(xiàn)4.6 課堂實踐課堂實踐數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2 4.2 數(shù)據(jù)查詢數(shù)據(jù)查詢4.2.1 單表無條件查詢單表無條件查詢4.2.2 單表有條件查詢單表有條件查詢4.2.3 聚集函數(shù)的使用聚集函數(shù)的使用4.2.4 分

2、組與排序分組與排序4.2.5 多表連接查詢多表連接查詢4.2.6 嵌套查詢嵌套查詢4.2.7 集合查詢集合查詢數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2 4.2 數(shù)據(jù)查詢數(shù)據(jù)查詢學(xué)生成績管理系統(tǒng)中，肯定設(shè)計到包括學(xué)生基本信息瀏學(xué)生成績管理系統(tǒng)中，肯定設(shè)計到包括學(xué)生基本信息瀏覽和查詢、成績查詢、各種數(shù)據(jù)的統(tǒng)計等內(nèi)容，如：覽和查詢、成績查詢、各種數(shù)據(jù)的統(tǒng)計等內(nèi)容，如：這些操作應(yīng)該這些操作應(yīng)該如何實現(xiàn)呢？如何實現(xiàn)呢？數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2 4.2 數(shù)據(jù)查

3、詢數(shù)據(jù)查詢數(shù)據(jù)查詢是數(shù)據(jù)庫中最常見的操作。數(shù)據(jù)查詢是數(shù)據(jù)庫中最常見的操作。 SQL語言是通過語言是通過SELECT語句語句來實現(xiàn)數(shù)據(jù)查來實現(xiàn)數(shù)據(jù)查詢的；詢的； 基本語法如下：基本語法如下：數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.1 4.2.1 單表無條件查詢單表無條件查詢1. 語法格式語法格式 SELECT ALL|DISTINCT TOP NPERCENT AS , AS ,. FROM 2. 說明說明 (1) ALL：表示輸出所有記錄，包括重復(fù)記錄。默認(rèn)值為：表示輸出所有記錄，包括重復(fù)記錄。默認(rèn)值為ALL。 DISTINC

4、T：表示在查詢結(jié)果中去掉重復(fù)值。：表示在查詢結(jié)果中去掉重復(fù)值。 (2) TOP N：返回查詢結(jié)果集中的前：返回查詢結(jié)果集中的前N 行。行。 加加PERCENT：返回查詢結(jié)果集中的前：返回查詢結(jié)果集中的前N%行。行。N的取值范圍是的取值范圍是0100 (3) 選項：查詢結(jié)果集中的輸出列?？蔀樽侄蚊?、表達(dá)式或函數(shù)。選項：查詢結(jié)果集中的輸出列。可為字段名、表達(dá)式或函數(shù)。用用“*”表示表中的所有字段表示表中的所有字段。若選項為表達(dá)式或函數(shù)，輸出的列名系統(tǒng)。若選項為表達(dá)式或函數(shù)，輸出的列名系統(tǒng)自動給出，不是原字段名，故用自動給出，不是原字段名，故用AS重命名。重命名。 (4) 顯示列名：在輸出結(jié)果中，設(shè)

5、置選項顯示的列名。用引號定界或不顯示列名：在輸出結(jié)果中，設(shè)置選項顯示的列名。用引號定界或不定定 界。界。 (5) 表名：要查詢的表。表不需打開，到當(dāng)前路徑下尋找表所對應(yīng)的文表名：要查詢的表。表不需打開，到當(dāng)前路徑下尋找表所對應(yīng)的文件。件。數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.1 4.2.1 單表無條件查詢單表無條件查詢1) 查詢指定列查詢指定列 【例例4-6】查詢查詢?nèi)w全體學(xué)生的學(xué)生的學(xué)號學(xué)號和和姓名姓名。 【例例4-7】查詢?nèi)w學(xué)生的姓名、學(xué)號、所在查詢?nèi)w學(xué)生的姓名、學(xué)號、所在系。系。SELECT sno,snameF

6、ROM studentSELECT sname,sno,sdeptFROM student數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.1 4.2.1 單表無條件查詢單表無條件查詢1) 查詢指定列查詢指定列 【例例4-8】查詢選修了課程的學(xué)生學(xué)號查詢選修了課程的學(xué)生學(xué)號(本例是本例是對表對表4-12 成績表成績表sc的查詢）的查詢）SELECT DISTINCT snoFROM sc數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.1 4.2.1 單表無條件查詢單表無條件查詢

7、2) 查詢?nèi)苛胁樵內(nèi)苛?【例例4-9】 查詢?nèi)w學(xué)生的詳細(xì)記錄。查詢?nèi)w學(xué)生的詳細(xì)記錄。 【例例4-10】 輸出學(xué)生表中的前輸出學(xué)生表中的前10條記錄。條記錄。SELECT * FROM studentSELECT TOP 10 * FROM student思考：若是查詢前思考：若是查詢前10%的記錄呢？的記錄呢？數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.1 4.2.1 單表無條件查詢單表無條件查詢3) 查詢經(jīng)過計算的列查詢經(jīng)過計算的列 【例例4-11】 查詢?nèi)w學(xué)生的姓名及其年齡查詢?nèi)w學(xué)生的姓名及其年齡SELECT sn

8、ame,YEAR(GETDATE()-YEAR(sbirthday) AS 年齡年齡FROM student數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.1 4.2.1 單表無條件查詢單表無條件查詢3) 查詢經(jīng)過計算的列查詢經(jīng)過計算的列 【例例4-12】 查詢?nèi)w學(xué)生的姓名、出生年份和所在系查詢?nèi)w學(xué)生的姓名、出生年份和所在系，要求用小寫字母表示所有系名，同時為姓名列指定，要求用小寫字母表示所有系名，同時為姓名列指定別名為別名為NAME，出生年份所在列指定別名為年份，系，出生年份所在列指定別名為年份，系別所在列指定別名為系別。別所在

9、列指定別名為系別。SELECT sname NAME,出生年份出生年份: AS 生日生日,年份年份=YEAR(sbirthday),LOWER(sdept) 系別系別FROM student注意：指定列名的注意：指定列名的3種方法，教材有誤！種方法，教材有誤！數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.1 4.2.1 單表無條件查詢單表無條件查詢3) 查詢經(jīng)過計算的列查詢經(jīng)過計算的列 【例例4-13】 將將sc表中的學(xué)生成績增加表中的學(xué)生成績增加20%后后輸出。輸出。SELECT sno,cno,degree*1.2 as 成績成

10、績FROM SC數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.1 4.2.1 單表無條件查詢單表無條件查詢4. 查詢結(jié)果的輸出查詢結(jié)果的輸出 Transact-SQL提供了一個擴展特性，允許用戶使用提供了一個擴展特性，允許用戶使用SELECT語句查詢得到的結(jié)果記錄來創(chuàng)建一個新的數(shù)語句查詢得到的結(jié)果記錄來創(chuàng)建一個新的數(shù)據(jù)表據(jù)表，創(chuàng)建新表使用，創(chuàng)建新表使用INTO子句。子句。 INTO子句子句不能單獨使用，它包含在不能單獨使用，它包含在SELECT語句中。一般用于創(chuàng)建一個語句中。一般用于創(chuàng)建一個臨時表臨時表。 INTO子句的語法格式如下

11、：子句的語法格式如下： INTO 數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.1 4.2.1 單表無條件查詢單表無條件查詢4. 查詢結(jié)果的輸出查詢結(jié)果的輸出 【例例4-14】使用使用INTO子句創(chuàng)建一個新表，存子句創(chuàng)建一個新表，存放放student表中的姓名和系別兩列。表中的姓名和系別兩列。SELECT sname,sdept INTO studtemp FROM Student數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.2 4.2.2 單表有條件查詢單表有條件查詢1

12、. 語法格式語法格式SELECT ALL|DISTINCT AS , AS,.FROM WHERE 數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.2 4.2.2 單表有條件查詢單表有條件查詢2. WHERE條件中的運算符條件中的運算符數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.2 4.2.2 單表有條件查詢單表有條件查詢1) 比較運算符比較運算符 使用比較運算符實現(xiàn)對查詢條件進(jìn)行限定，使用比較運算符實現(xiàn)對查詢條件進(jìn)行限定，其語法格式如下：其語法格式如下： WHERE 表

13、達(dá)式表達(dá)式1 比較運算符比較運算符 表達(dá)式表達(dá)式2 【例例4-15】 查詢所有男生的信息。查詢所有男生的信息。SELECT * FROM studentWHERE ssex=男男數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.2 4.2.2 單表有條件查詢單表有條件查詢1) 比較運算符比較運算符 使用比較運算符實現(xiàn)對查詢條件進(jìn)行限定，使用比較運算符實現(xiàn)對查詢條件進(jìn)行限定，其語法格式如下：其語法格式如下： WHERE 表達(dá)式表達(dá)式1 比較運算符比較運算符 表達(dá)式表達(dá)式2 【例例4-16】 查詢所有成績大于查詢所有成績大于80分的學(xué)生的分

14、的學(xué)生的學(xué)號和成績。學(xué)號和成績。SELECT sno AS 學(xué)號學(xué)號,degree 成績成績FROM scWHERE degree80數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.2 4.2.2 單表有條件查詢單表有條件查詢1) 比較運算符比較運算符 【例例4-17】 查詢所有男生的學(xué)號、姓名、系查詢所有男生的學(xué)號、姓名、系別及出生日期。別及出生日期。【例例4-184-18】 查詢計算機系全體學(xué)生的名單。查詢計算機系全體學(xué)生的名單。SELECT sno, sname,sdept,sbirthFROM studentWHERE sse

15、x=男男SELECT snameFROM studentWHERE sdept=計算機系計算機系數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.2 4.2.2 單表有條件查詢單表有條件查詢1) 比較運算符比較運算符 【例例4-19】 查詢考試成績不及格的學(xué)生的學(xué)查詢考試成績不及格的學(xué)生的學(xué)號。號。SELECT DISTINCT snoFROM scWHERE degree90 or degree60數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.2 4.2.2 單表有條件查詢

16、單表有條件查詢2) 邏輯運算符邏輯運算符 【例例4-22】 查詢非計算機系的學(xué)生信息。查詢非計算機系的學(xué)生信息。SELECT * FROM student WHERE NOT sdept=計算機系計算機系或：或： SELECT * FROM student WHERE sdept計算機系計算機系數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.2 4.2.2 單表有條件查詢單表有條件查詢課堂練習(xí)：課堂練習(xí)：數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.2 4.2.2 單表有條

17、件查詢單表有條件查詢3) 范圍運算符范圍運算符(BETWEEN AND) 在在WHERE子句中使用子句中使用BETWEEN關(guān)鍵字查關(guān)鍵字查找在某一范圍內(nèi)的數(shù)據(jù)，也可以使用找在某一范圍內(nèi)的數(shù)據(jù)，也可以使用NOT BETWEEN關(guān)鍵字查找不在某一范圍內(nèi)的數(shù)關(guān)鍵字查找不在某一范圍內(nèi)的數(shù)據(jù)。據(jù)。 其語法格式如下：其語法格式如下： WHERE 表達(dá)式表達(dá)式 NOT BETWEEN 初始值初始值 AND 終止值終止值數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.2 4.2.2 單表有條件查詢單表有條件查詢3) 范圍運算符范圍運算符(BETWEE

18、N AND) 【例例4-23】查詢成績在查詢成績在6070分之間的學(xué)生分之間的學(xué)生學(xué)號及成績。學(xué)號及成績。SELECT sno, degreeFROM scWHERE degree BETWEEN 60 AND 70條件表達(dá)式的另一種表示方法是：條件表達(dá)式的另一種表示方法是：degree=60 AND degree數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.4 4.2.4 分組與排序分組與排序2. 對查詢結(jié)果集進(jìn)行排序?qū)Σ樵兘Y(jié)果集進(jìn)行排序 用戶可以利用用戶可以利用ORDER BY子句子句對查詢結(jié)果按對查詢結(jié)果按照一個或多個字段進(jìn)

19、行升序照一個或多個字段進(jìn)行升序(ASC)或降序或降序(DESC)排序，默認(rèn)值為升序。排序，默認(rèn)值為升序。 語句語法為：語句語法為： ORDER BY ASC|DESC, ASC|DESC,數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.4 4.2.4 分組與排序分組與排序2. 對查詢結(jié)果集進(jìn)行排序?qū)Σ樵兘Y(jié)果集進(jìn)行排序 【例例4-43】 查詢選修了查詢選修了C03號課程的學(xué)生的號課程的學(xué)生的學(xué)號及其成績，查詢結(jié)果按分?jǐn)?shù)的降序排列學(xué)號及其成績，查詢結(jié)果按分?jǐn)?shù)的降序排列。SELECT sno,degreeFROM scWHERE cno=C

20、03ORDER BY degree DESC數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.4 4.2.4 分組與排序分組與排序2. 對查詢結(jié)果集進(jìn)行排序?qū)Σ樵兘Y(jié)果集進(jìn)行排序 【例例4-44】 查詢?nèi)w學(xué)生情況，查詢結(jié)果按查詢?nèi)w學(xué)生情況，查詢結(jié)果按所在系升序排列，同一系中的學(xué)生按出生日所在系升序排列，同一系中的學(xué)生按出生日期降序排列。期降序排列。SELECT * FROM studentORDER BY sdept ASC, sbirth DESC數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL

21、 Server4.2.5 4.2.5 多表連接查詢多表連接查詢多表連接查詢多表連接查詢 指查詢同時涉及兩個或兩個以上的表；指查詢同時涉及兩個或兩個以上的表；分為：交叉連接、內(nèi)連接、自連接和外連分為：交叉連接、內(nèi)連接、自連接和外連接。接。數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.5 4.2.5 多表連接查詢多表連接查詢廣義笛卡兒積：廣義笛卡兒積： 兩個分別為兩個分別為n目目(即關(guān)系具有即關(guān)系具有n個屬性個屬性)和和m目的目的關(guān)系關(guān)系R和和S的廣義笛卡兒積是一個的廣義笛卡兒積是一個(n+m)列的列的元組的集合。元組的集合。 記作：記作：RS= tr R ts S RS

22、元組的前元組的前n列是關(guān)系列是關(guān)系R的一個元組，后的一個元組，后m列是關(guān)系列是關(guān)系S的一個元組。的一個元組。 若若R有有k1個元組，個元組，S有有k2個元組，則關(guān)系個元組，則關(guān)系R和和關(guān)系關(guān)系S的廣義笛卡兒積有的廣義笛卡兒積有klk2個元組。個元組。ttsr數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.5 4.2.5 多表連接查詢多表連接查詢廣義笛卡兒積：廣義笛卡兒積： RSR.AR.BR.CS.AS.BS.Ca1b1c1a1b2c2a1b1c1a1b3c2a1b1c1a2b2c1a1b2c2a1

23、b2c2a1b2c2a1b3c2a1b2c2a2b2c1a2b2c1a1b2c2a2b2c1a1b3c2a2b2c1a2b2c1ABCABCa1b1c1a1b2c2a1b2c2a1b3c2a2b2c1a2b2c1數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.5 4.2.5 多表連接查詢多表連接查詢1. 交叉連接交叉連接 交叉連接又稱笛卡兒連接，是指兩個表之間做交叉連接又稱笛卡兒連接，是指兩個表之間做笛卡兒笛卡兒積操作積操作，得到結(jié)果集的行數(shù)是兩個表的行數(shù)的乘積。，得到結(jié)果集的行數(shù)是兩個表的行數(shù)的乘積。 命令的一般格式如下命令的一般格式如下:SELECT ALL|DIS

24、TINCT 別名別名. AS ,別名別名. AS. FROM 別名別名1 ,別名別名2 需要連接查詢的表名在需要連接查詢的表名在FROM子句中指定，表名之間子句中指定，表名之間用英文逗號隔開；用英文逗號隔開； 如果表名指定了別名，在該命令中，都必須用別名代如果表名指定了別名，在該命令中，都必須用別名代替表名替表名。數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.5 4.2.5 多表連接查詢多表連接查詢1. 交叉連接交叉連接 【例例4-45】 成績表成績表(sc)和課程名稱表和課程名稱表(course)進(jìn)行交叉連接。進(jìn)行交叉連接。SEL

25、ECT A.*, B.* FROM course A, sc B數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.5 4.2.5 多表連接查詢多表連接查詢2. 內(nèi)連接內(nèi)連接 內(nèi)連接命令的一般格式如下：內(nèi)連接命令的一般格式如下： SELECT ALL|DISTINCT 別名別名.AS ,別名別名.AS,. FROM 別名別名1, 別名別名2, WHERE AND 或者為：或者為： SELECT ALL|DISTINCT 別名別名.AS ,別名別名.AS,. FROM 別名別名1 INNER JOIN 別名別名2 ON WHERE 4.14

26、.4.6數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.5 4.2.5 多表連接查詢多表連接查詢2. 內(nèi)連接內(nèi)連接 連接條件表達(dá)式的一般格式為：連接條件表達(dá)式的一般格式為： 比較運算符：比較運算符： 使用等號使用等號“=”：等值連接：等值連接 使用不等號：不等值連接使用不等號：不等值連接數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.5 4.2.5 多表連接查詢多表連接查詢2. 內(nèi)連接內(nèi)連接 【例例4-46】 查詢每個學(xué)生及其選修課的情況查詢每個學(xué)生及其選修課的情況 學(xué)生的基本情況存放在學(xué)生

27、的基本情況存放在student表中，選課情況存表中，選課情況存放在放在sc表中，所以查詢過程涉及上述兩個表。這表中，所以查詢過程涉及上述兩個表。這兩個表是通過公共字段兩個表是通過公共字段sno實現(xiàn)內(nèi)連接的。實現(xiàn)內(nèi)連接的。SELECT A.*, B.*FROM student A,sc BWHERE A.sno=B.sno 數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.5 4.2.5 多表連接查詢多表連接查詢自然連接自然連接：若在等值連接中把目標(biāo)列中的：若在等值連接中把目標(biāo)列中的重復(fù)字段去掉，則稱為自然連接。重復(fù)字段去掉，則稱為自然連

28、接?！纠?-47】 用用自然連接自然連接完成查詢完成查詢:查詢每查詢每個學(xué)生及其選修課的情況。個學(xué)生及其選修課的情況。SELECT student.sno, sname, ssex, sbirth, sdept, cno, degreeFROM student, scWHERE student.sno=sc.sno數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.5 4.2.5 多表連接查詢多表連接查詢【例【例4-48】 輸出所有女學(xué)生的學(xué)號、姓輸出所有女學(xué)生的學(xué)號、姓名、課號及成績。名、課號及成績。SELECT A.sno, sna

29、me, cno, degreeFROM student A, sc BWHERE A.sno=B.sno AND ssex=女女SELECT A.sno, sname, cno, degreeFROM student A INNER JOIN sc B ON A.sno=B.sno WHERE ssex=女女?dāng)?shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.5 4.2.5 多表連接查詢多表連接查詢【例【例4-49】 輸出計算機系學(xué)生的學(xué)號、輸出計算機系學(xué)生的學(xué)號、姓名、課程號及成績。姓名、課程號及成績。SELECT A.sno, sna

30、me, cno, degreeFROM student A, sc BWHERE A.sno=B.sno AND sdept=CSSELECT A.sno, sname, cno, degreeFROM student A INNER JOIN sc B ON A.sno=B.snoWHERE sdept=CS數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.5 4.2.5 多表連接查詢多表連接查詢3. 自連接自連接:將同一個表的不同行連接起來將同一個表的不同行連接起來 在自連接中，在自連接中，必須為表指定兩個別名必須為表指定兩個別名，

31、使之，使之在邏輯上成為兩張表。在邏輯上成為兩張表。 自連接的命令的一般格式如下自連接的命令的一般格式如下: SELECT ALL|DISTINCT 別名別名. AS ,別名別名. AS,. FROM 別名別名1, 別名別名2, WHERE AND 數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.5 4.2.5 多表連接查詢多表連接查詢3. 自連接自連接: 【例例4-50】 查詢同時選修了查詢同時選修了C01和和C04課程的學(xué)生學(xué)課程的學(xué)生學(xué)號。號。 【例例4-51】 查詢與劉晨在同一個系學(xué)習(xí)的學(xué)生的姓名查詢與劉晨在同一個系學(xué)習(xí)的學(xué)生的

32、姓名和所在系。和所在系。SELECT sno FROM student A,student BWHERE A.sno=B.sno AND A.cno=C01 AND B.cno=C04SELECT B.sname, B.sdeptFROM student A ,student BWHERE A.sdept=B.sdept AND A.sname=劉晨劉晨 AND B.sname!=劉晨劉晨數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.5 4.2.5 多表連接查詢多表連接查詢4. 外連接外連接 在自然連接中，只有在兩個表中匹配的行才在

33、自然連接中，只有在兩個表中匹配的行才能在結(jié)果集中出現(xiàn)。能在結(jié)果集中出現(xiàn)。 而而在外連接中可以只限制一個表，而對另外在外連接中可以只限制一個表，而對另外一個表不加限制一個表不加限制(所有的行都出現(xiàn)在結(jié)果集中所有的行都出現(xiàn)在結(jié)果集中)。 外連接分為外連接分為左外連接、右外連接和全外連接左外連接、右外連接和全外連接。 數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.5 4.2.5 多表連接查詢多表連接查詢4. 外連接外連接 外連接命令的一般格式如下外連接命令的一般格式如下: SELECT ALL|DISTINCT 別名別名. AS ,別名別

34、名. AS,.FROM LEFT| RIGHT| FULL OUTERJOIN ON =數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.5 4.2.5 多表連接查詢多表連接查詢4. 外連接外連接 【例例4-52】 利用左外連接查詢改寫例利用左外連接查詢改寫例4-46(查查詢每個學(xué)生及其選修課的情況詢每個學(xué)生及其選修課的情況) 。SELECT student.sno,sname,ssex,sbirth,sdept,cno,degreeFROM student LEFT JOIN scON student.sno=sc.sno數(shù)據(jù)庫原理與

35、數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.6 4.2.6 嵌套查詢嵌套查詢在在SQL語言中，一個語言中，一個SELECTFROMWHERE語句語句稱為一個稱為一個查詢塊查詢塊。將一個查詢塊嵌套在另一個查詢塊的。將一個查詢塊嵌套在另一個查詢塊的WHERE子句或子句或HAVING子句的條件中稱為子句的條件中稱為嵌套查詢或嵌套查詢或子查詢子查詢。 例如：例如：SELECT sname FROM studentWHERE sno IN(SELECT sno FROM sc WHERE cno=C02)數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQ

36、L Server4.2.6 4.2.6 嵌套查詢嵌套查詢注意問題：注意問題： SQL語言允許多層嵌套查詢；語言允許多層嵌套查詢； 子查詢的子查詢的SQL語句必須用語句必須用括號定界括號定界； 子查詢不能使用子查詢不能使用ORDER BY子句，子句， ORDER BY子句只能對最終查詢結(jié)果排序；子句只能對最終查詢結(jié)果排序； 嵌套查詢的求解方法是由內(nèi)向外處理；嵌套查詢的求解方法是由內(nèi)向外處理；子查詢一般分為兩種：子查詢一般分為兩種：嵌套子查詢和相關(guān)嵌套子查詢和相關(guān)子查詢子查詢。數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.6 4.2.6

37、 嵌套查詢嵌套查詢1. 嵌套子查詢（不相關(guān)子查詢）嵌套子查詢（不相關(guān)子查詢） 嵌套子查詢的執(zhí)行不依賴于外部嵌套。嵌套子查詢的執(zhí)行不依賴于外部嵌套。 嵌套子查詢的執(zhí)行過程為：嵌套子查詢的執(zhí)行過程為： 首先執(zhí)行子查詢，子查詢得到的結(jié)果集不被顯示首先執(zhí)行子查詢，子查詢得到的結(jié)果集不被顯示出來，而是傳給外部查詢，作為外部查詢的條件出來，而是傳給外部查詢，作為外部查詢的條件使用，然后執(zhí)行外部查詢，并顯示查詢結(jié)果。子使用，然后執(zhí)行外部查詢，并顯示查詢結(jié)果。子查詢可以多層嵌套。查詢可以多層嵌套。 嵌套子查詢一般也分為兩種：嵌套子查詢一般也分為兩種：子查詢返回單子查詢返回單個值個值和和子查詢返回一個值列表子查

38、詢返回一個值列表。數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.6 4.2.6 嵌套查詢嵌套查詢1）返回單個值）返回單個值 【例例4-53】 查詢所有年齡大于平均年齡的學(xué)查詢所有年齡大于平均年齡的學(xué)生姓名。生姓名。SELECT sname FROM student WHERE year(getdate()-year(sbirthday)(SELECT AVG(year(getdate()-year(sbirthday) FROM student )數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL

39、 Server4.2.6 4.2.6 嵌套查詢嵌套查詢1）返回單個值）返回單個值 【例例4-54】 查詢與劉晨在同一個系學(xué)習(xí)的學(xué)查詢與劉晨在同一個系學(xué)習(xí)的學(xué)生。生。SELECT sno, sname, sdeptFROM student WHERE sdept=(SELECT sdept FROM student WHERE sname=劉晨劉晨)數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.6 4.2.6 嵌套查詢嵌套查詢2) 返回一個值列表返回一個值列表 （1）使用）使用IN操作符操作符的嵌套查詢。的嵌套查詢。 【例例4-55】

40、用用IN操作符改寫例操作符改寫例4-54查詢與查詢與劉晨在同一個系學(xué)習(xí)的學(xué)生。劉晨在同一個系學(xué)習(xí)的學(xué)生。SELECT sno, sname, sdeptFROM student WHERE sdept IN(SELECT sdept FROM student WHERE sname=劉晨劉晨)數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.6 4.2.6 嵌套查詢嵌套查詢2) 返回一個值列表返回一個值列表 （1）使用）使用IN操作符操作符的嵌套查詢。的嵌套查詢。 【例例4-56】 查詢沒有選修數(shù)學(xué)的學(xué)生學(xué)號和查詢沒有選修數(shù)學(xué)的學(xué)生學(xué)號

41、和姓名。姓名。SELECT sno,sname FROM student WHERE sno NOT IN ( SELECT sno FROM sc WHERE cno IN ( SELECT cno FROM course WHERE cname=數(shù)學(xué)數(shù)學(xué))數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.6 4.2.6 嵌套查詢嵌套查詢(2) 帶有帶有ANY或或ALL操作符的子查詢。操作符的子查詢。 ANY和和ALL操作符操作符在使用時必須和比較運算在使用時必須和比較運算符一起使用。符一起使用。 其格式為：其格式為：ANY|ALL。

42、數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.6 4.2.6 嵌套查詢嵌套查詢(2) 帶有帶有ANY或或ALL操作符的子查詢。操作符的子查詢。數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.6 4.2.6 嵌套查詢嵌套查詢(2) 帶有帶有ANY或或ALL操作符的子查詢。操作符的子查詢。 【例例4-57】 查詢其他系中比計算機系某一學(xué)查詢其他系中比計算機系某一學(xué)生年齡小的學(xué)生姓名和年齡。生年齡小的學(xué)生姓名和年齡。SELECT sname,sageFROM studentWHERE sageANY(SELECT

43、sage FROM student WHERE sdept=計算機系計算機系) AND sdept計算機系計算機系 /該句為父查詢該句為父查詢中的一個條件中的一個條件數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.6 4.2.6 嵌套查詢嵌套查詢(2) 帶有帶有ANY或或ALL操作符的子查詢。操作符的子查詢。 【例例4-58】 查詢其他系中比計算機系學(xué)生年查詢其他系中比計算機系學(xué)生年齡都小的學(xué)生。齡都小的學(xué)生。SELECT *FROM studentWHERE sageALL(SELECT sage FROM student WHER

44、E sdept=計算機系計算機系) AND sdept計算機系計算機系數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.6 4.2.6 嵌套查詢嵌套查詢(2) 帶有帶有ANY或或ALL操作符的子查詢。操作符的子查詢。 【例例4-58】 查詢其他系中比計算機系學(xué)生年查詢其他系中比計算機系學(xué)生年齡都小的學(xué)生。齡都小的學(xué)生。 一般聚集函數(shù)實現(xiàn)的子查詢效率高于一般聚集函數(shù)實現(xiàn)的子查詢效率高于ANY或或ALL查詢。查詢。SELECT *FROM studentWHERE sage(SELECT MIN(sage) FROM student WHERE sdept=計算機系計算機系)

45、 AND sdept計算機系計算機系數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.6 4.2.6 嵌套查詢嵌套查詢2. 相關(guān)子查詢相關(guān)子查詢(Correlated Subquery) 在相關(guān)子查詢中，在相關(guān)子查詢中，子查詢的執(zhí)行依賴于外部子查詢的執(zhí)行依賴于外部查詢查詢，即子查詢的查詢條件依賴于外部查詢，即子查詢的查詢條件依賴于外部查詢的某個屬性值。的某個屬性值。 相關(guān)子查詢的子查詢需要重復(fù)被執(zhí)行相關(guān)子查詢的子查詢需要重復(fù)被執(zhí)行，而嵌，而嵌套子查詢的子查詢只需被執(zhí)行一次套子查詢的子查詢只需被執(zhí)行一次! 在相關(guān)子查詢中，經(jīng)常要用到在相關(guān)子查詢中，經(jīng)常要用到EXISTS操作

46、符操作符， EXISTS只產(chǎn)生邏輯真值只產(chǎn)生邏輯真值“true”和邏輯假和邏輯假值值“false”,不返回任何實際數(shù)據(jù)。不返回任何實際數(shù)據(jù)。數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.6 4.2.6 嵌套查詢嵌套查詢2. 相關(guān)子查詢相關(guān)子查詢(Correlated Subquery) 【例例4-59】 查詢所有選修了查詢所有選修了C01號課程的學(xué)號課程的學(xué)生姓名。生姓名。SELECT snameFROM studentWHERE EXISTS ( SELECT * FROM sc WHERE sno=student.sno AND

47、 cno=C01) 數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL ServerSQL Server4.2.6 4.2.6 嵌套查詢嵌套查詢2. 相關(guān)子查詢相關(guān)子查詢(Correlated Subquery) 【例例4-60】 查詢選修了全部課程的學(xué)生姓名查詢選修了全部課程的學(xué)生姓名SELECT snameFROM studentWHERE NOT EXISTS (SELECT * FROM course WHERE NOT EXISTS (SELECT * FROM sc WHERE sno=student.sno AND cno=o)數(shù)據(jù)庫原理與數(shù)據(jù)庫原理與SQL Se

48、rverSQL Server4.2.6 4.2.6 嵌套查詢嵌套查詢3. 子查詢規(guī)則子查詢規(guī)則 (1) 通過比較運算符引入的子查詢的選擇列表只能包括一個表達(dá)式或列通過比較運算符引入的子查詢的選擇列表只能包括一個表達(dá)式或列名稱。名稱。 (2) 如果外部查詢的如果外部查詢的WHERE子句包括某個列名，則該子句必須與子查子句包括某個列名，則該子句必須與子查詢選擇列表中的該列兼容。詢選擇列表中的該列兼容。 (3) 子查詢的選擇列表中不允許出現(xiàn)子查詢的選擇列表中不允許出現(xiàn)ntext、text和和image數(shù)據(jù)類型。數(shù)據(jù)類型。 (4) 無修改的比較運算符無修改的比較運算符(即后面未跟關(guān)鍵字即后面未跟關(guān)鍵字 ANY 或或 ALL 的運算符的運算符)引引入的子查詢不能包括入的子查詢不能包括GROUP BY和和HAVING子句。子句。 (5) 包括包括GROUP BY的子查詢不能使用的子