數(shù)據(jù)庫的查詢和視圖

第4章

數(shù)據(jù)庫的查詢和視圖4.1關(guān)系運算1.選擇（Selection）選擇是單目運算，其運算對象是一個表。該運算按給定的條件，從表中選出滿足條件的行，形成一個新表，作為運算結(jié)果。選擇運算的記號為

F(R)。其中，

是選擇運算符，下標F是一個條件表達式，R是被操作的表。例如，若要在T表（表4.1）中找出T1<20的行形成一個新表，則運算式為

F(T)上式中F：T1<20，該選擇運算的結(jié)果如表4.2所示。T1T2T3T4T51A133M2B120N3A21212O5D1024P20F14Q100A328N表4.1T表4.1關(guān)系運算T1T2T3T4T51A133M2B120N3A21212O5D1024P表4.2

F(T)4.1關(guān)系運算2.投影（Projection）投影也是單目運算，該運算從表中選出指定的屬性值組成一個新表，記為ΠA(R)。其中，A是屬性名（即列名）表，R是表名。例如，在T表中對T1、T2和T5投影，運算式為

T1,T2,T5(T)該運算得到如表4.3所示的新表。T1T2T5T1T2T51A1M5DP2B1N20FQ3A2O100A3N表4.3

T1,T2,T5(T)4.1關(guān)系運算3.連接（JOIN）連接是把兩個表中的行按照給定的條件進行拼接而形成新表，記為RS。其中，R、S是被操作的表，F(xiàn)是條件。例如，若表A和B分別如表4.4和表4.5所示，則

如表4.6所示，其中，F(xiàn)為T1=T3。T1T2T1T2T1T21A6F2BT3T4T5T3T4T513M20NT1T2T3T4T51A13M2B20N表4.4A表表4.5B表表4.6F4.1關(guān)系運算數(shù)據(jù)庫應(yīng)用中最常用的是“自然連接”。進行自然連接運算要求兩個表有共同屬性（列），自然連接運算的結(jié)果表是在參與操作的兩個表的共同屬性上進行等值連接后，再去除重復(fù)的屬性后所得的新表。自然連接運算記為

，其中，R和S是參與運算的兩個表。例如，若表A和B分別如表4.7和表4.8所示，則

如表4.9所示。T1T2T3T1T2T3T1T2T310A1B15A1C220D2C2T1T4T5T6T1T4T5T61100A1D1200A2D11002B2C1510A2C2T1T2T3T4T5T65A1C210A2C220D2C20A2D1表4.7A表表4.8B表表4.94.2數(shù)據(jù)庫的查詢當(dāng)用戶登錄到SQLServer后，即被指定一個默認數(shù)據(jù)庫，通常是master數(shù)據(jù)庫。使用USEdatabase_name語句可以選擇當(dāng)前要操作的數(shù)據(jù)庫。USEPXSCJGOSELECT語句它是T-SQL的核心。語法格式如下：[WITH<common_table_expression>] /*指定臨時命名的結(jié)果集*/SELECT[ALL|DISTINCT][TOPexpression[PERCENT][WITHTIES]] <select_list> /*指定要選擇的列及其限定*/ [INTOnew_table] /*INTO子句，指定結(jié)果存入新表*/ [FROMtable_source]/*FROM子句，指定表或視圖*/ [WHEREsearch_condition] /*WHERE子句，指定查詢條件*/ [GROUPBYgroup_by_expression]/*GROUPBY子句，指定分組表達式*/ [HAVINGsearch_condition] /*HAVING子句，指定分組統(tǒng)計條件*/ [ORDERBYorder_expression[ASC|DESC]]/*ORDER子句，指定排序表達式和順序*/所有被使用的字句必須按語法說明中顯示的順序嚴格排序。Select字句返回表的結(jié)果集，稱為表值表達式。4.2.1選擇列<select_list>::={* /*選擇當(dāng)前表或視圖的所有列*/

|{table_name|view_name|table_alias}.*/*選擇指定的表或視圖的所有列*/

|{column_name|[]expression|$IDENTITY|$ROWGUID} /*選擇指定的列*/

|udt_column_name[{.|::}{{property_name|field_name}|method_name(argument[,...n])}] /*選擇用戶定義數(shù)據(jù)類型的屬性、方法和字段*/ |expression [[AS]column_alias] /*AS子句，定義列別名*/

|column_alias=expression /*選擇指定列并更改列標題*/}

[,...n]4.2.1選擇列1．選擇所有列使用“*”表示選擇一個表或視圖中的所有列?！纠?.1】

查詢PXSCJ數(shù)據(jù)庫中XSB表的所有數(shù)據(jù)。USEPXSCJGOSELECT* FROMXSBGO執(zhí)行完后SQLServerManagementStudio的結(jié)果窗口中將顯示XSB表的所有數(shù)據(jù)。4.2.1選擇列2．選擇一個表中指定的列使用SELECT語句選擇一個表中的某些列，各列名之間要以逗號分隔。其中，$IDENTITY表示選擇標識列，$ROWGUID表示選擇ROWGUIDCOL屬性的全局標識列。如果在FROM子句中有多個表具有ROWGUIDCOL屬性，則必須用特定的表名限定$ROWGUID，如T1.$ROWGUID。【例4.2】查詢PXSCJ數(shù)據(jù)庫的XSB表中各個同學(xué)的姓名、專業(yè)和總學(xué)分。USEPXSCJGOSELECT姓名,專業(yè),總學(xué)分 FROMXSBGOSQLServer2008中還能一次執(zhí)行多個查詢。4.2.1選擇列【例4.3】

查詢XSB表中計算機專業(yè)同學(xué)的學(xué)號、姓名和總學(xué)分，查詢XSB表中所有列。SELECT學(xué)號,姓名,總學(xué)分 FROMXSB WHERE專業(yè)

