1、數(shù)據(jù)庫查詢是數(shù)據(jù)庫中一個(gè)最重要也是最基本的功能，它是從數(shù)據(jù)庫中檢索符合條件的數(shù)據(jù)記錄的選擇過程。SQL Server 2005的數(shù)據(jù)庫查詢使用T-SQL語言，其基本的查詢語句是SELECT語句。 本章主要介紹SQL Server 2005數(shù)據(jù)庫查詢的方法及使用。,第8章 數(shù)據(jù)庫查詢,8.1 數(shù)據(jù)庫查詢 8.2 數(shù)據(jù)匯總 8.3 排序 8.4 分組 8.5 子查詢 8.6 集合操作 8.7 存儲查詢結(jié)果 8.8 函數(shù)查詢,第8章 數(shù)據(jù)庫查詢,T-SQL語言提供了SELECT語句進(jìn)行數(shù)據(jù)庫的查詢，該語句具有靈活的使用方式和豐富的功能。其語法格式如下： SELECT ALL|DISTINCT TOP

2、 AS , AS FORM ASLocal_Alias INNER | LEFT OUTER | RIGHT OUTER | FULL OUTER JOIN ASLocal_AliasON INTO |TO FILE ADDITIVE | TO PRINTER PROMPT | TO SCREEN PREFERENCE PreferenceNameNOCONSOLEPLAINNOWAIT WHERE AND AND | OR GROUP BY , HAVING UNION ALL ORDER BY ASC |DESC , ,8.1 數(shù)據(jù)庫查詢,8.1.1 選擇列 選擇列指的是通過限定返回結(jié)果的

3、列組成結(jié)果表。 1. 選擇指定列 選擇指定列指的是選擇一個(gè)表中的部分列，各列名之間用逗號隔開。 【例8-1】查詢學(xué)生表中學(xué)生的學(xué)號、姓名和院系名稱。 SELECT 學(xué)號,姓名,院系名稱 FROM 學(xué)生表 GO 【例8-2】查詢教師表中教師的姓名和職稱。 SELECT 姓名,職稱 FROM 教師表 GO,8.1 數(shù)據(jù)庫查詢,2. 選擇所有列 選擇所有列指的是選擇一個(gè)表中的全部列?？梢詫⑺辛忻剂谐觯髁兄g用逗號隔開，也可以使用符號“*”。 【例8-4】查詢所有課程信息。 SELECT 課程號,課程名,學(xué)分,備注 FROM 課程表 GO SELECT * FROM 課程表 GO,8.1 數(shù)據(jù)庫

4、查詢,3. 定義列別名 查詢結(jié)果默認(rèn)輸出的列名都是建表時(shí)的列名。但有時(shí)用戶希望查詢結(jié)果輸出時(shí)，顯示自己指定的列名顯示，這時(shí)就可以定義表列的別名。SELECT語句使用AS關(guān)鍵字來定義別名。 【例8-5】查詢所有學(xué)生的姓名、性別，各列的別名是name、sex。圖8-3 定義表列的別名 SELECT 姓名AS name,性別AS sex FROM 學(xué)生表 GO,8.1 數(shù)據(jù)庫查詢,4. 替換結(jié)果中數(shù)據(jù) 在對表進(jìn)行查詢時(shí)，有時(shí)希望對所查詢的某些列使用表達(dá)式進(jìn)行計(jì)算。SELECT語句支持表達(dá)式的使用。 【例8-8】查詢所有學(xué)生的分?jǐn)?shù)信息，如果分?jǐn)?shù)大于等于80，則為“優(yōu)秀”；大于等于60，則為“及格”；小

5、于60，則為“不及格”。使用CASE函數(shù)給每個(gè)學(xué)生的分?jǐn)?shù)設(shè)定等級。 SELECT 學(xué)號,課程號,分?jǐn)?shù),等級= CASE WHEN 分?jǐn)?shù)=80 THEN 優(yōu)秀 WHEN 分?jǐn)?shù)=60 THEN 及格 ELSE 不及格 END FROM 成績表 GO,8.1 數(shù)據(jù)庫查詢,8.1.2 選擇行 選擇行指的是通過限定返回結(jié)果的行組成結(jié)果表。選擇行可以和選擇列一起使用。 1. 消除結(jié)果中重復(fù)行 在對表進(jìn)行查詢時(shí)，有時(shí)查詢結(jié)果有許多重復(fù)行。SELECT語句使用DISTINCT關(guān)鍵字消除結(jié)果中的重復(fù)行。其語法格式如下： DISTINCT column_name ,column_name 說明：DISTINCT關(guān)

