SQLServer數(shù)據(jù)庫的查詢和視圖技術(shù)課件

1、 第第4章章 數(shù)據(jù)庫的查詢和視圖數(shù)據(jù)庫的查詢和視圖4.1 數(shù)據(jù)庫的查詢數(shù)據(jù)庫的查詢4.2 視視 圖圖1學(xué)習(xí)課件4.1 數(shù)據(jù)庫的查詢數(shù)據(jù)庫的查詢下面介紹select語句，它是t-sql的核心。語法主體格式如下：select /*指定查詢結(jié)果輸出列*/ into 新表 /*指定查詢結(jié)果存入新表*/ from , . /*指定查詢源：表或視圖*/ where /*指定查詢條件*/group by /*指定查詢結(jié)果分組條件*/ having /*指定查詢結(jié)果分組統(tǒng)計(jì)條件*/ order by /*指定查詢結(jié)果排序順序*/2學(xué)習(xí)課件4.1.1 選擇查詢結(jié)果輸出列選擇查詢結(jié)果輸出列1選擇所有列選擇所有列使

2、用“*”表示選擇一個(gè)表或視圖中的所有列?！纠?.1】 查詢pxscj數(shù)據(jù)庫中xsb表的所有記錄。t-sql命令如下：use pxscjselect * from xsb3學(xué)習(xí)課件4.1.1 選擇查詢結(jié)果輸出列選擇查詢結(jié)果輸出列執(zhí)行結(jié)果如圖4.1所示。4學(xué)習(xí)課件4.1.1 選擇查詢結(jié)果輸出列選擇查詢結(jié)果輸出列2選擇一個(gè)表中指定的列選擇一個(gè)表中指定的列可選擇一個(gè)表中的部分列，各列名之間要以逗號分隔?！纠?.2】 查詢xsb表中計(jì)算機(jī)專業(yè)學(xué)生的學(xué)號、姓名和總學(xué)分。t-sql命令如下：select 學(xué)號, 姓名, 總學(xué)分from xsbwhere 專業(yè) = 計(jì)算機(jī)5學(xué)習(xí)課件4.1.1 選擇查詢結(jié)果輸出

3、列選擇查詢結(jié)果輸出列3定義列別名定義列別名當(dāng)希望查詢結(jié)果中的列使用自己選擇的列標(biāo)題時(shí)，可以as更改列標(biāo)題名，該列標(biāo)題稱為該列的別名?！纠?.3】 查詢xsb表中計(jì)算機(jī)系同學(xué)的學(xué)號、姓名和總學(xué)分，查詢結(jié)果中各列的標(biāo)題分別指定為number、name和mark。t-sql命令如下：select 學(xué)號 as number, 姓名 as name, 總學(xué)分 as markfrom xsbwhere 專業(yè)= 計(jì)算機(jī)執(zhí)行結(jié)果如圖4.2所示。6學(xué)習(xí)課件4.1.1 選擇查詢結(jié)果輸出列選擇查詢結(jié)果輸出列也可以使用“列別名=表達(dá)式”更改列標(biāo)題。例如：select number = 學(xué)號, name = 姓名, m

4、ark = 總學(xué)分from xsbwhere 專業(yè)= 計(jì)算機(jī)當(dāng)自定義的列標(biāo)題中含有空格時(shí)，必須使用引號將標(biāo)題括起來。例如：select student number = 學(xué)號,姓名 as student name, mark = 總學(xué)分from xsbwhere 專業(yè)= 計(jì)算機(jī)7學(xué)習(xí)課件4.1.1 選擇查詢結(jié)果輸出列選擇查詢結(jié)果輸出列4替換查詢結(jié)果中的數(shù)據(jù)替換查詢結(jié)果中的數(shù)據(jù)在對表進(jìn)行查詢時(shí)，有時(shí)希望對所查詢的某些列得到的數(shù)據(jù)進(jìn)行變換。要替換查詢結(jié)果中的數(shù)據(jù)，則可使用case表達(dá)式，格式為：case when 條件1 then 表達(dá)式1 when 條件2 then 表達(dá)式2 else 表達(dá)式e

5、nd8學(xué)習(xí)課件4.1.1 選擇查詢結(jié)果輸出列選擇查詢結(jié)果輸出列【例4.4】 查詢xsb表中計(jì)算機(jī)系各同學(xué)的學(xué)號、姓名和總學(xué)分，對其總學(xué)分按以下規(guī)則進(jìn)行替換，列標(biāo)題更改為“等級”。若總學(xué)分為空值，則替換為“尚未選課”；若總學(xué)分小于50，則替換為“不及格”；若總學(xué)分在50與52之間，則替換為“合格”；若總學(xué)分大于52，則替換為“優(yōu)秀”。t-sql命令如下：select 學(xué)號, 姓名, 等級= case when 總學(xué)分 is null then 尚未選課when 總學(xué)分 =50 and 總學(xué)分50分人數(shù) from xsb where 總學(xué)分50 ;go19學(xué)習(xí)課件4.1.2 選擇查詢條件：選擇查詢

