子查询与高级查询.pptx
1第8章 子查询与高级查询本章要点：掌握在WHERE子句中使用子查询。掌握在HAVING子句中使用子查询。熟练掌握使用IN、ANY和ALL操作符实现子查询。熟练掌握关联子查询。熟练掌握嵌套子查询。掌握简单连接。熟练掌握多个表之间的内连接。熟练掌握多个表之间的外连接。了解多个表之间的交叉连接。掌握使用集合操作符实现集合查询。第1页/共39页28.1 子 查 询在外部的SELECT、UPDATE或DELETE语句内部使用SELECT语句，这个内部SELECT语句称为子查询(Subquery)。使用子查询，主要是将子查询的结果作为外部主查询的查找条件。第2页/共39页38.1.1 子查询的类型在子查询中可以使用两种比较操作符单行操作符和多行操作符。单行操作符：例如=、=、=、!=。多行操作符：例如ALL、ANY、IN、EXISTS。可以把子查询分为两种类型：单行子查询和多行子查询。另外，子查询还有下面3种子类型，这3种子类型可以返回一行或多行查询结果。多列子查询：向外部的SQL语句返回多列。关联子查询：引用外部的SQL语句中的一列或多列。在关联子查询中，可以使用EXISTS和NOT EXISTS操作符。嵌套子查询：在子查询中包含有子查询。指定子查询时，需要注意以下几点：子查询需要使用括号()括起来。子查询要放在比较操作符的右边。当子查询的返回值是一个集合而不是一个值时，不能使用单行操作符，而必须根据需要使用ANY、IN、ALL或EXISTS等操作符。第3页/共39页48.1.2 在WHERE子句中使用子查询1使用子查询在WHERE子句中使用子查询的一般用法形式如下：SELECT column_list FROM table_name WHERE expression operator(SELECT column_name FROM table_name WHERE condition GROUP BY exp HAVING having);其中，在外部SELECT语句的WHERE子句中，expression用来指定一个表达式，也可以是表中的一列；operator可以是单行和多行操作符；()中的内容表示子查询内容。第4页/共39页58.1.2 在WHERE子句中使用子查询2不能使用ORDER BY子句在子查询的SELECT语句中，可以使用FROM子句、WHERE子句、GROUP BY子句和HAVING子句等，但是有些情况下不能使用ORDER BY子句，例如在WHERE子句中使用子查询时，子查询语句中就不能使用ORDER BY子句。第5页/共39页68.1.3 在HAVING子句中使用子查询在SELECT语句中使用HAVING子句，可以实现对数据进行分组过滤。在HAVING子句中，如果使用子查询，那么就可以实现对子查询返回的结果根据分组进行过滤。【例8.3】对scott用户的emp表进行检索，在HAVING子句中使用子查询。获取哪些部门的员工平均工资小于全体员工的平均工资。具体如下：SQL SELECT deptno,AVG(sal)FROM scott.emp GROUP BY deptno 2 HAVING AVG(sal)、SELECT empno,ename,sal,deptno FROM scott.emp 2 WHERE sal (3 SELECT MAX(AVG(sal)FROM scott.emp WHERE deptno IN(4 SELECT deptno FROM scott.dept 5 WHERE loc IN(NEW YORK,CHICAGO)6 GROUP BY deptno);EMPNOENAME SAL DEPTNO-7566JONES 2975 20 7788SCOTT 3000 20 7839KING 5000 10 7902FORD 3000 20第15页/共39页168.1.10 在UPDATE和DELETE语句中使用子查询1在UPDATE语句中使用子查询在UPDATE语句中使用子查询，可以将子查询返回的结果赋值给需要更新的列。【例8.17】将员工编号为7839的员工的工资设置为平均工资，如下：SQL UPDATE scott.emp SET sal=(2 SELECT AVG(sal)FROM scott.emp)3 WHERE empno=7839;已更新 1 行。2在DELETE语句中使用子查询在DELETE语句中使用子查询，可以根据子查询返回的结果删除指定的行。【例8.18】删除工作地点在NEW YORK的所有员工信息，如下：SQL DELETE FROM scott.emp WHERE deptno IN(2 SELECT deptno FROM scott.dept WHERE loc=NEW YORK);已删除3行。第16页/共39页178.2 实验指导获取借阅次数最多的前5本图书实验指导8-1：获取借阅次数最多的前5本图书由于需要显示图书信息，所以从图书表book中进行检索；然后结合借书表lend，获得每本书的借阅情况；最后按照每本书的借阅次数降序排列，获得前5本图书信息即可。具体如下：参见教材P182上述语句中，在最外层SELECT语句的WHERE子句中使用了子查询，并且使用IN操作符指定匹配查询；第3行的SELECT语句中，在FROM子句中使用子查询，通过子查询获得分组和排序之后的bookid；最后在WHERE子句中指定rownum列小于等于5，指定排序之后的前5行记录。第17页/共39页补充在 select 子句中使用子查询显示每个员工和企业平均工资之间的差距select empno,ename,sal,sal-(select avg(sal)from scott.emp)saldiff from scott.emp4在from子句中使用子查询查询各个部门中，那些员工的工资低于其所在部门的平均工资select a.empno,a.ename,a.sal,a.deptno from scott.emp a,(select deptno,avg(sal)avgsal from scott.emp group by scott.emp.deptno)b where a.deptno=b.deptno and a.salb.avgsal18第18页/共39页练习背景：P106 图书管理系统出版时间最早的图书名称和图书编号价格次高的图书名称和图书编号每类图书中价格最低的图书的图书名称和图书编号那类图书中包含的图书最多。19第19页/共39页练习出版时间最早的图书名称和图书编号 select booknumber,bookname from book where book.booktime=(select min(booktime)from book)价格次高的图书名称和图书编号select booknumber,bookname from book where book.bookprice=(select max(bookprice)from book where bookprice SELECT empno,ename,sal,e.deptno,d.deptno,dname 2 FROM scott.emp e,scott.dept d 3 WHERE e.deptno=d.deptno;第24页/共39页258.3.3 使用INNER JOIN实现多个表的内连接在FROM子句中，使用JOIN连接的语法形式如下：FROM join_table1 join_type join_table2 ON(join_condition)join_type.ON join_condition,.语法说明如下。join_table1、join_table2：参与连接操作的表名。join_type：连接类型，连接类型有INNER JOIN(内连接)、OUTER JOIN(外连接)和CROSS JOIN(交叉连接)。join_condition：连接条件，由被连接表中的列和比较运算符、逻辑运算符等构成。可以使用多组join_type ON join_condition 子句，实现多个表的连接。第25页/共39页268.3.3 使用INNER JOIN实现多个表的内连接1等值连接所谓等值连接，是指在连接条件中使用等于(=)运算符比较被连接的值，也就是通过相等的列值连接起来的查询。2不等连接所谓不等连接，就是在连接条件中使用除等号(=)外的其他比较运算符，构成非等值连接查询。可以使用的比较运算符包括：(大于)、=(大于等于)、=(小于等于)、(不等于)、!=(不等于)、LIKE、IN和BETWEEN等。第26页/共39页278.3.3 使用INNER JOIN实现多个表的内连接3自然连接自然连接(NATURAL JOIN)是在两个表中寻找列名和数据类型都相同的字段，通过相同的字段将两个表连接在一起，并返回所有符合条件的结果。4使用USING关键字简化连接SQL/92标准可以使用USING关键字来简化连接查询，但是只有在查询满足下面两个条件时，才能使用USING关键字进行简化：查询必须是等值连接。等值连接中的列必须具有相同的名称和数据类型。第27页/共39页288.3.3 使用INNER JOIN实现多个表的内连接【例8.25】使用USING关键字，重写例8.22中的语句，如下：参见教材P187使用USING关键字简化连接时，需要注意以下几点。(1)使用emp表和dept表中的deptno列进行连接时，在USING子句和SELECT子句中，都不能为deptno列指定表名或表别名。(2)如果在连接查询时使用了两个表中相同的多个列，那么就可以在USING子句中指定多个列名，形式如下：参见教材P187(3)如果对多个表进行检索，那么就必须多次使用USING关键字进行指定，形式如下：参见教材P187第28页/共39页298.3.4 使用OUTER JOIN实现多个表的外连接对于外连接，Oracle中可以使用加号(+)来表示，也可以使用LEFT、RIGHT和FULL OUTER JOIN关键字。外连接可以分为下面这3类：左外连接(LEFT OUTER JOIN或LEFT JOIN)右外连接(RIGHT OUTER JOIN或RIGHT JOIN)全外连接(FULL OUTER JOIN或FULL JOIN)第29页/共39页308.3.4 使用OUTER JOIN实现多个表的外连接1左外连接左外连接是在检索结果中除了显示满足连接条件的行外，还显示JOIN关键字左侧表中所有满足检索条件的行。2右外连接右外连接是在结果中除了显示满足连接条件的行外，还显示JOIN右侧表中所有满足检索条件的行。3全外连接全外连接是在结果中除了显示满足连接条件的行外，还显示JOIN两侧表中所有满足检索条件的行。第30页/共39页318.3.5 使用CROSS JOIN实现交叉连接使用CROSS JOIN关键字，可以实现两个表的交叉连接，所得到的结果将是这两个表中各行数据的所有组合，即这两个表所有数据行的笛卡儿积。交叉连接与简单连接操作非常相似，不同的是，使用交叉连接时，在FROM子句中多个表名之间不是用逗号，而是使用CROSS JOIN关键字隔开。另外，在交叉连接中不需要使用关键字ON限定连接条件，但是可以添加WHERE子句设置连接条件。【例8.29】使用交叉连接，查询emp表和dept表中，部门编号为10的员工信息和部门信息，如下：参见教材P190第31页/共39页328.3.6 使用UNION操作符获取两个结果集的并集使用UNION操作符的语法如下：select_statement UNION ALL select_statement UNION ALL select_statement .其中，select_statement是查询的SELECT语句；ALL选项表示将所有行合并到结果集中，不指定该项，则只保留重复行中的一行。UNION操作符含义如图8-1所示。第32页/共39页338.3.6 使用UNION操作符获取两个结果集的并集【例8.30】使用UNION ALL操作符，对scott用户的emp表进行操作，获得员工编号大于7800或者所在部门编号为10的员工信息。使用ORDER BY语句将结果集按照deptno列升序排列输出。具体如下：参见教材P191【例8.31】使用UNION操作符，但是不指定ALL关键字，获得员工编号大于7800或者所在部门编号为10的员工信息。具体如下：参见教材P191第33页/共39页348.3.7 使用INTERSECT操作符获取两个结果集的交集使用INTERSECT操作符，获取结果集的公共行，也称为获取结果集的交集。INTERSECT操作符的使用语法同UNION，只是操作符不再是UNION ALL，而是INTERSECT。INTERSECT操作符的含义如图8-2所示。第34页/共39页358.3.8 使用MINUS操作符获取两个结果集的差集SQL语言中的MINUS集合运算，表示获得给定集合之间的差异，也就意味着所得到的结果集中，其中的元素仅存在于前一个集合中，而不存在于另一个集合。MINUS操作符的含义如图8-3所示。第35页/共39页368.3.8 使用MINUS操作符获取两个结果集的差集【例8.33】使用MINUS操作符，获得员工编号大于7800但是所在部门编号不是10的员工信息。具体如下：参见教材P193【例8.34】使用UNION ALL和MINUS运算符，获得员工编号大于7800或者所在部门编号为10的员工中，工资大于等于2000的所有员工信息。具体如下：参见教材P193第36页/共39页378.4 实验指导各类图书的借阅情况实验指导8-2：各类图书的借阅情况具体实现时，需要从借书表lend中获得每本书的借阅情况，通过内连接(INNER JOIN)，将该表的bookid列与book表中的bookid相关联；通过右外连接(RIGHT JOIN)，将book表中的typeid列与type表中的typeid列相关联，获得每本书所属的类别。代码如下：SQL SELECT COUNT(b.bookid),t.typename 2 FROM lend l INNER JOIN book b ON l.bookid=b.bookid 3 RIGHT JOIN type t ON b.typeid=t.typeid 4 GROUP BY t.typename;COUNT(B.BOOKID)TYPENAME-30历史类 55计算机类 10地理类 39文学类 0哲学类第37页/共39页388.5 习 题参见教材P194第38页/共39页39感谢您的观看。第39页/共39页