6、鍵字對后面的所有列消除重復(fù)行。一個(gè)SELECT語句中DISTINCT只能出現(xiàn)一次，而且必須放在所有列名之前。 【例8-10】查詢所有學(xué)生所在的院系名稱。 SELECT DISTINCT 院系名稱 FROM 學(xué)生表 GO,8.1 數(shù)據(jù)庫查詢,2. 限制結(jié)果返回行數(shù) 如果SELECT語句返回結(jié)果有很多行，可以使用TOP關(guān)鍵字限定返回行數(shù)。在SQL Server 2000中，關(guān)鍵字TOP后只能常數(shù)數(shù)值。在SQL Server 2005中進(jìn)行了改進(jìn)，TOP后還可以使用數(shù)值表達(dá)式。其語法格式如下： TOP n PERCENT 說明：其中n表示返回結(jié)果的前n行，n PERCENT表示返回結(jié)果的前n%行。

7、【例8-12】查詢教師表中前3名教師的信息。 SELECT TOP 3 * FROM 教師表 GO,8.1 數(shù)據(jù)庫查詢,3. 限制結(jié)果返回行的條件 在限定返回結(jié)果的行操作時(shí)，最重要的就是通過條件限制，SELECT語句中WHERE子句是最常用、最重要的條件子句。在WHERE子句指出查詢的條件，系統(tǒng)找出符合條件的結(jié)果。其語法格式如下： WHERE AND AND | OR (1) 表達(dá)式比較 在WHERE子句對表達(dá)式比較，可以使用比較運(yùn)算符和邏輯運(yùn)算符。 【例8-14】查詢5號課程分?jǐn)?shù)小于60的學(xué)生的學(xué)號、課程號和分?jǐn)?shù)。圖8-8 輸出比較結(jié)果 SELECT 學(xué)號,課程號,分?jǐn)?shù) FROM 成績表 W

8、HERE 分?jǐn)?shù)60 AND 課程號=5,8.1 數(shù)據(jù)庫查詢,(2) 限制取值 在查詢數(shù)據(jù)時(shí)，如果條件較多，得使用多個(gè)OR運(yùn)算符，這樣就使代碼顯得冗長。T-SQL提供了IN關(guān)鍵字來取代多個(gè)OR運(yùn)算符，表示如果表達(dá)式的取值與值表中的任意一個(gè)值匹配即返回TRUE。 【例8-17】查詢計(jì)算機(jī)學(xué)院和外語學(xué)院學(xué)生的信息。 SELECT * FROM 學(xué)生表 WHERE 院系名稱IN (計(jì)算機(jī)學(xué)院,外語學(xué)院) GO,8.1 數(shù)據(jù)庫查詢,(3) 限制范圍 在查詢數(shù)據(jù)時(shí)，有時(shí)需要限定范圍。T-SQL提供了BETWEEN關(guān)鍵字來取限制范圍，可以取代多個(gè)關(guān)系運(yùn)算符和邏輯運(yùn)算符（即等同于使用=、=和AND限制的范圍）

9、，表示如果表達(dá)式的取值在限定范圍中即返回TRUE。 【例8-19】查詢在20世紀(jì)70年代出生的教師信息。 SELECT * FROM 教師表 WHERE YEAR(出生日期) BETWEEN 1975 AND 1979 GO,8.1 數(shù)據(jù)庫查詢,(4) 模式匹配 在查詢數(shù)據(jù)時(shí)，有時(shí)并不知道查詢范圍，只知道查詢的模式。T-SQL提供了LIKE關(guān)鍵字來模式匹配查詢。LIKE關(guān)鍵字只能用于匹配字符串?dāng)?shù)據(jù)。,8.1 數(shù)據(jù)庫查詢,【例8-21】查詢姓“王”的男生的信息。 SELECT * FROM 學(xué)生表 WHERE 姓名LIKE 王% AND 性別=男 GO 【例8-23】查詢查詢工作人員表中，不姓“

