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

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

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)算之外，還定義了一組專門的關(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)算按給定的條件，從表中選出滿足條件的行形成一個(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查詢后的結(jié)果4.1.2投影投影（Projection）也是單目運(yùn)算。投影就是選擇表中指定的列，這樣在查詢結(jié)果中只顯示指定數(shù)據(jù)列，減少了顯示的數(shù)據(jù)量也提高查詢的性能?！纠?.2】若在表4.1中對(duì)“姓名”和“平均成績(jī)”投影，該查詢得到如表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ù)的查詢使用數(shù)據(jù)庫(kù)和表的主要目的是存儲(chǔ)數(shù)據(jù)以便在需要時(shí)進(jìn)行檢索、統(tǒng)計(jì)或組織輸出，通過(guò)PL/SQL的查詢可以從表或視圖中迅速方便地檢索數(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子句，指定查詢條件*/[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】查詢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】查詢XS表中ZXF大于45同學(xué)的XH、XM和ZXF。SELECTXH,XM,ZXFFROMXSWHEREZXF>45;當(dāng)在SELECT語(yǔ)句指定列的位置上使用*號(hào)時(shí)，表示選擇表的所有列?！纠?.7】查詢XS表中的所有列。 SELECT* FROMXS;該語(yǔ)句等價(jià)于語(yǔ)句： SELECTXH,XM,ZYM,XB,CSSJ,ZXF,BZ FROMXS;其執(zhí)行后將列出XS表中的所有數(shù)據(jù)。4.2.1選擇列2.修改查詢結(jié)果中的列標(biāo)題當(dāng)希望查詢結(jié)果中的某些列或所有列顯示時(shí)使用自己選擇的列標(biāo)題時(shí)，可以在列名之后使用AS子句來(lái)更改查詢結(jié)果的列標(biāo)題名，其中column_alias是指定的列標(biāo)題?！纠?.8】查詢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所示。更改查詢結(jié)果中的列標(biāo)題可以省略AS關(guān)鍵字。例如：SELECTXH學(xué)號(hào),XM姓名,ZXF總學(xué)分FROMXSWHEREZYM=’計(jì)算機(jī)’;4.2.1選擇擇列圖4.2更更改查查詢結(jié)結(jié)果中中的列列標(biāo)題題4.2.1選擇擇列3.計(jì)計(jì)算算列值值使用SELECT對(duì)對(duì)列進(jìn)進(jìn)行查查詢時(shí)時(shí)，在在結(jié)果果中可可以輸輸出對(duì)對(duì)列值值計(jì)算算后的的值，，即SELECT子子句可可使用用表達(dá)達(dá)式作作為結(jié)結(jié)果，，格式式為：：SELECTexpression[,expression]【例4.9】創(chuàng)建產(chǎn)產(chǎn)品銷銷售數(shù)數(shù)據(jù)庫(kù)庫(kù)XSH，，并在在其中中創(chuàng)建建產(chǎn)品品表CP，，其表表結(jié)構(gòu)構(gòu)如表表4.10所所表4.10CP表結(jié)構(gòu)4.2.1選擇列設(shè)CP表中中已有如表表4.11所示的數(shù)數(shù)據(jù)。表4.11CP表4.2.1選擇列下列語(yǔ)句句將列出出產(chǎn)品名名稱和產(chǎn)產(chǎn)品總值值：SELECTCPMCAS產(chǎn)產(chǎn)品名稱稱,JG*KCLAS產(chǎn)品品總值FROMCP;該語(yǔ)句的的執(zhí)行結(jié)結(jié)果如圖圖4.3所示。。圖4.3計(jì)算算列值4.2.1選擇擇列4.消消除結(jié)果果集中的的重復(fù)行行【例4.10】】對(duì)XSCJ數(shù)據(jù)據(jù)庫(kù)的XS表只只選擇ZYM和和ZXF，消除除結(jié)果集集中的重重復(fù)行。。SELECTDISTINCTZYMAS專業(yè)業(yè)名,ZXFAS總總學(xué)分分FROMXS;該語(yǔ)句執(zhí)執(zhí)行的結(jié)結(jié)果為：：專業(yè)名總總學(xué)學(xué)分計(jì)算機(jī)48計(jì)算機(jī)50計(jì)算機(jī)52計(jì)算機(jī)54通信工程程40通信工程程42通信工程程44通信工程程50與DISTINCT相相反，當(dāng)當(dāng)使用關(guān)關(guān)鍵字ALL時(shí)時(shí)，將保保留結(jié)果果集的所所有行。。4.2.1選擇擇列【例4.11】】以下的SELECT語(yǔ)語(yǔ)句對(duì)XSCJ數(shù)據(jù)庫(kù)庫(kù)的XS表選擇擇ZYM和ZXF，不不消除結(jié)結(jié)果集中中的重復(fù)復(fù)行。SELECTALLZYMAS專專業(yè)名,ZXFAS總學(xué)學(xué)分FROMXS;該語(yǔ)句執(zhí)執(zhí)行后結(jié)結(jié)果為：：專業(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通信工程程504.2.2選擇擇行1.表表達(dá)式比比較比較運(yùn)算算符用于于比較兩兩個(gè)表達(dá)達(dá)式值，，共有7個(gè)，分分別是：：=（（等于））、<（（小于）、<=（小小于等于于）、>（大于于）、>=（大大于等于于）、<>（不不等于））、!=（不等于）。。比較運(yùn)運(yùn)算的格格式為：：expression{=|<|<=|>|>=|<>|!=}expression當(dāng)兩個(gè)表表達(dá)式值值均不為為空值（（NULL）時(shí)時(shí)，比較較運(yùn)算返返回邏輯輯值TRUE（（真）或FALSE（（假）；；而當(dāng)兩兩個(gè)表達(dá)達(dá)式值中中有一個(gè)個(gè)為空值值或都為為空值時(shí)時(shí)，比較較運(yùn)算將將返回UNKNOWN。。【例4.12】】(1)查查詢XSH數(shù)數(shù)據(jù)庫(kù)CP表中中庫(kù)存量量在500以上上的產(chǎn)品品情況。。SELECT*FROMCPWHEREKCL>500;(2)查查詢XSCJ數(shù)據(jù)庫(kù)庫(kù)XS表表中通信信工程專專業(yè)總學(xué)學(xué)分大于于等于42的同同學(xué)的情情況。SELECT*FROMXSWHEREZYM=’通通信工程程’andZXF>=42;4.2.2選擇擇行2.模式匹配配LIKE謂詞用用于指出出一個(gè)字字符串是是否與指指定的字字符串相相匹配，，其運(yùn)算算對(duì)象可可以是char、varchar2和date類類型的數(shù)數(shù)據(jù)，返返回邏輯輯值TRUE或或FALSE。。LIKE謂詞詞表達(dá)式的格式式為：string_expression[NOT]LIKEstring_expression【例4.13】】查詢XSH數(shù)據(jù)據(jù)庫(kù)CP表中產(chǎn)產(chǎn)品名含含有“冰冰箱”的的產(chǎn)品情情況。SELECT*FROMCPWHERECPMCLIKE‘‘%冰冰箱%’’;執(zhí)行結(jié)果果為：CPBHCPMCJGKCL10001100冰冰箱A_1001500.050010002120冰冰箱箱A_2001850.020010001200冰冰箱箱B_2001600.0120010001102冰冰箱C_2101890.06004.2.2選擇擇行【例4.14】】查詢XSCJ數(shù)數(shù)據(jù)庫(kù)庫(kù)XS表中中姓““王””且單單名的的學(xué)生生情況況。SELECT*FROMXSWHEREXMLIKE‘‘王王_’’;執(zhí)行結(jié)結(jié)果為為：XHXMZYMXBCSSJZXFBZ061101王王林計(jì)計(jì)算機(jī)機(jī)男男10-二二月-8650061103王王燕計(jì)計(jì)算機(jī)機(jī)女女06-十十月-8550061201王王敏通通信工工程男男10-六月月-8442061202王王林通通信工工程男男29-一一月-8540有有一一門課課不及及格，，待補(bǔ)補(bǔ)考3.范范圍圍比較較用于范范圍比比較的的關(guān)鍵鍵字有有兩個(gè)個(gè)：BETWEEN和IN。。當(dāng)要查查詢的的條件件是某某個(gè)值值的范范圍時(shí)時(shí)，可可以使使用BETWEEN關(guān)鍵鍵字。。BETWEEN關(guān)關(guān)鍵字字指出出查詢范范圍，，格式式為：：expression[NOT]BETWEENexpression1ANDexpression2當(dāng)不使使用NOT時(shí)，，若表表達(dá)式式expression的值值在表表達(dá)式式expression1與與expression2之之間（（包括這這兩兩個(gè)個(gè)值值）），，則則返返回回TRUE，，否否則則返返回回FALSE；；使使用用NOT時(shí)時(shí)，，返返回回值值剛剛好好相相反反。。注意意：expression1的的值值不不能能大大于于expression2的的值值。。4.2.2選選擇擇行行【例例4.15】】(1)查詢?cè)僗SH數(shù)數(shù)據(jù)據(jù)庫(kù)庫(kù)CP表表中中價(jià)價(jià)格格在在2000元元與與4000元元之之間間的的產(chǎn)產(chǎn)品品情情況況。。SELECT*FROMCPWHEREJGBETWEEN2000AND4000;(2)查查詢?cè)僗SCJ數(shù)數(shù)據(jù)據(jù)庫(kù)庫(kù)XS表表中中不不在在1985年年出出生生的的學(xué)學(xué)生生情情況況。。SELECT*FROMXSWHERECSSJNOTBETWEENTO_DATE(‘‘19850101’’,’’YYYYMMDD’’)andTO_DATE(‘‘19851231’’,’’YYYYMMDD’’);使用用IN關(guān)關(guān)鍵鍵字字可可以以指指定定一一個(gè)個(gè)值值表表，，值值表表中中列列出出所所有有可可能能的的值值，，當(dāng)當(dāng)表表達(dá)達(dá)式式與與值值表表中中的的任任一一個(gè)個(gè)匹匹配時(shí)時(shí)，，即即返返回回TRUE，，否否則則返返回回FALSE。。使使用用IN關(guān)關(guān)鍵鍵字字指指定定值值表表的的格格式式為為：：expressionIN(expression[,……n])【例例4.16】】查詢XSH數(shù)數(shù)據(jù)庫(kù)CP表表中庫(kù)存量為為“200””、“300”和“500”的情況況。SELECT*FROMCPWHEREKCLIN(200,300,500);該語(yǔ)句與下列列語(yǔ)句等價(jià)：：SELECT*FROMCPWHEREKCL=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é)果果剛好相反。。【例4.17】】查詢XSCJ數(shù)數(shù)據(jù)庫(kù)庫(kù)中總總學(xué)分分尚不不定的的學(xué)生生情況況。SELECT*FROMXSWHEREZXFISNULL;4.2.2選擇擇行5.子子查查詢?cè)诓樵冊(cè)儣l件件中，，可以以使用用另一一個(gè)查查詢的的結(jié)果果作為為條件件的一一部分分，例例如判判定列列值是是否與某個(gè)查查詢的結(jié)果果集中的值值相等，作作為查詢條條件一部分分的查詢稱稱為子查詢?cè)?。PL/SQL允許SELECT多層嵌嵌套使用，，用來(lái)表示示復(fù)雜的查查詢。子查查詢除了可可以用在SELECT語(yǔ)句句中，還可可以用在INSERT、UPDATE及DELETE語(yǔ)語(yǔ)句中。子查詢通常常與IN、、EXIST謂詞及及比較運(yùn)算算符結(jié)合使使用。(1)IN子查詢?cè)僆N子查詢?cè)冇糜谶M(jìn)行行一個(gè)給定定值是否在在子查詢結(jié)結(jié)果集中的的判斷，格格式為：expression[NOT]IN(subquery)其中subquery是子查查詢。當(dāng)表表達(dá)式expression與子查詢?cè)僺ubquery的結(jié)果表表中的某個(gè)值相等時(shí)時(shí)，IN謂謂詞返回TRUE，，否則返回回FALSE；若使使用了NOT，則返返回的值剛好相反。。4.2.2選擇行【例4.18】在XSCJ數(shù)據(jù)庫(kù)中中有描述課課程情況的的表KC和和描述學(xué)生生成績(jī)表的的表XS_KC(表的結(jié)結(jié)構(gòu)和樣本本數(shù)據(jù)見附附錄A)。。查找選修修了課程號(hào)號(hào)為101的課程的的學(xué)生的情況：SELECT*FROMXSWHEREXHIN(SELECTXHFROMXS_KCWHEREKCH=‘‘101’’);在執(zhí)行包含含子查詢的的SELECT語(yǔ)句句時(shí)，系統(tǒng)統(tǒng)先執(zhí)行子子查詢，產(chǎn)產(chǎn)生一個(gè)結(jié)結(jié)果表，再再執(zhí)行查詢。。本例中，，先執(zhí)行子子查詢：SELECTXHFROMXS_KCWHEREKCH=‘‘101’’;得到一個(gè)只只含有學(xué)號(hào)號(hào)列的表，，XS_KC中課程程名列值為為‘101’的行在在結(jié)果表中中都有一行。再執(zhí)執(zhí)行外查詢?cè)?，若XS表中某行行的學(xué)號(hào)列列值等于子子查詢結(jié)果果表中的任任一個(gè)值，則該行行就被選擇擇。4.2.2選擇行【例4.19】查找未選修修離散數(shù)學(xué)學(xué)的學(xué)生的的情況。SELECTXH,XM,ZYM,ZXFFROMXSWHEREXHNOTIN(SELECTXHFROMXS_KCWHEREKCHIN(SELECTKCHFROMKCWHEREKCM='離散數(shù)數(shù)學(xué)'));執(zhí)行結(jié)果為為：XHXMZYMZXF061201王王敏敏通通信工程程42061202王王林通通信工程40061203王王玉民民通通信工工程42061204馬馬琳琳琳通通信工工程42061206李李計(jì)計(jì)通通信工程程42061210李李紅慶慶通通信工工程44061216孫孫祥欣欣通通信工工程42061218孫孫研通通信工程42061220吳吳薇華華通通信工工程42061221劉劉燕敏敏通通信工工程42061241羅羅林琳琳通通信工工程504.2.2選擇行2) 比較較子查詢這種子查詢?cè)兛梢哉J(rèn)為為是IN子子查詢的擴(kuò)擴(kuò)展，它使使表達(dá)式的的值與子查查詢的結(jié)果果進(jìn)行比較運(yùn)算，格格式為：expression{<|<=|=|>|>=|!=|<>}{ALL|SOME|ANY}(subquery)其中expression為為要進(jìn)行行比較的的表達(dá)式式，subquery是子查查詢。ALL、、SOME和ANY說(shuō)明對(duì)比比較運(yùn)算算的限制制。ALL指指定表達(dá)達(dá)式要與與子查詢?cè)兘Y(jié)果集集中的每每個(gè)值都都進(jìn)行比比較，當(dāng)當(dāng)表達(dá)式式與每個(gè)個(gè)值都滿滿足比較的的關(guān)系時(shí)時(shí)，才返返回TRUE，，否則返返回FALSE；SOME或或ANY表示表表達(dá)式只只要與子查詢?cè)兘Y(jié)果集集中的某某個(gè)值滿滿足比較較的關(guān)系系時(shí)，就就返回TRUE，否則則返回FALSE。4.2.2選擇擇行【例4.20】查找比比所有計(jì)計(jì)算機(jī)系系學(xué)生年年齡都大大的學(xué)生生。SELECT*FROMXSWHERECSSJ<ALL(SELECTCSSJFROMXSWHEREZYM='計(jì)計(jì)算機(jī)');執(zhí)行結(jié)果果如圖4.4所所示。圖4.4查查找結(jié)果果4.2.2選擇擇行【例4.21】查找課課程號(hào)206的的成績(jī)不不低于課課程號(hào)101的的最低成成績(jī)的學(xué)學(xué)生的學(xué)學(xué)號(hào)。SELECTXHFROMXS_KCWHEREKCH='206'ANDCJ>=ANY(SELECTCJFROMXS_KCWHEREKCH='101');(3)EXISTS子查詢?cè)僂XISTS謂謂詞用于于測(cè)試子子查詢的的結(jié)果是是否為空空表，若若子查詢?cè)兊慕Y(jié)果果集不為為空，則則EXISTS返返回TRUE，，否則返返回FALSE。EXISTS還可可與NOT結(jié)合合使用，，即NOTEXISTS，，其其返返回回值值與與EXIST剛剛好好相相反反。。格格式式為為：：[NOT]EXISTS(subquery)4.2.2選選擇擇行行【例4.22】查查找找選選修修206號(hào)號(hào)課課程程的的學(xué)學(xué)生生姓姓名名。。SELECTXMFROMXSWHEREEXISTS(SELECT*FROMXS_KCWHEREXH=XS.XHANDKCH=‘‘206’’);本例例在在子子查查詢?cè)兊牡臈l條件件中中使使用用了了限限定定形形式式的的列列名名引引用用XS.XH，，表表示示這這里里的的學(xué)學(xué)號(hào)號(hào)列列出出自表表XS。。4.2.2選選擇擇行行【例4.23】查查找找選選修修了了全全部部課課程程的的同同學(xué)學(xué)的的姓姓名名。。SELECTXMFROMXSWHERENOTEXISTS(SELECT*FROMKCWHERENOTEXISTS(SELECT*FROMXS_KCWHEREXH=XS.XHANDKCH=KC.KCH));本例例即即查查找找沒沒有有一一門門功功課課不不選選修修的的學(xué)學(xué)生生。。4.2.3查查詢?cè)儗?duì)對(duì)象象前面面介介紹紹了了SELECT的的選選擇擇列列和和行行，，這這里里介介紹紹SELECT查查詢?cè)兊牡膶?duì)對(duì)象象（（即即數(shù)數(shù)據(jù)據(jù)源源））的構(gòu)成成形式式?！纠?.24】】查找001102號(hào)號(hào)同學(xué)學(xué)所選選修的的全部部課程程的同同學(xué)的的學(xué)號(hào)號(hào)。本例即即要查查找這這樣的的學(xué)號(hào)號(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ǔ)句句查詢的的對(duì)象是是表。4.2.3查詢?cè)儗?duì)象【例4.25】】在XS表表中查找找1986年1月1日日以前出出生的學(xué)學(xué)生的姓姓名和專專業(yè)名。。SELECTXM,ZYMFROM(SELECT*FROMXSWHERECSSJ<TO_DATE(‘19860101’,’’YYYYMMDD'));執(zhí)行結(jié)果果為：XMZYM王燕計(jì)計(jì)算機(jī)機(jī)林一帆計(jì)計(jì)算機(jī)張強(qiáng)民計(jì)計(jì)算機(jī)嚴(yán)紅計(jì)計(jì)算機(jī)機(jī)王敏通通信工工程王林通通信工工程馬琳琳通通信工程程李計(jì)通通信工工程李紅慶通通信工程程孫祥欣通通信工程程劉燕敏通通信工程程4.2.4連接接1.連連接謂詞詞可以在SELECT語(yǔ)語(yǔ)句的WHERE子句句中使用用比較運(yùn)運(yùn)算符給給出連接接條件對(duì)對(duì)表進(jìn)行行連接，將這這種表示示形式稱稱為連接接謂詞表表示形式式?！纠?.26】】查找XSCJ數(shù)數(shù)據(jù)庫(kù)每每個(gè)學(xué)生生的情況況以及選選修的課課程情況況。SELECTXS.*,XS_KC.*FROMXS,XS_KCWHEREXS.XH=XS_KC.XH;結(jié)果表將將包含XS表和和XS_KC表表的所有有列，結(jié)結(jié)果如圖圖4.5所示。。圖4.5連連接后的的部分?jǐn)?shù)數(shù)據(jù)4.2.4連接接【例4.27】】自然連接接查詢。。SELECTXS.*,XS_KC.KCH,XS_KC.CJFROMXS,XS_KCWHEREXS.XH=XS_KC.XH;本例所得得的結(jié)果果表包含含以下字字段：學(xué)學(xué)號(hào)、姓姓名、專專業(yè)名、、性別、、出生時(shí)時(shí)間、總總學(xué)分、備注注、課程程號(hào)、成成績(jī)。若選擇的的字段名名在各個(gè)個(gè)表中是是唯一的的，則可可以省略略字段名名前的表表名。如如本例的的SELECT子子句也可可寫為：：SELECTXS.*,KCH,CJFROMXS,XS_KCWHEREXS.XH=XS_KC.XH;4.2.4連接接【例4.28】】查找選修修了206課程程且成績(jī)績(jī)?cè)?0分以上上的學(xué)生生姓名及及成績(jī)。。SELECTXMAS姓姓名,CJAS成成績(jī)FROMXS,XS_KCWHEREXS.XH=XS_KC.XHANDKCH=‘‘206’ANDCJ>=80;執(zhí)行結(jié)果果為：姓名成成績(jī)王燕81李方方80林一帆87張蔚89有時(shí)用戶戶所需要要的字段段來(lái)自兩兩個(gè)以上上的表，，那么就就要對(duì)兩兩個(gè)以上上的表進(jìn)進(jìn)行連接接，稱之為多多表連接接。4.2.4連接接【例4.29】】查找選修修了“計(jì)計(jì)算機(jī)基基礎(chǔ)”課課程且成成績(jī)?cè)?0分以以上的學(xué)學(xué)生學(xué)號(hào)號(hào)、姓名名、課程名及及成績(jī)。。SELECTXS.XH,XM,KCM,CJFROMXS,KC,XS_KCWHEREXS.XH=XS_KC.XHANDKC.KCH=XS_KC.KCHANDKCM=‘‘計(jì)算算機(jī)基礎(chǔ)礎(chǔ)’ANDCJ>=80;執(zhí)行結(jié)果果為：XHXMKCMCJ001101王王林計(jì)計(jì)算算機(jī)基礎(chǔ)礎(chǔ)80001104韋韋嚴(yán)平平計(jì)計(jì)算機(jī)機(jī)基礎(chǔ)90001108林林一帆帆計(jì)計(jì)算機(jī)機(jī)基礎(chǔ)85001110張張蔚計(jì)計(jì)算算機(jī)基礎(chǔ)礎(chǔ)95001111 趙琳計(jì)計(jì)算算機(jī)基礎(chǔ)91001201 王敏計(jì)計(jì)算算機(jī)基礎(chǔ)80001203 王玉民計(jì)計(jì)算機(jī)機(jī)基礎(chǔ)87001204 馬琳琳計(jì)計(jì)算機(jī)機(jī)基礎(chǔ)91001216 孫祥欣計(jì)計(jì)算機(jī)機(jī)基礎(chǔ)81001220 吳薇華計(jì)計(jì)算機(jī)機(jī)基礎(chǔ)82001241 羅林琳計(jì)計(jì)算機(jī)機(jī)基礎(chǔ)904.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表表示連接類型型，ON用于于指定連接條條件。join_type的格格式為：[INNER|{LEFT|RIGHT|FULL}[OUTER][<join_hint>]JOIN其中INNER表示示內(nèi)連接，，OUTER表示外外連接，join_hint是連接提提示。CROSSJOIN表表示交叉連連接。因此此，以JOIN關(guān)鍵鍵字指定的的連接有三三種類型。。4.2.4連接2. 以JOIN關(guān)關(guān)鍵字指定定的連接(1)內(nèi)內(nèi)連接內(nèi)連接按照照ON所指指定的連接接條件合并并兩個(gè)表，，返回滿足足條件的行行?！纠?.30】查找XSCJ數(shù)據(jù)庫(kù)庫(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á)下列列查詢：查查找選修了了206課課程且成績(jī)績(jī)?cè)?0分以上上的學(xué)生姓姓名及成績(jī)績(jī)。SELECTXM,CJFROMXSJOINXS_KCONXS.XH=XS_KC.XHWHEREKCH='206'ANDCJ>=80;內(nèi)連接還可可以用于多多個(gè)表的連連接。4.2.4連接【例4.32】用FROM的JOIN關(guān)鍵字字表達(dá)下列列查詢：查查找選修了了“計(jì)算機(jī)機(jī)基礎(chǔ)”課課程且成績(jī)?cè)?0分以上上的學(xué)生學(xué)學(xué)號(hào)、姓名名、課程名名及成績(jī)。。SELECTXS.XH,XM,KCM,CJFROMXSJOINXS_KCJOINKCONXS_KC.KCH=KC.KCHONXS.XH=XS_KC.XHWHEREKCM='計(jì)算機(jī)基基礎(chǔ)'ANDCJ>=80;作為一種特特例，可以以將一個(gè)表表與它自身身進(jìn)行連接接，稱為自自連接。若若要在一個(gè)個(gè)表中查找具有相相同列值的的行，則可可以使用自自連接。使使用自連接接時(shí)需為表表指定兩個(gè)個(gè)別名，且對(duì)所所有列的引引用均要用用別名限定定?！纠?.33】查找不同課課程成績(jī)相相同的學(xué)生生的學(xué)號(hào)、、課程號(hào)和和成績(jī)。SELECTa.XH,a.KCH,b.KCH,a.CJFROMXS_KCaJOINXS_KCbONa.CJ=b.CJANDa.XH=b.XHANDa.KCH!=b.KCH;執(zhí)行結(jié)果果為：XHKCHKCHCJ00110210220678001102206102784.2.4連接接2.以以JOIN關(guān)鍵鍵字指定定的連接接(2)外外連接接外連接的的結(jié)果表表不但包包含滿足足連接條條件的行行，還包包括相應(yīng)應(yīng)表中的的所有行行。外連連接包括三種種：左外連接接（LEFTOUTERJOIN）：：結(jié)果表表中除了了包括滿滿足連接接條件的的行外，，還包括左左表的的所有有行；；右外連接（（RIGHTOUTERJOIN）：結(jié)果果表中除了了包括滿足足連接條件件的行外，，還包括右表的的所有行；；完全外連接接（FULLOUTERJOIN）：結(jié)果果表中除了了包括滿足足連接條件件的行外，，還包括兩個(gè)表表的所有行行。其中的OUTER關(guān)關(guān)鍵字均可可省略。4.2.4連接【例4.34】查找所有學(xué)學(xué)生情況及及他們選修修的課程號(hào)號(hào)，若學(xué)生生未選修任任何課，也也要包括其情情況。SELECTXS.*,KCHFROMXSLEFTOUTERJOINXS_KCONXS.XH=XS_KC.XH;本例執(zhí)行行時(shí)，若若有學(xué)生生未選任任何課程程，則結(jié)結(jié)果表中中相應(yīng)行行的課程程號(hào)字段段值為NULL?！纠?.35】】查找被選選修了的的課程的的選修情情況和所所有開設(shè)設(shè)的課程程名。SELECTXS_KC.*,KCMFROMXS_KCRIGHTJOINKCONXS_KC.KCH=KC.KCH;本例執(zhí)行行時(shí)，若若某課程程未被選選修，則則結(jié)果表表中相應(yīng)應(yīng)行的學(xué)學(xué)號(hào)、課課程號(hào)和和成績(jī)字字段值均為NULL。。注意：：外連接接只能能對(duì)兩兩個(gè)表表進(jìn)行行。4.2.4連接接2.以以JOIN關(guān)關(guān)鍵字字指定定的連連接(3)交交叉連連接交叉連連接實(shí)實(shí)際上上是將將兩個(gè)個(gè)表進(jìn)進(jìn)行笛笛卡爾爾積運(yùn)運(yùn)算，，結(jié)果果表是是由第第一個(gè)個(gè)表的的每行行與第第二個(gè)表表的每每一行行拼接接后形形成的的表，，因此此結(jié)果果表的的行數(shù)數(shù)等于于兩個(gè)個(gè)表行行數(shù)之之積。?！纠?.36】列出出學(xué)生生所有有可能能的選選課情情況。。SELECTXH,XM,KCH,KCMFROMXSCROSSJOINKC;注意:交交叉連連接不不能有有條件件，且且不能能帶WHERE子句句。4.2.5匯總總1.統(tǒng)統(tǒng)計(jì)計(jì)函數(shù)數(shù)統(tǒng)計(jì)函函數(shù)用用于計(jì)計(jì)算表表中的的數(shù)據(jù)據(jù)，返返回單單個(gè)計(jì)計(jì)算結(jié)結(jié)果。。下面面對(duì)常常用的的幾個(gè)個(gè)統(tǒng)計(jì)計(jì)函數(shù)數(shù)加以介介紹。。（1））SUM和和AVG函函數(shù)。。SUM和和AVG函函數(shù)分分別用用于求求表達(dá)達(dá)式中中所有有值項(xiàng)項(xiàng)的總總和與平均均值，，語(yǔ)法法格式式為：：SUM/AVG([ALL|DISTINCT]expression)其中中expression是是常常量量、、列列、、函函數(shù)數(shù)或或表表達(dá)達(dá)式式。。SUM和和AVG函函數(shù)數(shù)只只能能對(duì)對(duì)數(shù)數(shù)值值型型數(shù)數(shù)據(jù)據(jù)進(jìn)行行計(jì)計(jì)算算。。ALL表表示示對(duì)對(duì)所所有有值值進(jìn)進(jìn)行行運(yùn)運(yùn)算算，，DISTINCT表表示示去去除除重重復(fù)復(fù)值值，，缺缺省省為為ALL。。SUM/AVG忽忽略略NULL值值。?！纠?.37】】求選選修修101課課程程的的學(xué)學(xué)生生的的平平均均成成績(jī)績(jī)。。SELECTAVG(CJ)AS課課程101平均均成績(jī)FROMXS_KCWHEREKCH='101';執(zhí)行結(jié)果果為：課程101平均均成績(jī)784.2.5匯總總1.統(tǒng)統(tǒng)計(jì)函函數(shù)（2）MAX和和MIN函數(shù)。。MAX和MIN函數(shù)數(shù)分別用用于求表表達(dá)式中中所有值值項(xiàng)的最最大值與最小值值，語(yǔ)法法格式為為：MAX/MIN([ALL|DISTINCT]expression)其中expression是是常量、、列、函函數(shù)或表表達(dá)式，，其數(shù)據(jù)據(jù)類型可可以是數(shù)數(shù)字、字字符和時(shí)時(shí)間日期類類型。ALL表表示對(duì)所所有值進(jìn)進(jìn)行運(yùn)算算，DISTINCT表示去去除重復(fù)復(fù)值，缺缺省為ALL。。MAX/MIN忽略略NULL值。?！纠?.38】】求選修101課課程的學(xué)學(xué)生的最最高分和和最低分分。SELECTMAX(CJ)AS課課程101的最最高分,MIN(CJ)AS課課程101的的最低分分FROMXS_KCWHEREKCH='101';執(zhí)行結(jié)果果為：課程101的最最高分課課程101的的最低分分95624.2.5匯總總1.統(tǒng)統(tǒng)計(jì)函函數(shù)（3）COUNT函數(shù)數(shù)。COUNT函數(shù)用用于統(tǒng)計(jì)計(jì)組中滿滿足條件件的行數(shù)數(shù)或總行行數(shù)，格格式為：：COUNT({[ALL|DISTINCT]expression}|*)其中expression是是一個(gè)表達(dá)達(dá)式。ALL表示對(duì)對(duì)所有值進(jìn)進(jìn)行運(yùn)算，，DISTINCT表示去除除重復(fù)值，缺省為為ALL。。選擇*時(shí)時(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)統(tǒng)計(jì)離散數(shù)數(shù)學(xué)課程成成績(jī)?cè)?5分以上的的人數(shù)。SELECTCOUNT(CJ)AS離離散數(shù)學(xué)85分以上上的人數(shù)FROMXS_KCWHERECJ>=85ANDKCH=(SELECTKCHFROMKCWHEREKCM='離離散數(shù)學(xué)');執(zhí)行結(jié)果為為：離散數(shù)學(xué)85分以上上的人數(shù)24.2.5匯總2. GROUPBY子句句GROUPBY子子句用于對(duì)對(duì)表或視圖圖中的數(shù)據(jù)據(jù)按字段分分組，格式式為：[GROUPBY[ALL]group_by_expression[,…n]group_by_expression：用用于分組的的表達(dá)式，，其中通常常包含字段段名。指定定ALL將顯示所有有組。使用用GROUPBY子句后，，SELECT子句句中的列表表中只能包包含在GROUPBY中中指出的列列或在統(tǒng)計(jì)計(jì)函數(shù)中指指定的列。?！纠?.40】將XSCJ數(shù)據(jù)庫(kù)中中各專業(yè)名名輸出。SELECTZYMAS專業(yè)名名FROMXSGROUPBYZYM;執(zhí)行結(jié)果為為：專業(yè)名計(jì)算機(jī)通信工程4.2.5匯總【例4.41】求XSCJ數(shù)據(jù)庫(kù)中中各專業(yè)的的學(xué)生數(shù)。。SELECTZYMAS專業(yè)名名,COUNT(*)AS學(xué)生數(shù)數(shù)FROMXSGROUPBYZYM;執(zhí)行結(jié)果為為：專業(yè)名學(xué)學(xué)生數(shù)計(jì)算機(jī)11通信工程11【例4.42】求被選修的的各門課程程的平均成成績(jī)和選修修該課程的的人數(shù)。SELECTKCHAS課程號(hào),AVG(CJ)AS平平均成績(jī)績(jī),COUNT(XH)AS選修修人數(shù)FROMXS_KCGROUPBYKCH;執(zhí)行結(jié)果為為：課程號(hào)平平均成成績(jī)選選修修人數(shù)1017820102771120675114.2.5匯總3. HAVING子句使用GROUPBY子句和和統(tǒng)計(jì)函數(shù)數(shù)對(duì)數(shù)據(jù)進(jìn)進(jìn)行分組后后，還可以以使用HAVING子句對(duì)分組數(shù)據(jù)進(jìn)進(jìn)行進(jìn)一步步的篩選。。例如查找找XSCJ數(shù)據(jù)庫(kù)中中平均成績(jī)績(jī)?cè)?5分分以上的學(xué)學(xué)生，就是在在XS_KC數(shù)據(jù)庫(kù)庫(kù)上按學(xué)號(hào)號(hào)分組后篩篩選出符合合平均成績(jī)績(jī)大于等于于85的學(xué)學(xué)生。HAVING子子句的格式式為：[HAVING<search_condition>]其中search_condition為查詢?cè)儣l件，與與WHERE子句的的查詢條件件類似，并并且可以使用統(tǒng)計(jì)函函數(shù)?！纠?.43】查找XSCJ數(shù)據(jù)庫(kù)庫(kù)中平均成成績(jī)?cè)?5分以上的的學(xué)生的學(xué)學(xué)號(hào)和平均均成績(jī)。SELECTXHAS學(xué)學(xué)號(hào),AVG(CJ)AS平平均成績(jī)FROMXS_KCGROUPBYXHHAVINGAVG(CJ)>=85;執(zhí)行結(jié)果為：：學(xué)號(hào)平平均成績(jī)績(jī)001110910012038700120491001241904.2.5匯匯總【例4.44】查找選修課程程超過(guò)2門且且成績(jī)都在80分以上的的學(xué)生的學(xué)號(hào)號(hào)。SELECTXHAS學(xué)號(hào)FROMXS_KCWHERECJ>=80GROUPBYXHHAVINGCOUNT(*)>2;查詢將XS_KC表中成成績(jī)大于或等等于80的記記錄按學(xué)號(hào)分分組，對(duì)每組組記錄計(jì)數(shù)，，選出記錄數(shù)大于于2的各組的的學(xué)號(hào)值形成成結(jié)果表?！纠?.45】查找通信工程程專業(yè)平均成成績(jī)?cè)?5分分以上的學(xué)生生的學(xué)號(hào)和平平均成績(jī)。SELECTXHAS學(xué)號(hào),AVG(CJ)AS平平均成績(jī)FROMXS_KCWHEREXHIN(SELECTXHFROMXSWHEREZYM='通信工程程')GROUPBYXHHAVINGAVG(CJ)>=85;4.2.6排排序在應(yīng)用中經(jīng)常常要對(duì)查詢的的結(jié)果排序輸輸出，例如學(xué)學(xué)生成績(jī)由高高到低排序。。在SELECT語(yǔ)句中，使使用ORDERBY子子句對(duì)查詢結(jié)結(jié)果進(jìn)行排序序。ORDERBY子句的格式為為：[ORDERBY{order_by_expression[ASC|DESC]}[,…n]其中order_by_expression是排序表達(dá)達(dá)式，可以是是列名、表達(dá)達(dá)式或一個(gè)正正整數(shù)，當(dāng)expression是一個(gè)正整整數(shù)時(shí)，表示示按表中的該該位置上列排排序。關(guān)鍵字字ASC表示升序排列列，DESC表示降序排排列，系統(tǒng)默默認(rèn)值為ASC?！纠?.46】將通信工程專專業(yè)的學(xué)生按按出生時(shí)間先先后排序。SELECT*FROMXSWHEREZYM='通信工程程'ORDERBYCSSJ;4.2.6排排序【例4.47】將計(jì)算機(jī)專業(yè)業(yè)學(xué)生的“計(jì)計(jì)算機(jī)基礎(chǔ)””課程成績(jī)按按降序排列。。SELECTXMAS姓名,KCMAS課程程名,CJAS成成績(jī)FROMXS,KC,XS_KCWHEREXS.XH=XS_KC.XHANDXS_KC.KCH=KC.KCHANDKCM='計(jì)計(jì)算機(jī)基礎(chǔ)'ANDZYM='計(jì)算機(jī)'ORDERBYCJDESC;執(zhí)行的結(jié)果為為：姓名 課程程名成成績(jī)張蔚 計(jì)算算機(jī)基礎(chǔ)95趙琳 計(jì)算算機(jī)基礎(chǔ)91韋嚴(yán)平計(jì)計(jì)算機(jī)基礎(chǔ)90林一帆計(jì)計(jì)算機(jī)基礎(chǔ)85王林 計(jì)算算機(jī)基礎(chǔ)80李明 計(jì)算算機(jī)基礎(chǔ)78張強(qiáng)民計(jì)計(jì)算機(jī)基礎(chǔ)66李方方計(jì)計(jì)算機(jī)基礎(chǔ)65嚴(yán)紅 計(jì)算算機(jī)基礎(chǔ)63王燕 計(jì)算算機(jī)基礎(chǔ)624.2.7SELECT語(yǔ)句的UNION子子句使用UNION子句可以以將兩個(gè)或多多個(gè)SELECT查詢的的結(jié)果合并成成一個(gè)結(jié)果集集，其格式為：{<queryspecification>|(<queryexpression>)}UNION[ALL]<queryspecification>|(<queryexpression>)[UNION[ALL]<queryspecification>|(<queryexpression>)[…n]]其中queryspecification和queryexpression都是SELECT查詢語(yǔ)句。。使用UNION組合合兩個(gè)查詢的的結(jié)果集的基基本規(guī)則是：：(1)所有有查詢中的列列數(shù)和列的順順序必須相同同。(2)數(shù)據(jù)據(jù)類型必須兼兼容。關(guān)鍵字ALL表示合并的的結(jié)果中包括括所有行，不不去除重復(fù)行行。不使用ALL則在合合并的結(jié)果去除重重復(fù)行。含有有UNION的SELECT查詢也也稱為聯(lián)合查查詢。4.2.7SELECT語(yǔ)語(yǔ)句句的的UNION子子句句【例例4.48】】設(shè)在在XSCJ數(shù)數(shù)據(jù)據(jù)庫(kù)庫(kù)中中建建兩兩個(gè)個(gè)表表：：數(shù)數(shù)學(xué)學(xué)系系學(xué)學(xué)生生、、外外語(yǔ)語(yǔ)系系學(xué)學(xué)生生，，表表結(jié)結(jié)構(gòu)構(gòu)與與XS表表相相同同，，兩兩個(gè)個(gè)表表分分別別存存儲(chǔ)儲(chǔ)數(shù)數(shù)學(xué)學(xué)系系和和外外語(yǔ)語(yǔ)系系的的學(xué)學(xué)生生情情況況，，下下列列語(yǔ)語(yǔ)句句將將這這兩兩個(gè)個(gè)表表的數(shù)數(shù)據(jù)據(jù)合合并并到到XS表表中中。。SELECT*FROMXSUNIONALLSELECT*FROM數(shù)數(shù)學(xué)學(xué)系系學(xué)學(xué)生生UNIONALLSELECT*FROM外外語(yǔ)語(yǔ)系系學(xué)學(xué)生生;UNION操操作作常常用用于于歸歸并并數(shù)數(shù)據(jù)據(jù)，，例例如如歸歸并并月月報(bào)報(bào)表表形形成成年年報(bào)報(bào)表表，，歸歸并并各各部部門門數(shù)數(shù)據(jù)據(jù)等等。。注意UNION還可以以與GROUPBY及ORDERBY一起使使用，用用來(lái)對(duì)合合并所得得的結(jié)果表進(jìn)行行分組或或排序。。4.3數(shù)數(shù)據(jù)庫(kù)的的視圖4.3.1視圖圖的概念念視圖是從從一個(gè)或或多個(gè)表表（或視視圖）導(dǎo)導(dǎo)出的表表。視圖與表表（有時(shí)時(shí)為與視視圖區(qū)別別，也稱稱表為基基表———BaseTable）不不同，視視圖是一一個(gè)虛表，，即視圖圖所對(duì)應(yīng)應(yīng)的數(shù)據(jù)據(jù)不進(jìn)行行實(shí)際存存儲(chǔ)，數(shù)數(shù)據(jù)庫(kù)中中只存儲(chǔ)儲(chǔ)視圖的的定義，，對(duì)視圖的數(shù)數(shù)據(jù)進(jìn)行行操作時(shí)時(shí)，系統(tǒng)統(tǒng)根據(jù)視視圖的定定義去操操作與視視圖相關(guān)關(guān)聯(lián)的基基表。視圖可以以由以下下任意一一項(xiàng)組成成：(1)一一個(gè)基基表的任任意子集集(2)兩兩個(gè)或或者兩個(gè)個(gè)以上基基表的合合集(3)兩兩個(gè)或或者兩兩個(gè)以以上基基表的的交集集(4)對(duì)對(duì)一個(gè)個(gè)或者者多個(gè)個(gè)基表表運(yùn)算算的結(jié)結(jié)果集集合另一個(gè)個(gè)視圖圖的子子集4.3.1視圖圖的概概念視圖一一經(jīng)定定義以以后，，就可可以象象表一一樣被被查詢?cè)?、修修改、、刪除除和更更新。。使用用視圖圖有有下下列列優(yōu)優(yōu)點(diǎn)點(diǎn)：：(1)為為用用戶戶集集中中數(shù)數(shù)據(jù)據(jù)，，簡(jiǎn)簡(jiǎn)化化用用戶戶的的數(shù)數(shù)據(jù)據(jù)查查詢?cè)兒秃吞幪幚砝?。?2) 屏蔽蔽數(shù)據(jù)庫(kù)的復(fù)復(fù)雜性。(3) 簡(jiǎn)化化用戶權(quán)限的的管理。(4) 便于于數(shù)據(jù)共享。。(5)可以以重新組織數(shù)數(shù)據(jù)以便輸出出到其它應(yīng)用用程序中。4.3.2創(chuàng)創(chuàng)建視圖1.用OEM創(chuàng)建視視圖以在XSCJ數(shù)據(jù)庫(kù)中創(chuàng)創(chuàng)建CS_XS（描述計(jì)計(jì)算機(jī)專業(yè)學(xué)學(xué)生情況）視視圖說(shuō)明創(chuàng)建建視圖的過(guò)程程。(1)在在如圖4.6所示的界面面中，選擇單單擊視圖進(jìn)入入“視圖搜索索”界面，如如圖4.7所所示。在該界界面選擇對(duì)象類型型,并可以以選擇輸入方方案名稱和對(duì)對(duì)象名稱,單單擊“開始始”按鈕，查查找滿足條件件的對(duì)象類型，若存在在滿足條件的的對(duì)象類型，，在結(jié)果項(xiàng)可可以查找到的的對(duì)象類型。。圖4.6Oracle企業(yè)管理器器圖4.7視圖圖搜索界面4.3.2創(chuàng)創(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)建視圖創(chuàng)建界面有三三個(gè)選項(xiàng)頁(yè)面面：一般信息息、選項(xiàng)和對(duì)對(duì)象選項(xiàng)頁(yè)面面。一般信息息頁(yè)面如圖4.8所示示，主主要定定義視視圖的的基本本信息息，如如名稱稱、所所屬用用戶方方案等等。選選項(xiàng)（（如圖圖4.9所示））和對(duì)對(duì)象選選項(xiàng)卡卡（如如圖4.10所所示）），定定義視視圖的的一些些高級(jí)級(jí)選項(xiàng)項(xiàng)，如如視圖圖是否否只讀、、約束束條件件等。。圖4.9選選項(xiàng)選選項(xiàng)界界面圖4.10對(duì)象象選項(xiàng)項(xiàng)界面面4.3.2創(chuàng)建建視圖圖1.用用OEM創(chuàng)建建視圖圖一般信信息頁(yè)頁(yè)面：：在““名稱稱”文文本框框輸入入視圖圖名稱稱CS_XS；；用戶戶方案案選ADMIN；在“查詢?cè)兾谋尽薄敝休斎肴雱?chuàng)建視視圖的SQL語(yǔ)語(yǔ)句，如如圖4.8所示示的SQL語(yǔ)句句。如果果有同名名視圖存在在，若選選中“若若存在則則替換””復(fù)選框框，那么么將用現(xiàn)現(xiàn)在定義義的視圖圖代替原原有同名名的視圖；；否則系系統(tǒng)將提提示錯(cuò)誤誤信息““視圖已已存在””，要求求重新命命名。在在別名文文本框可可以為視圖圖定義別別名。選項(xiàng)選項(xiàng)項(xiàng)頁(yè)面：：若選中中“強(qiáng)制制”選項(xiàng)項(xiàng)，則指指定創(chuàng)建建視圖而而無(wú)須考考慮視圖圖基表是是否存在或包包含該視視圖的方方案所有有者是否否具有權(quán)權(quán)限。只只有選中中“限制制”復(fù)選選框后才才能設(shè)置置只讀或是是否具有有約束條條件。““只讀””單選項(xiàng)項(xiàng)規(guī)定視視圖中不不能執(zhí)行行刪除、、插入、、更新操操作，只能能檢索數(shù)數(shù)據(jù)。““帶有復(fù)復(fù)選選項(xiàng)項(xiàng)”指定定在視圖圖中執(zhí)行行插入和和更新操操作時(shí)，，必須能能使該視圖圖查詢可可以選擇擇這些數(shù)數(shù)據(jù)，但但如果視視圖的查查詢中包包含子查查詢或該該視圖是是基于其它視視圖的，，這項(xiàng)指指定不一一定生效效?！凹s約束條件件”是指指定分配配給“復(fù)復(fù)選選項(xiàng)項(xiàng)”約束束條件的名稱稱。對(duì)象選項(xiàng)項(xiàng)頁(yè)面：：該選項(xiàng)項(xiàng)頁(yè)面僅僅用于對(duì)對(duì)象視圖圖。如果果您處理理的不是是對(duì)象視視圖,則則可跳過(guò)此此選項(xiàng)卡卡。如果果想創(chuàng)建建對(duì)象視視圖，則則要選中中“作為為對(duì)象視視圖”復(fù)復(fù)選框，，選擇對(duì)對(duì)象類型，，然后選選擇默認(rèn)認(rèn)屬性或或是選擇擇可用屬屬性列表表列出的的對(duì)象屬屬性。(6)單單擊““確定””，系統(tǒng)統(tǒng)執(zhí)行創(chuàng)創(chuàng)建視圖圖操作完完成后返返回如圖圖4.7所示界界面。4.3.2創(chuàng)建建視圖2.使使用CREATEVIEW語(yǔ)句創(chuàng)創(chuàng)建視圖圖PL/SQL中中用于創(chuàng)創(chuàng)建視圖圖的語(yǔ)句句是CREATEORREPLACEVIEW語(yǔ)語(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ì)算機(jī)專專業(yè)各學(xué)學(xué)生的學(xué)學(xué)號(hào)、其其選修的的課程號(hào)號(hào)及成績(jī)。要要保證對(duì)對(duì)該視圖圖的修改改都要符符合專業(yè)業(yè)名為計(jì)計(jì)算機(jī)這這個(gè)條件件。CREATEORREPLACEVIEWCS_KCASSELECTXS.XH,KCH,CJFROMXS,XS_KCWHEREXS.XH=XS_KC.XHANDZYM='計(jì)算機(jī)機(jī)'WITHCHECKOPTION;4.3.2創(chuàng)建視圖圖【例4.50】創(chuàng)建計(jì)算機(jī)機(jī)專業(yè)學(xué)生生的平均成成績(jī)視圖CS_KC_AVG，包括學(xué)學(xué)號(hào)（在視視圖中列名為為num））和平均成成績(jī)（在視視圖中列名名為score_avg）。。CREATEORREPLACEVIEWCS_KC_AVG(num,score_avg)ASSELECTXH,AVG(CJ)FROMXS_KCGROUPBYXH;4.3.3查查詢?cè)円曇晥D圖1.SELECT語(yǔ)語(yǔ)句句查查詢?cè)円曇晥D圖【例例4.51】】查找找計(jì)計(jì)算算機(jī)機(jī)專專業(yè)業(yè)的的學(xué)學(xué)生生學(xué)學(xué)號(hào)號(hào)和和選選修修的的課課程程號(hào)號(hào)。。SELECTXH,KCHFROMCS_KC;【例例4.52】】查找找平平均均成成績(jī)績(jī)?cè)谠?0分分以以上上的的學(xué)學(xué)生生的的學(xué)學(xué)號(hào)號(hào)和和平平均均成成績(jī)績(jī)。。本例例首首先先創(chuàng)創(chuàng)建建學(xué)學(xué)生生平平均均成成績(jī)績(jī)視視圖圖XS_KC_AVG，，包包括括學(xué)學(xué)號(hào)號(hào)（（在在視視圖圖中中列列名名為為num））和和平平均均成成績(jī)績(jī)（（在在視視圖圖中中列列名名為為score_avg））。。CREATEORREPLACEVIEWXS_KC_AVG(num,score_avg)ASSELECTXH,AVG(CJ)FROMXS_KCGROUPBYXH;再對(duì)XS_KC_AVG視圖圖進(jìn)行行查詢?cè)?。SELECT*FROMXS_KC_AVGWHEREscore_avg>=80;4.3.3查詢?cè)円晥D圖【例4.52】】查找平平均成成績(jī)?cè)谠?0分以以上的的學(xué)生生的學(xué)學(xué)號(hào)和和平均均成績(jī)績(jī)。執(zhí)行結(jié)結(jié)果為為：numscore_avg061110910612018006120387061204910612168106122082061241904.3.4更更新視視圖通過(guò)更更新視視圖（（包括括插入入、修修改和和刪除除）數(shù)數(shù)據(jù)可可以修修改基基表數(shù)數(shù)據(jù)。。但并并不是是所有有的視圖圖都可可以更更新，，只有有對(duì)滿滿足可可更新新條件件的視視圖，，才能能進(jìn)行行更新新。1.可可更更新視視圖要通過(guò)過(guò)視圖圖更新新基表表數(shù)據(jù)據(jù)，必必須保保證視視圖是是可更更新視視圖。。一個(gè)個(gè)可更更新視視圖滿滿足以以下條件件：(1)沒沒有使使用連連接函函數(shù)、、集合合運(yùn)算算函數(shù)數(shù)和組組函數(shù)數(shù)；(2)創(chuàng)創(chuàng)建視視圖的的SELECT語(yǔ)句句中沒沒有聚聚合函函數(shù)且且沒有有GROUPBY、ONNECTBY、、STARTWITH子句句及DISTINCT關(guān)關(guān)鍵字字；(3)創(chuàng)創(chuàng)建視視圖的的SELECT語(yǔ)句句中不不包含含從基基表列列通過(guò)過(guò)計(jì)算算所得得的列列；(4)創(chuàng)創(chuàng)建視視圖沒沒有包包含只只讀屬屬性。?！纠?.53】在XSCJ數(shù)數(shù)據(jù)庫(kù)庫(kù)中使使用以以下語(yǔ)語(yǔ)句創(chuàng)創(chuàng)建可可更新新視圖圖CS_XS1。CREATEORREPLACEVIEWCS_XS1ASSELECT*FROMXSWHEREZYM='計(jì)計(jì)算機(jī)機(jī)';4.3.4更更新視圖圖2.插插入數(shù)據(jù)據(jù)使用INSERT語(yǔ)句句通過(guò)視視圖向基基本表插插入數(shù)據(jù)據(jù)。【例4.54】】向CS_XS1視圖中中插入一一條記錄錄：（‘‘001115’,‘‘劉明儀儀’,‘‘計(jì)算機(jī)’’,男,‘1984-3-2’,50,‘三好好學(xué)生’））INSERTINTOCS_XS1VALUES('001115','劉劉明明儀儀','計(jì)計(jì)算算機(jī)機(jī)',’’男男’’,TO_DATE(‘‘19840302’’,’’YYYYMMDD’’),50,‘‘三三好好學(xué)學(xué)生生’’);使用用SELECT語(yǔ)語(yǔ)句句查查詢?cè)僀S_XS1依依據(jù)據(jù)的的基基本本表表XS：：SELECT*FROMXS;將會(huì)會(huì)看看到到該該表表已已添添加加了了學(xué)學(xué)號(hào)號(hào)為為001115的的數(shù)數(shù)據(jù)據(jù)行行。。4.3.4更更新新視視圖圖3.修修改改數(shù)數(shù)據(jù)據(jù)使用用UPDATE語(yǔ)語(yǔ)句句可可以以通通過(guò)過(guò)視視圖圖修修改改基基本本表表的的數(shù)數(shù)據(jù)據(jù)。?！纠?.55】】將CS_XS視視圖圖中中所所有有學(xué)學(xué)生生的的總總學(xué)學(xué)分分增增加加8。。UPDATECS_XSSET總總學(xué)學(xué)分分=總總學(xué)學(xué)分分+8;該語(yǔ)語(yǔ)句句實(shí)實(shí)際際上上是是將將CS_XS視視圖圖所所依依賴賴的的基基本本表表XS中中所所有有專專業(yè)業(yè)名名為為““計(jì)計(jì)算算機(jī)機(jī)””的的記記錄錄的的總總學(xué)學(xué)分分字字段段值值在在原原來(lái)來(lái)基基礎(chǔ)礎(chǔ)上上增增加加8。。若一一個(gè)個(gè)視視圖圖依依賴賴于于多多個(gè)個(gè)基基本本表表，，則則一一次次修修改改該該視視圖圖只只能能變變動(dòng)動(dòng)一一個(gè)個(gè)基基本本表表的的數(shù)數(shù)據(jù)據(jù)。?！纠?.56】】將CS_KC視視圖中中學(xué)號(hào)號(hào)為001101的的學(xué)生生的101課程程成績(jī)績(jī)改為為90。UPDATECS_KCSETCJ=90WHEREXH='001101'ANDKCH='101';4.3.4更更新視視圖4.刪刪除除數(shù)據(jù)據(jù)使用DELETE語(yǔ)語(yǔ)句可可以通通過(guò)視視圖刪刪除基基本表表的數(shù)數(shù)據(jù)。。但要要注意意，對(duì)對(duì)于依依賴于于多個(gè)基本本表的的視圖圖，不不能使使用DELETE語(yǔ)語(yǔ)句。。例如如，不不能通通過(guò)對(duì)對(duì)CS_KC視視圖執(zhí)執(zhí)行DELETE語(yǔ)語(yǔ)句而而刪除除與之之相關(guān)關(guān)的基基本表表XS及XS_KC表的的數(shù)據(jù)據(jù)。【例4.57】】刪除CS_XS中女女同學(xué)學(xué)的記記錄。。DELETEFROMCS_XSWHEREXB=’女女’;4.3.5修改改視圖圖的定定義修改視視圖定定義可可以通通過(guò)OEM的界界面進(jìn)進(jìn)行，，也可可使用用PL/SQL語(yǔ)句句。1.通通過(guò)過(guò)OEM修修改視視圖在如圖圖4.11所示示的窗窗口中中，在在一般般信息息選項(xiàng)項(xiàng)卡中中，對(duì)對(duì)視圖圖定義義進(jìn)行行修改改。在