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;执行结果为为：XHKCHKCHCJ001102 10220678001102 206102784.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;执行结果为：：学号平平均成绩绩00111091001203870012049100124190汇汇总【例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;排排序在应用中经常常要对查询的的结果排序输输出，例如学学生成绩由高高到低排序。。在SELECT语句中，使使用ORDERBY子子句对查询结结果进行排序序。ORDERBY子句的格式为为：[ORDERBY{order_by_expression[ASC|DESC]}[,…n]其中order_by_expression是排序表达达式，可以是是列名、表达达式或一个正正整数，当expression是一个正整整数时，表示示按表中的该该位置上列排排序。关键字字ASC表示升序排列列，DESC表示降序排排列，系统默默认值为ASC。【例4.46】将通信工程专专业的学生按按出生时间先先后排序。SELECT*FROMXSWHEREZYM='通信工程程'ORDERBYCSSJ;排排序【例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数据库库的视图视视图的概念视图是从一个个或多个表（（或视图）导导出的表。视图与表（有有时为与视图图区别，也称称表为基表———BaseTable）不同，，视图是一个虚表，即视视图所对应的的数据不进行行实际存储，，数据库中只只存储视图的的定义，对视图的数据进进行操作时，，系统根据视视图的定义去去操作与视图图相关联的基基表。视图可以由以以下任意一项项组成：(1) 一个个基表的任意意子集(2) 两个个或者两个以以上基表的合合集(3) 两个个或者两个以以上基表的交交集(4) 对一一个或者多个个基表运算的的结果集合另一个视图的的子集视视图的概念视图一经定义义以后，就可可以象表一样样被查询、修修改、删除和和更新。使用用视图有下列优优点：(1) 为用用户集中数据据，简化用户户的数据查询询和处理。(2) 屏蔽蔽数据库的复复杂性。(3) 简化化用户权限的的管理。(4) 便于于数据共享。。(5)可以以重新组织数数据以便输出出到其它应用用程序中。创创建视图1.用OEM创建视视图以在XSCJ数据库中创创建CS_XS（描述计计算机专业学学生情况）视视图说明创建建视图的过程程。(1)在在如图4.6所示的界面面中，选择单单击视图进入入“视图搜索索”界面，如如图4.7所所示。在该界界面选择对象类型型,并可以以选择输入方方案名称和对对象名称,单单击“开始始”按钮，查查找满足条件件的对象类型，若存在在满足条件的的对象类型，，在结果项可可以查找到的的对象类型。。图4.6Oracle企业管理器器图4.7视图图搜索界面创创建视图1.用OEM创建视视图(2) 单击击“创建”按按钮，进入视视图创建界面面，如图4.8所示。图4.8一一般信息选选项界面创创建视图创建界面有三三个选项页面面：一般信息息、选项和对对象选项页面面。一般信息息页面如图4.8所示，，主要定义视视图的基本信信息，如名称称、所属用户户方案等。选选项（如图4.9所示）和对象象选项卡（如如图4.10所示），定定义视图的一一些高级选项项，如视图是是否只读、约束条条件等。图4.9选项项选项界面图4.10对对象选项界面面创创建视图1. 用OEM创建视图图一般信息页面面：在“名称称”文本框输输入视图名称称CS_XS；用户方案案选ADMIN；在“查询文本本”中输入创创建视图的SQL语句，，如图4.8所示的SQL语句。如如果有同名视图存在，若若选中“若存存在则替换””复选框，那那么将用现在在定义的视图图代替原有同同名的视图；否则则系统将提示示错误信息““视图已存在在”，要求重重新命名。在在别名文本框框可以为视图定义义别名。选项选项页面面：若选中““强制”选项项，则指定创创建视图而无无须考虑视图图基表是否存在或包含该该视图的方案案所有者是否否具有权限。。只有选中““限制”复选选框后才能设设置只读或是否具具有约束条件件。“只读””单选项规定定视图中不能能执行删除、、插入、更新新操作，只能检索索数据。“带带有复选选项项”指定在视视图中执行插插入和更新操操作时，必须须能使该视图查询询可以选择这这些数据，但但如果视图的的查询中包含含子查询或该该视图是基于其它视图的的，这项指定定不一定生效效。“约束条条件”是指定定分配给“复复选选项”约约束条件的名称。对象选项页面面：该选项页页面仅用于对对象视图。如如果您处理的的不是对象视视图,则可跳过此选项项卡。如果想想创建对象视视图，则要选选中“作为对对象视图”复复选框，选择择对象类型，然后后选择默认属属性或是选择择可用属性列列表列出的对对象属性。(6) 单击击“确定”，，系统执行创创建视图操作作完成后返回回如图4.7所示界面。。创创建视图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.50】创建计算机专专业学生的平平均成绩视图图CS_KC_AVG，，包括学号（（在视图中列名为num）和平平均成绩（在在视图中列名名为score_avg）。CREATEORREPLACEVIEWCS_KC_AVG(num,score_avg)ASSELECTXH,AVG(CJ)FROMXS_KCGROUPBYXH;查查询视图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.52】查找平均成绩绩在80分以以上的学生的的学号和平均均成绩。执行结果为：：numscore_avg06111091061201800612038706120491061216810612208206124190更更新视图通过更新视图图（包括插入入、修改和删删除）数据可可以修改基表表数据。但并并不是所有的视图都可以以更新，只有有对满足可更更新条件的视视图，才能进进行更新。1. 可更新新视图要通过视图更更新基表数据据，必须保证证视图是可更更新视图。一一个可更新视视图满足以下条件：(1) 没有有使用连接函函数、集合运运算函数和组组函数；(2) 创建建视图的SELECT语语句中没有聚聚合函数且没没有GROUPBY、、ONNECTBY、STARTWITH子句及及DISTINCT关键键字；(3) 创建建视图的SELECT语语句中不包含含从基表列通通过计算所得得的列；(4) 创建建视图没有包包含只读属性性。【例4.53】在XSCJ数据库中使使用以下语句句创建可更新新视图CS_XS1。CREATEORREPLACEVIEWCS_XS1ASSELECT*FROMXSWHEREZYM='计算机';更更新视图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的数数据行。更更新视图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. 删除数数据使用DELETE语句可可以通过视图图删除基本表表的数据。但但要注意，对对于依赖于多多个基本表的视视图，不能使使用DELETE语句。。例如，不能能通过对CS_KC视图图执行DELETE语句而删除除与之相关的的基本表XS及XS_KC表的数据据。【例4.57】删除CS_XS中女同学学的记录。DELETEFROMCS_XSWHEREXB=’女女’;修修改视图的定定义修改视图定义义可以通过OEM的界面面进行，也可可使用PL/SQL语句句。1. 通过OEM修改视视图在如图4.11所示的窗窗口中，在一一般信息选项项卡中，对视视图定义进行行修改。在选选项和对象选项卡卡中，修改指指定选项或对对视图的约束束条件等。修修改完毕后单单击“应用””按钮即可。图4.11修修改视图图定义修修改视图的定定义2. 使用SQL命令修修改视图Oracle提供了ALTERVIEW语句句，但它不是是用于修改视视图定义，只只是用于重新新编译或验证现有有视图。【例4.58】修改视图CS_KC的定定义，包括学学号、姓名、、选修的课程程号、课程名名和成绩。CREATEORREPLACEFORCEVIEWCS_KCASSELECTXS.XH,XS.XM,XS_KC.KCH,KC.KCM,CJFROMXS,XS_KC,KCWHEREXS.XH=XS_KC.XHANDXS_KC.KCH=KC.KCHANDZYM=’’通信信工程程’WITHCHECKOPTION;4.3.6删除除视图图如果不不再需需要视视图了了，可可以通通过OEM和PL/SQL语语句两两种方方式，，把视视图的的定义义从数数据库库中删删除。。删除除一个个视图图，就就是删删除其其定义义和赋赋予的的全部部权限限。在如图图4.12界面面中，，选中中要删删除的的视图图，单单击““删除除”，，出现现确认认界面面，单单击““是””按钮钮即可可删除除所选选择的的视图图。图4.12删删除视视图4.3.6删除除视图图删除视视图的的PL/SQL语句句是DROPVIEW，格格式为为：DROPVIEW[schema.]view_name其中schema是是所要要删除除视图图的用用户方方案；；view_name是是视图图名。。例如：：DROPVIEWCS_KC;将删除除视图图CS_KC。。4.4格格式化化输出出结果果4.4.1替换换变量量在SQL*Plus环境境中，，可以以使用用替换换变量量来临临时存存储有有关的的数据据。Oracle使使用3种类型的的替换换变量量。1.&替替换变变量在SELECT语句句中，，如果果某个个变量量前面面使用用了&符号号，那那么表表示该该变量量是一一个替替换变量量。在在执行行SELECT语句句时，，系统统会提提示用用户为为该变变量提提供一一个具具体的的值。。【例4.59】】查询XSCJ数数据库库XS表计计算机机专业业的同同学情情况。。SELECTXHAS学学号,XMAS姓名名FROMXSWHEREZYM=&specialty_name;4.4.1替换变量量1. &替替换变量【例4.60】查找平均成成绩在80分以上的的学生的学学号、姓名名和平均成成绩。SETVERIFYONSELECT*FROMXS_KC_AVGWHEREscore_avg>=score_avg;执行过程为为：输入score_avg的值值：80原值 3：：WHEREscore_avg>=score_avg新值 3：：WHEREscore_avg>=80替换变量不不仅仅可以以用在WHERE子子句中，而而且还可以以用在下列列部分：(1)ORDERBY子子句。(2)列列表达式。。(3)表表名。(4)整整个SELECT语语句4.4.1替换变量量1. &替替换变量【例4.61】查找选修了了“离散数数学”课程程的学生学学号、姓名名、课程名名及成绩。。SELECTXS.XH,&name,KCM,&columnFROMXS,&kc,XS_KCWHEREXS.XH=XS_KC.XHAND&conditionANDKCM=&kcmORDERBY&column;执行过程及及结果为：：输入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的值值：‘‘离散数学学’原值3：WHEREXS.XH=XS_KC.XHAND&conditionANDKCM=&kcm新值3：WHEREXS.XH=XS_KC.XHANDKC.KCH=XS_KC.KCHANDKCM=’’离散数学学’4.4.1替换变量量输入column的值:CJ原值4：ORDERBY&column新值4：ORDERBYCJXHXMKCMCJ061113严严红离离散数学学60061104韦韦严平离离散数数学65061107李李明离离散数学学68061109张张强民离离散数数学70061101王王林离离散数学学71061111赵赵琳离离散数学学76061102程程明离离散数学学78061106李李方方离离散数数学80061103王王燕离离散数学学81061108林林一帆离离散数数学87061110张张蔚离离散数学学894.4.1替换变量量2. &&替换变量量在SELECT语句句中，如果果希望重新新使用某个个变量并且且不希望重重新提示输输入该值，，可以使用&&替换变变量。在上述例子子中，包含含了一个变变量&column，这个变变量出现了了两次，如如果只是使使用“&”符号号来定义替替换变量，，那么系统统会提示用用户输入两两次该变量