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

圆之蓝调
123 阅读3分钟

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

前言

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

练习一

查询和xiaoming相同部门的员工姓名和工资。

SELECT name, salary
FROM employees
WHERE department_id = (
                        SELECT department_id
                        FROM employees
                        WHERE name = 'xiaoming'
                        );

这里使用等号比较，这里姓名叫xiaoming的只有一个人。但是可能有重名的情况，所以为了保险起见，这里不使用=，而是使用IN。

SELECT name, salary
FROM employees
WHERE department_id IN (
                        SELECT department_id
                        FROM employees
                        WHERE name = 'xiaoming'
                        );

可见虽然结果是相同的，但是用IN考虑更周全。

练习二

查询工资比公司平均工资高的员工的员工号，姓名和工资。
先查出公司的平均工资，再查询工资比公司平均工资高的员工信息。

SELECT employee_id, name, salary
FROM employees
WHERE salary > (
                SELECT AVG(salary)
                FROM employees
                );

练习三

查询工资大于所有job_id是'IT'的员工的工资的员工的name,job_id,salary。
先查出工作是IT的所有员工工资，然后进行比较，大于所有，这里使用ALL。

SELECT job_id, name, salary
FROM employees
WHERE salary > ALL (
                SELECT salary
                FROM employees
                WHERE job_id = 'IT'
                );

练习四

查询和姓名中包含字母g的员工在相同部门的员工的员工号和姓名。
先查出员工姓名包含g的员工所在部门，并将结果去重，然后查询这些部门内员工的信息。

SELECT employee_id, name
FROM employees
WHERE department_id IN (
                        SELECT DISTINCT department_id
                        FROM employees
                        WHERE employee_id = '%g%'
                        );

练习五

查询在部门的location_id为100的部门工作的员工的员工号。
先查出部门的location_id为100的部门id，然后查询这些部门的员工信息。

SELECT employee_id
FROM employees
WHERE department_id IN (
                        SELECT department_id
                        FROM department
                        WHERE location_id = 100
                        );

练习六

查询管理者是xiaolan的员工姓名和工资。
先查询姓名是小兰的员工id，再查询管理者id是小兰员工id的员工的信息。

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

练习七

查询工资最低的员工的name和salary。
先查出最低工资，再查出工资是最低工资的员工信息。

SELECT name, salary
FROM employees
WHERE salary = (
                SELECT MIN(salary)
                FROM employees
                );

练习八

查询平均工资最低的部门信息。
方式一

  1. 先查出各个部门的平均工资。
  2. 将查询的结果作为新表，再从新表中查出最低的工资。
  3. 然后查询部门平均工资是最低工资的部门id。
  4. 最后根据部门id查出该部门的信息。
SELECT *
FROM departments
WHERE department_id = (
                        SELECT department_id
                        FROM employees
                        GROUP BY department_id
                        HAVING AVG(salary) = (
                                                SELECT MIN(avg_sal)
                                                FROM (
                                                        SELECT AVG(salary) avg_sal
                                                        FROM employees
                                                        GROUP BY department_id
                                                        ) t_dept_avg_sal
                                                )
                        );

这里分4部查询，有4层查询。

方式二
使用ALL查出最低工资的部门id。

  1. 先查出各个部门的平均工资。
  2. 使用ALL查出最低工资的部门id。
  3. 最后根据部门id查出该部门的信息。
SELECT *
FROM departments
WHERE department_id = (
                        SELECT department_id
                        FROM employees
                        GROUP BY department_id
                        HAVING AVG(salary) <= ALL (
                                                    SELECT AVG(salary) avg_sal
                                                    FROM employees
                                                    GROUP BY department_id
                                                )
                        );

使用ALL，少一层查询。

方式三
使用ORDER BY，然后LIMIT的方式，查出最低工资。

  1. 使用ORDER BY，然后LIMIT的方式，先查出各个部门的平均工资的最低工资。
  2. 查出最低工资的部门id。
  3. 最后根据部门id查出该部门的信息。
SELECT *
FROM departments
WHERE department_id = (
                        SELECT department_id
                        FROM employees
                        GROUP BY department_id
                        HAVING AVG(salary) = (
                                                SELECT AVG(salary) avg_sal
                                                FROM employees
                                                GROUP BY department_id
                                                ORDER BY avg_sal ASC
                                                LIMIT 1
                                              )
                        );

方式四
将查询出的平均工资中的最低工资当成新表，再与要查询的表连接查询得到部门信息。

  1. 使用ORDER BY，然后LIMIT的方式，先查出各个部门的平均工资的最低工资和部门id。
  2. 然后根据部门id查出该部门的信息。
SELECT d.*
FROM departments d, (
                    SELECT department_id, AVG(salary) avg_sal
                    FROM employees
                    GROUP BY department_id
                    ORDER BY avg_sal ASC
                    LIMIT 1
                    ) t_dept_avg_sal
WHERE d.'department_id' = t_dept_avg_sal.department_id

这里只需要2层，就可以查询出结果。

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

avatar
文章
阅读
粉丝