='計算機'GOSELECT* FROMXSB4.2.1選擇列執(zhí)行后在結(jié)果窗口中將分別列出兩個查詢語句的結(jié)果，如圖4.1所示：圖4.1一次執(zhí)行多個查詢4.2.1選擇列3．定義列別名當(dāng)希望查詢結(jié)果中的某些列或所有列顯示時使用自己選擇的列標題時，可以在列名之后使用AS子句來更改查詢結(jié)果的列標題名。其中，column_alias是指定的列別名。【例4.4】

查詢XSB表中計算機系同學(xué)的學(xué)號、姓名和總學(xué)分，結(jié)果中各列的標題分別指定為number、name和mark。USEPXSCJGOSELECT學(xué)號

ASnumber,姓名

ASname,總學(xué)分

ASmark FROMXSB WHERE專業(yè)='計算機'4.2.1選擇列執(zhí)行結(jié)果如下：4.2.1選擇列更改查詢結(jié)果中的列標題也可以使用column_alias=expression的形式。例如，SELECTnumber=學(xué)號,name=姓名,mark=總學(xué)分 FROMXSB WHERE專業(yè)='計算機'該語句的執(zhí)行結(jié)果與上例的結(jié)果完全相同。當(dāng)自定義的列標題中含有空格時，必須使用引號將標題括起來。例如，SELECT'Studentnumber'=學(xué)號,姓名

AS'Studentname',mark=總學(xué)分 FROMXSB WHERE專業(yè)='計算機'4.2.1選擇列4．替換查詢結(jié)果中的數(shù)據(jù)在對表進行查詢時，有時希望對所查詢的某些列得到的是一種概念而不是具體的數(shù)據(jù)。例如，查詢XSB表的總學(xué)分，希望知道的是學(xué)習(xí)的總體情況，這時，就可以用等級來替換總學(xué)分的具體數(shù)字。要替換查詢結(jié)果中的數(shù)據(jù)，則要使用查詢中的CASE表達式，格式為CASE WHEN條件1THEN表達式1 WHEN條件2THEN表達式2…… ELSE表達式END4.2.1選擇列【例4.5】

查詢XSB表中計算機系各同學(xué)的學(xué)號、姓名和總學(xué)分，對其總學(xué)分按以下規(guī)則進行替換：若總學(xué)分為空值，則替換為“尚未選課”；若總學(xué)分小于50，則替換為“不及格”；若總學(xué)分在50與52之間，則替換為“合格”；若總學(xué)分大于52，則替換為“優(yōu)秀”。列標題更改為“等級”。USEPXSCJGOSELECT學(xué)號,姓名,等級= CASE WHEN總學(xué)分

ISNULLTHEN'尚未選課' WHEN總學(xué)分

<50THEN'不及格' WHEN總學(xué)分

>=50and總學(xué)分<=52THEN'合格' ELSE'優(yōu)秀' END FROMXSB WHERE專業(yè)='計算機'GO4.2.1選擇列執(zhí)行結(jié)果如下：4.2.1選擇列5．計算列值使用SELECT對列進行查詢時，在結(jié)果中可以輸出對列值計算后的值，即SELECT子句可使用表達式作為結(jié)果，格式為SELECTexpression[,expression]【例4.6】按120分計算成績并顯示學(xué)號為081101的學(xué)生的成績情況。USEPXSCJGOSELECT學(xué)號,課程號,成績120=成績*1.20 FROMCJB WHERE學(xué)號='081101'執(zhí)行結(jié)果如下：4.2.1選擇列6．消除結(jié)果集中的重復(fù)行對表只選擇其某些列時，可能會出現(xiàn)重復(fù)行。例如，若對PXSCJ數(shù)據(jù)庫的XSB表只選擇專業(yè)和總學(xué)分，則出現(xiàn)多行重復(fù)的情況?？梢允褂肈ISTINCT關(guān)鍵字消除結(jié)果集中的重復(fù)行，其格式是SELECTDISTINCT|ALLcolumn_name[,column_name…]關(guān)鍵字DISTINCT的含義是，對結(jié)果集中的重復(fù)行只選擇一個，保證行的唯一性?！纠?.7】

對PXSCJ數(shù)據(jù)庫的XSB表只選擇專業(yè)和總學(xué)分，消除結(jié)果集中的重復(fù)行。USEPXSCJGOSELECTDISTINCT專業(yè),總學(xué)分 FROMXSB執(zhí)行結(jié)果如下：4.2.1選擇列7．限制結(jié)果集返回行數(shù)如果SELECT語句返回的結(jié)果集的行數(shù)非常多，那么可以使用TOP選項限制其返回的行數(shù)。TOP選項的基本格式為[TOPexpression[PERCENT][WITHTIES]]TOP子句可以用于

SELECT、INSERT、UPDATE和

DELETE語句中?！纠?.8】

對PXSCJ數(shù)據(jù)庫的XSB表選擇姓名、專業(yè)和總學(xué)分，返回結(jié)果集的前6行。SELECTTOP6姓名,專業(yè),總學(xué)分 FROMXSB4.2.1選擇列8．選擇用戶定義數(shù)據(jù)類型列udt_column_name為要指定的用戶定義類型列的名稱。有關(guān)用戶定義數(shù)據(jù)類型將在第5章中討論。9．聚合函數(shù)SELECT子句中的表達式中還可以包含所謂的聚合函數(shù)。聚合函數(shù)常常用于對一組值進行計算，然后返回單個值。聚合函數(shù)通常與GROUPBY子句一起使用。如果一個SELECT語句中有一個GROUPBY子句，則這個聚合函數(shù)對所有列起作用，如果沒有，則SELECT語句只產(chǎn)生一行作為結(jié)果。SQLServer2008所提供的聚合函數(shù)列于表4.10中。4.2.1選擇列函

數(shù)

名說

