MySQL高级 之 子查询

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

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

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

MySQL高级 之 子查询

 MySQL高级 之 子查询

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

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

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

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

MySQL高级 之 子查询

  

 多列子查询

 MySQL高级 之 子查询

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

列比较

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

成对比较

不成对比较

2、问题:查询与141号或174号员工的manager_iddepartment_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;

结果如图所示,生成新的临时表:

MySQL高级 之 子查询

-- 单独查询指定部门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;

MySQL高级 之 子查询

示例二:

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

MySQL高级 之 子查询

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

相关子查询

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

 

问题:返回比 本 部门平均工资高的员工的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 语句中的子查询关联条件也用到了主查询中的数据

结果如下:

MySQL高级 之 子查询

方式一:(在 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_namelocation。其中,

-- 若员工department_id location_id1800department_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;

MySQL高级 之 子查询

-- 在 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);

MySQL高级 之 子查询

相关子查询

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

MySQL高级 之 子查询

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

MySQL高级 之 子查询

 -- 查询员工中工资大于本部门平均工资的员工的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
);

 

上一篇:C#中的IntPtr


下一篇:C#调用系统打印机和收银钱箱(USB或虚拟USB端口链接的打印机)