2 第8章子查询与高级查询 本章要点 掌握在WHERE子句中使用子查询 掌握在HAVING子句中使用子查询 熟练掌握使用IN ANY和ALL操作符实现子查询 熟练掌握关联子查询 熟练掌握嵌套子查询 掌握简单连接 熟练掌握多个表之间的内连接 熟练掌握多个表之间的外连接 了解多个表之间的交叉连接 掌握使用集合操作符实现集合查询 3 8 1子查询 在外部的SELECT UPDATE或DELETE语句内部使用SELECT语句 这个内部SELECT语句称为子查询 Subquery 使用子查询 主要是将子查询的结果作为外部主查询的查找条件 4 8 1 1子查询的类型 在子查询中可以使用两种比较操作符 单行操作符和多行操作符 单行操作符 例如 多行操作符 例如ALL ANY IN EXISTS 可以把子查询分为两种类型 单行子查询和多行子查询 另外 子查询还有下面3种子类型 这3种子类型可以返回一行或多行查询结果 多列子查询 向外部的SQL语句返回多列 关联子查询 引用外部的SQL语句中的一列或多列 在关联子查询中 可以使用EXISTS和NOTEXISTS操作符 嵌套子查询 在子查询中包含有子查询 指定子查询时 需要注意以下几点 子查询需要使用括号 括起来 子查询要放在比较操作符的右边 当子查询的返回值是一个集合而不是一个值时 不能使用单行操作符 而必须根据需要使用ANY IN ALL或EXISTS等操作符 5 8 1 2在WHERE子句中使用子查询 1 使用子查询在WHERE子句中使用子查询的一般用法形式如下 SELECTcolumn listFROMtable nameWHEREexpressionoperator SELECTcolumn nameFROMtable nameWHEREconditionGROUPBYexpHAVINGhaving 其中 在外部SELECT语句的WHERE子句中 expression用来指定一个表达式 也可以是表中的一列 operator可以是单行和多行操作符 中的内容表示子查询内容 6 8 1 2在WHERE子句中使用子查询 2 不能使用ORDERBY子句在子查询的SELECT语句中 可以使用FROM子句 WHERE子句 GROUPBY子句和HAVING子句等 但是有些情况下不能使用ORDERBY子句 例如在WHERE子句中使用子查询时 子查询语句中就不能使用ORDERBY子句 7 8 1 3在HAVING子句中使用子查询 在SELECT语句中使用HAVING子句 可以实现对数据进行分组过滤 在HAVING子句中 如果使用子查询 那么就可以实现对子查询返回的结果根据分组进行过滤 例8 3 对scott用户的emp表进行检索 在HAVING子句中使用子查询 获取哪些部门的员工平均工资小于全体员工的平均工资 具体如下 SQL SELECTdeptno AVG sal FROMscott empGROUPBYdeptno2HAVINGAVG sal 3SELECTAVG sal FROMscott emp DEPTNOAVG SAL 301566 66667 8 8 1 4使用IN操作符实现指定匹配查询 1 使用IN操作符IN操作符用来检查在一个值列表中是否包含指定的值 这个值列表可以是子查询的返回结果 2 使用NOTIN操作符NOTIN操作符用来检查在一个值列表中是否不包含指定的值 NOTIN执行的操作正好与IN在逻辑上相反 3 常见的操作错误多行子查询可以返回多行记录 如果接收子查询结果的操作符是单行操作符 那么在执行语句时 可能会出现错误提示 9 8 1 5使用ANY操作符实现任意匹配查询 在进行多行子查询时 使用ANY操作符 用来将一个值与一个列表中的所有值进行比较 这个值只需要匹配列表中的一个值即可 然后将满足条件的数据返回 其中 值列表可以是子查询的返回结果 在使用ANY操作符之前 必须使用一个单行操作符 例如 等 例8 7 对scott用户的emp表进行操作 获得工资大于任意一个部门的平均工资的员工信息 如下 参见教材P176 10 8 1 6使用ALL操作符实现全部匹配查询 在进行子查询时 使用ALL操作符 用来将一个值与一个列表中的所有值进行比较 这个值需要匹配列表中的所有值 然后将满足条件的数据返回 其中 值列表可以是子查询的返回结果 在使用ALL操作符之前 必须使用一个单行操作符 例如 等 例8 8 对scott用户的emp表进行操作 获得工资大于所有部门的平均工资的员工信息 如下 参见教材P176 11 8 1 7实现多列子查询 多列子查询则是指返回多列数据的子查询语句 当多列子查询返回单行数据时 在WHERE子句中可以使用单行操作符 返回多行数据时 在WHERE子句中必须使用多行操作符 使用子查询比较多个列的数据时 可以使用下面两种方式 成对比较 要求多个列的数据必须同时匹配 非成对比较 通过指定连接关键字 例如AND或OR等 指定多个列的数据是否必须同时匹配 如果使用AND关键字 表示同时匹配 这样就可以实现与成对比较同样的结果 如果使用OR关键字 表示不必同时匹配 12 8 1 7实现多列子查询 例8 9 对scott用户的emp表进行操作 使用成对比较的方式 获得每个部门中工资最低的员工信息 如下 参见教材P177 例8 10 使用非成对比较的方式 并且指定AND关键字 获得每个部门中工资最低的员工信息 如下 参见教材P177 13 8 1 8实现关联子查询 关联子查询会引用外部查询中的一列或多列 这种子查询之所以被称为关联子查询 是因为它的确与外部语句相关 例8 11 查询各个部门中 哪些员工的工资低于其所在部门的平均工资 如下 参见教材P178 14 8 1 8实现关联子查询 1 使用EXISTS操作符在关联子查询中可以使用EXISTS或NOTEXISTS操作符 其中 EXISTS操作符用于检查子查询所返回的行是否存在 它可以在非关联子查询中使用 但是更常用于关联子查询 2 使用NOTEXISTS操作符在执行的操作逻辑上 NOTEXISTS操作符的作用与EXISTS操作符相反 在需要检查数据行中是否不存在子查询返回的结果时 就可以使用NOTEXISTS 15 8 1 8实现关联子查询 3 EXISTS与IN的比较在使用NOTEXISTS和NOTIN时 如果一个值列表中包含有空值 NOTEXISTS返回TRUE 而NOTIN则返回FALSE 例8 14 使用NOTEXISTS操作符 检索最高领导人员的有关信息 也就是该员工没有上级领导 对应的mgr列的值为NULL 具体如下 参见教材P179 例8 15 使用NOTIN操作符 重写上述语句 如下 参见教材P180 16 8 1 9实现嵌套子查询 所谓嵌套子查询 是指在子查询内部使用其他子查询 嵌套子查询的嵌套层次最多为255层 大多数情况下 嵌套子查询都在外层子查询的WHERE子句中 例8 16 已知工作地点在NEWYORK和CHICAGO的两个部门 要求根据这两个部门的平均工资中的最大值 获取工资大于这个最大值的员工信息 如下 SQL SELECTempno ename sal deptnoFROMscott emp2WHEREsal 3SELECTMAX AVG sal FROMscott empWHEREdeptnoIN 4SELECTdeptnoFROMscott dept5WHERElocIN NEWYORK CHICAGO 6GROUPBYdeptno EMPNOENAMESALDEPTNO 7566JONES2975207788SCOTT3000207839KING5000107902FORD300020 17 8 1 10在UPDATE和DELETE语句中使用子查询 1 在UPDATE语句中使用子查询在UPDATE语句中使用子查询 可以将子查询返回的结果赋值给需要更新的列 例8 17 将员工编号为7839的员工的工资设置为平均工资 如下 SQL UPDATEscott empSETsal 2SELECTAVG sal FROMscott emp 3WHEREempno 7839 已更新1行 2 在DELETE语句中使用子查询在DELETE语句中使用子查询 可以根据子查询返回的结果删除指定的行 例8 18 删除工作地点在NEWYORK的所有员工信息 如下 SQL DELETEFROMscott empWHEREdeptnoIN 2SELECTdeptnoFROMscott deptWHEREloc NEWYORK 已删除3行 18 8 2实验指导 获取借阅次数最多的前5本图书 实验指导8 1 获取借阅次数最多的前5本图书由于需要显示图书信息 所以从图书表book中进行检索 然后结合借书表lend 获得每本书的借阅情况 最后按照每本书的借阅次数降序排列 获得前5本图书信息即可 具体如下 参见教材P182上述语句中 在最外层SELECT语句的WHERE子句中使用了子查询 并且使用IN操作符指定匹配查询 第3行的SELECT语句中 在FROM子句中使用子查询 通过子查询获得分组和排序之后的bookid 最后在WHERE子句中指定rownum列小于等于5 指定排序之后的前5行记录 19 8 3高级查询 检索数据时 通过各个表之间共同列的关联性 可以查询存放在多个表中的不同实体的信息 如果在查询时需要对多个表进行操作 并且指定多个表的连接关系 则该查询就称为高级查询 也可以称为连接查询 20 8 3 1使用等号 实现多个表的简单连接 在连接查询中 如果仅仅通过SELECT子句和FROM子句连接多个表 那么查询的结果将是一个通过笛卡儿积所生成的表 例8 19 使用SELECT子句和FROM子句 从scott用户的emp表和dept表中检索数据 如果不指定检索条件 将得到56行记录 具体如下 参见教材P183由于scott emp表中有14行记录 scott dept表中有4行记录 所以笛卡儿积所生成的表一共有56 14 4 56 行记录 21 8 3 2使用表的别名 设置表的别名 只需要在FROM子句中引用该表时 将表别名跟在表的实际名称后面即可 表别名和表的实际名称之间使用空格进行分隔 例8 21 使用表别名的方式 重写例8 20中所示的语句 为scott emp表设置表别名为e 为scott dept表设置表别名为d 语句如下 SQL SELECTempno ename sal e deptno d deptno dname2FROMscott empe scott deptd3WHEREe deptno d deptno 22 8 3 3使用INNERJOIN实现多个表的内连接 在FROM子句中 使用JOIN连接的语法形式如下 FROMjoin table1join typejoin table2 ON join condition join type ONjoin condition 语法说明如下 join table1 join table2 参与连接操作的表名 join type 连接类型 连接类型有INNERJOIN 内连接 OUTERJOIN 外连接 和CROSSJOIN 交叉连接 join condition 连接条件 由被连接表中的列和比较运算符 逻辑运算符等构成 可以使用多组join type ONjoin condition 子句 实现多个表的连接 23 8 3 3使用INNERJOIN实现多个表的内连接 1 等值连接所谓等值连接 是指在连接条件中使用等于 运算符比较被连接的值 也就是通过相等的列值连接起来的查询 2 不等连接所谓不等连接 就是在连接条件中使用除等号 外的其他比较运算符 构成非等值连接查询 可以使用的比较运算符包括 大于 大于等于 不等于 不等于 LIKE IN和BETWEEN等 24 8 3 3使用INNERJOIN实现多个表的内连接 3 自然连接自然连接 NAT