oracle10g經(jīng)典實(shí)戰(zhàn)第4章 數(shù)據(jù)庫(kù)的查詢(xún)和視圖

第4章數(shù)據(jù)庫(kù)的查詢(xún)和視圖

4.1連接、選擇和投影

Oracle是一個(gè)關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)，關(guān)系數(shù)據(jù)庫(kù)建立在關(guān)系模型基礎(chǔ)之上，具有嚴(yán)格的數(shù)學(xué)理論基礎(chǔ)。關(guān)系數(shù)據(jù)庫(kù)對(duì)數(shù)據(jù)的操作除了包括集合代數(shù)的并、差等運(yùn)算之外，還定義了一組專(zhuān)門(mén)的關(guān)系運(yùn)算：連接、選擇和投影，關(guān)系運(yùn)算的特點(diǎn)是運(yùn)算的對(duì)象和結(jié)果都是表。

4.1.1選擇選擇（Selection），簡(jiǎn)單的說(shuō)就是通過(guò)一定的條件把自己所需要的數(shù)據(jù)檢索出來(lái)。選擇是單目運(yùn)算，其運(yùn)算對(duì)象是一個(gè)表。該運(yùn)算按給定的條件，從表中選出滿(mǎn)足條件的行形成一個(gè)新表，作為運(yùn)算結(jié)果。?4.1.1選擇【例4.1】學(xué)生情況表如表4.1所示。若要在學(xué)生情況表中找出學(xué)生表中性別為女且平均成績(jī)?cè)?0分以上的行形成一個(gè)新表，該選擇運(yùn)算的結(jié)果如表4.2所示。表4.1學(xué)生表

表4.2查詢(xún)后的結(jié)果?4.1.2投影投影（Projection）也是單目運(yùn)算。投影就是選擇表中指定的列，這樣在查詢(xún)結(jié)果中只顯示指定數(shù)據(jù)列，減少了顯示的數(shù)據(jù)量也提高查詢(xún)的性能?！纠?.2】若在表4.1中對(duì)“姓名”和“平均成績(jī)”投影，該查詢(xún)得到如表4.3所示的新表。表4.3投影后的新表?4.1.3連接連接（JOIN）是把兩個(gè)表中的行按照給定的條件進(jìn)行拼接而形成新表?！纠?.3】若表A和B分別如表4.4和表4.5所示，則連接后結(jié)果如表4.6所示。表4.4A表表4.5B表表4.6連接后的表?4.1.3連接【例4.4】若表A和表B分別如表4.7和表4.8所示，自然連接后的新表C如表4.9所示。

表4.7A表

表4.8B表

表4.9C表

?4.2數(shù)據(jù)庫(kù)的查詢(xún)使用數(shù)據(jù)庫(kù)和表的主要目的是存儲(chǔ)數(shù)據(jù)以便在需要時(shí)進(jìn)行檢索、統(tǒng)計(jì)或組織輸出，通過(guò)PL/SQL的查詢(xún)可以從表或視圖中迅速方便地檢索數(shù)據(jù)。PL/SQL的SELECT語(yǔ)句可以實(shí)現(xiàn)對(duì)表的選擇、投影及連接操作，其功能十分強(qiáng)大。下面介紹SELECT語(yǔ)句，它是PL/SQL的核心。SELECT語(yǔ)句很復(fù)雜，主要的子句如下：語(yǔ)法格式：

SELECTselect_list /*指定要選擇的列或行及其限定*/FROMtable_source/*FROM子句，指定表或視圖*/[WHEREsearch_condition]/*WHERE子句，指定查詢(xún)條件*/[GROUPBYgroup_by_expression]/*GROUPBY子句，指定分組表達(dá)式*/[HAVINGsearch_condition]/*HAVING子句，指定分組統(tǒng)計(jì)條件*/[ORDERBYorder_expression[ASC|DESC]]/*ORDER子句，指定排序表達(dá)式和順序*/?4.2.1選擇列選擇表中的列組成結(jié)果表，通過(guò)SELECT語(yǔ)句的SELECT子句來(lái)表示。語(yǔ)法格式：

SELECT[ALL|DISTINCT]<select_list>其中select_list指出了結(jié)果的形式，select_list的主要格式為：{* /*選擇當(dāng)前表或視圖的所有列*/ |{table_name|view_name|table_alias}.*/*選擇指定的表或視圖的所有列*/ |{colume_name|expression}[[AS]column_alias]/*選擇指定的列*/ |column_alias=expression /*選擇指定列并更改列標(biāo)題*/}[,…n]1. 選擇一個(gè)表中指定的列使用SELECT語(yǔ)句選擇一個(gè)表中的某些列，各列名之間要以逗號(hào)分隔。語(yǔ)法格式：SELECTcolumn_name[,column_name…]FROMtable_nameWHEREsearch_condition其功能是在FROM子句指定的表中檢索符合search_condition條件的列。?4.2.1選擇列【例4.5】查詢(xún)XSCJ數(shù)據(jù)庫(kù)的XS表中各個(gè)同學(xué)的XM、XH和ZXF。SELECTXH,XM,ZXFFROMXS;執(zhí)行結(jié)果如圖4.1所示。圖4.1在XS表中選擇列

?4.2.1選擇列【例4.6】查詢(xún)XS表中ZXF大于45同學(xué)的XH、XM和ZXF。SELECTXH,XM,ZXFFROMXSWHEREZXF>45;當(dāng)在SELECT語(yǔ)句指定列的位置上使用*號(hào)時(shí)，表示選擇表的所有列。【例4.7】查詢(xún)XS表中的所有列。 SELECT* FROMXS;該語(yǔ)句等價(jià)于語(yǔ)句： SELECTXH,XM,ZYM,XB,CSSJ,ZXF,BZ FROMXS;其執(zhí)行后將列出XS表中的所有數(shù)據(jù)。?4.2.1選擇列2.修改查詢(xún)結(jié)果中的列標(biāo)題當(dāng)希望查詢(xún)結(jié)果中的某些列或所有列顯示時(shí)使用自己選擇的列標(biāo)題時(shí)，可以在列名之后使用AS子句來(lái)更改查詢(xún)結(jié)果的列標(biāo)題名，其中column_alias是指定的列標(biāo)題?！纠?.8】查詢(xún)XS表中計(jì)算機(jī)同學(xué)的XH、XM和ZXF，結(jié)果中各列的標(biāo)題分別指定為學(xué)號(hào)、姓名和總學(xué)分。SELECTXHAS學(xué)號(hào),XMAS姓名,ZXFAS總學(xué)分FROMXSWHEREZYM=’計(jì)算機(jī)’;該語(yǔ)句的執(zhí)行結(jié)果如圖4.2所示。更改查詢(xún)結(jié)果中的列標(biāo)題可以省略AS關(guān)鍵字。例如：SELECTXH學(xué)號(hào),XM姓名,ZXF總學(xué)分FROMXSWHEREZYM=’計(jì)算機(jī)’;?4.2.1選擇列圖4.2更改查詢(xún)結(jié)果中的列標(biāo)題

?4.2.1選擇列3.計(jì)算列值使用SELECT對(duì)列進(jìn)行查詢(xún)時(shí)，在結(jié)果中可以輸出對(duì)列值計(jì)算后的值，即SELECT子句可使用表達(dá)式作為結(jié)果，格式為：SELECTexpression[,expression]【例4.9】創(chuàng)建產(chǎn)品銷(xiāo)售數(shù)據(jù)庫(kù)XSH，并在其中創(chuàng)建產(chǎn)品表CP，其表結(jié)構(gòu)如表4.10所

表4.10CP表結(jié)構(gòu)

?4.2.1選擇列設(shè)CP表中已有如表4.11所示的數(shù)據(jù)。

表4.11CP表

