MySQL（子查询）

子查询

#1.查询平均工资最低的部门

mysql中聚合函数是不能嵌套使用的

所以，这样的代码是错误的，无法执行

SELECT MIN(AVG(salary))
FROM employees
GROUP BY department_id

• 正确的查询如下： 

先查询到最低的部门平均工资

• 方法：在FROM中声明子查询（查询的部门平均工资生成新的表，然后再select ...from（新生成的表），即可对新生成的表进行查询） 

SELECT MIN(avg_salary)
FROM(
SELECT AVG(salary) avg_salary #需要给AVG(salary)字段生成别名，因为不能在MIN里面再套AVG
FROM employees
GROUP BY department_id
    ) dep_avg_salary   #新生成的表的别名

再接着，查询出平均工资最低的部门

 在HAVING中使用子查询

• 方法一：

SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(
        SELECT MIN(avg_salary)
        FROM(
        SELECT AVG(salary) avg_salary
        FROM employees
        GROUP BY department_id
            ) dep_avg_salary
		           )

•  方法二：用ALL关键字，就不用找出最低的部门平均工资。

SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)<=ALL(
			SELECT AVG(salary)
			FROM employees
			GROUP BY department_id
			)

 相关子查询

#2.查询员工中工资大于所在部门平均工资的员工的last_name,salary和其department_id

• 方法一：使用相关子查询,用WHERE关键字

SELECT last_name,department_id,salary
FROM employees e1
WHERE salary>(
SELECT AVG(salary)
FROM employees e2
WHERE e2.`department_id`=e1.`department_id`
)

•  方法二：在FROM中声明子查询

from型的子查询，子查询是作为from的一部分，子查询要用（）引起来，并且要给这个子查询取别名，把它当成一张“临时的虚拟的表”来使用

(由于FROM后面有两个表，里面生成的那个新表也含有字段department_id，所以在查询的时候要声明一下departemnt_id是查询的哪个表里面的)

SELECT last_name,e1.department_id,salary
FROM employees e1,(
SELECT AVG(salary) avg_sal,department_id
FROM employees
GROUP BY department_id)e2
WHERE e1.`department_id`=e2.`department_id`
AND e1.`salary`>e2.`avg_sal`

 #3.查询员工的id，salary，按照depertment_name排序（department_name在departments表中）

在ODER BY后面跟子查询

SELECT e.employee_id,e.salary,c.department_name
FROM employees e,departments c
WHERE e.`department_id`=c.`department_id`
ORDER BY(
SELECT department_name
FROM departments d
WHERE e.`department_id`=d.`department_id`
)ASC

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

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

#5.查询公司管理者的employee_id，和last_name信息 

• 方法一：WHERE关键字过滤

SELECT DISTINCT mgr.employee_id,mgr.last_name
FROM employees emp,employees mgr
WHERE emp.`manager_id`=mgr.`employee_id`

• 方法二：自连接

SELECT DISTINCT mgr.employee_id,mgr.last_name
FROM employees emp JOIN employees mgr
ON emp.`manager_id`=mgr.`employee_id`

• 方法三：子查询

SELECT employee_id,last_name
FROM employees
WHERE employee_id IN(
SELECT DISTINCT manager_id
FROM employees
)

• 方法四：EXISTS关键字

SELECT mgr.employee_id,mgr.last_name
FROM employees e1
WHERE EXISTS(
SELECT *#这块查询什么其实不重要，只要查询得到那么就返回TRUE
FROM employees e2
WHERE e1.`employee_id`=e2.`manager_id`
)

#6.查询departments表中，不存在于employees表中的部门的department_id和department_name

• 方法一：外连接 

SELECT d.department_id,d.department_name
FROM departments d LEFT JOIN employees e
ON d.`department_id`=e.`department_id`
WHERE e.`department_id` IS NULL

• 方法二：NOT EXISTS关键字

SELECT department_id,department_name
FROM departments d
WHERE NOT EXISTS(
SELECT *
FROM employees e
WHERE d.`department_id`=e.`department_id`
#如果存在这样的记录那么就返回false,不要这条记录，如果不存在这样的记录那么就返回TRUE,这条记录就是要查询的记录
);