基础07:子查询

子查询

  • 子查询指一个查询语句嵌套在另一个查询语句内部的查询,这个特性从MySQL 4.1开始引入。

  • SQL 中子查询的使用大大增强了 SELECT 查询的能力,因为很多时候查询需要从结果集中获取数据,或者 需要从同一个表中先计算得出一个数据结果,然后与这个数据结果(可能是某个标量,也可能是某个集 合)进行比较。

一、需求分析与问题解决

1.1 实际问题

-- 查找所有学生的英语成绩?
-- 第一种方式
SELECT cno FROM j_course WHERE cname='英语';
SELECT
j1.sname,
j2.score
FROM
j_student j1
INNER JOIN j_score j2 ON j1.sno = j2.sno
WHERE
j2.cno = 2;
-- 第二种方式(自连接)
SELECT
j1.sname,
j2.score
FROM
j_student j1
INNER JOIN j_score j2 ON j1.sno = j2.sno INNER JOIN j_course j3 ON j2.cno=j3.cno
WHERE
j3.cname='英语';
-- 第三种方式(子连接)
SELECT
j1.sname,
j2.score
FROM
j_student j1
INNER JOIN j_score j2 ON j1.sno = j2.sno
WHERE
j2.cno IN (SELECT cno FROM j_course WHERE cname='英语' );

image-20221007220532546

1.3 子查询的基本使用

  • 子查询的基本语法结构:
SELECT select_list
FROM table
WHERE expr operator (
SELECT select_list
FROM table);
  • 子查询(内查询)在主查询之前一次执行完成。

  • 子查询的结果被主查询(外查询)使用 。

  • 注意事项

    • 子查询要包含在括号内;

    • 将子查询放在比较条件的右侧;

    • 单行操作符对应单行子查询,多行操作符对应多行子查询。

1.3 子查询的分类

  • 分类方式1

    我们按内查询的结果返回一条还是多条记录,将子查询分为 单行子查询多行子查询

    • 单行子查询

      image-20221007221048021

    • 多行子查询

      image-20221007221111819

  • 分类方式2

    我们按内查询是否被执行多次,将子查询划分为 相关(或关联)子查询不相关(或非关联)子查询

    • 子查询从数据表中查询了数据结果,如果这个数据结果只执行一次,然后这个数据结果作为主查询的条 件进行执行,那么这样的子查询叫做不相关子查询。
    • 同样,如果子查询需要执行多次,即采用循环的方式,先从外部查询开始,每次都传入子查询进行查 询,然后再将结果反馈给外部,这种嵌套的执行方式就称为相关子查询。

二、单行子查询

2.1 单行比较操作符

操作符 含义
= equal to
> greater than
>= greater than or equal to
< less than
<= less than or equal to
<> not equal to

2.2 代码示例

  1. 查询预估成绩为85分,且出生年份大于1984年的学生信息
SELECT
*
FROM
j_student
WHERE
pre_score = ( SELECT pre_score FROM j_student WHERE sbir > '1984-01-01' );

image-20221008204444678

  1. 返回最小预估成绩的学生信息
SELECT *FROM j_student WHERE pre_score =(SELECT MIN(pre_score) FROM j_student);

image-20221008204550645

  1. 查询预估成绩为70或75的学生姓名和性别信息
-- 实现方式1:不成对比较
SELECT sname,ssex
FROM j_student
WHERE sname IN (SELECT sname FROM j_student WHERE pre_score IN(70,75))
AND ssex IN (SELECT ssex FROM j_student WHERE pre_score IN(70,75)) ;
-- 实现方式2:成对比较
SELECT sname,ssex
FROM j_student
WHERE (sname,ssex) IN (SELECT sname,ssex FROM j_student WHERE pre_score IN(70,75)) ;

image-20221008204632880

2.3 HAVING中的子查询

  • 首先执行子查询;
  • 向主查询中的HAVING子句返回结果。
-- 查询最低工资大于110号部门最低工资的部门id和其最低工资
SELECT department_id, MIN(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING MIN(salary) >
(SELECT MIN(salary)
FROM employees
WHERE department_id = 50);

image-20221008210926573

2.4 CASE中的子查询

-- 显式员工的employee_id,last_name和location。其中,若员工department_id与location_id为1800的department_id相同,则location为’Canada’,其余则为’USA’。
SELECT employee_id, last_name,
(CASE department_id
WHEN
(SELECT department_id FROM departments
WHERE location_id = 1800)
THEN 'Canada' ELSE 'USA' END) location
FROM employees;

image-20221008211139155

2.5 子查询中的空值问题

-- no rows selected:不返回任何行
SELECT last_name, job_id
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE last_name = 'Haas');

2.6 非法使用子查询

-- 多行子查询使用单行比较符
-- 括号内的子查询出来存在多条数据
SELECT employee_id, last_name
FROM employees
WHERE salary = -- 此处=应替换为IN
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);

image-20221008211508724

三、多行子查询

  • 也称为集合比较子查询
  • 内查询返回多行
  • 使用多行比较操作符、

3.1 多行比较操作符

操作符 含义
IN 等于列表中的任意一个
ANY 需要和单行比较操作符一起使用,和子查询返回的某一个值比较
ALL 需要和单行比较操作符一起使用,和子查询返回的所有值比较
SOME 实际上是ANY的别名,作用相同,一般常使用ANY

3.2 代码示例

