第5章數(shù)據(jù)庫查詢與視圖

1、第第5章章 數(shù)據(jù)庫查詢與視圖數(shù)據(jù)庫查詢與視圖 5.1 SELECT語句的格式與簡單查詢語句的格式與簡單查詢 5.2 SELECT多表連接查詢與創(chuàng)建新表多表連接查詢與創(chuàng)建新表 5.3 用用SELECT語句對數(shù)據(jù)進行統(tǒng)計匯總語句對數(shù)據(jù)進行統(tǒng)計匯總 5.4 SELECT合并結果集與子查詢合并結果集與子查詢 5.5 使用企業(yè)管理器查詢（編輯）數(shù)據(jù)使用企業(yè)管理器查詢（編輯）數(shù)據(jù) 5.6 視圖的基本概念視圖的基本概念 5.7 視圖的創(chuàng)建與使用視圖的創(chuàng)建與使用 5.8 查看、編輯和刪除視圖查看、編輯和刪除視圖 5.9 實訓要求與習題實訓要求與習題第第5章章 數(shù)據(jù)庫查詢與視圖數(shù)據(jù)庫查詢與視圖 學習目的與要求學

2、習目的與要求 創(chuàng)建數(shù)據(jù)庫的目的是為了有效的存儲管理數(shù)據(jù)，更重要創(chuàng)建數(shù)據(jù)庫的目的是為了有效的存儲管理數(shù)據(jù)，更重要的是對他們進行整理加工以獲得我們需要的重要信息。而的是對他們進行整理加工以獲得我們需要的重要信息。而查詢和視圖正是從數(shù)據(jù)庫中獲取信息最簡單、最常用、最查詢和視圖正是從數(shù)據(jù)庫中獲取信息最簡單、最常用、最主要的操作，是數(shù)據(jù)庫使用頻率最高的操作，是數(shù)據(jù)庫應主要的操作，是數(shù)據(jù)庫使用頻率最高的操作，是數(shù)據(jù)庫應用的靈魂，其主要作用是根據(jù)用戶的請求，對眾多數(shù)據(jù)表用的靈魂，其主要作用是根據(jù)用戶的請求，對眾多數(shù)據(jù)表的大量數(shù)據(jù)進行處理，篩選、查找、并統(tǒng)計出用戶需要的的大量數(shù)據(jù)進行處理，篩選、查找、并統(tǒng)計出

3、用戶需要的信息資料。通過本章學習，讀者應理解和掌握信息資料。通過本章學習，讀者應理解和掌握SQL Server 2000數(shù)據(jù)表的各種查詢操作，包括單表單條件查詢、單表數(shù)據(jù)表的各種查詢操作，包括單表單條件查詢、單表多條件查詢、多表連接及多條件查詢、統(tǒng)計匯總查詢、子多條件查詢、多表連接及多條件查詢、統(tǒng)計匯總查詢、子查詢及查詢結果的排序、分組；理解視圖的意義，掌握視查詢及查詢結果的排序、分組；理解視圖的意義，掌握視圖的創(chuàng)建和使用。圖的創(chuàng)建和使用。5.1 SELECT語句的格式與簡單查詢語句的格式與簡單查詢 5.1.1、SELECT查詢語句格式查詢語句格式 SELECT 記錄顯示范圍記錄顯示范圍 字段

4、列表字段列表 INTO 新表名新表名 FROM 表名或表名列表及其連接方式表名或表名列表及其連接方式 WHERE 條件表達式條件表達式 GROUP BY 分組字段名列表分組字段名列表 HAVING 分組條件表達式分組條件表達式 ORDER BY 排序字段名列表排序字段名列表 ASC | DESC COMPUTE 集合函數(shù)集合函數(shù)(列名列名1) BY 列名列名2 n 說明：說明：l l SELECT語句中各子句的順序：語句中各子句的順序： SELECT 記錄范圍記錄范圍 字段列表字段列表 INTO FROM WHERE GROUP BY HAVING ORDER BY COMPUTE l l F

5、ROM用于指定數(shù)據(jù)來源：用于指定數(shù)據(jù)來源： 單表查詢簡單格式：單表查詢簡單格式：FROM 表名表名 多表查詢時的格式：多表查詢時的格式：FROM 表名列表及其連接方式表名列表及其連接方式l l COMPUTE子句不能與子句不能與INTO子句或子句或GROUP BY子句同時使用。子句同時使用。5.1.2、使用、使用SELECT語句進行無數(shù)據(jù)源檢索語句進行無數(shù)據(jù)源檢索 無數(shù)源檢索就是查詢輸出不在數(shù)據(jù)表中的數(shù)據(jù)。一般用無數(shù)源檢索就是查詢輸出不在數(shù)據(jù)表中的數(shù)據(jù)。一般用來輸出常量或變量的值（相當于來輸出常量或變量的值（相當于PRINT或其他語言的輸出或其他語言的輸出語句），也可用于查看語句），也可用于查

6、看SQL Server2000的系統(tǒng)信息。的系統(tǒng)信息。 SELECT輸出常量或變量值時，在網(wǎng)格窗口用表格的形輸出常量或變量值時，在網(wǎng)格窗口用表格的形式輸出。式輸出。 【例【例5-1】用】用SELECT語句輸出常量值：語句輸出常量值： SELECT sql server 6.5, 256*256 該語句相當于顯示兩個計算列，未指定別名則列標題為該語句相當于顯示兩個計算列，未指定別名則列標題為“（無名列）（無名列）”。也可使用語句：。也可使用語句： SELECT 字符串常量字符串常量=sql server 6.5, 計算結果計算結果=256*256 該語句為兩個計算列指定了別名作列標題。兩個語句分

7、該語句為兩個計算列指定了別名作列標題。兩個語句分別執(zhí)行結果如圖別執(zhí)行結果如圖5-1所示。所示。5.1.2、使用、使用SELECT語句進行無數(shù)據(jù)源檢索語句進行無數(shù)據(jù)源檢索 【例【例5-2】查看全局變量】查看全局變量本地本地SQL Server服務器的版本服務器的版本信息：信息： SELECT version 其中其中version為系統(tǒng)無參數(shù)存儲過程，也稱為全局變?yōu)橄到y(tǒng)無參數(shù)存儲過程，也稱為全局變量。服務器的返回結果是：量。服務器的返回結果是： Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyrig

