SQL Server教程（第6版）(2008版) 課件 第4章 數(shù)據(jù)庫的查詢和視圖

第4章

數(shù)據(jù)庫的查詢和視圖——關系運算關系運算1．選擇選擇運算的記號為

F(R)。其中，

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

F(T)上式中F為T1<20，該選擇運算的結果如表。T1T2T3T4T5

T1T2T3T4T51A133M

1A133M2B120N

2B120N3A21212O

3A21212O5D1024P

5D1024P20F14Q

100A328N

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

T1,T2,T5(T)該運算得到如表所示的新表。T1T2T5T1T2T51A1M5DP2B1N20FQ3A20100A3N關系運算3．連接連接是把兩個表中的行按照指定的條件進行拼接而形成新表，記為。其中，R、S是被操作的表，F(xiàn)是條件。例如，若表A和表B分別如表1和表2所示，則如表3所示，其中F為T1=T3。T1T2T1T2T1T21A6F2BT3T4T5T3T4T513M20NT1T2T3T4T51A13M2B20N表1A表表2B表表3關系運算數(shù)據(jù)庫應用中最常用的是“自然連接”。進行自然連接運算要求兩個表有共同屬性（列），自然連接運算的結果表是在參與操作的兩個表的共同屬性上進行等值連接后，再去除重復的屬性后所得的新表。自然連接運算記為，其中R和S是參與運算的兩個表。例如，若表A和B分別如表1和表2所示，則

如表3所示。T1T2T3T1T2T3T1T2T310A1B15A1C220D2C2T1T4T5T6T1T4T5T61100A1D1200A2D11002B2C1510A2C2T1T2T3T4T5T65A1C210A2C220D2C20A2D1表1A表表2B表表3第4章

數(shù)據(jù)庫的查詢和視圖——數(shù)據(jù)庫的查詢數(shù)據(jù)庫的查詢當用戶登錄到SQLServer后，即被指定一個默認數(shù)據(jù)庫，通常是master數(shù)據(jù)庫。使用“USE數(shù)據(jù)庫名”可以選擇當前要操作的數(shù)據(jù)庫。例如，要選擇xscj為當前數(shù)據(jù)庫，可以使用如下語句。USExscj一旦選擇了當前數(shù)據(jù)庫后，若不對操作的數(shù)據(jù)庫對象加以限定，則其后的命令均是針對當前數(shù)據(jù)庫中的表或視圖等進行的。下面介紹SELECT語句，它是T-SQL的核心。其語法格式如下。SELECT列名,…[INTO新表] /*（1）*/ FROM表名|視圖名 /*（2）*/ [WHERE條件] /*（3）*/ [GROUPBY條件] /*（4）*/ [HAVING 條件] /*（5）*/ [ORDERBY…] /*（6）*/說明：（1）指定查詢結果所要輸出的列，還可以用INTO指定將查詢結果存入新表。（2）指定查詢源：表或視圖。（3）指定查詢條件。（4）指定查詢結果分組條件。（5）指定查詢結果分組統(tǒng)計條件。（6）指定查詢結果排序順序。01選擇輸出項選擇指定的列替換查詢結果中的數(shù)據(jù)計算列值消除結果集中的重復行限制結果集返回行數(shù)聚合函數(shù)選擇輸出項1．選擇指定的列SELECT*|列名[AS]別名|表達式[AS]別名，...說明：（1）選擇表中的部分列，各列名之間要以逗號分隔，“*”表示所有列。（2）用AS子句可更改查詢結果的列標題，當自定義的列標題中含有空格時，必須使用引號將標題括起來。不允許在WHERE子句中使用列別名?！纠?.1】分別查詢學生表計算機專業(yè)所有記錄和通信工程專業(yè)中學生的學號姓名和總學分。在SSMS窗口主菜單下面的工具欄，單擊“新建查詢”按鈕，系統(tǒng)新建一個文本編輯窗口，在其中輸入下列T-SQL語句：USExscj;SELECT*FROMxsbWHERE專業(yè)='計算機'; /*(a)*/SELECT學號ASID,姓名ASNAME,總學分AS'Totalcredit’ /*(b)*/ FROMxsb WHERE專業(yè)='通信工程';選擇輸出項單擊工具欄“！執(zhí)行”按鈕”，查詢結果如圖。

選擇輸出項2．替換查詢結果中的數(shù)據(jù)語句如下：CASEWHEN條件1THEN表達式1WHEN條件2THEN表達式2……ELSE表達式nEND選擇輸出項【例4.2】把成績表百分制成績分成等級。USExscj;SELECT學號,成績, CASE WHEN成績>=90THEN'優(yōu)秀' WHEN成績>=80AND成績<90THEN'良好' WHEN成績>=70AND成績<80THEN'中等' WHEN成績>=60AND成績<70THEN'及格' ELSE'不及格' ENDAS等級 FROMcjb WHERE課程號='206';查詢結果如圖。選擇輸出項3．計算列值在結果中可以輸出對包含列在內(nèi)的表達式計算后的值，即可使用表達式作為輸出項?！纠?.3】查詢2022年入學學生年齡。SELECT學號,姓名,出生日期,year(getdate())-year(出生日期)+1AS年齡 FROMxsb WHEREleft(學號,2)='22'查詢結果如圖。選擇輸出項4．消除結果集中的重復行對表查詢輸出部分列時，可能會出現(xiàn)重復行。可以使用DISTINCT或DISTINCTROW關鍵字消除結果集中的重復行?！纠?.4】查詢學生表包含的專業(yè)和成績表已有課程號。語句如下：SELECTDISTINCT專業(yè) FROMxsbSELECTDISTINCT課程號 FROMcjb查詢結果如圖。