?4.2.1選擇列下列語(yǔ)句將列出產(chǎn)品名稱(chēng)和產(chǎn)品總值：SELECTCPMCAS產(chǎn)品名稱(chēng),JG*KCLAS產(chǎn)品總值FROMCP;該語(yǔ)句的執(zhí)行結(jié)果如圖4.3所示。圖4.3計(jì)算列值?4.2.1選擇列4. 消除結(jié)果集中的重復(fù)行【例4.10】對(duì)XSCJ數(shù)據(jù)庫(kù)的XS表只選擇ZYM和ZXF，消除結(jié)果集中的重復(fù)行。SELECTDISTINCTZYMAS專(zhuān)業(yè)名,ZXFAS總學(xué)分FROMXS; 該語(yǔ)句執(zhí)行的結(jié)果為：專(zhuān)業(yè)名總學(xué)分計(jì)算機(jī) 48計(jì)算機(jī) 50計(jì)算機(jī) 52計(jì)算機(jī) 54通信工程 40通信工程 42通信工程 44通信工程 50與DISTINCT相反，當(dāng)使用關(guān)鍵字ALL時(shí)，將保留結(jié)果集的所有行。?4.2.1選擇列【例4.11】以下的SELECT語(yǔ)句對(duì)XSCJ數(shù)據(jù)庫(kù)的XS表選擇ZYM和ZXF，不消除結(jié)果集中的重復(fù)行。SELECTALLZYMAS專(zhuān)業(yè)名,ZXFAS總學(xué)分FROMXS;該語(yǔ)句執(zhí)行后結(jié)果為：專(zhuān)業(yè)名總學(xué)分計(jì)算機(jī) 50計(jì)算機(jī) 50計(jì)算機(jī) 50計(jì)算機(jī) 50計(jì)算機(jī) 54計(jì)算機(jī) 52計(jì)算機(jī) 50計(jì)算機(jī) 50計(jì)算機(jī) 50計(jì)算機(jī) 48計(jì)算機(jī)50通信工程 42通信工程 40通信工程 42通信工程 42通信工程 44通信工程 42通信工程 42通信工程 42通信工程 42通信工程 42通信工程 50?4.2.2選擇行1.表達(dá)式比較比較運(yùn)算符用于比較兩個(gè)表達(dá)式值，共有7個(gè)，分別是：=（等于）、<（小于）、<=（小于等于）、>（大于）、>=（大于等于）、<>（不等于）、!=（不等于）。比較運(yùn)算的格式為：expression{=|<|<=|>|>=|<>|!=}expression當(dāng)兩個(gè)表達(dá)式值均不為空值（NULL）時(shí)，比較運(yùn)算返回邏輯值TRUE（真）或FALSE（假）；而當(dāng)兩個(gè)表達(dá)式值中有一個(gè)為空值或都為空值時(shí)，比較運(yùn)算將返回UNKNOWN?！纠?.12】(1)查詢(xún)XSH數(shù)據(jù)庫(kù)CP表中庫(kù)存量在500以上的產(chǎn)品情況。SELECT* FROMCPWHEREKCL>500;(2)查詢(xún)XSCJ數(shù)據(jù)庫(kù)XS表中通信工程專(zhuān)業(yè)總學(xué)分大于等于42的同學(xué)的情況。SELECT* FROMXS WHEREZYM=’通信工程’andZXF>=42;?4.2.2選擇行2.

模式匹配LIKE謂詞用于指出一個(gè)字符串是否與指定的字符串相匹配，其運(yùn)算對(duì)象可以是char、varchar2和date類(lèi)型的數(shù)據(jù)，返回邏輯值TRUE或FALSE。LIKE謂詞表達(dá)式的格式為：string_expression[NOT]LIKEstring_expression【例4.13】查詢(xún)XSH數(shù)據(jù)庫(kù)CP表中產(chǎn)品名含有“冰箱”的產(chǎn)品情況。SELECT* FROMCP WHERECPMCLIKE‘%冰箱%’;執(zhí)行結(jié)果為：CPBHCPMCJGKCL10001100 冰箱A_100 1500.0 50010002120 冰箱A_200 1850.0 20010001200 冰箱B_200 1600.0 120010001102 冰箱C_210 1890.0600?4.2.2選擇行【例4.14】查詢(xún)XSCJ數(shù)據(jù)庫(kù)XS表中姓“王”且單名的學(xué)生情況。SELECT* FROMXS WHEREXMLIKE‘王_’;執(zhí)行結(jié)果為：XHXMZYMXBCSSJZXFBZ061101 王林計(jì)算機(jī) 男 10-二月-8650 061103 王燕計(jì)算機(jī) 女 06-十月-8550 061201 王敏通信工程 男 10-六月-8442 061202 王林通信工程男 29-一月-8540有一門(mén)課不及格，待補(bǔ)考3.范圍比較用于范圍比較的關(guān)鍵字有兩個(gè)：BETWEEN和IN。當(dāng)要查詢(xún)的條件是某個(gè)值的范圍時(shí)，可以使用BETWEEN關(guān)鍵字。BETWEEN關(guān)鍵字指出查詢(xún)范圍，格式為：expression[NOT]BETWEENexpression1ANDexpression2當(dāng)不使用NOT時(shí)，若表達(dá)式expression的值在表達(dá)式expression1與expression2之間（包括這兩個(gè)值），則返回TRUE，否則返回FALSE；使用NOT時(shí)，返回值剛好相反。注意：expression1的值不能大于expression2的值。?4.2.2選擇行【例4.15】(1)

