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

1、第4章 數(shù)據(jù)庫查詢和視圖,4.1 關(guān)系運(yùn)算,4.2 數(shù)據(jù)庫查詢,4.3 視圖,4.4 游標(biāo),4.1 關(guān)系運(yùn)算,1. 選擇（Selection） 例如，若要在T表（表4.1）中找出T120的行形成一個(gè)新表，則運(yùn)算式為 F(T) 上式中F：T120，該選擇運(yùn)算的結(jié)果如表4.2所示。,4.1 關(guān)系運(yùn)算,2. 投影（Projection） 例如，在T表中對T1、T2和T5投影，運(yùn)算式為 T1,T2,T5(T) 該運(yùn)算得到如表4.3所示的新表。,4.1 關(guān)系運(yùn)算,3. 連接（Join） 例如，若表A和B分別如表4.4和表4.5所示，則 如表4.6所示，其中，F(xiàn)為T1=T3。,4.1 關(guān)系運(yùn)算,例如，若表

2、A和B分別如表4.7和表4.8所示，則 如表4.9所示。,4.2 數(shù)據(jù)庫查詢,4.2.1 選擇列 通過SELECT語句的項(xiàng)組成結(jié)果表的列。語法格式如下： := * /*選擇當(dāng)前表或視圖的所有列*/ | table_name | view_name | table_alias .* /*選擇指定的表或視圖的所有列*/ | column_name | expression | $IDENTITY /*選擇指定的列*/ | expression AS column_alias /*AS子句，定義列別名*/ | column_alias = expression /*選擇指定列并更改列標(biāo)題*/ ,.n

3、 ,4.2.1 選擇列,1選擇所有列 使用“*”表示選擇一個(gè)表或視圖中的所有列。 【例4.1】 查詢PXSCJ數(shù)據(jù)庫中XSB表的所有數(shù)據(jù)。 USE PXSCJ GO SELECT * FROM XSB GO,4.2.1 選擇列,2選擇一個(gè)表中指定的列 使用SELECT語句選擇一個(gè)表中的某些列，各列名之間要以逗號(hào)分隔。其中，$IDENTITY表示選擇標(biāo)識(shí)列。 【例4.2】 查詢PXSCJ數(shù)據(jù)庫的XSB表中各個(gè)同學(xué)的姓名、專業(yè)和總學(xué)分。 USE PXSCJ GO SELECT 姓名,專業(yè),總學(xué)分 FROM XSB GO 【例4.3】 查詢XSB表中計(jì)算機(jī)專業(yè)同學(xué)的學(xué)號(hào)、姓名和總學(xué)分，查詢XSB表中

4、所有列。 SELECT 學(xué)號(hào), 姓名, 總學(xué)分 FROM XSB WHERE 專業(yè) = 計(jì)算機(jī) GO SELECT * FROM XSB,4.2.1 選擇列,執(zhí)行后在結(jié)果窗口中將分別列出兩個(gè)查詢語句的結(jié)果，如圖4.1所示。,4.2.1 選擇列,3定義列別名 【例4.4】 查詢XSB表中計(jì)算機(jī)系同學(xué)的學(xué)號(hào)、姓名和總學(xué)分，結(jié)果中各列的標(biāo)題分別指定為number、name和mark。代碼如下，執(zhí)行結(jié)果如圖4.2所示。 USE PXSCJ GO SELECT 學(xué)號(hào) AS number, 姓名 AS name, 總學(xué)分 AS mark FROM XSB WHERE 專業(yè)= 計(jì)算機(jī),4.2.1 選擇列,更

5、改查詢結(jié)果中的列標(biāo)題也可以使用column_alias=expression的形式。例如， SELECT number = 學(xué)號(hào), name = 姓名, mark = 總學(xué)分 FROM XSB WHERE 專業(yè)= 計(jì)算機(jī) 該語句的執(zhí)行結(jié)果與上例的結(jié)果完全相同。 當(dāng)自定義的列標(biāo)題中含有空格時(shí)，必須使用引號(hào)將標(biāo)題括起來。例如， SELECT Student number = 學(xué)號(hào),姓名 AS Student name, mark = 總學(xué)分 FROM XSB WHERE 專業(yè)= 計(jì)算機(jī),4.2.1 選擇列,4替換查詢結(jié)果中的數(shù)據(jù) 要替換查詢結(jié)果中的數(shù)據(jù)，則要使用查詢中的CASE表達(dá)式，格式為 CA

6、SE WHEN 條件1 THEN 表達(dá)式1 WHEN 條件2 THEN 表達(dá)式2 ELSE 表達(dá)式 END,4.2.1 選擇列,【例4.5】 查詢XSB表中計(jì)算機(jī)系各同學(xué)的學(xué)號(hào)、姓名和總學(xué)分，對其總學(xué)分按以下規(guī)則進(jìn)行替換：若總學(xué)分為空值，則替換為“尚未選課”；若總學(xué)分小于50，則替換為“不及格”；若總學(xué)分在50與52之間，則替換為“合格”；若總學(xué)分大于52，則替換為“優(yōu)秀”。列標(biāo)題更改為“等級(jí)”。代碼如下，執(zhí)行結(jié)果如圖4.3所示。 USE PXSCJ GO SELECT 學(xué)號(hào), 姓名, 等級(jí)= CASE WHEN 總學(xué)分 IS NULL THEN 尚未選課 WHEN 總學(xué)分 =50 and 總

7、學(xué)分=52 THEN 合格 ELSE 優(yōu)秀 END FROM XSB WHERE 專業(yè)= 計(jì)算機(jī) GO,4.2.1 選擇列,5計(jì)算列值 使用SELECT對列進(jìn)行查詢時(shí)，在結(jié)果中可以輸出對列值計(jì)算后的值，即SELECT子句可使用表達(dá)式作為結(jié)果，格式為 SELECT expression , expression 【例4.6】 按120分計(jì)算成績并顯示學(xué)號(hào)為081101的學(xué)生的成績情況。代碼如下，執(zhí)行結(jié)果如圖4.4所示。 USE PXSCJ GO SELECT 學(xué)號(hào), 課程號(hào), 成績120=成績*1.20 FROM CJB WHERE 學(xué)號(hào)= 081101,4.2.1 選擇列,6消除結(jié)果集中的重復(fù)

