資料庫的查詢與檢視

第4章 資料庫的查詢和檢視

4.1.1 選擇列

通過SELECT語句的<select_list>項組成結果表的列。

<select_list>::=

SELECT [ ALL |DISTINCT ] [ TOP n [ PERCENT ] [ WITH TIES ] ]

{ * /*選擇當前表或檢視的所有列*/

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

| { colume_name |expression | IDENTITYCOL | ROWGUIDCOL }

[ [ AS ]column_alias ] /*選擇指定的列*/

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

} [ , … n ]

1. 選擇一個表中指定的列

一般情況下，希望包含在結果表中的列表清單在SELECT關鍵字之後，就像前面用到的那樣，當在SELECT關鍵字後面指定*時，就表示全部列都包含在結果表中。不指定*，可以列出一個表中的某些列，各列名之間要以逗號分隔。

 

4.1.1 選擇列

【例4.1】查詢XSCJ資料庫的XS表中各個同學的姓名、專業名和總學分。

USE XSCJ

SELECT 姓名,專業名,總學分

FROM XS

 

4.1.1 選擇列

【例4.2】查詢XS表中計算機專業同學的學號、姓名和總學分。查詢XS表中的所有列。

SELECT 學號，姓名，總學分

FROM XS

WHERE 專業名= ‘計算機’

GO

SELECT *

FROM XS

GO

當在SELECT語句指定列的位置上使用*號時，表示選擇表的所有列。

執行後將列出XS表中的所有資料，如圖4.2所示：

 

4.1.1 選擇列

2. 修改查詢結果中的列標題

當希望查詢結果中的某些列或所有列顯示時使用自己選擇的列標題時，可以在列名之後使用AS子句來更改查詢結果的列標題名。其中column_alias是指定的列標題。

【例4.3】查詢XS表中計算機系同學的學號、姓名和總學分，結果中各列的標題 分別指定為number、name和mark。

SELECT 學號 AS number, 姓名 AS name,總學分 AS mark

FROM XS

WHERE 專業名= ‘計算機’

該語句的執行結果如圖4.3所示。

更改查詢結果中的列標題也可以使用column_alias=expression的形式。例如：

SELECT number = 學號, name = 姓名, mark = 總學分

FROM XS

WHERE 專業名= ’計算機’

該語句的執行結果與上例的結果完全相同。

 

4.1.1 選擇列

注意，當自定義的列標題中含有空格時，必須使用引號將標題括起來。例如：

SELECT ‘Student number’ = 學號，姓名 AS ‘Student name’， mark = 總學分

FROM XS

WHERE 專業名= ‘計算機’

 

4.1.1 選擇列

3. 替換查詢結果中的資料

在對錶進行查詢時，有時對所查詢的某些列希望得到的是一種概念而不是具體的資料。例如查詢XS表的總學分，所希望知道的是學習的總體情況，這時，就可以用等級來替換總學分的具體數字。

要替換查詢結果中的資料，則要使用查詢中的CASE表示式，格式為：

CASE

WHEN 條件1 THEN 表示式1

WHEN 條件2 THEN 表示式2

……

ELSE 表示式

END

SQL Server按照順序計算每一個條件，如果找到條件為真的語句，SQL Server就執行TEHN關鍵字後的表示式；否則執行可選的ELSE關鍵字後的語句。若沒有為真的條件，也沒有關鍵字，則CASE表示式返回值為空。

 

4.1.1 選擇列

【例4.4】查詢XS表中計算機系各同學的學號、姓名和總學分，對其總學分按以下規則進行替換：若總學分為空值，替換為“尚未選課”；若總學分小於50，替換為“不及格”；若總學分在50與52之間，替換為“合格”；若總學分大於52，替換為“優秀”。列標題更改為“等級”。

SELECT 學號, 姓名,

等級=

CASE

WHEN 總學分 IS NULL THEN ‘尚未選課’

WHEN 總學分 < 50 THEN ‘不及格’

WHEN 總學分 >=50 and 總學分<=52THEN ‘合格’

ELSE ‘優秀’

END

FROM XS

WHERE 專業名=’計算機’

 

4.1.1 選擇列

4. 計算列值

使用SELECT對列進行查詢時，在結果中可以輸出對列值計算後的值，即SELECT子句可使用表示式作為結果，格式為：

SELECT expression[ , expression ]

【例4.5】按120分計算成績顯示。

SELECT 學號, 課程號,

成績120=成績*1.20

FROM XS_KC

4.1.1 選擇列

計算列值使用算術運算子：+（加）、-（減）、*（乘）、/（除）和%（取餘），其中5種算術運算子（+、-、*、/）可以用於任何數字型別的列，包括：int、smallint、tinyint、decimal、numeric、float、real、money和smallmoney；%可以用於上述除money和smallmoney以外的數字型別。

5. 消除結果集中的重複行

對錶只選擇其某些列時，可能會出現重複行。例如，若對XSCJ資料庫的XS表只選擇專業名和總學分，則出現多行重複的情況。可以使用DISTINCT關鍵字消除結果集中的重複行，其格式是：

SELECT DISTINCTcolumn_name [ , column_name…]

關鍵字DISTINCT的含義是對結果集中的重複行只選擇一個，保證行的唯一性。

【例4.6】對XSCJ資料庫的XS表只選擇專業名和總學分，消除結果集中的重複行。

SELECT DISTINCT 專業名, 總學分

FROM XS

圖4.6消除重複行

與DISTINCT相反，當使用關鍵字ALL時，將保留結果集的所有行。當SELECT語句中預設ALL與DISTINCT時，預設值為ALL。

6. 限制結果集返回行數

如果SELECT語句返回的結果集的行數非常多，可以使用TOP選項限制其返回的行數。TOP選項的基本格式為：

TOP n [ PERCENT ]

其中n是一個正整數，表示返回查詢結果集的前n行。若帶PERCENT關鍵字，則表示返回結果集的前n%行。

 

4.1.1 選擇列

【例4.7】對XSCJ資料庫的XS表選擇姓名、專業名和總學分，只返回結果集的前6行。

SELECT TOP 6 姓名，專業名，總學分

FROM XS

 

4.1.2 選擇行

在SQL Server中，選擇行是通過在SELECT語句中WHERE子句指定選擇的條件來實現的。這一節將詳細討論WHERE子句中查詢條件的構成。WHERE子句必須緊跟FROM子句之後，其基本格式為：

在SQL中，返回邏輯值（TRUE或FALSE）的運算子或關鍵字都可稱為謂詞。

1. 表示式比較

比較運算子用於比較兩個表示式值，共有9個，分別是： =（等於）、<（小於）、<=（小於等於）、>（大於）、>=（大於等於）、<>（不等於）、!=（不等於）、!<（不小於）、!>（不大於）。比較運算的格式為：

expression { = |< | <= | > | >= | <> | != | !< | !> } expression

其中expression是除text、ntext和image外型別的表示式。

當兩個表示式值均不為空值（NULL）時，比較運算返回邏輯值TRUE（真）或FALSE（假）；而當兩個表示式值中有一個為空值或都為空值時，比較運算將返回UNKNOWN。

【例4.8】查詢XSCJ資料庫XS表中通訊工程專業總學分大於等於42的同學的情況。

SELECT *

FROM XS

WHERE專業名 = ‘通訊工程’ and 總學分 >=42

 

4.1.2 選擇行

2. 模式匹配

LIKE謂詞用於指出一個字串是否與指定的字串相匹配，其運算物件可以是char、varchar、text、ntext、datetime和smalldatetime型別的資料，返回邏輯值TRUE或FALSE。LIKE謂詞表示式的格式為：

string_expression[ NOT ] LIKE string_expression [ ESCAPE ‘escape_character’ ]

【例4.9】查詢XSCJ資料庫XS表中計算機系的學生情況。查詢XSCJ資料庫XS表中姓“王”且單名的學生情況。

SELECT *

FROM XS

WHERE 專業名 LIKE ‘計算機’

GO

SELECT *

FROM XS

WHERE 姓名 LIKE ‘王_’

GO

 

4.1.2 選擇行

使用LIKE進行模式匹配時，常使用萬用字元，即可進行模糊查詢。有關萬用字元的格式和含義請見附錄中介紹T-SQL語言的相關說明。

執行結果如圖4.8所示。

若要匹配的內容為萬用字元的字元（包括%、_、[] 、^），可使用關鍵字ESCAPE。以告訴系統其後的每個字元均作為實際匹配的字元，而不再作為萬用字元。

3. 範圍比較

用於範圍比較的關鍵字有兩個：BETWEEN和IN。

當要查詢的條件是某個值的範圍時，可以使用BETWEEN關鍵字。BETWEEN關鍵字指出查詢範圍，格式為：

expression [ NOT ]BETWEEN expression1 AND expression2

 

4.1.2 選擇行

當不使用NOT時，若表示式expression的值在表示式expression1與expression2之間（包括這兩個值），則返回TRUE，否則返回FALSE；使用NOT時，返回值剛好相反。

注意：expression1的值不能大於expression2的值。

使用IN關鍵字可以指定一個值表，值表中列出所有可能的值，當與值表中的任一個匹配時，即返回TRUE，否則返回FALSE。使用IN關鍵字指定值表的格式為：

expression IN (expression [,…n])

【例4.10】查詢XSCJ資料庫XS表中不在1989年出生的學生情況。查詢XSCJ資料庫XS表中專業名為“計算機”或“通訊工程”或“無線電”的學生的情況。

SELECT *

FROM XS

WHERE 出生時間 NOT BETWEEN ‘1989-1-1’ and ‘1989-12-31’

GO

SELECT *

FROM XS

WHERE 專業名 IN (’計算機’, ’通訊工程’, ’無線電’)

GO

 

4.1.2 選擇行

該語句與下列語句等價：

SELECT *

FROM XS

