oracleg经典实战第章 数据库的查询和视图

第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所示示的窗窗口中中，在在一般般信息息选项项卡中中，对对视图图定义义进行行修改改。在