使用Select語句

1、第6章 Transact SQL語言6 61 1 SQLSQL簡介簡介6 62 2 函數(shù)函數(shù)6 63 3 使用使用SelectSelect語句語句6 64 4 使用使用WHEREWHERE字句字句6 65 5 使用使用ORDER BY ORDER BY 字句字句6 66 6 匯總數(shù)據(jù)匯總數(shù)據(jù)6 67 7 高級(jí)查詢技巧高級(jí)查詢技巧6.1 SQL6.1 SQL語言簡介語言簡介 SQL( Structured Query Language) 意思為結(jié)構(gòu)化查詢語言,SQL語言的主要功能就是同各種數(shù)據(jù)庫建立聯(lián)系，進(jìn)行溝通。在SQL Server數(shù)據(jù)庫中，Transact-SQL語言由以下幾部分組成： （

2、1）數(shù)據(jù)定義語言（DDL） （2）數(shù)據(jù)操縱語言（DML） （3）數(shù)據(jù)控制語言（DCL） （4） 流程控制語言（FCL） （5）其它語言要素 （ADE） 返回目錄6.2 函數(shù) 函數(shù)的主要作用是用來幫助用戶獲得系統(tǒng)的有關(guān)信息、執(zhí)行數(shù)學(xué)計(jì)算和統(tǒng)計(jì)功能、實(shí)現(xiàn)數(shù)據(jù)類型轉(zhuǎn)換等操作。Transact-SQL編程語言提供了三種函數(shù)： （1）集合函數(shù)：集合函數(shù)用于對(duì)一組值執(zhí)行計(jì)算，并返回一個(gè)單一的值。 （2）行集函數(shù)：行集函數(shù)可以在Transact-SQL語句中當(dāng)作表引用。 （3）數(shù)量函數(shù)：數(shù)量函數(shù)用于對(duì)傳遞給它的一個(gè)或者多個(gè)參數(shù)值進(jìn)行處理和計(jì)算，并返回一個(gè)單一的值。 本節(jié)主要介紹集合函數(shù)和數(shù)量函數(shù)。 返回目錄

3、 6.2.1 6.2.1 集合函數(shù)集合函數(shù) 集合函數(shù)對(duì)一個(gè)集合值進(jìn)行操作，返回單個(gè)的數(shù)值。如求一個(gè)結(jié)果集合的最大值、平均值等。常用的集合函數(shù)如下： 1.SUM和和AVG SUM和AVG函數(shù)分別用于求表達(dá)式中所有值項(xiàng)的總和與平均值。其語法格式為： SUM/AVG （ALL | DISTINCT ） 其中，expression是列名，可以是常量、列、函數(shù)或表達(dá)式，其數(shù)據(jù)類型只能是：int、smallint、tinyint、bigint、decimal、numeric、float、real、money和 smallmoney。All表示對(duì)所有值進(jìn)行運(yùn)算，DISTINCT表示去除重復(fù)值，默認(rèn)為ALL。

4、SUM/AVG忽略NULL值。 【例6.1】 求樣例數(shù)據(jù)庫pubs中每類書籍的平均價(jià)格和預(yù)付款總和。 返回目錄2. MAX和和MINMAX和MIN分別用于求表達(dá)式中所有值項(xiàng)的最大值與最小值。其語法格式為：MAX/MIN（ALL | DISTINCT ）其中，expression是列名，可以是常量、列、函數(shù)或表達(dá)式，其數(shù)據(jù)類型可以是數(shù)值型、字符型和日期型。All表示對(duì)所有值進(jìn)行運(yùn)算，DISTINCT表示去除重復(fù)值，默認(rèn)為ALL。MAX/MIN忽略NULL值?！纠?.2】求樣例數(shù)據(jù)庫pubs中年度銷售額最高的書。 3. COUNTCOUNT函數(shù)用于統(tǒng)計(jì)組中滿足條件的行數(shù)或總行數(shù)。其語法格式如下：C