WHERE 專業名 = ’計算機’ or 專業名 = ’通訊工程’ or 專業名 = ’無線電’

說明：IN關鍵字最主要的作用是表達子查詢。

4. 空值比較

如果兩個值當中有一個或者都為空，則對這兩個值的比較結果是未知的。NULL謂詞提供了一種方法，用來測試值為空或者非空。格式為：

expression IS [NOT ] NULL

當不使用NOT時，若表示式expression的值為空值，返回TRUE，否則返回FALSE；當使用NOT時，結果剛好相反。

【例4.11】查詢XSCJ資料庫中總學分尚不定的學生情況。

SELECT *

FROM XS

WHERE 總學分 IS NULL

 

4.1.2 選擇行

5. CONTAINS謂詞

若需要在表中搜尋指定的單詞、短語或近義詞等，可以使用CONTAINS謂詞。CONTAINS謂詞用於在表中搜尋指定的字串，可以是精確匹配，也可以是模糊匹配，還可以是加權匹配。要使用CONTAINS謂詞，必須在操作的表上事先建立全文索引。

CONTAINS ( {column | * } , ‘<contains_search_condition>’)

上式中，column表示在指定的列中搜尋，*表示在所有列中搜尋； <contains_search_condition>為搜尋的限定或說明。

<contains_search_condition>::=

{<simple_term> | <prefix_term> | <generation_term> |<proximity_term> | <weighted_term> }

| {(<contains_search_condition> ) { AND | AND NOT | OR }<contains_search_condition> […n] }

說明：

simple_term：用於說明搜尋的是單詞還是短語，格式為：word | “phrase”，word為單詞，即不含空格和標點符號的字串；短語是含一個或多個空格的字串。如果搜尋的是短語，則需要用雙引號將其括起來。

 

4.1.2 選擇行

prefix_term：給出了要搜尋的單詞或短語必須匹配的字首，格式為：

{ “word*” |“phase*”}

其中word為單詞，phase為短語，當查詢的串是是短語時，需用雙引號定界。

generation_term：說明搜尋包含原詞的派生詞，所謂派生詞是指原詞的名詞單、複數形式或動詞的各種時態等。格式為：

FORMSOF (INFLECTIONAL , <simple_term> [,…n])

proximity_term：表示搜尋包含NEAR或~運算子左右兩邊的詞或短語，格式為： { <simple_term> | <prefix_term> } { { NEAR | ~ } {<simple_term> | <prefix_term> }[…n]

weight_term：指明本語句是加權搜尋，即查詢的資料與給定的權重進行加權匹配，格式為：

ISABOUT ( { {<simple_term> | <prefix_term> | <generation_term> |<proximity_term> } [ WEUGHT (weight_value) ] } [,…n] )

其中weight_value是一個0～1之間的數，表示權重。

6. FREETEXT謂詞

與CONTAINS謂詞類似，FREETEXT謂詞也用於在一個表中搜尋單詞或短語，並要求表已建全文索引。格式為：

FREETEXT ( {column | * }, ‘freetext_string’ )

其中freetext_string是要搜尋的字串。

FREETEXT的查詢精度沒有CONTAINS高，他並不要求對它們進行嚴格的模式匹配。 FREETEXT對所查詢的串也沒有寫法要求，因此FREETEXT也稱為自由式查詢。

 

4.1.2 選擇行

7. 子查詢

子查詢通常與IN、EXIST謂詞及比較運算子結合使用。

（1）IN子查詢

IN子查詢用於進行一個給定值是否在子查詢結果集中的判斷，格式為：

expression [ NOT ]IN ( subquery )

其中subquery是子查詢。當表示式expression與子查詢subquery的結果表中的某個值相等時，IN謂詞返回TRUE，否則返回FALSE；若使用了NOT，則返回的值剛好相反。

【例4.12】在XSCJ資料庫中有描述課程情況的表KC和描述學生成績表的表XS_KC，表的結構和樣本資料見附錄A。要查詢選修了課程號為101的課程的學生的情況：

SELECT *

FROM XS

WHERE 學號 IN

( SELECT 學號 FROM XS_KC WHERE 課程號 = ‘101’ )

 

4.1.2 選擇行

在執行包含子查詢的SELECT語句時，系統先執行子查詢，產生一個結果表，再執行查詢。本例中，先執行子查詢：

SELECT 學號

FROM XS_KC

WHERE 課程名 = ‘101’

SELECT *

FROM XS

WHERE 學號 NOT IN

( SELECT 學號

FROM XS_KC

WHERE 課程號 IN

( SELECT 課程號

FROM KC

WHERE 課程名 = '離散數學'

)

)

 

 

4.1.2 選擇行

（2）比較子查詢

這種子查詢可以認為是IN子查詢的擴充套件，它使表示式的值與子查詢的結果進行比較運算，格式為：

expression { <| <= | = | > | >= | != | <> | !< | !> } { ALL | SOME | ANY} ( subquery )

其中expression為要進行比較的表示式，subquery是子查詢。ALL、SOME和ANY說明對比較運算的限制。

ALL指定表示式要與子查詢結果集中的每個值都進行比較，當表示式與每個值都滿足比較的關係時，才返回TRUE，否則返回FALSE；

SOME或ANY表示表示式只要與子查詢結果集中的某個值滿足比較的關係時，就返回 TRUE，否則返回FALSE。

【例4.14】查詢比所有計算機系的學生年齡都大的學生。

SELECT *

FROM XS

WHERE 出生時間 <ALL

( SELECT 出生時間

FROM XS

WHERE 專業名 = '計算機'

)

 

【例4.15】查詢課程號206的成績不低於課程號101的最低成績的學生的學號。

SELECT 學號

FROM XS_KC

WHERE 課程號 = '206' AND 成績 !< ANY

( SELECT 成績

FROM XS_KC

WHERE 課程號 = '101'

)

 

4.1.2 選擇行

（3）EXISTS子查詢

EXISTS謂詞用於測試子查詢的結果是否為空表，若子查詢的結果集不為空，則EXISTS返回TRUE，否則返回FALSE。EXISTS還可與NOT結合使用，即NOT EXISTS，其返回值與EXIST剛好相反。格式為：

[ NOT ] EXISTS (subquery )

【例4.16】查詢選修206號課程的學生姓名。

SELECT 姓名

FROM XS

WHERE EXISTS

( SELECT *

FROM XS_KC

WHERE 學號 = XS.學號 AND 課程號 = ‘206’

)

分析：

① 本例在子查詢的條件中使用了限定形式的列名引用XS.學號，表示這裡的學號列出自表XS。

 

4.1.2 選擇行

② 本例與前面的子查詢例子不同點是，前面的例子中，內層查詢只處理一次，得到一個結果集，再依次處理外層查詢；而本例的內層查詢要處理多次，因為內層查詢與XS.學號有關，外層查詢中XS表的不同行有不同的學號值。這類子查詢稱為相關子查詢，因為子查詢的條件依賴與外層查詢中的某些值。

【例4.17】查詢選修了全部課程的同學的姓名。

SELECT 姓名

FROM XS

WHERE NOT EXISTS

(SELECT *

FROM KC

WHERE NOT EXISTS

( SELECT *

FROM XS_KC

WHERE 學號=XS.學號 AND 課程號=KC.課程號

)

)

 

4.1.3 FROM子句

SELECT的查詢物件由FROM子句指定，其格式為：

[ FROM{<table_source>} [,…n] ]

其中table_source指出了要查詢的表或檢視。

<table_source>::=

table_name [ [ AS] table_alias ] [ WITH ( <table_hint>[,…] ) ]

/*查詢表，可以指定別名*/

| view_name [ [ AS] table_alias ] /*查詢檢視*/

| rowset_function[ [AS ] table_alias ] /*行集函式*/

| OPENXML /*XML文件*/

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

|<joined_table> /*連線表*/

 

1. table_name

table_name指定SELECT語句要查詢的表，從前面的例子中已經瞭解了其作用。可以使用AS選項為表指定別名，AS也可以省略，直接給出別名即可。別名主要用在相關子查詢及連線查詢中。

【例4.18】查詢選修了學號為081102同學所選修的全部課程的同學的學號。

分析：本例即要查詢這樣的學號y，對所有的課程號x，若081102號同學選修了該課，那麼y也選修了該課。

SELECT DISTINCT 學號

FROM XS_KC AS CJ1

WHERE NOT EXISTS

( SELECT *

FROM XS_KC AS CJ2

WHERE CJ2.學號 = ‘081102’ AND NOT EXISTS

( SELECT *

FROM XS_KC AS CJ3

WHERE CJ3.學號= CJ1.學號 AND CJ3.課程號 = CJ2.課程號

)

)

4.1.3 FROM子句

 

4.1.3 FROM子句

2. view_name

view_name為檢視名，也可以為其指定別名。有關檢視的介紹請見4.3節。

3. rowset_function

rowset_function是一個行集函式，行集函式通常返回一個表或檢視。主要的行集函式有CONTAINSTABLE、FREETEXTTABLE、OPENDATASOURCE、OPENQUERY、OPENROWSET和OPENXML。

(1) CONTAINSTABLE函式

該函式與CONTAINS謂詞相對應，用於對錶進行全文查詢，並且要求所查詢的表上建立了全文索引。CONTAINSTABLE函式的語法格式為：

CONTAINSTABLE (table , { column | * } , ‘<contains_search_condition>’ [ , top_n_by_rank] )

其中table是進行全文查詢的表，column指定被查詢的列，*指對所有列進行查詢。 contains_search_condition與CONTAINS謂詞中的搜尋條件完全相同。

CONTAINSTABLE函式返回包含滿足匹配條件的行，共有兩列：KEY和RANK，其中KEY是包含被檢索的字串的行的主鍵值，RANK是一個等級值，指明行與字串匹配的情況。可選項top_n_by_rank，說明只返回按RANK降序排列的結果表的前n行，其中n是正整數。

 

4.1.3 FROM子句

CONTAINSTABLE函式常與INNER JOIN結合使用。

(2) FREETEXTTABLE函式

FREETEXTTABLE函式與FREETEXT謂詞相對應，它的使用與CONTAINSTABLE函式類似，格式為：

FREETEXTTABLE (table , { column | * } , ‘freetext_string’ [ , top_n_by_rank ] )

該函式使用與FREETEXT謂詞相同的搜尋條件。

(3) OPENDATASOURCE函式

該函式使使用者連線到伺服器。格式為：

OPENDATASOURCE (provider_name , init_string )

其中provider_name是註冊為用於訪問資料來源的OLE DB 提供程式的 PROGID 的名稱，init_string是連線字串，這些字串將要傳遞給目標提供程式的 IDataInitialize 介面。

例如：

SELECT *

FROMOPENDATASOURCE

（ 'SQLOLEDB','Data Source=ServerName;UserID=MyUID;Password=MyPass'

).Northwind.dbo.Categories

 

