第8章 數(shù)據(jù)庫表的高級查詢操作

1、1 第8章 數(shù)據(jù)庫中表(zhngbio)的 高級查詢操作共四十三頁第8章 數(shù)據(jù)庫中表(zhngbio)的高級查詢操作 8.1 限定條件的查詢 8.2 使用GROUP BY子句的查詢 8.3 使用聚合(jh)函數(shù)的查詢 8.4 使用ORDER BY子句的查詢 8.5 使用UNION組合多個運算結(jié)果2共四十三頁38.1 限定條件(tiojin)的查詢 SELECT語句中的WHERE子句是用來控制結(jié)果集的記錄構(gòu)成。可以在WHERE子句中指定(zhdng)一系列查詢條件，而只有這些滿足條件的記錄集才可以用來構(gòu)造結(jié)果集。 WHERE子句中的查詢或限定條件如下： 比較運算符（如=、）。 范圍說明（BETW

2、EEN和NOT BETWEEN）。 可選值列表（IN、NOT IN）。 模式匹配（LIKE 和 NOT LIKE）。 上述條件的邏輯組合（AND 、OR 和 NOT）。共四十三頁4 1比較查詢條件 比較查詢條件的表達式由比較的雙方和比較運算符組成。系統(tǒng)將根據(jù)查詢條件的真假來決定(judng)某一條記錄是否滿足該查詢條件，最后滿足該查詢條件的記錄才會出現(xiàn)在最終的結(jié)果集中。注意：text、ntext和image數(shù)據(jù)類型不能同比較運算符組合成查詢條件?！纠?.1】在“讀者”表中檢索年齡不大于20歲的讀者姓名。 程序代碼如下: USE TSGL SELECT 姓名 FROM 讀者 WHERE 年齡20

3、 AND 年齡25 OR 年齡20共四十三頁7圖8-1 例8.3程序運行結(jié)果(ji gu)共四十三頁8 3列表查詢條件(tiojin) 包含列表查詢條件的查詢將返回所有與列表中任意一個值匹配的記錄，通常使用IN關(guān)鍵字（包含）來指定列表查詢條件。列表中的項目之間必須使用逗號分隔。【例8.4】在“讀者”表檢索專業(yè)是計算機應(yīng)用或自動控制的讀者姓名。程序代碼如下：USE TSGLSELECT 姓名,專業(yè) FROM 讀者 WHERE 專業(yè) IN(計算機應(yīng)用, 自動控制)也可以不使用IN關(guān)鍵字進行查詢，如：USE TSGLSELECT 姓名,專業(yè)FROM 讀者WHERE 專業(yè) = 計算機應(yīng)用 OR 專業(yè)

4、= 自動控制 共四十三頁9 4模式查詢條件 模式查詢條件通常用來返回符合某種格式的所有記錄(jl)，可以使用LIKE或NOT LIKE關(guān)鍵字來指定模式查詢條件。LIKE關(guān)鍵字使用通配符來表示字符串需要的匹配模式，如表8-1所示。LIKE關(guān)鍵字的使用格式舉例如表8-2所示。【例8.5】在“讀者”表中檢索專業(yè)以“計算機”開頭的讀者姓名。程序代碼如下:USE TSGLSELECT 姓名,專業(yè)FROM 讀者WHERE 專業(yè) LIKE 計算機%共四十三頁107.1 限定條件(tiojin)的查詢通配符含義%由零個或者更多字符組成的任意字符串_任意單個字符用于指定范圍，例如a-h，表示a到h范圍內(nèi)的任何單

5、個字符用于指定范圍，例如a-h，表示a到h范圍以外的任何單個字符表8-1 通配符及其含義(hny)Like格式檢索范圍Like Me%查詢以字母Me開頭的所有字符串（如Mengyue）Like %ing查詢以字母ing結(jié)尾的所有字符串（如ming、string）Like %en%將查詢在任何位置包含字母en的所有字符串（如meng、green）Like _engyue將查詢以字母engyue結(jié)尾的所有7個字母的名稱（如mengyue）Like B-King將查詢以字符串ing結(jié)尾，以從B到K任何字母開頭的所有名稱Like Md%建查詢以字母M開頭，并且第二個字母不是d的所有名稱表8-2 LIK