6、條件：where子句子句1表達(dá)式比較表達(dá)式比較比較運(yùn)算符用于比較兩個(gè)表達(dá)式值，共有9個(gè)，分別是 =（等于）、（小于）、（大于）、=（大于等于）、（不等于）、!=（不等于）、!（不大于）。比較運(yùn)算的格式為：表達(dá)式1 比較運(yùn)算符 表達(dá)式2其中，“表達(dá)式”是除text、ntext和image以外類型的表達(dá)式?！纠?.11】 查詢xsb表中通信工程專業(yè)總學(xué)分大于等于42的同學(xué)的情況。t-sql命令如下：select *from xsbwhere 專業(yè)= 通信工程 and 總學(xué)分 = 4220學(xué)習(xí)課件4.1.2 選擇查詢條件：選擇查詢條件：where子句子句2模式匹配模式匹配like謂詞用于指出字符串是

7、否與指定的字符串相匹配，返回邏輯值true或false。格式為：表達(dá)式 not like 模式串 escape 轉(zhuǎn)義符 說明：說明：（1）表達(dá)式：一般為字符串表達(dá)式，在查詢語句中可以是列名。（2）模式串：可以使用通配符，表4.2列出了like謂詞可以使用的通配符及其說明。（3）轉(zhuǎn)義符：應(yīng)為有效的sql server字符，沒有默認(rèn)值，且必須為單個(gè)字符。當(dāng)模式串中含有與通配符相同的字符時(shí)，應(yīng)通過該字符前的轉(zhuǎn)義符指明其為模式串中的一個(gè)匹配字符。使用escape可指定轉(zhuǎn)義符。（4）not like：使用not like與like的作用相反。通 配 符說 明%代表倒數(shù)_（下畫線）代表單個(gè)字符 指定范圍（

8、如a-f、0-9）或集合（如abcdef）中的任何單個(gè)字符指定不屬于范圍（如 a-f、0-9）或集合（如abcdef）的任何單個(gè)字符21學(xué)習(xí)課件4.1.2 選擇查詢條件：選擇查詢條件：where子句子句【例4.12】 查詢xsb表中姓“王”且單名的學(xué)生情況。t-sql命令如下，執(zhí)行結(jié)果如圖4.9所示。select *from xsbwhere 姓名 like 王_ 22學(xué)習(xí)課件4.1.2 選擇查詢條件：選擇查詢條件：where子句子句【例4.13】 查詢xsb表中學(xué)號倒數(shù)第5個(gè)數(shù)字為9，且倒數(shù)第1個(gè)數(shù)在15之間的學(xué)生學(xué)號、姓名及專業(yè)。t-sql命令如下，執(zhí)行結(jié)果如圖4.10所示。select

9、學(xué)號,姓名,專業(yè) from xsb where 學(xué)號 like %9_ _ _1-5 23學(xué)習(xí)課件4.1.2 選擇查詢條件：選擇查詢條件：where子句子句【例4.13】 查詢xsb表姓名中倒數(shù)第2個(gè)字為玉的學(xué)生學(xué)號、姓名及專業(yè)。t-sql命令如下，執(zhí)行結(jié)果如圖4.10所示。select 學(xué)號,姓名,專業(yè) from xsb where 學(xué)號 like %玉_24學(xué)習(xí)課件4.1.2 選擇查詢條件：選擇查詢條件：where子句子句3范圍比較范圍比較（1）當(dāng)要查詢的條件是某個(gè)值的范圍時(shí)，可以使用between關(guān)鍵字。between關(guān)鍵字指出查詢范圍，格式為：表達(dá)式 not between 表達(dá)式1

10、and 表達(dá)式2當(dāng)不使用not時(shí)，若“表達(dá)式”的值在“表達(dá)式1”與“表達(dá)式2”之間（包括這兩個(gè)值），則返回true，否則返回false；當(dāng)使用not時(shí)，返回值剛好相反?！纠?.14】 查詢xsb表中不在1995年出生的學(xué)生情況。t-sql命令如下，執(zhí)行結(jié)果如圖4.11所示。select 學(xué)號, 姓名, 專業(yè), 出生時(shí)間 from xsb where 出生時(shí)間 not between 1995-1-1 and 1995-12-31也可使用下列命令：select 學(xué)號, 姓名, 專業(yè), 出生時(shí)間 from xsb where where year(出生時(shí)間)!=199525學(xué)習(xí)課件4.1.2 選擇