4.1.3 FROM子句

(4) OPENQUERY函式

該函式在給定的連結伺服器（一個 OLE DB 資料來源）上執行指定的直接傳遞查詢，返回查詢的結果集。格式為：

OPENQUERY (linked_server , ‘query’ )

其中linked_server為連線的伺服器名，query是查詢命令串。

例如：

EXECsp_addlinkedserver 'OSvr', 'Oracle 7.3',

'MSDAORA',

'ORCLDB'

GO

SELECT *

FROMOPENQUERY(OSvr, 'SELECT title, id FROM al.book')

GO

該例使用為Oracle提供的OLE DB對Oracle資料庫建立了一個名為Osvr的連線伺服器，然後對該其進行檢索。

(5) OPENROWSET函式

該函式與OPENQUERY函式功能相同。

(6) OPENXML函式

OPENXML 通過XML文件提供行集檢視。

 

4.1.3 FROM子句

4. derived_table

derived_table是由SELECT查詢語句的執行而返回的表，必須使用為其指定一個別名，也可以為列指定別名。

【例4.19】在XS表中查詢1990年1月1日以前出生的學生的姓名和專業名，分別使用別名stu_name和speciality表示。

SELECTm.stu_name,m.speciality

FROM ( SELECT *FROM XS WHERE 出生時間<'19900101' ) AS m

(num,stu_name,speciality,sex,birthday,score,mem ,photo )

注意，若要為列指定別名，則必須為所有列指定別名。

執行結果如圖4.11所示。

5. joined_table

joined_table為連線表，將在下一小節介紹。

 

4.1.4 連線

連線是兩元運算，可以兩個或多個表進行查詢，結果通常是含有參加連線運算的兩個表（或多個表）的指定列的表。例如在XSCJ資料庫中需要查詢選修了離散數學課程的學生的姓名和成績，就需要將XS、KC和XS_KC三個表進行連線，才能查詢到結果。

在T-SQL中，連線查詢有兩大類表示形式，一是符合SQL標準連線謂詞表示形式，一是T-SQL擴充套件的使用關鍵字JOIN的表示形式。

1. 連線謂詞

可以在SELECT語句的WHERE子句中使用比較運算子給出連線條件對錶進行連線，將這種表示形式稱為連線謂詞表示形式。

【例4.20】查詢XSCJ資料庫每個學生的情況以及選修的課程情況。

SELECT XS.* ,XS_KC.*

FROM XS , XS_KC

WHERE XS.學號 = XS_KC.學號

 

 

4.1.4 連線

【例4.21】自然連線查詢

SELECT XS.* ,XS_KC.課程號, XS_KC.成績

FROM XS , XS_KC

WHERE XS.學號= XS_KC.學號

本例所得的結果表包含以下欄位：學號、姓名、專業名、性別、出生時間、總學分、備註、課程號、成績。

若選擇的欄位名在各個表中是唯一的，則可以省略欄位名前的表名。如本例的 SELECT子句也可寫為：

SELECT XS.* , 課程號, 成績

FROM XS , XS_KC

WHERE XS.學號 = XS_KC.學號

【例4.22】查詢選修了206課程且成績在80分以上的學生姓名及成績。

SELECT 姓名 , 成績

FROM XS , XS_KC

WHERE XS.學號 = XS_KC.學號 AND 課程號 = ‘206’ AND 成績 >= 80

 

4.1.4 連線

【例4.23】查詢選修了“計算機基礎”課程且成績在80分以上的學生學號、姓名、課程名及成績。

SELECT XS.學號, 姓名, 課程名, 成績

FROM XS , KC ,XS_KC

WHERE XS.學號 = XS_KC.學號 AND KC.課程號 = XS_KC.課程號

AND 課程名 = ‘計算機基礎’ AND 成績 >= 80

 

4.1.4 連線

2. 以JOIN關鍵字指定的連線

對兩個表之間的簡單內部連線（不匹配的行將從結果中刪除），通過在FROM字句中列出多個，並在WHERE子句中加上連線條件即可完成這個功能。而對更復診的外部連線（不匹配的行需要保留），或者多一個一個連線的查詢中用到的表多於兩個，那麼在FORM子句中需要明確的連線句法。T-SQL擴充套件了以JOIN關鍵字指定連線的表示方式，使表的連線運算能力有了增強。FROM子句的joined_table表示將多個表連線起來。

 

4.1.4 連線

<joined_table>::=

<table_source><join_type> <table_source> ON <search_condition>

|<table_source> CROSS JOIN <table_source>

|<joined_table>

其中table_source為指定要在Transact-SQL語句中使用的表或檢視（帶或不帶別名均可），join_type表示連線型別，ON用於指定連線條件。join_type的格式為：