5、OUNT （ALL | DISTINCT |*）其中，expression是表達(dá)式，其數(shù)據(jù)類型是除uniqueidentifier、text、image或ntext之外的任何類型。All表示對(duì)所有值進(jìn)行運(yùn)算，DISTINCT表示去除重復(fù)值，默認(rèn)為ALL。選擇*時(shí)將統(tǒng)計(jì)總行數(shù)。COUNT忽略NULL值。 返回目錄 6.2.2 6.2.2 數(shù)量函數(shù)數(shù)量函數(shù) 常用的數(shù)量函數(shù)包括數(shù)值函數(shù)、日期時(shí)間函數(shù)、字符串函數(shù)、文本圖像函數(shù)和系統(tǒng)函數(shù)。 1. 1. 數(shù)值函數(shù)數(shù)值函數(shù) 數(shù)值函數(shù)用于對(duì)數(shù)字表達(dá)式進(jìn)行數(shù)學(xué)運(yùn)算并返回運(yùn)算結(jié)果。 （1）ABS ABS函數(shù)返回給定數(shù)字表達(dá)式的絕對(duì)值。語法格式為： ABS（num

6、eric_expression） 【例6.3】顯示ABS函數(shù)對(duì)三個(gè)數(shù)字8.0、-5.0和0.3的不同效果。 （2）RAND RAND函數(shù)返回0-1之間的一個(gè)隨機(jī)值。語法格式為： RAND（seed） 其中，參數(shù)seed為整型表達(dá)式，返回值類型為float。返回目錄 2. 2. 字符串函數(shù)字符串函數(shù) 基本字符串函數(shù)：UPPER、LOWER、SPACE、REPLACE、REPLICATE 、STUFF、REVERSE、LTRIM、RTRIM。 字符串查找函數(shù)：CHARINDEX、PATINDEX。 長度和分析函數(shù)：DATALENGTH、SUBSTRING、RIGHT。 轉(zhuǎn)換函數(shù)：ASC、CHAR、

7、STR、SOUNDEX、DIFFERENCE 下面給出幾個(gè)例子說明字符串函數(shù)的使用。 【例6.4】-【例6.7】 返回目錄3. 日期和時(shí)間函數(shù)日期和時(shí)間函數(shù)用于對(duì)日期和時(shí)間數(shù)據(jù)進(jìn)行各種不同的處理和運(yùn)算，用于處理datatime和smalldatetime類型的數(shù)據(jù)，并返回一個(gè)字符串、數(shù)字值或日期和時(shí)間值。（1）DAYDAY函數(shù)返回 date_expression 中的日期值，語法格式如下：DAY (date_expression)DAY函數(shù)使用整數(shù)值作參數(shù)，系統(tǒng)從 1900 年 1 月 1 日起往后數(shù)指定，整數(shù)值+1天，再返回其日期。（2）MONTHMONTH返回 date_expressio

8、n 中的月份值，函數(shù)語法格式如下：MONTH(date_expression)與 DAY函數(shù)不同的是：MONTH函數(shù)的參數(shù)為整數(shù)時(shí)，一律返回整數(shù)值1。即 SQL Server認(rèn)為其是1900年1月。（3）YEARYEAR函數(shù)返回 date_expression 中的年份值，語法格式如下：YEAR(date_expression)返回目錄6.3使用Select語句 6.3.1使用使用Select 語句的基本介紹語句的基本介紹 SELECT語句是是查詢數(shù)據(jù)的基本方法。SELECT語句可以從數(shù)據(jù)庫中查詢行，并允許從一個(gè)或多個(gè)表中選擇一個(gè)或多個(gè)行或列。 SELECT語句主要子句格式可歸納如下： SEL

9、ECT DISTINCTTOP n select_list INTO new_table FROM table_source WHERE search_condition GROUP BY group_by_expression HAVING search_condition ORDER BY order_expression ASC|DESC COMPUTE expression 其中： 表示可選項(xiàng)。 返回目錄 SELECT語句中各子句的作用如下： SELECT字句，指定由查詢返回的列。 INTO字句，創(chuàng)建新表并將結(jié)果行從查詢插入新表中。 FROM字句，指定從其中查詢行的表。 WHERE字句

