MySQL高级 之 子查询

本文主要是介绍MySQL高级 之 子查询，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

>>>  多年前的 SQL 联系笔记，现在回头看很多问题，也懒得整理了，直接搬运到 博客，仅仅是一些练习题，并非知识库 <<<

子查询是嵌套在 SQL 语句中的另一个SELECT 语句

子查询可以用在很多地方，如 where 后面 from 后面 group by 后面 order by后面、select后面等等

子查询 (内查询) 在主查询执行之前执行

主查询(外查询)使用子查询的结果：

1、问题：查询工资大于149号员工工资的员工的信息

SELECT *
FROM employees e
WHERE salary > (
    SELECT salary
    FROM employees
    WHERE employee_id = 149

 多列子查询

 主查询与子查询返回的 多个列 进行比较

列比较

多列子查询中的比较分为两种:

成对比较

不成对比较

2、问题：查询与141号或174号员工的manager_id和department_id相同的 其他员工的 employee_id, manager_id, department_id

不成对查询：

SELECT employee_id, manager_id, department_id
FROM employees
WHERE manager_id IN (
    SELECT manager_id
    FROM employees
    WHERE employee_id IN (141, 174)
)
  AND department_id IN (
    SELECT department_id
    FROM employees
    WHERE employee_id IN (141, 174)
)
-- 上段查询的到的是所有的结果（包括了141和174号员工），其他员工则需要排除141和174号员工
  AND employee_id NOT IN (141, 174);

成对查询：

SELECT employee_id, manager_id, department_id
FROM employees
WHERE (manager_id, department_id) IN (
    SELECT manager_id, department_id
    FROM employees
    WHERE employee_id IN (141, 174)
)
-- 上段查询的到的是所有的结果（包括了141和174号员工），其他员工则需要排除141和174号员工
  AND employee_id NOT IN (141, 174);

在FROM 子句中使用子查询：

-- 查询各个部门的平均工资
SELECT AVG(salary)
FROM employees
GROUP BY department_id;

-- 查询各个部门的平均工资，显示部门ID和名称
SELECT d.department_id, department_name,avg(salary)
FROM employees e,
     departments d
WHERE e.department_id = d.department_id
GROUP BY e.department_id;

结果如图所示，生成新的临时表：

-- 单独查询指定部门ID的部门平均工资
SELECT AVG(salary)
FROM employees
WHERE employees.department_id = 80;
-- 或者
SELECT AVG(salary)
FROM employees
WHERE department_id = 80;
-- 或者
SELECT AVG(salary)
FROM employees e
WHERE e.department_id = 80;

-- 获取各个部门中平均工资的最大值,Oracle中支持此种方式，主函数嵌套，但是MySQL中不支持
SELECT max(AVG(salary))
FROM employees
GROUP BY department_id;

MySQL中不能使用主函数嵌套，解决方案，将上图查询结果的临时表当做一张表再次查询，
即将 临时表的查询语句嵌套如 下一次查询的 FROM （table）语句中，临时表也是一张表

示例一：

SELECT temp_id,temp_name,max(avg_sal)
FROM (
         SELECT d.department_id temp_id, department_name temp_name, avg(salary) avg_sal
         FROM employees e,
              departments d
         WHERE e.department_id = d.department_id
         GROUP BY e.department_id
     ) t_avg;

示例二：

SELECT max(avg_sal)
FROM (
         SELECT AVG(salary) avg_sal   -- 临时表中的列必须有自己的名字
         FROM employees
         GROUP BY department_id
     ) t_avg;   -- 临时表也必须有自己的名字

 注意，临时表中，每个列都必须有自己的名字，临时表也必须有自己的名字（推荐使用示例二，只显示关键的列，不容易出错）

相关子查询

主查询与子查询中有相关性，子查询无法单独的完成查询，子查询中条件也需要主查询中提供

问题：返回比 本 部门平均工资高的员工的last_name, department_id, salary及平均工资

-- 方式一：（在 select 和 where 语句中使用子查询）
-- -- 难度分解，先查询比本部门平均工资高的员工的last_name,department_id, salary
SELECT last_name, department_id, salary
FROM employees e1
WHERE e1.salary >(
          SELECT AVG(salary)
          FROM employees e2
          WHERE e2.department_id = e1.department_id
          GROUP BY department_id
      );
-- 子查询需要主查询提供数据，这就是相关性，相关子查询

SELECT 语句中加入子查询 均工资

(SELECT AVG(salary)
 FROM employees e3
 WHERE e3.department_id = e1.department_id
 GROUP BY e3.department_id)

SELECT last_name,
       department_id,
       salary,
       (SELECT AVG(salary)
        FROM employees
        WHERE department_id = e1.department_id
        GROUP BY department_id) avg_Salary --可以给这个临时表价格别名，显示的时候好看一些（也可以不加）
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department_id = e1.department_id
    GROUP BY department_id
);
-- 同样 SELECT 语句中的子查询关联条件也用到了主查询中的数据

