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

《oracle10g经典实战第4章-数据库的查询和视图.ppt》由会员分享，可在线阅读，更多相关《oracle10g经典实战第4章-数据库的查询和视图.ppt（95页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、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）是把两个表中的行按照给定的条件进行拼接而

2、形成新表。【例【例4.3】若表A和B分别如表4.4和表4.5所示，则连接后结果如表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语句可以实现对表的选择、投影及连接操作，其功能十分强大。 下面介绍SELE

3、CT语句，它是PL/SQL的核心。SELECT语句很复杂，主要的子句如下： 语法格式：语法格式： 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 /

4、*ORDER子句，指定排序表达式和顺序*/4.2.1选择列选择列选择表中的列组成结果表，通过SELECT语句的SELECT子句来表示。语法格式：语法格式： SELECT ALL | DISTINCT 其中select_list指出了结果的形式，select_list的主要格式为： * /*选择当前表或视图的所有列*/ | table_name | view_name | table_alias . * /*选择指定的表或视图的所有列*/ | colume_name | expression AS column_alias /*选择指定的列*/ | column_alias = expressi

5、on /*选择指定列并更改列标题*/ , n 1. 选择一个表中指定的列选择一个表中指定的列使用SELECT语句选择一个表中的某些列，各列名之间要以逗号分隔。语法格式：语法格式： 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

6、表中选择列表中选择列 4.2.1选择列选择列【例【例4.6】查询XS表中ZXF大于45同学的XH、XM和ZXF。 SELECT XH,XM,ZXF FROM 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.修改查询结果中的列标题修改查询结果中的列标题当希望查询结果中的某些列或所有列显示时使用自己选择的列标题时，可以在

7、列名之后使用AS子句来更改查询结果的列标题名，其中column_alias是指定的列标题。【例【例4.8】查询XS表中计算机同学的XH、XM和ZXF，结果中各列的标题分别指定为学号、姓名和总学分。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. 计算列值

8、计算列值使用SELECT对列进行查询时，在结果中可以输出对列值计算后的值，即SELECT子句可使用表达式作为结果，格式为：SELECT expression , expression 【例【例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

9、.1选择列选择列4. 消除结果集中的重复行消除结果集中的重复行【例【例4.10】对XSCJ数据库的XS表只选择ZYM和ZXF，消除结果集中的重复行。SELECT DISTINCT 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 ZY

10、M AS 专业名,ZXF AS 总学分FROM XS;该语句执行后结果为：专业名 总学分计算机 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 = | |

11、| = | | != expression当两个表达式值均不为空值（NULL）时，比较运算返回逻辑值TRUE（真）或FALSE（假）；而当两个表达式值中有一个为空值或都为空值时，比较运算将返回UNKNOWN。【例【例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谓词用于指出一个字符串是否与指定的

12、字符串相匹配，其运算对象可以是char、varchar2和date类型的数据，返回逻辑值TRUE或FALSE。LIKE谓词表达式的格式为：string_expression NOT LIKE string_expression【例【例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_2

13、10 1890.0 6004.2.2选择行选择行【例【例4.14】查询XSCJ数据库XS表中姓“王”且单名的学生情况。SELECT * FROM XS WHERE XM LIKE 王_;执行结果为：XH XM ZYM XB CSSJ ZXFBZ061101 王林 计算机 男10-二月-8650061103 王燕 计算机 女06-十月-8550061201王敏 通信工程 男10-六月-8442061202王林 通信工程 男29-一月-8540有一门课不及格，待补考3.范围比较范围比较用于范围比较的关键字有两个：BETWEEN和IN。当要查询的条件是某个值的范围时，可以使用BETWEEN关键字。B

14、ETWEEN关键字指出查询范围，格式为：expression NOT BETWEEN expression1 AND expression2当不使用NOT时，若表达式expression的值在表达式expression1与expression2之间（包括这两个值），则返回TRUE，否则返回FALSE；使用NOT时，返回值刚好相反。注意注意：expression1的值不能大于expression2的值。 4.2.2选择行选择行【例【例4.15】(1) 查询XSH数据库CP表中价格在2000元与4000元之间的产品情况。 SELECT * FROM CP WHERE JG BETWEEN 2000

15、 AND 4000;(2) 查询XSCJ数据库XS表中不在1985年出生的学生情况。 SELECT * FROM XS WHERE CSSJ NOT BETWEEN TO_DATE(19850101,YYYYMMDD) and TO_DATE(19851231,YYYYMMDD);使用IN关键字可以指定一个值表，值表中列出所有可能的值，当表达式与值表中的任一个匹配时，即返回TRUE，否则返回FALSE。使用IN关键字指定值表的格式为：expression IN ( expression ,n)【例【例4.16】查询XSH数据库CP表中库存量为“200”、“300”和“500”的情况。 SELE