明AVG求組中值的平均值BINARY_CHECKSUM返回對表中的行或表達式列表計算的二進制校驗值，可用于檢測表中行的更改CHECKSUM返回在表的行上或在表達式列表上計算的校驗值，用于生成哈希索引CHECKSUM_AGG返回組中值的校驗值COUNT求組中項數(shù)，返回int類型整數(shù)COUNT_BIG求組中項數(shù)，返回bigint類型整數(shù)GROUPING產(chǎn)生一個附加的列GROUPING_ID為聚合列列表中的每一行創(chuàng)建一個值以標識聚合級別MAX求最大值MIN求最小值SUM返回表達式中所有值的和STDEV返回給定表達式中所有值的統(tǒng)計標準偏差STDEVP返回給定表達式中所有值的填充統(tǒng)計標準偏差VAR返回給定表達式中所有值的統(tǒng)計方差VARP返回給定表達式中所有值的填充統(tǒng)計方差表4.10聚合函數(shù)表4.2.1選擇列下面對常用的聚合函數(shù)加以介紹。（1）SUM和AVGSUM和AVG分別用于求表達式中所有值項的總和與平均值，語法格式為SUM/AVG([ALL|DISTINCT]expression)其中，expression是常量、列、函數(shù)或表達式，其數(shù)據(jù)類型只能是int、smallint、tinyint、bigint、decimal、numeric、float、real、money和smallmoney。ALL表示對所有值進行運算，DISTINCT表示去除重復(fù)值，默認為ALL。SUM/AVG忽略NULL值?！纠?.9】

求選修101課程的學(xué)生的平均成績。SELECTAVG(成績)AS'課程101平均成績' FROMCJB WHERE課程號

='101'4.2.1選擇列使用聚合函數(shù)作為SELECT的選擇列時，若不為其指定列標題，則系統(tǒng)將對該列輸出標題“無列名”?！纠?.10】

求學(xué)號為081101的同學(xué)所學(xué)課程的總成績。SELECTSUM(成績)AS'課程總成績' FROMCJB WHERE學(xué)號

='081101';結(jié)果為234。4.2.1選擇列（2）MAX和MINMAX和MIN分別用于求表達式中所有值項的最大值與最小值，語法格式為MAX/MIN([ALL|DISTINCT]expression)其中，expression是常量、列、函數(shù)或表達式，其數(shù)據(jù)類型可以是數(shù)字、字符和時間日期類型。ALL、DISTINCT的含義及默認值與SUM/AVG函數(shù)相同。MAX/MIN忽略NULL值?！纠?.11】求選修101課程的學(xué)生的最高分和最低分。SELECTMAX(成績)AS'課程101的最高分',MIN(成績)AS'課程101的最低分' FROMCJB WHERE課程號

='101'執(zhí)行結(jié)果如下：4.2.1選擇列（3）COUNTCOUNT用于統(tǒng)計組中滿足條件的行數(shù)或總行數(shù)，格式為COUNT({[ALL|DISTINCT]expression}|*)其中，expression是一個表達式，其數(shù)據(jù)類型是除text、image或ntext之外的任何類型。ALL、DISTINCT的含義及默認值與SUM/AVG函數(shù)相同，COUNT忽略NULL值。【例4.12】

求學(xué)生的總數(shù)。SELECTCOUNT(*)AS'學(xué)生總數(shù)' FROMXSB學(xué)生總數(shù)為22，使用COUNT(*)時將返回檢索行的總數(shù)目，不論其是否包含

NULL值?！纠?.13】

統(tǒng)計備注不為空的學(xué)生數(shù)。SELECTCOUNT(備注)AS'備注不為空的學(xué)生數(shù)' FROMXSB;4.2.1選擇列【例4.14】統(tǒng)計總學(xué)分在50分以上的人數(shù)。SELECTCOUNT(總學(xué)分)AS'總學(xué)分在50分以上的人數(shù)' FROMXSB WHERE總學(xué)分>50;執(zhí)行結(jié)果為2?！纠?.15】

求選修了課程的學(xué)生總數(shù)。SELECTCOUNT(DISTINCT學(xué)號) FROMCJBCOUNT_BIG函數(shù)的格式、功能與COUNT函數(shù)都相同，區(qū)別僅在于COUNT_BIG返回bigint類型值。4.2.2WHERE子句在SQLServer2008中，選擇行是通過在SELECT語句的WHERE子句中指定選擇的條件來實現(xiàn)的。WHERE子句必須緊跟在FROM子句之后，其基本格式為WHERE<search_condition>其中，search_condition為查詢條件。<search_condition>::= {[NOT]<precdicate>|(<search_condition>)} [{AND|OR}[NOT]{<predicate>|(<search_condition>)}][,…n]其中，<predicate>為判定運算，結(jié)果為TRUE、FALSE或UNKNOWN。NOT表示對判定的結(jié)果取反，AND用于組合兩個條件，兩個條件都為TRUE時值才為TRUE。OR也用于組合兩個條件，兩個條件有一個條件為TRUE時值就為TRUE。4.2.2WHERE子句<predicate>::={

expression{=|<|<=|>|>=|<>|!=|!<|!>}expression /*比較運算*/|match_expression[NOT]LIKEpattern[ESCAPEEscape_character] /*字符串模式匹配*/|expression[NOT]BETWEENexpressionANDexpression /*指定范圍*/|expressionIS[NOT]NULL /*是否空值判斷*/|CONTAINS({column|*},'<contains_search_condition>') /*包含式查詢*/|FREETEXT({column|*},'freetext_string') /*自由式查詢*/|expression[NOT]IN(subquery|expression[,…n]) /*IN子句*/

|expression{=|<|<=|>|>=|<>|!=|!<|!>}{ALL|SOME|ANY}(subquery) /*比較子查詢*/|EXIST(subquery) /*EXIST子查詢*/}4.2.2WHERE子句1．比較運算表達式比較運算符共有9個，分別是