8、ht (c) 1988-2000 Microsoft Corporation Personal Edition on Windows 4.10 (Build 2222: A ) 【例【例5-3】查詢本地】查詢本地SQL Server服務器使用的語言：服務器使用的語言： SELECT language 服務器的返回結果是：簡體中文服務器的返回結果是：簡體中文5.1.3、指定、指定字段列表及列別名（列標題）字段列表及列別名（列標題） 字段列表用于指定查詢結果集中所需要顯示的列，可以字段列表用于指定查詢結果集中所需要顯示的列，可以使用以下格式：使用以下格式： * 指定所使用的全部數(shù)據(jù)表的全部字段指定

9、所使用的全部數(shù)據(jù)表的全部字段 表名表名.* 多表查詢時指定某一個表的全部字段多表查詢時指定某一個表的全部字段 字段列表字段列表 指定所需要顯示的列指定所需要顯示的列 字段列表可以指定字段名也可以指定表達式（計算列），字段列表可以指定字段名也可以指定表達式（計算列），還可為字段列或計算列指定別名（列標題），多個列之間還可為字段列或計算列指定別名（列標題），多個列之間用逗號隔開。指定顯示列的格式：用逗號隔開。指定顯示列的格式： 表名表名.字段名字段名 | 計算表達式計算表達式 | 別名別名= 表名表名.字段名字段名 | 計算表達式計算表達式 若指定別名時也可寫成若指定別名時也可寫成ANSI標準的格

10、式：標準的格式： 表名表名.字段名字段名 | 計算表達式計算表達式 AS 別名別名5.1.3、指定、指定字段列表及列別名（列標題）字段列表及列別名（列標題）說明：說明：l l 多表查詢時同名字段必須加表名作字段名前綴，單表或多表查詢時同名字段必須加表名作字段名前綴，單表或不重復字段可以不加。不重復字段可以不加。l l 指定顯示的列可以單個字段，也可以是派生列指定顯示的列可以單個字段，也可以是派生列由多個由多個字段運算后產(chǎn)生的列或是由表達式計算后產(chǎn)生的列字段運算后產(chǎn)生的列或是由表達式計算后產(chǎn)生的列統(tǒng)稱統(tǒng)稱為計算列。為計算列。l l 顯示字段列時若不指定別名則以字段名為查詢結果的列顯示字段列時若不

11、指定別名則以字段名為查詢結果的列標題，可以指定別名代替字段名作為查詢結果的列標題。標題，可以指定別名代替字段名作為查詢結果的列標題。l l 顯示計算列時若不指定別名則顯示為顯示計算列時若不指定別名則顯示為“（無名列）（無名列）”，可指定別名作為顯示的列標題?？芍付▌e名作為顯示的列標題。l l 字段列表的順序是查詢結果的顯示順序，可以與數(shù)據(jù)表字段列表的順序是查詢結果的顯示順序，可以與數(shù)據(jù)表定義的字段順序不同。定義的字段順序不同。5.1.3、指定、指定字段列表及列別名（列標題）字段列表及列別名（列標題） 注意：注意：uu 別名就是表達式的名字，為字段指定別名相當于表達式別名就是表達式的名字，為字段

12、指定別名相當于表達式中只有一個字段變量。中只有一個字段變量。uu 別名不允許出現(xiàn)在其他表達式當中，當某個表達式需要別名不允許出現(xiàn)在其他表達式當中，當某個表達式需要使用別名所代表的計算結果時，必須使用該原表達式或字使用別名所代表的計算結果時，必須使用該原表達式或字段名。段名。 【例【例5-4】查詢】查詢“進貨表進貨表2006”的全部記錄，只顯示的全部記錄，只顯示“進進貨日期，貨號，數(shù)量，供貨商貨日期，貨號，數(shù)量，供貨商ID”字段：字段： SELECT 供貨商供貨商ID, 進貨日期進貨日期, 貨號貨號, 數(shù)量數(shù)量 FROM 進貨表進貨表2006 注意：注意：SELECT語句中指定的字段列表順序可以

13、任意，語句中指定的字段列表順序可以任意，不需要與表的定義順序一致。它只表示查詢結果集的顯示不需要與表的定義順序一致。它只表示查詢結果集的顯示順序，不會對數(shù)據(jù)表的字段順序進行更改。順序，不會對數(shù)據(jù)表的字段順序進行更改。5.1.4、指定查詢結果的顯示范圍、指定查詢結果的顯示范圍 指定查詢結果集中記錄的顯示范圍有三個選項：指定查詢結果集中記錄的顯示范圍有三個選項： ALL 顯示查詢結果集的全部記錄（默認值）顯示查詢結果集的全部記錄（默認值） | DISTINCTROW 對查詢結果集過濾重復行對查詢結果集過濾重復行 | TOP n percent 顯示查詢結果集開頭的顯示查詢結果集開頭的 n %個記錄

14、個記錄 說明：說明：l l 若不指定顯示范圍則默認為若不指定顯示范圍則默認為ALL，顯示查詢結果集的全部，顯示查詢結果集的全部記錄。記錄。l l ALL、DISTINCT、TOP三項參數(shù)必須單獨使用，不允許三項參數(shù)必須單獨使用，不允許同時出現(xiàn)在一個同時出現(xiàn)在一個SELECT語句中。語句中。5.1.4、指定查詢結果的顯示范圍、指定查詢結果的顯示范圍 【例【例5-5】查詢】查詢“銷售表銷售表2006”的記錄，按不同記錄顯示的記錄，按不同記錄顯示范圍顯示全部字段范圍顯示全部字段 * ： 顯示全部查詢結果的記錄：顯示全部查詢結果的記錄： SELECT ALL * FROM 銷售表銷售表2006 在查詢