8、行 可以使用DISTINCT關(guān)鍵字消除結(jié)果集中的重復(fù)行，其格式是 SELECT DISTINCT | ALL column_name , column_name 關(guān)鍵字DISTINCT的含義是，對結(jié)果集中的重復(fù)行只選擇一個(gè)，保證行的唯一性。 【例4.7】 對PXSCJ數(shù)據(jù)庫的XSB表只選擇專業(yè)和總學(xué)分，消除結(jié)果集中的重復(fù)行。代碼如下，執(zhí)行結(jié)果如圖4.5所示。 USE PXSCJ GO SELECT DISTINCT 專業(yè),總學(xué)分 FROM XSB,4.2.1 選擇列,7限制結(jié)果集返回行數(shù) 如果SELECT語句返回的結(jié)果集的行數(shù)非常多，那么可以使用TOP選項(xiàng)限制其返回的行數(shù)。TOP選項(xiàng)的基本格式

9、為 TOP expression PERCENT WITH TIES 【例4.8】 對PXSCJ數(shù)據(jù)庫的XSB表選擇姓名、專業(yè)和總學(xué)分，返回結(jié)果集的前6行。 SELECT TOP 6 姓名,專業(yè),總學(xué)分 FROM XSB,4.2.1 選擇列,8聚合函數(shù) SQL Server 2008所提供的聚合函數(shù)列于表4.10中。,4.2.1 選擇列,下面對常用的聚合函數(shù)加以介紹。 （1）SUM和AVG SUM和AVG分別用于求表達(dá)式中所有值項(xiàng)的總和與平均值，語法格式為 SUM /AVG ( ALL | DISTINCT expression ) 【例4.9】 求選修101課程的學(xué)生的平均成績。 SELEC

10、T AVG(成績) AS 課程101平均成績 FROM CJB WHERE 課程號(hào) = 101 【例4.10】 求學(xué)號(hào)為081101的同學(xué)所學(xué)課程的總成績。 SELECT SUM(成績) AS 課程總成績 FROM CJB WHERE 學(xué)號(hào) = 081101;,4.2.1 選擇列,（2）MAX和MIN MAX和MIN分別用于求表達(dá)式中所有值項(xiàng)的最大值與最小值，語法格式為 MAX / MIN ( ALL | DISTINCT expression ) 【例4.11】 求選修101課程的學(xué)生的最高分和最低分。語句如下，執(zhí)行結(jié)果如圖4.6所示。 SELECT MAX(成績) AS 課程101的最高分

11、 , MIN(成績) AS 課程101的最低分 FROM CJB WHERE 課程號(hào) = 101,4.2.1 選擇列,（3）COUNT COUNT用于統(tǒng)計(jì)組中滿足條件的行數(shù)或總行數(shù)，格式為 COUNT ( ALL | DISTINCT expression | * ) 【例4.12】 求學(xué)生的總數(shù)。 SELECT COUNT(*) AS 學(xué)生總數(shù) FROM XSB 【例4.13】 統(tǒng)計(jì)備注不為空的學(xué)生數(shù)。 SELECT COUNT(備注) AS 備注不為空的學(xué)生數(shù) FROM XSB;,4.2.1 選擇列,【例4.14】 統(tǒng)計(jì)總學(xué)分在50分以上的人數(shù)。 SELECT COUNT(總學(xué)分) AS

12、總學(xué)分在50分以上的人數(shù) FROM XSB WHERE 總學(xué)分50; 執(zhí)行結(jié)果為2。 【例4.15】 求選修了課程的學(xué)生總數(shù)。 SELECT COUNT(DISTINCT 學(xué)號(hào)) FROM CJB,4.2.2 WHERE子句,1表達(dá)式比較 比較運(yùn)算符用于比較兩個(gè)表達(dá)式值，共有9個(gè)，分別是 =（等于）、（大于）、=（大于等于）、（不等于）、!=（不等于）、!（不大于）。比較運(yùn)算的格式為 expression = | | = | | != | ! expression 【例4.16】 查詢PXSCJ數(shù)據(jù)庫XSB表中學(xué)號(hào)為081101的同學(xué)的情況。代碼如下，執(zhí)行結(jié)果如圖4.7所示。 USE PXSC

13、J GO SELECT 姓名,學(xué)號(hào),總學(xué)分 FROM XSB WHERE 學(xué)號(hào)=081101;,4.2.2 WHERE子句,【例4.17】 查詢XSB表中總學(xué)分大于50的同學(xué)的情況。代碼如下，執(zhí)行結(jié)果如圖4.8所示。 SELECT 姓名,學(xué)號(hào),出生時(shí)間,總學(xué)分 FROM XSB WHERE 總學(xué)分50;,【例4.18】 查詢XSB表中通信工程專業(yè)總學(xué)分大于等于42的同學(xué)的情況。 USE PXSCJ GO SELECT * FROM XSB WHERE 專業(yè)= 通信工程 AND 總學(xué)分 = 42,4.2.2 WHERE子句,2模式匹配 LIKE謂詞用于指出一個(gè)字符串是否與指定的字符串相匹配，返回

14、邏輯值TRUE或FALSE。LIKE謂詞表達(dá)式的格式為 match_expression NOT LIKE pattern ESCAPE Escape_character 【例4.19】 查詢XSB表中姓“王”且單名的學(xué)生情況。 SELECT * FROM XSB WHERE 姓名 LIKE 王_ 執(zhí)行結(jié)果如圖4.9所示。,4.2.2 WHERE子句,【例4.20】 查詢XSB表中學(xué)號(hào)倒數(shù)第3個(gè)數(shù)字為1，且倒數(shù)第1個(gè)數(shù)在15之間的學(xué)生學(xué)號(hào)、姓名及專業(yè)。 SELECT 學(xué)號(hào),姓名,專業(yè) FROM XSB WHERE 學(xué)號(hào) LIKE %1_12345 如果需要查找一個(gè)通配符，則必須使用一個(gè)轉(zhuǎn)義字符