16、CT * FROM CP WHERE KCL IN (200,300,500);该语句与下列语句等价： SELECT * FROM CP WHERE KCL=200 OR KCL=300 OR KCL=500;4.2.2选择行选择行4. 空值比较空值比较当需要判定一个表达式的值是否为空值时，使用IS NULL关键字，格式为：expression IS NOT NULL当不使用NOT时，若表达式expression的值为空值，返回TRUE，否则返回FALSE；当使用NOT时，结果刚好相反。【例【例4.17】查询XSCJ数据库中总学分尚不定的学生情况。 SELECT * FROM XS WHERE

17、 ZXF IS NULL;4.2.2选择行选择行5. 子查询子查询在查询条件中，可以使用另一个查询的结果作为条件的一部分，例如判定列值是否与某个查询的结果集中的值相等，作为查询条件一部分的查询称为子查询。PL/SQL允许SELECT多层嵌套使用，用来表示复杂的查询。子查询除了可以用在SELECT语句中，还可以用在INSERT、UPDATE及DELETE语句中。子查询通常与IN、EXIST谓词及比较运算符结合使用。(1) IN子查询IN子查询用于进行一个给定值是否在子查询结果集中的判断，格式为：expression NOT IN ( subquery )其中subquery是子查询。当表达式ex

18、pression与子查询subquery的结果表中的某个值相等时，IN谓词返回TRUE，否则返回FALSE；若使用了NOT，则返回的值刚好相反。 4.2.2选择行选择行【例【例4.18】在XSCJ数据库中有描述课程情况的表KC和描述学生成绩表的表XS_KC(表的结构和样本数据见附录A)。查找选修了课程号为101的课程的学生的情况： SELECT * FROM XSWHERE XH IN ( SELECT XH FROM XS_KC WHERE KCH = 101 );在执行包含子查询的SELECT语句时，系统先执行子查询，产生一个结果表，再执行查询。本例中，先执行子查询： SELECT XH

19、FROM XS_KC WHERE KCH = 101;得到一个只含有学号列的表，XS_KC中课程名列值为101的行在结果表中都有一行。再执行外查询，若XS表中某行的学号列值等于子查询结果表中的任一个值，则该行就被选择。4.2.2选择行选择行【例【例4.19】查找未选修离散数学的学生的情况。 SELECT XH,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 王敏 通

20、信工程 42061202 王林 通信工程 40061203 王玉民 通信工程 42061204 马琳琳 通信工程 42061206 李计 通信工程 42061210 李红庆 通信工程 44061216 孙祥欣 通信工程 42061218 孙研 通信工程 42061220 吴薇华 通信工程 42061221 刘燕敏 通信工程 42061241 罗林琳 通信工程 504.2.2选择行选择行2) 比较子查询这种子查询可以认为是IN子查询的扩展，它使表达式的值与子查询的结果进行比较运算，格式为： expression | | = | != | ALL | SOME | ANY ( subquery )

21、其中expression为要进行比较的表达式，subquery是子查询。ALL、SOME和ANY说明对比较运算的限制。ALL指定表达式要与子查询结果集中的每个值都进行比较，当表达式与每个值都满足比较的关系时，才返回TRUE，否则返回FALSE；SOME或ANY表示表达式只要与子查询结果集中的某个值满足比较的关系时，就返回TRUE，否则返回FALSE。4.2.2选择行选择行【例例4.20】查找比所有计算机系学生年龄都大的学生。 SELECT * FROM XSWHERE CSSJ = ANY ( SELECT CJ FROM XS_KC WHERE KCH = 101);(3) EXISTS子查

22、询EXISTS谓词用于测试子查询的结果是否为空表，若子查询的结果集不为空，则EXISTS返回TRUE，否则返回FALSE。EXISTS还可与NOT结合使用，即NOT EXISTS，其返回值与EXIST刚好相反。格式为： NOT EXISTS ( subquery )4.2.2选择行选择行【例例4.22】查找选修206号课程的学生姓名。 SELECT XM FROM XS WHERE EXISTS ( SELECT * FROM XS_KC WHERE XH=XS.XH AND KCH=206 );本例在子查询的条件中使用了限定形式的列名引用XS.XH，表示这里的学号列出自表XS。 4.2.2选

