MySQL子查询篇（精选20道子查询练习题）-天翼云

说白了子查询就是嵌套查询

SQL 中子查询的使用大大增强了 SELECT 查询的能力

因为很多时候查询需要 从结果集中获取数据 ，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集合）进行比较。

如果我们遇见下面这种问题，就要用到子查询了

现有方法：

先查出Abel的工资，然后那结果在查询一次进行筛选

单行子查询

单行比较操作符

什么是单行操作符

就是它只能匹配一行数据，如果你的子查询查出了多条数据，那么它就报错

题目：查询工资大于149号员工工资的员工的信息

题目：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资

写子查询什么思路呢？

根据题目，先单独写出子查询，最后添加到查询语句中

比如题目说返回job_id与141号员工相同，那我们就可以先写出141号员工的job_id是多少的查询语句，之后在放到总查询语句中

题目：查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id， manager_id，department_id

最后的AND employee_id NOT IN (174, 141);是为了去掉141、174员工它们本身，因为题目要求是求其他员工的…

这里的IN属于多行子查询操作符，这里提前用到了。因为子查询结果不唯一

CASE中的子查询

题目：显式员工的employee_id,last_name和location。其中，若员工department_id与location_id为1800 的department_id相同，则location为’Canada’，其余则为’USA’。

若员工department_id与location_id为1800的department_id相同，则location为’Canada’，其余则为’USA’
这题明显的CASE，也就是SWITCH形式的
1、根据location_id为1800的department_id先写出这个查询
2、然后CASE后面跟的条件就是department_id，拿CASE后面跟的条件去和子查询查出的条件相比即可
3、给它起个别名location

2、聚合函数不能嵌套查询（MIN（AVG（SALARY）））

3、派生表要有自己的别名，不然会出现如下错误：

Every derived table must have its own alias（每个派生表都必须有自己的别名）

4、为了最后结果SQL的美观，前面做的小查询最好把它写成简写形式

-- 1.每个部门的平均工资
SELECT AVG(salary) FROM employees WHERE department_id IS NOT NULL GROUP BY department_id;

-- 2.平均工资最低
SELECT MIN(avg_salary) FROM (
SELECT AVG(salary) avg_salary FROM employees WHERE department_id IS NOT NULL GROUP BY department_id) avg_emp_salary

-- 3.平均工资最低的部门
SELECT
  department_id 
FROM
  employees 
GROUP BY
  department_id 
HAVING
  AVG( salary ) = ( SELECT MIN( avg_salary ) FROM ( SELECT AVG( salary ) avg_salary FROM employees WHERE department_id IS NOT NULL GROUP BY department_id ) avg_emp_salary );

方式二:让平均工资小于所有平均工资最小的,那直接就满足条件了

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

意思就是子查询查出来的结果是空值，导致外面的查询对应的结果也是空值

在内部查询中使用外部的表建立关联关系即可实现相关子查询

举个例子：

当外部查出第一条数据的时候department_id为10，那么进到子查询就是查department_id为10的平均工资

当外部查出第二条数据的时候department_id为20，那么进到子查询就是查department_id为20的平均工资

-- 1.60号部门的平均工资
SELECT AVG(salary) FROM employees WHERE department_id=60
-- 2.员工中工资大于60号部门的平均工资
SELECT
  last_name,
  salary
FROM
  employees
WHERE
  salary > (SELECT AVG(salary) FROM employees WHERE department_id=60)
-- 3.那本部门怎么表示呢?
-- 在内部查询中使用外部的表建立关联关系即可
SELECT
  last_name,
  salary
FROM
  employees e1
WHERE
  salary > (SELECT AVG(salary) FROM employees e2 WHERE department_id=e1.department_id);

方式二：在 FROM 中使用子查询

1、先把部门平均工资查询出来，把查询结果当成新表e2来用

2、和员工表e1进行连接

3、通过添加条件**e1.salary > e2.avg_salay（员工的工资大于每个部门的平均工资）**进行筛选

4、FROM使用的情况就是很多时候我们想查询的时候数据是不存在的， 我们需要先把数据查出来然后把这个查出来的数据当成一个新的表来使用

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

题目：查询员工的id,salary,按照department_name 排序

很明显department_name 在department表中，所以这又是一个相关子查询，还是带多写

结论：在查询语句中，除了GROUP BY和LIMIT之外，其他位置都可以声明子查询