[ INNER | { LEFT |RIGHT | FULL } [ OUTER ] [ <join_hint> ] JOIN

其中INNER表示內連線，OUTER表示外連線，join_hint是連線提示。

CROSS JOIN表示交叉連線。因此，以JOIN關鍵字指定的連線有三種型別：

(1) 內連線

內連線按照ON所指定的連線條件合併兩個表，返回滿足條件的行。

【例4.24】查詢XSCJ資料庫每個學生的情況以及選修的課程情況。

SELECT *

FROM XS INNER JOINXS_KC

ON XS.學號 = XS_KC.學號

 

4.1.4 連線

結果表將包含XS表和XS_KC表的所有欄位（不去除重複欄位—學號）。若要去除重複的學號欄位，可將SELECT子句改為：

SELECT XS.* , 課程號, 成績

內連線是系統預設的，可以省略INNER關鍵字。使用內連線後仍可使用WHERE子句指定條件。

【例4.25】用FROM的JOIN關鍵字表達下列查詢：查詢選修了206課程且成績在80分以上的學生姓名及成績。

SELECT 姓名 , 成績

FROM XS JOIN XS_KCON XS.學號 = XS_KC.學號

WHERE 課程號 = '206' AND成績>=80

內連線還可以用於多個表的連線。

【例4.26】用FROM的JOIN關鍵字表達下列查詢：查詢選修了“計算機基礎”課程且成績在80分以上的學生學號、姓名、課程名及成績。

SELECT XS.學號 , 姓名 , 課程名 , 成績

FROM XS JOIN XS_KCJOIN KC ON XS_KC.課程號 = KC.課程號

ON XS.學號 = XS_KC.學號

WHERE 課程名 = '計算機基礎' AND 成績>=80

作為一種特例，可以將一個表與它自身進行連線，稱為自連線。若要在一個表中查詢具有相同列值的行，則可以使用自連線。使用自連線時需為表指定兩個別名，且對所有列的引用均要用別名限定。

 

4.1.4 連線

【例4.27】查詢不同課程成績相同的學生的學號、課程號和成績。

SELECT a.學號,a.課程號,b.課程號,a.成績

FROM XS_KC a JOINXS_KC b

ON a.成績=b.成績 AND a.學號=b.學號 AND a.課程號!=b.課程號

圖4.15查詢不同課程成績相同的學生的學號、課程號和成績

(2) 外連線

外連線的結果表不但包含滿足連線條件的行，還包括相應表中的所有行。外連線包括三種：

● 左外連線（LEFT OUTER JOIN）：結果表中除了包括滿足連線條件的行外，還包括左表的所有行；

● 右外連線（RIGHT OUTER JOIN）：結果表中除了包括滿足連線條件的行外，還包括右表的所有行；

 

4.1.4 連線

● 完全外連線（FULL OUTER JOIN）：結果表中除了包括滿足連線條件的行外，還包括兩個表的所有行。

其中的OUTER關鍵字均可省略。

【例4.28】查詢所有學生情況，及他們選修的課程號，若學生未選修任何課，也要包括其情況。

SELECT XS.* , 課程號

FROM XS LEFT OUTERJOIN XS_KC ON XS.學號 = XS_KC.學號

本例執行時，若有學生未選任何課程，則結果表中相應行的課程號欄位值為NULL。

【例4.29】查詢被選修了的課程的選修情況和所有開設的課程名。

SELECT XS_KC.* , 課程名

FROM XS_KC RIGHTJOIN KC ON XS_KC.課程號= KC.課程號

本例執行時，若某課程未被選修，則結果表中相應行的學號、課程號和成績欄位值均為NULL。

注意外連線只能對兩個表進行。

(3) 交叉連線

交叉連線實際上是將兩個表進行笛卡爾積運算，結果表是由第一個表的每行與第二個表的每一行拼接後形成的表，因此結果表的行數等於兩個錶行數之積。

【例4.30】列出學生所有可能的選課情況。

SELECT 學號，姓名，課程號，課程名

FROM XS CROSS JOINKC

注意: 交叉連線不能有條件，且不能帶WHERE子句。

 

4.1.5 資料彙總

對錶資料進行檢索時，經常需要對結果進行彙總或計算，例如在學生成績資料庫中求某門功課的總成績、統計各分數段的人數等。本小節將討論SELECT語句中用於資料統計的子句及函式。

1. 聚合函式

聚合函式用於計算表中的資料，返回單個計算結果。SQL Server 2000所提供的聚合函式列於表4.10中。

 

4.1.5 資料彙總

表4.10 聚合函式表

函式名	說明
AVG	求組中值的平均值。
BINARY_CHECKSUM	返回對錶中的行或表示式列表計算的二進位制校驗值，可用於檢測表中行的更改。
CHECKSUM	返回在表的行上或在表示式列表上計算的校驗值，用於生成雜湊索引。
CHECKSUM_AGG	返回組中值的校驗值。
COUNT	求組中項數，返回int型別整數。
COUNT_BIG	求組中項數，返回bigint型別整數。
GROUPING	產生一個附加的列。
MAX	求最大值。
MIN	求最小值。
SUM	返回表示式中所有值的和。
STDEV	返回給定表示式中所有值的統計標準偏差。
STDEVP	返回給定表示式中所有值的填充統計標準偏差。
VAR	返回給定表示式中所有值的統計方差。
VARP	返回給定表示式中所有值的填充的統計方差。

 

4.1.5 資料彙總

下面對常用的幾個聚合函式加以介紹。

（1）SUM和AVG

SUM和AVG分別用於求表示式中所有值項的總和與平均值，語法格式為：

SUM / AVG ( [ ALL| DISTINCT ] expression )

其中expression是常量、列、函式或表示式，其資料型別只能是：int、smallint、tinyint、bigint、decimal、numeric、float、real、money和smallmoney。ALL表示對所有值進行運算，DISTINCT表示去除重複值，預設為ALL。SUM / AVG忽略NULL值。

【例4.31】求選修101課程的學生的平均成績。

SELECT AVG(成績) AS '課程101平均成績'

FROM XS_KC

WHERE 課程號 = '101'

執行結果為：

課程101平均成績

78

使用聚合函式作為SELECT的選擇列時，若不為其指定列標題，則系統將對該列輸出標題“（無列名）”。

（2）MAX和MIN

MAX和MIN分別用於求表示式中所有值項的最大值與最小值，語法格式為：

MAX / MIN ( [ ALL| DISTINCT ] expression )

 

4.1.5 資料彙總

其中expression是常量、列、函式或表示式，其資料型別可以是數字、字元和時間日期型別。ALL、DISTINCT的含義及預設值與SUM/AVG函式相同。MAX/MIN忽略NULL值。

【例4.32】求選修101課程的學生的最高分和最低分。

SELECT MAX(成績) AS '課程101的最高分' ,MIN(成績) AS '課程101的最低分'

FROM XS_KC

WHERE 課程號 = '101'

執行結果為：

課程101的最高分 課程101的最低分

95 62

（3）COUNT

COUNT用於統計組中滿足條件的行數或總行數，格式為：

COUNT ( { [ ALL |DISTINCT ] expression } | * )

其中expression是一個表示式，其資料型別是除uniqueidentifier、text、image或ntext之外的任何型別。ALL、DISTINCT的含義及預設值與SUM/AVG函式相同。選擇*時將統計總行數。COUNT忽略NULL值。

【例4.33】求學生的總人數。

SELECT COUNT(*) AS'學生總數'

FROM XS

其中：COUNT(*) 不需要任何引數。

 

4.1.5 資料彙總

【例4.34】求選修了課程的學生總人數。

SELECTCOUNT(DISTINCT 學號)

FROM XS_KC

【例4.35】統計離散數學課程成績在85分以上的人數。

SELECT COUNT(成績) AS '離散數學85分以上的人數'

FROM XS_KC

WHERE 成績 >= 85 AND 課程號 IN

( SELECT 課程號

FROM KC

WHERE 課程名 = '離散數學'

)

執行結果為：

離散數學85分以上的人數

2

COUNT_BIG函式的格式、功能與COUNT函式都相同，區別僅在於COUNT_BIG返回bigint型別值。

 

4.1.5 資料彙總

（4）GROUPING

GROUPING函式為輸出的結果表產生一個附加列，該列的值為1或0，格式為：

GROUPING（ column_name ）

當用 CUBE 或 ROLLUP 運算子新增行時，附加的列輸出值為1，當所新增的行不 是由 CUBE 或 ROLLUP 產生時，附加列值為0。

該函式只能與帶有CUBE或ROLLUP運算子的GROUP BY子句一起使用。

2. GROUP BY子句

GROUP BY子句用於對錶或檢視中的資料按欄位分組，格式為：

[ GROUP BY [ ALL ]group_by_expression [,…n]

[ WITH { CUBE |ROLLUP } ] ]

說明:

group_by_expression:用於分組的表示式，其中通常包含欄位名。指定ALL將顯示所有組。使用GROUPBY子句後，SELECT子句中的列表中只能包含在GROUPBY中指出的列或在聚合函式中指定的列。WITH指定CUBE或ROLLUP操作符，CUBE或 ROLLUP與聚合函式一起使用，在查詢結果中增加附加記錄。

【例4.36】將XSCJ資料庫中各專業名輸出。

SELECT 專業名

FROM XS

GROUP BY 專業名

 

【例4.37】求XSCJ資料庫中各專業的學生數。

SELECT 專業名,COUNT(*) AS '學生數'

FROM XS

GROUP BY 專業名

 

4.1.5 資料彙總

【例4.38】求被選修的各門課程的平均成績和選修該課程的人數。

SELECT 課程號, AVG(成績) AS '平均成績',COUNT(學號) AS '選修人數'

FROM XS_KC

GROUP BY 課程號

 

4.1.5 資料彙總

【例4.39】在XSCJ資料庫上產生一個結果集，包括每個專業的男生、女生人數、總人數及學生總人數。

SELECT 專業名, 性別 , COUNT(*) AS '人數'

FROM XS

GROUP BY 專業名,性別

WITH ROLLUP

4.1.5 資料彙總

【例4.39】在XSCJ資料庫上產生一個結果集，包括每個專業的男生、女生人數、總人數及學生總人數。

SELECT 專業名, 性別 , COUNT(*) AS '人數'

FROM XS

GROUP BY 專業名,性別

WITH ROLLUP

 

 

4.1.5 資料彙總

【例4.40】在XSCJ資料庫上產生一個結果集，包括各專業每門課程的平均成績、每門課程的總平均成績和所有課程的總平均成績。

SELECT 課程名, 專業名, AVG(成績) AS '平均成績'

FROM XS_KC,KC,XS

WHERE XS_KC.課程號 = KC.課程號 AND XS_KC.學號 = XS.學號

GROUP BY 課程名, 專業名

WITH ROLLUP

 

4.1.5 資料彙總

【例4.41】在XSCJ資料庫上產生一個結果集，包括每個專業的男生、女生人數、總人數及男生總數、女生總數、學生總人數。

SELECT 專業名, 性別 , COUNT(*) AS '人數'

FROM XS

GROUP BY 專業名,性別

WITH CUBE

 

4.1.5 資料彙總

執行結果如圖4.22所示。

4.1.5 資料彙總

執行結果如圖4.22所示。

圖4.22 查詢結果

分析：本例中用於分組的列（即GROUP BY子句中的列）為專業名和性別，在XS表中，專業名有兩個不同的值（計算機、通訊工程），性別也有兩個不同的值（0、1），再加上NULL值，因此它們可能的組合有5種，因此生成5個彙總行。

 

4.1.5 資料彙總

【例4.42】在XSCJ資料庫上產生一個結果集，包括各專業每門課程的平均成績、每門課程的總平均成績、每個專業的總平均成績和所有課程的總平均成績。

SELECT 課程名,專業名, AVG(成績) AS '平均成績'

FROM XS_KC, KC, XS

WHERE XS_KC.課程號 = KC.課程號 AND XS_KC.學號 = XS.學號

GROUP BY 課程名,專業名

 

4.1.5 資料彙總

【例4.43】統計各專業男生、女生人數及學生總人數，標誌彙總行。

SELECT 專業名, 性別 , COUNT(*) AS '人數', GROUPING(專業名) AS'spec', GROUPING(性別) AS 'sx'

FROM XS

GROUP BY 專業名,性別

WITH CUBE

 

4.1.5 資料彙總

3. HAVING子句

使用GROUP BY子句和聚合函式對資料進行分組後，還可以使用HAVING子句對分組資料進行進一步的篩選。HAVING子句有一個與WHERE子句類似的形式。

例如查詢XSCJ資料庫中平均成績在85分以上的學生，就是在XS_KC資料庫上按學號分組後篩選出符合平均成績大於等於85的學生。HAVING子句的格式為：

[ HAVING<search_condition> ]

其中search_condition為查詢條件，與WHERE子句的查詢條件類似，並且可以使用聚合函式。

【例4.44】查詢XSCJ資料庫中平均成績在85分以上的學生的學號和平均成績。

SELECT 學號 , AVG(成績) AS '平均成績'

FROM XS_KC

GROUP BY 學號

HAVING AVG(成績) > =85

 

4.1.5 資料彙總

【例4.45】查詢選修課程超過2門且成績都在80分以上的學生的學號。

SELECT 學號

FROM XS_KC

WHERE 成績 >= 80

GROUP BY 學號

HAVING COUNT(*)> 2

分析：本查詢將XS_KC表中成績大於80的記錄按學號分組，對每組記錄計數，選出記錄數大於2的各組的學號值形成結果表。

【例4.46】查詢通訊工程專業平均成績在85分以上的學生的學號和平均成績。

SELECT 學號,AVG(成績) AS '平均成績'

FROM XS_KC

WHERE 學號 IN

( SELECT 學號

FROM XS

WHERE 專業名 = '通訊工程'

)

GROUP BY 學號

HAVING AVG(成績) > =85

 

4.1.5 資料彙總

分析：先執行WHERE查詢條件中的子查詢，得到通訊工程專業所有學生的學號集；然後對XS_KC中的每條記錄，判斷其學號欄位值是否在前面所求得的學號集中。若否，則跳過該記錄，繼續處理下一條記錄，若是則加入WHERE的結果集。對XS_KC均篩選完後，按學號進行分組，再在各分組記錄中選出平均成績值大於等於85的記錄形成最後的結果集。

4. COMPUTE子句

COMPUTE子句用於分類彙總。對於互動式的檢索，SQL提供了計算部分和的能力。使用者可以在包含幾個選擇表示式的SELECT語句後，帶有幾個使用合計函式的COMPUTE子句，計算不同層次的合計值。COMPUTE子句格式為：

[ COMPUTE { 聚合函式名（expression）} [ ,…n ] [ BY expression [ ,…n ] ] ]

其中聚合函式名見表4.10，expression是列名。

COMPUTE將產生額外的彙總行。

【例4.47】查詢通訊工程專業學生的學號、姓名、出生時間，併產生一個學生總人數行。

SELECT 學號,姓名, 出生時間

FROM XS

WHERE 專業名 = '通訊工程'

COMPUTE COUNT(學號)

 

4.1.5 資料彙總

執行結果如圖4.26所示。從圖中可以看出，COMPUTE子句產生附加的彙總行，其列標題是系統自定的，對於COUNT函式為cnt，對於AVG函式為avg，對於SUM函式為sum，等等。

COMPUTE BY子句要與ORDER BY子句一起使用，將在下一小節介紹。

 

4.1.6 排序

在應用中經常要對查詢的結果排序輸出，例如學生成績由高到低排序。在SELECT語句中，使用ORDERBY子句對查詢結果進行排序。ORDER BY子句的格式為：

[ ORDER BY {order_by_expression [ ASC | DESC ] } [ ,…n ]

其中order_by_expression是排序表示式，可以是列名、表示式或一個正整數，當 expression是一個正整數時，表示按表中的該位置上列排序。

關鍵字ASC表示升序排列，DESC表示降序排列，系統預設值為ASC。

【例4.48】將通訊工程專業的學生按出生時間先後排序。

SELECT *

FROM XS

WHERE 專業名 = '通訊工程'

ORDER BY 出生時間

【例4.49】將計算機專業學生的“計算機基礎”課程成績按降序排列。

SELECT 姓名,課程名,成績

FROM XS,KC,XS_KC

WHERE XS.學號 = XS_KC.學號 ANDXS_KC.課程號 = KC.課程號

AND 課程名 = '計算機基礎' AND 專業名= '計算機'

ORDER BY 成績 DESC

 

4.1.6 排序

ORDER BY子句可以與COMPUTE BY子句一起使用，在對結果排序的同時還產生附加的彙總行。

【例4.50】將學生按專業名排序，並彙總各專業人數和平均學分。

SELECT 學號,姓名,出生時間,總學分

FROM XS

ORDER BY 專業名

COMPUTE COUNT(學號),

AVG(總學分) BY 專業名

 

4.2.1 檢視概念

檢視一經定義以後，就可以象表一樣被查詢、修改、刪除和更新。使用檢視有下列優點：

為使用者集中資料，簡化使用者的資料查詢和處理。有時使用者所需要的資料分散在多個表中，定義檢視可將它們集中在一起，從而方便使用者的資料查詢和處理。

遮蔽資料庫的複雜性。使用者不必瞭解複雜的資料庫中的表結構，並且資料庫表的更改也不影響使用者對資料庫的使用。

簡化使用者許可權的管理。只需授予使用者使用檢視的許可權，而不必指定使用者只能使用表的特定列，也增加了安全性。

便於資料共享。各使用者不必都定義和儲存自己所需的資料，可共享資料庫的資料，這樣同樣的資料只需儲存一次。

可以重新組織資料以便輸出到其它應用程式中。

使用檢視時，要注意下列事項：

(1) 只有在當前資料庫中才能建立檢視。檢視的命名必須遵循識別符號命名規則，不能與表同名，且對每個使用者檢視名必須是唯一的，即對不同使用者，即使是定義相同的檢視，也必須使用不同的名字。

(2) 不能把規則、預設值或觸發器與檢視相關聯。

(3) 不能在檢視上建立任何索引，包括全文索引。

 

4.2.2 建立檢視

1. 在SQL Server Enterprise Manager中建立檢視

以在XSCJ資料庫中建立CS_XS（描述計算機專業學生情況）檢視說明在SQL Server Enterprise Manager中建立檢視的過程。

第1步 開啟SQL Server Enterprise Manager，在資料庫XSCJ上單擊滑鼠右鍵，在彈出的快捷選單上選擇“新建”?“檢視..”，

 

4.2.2 建立檢視

第2步 在所出現的視窗的第一個子視窗中單擊滑鼠右鍵，將彈出一個“增加表”快捷選單，如圖4.29所示。

“增加表”快捷選單

 

4.2.2 建立檢視

第3步 在“增加表”快捷選單中選擇“新增表…”功能項，將出現如圖4.30所示的“新增表”對話方塊。

第4步 在圖4.30所示的“新增表”對話方塊中選擇與檢視相關聯的表、檢視或函式，可以使用Ctrl或Shift鍵進行多選，選擇完畢後，單擊“新增”按鈕。

新增表對話方塊

 

4.2.2 建立檢視

第5步 在圖4.29所示的視窗的第2個子視窗中選擇建立檢視所需的欄位，可以指定列的別名、排序方式和規則（本例指定“專業名”欄位的規則為“計算機”）等，如圖4.31所示。注意當檢視中需要一個與原欄位名不同的欄位名、或檢視的源表中有同名的欄位、或檢視中包含了計算列時，需要為檢視中的這樣的列重新指定名稱。

選擇列

 

4.2.2 建立檢視

第6步 上一步完成後，單擊儲存按鈕，出現儲存檢視對話方塊，在其中輸入檢視名“CS_XS”，並單擊“確定“按鈕，便完成了檢視的建立。

檢視一經建立成功，其中便包含了所選擇的列資料。例如，若建立了CS_XS檢視，則可檢視其結構及內容：在CS_XS檢視上單擊滑鼠右鍵，選擇“設計檢視”功能項可以檢視並可修改檢視結構，選擇“開啟檢視”?“返回所有行”將可檢視檢視資料內容。

2. 通過檢視嚮導建立檢視

第1步 開啟SQL Server Enterprise Manager，選擇“工具”選單中的“嚮導”功能項，出現如圖4.32所示的選擇嚮導對話方塊。

 選擇嚮導對話方塊

 

4.2.2 建立檢視

第2步 在選擇嚮導對話方塊中展開“資料庫”，選擇“建立檢視嚮導”功能項，單擊“確定”，將出現歡迎使用嚮導介面。

第3步 在歡迎使用嚮導介面中單擊“下一步”，出現選擇資料庫對話方塊。選擇資料庫名“XSCJ”，單擊“下一步”，出現如圖4.33所示的選擇表對話方塊。

 選擇表

 

4.2.2 建立檢視

第4步 在選擇表對話方塊中選擇與檢視相關聯的表，單擊“下一步”，出現如圖4.34所示的選擇列對話方塊。

第5步 在選擇列對話方塊中選擇檢視所需要的列，單擊“下一步”，出現如圖4.35所示的檢視限制對話方塊。

第6步 在檢視限制對話方塊中輸入對檢視的限制條件，本例為：WHERE 專業名=‘計算機’，單擊“下一步”，出現輸入檢視名對話方塊。

第7步 在輸入檢視名對話方塊中輸入檢視名“XS_XS”，單擊“下一步”，出現如圖4.36所示的檢視建立完成對話方塊，單擊“完成”，則檢視即建立完畢。

選擇列                       輸入檢視限制

 

4.2.2 建立檢視

 檢視建立完成

3. 使用CREATE VIEW語句建立檢視

T-SQL中用於建立檢視的語句是CREATE VIEW語句，例如用該語句建立檢視CS_XS，其表示形式為：

USE XSCJ

GO

CREATE VIEW CS_XS

AS

SELECT *

FROM XS

WHERE 專業名 = '計算機'

GO

 

4.2.2 建立檢視

語法格式：

CREATE VIEW [<database_name>. ][ <owner>. ] view_name [ ( column_name [ ,…n ] )]

[ WITH <view_attribute>[,…n ] ]

ASselect_statement

[ WITH CHECKOPTION ]

說明：

database_name是資料庫名；owner是所有者名；view_name是檢視名。

column_name：列名，它是檢視中包含的列，可以有多個列名，最多可引用1024個列。 若使用與源表或檢視中相同的列名時，則不必給出column_name。

WITHview_attribute: 指出檢視的屬性，view_attribute可以取以下值：

(1) ENCRYPTION：說明在系統表syscomments中儲存CREATEVIEW語句時進行加密。

(2) SCHEMABINDING：說明將檢視與其所依賴的表或檢視結構相關聯。

(3) VIEW_METADATA：指定為引用檢視的查詢請求瀏覽模式的後設資料時，向DBLIB、 ODBC或OLEDB API返回有關檢視的後設資料資訊，而不是返回給基表或其它表。

select_statement：用來建立檢視的SELECT語句，可在SELECT語句中查詢多個表或檢視，以表明新建立的檢視所參照的表或檢視。但對SELECT語句有以下的限制：

 

4.2.2 建立檢視

(1) 定義檢視的使用者必須對所參照的表或檢視有查詢（即可執行SELECT語句）許可權；

(2) 不能使用COMPUTE或COMPUTE BY子句；

(3) 不能使用ORDER BY子句；

(4) 不能使用INTO子句；

(5) 不能在臨時表或表變數上建立檢視。

WITH CHECK OPTION:指出在檢視上所進行的修改都要符合select_statement所指定的限制條件，這樣可以確保資料修改後，仍可通過檢視看到修改的資料。例如對於CS_XS檢視，只能修改除“專業名”欄位以外的欄位值，而不能把專業名欄位的值改為“計算機”以外的值，以保證仍可通過CS_XS查詢到修改後的資料。

注意：CREATE VIEW必須是批命令的第一條語句。

【例4.51】建立CS_KC檢視，包括計算機專業各學生的學號、其選修的課程號及成績。 要保證對該檢視的修改都要符合專業名為計算機這個條件。

CREATE VIEW CS_KCWITH ENCRYPTION

AS

SELECT XS.學號,課程號,成績

FROM XS,XS_KC

WHERE XS.學號 = XS_KC.學號 AND 專業名 = '計算機'

WITH CHECK OPTION

 

4.2.2 建立檢視

建立檢視時，源表可以是基本表，也可以是檢視。

【例4.52】建立計算機專業學生的平均成績檢視CS_KC_AVG，包括學號（在檢視中列名為num）和平均成績（在檢視中列名為score_avg）。

CREATE VIEWCS_KC_AVG(num,score_avg)

AS

SELECT 學號,AVG(成績)

FROM CS_KC

GROUP BY 學號

 

4.2.3 查詢檢視

再對XS_KC_AVG檢視進行查詢。

SELECT *

FROM XS_KC_AVG

WHEREscore_avg>=80

檢視定義後，就可以如同查詢基本表那樣對檢視進行查詢。

【例4.53】使用檢視CS_KC查詢計算機專業的學生學號和選修的課程號。

SELECT 學號,課程號

FROM CS_KC

【例4.54】查詢平均成績在80分以上的學生的學號和平均成績。

本例首先建立學生平均成績檢視XS_KC_AVG，包括學號（在檢視中列名為num）和平均成績（在檢視中列名為score_avg）。

CREATE VIEWXS_KC_AVG ( num,score_avg )

AS

SELECT 學號,AVG(成績)

FROM XS_KC

GROUP BY學號

 

4.2.3 查詢檢視

使用檢視查詢時，若其關聯的基本表中新增了新欄位，則必須重新建立檢視才能查詢到新欄位。例如，若XS表新增了“籍貫”欄位，那麼在其上建立的檢視CS_XS若不重建檢視，那麼以下查詢：

SELECT * FROMCS_XS

結果將不包含“籍貫”欄位。只有重建CS_XS檢視後再對它進行查詢，結果才會包含“籍貫”欄位。

如果與檢視相關聯的表或檢視被刪除，則該檢視將不能再使用。

的檢視。

 

4.2.4 更新檢視

通過更新檢視（包括插入、修改和刪除）資料可以修改基本表資料。但並不是所有的檢視都可以更新，只有對滿足可更新條件的檢視，才能進行更新。

1. 可更新檢視

要通過檢視更新基本表資料，必須保證檢視是可更新檢視。一個可更新檢視可以是以下情形之一：

(1) 滿足以下條件的檢視：建立檢視的SELECT語句中沒有聚合函式，且沒有TOP、GROUP BY、UNION子句及DISTINCT關鍵字；建立檢視的SELECT語句中不包含從基本表 列通過計算所得的列；建立檢視的SELECT語句的FROM子句中至少要包含一個基本表。

(2) 可更新的分割槽檢視。

(3) 通過INSTEAD OF觸發器建立的可更新檢視。

前面建立的檢視CS_XS、CS_KC是可更新檢視，而CS_KC_AVG是不可更新

 

4.2.4 更新檢視

【例4.55】在XSCJ資料庫中使用以下的CREATE TABLE語句建立表XS1和XS2，它們在分割槽列（即鍵值）上的CHECK約束分別為BETWEEN‘084001’ AND ‘084050’和 BETWEEN '083001' AND '083050'，兩個表在鍵值約束上是不重疊的。

CREATE TABLE XS1

(學號 char(6) PRIMARY KEY CHECK (學號 BETWEEN '084001' AND '084050'),

姓名 char(8) NOT NULL,

專業名 char(10) NULL,

性別 bit NOT NULL,

總學分 tinyint NULL

)

CREATE TABLE XS2

(學號 char(6) PRIMARY KEY CHECK (學號 BETWEEN '083001' AND '083050'),

姓名 char(8) NOT NULL,

專業名 char(10) NULL,

性別 bit NOT NULL,

總學分 tinyint NULL

)

 

4.2.4 更新檢視

並向XS1表中加入以下的資料行：

084001王進外語 140

084002郭明外語 138

向XS2表中加入以下的資料行：

083001李玉物理 038

083002劉明物理 138

使用以下的CREATE VIEW語句建立的檢視XS12_VIEW將是可更新的分割槽檢視：

CREATE VIEWXS12_VIEW

AS

SELECT *

FROM XS1

UNION ALL

SELECT *

FROM XS2

INSTEAD OF觸發器將在後面的章節介紹。

對檢視進行更新操作時，要注意基本表對資料的各種約束和規則要求。

 

4.2.4 更新檢視

2. 插入資料

使用INSERT語句通過檢視向基本表插入資料，有關INSERT語句的語法介紹見第3章。

【例4.56】向CS_XS檢視中插入一條記錄：

（ ‘081115’ , ‘劉明儀’, ‘計算機’, 1,‘1989-3-2’, 50 ,NULL,NULL）

INSERT INTO CS_XS

VALUES('081115', '劉明儀','計算機',1,'1989-3-2',50,NULL,NULL)

使用SELECT語句查詢CS_XS依據的基本表XS：

SELECT * FROM XS

將會看到該表已新增了('081115', '劉明儀','計算機',1,'1989-3-2',50,NULL,NULL)行。

當檢視所依賴的基本表有多個時，不能向該檢視插入資料，因為這將會影響多個基表。例如不能向檢視CS_KC插入資料，因為CS_KC依賴兩個基本表：XS和XS_KC。

向可更新的分割槽檢視中插入資料時，系統會按照插入記錄的鍵值所屬的範圍，將資料插入到其鍵值所屬的基本表中。

【例4.57】向XS12_VIEW檢視中插入一條記錄：

（ ‘084010’ , ‘藍圓圓’, ‘外語’, 0, 40 ）

INSERT INTOXS12_VIEW

VALUES('084010','藍圓圓','外語',0,40)

 

4.2.4 更新檢視

由於插入記錄的鍵值為084010，因此該記錄實際上是插入到表XS1中，使用以下的SELECT查詢語句：

SELECT * FROM XS1

3. 修改資料

使用UPDATE語句可以通過檢視修改基本表的資料，有關UPDATE語句的語法介紹見第3章。

【例4.58】將CS_XS檢視中所有學生的總學分增加8。

UPDATE CS_XS

SET 總學分 = 總學分+ 8

 

4.2.4 更新檢視

該語句實際上是將CS_XS檢視所依賴的基本表XS中所有專業名為“計算機”的記錄的總學分欄位值在原來基礎上增加8。

若一個檢視（非分割槽檢視）依賴於多個基本表，則一次修改該檢視只能變動一個基本表的資料。

【例4.59】將CS_KC檢視中學號為081101的學生的101課程成績改為90。

UPDATE CS_KC

SET 成績=90

WHERE 學號='081101' AND 課程號='101'

本例中，檢視CS_KC依賴於兩個基本表：XS和XS_KC，對CS_KC檢視的一次修改只能改變學號（源於XS表）或者課程號和成績（源於XS_KC表）。以下的修改是錯誤的：

UPDATE CS_KC

SET 學號=’081120’,課程號=’208’

WHERE 成績=90

對於可更新的分割槽檢視，則一次修改可以變動其依賴的多個基本表。

 

4.2.4 更新檢視

【例4.60】將XS12_VIEW檢視中的每個學生的總學分增加10。

UPDATE XS12_VIEW

SET 總學分 = 總學分+ 10

用SELECT語句分別查詢檢視XS12_VIEW所依賴的基本表XS1和XS2：

SELECT * FROM XS1

ELECT * FROM XS2

4.2.4 更新檢視

4. 刪除資料

【例4.61】刪除CS_XS中女同學的記錄。

DELETE FROM CS_XS

WHERE 性別 = 0

可以通過可更新的分割槽檢視刪除其所依賴的基本表資料。

【例4.62】刪除XS12_VIEW中學號為“084001”的學生記錄。

DELETE FROMXS12_VIEW

WHERE 學號 = '084001'

【例4.63】通過SQLServer Enterprise Manager的介面對檢視XS12_VIEW進行如下操作：

① 增加一條記錄（’084003’,’汪小波’,’外語’,1,50）。

② 將學號為083001的學生的總學分改為50。

③ 刪除學號為083002的學生記錄。

 

4.2.4 更新檢視

第1步 展開資料庫和檢視，在XS12_VIEW檢視上單擊滑鼠右鍵，在彈出的快捷選單上依次選擇“開啟檢視”?“返回所有行”

 開啟檢視

 

 更新檢視

第2步 新增記錄，在所出現的如圖4.42所示的表“XS12_VIEW”視窗中新增新記錄，輸入新記錄各欄位的值。

第3步 修改記錄，定位到需修改的學號為“083001”行的總學分欄位，刪除原值48，輸入新值50。

第4步 刪除記錄，如圖4.43所示，定位到需刪除的學號為“083002”的行，單擊滑鼠右鍵，在彈出的快捷選單上選擇“刪除”，將出現確認是否刪除的對話方塊，在其中單擊“確定”。

通過檢視插入資料

 

4.2.4 更新檢視

通過檢視刪除資料

 

4.2.5 修改檢視的定義

修改檢視定義可以通過SQL Server Enterprise Manager的介面進行，也可使用T-SQL的ALTER VIEW語句。

1. 通過SQL Server Enterprise Manager修改檢視

第1步 在SQL Server Enterprise Manager中展開資料庫和檢視，在需修改的檢視上單擊滑鼠右鍵，在彈出的快捷選單上選擇“設計檢視”，將出現如圖4.44所示的視窗。

第2步 在視窗中對檢視定義進行修改，修改完後單擊儲存圖示按鈕即可。

注意：對加密儲存的檢視定義不能在SQL Server Enterprise Manager中通過介面修改，例如對檢視CS_KC不能用此法修改。

 修改檢視定義

 

4.2.5 修改檢視的定義

2. 使用ALTER VIEW語句修改檢視

語法格式：

ALTER VIEW [<database_name> . ][ <owner> . ] view_name [ ( column_name [ ,…n ]) ]

[ WITH<view_attribute>[,…n ] ]

ASselect_statement

[ WITH CHECKOPTION ]

其中view_attribute、select_statement等引數與CREATEVIEW語句中含義相同。

【例4.64】將CS_XS檢視修改為只包含計算機專業學生的學號、姓名和總學分。

USE XSCJ

GO

ALTER VIEW CS_XS

AS

SELECT 學號,姓名,總學分

FROM XS

WHERE 專業名 = '計算機'

GO

 

4.2.5 修改檢視的定義

注意：和CREATE VIEW一樣，ALTERVIEW也必須是批命令中的第一條語句。

使用ENCRYPTION屬性定義的檢視（即加密儲存檢視）可以使用ALTER VIEW語句修改。

【例4.65】檢視CS_KC是加密儲存檢視，修改其定義：包括學號、姓名、選修的課程號、課程名和成績。

ALTER VIEW CS_KCWITH ENCRYPTION

AS

SELECT XS.學號,XS.姓名,XS_KC.課程號,KC.課程名,成績

FROM XS,XS_KC,KC

WHERE XS.學號 = XS_KC.學號 ANDXS_KC.課程號 = KC.課程號

AND 專業名 = '計算機'

WITH CHECK OPTION

 

4.2.6 刪除檢視

刪除檢視同樣也可以通過企業管理器介面和T-SQL語句兩種方式來實現。

在企業管理器中刪除檢視的操作方法是：展開資料庫和檢視，在需刪除的檢視上單擊滑鼠右鍵，在彈出的快捷選單上選擇“刪除”，出現如圖4.45所示的對話方塊，在其中點選“全部除去”按鈕即可刪除指定的檢視。

刪除檢視

語法格式：

DROP VIEW { view }[ , …n ]

其中view是檢視名，使用DROPVIEW一次可刪除多個檢視。例如：

DROP VIEW CS_KC

將刪除檢視CS_KC。

 

4.3.1 遊標概念

在SQL Server中，有兩類遊標可以用於應用程式中：前端（客戶端）遊標和後端（伺服器端）遊標。伺服器端遊標是由資料庫伺服器建立和管理的遊標，而客戶端遊標是由ODBC 和 DB-Library支援，在客戶端實現的遊標。在客戶端遊標中，所有的遊標操作都在客戶端快取記憶體中執行。最初實現 DB-Library 客戶端遊標時 SQLServer 尚不支援伺服器遊標，而ODBC 客戶端遊標，是為了用於僅支援遊標特性預設設定的 ODBC 驅動程式。 由於 DB-Library 和 SQLServer ODBC 驅動程式完全支援通過伺服器遊標的遊標操作，所以我們應儘量不使用客戶端遊標。SQL Sever 2000中對客戶端遊標的支援也主要是考慮向後相容。本節除非特別指明，所說的遊標均為伺服器遊標。

SQL Server對遊標的使用要遵循宣告遊標?開啟遊標?讀取資料?關閉遊標?刪除遊標。

 

4.3.2 宣告遊標

T-SQL中宣告遊標使用DECLARE CURSOR語句，該語句有兩種格式，分別支援SQL-92標準和T-SQL擴充套件的遊標宣告。

1. SQL-92語法

語句格式：

DECLAREcursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR

FORselect_statement

[ FOR { READ ONLY| UPDATE [ OF column_name [ ,…n ] ] } ]

說明：

cursor_name：遊標名，它是與某個查詢結果集相聯絡的符號名，要符合SQL Server識別符號命名規則。

INSENSITIVE：指定系統將建立供所定義的遊標使用的資料的臨時複本，對遊標的所有請求都從tempdb中的該臨時表中得到應答；因此，在對該遊標進行提取操作時返回的資料中不反映對基表所做的修改，並且該遊標不允許修改。如果省略 INSENSITIVE，則任何使用者對基表提交的刪除和更新都反映在後面的提取中。

SCROLL：說明所宣告的遊標可以前滾、後滾，可使用所有的提取選項（FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE）。如果省略SCROLL，則只能使用NEXT提取選項。

 

4.3.2 宣告遊標

select_statement：SELECT語句，由該查詢產生與所宣告的遊標相關聯的結果集。該SELECT語句中不能出現COMPUTE、COMPUTE BY、INTO或FOR BROWSE關鍵字。

READ ONLY：說明所宣告的遊標為只讀的。UPDATE指定遊標中可以更新的列，若有引數OF column_name [ ,…n ]，則只能修改給出的這些列，若在UPDATE中未指出列，則可以修改所有列。

以下是一個符合SQL-92標準的遊標宣告：

DECLARE XS_CUR1CURSOR

FOR

SELECT 學號,姓名,性別,出生時間,總學分

FROM XS

WHERE 專業名 = '計算機'

FOR READ ONLY

該語句定義的遊標與單個表的查詢結果集相關聯，是隻讀的，遊標只能從頭到尾順序提取資料，相當於下面所講的只進遊標。

 

4.3.2 宣告遊標

2. T-SQL擴充套件

語句格式：

DECLAREcursor_name CURSOR

[ LOCAL | GLOBAL ]/*遊標作用域*/

[ FORWORD_ONLY |SCROLL ] /*遊標移動方向*/

[ STATIC | KEYSET| DYNAMIC | FAST_FORWARD ] /*遊標型別*/

[ READ_ONLY |SCROLL_LOCKS | OPTIMISTIC ] /*訪問屬性*/

[ TYPE_WARNING ]/*型別轉換警告資訊*/

FOR select_statement/*SELECT查詢語句*/

[ FOR UPDATE [ OFcolumn_name [ ,…n ] ] ] /*可修改的列*/

說明：

cursor_name: 為遊標名。

LOCAL與GLOBAL：說明遊標的作用域。

 

4.3.2 宣告遊標

FORWARD_ONLY和SCROLL：說明遊標的移動方向。FORWARD_ONLY表示遊標只能從第一行滾動到最後一行，即該遊標只能支援FETCH的NEXT提取選項。SCROLL含義與SQL-92標準中相同。

STATIC | KEYSET |DYNAMIC | FAST_FORWARD：用於定義遊標的型別，T-SQL擴充套件遊標有四種型別：

(1) 靜態遊標

關鍵字STATIC指定遊標為靜態遊標，它與SQL-92標準的INSENSITIVE關鍵字功能相同。靜態遊標的完整結果集在遊標開啟時建立在tempdb中，一旦開啟後，就不再變化。資料庫中所做的任何影響結果整合員的更改（包括增加、修改或刪除資料），都不會反映到遊標中，新的資料值不會顯示在靜態遊標中。靜態遊標只能是隻讀的。由於靜態遊標的結果集儲存在tempdb的工作表中，所以結果集中的行大小不能超過 SQL Server 表的最大行大小。有時也將這類遊標識別為快照遊標，它完全不受其他使用者行為的影響。

(2) 動態遊標

關鍵字DYNAMIC指定遊標為動態遊標。與靜態遊標不同，動態遊標能夠反映對結果集中所做的更改。結果集中的行資料值、順序和成員在每次提取時都會改變，所有使用者做的全部UPDATE、INSERT和DELETE語句均通過遊標反映出來，並且如果使用API函式（如SQLSetPos）或Transact-SQL WHERE CURRENT OF 子句通過遊標進行更新，則它們 也立即在遊標中反映出來，而在遊標外部所做的更新直到提交時才可見。動態遊標不支援 ABSOLUTE 提取選項。

 

4.3.2 宣告遊標

(3) 只進遊標

關鍵字FAST_FORWARD定義一個快速只進遊標，它是優化的只進遊標。只進遊標只支援遊標從頭到尾順序提取資料。對所有由當前使用者發出或由其它使用者提交、並影響結果集中的行的INSERT、UPDATE和 DELETE語句對資料的修改在從遊標中提取時可立即反映出來的。但因只進遊標不能向後滾動，所以在行提取後對行所做的更改對遊標是不可見的。

(4) 鍵集驅動遊標

關鍵字KEYSET定義一個鍵集驅動遊標。顧名思義，這種遊標是由稱為鍵的列或列的組合控制的。開啟鍵集驅動遊標時，其中的成員和行順序是固定的。鍵集驅動遊標中資料行的鍵值在遊標開啟時建立在 tempdb 中。可以通過鍵集驅動遊標修改基本表中的非關鍵字列的值，但不可插入資料。

遊標型別與移動方向之間的關係：

● FAST_FORWARD不能與SCROLL一起使用，且FAST_FORWARD與FORWARD_ONLY只能選用一個。

● 若指定了移動方向為FORWARD_ONLY，而沒有用STATIC、KETSET或DYNAMIC關鍵字指定遊標型別，則預設所定義的遊標為動態遊標。

● 若移動方向FORWARD_ONLY和SCROLL都沒有指定，那麼移動方向關鍵字的預設值由以下條件決定：① 若指定了遊標型別為STATIC、KEYSET或DYNAMIC，則移動方向預設為SCROLL；②若沒有用STATIC、KETSET或DYNAMIC關鍵字指定遊標型別，則移動方向預設值為FORWARD_ONLY。

 

4.3.2 宣告遊標

READ_ONLY |SCROLL_LOCKS | OPTIMISTIC：說明遊標或基表的訪問屬性。READ_ONLY說明所宣告的遊標為只讀的，不能通過該遊標更新資料。SCROLL_LOCKS關 鍵字說明通過遊標完成的定位更新或定位刪除可以成功。如果宣告中已指定了關鍵字 FAST_FORWARD，則不能指定SCROLL_LOCKS。OPTIMISTIC關鍵字說明如果行自從被 讀入遊標以來已得到更新，則通過遊標進行的定位更新或定位刪除不成功。如果宣告中已 指定了關鍵字FAST_FORWARD，則不能指定OPTIMISTIC。

TYPE_WARNING：指定如果遊標從所請求的型別隱性轉換為另一種型別，則給客戶端傳送警告訊息。

select_statement：SELECT查詢語句，由該查詢產生與所宣告的遊標相關聯的結果集。 該SELECT語句中不能出現COMPUTE、COMPUTE BY、INTO或FOR BROWSE關鍵字。

FOR UPDATE：指出遊標中可以更新的列，若有引數OF column_name [ ,…n ]，則只能修改給出的這些列，若在UPDATE中未指出列，則可以修改所有列。

以下是一個T-SQL擴充套件遊標宣告：

DECLARE XS_CUR2CURSOR

DYNAMIC

FOR

SELECT 學號,姓名,總學分

FROM XS

WHERE 專業名 = '計算機'

FOR UPDATE OF 總學分

該語句宣告一個名為XS_CUR2的動態遊標，可前後滾動，可對總學分列進行修改。

 

4.3.3 開啟遊標

宣告遊標後，要使用遊標從中提取資料，就必須先開啟遊標。在T-SQL中，使用OPEN語句開啟遊標，其格式為：

OPEN { { [ GLOBAL] cursor_name } | cursor_variable_name }

其中cursor_name是要開啟的遊標名，cursor_variable_name是遊標變數名，該名稱引用一個遊標。GLOBAL說明開啟的是全域性遊標，否則開啟區域性遊標。

OPEN語句開啟遊標，然後通過執行在DECLARE CURSOR（或 SETcursor_variable）語 句中指定的T-SQL語句填充遊標（即生成與遊標相關聯的結果集）。

例如，語句：

OPEN XS_CUR1

開啟遊標XS_CUR1。該遊標被開啟後，就可以提取其中的資料。

如果所開啟的是靜態遊標（使用 INSENSITIVE 或 STATIC 關鍵字），那麼OPEN將建立一個臨時表以儲存結果集。如果所開啟的是鍵集驅動遊標（使用KEYSET關鍵字），那麼OPEN將建立一個臨時表以儲存鍵集。臨時表都儲存在tempdb中。

開啟遊標後，可以使用全域性變數@@CURSOR_ROWS檢視遊標中資料行的數目。全域性變數@@CURSOR_ROWS中儲存著最後開啟的遊標中的資料行數。當其值為0時，表示沒有遊標開啟；當其值為-1時，表示遊標為動態的；當其值為-m（m為正整數）時，遊標採用非同步方式填充，m為當前鍵集中已填充的行數；當其值為m（m為正整數）時，遊標已被完全填充，m是遊標中的資料行數。

 

4.3.3 開啟遊標

【例4.66】定義遊標XS_CUR3，然後開啟該遊標，輸出其行數。

DECLARE XS_CUR3CURSOR

LOCAL SCROLLSCROLL_LOCKS

FOR

SELECT 學號,姓名,總學分

FROM XS

FOR UPDATE OF 總學分

OPEN XS_CUR3

SELECT '遊標XS_CUR3資料行數' =@@CURSOR_ROWS

結果為：

遊標XS_CUR3資料行數

19

說明：本例中的語句SELECT '遊標XS_CUR3資料行數' = @@CURSOR_ROWS用於為變數賦值。

 

4.3.4 讀取資料

遊標開啟後，就可以使用FETCH語句從中讀取資料。

語法格式：

FETCH

[ [ NEXT | PRIOR |FIRST | LAST | ABSOLUTE { n | @nvar } | RELATIVE { n | @nvar} ]

FROM ]

{ { [ GLOBAL ]cursor_name } | @cursor_variable_name }

[ INTO@variable_name [ ,…n ] ]

 

4.3.4 讀取資料

說明：

cursor_name：要從中提取資料的遊標名，@cursor_variable_name遊標變數名，引用要進行提取操作的已開啟的遊標。

NEXT | PRIOR |FIRST | LAST | ABSOLUTE | RELATIVE：用於說明讀取資料的位置。NEXT說明讀取當前行的下一行，並且使其置為當前行。如果FETCH NEXT是對遊標的第 一次提取操作，則讀取的是結果集第一行。NEXT為預設的遊標提取選項。PRIOR說明讀取當前行的前一行，並且使其置為當前行。如果FETCH PRIOR是對遊標的第一次提取操 作，則無值返回且遊標置於第一行之前。FIRST讀取遊標中的第一行並將其作為當前行。 LAST讀取遊標中的最後一行並將其作為當前行。

ABSOLUTE { n |@nvar }和RALATIVE { n | @nvar }給出讀取資料的位置與遊標頭或 當前位置的關係，其中n必須為整型常量，變數@nvar必須為 smallint、tinyint 或 int型別。

ABSOLUTE { n |@nvar }：若n或@nvar為正數，則讀取從遊標頭開始的第n行並將讀 取的行變成新的當前行；若n或@nvar為負數，則讀取遊標尾之前的第n行並將讀取的行變 成新的當前行；若n或@nvar 為 0，則沒有行返回。

RALATIVE { n |@nvar }：若n或@nvar為正數，則讀取當前行之後的第 n 行並將讀 取的行置新的當前行；若n或@nvar為負數，則讀取當前行之前的第n行並將讀取的行變成 新的當前行；如果n或@nvar為 0，則讀取當前行。如果對遊標的第一次提取操作時將 FETCH RELATIVE中的n或@nvar指定為負數或0，則沒有行返回。

 

4.3.4 讀取資料

INTO：說明將讀取的遊標資料存放到指定的變數中。

GLOBAL：全域性遊標。

【例4.67】從遊標XS_CUR1中提取資料。設該遊標已經開啟。

FETCH NEXT FROMXS_CUR1

結果為：

學號 姓名 性別 出生時間 總學分

081101王林 11990-02-10 00:00:0058

分析：由於XS_CUR1是隻進遊標，所以只能使用NEXT提取資料。

【例4.68】從遊標XS_CUR1中提取資料。設該遊標已經開啟。

FETCH FIRST FROMXS_CUR2

---讀取遊標第一行（當前行為第一行），結果為：

學號 姓名 總學分

081101王林 58

FETCH NEXT FROMXS_CUR2

--讀取下一行（當前行為第二行），結果為：

學號 姓名 總學分

081102程明 58

 

4.3.4 讀取資料

FETCH PRIOR FROMXS_CUR2

--讀取上一行（當前行為第一行），結果為：

學號 姓名 總學分

081101王林 58

FETCH LAST FROMXS_CUR2

--讀取最後一行（當前行為最後一行），結果為：

學號 姓名 總學分

081115劉明儀 58

FETCH RELATIVE -2FROM XS_CUR2

--讀取當前行的上二行（當前行為倒數第三行），結果為：

學號 姓名 總學分

081108林一帆 60

分析：XS_CUR2是動態遊標，可以前滾、後滾，可以使用FETCH語句中的除ABSOLUTE以外的提取選項。

FETCH語句的執行狀態儲存在全域性變數@@FETCH_STATUS中，其值為0，表示上一個FETCH執行成功；為-1，表示所要讀取的行不在結果集中；為-2，表示被提取的行已不存在（已被刪除）。可以測試@@FETCH_STATUS來控制迴圈。

 

4.3.4 讀取資料

例如接著上例繼續執行如下語句：

FETCH RELATIVE 3FROM XS_CUR2

SELECT 'FETCH執行情況' = @@FETCH_STATUS

結果為：

FETCH執行情況

-1

 

4.3.5 關閉遊標

遊標使用完以後，要及時關閉。關閉遊標使用CLOSE語句，格式為：

CLOSE { { [ GLOBAL] cursor_name } | @cursor_variable_name }

語句引數的含義與OPEN語句中相同。例如：

CLOSE XS_CUR2

將關閉遊標XS_CUR2。

 

4.3.6 刪除遊標

遊標關閉後，其定義仍在，需要時可用OPEN語句開啟它再使用。若確認遊標不再需要，就要釋放其定義佔用的系統空間，即刪除遊標。刪除遊標使用DEALLOCATE語句，格式為：

DEALLOCATE { { [GLOBAL ] cursor_name } | @cursor_variable_name }

語句引數的含義與OPEN和CLOSE語句中相同。例如：

DEALLOCATE XS_CUR2

將刪除遊標XS_CUR2。

 

習 題

1. 試說明SELECT語句的作用。說明SELECT語句的FROM、WHERE、GROUP及ORDER子句的作用。

2．查詢內容是多個欄位的運算結果，是否可以查詢？

3．查詢結果顯示的列名是否可以與欄位名不同？

4．查詢內容與多個表有關，表與表之間如何關聯？

5．經常需要查詢的內容是否可以用檢視完成？

6. 寫出T-SQL語句，對產品銷售數 據庫(CPXS)進行如下操作：

(1) 查詢單價在2000—2900元之間的商品名。

(2) 統計所有商品總數。

(3) 查詢2007年12月產品銷售總額。

(4) 查詢客戶名為“XXY”的客戶在2007年12月18日所購買的產品清單。

(5) 在產品銷售資料庫上建立冰箱產品表的檢視BXCP。

(6) 在BXCP檢視上查詢庫存量在100臺以下的產品編號和庫存量。

7．什麼時候需要使用遊標？使用遊標的步驟。