子查询：

  一条查询语句中又嵌套了另一条完整的select语句，其中被嵌套的select语句，称为子查询或内查询，在外面的查询语句，称为主查询或外查询。

分类：

按子查询出现的位置：

    select后面：仅仅支持标量子查询。

    from后面：支持表子查询。

    where或having后面：标量子查询 (单行子查询)、列子查询 (多行子查询)、行子查询(多行多列)。

    exists后面(也称相关子查询)：表子查询。

按结果集的行列数不同：

    标量子查询（结果集只有一行一列）

    列子查询（结果集只有一列多行）

    行子查询（结果集有一行多列）

    表子查询（结果集一般为多行多列）

特点：

1：子查询都放在小括号内。

2：子查询可以放在from后面、select后面、where后面、having后面，但一般放在条件的右侧。

3：子查询优先于主查询执行，主查询使用了子查询的执行结果。

4：子查询根据查询结果的行数不同分为以下两类：

  ①：单行子查询：

    结果只有一行，一般搭配单行操作符使用：> < = >= <=

    非法使用子查询的情况：a：子查询的结果为一组值。

               b：子查询的结果为空。

  ②：多行子查询：

    结果集有多行，一般搭配多行操作符使用：any、all、in、not、in

    in：属于子查询结果中任意一个就行。

    any和all往往可以用其他查询。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
标量子查询
例1：谁的工资比Angel高。
①查询Angel的工资
SELECT salary
FROM employees
WHERE last_name = 'Angel';

②：查询员工的信息，满足salary>①的结果
SELECT *
FROM employees
WHERE salary>{
	SELECT salary
    FROM employees
    WHERE last_name = 'Angel'
};

2：返回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

③：查询员工的姓名，job_id和工资，要求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
};

3：返回公司工资最少的员工的last_name,job_id和salary
①：查询公司的最低工资
SELECT MIN(salary)
FROM employees

②：查询last_name,job_id和salary,要求salary=①
SELECT last_name,job_id,salary
FROM employees
WHERE salary={
	SELECT MIN(salary)
	FROM employees
};

4：查询最低工资大于50号部门最低工资的部门id和其最低工资
①：查询50号部门的最低工资
SELECT MIN(salary)
FROM employees
WHERE depertment_id = 50

②：查询每个部门的最低工资
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id

③：在②的基础上筛选，满足min(salary)>①
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>{
    SELECT MIN(salary)
    FROM employees
    WHERE department_id = 50
};


列子查询(多行子查询)：
例1：返回location_id是1400或1700的部门中的所有员工姓名
①：查询locati_id是1400或1700的部门编号
SELECT DISTINCT department_id
FROM depertments
WHERE location_id IN (1400,1700)

②：查询员工姓名，要求部门号是①列表中的某一个
SELECT last_name
FROM employees
WHERE department_id <>ALL{
    SELECT DISTINCT department_id
    FROM depertments
    WHERE location_id IN (1400,1700)
};

2：返回其它工种中比job_id为'IT_PROG'工种任一工资低的员工的员工号、姓名、job_id以及salary
①：查询job_id为'IT_PROG'部门任一工资
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'

②：查询员工号、姓名、job_id以及salary，salary<①中的任意一个
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ANY(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id = 'IT_PROG'

) AND job_id<>'IT_PROG';

#或
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<(
	SELECT MAX(salary)
	FROM employees
	WHERE job_id = 'IT_PROG'

) AND job_id<>'IT_PROG';

3：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工   的员工号、姓名、job_id 以及salary
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ALL(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id = 'IT_PROG'

) AND job_id<>'IT_PROG';

#或
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<(
	SELECT MIN( salary)
	FROM employees
	WHERE job_id = 'IT_PROG'

) AND job_id<>'IT_PROG';


行子查询（结果集一行多列或多行多列）
例1：查询员工编号最小并且工资最高的员工信息
SELECT * 
FROM employees
WHERE (employee_id,salary)=(
	SELECT MIN(employee_id),MAX(salary)
	FROM employees
);

①：查询最小的员工编号
SELECT MIN(employee_id)
FROM employees

#②查询最高工资
SELECT MAX(salary)
FROM employees

#③：查询员工信息
SELECT *
FROM employees
WHERE employee_id=(
	SELECT MIN(employee_id)
	FROM employees
)AND salary=(
	SELECT MAX(salary)
	FROM employees
);


select后面仅仅支持标量子查询
例1：查询每个部门的员工个数
SELECT d.*,(

	SELECT COUNT(*)
	FROM employees e
	WHERE e.department_id = d.`department_id`
 ) 个数
 FROM departments d;
 
 2：查询员工号=102的部门名