11、查詢條件：選擇查詢條件：where子句子句圖4.11 執(zhí)行結(jié)果26學(xué)習(xí)課件4.1.2 選擇查詢條件：選擇查詢條件：where子句子句（2）使用in關(guān)鍵字可以指定一個(gè)值表，值表中列出所有可能的值，當(dāng)與值表中的任一個(gè)匹配時(shí)，即返回true，否則返回false。格式為：表達(dá)式 in ( 表達(dá)式 , )【例4.15】 查詢xsb表中專業(yè)為“軟件工程”或“通信工程”學(xué)生情況。t-sql命令如下：select *from xsbwhere 專業(yè) in (軟件工程, 通信工程)下列t-sql命令等價(jià)：select *from xsbwhere 專業(yè)= 計(jì)算機(jī) or 專業(yè)= 通信工程 27學(xué)習(xí)課件4.1.2

12、選擇查詢條件：選擇查詢條件：where子句子句4空值比較空值比較當(dāng)需要判定一個(gè)表達(dá)式的值是否為空值時(shí)，使用is null關(guān)鍵字，格式為：表達(dá)式 is not null當(dāng)不使用not時(shí)，若表達(dá)式的值為空值，則返回true，否則返回false；當(dāng)使用not時(shí)，結(jié)果剛好相反。【例4.16】 查詢總學(xué)分尚不定的學(xué)生情況。select *from xsbwhere 備注 is null28學(xué)習(xí)課件4.1.2 選擇查詢條件：選擇查詢條件：where子句子句5子查詢子查詢1）in子查詢子查詢in子查詢用于進(jìn)行一個(gè)給定值是否在子查詢結(jié)果集中的判斷，格式為：表達(dá)式 not in ( 子查詢 )當(dāng)表達(dá)式與子查詢的

13、結(jié)果表中的某個(gè)值相等時(shí)，in謂詞返回true，否則返回false；若使用了not，則返回的值剛好相反?！纠?.17】 查詢選修了課程號為206的課程的學(xué)生情況。t-sql命令如下：select * from xsbwhere 學(xué)號 in (select 學(xué)號 from cjb where 課程號 = 206)本例中，先執(zhí)行子查詢：select 學(xué)號from cjb where 課程名 = 20629學(xué)習(xí)課件4.1.2 選擇查詢條件：選擇查詢條件：where子句子句【例4.18】 查詢未選修離散數(shù)學(xué)的學(xué)生學(xué)號和姓名。t-sql命令如下：select 學(xué)號, 姓名from xsbwhere 學(xué)號

14、not in( select 學(xué)號 from cjb where 課程號 in(select 課程號 from kcb where 課程名 = 離散數(shù)學(xué) )30學(xué)習(xí)課件4.1.2 選擇查詢條件：選擇查詢條件：where子句子句2）比較子查詢）比較子查詢這種子查詢可以認(rèn)為是in子查詢的擴(kuò)展，它使表達(dá)式的值與子查詢的結(jié)果進(jìn)行比較運(yùn)算，格式為：表達(dá)式 比較運(yùn)算符 all | some | any ( 子查詢 )其中，all、some和any說明對比較運(yùn)算的限制。【例4.19】 查詢選修了離散數(shù)學(xué)的學(xué)生學(xué)號和姓名。t-sql命令如下：select 學(xué)號from cjbwhere 課程號 = ( sel

15、ect 課程號 from kcb where 課程名 =離散數(shù)學(xué));31學(xué)習(xí)課件4.1.2 選擇查詢條件：選擇查詢條件：where子句子句【例4.20】 查詢比所有計(jì)算機(jī)系的學(xué)生年齡都大的學(xué)生。t-sql命令如下：select * from xsbwhere 出生時(shí)間 all( select 出生時(shí)間from xsbwhere 專業(yè)= 計(jì)算機(jī))【例4.21】 查詢206課程號成績不低于101課程號最低成績的學(xué)生學(xué)號。t-sql命令如下：select 學(xué)號 from cjbwhere 課程號 = 206 and 成績 !=50) as student where 性別=1執(zhí)行結(jié)果如圖4.13所示