選擇輸出項5．限制結果集返回行數(shù)如果SELECT語句返回的結果集的行數(shù)非常多，那么可以使用TOP選項限制其返回的行數(shù)。TOP選項的語法格式如下。TOP表達式[PERCENT]指示只能從查詢結果集返回指定數(shù)目的行或指定百分比數(shù)目的行?！氨磉_式”可以是指定數(shù)目或百分比數(shù)。若帶PERCENT關鍵字，則表示返回結果集的前（表達式值）％行。【例4.5】對xscj數(shù)據(jù)庫的xsb表選擇姓名、專業(yè)和總學分，返回結果集的前6行。語句如下：SELECTTOP6姓名,專業(yè),總學分 FROMxsb選擇輸出項6．聚合函數(shù)聚合函數(shù)常常用于對一組值進行計算，然后返回單個值。聚合函數(shù)通常與GROUPBY子句一起使用。如果一個SELECT語句中有一個GROUPBY子句，則這個聚合函數(shù)對所有列起作用，如果沒有，則SELECT語句只產(chǎn)生一行作為結果。SQLServer所提供的聚合函數(shù)列于表中。函

數(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.6】求選修101號課程的學生人數(shù)、最高分、最低分和平均分，并統(tǒng)計學生總數(shù)和專業(yè)個數(shù)。語句如下：SELECT'計算機導論',COUNT(成績)人數(shù),MAX(成績)最高分,MIN(成績)最低分,AVG(成績)平均分 FROMcjb WHERE課程號='101' /*(a)*/SELECTCOUNT(*)AS'學生總數(shù)',COUNT(DISTINCT專業(yè))AS'專業(yè)個數(shù)' FROMxsb /*(b)*/查詢結果如圖。

02查詢條件表達式比較模式匹配范圍比較空值比較EXCEPT和INTERSECT全文索引查詢條件1．表達式比較語法格式如下：表達式1比較運算符表達式2說明：（1）“表達式”是除text、ntext和image以外類型的表達式。比較運算符共有9個。（2）當兩個表達式的值均不為空值（NULL）時，比較運算返回邏輯值TRUE（真）或FALSE（假）。而當兩個表達式的值中有一個為空值或都為空值時，比較運算將返回UNKNOWN?！纠?.7】查詢xsb表中通信工程專業(yè)總學分大于等于42的同學的情況。語句如下：SELECT* FROMxsb WHERE專業(yè)='通信工程'AND總學分>=42查詢結果如圖。查詢條件2．模式匹配LIKE謂詞用于指定字符串與指定的字符串相匹配，返回邏輯值TRUE或FALSE。LIKE謂詞表達式的語法格式如下。表達式[NOT]LIKE模式串[ESCAPE轉(zhuǎn)義符]說明：（1）表達式：一般為字符串表達式，在查詢語句中可以是列名。（2）NOTLIKE：使用NOTLIKE與LIKE的作用相反。（3）模式串：可以使用通配符，表列出了LIKE謂詞可以使用的通配符及其說明。通

配

符說

明%代表倒數(shù)_（下劃線）代表單個字符[]指定范圍（如[a-f]、[0-9]）或集合（如[abcdef]）中的任何單個字符[^]指定不屬于范圍（如[^a-f]、[^0-9]）或集合（如[^abcdef]）的任何單個字符查詢條件（4）轉(zhuǎn)義符：應為有效的SQLServer字符，沒有默認值，且必須為單個字符。當模式串中含有與通配符相同的字符時，應通過該字符前的轉(zhuǎn)義符指明其為模式串中的一個匹配字符。使用ESCAPE可指定轉(zhuǎn)義符?！纠?.8】查詢xsb表中姓“王”且單名的學生情況。語句如下：SELECT* FROMxsb WHERE姓名LIKE'王_'查詢結果如圖。查詢條件【例4.9】查詢xsb表中學號20打頭，倒數(shù)第1個數(shù)在1～5之間的學生學號、姓名及專業(yè)。語句如下：SELECT學號,姓名,專業(yè) FROMxsb WHERE學號LIKE'20%_[1-5]'查詢結果如圖。查詢條件3．范圍比較（1）當要查詢的條件是某個值的范圍時，可以使用BETWEEN關鍵字。BETWEEN關鍵字指出查詢范圍，其語法格式如下。表達式[NOT]BETWEEN表達式1AND表達式2當不使用NOT時，若“表達式”的值在“表達式1”與“表達式2”之間（包括這兩個值），則返回TRUE，否則返回FALSE；當使用NOT時，返回值剛好相反?！纠?.10】查詢xsb表中不在2002年10～12月出生的學生情況。語句如下：SELECT學號,姓名,專業(yè),出生日期 FROMxsb WHERE出生日期NOTBETWEEN'2002-10-1'AND'2002-12-31'查詢結果如圖。查詢條件（2）使用IN關鍵字可以指定一個值表，值表中列出所有可能的值，當與值表中的任一個匹配時，即返回TRUE，否則返回FALSE。使用IN關鍵字指定值表的語法格式如下。表達式IN(表達式,…)【例4.11】查詢xsb表中專業(yè)為“軟件工程”或“通信工程”的學生情況。語句如下：SELECT* FROMxsb WHERE專業(yè)IN('軟件工程','通信工程')該語句與下列語句等價。SELECT* FROMxsb WHERE專業(yè)='軟件工程'OR專業(yè)='通信工程'查詢條件4．空值比較當需要判定一個表達式的值是否為空值時，使用ISNULL關鍵字，其語法格式如下。表達式IS[NOT]NULL當不使用NOT時，若表達式的值為空值，則返回TRUE，否則返回FALSE；當使用NOT時，結果剛好相反?！纠?.12】查詢包括備注內(nèi)容的學生情況。語句如下：SELECT姓名,備注 FROMxsb WHERE備注ISNOTNULL查詢結果如圖。查詢條件5.EXCEPT和INTERSECTEXCEPT和INTERSECT用于比較兩個查詢的結果，返回非重復值。語法格式如下。查詢EXCEPT|INTERSECT查詢說明：（1）使用EXCEPT和INTERSECT比較兩個查詢的規(guī)則和UNION語句一樣。（2）EXCEPT從左邊的查詢中返回右邊查詢沒有找到的所有非重復值。INTERSECT返回INTERSECT左右兩邊查詢都返回的所有非重復值。（3）EXCEPT或INTERSECT返回的結果集的列名與左側的查詢返回的列名相同。（4）ORDERBY子句中的列名或別名必須引用左側查詢返回的列名。查詢條件【例4.13】查找計算機專業(yè)出生日期不大于2004-1-1的學生信息。語句如下。SELECT*FROMxsbWHERE專業(yè)='計算機’ EXCEPT SELECT*FROMxsbWHERE出生日期>'2004-1-1'查詢結果如圖。查詢條件6．全文索引1）CONTAINS謂詞CONTAINS謂詞用于在表中搜索指定的字符串，可以是精確匹配，也可以是模糊匹配，還可以是加權匹配，這樣可以在表中搜索指定的單詞、短語或近義詞等。新建全文索引步驟如下：（1）啟動全文搜索服務這項服務默認是禁用的。在Windows命令行鍵入“SQLServerManager13.msc”按“Enter鍵”，打開“SqlServerConfigurationManager（SQLServer配置管理器）”窗口，選擇啟動全文搜索服務。（2）創(chuàng)建全文目錄在“對象資源管理器”面板中展開數(shù)據(jù)庫“xscj”，選擇“存儲”目錄下的“全文目錄”，右擊，在彈出的快捷菜單中選擇“新建全文目錄”命令，在打開的“新建全文目錄-xscj”窗口中填寫全文目錄名稱，如“fulltext”，單擊“確定”按鈕。查詢條件（3）注冊全文索引表在全文目錄中注冊需要全文索引的表。在“全文目錄”目錄下右擊剛剛新建的全文目錄“fulltext”，在彈出的快捷菜單中選擇“屬性”命令，打開“全文目錄屬性-fulltext”窗口。選擇“表/視圖”選項卡，選擇需要全文索引的表，如“dbo.xsb”，單擊按鈕，在“合格列”選項組中會顯示能夠進行全文索引的列，勾選需要的列（這里勾選“姓名”和“備注”）復選框，單擊“確定”按鈕。在全文目錄中注冊了需要全文索引的表后，即可使用CONTAINS謂詞對表進行包含式查詢。CONTAINS謂詞語法格式如下：CONTAINS(列名|*,'包含查詢條件')查詢條件說明：（1）“列名”表示在指定的列中搜索，可以指定多個列，類型為char、varchar、nchar、nvarchar、text、ntext、image、xml和varbinary(MAX)的列是可進行全文搜索的有效列；“*”表示在所有列中搜索。（2）包含查詢條件指定要在列中搜索的文本和匹配條件?！纠?.14】使用CONTAINS謂詞搜索xsb表中包含字符“計算機”的所有行。語句如下：SELECT* FROMxsb WHERECONTAINS(*,'計算機')查詢條件2）FREETEXT謂詞與CONTAINS謂詞類似，F(xiàn)REETEXT謂詞也用于在一個表中搜索單詞或短語，并要求表已建立全文索引。FREETEXT的查詢精度沒有CONTAINS高，并不要求對它們進行嚴格的模式匹配。FREETEXT對所查詢的串也沒有寫法要求，因此FREETEXT也稱為自由式查詢。語法格式如下：FREETEXT(列名|列表|*,'搜索字符串')【例4.15】使用FREETEXT謂詞搜索xsb表中包含字符“琳”的所有行。語句如下：SELECT* FROMxsb WHEREFREETEXT(*,'琳')03子

查

詢IN子查詢比較子查詢EXISTS子查詢其他子句使用子查詢子查詢1．IN子查詢IN子查詢用于進行一個指定值是否在子查詢結果集中的判斷。語法格式如下：表達式[NOT]IN(子查詢)說明：（1）當表達式與子查詢的結果表中的某個值相等時，IN謂詞返回TRUE，否則返回FALSE；若使用了NOT，則返回的值剛好相反。（2）IN和NOTIN子查詢只能返回一列數(shù)據(jù)。對于較復雜的查詢，可以使用嵌套的子查詢。子查詢【例4.16】查找未選修離散數(shù)學的學生情況。語句如下：SELECT*FROMxsb /*(c)*/ WHERE學號NOTIN ( SELECT學號 /*(b)*/ FROMcjb WHERE課程號IN ( SELECT課程號 /*(a)*/ FROMkcb WHERE課程名='離散數(shù)學' ) )子查詢2．比較子查詢這種子查詢可以認為是IN子查詢的擴展，它使表達式的值與子查詢的結果進行比較運算，其語法格式如下。表達式比較運算符[ALL|SOME|ANY](子查詢)其中，ALL、SOME和ANY說明對比較運算的限制?！纠?.17】查找比所有計算機專業(yè)的學生年齡都大的學生。語句如下：SELECT* FROMxsb WHERE出生日期<=ALL ( SELECT出生日期 FROMxsb WHERE專業(yè)='計算機' )查詢結果如圖。子查詢【例4.18】查找206號課程成績不低于101號課程最低成績的學生學號和姓名。語句如下：SELECT學號,姓名 /*(c)*/ FROMxsb WHERE學號IN ( SELECT學號 /*(b)*/ FROMcjb WHERE課程號='206'AND成績!<ANY ( SELECT成績 /*(a)*/ FROMcjb WHERE課程號='101' ) )查詢結果如圖。子查詢3．EXISTS子查詢EXISTS謂詞用于測試子查詢的結果是否為空表。語法格式如下：[NOT]EXISTS(子查詢)若子查詢的結果集不為空，則EXISTS返回TRUE，否則返回FALSE。NOTEXISTS返回值與EXISTS剛好相反?！纠?.19】查找選修206號課程的學生姓名。語句如下：SELECT學號,姓名

FROMxsb WHEREEXISTS ( SELECT* FROMcjb WHERE學號=xsb.學號AND課程號='206' )子查詢【例4.20】查找選修了全部課程的同學的姓名。語句如下：SELECT姓名 FROMxsb WHERENOTEXISTS ( SELECT* FROMkcb WHERENOTEXISTS ( SELECT* FROMcjb WHERE學號=xsb.學號AND課程號=kcb.課程號 ) )子查詢4.其他子句使用子查詢子查詢還可以用在SELECT語句的其他子句中，如輸出項、FROM子句?！纠?.21】從xsb表中查找所有女學生的學號、姓名及其與“221101”號學生的年齡差距。語句如下：SELECT學號,姓名,YEAR(出生日期)-YEAR( ( SELECT出生日期 FROMxsb WHERE學號='221101' ))AS年齡差 FROMxsb WHERE性別=0查詢結果如圖。04指定查詢對象表或視圖名導出表行集函數(shù)表值函數(shù)行轉(zhuǎn)列表和列轉(zhuǎn)行表指定查詢對象1．表或視圖名表或視圖名指定SELECT語句要查詢的表或視圖，表和視圖可以是一個或多個。在查詢對象包含多表時，引用同名列名需要加“表名”作為前綴。如果表用AS指定別名，則只能采用“別名”作為前綴?！纠?.22】查找“221101”號學生計算機導論課的成績。語句如下：SELECT成績 FROMcjb,kcb WHEREcjb.課程號=kcb.課程號 AND學號='221101'AND課程名='計算機導論'查詢結果為80。指定查詢對象【例4.23】查找與221101所選修的全部課程相同的同學學號。語句如下：SELECTDISTINCT學號 FROMcjbASCJ1 WHERENOTEXISTS ( SELECT* FROMcjbASCJ2 WHERECJ2.學號='221101'ANDNOTEXISTS ( SELECT* FROMcjbASCJ3 WHERECJ3.學號=CJ1.學號 ANDCJ3.課程號=CJ2.課程號 ) )指定查詢對象2．導出表導出表是由子查詢中SELECT語句的執(zhí)行而返回的表，但必須使用AS關鍵字作為子查詢產(chǎn)生的中間表定義一個別名。【例4.24】從xsb表中查找總學分大于45的男同學的姓名和學號。語句如下：SELECT姓名,學號,總學分 FROM ( SELECT姓名,學號,性別,總學分 FROMxsb WHERE總學分>=45 )ASstudent WHERE性別=1查詢結果如圖。指定查詢對象【例4.25】在xsb表中查找2003年1月1日以前出生的學生學號、姓名、專業(yè)、出生日期和總學分。語句如下：SELECTnum,name,speciality,birthday,score FROM ( SELECT學號,姓名,出生日期,專業(yè),總學分FROMxsbWHERE出生日期<'20030101' )ASstudent(num,name,birthday,speciality,score)查詢結果如圖。指定查詢對象3．行集函數(shù)行集函數(shù)通常返回一個表或視圖。主要的行集函數(shù)有CONTAINSTABLE、FREETEXTTABLE、OPENDATASOURCE、OPENQUERY、OPENROWSET和OPENXML。另外，OPENROWSET函數(shù)還可以用于插入圖片文件、文本文件、Word文件、Excel文件等內(nèi)容。這里以插入圖片文件為例，具體的操作步驟如下。（1）建立測試表，語句如下。USEtest1CREATETABLEtab1( id int IDENTITY(1,1), name varchar(50), data varbinary(MAX))指定查詢對象（2）使用OPENROWSET函數(shù)將圖片文件導入數(shù)據(jù)庫表列，語句如下。INSERTINTOtab1(name,data) SELECT'picture',BulkColumn FROMOPENROWSET(Bulk'E:\picture.jpg',SINGLE_BLOB)ASBLOB（3）查詢導入數(shù)據(jù)。若上述腳本執(zhí)行成功，則可以通過下述查詢語句來查詢表中插入的數(shù)據(jù)。SELECT*FROMtab1查詢結果如圖。指定查詢對象4．表值函數(shù)所謂表值函數(shù)就是返回一個表的用戶自定義函數(shù)，本書第7章將介紹有關內(nèi)容。5．行轉(zhuǎn)列表和列轉(zhuǎn)行表1）行轉(zhuǎn)列表語法格式如下：表源PIVOT(

聚合函數(shù)名(值列) FOR轉(zhuǎn)換列 IN(列表))AS表別名指定查詢對象【例4.26】查找xsb表中2003年1月1日以前出生的學生的姓名和總學分，并列出其屬于計算機專業(yè)還是通信工程專業(yè)的情況，1表示是，0表示否。語句如下：USExscjSELECT姓名,總學分,計算機,通信工程 FROMxsb PIVOT ( COUNT(學號) FOR專業(yè) IN(計算機,通信工程) )ASpvt WHERE出生日期<'2003-01-01'查詢結果如圖。指定查詢對象【例4.27】將kcb表中的開課學期和學分列轉(zhuǎn)換為行輸出。語句如下：SELECT課程號,課程名,選項,內(nèi)容 FROMkcb UNPIVOT (

內(nèi)容 FOR選項IN(學分,開課學期) )unpvt WHERE課程號>300查詢結果如圖。05表

連

接WHERE子句中連接謂詞FROM子句JOIN連接表連接1．WHERE子句中連接謂詞可以在WHERE子句中使用比較運算符給出連接條件對表進行連接，將這種表示形式稱為連接謂詞表示形式。連接謂詞中的比較符可以是“<”、“<=”、“=”、“>”、“>=”、“!=”、“<>”、“!<”和“!>”，當比較符為“=”時，就是等值連接?！纠?.28】查找xscj數(shù)據(jù)庫中每個學生的情況及選修的課程情況。語句如下：SELECTxsb.*,cjb.* FROMxsb,cjb WHERExsb.學號=cjb.學號結果表將包含xsb表和cjb表的所有列共42個記錄，因為cjb表為42條記錄。表連接1）自然連接它在目標列中去除相同的列名?！纠?.29】通過自然連接進行查詢。語句如下：SELECTxsb.*,課程號,成績 FROMxsb,cjb WHERExsb.學號=cjb.學號顯示xsb表所有列和cjb表課程號和成績列【例4.30】查找選修了206號課程且成績在80分以上的學生姓名及成績。語句如下。SELECTcjb.學號,姓名,成績 FROMxsb,cjb WHERExsb.學號=cjb.學號AND課程號='206'AND成績>=80查詢結果如圖。表連接2）多表連接對兩個以上的表進行連接，稱之為多表連接?！纠?.31】查找選修了“計算機導論”課程且成績在80分以上的學生學號、姓名及成績。語句如下。SELECTxsb.學號,姓名,成績 FROMxsb,kcb,cjb WHERExsb.學號=cjb.學號 ANDkcb.課程號=cjb.課程號 AND課程名='計算機導論' AND成績>=80查詢結果如圖。表連接2．FROM子句JOIN連接T-SQL擴展了FROM子句以JOIN關鍵字的表示方式，可以將多個表連接起來。語法格式如下。FROM表源JOIN連接類型表源ON查詢條件其中：表源為連接的表；ON用于指定連接條件；JOIN連接類型有3種——內(nèi)連接、外連接和交叉連接。1）內(nèi)連接指定了INNER關鍵字的連接是內(nèi)連接，內(nèi)連接按照ON所指定的連接條件合并兩個表，返回滿足條件的行。（1）內(nèi)連接是系統(tǒng)默認的，可以省略INNER關鍵字。【例4.32】查找xscj數(shù)據(jù)庫每個學生的情況及選修的課程情況。語句如下：SELECT* FROMxsbJOINcjb ONxsb.學號=cjb.學號表連接（2）使用內(nèi)連接后仍可使用WHERE子句指定條件。【例4.33】查找選修了206號課程且成績在80分以上的學生姓名及成績。語句如下：SELECT姓名,成績 FROMxsbJOINcjb ONxsb.學號=cjb.學號 WHERE課程號='206'AND成績>=80表連接（3）內(nèi)連接用于多個表的連接?！纠?.34】查找選修了“計算機導論”課程且成績在80分以上的學生學號、姓名及成績。語句如下：SELECT xsb.學號,姓名,成績 FROMxsbJOINcjbJOINkcb ONcjb.課程號=kcb.課程號 ONxsb.學號=cjb.學號 WHERE課程名='計算機導論'AND成績>=80查詢結果如圖。表連接（4）可以將一個表與它自身進行連接，稱為自連接。若要在一個表中查找具有相同列值的行，則可以使用自連接。使用自連接時需為表指定兩個別名，且對所有列均要用別名限定。【例4.35】查找不同課程成績相同的學生的學號、課程號和成績。語句如下。SELECTa.學號,a.課程號,b.課程號,a.成績 FROMcjbaJOINcjbb ONa.成績=b.成績ANDa.學號=b.學號ANDa.課程號!=b.課程號表連接2）外連接指定了OUTER關鍵字的為外連接，外連接的結果表不但包含滿足連接條件的行，還包括相應表中的所有行。外連接包括以下3種。

左外連接（LEFTOUTERJOIN）：結果表中除了包括滿足連接條件的行外，還包括左表的所有行。

右外連接（RIGHTOUTERJOIN）：結果表中除了包括滿足連接條件的行外，還包括右表的所有行。

完全外連接（FULLOUTERJOIN）：結果表中除了包括滿足連接條件的行外，還包括兩個表的所有行?！纠?.36】查找所有學生情況及他們選修的課程號，若學生未選修任何課，也要包括其情況。語句如下：SELECTxsb.*,課程號 FROMxsbLEFTOUTERJOINcjb ONxsb.學號=cjb.學號表連接【例4.37】查找課程的選修情況和所有開設的課程名。語句如下：SELECTcjb.*,課程名 FROMcjbRIGHTJOINkcb ONcjb.課程號=kcb.課程號本例執(zhí)行時，若某課程未被選修，則結果表中相應行的學號、課程號和成績列值均為NULL。顯示的后面部分記錄如圖。表連接3）交叉連接CROSSJOIN表示交叉連接，交叉連接實際上是將兩個表進行笛卡兒積運算，結果表是由第一個表的每一行與第二個表的每一行拼接后形成的表，因此結果表的行數(shù)等于兩個表的行數(shù)之積。【例4.38】列出學生所有可能的選課情況。語句如下：SELECT學號,姓名,課程號,課程名 FROMxsbCROSSJOINkcb交叉連接也可以使用ON子句進行條件限定。06查詢結果分組查詢結果分組GROUPBY子句有ISO標準和非ISO標準兩種語法格式可用。這里介紹ISO標準的GROUPBY子句。語法格式如下。GROUPBY

列表達式 /*（a）*/ |ROLLUP(復合元素列表) /*（b）*/ |CUBE(復合元素列表) /*（c）*/ |GROUPINGSETS(分組集合項列表) /*（d）*/說明：（a）列表達式：指定分組的列名及由其列名和運算符構成的表達式。（b）ROLLUP：生成簡單的聚合行、小計行或超聚合行，還生成一個總計行，返回的分組數(shù)等于“復合元素列表”中的表達式數(shù)+1。（c）CUBE：生成簡單的聚合行、超聚合行和交叉表格行。CUBE針對“復合元素列表”中表達式的所有排列輸出一個分組。（d）GROUPINGSETS：在一個查詢中指定數(shù)據(jù)的多個分組。僅聚合指定組，而不聚合由CUBE或ROLLUP生成的整組聚合。查詢結果分組【例4.39】生成一個結果集，包括每個專業(yè)的男生人數(shù)、女生人數(shù)、總人數(shù)及學生總人數(shù)。語句如下。SELECT專業(yè),性別,COUNT(*)AS'人數(shù)' FROMxsb GROUPBYROLLUP(專業(yè),性別)查詢結果如圖。查詢結果分組【例4.40】生成一個結果集，包括每個專業(yè)的男生人數(shù)、女生人數(shù)、男生總數(shù)、女生總數(shù)和學生總人數(shù)。語句如下。SELECT專業(yè),性別,COUNT(*)AS'人數(shù)' FROMxsb GROUPBYCUBE(專業(yè),性別)查詢結果如圖。查詢結果分組【例4.41】生成一個結果集，分別根據(jù)專業(yè)和性別對人數(shù)進行聚合。語句如下。SELECT專業(yè),性別,COUNT(*)AS'人數(shù)' FROMxsb GROUPBYGROUPINGSETS(專業(yè),性別)查詢結果如圖。07指定分組篩選條件指定分組篩選條件使用GROUPBY子句和聚合函數(shù)對數(shù)據(jù)進行分組后，還可以使用HAVING子句對分組數(shù)據(jù)進一步篩選。語法格式如下。HAVING條件其中，HAVING子句中條件可以使用聚合函數(shù)，而WHERE子句中不可以?！纠?.42】查找平均成績在85分以上的學生的學號和平均成績。語句如下。SELECT學號,AVG(成績)AS'平均成績' FROMcjb GROUPBY學號 HAVINGAVG(成績)>=85查詢結果如圖。指定分組篩選條件【例4.43】查找選修超過兩門課程且成績都在70分以上的學生的學號。語句如下：SELECT學號 FROMcjb WHERE成績>=70 GROUPBY學號 HAVINGCOUNT(*)>2查詢結果如圖。指定分組篩選條件【例4.44】查找通信工程專業(yè)平均成績在85分以上的學生的學號和平均成績。語句如下：SELECT學號,AVG(成績)AS'平均成績’ FROMcjb WHERE學號IN /*（a）*/ ( SELECT學號 FROMxsb WHERE專業(yè)='通信工程' ) GROUPBY學號 /*（b）*/ HAVINGAVG(成績)>=85 /*（c）*/查詢結果如圖。08指定輸出順序指定輸出順序使用ORDERBY子句對查詢結果進行排序。語法格式如下。ORDERBY排序表達式[ASC|DESC]其中，“排序表達式”是列名和包含列名由運算符構成的表達式；關鍵字ASC表示升序排列，DESC表示降序排列，系統(tǒng)默認值為ASC?！纠?.45】將出生日期2003年后學生先按照專業(yè)名拼音排序，相同專業(yè)按照出生日期從小到大排序。語句如下：SELECT學號,姓名,專業(yè),出生日期 FROMxsb WHERE出生日期>='2004-1-1' ORDERBY專業(yè)DESC,出生日期查詢結果如圖。指定輸出順序【例4.46】將計算機專業(yè)學生的“計算機導論”課程成績按降序排列。語句如下：SELECTxsb.學號,姓名,成績 FROMxsb,kcb,cjb WHERExsb.學號=cjb.學號

ANDcjb.課程號=kcb.課程號 AND課程名='計算機導論' AND專業(yè)='計算機' ORDERBY成績DESC查詢結果如圖。09指定查詢結果目的地生成子表復制表結構指定查詢結果目的地SELECT查詢所得的結果可保存到一個新建的表中。語法格式如下。INTO新表1.生成子表【例4.47】分別生成“計算機”和“通信過程”專業(yè)學生子表。語句如下。USExscjSELECT*INTOxsb_jsj FROMxsb WHERE專業(yè)='計算機'SELECT*INTOxsb_txgc FROMxsb WHERE專業(yè)='通信工程'指定查詢結果目的地2.復制表結構【例4.48】在test2數(shù)據(jù)庫中生成xsb空表。語句如下。USExscj SELECT*INTOtest2.dbo.xsb FROMxsbWHERE1=210表

聯(lián)

合表聯(lián)合常用于歸檔數(shù)據(jù)，如歸檔月報表形成年報表、歸檔各部門數(shù)據(jù)等。另外，UNION還可以與GROUPBY及ORDERBY一起使用，用來對合并所得的結果表進行分組或排序。語法格式如下。查詢UNION[ALL]查詢UNION[ALL]...[GROUPBY...][ORDERBY...]說明：（1）所有查詢的列名、個數(shù)、順序必須相同，數(shù)據(jù)類型必須相同或者兼容。（2）關鍵字ALL表示合并的結果中包括所有行，不去除重復行。（3）若不指定INTO子句，結果將合并到第一個表中。表聯(lián)合【例4.49】查詢“計算機”表和“通信工程”表中女同學，按出生日期從小到大排序。語句如下。USExscjSELECT* FROMxsb_jsj WHERE性別=0 UNIONALL SELECT* FROMxsb_txgc WHERE性別=0 ORDERBY出生日期查詢結果如圖。11公用表表達式（CTE）公用表表達式（CTE）在SELECT語句的最前面可以使用一條WITH子句來指定臨時結果集，這種臨時命名的結果集也稱為公用表表達式（CommonTableExpression，CTE），其相當于一個臨時表，只不過它的生命周期在該批處理語句執(zhí)行完后就結束了。語法格式如下。WITHCTE名(列名,…) AS(CTE查詢定義) SELECT…說明：“列名”與“CTE查詢定義”返回的列名稱和個數(shù)相同。若不定義，則就是查詢列名稱。SELECT語句可以直接查詢CTE臨時表數(shù)據(jù)。公用表表達式（CTE）【例4.50】使用CTE從cjb表中查詢選了101號課程的學生學號、成績，并定義新的列名為num、score。再使用SELECT語句從CTE和xsb表中查詢姓名為“王林”的學生學號和成績情況。語句如下：USExscj;WITHcte_stu(num,score) AS(SELECT學號,成績FROMcjbWHERE課程號='101') SELECTnum,score FROMcte_stu,xsb WHERExsb.姓名='王林'ANDxsb.學號=cte_stu.num查詢結果如圖。第4章

數(shù)據(jù)庫的查詢和視圖——視圖視圖使用視圖有下列優(yōu)點。（1）為用戶集中數(shù)據(jù)，簡化用戶的數(shù)據(jù)查詢和處理。有時用戶所需要的數(shù)據(jù)分散在多個表中，定義視圖可將分散在多個表中的數(shù)據(jù)集中在一起，從而方便用戶進行數(shù)據(jù)查詢和處理。（2）屏蔽數(shù)據(jù)庫的復雜性。用戶不必了解復雜的數(shù)據(jù)庫中的表結構，并且表結構非列名修改也不影響用戶對數(shù)據(jù)庫的使用。（3）簡化用戶權限的管理。只需授予用戶使用視圖的權限，而不必指定用戶只能使用表的特定列，也增加了安全性。（4）便于數(shù)據(jù)共享。各用戶不必都定義和存儲自己所需的數(shù)據(jù)，即可共享數(shù)據(jù)庫的表數(shù)據(jù)，這樣，同樣的數(shù)據(jù)只需存儲一次。（5）可以重新組織數(shù)據(jù)以便輸出到其他應用程序中。01創(chuàng)建視圖創(chuàng)建視圖分區(qū)視圖界面創(chuàng)建視圖創(chuàng)建視圖1．創(chuàng)建視圖語法格式如下。CREATEVIEW視圖名[(列名,…)][WITH視圖屬性] AS SELECT語句 [WITHCHECKOPTION]說明：（1）(列名,…)：它是視圖中包含的列，若使用與源表相同的列名，則不必給出列名表。（2）WITH視圖屬性：指出視圖的屬性。（3）SELECT語句：用來創(chuàng)建視圖的SELECT語句，但定義視圖的用戶必須對源表有執(zhí)行SELECT語句的權限。（4）WITHCHECKOPTION：指出在視圖上所進行的修改都要符合SELECT語句所指定的限制條件，這樣可以確保數(shù)據(jù)修改后，仍可通過視圖看到修改的數(shù)據(jù)。（5）創(chuàng)建視圖的用戶已被數(shù)據(jù)庫所有者授權可以使用CREATEVIEW語句，并且有權操作視圖所涉及的表或其他視圖。創(chuàng)建視圖【例4.51】創(chuàng)建學生的學號、姓名和備注視圖xsv。USExscjGOCREATEVIEWxsvWITHENCRYPTIONAS SELECTxsb.學號,姓名,備注 FROMxsb WITHCHECKOPTIONGO說明：（1）命令方式創(chuàng)建視圖語句必須是批處理中的第1個命令。（2）視圖創(chuàng)建后，在對象資源管理器中，xscj數(shù)據(jù)庫視圖下就會顯示xsv。創(chuàng)建視圖【例4.52】創(chuàng)建計算機學生的學號、姓名、課程號及成績視圖cj_jsjv。語句如下：USExscjGOCREATEVIEWcj_jsjvWITHENCRYPTIONAS SELECTxsb.學號,姓名,課程號,成績 FROMxsb,cjb WHERExsb.學號=cjb.學號AND專業(yè)='計算機'GO創(chuàng)建視圖【例4.53】創(chuàng)建計算機專業(yè)學生的平均成績視圖cj_jsj_avgv，包括學號（列名為num）和平均成績（列名為score_avg）。語句如下：CREATEVIEWcj_jsj_avgv(num,score_avg)AS SELECT學號,AVG(成績) FROMcj_jsjv GROUPBY學號GO創(chuàng)建視圖顯示cj_jsjv和cj_jsj_avgv視圖中的內(nèi)容，語句如下，查詢結果如圖。SELECTTOP7學號,姓名,課程號,成績 FROMcj_jsjvSELECT*FROMcj_jsj_avgv

創(chuàng)建視圖2．分區(qū)視圖分區(qū)視圖在一臺或多臺服務器間水平連接一組成員表中的分區(qū)數(shù)據(jù)，使數(shù)據(jù)看起來就像來自同一個表。SQLServer可區(qū)分本地分區(qū)視圖和分布式分區(qū)視圖。在本地分區(qū)視圖中，所有參與的表和視圖都位于同一個SQLServer實例上；在分布式分區(qū)視圖中，至少有一個參與表位于不同的（遠程）服務器上。創(chuàng)建視圖的語句的格式如下。CREATEVIEW視圖名 AS SELECT列表1 FROM源表1 UNIONALL SELECT列表2 FROM源表2 UNIONALL … SELECT列表n FROM源表n創(chuàng)建視圖3．界面創(chuàng)建視圖在xscj數(shù)據(jù)庫中創(chuàng)建學生成績大于等于60的視圖，包含學號、姓名、專業(yè)、總學分、課程名和成績列。記錄順序先按課程號從小到大，課程號相同按成績從大到小排列。操作步驟如下。（1）在對象資源管理器xscj下選擇“視圖”，右擊，在彈出的快捷菜單中選擇“新建視圖”菜單。（2）在彈出的“添加表”對話框“表”選項卡中選擇構建視圖的基表（例如：cjb、kcb和xsb），單擊“添加”按鈕，如圖。創(chuàng)建視圖（3）單擊“關閉”按鈕關閉該對話框，系統(tǒng)在“查詢分析器”中創(chuàng)建一個圖形可視化選項卡，表之間相同列名自動建立了關聯(lián)線。（4）在表中列名前勾選視圖使用的列，下面對應表格生成相應的記錄，表格欄目是描述對應列在視圖中的屬性。按照要求勾選列對應輸出；選擇排序類型+排序順序；在篩選器中描述條件，視圖的整體條件由不會空的篩選器條件通過“與構成”，如果是“或”的關系，需要寫在“或…”欄中。如圖。創(chuàng)建視圖（5）當視圖中需要一個與原列名不同的列名，或視圖中包含了計算列時，可以在“別名”選項中指定。完成后，單擊面板上的“保存”按鈕。彈出“保存視圖”對話框，在其中輸入視圖名“cj_kc_xsv1”，并單擊“確定”按鈕，便完成了視圖的創(chuàng)建。（6）展開數(shù)據(jù)庫→xscj→視圖下，選擇“cj_kc_xsv1”選項，右擊，在彈出的快捷菜單中選擇“設計”命令，可查看并修改視圖，選擇“編輯前200行”命令，可查看視圖數(shù)據(jù)內(nèi)容。如圖。02查詢視圖查詢視圖【例4.54】查找平均成績在80分以上的學生的學號和平均成績。前面已經(jīng)創(chuàng)建計算機專業(yè)學生平均成績視圖cj_jsj_avgv，包括學號（列名為num）和平均成績（列名為score_avg）。這里對cj_jsj_avgv視圖進行查詢。SQL語句如下。USExscjSELECT* FROMcj_jsj_avgv WHEREscore_avg>=8003更新視圖插入數(shù)據(jù)修改數(shù)據(jù)刪除數(shù)據(jù)更新視圖創(chuàng)建可更新視圖需要注意以下幾點。（1）創(chuàng)建視圖的SELECT語句中沒有聚合函數(shù)、TOP、GROUPBY、UNION子句及DISTINCT關鍵字；不包含從基表列通過計算所得的列；FROM子句中至少要包含一個基表。（2）更新視圖語句不能同時影響多個基表，并且在創(chuàng)建視圖包含“WITHCHECKOPTION”項時需要符合對應的條件。（3）對于可更新的分區(qū)視圖，在實現(xiàn)分區(qū)視圖之前，必須先實現(xiàn)水平分區(qū)表。原始表被分成若干個較小的成員表，每個成員表包含與原始表相同數(shù)量的列，并且每一列具有與原始表中的相應列同樣的特性（如數(shù)據(jù)類型、大小、排序規(guī)則）。（4）通過INSTEADOF觸發(fā)器創(chuàng)建的可更新視圖。更新視圖1．插入數(shù)據(jù)使用INSERT語句通過視圖向基表插入數(shù)據(jù)?！纠?.55】向cj_jsjv視圖中插入“221191,林時”記錄。語句如下：USExscjINSERTINTOcj_jsjv (學號,姓名)VALUES('221191','林時')SELECT* /*(a)*/ FROMcj_jsjv WHERE學號='221191'SELECT* /*(b)*/ FROMxsb WHERE學號='221191'查詢結果如圖。

更新視圖說明：（1）視圖由xsb表和cjb表通過學號連接構成，視圖的學號列對應xsb.學號列，所以插入內(nèi)容進入xsb表中而沒有加入cjb表中。所以視圖查詢不到插入的記錄，而xsb表查詢到插入的記錄。（2）采用下列語句對視圖cj_jsjv的更新操作不能成功：INSERTINTOcj_jsjv (學號,姓名,課程號,成績)VALUES('221191','林時時',301,80)執(zhí)行上述語句更新操作會影響xsb和cjb兩個基表。更新視圖2．修改數(shù)據(jù)使用UPDATE語句可以通過視圖修改基表的數(shù)據(jù)?！纠?.56】通過xsv視圖修改學號為221191學生的備注為“從南京工業(yè)大學轉(zhuǎn)入”。語句如下：USExscjUPDATExsv SET備注='從南京工業(yè)大學轉(zhuǎn)入' WHERE學號='221191'SELECT* FROMxsb WHERE學號='221191'查詢結果如圖。更新視圖3．刪除數(shù)據(jù)使用DELETE語句可以通過視圖刪除基表的數(shù)據(jù)。但要注意，對于依賴于多個基表的視圖，不能使用DELETE語句?！纠?.57】刪除xsv視圖中學號為221191學生記錄。語句如下：DELETEFROMxsv WHERE學號='221191'SELECT* FROMxsb WHERE學號='221191'04修改視圖定義修改視圖定義語法格式如下。ALTERVIEW視圖名[WITH視圖屬性] AS SELECT語句 [WITHCHECKOPTION]其中，視圖屬性、SELECT語句、WITHCHECKOPTION等與CREATEVIEW語句中的含義完全相同。05刪除視圖刪除視圖刪除視圖同樣也可以通過“對象資源管理器”面板中的圖形向?qū)Ш蚑-SQL語句兩種方式來實現(xiàn)。選擇指定數(shù)據(jù)庫下視圖項，右擊，在彈出的快捷菜單中選擇“刪除”命令項，彈出“刪除”對話框，單擊“確定”按鈕即可刪除指定的視圖。刪除視圖語法格式如下。DROPVIEW視圖名,…使用DROPVIEW可刪除一個或多個視圖，各待刪的視圖名之間以逗號分隔。第4章

