第九章_子查询

 # 第九章子查询
 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
);