15、。 【例4.21】 查詢XSB表中名字包含下劃線的學(xué)生學(xué)號(hào)和姓名。 SELECT 學(xué)號(hào),姓名 FROM XSB WHERE 學(xué)號(hào) LIKE %#_% ESCAPE #,4.2.2 WHERE子句,3范圍比較 用于范圍比較的關(guān)鍵字有兩個(gè)：BETWEEN和IN。當(dāng)要查詢的條件是某個(gè)值的范圍時(shí)，可以使用BETWEEN關(guān)鍵字。BETWEEN關(guān)鍵字指出查詢范圍，格式為： expression NOT BETWEEN expression1 AND expression2 使用IN關(guān)鍵字可以指定一個(gè)值表，值表中列出所有可能的值，當(dāng)與值表中的任一個(gè)匹配時(shí)，即返回TRUE，否則返回FALSE。使用IN關(guān)鍵字指

16、定值表的格式為： expression IN ( expression ,n),4.2.2 WHERE子句,【例4.22】 查詢XSB表中不在1989年出生的學(xué)生情況。 SELECT 學(xué)號(hào), 姓名, 專業(yè), 出生時(shí)間 FROM XSB WHERE 出生時(shí)間 NOT BETWEEN 1989-1-1 and 1989-12-31 【例4.23】 查詢XSB表中專業(yè)為“計(jì)算機(jī)”、“通信工程”或“無線電”的學(xué)生情況。 SELECT * FROM XSB WHERE 專業(yè) IN (計(jì)算機(jī), 通信工程, 無線電) 該語句與下列語句等價(jià)： SELECT * FROM XSB WHERE 專業(yè)= 計(jì)算機(jī) o

17、r 專業(yè)= 通信工程 or 專業(yè)=無線電,4.2.2 WHERE子句,4空值比較 當(dāng)需要判定一個(gè)表達(dá)式的值是否為空值時(shí)，使用IS NULL關(guān)鍵字，格式為： expression IS NOT NULL 【例4.24】 查詢總學(xué)分尚不定的學(xué)生情況。 SELECT * FROM XSB WHERE 總學(xué)分 IS NULL,4.2.2 WHERE子句,5子查詢 （1）IN子查詢 IN子查詢用于進(jìn)行一個(gè)給定值是否在子查詢結(jié)果集中的判斷，格式為： expression NOT IN ( subquery ) 【例4.25】 查找選修了課程號(hào)為206的課程的學(xué)生情況。 在“查詢分析器”窗口中輸入并執(zhí)行如下

18、查詢腳本： USE PXSCJ GO SELECT * FROM XSB WHERE 學(xué)號(hào) IN ( SELECT 學(xué)號(hào) FROM CJB WHERE 課程號(hào) = 206),4.2.2 WHERE子句,【例4.26】 查找未選修離散數(shù)學(xué)的學(xué)生情況。 SELECT * FROM XSB WHERE 學(xué)號(hào) NOT IN ( SELECT 學(xué)號(hào) FROM CJB WHERE 課程號(hào) IN ( SELECT 課程號(hào) FROM KCB WHERE 課程名 = 離散數(shù)學(xué) ) ),4.2.2 WHERE子句,（2）比較子查詢 這種子查詢可以認(rèn)為是IN子查詢的擴(kuò)展，它使表達(dá)式的值與子查詢的結(jié)果進(jìn)行比較運(yùn)算，格

19、式為 expression | = | != | | ! ALL | SOME | ANY ( subquery ) 【例4.27】 查找選修了離散數(shù)學(xué)的學(xué)生學(xué)號(hào)。 SELECT 學(xué)號(hào) FROM CJB WHERE 課程號(hào) = ( SELECT 課程號(hào) FROM KCB WHERE 課程名 =離散數(shù)學(xué) );,4.2.2 WHERE子句,【例4.28】 查找比所有計(jì)算機(jī)系的學(xué)生年齡都大的學(xué)生。 SELECT * FROM XSB WHERE 出生時(shí)間 ALL ( SELECT 出生時(shí)間 FROM XSB WHERE 專業(yè)= 計(jì)算機(jī) ) 執(zhí)行結(jié)果如圖4.10所示。,4.2.2 WHERE子句,（3

20、）EXISTS子查詢 EXISTS謂詞用于測試子查詢的結(jié)果是否為空表，若子查詢的結(jié)果集不為空，則EXISTS返回TRUE，否則返回FALSE。EXISTS還可與NOT結(jié)合使用，即NOT EXISTS，其返回值與EXISTS剛好相反。其格式為 NOT EXISTS ( subquery ) 【例4.29】 查找選修206號(hào)課程的學(xué)生姓名。 SELECT 姓名 FROM XSB WHERE EXISTS ( SELECT * FROM CJB WHERE 學(xué)號(hào) = XSB.學(xué)號(hào) AND 課程號(hào) = 206 ),4.2.2 WHERE子句,【例4.30】 查找選修了全部課程的同學(xué)的姓名。 SELEC

21、T 姓名 FROM XSB WHERE NOT EXISTS ( SELECT * FROM KCB WHERE NOT EXISTS ( SELECT * FROM CJB WHERE 學(xué)號(hào)=XSB.學(xué)號(hào) AND 課程號(hào)=KCB.課程號(hào) ) ),4.2.2 WHERE子句,【例4.31】 從XSB表中查找所有女學(xué)生的姓名、學(xué)號(hào)及其與081101號(hào)學(xué)生的年齡差距。 SELECT 學(xué)號(hào), 姓名, YEAR(出生時(shí)間)-YEAR( ( SELECT 出生時(shí)間 FROM XSB WHERE 學(xué)號(hào)=081101 ) ) AS 年齡差距 FROM XSB WHERE 性別=0 執(zhí)行結(jié)果如圖4.11所示。

