第09章_子查询

#第09章_子查询

#需求:谁的工资比Abel的高?
#方式1:
SELECT last_name,salary
FROM employees
WHERE last_name = 'Abel';

SELECT last_name,salary
FROM employees
WHERE salary > 10000;

#方式2:自连接
SELECT e2.last_name,e2.salary
FROM employees e1, employees e2
WHERE e1.salary < e2.salary#多表的连接条件
AND e1.last_name = 'Abel';

#3.子查询:引入子查询:

SELECT last_name,salary
FROM employees
WHERE salary > (
		SELECT salary
		FROM employees
		WHERE last_name = 'Abel'
		);

#2.称谓的规范:外查询(主查询) ,内查询(子查询)

/*
子查询（内查询）在主查询之前一次执行完成。
子查询的结果被主查询（外查询）使用。

注意事项:
子查询要包含在括号内
将子查询放在比较条件的右侧
单行操作符对应单行子查询，多行操作符对应多行子查询
*/


/*
3.子查询的分类:

角度1:单行子查询(子查询返回一个结果供外查询使用)vs多行子查询(子查询返回多个结果供外查询使用)

角度2:内查询是否被执行多次:
相关子查询  vs  不相关子查询
  

*/
#4.单行子查询：
#4.1单行操作符：=  > >= < <= <>

#子查询编写步骤：从里往外 或者 从外往里写
SELECT last_name, salary
FROM employees
WHERE salary > (
		SELECT salary
		FROM employees
		WHERE employee_id = 149
		);
		
SELECT last_name,job_id, salary
FROM employees
WHERE salary > (
		SELECT salary
		FROM employees
		WHERE employee_id = 143
		) AND job_id = (
		SELECT job_id
		FROM employees
		WHERE employee_id = 141
		);

SELECT last_name,job_id, salary
FROM employees
WHERE salary = (
		SELECT MIN(salary)
		FROM employees
		);

SELECT employee_id,manager_id,department_id
FROM employees
WHERE manager_id = (
		SELECT manager_id
		FROM employees
		WHERE employee_id = 141
		) 
AND department_id = (
		SELECT department_id
		FROM employees
		WHERE employee_id = 141
		)
AND employee_id <> 141;#不等于，排除了141号本身！ 

#5.多行子查询：
#5.1操作符 IN ANY ALL SOME(ANY)

#IN

#ANY ALL
#题目：返回其它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
#在MySQL中聚合函数不能嵌套。
#方式一：
SELECT MIN(avg_sal)
FROM(
	SELECT AVG(salary) avg_sal
	FROM employees
	GROUP BY department_id
	)

#方式二：
SELECT department_id
FROM employees
GROUP BY department_id
HAVING department_id IS NOT NULL
AND AVG(salary) <= ALL(
		SELECT AVG(salary)
		FROM employees
		GROUP BY department_id
		HAVING department_id IS NOT NULL
		);

#5.3空值问题：

#6 相关子查询：

#方式1：
SELECT last_name ,salary,department_id
FROM employees e1
WHERE  e1.salary > (
		SELECT AVG(salary)
		FROM employees e2
		GROUP BY department_id
		HAVING department_id = e1.department_id
		);
#方式二：在from中声明子查询：
SELECT e1.last_name ,e1.salary,e1.department_id
FROM employees e1,(
	SELECT AVG(salary) avg_sal,department_id
	FROM employees 
	GROUP BY department_id
	) avg_sal_dep
WHERE e1.department_id = avg_sal_dep.department_id
AND e1.salary > avg_sal_dep.avg_sal 
		
#题目：查询员工的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
	)

#结论：在SELECT 中除了GROUP BY和LIMIT都可以写子查询。
#sql99语法:
/*

SELECT ...字段1,...字段2,...(存在聚合函数)
FROM ...(LEFT / RIGHT)JOIN...ON 多表的连接条件
	(LEFT / RIGHT)JOIN...ON 多表的连接条件
WHERE 不包含聚合函数的过滤条件
GROUP BY ...,...
HAVING 包含聚合函数的过滤条件
ORDER BY ...,...(ASC,DESC)
LIMIT ...(分页操作)

*/
 
#题目：若employees表中employee_id与job_history表中employee_id相同的数目不小于2，
#输出这些相同id的员工的employee_id,last_name和其job_id

SELECT *
FROM employees

SELECT *
FROM job_history

SELECT employee_id,last_name,job_id
FROM employees e
WHERE 2 <= (
	SELECT COUNT(*)
	FROM job_history j
	WHERE e.employee_id = j.employee_id
		)


#EXISTS NOT EXISTS关键字
#题目：查询公司管理者的employee_id，last_name，job_id，department_id信息
#方式1：
SELECT DISTINCT mgr.employee_id,mgr.last_name,mgr.job_id,mgr.department_id
FROM employees emp JOIN employees mgr
ON emp.manager_id = mgr.employee_id

#方式2：
SELECT employee_id,last_name,job_id,department_id
FROM employees e 
WHERE employee_id IN (
			SELECT DISTINCT manager_id
			FROM employees
			);

#方式3：使用EXIST关键字 
SELECT employee_id,last_name,job_id,department_id
FROM employees e1 
WHERE  EXISTS(
		SELECT *
		FROM employees e2
		WHERE e1.`employee_id` = e2.`manager_id`
		);

#题目：查询departments表中，不存在于employees表中的部门的department_id和department_name


#方式1：
SELECT d.department_id, d.department_name 
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.`department_id` IS NULL;


#方式2：
SELECT department_id,department_name
FROM departments d
WHERE NOT EXISTS(
		SELECT *
		FROM employees e
		WHERE d.department_id = e.department_id
		); 

SELECT employee_id,last_name
FROM employees
WHERE department_id IN (
			SELECT DISTINCT department_id
			FROM employees
			WHERE last_name LIKE '%u%'
			);


#