=（等于）、<（小于）、<=（小于等于）、>（大于）、>=（大于等于）、<>（不等于）、!=（不等于）、!<（不小于）、!>（不大于）。expression{=|<|<=|>|>=|<>|!=|!<|!>}expression其中，expression是除text、ntext和image以外類型的表達式。當(dāng)兩個表達式值均不為空值（NULL）時，比較運算返回邏輯值TRUE（真）或FALSE（假）。而當(dāng)兩個表達式值中有一個為空值或都為空值時，比較運算將返回UNKNOWN?！纠?.16】

查詢PXSCJ數(shù)據(jù)庫XSB表中學(xué)號為081101的同學(xué)的情況。USEPXSCJGO SELECT姓名,學(xué)號,總學(xué)分FROMXSBWHERE學(xué)號='081101';執(zhí)行結(jié)果如下：4.2.2WHERE子句【例4.17】

查詢XSB表中總學(xué)分大于50的同學(xué)的情況。SELECT姓名,學(xué)號,出生時間,總學(xué)分FROMXSBWHERE總學(xué)分>50;執(zhí)行結(jié)果如下：【例4.18】

查詢XSB表中通信工程專業(yè)總學(xué)分大于等于42的同學(xué)的情況。USEPXSCJGOSELECT* FROMXSB WHERE專業(yè)='通信工程'AND總學(xué)分

>=424.2.2WHERE子句2．模式匹配LIKE謂詞用于指出一個字符串是否與指定的字符串相匹配，返回邏輯值TRUE或FALSE。LIKE謂詞表達式的格式為match_expression[NOT]LIKEpattern[ESCAPEEscape_character]說明如下。match_expression：匹配表達式，一般為字符串表達式，在查詢語句中可以是列名。pattern：在match_expression

中的搜索模式串。在搜索模式串中可以使用通配符，表4.11列出了LIKE謂詞可以使用的通配符及其說明。通

配

符說

明%代表0個或多個字符_（下畫線）代表單個字符[]指定范圍（如[a-f]、[0-9]）或集合（如[abcdef]）中的任何單個字符[^]指定不屬于范圍（如

[^a-f]、[^0-9]）或集合（如[^abcdef]）的任何單個字符表4.11通配符列表4.2.2WHERE子句Escape_character：轉(zhuǎn)義字符，應(yīng)為有效的SQLServer字符，Escape_character沒有默認值，且必須為單個字符。當(dāng)模式串中含有與通配符相同的字符時，應(yīng)通過該字符前的轉(zhuǎn)義字符指明其為模式串中的一個匹配字符。使用ESCAPE可指定轉(zhuǎn)義符。NOTLIKE：使用NOTLIKE與LIKE的作用相反。使用帶%通配符的LIKE時，若使用LIKE進行字符串比較，則模式字符串中的所有字符都有意義，包括起始或尾隨空格?！纠?.19】

查詢XSB表中姓“王”且單名的學(xué)生情況。SELECT* FROMXSB WHERE姓名

LIKE'王_'執(zhí)行結(jié)果如下：4.2.2WHERE子句【例4.20】

查詢XSB表中學(xué)號倒數(shù)第3個數(shù)字為1，且倒數(shù)第1個數(shù)在1～5之間的學(xué)生學(xué)號、姓名及專業(yè)。SELECT學(xué)號,姓名,專業(yè) FROMXSB WHERE學(xué)號LIKE'%1_[12345]'如果需要查找一個通配符，通配符失去通配意義，必須使用一個轉(zhuǎn)義字符，取其符號本意?！纠?.21】查詢XSB表中名字包含下劃線的學(xué)生學(xué)號和姓名。SELECT學(xué)號,姓名 FROMXSB WHERE學(xué)號LIKE'%#_%'ESCAPE'#'

4.2.2WHERE子句3．范圍比較用于范圍比較的關(guān)鍵字有兩個：BETWEEN和IN。當(dāng)要查詢的條件是某個值的范圍時，可以使用BETWEEN關(guān)鍵字。BETWEEN關(guān)鍵字指出查詢范圍，格式為：expression[NOT]BETWEENexpression1ANDexpression2當(dāng)不使用NOT時，若表達式expression的值在表達式expression1與expression2之間（包括這兩個值），則返回TRUE，否則返回FALSE；當(dāng)使用NOT時，返回值剛好相反。使用IN關(guān)鍵字可以指定一個值表，值表中列出所有可能的值，當(dāng)與值表中的任一個匹配時，即返回TRUE，否則返回FALSE。使用IN關(guān)鍵字指定值表的格式為：expressionIN(expression[,…n])【例4.22】查詢XSB表中不在1989年出生的學(xué)生情況。SELECT學(xué)號,姓名,專業(yè),出生時間 FROMXSB

WHERE出生時間

NOTBETWEEN'1989-1-1'and'1989-12-31'4.2.2WHERE子句【例4.23】

查詢XSB表中專業(yè)為“計算機”、“通信工程”或“無線電”的學(xué)生情況。SELECT* FROMXSB WHERE專業(yè)

IN('計算機','通信工程','無線電')該語句與下列語句等價：SELECT* FROMXSB WHERE專業(yè)='計算機'or

專業(yè)='通信工程'or專業(yè)='無線電'4.2.2WHERE子句4．空值比較當(dāng)需要判定一個表達式的值是否為空值時，使用ISNULL關(guān)鍵字，格式為：expressionIS[NOT]NULL當(dāng)不使用NOT時，若表達式expression的值為空值，則返回TRUE，否則返回FALSE；當(dāng)使用NOT時，結(jié)果剛好相反?！纠?.24】查詢總學(xué)分尚不定的學(xué)生情況。SELECT* FROMXSB WHERE總學(xué)分

ISNULL4.2.2WHERE子句5．CONTAINS謂詞若需要在表中搜索指定的單詞、短語或近義詞，可以使用CONTAINS謂詞。精確匹配或模糊匹配，還可以加權(quán)匹配。要使用此謂詞，必須在操作的表上事先建立全文索引。

功能名稱DatacenterEnterpriseStandardWebWorkgroup具有高級服務(wù)的

ExpressExpresswithToolsExpress表和索引分區(qū)支持支持并行索引操作支持支持并行一致性檢查