SELECT … , … ,…(存在聚合函数)

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
  employee_id,
  last_name,
  job_id 
FROM
  employees e 
WHERE
  2 <= ( SELECT COUNT(*) FROM job_history WHERE employee_id = e.employee_id );

EXISTS 与 NOT EXISTS关键字

关联子查询通常也会和 EXISTS操作符一起来使用， 用来检查在子查询中是否存在满足条件的行。

如果在子查询中不存在满足条件的行：

条件返回FALSE

继续在子查询中查找

如果在子查询中存在满足条件的行：

不在子查询中继续查找

条件返回TRUE

NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。

题目：查询公司管理者的employee_id，last_name，job_id，department_id信息

-- 自连接
SELECT
  DISTINCT
  e1.employee_id,
  e1.last_name,
  e1.job_id,
  e1.department_id
FROM
  employees e1 JOIN employees e2
WHERE
  e1.employee_id = e2.manager_id

-- 子查询1
-- 如果能用自连接的话最好使用自连接，因为它的速度快，效率高
-- 这个子查询的思路是：
-- 1、先查出所有员工的manage_id,并进行去重，因为多个员工可能有共同的领导
-- 2、外查询在进行获取manage_id的信息
SELECT
  employee_id,
  last_name,
  job_id,
  department_id
FROM
  employees
WHERE
  employee_id IN (SELECT DISTINCT manager_id FROM employees)


-- 子查询2
-- EXISTS关键字是用来检查子查询中是否存在满足的条件
-- 满足返回true，打印
-- 不满足继续找，直到true为止
-- 那么NOT EXISTS意思就是和EXISTS相反
SELECT
  employee_id,
  last_name,
  job_id,
  department_id
FROM
  employees e1
WHERE
  EXISTS (SELECT * FROM employees WHERE e2.manager_id = e1.employee_id)方式一：

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

NOT EXISTS不存在返回true停止查找子查询，存在返回false，继续查找子查询

#1.查询和Zlotkey相同部门的员工姓名和工资
-- 1.分析题,找出定语"查询和Zlotkey相同部门"(一般"的"前面的做定于修饰后面的句子)
-- 2.写出定语的查询语句SELECT department_id FROM employees WHERE last_name = 'Zlotkey'找出了Zlotkey部门ID
-- 3.完成题目
SELECT
  last_name,
  salary 
FROM
  employees 
WHERE
  department_id = ( SELECT department_id FROM employees WHERE last_name = 'Zlotkey' );


#2.查询工资比公司平均工资高的员工的员工号，姓名和工资。
-- 1.分析题,找出定语"工资比公司平均工资高"
-- 2.写出定语的查询语句salary > (SELECT AVG(salary) avg_emp FROM employees)
-- 3.完成题目
SELECT 
  employee_id,
  last_name,
  salary
FROM
  employees
WHERE
  salary > (SELECT AVG(salary) avg_emp FROM employees)
  

#3.选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary
-- 1.分析题,找出定语"工资大于所有JOB_ID = 'SA_MAN'的员工"
-- 2.写出定语的查询语句salary > ALL (SELECT salary FROM employees WHERE JOB_ID = 'SA_MAN')
-- 3.完成题目
SELECT 
  last_name,
  job_id,
  salary
FROM
  employees
WHERE
  salary > ALL (SELECT salary FROM employees WHERE job_id = 'SA_MAN')


#4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
-- 1.分析题,找出定语"姓名中包含字母u的员工在相同部门的员工"
-- 2.写出定语的查询语句SELECT department_id,last_name FROM employees WHERE last_name LIKE '%u%'
-- 3.完成题目
SELECT
  employee_id,
  last_name
FROM
  employees
WHERE
  (department_id,last_name) IN (SELECT department_id,last_name FROM employees WHERE last_name LIKE '%u%' );

#5.查询在部门的location_id为1700的部门工作的员工的员工号
-- 1.分析题,找出定语"部门的location_id为1700的部门工作的员工"
-- 2.写出定语的查询语句SELECT department_id FROM departments WHERE location_id = 1700
-- 3.完成题目
SELECT
  employee_id
FROM
  employees
WHERE
  department_id IN (SELECT department_id FROM departments WHERE location_id = 1700)


#6.查询管理者是King的员工姓名和工资
-- 1.分析题,找出定语"管理者是King"
-- 2.写出定语的查询语句SELECT employee_id FROM employees WHERE last_name = 'King'
-- 3.完成题目
SELECT
  last_name,
  salary