查詢(xún)XSH數(shù)據(jù)庫(kù)CP表中價(jià)格在2000元與4000元之間的產(chǎn)品情況。SELECT* FROMCP WHEREJGBETWEEN2000AND4000;(2)查詢(xún)XSCJ數(shù)據(jù)庫(kù)XS表中不在1985年出生的學(xué)生情況。SELECT* FROMXS WHERECSSJNOTBETWEENTO_DATE(‘19850101’,’YYYYMMDD’)and TO_DATE(‘19851231’,’YYYYMMDD’);使用IN關(guān)鍵字可以指定一個(gè)值表，值表中列出所有可能的值，當(dāng)表達(dá)式與值表中的任一個(gè)匹配時(shí)，即返回TRUE，否則返回FALSE。使用IN關(guān)鍵字指定值表的格式為：expressionIN(expression[,…n])【例4.16】查詢(xún)XSH數(shù)據(jù)庫(kù)CP表中庫(kù)存量為“200”、“300”和“500”的情況。SELECT* FROMCP WHEREKCLIN(200,300,500);該語(yǔ)句與下列語(yǔ)句等價(jià)：SELECT* FROMCP WHEREKCL=200ORKCL=300ORKCL=500;?4.2.2選擇行4. 空值比較當(dāng)需要判定一個(gè)表達(dá)式的值是否為空值時(shí)，使用ISNULL關(guān)鍵字，格式為：expressionIS[NOT]NULL當(dāng)不使用NOT時(shí)，若表達(dá)式expression的值為空值，返回TRUE，否則返回FALSE；當(dāng)使用NOT時(shí)，結(jié)果剛好相反?！纠?.17】查詢(xún)XSCJ數(shù)據(jù)庫(kù)中總學(xué)分尚不定的學(xué)生情況。SELECT* FROMXS WHEREZXFISNULL;?4.2.2選擇行5. 子查詢(xún)?cè)诓樵?xún)條件中，可以使用另一個(gè)查詢(xún)的結(jié)果作為條件的一部分，例如判定列值是否與某個(gè)查詢(xún)的結(jié)果集中的值相等，作為查詢(xún)條件一部分的查詢(xún)稱(chēng)為子查詢(xún)。PL/SQL允許SELECT多層嵌套使用，用來(lái)表示復(fù)雜的查詢(xún)。子查詢(xún)除了可以用在SELECT語(yǔ)句中，還可以用在INSERT、UPDATE及DELETE語(yǔ)句中。子查詢(xún)通常與IN、EXIST謂詞及比較運(yùn)算符結(jié)合使用。(1) IN子查詢(xún)IN子查詢(xún)用于進(jìn)行一個(gè)給定值是否在子查詢(xún)結(jié)果集中的判斷，格式為： expression[NOT]IN(subquery)其中subquery是子查詢(xún)。當(dāng)表達(dá)式expression與子查詢(xún)subquery的結(jié)果表中的某個(gè)值相等時(shí)，IN謂詞返回TRUE，否則返回FALSE；若使用了NOT，則返回的值剛好相反。?4.2.2選擇行【例4.18】在XSCJ數(shù)據(jù)庫(kù)中有描述課程情況的表KC和描述學(xué)生成績(jī)表的表XS_KC(表的結(jié)構(gòu)和樣本數(shù)據(jù)見(jiàn)附錄A)。查找選修了課程號(hào)為101的課程的學(xué)生的情況： SELECT* FROMXS WHEREXHIN (SELECTXHFROMXS_KCWHEREKCH=‘101’);在執(zhí)行包含子查詢(xún)的SELECT語(yǔ)句時(shí)，系統(tǒng)先執(zhí)行子查詢(xún)，產(chǎn)生一個(gè)結(jié)果表，再執(zhí)行查詢(xún)。本例中，先執(zhí)行子查詢(xún)： SELECTXH FROMXS_KC WHEREKCH=‘101’;得到一個(gè)只含有學(xué)號(hào)列的表，XS_KC中課程名列值為‘101’的行在結(jié)果表中都有一行。再執(zhí)行外查詢(xún)，若XS表中某行的學(xué)號(hào)列值等于子查詢(xún)結(jié)果表中的任一個(gè)值，則該行就被選擇。?4.2.2選擇行【例4.19】查找未選修離散數(shù)學(xué)的學(xué)生的情況。SELECTXH,XM,ZYM,ZXF FROMXS WHEREXHNOTIN (SELECTXH FROMXS_KC WHEREKCHIN (SELECTKCH FROMKC WHEREKCM='離散數(shù)學(xué)' ) );?執(zhí)行結(jié)果為：XHXMZYMZXF061201 王敏 通信工程 42061202 王林 通信工程 40061203 王玉民通信工程 42061204 馬琳琳通信工程 42061206李計(jì) 通信工程 42061210 李紅慶通信工程 44061216 孫祥欣通信工程 42061218 孫研 通信工程 42061220 吳薇華通信工程 42061221 劉燕敏通信工程 42061241 羅林琳通信工程 50?4.2.2選擇行2) 比較子查詢(xún)這種子查詢(xún)可以認(rèn)為是IN子查詢(xún)的擴(kuò)展，它使表達(dá)式的值與子查詢(xún)的結(jié)果進(jìn)行比較運(yùn)算，格式為： expression{<|<=|=|>|>=|!=|<>}{ALL|SOME|ANY}(subquery)其中expression為要進(jìn)行比較的表達(dá)式，subquery是子查詢(xún)。ALL、SOME和ANY說(shuō)明對(duì)比較運(yùn)算的限制。ALL指定表達(dá)式要與子查詢(xún)結(jié)果集中的每個(gè)值都進(jìn)行比較，當(dāng)表達(dá)式與每個(gè)值都滿(mǎn)足比較的關(guān)系時(shí)，才返回TRUE，否則返回FALSE；SOME或ANY表示表達(dá)式只要與子查詢(xún)結(jié)果集中的某個(gè)值滿(mǎn)足比較的關(guān)系時(shí)，就返回TRUE，否則返回FALSE。?4.2.2選擇行【例4.20】查找比所有計(jì)算機(jī)系學(xué)生年齡都大的學(xué)生。 SELECT* FROMXS WHERECSSJ<ALL (SELECTCSSJ FROMXS WHEREZYM='計(jì)算機(jī)' );執(zhí)行結(jié)果如圖4.4所示。?圖4.4查找結(jié)果?4.2.2選擇行【例4.21】查找課程號(hào)206的成績(jī)不低于課程號(hào)101的最低成績(jī)的學(xué)生的學(xué)號(hào)。 SELECTXH FROMXS_KC WHEREKCH='206'ANDCJ>=ANY (SELECTCJ FROMXS_KC WHEREKCH='101' );(3) EXISTS子查詢(xún)EXISTS謂詞用于測(cè)試子查詢(xún)的結(jié)果是否為空表，若子查詢(xún)的結(jié)果集不為空，則EXISTS返回TRUE，否則返回FALSE。EXISTS還可與NOT結(jié)合使用，即NOTEXISTS，其返回值與EXIST剛好相反。格式為：[NOT]EXISTS(subquery)?4.2.2選擇行【例4.22】查找選修206號(hào)課程的學(xué)生姓名。SELECTXM FROMXS WHEREEXISTS (SELECT* FROMXS_KC WHEREXH=XS.XHANDKCH=‘206’ );本例在子查詢(xún)的條件中使用了限定形式的列名引用XS.XH，表示這里的學(xué)號(hào)列出自表XS。?4.2.2選擇行【例4.23】查找選修了全部課程的同學(xué)的姓名。 SELECTXM FROMXS WHERENOTEXISTS (SELECT* FROMKC WHERENOTEXISTS (SELECT* FROMXS_KC WHEREXH=XS.XHANDKCH=KC.KCH ) );本例即查找沒(méi)有一門(mén)功課不選修的學(xué)生。?4.2.3查詢(xún)對(duì)象前面介紹了SELECT的選擇列和行，這里介紹SELECT查詢(xún)的對(duì)象（即數(shù)據(jù)源）的構(gòu)成形式?！纠?.24】查找001102號(hào)同學(xué)所選修的全部課程的同學(xué)的學(xué)號(hào)。本例即要查找這樣的學(xué)號(hào)y，對(duì)所有的課程號(hào)x，若001102號(hào)同學(xué)選修了該課，那么y也選修了該課。SELECTDISTINCTXHFROMXS_KCCJ1WHERENOTEXISTS(SELECT*FROMXS_KCCJ2WHERECJ2.XH=‘001102’ANDNOTEXISTS(SELECT*FROMXS_KCCJ3WHERECJ3.XH=CJ1.XHANDCJ3.KCH=CJ2.KCH));本例子指定SELECT語(yǔ)句查詢(xún)的對(duì)象是表。?4.2.3查詢(xún)對(duì)象【例4.25】在XS表中查找1986年1月1日以前出生的學(xué)生的姓名和專(zhuān)業(yè)名。SELECTXM,ZYMFROM(SELECT*FROMXSWHERECSSJ<TO_DATE(‘19860101’,’YYYYMMDD'));執(zhí)行結(jié)果為：XMZYM王燕 計(jì)算機(jī)林一帆 計(jì)算機(jī)張強(qiáng)民 計(jì)算機(jī)嚴(yán)紅 計(jì)算機(jī)王敏 通信工程王林 通信工程馬琳琳 通信工程李計(jì) 通信工程李紅慶 通信工程孫祥欣 通信工程劉燕敏 通信工程?4.2.4連接1.連接謂詞可以在SELECT語(yǔ)句的WHERE子句中使用比較運(yùn)算符給出連接條件對(duì)表進(jìn)行連接，將這種表示形式稱(chēng)為連接謂詞表示形式。【例4.26】查找XSCJ數(shù)據(jù)庫(kù)每個(gè)學(xué)生的情況以及選修的課程情況。SELECTXS.*,XS_KC.* FROMXS,XS_KC WHEREXS.XH=XS_KC.XH;結(jié)果表將包含XS表和XS_KC表的所有列，結(jié)果如圖4.5所示。?圖4.5連接后的部分?jǐn)?shù)據(jù)?4.2.4連接【例4.27】自然連接查詢(xún)。SELECTXS.*,XS_KC.KCH,XS_KC.CJ FROMXS,XS_KC WHEREXS.XH=XS_KC.XH;本例所得的結(jié)果表包含以下字段：學(xué)號(hào)、姓名、專(zhuān)業(yè)名、性別、出生時(shí)間、總學(xué)分、備注、課程號(hào)、成績(jī)。若選擇的字段名在各個(gè)表中是唯一的，則可以省略字段名前的表名。如本例的SELECT子句也可寫(xiě)為：SELECTXS.*,KCH,CJ FROMXS,XS_KC WHEREXS.XH=XS_KC.XH;?4.2.4連接【例4.28】查找選修了206課程且成績(jī)?cè)?0分以上的學(xué)生姓名及成績(jī)。SELECTXMAS姓名,CJAS成績(jī) FROMXS,XS_KC WHEREXS.XH=XS_KC.XHANDKCH=‘206’ANDCJ>=80;執(zhí)行結(jié)果為：姓名成績(jī)王燕 81李方方 80林一帆 87張蔚 89有時(shí)用戶(hù)所需要的字段來(lái)自?xún)蓚€(gè)以上的表，那么就要對(duì)兩個(gè)以上的表進(jìn)行連接，稱(chēng)之為多表連接。?4.2.4連接【例4.29】查找選修了“計(jì)算機(jī)基礎(chǔ)”課程且成績(jī)?cè)?0分以上的學(xué)生學(xué)號(hào)、姓名、課程名及成績(jī)。SELECTXS.XH,XM,KCM,CJ FROMXS,KC,XS_KC WHEREXS.XH=XS_KC.XHANDKC.KCH=XS_KC.KCH ANDKCM=‘計(jì)算機(jī)基礎(chǔ)’ANDCJ>=80;執(zhí)行結(jié)果為：XHXMKCMCJ001101 王林 計(jì)算機(jī)基礎(chǔ)80001104 韋嚴(yán)平 計(jì)算機(jī)基礎(chǔ)90001108 林一帆 計(jì)算機(jī)基礎(chǔ)85001110 張蔚 計(jì)算機(jī)基礎(chǔ)95001111 趙琳 計(jì)算機(jī)基礎(chǔ)91001201 王敏 計(jì)算機(jī)基礎(chǔ)80001203 王玉民 計(jì)算機(jī)基礎(chǔ)87001204 馬琳琳 計(jì)算機(jī)基礎(chǔ)91001216 孫祥欣 計(jì)算機(jī)基礎(chǔ)81001220 吳薇華 計(jì)算機(jī)基礎(chǔ)82001241 羅林琳 計(jì)算機(jī)基礎(chǔ)90?4.2.4連接2. 以JOIN關(guān)鍵字指定的連接PL/SQL擴(kuò)展了以JOIN關(guān)鍵字指定連接的表示方式，使表的連接運(yùn)算能力有了增強(qiáng)。連接表的格式為：<table_source><join_type><table_source>ON<search_condition> |<table_source>CROSSJOIN<table_source> |<joined_table>其中table_source為需連接的表，join_type表示連接類(lèi)型，ON用于指定連接條件。join_type的格式為：[INNER|{LEFT|RIGHT|FULL}[OUTER][<join_hint>]JOIN其中INNER表示內(nèi)連接，OUTER表示外連接，join_hint是連接提示。CROSSJOIN表示交叉連接。因此，以JOIN關(guān)鍵字指定的連接有三種類(lèi)型。?4.2.4連接2. 以JOIN關(guān)鍵字指定的連接(1)內(nèi)連接內(nèi)連接按照ON所指定的連接條件合并兩個(gè)表，返回滿(mǎn)足條件的行?！纠?.30】查找XSCJ數(shù)據(jù)庫(kù)每個(gè)學(xué)生的情況以及選修的課程情況。SELECT* FROMXSINNERJOINXS_KCONXS.XH=XS_KC.XH;結(jié)果表將包含XS表和XS_KC表的所有字段（不去除重復(fù)字段—學(xué)號(hào)）。若要去除重復(fù)的學(xué)號(hào)字段，可將SELECT子句改為：SELECTXS.*,KCH,CJ【例4.31】用FROM的JOIN關(guān)鍵字表達(dá)下列查詢(xún)：查找選修了206課程且成績(jī)?cè)?0分以上的學(xué)生姓名及成績(jī)。SELECTXM,CJ FROMXSJOINXS_KCONXS.XH=XS_KC.XH WHEREKCH='206'ANDCJ>=80;內(nèi)連接還可以用于多個(gè)表的連接。?4.2.4連接【例4.32】用FROM的JOIN關(guān)鍵字表達(dá)下列查詢(xún)：查找選修了“計(jì)算機(jī)基礎(chǔ)”課程且成績(jī)?cè)?0分以上的學(xué)生學(xué)號(hào)、姓名、課程名及成績(jī)。SELECTXS.XH,XM,KCM,CJ FROMXSJOINXS_KCJOINKCONXS_KC.KCH=KC.KCH ONXS.XH=XS_KC.XH WHEREKCM='計(jì)算機(jī)基礎(chǔ)'ANDCJ>=80;作為一種特例，可以將一個(gè)表與它自身進(jìn)行連接，稱(chēng)為自連接。若要在一個(gè)表中查找具有相同列值的行，則可以使用自連接。使用自連接時(shí)需為表指定兩個(gè)別名，且對(duì)所有列的引用均要用別名限定?！纠?.33】查找不同課程成績(jī)相同的學(xué)生的學(xué)號(hào)、課程號(hào)和成績(jī)。SELECTa.XH,a.KCH,b.KCH,a.CJ FROMXS_KCaJOINXS_KCbONa.CJ=b.CJANDa.XH=b.XHANDa.KCH!=b.KCH;執(zhí)行結(jié)果為：XHKCHKCHCJ001102 102 206 78001102 206 102 78?4.2.4連接2. 以JOIN關(guān)鍵字指定的連接(2)外連接外連接的結(jié)果表不但包含滿(mǎn)足連接條件的行，還包括相應(yīng)表中的所有行。外連接包括三種：左外連接（LEFTOUTERJOIN）：結(jié)果表中除了包括滿(mǎn)足連接條件的行外，還包括左表的所有行；右外連接（RIGHTOUTERJOIN）：結(jié)果表中除了包括滿(mǎn)足連接條件的行外，還包括右表的所有行；完全外連接（FULLOUTERJOIN）：結(jié)果表中除了包括滿(mǎn)足連接條件的行外，還包括兩個(gè)表的所有行。其中的OUTER關(guān)鍵字均可省略。?4.2.4連接【例4.34】查找所有學(xué)生情況及他們選修的課程號(hào)，若學(xué)生未選修任何課，也要包括其情況。SELECTXS.*,KCH FROMXSLEFTOUTERJOINXS_KCONXS.XH=XS_KC.XH;本例執(zhí)行時(shí)，若有學(xué)生未選任何課程，則結(jié)果表中相應(yīng)行的課程號(hào)字段值為NULL?！纠?.35】查找被選修了的課程的選修情況和所有開(kāi)設(shè)的課程名。SELECTXS_KC.*,KCM FROMXS_KCRIGHTJOINKCONXS_KC.KCH=KC.KCH;本例執(zhí)行時(shí)，若某課程未被選修，則結(jié)果表中相應(yīng)行的學(xué)號(hào)、課程號(hào)和成績(jī)字段值均為NULL。注意：外連接只能對(duì)兩個(gè)表進(jìn)行。?4.2.4連接2. 以JOIN關(guān)鍵字指定的連接(3)交叉連接交叉連接實(shí)際上是將兩個(gè)表進(jìn)行笛卡爾積運(yùn)算，結(jié)果表是由第一個(gè)表的每行與第二個(gè)表的每一行拼接后形成的表，因此結(jié)果表的行數(shù)等于兩個(gè)表行數(shù)之積。【例4.36】列出學(xué)生所有可能的選課情況。SELECTXH,XM,KCH,KCM FROMXSCROSSJOINKC;注意:交叉連接不能有條件，且不能帶WHERE子句。?4.2.5匯總1. 統(tǒng)計(jì)函數(shù)統(tǒng)計(jì)函數(shù)用于計(jì)算表中的數(shù)據(jù)，返回單個(gè)計(jì)算結(jié)果。下面對(duì)常用的幾個(gè)統(tǒng)計(jì)函數(shù)加以介紹。（1）SUM和AVG函數(shù)。SUM和AVG函數(shù)分別用于求表達(dá)式中所有值項(xiàng)的總和與平均值，語(yǔ)法格式為：SUM/AVG([ALL|DISTINCT]expression)其中expression是常量、列、函數(shù)或表達(dá)式。SUM和AVG函數(shù)只能對(duì)數(shù)值型數(shù)據(jù)進(jìn)行計(jì)算。ALL表示對(duì)所有值進(jìn)行運(yùn)算，DISTINCT表示去除重復(fù)值，缺省為ALL。SUM/AVG忽略NULL值?！纠?.37】求選修101課程的學(xué)生的平均成績(jī)。SELECTAVG(CJ)AS課程101平均成績(jī) FROMXS_KC WHEREKCH='101';執(zhí)行結(jié)果為：課程101平均成績(jī)78?4.2.5匯總1.統(tǒng)計(jì)函數(shù)（2）MAX和MIN函數(shù)。MAX和MIN函數(shù)分別用于求表達(dá)式中所有值項(xiàng)的最大值與最小值，語(yǔ)法格式為：MAX/MIN([ALL|DISTINCT]expression)其中expression是常量、列、函數(shù)或表達(dá)式，其數(shù)據(jù)類(lèi)型可以是數(shù)字、字符和時(shí)間日期類(lèi)型。ALL表示對(duì)所有值進(jìn)行運(yùn)算，DISTINCT表示去除重復(fù)值，缺省為ALL。MAX/MIN忽略NULL值?！纠?.38】求選修101課程的學(xué)生的最高分和最低分。SELECTMAX(CJ)AS課程101的最高分,MIN(CJ)AS課程101的最低分 FROMXS_KC WHEREKCH='101';執(zhí)行結(jié)果為：課程101的最高分課程101的最低分9562?4.2.5匯總1.統(tǒng)計(jì)函數(shù)（3）COUNT函數(shù)。COUNT函數(shù)用于統(tǒng)計(jì)組中滿(mǎn)足條件的行數(shù)或總行數(shù)，格式為：COUNT({[ALL|DISTINCT]expression}|*)其中expression是一個(gè)表達(dá)式。ALL表示對(duì)所有值進(jìn)行運(yùn)算，DISTINCT表示去除重復(fù)值，缺省為ALL。選擇*時(shí)將統(tǒng)計(jì)總行數(shù)。COUNT忽略NULL值?！纠?.39】(1)求學(xué)生的總?cè)藬?shù)。SELECTCOUNT(*)AS學(xué)生總數(shù) FROMXS;COUNT(*)不需要任何參數(shù)。(2)求選修了課程的學(xué)生總?cè)藬?shù)。SELECTCOUNT(DISTINCTXH) FROMXS_KC;(3)統(tǒng)計(jì)離散數(shù)學(xué)課程成績(jī)?cè)?5分以上的人數(shù)。SELECTCOUNT(CJ)AS離散數(shù)學(xué)85分以上的人數(shù) FROMXS_KC WHERECJ>=85ANDKCH= (SELECTKCH FROMKCWHEREKCM='離散數(shù)學(xué)');執(zhí)行結(jié)果為：離散數(shù)學(xué)85分以上的人數(shù)2?4.2.5匯總2. GROUPBY子句GROUPBY子句用于對(duì)表或視圖中的數(shù)據(jù)按字段分組，格式為：[GROUPBY[ALL]group_by_expression[,…n]group_by_expression：用于分組的表達(dá)式，其中通常包含字段名。指定ALL將顯示所有組。使用GROUPBY子句后，SELECT子句中的列表中只能包含在GROUPBY中指出的列或在統(tǒng)計(jì)函數(shù)中指定的列?！纠?.40】將XSCJ數(shù)據(jù)庫(kù)中各專(zhuān)業(yè)名輸出。SELECTZYMAS專(zhuān)業(yè)名 FROMXS GROUPBYZYM;執(zhí)行結(jié)果為：專(zhuān)業(yè)名計(jì)算機(jī)通信工程?4.2.5匯總【例4.41】求XSCJ數(shù)據(jù)庫(kù)中各專(zhuān)業(yè)的學(xué)生數(shù)。SELECTZYMAS專(zhuān)業(yè)名,COUNT(*)AS學(xué)生數(shù) FROMXS GROUPBYZYM;執(zhí)行結(jié)果為：專(zhuān)業(yè)名學(xué)生數(shù)計(jì)算機(jī) 11通信工程 11【例4.42】求被選修的各門(mén)課程的平均成績(jī)和選修該課程的人數(shù)。SELECTKCHAS課程號(hào),AVG(CJ)AS平均成績(jī),COUNT(XH)AS選修人數(shù) FROMXS_KC GROUPBYKCH;執(zhí)行結(jié)果為：課程號(hào)平均成績(jī)選修人數(shù)101 78 20102 77 11206 75 11?4.2.5匯總3. HAVING子句使用GROUPBY子句和統(tǒng)計(jì)函數(shù)對(duì)數(shù)據(jù)進(jìn)行分組后，還可以使用HAVING子句對(duì)分組數(shù)據(jù)進(jìn)行進(jìn)一步的篩選。例如查找XSCJ數(shù)據(jù)庫(kù)中平均成績(jī)?cè)?5分以上的學(xué)生，就是在XS_KC數(shù)據(jù)庫(kù)上按學(xué)號(hào)分組后篩選出符合平均成績(jī)大于等于85的學(xué)生。HAVING子句的格式為：[HAVING<search_condition>]其中search_condition為查詢(xún)條件，與WHERE子句的查詢(xún)條件類(lèi)似，并且可以使用統(tǒng)計(jì)函數(shù)。【例4.43】查找XSCJ數(shù)據(jù)庫(kù)中平均成績(jī)?cè)?5分以上的學(xué)生的學(xué)號(hào)和平均成績(jī)。SELECTXHAS學(xué)號(hào),AVG(CJ)AS平均成績(jī) FROMXS_KC GROUPBYXH HAVINGAVG(CJ)>=85;執(zhí)行結(jié)果為：學(xué)號(hào)平均成績(jī)001110 91001203 8700120491001241 90?4.2.5匯總【例4.44】查找選修課程超過(guò)2門(mén)且成績(jī)都在80分以上的學(xué)生的學(xué)號(hào)。SELECTXHAS學(xué)號(hào) FROMXS_KC WHERECJ>=80 GROUPBYXH HAVINGCOUNT(*)>2;查詢(xún)將XS_KC表中成績(jī)大于或等于80的記錄按學(xué)號(hào)分組，對(duì)每組記錄計(jì)數(shù)，選出記錄數(shù)大于2的各組的學(xué)號(hào)值形成結(jié)果表?！纠?.45】查找通信工程專(zhuān)業(yè)平均成績(jī)?cè)?5分以上的學(xué)生的學(xué)號(hào)和平均成績(jī)。SELECTXHAS學(xué)號(hào),AVG(CJ)AS平均成績(jī) FROMXS_KC WHEREXHIN (SELECTXH FROMXS WHEREZYM='通信工程') GROUPBYXH HAVINGAVG(CJ)>=85;?4.2.6排序在應(yīng)用中經(jīng)常要對(duì)查詢(xún)的結(jié)果排序輸出，例如學(xué)生成績(jī)由高到低排序。在SELECT語(yǔ)句中，使用ORDERBY子句對(duì)查詢(xún)結(jié)果進(jìn)行排序。ORDERBY子句的格式為：[ORDERBY{order_by_expression[ASC|DESC]}[,…n]其中order_by_expression是排序表達(dá)式，可以是列名、表達(dá)式或一個(gè)正整數(shù)，當(dāng)expression是一個(gè)正整數(shù)時(shí)，表示按表中的該位置上列排序。關(guān)鍵字ASC表示升序排列，DESC表示降序排列，系統(tǒng)默認(rèn)值為ASC。【例4.46】將通信工程專(zhuān)業(yè)的學(xué)生按出生時(shí)間先后排序。SELECT* FROMXS WHEREZYM='通信工程' ORDERBYCSSJ;?4.2.6排序【例4.47】將計(jì)算機(jī)專(zhuān)業(yè)學(xué)生的“計(jì)算機(jī)基礎(chǔ)”課程成績(jī)按降序排列。SELECTXMAS姓名,KCMAS課程名,CJAS成績(jī)FROMXS,KC,XS_KC WHEREXS.XH=XS_KC.XHANDXS_KC.KCH=KC.KCHANDKCM='計(jì)算機(jī)基礎(chǔ)'ANDZYM='計(jì)算機(jī)' ORDERBYCJDESC;執(zhí)行的結(jié)果為：姓名 課程名 成績(jī)張蔚 計(jì)算機(jī)基礎(chǔ) 95趙琳 計(jì)算機(jī)基礎(chǔ) 91韋嚴(yán)平 計(jì)算機(jī)基礎(chǔ) 90林一帆 計(jì)算機(jī)基礎(chǔ) 85王林 計(jì)算機(jī)基礎(chǔ) 80李明 計(jì)算機(jī)基礎(chǔ) 78張強(qiáng)民 計(jì)算機(jī)基礎(chǔ) 66李方方 計(jì)算機(jī)基礎(chǔ) 65嚴(yán)紅 計(jì)算機(jī)基礎(chǔ) 63王燕 計(jì)算機(jī)基礎(chǔ) 62?4.2.7SELECT語(yǔ)句的UNION子句使用UNION子句可以將兩個(gè)或多個(gè)SELECT查詢(xún)的結(jié)果合并成一個(gè)結(jié)果集，其格式為： {<queryspecification>|(<queryexpression>)} UNION[ALL]<queryspecification>|(<queryexpression>) [UNION[ALL]<queryspecification>|(<queryexpression>)[…n]]其中queryspecification和queryexpression都是SELECT查詢(xún)語(yǔ)句。使用UNION組合兩個(gè)查詢(xún)的結(jié)果集的基本規(guī)則是：(1)所有查詢(xún)中的列數(shù)和列的順序必須相同。(2)數(shù)據(jù)類(lèi)型必須兼容。關(guān)鍵字ALL表示合并的結(jié)果中包括所有行，不去除重復(fù)行。不使用ALL則在合并的結(jié)果去除重復(fù)行。含有UNION的SELECT查詢(xún)也稱(chēng)為聯(lián)合查詢(xún)。?4.2.7SELECT語(yǔ)句的UNION子句【例4.48】設(shè)在XSCJ數(shù)據(jù)庫(kù)中建兩個(gè)表：數(shù)學(xué)系學(xué)生、外語(yǔ)系學(xué)生，表結(jié)構(gòu)與XS表相同，兩個(gè)表分別存儲(chǔ)數(shù)學(xué)系和外語(yǔ)系的學(xué)生情況，下列語(yǔ)句將這兩個(gè)表的數(shù)據(jù)合并到XS表中。SELECT* FROMXS UNIONALL SELECT* FROM數(shù)學(xué)系學(xué)生 UNIONALL SELECT* FROM外語(yǔ)系學(xué)生;UNION操作常用于歸并數(shù)據(jù)，例如歸并月報(bào)表形成年報(bào)表，歸并各部門(mén)數(shù)據(jù)等。注意UNION還可以與GROUPBY及ORDERBY一起使用，用來(lái)對(duì)合并所得的結(jié)果表進(jìn)行分組或排序。?4.3數(shù)據(jù)庫(kù)的視圖4.3.1視圖的概念視圖是從一個(gè)或多個(gè)表（或視圖）導(dǎo)出的表。視圖與表（有時(shí)為與視圖區(qū)別，也稱(chēng)表為基表——BaseTable）不同，視圖是一個(gè)虛表，即視圖所對(duì)應(yīng)的數(shù)據(jù)不進(jìn)行實(shí)際存儲(chǔ)，數(shù)據(jù)庫(kù)中只存儲(chǔ)視圖的定義，對(duì)視圖的數(shù)據(jù)進(jìn)行操作時(shí)，系統(tǒng)根據(jù)視圖的定義去操作與視圖相關(guān)聯(lián)的基表。視圖可以由以下任意一項(xiàng)組成：(1) 一個(gè)基表的任意子集(2) 兩個(gè)或者兩個(gè)以上基表的合集(3) 兩個(gè)或者兩個(gè)以上基表的交集(4) 對(duì)一個(gè)或者多個(gè)基表運(yùn)算的結(jié)果集合另一個(gè)視圖的子集?4.3.1視圖的概念視圖一經(jīng)定義以后，就可以象表一樣被查詢(xún)、修改、刪除和更新。使用視圖有下列優(yōu)點(diǎn)：(1) 為用戶(hù)集中數(shù)據(jù)，簡(jiǎn)化用戶(hù)的數(shù)據(jù)查詢(xún)和處理。(2) 屏蔽數(shù)據(jù)庫(kù)的復(fù)雜性。(3) 簡(jiǎn)化用戶(hù)權(quán)限的管理。(4) 便于數(shù)據(jù)共享。(5)可以重新組織數(shù)據(jù)以便輸出到其它應(yīng)用程序中。?4.3.2創(chuàng)建視圖1.用OEM創(chuàng)建視圖以在XSCJ數(shù)據(jù)庫(kù)中創(chuàng)建CS_XS（描述計(jì)算機(jī)專(zhuān)業(yè)學(xué)生情況）視圖說(shuō)明創(chuàng)建視圖的過(guò)程。(1)在如圖4.6所示的界面中，選擇單擊視圖進(jìn)入“視圖搜索”界面，如圖4.7所示。在該界面選擇對(duì)象類(lèi)型,并可以選擇輸入方案名稱(chēng)和對(duì)象名稱(chēng),單擊“開(kāi)始”按鈕，查找滿(mǎn)足條件的對(duì)象類(lèi)型，若存在滿(mǎn)足條件的對(duì)象類(lèi)型，在結(jié)果項(xiàng)可以查找到的對(duì)象類(lèi)型。圖4.6Oracle企業(yè)管理器

圖4.7視圖搜索界面

?4.3.2創(chuàng)建視圖1.用OEM創(chuàng)建視圖(2) 單擊“創(chuàng)建”按鈕，進(jìn)入視圖創(chuàng)建界面，如圖4.8所示。圖4.8一般信息選項(xiàng)界面

?4.3.2創(chuàng)建視圖創(chuàng)建界面有三個(gè)選項(xiàng)頁(yè)面：一般信息、選項(xiàng)和對(duì)象選項(xiàng)頁(yè)面。一般信息頁(yè)面如圖4.8所示，主要定義視圖的基本信息，如名稱(chēng)、所屬用戶(hù)方案等。選項(xiàng)（如圖4.9所示）和對(duì)象選項(xiàng)卡（如圖4.10所示），定義視圖的一些高級(jí)選項(xiàng)，如視圖是否只讀、約束條件等。

圖4.9選項(xiàng)選項(xiàng)界面

圖4.10對(duì)象選項(xiàng)界面?4.3.2創(chuàng)建視圖1. 用OEM創(chuàng)建視圖一般信息頁(yè)面：在“名稱(chēng)”文本框輸入視圖名稱(chēng)CS_XS；用戶(hù)方案選ADMIN；在“查詢(xún)文本”中輸入創(chuàng)建視圖的SQL語(yǔ)句，如圖4.8所示的SQL語(yǔ)句。如果有同名視圖存在，若選中“若存在則替換”復(fù)選框，那么將用現(xiàn)在定義的視圖代替原有同名的視圖；否則系統(tǒng)將提示錯(cuò)誤信息“視圖已存在”，要求重新命名。在別名文本框可以為視圖定義別名。選項(xiàng)選項(xiàng)頁(yè)面：若選中“強(qiáng)制”選項(xiàng)，則指定創(chuàng)建視圖而無(wú)須考慮視圖基表是否存在或包含該視圖的方案所有者是否具有權(quán)限。只有選中“限制”復(fù)選框后才能設(shè)置只讀或是否具有約束條件?！爸蛔x”單選項(xiàng)規(guī)定視圖中不能執(zhí)行刪除、插入、更新操作，只能檢索數(shù)據(jù)?！皫в袕?fù)選選項(xiàng)”指定在視圖中執(zhí)行插入和更新操作時(shí)，必須能使該視圖查詢(xún)可以選擇這些數(shù)據(jù)，但如果視圖的查詢(xún)中包含子查詢(xún)或該視圖是基于其它視圖的，這項(xiàng)指定不一定生效?！凹s束條件”是指定分配給“復(fù)選選項(xiàng)”約束條件的名稱(chēng)。對(duì)象選項(xiàng)頁(yè)面：該選項(xiàng)頁(yè)面僅用于對(duì)象視圖。如果您處理的不是對(duì)象視圖,則可跳過(guò)此選項(xiàng)卡。如果想創(chuàng)建對(duì)象視圖，則要選中“作為對(duì)象視圖”復(fù)選框，選擇對(duì)象類(lèi)型，然后選擇默認(rèn)屬性或是選擇可用屬性列表列出的對(duì)象屬性。(6) 單擊“確定”，系統(tǒng)執(zhí)行創(chuàng)建視圖操作完成后返回如圖4.7所示界面。?4.3.2創(chuàng)建視圖2. 使用CREATEVIEW語(yǔ)句創(chuàng)建視圖PL/SQL中用于創(chuàng)建視圖的語(yǔ)句是CREATEORREPLACEVIEW語(yǔ)句。語(yǔ)法格式： CREATE[ORREPLACE][FORCE|NOFORCE]VIEW[schema.]view_name [(column_name[,…n])] ASselect_statement [WITHCHECKOPTION[CONSTRAINTconstraint_name]] [WITHREADONLY]【例4.49】創(chuàng)建CS_KC視圖，包括計(jì)算機(jī)專(zhuān)業(yè)各學(xué)生的學(xué)號(hào)、其選修的課程號(hào)及成績(jī)。要保證對(duì)該視圖的修改都要符合專(zhuān)業(yè)名為計(jì)算機(jī)這個(gè)條件。CREATEORREPLACEVIEWCS_KC AS SELECTXS.XH,KCH,CJ FROMXS,XS_KC WHEREXS.XH=XS_KC.XHANDZYM='計(jì)算機(jī)' WITHCHECKOPTION;?4.3.2創(chuàng)建視圖【例4.50】創(chuàng)建計(jì)算機(jī)專(zhuān)業(yè)學(xué)生的平均成績(jī)視圖CS_KC_AVG，包括學(xué)號(hào)（在視圖中列名為num）和平均成績(jī)（在視圖中列名為score_avg）。CREATEORREPLACEVIEWCS_KC_AVG(num,score_avg) AS SELECTXH,AVG(CJ) FROMXS_KC GROUPBYXH;?4.3.3查詢(xún)視圖1. SELECT語(yǔ)句查詢(xún)視圖【例4.51】查找計(jì)算機(jī)專(zhuān)業(yè)的學(xué)生學(xué)號(hào)和選修的課程號(hào)。SELECTXH,KCH FROMCS_KC;【例4.52】查找平均成績(jī)?cè)?0分以上的學(xué)生的學(xué)號(hào)和平均成績(jī)。本例首先創(chuàng)建學(xué)生平均成績(jī)視圖XS_KC_AVG，包括學(xué)號(hào)（在視圖中列名為num）和平均成績(jī)（在視圖中列名為score_avg）。CREATEORREPLACEVIEWXS_KC_AVG(num,score_avg)AS SELECTXH,AVG(CJ)FROMXS_KCGROUPBYXH;再對(duì)XS_KC_AVG視圖進(jìn)行查詢(xún)。SELECT* FROMXS_KC_AVG WHEREscore_avg>=80;?4.3.3查詢(xún)視圖【例4.52】查找平均成績(jī)?cè)?0分以上的學(xué)生的學(xué)號(hào)和平均成績(jī)。執(zhí)行結(jié)果為：numscore_avg061110 9106120180061203 87061204 91061216 81061220 82061241 90?4.3.4更新視圖通過(guò)更新視圖（包括插入、修改和刪除）數(shù)據(jù)可以修改基表數(shù)據(jù)。但并不是所有的視圖都可以更新，只有對(duì)滿(mǎn)足可更新條件的視圖，才能進(jìn)行更新。1. 可更新視圖要通過(guò)視圖更新基表數(shù)據(jù)，必須保證視圖是可更新視圖。一個(gè)可更新視圖滿(mǎn)足以下條件：(1) 沒(méi)有使用連接函數(shù)、集合運(yùn)算函數(shù)和組函數(shù)；(2) 創(chuàng)建視圖的SELECT語(yǔ)句中沒(méi)有聚合函數(shù)且沒(méi)有GROUPBY、ONNECTBY、STARTWITH子句及DISTINCT關(guān)鍵字；(3) 創(chuàng)建視圖的SELECT語(yǔ)句中不包含從基表列通過(guò)計(jì)算所得的列；(4) 創(chuàng)建視圖沒(méi)有包含只讀屬性?！纠?.53】在XSCJ數(shù)據(jù)庫(kù)中使用以下語(yǔ)句創(chuàng)建可更新視圖CS_XS1。CREATEORREPLACEVIEWCS_XS1 AS SELECT* FROMXS WHEREZYM='計(jì)算機(jī)';?4.3.4更新視圖2. 插入數(shù)據(jù)使用INSERT語(yǔ)句通過(guò)視圖向基本表插入數(shù)據(jù)?！纠?.54】向CS_XS1視圖中插入一條記錄：（‘001115’,‘劉明儀’,‘計(jì)算機(jī)’,男,‘1984-3-2’,50,‘三好學(xué)生’）INSERTINTOCS_XS1VALUES('001115','劉明儀','計(jì)算機(jī)',’男’,TO_DATE(‘19840302’,’YYYYMMDD’),50,‘三好學(xué)生’);使用SELECT語(yǔ)句查詢(xún)CS_XS1依據(jù)的基本表XS：SELECT*FROMXS;將會(huì)看到該表已添加了學(xué)號(hào)為001115的數(shù)據(jù)行。?4.3.4更新視圖3. 修改數(shù)據(jù)使用UPDATE語(yǔ)句可以通過(guò)視圖修改基本表的數(shù)據(jù)。【例4.55】將CS_XS視圖中所有學(xué)生的總學(xué)分增加8。UPDATECS_XS SET總學(xué)分=總學(xué)分+8;該語(yǔ)句實(shí)際上是將CS_XS視圖所依賴(lài)的基本表XS中所有專(zhuān)業(yè)名為“計(jì)算機(jī)”的記錄的總學(xué)分字段值在原來(lái)基礎(chǔ)上增加8。若一個(gè)視圖依賴(lài)于多個(gè)基本表，則一次修改該視圖只能變動(dòng)一個(gè)基本表的數(shù)據(jù)?！纠?.56】將CS_KC視圖中學(xué)號(hào)為001101的學(xué)生的101課程成績(jī)改為90。UPDATECS_KC SETCJ=90 WHEREXH='001101'ANDKCH='101';?4.3.4更新視圖4. 刪除數(shù)據(jù)使用DELETE語(yǔ)句可以通過(guò)視圖刪除基本表的數(shù)據(jù)。但要注意，對(duì)于依賴(lài)于多個(gè)基本表的視圖，不能使用DELETE語(yǔ)句。例如，不能通過(guò)對(duì)CS_KC視圖執(zhí)行DELETE語(yǔ)句而刪除與之相關(guān)的基本表XS及XS_KC表的數(shù)據(jù)?！纠?.57】刪除CS_XS中女同學(xué)的記錄。DELETEFROMCS_XS WHEREXB=’女’;?4.3.5修改視圖的定義修改視圖定義可以通過(guò)OEM的界面進(jìn)行，也可使用PL/SQL語(yǔ)句。1. 通過(guò)OEM修改視圖在如圖4.11所示的窗口中，在一般信息選項(xiàng)卡中，對(duì)視圖定義進(jìn)行修改。在選項(xiàng)和對(duì)象選項(xiàng)卡中，修改指定選項(xiàng)或?qū)σ晥D的約束條件等。修改完畢后單擊“應(yīng)用”按鈕即可。圖4.11修改視圖定義?4.3.5修改視圖的定義2. 使用SQL命令修改視圖Oracle提供了ALTERVIEW語(yǔ)句，但它不是用于修改視圖定義，只是用于重新編譯或驗(yàn)證現(xiàn)有視圖。【例4.58】修改視圖CS_KC的定義，包括學(xué)號(hào)、姓名、選修的課程號(hào)、課程名和成績(jī)。 CREATEORREPLACEFORCEVIEWCS_KC AS SELECTXS.XH,XS.XM,XS_KC.KCH,KC.KCM,CJ FROMXS,XS_KC,KC WHEREXS.XH=XS_KC.XHANDXS_KC.KCH=KC.KCHANDZYM=’通信工程’ WITHCHECKOPTION;?4.3.6刪除視圖如果不再需要視圖了，可以通過(guò)OEM和PL/SQL語(yǔ)句兩種方式，把視圖的定義從數(shù)據(jù)庫(kù)中刪除。刪除一個(gè)視圖，就是刪除其定義和賦予的全部權(quán)限。在如圖4.12界面中，選中要?jiǎng)h除的視圖，單擊“刪除”，出現(xiàn)確認(rèn)界面，單擊“是”按鈕即可刪除所選擇的視圖。圖4.12刪除視圖?4.3.6刪除視圖刪除視圖的PL/SQL語(yǔ)句是DROPVIEW，格式為： DROPVIEW[schema.]view_name其中schema是所要?jiǎng)h除視圖的用戶(hù)方案；view_name是視圖名。例如： DROPVIEWCS_KC;將刪除視圖CS_KC。?4.4格式化輸出結(jié)果4.4.1替換變量在SQL*Plus環(huán)境中，可以使用替換變量來(lái)臨時(shí)存儲(chǔ)有關(guān)的數(shù)據(jù)。Oracle使用3種類(lèi)型的替換變量。1. &替換變量在SELECT語(yǔ)句中，如果某個(gè)變量前面使用了&符號(hào)，那么表示該變量是一個(gè)替換變量。在執(zhí)行SELECT語(yǔ)句時(shí)，系統(tǒng)會(huì)提示用戶(hù)為該變量提供一個(gè)具體的值。【例4.59】查詢(xún)XSCJ數(shù)據(jù)庫(kù)XS表計(jì)算機(jī)專(zhuān)業(yè)的同學(xué)情況。 SELECTXHAS學(xué)號(hào),XMAS姓名 FROMXS WHEREZYM=&specialty_name;?4.4.1替換變量1. &替換變量【例4.60】查找平均成績(jī)?cè)?0分以上的學(xué)生的學(xué)號(hào)、姓名和平均成績(jī)。SETVERIFYONSELECT* FROMXS_KC_AVG WHEREscore_avg>=score_avg;執(zhí)行過(guò)程為：輸入score_avg的值：80原值 3：WHEREscore_avg>=score_avg新值 3：WHEREscore_avg>=80替換變量不僅僅可以用在WHERE子句中，而且還可以用在下列部分：(1) ORDERBY子句。(2) 列表達(dá)式。(3) 表名。(4) 整個(gè)SELECT語(yǔ)句?4.4.1替換變量1. &替換變量【例4.61】查找選修了“離散數(shù)學(xué)”課程的學(xué)生學(xué)號(hào)、姓名、課程名及成績(jī)。 SELECTXS.XH,&name,KCM,&column FROMXS,&kc,XS_KC WHEREXS.XH=XS_KC.XHAND&condition ANDKCM=&kcmORDERBY&column;執(zhí)行過(guò)程及結(jié)果為：輸入name的值：XM輸入column的值：CJ原值1：SELECTXS.XH,&name,KCM,&column新值1：SELECTXS.XH,XM,KCM,CJ原值2：FROMXS,&kc,XS_KC新值2：FROMXS,kc,XS_KC輸入condition的值：KC.KCH=XS_KC.KCH輸入kcm的值：‘離散數(shù)學(xué)’原值3：WHEREXS.XH=XS_KC.XHAND&conditionANDKCM=&kcm新值3：WHEREXS.XH=XS_KC.XHANDKC.KCH=XS_KC.KCHANDKCM=’離散數(shù)學(xué)’?4.4.1替換變量輸入column的值:CJ原值4：ORDERBY&column新值4：ORDERBYCJXHXMKCMCJ061113嚴(yán)紅離散數(shù)學(xué)60061104韋嚴(yán)平離散數(shù)學(xué)65061107李明離散數(shù)學(xué)68061109張強(qiáng)民離散數(shù)學(xué)70061101王林離散數(shù)學(xué)71061111趙琳離散數(shù)學(xué)76061102程明離散數(shù)學(xué)78061106李方方離散數(shù)學(xué)80061103王燕離散數(shù)學(xué)81061108林一帆離散數(shù)學(xué)87061110張蔚離散數(shù)學(xué)89?4.4.1替換變量2. &&替換變量在SELECT語(yǔ)句中，如果希望重新使用某個(gè)變量并且不希望重新提示輸入該值，可以使用&&替換變量。在上述例子中，包含了一個(gè)變量&column，這個(gè)變量出現(xiàn)了兩次，如果只是使用“&”符號(hào)來(lái)定義替換變量，那么系統(tǒng)會(huì)提示用戶(hù)輸入兩次該變量。在此，為該變量提供了列名CJ?！纠?.62】查找選修了“離散數(shù)學(xué)”課程的學(xué)生學(xué)號(hào)、姓名、課程名及成績(jī)。執(zhí)行過(guò)程和結(jié)果為：輸入name的值：XM輸入column的值：CJ原值1：SELECTXS.XH,&name,KCM,&column新值1：SELECTXS.XH,XM,KCM,CJ原值2：FROMXS,&kc,XS_KC新值2：FROMXS,kc,XS_KC輸入condition的值：KC.KCH=XS_KC.KCH輸入kcm的值：'離散數(shù)學(xué)'原值3：WHEREXS.XH=XS_KC.XHAND&conditionANDKCM=&kcm新值3：WHEREXS.XH=XS_KC.XHANDKC.KCH=XS_KC.KCHANDKCM='離散數(shù)學(xué)'?4.4.1替換變量輸入column的值:NAME原值4：ORDERBY&column新值4：ORDERBYNAMEXHXMKCMCJ061102程明離散數(shù)學(xué)78061106李方方離散數(shù)學(xué)80061107李明離散數(shù)學(xué)68061108林一帆離散數(shù)學(xué)87061101王林離散數(shù)學(xué)71061103王燕離散數(shù)學(xué)81061104韋嚴(yán)平離散數(shù)學(xué)65061113嚴(yán)紅離散數(shù)學(xué)60061109張強(qiáng)民離散數(shù)學(xué)70061110張蔚離散數(shù)學(xué)89061111趙琳離散數(shù)學(xué)76?4.4.1替換變量【例4.63】查詢(xún)選修課程超過(guò)2門(mén)且成績(jī)?cè)?5分以上的學(xué)生的學(xué)號(hào)。 SELECT&&column FROMXS_KC WHERECJ>=75 GROUPBY&column HAVINGCOUNT(*)>2;執(zhí)行過(guò)程：輸入column的值：XH原值1：