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

《oracle10g经典实战第4章 数据库的查询和视图》由会员分享，可在线阅读，更多相关《oracle10g经典实战第4章 数据库的查询和视图（95页珍藏版）》请在装配图网上搜索。

1、第第4章章 数据库的查询和视图数据库的查询和视图 4.1连接、选择和投影连接、选择和投影 Oracle是一个关系数据库管理系统，关系数据库建立在关系模型基础之上，具有严格的数学理论基础。关系数据库对数据的操作除了包括集合代数的并、差等运算之外，还定义了一组专门的关系运算：连接、选择和投影，关系运算的特点是运算的对象和结果都是表。4.1.1选择选择 选择（Selection），简单的说就是通过一定的条件把自己所需要的数据检索出来。选择是单目运算，其运算对象是一个表。该运算按给定的条件，从表中选出满足条件的行形成一个新表，作为运算结果。4.1.1选择选择 【例【例4.1】学生情况表如表4.1所示。

2、若要在学生情况表中找出学生表中性别为女且平均成绩在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所示，则

3、连接后结果如表4.6所示。表表4.4 A表表表表4.5 B表表表表4.6 连接后的表连接后的表4.1.3连接连接 【例【例4.4】若表A和表B分别如表4.7和表4.8所示，自然连接后的新表C如表4.9所示。表表4.7 A表表 表表4.8 B表表 表表4.9 C表表 4.2 数据库的查询数据库的查询 使用数据库和表的主要目的是存储数据以便在需要时进行检索、统计或组织输出，通过PL/SQL的查询可以从表或视图中迅速方便地检索数据。PL/SQL的SELECT语句可以实现对表的选择、投影及连接操作，其功能十分强大。下面介绍SELECT语句，它是PL/SQL的核心。SELECT语句很复杂，主要的子句如下

4、：语法格式：语法格式：SELECT select_list /*指定要选择的列或行及其限定*/FROM table_source /*FROM子句，指定表或视图*/WHERE search_condition /*WHERE子句，指定查询条件*/GROUP BY group_by_expression /*GROUP BY子句，指定分组表达式*/HAVING search_condition /*HAVING子句，指定分组统计条件*/ORDER BY order_expression ASC|DESC /*ORDER子句，指定排序表达式和顺序*/4.2.1选择列选择列选择表中的列组成结果表，通

5、过SELECT语句的SELECT子句来表示。语法格式：语法格式：SELECT ALL|DISTINCT 其中select_list指出了结果的形式，select_list的主要格式为：*/*选择当前表或视图的所有列*/|table_name|view_name|table_alias .*/*选择指定的表或视图的所有列*/|colume_name|expression AS column_alias /*选择指定的列*/|column_alias=expression /*选择指定列并更改列标题*/,n 1.选择一个表中指定的列选择一个表中指定的列使用SELECT语句选择一个表中的某些列，各列

6、名之间要以逗号分隔。语法格式：语法格式：SELECT column_name ,column_name FROM table_name WHERE search_condition其功能是在FROM子句指定的表中检索符合search_condition条件的列。4.2.1选择列选择列【例【例4.5】查询XSCJ数据库的XS表中各个同学的XM、XH和ZXF。SELECT XH,XM,ZXF FROM XS;执行结果如图4.1所示。图图4.1 在在XS表中选择列表中选择列 4.2.1选择列选择列【例【例4.6】查询XS表中ZXF大于45同学的XH、XM和ZXF。SELECT XH,XM,ZXF F

7、ROM XS WHERE ZXF45;当在SELECT语句指定列的位置上使用*号时，表示选择表的所有列。【例【例4.7】查询XS表中的所有列。SELECT*FROM XS;该语句等价于语句：SELECT XH,XM,ZYM,XB,CSSJ,ZXF,BZ FROM XS;其执行后将列出XS表中的所有数据。4.2.1选择列选择列2.修改查询结果中的列标题修改查询结果中的列标题当希望查询结果中的某些列或所有列显示时使用自己选择的列标题时，可以在列名之后使用AS子句来更改查询结果的列标题名，其中column_alias是指定的列标题。【例【例4.8】查询XS表中计算机同学的XH、XM和ZXF，结果中各

8、列的标题分别指定为学号、姓名和总学分。SELECT XH AS 学号,XM AS 姓名,ZXF AS 总学分FROM XSWHERE ZYM=计算机;该语句的执行结果如图4.2所示。更改查询结果中的列标题可以省略AS关键字。例如：SELECT XH 学号,XM 姓名,ZXF 总学分FROM XSWHERE ZYM=计算机;4.2.1选择列选择列图图4.2更改查询结果中的列标题更改查询结果中的列标题 4.2.1选择列选择列3.计算列值计算列值使用SELECT对列进行查询时，在结果中可以输出对列值计算后的值，即SELECT子句可使用表达式作为结果，格式为：SELECT expression ,ex