16、。38學(xué)習(xí)課件4.1.3 指定查詢對象：指定查詢對象：from子句子句【例4.28】 在xsb表中查詢在1995年1月1日以前出生的學(xué)生的姓名和專業(yè)。t-sql命令如下，執(zhí)行結(jié)果如圖4.14所示。select , student.specialityfrom ( select * from xsb where 出生時(shí)間= 8042學(xué)習(xí)課件4.1.4 連接連接（2）多表連接。）多表連接。有時(shí)，用戶所需要的字段來自兩個(gè)以上的表，那么就要對兩個(gè)以上的表進(jìn)行連接，稱之為多表連接。【例4.32】 查詢選修了“計(jì)算機(jī)基礎(chǔ)”課程且成績在80分以上的學(xué)生學(xué)號、姓名、課程名及成績。t-s

17、ql命令如下，執(zhí)行結(jié)果如圖4.16所示。select xsb.學(xué)號, 姓名, 課程名, 成績from xsb , kcb , cjbwhere xsb.學(xué)號 = cjb.學(xué)號 and kcb.課程號 = cjb.課程號and 課程名 = 計(jì)算機(jī)基礎(chǔ) and 成績 = 8043學(xué)習(xí)課件4.1.4 連接連接2以以join關(guān)鍵字指定的連接關(guān)鍵字指定的連接t-sql擴(kuò)展了以join關(guān)鍵字指定連接的表示方式，使表的連接運(yùn)算能力有所增強(qiáng)。from子句的表示將多個(gè)表連接起來。格式如下： := on | cross join | 左表源 cross | outer apply 右表源| ( ) 44學(xué)習(xí)課件4

18、.1.4 連接連接說明：（1）：準(zhǔn)備要連接的表。（2）：表示連接類型。格式為： := inner | left | right | full outer join其中，inner表示內(nèi)連接，outer表示外連接。（3）on：用于指定連接條件，為連接的條件。（4）apply運(yùn)算符：使用apply運(yùn)算符可以為實(shí)現(xiàn)查詢操作的外部表表達(dá)式返回的每個(gè)行調(diào)用表值函數(shù)。（5）cross join：表示交叉連接。45學(xué)習(xí)課件4.1.4 連接連接因此，以join關(guān)鍵字指定的連接有三種類型：內(nèi)連接、外連接、交叉連接。 內(nèi)連接。指定了inner關(guān)鍵字的連接是內(nèi)連接，內(nèi)連接按照on所指定的連接條件合并兩個(gè)表，返回滿足

19、條件的行?！纠?.33】 查詢每個(gè)學(xué)生的情況以及選修的課程情況。t-sql命令如下：select *from xsb inner join cjb on xsb.學(xué)號 =cjb.學(xué)號執(zhí)行的結(jié)果將包含xsb表和cjb表的所有字段（不去除重復(fù)字段學(xué)號）?！纠?.34】查詢選修了206課程且成績在80分以上的學(xué)生姓名及成績。t-sql命令如下：select 姓名, 成績from xsb join cjb on xsb.學(xué)號 = cjb.學(xué)號 where 課程號 = 206 and 成績=8046學(xué)習(xí)課件4.1.4 連接連接【例4.35】 查詢選修了“計(jì)算機(jī)基礎(chǔ)”課程且成績在80分以上的學(xué)生學(xué)號、姓名

20、、課程名及成績。t-sql命令如下：select xsb.學(xué)號, 姓名, 課程名, 成績from xsb join cjb join kcb on cjb.課程號 = kcb.課程號 on xsb.學(xué)號 = cjb.學(xué)號 where 課程名= 計(jì)算機(jī)基礎(chǔ) and 成績=80 作為一種特例，可以將一個(gè)表與它自身進(jìn)行連接，稱為自連接。【例4.36】 查詢不同課程成績相同的學(xué)生的學(xué)號、課程號和成績。t-sql命令如下：select a.學(xué)號, a.課程號, b.課程號, a.成績from cjb a join cjb b on a.成績=b.成績 and a.學(xué)號=b.學(xué)號 and a.課程號!=b

21、.課程號執(zhí)行結(jié)果如圖4.17所示。47學(xué)習(xí)課件4.1.4 連接連接 外連接。指定了outer關(guān)鍵字的為外連接，外連接的結(jié)果表不但包含滿足連接條件的行，還包括相應(yīng)表中的所有行。外連接包括以下三種： left outer join（左外連接）：結(jié)果表中除了包括滿足連接條件的行外，還包括左表的所有行。 right outer join（右外連接）：結(jié)果表中除了包括滿足連接條件的行外，還包括右表的所有行。 full outer join（完全外連接）：結(jié)果表中除了包括滿足連接條件的行外，還包括兩個(gè)表的所有行。48學(xué)習(xí)課件4.1.4 連接連接【例4.37】 查詢所有學(xué)生情況，以及他們選修的課程號。t-s

22、ql命令如下，執(zhí)行結(jié)果如圖4.18所示。select xsb.* , 課程號 from xsb left outer join cjb on xsb.學(xué)號 = cjb.學(xué)號49學(xué)習(xí)課件4.1.4 連接連接【例4.38】 查詢被選修了的課程的選修情況和所有開設(shè)的課程名。select cjb.* , 課程名from cjb right join kcb on cjb.課程號= kcb.課程號50學(xué)習(xí)課件4.1.4 連接連接 交叉連接。交叉連接實(shí)際上是將兩個(gè)表進(jìn)行笛卡兒積運(yùn)算，結(jié)果表是由第一個(gè)表的每一行與第二個(gè)表的每一行拼接后形成的表，因此結(jié)果表的行數(shù)等于兩個(gè)表的行數(shù)之積?！纠?.39】 列出學(xué)生所

23、有可能的選課情況。t-sql命令如下，執(zhí)行結(jié)果如圖4.19所示。select 學(xué)號, 姓名, 課程號, 課程名from xsb cross join kcb51學(xué)習(xí)課件4.1.5 指定查詢結(jié)果分組方法：指定查詢結(jié)果分組方法：group by子子句句這里介紹iso標(biāo)準(zhǔn)的group by子句。語法格式如下：group by| rollup ( )| cube ()| grouping sets ( )52學(xué)習(xí)課件4.1.5 指定查詢結(jié)果分組方法：指定查詢結(jié)果分組方法：group by子子句句【例4.40】 在pxscj數(shù)據(jù)庫xsb表中分別用group by rollup、group by cube

24、和group by grouping sets對（專業(yè),性別）產(chǎn)生一個(gè)結(jié)果集。（1）采用）采用group by rollup。select 專業(yè), 性別 , count(*) as 人數(shù)from xsbgroup by rollup(專業(yè),性別)（2）采用）采用group by cube。select 專業(yè), 性別, count(*) as 人數(shù)from xsbgroup by cube(專業(yè),性別) 53學(xué)習(xí)課件4.1.5 指定查詢結(jié)果分組方法：指定查詢結(jié)果分組方法：group by子句子句（3）采用）采用group by grouping sets。執(zhí)行結(jié)果如圖4.20所示。select

25、專業(yè), 性別, count(*) as 人數(shù)from xsbgroup by grouping sets(專業(yè),性別) (a) (b) (c) 圖4.20 執(zhí)行結(jié)果 54學(xué)習(xí)課件4.1.6 指定查詢結(jié)果分組后篩選條件：指定查詢結(jié)果分組后篩選條件：having子句子句使用group by子句和聚合函數(shù)對數(shù)據(jù)進(jìn)行分組后，還可以使用having子句對分組數(shù)據(jù)做進(jìn)一步的篩選。格式為： having 其中，與where子句的查詢條件類似，不過having子句中可以使用聚合函數(shù)，而where子句中不可以?！纠?.41】 查詢平均成績在85分以上的學(xué)生的學(xué)號和平均成績。t-sql命令如下，執(zhí)行結(jié)果如圖4.2

26、1所示。select 學(xué)號, avg(成績) as 平均成績from cjbgroup by 學(xué)號having avg(成績) =8555學(xué)習(xí)課件4.1.6 指定查詢結(jié)果分組后篩選條件：指定查詢結(jié)果分組后篩選條件：having子句子句【例4.42】 查詢選修課程超過2門且成績都在80分以上的學(xué)生的學(xué)號。t-sql命令如下：select 學(xué)號 from cjbwhere 成績 = 80group by 學(xué)號 having count(*) 256學(xué)習(xí)課件4.1.6 指定查詢結(jié)果分組后篩選條件：指定查詢結(jié)果分組后篩選條件：having子句子句【例4.43】 查詢通信工程專業(yè)平均成績在85分以上的學(xué)

27、生的學(xué)號和平均成績。t-sql命令如下，執(zhí)行結(jié)果如圖4.22所示。select 學(xué)號, avg(成績) as 平均成績from cjbwhere 學(xué)號 in (select 學(xué)號from xsbwhere 專業(yè) = 通信工程)group by 學(xué)號 having avg(成績) =8557學(xué)習(xí)課件4.1.7 指定查詢結(jié)果排序順序：指定查詢結(jié)果排序順序：order by子子句句在應(yīng)用中經(jīng)常要對查詢的結(jié)果排序輸出，如將學(xué)生成績由高到低排序。在select語句中，使用order by子句對查詢結(jié)果進(jìn)行排序。格式為： order by排序表達(dá)式 collate 排序名 asc | desc 58學(xué)習(xí)課

28、件4.1.7 指定查詢結(jié)果排序順序：指定查詢結(jié)果排序順序：order by子子句句【例4.44】 將通信工程專業(yè)的學(xué)生按出生時(shí)間先后順序排序。t-sql命令如下，執(zhí)行結(jié)果如圖4.23所示。select *from xsbwhere 專業(yè)= 通信工程order by 出生時(shí)間59學(xué)習(xí)課件4.1.7 指定查詢結(jié)果排序順序：指定查詢結(jié)果排序順序：order by子子句句【例4.45】 將計(jì)算機(jī)專業(yè)學(xué)生的“計(jì)算機(jī)基礎(chǔ)”課程成績按降序排列。t-sql命令如下，執(zhí)行結(jié)果如圖4.24所示。select 姓名, 課程名, 成績from xsb, kcb, cjbwhere xsb.學(xué)號 = cjb.學(xué)號 an

29、d cjb.課程號 = kcb.課程號 and 課程名= 計(jì)算機(jī)基礎(chǔ)and 專業(yè)= 計(jì)算機(jī)60學(xué)習(xí)課件4.1.8 select語句的其他語法語句的其他語法1into使用into子句可以將select查詢所得的結(jié)果保存到一個(gè)新建的表中。into子句的格式為： into 新表 其中，“新表”是要?jiǎng)?chuàng)建的新表名?！纠?.46】 由xsb表創(chuàng)建“計(jì)算機(jī)學(xué)生”表，包括學(xué)號和姓名。t-sql命令如下：select 學(xué)號, 姓名into 計(jì)算機(jī)學(xué)生from xsbwhere 專業(yè)= 計(jì)算機(jī)61學(xué)習(xí)課件4.1.8 select語句的其他語法語句的其他語法 同樣再創(chuàng)建一個(gè)“通信工程學(xué)生”表。完成后，刷新表后的對象