22、,4.2.3 FROM子句,1表明或視圖名 From子句后可以指定一個(gè)或多個(gè)表明或視圖名作為查詢對象。有關(guān)視圖的內(nèi)容在4.3節(jié)中介紹。 【例4.32】 查找表KCB中101號(hào)課程的開課學(xué)期。 USE PXSCJ GO SELECT 開課學(xué)期 FROM KCB WHERE 課程號(hào)= 101 查詢結(jié)果為1。 【例4.33】 查找081101號(hào)學(xué)生計(jì)算機(jī)基礎(chǔ)課的成績。 SELECT 成績 FROM CJB, KCB WHERE CJB.課程號(hào)=KCB.課程號(hào) AND 學(xué)號(hào)=081101 AND 課程名=計(jì)算機(jī)基礎(chǔ),4.2.3 FROM子句,2表值函數(shù) 所謂表值函數(shù)就是返回一個(gè)表的用戶自定義函數(shù)。 3

23、結(jié)果集 【例4.34】 從XSB表中查找總學(xué)分大于50的男同學(xué)的姓名和學(xué)號(hào)。代碼如下，執(zhí)行結(jié)果如圖4.12所示。 SELECT 姓名,學(xué)號(hào),總學(xué)分 FROM ( SELECT 姓名, 學(xué)號(hào), 性別, 總學(xué)分 FROM XSB WHERE 總學(xué)分50 ) AS STUDENT WHERE 性別=1;,4.2.3 FROM子句,【例4.35】 在XSB表中查找1990年1月1日以前出生的學(xué)生的姓名和專業(yè)，分別使用別名stu_name和speciality表示。代碼如下，執(zhí)行結(jié)果如圖4.13所示。 SELECT m.stu_name, m.speciality FROM ( SELECT * FRO

24、M XSB WHERE 出生時(shí)間19900101 ) AS m( num,stu_name, sex, birthday, speciality,score, mem ),4.2.3 FROM子句,4和 的格式如下： table_source PIVOT AS table_alias 其中， := ( aggregate_function ( value_column ) FOR pivot_column IN () ),4.2.3 FROM子句,【例4.36】 查找XSB表中1990年1月1日以前出生的學(xué)生的姓名和總學(xué)分，并列出其屬于計(jì)算機(jī)專業(yè)還是通信工程專業(yè)的情況，1表示是，0表示否。代碼

25、如下，執(zhí)行結(jié)果如圖4.14所示。 SELECT 姓名,總學(xué)分,計(jì)算機(jī),通信工程 FROM XSB PIVOT ( COUNT(學(xué)號(hào)) FOR 專業(yè) IN(計(jì)算機(jī),通信工程) )AS pvt WHERE 出生時(shí)間格式如下： table_source UNPIVOT table_alias 其中， := ( value_column FOR pivot_column IN ( ) ),4.2.3 FROM子句,【例4.37】 將KCB表中的開課學(xué)期和學(xué)分列轉(zhuǎn)換為行輸出。代碼如下，執(zhí)行結(jié)果如圖4.15所示。 SELECT 課程號(hào),課程名,選項(xiàng),內(nèi)容 FROM KCB UNPIVOT ( 內(nèi)容 FOR

26、 選項(xiàng) IN (學(xué)分,開課學(xué)期) )unpvt,4.2.4 連接,1連接謂詞 可以在SELECT語句的WHERE子句中使用比較運(yùn)算符給出連接條件對表進(jìn)行連接，將這種表示形式稱為連接謂詞表示形式。 【例4.38】 查找PXSCJ數(shù)據(jù)庫每個(gè)學(xué)生的情況以及選修的課程情況。 USE PXSCJ GO SELECT XSB.* , CJB.* FROM XSB , CJB WHERE XSB.學(xué)號(hào) = CJB.學(xué)號(hào),4.2.4 連接,【例4.39】 自然連接查詢。 SELECT XSB.* , CJB.課程號(hào), CJB.成績 FROM XSB , CJB WHERE XSB.學(xué)號(hào)= CJB.學(xué)號(hào) 若選擇

27、的字段名在各個(gè)表中是唯一的，則可以省略字段名前的表名。例如，本例的SELECT語句也可寫為： SELECT XSB.* , 課程號(hào), 成績 FROM XSB , CJB WHERE XSB.學(xué)號(hào) = CJB.學(xué)號(hào),4.2.4 連接,【例4.40】 查找選修了206號(hào)課程且成績在80分以上的學(xué)生姓名及成績。代碼如下，執(zhí)行結(jié)果如圖4.16所示。 SELECT 姓名, 成績 FROM XSB , CJB WHERE XSB.學(xué)號(hào) = CJB.學(xué)號(hào) AND 課程號(hào) = 206 AND 成績 = 80,4.2.4 連接,【例4.41】 查找選修了“計(jì)算機(jī)基礎(chǔ)”課程且成績在80分以上的學(xué)生學(xué)號(hào)、姓名、課程

28、名及成績。代碼如下，執(zhí)行結(jié)果如圖4.17所示。 SELECT XSB.學(xué)號(hào), 姓名, 課程名, 成績 FROM XSB , KCB , CJB WHERE XSB.學(xué)號(hào) = CJB.學(xué)號(hào) AND KCB.課程號(hào) = CJB.課程號(hào) AND 課程名 = 計(jì)算機(jī)基礎(chǔ) AND 成績 = 80,4.2.4 連接,2以JOIN關(guān)鍵字指定的連接 T-SQL擴(kuò)展了以JOIN關(guān)鍵字指定連接的表示方式，使表的連接運(yùn)算能力有了增強(qiáng)。FROM子句的表示將多個(gè)表連接起來。 格式如下。 := ON | CROSS JOIN | left_table_source CROSS | OUTER APPLY right_ta

29、ble_source | ( ) ,4.2.4 連接,（1）內(nèi)連接。指定了INNER關(guān)鍵字的連接是內(nèi)連接，內(nèi)連接按照ON所指定的連接條件合并兩個(gè)表，返回滿足條件的行。 【例4.42】 查找PXSCJ數(shù)據(jù)庫每個(gè)學(xué)生的情況以及選修的課程情況。 SELECT * FROM XSB INNER JOIN CJB ON XSB.學(xué)號(hào) =CJB.學(xué)號(hào) 【例4.43】 用FROM子句的JOIN關(guān)鍵字表達(dá)下列查詢：查找選修了206號(hào)課程且成績在80分以上的學(xué)生姓名及成績。代碼如下，執(zhí)行結(jié)果如圖4.18所示。 SELECT 姓名, 成績 FROM XSB JOIN CJB ON XSB.學(xué)號(hào) = CJB.學(xué)號(hào)

