1 #进阶7：子查询
2 /*
3 含义：
4 出现在其他语句中的select语句，称为子查询或内查询
5 外部的查询语句，称为主查询或外查询
6
7 分类：
8 按子查询出现的位置：
9 select后面：
10 仅仅支持标量子查询
11 from后面：
12 支持表子查询
13 where或having后面：（√）
14 标量子查询（√）
15 列子查询 （√）
16 行子查询
17 exists后面（相关子查询）：
18 表子查询
19 按结果集的行列数不同：
20 标量子查询（结果集只有一行一列）
21 列子查询（结果集有多行一列）
22 行子查询（结果集有一行多列）
23 表子查询（结果集一般为多行多列）
24 */
25
26 #一、where或having后面
27 /*
28 1.标量子查询（单行子查询）
29 2.列子查询（多行子查询）
30 3.行子查询（多列多行）
31
32 特点：
33 (1)子查询放在小括号内
34 (2)子查询一般放在条件的右侧
35 (3)标量子查询，一般搭配着单行操作符使用
36 > < >= <= <>
37
38 列子查询，一般搭配着多行操作符使用
39 in、any/some、all
40 (4)子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果
41 */
42
43 #1.标量子查询
44 #案例1：谁的工资比Abel高？
45 #(1)查询Abel的工资
46 SELECT salary
47 FROM employees
48 WHERE last_name='Abel';
49 #(2)查询员工的信息，满足salary>(1)的结果
50 SELECT *
51 FROM employees
52 WHERE salary>(
53 SELECT salary
54 FROM employees
55 WHERE last_name='Abel'
56 );
57 #案例2：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资
58 #(1)查询141号员工的job_id
59 SELECT job_id
60 FROM employees
61 WHERE employee_id='141'
62 #(2)查询143号员工的salary
63 SELECT salary
64 FROM employees
65 WHERE employee_id='143'
66 #(3)查询员工姓名，job_id和工资，要求job_id=(1)并且salary>(2)
67 SELECT last_name,job_id,salary
68 FROM employees WHERE job_id=(
69 SELECT job_id
70 FROM employees
71 WHERE employee_id='141'
72 )AND salary>(
73 SELECT salary
74 FROM employees
75 WHERE employee_id='143'
76 );
77 #案例3：返回公司工资最少的员工的last_name,job_id和salary
78 #(1)查询公司的最低工资
79 SELECT MIN(salary)
80 FROM employees
81 #(2)查询last_name,job_id和salary，要求salary=(1)
82 SELECT last_name,job_id,salary
83 FROM employees
84 WHERE salary=(
85 SELECT MIN(salary)
86 FROM employees
87 );
88 #案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资
89 #(1)查询50号部门的最低工资
90 SELECT MIN(salary)
91 FROM employees
92 WHERE department_id='50'
93 #(2)查询每个部门的最低工资
94 SELECT department_id,MIN(salary) '最低工资'
95 FROM employees
96 GROUP BY department_id
97 #(3)在(2)的基础上筛选，满足min(salary)>(1)
98 SELECT department_id,MIN(salary) '最低工资'
99 FROM employees
100 GROUP BY department_id
101 HAVING 最低工资>(
102 SELECT MIN(salary)
103 FROM employees
104 WHERE department_id='50'
105 );
106
107 #非法使用标量子查询——子查询的结果不是一行一列
108 SELECT department_id,MIN(salary) '最低工资'
109 FROM employees
110 GROUP BY department_id
111 HAVING 最低工资>(
112 SELECT salary
113 FROM employees
114 WHERE department_id='50'
115 );
116
117
118 #2.列子查询（多行子查询）
119 #案例1：返回location_id是1400或1700的部门中的所有员工姓名
120 #(1)查询location_id是1400或1700的部门编号
121 SELECT DISTINCT department_id
122 FROM departments
123 WHERE location_id IN(1400,1700)
124 #(2)查询员工姓名，要求部门号是(1)列表中的某一个
125 SELECT last_name
126 FROM employees
127 WHERE department_id IN(
128 SELECT DISTINCT department_id
129 FROM departments
130 WHERE location_id IN(1400,1700)
131 );
132 #或
133 SELECT last_name
134 FROM employees
135 WHERE department_id =ANY(
136 SELECT DISTINCT department_id
137 FROM departments
138 WHERE location_id IN(1400,1700)
139 );
140 #案例2：返回其他工种中比job_id为'IT_PROG'工种任一工资低的员工的
141 #员工号、姓名、job_id以及salary
142 #(1)查询job_id为'IT_PROG'工种任一工资
143 SELECT salary
144 FROM employees
145 WHERE job_id='IT_PROG'
146 #(2)查询员工号、姓名、job_id以及salary，salary<(1)中的任意一个
147 SELECT employee_id,last_name,job_id,salary
148 FROM employees
149 WHERE salary<ANY(
150 SELECT salary
151 FROM employees
152 WHERE job_id='IT_PROG'
153 )AND job_id <>'IT_PROG';
154 #或
155 SELECT employee_id,last_name,job_id,salary
156 FROM employees
157 WHERE salary<(
158 SELECT MAX(salary)
159 FROM employees
160 WHERE job_id='IT_PROG'
161 )AND job_id <>'IT_PROG';
162 #案例3：返回其他工种中比job_id为'IT_PROG'工种所有工资都低的员工的
163 #员工号、姓名、job_id以及salary
164 SELECT employee_id,last_name,job_id,salary
165 FROM employees
166 WHERE salary<ALL(
167 SELECT DISTINCT salary
168 FROM employees
169 WHERE job_id='IT_PROG'
170 )AND job_id <>'IT_PROG';
171 #或
172 SELECT employee_id,last_name,job_id,salary
173 FROM employees
174 WHERE salary<(
175 SELECT DISTINCT MIN(salary)
176 FROM employees
177 WHERE job_id='IT_PROG'
178 )AND job_id <>'IT_PROG';
179
180 #3.行子查询（结果集一行多列或多行多列）
181 #案例：查询员工编号最小并且工资最高的员工信息
182 #(1)查询最小的员工编号
183 SELECT MIN(employee_id)
184 FROM employees
185 #(2)查询最高工资
186 SELECT MAX(salary)
187 FROM employees
188 #(3)查询员工信息
189 SELECT * FROM employees
190 WHERE employee_id=(
191 SELECT MIN(employee_id)
192 FROM employees
193 ) AND salary=(
194 SELECT MAX(salary)
195 FROM employees
196 );
197 #或
198 SELECT * FROM employees
199 WHERE (employee_id,salary)=(
200 SELECT MIN(employee_id),MAX(salary)
201 FROM employees
202 );
203
204 #二、select后面
205 /*
206 仅仅支持标量子查询
207 */
208 #案例：查询每个部门的员工个数
209 SELECT d.*,(
210 SELECT COUNT(*)
211 FROM employees e
212 WHERE e.department_id=d.department_id
213 ) 个数
214 FROM departments d;
215 #案例2：查询员工号=102的部门名
216 SELECT(
217 SELECT department_name
218 FROM departments d
219 INNER JOIN employees e ON d.department_id=e.department_id
220 WHERE e.employee_id='102'
221 ) 部门名;
222
223 #三、from后面
224 /*
225 将子查询结果充当一张表，要求必须起别名
226 */
227 #案例：查询每个部门的平均工资的工资等级
228 #(1)查询每个部门的平均工资
229 SELECT AVG(salary),department_id
230 FROM employees
231 GROUP BY department_id
232 #(2)连接(1)的结果集和job_grades表，筛选条件平均工资
233 SELECT ag_dep.*,grade_level
234 FROM (
235 SELECT AVG(salary) ag,department_id
236 FROM employees
237 GROUP BY department_id
238 ) ag_dep
239 INNER JOIN job_grades ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;
240
241 #四、exists后面（相关子查询）
242 /*
243 语法：
244 exists(完整的查询语句)
245 结果：
246 1或0
247 */
248 SELECT EXISTS(SELECT employee_id FROM employees WHERE salary='30000');
249 #案例1：查询有员工的部门名
250 #in
251 SELECT department_name FROM departments
252 WHERE department_id IN(
253 SELECT department_id FROM employees
254 WHERE employee_id IS NOT NULL
255 );
256 #exists
257 SELECT department_name FROM departments d
258 WHERE EXISTS (
259 SELECT employee_id FROM employees e
260 WHERE d.department_id=e.department_id
261 );
262
263 #案例2：查询没有女朋友的男神信息
264 #in
265 SELECT * FROM boys
266 WHERE boys.id NOT IN (
267 SELECT boyfriend_id FROM beauty
268 );
269 #exists
270 SELECT * FROM boys bo
271 WHERE NOT EXISTS(
272 SELECT boyfriend_id FROM beauty b
273 WHERE bo.id=b.boyfriend_id
274 );
