子查询又叫嵌套查询，总结一下子查询的各种操作和处理，以便于记忆和熟练的运用。

概念：什么是子查询？

子查询就是将一个查询语句嵌套在另一个查询语句中，内层语句的查询结果，可以作为外层查询语句的条件，本文重点讲解的是放在where或者having关键词之后的情况，当然不仅限这种情形。

子查询分为一下三种情况（这里只总结运用较多的三种）

一、列子查询（多行子查询，即子查询的结果集是多行单列），位置放在where和having之后。

包含一下几种常见的情形：

1、使用【not】in 的子查询

2、使用比较运算符的子查询（=、> 、 >=、 <=、 <>、 !=、 <=>）

3、使用[not] exists 的子查询

4、使用any  some  或者 all 的子查询，具体如右图：

(仅示例)

select id ,username from student where score >=any(select level from scholarship);大于等于任意一个，即取最小值

select id ,username from student where score >=some(select level from scholarship)同上

select id ,username from student where score >=all(select level from scholarship)大于等于所有的，就是大于等于里面最大的那个


其他情况参考这个示例

二、标量子查询

标量子查询的子结果集是单行单列，并且子查询条件一般放在where或者having的后面，要注意放在（）内，且不加“；”。

举例说明一下：

select * from employees 

where salary >(

    select  salary  from employees where 
    last_name='Abel'

);

select department_id ,min(salary) from employees 

group by department_id 

having min(salary) >( 

select min(salay)

from employees where department_id =50
);

 三、行子查询（结果集是一行多列或者多行多列，用法较少，不做推荐）

举例说明：

select * from employees 

where (employee_id ,salary )=(

select min(employee_id),max(salary)

from employees

);


当然用之前的方法也是可以解决的：

select * from empioyees

where employee_id=(

select min(employee_id) from employees

)

and 

salary=(

select max(salary) from employees

);

当然子查询还有一些其他的用法，比如下面的在创建表格时的运用。

将查询结果写入到数据表格中

insert table  [column_name,.......] select column_name,....... from table;

创建数据表的同时将查询结果写入到数据表中

create table if not exists table_name [create_definition,......] select_statement

ps:这里需要注意的是，新建表格的字段要与查询的字段保持一致，才可以按照对应的字段生成数据，如果不一致，会在原来的基础上生成新的字段。

create table test2 (id tinyint unsigned auto_increment key ,num tinyint unsigned ) select id ,level from scholarship ;

+------+----+-------+
| num  | id | level |
+------+----+-------+
| NULL |  1 |    90 |
| NULL |  2 |    80 |
| NULL |  3 |    70 |
+------+----+-------+

这样就多出了一个level字段或者说num字段没有用上，如果我们把num字段改成level就能生成合适的表格内容了。
+----+-------+
| id | level |
+----+-------+
|  1 |    90 |
|  2 |    80 |
|  3 |    70 |
+----+-------+

另外还有一些子查询语句是放在select下面的，举一个例子说明一下：

select d.*,(

select count(*)

from employees e 

where e.department=d.department

)

from departments d;


select  (

select department_name from departments d

join employees e

on d.department_id =e.department_id

) from departments d;

还有一部分是放在from后面的，一般from后面跟着的是表格，这里相当于把子查询的结果集当成是表格来处理，不再做示例了，有兴趣的可以百度一下。

一、子查询含义

出现在其他语句中的select语句，称为子查询或内查询。

二、子查询分类

按子查询的位置分

按结果集的行列数不同分

三、WHERE后面的子查询

1. 标量子查询

tips: 单行操作符（> < =等）只能搭配标量子查询

查询工资比Abel高的所有员工信息

①查询Abel的工资

SELECT 	salary
FROM 	employees
WHERE 	last_name = 'Abel';

结果为单行单列：

②查询员工的信息，满足salary>①

查询公司工资最少的员工的last_name, job_id, salary：
①查询MIN(salary)

SELECT MIN(salary)
FROM employees;

②查询符号这项工资的员工，salary=①

SELECT last_name, job_id, salary
FROM employees
WHERE salary=(
	SELECT MIN(salary)
	FROM employees
);