结果如下：

方式一：（在 from 语句中使用子查询），将平均工资查询结果临时表作为一张表，这样查询更简单，普通子查询就可解决

子查询可独立完成查询

SELECT last_name,
       e1.department_id,
       e2.avg_salary
FROM employees e1,
     (
         SELECT avg(salary) avg_salary, department_id
         FROM employees
         GROUP BY department_id
     ) e2  -- 将查询平均工资的临时表作为一张表，作为 FROM 的表，注意：一定要给临时表 别名
WHERE e1.department_id = e2.department_id
  AND e1.salary > e2.avg_salary;

或者：

SELECT e1.last_name,
       e1.department_id,
       e1.salary,
       e2.avg_salary
FROM employees e1,
     (SELECT avg(salary) avg_salary,
             department_id
      FROM employees
      WHERE department_id
      GROUP BY department_id
     ) e2  -- 将查询平均工资的临时表作为一张表，作为 FROM 的表，注意：一定要给临时表 别名
WHERE e1.department_id = e2.department_id
  AND e1.salary > e2.avg_salary;

-- 显示员工的employee_id,last_name和location。其中，

-- 若员工department_id与 location_id为1800的department_id相同，则location为’Canada’,其余为’USA’

即：{department_id = （查询 department_id where条件 location_id=1800）}  为Canada，剩下的为 USA

SELECT employee_id,
       last_name,
       (CASE
            WHEN department_id = (
                SELECT department_id
                FROM departments
                WHERE location_id = 1800
            ) THEN 'CANADA'
            ELSE 'USA' END) location
FROM employees;

-- 在 ORDER BY 子句中使用单列子查询

-- 问题：查询员工的employee_id,last_name,要求按照员工的department_name排序

SELECT employee_id, last_name
FROM employees e
ORDER BY (SELECT department_name
          FROM departments
          WHERE e.department_id = departments.department_id);

相关子查询

相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询

子查询中使用主查询中的列

 -- 查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id

SELECT last_name, salary, department_id
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE e.department_id = department_id
);

EXISTS 操作符应用举例

-- 常规方式查询,manager也是员工，也有员工id，员工的manager_id就是管理者的员工id，所以，员工id在manager_id

-- 队列里的就是管理者

SELECT employee_id, last_name, job_id, department_id
FROM employees
WHERE employee_id IN (
    SELECT manager_id
    FROM employees
);

方式二 ：EXISTS 关键字

EXISTS 判断返回 false 或 true，

SELECT employee_id, last_name, job_id, department_id
FROM employees e
WHERE exists(                           -- 子查询只负责判断true / false （底层返回true或false），单独查询子查询无结果
              SELECT 'x'     -- 没有具体的明确的查询设么，所以，任意字符即可，常规以 X 代替
              FROM employees
              WHERE manager_id =  e.employee_id
          );

NOT EXISTS 操作符应用举例：

-- 查询departments表中，不存在employees表中的部门的department_id 和 department_name

SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS(
        SELECT 'X'
        FROM employees e
        WHERE e.department_id = d.department_id
    )

删除应用举例

如：删除数据表中重复的数据，重复的数据即：数据条数 > 1 ，count （*） > 1 ;

首先查询出重复的数据

-- 删除表employees中重复的数据,先查询出数量大于 1 的记录，按照 employee_id 删除，所以还要查询出employee_id
SELECT employee_id,last_name
FROM employees
GROUP BY last_name
HAVING count(*) > 1;

或者

-- 出现在 select 子句后的非分组函数，一定出现在 group by 子句后（MySQL中不会报错，Oracle中如下句子直接报错）
DELETE
FROM employees
WHERE employee_id IN (
    SELECT employee_id
    FROM (     -- 这一步重复的书写，因为MySQL只能将结果临时表当做一张表，并要求有自己的表名 e 
             SELECT employee_id
             FROM employees
             GROUP BY last_name
             HAVING count(*) > 1
         ) e
);

这篇关于MySQL高级 之 子查询的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！