子查询就是将一个查询的结果作为另一个查询的数据来源或判断条件的查询。

常见的子查询包括：WHERE子查询，FROM子查询，HAVING子查询，EXISTS子查询，子查询必须使用小括号括起来。
WHERE子查询
案例

查询出低于平均工资的雇员信息（子查询返回的结果集是单行单列）
SELECT * FROM emp WHERE sal<(SELECT AVG(sal) FROM emp);



查询出公司最早入职的雇员信息
SELECT * FROM emp WHERE hiredate=(SELECT MIN(hiredate) FROM emp);



查询出与SCOTT从事统一工作且工资相同的雇员信息

若子查询中返回的是单行多列的结果集

主查询中的条件判断字段的顺序要与子查询中需要查询的字段顺序保持一致
SELECT * FROM emp WHERE (job,sal)=
(SELECT job,sal FROM emp WHERE ename='SCOTT')
 AND ename<>'SCOTT'; 



查询出与经理职位工资相同的员工的工资

子查询中返回的结果集是多行单列
SELECT * FROM emp WHERE sal IN(SELECT sal FROM emp WHERE job='MANAGER')
AND job<>'MANAGER';

ANY 、ALL关键字

ANY表示范围内任意一个，ALL表示范围内全部

查询出薪资比经理的最高薪资低的员工信息(即小于任意一个经理的薪资)
SELECT * FROM emp WHERE sal<ANY(SELECT sal FROM emp WHERE job='MANAGER');



查询出比经理的最低薪资低的员工信息(即小于所有的经理的薪资)
SELECT * FROM emp WHERE sal<ALL(SELECT sal FROM emp WHERE job='MANAGER');



查询出薪资排名第三的雇员的信息
SELECT * FROM emp
 WHERE sal=(SELECT MAX(sal) FROM emp WHERE sal<(SELECT max(sal) FROM emp WHERE sal<(SELECT MAX(sal) FROM emp)));


HAVING子查询
HAVING子查询指的是分组过滤条件下的子查询，在HAVING子句中。

案例

查询出平均薪资高于所有员工平均薪资的职位的名称，以及职位的人数，以及这些职位的平均薪资
SELECT job,COUNT(*),AVG(sal) FROM emp
GROUP BY job HAVING AVG(sal)>
(SELECT AVG(sal) FROM emp);



查询出平均薪资最高的职位的名称和该职位的平均薪资
SELECT job,AVG(sal) FROM emp
GROUP BY job HAVING AVG(sal) = 
(SELECT MAX(AVG(sal)) FROM emp GROUP BY job);


FROM子查询
FROM子查询的数据来源是另一个查询的结果相当于一张临时数据表，

通常为子查询定义别名，如果要用子查询中的字段使用别名.字段名调用，

返回的一般是多行多列的结果集

案例

查询平均工资高于2000的职位名称以及该职位的平均工资
SELECT job,avgsal
 FROM (SELECT job,AVG(sal) AS avgsal FROM emp GROUP BY job)temp
 WHERE temp.avgsal>2000;



查询出有佣金并且薪资高于佣金的雇员的信息
SELECT * FROM (SELECT * FROM emp WHERE comm IS NOT NULL)
WHERE sal>comm;



查询各岗位的最高薪水、最低薪水，要求只统计薪水>1000的；
SELECT job,MAX(sal),MIN(sal)
 FROM (SELECT * FROM emp WHERE sal>1000)
 GROUP BY job;

查询各部门的平均薪水及部门编号，只统计薪水>2000的
SELECT AVG(sal),deptno
 FROM (SELECT * FROM emp WHERE sal>2000)
  GROUP BY deptno;



查询各部门的平均薪水及部门编号，要求只有员工姓名包含’A’的才参与统计，只列出平均薪水>1500的，按照平均薪水降序排列
SELECT AVG(sal) avgsal,deptno FROM
 (SELECT * FROM emp WHERE ename LIKE '%A%')
 GROUP BY deptno HAVING AVG(sal) > 1500 ORDER BY avgsal DESC;



查询最高薪水的员工信息
SELECT * FROM emp WHERE sal=(SELECT MAX(sal) FROM emp);



查询薪水大于所在部门平均薪水的员工信息
SELECT * FROM emp e,
 (SELECT deptno,AVG(sal) avgsal 
 FROM emp GROUP BY deptno)t     
 WHERE e.deptno=t.deptno AND sal>avgsal;



查询各部门最高薪水的员工信息
SELECT * FROM emp e,
(SELECT deptno,MAX(sal) maxsal FROM emp GROUP BY deptno) t 
 WHERE e.deptno=t.deptno AND e.sal=t.maxsal;


EXISTS子查询
EXISTS子查询的特征：将主查询中的数据带到子查询中验证，若验证成功则返回TRUE，验证失败返回FALSE；当主查询接收到TRUE时，该数据显示，若接收到FALSE，该数据不显示。

