第 4 章 数据库的查询和视图

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

2. 4.1.1选择 【例4.1】学生情况表如表4.1所示。 若要在学生情况表中找出学生表中性别为女且平均成绩在80分以上的行形成 一个新表， 该选择运算的结果如表4.2所示。 表4.1 学生表 表4.2 查询后的结果

3. 4.1.2投影 投影（Projection）也是单目运算。投影就是选择表中指定的列，这样在查询 结果中只显示指定数据列，减少了显示的数据量也提高查询的性能。 【例4.2】若在表4.1中对“姓名”和“平均成绩”投影，该查询得到如表4.3所示的 新表。 表4.3 投影后的新表

4. 4.1.3连接 表4.4 A表 连接（JOIN）是把两个表中的行按照给定的条件进行拼接而形成新表。 【例4.3】若表A和B分别如表4.4和表4.5所示，则连接后结果如表4.6所示。 表4.5 B表 表4.6 连接后的表

5. 4.1.3连接 表4.7 A表 【例4.4】若表A和表B分别如表4.7和表4.8所示，自然连接后的新表C如表4.9 所示。 表4.8 B表 表4.9 C表

6. 4.2 数据库的查询 使用数据库和表的主要目的是存储数据以便在需要时进行检索、统计或组织 输出，通过PL/SQL的查询可以从表或视图中迅速方便地检索数据。PL/SQL的 SELECT语句可以实现对表的选择、投影及连接操作，其功能十分强大。 下面介绍SELECT语句，它是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 ]] /*ORDER子句，指定排序表达式和顺序*/

7. 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语句选择一个表中的某些列，各列名之间要以逗号分隔。 语法格式： SELECT column_name [ , column_name…] FROM table_name WHERE search_condition 其功能是在FROM子句指定的表中检索符合search_condition条件的列。

8. 4.2.1选择列 【例4.5】查询XSCJ数据库的XS表中各个同学的XM、XH和ZXF。 SELECT XH, XM,ZXF FROM XS; 执行结果如图4.1所示。 图4.1 在XS表中选择列

9. 4.2.1选择列 【例4.6】查询XS表中ZXF大于45同学的XH、XM和ZXF。 SELECT XH,XM,ZXF FROM XS WHERE ZXF>45; 当在SELECT语句指定列的位置上使用*号时，表示选择表的所有列。 【例4.7】查询XS表中的所有列。 SELECT * FROM XS; 该语句等价于语句： SELECT XH,XM,ZYM,XB,CSSJ,ZXF,BZ FROM XS; 其执行后将列出XS表中的所有数据。

10. 4.2.1选择列 2.　修改查询结果中的列标题 　当希望查询结果中的某些列或所有列显示时使用自己选择的列标题时，可以在 列名之后使用AS子句来更改查询结果的列标题名，其中column_alias是指定的列 标题。 【例4.8】查询XS表中计算机同学的XH、XM和ZXF，结果中各列的标题分别 指定为学号、姓名和总学分。 SELECT XH AS 学号,XM AS 姓名,ZXF AS 总学分 FROM XS WHERE ZYM=’计算机’; 　该语句的执行结果如图4.2所示。 　更改查询结果中的列标题可以省略AS关键字。例如： SELECT XH 学号, XM 姓名, ZXF 总学分 FROM XS WHERE ZYM=’计算机’;

11. 4.2.1选择列 图4.2更改查询结果中的列标题

12. 4.2.1选择列 3. 计算列值 　使用SELECT对列进行查询时，在结果中可以输出对列值计算后的值，即 SELECT子句可使用表达式作为结果，格式为： SELECT expression [ , expression ] 【例4.9】创建产品销售数据库XSH，并在其中创建产品表CP，其表结构如表 4.10所 表4.10 CP表结构

13. 4.2.1选择列 表4.11 CP表 设CP表中已有如表4.11所示的数据。

14. 4.2.1选择列 下列语句将列出产品名称和产品总值： SELECT CPMC AS 产品名称, JG * KCL AS产品总值 FROM CP; 该语句的执行结果如图4.3所示。 图4.3 计算列值

15. 4.2.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时，将保留结果集的所有行。

16. 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 　计算机 　 50 　计算机 　 50 　计算机 　 48 　计算机 　　　　　50 　通信工程 42 　通信工程 40 　通信工程 42 　通信工程 42 　通信工程 44 　通信工程 42 　通信工程 42 　通信工程 42 　通信工程 42 　通信工程 42 　通信工程 50