-- IN
SELECT employee_id, last_name
FROM employees
WHERE salary IN
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);

image-20221008212444499

-- 返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary
SELECT employee_id, last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ANY (
SELECT salary
FROM employees
WHERE job_id='IT_PROG'
);

image-20221008213339801

-- 返回其它job_id中比job_id为‘IT_PROG’部门所有工资低的员工的员工号、姓名、job_id 以及salary
SELECT employee_id, last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ALL (
SELECT salary
FROM employees
WHERE job_id='IT_PROG'
);

image-20221008213512276

-- 查询平均工资最低的部门id
-- MYSQL中聚合函数不能用于嵌套使用,例如:MIN(AVG(salary)) 这种写法是报错的
#方式1
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
) dept_avg_sal
)
#方式2
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id
)

image-20221008214427775

3.3 空值问题

-- no rows selected:不返回任何行
SELECT last_name
FROM employees
WHERE employee_id NOT IN (
SELECT manager_id
FROM employees
);

3.4 相关子查询

3.4.1 相关子查询执行流程

  • 如果子查询的执行依赖于外部查询,通常情况下都是因为子查询中的表用到了外部的表,并进行了条件 关联,因此每执行一次外部查询,子查询都要重新计算一次,这样的子查询就称之为 关联子查询 。 相关子查询按照一行接一行的顺序执行,主查询的每一行都执行一次子查询。

image-20221010203210980

3.4.2 代码示例

  1. 题目一:查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
-- 方式一:相关子查询
SELECT last_name,salary,department_id
FROM employees outer
WHERE salary >
(SELECT AVG(salary)
FROM employees
WHERE department_id=
outer.department_id);
-- 方式二:在FROM中使用子查询
-- from型的子查询:子查询是作为from的一部分,子查询要用()引起来,并且要给这个子查询取别名, 把它当成一张“临时的虚拟的表”来使用。
SELECT e1.last_name,e1.salary,e1.department_id
FROM employees e1,(SELECT department_id,AVG(salary) dept_avg_sal FROM employees GROUP
BY department_id) e2
WHERE e1.department_id = e2.department_id
AND e2.dept_avg_sal < e1.salary;

image-20221010204334801

  1. 题目二:查询员工的id,salary,按照department_name 排序
SELECT employee_id,salary
FROM employees e
ORDER BY (
SELECT department_name
FROM departments d
WHERE e.`department_id` = d.`department_id`
);

image-20221010205152879

  • 结论:在SELECT中,除了GROUP BY 和 LIMIT之外,其他位置都可以声明子查询!
  1. 题目三:若employees表中employee_id与job_history表中employee_id相同的数目不小于2,输出这些相同 id的员工的employee_id,last_name和其job_id
SELECT e.employee_id, last_name,e.job_id
FROM employees e
WHERE 2 <= (SELECT COUNT(*)
FROM job_history
WHERE employee_id = e.employee_id);

image-20221010210346598

3.4.3 EXISTS 与 NOT EXISTS关键字

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

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

    • 条件返回FALSE;
    • 继续在子查询中查找。
  • 如果在子查询中存在满足条件的行:

    • 不在子查询中继续查找;
    • 条件返回TRUE。
  • NOT EXISTS关键字表示如果不存在某种条件,则返回TRUE,否则返回FALSE。

-- 题目一:查询公司管理者的employee_id,last_name,job_id,department_id信息
-- 方式一:使用EXISTS
SELECT employee_id, last_name, job_id, department_id
FROM employees e1
WHERE EXISTS ( SELECT *
FROM employees e2
WHERE e2.manager_id =
e1.employee_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;
-- 方式三:子查询
SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id IN (
SELECT DISTINCT manager_id
FROM employees
);

image-20221010211328144

-- 查询departments表中,不存在于employees表中的部门的department_id和department_name
-- 方式一:NOT EXISTS
SELECT department_id,department_name
FROM departments d
WHERE NOT EXISTS (
SELECT *
FROM employees e
WHERE e.department_id = d.department_id
);
-- 方式二:右外连接
SELECT d.department_id,d.department_name
FROM departments d RIGHT JOIN employees e
ON d.department_id = e.department_id
WHERE e.department_id IS NULL;

image-20221010212128157

3.4.4 相关更新

  • 使用相关子查询依据一个表中的数据更新另一个表的数据。
UPDATE table1 alias1
SET column = (SELECT expression
FROM table2 alias2
WHERE alias1.column = alias2.column);
-- 题目:在employees中增加一个department_name字段,数据为员工对应的部门名称
# 1
ALTER TABLE employees
ADD(department_name VARCHAR2(14));
# 2
UPDATE employees e
SET department_name = (
SELECT department_name
FROM departments d
WHERE e.department_id = d.department_id
);

3.4.5 相关删除

  • 使用相关子查询依据一个表中的数据删除另一个表的数据。
DELETE FROM table1 alias1
WHERE column operator (
SELECT expression
FROM table2 alias2
WHERE alias1.column = alias2.column
);
-- 题目:删除表employees中,其与emp_history表皆有的数据
DELETE FROM employees e
WHERE employee_id in (
SELECT employee_id
FROM emp_history
WHERE employee_id = e.employee_id
);
posted @   浴火成诗  阅读(106)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· 上周热点回顾(2.17-2.23)
点击右上角即可分享
微信分享提示

目录在这里