10、張”的職工編號和姓名。 SELECT 人員編號,姓名 FROM 工作人員表 WHERE 姓名LIKE 張% GO,8.1 數(shù)據(jù)庫查詢,(5) 空值處理 當(dāng)需要判斷一個(gè)表達(dá)式或表數(shù)據(jù)是否為空，T-SQL提供了IS NULL或NULL關(guān)鍵字來判斷。 【例8-24】查詢成績表中分?jǐn)?shù)為空的學(xué)生的學(xué)號、課程號。 SELECT 學(xué)號,課程號 FROM 成績表 WHERE 分?jǐn)?shù)IS NULL GO,8.1 數(shù)據(jù)庫查詢,8.1.3 連接 在實(shí)際應(yīng)用中，數(shù)據(jù)查詢往往會涉及到多個(gè)表，這就需要將多個(gè)表連接起來進(jìn)行查詢。這種連接分為交叉連接、內(nèi)連接、外連接和自連接4種。交叉連接相當(dāng)于作笛卡兒乘積，內(nèi)連接又分為相等連接

11、、自然連接。外連接分為左外連接、右外連接和全外連接。,8.1 數(shù)據(jù)庫查詢,1 交叉連接,語法: SELECT 列名列表 FROM 表名1 CROSS JOIN 表名2 或者 SELECT 列名列表 FROM 表名, 表名2 交叉連接的結(jié)果是兩個(gè)表的笛卡兒積，在實(shí)際應(yīng)用中一般是沒有意義的，但在數(shù)據(jù)庫的數(shù)學(xué)模式上有重要的作用。連接后該結(jié)果集的行數(shù)等于兩個(gè)表的行數(shù)積，列屬等于兩個(gè)表的列數(shù)和。它是其他連接運(yùn)算的基礎(chǔ)。,例如:對學(xué)生表和課程表進(jìn)行交叉連接 方法1: Select 學(xué)生表.*,課程表.* from 學(xué)生表 Join 課程表 方法2: Select學(xué)生表.*,課程表.* from 學(xué)生表,課

12、程表,因?yàn)橐昧硕鄠€(gè)表,為了簡化select子句。因此，在from子句中給每個(gè)表起一個(gè)別名，select子句中可以通過別名引用表。 Select a.*,b.* from 學(xué)生表as a,課程表 as b,2 內(nèi)連接,內(nèi)連接就是只包含滿足連接條件的數(shù)據(jù)行，是將交叉連接結(jié)果集按照連接條件進(jìn)行過濾的結(jié)果，也稱自然連接。連接條件通常采用“主鍵=外鍵”的形式。內(nèi)連接有以下兩種語法格式。 SELECT 列名列表 FROM 表名1 INNER JOIN 表名2 ON 表名1.列名=表名2.列名 或 SELECT 列名列表 FROM 表名1, 表名2 WHERE 表名1.列名=表名2.列名,例如：查詢每個(gè)學(xué)

13、生選修課程的信息，查詢結(jié)果包括學(xué)生的姓名、所選課程的課程名。 分析：姓名在學(xué)生表中，課程名在課程表中。所以本查詢用到兩個(gè)表，要用內(nèi)連接將它們連接起來，但因?yàn)檫@兩表不存在共同的列，不能進(jìn)行自然連接。因此要加入中間表，中間表中應(yīng)含有能與其它兩個(gè)表進(jìn)行自然連接的公共列，這個(gè)表就是成績表。 方法1： select a.姓名,c.課程名 from 學(xué)生表 as a inner join 成績表 as b on a.學(xué)號=b.學(xué)號 inner join 課程表 as c on b.課程號=c.課程號,方法2： select a.姓名,c.課程名 from 學(xué)生表 as a， 成績表 as b ，課程表 a