6、E關(guān)鍵字的使用格式舉例共四十三頁11 5邏輯運算符 除了前面已經(jīng)提到的查詢條件外，還需要使用邏輯運算符才能組成完整的查詢條件。邏輯運算符有AND、OR、NOT。其中，AND、OR用于連接WHERE語句中的查詢條件，NOT用于反轉(zhuǎn)查詢條件的結(jié)果。【例8.6】在“讀者”表中檢索專業(yè)以“計算機”開頭(ki tu)并且性別為男的讀者姓名。程序代碼如下:USE TSGLSELECT 姓名,專業(yè)FROM 讀者WHERE 專業(yè) Like 計算機% AND 性別=男共四十三頁128.2 使用(shyng)GROUP BY子句 GROUP BY 子句的主要作用是可以將數(shù)據(jù)記錄設(shè)置的條件分成多個組，而且只有使用了

7、GROUP BY 子句，SELECT子句中所使用的聚合函數(shù)才會起作用。GROUP BY 子句關(guān)鍵字后面將跟著用于分組的字段名稱列表，這個列表將決定查詢結(jié)果集分組的依據(jù)和順序。在最終的結(jié)果集中，分組列表包含(bohn)字段的每個非重復(fù)值只存在一條記錄。共四十三頁13【例8.7】在“讀者”表中檢索每個專業(yè)的平均年齡。程序代碼如下:USE TSGLSELECT 專業(yè), AVG(年齡(ninlng) AS 平均年齡 FROM 讀者 GROUP BY 專業(yè) 在查詢分析器的“查詢腳本編輯器”中輸入并運行該程序，運行結(jié)果如圖8-2所示。共四十三頁14圖8-2 例8.7程序運行結(jié)果(ji gu)共四十三頁15

8、 也可以在GROUP BY關(guān)鍵字后使用多個字段名稱作為分組字段，這樣系統(tǒng)將根據(jù)這些字段的先后順序，對結(jié)果集進行更加詳細的分組?！纠?.8】在“讀者”表中檢索每個專業(yè)的男生和女生的平均年齡。程序代碼如下:USE TSGLSELECT 專業(yè),性別,AVG(年齡)AS 平均年齡 FROM 讀者GROUP BY 專業(yè),性別 在查詢分析器的“查詢腳本(jiobn)編輯器”中輸入并運行該程序，運行結(jié)果如圖8-3所示。共四十三頁16圖8-3 例8.8程序運行結(jié)果(ji gu)共四十三頁178.3 使用聚合(jh)函數(shù)聚合函數(shù)包括SUM、AVG、COUNT、COUNT(*)、MAX、MIN。它們的作用是在查詢

9、(chxn)結(jié)果集中生成匯總值。除了COUNT(*)外，其他匯總函數(shù)都處理單個字段中全部符合條件的值以生成一個結(jié)果集。這些匯總函數(shù)都可以應(yīng)用于數(shù)據(jù)表中的所有記錄，匯總函數(shù)的語法及其功能如表8-3所示。函數(shù)語法說明功能SUM（表達式）返回數(shù)值表達式中所有值的和AVG（表達式）返回數(shù)值表達式中所有值的平均值COUNT（表達式）返回數(shù)值表達式中值的個數(shù)COUNT（*）返回選定的行數(shù)MAX（表達式）返回表達式中的最大值MIN（表達式）返回表達式中的最小值表8-3共四十三頁18 8.3.1 SUM函數(shù)(hnsh) 聚合函數(shù)SUM的功能是返回數(shù)值表達式中所有值的和。用來求和的表達式通常是字段名稱或包含字段

10、名稱的表達式。 使用SUM函數(shù)時，應(yīng)注意以下幾點： 運算時，SUM將忽略求和對象中的空值。 可以同時使用DISTINCT關(guān)鍵字，以便在求和之前去掉重復(fù)值。 SUM函數(shù)只能對數(shù)值類型的字段使用。共四十三頁19【例8.9】在“圖書”表中，以分類號檢索各類書籍的單價總和。程序代碼如下(rxi):USE TSGLSELECT 分類號,SUM(單價) AS 單價總和FROM 圖書 GROUP BY 分類號 在查詢分析器的“查詢腳本編輯器”中輸入并運行該程序，運行結(jié)果如圖8-4所示。共四十三頁20圖8-4 例8.9程序運行結(jié)果(ji gu)共四十三頁21 8.3.2 AVG函數(shù) 聚合函數(shù)AVG的功能是返回

11、組中值的平均值。用來求平均值的表達式，通常(tngchng)是字段名稱或包含字段名稱的表達式。 使用AVG函數(shù)時，應(yīng)注意以下幾點： 運算時，AVG將忽略運算對象中的空值。 可以同時使用DISTINCT關(guān)鍵字，以便在運算之前去掉重復(fù)值。 AVG函數(shù)只能對數(shù)值類型的字段使用。共四十三頁22【例8.10】在“圖書(tsh)”表中，按分類號檢索各類圖書(tsh)的單價平均值。程序代碼如下:USE TSGLSELECT 分類號,AVG(單價) AS 平均單價FROM 圖書 GROUP BY 分類號共四十三頁23 8.3.3 MAX函數(shù) 聚合函數(shù)MAX的功能是返回表達式中最高值。用來(yn li)選取最高

12、值的表達式通常是字段名稱或包含字段名稱的表達式。 除了可以從數(shù)值類型的字段中選取最大值外，MAX函數(shù)另外一個常用的功能是從字符類型的字段中選取最大值。 使用MAX函數(shù)時，應(yīng)注意以下兩點： 運算時，MAX將忽略運算對象中的空值。 不能使用MAX函數(shù)從bit、text、image數(shù)據(jù)類型的字段中選取最大值。共四十三頁24【例8.11】在“讀者(dzh)”表中，按性別查找年齡最大的值。程序代碼如下:USE TSGLSELECT 性別,MAX(年齡) AS 最大年齡FROM 讀者 GROUP BY 性別 在查詢分析器的“查詢腳本編輯器”中輸入并運行該程序，運行結(jié)果如圖8-5所示。【例8.12】在“讀者

13、”表中，按專業(yè)檢索出姓名按照字母順序排在最后的讀者姓名。程序代碼如下。USE TSGLSELECT 專業(yè),MAX(姓名) as 姓名 FROM 讀者 GROUP BY 專業(yè) 共四十三頁25圖8-5 例8.11程序運行結(jié)果(ji gu)共四十三頁26 8.3.4 MIN函數(shù) 聚合函數(shù)MIN的功能是返回表達式中的最小值。用來(yn li)選取最小值的表達式通常是字段名稱或包含字段名稱的表達式。 除了可以從數(shù)值類型的字段中選取最小值外，MIN函數(shù)另外一個常用的功能是從字符類型的字段中選取最小值。 使用MIN函數(shù)時，應(yīng)注意以下兩點： 運算時，MIN將忽略運算對象中的空值。 不能使用MIN函數(shù)從bit、

14、text、image數(shù)據(jù)類型的字段中選取最小值。共四十三頁27【例8.13】在“圖書”表中，按書名檢索圖書中單價最小的記錄。程序代碼如下:USE TSGLSELECT 書名,MIN(單價) as 最小單價 FROM 圖書 GROUP BY 書名【例8.14】在“讀者”表中，按專業(yè)檢索姓名(xngmng)按照字母順序排在最前邊的讀者姓名(xngmng)。程序代碼如下:USE TSGLSELECT 專業(yè),MIN(姓名) as 姓名FROM 讀者 GROUP BY 專業(yè)共四十三頁288.3.5 COUNT函數(shù)和COUNT(*)函數(shù) 聚合函數(shù)COUNT的功能是返回表達式中值的個數(shù)，其表達式通常是字段名

15、稱。 聚合函數(shù)COUNT(*)的功能是返回符合條件的記錄條數(shù)。 這兩個函數(shù)的主要區(qū)別如下： COUNT函數(shù)將忽略對象中的空值，而COUNT(*)函數(shù)則將所有符合條件的記錄都計算在內(nèi)。 使用COUNT函數(shù)可以同時使用可選關(guān)鍵字DISTINCT去掉(q dio)重復(fù)值，而使用COUNT(*)函數(shù)時則不可以。 不能使用COUNT函數(shù)來計算定義為text和image數(shù)據(jù)類型的字段個數(shù)，但是可以使用COUNT(*)函數(shù)。共四十三頁29【例8.15】在“借閱”表中，檢索(jin su)出借書日期在2003年12月1日之前的所有記錄。程序代碼如下:USE TSGL SELECT COUNT(*) AS 記錄

16、數(shù)FROM 借閱 WHERE 借書日期 = 12/1/2003共四十三頁30【例8.16】在“借閱”表中，檢索出借書日期在2003年12月1日之前的讀者人數(shù)。具體代碼如下:USE TSGLSELECT COUNT(DISTINCT 借書證號) AS讀者數(shù)FROM 借閱 WHERE 借書日期=12/1/2003 在查詢分析器的“查詢腳本編輯器”中輸入并運行該程序，運行結(jié)果(ji gu)如圖8-6所示。 從上面兩個例子可以看出：COUNT函數(shù)所獲得的數(shù)據(jù)精確而有針對性，而COUNT(*)函數(shù)在使用時相對簡單。共四十三頁31圖8-6 例8.16程序運行結(jié)果(ji gu)共四十三頁328.4 使用(s

17、hyng)ORDER BY子句 ORDER BY子句將根據(jù)查詢結(jié)果中的一個字段或多個字段對查詢結(jié)果進行排序(pi x)，這種排序(pi x)的順序可以是升序的（使用ASC關(guān)鍵字），也可以是降序的（使用DESC關(guān)鍵字）。如果沒有指定排序的順序是升序還是降序，系統(tǒng)將默認為升序。當(dāng)然，在ORDER BY子句中可以指定不止一個字段，在這種情況下，系統(tǒng)將根據(jù)ORDER BY子句中指定的排序字段的順序?qū)Σ樵兘Y(jié)果進行排序。共四十三頁33【例8.17】在“讀者”表中檢索每個專業(yè)的學(xué)生信息，并按年齡(ninlng)由小到大進行輸出。程序代碼如下:USE TSGLSELECT 姓名,專業(yè),年齡 FROM 讀者 O