23、择行选择行【例例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的选择列和行，这里介绍SELECT查询的对象（即数据源）的构成形式。【例【例4.24】查找001102号同学所选修的全部课程的同学的学号。本例即要查找这样的学号y，对所有的课程号x，若001102号同学

24、选修了该课，那么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语句查询的对象是表。4.2.3查询对象查询对象【例【例4.25】在XS表中查找1986年1月1日以前出生的学生的姓名和专业名。 SELECT XM,ZYM FROM (

25、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_KC.XH AND KC.KCH = XS_KC. KCH AND KCM = 计算机基础 AND CJ = 80;执行结果为：X

26、H XM KCM CJ001101 王林 计算机基础 80001104 韦严平 计算机基础 90001108 林一帆 计算机基础 85001110张蔚 计算机基础 95001111赵琳 计算机基础 91001201 王敏 计算机基础 80001203 王玉民 计算机基础 87001204 马琳琳 计算机基础 91001216 孙祥欣 计算机基础 81001220 吴薇华 计算机基础 82001241 罗林琳 计算机基础 904.2.4连接连接2. 以以JOIN关键字指定的连接关键字指定的连接PL/SQL扩展了以JOIN关键字指定连接的表示方式，使表的连接运算能力有了增强。连接表的格式为： ON

27、 | 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所指定的连接条件合并两个表，返回满足条件的行。【例【例4.30】查找XSCJ数据库每个学生的情况以及选修的课程情况

28、。 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=80;内连接还可以用于多个表的连接。 4.2.4连接连

29、接【例【例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.33】查找不同课程成绩

30、相同的学生的学号、课程号和成绩。 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

33、 FROM XS 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值。

34、【例【例4.37】求选修101课程的学生的平均成绩。 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忽略NU

35、LL值。【例【例4.38】求选修101课程的学生的最高分和最低分。 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。选择*时

36、将统计总行数。COUNT忽略NULL值。【例【例4.39】(1) 求学生的总人数。 SELECT 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

37、汇总汇总2. GROUP BY子句子句GROUP BY子句用于对表或视图中的数据按字段分组，格式为： GROUP BY 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

38、数据库中各专业的学生数。 SELECT ZYM AS 专业名,COUNT(*) AS 学生数 FROM XS GROUP BY 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子句和统计函数对数据进行分组后，还可以使用

39、HAVING子句对分组数据进行进一步的筛选。例如查找XSCJ数据库中平均成绩在85分以上的学生，就是在XS_KC数据库上按学号分组后筛选出符合平均成绩大于等于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 9100120

40、3 87001204 91001241 904.2.5汇总汇总【例【例4.44】查找选修课程超过2门且成绩都在80分以上的学生的学号。 SELECT 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 F

41、ROM XS WHERE ZYM= 通信工程 ) GROUP BY XH HAVING AVG(CJ) =85;4.2.6排序排序在应用中经常要对查询的结果排序输出，例如学生成绩由高到低排序。在SELECT语句中，使用ORDER BY子句对查询结果进行排序。ORDER BY子句的格式为： ORDER BY order_by_expression ASC | DESC ,n 其中order_by_expression是排序表达式，可以是列名、表达式或一个正整数，当expression是一个正整数时，表示按表中的该位置上列排序。关键字ASC表示升序排列，DESC表示降序排列，系统默认值为ASC。【

42、例【例4.46】将通信工程专业的学生按出生时间先后排序。 SELECT * FROM XS WHERE ZYM= 通信工程 ORDER BY CSSJ;4.2.6排序排序【例【例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韦严平计

43、算机基础90林一帆计算机基础85王林计算机基础80李明计算机基础78张强民计算机基础66李方方计算机基础65严红计算机基础63王燕计算机基础624.2.7 SELECT语句的语句的UNION子句子句使用UNION子句可以将两个或多个SELECT查询的结果合并成一个结果集，其格式为： | ( ) UNION A LL | ( ) UNION A LL | ( ) n 其中query specification和query expression都是SELECT查询语句。使用 UNION 组合两个查询的结果集的基本规则是： (1) 所有查询中的列数和列的顺序必须相同。(2) 数据类型必须兼容。 关键

44、字ALL表示合并的结果中包括所有行，不去除重复行。不使用ALL则在合并的结果去除重复行。含有UNION的SELECT查询也称为联合查询。4.2.7 SELECT语句的语句的UNION子句子句【例【例4.48】设在XSCJ数据库中建两个表：数学系学生、外语系学生，表结构与XS表相同，两个表分别存储数学系和外语系的学生情况，下列语句将这两个表的数据合并到XS表中。 SELECT * FROM XS UNION ALL SELECT * FROM 数学系学生 UNION ALL SELECT * FROM 外语系学生;UNION操作常用于归并数据，例如归并月报表形成年报表，归并各部门数据等。注意UN