FROM
  employees
WHERE
  manager_id IN (SELECT employee_id FROM employees WHERE last_name = 'King')


#7.查询工资最低的员工信息: last_name, salary
-- 1.分析题,找出定语"工资最低"
-- 2.写出定语的查询语句SELECT MIN(salary) FROM employees
-- 3.完成题目
SELECT
  last_name,
  salary
FROM
  employees
WHERE
  salary = (SELECT MIN(salary) FROM employees)

#8.查询平均工资最低的部门信息
-- 1.分析题,找出定语"平均工资最低的部门"
-- 2.写出定语的查询语句
-- SELECT MIN( avg_salary ) FROM ( SELECT AVG( salary ) avg_salary FROM employees GROUP BY department_id ) t_avg_salary
-- 3.找到平均工资后,在把这个平均工资当成一个新表来用即可.需要注意的点就是要给新表起别名,要给平均薪资字段起别名
-- 方式一:先求出平均工资,在求最低工资,因为前两次求的是平均工资最低的数据,所以最后条件要用AVG函数来接收
SELECT
  *
FROM
  departments
WHERE
  department_id = (
    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 ) t_avg_salary )
  )


-- 方式二:求出各部门最低工资后,直接设置条件让它等于查出结果中最小的那个即可
SELECT
  *
FROM
  departments
WHERE
  department_id = (
    SELECT
      department_id
    FROM
      employees
    GROUP BY
      department_id 
    HAVING
      AVG( salary ) <= ALL ( SELECT AVG( salary ) avg_salary FROM employees GROUP BY department_id )
  )


-- 方式三:求出各部门平均工资后,进行升序排序,然后通过分页查询取第一条数据即可
SELECT
  * 
FROM
  departments 
WHERE
  department_id = (
    SELECT
      department_id 
    FROM
      employees 
    GROUP BY
      department_id 
    HAVING
      AVG( salary )=(
        SELECT
          AVG( salary ) avg_salary 
        FROM
          employees 
        GROUP BY
          department_id 
        ORDER BY
          avg_salary 
          LIMIT 0,
          1 
        ));

-- 方式四
-- 查询出各部门平均工资后,把数据当成新表来用,与旧表进行自连接
SELECT
  d.*
FROM
  departments d,(
    SELECT department_id,AVG(salary) avg_sal
    FROM employees
    GROUP BY department_id
    ORDER BY avg_sal
    LIMIT 0,1) t_avg_sal
WHERE d.department_id = t_avg_sal.department_id



#9.查询平均工资最低的部门信息和该部门的平均工资（相关子查询）
-- 方式一：
-- 1.这里部门信息采用子查询查到的平均工资最低的数值
-- 2.通过平均工资最低的数值在进行查询到部门ID
-- 3.根据部门ID获取部门信息
-- 4.平均工资通过在select语句中在进行相关子查询，获得该部门的平均工资
-- 这种方式呢，是通过平均工资最低数值来获取部门ID，其实部门ID是可以直接获取的，这里相当于多走一步
SELECT
  d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.department_id) avg_salary
FROM
  departments d
WHERE
  department_id = (
    SELECT
      department_id
    FROM
      employees
    GROUP BY
      department_id 
    HAVING
      AVG( salary ) <= ALL ( SELECT AVG( salary ) avg_salary FROM employees GROUP BY department_id )
  )

-- 方式二：
-- 在方式一基础上，获取最低工资的方式不在是和全部部门的工资作比较，而是直接让工资等于升序过后的第一个工资
SELECT d.*,( SELECT AVG( salary ) FROM employees WHERE department_id = d.department_id ) avg_salary 
FROM
  departments d 
WHERE
  department_id = (
  SELECT
    department_id 
  FROM
    employees 
  GROUP BY
    department_id 
  HAVING
    AVG( salary ) = ( SELECT AVG( salary ) avg_salary FROM employees GROUP BY department_id ORDER BY avg_salary LIMIT 0, 1 ) 
  );
  
-- 方式三：
-- 1.通过MIN聚合函数来获取最低平均工资
-- 2.然后在筛选平均工资为最低平均工资的部门
-- 3.获取部门信息....
SELECT d.*,( SELECT AVG( salary ) FROM employees WHERE department_id = d.department_id ) avg_salary 
FROM
  departments d 
WHERE
  department_id = (
  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 ) t_avg_salary ));


