子查询
--子查询练习
/*
1、子查询(内查询)在主查询(外查询)之前一次执行完成
2、子查询的结果被主查询使用
3、在查询列表中使用子查询,只能是单行单列。见练习2
4、除非进行TOP N 分析,否则不要在子查询中使用ORDER BY语句,
因子查询效率较低,排序耗费资源。见练习3
*/
--练习1:查询所有工资大于CLARK的员工信息
--把子查询结果当做一个值使用
SELECT * FROM emp
WHERE sal > (SELECT sal FROM emp WHERE ename = 'CLARK')
--练习2:查询所有员工信息以及工资总和
--把子查询结果当做一个列使用
SELECT emp.*,(SELECT SUM(sal) FROM emp) 工资总和
FROM emp;
--练习3:查询工资最高TOP 5
--把子查询结果当做一个表使用
SELECT * FROM (SELECT * FROM emp ORDER BY sal DESC)
WHERE ROWNUM <= 5;
--练习4:查询员工表中第6到第12条数据
--利用子查询解决ROWNUM不能计算大于等于某个值的问题
SELECT * FROM (SELECT ROWNUM rnum,emp.* FROM emp)
WHERE rnum BETWEEN 6 AND 12;
--推荐写法(提前过滤)
SELECT * FROM (SELECT ROWNUM rnum, emp.* FROM emp WHERE ROWNUM<=12)
WHERE rnum >=6;
--多行子查询
--练习1:查询所有不是部门经理的员工
--IN 的使用
SELECT * FROM emp e
WHERE e.empno NOT IN (SELECT manager_id FROM DEPT
WHERE manager_id IS NOT NULL)
--练习2:查询所有员工人数不少于3人的部门信息
--子查询中可以使用子句
SELECT * FROM dept
WHERE deptno IN (SELECT deptno FROM emp
GROUP BY deptno
HAVING COUNT(*)>=3 )
--相关子查询(内外交互式)
--练习:查询员工编号,姓名,部门编号,工资,本部门工资总和
SELECT empno,ename,deptno,sal,
(SELECT SUM(sal) FROM emp
WHERE e.deptno = deptno) 部门工资总和
FROM emp e
ORDER BY deptno;
1.子查询
前面的系列介绍的都是简单的查询场景,其中都只涉及到单张表的数据检索。但是在日常是实际应用中,数据模型之间的关系都非常的复杂,数据的需求一般都是来源于多个数据模型之间的组合而成,即对应多张表的数据关联。
对应以上场景,在SQL中一般有三种实现的方式:
- 使用多个单条SQL,按逻辑步骤检索,将其中的一条检索结果作为下一条检索的条件;
- 使用子查询,即将多个单挑SQL利用相应的逻辑关键字合并,子查询是DBMS所支持;
- 使用表联结的方式,即join;
本章就简单的回顾下SQL中的子查询,从上面的总结中可以看出,子查询其实利用模型之间的关系将单条SQL合并成一条复杂的SQL。那么如果要写出这样的复杂子查询的SQL,首先需要梳理清楚需求中的数据模型之间的关系,根据需求的结果区分出查询主体,查询的关联关系体,然后再分成单步骤的SQL,最后将其合并即可;
下面看个例子:
有三个实体,对应三张表:
- 顾客表
- 商品表
- 订单表
订单中含有商品id,顾客id。现在需求:查询购买商品x1的所有顾客。
-
分析:最终的结果是要查询出顾客,所以查询柱体是顾客,即外查询是顾客。但是查询顾客的条件是,购买了x1商品的顾客,所以需要查出购买了x1商品的顾客id,然后根据顾客id查询顾客,所以子查询是根据商品id x1查询。
-
步骤:
-
在表orders中查询x1商品的顾客id: select cus_id from orders where mer_id = 'x1';
-
根据上述的查询结果作为条件,查询顾客:select * from customers where cus_id = 'xxxx';
最后将根据需求结果和实体的关联关系合并SQL:
select * from customers where customers.cus_id in (select cus_id from orders where mer_id = 'x1');
其实SQL中子查询有两种应用方式:
- 第一种也就上面的最常使用到的场景,将子查询的结果作为外查询的条件,即子查询属于外查询where子句的一部分
- 第二种常用是将子查询统计结果作为外查询的列
比如需求:统计每个顾客购订单数量。
-
分析:顾客仍然是主体,所以外查询是查顾客表。但是执行的逻辑刚好和上述例子相反,上述是以商品id为条件查询顾客。这里是查询顾客以及其订单数,但是订单数的统计是从顾客这一维度出发,所以需要根据顾客查询订单数。
-
步骤:
- 先查询出所有顾客: select * from customers;
- 再根据顾客去统计每个顾客的订单数: select count(*) from orders where orders.cus_id = 'xxxxx';
最后组合SQL:
select cus_id, cus_name, (select count(*) from orders where orders.cus_id = customers.cus_id) from customers;
以上例子是子查询的第二种用法。
2.总结
上述总结的子查询的两种方式:
- 要么是根据子查询的逻辑结果作为外查询的查询条件(子查询在where子句中)
- 要么是根据外查询的结果作为子查询的条件(子查询在select子句中)
SQL中没有限制子查询的数量,但是一般实际应用中子查询的不宜使用过多:
- 使用子查询必然会导致SQL更为复杂,SQL表述的语义较为难以理解,可阅读性变差
- 子查询使用过多,会严重消耗性能
- 子查询不利于SQL的调试,问题的排查