45、ION还可以与GROUP BY及ORDER BY一起使用，用来对合并所得的结果表进行分组或排序。4.3数据库的视图数据库的视图4.3.1视图的概念视图的概念视图是从一个或多个表（或视图）导出的表。视图与表（有时为与视图区别，也称表为基表Base Table）不同，视图是一个虚表，即视图所对应的数据不进行实际存储，数据库中只存储视图的定义，对视图的数据进行操作时，系统根据视图的定义去操作与视图相关联的基表。视图可以由以下任意一项组成：(1) 一个基表的任意子集(2) 两个或者两个以上基表的合集(3) 两个或者两个以上基表的交集(4) 对一个或者多个基表运算的结果集合另一个视图的子集4.3.1视图

46、的概念视图的概念视图一经定义以后，就可以象表一样被查询、修改、删除和更新。使用视图有下列优点：(1)为用户集中数据，简化用户的数据查询和处理。(2)屏蔽数据库的复杂性。 (3)简化用户权限的管理。 (4)便于数据共享。 (5) 可以重新组织数据以便输出到其它应用程序中。 4.3.2创建视图创建视图1. 用用OEM创建视图创建视图以在XSCJ数据库中创建CS_XS（描述计算机专业学生情况）视图说明创建视图的过程。(1) 在如图4.6所示的界面中，选择单击视图进入“视图搜索”界面，如图4.7所示。在该界面选择对象类型, 并可以选择输入方案名称和对象名称, 单击“开始”按钮，查找满足条件的对象类型，

47、若存在满足条件的对象类型，在结果项可以查找到的对象类型。 图图4.6 Oracle企业管理器企业管理器 图图4.7视图搜索界面视图搜索界面 4.3.2创建视图创建视图1. 用用OEM创建视图创建视图(2) 单击“创建”按钮，进入视图创建界面，如图4.8所示。 图图4.8 一般信息选项界面一般信息选项界面 4.3.2创建视图创建视图创建界面有三个选项页面：一般信息、选项和对象选项页面。一般信息页面如图4.8所示，主要定义视图的基本信息，如名称、所属用户方案等。选项（如图4.9所示）和对象选项卡（如图4.10所示），定义视图的一些高级选项，如视图是否只读、约束条件等。 图图4.9选项选项界面选项选

48、项界面 图图4.10对象选项界面对象选项界面4.3.2创建视图创建视图1.用用OEM创建视图创建视图一般信息页面：在“名称”文本框输入视图名称CS_XS；用户方案选ADMIN；在“查询文本”中输入创建视图的SQL语句，如图4.8所示的SQL语句。如果有同名视图存在，若选中“若存在则替换”复选框，那么将用现在定义的视图代替原有同名的视图；否则系统将提示错误信息“视图已存在”，要求重新命名。在别名文本框可以为视图定义别名。 选项选项页面：若选中“强制”选项，则指定创建视图而无须考虑视图基表是否存在或包含该视图的方案所有者是否具有权限。只有选中“限制”复选框后才能设置只读或是否具有约束条件。“只读”

49、单选项规定视图中不能执行删除、插入、更新操作，只能检索数据。“带有复选选项”指定在视图中执行插入和更新操作时，必须能使该视图查询可以选择这些数据，但如果视图的查询中包含子查询或该视图是基于其它视图的，这项指定不一定生效。“约束条件”是指定分配给“复选选项”约束条件的名称。 对象选项页面：该选项页面仅用于对象视图。如果您处理的不是对象视图, 则可跳过此选项卡。如果想创建对象视图，则要选中“作为对象视图”复选框，选择对象类型，然后选择默认属性或是选择可用属性列表列出的对象属性。(6) 单击“确定”，系统执行创建视图操作完成后返回如图4.7所示界面。4.3.2创建视图创建视图2. 使用使用CREAT

50、E VIEW语句创建视图语句创建视图PL/SQL中用于创建视图的语句是CREATE OR REPLACE VIEW语句。语法格式：语法格式：CREATE OR REPLACE FORCE | NOFORCE VIEW schema.view_name ( column_name ,n ) AS select_statementWITH CHECK OPTIONCONSTRAINT constraint_nameWITH READ ONLY【例【例4.49】创建CS_KC视图，包括计算机专业各学生的学号、其选修的课程号及成绩。要保证对该视图的修改都要符合专业名为计算机这个条件。 CREATE O