30、WHERE 課程號(hào) = 206 AND 成績=80,4.2.4 連接,【例4.44】 用FROM子句的JOIN關(guān)鍵字表達(dá)下列查詢：查找選修了“計(jì)算機(jī)基礎(chǔ)”課程且成績在80分以上的學(xué)生學(xué)號(hào)、姓名、課程名及成績。 SELECT XSB.學(xué)號(hào), 姓名, 課程名, 成績 FROM XSB JOIN CJB JOIN KCB ON CJB.課程號(hào) = KCB.課程號(hào) ON XSB.學(xué)號(hào) = CJB.學(xué)號(hào) WHERE 課程名= 計(jì)算機(jī)基礎(chǔ) AND 成績=80 【例4.45】 查找不同課程成績相同的學(xué)生的學(xué)號(hào)、課程號(hào)和成績。 SELECT a.學(xué)號(hào), a.課程號(hào), b.課程號(hào), a.成績 FROM CJB

31、a JOIN CJB b ON a.成績=b.成績 AND a.學(xué)號(hào)=b.學(xué)號(hào) AND a.課程號(hào)!=b.課程號(hào) 執(zhí)行結(jié)果如圖4.19所示。,4.2.4 連接,（2）外連接。指定了OUTER關(guān)鍵字的為外連接，外連接的結(jié)果表不但包含滿足連接條件的行，還包括相應(yīng)表中的所有行。外連接包括以下三種。 左外連接（LEFT OUTER JOIN）：結(jié)果表中除了包括滿足連接條件的行外，還包括左表的所有行； 右外連接（RIGHT OUTER JOIN）：結(jié)果表中除了包括滿足連接條件的行外，還包括右表的所有行； 完全外連接（FULL OUTER JOIN）：結(jié)果表中除了包括滿足連接條件的行外，還包括兩個(gè)表的所有

32、行。,4.2.4 連接,【例4.46】 查找所有學(xué)生情況，以及他們選修的課程號(hào)，若學(xué)生未選修任何課，也要包括其情況。 SELECT XSB.* , 課程號(hào) FROM XSB LEFT OUTER JOIN CJB ON XSB.學(xué)號(hào) = CJB.學(xué)號(hào) 【例4.47】 查找被選修了的課程的選修情況和所有開設(shè)的課程名。 SELECT CJB.* , 課程名 FROM CJB RIGHT JOIN KCB ON CJB.課程號(hào)= KCB.課程號(hào),4.2.4 連接,（3）交叉連接。交叉連接實(shí)際上是將兩個(gè)表進(jìn)行笛卡爾積運(yùn)算，結(jié)果表是由第一個(gè)表的每一行與第二個(gè)表的每一行拼接后形成的表，因此結(jié)果表的行數(shù)等于

33、兩個(gè)表的行數(shù)之積。 【例4.48】 列出學(xué)生所有可能的選課情況。 SELECT 學(xué)號(hào), 姓名, 課程號(hào), 課程名 FROM XSB CROSS JOIN KCB,4.2.5 GROUP BY子句,1非ISO標(biāo)準(zhǔn)的GROUP BY子句 語法格式如下。 GROUP BY ALL group_by_expression ,n WITH CUBE | ROLLUP 【例4.49】 將PXSCJ數(shù)據(jù)庫中各專業(yè)輸出。代碼如下，執(zhí)行結(jié)果如圖4.20所示。 SELECT 專業(yè) FROM XSB GROUP BY 專業(yè) 【例4.50】 求各專業(yè)的學(xué)生數(shù)。代碼如下，執(zhí)行結(jié)果如圖4.21所示。 SELECT 專業(yè),

34、 COUNT(*) AS 學(xué)生數(shù) FROM XSB GROUP BY 專業(yè),4.2.5 GROUP BY子句,【例4.51】 求被選修的各門課程的平均成績和選修該課程的人數(shù)。代碼如下，執(zhí)行結(jié)果如圖4.22所示。 SELECT 課程號(hào), AVG(成績) AS 平均成績 , COUNT(學(xué)號(hào)) AS 選修人數(shù) FROM CJB GROUP BY 課程號(hào),4.2.5 GROUP BY子句,2ISO標(biāo)準(zhǔn)的GROUP BY子句 語法格式如下。 GROUP BY | ROLLUP ( ) | CUBE () | GROUPING SETS ( ) ,4.2.5 GROUP BY子句,【例4.52】 在PX

35、SCJ數(shù)據(jù)庫上產(chǎn)生一個(gè)結(jié)果集，包括每個(gè)專業(yè)的男生、女生人數(shù)、總?cè)藬?shù)及學(xué)生總?cè)藬?shù)。代碼執(zhí)行結(jié)果如圖4.23所示。 SELECT 專業(yè), 性別 , COUNT(*) AS 人數(shù) FROM XSB GROUP BY ROLLUP(專業(yè),性別),4.2.5 GROUP BY子句,【例4.53】 在PXSCJ數(shù)據(jù)庫上產(chǎn)生一個(gè)結(jié)果集，包括每個(gè)專業(yè)的男生、女生人數(shù)、總?cè)藬?shù)，以及男生總數(shù)、女生總數(shù)、學(xué)生總?cè)藬?shù)。代碼執(zhí)行結(jié)果如圖4.24所示。 SELECT 專業(yè), 性別, COUNT(*) AS 人數(shù) FROM XSB GROUP BY CUBE(專業(yè),性別),4.2.5 GROUP BY子句,【例4.54】

36、生成一個(gè)結(jié)果集，分別根據(jù)專業(yè)和性別對人數(shù)進(jìn)行聚合。代碼執(zhí)行結(jié)果如圖4.25所示。 SELECT 專業(yè), 性別, COUNT(*) AS 人數(shù) FROM XSB GROUP BY GROUPING SETS(專業(yè),性別),4.2.6 HAVING子句,例如，查找PXSCJ數(shù)據(jù)庫中平均成績在85分以上的學(xué)生，就是在CJB表上按學(xué)號(hào)分組后篩選出符合平均成績大于等于85的學(xué)生。 HAVING子句的格式為 HAVING 【例4.55】 查找平均成績在85分以上的學(xué)生的學(xué)號(hào)和平均成績。代碼執(zhí)行結(jié)果如圖4.26所示。 USE PXSCJ GO SELECT 學(xué)號(hào), AVG(成績) AS 平均成績 FROM