(DBCC)支持支持全文搜索支持支持支持支持支持支持查詢中的語言規(guī)范支持支持支持支持支持支持4.2.2WHERE子句第1步：要建立全文索引，首先要啟動全文搜索服務(wù)，這項服務(wù)默認是禁用的。啟動的方法是：打開“SQLServer配置管理器”窗口，進入“SQLFull-TextFilterDaemonLauncher”服務(wù)的屬性對話框。選擇“服務(wù)”選項卡，將“啟動模式”設(shè)置為“自動”，如圖4.2所示。設(shè)置完后單擊“應(yīng)用”按鈕，再選擇“登錄”選項卡，將內(nèi)置賬戶設(shè)置為本地賬戶，如localsystem，如圖4.3所示，單擊“啟動”按鈕啟動全文搜索，最后單擊“確定”按鈕關(guān)閉窗口。

圖4.2更改服務(wù)啟動模式

圖4.3啟動全文搜索4.2.2WHERE子句第2步：創(chuàng)建全文目錄。在對象資源管理器中展開數(shù)據(jù)庫“PXSCJ”，選擇“存儲”目錄下的“全文目錄”，右擊鼠標，選擇“新建全文目錄”選項。在彈出的“新建全文目錄-PXSCJ”窗口中填寫全文目錄名稱，如fulltext，如圖4.4所示，單擊“確定”按鈕。圖4.4創(chuàng)建全文目錄4.2.2WHERE子句第3步：在全文目錄中注冊需要全文索引的表。在“全文目錄”目錄下右擊剛剛新建的全文目錄“fulltext”，選擇“屬性”菜單項，進入fulltext的屬性窗口。選擇“表/視圖”選項卡，選擇需要全文索引的表，如“dbo.XSB”，單擊

按鈕。選擇完需要全文索引的表后，在“合格列”欄中會顯示能夠進行全文索引的列，選擇需要的列，如圖4.5所示，單擊“確定”按鈕。圖4.5在全文目錄中注冊需要全文索引的表4.2.2WHERE子句CONTAINS謂詞的語法格式如下。CONTAINS({column|*},'<contains_search_condition>'[,LANGUAGElanguage_term])其中，column表示在指定的列中搜索，可以指定多個列；類型為

char、varchar、nchar、nvarchar、text、ntext、image、xml和

varbinary(max)的列是可進行全文搜索的有效列。*表示在所有列中搜索，language_term表示用戶查詢時所用的語言。<contains_search_condition>指定要在

column_name

中搜索的文本和匹配條件。<contains_search_condition>::=

{ <simple_term>

|<prefix_term>

|<generation_term>

|<proximity_term>

|<weighted_term>

}

|{ (<contains_search_condition>)[{<AND>|<ANDNOT>|<OR>}] <contains_search_condition>[...n]

}4.2.2WHERE子句說明如下。<simple_term>：用于說明搜索的是單詞還是短語，格式為：word|"phrase"其中，word為單詞，即不含空格和標點符號的字符串；短語是含一個或多個空格的字符串。如果搜索的是短語，則需要用雙引號將其括起來。<prefix_term>：給出了要搜索的單詞或短語必須匹配的前綴，其格式為：{"word*"|"phase*"}其中，word為單詞，phase為短語，當(dāng)查詢的串是短語時，需用雙引號定界。<generation_term>：說明搜索包含原詞的派生詞，所謂派生詞是指原詞的名詞單、復(fù)數(shù)形式或動詞的各種時態(tài)等。格式為：

