第7章 数据库中表的高级查询操作

1、第第7章章 数据库中表的高数据库中表的高级查询操作级查询操作计算机系 胡骏学习目标学习目标lT-SQL语言中，SELECT语句是功能非常强大的语句，本章着重介绍复杂查询的实现l需要掌握的内容 GROUP BY ORDER BY 以及聚合函数的使用 UNION运算符的使用方法 连接查询7.1 限定条件的查询限定条件的查询lSELECT语句中的WHERE子句是用来控制结果集的记录构成。可以再WHERE子句中指定一系列的查询条件，满足这些条件的记录集构成结果集。 比较运算符(=、) 范围说明(BETWEEN 和NOT BETWEEN) 可选值列表(IN 、NOT IN) 模糊查询(LIKE和NOT

2、LIKE) 逻辑组合(AND、OR、NOT)7.1 限定条件的查询限定条件的查询1.比较条件查询2.范围查询3.列表查询5.逻辑运算符略7.1 限定条件的查询限定条件的查询4.模糊查询 通配符 7.1 限定条件的查询限定条件的查询l例：注意注意查询包含字符串 30% 任何记录LIKE %30%对吗？ 如果遇到以上情况，可以使用ESCAPE子句用以指定转义符LIKE %30!% ESCAPE!例题例题ESCAPE 子句练习子句练习 说明：转义符的作用是指定其后第一个字符为本意字符不为通配符，而转义符本身在查询的时候会忽略。转义符可为除通配符以外其他所有字符。练习练习7.2 使用使用GROUP B

3、Y子句的查询子句的查询lGROUP BY子句的主要作用是可以将数据记录的条件分成多个组，而且只有使用了GROUP BY子句，SELECT 子句中所使用的聚合函数才起作用。GROUP BY子句例子子句例子练习1 求有成绩的每个学生的平均成绩2 求每门课程的平均成绩 3 求各个课程号及相应的选课人数解答解答1 select sno, avg(grade) AS 平均分平均分 from sc group by snoHAVING 子句子句1 指定组或聚合的搜索条件。HAVING 通常与 GROUP BY 子句一起使用。2 如果不使用 GROUP BY 子句，HAVING 的行为与 WHERE 子句一

4、样。 3 HAVING子句在整个查询语句的最后 select from where group by having HAVING子句例题子句例题1 求平均成绩大于86分的学生学号及平均成绩2 求平均成绩大于86分的课程的课程号及课程平均成绩解答解答1 select sno,avg(grade) as 学生平均成学生平均成绩绩 from sc group by sno having avg(grade) 86HAVING子句练习子句练习 查询18岁以上（不含18岁）的男女生的人数与性别 7.3 使用聚合函数查询使用聚合函数查询l聚合函数包括SUM、AVG、COUNT、COUNT(*)、MAX、M

5、IN。他们的作用是在查询结果集中生成汇总值。 其中SUM、AVG、 MAX、MIN略7.3 使用聚合函数查询使用聚合函数查询 COUNT以及COUNT(*)各自的作用。 COUNT的功能是返回表达式值的个数，其表达式通常是字段名称。 COUNT(*)的功能是返回符合条件的记录的个数。COUNT、COUNT(*)例题例题1 Select count(SNO) as 个数 From sc Where grade =882 Select count(*) as 个数 Form sc Where grade=88COUNT、COUNT(*)例题例题1 select count(distinct sno

6、) from sc where grade802 select count(*) from sc where grade80总结总结l两个函数有以下区别： COUNT函数忽略对象中的空值，而COUNT(*)返回所有满足条件记录的个数。 COUNT函数可选用DISTINCT去掉重复记录，而COUNT(*)则不能。 7.4 ORDER BY 子句查询子句查询lORDER BY 子句的作用是根据查询结果中的一个字段或多个字段对查询结果进行排序。 如果是升序则为ASC 如果是降序则为DESCCompute 子句子句 COMPUTE 子句通常和ORDER BY 子句连用生成分类汇总语法： COMPUTE

7、 AVG | COUNT | MAX | MIN | SUM ( expression ) ,.n BY expression ,.n Compute 子句子句练习练习l查询SC表中所有信息，根据SNO学号排序，并汇总每个同学的平均分。7.5 UNION组合多个运算结果组合多个运算结果lUNION运算符可用来将两个或多个SELECT语句的查询结果组合成一个结果集。l组合的基本原则 所有查询中的列数和列的顺序必须相同 数据类型必须兼容。补充：补充：连接查询连接查询挂籍52裴一就51王智欣50樊科毅49姓名学号70696810310110025687849711411812225171629710

8、9123113250808683114125114249综合化学X物理X英语数学语文班别学号表:gao202表:成绩2班114118122裴一就51109123113王智欣50114125114樊科毅49英语数学语文姓名学号查询后的表当查询同时涉及两个或两个以上的表时，称为连接查询补充：补充：连接查询连接查询 l数据表之间的联系是通过表的字段值来体现的，这种字段称为连接字段。l连接操作的目的就是通过加在连接字段的条件将多个表连接起来，以便从多个表中查询数据。连接操作的执行过程连接操作的执行过程l嵌套循环法 首先在表1中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将