37、CJB GROUP BY 學(xué)號(hào) HAVING AVG(成績) =85,4.2.6 HAVING子句,【例4.56】 查找選修課程超過2門且成績都在80分以上的學(xué)生的學(xué)號(hào)。 SELECT 學(xué)號(hào) FROM CJB WHERE 成績 = 80 GROUP BY 學(xué)號(hào) HAVING COUNT(*) 2,4.2.6 HAVING子句,【例4.57】 查找通信工程專業(yè)平均成績在85分以上的學(xué)生的學(xué)號(hào)和平均成績。代碼執(zhí)行結(jié)果如圖4.27所示。 SELECT 學(xué)號(hào), AVG(成績) AS 平均成績 FROM CJB WHERE 學(xué)號(hào) IN ( SELECT 學(xué)號(hào) FROM XSB WHERE 專業(yè) = 通信

38、工程 ) GROUP BY 學(xué)號(hào) HAVING AVG(成績) =85,4.2.7 ORDER BY子句,在應(yīng)用中經(jīng)常要對查詢的結(jié)果排序輸出，如將學(xué)生成績由高到低排序。在SELECT語句中，使用ORDER BY子句對查詢結(jié)果進(jìn)行排序。ORDER BY子句的格式為： ORDER BY order_by_expression COLLATE collation_name ASC | DESC ,.n 【例4.58】 將通信工程專業(yè)的學(xué)生按出生時(shí)間先后順序排序。 SELECT * FROM XSB WHERE 專業(yè)= 通信工程 ORDER BY 出生時(shí)間,4.2.7 ORDER BY子句,【例4.5

39、9】 將計(jì)算機(jī)專業(yè)學(xué)生的“計(jì)算機(jī)基礎(chǔ)”課程成績按降序排列。 SELECT 姓名, 課程名, 成績 FROM XSB, KCB, CJB WHERE XSB.學(xué)號(hào) = CJB.學(xué)號(hào) AND CJB.課程號(hào) = KCB.課程號(hào) AND 課程名= 計(jì)算機(jī)基礎(chǔ) AND 專業(yè)= 計(jì)算機(jī) ORDER BY 成績 DESC ORDER BY子句可以與COMPUTE BY子句一起使用，在對結(jié)果排序的同時(shí)還產(chǎn)生附加的匯總行。COMPUTE子句用于分類匯總，將產(chǎn)生額外的匯總行。格式為 COMPUTE 聚合函數(shù)名(expression) ,n BY expression ,n ,4.2.7 ORDER BY子句,【

40、例4.60】 查找通信工程專業(yè)學(xué)生的學(xué)號(hào)、姓名、出生時(shí)間，并產(chǎn)生一個(gè)學(xué)生總?cè)藬?shù)行。代碼執(zhí)行結(jié)果如圖4.28所示。 SELECT 學(xué)號(hào), 姓名, 出生時(shí)間 FROM XSB WHERE 專業(yè)= 通信工程 COMPUTE COUNT(學(xué)號(hào)),4.2.7 ORDER BY子句,【例4.61】 將學(xué)生按專業(yè)排序，并匯總各專業(yè)人數(shù)和平均學(xué)分。代碼執(zhí)行結(jié)果如圖4.29所示。 SELECT 學(xué)號(hào), 姓名, 出生時(shí)間, 總學(xué)分 FROM XSB ORDER BY 專業(yè) COMPUTE COUNT(學(xué)號(hào)), AVG(總學(xué)分) BY 專業(yè),4.2.8 SELECT語句的其他語法,1INTO 使用INTO子句可以將

41、SELECT查詢所得的結(jié)果保存到一個(gè)新建的表中。INTO子句的格式為 INTO new_table 【例4.62】 由XSB表創(chuàng)建“計(jì)算機(jī)系學(xué)生”表，包括學(xué)號(hào)和姓名。 SELECT 學(xué)號(hào), 姓名 INTO 計(jì)算機(jī)系學(xué)生 FROM XSB WHERE 專業(yè)= 計(jì)算機(jī),4.2.8 SELECT語句的其他語法,2UNION 使用UNION子句可以將兩個(gè)或多個(gè)SELECT查詢的結(jié)果合并成一個(gè)結(jié)果集，其格式為 | ( ) UNION A LL | ( ) UNION A LL | ( ) n 使用UNION組合兩個(gè)查詢的結(jié)果集的基本規(guī)則是： （1）所有查詢中的列數(shù)和列的順序必須相同。 （2）數(shù)據(jù)類型必須

42、兼容。,4.2.8 SELECT語句的其他語法,【例4.63】 查找學(xué)號(hào)為081101和學(xué)號(hào)為081210的兩位同學(xué)的信息。 SELECT * FROM XSB WHERE 學(xué)號(hào)= 081101 UNION ALL SELECT * FROM XSB WHERE 學(xué)號(hào)= 081210 執(zhí)行結(jié)果如圖4.30所示。,4.2.8 SELECT語句的其他語法,3EXCEPT 和 INTERSECT EXCEPT和INTERSECT用于比較兩個(gè)查詢的結(jié)果，返回非重復(fù)值。語法格式如下： | ( ) EXCEPT | INTERSECT | ( ) ,4.2.8 SELECT語句的其他語法,【例4.64】