15、記錄中去掉重復行：在查詢記錄中去掉重復行： SELECT DISTINCT * FROM 銷售表銷售表2006 只顯示查詢結果前只顯示查詢結果前5條記錄：條記錄： SELECT TOP 5 * FROM 銷售表銷售表2006 只顯示查詢結果前只顯示查詢結果前20%記錄：記錄： SELECT TOP 20 percent * FROM 銷售表銷售表2006 當查詢結果的數(shù)據(jù)量非常龐大又沒有必要對所有數(shù)據(jù)進當查詢結果的數(shù)據(jù)量非常龐大又沒有必要對所有數(shù)據(jù)進行瀏覽時，使用行瀏覽時，使用TOP指定顯示記錄的范圍可以大大減少查指定顯示記錄的范圍可以大大減少查詢時間。詢時間。5.1.4、指定查詢結果的顯示范

16、圍、指定查詢結果的顯示范圍 【例【例5-6】查詢】查詢“銷售表銷售表2006”的記錄，只顯示字段的記錄，只顯示字段“客客戶名稱戶名稱”，比較過濾重復行的效果：，比較過濾重復行的效果： SELECT 客戶名稱客戶名稱 FROM 銷售表銷售表2006 SELECT DISTINCT 客戶名稱客戶名稱 FROM 銷售表銷售表2006 注意：注意：uu 如果使用語句：如果使用語句： SELECT ALL DISTINCT 客戶名稱客戶名稱 FROM 銷售表銷售表2006 或者想在查詢結果的前或者想在查詢結果的前5條記錄中去掉重復行：條記錄中去掉重復行： SELECT TOP 5 DISTINCT 客戶

17、名稱客戶名稱 FROM 銷售表銷售表2006 都會產(chǎn)生語法錯誤，因為都會產(chǎn)生語法錯誤，因為ALL、DISTINCT、TOP不允不允許同時使用。許同時使用。uu 使用使用DISTINCT關鍵字對多列字段查詢時將返回多列數(shù)關鍵字對多列字段查詢時將返回多列數(shù)據(jù)組合后的惟一記錄。據(jù)組合后的惟一記錄。5.1.4、指定查詢結果的顯示范圍、指定查詢結果的顯示范圍 【例【例5-7】查詢】查詢“進貨表進貨表2006”的前的前5條記錄，只顯示條記錄，只顯示“進進貨日期，貨號，數(shù)量，供貨商貨日期，貨號，數(shù)量，供貨商ID”字段，使用別名顯示標字段，使用別名顯示標題題“進貨日期，商品編號，數(shù)量，供貨商代碼進貨日期，商品

18、編號，數(shù)量，供貨商代碼”。 SELECT TOP 5 供貨商代碼供貨商代碼=供貨商供貨商ID, 進貨日期進貨日期, 商品編號商品編號=貨號貨號, 數(shù)量數(shù)量 FROM 進貨表進貨表2006 或者：或者： SELECT TOP 5 供貨商供貨商ID 供貨商代碼供貨商代碼, 進貨日期進貨日期, 貨號貨號 AS 商品編號商品編號, 數(shù)量數(shù)量 FROM 進貨表進貨表2006 注意：注意：uu 用戶可使用任意的別名作為結果集的列標題，或為計算用戶可使用任意的別名作為結果集的列標題，或為計算列加上任意的標題。列加上任意的標題。uu 在計算列表達式后面直接給出列名是在計算列表達式后面直接給出列名是ANSI規(guī)則

19、的標準規(guī)則的標準方法，方法，AS可以省略，別名也可以加單引號。如：供貨商可以省略，別名也可以加單引號。如：供貨商ID 供貨商代碼供貨商代碼5.1.4、指定查詢結果的顯示范圍、指定查詢結果的顯示范圍 【例【例5-8】使用字段組合的計算列查詢】使用字段組合的計算列查詢“商品一覽表商品一覽表”，顯示顯示“貨號，貨名，每件毛利，字段外數(shù)據(jù)貨號，貨名，每件毛利，字段外數(shù)據(jù)”。 USE diannaoxs SELECT 商品信息商品信息=貨號貨號+ , +貨名貨名, 每件毛利每件毛利= 參考價格參考價格*0.1, 30*2+5 字段外數(shù)據(jù)字段外數(shù)據(jù), 256*256 FROM 商品一覽表商品一覽表 本例假

20、設本例假設“每件毛利每件毛利”為參考價格為參考價格10%，“字段外數(shù)據(jù)字段外數(shù)據(jù)”并沒有實際意義，只是為了說明語法。運行結果如圖并沒有實際意義，只是為了說明語法。運行結果如圖5-3所所示。示。 注意：注意：uu 在在T-SQL的計算列表達式中，允許使用的計算列表達式中，允許使用+、-、*、/ 、%以及位運算的邏輯運算符以及位運算的邏輯運算符AND（&）、）、OR（|）、）、XOR（）、）、NOT（）以及字符串連接符（）以及字符串連接符（+）。）。uu 字符型字段可以使用加號將幾個字段的數(shù)據(jù)連接輸出在字符型字段可以使用加號將幾個字段的數(shù)據(jù)連接輸出在一列中。如：貨號一列中。如：貨號+貨名貨名5.1

21、.5、用、用WHERE子句查詢滿足條件的記錄子句查詢滿足條件的記錄 格式：格式：WHERE 條件表達式條件表達式 功能：從查詢的數(shù)據(jù)集中挑選出符合條件的記錄。功能：從查詢的數(shù)據(jù)集中挑選出符合條件的記錄。 說明：說明：l l WHERE子句必須緊跟在子句必須緊跟在FROM子句后面。子句后面。l l 條件表達式用于指定被顯示記錄所滿足的查詢條件。條件表達式用于指定被顯示記錄所滿足的查詢條件。 注意：注意：uu 條件表達式中可以包含字段名，但不允許使用為某個字條件表達式中可以包含字段名，但不允許使用為某個字段或計算列指定的別名，必須使用原字段名或計算列的表段或計算列指定的別名，必須使用原字段名或計算