-- 方式四：
-- 平均工资最低的部门
SELECT department_id FROM employees GROUP BY department_id ORDER BY AVG(salary) LIMIT 0,1
-- 部门信息和该部门的平均工资
-- 1.这里部门信息采用子查询查到的平均工资最低的部门ID来获取部门信息
-- 2.平均工资通过在select语句中在进行相关子查询，获得该部门的平均工资
SELECT d.*,( SELECT AVG( salary ) FROM employees e WHERE d.department_id = e.department_id GROUP BY department_id ) avg_sala
FROM
  departments d 
WHERE
  department_id = ( SELECT department_id FROM employees GROUP BY department_id ORDER BY AVG( salary ) LIMIT 0, 1 );

-- 方式五：
-- 效率最高,通过自连接
SELECT d.*,t_avg_sal.avg_sal
FROM  departments d,(
  SELECT
    department_id,
    AVG(salary) avg_sal
  FROM
    employees 
  GROUP BY
    department_id 
  ORDER BY
    AVG( salary ) 
    LIMIT 0,1) t_avg_sal
WHERE d.department_id = t_avg_sal.department_id



#10.查询平均工资最高的 job 信息
-- 1.分析题,找出定语"平均工资最高"
-- 2.写出定语的查询语句AVG( salary ) >= ALL(SELECT AVG( salary ) avg_salary FROM employees GROUP BY job_id)
-- 3.完成题目
SELECT
  j.* 
FROM
  jobs j,
  ( SELECT job_id, AVG( salary ) avg_salary FROM employees GROUP BY job_id ORDER BY avg_salary DESC LIMIT 0, 1 ) t_avg_salary 
WHERE
  j.job_id = t_avg_salary.job_id;

#11.查询平均工资高于公司平均工资的部门有哪些?
-- 1.分析题,找出定语"平均工资高于公司平均工资的部门"
-- 2.写出定语的查询语句AVG(salary) > (SELECT AVG(salary) FROM employees)
-- 3.完成题目
SELECT
  department_id
FROM
  employees
WHERE
  department_id IS NOT NULL
GROUP BY
  department_id
HAVING
  AVG(salary) > (SELECT AVG(salary) FROM employees)



#12.查询出公司中所有 manager 的详细信息
-- 1.分析题,找出定语"公司中所有 manager "
-- 2.写出定语的查询语句employee_id IN (SELECT DISTINCT manager_id FROM employees)
-- 3.完成题目
SELECT
  employee_id,
  last_name,
  salary
FROM
  employees
WHERE
  employee_id IN (SELECT DISTINCT manager_id FROM employees)
  
SELECT
  employee_id,
  last_name,
  salary
FROM
  employees e1
WHERE
  EXISTS (SELECT * FROM employees e2 WHERE e2.manager_id = e1.employee_id)


-- 自连接
SELECT
  e1.employee_id,
  e1.last_name,
  e1.salary
FROM
  employees e1 JOIN employees e2
WHERE
  e1.employee_id = e2.manager_id
  
  

#13.各个部门中最高工资中最低的那个部门的 最低工资是多少?
-- 1.分析题,找出定语"各个部门中最高工资中最低的那个部门 "
-- 2.写出定语的查询语句salary <= ALL(SELECT MAX(salary) FROM employees GROUP BY department_id)
-- 3.完成题目
SELECT
  MIN(salary)
FROM
  employees
WHERE
  salary <= ALL(SELECT MAX(salary) FROM employees GROUP BY department_id)
  
-- 自连接
SELECT
  salary
FROM
  employees e1,(SELECT department_id,MAX(salary) max_salary FROM employees GROUP BY department_id ORDER BY max_salary LIMIT 0,1
) t_max_salay
WHERE
  e1.department_id = t_max_salay.department_id


#14.查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
-- 1.分析题,找出定语"平均工资最高的部门 "
-- 2.写出定语的查询语句salary >= ALL(SELECT AVG(salary) FROM employees GROUP BY department_id)获取最高部门的ID
-- 3.获得所有manager的信息
-- 4.设置限制条件进行匹配
SELECT
  last_name, 
  department_id, 
  email, 
  salary
FROM
  employees
WHERE
  employee_id IN (SELECT DISTINCT manager_id FROM employees)
  AND department_id = (SELECT department_id FROM employees WHERE salary>=ALL (
SELECT AVG(salary) FROM employees GROUP BY department_id))
  