9、pression【例【例4.9】创建产品销售数据库XSH，并在其中创建产品表CP，其表结构如表4.10所 表表4.10 CP表结构表结构 4.2.1选择列选择列设CP表中已有如表4.11所示的数据。表表4.11 CP表表 4.2.1选择列选择列下列语句将列出产品名称和产品总值：SELECT CPMC AS 产品名称,JG*KCL AS产品总值FROM CP;该语句的执行结果如图4.3所示。图图4.3 计算列值计算列值4.2.1选择列选择列4.消除结果集中的重复行消除结果集中的重复行【例【例4.10】对XSCJ数据库的XS表只选择ZYM和ZXF，消除结果集中的重复行。SELECT DISTINC

10、T ZYM AS 专业名,ZXF AS 总学分FROM XS;该语句执行的结果为：专业名 总学分计算机 48计算机 50计算机 52计算机 54通信工程 40通信工程 42通信工程 44通信工程 50与DISTINCT相反，当使用关键字ALL时，将保留结果集的所有行。4.2.1选择列选择列【例【例4.11】以下的SELECT语句对XSCJ数据库的XS表选择ZYM和ZXF，不消除结果集中的重复行。SELECT ALL ZYM AS 专业名,ZXF AS 总学分FROM XS;该语句执行后结果为：专业名 总学分计算机 50计算机 50计算机 50计算机 50计算机 54计算机 52计算机 50计算

11、机 50计算机 50计算机 48计算机 50通信工程 42通信工程 40通信工程 42通信工程 42通信工程 44通信工程 42通信工程 42通信工程 42通信工程 42通信工程 42通信工程 504.2.2选择行选择行1.表达式比较表达式比较比较运算符用于比较两个表达式值，共有7个，分别是：=（等于）、（小于）、（大于）、=（大于等于）、（不等于）、!=（不等于）。比较运算的格式为：expression =|=|!=expression当两个表达式值均不为空值（NULL）时，比较运算返回逻辑值TRUE（真）或FALSE（假）；而当两个表达式值中有一个为空值或都为空值时，比较运算将返回UNKN

12、OWN。【例【例4.12】(1)查询XSH数据库CP表中库存量在500以上的产品情况。SELECT*FROM CP WHERE KCL 500;(2)查询XSCJ数据库XS表中通信工程专业总学分大于等于42的同学的情况。SELECT*FROM XS WHERE ZYM=通信工程 and ZXF=42;4.2.2选择行选择行2.模式匹配模式匹配LIKE谓词用于指出一个字符串是否与指定的字符串相匹配，其运算对象可以是char、varchar2和date类型的数据，返回逻辑值TRUE或FALSE。LIKE谓词表达式的格式为：string_expression NOT LIKE string_expr

13、ession【例【例4.13】查询XSH数据库CP表中产品名含有“冰箱”的产品情况。SELECT*FROM CP WHERE CPMC LIKE%冰箱%;执行结果为：CPBH CPMC JG KCL10001100 冰箱A_100 1500.050010002120冰箱A_200 1850.020010001200冰箱B_200 1600.0120010001102 冰箱C_210 1890.0 6004.2.2选择行选择行【例【例4.14】查询XSCJ数据库XS表中姓“王”且单名的学生情况。SELECT*FROM XS WHERE XM LIKE 王_;执行结果为：XH XM ZYM XB

14、CSSJ ZXFBZ061101王林 计算机 男10-二月-8650061103王燕 计算机 女06-十月-8550061201王敏 通信工程 男10-六月-8442061202王林 通信工程 男29-一月-8540有一门课不及格，待补考3.范围比较范围比较用于范围比较的关键字有两个：BETWEEN和IN。当要查询的条件是某个值的范围时，可以使用BETWEEN关键字。BETWEEN关键字指出查询范围，格式为：expression NOT BETWEEN expression1 AND expression2当不使用NOT时，若表达式expression的值在表达式expression1与exp

15、ression2之间（包括这两个值），则返回TRUE，否则返回FALSE；使用NOT时，返回值刚好相反。注意注意：expression1的值不能大于expression2的值。4.2.2选择行选择行【例【例4.15】(1)查询XSH数据库CP表中价格在2000元与4000元之间的产品情况。SELECT*FROM CP WHERE JG BETWEEN 2000 AND 4000;(2)查询XSCJ数据库XS表中不在1985年出生的学生情况。SELECT*FROM XS WHERE CSSJ NOT BETWEEN TO_DATE(19850101,YYYYMMDD)and TO_DATE(19

16、851231,YYYYMMDD);使用IN关键字可以指定一个值表，值表中列出所有可能的值，当表达式与值表中的任一个匹配时，即返回TRUE，否则返回FALSE。使用IN关键字指定值表的格式为：expression IN(expression,n)【例【例4.16】查询XSH数据库CP表中库存量为“200”、“300”和“500”的情况。SELECT*FROM CP WHERE KCL IN(200,300,500);该语句与下列语句等价：SELECT*FROM CP WHERE KCL=200 OR KCL=300 OR KCL=500;4.2.2选择行选择行4.空值比较空值比较当需要判定一个表

17、达式的值是否为空值时，使用IS NULL关键字，格式为：expression IS NOT NULL当不使用NOT时，若表达式expression的值为空值，返回TRUE，否则返回FALSE；当使用NOT时，结果刚好相反。【例【例4.17】查询XSCJ数据库中总学分尚不定的学生情况。SELECT*FROM XS WHERE ZXF IS NULL;4.2.2选择行选择行5.子查询子查询在查询条件中，可以使用另一个查询的结果作为条件的一部分，例如判定列值是否与某个查询的结果集中的值相等，作为查询条件一部分的查询称为子查询。PL/SQL允许SELECT多层嵌套使用，用来表示复杂的查询。子查询除了可