18、RDER BY 專業(yè),年齡 在查詢分析器的“查詢腳本編輯器”中輸入并運行該程序，運行結(jié)果如圖8-7所示。共四十三頁34圖8-7 例8.17程序運行結(jié)果(ji gu)共四十三頁35【例8.18】在“讀者”表中，首先將中間結(jié)果集按照所屬專業(yè)(zhuny)名稱升序排列，然后按照年齡大小進行降序輸出。程序代碼如下:USE TSGLSELECT * FROM 讀者 ORDER BY 專業(yè),年齡 DESC共四十三頁368.5 使用UNION組合(zh)多個運算結(jié)果 UNION（并）運算符可用來將兩個或多個SELECT語句的查詢結(jié)果組合成一個結(jié)果集。 使用(shyng)UNION運算符組合兩個查詢結(jié)果集的基

19、本規(guī)則如下： 所有查詢中的列數(shù)和列的順序必須相同。 數(shù)據(jù)類型必須兼容。UNION運算符的基本語法格式：select_statement UNION ALL select_statement其中：select_statement 是SELECT 查詢語句。共四十三頁37【例8.19】使用UNION將“讀者(dzh)”表和“學(xué)生”表中的人員組合輸出。程序代碼如下:USE TSGLSELECT 姓名,性別 FROM 讀者UNION SELECT 姓名,性別 FROM 學(xué)生 在查詢分析器的“查詢腳本編輯器”中輸入并運行該程序，運行結(jié)果如圖8-8圖8-10所示。共四十三頁38圖8-8 讀者(dzh)表中的記錄圖8-9 學(xué)生(xu sheng)表中的記錄共四十三頁39圖8-10