30、資源管理器如圖4.25所示。62學(xué)習(xí)課件4.1.8 select語句的其他語法語句的其他語法2union使用union子句可以將兩個(gè)或多個(gè)select查詢的結(jié)果合并成一個(gè)結(jié)果集，其格式為： | ( ) union all | ( ) union all | ( ) 其中，和都是select查詢語句。使用union組合兩個(gè)查詢的結(jié)果集的基本規(guī)則是：（1）所有查詢中的列數(shù)和列的順序必須相同。（2）數(shù)據(jù)類型必須兼容。63學(xué)習(xí)課件4.1.8 select語句的其他語法語句的其他語法【例4.47】 在“計(jì)算機(jī)學(xué)生”表和“通信工程學(xué)生”表中查詢學(xué)號為191301和學(xué)號為221301的兩位同學(xué)的姓名。t-s

31、ql命令如下：select *from 計(jì)算機(jī)學(xué)生where 學(xué)號= 191301union allselect *from 通信工程學(xué)生where 學(xué)號= 221301執(zhí)行結(jié)果如圖4.26所示。64學(xué)習(xí)課件4.1.8 select語句的其他語法語句的其他語法3except和和intersectexcept和intersect用于比較兩個(gè)查詢的結(jié)果，返回非重復(fù)值。語法格式如下： | ( ) except | intersect | ( ) 其中，和都是select查詢語句?！纠?.48】 查詢計(jì)算機(jī)專業(yè)女學(xué)生的信息。t-sql命令如下：select * from xsb where 專業(yè)= 計(jì)