18、以用在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)

19、。查找选修了课程号为101的课程的学生的情况：SELECT*FROM XSWHERE XH IN (SELECT XH FROM XS_KC WHERE KCH=101);在执行包含子查询的SELECT语句时，系统先执行子查询，产生一个结果表，再执行查询。本例中，先执行子查询：SELECT XH FROM XS_KC WHERE KCH=101;得到一个只含有学号列的表，XS_KC中课程名列值为101的行在结果表中都有一行。再执行外查询，若XS表中某行的学号列值等于子查询结果表中的任一个值，则该行就被选择。4.2.2选择行选择行【例【例4.19】查找未选修离散数学的学生的情况。SELECT X

20、H,XM,ZYM,ZXF FROM XS WHERE XH NOT IN (SELECT XH FROM XS_KC WHERE KCH IN (SELECT KCH FROM KC WHERE KCM=离散数学 );执行结果为：XH XM ZYM ZXF061201 王敏 通信工程 42061202 王林 通信工程 40061203 王玉民 通信工程 42061204 马琳琳 通信工程 42061206 李计 通信工程 42061210 李红庆 通信工程 44061216 孙祥欣 通信工程 42061218 孙研 通信工程 42061220 吴薇华 通信工程 42061221 刘燕敏 通信工

21、程 42061241 罗林琳 通信工程 504.2.2选择行选择行2)比较子查询这种子查询可以认为是IN子查询的扩展，它使表达式的值与子查询的结果进行比较运算，格式为：expression|=|!=|ALL|SOME|ANY (subquery)其中expression为要进行比较的表达式，subquery是子查询。ALL、SOME和ANY说明对比较运算的限制。ALL指定表达式要与子查询结果集中的每个值都进行比较，当表达式与每个值都满足比较的关系时，才返回TRUE，否则返回FALSE；SOME或ANY表示表达式只要与子查询结果集中的某个值满足比较的关系时，就返回TRUE，否则返回FALSE。4

22、.2.2选择行选择行【例例4.20】查找比所有计算机系学生年龄都大的学生。SELECT*FROM XSWHERE CSSJ=ANY(SELECT CJ FROM XS_KC WHERE KCH=101);(3)EXISTS子查询EXISTS谓词用于测试子查询的结果是否为空表，若子查询的结果集不为空，则EXISTS返回TRUE，否则返回FALSE。EXISTS还可与NOT结合使用，即NOT EXISTS，其返回值与EXIST刚好相反。格式为：NOT EXISTS(subquery)4.2.2选择行选择行【例例4.22】查找选修206号课程的学生姓名。SELECT XM FROM XS WHERE

