子查询介绍：出现在其他语句中的select语句，被包裹的select语句就是子查询或查询
包裹子查询的外部的查询语句：称主查询语句
案例：查询在位置编号为1700的部门里的所有员工的名字
SELECT last_name FROM employees WHERE department_id
IN(SELECT department_id FROM departments
WHERE location_id=1700
);
1.子查询分类
通过位置来分：
SELECT 后面：仅仅支持量子查询
FROM 后面：支持表查询
WHERE 或having 后面: 支持标量子查询(重要) 列子查询(重要) 行子查询(用的较少)
EXISTS 后面(相关查询)：支持表子查询
按结果集的行列数不同的分类
标量子查询（结果集只有一行一列）
列子查询(结果集只有一列但有多行)
行子查询(结果集只有一行当有多列)
表子查询(结果集多行多列)
2.子查询特点
子查询放在小括号内
子查询一般放在条件但右侧
标量子查询，一般搭配着单行操作符来使用(> < >= <= <> =)
列子查询，一般搭配着多行操作符使用：in any/some all
子查询但执行顺序优先于主查询(SELECT后的子查询例外)
3.where后面的标量子查询
案例：查询工资比Abel这个人的高的员工信息
SELECT * FROM employees
WHERE salary >(
SELECT salary
FROM employees
WHERE last_name = 'Abel'
);
案例：查询job_id与141号员工相同，salary比143号员工多的员工姓名,job_id 和工资
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);
这个案例说明一个主查询里可以放很多个子查询
案例：子查询里用到分组查询函数：查询员工工资最少的员工的last_name,job_id和salary
SELECT last_name,job_id,salary FROM employees
WHERE salary = (SELECT min(salary) FROM employees );
案例：查询最低工资大于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.where后面的列子查询(行多子查询)
首先来看一下多行操作符：
in/not in:等于列表中的任意一个
a in(10,20,30); 可以替换 a=10 or a=20 or a=30
any/some:和子查询返回的某一个比较
a > any(10,20,30); 可以替换 a > min(10,20,30)
all:和子查询返回的所有值比较
a>all(10,20,30); 可以替换 a > max(10,20,30)
案例：返回location_id 是1400或1700的部门中的所有员工的名字
SELECT last_name FROM employees WHERE department_id
IN(SELECT department_id FROM departments WHERE location_id =1400 OR location_id=1700
);
案例：查询其他工种中比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为'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');
5.where后面的执行子查询(一行多列)
案例：查询员工编号最小且工资最高的员工信息
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);
6.SELECT 和FROM 后面的子查询
SELECT 后面(很少用到的,可以用前面讲的方法实现)：
案例：查询每个部门的部门信息和对应的员工个数(不用连接查询)
SELECT d.*,COUNT(e.employee_id) FROM departments d
LEFT JOIN employees e
ON d.department_id = e.department_id
GROUP BY d.department_id;
SELECT d.*,( SELECT COUNT(*) FROM employees e WHERE d.department_id =e.department_id )FROM departments d;
案例：查询员工号等于102的部门名(不用连接查询)
SELECT department_name FROM departments WHERE department_id =(SELECT e.department_id FROM employees e WHERE e.employee_id=102);
SELECT (SELECT d.department_name FROM departments d WHERE d.department_id = e.department_id ) FROM employees e WHERE e.employee_id=102;
案例：查询每个部门的平均工资等级
SELECT AVG(e.salary) FROM employees e GROUP BY e.department_id
(SELECT AVG(e.salary) avg_sal FROM employees e GROUP BY e.department_id) avg_sal_res 当成一个新的表
SELECT avg_sal_res.avg_sal,g.grade_level
FROM
(SELECT AVG(e.salary) avg_sal FROM employees e GROUP BY e.department_id) avg_sal_res
INNER JOIN job_grades g
on avg_sal BETWEEN g.lowest_sal AND g.highest_sal;
6.EXISTS后面(相关子查询)
exists的作用是：判断子查询有没有结果的存在
案例：
SELECT EXISTS(SELECT employee_id FROM employees);
SELECT EXISTS(SELECT employee_id FROM employees WHERE employee_id=99999);
案例：查询有员工的部门名,EXISTS 很少使用
SELECT EXISTS(SELECT employee_id FROM employees WHERE employee_id=99999);
SELECT department_name FROM departments d WHERE EXISTS(SELECT * FROM employees e WHERE e.department_id = d.department_id);
SELECT department_name FROM departments d WHERE department_id IN(SELECT e.department_id FROM employees e WHERE e.department_id = d.department_id);
注意
本文用到的myemployees.sql 文件在MySQL实战2 语法、筛选条件和函数 附件中
网友评论