17. 4.2.2选择行 1.　表达式比较 　比较运算符用于比较两个表达式值，共有7个，分别是： =（等于）、<（小 于）、<=（小于等于）、>（大于）、>=（大于等于）、<>（不等于）、!=（不 等于）。比较运算的格式为： expression { = | < | <= | > | >= | <> | != } 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;

18. 4.2.2选择行 2.模式匹配 LIKE谓词用于指出一个字符串是否与指定的字符串相匹配，其运算对象可以是 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 KCL 10001100 冰箱A_100 1500.0 500 10002120 冰箱A_200 1850.0 200 10001200 冰箱B_200 1600.0 1200 10001102　　 冰箱C_210 1890.0 600

19. 4.2.2选择行 【例4.14】查询XSCJ数据库XS表中姓“王”且单名的学生情况。 SELECT * FROM XS WHERE XM LIKE ‘王_’; 　执行结果为： XH XM ZYM XB CSSJ ZXFBZ 061101 　王林　 计算机 　男 10-二月-8650 061103 　王燕 　计算机 　女 06-十月-8550 061201 　王敏　 通信工程 　男 10-六月-8442 061202 　王林　 通信工程 　男 29-一月-8540　　有一门课不及格，待补考 3.　范围比较 　用于范围比较的关键字有两个：BETWEEN和IN。 　当要查询的条件是某个值的范围时，可以使用BETWEEN关键字。BETWEEN关键字指出 查询范围，格式为： expression [ NOT ] BETWEEN expression1 AND expression2 　当不使用NOT时，若表达式expression的值在表达式expression1与expression2之间（包 括这两个值），则返回TRUE，否则返回FALSE；使用NOT时，返回值刚好相反。 　注意：expression1的值不能大于expression2的值。

20. 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(‘19851231’,’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;

21. 4.2.2选择行 4. 空值比较 当需要判定一个表达式的值是否为空值时，使用IS NULL关键字，格式为： expression IS [ NOT ] NULL 当不使用NOT时，若表达式expression的值为空值，返回TRUE，否则返回 FALSE；当使用NOT时，结果刚好相反。 【例4.17】查询XSCJ数据库中总学分尚不定的学生情况。 SELECT * FROM XS WHERE ZXF IS NULL;

22. 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，则返回的值 刚好相反。

23. 4.2.2选择行 【例4.18】在XSCJ数据库中有描述课程情况的表KC和描述学生成绩表的表 XS_KC(表的结构和样本数据见附录A)。查找选修了课程号为101的课程的学生 的情况： SELECT * FROM XS WHERE XH IN ( SELECT XH FROM XS_KC WHERE KCH = ‘101’ ); 在执行包含子查询的SELECT语句时，系统先执行子查询，产生一个结果表，再 执行查询。本例中，先执行子查询： SELECT XH FROM XS_KC WHERE KCH = ‘101’; 得到一个只含有学号列的表，XS_KC中课程名列值为‘101’的行在结果表中都有 一行。再执行外查询，若XS表中某行的学号列值等于子查询结果表中的任一个 值，则该行就被选择。

24. 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 = '离散数学' ) );

25. 执行结果为： XH XM ZYM ZXF 061201 王敏 通信工程 42 061202 王林 通信工程 40 061203 王玉民 通信工程 42 061204 马琳琳 通信工程 42 061206 李计 通信工程 42 061210 李红庆 通信工程 44 061216 孙祥欣 通信工程 42 061218 孙研 通信工程 42 061220 吴薇华 通信工程 42 061221 刘燕敏 通信工程 42 061241 罗林琳 通信工程 50

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

27. 4.2.2选择行 【例4.20】查找比所有计算机系学生年龄都大的学生。 SELECT * FROM XS WHERE CSSJ <ALL ( SELECT CSSJ FROM XS WHERE ZYM= '计算机' ); 执行结果如图4.4所示。

28. 图4.4 查找结果

29. 4.2.2选择行 【例4.21】查找课程号206的成绩不低于课程号101的最低成绩的学生的学号。 SELECT XH FROM XS_KC WHERE KCH = '206' AND CJ>= ANY ( SELECT CJ FROM XS_KC WHERE KCH = '101' ); (3) EXISTS子查询 EXISTS谓词用于测试子查询的结果是否为空表，若子查询的结果集不为空，则 EXISTS返回TRUE，否则返回FALSE。EXISTS还可与NOT结合使用，即NOT EXISTS，其返回值与EXIST刚好相反。格式为： [ NOT ] EXISTS ( subquery )