32、算機(jī)exceptselect * from xsb where 性別=1執(zhí)行結(jié)果如圖4.27所示。65學(xué)習(xí)課件4.1.8 select語句的其他語法語句的其他語法【例4.49】 查詢總學(xué)分大于42的男學(xué)生信息。t-sql命令如下：select * from xsb where 總學(xué)分42intersectselect * from xsb where 性別=166學(xué)習(xí)課件4.1.8 select語句的其他語法語句的其他語法4cte在select語句的最前面可以使用一條with子句來指定臨時(shí)結(jié)果集，語法格式如下： with , . select .其中：:=表達(dá)式名 ( 列名 , . ) as

33、( cte查詢定義 )67學(xué)習(xí)課件4.1.8 select語句的其他語法語句的其他語法【例4.50】 使用cte從cjb表中查詢選了101課程的學(xué)生學(xué)號、成績，并定義新的列名為number、point。再使用select語句從cte和xsb中查詢姓名為“王林”的學(xué)生學(xué)號和成績情況。t-sql命令如下，執(zhí)行結(jié)果如圖4.28所示。use pxscjgowith cte_stu(number,point)as (select 學(xué)號,成績 from cjb where 課程號=101)select number, point from cte_stu, xsb where xsb.姓名=王林 and

34、xsb.學(xué)號=cte_stu.number68學(xué)習(xí)課件4.2 視視 圖圖4.2.1 視圖概念視圖概念視圖一經(jīng)定義以后，就可以像表一樣被查詢、修改、刪除和更新。使用視圖有下列優(yōu)點(diǎn)：（1）為用戶集中數(shù)據(jù)，簡化用戶的數(shù)據(jù)查詢和處理。有時(shí)，用戶所需要的數(shù)據(jù)分散在多個(gè)表中，定義視圖可將它們集中在一起，從而方便用戶進(jìn)行數(shù)據(jù)查詢和處理。（2）屏蔽數(shù)據(jù)庫的復(fù)雜性。用戶不必了解復(fù)雜的數(shù)據(jù)庫中的表結(jié)構(gòu)，并且數(shù)據(jù)庫表的更改也不影響用戶對數(shù)據(jù)庫的使用。（3）簡化用戶權(quán)限的管理。只需授予用戶使用視圖的權(quán)限，而不必指定用戶只能使用表的特定列，也增加了安全性。（4）便于數(shù)據(jù)共享。各用戶不必都定義和存儲自己所需的數(shù)據(jù)，而可共