23、 EXISTS (SELECT*FROM XS_KC WHERE XH=XS.XH AND KCH=206);本例在子查询的条件中使用了限定形式的列名引用XS.XH，表示这里的学号列出自表XS。4.2.2选择行选择行【例例4.23】查找选修了全部课程的同学的姓名。SELECT XM FROM XS WHERE NOT EXISTS (SELECT*FROM KC WHERE NOT EXISTS (SELECT*FROM XS_KC WHERE XH=XS.XH AND KCH=KC.KCH );本例即查找没有一门功课不选修的学生。4.2.3查询对象查询对象前面介绍了SELECT的选择列和行，

24、这里介绍SELECT查询的对象（即数据源）的构成形式。【例【例4.24】查找001102号同学所选修的全部课程的同学的学号。本例即要查找这样的学号y，对所有的课程号x，若001102号同学选修了该课，那么y也选修了该课。SELECT DISTINCT XH FROM XS_KC CJ1 WHERE NOT EXISTS (SELECT*FROM XS_KC CJ2 WHERE CJ2.XH=001102 AND NOT EXISTS (SELECT*FROM XS_KC CJ3 WHERE CJ3.XH=CJ1.XH AND CJ3.KCH=CJ2.KCH );本例子指定SELECT语句查询的

25、对象是表。4.2.3查询对象查询对象【例【例4.25】在XS表中查找1986年1月1日以前出生的学生的姓名和专业名。SELECT XM,ZYM FROM (SELECT*FROM XS WHERE CSSJ=80;执行结果为：姓名 成绩 王燕 81 李方方 80 林一帆 87 张蔚 89有时用户所需要的字段来自两个以上的表，那么就要对两个以上的表进行连接，称之为多表连接。4.2.4连接连接【例【例4.29】查找选修了“计算机基础”课程且成绩在80分以上的学生学号、姓名、课程名及成绩。SELECT XS.XH,XM,KCM,CJ FROM XS,KC,XS_KC WHERE XS.XH=XS_K

26、C.XH AND KC.KCH=XS_KC.KCH AND KCM=计算机基础 AND CJ=80;执行结果为：XH XM KCM CJ001101 王林 计算机基础 80001104 韦严平 计算机基础 90001108 林一帆 计算机基础 85001110 张蔚 计算机基础 95001111 赵琳 计算机基础 91001201 王敏 计算机基础 80001203 王玉民 计算机基础 87001204 马琳琳 计算机基础 91001216 孙祥欣 计算机基础 81001220 吴薇华 计算机基础 82001241 罗林琳 计算机基础 904.2.4连接连接2.以以JOIN关键字指定的连接关键

27、字指定的连接PL/SQL扩展了以JOIN关键字指定连接的表示方式，使表的连接运算能力有了增强。连接表的格式为：ON|CROSS JOIN|其中table_source为需连接的表，join_type表示连接类型，ON用于指定连接条件。join_type的格式为：INNER|LEFT|RIGHT|FULL OUTER JOIN其中INNER表示内连接，OUTER表示外连接，join_hint是连接提示。CROSS JOIN表示交叉连接。因此，以JOIN关键字指定的连接有三种类型。4.2.4连接连接2.以以JOIN关键字指定的连接关键字指定的连接(1)内连接内连接按照ON所指定的连接条件合并两个表

28、，返回满足条件的行。【例【例4.30】查找XSCJ数据库每个学生的情况以及选修的课程情况。SELECT*FROM XS INNER JOIN XS_KC ON XS.XH=XS_KC.XH;结果表将包含XS表和XS_KC表的所有字段（不去除重复字段学号）。若要去除重复的学号字段，可将SELECT子句改为：SELECT XS.*,KCH,CJ【例【例4.31】用FROM的JOIN关键字表达下列查询：查找选修了206课程且成绩在80分以上的学生姓名及成绩。SELECT XM,CJ FROM XS JOIN XS_KC ON XS.XH=XS_KC.XH WHERE KCH=206 AND CJ=8

29、0;内连接还可以用于多个表的连接。4.2.4连接连接【例【例4.32】用FROM的JOIN关键字表达下列查询：查找选修了“计算机基础”课程且成绩在80分以上的学生学号、姓名、课程名及成绩。SELECT XS.XH,XM,KCM,CJ FROM XS JOIN XS_KC JOIN KC ON XS_KC.KCH=KC.KCH ON XS.XH=XS_KC.XH WHERE KCM=计算机基础 AND CJ=80;作为一种特例，可以将一个表与它自身进行连接，称为自连接。若要在一个表中查找具有相同列值的行，则可以使用自连接。使用自连接时需为表指定两个别名，且对所有列的引用均要用别名限定。【例【例4

30、.33】查找不同课程成绩相同的学生的学号、课程号和成绩。SELECT a.XH,a.KCH,b.KCH,a.CJ FROM XS_KC a JOIN XS_KC b ON a.CJ=b.CJ AND a.XH=b.XH AND a.KCH!=b.KCH;执行结果为：XH KCH KCH CJ001102 102 206 78001102 206 102 784.2.4连接连接2.以以JOIN关键字指定的连接关键字指定的连接(2)外连接外连接的结果表不但包含满足连接条件的行，还包括相应表中的所有行。外连接包括三种：左外连接（LEFT OUTER JOIN）：结果表中除了包括满足连接条件的行外，还

31、包括左表的所有行；右外连接（RIGHT OUTER JOIN）：结果表中除了包括满足连接条件的行外，还包括右表的所有行；完全外连接（FULL OUTER JOIN）：结果表中除了包括满足连接条件的行外，还包括两个表的所有行。其中的OUTER关键字均可省略。4.2.4连接连接【例【例4.34】查找所有学生情况及他们选修的课程号，若学生未选修任何课，也要包括其情况。SELECT XS.*,KCH FROM XS LEFT OUTER JOIN XS_KC ON XS.XH=XS_KC.XH;本例执行时，若有学生未选任何课程，则结果表中相应行的课程号字段值为NULL。【例【例4.35】查找被选修了的

32、课程的选修情况和所有开设的课程名。SELECT XS_KC.*,KCM FROM XS_KC RIGHT JOIN KC ON XS_KC.KCH=KC.KCH;本例执行时，若某课程未被选修，则结果表中相应行的学号、课程号和成绩字段值均为NULL。注意：注意：外连接只能对两个表进行。4.2.4连接连接2.以以JOIN关键字指定的连接关键字指定的连接(3)交叉连接交叉连接实际上是将两个表进行笛卡尔积运算，结果表是由第一个表的每行与第二个表的每一行拼接后形成的表，因此结果表的行数等于两个表行数之积。【例例4.36】列出学生所有可能的选课情况。SELECT XH,XM,KCH,KCM FROM XS

33、 CROSS JOIN KC;注意:交叉连接不能有条件，且不能带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课程的学生的

34、平均成绩。SELECT AVG(CJ)AS 课程101平均成绩 FROM XS_KC WHERE KCH=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课程的学生的最高分和最低

35、分。SELECT MAX(CJ)AS 课程101的最高分,MIN(CJ)AS 课程101的最低分 FROM XS_KC WHERE KCH=101;执行结果为：课程101的最高分 课程101的最低分 95 624.2.5汇总汇总1.统计函数统计函数（3）COUNT函数。COUNT函数用于统计组中满足条件的行数或总行数，格式为：COUNT(ALL|DISTINCT expression|*)其中expression是一个表达式。ALL表示对所有值进行运算，DISTINCT表示去除重复值，缺省为ALL。选择*时将统计总行数。COUNT忽略NULL值。【例【例4.39】(1)求学生的总人数。SELE

36、CT COUNT(*)AS 学生总数 FROM XS;COUNT(*)不需要任何参数。(2)求选修了课程的学生总人数。SELECT COUNT(DISTINCT XH)FROM XS_KC;(3)统计离散数学课程成绩在85分以上的人数。SELECT COUNT(CJ)AS 离散数学85分以上的人数 FROM XS_KC WHERE CJ=85 AND KCH=(SELECT KCH FROM KC WHERE KCM=离散数学 );执行结果为：离散数学85分以上的人数 24.2.5汇总汇总2.GROUP BY子句子句GROUP BY子句用于对表或视图中的数据按字段分组，格式为：GROUP BY

37、 ALL group_by_expression,ngroup_by_expression：用于分组的表达式，其中通常包含字段名。指定ALL将显示所有组。使用GROUP BY子句后，SELECT子句中的列表中只能包含在GROUP BY中指出的列或在统计函数中指定的列。【例【例4.40】将XSCJ数据库中各专业名输出。SELECT ZYM AS 专业名 FROM XS GROUP BY ZYM;执行结果为：专业名计算机 通信工程 4.2.5汇总汇总【例【例4.41】求XSCJ数据库中各专业的学生数。SELECT ZYM AS 专业名,COUNT(*)AS 学生数 FROM XS GROUP BY

38、 ZYM;执行结果为：专业名 学生数计算机 11通信工程 11【例【例4.42】求被选修的各门课程的平均成绩和选修该课程的人数。SELECT KCH AS课程号,AVG(CJ)AS 平均成绩,COUNT(XH)AS 选修人数 FROM XS_KC GROUP BY KCH;执行结果为：课程号 平均成绩 选修人数101 7820102 7711206 75114.2.5汇总汇总3.HAVING子句子句使用GROUP BY子句和统计函数对数据进行分组后，还可以使用HAVING子句对分组数据进行进一步的筛选。例如查找XSCJ数据库中平均成绩在85分以上的学生，就是在XS_KC数据库上按学号分组后筛选

39、出符合平均成绩大于等于85的学生。HAVING子句的格式为：HAVING 其中search_condition为查询条件，与WHERE子句的查询条件类似，并且可以使用统计函数。【例【例4.43】查找XSCJ数据库中平均成绩在85分以上的学生的学号和平均成绩。SELECT XH AS 学号,AVG(CJ)AS 平均成绩 FROM XS_KC GROUP BY XH HAVING AVG(CJ)=85;执行结果为：学号 平均成绩001110 91001203 87001204 91001241 904.2.5汇总汇总【例【例4.44】查找选修课程超过2门且成绩都在80分以上的学生的学号。SELEC

40、T XH AS学号 FROM XS_KC WHERE CJ=80 GROUP BY XH HAVING COUNT(*)2;查询将XS_KC表中成绩大于或等于80的记录按学号分组，对每组记录计数，选出记录数大于2的各组的学号值形成结果表。【例【例4.45】查找通信工程专业平均成绩在85分以上的学生的学号和平均成绩。SELECT XH AS学号,AVG(CJ)AS 平均成绩 FROM XS_KC WHERE XH IN (SELECT XH FROM XS WHERE ZYM=通信工程 )GROUP BY XH HAVING AVG(CJ)=85;4.2.6排序排序在应用中经常要对查询的结果排序

41、输出，例如学生成绩由高到低排序。在SELECT语句中，使用ORDER BY子句对查询结果进行排序。ORDER BY子句的格式为：ORDER BY order_by_expression ASC|DESC ,n 其中order_by_expression是排序表达式，可以是列名、表达式或一个正整数，当expression是一个正整数时，表示按表中的该位置上列排序。关键字ASC表示升序排列，DESC表示降序排列，系统默认值为ASC。【例【例4.46】将通信工程专业的学生按出生时间先后排序。SELECT*FROM XS WHERE ZYM=通信工程 ORDER BY CSSJ;4.2.6排序排序【例

42、【例4.47】将计算机专业学生的“计算机基础”课程成绩按降序排列。SELECT XM AS 姓名,KCM AS 课程名,CJ AS 成绩 FROM XS,KC,XS_KC WHERE XS.XH=XS_KC.XH AND XS_KC.KCH=KC.KCH AND KCM=计算机基础 AND ZYM=计算机 ORDER BY CJ DESC;执行的结果为：姓名课程名成绩张蔚计算机基础95赵琳计算机基础91韦严平计算机基础90林一帆计算机基础85王林计算机基础80李明计算机基础78张强民计算机基础66李方方计算机基础65严红计算机基础63王燕计算机基础624.2.7 SELECT语句的语句的UNI

43、ON子句子句使用UNION子句可以将两个或多个SELECT查询的结果合并成一个结果集，其格式为：|()UNION A LL|()UNION A LL|()n 其中query specification和query expression都是SELECT查询语句。使用 UNION 组合两个查询的结果集的基本规则是：(1)所有查询中的列数和列的顺序必须相同。(2)数据类型必须兼容。关键字ALL表示合并的结果中包括所有行，不去除重复行。不使用ALL则在合并的结果去除重复行。含有UNION的SELECT查询也称为联合查询。4.2.7 SELECT语句的语句的UNION子句子句【例【例4.48】设在XSC

44、J数据库中建两个表：数学系学生、外语系学生，表结构与XS表相同，两个表分别存储数学系和外语系的学生情况，下列语句将这两个表的数据合并到XS表中。SELECT*FROM XS UNION ALL SELECT*FROM 数学系学生 UNION ALL SELECT*FROM 外语系学生;UNION操作常用于归并数据，例如归并月报表形成年报表，归并各部门数据等。注意UNION还可以与GROUP BY及ORDER BY一起使用，用来对合并所得的结果表进行分组或排序。4.3数据库的视图数据库的视图4.3.1视图的概念视图的概念视图是从一个或多个表（或视图）导出的表。视图与表（有时为与视图区别，也称表为

45、基表Base Table）不同，视图是一个虚表，即视图所对应的数据不进行实际存储，数据库中只存储视图的定义，对视图的数据进行操作时，系统根据视图的定义去操作与视图相关联的基表。视图可以由以下任意一项组成：(1)一个基表的任意子集(2)两个或者两个以上基表的合集(3)两个或者两个以上基表的交集(4)对一个或者多个基表运算的结果集合另一个视图的子集4.3.1视图的概念视图的概念视图一经定义以后，就可以象表一样被查询、修改、删除和更新。使用视图有下列优点：(1)为用户集中数据，简化用户的数据查询和处理。(2)屏蔽数据库的复杂性。(3)简化用户权限的管理。(4)便于数据共享。(5)可以重新组织数据以便

46、输出到其它应用程序中。4.3.2创建视图创建视图1.用用OEM创建视图创建视图以在XSCJ数据库中创建CS_XS（描述计算机专业学生情况）视图说明创建视图的过程。(1)在如图4.6所示的界面中，选择单击视图进入“视图搜索”界面，如图4.7所示。在该界面选择对象类型,并可以选择输入方案名称和对象名称,单击“开始”按钮，查找满足条件的对象类型，若存在满足条件的对象类型，在结果项可以查找到的对象类型。图图4.6 Oracle企业管理器企业管理器 图图4.7视图搜索界面视图搜索界面 4.3.2创建视图创建视图1.用用OEM创建视图创建视图(2)单击“创建”按钮，进入视图创建界面，如图4.8所示。图图4

47、.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语句。如果有同名视图存在，若

48、选中“若存在则替换”复选框，那么将用现在定义的视图代替原有同名的视图；否则系统将提示错误信息“视图已存在”，要求重新命名。在别名文本框可以为视图定义别名。(4)选项选项页面：若选中“强制”选项，则指定创建视图而无须考虑视图基表是否(5)存在或包含该视图的方案所有者是否具有权限。只有选中“限制”复选框后才能设置(6)只读或是否具有约束条件。“只读”单选项规定视图中不能执行删除、插入、更新操(7)作，只能检索数据。“带有复选选项”指定在视图中执行插入和更新操作时，必须能(8)使该视图查询可以选择这些数据，但如果视图的查询中包含子查询或该视图是基(9)于其它视图的，这项指定不一定生效。“约束条件”是

49、指定分配给“复选选项”约束条(10)件的名称。(5)对象选项页面：该选项页面仅用于对象视图。如果您处理的不是对象视图,则(6)可跳过此选项卡。如果想创建对象视图，则要选中“作为对象视图”复选框，选择对(7)象类型，然后选择默认属性或是选择可用属性列表列出的对象属性。(6)单击“确定”，系统执行创建视图操作完成后返回如图4.7所示界面。4.3.2创建视图创建视图2.使用使用CREATE VIEW语句创建视图语句创建视图PL/SQL中用于创建视图的语句是CREATE OR REPLACE VIEW语句。语法格式：语法格式：CREATE OR REPLACE FORCE|NOFORCE VIEW s

50、chema.view_name (column_name ,n )AS select_statementWITH CHECK OPTIONCONSTRAINT constraint_nameWITH READ ONLY【例【例4.49】创建CS_KC视图，包括计算机专业各学生的学号、其选修的课程号及成绩。要保证对该视图的修改都要符合专业名为计算机这个条件。CREATE OR REPLACE VIEW CS_KC AS SELECT XS.XH,KCH,CJ FROM XS,XS_KC WHERE XS.XH=XS_KC.XH AND ZYM=计算机WITH CHECK OPTION;4.3.2

51、创建视图创建视图【例【例4.50】创建计算机专业学生的平均成绩视图CS_KC_AVG，包括学号（在视图中列名为num）和平均成绩（在视图中列名为score_avg）。CREATE OR REPLACE VIEW CS_KC_AVG(num,score_avg)AS SELECT XH,AVG(CJ)FROM XS_KC GROUP BY XH;4.3.3查询视图查询视图1.SELECT语句查询视图语句查询视图【例【例4.51】查找计算机专业的学生学号和选修的课程号。SELECT XH,KCH FROM CS_KC;【例【例4.52】查找平均成绩在80分以上的学生的学号和平均成绩。本例首先创建学

52、生平均成绩视图XS_KC_AVG，包括学号（在视图中列名为num）和平均成绩（在视图中列名为score_avg）。CREATE OR REPLACE VIEW XS_KC_AVG(num,score_avg)AS SELECT XH,AVG(CJ)FROM XS_KC GROUP BY XH;再对XS_KC_AVG视图进行查询。SELECT*FROM XS_KC_AVG WHERE score_avg=80;4.3.3查询视图查询视图【例【例4.52】查找平均成绩在80分以上的学生的学号和平均成绩。执行结果为：num score_avg061110 91061201 80061203 8706

53、1204 91061216 81061220 82061241 904.3.4 更新视图更新视图通过更新视图（包括插入、修改和删除）数据可以修改基表数据。但并不是所有的视图都可以更新，只有对满足可更新条件的视图，才能进行更新。1.可更新视图可更新视图要通过视图更新基表数据，必须保证视图是可更新视图。一个可更新视图满足以下条件：(1)没有使用连接函数、集合运算函数和组函数；(2)创建视图的SELECT语句中没有聚合函数且没有GROUP BY、ONNECT BY、START WITH子句及DISTINCT关键字；(3)创建视图的SELECT语句中不包含从基表列通过计算所得的列；(4)创建视图没有包

54、含只读属性。【例例4.53】在XSCJ数据库中使用以下语句创建可更新视图CS_XS1。CREATE OR REPLACE VIEW CS_XS1 AS SELECT*FROM XS WHERE ZYM=计算机;4.3.4 更新视图更新视图2.插入数据插入数据使用INSERT语句通过视图向基本表插入数据。【例【例4.54】向CS_XS1视图中插入一条记录：（001115,刘明仪,计算机,男,1984-3-2,50,三好学生）INSERT INTO CS_XS1 VALUES(001115,刘明仪,计算机,男,TO_DATE(19840302,YYYYMMDD),50,三好学生);使用SELECT

55、语句查询CS_XS1依据的基本表XS：SELECT*FROM XS;将会看到该表已添加了学号为001115的数据行。4.3.4 更新视图更新视图3.修改数据修改数据使用UPDATE语句可以通过视图修改基本表的数据。【例【例4.55】将CS_XS视图中所有学生的总学分增加8。UPDATE CS_XS SET 总学分=总学分+8;该语句实际上是将CS_XS视图所依赖的基本表XS中所有专业名为“计算机”的记录的总学分字段值在原来基础上增加8。若一个视图依赖于多个基本表，则一次修改该视图只能变动一个基本表的数据。【例【例4.56】将CS_KC视图中学号为001101的学生的101课程成绩改为90。UP

56、DATE CS_KC SET CJ=90 WHERE XH=001101 AND KCH=101;4.3.4 更新视图更新视图4.删除数据删除数据使用DELETE语句可以通过视图删除基本表的数据。但要注意，对于依赖于多个基本表的视图，不能使用DELETE语句。例如，不能通过对CS_KC视图执行DELETE语句而删除与之相关的基本表XS及XS_KC表的数据。【例【例4.57】删除CS_XS中女同学的记录。DELETE FROM CS_XS WHERE XB=女;4.3.5修改视图的定义修改视图的定义修改视图定义可以通过OEM的界面进行，也可使用PL/SQL语句。1.通过通过OEM修改视图修改视图

57、在如图4.11所示的窗口中，在一般信息选项卡中，对视图定义进行修改。在选项和对象选项卡中，修改指定选项或对视图的约束条件等。修改完毕后单击“应用”按钮即可。图图4.11 修改视图定义修改视图定义4.3.5修改视图的定义修改视图的定义2.使用使用SQL命令修改视图命令修改视图Oracle提供了ALTER VIEW语句，但它不是用于修改视图定义，只是用于重新编译或验证现有视图。【例【例4.58】修改视图CS_KC的定义，包括学号、姓名、选修的课程号、课程名和成绩。CREATE OR REPLACE FORCE VIEW CS_KCASSELECT XS.XH,XS.XM,XS_KC.KCH,KC.

58、KCM,CJ FROM XS,XS_KC,KC WHERE XS.XH=XS_KC.XH AND XS_KC.KCH=KC.KCH AND ZYM=通信工程 WITH CHECK OPTION;4.3.6删除视图删除视图如果不再需要视图了，可以通过OEM和PL/SQL语句两种方式，把视图的定义从数据库中删除。删除一个视图，就是删除其定义和赋予的全部权限。在如图4.12界面中，选中要删除的视图，单击“删除”，出现确认界面，单击“是”按钮即可删除所选择的视图。图图4.12 删除视图删除视图4.3.6删除视图删除视图删除视图的PL/SQL语句是DROP VIEW，格式为：DROP VIEW sche

59、ma.view_name其中schema是所要删除视图的用户方案；view_name是视图名。例如：DROP VIEW CS_KC;将删除视图CS_KC。4.4 格式化输出结果格式化输出结果4.4.1替换变量替换变量在SQL*Plus环境中，可以使用替换变量来临时存储有关的数据。Oracle使用3种类型的替换变量。1.&替换变量替换变量在SELECT语句中，如果某个变量前面使用了&符号，那么表示该变量是一个替换变量。在执行SELECT语句时，系统会提示用户为该变量提供一个具体的值。【例【例4.59】查询XSCJ数据库XS表计算机专业的同学情况。SELECT XH AS 学号,XM AS 姓名F

60、ROM XSWHERE ZYM=&specialty_name;4.4.1替换变量替换变量1.&替换变量替换变量【例【例4.60】查找平均成绩在80分以上的学生的学号、姓名和平均成绩。SET VERIFY ON SELECT*FROM XS_KC_AVG WHERE score_avg=score_avg;执行过程为：输入score_avg的值：80原值3：WHERE score_avg=score_avg新值3：WHERE score_avg=80替换变量不仅仅可以用在WHERE子句中，而且还可以用在下列部分：(1)ORDER BY子句。(2)列表达式。(3)表名。(4)整个SELECT语句

61、 4.4.1替换变量替换变量1.&替换变量替换变量【例【例4.61】查找选修了“离散数学”课程的学生学号、姓名、课程名及成绩。SELECT XS.XH,&name,KCM,&column FROM XS,&kc,XS_KC WHERE XS.XH=XS_KC.XH AND&condition AND KCM=&kcm ORDER BY&column;执行过程及结果为：输入 name 的值：XM输入 column 的值：CJ原值 1：SELECT XS.XH,&name,KCM,&column新值 1：SELECT XS.XH,XM,KCM,CJ原值 2：FROM XS,&kc,XS_KC新值

62、2：FROM XS,kc,XS_KC输入 condition 的值：KC.KCH=XS_KC.KCH输入 kcm 的值：离散数学原值 3：WHERE XS.XH=XS_KC.XH AND&condition AND KCM=&kcm新值 3：WHERE XS.XH=XS_KC.XH AND KC.KCH=XS_KC.KCH AND KCM=离散数学4.4.1替换变量替换变量输入 column 的值:CJ原值 4：ORDER BY&column新值 4：ORDER BY CJXH XM KCM CJ061113 严红 离散数学 60061104 韦严平 离散数学 65061107 李明 离散数学

63、 68061109 张强民 离散数学 70061101 王林 离散数学 71061111 赵琳 离散数学 76061102 程明 离散数学 78061106 李方方 离散数学 80061103 王燕 离散数学 81061108 林一帆 离散数学 87061110 张蔚 离散数学 894.4.1替换变量替换变量2.&替换变量替换变量在SELECT语句中，如果希望重新使用某个变量并且不希望重新提示输入该值，可以使用&替换变量。在上述例子中，包含了一个变量&column，这个变量出现了两次，如果只是使用“&”符号来定义替换变量，那么系统会提示用户输入两次该变量。在此，为该变量提供了列名CJ。【例【例

64、4.62】查找选修了“离散数学”课程的学生学号、姓名、课程名及成绩。执行过程和结果为：输入 name 的值：XM输入 column 的值：CJ原值 1：SELECT XS.XH,&name,KCM,&column新值 1：SELECT XS.XH,XM,KCM,CJ原值 2：FROM XS,&kc,XS_KC新值 2：FROM XS,kc,XS_KC输入 condition 的值：KC.KCH=XS_KC.KCH输入 kcm 的值：离散数学原值 3：WHERE XS.XH=XS_KC.XH AND&condition AND KCM=&kcm新值 3：WHERE XS.XH=XS_KC.XH

65、AND KC.KCH=XS_KC.KCH AND KCM=离散数学4.4.1替换变量替换变量输入 column 的值:NAME原值 4：ORDER BY&column新值 4：ORDER BY NAMEXH XM KCM CJ061102 程明 离散数学 78061106 李方方 离散数学 80061107 李明 离散数学 68061108 林一帆 离散数学 87061101 王林 离散数学 71061103 王燕 离散数学 81061104 韦严平 离散数学 65061113 严红 离散数学 60061109 张强民 离散数学 70061110 张蔚 离散数学 89061111 赵琳 离散数

66、学 764.4.1替换变量替换变量【例【例4.63】查询选修课程超过2门且成绩在75分以上的学生的学号。SELECT&column FROM XS_KC WHERE CJ=75 GROUP BY&column HAVING COUNT(*)2;执行过程：输入 column 的值：XH原值 1：SELECT&column新值 1：SELECT XH原值 4：GROUP BY&column新值 4：GROUP BY XH4.4.1替换变量替换变量3.DEFINE和和ACCEPT命令命令为了在PL/SQL语句中定义变量，可以使用DEFINE和ACCEPT命令。DEFINE命令用来创建一个数据类型为CHAR用户定义的变量。相反地，使用UNDEFINE命令可以清除定义的变量。语法格式：DEFINE variable=value其中，如果不带任何参数，直接使用DEFINE命令，则显示所有用户定义的变量。variable是变量名，value是变量的值。DEFINE value是显示指定变量的值和数据类型。DEFINE variable=value是创建一个CHAR类型的用户变量，且为该变量赋初值。4