查询job_id与141号员工相同，salary比143号员工多的员工的姓名，job_id和工资
①141号员工的job_id

SELECT job_id
FROM employees
WHERE employee_id=141;

②143号员工的salary

SELECT salary
FROM employees
WHERE employee_id=143;

③ 结果集映射到last_name, job_id, salary，且满足条件 job_id=①和salary>②

SELECT last_name, job_id, salary
FROM employees
WHERE job_id=(
	SELECT job_id
	FROM employees
	WHERE employee_id=141
)AND salary>(
	SELECT salary
	FROM employees
	WHERE employee_id=143
);

2. 列子查询（多行子查询）

使用多行操作符：

• IN/NOT IN
  • IN()等价于=ANY() NOT IN()等价于<>ALL()
• ANY/SOME
• ALL

返回location_id是1400或1700的部门中的所有员工姓名

使用联表查询操作得到结果：

SELECT last_name
FROM departments d INNER JOIN employees e
	ON d.department_id = e.department_id
WHERE d.location_id IN (1400, 1700);

使用列子查询得到结果：
①查询location_id是1400或1700的所有部门的编号

SELECT department_id
FROM departments
WHERE location_id IN (1400, 1700);

②查询满足部门编号在列表内的员工的姓名

SELECT last_name
FROM employees
WHERE department_id IN(
	SELECT department_id
	FROM departments
	WHERE location_id IN (1400, 1700)
);

查询其他工种中的
比job_id为IT_PROG工种的任一员工工资低的
员工的：工号，姓名

①获取IT_PROG工种所有工资

SELECT DISTINCT salary
FROM employees
WHERE job_id='IT_PROG'

②查询其他部门中符合条件salary<min(①)的员工信息

SELECT job_id, last_name
FROM employees
WHERE salary < ANY(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id='IT_PROG'
) AND job_id!='IT_PROG';

3. 行子查询（结果为一行多列或多行多列）

查询员工编号最小而且工资最高的员工信息
①查询最小的员工编号

SELECT MIN(employee_id)
FROM employees;

②查询员工的最高工资

SELECT MAX(salary)
FROM employees;

③查询符合employee_id=①，salary=②的员工

SELECT *
FROM employees
WHERE employee_id=(
	SELECT MIN(employee_id)
	FROM employees
)AND salary=(
	SELECT MAX(salary)
	FROM employees
);

使用行子查询的等价写法：

SELECT *
FROM employees
WHERE (employee_id, salary) = (
	SELECT MIN(employee_id), MAX(salary)
	FROM employees
);

四 、SELECT后面的子查询

查询每个部门的员工个数

使用联表查询操作：

SELECT department_name 部门名, COUNT(1) 员工数量
FROM employees e RIGHT JOIN departments d
	ON e.department_id = d.department_id
GROUP BY department_name

使用子查询：

SELECT department_id, department_name,(
	SELECT COUNT(1)
	FROM employees e
	WHERE e.department_id=d.department_id
) 对应员工数
FROM departments d;

在使用子查询中，外部的表名的别名可以传递到子查询中，例如上面的departments 表的别名d就传递到了子查询中。

该子查询可以理解为：

1. 先获取外层查询的结果集
2. 然后对结果集的每条记录进行遍历，将对应的参数填入到子查询中得到单条记录的结果拼接到新列中

五、FROM后面的子查询

查询每个部门的平均工资的工资等级

①查询每个部门的平均工资

SELECT AVG(salary) ag,department_id
FROM employees e
WHERE e.department_id IS NOT NULL
GROUP BY department_id;

②查询对应的工资等级,连接①的结果集和job_grades表

SELECT department_id, ag, grade_level
FROM (
	SELECT AVG(salary) ag,department_id
	FROM employees e
	WHERE e.department_id IS NOT NULL
	GROUP BY department_id
) ag_dep INNER JOIN job_grades j
	ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

即可以将子查询的结果集作为一张表用于联表查询操作

六、EXISTS后面的子查询

EXISTS语句的作用