案例

查询出有部门的雇员信息
SELECT * FROM emp e WHERE EXISTS (SELECT * FROM dept d
 WHERE e.deptno=d.deptno);



查询出没有佣金的雇员的信息
SELECT * FROM emp e WHERE EXISTS(
 SELECT * FROM emp p WHERE e.comm=p.comm);

子查询：嵌套在其他查询中的查询。

有三张表分别如下：

customers: 存储顾客信息


orderitems：只存储订单信息，无客户信息


orders：存储订单号和顾客id
 

 

注意：一般在子查询中，程序先运行在嵌套在最内层的语句，再运行外层。因此在写子查询语句时，可以先测试下内层的子查询语句是否输出了想要的内容，再一层层往外测试，增加子查询正确率。否则多层的嵌套使语句可读性很低。

 

子查询训练：

训练1：

查询买了商品为’TNT2’的顾客信息

子查询中涉及3张表的订单。因此分三步，1. 在orderitems里找出TNT2的订单号；2. 在orders里找出第一步找出的订单号对应的客户id；3. 在customers中找出第二步对应客户的所有信息。

1. 在orderitems里找出TNT2的订单号

SELECT order_num FROM orderitems

WHERE prod_id='TNT2';



2. 在orders里找出第一步找出的订单号对应的客户id

SELECT cust_id FROM orders 

WHERE order_num 

IN (SELECT order_num FROM orderitems WHERE prod_id='TNT2');



3. 在customers中找出第二步对应客户的所有信息        

SELECT * FROM customers 

WHERE cust_id 

IN (SELECT cust_id FROM orders WHERE order_num IN 

(SELECT order_num FROM orderitems WHERE prod_id='TNT2'));



这样就得到结果啦~

子查询一般与IN操作符结合使用，也可用=><等。

 

另外，子查询还可以用于计算字段。

训练2：

如，想要查询每个客户的订单数

分步思考：1. 查询某个客户的订单数；2. 某个客户改为所有客户。

1. 查询某个客户的订单数

SELECT COUNT(*) AS orders1 FROM orders WHERE cust_id =10001;



2. 某个客户改为所有客户

SELECT cust_name, cust_state, 

(SELECT COUNT(*) FROM orders WHERE orders.cust_id=customers.cust_id) 

AS orders1 

FROM customers 

ORDER BY cust_name;



注意：当多个表都拥有同样的列名如cust_id时，一定要完全限定列名。

一个查询语句嵌套在另一个查询语句内部的查询

常用操作符：ANY（SOME）, ALL, IN, EXISTS

比较运算符：>, >=, <, <=, != 等

创建两个表，表明分别是tb1，tb2，并向其各插入了数据。

        

 

1.带ANY（SOME）关键字的子查询

这里any和some是同义词，都是表示满足任一条件。

例：查询tb1中的所有数据，只要大于tb2表中数据的任意值，既符合查询条件。


select num1 from tb1 where num1>ANY(select num2 from tb2);




 

2.带ALL关键字的子查询

例：查询tb1中的所有数据，返回其中比tb2表中任何值都大的数据。


select num1 from tb1 where num1>ALL(select num2 from tb2);




 

3.带EXISTS关键字的子查询



例1：查询fruits表中name字段下是否有apple，若有则查询fruits表中的记录，否则不查询。


select * from fruits where EXISTS(select name from fruits where name='apple');




例2：查询tb1表中是否存在num1=13的记录，若存在，则查询fruits表中price大于6.00的记录。


select * from fruits where price>6.00 and EXISTS (select num1 from tb1 where num1=13);




相反，可以使用 not exists 查询不存在的，用法相同。

 

4.带IN关键字的子查询

表示查询结果在后面子查询的范围之中

例如：查询tb1表中的num1字段下的数据作为数据列，fruits表中id字段下的数据与其进行比较，返回符合条件的记录。


select * from fruits where id IN (select num1 from tb1);




 

5.带比较运算符的子查询


select * from 表名 where 字段名 > (select ······)；


 

 

 

1:子查询(表子查询)

1:表子查询:子查询返回的结果是多行多列的二维表(子查询的返回的结果当做一个二维表来使用)

2:案例演示

需要:找出每个班最高的一个学生

1:确定数据源: 先将学生按照身高进行降序排序

select * from t_student order by height desc;

2:从每个班中选出第一个学生

select * from t_student group by c_id 每个班选出第一个学生

表子查询: from子查询,得到的结果作为from数据源

3:SQL语句

1:查询班级表

SELECT * FROM t_class;





2:查询学生表

SELECT * FROM t_student;





3;SELECT * FROM (SELECT * FROM t_student ORDER BY height DESC) AS student GROUP BY c_id;