FORMSOF({INFLECTIONAL|THESAURUS},<simple_term>[,...n])INFLECTIONAL選項表示指定要對指定的簡單字詞使用與語言相關(guān)的詞干分析器。THESAURUS選項表示指定使用對應(yīng)于列全文語言或指定的查詢語言的同義詞庫。<proximity_term>：表示搜索包含NEAR或~運算符左右兩邊的詞或短語。格式為：{<simple_term>|<prefix_term>}{{NEAR|~}{<simple_term>|<prefix_term>}[…n]4.2.2WHERE子句<weight_term>：指明本語句是加權(quán)搜索，即查詢的數(shù)據(jù)與給定的權(quán)重進行加權(quán)匹配。格式為：ISABOUT({{

<simple_term>

|<prefix_term>

|<generation_term>

|<proximity_term>

}

[WEIGHT(weight_value)]

}[,...n])其中，weight_value是一個0～1之間的數(shù)，表示權(quán)重。4.2.2WHERE子句【例4.25】

使用CONTAINS謂詞搜索XSB表中包含字符“工程”的所有行。USEPXSCJGOSELECT* FROMXSB WHERECONTAINS(*,'工程')執(zhí)行結(jié)果如下：查詢多個列UseAdventureWorks2012;GOSELECTName,ColorFROMProduction.ProductWHERECONTAINS((Name,Color),'Red');USEAdventureWorks2012;GOSELECTNameFROMProduction.ProductWHERECONTAINS(Name,‘MountainORRoad’)

//示例返回包含短語"Mountain"或"Road"的所有產(chǎn)品。GOUSEAdventureWorks2012;GOSELECTNameFROMProduction.ProductWHERECONTAINS(Name,‘“Chain*”’);//前綴，返回的所有產(chǎn)品名稱中，其Name列中至少有一個詞以前輟chain開頭GOUSEAdventureWorks2012;GOSELECTNameFROMProduction.ProductWHERECONTAINS(Name,'"chain*"OR"full*"');GO//示例將返回包含以"chain"或"full"為前綴的字符串的所有類別說明。表中搜索包含“bike”詞、在“control”詞的10個詞范圍內(nèi)且使用指定順序（即，“bike”排在“control”前面）的所有注釋。

USEAdventureWorks2012;GOSELECTCommentsFROMProduction.ProductReviewWHERECONTAINS(Comments,'NEAR((bike,control),10,TRUE)');GO//

proximity_term>CONTAINS與<generation_term>一起使用USEAdventureWorks2012;GOSELECTDescriptionFROMProduction.ProductDescriptionWHERECONTAINS(Description,'FORMSOF(INFLECTIONAL,ride)');GO//示例搜索包含以下形式的ride詞的所有產(chǎn)品：“riding”、“ridden”等。將CONTAINS與<weighted_term>一起使用

USEAdventureWorks2012;GOSELECTDescriptionFROMProduction.ProductDescriptionWHERECONTAINS(Description,'ISABOUT(performanceweight(.8),comfortableweight(.4),smoothweight(.2))');GO以下示例使用變量替代具體的搜索詞。USEAdventureWorks2012;GODECLARE@SearchWordnvarchar(30)SET@SearchWord=N‘Performance‘//Unicode字符串常量SELECTDescriptionFROMProduction.ProductDescription

WHERECONTAINS(Description,@SearchWord);GO將CONTAINS與邏輯運算符(AND)一起使用USEAdventureWorks2012;GOSELECTDescriptionFROMProduction.ProductDescriptionWHEREProductDescriptionID<>5AND

CONTAINS(Description,'AluminumANDspindle');GO4.2.2WHERE子句6．FREETEXT謂詞與CONTAINS謂詞類似，F(xiàn)REETEXT謂詞也用于在一個表中搜索單詞或短語，并要求表已建立全文索引。格式為FREETEXT({column|column_list|*},'freetext_string'[,LANGUAGElanguage_term])其中，freetext_string是要搜索的字符串。FREETEXT的查詢精度沒有CONTAINS高，并不要求對它們進行嚴格的模式匹配。FREETEXT對所查詢的串也沒有寫法要求，因此FREETEXT也稱為自由式查詢?！纠?.26】

使用FREETEXT謂詞搜索XSB表中包含字符“李”的所有行。SELECT* FROMXSB WHEREFREETEXT(*,'李')USEAdventureWorks2012;GOSELECTTitleFROMProduction.DocumentWHEREFREETEXT(Document,'vitalsafetycomponents');GO搜索包含與vital、safety、components相關(guān)的單詞的所有文檔。4.2.2WHERE子句7．子查詢T-SQL允許SELECT多層嵌套使用，用來表示復(fù)雜的查詢。子查詢除了可以用在SELECT語句中，還可以用在INSERT、UPDATE及DELETE語句中。子查詢通常與IN、EXIST謂詞及比較運算符結(jié)合使用。（1）IN子查詢IN子查詢用于進行一個給定值是否在子查詢結(jié)果集中的判斷，格式為：expression[NOT]IN(subquery)其中，subquery是子查詢。當(dāng)表達式expression與子查詢subquery的結(jié)果表中的某個值相等時，IN謂詞返回TRUE，否則返回FALSE；若使用了NOT，則返回的值剛好相反。4.2.2WHERE子句【例4.27】查找選修了課程號為206的課程的學(xué)生情況。USEPXSCJGOSELECT* FROMXSB WHERE學(xué)號

IN (SELECT學(xué)號 FROMCJB WHERE課程號

='206')在執(zhí)行包含子查詢的SELECT語句時，系統(tǒng)先執(zhí)行子查詢，產(chǎn)生一個結(jié)果表，再執(zhí)行查詢。本例中，先執(zhí)行上面括號里面的子查詢：SELECT學(xué)號FROMCJBWHERE課程名

='206'4.2.2WHERE子句【例4.28】

查找未選修離散數(shù)學(xué)的學(xué)生情況。SELECT* FROMXSB WHERE學(xué)號

NOTIN (SELECT學(xué)號FROMCJBWHERE課程號

IN (SELECT課程號 FROMKCBWHERE課程名

='離散數(shù)學(xué)') )A.比較OR和INUSEAdventureWorks2012;GOSELECTp.FirstName,p.LastName,e.JobTitleFROMPerson.PersonpJOINHumanResources.EmployeeASeONp.BusinessEntityID=e.BusinessEntityIDWHEREe.JobTitle='DesignEngineer'ORe.JobTitle='ToolDesigner'ORe.JobTitle='MarketingAssistant';GOUSEAdventureWorks2012;GOSELECTp.FirstName,p.LastName,e.JobTitleFROMPerson.PersonpJOINHumanResources.EmployeeASeONp.BusinessEntityID=e.BusinessEntityIDWHEREe.JobTitleIN('DesignEngineer','ToolDesigner','MarketingAssistant');GOB.帶子查詢使用INUSEAdventureWorks2012;GOSELECTp.FirstName,p.LastNameFROMPerson.PersonASpJOINSales.SalesPersonASspONp.BusinessEntityID=sp.BusinessEntityIDWHEREp.BusinessEntityIDIN(SELECTBusinessEntityIDFROMSales.SalesPersonWHERESalesQuota>250000);GOC.帶子查詢使用NOTIN以下示例查找銷售額不超過$250,000的銷售人員。NOTIN查找與值列表中的項不匹配的銷售人員。USEAdventureWorks2012GOSELECTp.FirstName,p.LastNameFROMPerson.PersonASpJOINSales.SalesPersonASspONp.BusinessEntityID=sp.BusinessEntityIDWHEREp.BusinessEntityIDNOTIN(SELECTBusinessEntityIDFROMSales.SalesPersonWHERESalesQuota>250000);GO4.2.2WHERE子句（2）比較子查詢這種子查詢可以認為是IN子查詢的擴展，它使表達式的值與子查詢的結(jié)果進行比較運算，格式為expression{<|<=|=|>|>=|!=|<>|!<|!>}{ALL|SOME|ANY}(subquery)其中，expression為要進行比較的表達式，subquery是子查詢。ALL、SOME和ANY說明對比較運算的限制。ALL指定表達式要與子查詢結(jié)果集中的每個值都進行比較，當(dāng)表達式與每個值都滿足比較的關(guān)系時，才返回TRUE，否則返回FALSE；SOME或ANY表示表達式只要與子查詢結(jié)果集中的某個值滿足比較的關(guān)系，就返回TRUE，否則返回FALSE。4.2.2WHERE子句【例4.29】查找選修了離散數(shù)學(xué)的學(xué)生學(xué)號。SELECT學(xué)號 FROMCJB WHERE課程號

=( SELECT課程號 FROMKCBWHERE課程名

='離散數(shù)學(xué)‘ );【例4.30】

查找比所有計算機系的學(xué)生年齡都大的學(xué)生。SELECT* FROMXSB WHERE 出生時間

<ALL (SELECT出生時間 FROMXSBWHERE專業(yè)='計算機' )4.2.2WHERE子句執(zhí)行結(jié)果如下：【例4.31】查找206號課程成績不低于101號課程最低成績的學(xué)生學(xué)號。SELECT學(xué)號

FROMCJB WHERE 課程號

='206'AND成績

!<ANY (SELECT成績FROMCJBWHERE課程號

='101' )4.2.2WHERE子句（3）EXISTS子查詢EXISTS謂詞用于測試子查詢的結(jié)果是否為空表，若子查詢的結(jié)果集不為空，則EXISTS返回TRUE，否則返回FALSE。EXISTS還可與NOT結(jié)合使用，即NOTEXISTS，其返回值與EXISTS剛好相反。其格式為[NOT]EXISTS(subquery)【例4.32】查找選修206號課程的學(xué)生姓名。SELECT姓名 FROMXSB WHERE EXISTS ( SELECT* FROMCJB WHERE學(xué)號

=XSB.學(xué)號

AND課程號

='206' )4.2.2WHERE子句其處理過程如下：首先查找外層查詢中XSB表的第一行，根據(jù)該行的學(xué)號列值處理內(nèi)層查詢，若結(jié)果不為空，則WHERE條件為真，就把該行的姓名值取出作為結(jié)果集的一行；然后再找XSB表的第2，3…行，重復(fù)上述處理過程直到XSB表的所有行都查找完為止。【例4.33】查找選修了全部課程的同學(xué)的姓名。SELECT姓名

FROMXSB WHERENOTEXISTS

( SELECT* FROMKCB WHERENOTEXISTS ( SELECT* FROMCJB WHERE學(xué)號=XSB.學(xué)號

AND課程號=KCB.課程號 ) )比較使用EXISTS和IN的查詢SELECTa.FirstName,a.LastNameFROMPerson.PersonASaWHEREEXISTS(SELECT*FROMHumanResources.EmployeeASbWHEREa.BusinessEntityID=b.BusinessEntityIDANDa.LastName='Johnson');GOSELECTa.FirstName,a.LastNameFROMPerson.PersonASaWHEREa.LastNameIN(SELECTa.LastNameFROMHumanResources.EmployeeASbWHEREa.BusinessEntityID=b.BusinessEntityIDANDa.LastName='Johnson');GO比較使用EXISTS和=ANY的查詢USEAdventureWorks2012;GOSELECTDISTINCTs.NameFROMSales.StoreASsWHEREEXISTS(SELECT*FROMPurchasing.VendorASvWHEREs.Name=v.Name);GOUSEAdventureWorks2012;GOSELECTDISTINCTs.NameFROMSales.StoreASsWHEREs.Name=ANY(SELECTv.NameFROMPurchasing.VendorASv);GO比較使用EXISTS和IN的查詢USEAdventureWorks2012;GOSELECTp.FirstName,p.LastName,e.JobTitleFROMPerson.PersonASpJOINHumanResources.EmployeeASeONe.BusinessEntityID=p.BusinessEntityID

WHEREEXISTS(SELECT*FROMHumanResources.DepartmentASdJOINHumanResources.EmployeeDepartmentHistoryASedhONd.DepartmentID=edh.DepartmentIDWHEREe.BusinessEntityID=edh.BusinessEntityIDANDd.NameLIKE'P%');GOUSEAdventureWorks2012;GOSELECTp.FirstName,p.LastName,e.JobTitleFROMPerson.PersonASpJOINHumanResources.EmployeeASeONe.BusinessEntityID=p.BusinessEntityID

JOINHumanResources.EmployeeDepartmentHistoryASedhONe.BusinessEntityID=edh.BusinessEntityID

WHEREedh.DepartmentIDIN(SELECTDepartmentIDFROMHumanResources.DepartmentWHERENameLIKE'P%');GO4.2.2WHERE子句另外，子查詢還可以用在SELECT語句的其他子句中，如FROM子句。SELECT關(guān)鍵字后面也可以定義子查詢?！纠?.34】從XSB表中查找所有女學(xué)生的姓名、學(xué)號及其與081101號學(xué)生的年齡差距。SELECT學(xué)號,姓名,YEAR(出生時間)-YEAR( (SELECT出生時間 FROMXSB WHERE學(xué)號='081101' ))AS年齡差距 FROMXSB WHERE性別=0執(zhí)行結(jié)果如下：4.2.3FROM子句SELECT的查詢對象由FROM子句指定，其格式為[FROM{<table_source>}[,…n]]其中，table_source指出了要查詢的表或視圖。<table_source>::={

table_or_view_name[[AS]table_alias] /*查詢表或視圖，可指定別名*/

[WITH(<table_hint>[[,]...n])]

|rowset_function[[AS]table_alias] /*行集函數(shù)*/

[(bulk_column_alias[,...n])]

|user_defined_function[[AS]table_alias] /*指定表值函數(shù)*/

|OPENXML<openxml_clause> /*XML文檔*/

|derived_table[AS]table_alias[(column_alias[,...n])] /*子查詢*/

|<joined_table> /*連接表*/|<pivoted_table> /*將行轉(zhuǎn)換為列*/|<unpivoted_table> /*將列轉(zhuǎn)換為行*/}4.2.3FROM子句1．table_or_view_nametable_or_view_name指定SELECT語句要查詢的表或視圖，表和視圖可以是一個或多個，有關(guān)視圖的內(nèi)容在4.3節(jié)中介紹?！纠?.35】查找表KCB中101號課程的開課學(xué)期。USEPXSCJGOSELECT開課學(xué)期 FROMKCB WHERE課程號='101'查詢結(jié)果為1?！纠?.36】查找081101號學(xué)生計算機基礎(chǔ)課的成績。SELECT成績 FROMCJB,KCB WHERECJB.課程號=KCB.課程號 AND學(xué)號='081101' AND課程名='計算機基礎(chǔ)'4.2.3FROM子句【例4.37】查找選修了學(xué)號為081102的同學(xué)所選修的全部課程的同學(xué)的學(xué)號。

SELECTDISTINCT學(xué)號 FROMCJBASCJ1 WHERENOTEXISTS ( SELECT* FROMCJBASCJ2 WHERECJ2.學(xué)號

='081102'ANDNOTEXISTS ( SELECT* FROMCJBASCJ3 WHERECJ3.學(xué)號=CJ1.學(xué)號

ANDCJ3.課程號

=CJ2.課程號 ) )4.2.3FROM子句2．rowset_functionrowset_function是一個行集函數(shù)，行集函數(shù)通常返回一個表或視圖。bulk_column_alias是替代結(jié)果集內(nèi)列名的可選別名。主要的行集函數(shù)有CONTAINSTABLE、FREETEXTTABLE、OPENDATASOURCE、OPENQUERY、OPENROWSET和OPENXML。（1）CONTAINSTABLE函數(shù)。該函數(shù)與CONTAINS謂詞相對應(yīng)，用于對表進行全文查詢，并且要求所查詢的表上建立了全文索引。CONTAINSTABLE函數(shù)的語法格式為：CONTAINSTABLE(table,{column|column_list|*},'<contains_search_condition>'[,top_n_by_rank])其中，table是進行全文查詢的表，column指定被查詢的列，column_list可以指定多個列，*指對所有列進行查詢。contains_search_condition與CONTAINS謂詞中的搜索條件完全相同。（2）FREETEXTTABLE函數(shù)。FREETEXTTABLE函數(shù)與FREETEXT謂詞相對應(yīng)，它的使用與CONTAINSTABLE函數(shù)類似，格式為：FREETEXTTABLE(table,{column|column_list|*},'freetext_string'[,top_n_by_rank])該函數(shù)使用與FREETEXT謂詞相同的搜索條件。4.2.3FROM子句（3）OPENDATASOURCE函數(shù)。該函數(shù)使用戶連接到服務(wù)器。格式為：OPENDATASOURCE(provider_name,init_string)其中，provider_name是用于訪問數(shù)據(jù)源OLEDB訪問接口的PROGID的名稱，

init_string是連接字符串，這些字符串將要傳遞給目標提供程序的

IDataInitialize

接口。（4）OPENQUERY函數(shù)。該函數(shù)在給定的鏈接服務(wù)器（一個OLEDB數(shù)據(jù)源）上執(zhí)行指定的直接傳遞查詢，返回查詢的結(jié)果集。（5）OPENROWSET函數(shù)。該函數(shù)與OPENQUERY函數(shù)功能相同，只是語法格式不同。（6）OPENXML函數(shù)。OPENXML通過XML文檔提供行集視圖。

4.2.3FROM子句3．user_defined_functionuser_defined_function是表值函數(shù)，所謂表值函數(shù)就是返回一個表的用戶自定義函數(shù)，有關(guān)用戶自定義函數(shù)的內(nèi)容將在第5章中介紹。4．derived_table子查詢可以用在FROM子句中，derived_table表示由子查詢中SELECT語句的執(zhí)行而返回的表，但必須使用AS關(guān)鍵字為子查詢產(chǎn)生的中間表定義一個別名?！纠?.38】從XSB表中查找總學(xué)分大于50的男同學(xué)的姓名和學(xué)號。SELECT姓名,學(xué)號,總學(xué)分FROM(SELECT姓名,學(xué)號,性別,總學(xué)分 FROMXSB WHERE總學(xué)分>50 )ASSTUDENTWHERE性別=1;4.2.3FROM子句【例4.39】在XSB表中查找1990年1月1日以前出生的學(xué)生的姓名和專業(yè)，分別使用別名stu_name和speciality表示。(若要為列制定別名，必須為所有列指定別名)SELECTm.stu_name,m.speciality FROM(SELECT*FROMXSBWHERE出生時間<'19900101') ASm(num,stu_name,sex,birthday,speciality,score,mem)

執(zhí)行結(jié)果如下：4.2.3FROM子句5．pivoted_table和unpivoted_table<pivoted_table>的格式如下：<pivoted_table>::=

table_sourcePIVOT<pivot_clause>[AS]table_alias其中，<pivot_clause>::=(aggregate_function(value_column)FORpivot_columnIN(<column_list>))4.2.3FROM子句【例4.40】查找XSB表中1990年1月1日以前出生的學(xué)生的姓名和總學(xué)分，并列出其屬于計算機專業(yè)還是通信工程專業(yè)的情況，1表示是，0表示否。SELECT姓名,總學(xué)分,計算機,通信工程 FROMXSB PIVOT ( COUNT(學(xué)號) FOR專業(yè) IN(計算機,通信工程) )ASpvt WHERE出生時間<'1990-01-01'4.2.3FROM子句執(zhí)行結(jié)果如下：<unpivoted_table>格式如下：<unpivoted_table>::=

table_sourceUNPIVOT<unpivot_clause>table_alias其中，<unpivot_clause>::=(value_columnFORpivot_columnIN(<column_list>))USEAdventureWorksGOSELECTVendorID,[164]ASEmp1,[198]ASEmp2,[223]ASEmp3,[231]ASEmp4,[233]ASEmp5FROM(SELECTPurchaseOrderID,EmployeeID,VendorIDFROMPurchasing.PurchaseOrderHeader)ASpPIVOT(COUNT(