10、，指定用于限制返回的行的搜索條件。 GROUP BY字句，指定查詢結(jié)果的分組條件。 HAVING字句，指定組或聚合的搜索條件。 ORDER BY字句，指定結(jié)果集的排序方式。 COMPUTE子句，在結(jié)果集的末尾生成一個(gè)匯總數(shù)據(jù)行。 返回目錄6.3.2 查詢特定列的信息查詢特定列的信息從表中查詢特定列的信息的SELECT語句主要的用法是：SELECT DISTINCTTOP n *|column_name|expression AS column_alias ,.n FROM table_source其中： 表示可選項(xiàng)， 表示必選項(xiàng)，| 表示只能選一項(xiàng)， ,.n 表示前面的項(xiàng)可重復(fù)n 次。語法選項(xiàng)

11、說明： *，表示表中所有的列。 column_name，列名 expression（表達(dá)式），是列名、常量、函數(shù)以及由運(yùn)算符連接的列名、常量和函數(shù)的任意組合，者是子查詢。 AS column_alias，為列名取一個(gè)別名。顯示查詢結(jié)果時(shí)，別名將代替列名。 table_source,指定要查詢的表（包括視圖、派生表和聯(lián)接表）。 DISTINCT，指定在查詢結(jié)果集中只能顯示唯一行 TOP n，指定只從查詢結(jié)果集中輸出前n行。【例6.8-6.10】查詢特定列。 返回目錄 6.3.3使用算術(shù)運(yùn)算符使用算術(shù)運(yùn)算符 在SELECT語句中，在列出現(xiàn)的位置上，可以使用expression，expression

12、是列名、常量、函數(shù)以及由運(yùn)算符連接的列名、常量和函數(shù)的任意組合。所以，可以使用算術(shù)運(yùn)算符操縱列，對(duì)查詢結(jié)果進(jìn)行計(jì)算。這些算術(shù)運(yùn)算符包括：+（加）、（減）、*（乘）、/（除）和%（取模）。 【例6.11-6.12】使用算術(shù)運(yùn)算符 返回目錄6 64 4使用使用WHEREWHERE字句字句 使用SELECT語句查詢數(shù)據(jù)，一般只是從表中篩選出想要的數(shù)據(jù)，這就要用到WHERE子句。帶WHERE子句的SELECT語句的主要用法是：SELECT select_listFROM table_sourceWHERE search_condition 其中，search_condition指定篩選數(shù)據(jù)行的條件，s

13、earch_condition是由表達(dá)式以及邏輯運(yùn)算符等組成的。 返回目錄search_condition支持的運(yùn)算符如下表所示。返回目錄操作符作用=、=、=、!=、!、!比較運(yùn)算符BETWEEN、NOT BETWEEN值是否在范圍之內(nèi)IN 、NOT IN值是否屬于列表值之一LIKE、NOT LIKE字符串匹配運(yùn)算符IS NULL、IS NOT NULL值是否為NULL 6 64 4使用使用WHEREWHERE字句字句6.4.1 比較運(yùn)算符比較運(yùn)算符在WHERE子句中，可以使用=、=、=、!=、!、!等比較運(yùn)算符對(duì)兩個(gè)表達(dá)式進(jìn)行比較，并以比較結(jié)果作為篩選的條件。用法是：SELECT selec

14、t_list FROM table_source WHERE expression OPERATOR expession其中：OPERATOR為比較運(yùn)算符?！纠?.13-6.14】 6.4.2 BETWEEN關(guān)鍵字關(guān)鍵字在WHERE子句中，可以使用BETWEEN、NOT BETWEEN兩個(gè)運(yùn)算符來確定表達(dá)式的取值是否在范圍之內(nèi)，并以此作為篩選的條件。用法是：SELECT select_list FROM table_source WHERE expression NOT BETWEEN expression AND expression其中： 表示可選項(xiàng)?！纠?.15-6.16】 返回目錄6.

15、4.3 IN 關(guān)鍵字關(guān)鍵字在WHERE子句中，可以使用IN、NOT IN兩個(gè)運(yùn)算符來確定表達(dá)式的取值是否屬于列表值之一，并以此作為篩選的條件。用法是：SELECT select_list FROM table_source WHERE expression NOT IN (value_list) 其中： 表示可選項(xiàng)，value_list表示值列表，若有多個(gè)值，值之間用逗號(hào)分隔。 【例6.17-6.18】 6.4.4 LIKE關(guān)鍵字關(guān)鍵字在WHERE子句中，可以使用LIKE、NOT LIKE兩個(gè)運(yùn)算符來把表達(dá)式與字符串進(jìn)行比較，并以此作為篩選的條件。用法是：SELECT select_list