SELECT (
	SELECT department_name,e.department_id
	FROM departments d
	INNER JOIN employees e
	ON d.department_id=e.department_id
	WHERE e.employee_id=102
	
) 部门名;


from后面将子查询结果充当一张表，要求必须起别名
例1：查询每个部门的平均工资的工资等级
①：查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id

②：连接①的结果集和job_grades表，筛选条件平均工资 between lowest_sal and highest_sal
SELECT  ag_dep.*,g.`grade_level`
FROM (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;


exists后面（相关子查询）
语法：
exists(完整的查询语句)
结果：1或0
SELECT EXISTS(SELECT employee_id FROM employees WHERE salary=300000);
例1：查询有员工的部门名
#in
SELECT department_name
FROM departments d
WHERE d.`department_id` IN(
	SELECT department_id
	FROM employees
)

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

2：查询没有女朋友的男神信息
#in
SELECT bo.*
FROM boys bo
WHERE bo.id NOT IN(
	SELECT boyfriend_id
	FROM beauty
)

#exists
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
	SELECT boyfriend_id
	FROM beauty b
	WHERE bo.`id`=b.`boyfriend_id`
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
经典例题：
1：查询工资最低的员工信息: last_name, salary
①：查询最低的工资
SELECT MIN(salary)
FROM employees

②：查询
last_name,salary，要求salary=①
SELECT last_name,salary
FROM employees
WHERE salary=(
	SELECT MIN(salary)
	FROM employees
);

2：查询平均工资最低的部门信息

#方式一：
①：各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
②：查询①结果上的最低平均工资
SELECT MIN(ag)
FROM (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
) ag_dep

③：查询哪个部门的平均工资=②
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(
	SELECT MIN(ag)
	FROM (
		SELECT AVG(salary) ag,department_id
		FROM employees
		GROUP BY department_id
	) ag_dep

);

④：查询部门信息
SELECT d.*
FROM departments d
WHERE d.`department_id`=(
	SELECT department_id
	FROM employees
	GROUP BY department_id
	HAVING AVG(salary)=(
		SELECT MIN(ag)
		FROM (
			SELECT AVG(salary) ag,department_id
			FROM employees
			GROUP BY department_id
		) ag_dep

)

);

#方式二：
①：各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id

②：求出最低平均工资的部门编号
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) 
LIMIT 1;

③：查询部门信息
SELECT *
FROM departments
WHERE department_id=(
	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary) 
	LIMIT 1
);

3：查询平均工资最低的部门信息和该部门的平均工资
①：各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id

②：求出最低平均工资的部门编号
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) 
LIMIT 1;

③：查询部门信息
SELECT d.*,ag
FROM departments d
JOIN (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary) 
	LIMIT 1

) ag_dep
ON d.`department_id`=ag_dep.department_id;



4：查询平均工资最高的 job 信息
①：查询最高的job的平均工资
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 1

②：查询job信息
SELECT * 
FROM jobs
WHERE job_id=(
	SELECT job_id
	FROM employees
	GROUP BY job_id
	ORDER BY AVG(salary) DESC
	LIMIT 1

);

5：查询平均工资高于公司平均工资的部门有哪些?
①：查询平均工资
SELECT AVG(salary)
FROM employees

②：查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id

③：筛选②结果集，满足平均工资>①
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)>(
	SELECT AVG(salary)
	FROM employees

);

6：查询出公司中所有 manager 的详细信息.
①：查询所有manager的员工编号
SELECT DISTINCT manager_id
FROM employees

②查询详细信息，满足employee_id=①
SELECT *
FROM employees
WHERE employee_id =ANY(
	SELECT DISTINCT manager_id
	FROM employees

);

7：各个部门中 最高工资中最低的那个部门的 最低工资是多少
①：查询各部门的最高工资中最低的部门编号
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY MAX(salary)
LIMIT 1

②：查询①结果的那个部门的最低工资
SELECT MIN(salary) ,department_id
FROM employees
WHERE department_id=(
	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY MAX(salary)
	LIMIT 1
);

8：查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
①：查询平均工资最高的部门编号
SELECT 
    department_id 
FROM
    employees 
GROUP BY department_id 
ORDER BY AVG(salary) DESC 
LIMIT 1 

②：将employees和departments连接查询，筛选条件是①
    SELECT 
        last_name, d.department_id, email, salary 
    FROM
        employees e 
        INNER JOIN departments d 
            ON d.manager_id = e.employee_id 
    WHERE d.department_id = 
        (SELECT 
            department_id 
        FROM
            employees 
        GROUP BY department_id 
        ORDER BY AVG(salary) DESC 
        LIMIT 1) ;

2021-03-27