43、查找專業(yè)為計(jì)算機(jī)但性別不為男的學(xué)生信息。 USE PXSCJ GO SELECT * FROM XSB WHERE 專業(yè)= 計(jì)算機(jī) EXCEPT SELECT * FROM XSB WHERE 性別=1 執(zhí)行結(jié)果如圖4.31所示。,4.2.8 SELECT語句的其他語法,【例4.65】 查找總學(xué)分大于42且性別為男的學(xué)生信息。 SELECT * FROM XSB WHERE 總學(xué)分42 INTERSECT SELECT * FROM XSB WHERE 性別=1,4.2.9 CTE：WITH語句,WITH語法格式如下： WITH ,.n 其中， := expression_name ( col

44、umn_name ,.n ) AS ( CTE_query_definition ),4.2.9 CTE：WITH語句,【例4.66】 使用CTE從CJB表中查詢選了101號(hào)課程的學(xué)生學(xué)號(hào)、成績，并定義新的列名為number、point。再使用SELECT語句從CTE和XSB中查詢姓名為“王林”的學(xué)生學(xué)號(hào)和成績情況。代碼執(zhí)行結(jié)果如圖4.32所示。 USE PXSCJ GO WITH cte_stu(number, point) AS (SELECT 學(xué)號(hào),成績 FROM CJB WHERE 課程號(hào)=101) SELECT number, point FROM cte_stu, XSB WHER

45、E XSB.姓名=王林 AND XSB.學(xué)號(hào)=cte_stu.number,4.2.9 CTE：WITH語句,【例4.67】 計(jì)算數(shù)字110的階乘。代碼執(zhí)行結(jié)果如圖4.33所示。 WITH MyCTE(n,njc) AS( SELECT n=1, njc=1 UNION ALL SELECT n=n+1, njc=njc*(n+1) FROM MyCTE WHERE n10 ) SELECT n, njc FROM MyCTE,4.3 視圖,4.3.1 視圖概念 視圖一經(jīng)定義以后，就可以像表一樣被查詢、修改、刪除和更新。使用視圖有下列優(yōu)點(diǎn)： （1）為用戶集中數(shù)據(jù)，簡化用戶的數(shù)據(jù)查詢和處理。有時(shí)

46、用戶所需要的數(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ù)共享。各用戶不必都定義和存儲(chǔ)自己所需的數(shù)據(jù)，而可共享數(shù)據(jù)庫的數(shù)據(jù)，這樣，同樣的數(shù)據(jù)只需存儲(chǔ)一次。 （5）可以重新組織數(shù)據(jù)以便輸出到其他應(yīng)用程序中。,4.3.2 創(chuàng)建視圖,1在SQL Server Management Studio中創(chuàng)建視圖 其主要步驟如下。 第1步：啟動(dòng)SQ

47、L Server Management Studio，在對象資源管理器中展開“數(shù)據(jù)庫PXSCJ”，選擇其中的“視圖”項(xiàng)，右擊鼠標(biāo)，在彈出的快捷菜單上選擇“新建視圖(N)”菜單項(xiàng)。 第2步：在隨后出現(xiàn)的“添加表”對話框中，添加所需要關(guān)聯(lián)的基本表、視圖、函數(shù)、同義詞。這里只使用“表”選項(xiàng)卡，選擇表“XSB”，如圖4.34所示，單擊“添加”按鈕。,4.3.2 創(chuàng)建視圖,第3步：基表添加完后，在“視圖”選項(xiàng)卡的“關(guān)系圖”區(qū)域中顯示了基表的全部列信息，如圖4.35所示。,4.3.2 創(chuàng)建視圖,2使用CREATE VIEW語句創(chuàng)建視圖 T-SQL中用于創(chuàng)建視圖的語句是CREATE VIEW語句，例如，用該

48、語句創(chuàng)建視圖CS_XS，其表示形式為 USE PXSCJ GO CREATE VIEW CS_XS AS SELECT * FROM XSB WHERE 專業(yè)= 計(jì)算機(jī) 語法格式： CREATE VIEW schema_name . view_name (column ,.n ) WITH view_attribute ,.n AS select_statement ; WITH CHECK OPTION ,4.3.2 創(chuàng)建視圖,【例4.78】 創(chuàng)建CS_KC視圖，包括計(jì)算機(jī)專業(yè)各學(xué)生的學(xué)號(hào)、其選修的課程號(hào)及成績。要保證對該視圖的修改都符合“專業(yè)為計(jì)算機(jī)”這一條件。 USE PXSCJ GO

49、CREATE VIEW CS_KC WITH ENCRYPTION AS SELECT XSB.學(xué)號(hào), 課程號(hào), 成績 FROM XSB, CJB WHERE XSB.學(xué)號(hào) =CJB.學(xué)號(hào) AND 專業(yè) = 計(jì)算機(jī) WITH CHECK OPTION 【例4.79】 創(chuàng)建計(jì)算機(jī)專業(yè)學(xué)生的平均成績視圖CS_KC_AVG，包括學(xué)號(hào)（在視圖中列名為num）和平均成績（在視圖中列名為score_avg）。 CREATE VIEW CS_KC_AVG(num,score_avg) AS SELECT 學(xué)號(hào), AVG(成績) FROM CJB GROUP BY 學(xué)號(hào),4.3.3 查詢視圖,【例4.70】

50、使用視圖CS_KC查找計(jì)算機(jī)專業(yè)的學(xué)生學(xué)號(hào)和選修的課程號(hào)。 SELECT 學(xué)號(hào), 課程號(hào) FROM CS_KC 【例4.71】 查找平均成績在80分以上的學(xué)生的學(xué)號(hào)和平均成績。 本例首先創(chuàng)建學(xué)生平均成績視圖XS_KC_AVG，包括學(xué)號(hào)（在視圖中列名為num）和平均成績（在視圖中列名為score_avg）。 CREATE VIEW XS_KC_AVG ( num,score_avg ) AS SELECT 學(xué)號(hào), AVG(成績) FROM CJB GROUP BY 學(xué)號(hào),4.3.3 查詢視圖,再對XS_KC_AVG視圖進(jìn)行查詢。執(zhí)行結(jié)果如圖4.36所示。 SELECT * FROM XS_KC_

51、AVG WHERE score_avg = 80,4.3.4 更新視圖,1可更新視圖 （1）滿足以下條件的視圖。 創(chuàng)建視圖的SELECT語句中沒有聚合函數(shù)，且沒有TOP、GROUP BY、UNION子句及DISTINCT關(guān)鍵字。 創(chuàng)建視圖的SELECT語句中不包含從基本表列通過計(jì)算所得的列。 創(chuàng)建視圖的SELECT語句的FROM子句中至少要包含一個(gè)基本表。 （2）通過INSTEAD OF觸發(fā)器創(chuàng)建的可更新視圖。,4.3.4 更新視圖,2插入數(shù)據(jù) 【例4.72】 向CS_XS視圖中插入以下記錄： (081115, 劉明儀, 1, 1998-3-2, 計(jì)算機(jī), 50 , NULL) INSERT