16、FROM table_source WHERE expression NOT LIKE string 其中： 表示可選項(xiàng)，string表示用來進(jìn)行比較的字符串。【例6.19-6.22】返回目錄6.6.5 5使用使用ORDER BY ORDER BY 字句字句 使用ORDER BY子句，可以對(duì)查詢的結(jié)果進(jìn)行排序。帶ORDER BY子句的SELECT語句的主要用法是： SELECT select_list FROM table_source ORDER BY expression DESC,.n 其中： 表示可選項(xiàng)； 表示必選項(xiàng)； ,.n 表示前面的項(xiàng)可重復(fù)n次；expression指定要排序的列

17、,可以是列名或列的別名和表達(dá)式；DESC表示降序?！纠?.23-6.25】返回目錄6.6 匯總數(shù)據(jù)匯總數(shù)據(jù) 匯總函數(shù)會(huì)將某個(gè)特定的一組數(shù)值進(jìn)行計(jì)算并將結(jié)果以單一值來返回。除了count函數(shù)之外，其余的匯總函數(shù)會(huì)忽略所有Null的值。般來說，其匯總函數(shù)通常會(huì)與SELECT語句中的GROUP BY一起使用。要使用匯總函數(shù)之前，必須要注意總和并不是適用在每一個(gè)TRANSACT-SQL語句中使用，它只能在如下所示的語句或子句中來使用： (1)SELECT語句的選擇列表(子查詢或外部查詢)。 (2)COMPUTE或COMPUTE BY子句。 (3)HAVING 子句。返回目錄 6.6.1合計(jì)函數(shù)合計(jì)函數(shù)

18、 合計(jì)函數(shù)在函數(shù)執(zhí)行過程中對(duì)某一數(shù)據(jù)集中的數(shù)據(jù)進(jìn)行合計(jì)或聯(lián)合,執(zhí)行完畢后的輸出結(jié)果是一個(gè)簡單的數(shù)值。典型的合計(jì)函數(shù)是COUNT（）、SUM（）、 AVG（）、MAX（）和MIN（）。 返回目錄 6.6.2 GROUP BY 和和HAVING子句子句1. GROUP BY子句GROUP BY子句通常用于對(duì)某一個(gè)數(shù)據(jù)集的子集或其中的一組數(shù)據(jù)進(jìn)行合計(jì)運(yùn)算，GROUP BY子句包含以下組件：（1）個(gè)或多個(gè)自由集合的表達(dá)式。通常是對(duì)分組列的引用。（2）ALL關(guān)鍵字(可選)，該關(guān)鍵字指定返回由GROUP BY子句產(chǎn)生的所有組，即使某些組沒有符合搜索條件的行。（3）CUBE或ROLLUP。在GROUP BY

19、子句中，必須指定表或視圖列的名稱，而不是使用AS子句指派的結(jié)果集列的名稱。2.HAVING子句HAVING子句指定組或集合的搜索條件，HAVING通常與GROUP BY一起使用。如果不使用GROUP BY子句，HAVING的行為與WHERE子句一樣。HAVING子句的語法格式如下：HAVINGsearch_condition參數(shù)search_condition指定組或集合應(yīng)滿足的搜索條件。返回目錄6.6.3 COMPUTE和和COMPUTE BY子句子句GROUP BY有一個(gè)缺點(diǎn)，就是返回的結(jié)果集中只有合計(jì)數(shù)據(jù)，而不是原始的詳細(xì)記錄。在SQL Server中完成這項(xiàng)工作可以使用COMPUTE