數(shù)據(jù)庫的查詢和視圖——游標01聲明游標SQL-92標準T-SQL擴展游標聲明游標1．SQL-92標準語法格式如下。DECLARE游標名CURSOR FOR SELECT語句 [FORREADONLY|UPDATE[OF列名,…]]說明：（1）游標名：它是與某個查詢結果集相聯(lián)系的符號名，要符合SQLServer標識符的命名規(guī)則。（2）SELECT語句：由該查詢產(chǎn)生與所聲明的游標相關聯(lián)的結果集。該SELECT語句中不能出現(xiàn)COMPUTE、COMPUTEBY、INTO或FORBROWSE關鍵字。（3）READONLY：所聲明的游標為只讀的。（4）UPDATE…：指定游標中可以更新的列。若有參數(shù)“OF列名，…”，則只能修改給出的這些列；若在UPDATE中未指出列，則可以修改所有列。聲明游標【例4.58】定義一個符合SQL-92標準的游標聲明。語句如下。DECLARExs_cur1CURSOR FOR SELECT學號,姓名,性別,出生日期,總學分 FROMxsb WHERE專業(yè)='計算機' FORREADONLYGO說明：（1）該語句定義的游標與單個表的查詢結果集相關聯(lián)，是只讀的，游標只能從頭到尾順序提取數(shù)據(jù)，相當于下面所介紹的只進游標。（2）游標是一個臨時對象，聲明后在SSMS的對象資源管理器中并不存在。聲明游標2．T-SQL擴展游標語法格式如下。DECLARE游標名CURSOR [LOCAL|GLOBAL] /*（a）*/ [FORWARD_ONLY|SCROLL] /*（b）*/ [STATIC|DYNAMIC|FAST_FORWARD|KEYSET] /*（c）*/ [READ_ONLY|SCROLL_LOCKS|OPTIMISTIC] /*（d）*/ FOR SELECT語句 [FORUPDATE[OF列名,…]] /*（e）*/下面對擴展游標進行說明。（a）游標作用域。（b）游標移動方向。（c）游標類