52、INTO CS_XS VALUES(081115, 劉明儀, 1,1998-3-2, 計(jì)算機(jī),50,NULL) 使用SELECT語句查詢CS_XS依據(jù)的基本表XSB： SELECT * FROM XSB,4.3.4 更新視圖,3修改數(shù)據(jù) 【例4.73】 將CS_XS視圖中所有學(xué)生的總學(xué)分增加8。 UPDATE CS_XS SET 總學(xué)分=總學(xué)分+ 8 【例4.74】 將CS_KC視圖中學(xué)號(hào)為081101的學(xué)生的101號(hào)課程成績改為90。 UPDATE CS_KC SET 成績=90 WHERE 學(xué)號(hào)=081101 AND 課程號(hào)=101 本例中，視圖CS_KC依賴于兩個(gè)基本表：XSB和CJB，

53、對CS_KC視圖的一次修改只能改變學(xué)號(hào)（源于XSB表）或者課程號(hào)和成績（源于CJB表）。以下修改是錯(cuò)誤的： UPDATE CS_KC SET 學(xué)號(hào)=081120, 課程號(hào)=208 WHERE 成績=90,4.3.4 更新視圖,4刪除數(shù)據(jù) 【例4.75】 刪除CS_XS中女同學(xué)的記錄。 DELETE FROM CS_XS WHERE 性別 = 0 【例4.76】 在對象資源管理器中對視圖CS_XS進(jìn)行如下操作： （1）增加一條記錄(081115, 劉明儀, 1, 1998-3-2, 計(jì)算機(jī), 50 , NULL)。 （2）將學(xué)號(hào)為081115的學(xué)生的總學(xué)分改為55。 （3）刪除學(xué)號(hào)為081115

54、的學(xué)生記錄。,4.3.5 修改視圖的定義,1通過SQL Server Enterprise Manager修改視圖 在對象資源管理器中右擊視圖“dbo.CS_XS”，在彈出的快捷菜單中選擇“設(shè)計(jì)”菜單項(xiàng)，進(jìn)入視圖修改窗口。該窗口與創(chuàng)建視圖的窗口類似，其中可以查看并可修改視圖結(jié)構(gòu)，修改完后單擊“保存”圖標(biāo)即可。 2使用ALTER VIEW語句修改視圖 語法格式： ALTER VIEW schema_name . view_name ( column ,.n ) WITH view_attribute ,.n AS select_statement ; WITH CHECK OPTION ,4.3

55、.5 修改視圖的定義,【例4.77】 將CS_XS視圖修改為只包含計(jì)算機(jī)專業(yè)學(xué)生的學(xué)號(hào)、姓名和總學(xué)分。 USE PXSCJ GO ALTER VIEW CS_XS AS SELECT 學(xué)號(hào), 姓名, 總學(xué)分 FROM XSB WHERE 專業(yè)= 計(jì)算機(jī),4.3.5 修改視圖的定義,【例4.78】 視圖CS_KC是加密存儲(chǔ)視圖，修改其定義，包括學(xué)號(hào)、姓名、選修的課程號(hào)、課程名和成績。 ALTER VIEW CS_KC WITH ENCRYPTION AS SELECT XSB.學(xué)號(hào),XSB.姓名,CJB.課程號(hào),KCB.課程名,成績 FROM XSB, CJB, KCB WHERE XSB.學(xué)號(hào)

56、 = CJB.學(xué)號(hào) AND CJB.課程號(hào) = KCB.課程號(hào) AND 專業(yè)= 計(jì)算機(jī) WITH CHECK OPTION,4.3.6 刪除視圖,1通過對象資源管理器刪除視圖 在對象資源管理器中刪除視圖的操作方法是： 在“視圖”目錄下選擇需要?jiǎng)h除的視圖，右擊鼠標(biāo)，在彈出的快捷菜單上選擇“刪除”菜單項(xiàng)，出現(xiàn)“刪除”對話框，單擊“確定”按鈕，即刪除了指定的視圖。 2T-SQL命令方式刪除視圖 語法格式： DROP VIEW schema_name . view_name .,n ; 其中，view_name是視圖名，使用DROP VIEW可刪除一個(gè)或多個(gè)視圖。例如， DROP VIEW CS_XS

57、, CS_KC,4.4 游標(biāo),4.4.1 游標(biāo)概念 SQL Server通過游標(biāo)提供了對一個(gè)結(jié)果集進(jìn)行逐行處理的能力，游標(biāo)可看做一種特殊的指針，它與某個(gè)查詢結(jié)果相聯(lián)系，可以指向結(jié)果集的任意位置，以便對指定位置的數(shù)據(jù)進(jìn)行處理。使用游標(biāo)可以在查詢數(shù)據(jù)的同時(shí)對數(shù)據(jù)進(jìn)行處理。 在SQL Server中，有兩類游標(biāo)可以用于應(yīng)用程序中：前端（客戶端）游標(biāo)和后端（服務(wù)器端）游標(biāo)。服務(wù)器端游標(biāo)是由數(shù)據(jù)庫服務(wù)器創(chuàng)建和管理的游標(biāo)，而客戶端游標(biāo)是由ODBC 和DB-Library 支持，在客戶端實(shí)現(xiàn)的游標(biāo)。,4.4.2 聲明游標(biāo),1SQL-92語法 語句格式： DECLARE cursor_name INSENSITIVE SCROLL CURSOR FOR select_statement FOR READ ONLY | UPDATE OF column_name ,.n ; 以下是一個(gè)符合SQL-92標(biāo)準(zhǔn)的游標(biāo)聲明： DECLARE XS_CUR1 CURSOR FOR SELECT 學(xué)號(hào),姓名,性別,出生時(shí)間,總學(xué)分 FROM XSB WHERE