14、s c Where a.學(xué)號=b.學(xué)號 and b.課程號=c.課程號,例2：查詢外語學(xué)院的學(xué)生選修課程但分?jǐn)?shù)不及格的信息。查詢結(jié)果包括姓名和課程名 分析：該題是在上述多表連接的基礎(chǔ)上增加兩個(gè)條件，一是學(xué)生所在院系為外語系：學(xué)生表.院系名稱=外語系；二是分?jǐn)?shù)不及格：成績表.分?jǐn)?shù)60 方法1： select a.姓名,c.課程名 from 學(xué)生表 as a inner join 成績表 as b on a.學(xué)號=b.學(xué)號 inner join 課程表 as c on b.課程號=c.課程號 Where a.院系名稱=外語系 and c.分?jǐn)?shù)60,方法2： select a.姓名,c.課程名 fr

15、om 學(xué)生表 as a， 成績表 as b ，課程表 as c Where a.學(xué)號=b.學(xué)號 and b.課程號=c.課程號 and a.院系名稱=外語系 and c.分?jǐn)?shù)60,外連接根據(jù)連接時(shí)保留表中記錄的側(cè)重不同分為“左外連接”、“右外連接”和“全外連接”。 左外連接 將左表中的所有記錄分別與右表中的每條記錄進(jìn)行組合，結(jié)果集中除返回內(nèi)部連接的記錄以外，還在查詢結(jié)果中返回左表中不符合條件的記錄，并在右表的相應(yīng)列中填上NULL。左外連接的語法格式如下。 SELECT 列名列表 FROM 表名1 AS A LEFT OUTER JOIN 表名2 AS B ON A.列名=B.列名,3 外連接,

16、例子：查詢學(xué)生選修的課程的情況，輸出學(xué)號和課程名，如果有課程沒有學(xué)生選修，也輸出。 分析：使用內(nèi)連接只顯示符合連接條件的記錄。如果某門課程沒有學(xué)生選修，那么在成績表中就沒有該課程的課程號，因此該課程的課程名無法顯示。但是通過外連接，可以將其顯示。 結(jié)果： SELECT 學(xué)號,課程名 FROM 課程表 LEFT JOIN 成績表 ON 課程表.課程號=成績表.課程號,上述結(jié)果中如果將兩個(gè)表的位置換一下，就變成了右外連接。,T-SQL提供了聚集函數(shù)對數(shù)據(jù)進(jìn)行計(jì)算。,8.2 數(shù)據(jù)匯總,【例8-33】統(tǒng)計(jì)選修了5號課程的學(xué)生的總分、平均分、最高分和最低分。 SELECT SUM(分?jǐn)?shù)),AVG(分?jǐn)?shù))

17、,MAX(分?jǐn)?shù)),MIN(分?jǐn)?shù)) FROM 成績表 WHERE 課程號=5 【例8-34】統(tǒng)計(jì)選修了1號課程的學(xué)生的人數(shù)。 SELECT count(*),count(分?jǐn)?shù)) FROM 成績表 WHERE 課程號=1 【例8-35】統(tǒng)計(jì)紅色零件的總數(shù)。 SELECT 顏色,COUNT(顏色) FROM 庫存零件表 WHERE 顏色=紅,8.2 數(shù)據(jù)匯總,查詢結(jié)果輸出，默認(rèn)是按照表記錄物理順序輸出。但在實(shí)際應(yīng)用中經(jīng)常要對查詢結(jié)果排序輸出。 T-SQL提供了ORDER BY子句對查詢結(jié)果排序。其語法格式如下： SELECT select_list FROM table_name WHERE con

18、dition ORDER BY column_name | alias | position ASC | DESC 【例8-37】查詢選修了5號課程的學(xué)生，并按分?jǐn)?shù)從高到低排序輸出學(xué)生的學(xué)號和分?jǐn)?shù)。 SELECT 學(xué)號,分?jǐn)?shù) FROM 成績表 WHERE 課程號=5 ORDER BY 分?jǐn)?shù)DESC,8.3 排序,使用聚集函數(shù)可以統(tǒng)計(jì)數(shù)據(jù)，但有時(shí)需要統(tǒng)計(jì)不同類別的數(shù)據(jù)。T-SQL提供了GROUP BY子句對查詢結(jié)果分組。其語法格式如下： SELECT select_list FROM table_name WHERE condition GROUP BY column_name HAVING c