9、表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。l表2全部查找完后，再找表1中第二个元组，然后再从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第二个元组与该元组拼接起来，形成结果表中一个元组。l重复上述操作，直到表1中的全部元组都处理完毕 连接方法连接方法l方法1：表之间满足一定的条件的行进行连接，此时FROM子句中指明进行连接的表名，WHERE子句指明连接的列名及其连接条件。 Select 学号,姓名,语文,数学,英语 from gao202,成绩2班 where gao202.学号=成绩2班.学号 连接方法连接方法l方法2：利用关键字JOIN进行连接 CROSS

10、 JOIN（交叉连接） INNER JOIN （内连接） LEFT （OUTER） JOIN RIGHT （OUTER） JOIN （外连接） FULL （OUTER） JOIN连接查询类型连接查询类型广义笛卡尔积等值连接(含自然连接)与非等值连接自身连接外连接查询复合条件连接查询交叉连接交叉连接 又称为非限制连接，不带连接谓词的连接，它将两个表不加任何约束地组合在一起，也就是将第一个表的每条记录分别与第二个表的每条记录组成新记录。交叉连接交叉连接交叉连接有两种语法格式：1、SELECT 列名列表 FROM 表名1,表名22、SELECT 列名列表 FROM 表名1 CROSS JOIN 表名

11、2交叉连接示意图交叉连接示意图Select * from 商品,库存或者Select * from 商品 cross join库存等值与非等值连接查询等值与非等值连接查询 . . 其中,比较运算符主要有：、!。 当比较运算符为“时，称为等值连接，其他情况为非等值连接等值连接查询等值连接查询 l等值连接是指将两个表根据各自表中共有字段 值相等连接起来的连接查询。 select * from 库存,商品where 库存.商品名称=商品.商品名称自然连接查询自然连接查询l等值连接的一种特殊情况，把目标列中重复的 属性列去掉 。 select 库存.商品名称,库存.商品名称,库存.商品单价,库存.商品

12、数量,商品.销售数量,商品.销售单价 from 库存,商品 where 库存.商品名称=商品.商品名称非等值连接非等值连接CREATE TABLE 等级(low int,upp int,rank char(10)INSERT INTO 等级 values(90,100,非常优秀)INSERT INTO 等级 values(80,89,优秀)INSERT INTO 等级 values(70,79,中)INSERT INTO 等级 values(60,69,及格)INSERT INTO 等级 values(0,59,不及格)非等值连接查询非等值连接查询SELECT 学号,姓名,高等数学,rankF

13、ROM 等级,学生成绩表 WHERE 高等数学 BETWEEN low AND upp注意：注意：l等值连接和自然连接实际上就是内连接（inner join）。所有等值连接和自然连接查询的语句都可以改写为inner join 语句。 例: select * from 库存,销售 where 库存.商品名称=销售.商品名称 改： select * from 库存 inner Join 销售 on 库存.商品名称=销售.商品名称 自身连接自身连接 当一个表与自已进行连接操作时，称为表的自身连接。 自身连接就是一张表的两个副本之间的内连接，使用它可以将同一个表的不同行连接起来。 自身连接自身连接 使

14、用自身连接时，必须为表指定两个不同的别名，使之在逻辑上成为两个表以示区别。 由于所有属性名都是同名属性，因此必须使用表别名前缀。自身连接自身连接l查询电子商务成绩表中选修高等数学课程的成绩高于计算机基础课程成绩的所有学生学号、姓名，并按成绩从高到低排列 自身连接自身连接 SELECT x.学号, x.姓名, x.高等数学, y.计算机应用基础 FROM 电子商务成绩表 as x,电子商务成绩表 as y WHERE x.高等数学y.计算机应用基础 and x.学号=y.学号 ORDER BY x.高等数学 DESC测试结果测试结果外连接（外连接（OUTER JOIN） l外连接与普通连接的共同

15、点与不同点 共同点：都能将两个或多个表进行连接输出 不同点： 内连接操作只输出满足连接条件的元组外连接操作除输出满足连接条件的元组外，还可以使一个表（左外连接，右外连接）或两个表（全外连接）中的不满足连接条件的元组也出现在结果中。外连接外连接左外连接左外连接 LEFTOUTER JOIN 将左表的所有记录分别与右表的每一条记录进行连接组合，结果集中除显示符合条件的数据行以外，还显示左边表中不符合条件的数据行，此时右边数据行会以NULL来显示。左外连接测试左外连接测试 SELECT T1.*,T2.* FROM T1 LEFT JOIN T2 ON T1.sno=T2.sno结果：T1.sno

16、sname T2.sno degree001 王辉 001 80002 程伟刚 NULL NULL003 薛东清 003 90004 孙强 004 78右外连接右外连接 RIGHT OUTER JOIN l将右表的所有记录分别与左表的每一条记录进行连接组合，结果集中除显示符合条件的数据行以外，还要显示右边表中不符合条件的数据行，此时左边数据行会以NULL来显示。 右外连接测试右外连接测试SELECT T1.*,T2.* FROM T1 RIGHT JOIN T2 ON T1.sno=T2.sno结果：T1.sno sname T2.sno degree001 王辉 001 80003 薛东清 003 90004 孙强 004 78NULLNULL 005 87全外连接：全外连接：FULLOUTER JOINl显示符合条件的数据行以及左边表和右边表中不符合条件的数据行，此时缺乏数据的数据行会以NULL来显示。全外连接测试全外连接测试SELECT T1.*,T2.* FROM T1 FULL JOIN T2 ON T1.sno=T2.sno结果：T1.sno sname T2.sno deg