EXISTS语句后的括号中填入一个子查询语句，返回的结果为false(0)或true(1)，分别代表查询结果是否为空。

例：

SELECT EXISTS(SELECT employee_id FROM employees);
SELECT EXISTS(SELECT employee_id FROM employees WHERE salary=30000);

简单应用

查询有员工的部门名：

SELECT department_name
FROM departments d
WHERE EXISTS(
	SELECT * 
	FROM employees e
	WHERE e.department_id = d.department_id
);

这里等价于用IN

SELECT department_name
FROM departments d 
WHERE d.department_id IN(
	SELECT DISTINCT department_id
	FROM employees
);

查询没有女票的男神信息

使用EXISTS语句

1. 查询男神信息
2. 筛选条件：他没有女票
   等价于beauty 表中没有boyfriend_id为当前男生的记录
   等价于beauty 表中boyfriend_id为当前男生的id的记录不存在

SELECT *
FROM boys bo
WHERE NOT EXISTS(
	SELECT *
	FROM beauty b
	WHERE bo.id=b.boyfriend_id
);

使用IN完成相同的任务：

1. 查询男神信息
2. 筛选条件：他没有女票
   等价于当前男生的id不在beauty 表中boyfriend_id字段的集合中

SELECT * 
FROM boys b
WHERE b.id NOT IN(
	SELECT boyfriend_id
	FROM beauty
);

子查询

在SQL语句中，一个select-from-where语句称为一个查询块。如果一个select语句嵌套在一个select，insert，update或delete语句中，则称之为子查询或者内存查询。包含子查询的语句则称为主查询或者外层查询
子查询语句一般出现在where和having子句中
写在where中的子句中的子查询一般有下面的形式

• WHERE <列名> [NOT] IN (子查询) // (集合
• WHERE <列名> 比较运算符 (子查询) //(单值
• WHERE EXISTS (子查询) //(是否空值
  例如：
  使用子查询进行基于集合的查询

//查询与钟文辉同学在同一个系的同学
//第一步先查询钟文辉同学所在的系
//然后把系作为已知条件查找同学信息
SELECT * FROM Student
WHERE Sdept IN(
SELECT Sdept FROM Student Where Sname ='钟文辉')	;

使用子查询进行比较查询

//查询选修了C04课程且课程成绩高于此课程平均成绩的的学生学号和该门课成绩
//先查出C04课程的平均成绩
//然后把平均成绩作为已知条件查找同学学号和成绩
SELECT Sno, Grade FROM SC
WHERECno='C04' AND Grade>(
SELECT AVG(Grade) FROM SC Where Cno ='C04')	;

带有ANY和ALL的子查询

//查询比C03课程成绩都高的选了C04课程的学生的学号和成绩
SELECT Sno, Grade FROM SC
WHERECno='C04' AND Grade>ALL(
SELECT  Grade  FROM SC Where Cno ='C03')	;

使用带EXSITS谓词的子查询

//查询选修了C04课程且课程的学生姓名
SELECT Sname FROM SC
WHERE 	EXISTS(
SELECT * FROM SC
Where SC.Sno=Student.Sno  AND  Cno ='C04');

注意，带exists谓词的子查询是先执行外层，再执行内层查询，根据内存查询返回的true和false来判断外层数据是否满足条件。

查询的集合运算

SQL提供与关系代数中集合并，交，差对应的谓词，他们分别是UNION，INTERSECT，EXCEPT，当使用这些操作进行查询时，参与运算的两个查询分别用括号括起来。
例如：

//查询计算机系和机电系的所有学生信息
（ SELECT *FROM Student 
WHERE Sdept='计算机系'）
UNION
（ SELECT *FROM Student 
WHERE Sdept='机电系'）
//查询同时选修了C03和C04课程的学生的学号
(SELECT Sno FROM SC WHERE Cno='C03')
INTERSECT
(SELECT Sno FROM SC WHERE Cno='C04')
//查询选修了C01但是没有选修C02课程的学生的学号
(SELECT Sno FROM SC WHERE Cno='C01')
EXCEPT
(SELECT Sno FROM SC WHERE Cno='C02')