第4章數據庫的查詢和視圖

4.1連接、選擇和投影

Oracle是一個關系數據庫管理系統，關系數據庫建立在關系模型基礎之上，具有嚴格的數學理論基礎。關系數據庫對數據的操作除了包括集合代數的并、差等運算之外，還定義了一組專門的關系運算：連接、選擇和投影，關系運算的特點是運算的對象和結果都是表。

4.1.1選擇選擇（Selection），簡單的說就是通過一定的條件把自己所需要的數據檢索出來。選擇是單目運算，其運算對象是一個表。該運算按給定的條件，從表中選出滿足條件的行形成一個新表，作為運算結果。4.1.1選擇【例4.1】學生情況表如表4.1所示。若要在學生情況表中找出學生表中性別為女且平均成績在80分以上的行形成一個新表，該選擇運算的結果如表4.2所示。表4.1學生表

表4.2查詢后的結果4.1.2投影投影（Projection）也是單目運算。投影就是選擇表中指定的列，這樣在查詢結果中只顯示指定數據列，減少了顯示的數據量也提高查詢的性能。【例4.2】若在表4.1中對“姓名”和“平均成績”投影，該查詢得到如表4.3所示的新表。表4.3投影后的新表4.1.3連接連接（JOIN）是把兩個表中的行按照給定的條件進行拼接而形成新表。【例4.3】若表A和B分別如表4.4和表4.5所示，則連接后結果如表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數據庫的查詢使用數據庫和表的主要目的是存儲數據以便在需要時進行檢索、統計或組織輸出，通過PL/SQL的查詢可以從表或視圖中迅速方便地檢索數據。PL/SQL的SELECT語句可以實現對表的選擇、投影及連接操作，其功能十分強大。下面介紹SELECT語句，它是PL/SQL的核心。SELECT語句很復雜，主要的子句如下：語法格式：

SELECTselect_list /*指定要選擇的列或行及其限定*/FROMtable_source/*FROM子句，指定表或視圖*/[WHEREsearch_condition]/*WHERE子句，指定查詢條件*/[GROUPBYgroup_by_expression]/*GROUPBY子句，指定分組表達式*/[HAVINGsearch_condition]/*HAVING子句，指定分組統計條件*/[ORDERBYorder_expression[ASC|DESC]]/*ORDER子句，指定排序表達式和順序*/4.2.1選擇列選擇表中的列組成結果表，通過SELECT語句的SELECT子句來表示。語法格式：

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

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