19、ondition | WITH CUBE | ROLLUP ,8.4 分組,【例8-39】統(tǒng)計(jì)每門課程的總分和平均分，并按平均分從高到低排序輸出。 SELECT 課程號,總分=SUM(分?jǐn)?shù)),平均分=AVG(分?jǐn)?shù)) FROM 成績表 GROUP BY 課程號 ORDER BY AVG(分?jǐn)?shù)) DESC GO 【例8-40】統(tǒng)計(jì)每個(gè)學(xué)院學(xué)生的男女生人數(shù)。 SELECT 院系名稱,性別,COUNT(*) FROM 學(xué)生表 GROUP BY 院系名稱,性別 GO,8.4 分組,子查詢就是嵌套在另一個(gè)查詢（SELECT）語句中的查詢 （SELECT）語句，因此子查詢也稱為嵌套查詢。外部的 SELECT

20、語句稱為外圍查詢（父查詢），內(nèi)部的SELECT語句 稱為子查詢。子查詢的結(jié)果將作為外圍查詢的參數(shù)，這種關(guān) 系就好象是函數(shù)調(diào)用嵌套，將嵌套函數(shù)的返回值作為調(diào)用函 數(shù)的參數(shù)。,8.5 子查詢,使用子查詢時(shí)要注意以下幾點(diǎn)： 1) 子查詢需要用括號( )括起來。 2) 子查詢可以嵌套。 3) 子查詢的SELECT語句中不能使用image、text和ntext數(shù)據(jù)類型。 4) 子查詢返回的結(jié)果的數(shù)據(jù)類型必須匹配外圍查詢WHERE語句的數(shù)據(jù)類型。 5) 子查詢不能使用ORDER BY子句。 子查詢具有兩種不同處理方式：無關(guān)子查詢和相關(guān)子查詢。,8.5 子查詢,8.5.1 無關(guān)子查詢 無關(guān)子查詢指的是在外圍

21、查詢之前執(zhí)行，然后返回?cái)?shù)據(jù)供外圍查詢使用，它和外圍查詢的聯(lián)系僅此而已。在編寫嵌套子查詢的SQL語句時(shí)，如果被嵌套的查詢中不包含對于外圍查詢的任何引用，就可以使用無關(guān)子查詢。最常用的無關(guān)子查詢方式是IN（或NOT IN）子句。其語法格式如下： SELECT select_list FROM table_name WHERE condition NOT IN (SELECT select_list FROM table_name WHERE condition),8.5 子查詢,【例8-42】查詢年齡最小的學(xué)生的學(xué)號和姓名。 SELECT 學(xué)號,姓名 FROM 學(xué)生表 WHERE 出生日期IN (

22、SELECT MAX(出生日期) FROM 學(xué)生表) GO 【例8-43】查詢選修了“數(shù)據(jù)庫應(yīng)用”課程的學(xué)生的學(xué)號和姓名。 SELECT 學(xué)號,姓名 FROM 學(xué)生表 WHERE 學(xué)號IN (SELECT 學(xué)號 FROM 成績表 WHERE 課程號IN (SELECT 課程號 FROM 課程表 WHERE 課程名=數(shù)據(jù)庫應(yīng)用) GO,8.5 子查詢,【例8-45】查詢比體育學(xué)院的學(xué)生年齡都大的學(xué)生。 SELECT * FROM 學(xué)生表 WHERE 院系名稱體育學(xué)院 AND 出生日期ALL (SELECT 出生日期 FROM 學(xué)生表 WHERE 院系名稱=體育學(xué)院) GO 【例8-46】查詢與5

23、號課程的最高分相同的學(xué)生的學(xué)號和分?jǐn)?shù)。 SELECT 學(xué)號,分?jǐn)?shù) FROM 成績表 WHERE 分?jǐn)?shù)= (SELECT MAX(分?jǐn)?shù)) FROM 成績表 WHERE 課程號=5) GO,8.5 子查詢,8.5.2 相關(guān)子查詢 相關(guān)子查詢在執(zhí)行時(shí)，要使用到外圍查詢的數(shù)據(jù)。外圍查詢首先選擇數(shù)據(jù)提供給子查詢，然后子查詢對數(shù)據(jù)進(jìn)行比較，執(zhí)行結(jié)束后再將它的查詢結(jié)果返回到它的外圍查詢中。如果有結(jié)果返回，則外圍查詢輸出。相關(guān)子查詢通常使用關(guān)系運(yùn)算符與邏輯運(yùn)算符（EXISTS，AND，SOME，ANY，ALL）。 【例8-48】查找所有選修課程的學(xué)生的姓名。 SELECT DISTINCT 姓名 FROM 學(xué)

