持续创作，加速成长！这是我参与「掘金日新计划 · 6 月更文挑战」的第17天，点击查看活动详情

前言

上篇我们就MySQL的子查询做了一些练习。有兴趣的小伙伴可以阅读(# MySQL学习-子查询练习（二））。
下面就MySQL的子查询继续做一些练习。

练习十三

查询各个部门中最高工资中最低的那个部门的最低工资是多少。
分析：

先查出各个部门的最高工资。
然后查出这些工资中最低工资的部门。
最后查出该部门的最低工资是多少。

方式一：

SELECT MIN(salary)
FROM employees
WHERE department_id = (
                        SELECT department_id
                        FROM employees
                        GROUP BY department_id
                        HAVING MAX(salary) = (
                                                SELECT MIN(max_sal)
                                                FROM (
                                                        SELECT MAX(salary) max_sal 
                                                        FROM employees
                                                        GROUP BY department_id
                                                        ) t_dept_max_sal
                                                  )
                        );

方式二： 使用ALL查询。

SELECT MIN(salary)
FROM employees
WHERE department_id = (
                        SELECT department_id
                        FROM employees
                        GROUP BY department_id
                        HAVING MAX(salary) <= ALL (
                                                    SELECT MAX(salary)
                                                    FROM employees
                                                    GROUP BY department_id  
                                                  )
                        );

方式三： 使用ORDER BY，LIMIT的方式查出结果。

SELECT MIN(salary)
FROM employees
WHERE department_id = (
                        SELECT department_id
                        FROM employees
                        GROUP BY department_id
                        HAVING MAX(salary) = (
                                                SELECT MAX(salary) max_sal
                                                FROM employees
                                                GROUP BY department_id  
                                                ORDER BY max_sal ASC 
                                                LIMIT 0,1
                                              )
                        );

方式四：

SELECT MIN(salary)
FROM employees e, (
                    SELECT department_id, MAX(salary) max_sal
                    FROM employees
                    GROUP BY department_id  
                    ORDER BY max_sal ASC 
                    LIMIT 0,1
                    ) t_dept_max_sal
WHERE e.department_id = t_dept_max_sal.department_id

练习十四

查询平均工资最高的部门的manager的详细信息：name,department_id,email,salary。
分析：

先查出各个部门的平均工资。
再查出工资中最高的工资。
再用工资查出所在部门。
再查出部门的管理者id。
最后查出该管理者的详细信息。

方式一：

SELECT name, department_id, email, salary
FROM employees
WHERE employee_id IN (
                        SELECT DISTINCT manager_id
                        FROM employees
                        WHERE department_id = (
                                                SELECT department_id
                                                FROM employees
                                                GROUP BY department_id
                                                HAVING AVG(salary) = (
                                                                        SELECT MAX(avg_sal)
                                                                        FROM (
                                                                                SELECT AVG(salary)
                                                                                FROM employees
                                                                                GROUP BY department_id
                                                                                ) t_dept_avg_sal
                                                                        )
                                                )
                        );

方式二： 使用ALL。

SELECT name, department_id, email, salary
FROM employees
WHERE employee_id IN (
                        SELECT DISTINCT manager_id
                        FROM employees
                        WHERE department_id = (
                                                SELECT department_id
                                                FROM employees
                                                GROUP BY department_id
                                                HAVING AVG(salary) >= ALL (
                                                                        SELECT AVG(salary)
                                                                        FROM employees
                                                                        GROUP BY department_id
                                                                        )
                                                )
                        );

方式三：

SELECT name, department_id, email, salary
FROM employees
WHERE employee_id IN (
                       SELECT DISTINCT manager_id
                        FROM employees e, (
                                            SELECT department_id, AVG(salary) avg_sal
                                            FROM employees
                                            GROUP BY department_id  
                                            ORDER BY avg_sal DESC 
                                            LIMIT 0,1
                                            ) t_dept_avg_sal
                        WHERE e.department_id = t_dept_avg_sal.department_id 
                       );

练习十五

查询部门的部门号，其中不包括job_id是'IT'的部门号
分析：

先查出job_id是'IT'的部门号。
再查出部门号不是上面查出部门号的部门。

方式一：
使用NOT IN。

SELECT department_id
FROM departments
WHERE department_id NOT IN (
                            SELECT DISTINCT department_id
                            FROM employees
                            WHERE job_id = 'IT'
                            );

方式二：
使用EXISTS。

SELECT department_id
FROM departments d
WHERE EXISTS (
                SELECT *
                FROM employees e
                WHERE d.department_id = e.department_id
                AND job_id = 'IT'
                );

练习十六

查询没有管理者的员工的name。
分析：

先查出有管理者的员工信息。
再查出不在这些结果中的员工信息。

SELECT name
FROM employees emp
WHERE NOT EXISTS (
                    SELECT *
                    FROM employees mgr
                    WHERE emp.manager_id = mgr.employee_id
                    );

这里使用NOT EXISTS，查询不在子查询结果中的结果。

练习十七

查询员工号，姓名，雇佣时间，工资，其中员工的管理者是'xiaoming'。
分析：

先查出管理者是'xiaoming'的员工id。
再查出该管理者管理的员工信息。

方式一：
使用IN。

SELECT employee_id, name, hire_date, salary
FROM employees
WHERE manager_id IN (
                        SELECT employee_id
                        FROM employees
                        WHERE name = 'xiaoming'
                        );

方式一：
使用EXISTS。

SELECT employee_id, name, hire_date, salary
FROM employees e1
WHERE EXISTS (
                SELECT *
                FROM employees e2
                WHERE e1.manager_id = e2.employee_id
                AND name = 'xiaoming'
                );

练习十八

查询各部门中工资比本部门平均工资高的员工的员工号，姓名工资。
分析：

先查出所有的员工信息。
再查出该员工所在部门的平均工资。
最后查出该员工是否工资比部门的平均工资高。

方式一：
使用相关子查询。

SELECT name, salary
FROM employees e1
WHERE salary > (
                SELECT AVG(salary)
                WHERE employees e2
                WHERE department_id = e1.department_id
                );

方式二：
在FROM中声明子查询。

SELECT e.name, e.salary
FROM employees e, (
                    SELECT department_id, AVG(salary) avg_sal
                    FROM employees
                    GROUP BY department_id
                    ) t_dept_avg_sal
WHERE e.department_id = t_dept_avg_sal.department_id
AND e.salary > t_dept_avg_sal.avg_sal

练习十九

查询每个部门下的部门人数大于5的部门名称。
分析：

先查出所有的部门信息。
再查出该部门的人数。
最后查出人数大于5的部门。

使用相关子查询。

SELECT department_name
FROM departments d
WHERE 5 < (
                SELECT COUNT(*)
                WHERE employees e
                WHERE d.department_id = e.department_id
                );

练习二十

查询每个国家下的部门个数大于2的国家编号。
分析：

先查出所有的国家信息。
再查出该国家的部门个数。
最后查出个数大于2的部门的国家。

使用相关子查询。

SELECT country_id
FROM locations l
WHERE 2 < (
                SELECT COUNT(*)
                WHERE departments d
                WHERE l.location_id = d.location_id
                );

子查询的编写步骤：

从里往外写。
从外往里写。

实际使用时如何选择呢？

总结：

如果子查询相对比较简单，建议从外往里写。一旦子查询结构比较复杂，则建议从里往外写。
如果是相关子查询的话，通常都是从外往里写。

今天先学习到这里，明天继续。

MySQL学习-子查询练习（三）

前言