30. 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。

31. 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 ) ); 本例即查找没有一门功课不选修的学生。

32. 4.2.3查询对象 前面介绍了SELECT的选择列和行，这里介绍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语句查询的对象是表。

33. 4.2.3查询对象 【例4.25】在XS表中查找1986年1月1日以前出生的学生的姓名和专业名。 SELECT XM,ZYM FROM (SELECT * FROM XS WHERE CSSJ<TO_DATE(‘19860101’,’YYYYMMDD')); 执行结果为： XM ZYM 王燕 计算机 林一帆 计算机 张强民 计算机 严红 计算机 王敏 通信工程 王林 通信工程 马琳琳 通信工程 李计 通信工程 李红庆 通信工程 孙祥欣 通信工程 刘燕敏 通信工程

34. 4.2.4连接 1. 连接谓词 可以在SELECT语句的WHERE子句中使用比较运算符给出连接条件对表进行连 接，将这种表示形式称为连接谓词表示形式。 【例4.26】查找XSCJ数据库每个学生的情况以及选修的课程情况。 SELECT XS.* ,XS_KC.* FROM XS , XS_KC WHERE XS.XH=XS_KC.XH; 结果表将包含XS表和XS_KC表的所有列，结果如图4.5所示。

35. 图4.5 连接后的部分数据

36. 4.2.4连接 【例4.27】自然连接查询。 SELECT XS.* , XS_KC.KCH, XS_KC.CJ FROM XS , XS_KC WHERE XS.XH=XS_KC.XH; 本例所得的结果表包含以下字段：学号、姓名、专业名、性别、出生时间、总学 分、备注、课程号、成绩。 若选择的字段名在各个表中是唯一的，则可以省略字段名前的表名。如本例的 SELECT子句也可写为： SELECT XS.* , KCH , CJ FROM XS , XS_KC WHERE XS.XH = XS_KC.XH;

37. 4.2.4连接 【例4.28】查找选修了206课程且成绩在80分以上的学生姓名及成绩。 SELECT XM AS 姓名,CJ AS 成绩 FROM XS , XS_KC WHERE XS.XH = XS_KC.XH AND KCH = ‘206’ AND CJ >= 80; 执行结果为： 姓名 成绩 王燕 81 李方方 80 林一帆 87 张蔚 89 有时用户所需要的字段来自两个以上的表，那么就要对两个以上的表进行连接， 称之为多表连接。

38. 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; 执行结果为： XH XM KCM CJ 001101 王林 计算机基础 80 001104 韦严平 计算机基础 90 001108 林一帆 计算机基础 85 001110 张蔚 计算机基础 95 001111 赵琳 计算机基础 91 001201 王敏 计算机基础 80 001203 王玉民 计算机基础 87 001204 马琳琳 计算机基础 91 001216 孙祥欣 计算机基础 81 001220 吴薇华 计算机基础 82 001241 罗林琳 计算机基础 90

39. 4.2.4连接 2. 以JOIN关键字指定的连接 PL/SQL扩展了以JOIN关键字指定连接的表示方式，使表的连接运算能力有了增 强。连接表的格式为： <table_source> <join_type> <table_source> ON <search_condition> | <table_source> CROSS JOIN <table_source> | <joined_table> 其中table_source为需连接的表，join_type表示连接类型，ON用于指定连接条 件。join_type的格式为： [ INNER | { LEFT | RIGHT | FULL } [ OUTER ] [ <join_hint> ] JOIN 其中INNER表示内连接，OUTER表示外连接，join_hint是连接提示。CROSS JOIN表示交叉连接。因此，以JOIN关键字指定的连接有三种类型。

40. 4.2.4连接 2. 以JOIN关键字指定的连接 (1) 内连接 内连接按照ON所指定的连接条件合并两个表，返回满足条件的行。 【例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>=80; 内连接还可以用于多个表的连接。

41. 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.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 CJ 001102 102 206 78 001102 206 102 78

42. 4.2.4连接 2. 以JOIN关键字指定的连接 (2) 外连接 外连接的结果表不但包含满足连接条件的行，还包括相应表中的所有行。外连接 包括三种： 左外连接（LEFT OUTER JOIN）：结果表中除了包括满足连接条件的行外，还 包括左表的所有行； 右外连接（RIGHT OUTER JOIN）：结果表中除了包括满足连接条件的行外，还 包括右表的所有行； 完全外连接（FULL OUTER JOIN）：结果表中除了包括满足连接条件的行外，还 包括两个表的所有行。 其中的OUTER关键字均可省略。

43. 4.2.4连接 【例4.34】查找所有学生情况及他们选修的课程号，若学生未选修任何课，也要 包括其情况。 SELECT XS.* , KCH FROM XS LEFT OUTER JOIN XS_KC ON XS.XH = XS_KC.XH; 本例执行时，若有学生未选任何课程，则结果表中相应行的课程号字段值为NULL。 【例4.35】查找被选修了的课程的选修情况和所有开设的课程名。 SELECT XS_KC.* , KCM FROM XS_KC RIGHT JOIN KC ON XS_KC.KCH= KC.KCH; 本例执行时，若某课程未被选修，则结果表中相应行的学号、课程号和成绩字段值 均为NULL。 注意：外连接只能对两个表进行。

44. 4.2.4连接 2. 以JOIN关键字指定的连接 (3) 交叉连接 交叉连接实际上是将两个表进行笛卡尔积运算，结果表是由第一个表的每行与第 二个表的每一行拼接后形成的表，因此结果表的行数等于两个表行数之积。 【例4.36】列出学生所有可能的选课情况。 SELECT XH, XM, KCH, KCM FROM XS CROSS JOIN KC; 注意: 交叉连接不能有条件，且不能带WHERE子句。

45. 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课程的学生的平均成绩。 SELECT AVG(CJ) AS 课程101平均成绩 FROM XS_KC WHERE KCH='101'; 执行结果为： 课程101平均成绩 78

46. 4.2.5汇总 1. 统计函数 （2）MAX和MIN函数。MAX和MIN函数分别用于求表达式中所有值项的最大值 与最小值，语法格式为： MAX / MIN ( [ ALL | DISTINCT ] expression ) 其中expression是常量、列、函数或表达式，其数据类型可以是数字、字符和时 间日期类型。ALL表示对所有值进行运算，DISTINCT表示去除重复值，缺省为 ALL。MAX/MIN忽略NULL值。 【例4.38】求选修101课程的学生的最高分和最低分。 SELECT MAX(CJ) AS 课程101的最高分, MIN(CJ) AS 课程101的最低分 FROM XS_KC WHERE KCH='101'; 执行结果为： 课程101的最高分 课程101的最低分 95 62

47. 4.2.5汇总 1. 统计函数 （3）COUNT函数。COUNT函数用于统计组中满足条件的行数或总行数，格式为： COUNT ( { [ ALL | DISTINCT ] expression } | * ) 其中expression是一个表达式。ALL表示对所有值进行运算，DISTINCT表示去除重复 值，缺省为ALL。选择*时将统计总行数。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分以上的人数 2

48. 4.2.5汇总 2. GROUP BY子句 GROUP BY子句用于对表或视图中的数据按字段分组，格式为： [ GROUP BY [ ALL ] group_by_expression [,…n] group_by_expression：用于分组的表达式，其中通常包含字段名。指定ALL 将显示所有组。使用GROUP BY子句后，SELECT子句中的列表中只能包含在 GROUP BY中指出的列或在统计函数中指定的列。 【例4.40】将XSCJ数据库中各专业名输出。 SELECT ZYM AS 专业名 FROM XS GROUP BY ZYM; 执行结果为： 专业名 计算机 通信工程

49. 4.2.5汇总 【例4.41】求XSCJ数据库中各专业的学生数。 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 78 20 102 77 11 206 75 11

50. 4.2.5汇总 3. HAVING子句 使用GROUP BY子句和统计函数对数据进行分组后，还可以使用HAVING子句对 分组数据进行进一步的筛选。例如查找XSCJ数据库中平均成绩在85分以上的学 生，就是在XS_KC数据库上按学号分组后筛选出符合平均成绩大于等于85的学 生。HAVING子句的格式为： [ HAVING <search_condition> ] 其中search_condition为查询条件，与WHERE子句的查询条件类似，并且可以 使用统计函数。 【例4.43】查找XSCJ数据库中平均成绩在85分以上的学生的学号和平均成绩。 SELECT XH AS 学号, AVG(CJ) AS 平均成绩 FROM XS_KC GROUP BY XH HAVING AVG(CJ)>=85; 执行结果为： 学号 平均成绩 001110 91 001203 87 001204 91 001241 90