MySQL 子查询

4 子查询

#谁的工资比ABEL高
#方式一
SELECT salary
FROM employees
WHERE last_name='Abel';
#获得Abel的工资为11000
SELECT last_name,salary
FROM employees
WHERE salary>11000;
#方式二
SELECT e1.last_name,e2.salary
FROM employees e1,employees e2
WHERE e1.salary>e2.salary
AND e2.last_name = 'ABEL';
#方式三——子查询
SELECT last_name,salary
FROM employees
WHERE salary>(SELECT salary
              FROM employees
              WHERE last_name='Abel');

4.1 规定

• 外层的成为外查询（主查询）
• 内层的称为内查询（子查询）
• 子查询在主查询之前执行，结果被主查询使用
• 将子查询写在比较条件的右侧

4.2 子查询的分类

4.2.1 单行子查询和多行子查询

• 子查询分为单行子查询和多行子查询
• 子查询计算出一个结果由主查询使用，则成为单行子查询
• 子查询计算出多个结果由主查询使用，则成为多行子查询

4.2.1.1 单行子查询的比较符

• 基本只使用'>'、'>='、'='、'<'、'<='、'<>'

#查询工资大于149号员工的员工信息
SELECT employee_id,last_name,salary
FROM employees
WHERE salary>(SELECT salary
             FROM employees
             WHERE department_id =149);
#题目:返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资
SELECT last_name,job_id,salary
FROM employees
WHERE job_id =(SELECT job_id
              FROM employees
              WHERE empolyee_id = 141)
AND salary>(SELECT salary
              FROM employees
              WHERE empolyee_id = 143);

4.2.1.2 HAVING子查询

#查询最低工资大于50好部门最低工资的部门ID和其最低工资
SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(SELECT MIN(salary)
                   FROM employees
                   WHERE department_id =50
                   );

4.2.1.3 CASE子查询

#题目:显式员工的employee_id,last_name和location。其中，若员工department id与location_id为1800的department_id相同，则location为' canada'，其余则为'USA'。
SELECT employee_id,last_name,CASE department_id WHEN (SELECT department_id
                                                      FROM departments
                                                      WHERE location_id = 1800) 
THEN 'Canana'
ELSE 'USA' END
FROM employees

4.2.1.4 子查询空值问题

• 如果子查询结果是空值，则外查询也是控制

---

4.2.1.5 非法子查询

• Subquery returns more than 1 row
• 在单行查询中，子查询返回了超过1行的数据

4.2.1.6 多行子查询

• 多行子查询使用的操作符

操作符	含义
IN	等于列表中的任意一个
ANY	需要和单行比较操作符一起使用，和子查询返回的某一个值比较
ALL	需要和单行比较操作符一起使用，和子查询返回的所有值比较
SOME	实际上是ANY的别名，作用相同，一般常使用ANY

#题目:返回其它job id中比job id为'IT_PROG'部门任一工资低的员工的员工号、姓名、job_ id 以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <>'IT_PROG'
AND salary < ANY (SELECT salary
                 FROM employees
                 WHERE job_id ='IT_PROG');
               
#题目:返回其它job id中比job id为'IT_PROG'部门所有工资低的员工的员工号、姓名、job_ id 以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <>'IT_PROG'
AND salary < ALL (SELECT salary
                 FROM employees
                 WHERE job_id ='IT_PROG');
#查询平均工资最低的部门ID
#方法一
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 0,1;
#方法二
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) =(
						SELECT MIN(min_salary)
						FROM (
									SELECT department_id,AVG(salary) min_salary
									FROM employees
									GROUP BY department_id
									)	able_dept	
								);

#将子查询作为表返回时，必须给表起一个名字
#聚合函数不能嵌套聚合函数
#将聚合函数的结果作为一个表时，则可以进行嵌套。
#方法三
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL(
						SELECT department_id,AVG(salary) min_salary
						FROM employees
						GROUP BY department_id
						)	
#只要小于等于所有的平均值，则自身为最小。								

4.2.2 相关子查询和不相关子查询

• 子查询返回的结果不因主查询的内容而改变，则成为不相关子查询
• 需要将外查询的信息传入内查询

#相关子查询的需求：查询工资大于本部门平均工资的员工信息
SELECT last_name,department_id
FROM employees e1
WHERE salary> (SELECT AVG(salary)
               FROM employees e2
               WHERE e2department_id =e1.department_id
              )
#方法2
SELECT last_name,e1.department_id
FROM employees e1,(SELECT department_id,AVG(salary) avg_salary
                  FROM employees
                  GROUP BY department_id) table_department_avg_salary
WHERE e1.department_id =table_department_avg_salary.department_id
AND e1.salary>table_department_avg_salary.avg_salary

4.3 结论

• 出了GROUP BY和LIMIT中其他都可以声明子查询。