1、何为子查询
嵌套在其它语句内部的select语句,称为子查询或内查询,外面的语句可以是insert、update、delete、select等,一般select作为外面语句较多,外面如果为select语句,则此语句称为外查询或主查询
2、子查询的分类
按结果集的行列数不同分类
标量子查询
子查询的结果只有一行一列
注:标量子查询,一般搭配着单行操作符使用,常见的单行操作符有:>、<、<=、>=、<>、=
列子查询
子查询的结果只有一列多行
注:列子查询,一般搭配着多行操作符使用,常见的多行操作符有:in、any/some、all
行子查询
子查询的结果只有一行多列
表子查询
子查询的结果一般为多行多列
按子查询出现的位置分类
select后面
仅仅支持标量子查询
from后面
有查询结果就可以
注意事项:将子查询结果充当一张表,必须起别名
where或having后面:
支持标量子查询(重点)
支持列子查询(重点)
支持行子查询(很少用)
exists后面
有查询结果就可以
3、子查询的特点
1、子查询需要放在小括号内
2、子查询一般放在条件的右侧
3、子查询的执行优先于主查询
4、案例
放在where或者having后面
标量子查询案例
案例1:谁的工资比Abel高?
SELECT last_name,salary
FROM employees
WHERE salary>
(SELECT salary
FROM employees
WHERE last_name ='ABel'
);
案例2:返回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
)
案例3:返回公司工资最少的员工last_name,job_id和salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary = (SELECT MIN(salary)
FROM employees
)
案例4:查询最低工资大于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
)
列(多行)子查询案例
列子查询的特点:返回一列多行
多行比较符:
1、in|not in:判断是否属于in列表(常用)
2、any|some:用于和any列表中的任意一个值进行比较
3、all:用于和all列表中的所有值进行比较
案例1:返回location_id是1400或1700的部门中的所有员工姓名
SELECT last_name
FROM employees
WHERE department_id in(SELECT DISTINCT department_id
FROM departments
WHERE location_id in(1400,1700)
)
-- 注意对in列表中的值进行去重处理
案例2:查询出其它工种中,比员工的job_id为‘IT_PROG’的任意工资低的员工工号、姓名、job_id以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < ANY(SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'
)
AND job_id <> 'IT_PROG';
-- 注意对any列表中的值进行去重处理
-- 或者
SELECT employee_id,last_name,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 employee_id,last_name,job_id,salary
FROM employees
WHERE salary < ALL(SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'
)
AND job_id <> 'IT_PROG';
-- 注意对any列表中的值进行去重处理
-- 或者
SELECT employee_id,last_name,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后面
案例1:查询每个部门的员工数
SELECT d.*,(SELECT COUNT(*)
FROM employees e
WHERE d.department_id = e.department_id
) 个数
FROM departments d
-- select后面的子查询只能是 标量子查询,查询结果有n行,该标量子查询会被执行n次.
-- 此处标量子查询的功能:针对d表当前行的department_id从e表中筛选出和该department_id相等的记录,并统计这些记录的个数.
-- 或者
SELECT d.*,COUNT(e.department_id)
FROM departments d
LEFT JOIN employees e
on d.department_id = e.department_id
GROUP BY d.department_id
案例2:查询员工号为102的 部门名
-- 方法一:根据e表当前行的department_id在d表中寻找与该department_id相等的记录,并返回该记录的 department_name 等价于在e表的右侧新增部门名列
SELECT employee_id,(SELECT department_name
FROM departments d
WHERE e.department_id = d.department_id) 部门名
FROM employees e
WHERE employee_id = 102
-- 方法二
SELECT d.department_name
FROM employees e
LEFT JOIN departments d
on e.department_id = d.department_id
WHERE e.employee_id = 102
放在from后面
案例1:查询每个部门的平均工资的工资等级
SELECT g.grade_level,ag_dep.*
FROM job_grades g
INNER JOIN (SELECT department_id,AVG(salary) ag
FROM employees
GROUP BY department_id
) ag_dep
on ag_dep.ag BETWEEN g.lowest_sal and g.highest_sal
放在exists后面(相关子查询)
语法:exists(完整的查询语句)
返回:若查询语句有查询结果,则返回 1,否则返回 0
案例1:查询有员工的部门名
SELECT department_id,department_name
FROM departments d
WHERE EXISTS (SELECT *
FROM employees e
WHERE d.department_id = e.department_id
)
-- 执行顺序
-- 1.首先执行一次外部查询
-- 2.对于外部查询中的每一行分别执行一次子查询,根据d表当前行的department_id在e表中寻找与该department_id相等的记录,若存在这样的记录,子查询返回1,否则返回0
-- 3.通过where筛选出子查询结果为 1 的行.
-- 或
SELECT department_id,department_name
FROM departments
WHERE department_id in (SELECT DISTINCT department_id
FROM employees
)
5、子查询练习题
案例1:查询和zlotkey相同部门的员工姓名和工资
SELECT CONCAT(last_name,first_name),salary
FROM employees
WHERE department_id = (SELECT department_id
FROM employees
WHERE last_name = 'zlotkey'
);
案例2:查询工资比公司平均工资 高的员工的员工号,姓名和工资。
SELECT CONCAT(last_name,first_name),employee_id,salary
FROM employees
WHERE salary > (SELECT AVG(salary)
FROM employees
);
案例3:查询各部门中工资比本部门平均工资高的员工的员工号,姓名和工资
SELECT
last_name,
salary
FROM
employees e
WHERE
salary > ( SELECT AVG( salary ) FROM employees s GROUP BY department_id HAVING e.department_id = s.department_id );
-- 方法二
SELECT
last_name,
salary
FROM
employees e
INNER JOIN ( SELECT AVG( salary ) ag, department_id d FROM employees GROUP BY department_id ) ag_dep ON e.department_id = ag_dep.d
WHERE
salary > ag_dep.ag
案例4:查询姓名包含u的部门里所有员工的员工名和员工号
SELECT
last_name,
employee_id
FROM
employees
WHERE
department_id IN ( SELECT department_id FROM employees WHERE last_name LIKE '%u%' )
案例5:查询在部门的location_id为1700的部门工作的员工的员工号
SELECT
employee_id
FROM
employees
WHERE
department_id IN ( SELECT department_id FROM departments WHERE location_id = 1700 );
案例6:查询管理者是K_ing的员工姓名和工资
SELECT
last_name,
salary
FROM
employees
WHERE
manager_id IN ( SELECT employee_id FROM employees WHERE last_name = 'k_ing' );
案例7:查询工资最高的员工的姓名,要求first_name和1ast_name显示为一-列,列名为姓.名
SELECT
CONCAT( first_name, '.', last_name ) '姓.名'
FROM
employees
WHERE
salary = ( SELECT MAX( salary ) FROM employees );