-- 平均工资最高的部门
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal DESC
LIMIT 0,1

-- 自连接方式
SELECT *
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_avg_sal
WHERE
  e.department_id = t_avg_sal.department_id)

#15. 查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号
-- 这里注意,题目说的是部门中的部门号,不是员工表中的部门号,所以也要进行子查询
SELECT
  department_id
FROM
  departments
WHERE
  department_id NOT IN (
      SELECT DISTINCT
        department_id 
      FROM
        employees 
      WHERE
        job_id = 'ST_CLERK'
  )


#16. 选择所有没有管理者的员工的last_name
SELECT
  last_name
FROM
  employees
WHERE
  manager_id IS NULL


#17．查询员工号、姓名、雇用时间、工资，其中员工的管理者为 'De Haan'
SELECT
  employee_id,
  last_name,
  hire_date,
  salary
FROM
  employees
WHERE
  manager_id = (SELECT employee_id FROM employees WHERE last_name = 'De Haan')


#18.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
-- 1.分析题,找出定语"各部门中工资比本部门平均工资高的员工 "
-- 2.写出定语的查询语句SELECT AVG( salary ) FROM employees e2 WHERE e2.department_id = e1.department_id GROUP BY department_id 
-- 3.设置限制条件进行匹配
SELECT
  e1.employee_id,
  e1.last_name,
  e1.salary 
FROM
  employees e1 
WHERE
  salary > ( SELECT AVG( salary ) FROM employees e2 WHERE e2.department_id = e1.department_id GROUP BY department_id  );

#19.查询每个部门下的部门人数大于 5 的部门名称
-- 1.分析题,找出定语"查询每个部门下的部门人数大于 5  "
-- 2.写出定语的查询语句5 < (SELECT COUNT(*) FROM employees e2 WHERE e2.department_id = e1.department_id )
-- 3.设置限制条件进行匹配
SELECT DISTINCT
  department_name
FROM
  employees e1
WHERE
  5 < (SELECT COUNT(*) FROM employees e2 WHERE e2.department_id = e1.department_id )

#20.查询每个国家下的部门个数大于 2 的国家编号
-- 1.分析题,找出定语"查询每个国家下的部门个数大于2"
-- 2.写出定语的查询语句2 < (SELECT COUNT(*) FROM departments d WHERE l.location_id=d.location_id)
-- 3.设置限制条件进行匹配
SELECT
  country_id
FROM
  locations l
WHERE
  2 < (
    SELECT COUNT(*)
    FROM departments d
    WHERE l.location_id = d.location_id
  )

应用商城

合作伙伴

开发者

支持与服务

了解天翼云

MySQL子查询篇（精选20道子查询练习题）

MySQL子查询篇（精选20道子查询练习题）

相关、不相关子查询

单行子查询

单行比较操作符

CASE中的子查询

EXISTS 与 NOT EXISTS关键字

相关文章

SQL基础操作全解析：从多表查询到表管理与约束

MySql数据库复杂查询示例

认识MySQL中的聚合函数以及聚合函数的作用，拿捏这些细节

MySQL数据库索引

图解MySQL的SQL语句执行全流程，看看自己的SQL语句是如何执行的

图文解说：MySQL核心模块知识和流程

MySQL EXPLAIN，数据库调优的秘密通道

MySQL中的Decimal数据类型用法详解

位运算在数据库中的运用实践-以MySQL和PG为例

深入探讨MySQL索引的设计原则及优化策略

作者介绍

最新文章

MySql数据库复杂查询示例

MySQL数据库索引

MySQL EXPLAIN，数据库调优的秘密通道

MySQL中的Decimal数据类型用法详解

深入探讨MySQL索引的设计原则及优化策略

linux安装mysql

热门文章

Confluence 6 MySQL 输入你的数据库细节

MySQL timestamp(3)问题

MySQL安全小技巧

MySQL数据库的安装（安装以及简单数据库操作）

MySQL的数据库database(二)

MySQL的子查询(二十)

热门标签

相关产品

弹性云主机

天翼云电脑（公众版）

对象存储

云硬盘

随机文章

深入理解MySQL数据库隔离级别

【MySQL】—— 初识MySQL （数据类型，建表查表）

00使用yum仓库安装MySQL8

MySQL第九课 frm和idb文件说明

面试之前，MySQL表连接必须过关！——表连接的原理

MySQL 的日期和时间函数