22、列的表達式，因為達式，因為WHERE子句指定的內容就是表達式。子句指定的內容就是表達式。uu 條件表達式的運算結果必須是邏輯值條件表達式的運算結果必須是邏輯值TRUE、FALSE、UNKNOWN。5.1.5、用、用WHERE子句查詢滿足條件的記錄子句查詢滿足條件的記錄 條件表達式中可以使用上一章所介紹的邏輯運算符和表條件表達式中可以使用上一章所介紹的邏輯運算符和表達式：達式：1、比較運算符：、比較運算符： = = = != ! !=100005.1.5、用、用WHERE子句查詢滿足條件的記錄子句查詢滿足條件的記錄【例【例5-10】在】在“商品一覽表商品一覽表”中查詢參考價格下浮中查詢參考價格下

23、浮25%以后低于以后低于1000元元的商品信息。的商品信息。 SELECT 貨號貨號, 貨名貨名, 規(guī)格規(guī)格, 原參考價格原參考價格=參考價格參考價格, 下浮后價格下浮后價格=參考價格參考價格*0.75 FROM 商品一覽表商品一覽表 WHERE 參考價格參考價格*0.751000 注意：空值注意：空值NULL不參與數(shù)值的比較。不參與數(shù)值的比較?！纠纠?-11】在】在“銷售表銷售表2006”中查詢中查詢“陳剛陳剛”銷售金額低于銷售金額低于10000元的銷元的銷售記錄。售記錄。 SELECT * FROM 銷售表銷售表2006 WHERE 銷售員銷售員=陳剛陳剛 and 金額金額=2006/2

24、/1 and 銷售日期銷售日期=2006-3-1 或者：或者： SELECT * FROM 銷售表銷售表2006 WHERE 銷售日期銷售日期 between 2006/2/1 and 2006-3-1 注意：未設置時間的日期默認時間為：注意：未設置時間的日期默認時間為：00：00：00，所以終值為，所以終值為2006年年3月月1日日5.1.5、用、用WHERE子句查詢滿足條件的記錄子句查詢滿足條件的記錄【例【例5-13】在】在“銷售表銷售表2006”查詢銷售數(shù)量不在查詢銷售數(shù)量不在3到到25之間的銷售記錄。之間的銷售記錄。 SELECT * FROM 銷售表銷售表2006 WHERE 數(shù)量數(shù)

25、量 not between 3 and 25 或者：或者： SELECT * FROM 銷售表銷售表2006 WHERE 數(shù)量數(shù)量25【例【例5-14】在】在“員工表員工表”中查詢中查詢1980年出生的員工信息年出生的員工信息 SELECT * FROM 員工表員工表 WHERE 出生日期出生日期 between 1980/1/1 and 1981/1/1 或者：或者： SELECT * FROM 員工表員工表 WHERE year(出生日期出生日期)=1980 1980作為日期處理時也可以加上單引號。作為日期處理時也可以加上單引號?！纠纠?-15】在】在“員工表員工表”中查詢陳剛和高宏的資