35、享數(shù)據(jù)庫的數(shù)據(jù)，這樣，同樣的數(shù)據(jù)只需存儲一次。（5）可以重新組織數(shù)據(jù)以便輸出到其他應(yīng)用程序中。69學(xué)習(xí)課件4.2.2 創(chuàng)建視圖創(chuàng)建視圖1通過界面創(chuàng)建視圖通過界面創(chuàng)建視圖下面以在一個(gè)數(shù)據(jù)庫中創(chuàng)建視圖為例說明在“ssms”中創(chuàng)建視圖的過程?！纠?.51】 在pxscj數(shù)據(jù)庫學(xué)生表(xsb)中創(chuàng)建名為cxs視圖，視圖中僅僅包含計(jì)算機(jī)專業(yè)學(xué)生。1）創(chuàng)建名）創(chuàng)建名cxs視圖視圖步驟如下：（1）在“對象資源管理器”中展開“數(shù)據(jù)庫”“pxscj”，選擇其中的“視圖”項(xiàng)，右擊鼠標(biāo)，在彈出的快捷菜單上選擇“新建視圖”菜單項(xiàng)。（2）在隨后出現(xiàn)的“添加表”對話框中，添加視圖所需要關(guān)聯(lián)的對象。這里在“表”選項(xiàng)卡中選擇

36、“xsb”表，單擊“添加”按鈕，單擊“關(guān)閉”按鈕。70學(xué)習(xí)課件4.2.2 創(chuàng)建視圖創(chuàng)建視圖（3）在窗口下部“輸出”欄中去除創(chuàng)建的視圖中不所需的字段，在“排序類型”欄中指定列的排序方式，在“篩選器”欄中指定創(chuàng)建視圖的規(guī)則。本例在“專業(yè)”字段的“篩選器”欄中填寫“計(jì)算機(jī)”，如圖4.29所示。（4）完成后，單擊面板上的“保存”按鈕，在“保存視圖”對話框中輸入視圖名“cxs”，并單擊“確定”按鈕，便完成了視圖的創(chuàng)建。71學(xué)習(xí)課件4.2.2 創(chuàng)建視圖創(chuàng)建視圖2）顯示）顯示cxs視圖視圖刷新pxscj數(shù)據(jù)庫下的視圖，展開“數(shù)據(jù)庫”“pxscj”“視圖”，選擇“dbo.cxs”，右擊鼠標(biāo)，在彈出的快捷菜單中

37、選擇“設(shè)計(jì)”菜單項(xiàng)，可以查看并修改視圖結(jié)構(gòu)，選擇“編輯前200行”菜單項(xiàng)，將可查看視圖數(shù)據(jù)，如圖4.30所示。72學(xué)習(xí)課件4.2.2 創(chuàng)建視圖創(chuàng)建視圖3）視圖的好處）視圖的好處創(chuàng)建視圖可以向最終用戶隱藏復(fù)雜的表連接，簡化了用戶的sql程序設(shè)計(jì)。通過在創(chuàng)建視圖時(shí)指定限制條件和指定列來限制用戶對基本表的訪問。例如，若限定某用戶只能查詢視圖cxs，實(shí)際上就是限制了它只能訪問xsb表的專業(yè)字段值為“計(jì)算機(jī)”的行。73學(xué)習(xí)課件4.2.2 創(chuàng)建視圖創(chuàng)建視圖2通過命令創(chuàng)建視圖通過命令創(chuàng)建視圖t-sql中用于創(chuàng)建視圖的語句是create view語句，其格式為：create view 視圖名 with , .

38、 as select 語句 ; with check option 1）語句主體）語句主體主體結(jié)構(gòu)說明如下。（1）架構(gòu)名：數(shù)據(jù)庫架構(gòu)名。（2）列名：視圖中包含的列，可以有多個(gè)列名。若使用與源表或視圖相同的列名，則不必給出列名。（3）with ：指出視圖的屬性。（4）select語句：用來創(chuàng)建視圖的語句，源表可以是基本表，也可以是視圖。（5）with check option：指出在視圖上所進(jìn)行的修改都要符合select語句所指定的限制條件，這樣可以確保數(shù)據(jù)修改后，仍可通過視圖看到修改的數(shù)據(jù)。74學(xué)習(xí)課件4.2.2 創(chuàng)建視圖創(chuàng)建視圖2）定義定義定義的具體格式如下： := encryption s

