# 第九章子查询
SELECT last_name,salary
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE last_name='Abel'
);
# 称谓的规范:外查询,内查询
/*子查询(内查询)在主查询之前一次执行完成。
子查询的结果被主查询(外查询)使用 。
注意事项
子查询要包含在括号内
将子查询放在比较条件的右侧
单行操作符对应单行子查询,多行操作符对应多行子查询
*/
# 3:子查询的分类
-- 列表1
-- 从内查询返回的结果的条目数
-- 单行子查询 VS 多行子查询
-- 角度2: 内查询是否被执行多次
-- 相关(或关联)子查询 和 不相关(或非关联)子查询 。
-- 题目:查询工资大于149号员工工资的员工的信息
# 子查询的编写技巧 1.从里往外写 2.从外往里写
SELECT employee_id,last_name,salary
FROM employees
WHERE salary >(
SELECT salary
FROM employees
WHERE employee_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 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
)
-- 5:多行子查询
-- 也称为集合比较子查询
-- 内查询返回多行
-- 使用多行比较操作符
SELECT employee_id ,last_name
FROM employees
WHERE salary IN(
SELECT MIN(salary)
FROM employees
GROUP BY department_id
);
-- _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'
)
# 相关子查询
-- 题目:查询员工中工资大于公司平均工资的员工的last_name,salary和其department_id
SELECT last_name,salary,department_id
FROM employees
WHERE salary>(
SELECT AVG(salary)
FROM employees
)
-- 题目:查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
-- 方式一
SELECT last_name,salary,department_id
FROM employees e1
WHERE salary>(
SELECT AVG(salary)
FROM employees e2
WHERE department_id=e1.`department_id`
)
-- 方式二 在from中声明子查询
SELECT e.last_name,salary,department_id
FROM employees e,(
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id
)t_dept
WHERE e.department_id=t_dept.department_id
AND e.salary>t_dept.
-- 在ORDER BY 中使用子查询:
-- 题目:查询员工的id,salary,按照department_name 排序
SELECT employee_id,salary
FROM employees e
ORDER BY (
SELECT department_name
FROM departments d
WHERE e.`department_id` = d.`department_id`
);
-- EXISTS 与 NOT EXISTS关键字
-- 题目:查询公司管理者的employee_id,last_name,job_id,department_id信息
-- 方式一:自连接
SELECT DISTINCT mgr.employee_id,mgr.last_name,mgr.job_id,mgr.department_id
FROM wmployees emp JOIN wmployees mgr
ON emp.manger_id=mgr.employee_id
# 方式二; 子查询
-- 方式三 使用 exists
SELECT employees_id,last_name,job_id,department_id
FROM employees e1
WHERE EXISTS (
SELECT *
FROM employees e2
WHERE e1. employee_id=e2.manger_id
);
第九章_子查询
最新推荐文章于 2024-08-23 17:37:15 发布