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 );