24、生表 WHERE EXISTS (SELECT * FROM 成績表 WHERE 學(xué)生表.學(xué)號=成績表.學(xué)號) GO,8.5 子查詢,【例8-49】查詢成績高于李明最低分?jǐn)?shù)的學(xué)生的姓名、課程名和分?jǐn)?shù)。 SELECT 姓名,課程名,分?jǐn)?shù) FROM 學(xué)生表a,課程表b,成績表c WHERE a.學(xué)號=c.學(xué)號AND b.課程號=c.課程號 AND 分?jǐn)?shù)ANY (SELECT c.分?jǐn)?shù) FROM 學(xué)生表a,課程表b,成績表c WHERE a.學(xué)號=c.學(xué)號AND b.課程號=c.課程號AND a.姓名=李明) AND 姓名李明 GO 【例8-50】查詢分?jǐn)?shù)都大于王林分?jǐn)?shù)的學(xué)生、課程名和分?jǐn)?shù)。 SEL

25、ECT 姓名,課程名,分?jǐn)?shù) FROM 學(xué)生表,課程表,成績表 WHERE 學(xué)生表.學(xué)號=成績表.學(xué)號AND 課程表.課程號=成績表.課程號 AND 分?jǐn)?shù)ALL (SELECT c.分?jǐn)?shù) FROM 學(xué)生表a ,課程表b,成績表c WHERE a.學(xué)號=c.學(xué)號AND b.課程號=c.課程號AND a.姓名=王林) AND 姓名王林 GO,8.5 子查詢,SELECT查詢操作的對象是集合，結(jié)果也是集合。T-SQL提供了UNION、EXCEPT和INTERSECT三種集合操作。這一節(jié)主要介紹SELECT查詢集合的使用。 1. 集合合并 UNION將兩個(gè)或更多查詢的結(jié)果合并為單個(gè)結(jié)果集，該結(jié)果集包含聯(lián)

26、合查詢中的所有查詢的全部行。UNION運(yùn)算不同于連接查詢。UNION合并兩個(gè)查詢結(jié)果集的基本規(guī)則是： 1) 所有查詢中的列數(shù)和列的順序必須相同。 2) 數(shù)據(jù)類型必須兼容。 其語法格式如下： | ( ) UNION ALL ) UNION ALL | ( ) .n ,8.6 集合操作,【例8-52】從學(xué)生表中選擇學(xué)生的學(xué)號和姓名，與從教師表中選擇教師的編號和姓名合并。 SELECT 學(xué)號,姓名 FROM 學(xué)生表 UNION SELECT 教師編號,姓名 FROM 教師表 GO,8.6 集合操作,2. 集合刪除 EXCEPT和INTERSECT比較兩個(gè)查詢的結(jié)果，返回非重復(fù)值。EXCEPT從左查詢中返回右查詢沒有找到的所有非重復(fù)值。INTERSECT返回INTERSECT操作數(shù)左右兩邊的兩個(gè)查詢都返回的所有非重復(fù)值。EXCEPT和INTERSECT刪除的兩個(gè)查詢的結(jié)果集基本規(guī)則是： 1) 所有查詢中的列數(shù)和列的順序必須相同。 2) 數(shù)據(jù)類型必須兼容。 其語法格式如下： | ( ) EXCEPT | INTERSECT | ( ) ,8.6 集合操作,SELECT查詢結(jié)果一般情況下，只是輸出一個(gè)結(jié)果集，并不將數(shù)據(jù)添加到表中。但T-SQL提供了INTO關(guān)鍵字，可以將查詢結(jié)果添加到表中存儲。其語法格式如下： INTO n