20、BY子句。COMPUTE子句的語法格式如下： COMPUTE AVG | COUNT | MAX | MIN | STDEV | STDEVP | VAR | VARP | SUM ( expression ) ,.n BY expression ,.n 6.6.4 6.6.4 ROLLUP和和CUBE選項(xiàng)選項(xiàng)SQL Server 2000中有許多生成合計(jì)數(shù)據(jù)的方法，當(dāng)這些選擇產(chǎn)生相關(guān)的結(jié)果輸出時(shí)，卻很難解釋說明。ROLLUP關(guān)鍵字對(duì)GROUP BY子句中的列按不同的等級(jí)進(jìn)行分組，然后對(duì)這些分組進(jìn)行合計(jì)。CUBE關(guān)鍵字則創(chuàng)建所有可能的分組，然后對(duì)這些組進(jìn)行合計(jì)。我們建議使用這兩個(gè)函數(shù)，以確保數(shù)

21、據(jù)庫設(shè)計(jì)原型能不斷改進(jìn)，確保在數(shù)據(jù)庫中查詢到的信息能按要求生成輸出報(bào)表。返回目錄6.76.7高級(jí)查詢技巧高級(jí)查詢技巧 6.7.1 聯(lián)合查詢聯(lián)合查詢 聯(lián)合查詢就是使用UNION字句將來自不同查詢的結(jié)果合并成為一個(gè)結(jié)果， UNION會(huì)自動(dòng)將重復(fù)的數(shù)據(jù)行刪除。帶UNION子句的SELECT語句的主要用法是： SELECT select_list FROM table_sourceWHERE search_conditionUNIONSELECT select_list FROM table_source WHERE search_condition ,.n ORDER BY order_by_exp

22、ression DESC,.n 其中： 表示可選項(xiàng)； 表示必選項(xiàng)； ,.n 表示前面的項(xiàng)可重復(fù)n次 【例6.26-6.27】 返回目錄 6.7.2連接查詢連接查詢?cè)谶M(jìn)行一個(gè)查詢時(shí)，用戶往往需要從多個(gè)表中查詢相關(guān)數(shù)據(jù)，這就需要用到連接查詢。連接查詢的主要用法是：SELECT select_list FROM first_table join_type second_table ON (join_condition) WHERE search_conditionORDER BY order_expression DESC其中：join_type指定所執(zhí)行的連接類型；join_condition指定

23、連接條件。連接類型可分為內(nèi)聯(lián)接(INNER JOIN)、外聯(lián)接(OUTER JOIN)和交叉聯(lián)接(CROSS JOIN)三類。1 內(nèi)連接(INNER JOIN)內(nèi)連接使用比較運(yùn)算符根據(jù)每個(gè)表共有的列的值匹配兩個(gè)表中的行。它所使用比較算符有=、 =、 =、! =、! 、! 等?！纠?.28-6.29】返回目錄2 外連接(OUTER JOIN)外部連接中，參與連接的表有主從之分，以主表的每行數(shù)據(jù)去匹配從表的數(shù)據(jù)行，符合連接條件的數(shù)據(jù)將直接返回到查詢結(jié)果中；如果主表的行在從表中沒有相匹配的行，與內(nèi)連接丟棄不匹配行的做法不同，主表的行不會(huì)被丟棄，而是也返回到查詢結(jié)果中，相對(duì)應(yīng)的從表的行的列位置將被填上

24、 NULL 值后再返回到結(jié)果集中。外連接又可分為左連接(LEFT OUTER JOIN)、右連接(RIGHT OUTER JOIN)和完全連接(FULL OUTER JOIN)。(1) 左外連接(LEFT OUTER JOIN)以連結(jié)左邊的表作為主表。(2) 右外連接(RIGHT OUTER JOIN)以連結(jié)左邊的表作為主表。 (2) 右外連接(RIGHT OUTER JOIN)以連結(jié)左邊的表作為主表。 【例6.30-6.31】3交叉連接(CROSS JOIN)交叉連接產(chǎn)生的查詢結(jié)果的行數(shù)為第一個(gè)表的行數(shù)乘以第二個(gè)表的行數(shù)，即笛卡爾積。 【例6.33】返回目錄 6.7.3 子查詢子查詢 子查詢是一個(gè)包含在另一條SELECT語句里的SELECT語句。外層的SELECT語句叫外部查詢，內(nèi)層的SELECT語句叫內(nèi)部查詢（或子查詢）。通常，任何允許使用表達(dá)式的地方都可以使用子查詢。 包括子查詢的SELECT語句主