26、料。中查詢陳剛和高宏的資料。 SELECT 員工員工ID, 姓名姓名, 性別性別, 出生日期出生日期=Convert(char(12), 出生日期出生日期,111), 年齡年齡=year(getdate()-year(出生日期出生日期), 部門部門, 工齡工齡=CASt(year(getdate()-year(工作時間工作時間) AS varchar(2)+年年 FROM 員工表員工表 WHERE 姓名姓名=陳剛陳剛 or 姓名姓名=高宏高宏 注意表達式中注意表達式中char()與與varchar()的區(qū)別，會影響輸出列寬。的區(qū)別，會影響輸出列寬。5.1.5、用、用WHERE子句查詢滿足條件的

27、記錄子句查詢滿足條件的記錄【例【例5-16】在】在“員工表員工表”中查詢姓中查詢姓“于于”的員工信息。的員工信息。 SELECT * FROM 員工表員工表 WHERE 姓名姓名 like 于于%【例【例5-17】在】在“銷售表銷售表2006”中查詢?yōu)H坊與青島兩地的客戶中查詢?yōu)H坊與青島兩地的客戶 方法一：方法一： SELECT DISTINCT 客戶名稱客戶名稱 FROM 銷售表銷售表2006 WHERE 客戶名稱客戶名稱 like %濰坊濰坊% or 客戶名稱客戶名稱 like %青島青島% 方法二：方法二： SELECT DISTINCT 客戶名稱客戶名稱, 購貨日期購貨日期=Conver

28、t(char(12), 銷售日期銷售日期, 111), 購買商品購買商品=貨名貨名, 數(shù)量數(shù)量, 金額金額=Convert(varchar(10), 金額金額) FROM 銷售表銷售表2006 WHERE 客戶名稱客戶名稱 like %濰坊濰坊% or 客戶名稱客戶名稱 like %青島青島%【例【例5-18】在】在“供貨商表供貨商表”中查詢賬戶后中查詢賬戶后4位數(shù)是位數(shù)是7765的廠家信息。的廠家信息。 SELECT * FROM 供貨商表供貨商表 WHERE 賬戶賬戶 like %7765【例【例5-19】在】在“銷售表銷售表2006”中查詢不知名中查詢不知名(未輸入貨名，列值為空未輸入貨

29、名，列值為空)商品商品的銷售記錄。的銷售記錄。 SELECT * FROM 銷售表銷售表2006 WHERE 貨名貨名 is null 運行代碼后在網(wǎng)格窗口沒有任何數(shù)據(jù)輸出運行代碼后在網(wǎng)格窗口沒有任何數(shù)據(jù)輸出該字段沒有空值的記錄。該字段沒有空值的記錄。5.1.65.1.6、用、用ORDER BYORDER BY子句對查詢結果集排序子句對查詢結果集排序 格式：格式：ORDER BY 列名或別名列名或別名 ASC | DESC , n 功能：按結果集中指定列的數(shù)值大小，排序后按功能：按結果集中指定列的數(shù)值大小，排序后按ASC或或DESC指定的順序顯示。指定的順序顯示。l l ASC是默認方式表示按

30、升序排序可以省略，是默認方式表示按升序排序可以省略，DESC表示降表示降序排序。序排序。l l 指定多列排序時，各列的先后順序決定排序的優(yōu)先級。指定多列排序時，各列的先后順序決定排序的優(yōu)先級。l l 如果對如果對SELECT語句中的別名字段或計算列排序，如果語句中的別名字段或計算列排序，如果沒用沒用DISTINCT過濾重復行，則允許在過濾重復行，則允許在ORDER BY中使用中使用字段別名排序，否則只能使用原字段名或計算列表達式。字段別名排序，否則只能使用原字段名或計算列表達式。l l 如果如果SELECT語句中沒有指定語句中沒有指定GROUP BY分組，也沒用分組，也沒用DISTINCT過濾

31、重復行，則可指定不在過濾重復行，則可指定不在SELECT字段列表中字段列表中的字段排序，但該字段必須包含在的字段排序，但該字段必須包含在FROM指定的數(shù)據(jù)源中。指定的數(shù)據(jù)源中。l l Ntext、Text或或Image類型的列不允許排序。類型的列不允許排序。5.1.65.1.6、用、用ORDER BYORDER BY子句對查詢結果集排序子句對查詢結果集排序 uu 使用使用GROUP BY分組時允許用別名，但不允許對分組時允許用別名，但不允許對SELECT沒有指定的字段排序。沒有指定的字段排序。uu 使用使用DISTINCT過濾重復行時，即不允許用別名也不允過濾重復行時，即不允許用別名也不允許對

32、許對SELECT沒有指定的字段排序。沒有指定的字段排序。uu ORDER BY的作用只是排列查詢結果集中的顯示順序，的作用只是排列查詢結果集中的顯示順序，而不是對數(shù)據(jù)表排序。如果要對數(shù)據(jù)表排序可以創(chuàng)建索引而不是對數(shù)據(jù)表排序。如果要對數(shù)據(jù)表排序可以創(chuàng)建索引對象，增加查詢速度。對象，增加查詢速度?！纠纠?-20】按進貨】按進貨“數(shù)量數(shù)量”升序排序查詢升序排序查詢“進貨表進貨表2006”的的記錄。記錄。 USE diannaoxs SELECT * FROM 進貨表進貨表2006 ORDER BY 數(shù)量數(shù)量 5.1.65.1.6、用、用ORDER BYORDER BY子句對查詢結果集排序子句對查詢

33、結果集排序 【例【例5-21】按銷售】按銷售“數(shù)量數(shù)量”降序排序查詢降序排序查詢“銷售表銷售表2006”中中“章曉曉章曉曉”的銷售記錄。的銷售記錄。 SELECT 銷售員銷售員 AS 姓名姓名,銷售日期銷售日期,貨名貨名 商品名稱商品名稱,數(shù)量數(shù)量 FROM 銷售表銷售表2006 WHERE 銷售員銷售員=章曉曉章曉曉 ORDER BY 數(shù)量數(shù)量 DESC 注意：注意：WHERE子句中不允許使用別名，若寫成：子句中不允許使用別名，若寫成：WHERE 姓名姓名=章曉曉章曉曉是錯誤的。是錯誤的。 5.1.65.1.6、用、用ORDER BYORDER BY子句對查詢結果集排序子句對查詢結果集排序

34、【例【例5-22】按】按“年齡年齡”降序、降序、“姓名姓名”升序排序查詢升序排序查詢“員工表員工表”的信息。的信息。 SELECT 員工員工ID, 姓名姓名, 性別性別, 出生日期出生日期=Convert(char(12), 出生日期出生日期, 111), 年齡年齡=year(getdate() - year(出生日期出生日期), 部門部門 FROM 員工表員工表 ORDER BY 年齡年齡 DESC, 姓名姓名 注意：注意：uu “年齡年齡”是計算列的別名，是計算列的別名，WHERE子句不允許使用別名，但子句不允許使用別名，但ORDER BY子句只要不與子句只要不與DISTINCT同時使用則

35、可以使用別名，并按同時使用則可以使用別名，并按該計算列的值進行排序。也可以直接指定按計算表達式排序：該計算列的值進行排序。也可以直接指定按計算表達式排序： ORDER BY year(getdate()-year(出生日期出生日期) DESC, 姓名姓名uu 多個字段排序時多個字段排序時ORDER BY的書寫順序確定優(yōu)先級，本例的書寫順序確定優(yōu)先級，本例“年齡年齡”是第一級，在年齡相同的記錄中再按第二級是第一級，在年齡相同的記錄中再按第二級“姓名姓名”排序，依次可以排序，依次可以有第三級、第四級有第三級、第四級。uu 對漢字的排序規(guī)則與對漢字的排序規(guī)則與漢語詞典漢語詞典中按拼音排列的順序相同，

36、如中按拼音排列的順序相同，如“章章”大于大于“孫孫”。 5.1.65.1.6、用、用ORDER BYORDER BY子句對查詢結果集排序子句對查詢結果集排序 【例【例5-23】按】按“參考價格參考價格”升序排序查詢升序排序查詢“商品一覽表商品一覽表”的部分信息。的部分信息。 SELECT 貨號貨號, 貨名貨名, 規(guī)格規(guī)格 FROM 商品一覽表商品一覽表 ORDER BY 參考價格參考價格 由于由于SELECT語句中沒有用語句中沒有用GROUP BY分組，也沒有用分組，也沒有用DISTINCT過濾重復行，所以可以指定對不在過濾重復行，所以可以指定對不在SELECT字段字段列表中的列表中的“參考價

37、格參考價格”排序，雖然結果集中不顯示參考價排序，雖然結果集中不顯示參考價格，但已按各自格，但已按各自“參考價格參考價格”進行了排序，其中進行了排序，其中NULL被排被排在第一位。查詢結果如圖在第一位。查詢結果如圖5-18所示。所示。5.2 SELECT5.2 SELECT多表連接查詢與創(chuàng)建新表多表連接查詢與創(chuàng)建新表 在第在第1章我們介紹了數(shù)據(jù)表的交叉連接、內連接、外連接、章我們介紹了數(shù)據(jù)表的交叉連接、內連接、外連接、自連接等自連接等4種方式，用于多個數(shù)據(jù)源的多表連接的種方式，用于多個數(shù)據(jù)源的多表連接的FROM子子句語法格式為：句語法格式為： FROM 表名表名1 IN 數(shù)據(jù)庫名數(shù)據(jù)庫名 連接方

38、式連接方式 表名表名2 ON 連接條件連接條件 n 或：或： FROM 表名列表表名列表 WHERE 連接條件連接條件5.2.1、交叉連接、交叉連接cross join 又稱非限制連接、無條件連接或笛卡爾連接，就是將兩又稱非限制連接、無條件連接或笛卡爾連接，就是將兩個表不加任何限制的組合在一起，連接結果是具有兩個表個表不加任何限制的組合在一起，連接結果是具有兩個表記錄數(shù)乘積的邏輯數(shù)據(jù)表。交叉連接沒有實際意義，僅用記錄數(shù)乘積的邏輯數(shù)據(jù)表。交叉連接沒有實際意義，僅用于說明表直接的連接原理。于說明表直接的連接原理。 格式一：格式一：SELECT 字段列表字段列表 FROM 表名表名1 Cross J

39、oin 表名表名2 n 格式二：格式二：SELECT 字段列表字段列表 FROM 表名表名1, 表名表名2 , n 【例【例5-24】將】將“供貨商表供貨商表”與與“進貨表進貨表2006”進行交叉連進行交叉連接并觀察結果。接并觀察結果。 USE diannaoxs SELECT * FROM 供貨商表供貨商表, 進貨表進貨表2006 或者：或者： SELECT * FROM 供貨商表供貨商表 cross join 進貨表進貨表2006 其中其中“供貨商表供貨商表”5條記錄，條記錄，“進貨表進貨表2006”10條記錄，條記錄，連接結果總共有連接結果總共有50條記錄。條記錄。5.2.2、內連接、內

40、連接 inner join 也叫自然連接，只將兩個表中滿足指定條件的記錄連接也叫自然連接，只將兩個表中滿足指定條件的記錄連接成一條新記錄，舍棄所有不滿足條件沒有進行連接的記錄。成一條新記錄，舍棄所有不滿足條件沒有進行連接的記錄。 內連接是數(shù)據(jù)表最常用的連接方式，其語法格式為：內連接是數(shù)據(jù)表最常用的連接方式，其語法格式為： 格式一：格式一： SELECT 列名列表列名列表 FROM 表名表名1 inner Join 表名表名2 ON 表名表名1.列名列名=表名表名2 .列名列名 n 格式二：格式二： SELECT 列名列表列名列表 FROM 表名表名1 , 表名表名2 , WHERE 表名表名1

41、.列名列名=表名表名2 .列名列名 and 5.2.2、內連接、內連接 inner join 說明：說明：l l 當表名太長時，一般可在當表名太長時，一般可在FROM指定表的同時為表定義指定表的同時為表定義一個別名，定義格式為：一個別名，定義格式為： 表名表名 AS 別名別名 （用（用AS或空格隔開）或：表名或空格隔開）或：表名.別名別名l l 如果兩個表有相同的字段名，在指定字段名時必須在列如果兩個表有相同的字段名，在指定字段名時必須在列名前面加上表名（或表別名）作為前綴加以區(qū)別，用名前面加上表名（或表別名）作為前綴加以區(qū)別，用“表表名名.列名列名”或或“表別名表別名.列名列名”表示。表示。

42、l l 如果列名是某個表中單獨具有的，可以不加前綴，但加如果列名是某個表中單獨具有的，可以不加前綴，但加上表名會增強可讀性。上表名會增強可讀性。 注意：為表名定義別名后，在注意：為表名定義別名后，在SELECT及各個子句中指及各個子句中指定字段時必須使用定字段時必須使用“別名別名.列名列名”的格式，不允許再使用的格式，不允許再使用“表名表名.列名列名”。5.2.2、內連接、內連接 inner join 【例【例5-25】將】將“供貨商表供貨商表”與與“進貨表進貨表2006”進行簡單的內進行簡單的內連接。連接。 USE diannaoxs SELECT * FROM 供貨商表供貨商表 AS g

43、join 進貨表進貨表2006 AS j ON g.供貨商供貨商ID=j.供貨商供貨商ID 或：或： SELECT * FROM 供貨商表供貨商表 AS g , 進貨表進貨表2006 AS j WHERE g.供貨商供貨商ID=j.供貨商供貨商ID 該語句將貨號相等的記錄連接起來顯示兩個表的全部字段。該語句將貨號相等的記錄連接起來顯示兩個表的全部字段。5.2.2、內連接、內連接 inner join 【例【例5-26】將】將“商品一覽表商品一覽表”與與“銷售表銷售表2006”進行內連接，進行內連接，顯示完整的銷售表。顯示完整的銷售表。 SELECT 銷售日期銷售日期=convert(varch

44、ar(12), 銷售日期銷售日期, 111), 客戶名稱客戶名稱, s.貨號貨號, s.貨名貨名, s.規(guī)格規(guī)格, s.單位單位, 單價單價=convert(varchar(10), 單價單價), 數(shù)量數(shù)量, 金額金額=convert(varchar(10), 金額金額, 1), 銷售員銷售員 FROM 商品一覽表商品一覽表 s JOIN 銷售表銷售表2006 x ON s.貨號貨號=x.貨號貨號 注意：注意：uu 內連接時兩個表的先后順序任意（外連接則區(qū)分左右）。內連接時兩個表的先后順序任意（外連接則區(qū)分左右）。uu 兩個表共有的字段若內容相同，可任選其一，但表名前兩個表共有的字段若內容相同

45、，可任選其一，但表名前綴不能省略。綴不能省略。5.2.2、內連接、內連接 inner join 【例【例5-27】將】將“供貨商表供貨商表”“”“進貨表進貨表2006”“商品一覽表商品一覽表”三三個表進行內連接。個表進行內連接。 SELECT 進貨日期進貨日期=convert(varchar(12), 進貨日期進貨日期, 111), s.貨號貨號, s.貨名貨名, s.規(guī)格規(guī)格, s.單位單位, 數(shù)量數(shù)量, 進價進價=convert(varchar(10), 進價進價), 供貨商供貨商, 發(fā)貨人發(fā)貨人=聯(lián)系人聯(lián)系人, 收貨人收貨人 FROM 供貨商表供貨商表 AS g join 進貨表進貨表2

46、006 j ON g.供貨商供貨商ID=j.供貨商供貨商ID join 商品一覽表商品一覽表 s ON j.貨號貨號=s.貨號貨號 或：或： SELECT 進貨日期進貨日期=convert(varchar(12), 進貨日期進貨日期, 111), s.貨號貨號, s.貨名貨名, s.規(guī)格規(guī)格, s.單位單位, 數(shù)量數(shù)量, 進價進價=convert(varchar(10), 進價進價),供貨商供貨商, 發(fā)貨人發(fā)貨人=聯(lián)系人聯(lián)系人,收貨人收貨人 FROM 供貨商表供貨商表 AS g , 進貨表進貨表2006 j , 商品一覽表商品一覽表 s WHERE g.供貨商供貨商ID=j.供貨商供貨商ID

47、and j.貨號貨號=s.貨號貨號5.2.3、外連接、外連接left | right | full outer join 在內連接（自然連接）中，必須是兩個表中匹配的記錄才在內連接（自然連接）中，必須是兩個表中匹配的記錄才能在結果集中出現(xiàn)。而外連接只限制一個表，對另一個表不能在結果集中出現(xiàn)。而外連接只限制一個表，對另一個表不加限制（所有的行都可出現(xiàn)在結果集中），以便在結果集中加限制（所有的行都可出現(xiàn)在結果集中），以便在結果集中保證該表的完整性。外連接分為左外連接、右外連接、全外保證該表的完整性。外連接分為左外連接、右外連接、全外連接三種。連接三種。1、左外連接、左外連接 左外連接返回左表左外連

48、接返回左表(表名表名1)的全部記錄及右表相關的信息。的全部記錄及右表相關的信息。 左外連接取左表的全部記錄按指定條件與右表中滿足條件左外連接取左表的全部記錄按指定條件與右表中滿足條件的記錄進行連接，若右表中沒有滿足條件的記錄則在相應字的記錄進行連接，若右表中沒有滿足條件的記錄則在相應字段填入段填入NULL（Bit位類型字段填位類型字段填0）。但條件不限制左表，）。但條件不限制左表，左表的全部記錄都包括在結果集中，以保持左表的完整性。左表的全部記錄都包括在結果集中，以保持左表的完整性。 SELECT 列名列表列名列表 FROM 表名表名1 left outer join 表名表名2 ON 表名表

49、名1.列名列名=表名表名2 .列名列名1、左外連接、左外連接【例【例5-28】用左外連接查詢】用左外連接查詢“供貨商表供貨商表”與與“進貨表進貨表2006”，獲取生產(chǎn)廠家提供貨物的品種（貨號）、供貨日期和供貨數(shù)獲取生產(chǎn)廠家提供貨物的品種（貨號）、供貨日期和供貨數(shù)量。量。 SELECT 生產(chǎn)廠家生產(chǎn)廠家=g.供貨商供貨商, j.貨號貨號, 供貨日期供貨日期=convert(varchar(15), j.進貨日期進貨日期, 111) , 供貨數(shù)量供貨數(shù)量=j.數(shù)量數(shù)量, 貨款金額貨款金額=convert(varchar(10), j.進價進價*j.數(shù)量數(shù)量), j.收貨人收貨人 FROM 供貨商表

50、供貨商表 AS g left join 進貨表進貨表2006 j ON g.供貨商供貨商ID=j.供貨商供貨商ID 注意：注意：uu 左外連接默認按左表的主鍵順序排序。左外連接默認按左表的主鍵順序排序。uu 左外連接可以保證左表的完整性，在查詢結果中明顯看左外連接可以保證左表的完整性，在查詢結果中明顯看到還沒有進貨的廠家，該廠家的進貨數(shù)據(jù)為到還沒有進貨的廠家，該廠家的進貨數(shù)據(jù)為NULL，說明還，說明還沒有業(yè)務發(fā)生。沒有業(yè)務發(fā)生。2、右外連接、右外連接 右外連接返回右表的全部記錄及左表相關的信息。右外連接返回右表的全部記錄及左表相關的信息。 右外連接與左外連接相同，只是把兩個表的順序顛倒了一右外

51、連接與左外連接相同，只是把兩個表的順序顛倒了一下，就是取右表的全部記錄按指定條件與左表中滿足條件的下，就是取右表的全部記錄按指定條件與左表中滿足條件的記錄進行連接，若左表中沒有滿足條件的記錄則在相應字段記錄進行連接，若左表中沒有滿足條件的記錄則在相應字段填入填入NULL（Bit位類型字段填位類型字段填0），右表的全部記錄都在結），右表的全部記錄都在結果集中，保持右表的完整性。果集中，保持右表的完整性。 SELECT 列名列表列名列表 FROM 表名表名1 right outer join 表名表名2 ON 表名表名1.列名列名=表名表名2 .列名列名 注意：右外連接與左外連接只是表的順序不一樣

52、，如果把注意：右外連接與左外連接只是表的順序不一樣，如果把左外連接中表的順序變一下，再使用右外連接，其結果是相左外連接中表的順序變一下，再使用右外連接，其結果是相同的。同的。2、右外連接、右外連接【例【例5-29】將】將“銷售表銷售表2006”與與“商品一覽表商品一覽表”右外連接，右外連接，查詢銷售產(chǎn)品與公司所經(jīng)營產(chǎn)品的對應情況。查詢銷售產(chǎn)品與公司所經(jīng)營產(chǎn)品的對應情況。 SELECT 銷售日期銷售日期=convert(varchar(12), 銷售日期銷售日期,111), 客戶名稱客戶名稱, s.貨號貨號, s.貨名貨名, s.規(guī)格規(guī)格, s.單位單位, s.庫存量庫存量, 單價單價=conv

53、ert(varchar(10), 單價單價), 數(shù)量數(shù)量, 金額金額=convert(varchar(10), 金額金額, 1) FROM 銷售表銷售表2006 x right join 商品一覽表商品一覽表 s ON s.貨號貨號=x.貨號貨號 注意：注意：uu 右外連接默認按右表的主鍵右外連接默認按右表的主鍵“貨號貨號”順序排序，若增加順序排序，若增加“ORDER BY 銷售日期銷售日期”則順序與則順序與“銷售日期銷售日期”一致，沒銷售的產(chǎn)品（日期為一致，沒銷售的產(chǎn)品（日期為NULL）將排在最前面。）將排在最前面。uu 右外連接保證右表的完整性，查詢結果可看出未銷售的產(chǎn)品（銷售右外連接保證

54、右表的完整性，查詢結果可看出未銷售的產(chǎn)品（銷售信息為信息為NULL）。）。uu 如果計算了庫存量之后就可看到第如果計算了庫存量之后就可看到第5條記錄有庫存沒銷售，第條記錄有庫存沒銷售，第12條記條記錄庫存為錄庫存為0說明還沒有進貨，所以沒有銷售。說明還沒有進貨，所以沒有銷售。3、全外連接、全外連接 全外連接返回左表與右表的全部記錄。全外連接返回左表與右表的全部記錄。 全外連接相當于先進行左外連接再進行右外連接的綜合連全外連接相當于先進行左外連接再進行右外連接的綜合連接，就是取左表的全部記錄按指定條件與右表中滿足條件的接，就是取左表的全部記錄按指定條件與右表中滿足條件的記錄進行連接。右表中不滿足

55、條件的記錄則在相應字段填入記錄進行連接。右表中不滿足條件的記錄則在相應字段填入NULL，再將右表不滿足條件的記錄列出，在左表不符合條，再將右表不滿足條件的記錄列出，在左表不符合條件記錄的相應字段填入件記錄的相應字段填入NULL。 全外連接使兩個表的全部記錄都包括在結果集中，可以保全外連接使兩個表的全部記錄都包括在結果集中，可以保持兩個表的完整性。持兩個表的完整性。 語法格式：語法格式： SELECT 列名列表列名列表 FROM 表名表名1 full outer join 表名表名2 ON 表名表名1.列名列名=表名表名2 .列名列名3、全外連接、全外連接 【例【例5-30】使用全外連接查詢】使

56、用全外連接查詢“供貨商表供貨商表”與與“進貨表進貨表2006”，獲取生產(chǎn)廠家給提供供貨的品種、供貨日期和供貨，獲取生產(chǎn)廠家給提供供貨的品種、供貨日期和供貨數(shù)量。數(shù)量。 SELECT 生產(chǎn)廠家生產(chǎn)廠家=g.供貨商供貨商, j.貨號貨號, 供貨日期供貨日期=convert(varchar(15), j.進貨日期進貨日期, 111) , 供貨數(shù)量供貨數(shù)量=j.數(shù)量數(shù)量, 貨款金額貨款金額=convert(varchar(10), j.進價進價*j.數(shù)量數(shù)量) , j.收貨人收貨人 FROM 供貨商表供貨商表 AS g full join 進貨表進貨表2006 j ON g.供貨商供貨商ID=j.供貨

57、商供貨商ID 注意：注意：uu 該查詢結果與【例該查詢結果與【例5-28】圖】圖5-21左連接時完全一致，明顯左連接時完全一致，明顯看到已建立廠家信息尚未進貨的廠家?？吹揭呀S家信息尚未進貨的廠家。uu 假設已經(jīng)進貨卻還沒有登記廠家信息，在全外連接中也假設已經(jīng)進貨卻還沒有登記廠家信息，在全外連接中也會一目了然（但設置了外鍵這種情況是不可能發(fā)生的）。會一目了然（但設置了外鍵這種情況是不可能發(fā)生的）。5.2.4、自內連接、自內連接join 自內連接簡稱自連接，是一張表自己對自己的內連接，即自內連接簡稱自連接，是一張表自己對自己的內連接，即在一張表的兩個副本之間進行內連接。用自連接可以將同一在一張

58、表的兩個副本之間進行內連接。用自連接可以將同一個表的不同行連接起來。個表的不同行連接起來。 使用自連接時，必須為兩個副本指定別名，使之在邏輯上使用自連接時，必須為兩個副本指定別名，使之在邏輯上成為兩個表。成為兩個表。 語法格式：語法格式： SELECT 列名列表列名列表 FROM 表名表名 AS 別名別名1 join 表名表名.別名別名2 ON 別名別名1.列名列名=別名別名2.列名列名5.2.4、自內連接、自內連接join 【例【例5-31】使用自連接在】使用自連接在“進貨表進貨表2006”中找出已經(jīng)進貨中找出已經(jīng)進貨2次次及及2次以上的廠家。次以上的廠家。 USE diannaoxs SE

59、LECT DISTINCT g1.供貨商供貨商ID, g1.貨號貨號, 供貨日期供貨日期1=convert(varchar(15), g1.進貨日期進貨日期, 111) , 供貨數(shù)量供貨數(shù)量=g1.數(shù)量數(shù)量, 貨款金額貨款金額=convert(varchar(10), g1.進價進價*g1.數(shù)量數(shù)量) FROM 進貨表進貨表2006 AS g1 join 進貨表進貨表2006 AS g2 ON g1.供貨商供貨商ID= g2.供貨商供貨商ID and g1.貨號貨號g2.貨號貨號 ORDER BY g1.供貨商供貨商ID , convert( varchar(15), g1.進貨日期進貨日期,

60、 111 ) 查詢結果如圖查詢結果如圖5-23所示?？梢娖渲兄挥兴尽？梢娖渲兄挥?個進貨兩次以上個進貨兩次以上的廠家及有關的信息，另外的廠家的廠家及有關的信息，另外的廠家SHKD只進貨一次，只進貨一次，SDKJ沒有進貨則被篩除了。沒有進貨則被篩除了。5.2.4、自內連接、自內連接join 注意：注意：uu自連接雖然使用一個表但有兩個拷貝，在邏輯上是兩個表自連接雖然使用一個表但有兩個拷貝，在邏輯上是兩個表而且字段完全相同，因此字段列表中字段名必須加上其中一而且字段完全相同，因此字段列表中字段名必須加上其中一個表的別名做前綴。個表的別名做前綴。uu使用自連接會產(chǎn)生許多重復行，一般加關鍵字使用自連