39、chemabinding view_metadata （1）encryption：在系統(tǒng)表syscomments中存儲create view語句時(shí)進(jìn)行加密。（2）schemabinding：將視圖與其所依賴的表或視圖結(jié)構(gòu)相關(guān)聯(lián)。（3）view_metadata：當(dāng)引用視圖的瀏覽模式的元數(shù)據(jù)時(shí)，向dblib、odbc或oledb api返回有關(guān)視圖的元數(shù)據(jù)信息，而不返回基表的元數(shù)據(jù)信息。75學(xué)習(xí)課件4.2.2 創(chuàng)建視圖創(chuàng)建視圖【例4.52】 創(chuàng)建ccj視圖，包括計(jì)算機(jī)專業(yè)各學(xué)生的學(xué)號、其選修的課程號及成績。要保證對該視圖的修改都符合專業(yè)為計(jì)算機(jī)的這個(gè)條件。t-sql命令如下：create vie

40、w ccj with encryptionas select xsb.學(xué)號,課程號,成績from xsb, cjbwhere xsb.學(xué)號=cjb.學(xué)號 and 專業(yè) = 計(jì)算機(jī)with check option【例4.53】 創(chuàng)建計(jì)算機(jī)專業(yè)學(xué)生的平均成績視圖ccj_avg，包括學(xué)號（在視圖中列名為num）和平均成績（在視圖中列名為score_avg）。t-sql命令如下：create view ccj_avg(num, score_avg)asselect 學(xué)號, avg(成績)from ccjgroup by 學(xué)號76學(xué)習(xí)課件4.2.2 創(chuàng)建視圖創(chuàng)建視圖3）分區(qū)視圖）分區(qū)視圖在一般情況下，

41、如果視圖為下列格式，則稱其為分區(qū)視圖。create view 視圖名asselect from t1union allselect from t2union all.select from tn77學(xué)習(xí)課件4.2.2 創(chuàng)建視圖創(chuàng)建視圖3視圖查詢視圖查詢視圖定義后，就可以像查詢基本表那樣對視圖進(jìn)行查詢了?！纠?.54】 顯示ccj和ccj_avg視圖中的內(nèi)容。t-sql命令如下：select * from ccj執(zhí)行結(jié)果如圖4.31(a)所示。select * from ccj_avgwhere score_avg = 75執(zhí)行結(jié)果如圖4.31(b)所示。 78學(xué)習(xí)課件4.2.3 更新視圖更新視

42、圖1可更新視圖可更新視圖（1）滿足以下條件的視圖。）滿足以下條件的視圖。 創(chuàng)建視圖的select語句中沒有聚合函數(shù)，且沒有top、group by、union子句及distinct關(guān)鍵字。 創(chuàng)建視圖的select語句中不包含從基本表列通過計(jì)算所得的列。 創(chuàng)建視圖的select語句的from子句中至少要包含一個(gè)基本表。（2）可更新的分區(qū)視圖。）可更新的分區(qū)視圖。在實(shí)現(xiàn)分區(qū)視圖之前，必須先實(shí)現(xiàn)水平分區(qū)表。原始表被分成若干個(gè)較小的成員表，每個(gè)成員表包含與原始表相同數(shù)量的列，并且每一列具有與原始表中的相應(yīng)列同樣的特性（如數(shù)據(jù)類型、大小、排序規(guī)則）。（3）通過）通過instead of觸發(fā)器創(chuàng)建的可更新視圖。觸發(fā)器創(chuàng)建的可更新視圖。79學(xué)習(xí)課件4.2.3 更新視圖更新視圖2插入數(shù)據(jù)使用insert語句通過視圖向基本表插入數(shù)據(jù)?！纠?.55】 向cxs視圖中插入以下記錄：191315, 劉明儀, 1, 1996-3-2, 50 t-sql命令如下：insert into cxsvalues(191315, 劉明儀, 1,1996-3-2, 50 )執(zhí)行結(jié)果如圖4.32所示。80學(xué)習(xí)課件4.2.3 更新視圖更新視圖3修改數(shù)據(jù)修改數(shù)據(jù)使用update語句可以通過視圖修改基本表的數(shù)據(jù)。（1）對一個(gè)基本表視圖修改數(shù)據(jù)。）對一個(gè)基本表視圖修改數(shù)據(jù)?！纠?.56】 將cxs視圖中所