数据库圣经--聚合查询子查询分组查询合并查询

Han.miracle

发布于 2025-12-23 09:56:48

2230

文章被收录于专栏：Java核心技术图谱：原理·对比·避坑Java核心技术图谱：原理·对比·避坑

数据库初始化操作操作流程及作用

截图中的命令是 MySQL 命令行下的数据库初始化步骤：

切换数据库命令：use test_002作用：将当前操作的数据库切换为test_002，提示 “Database changed” 表示切换成功。
导入初始化 SQL 文件命令：\. d:\database\student.sql作用：通过 MySQL 的source命令（简写为\.），导入指定路径下的student.sql文件 —— 该文件通常包含表结构创建语句、初始数据插入语句，用于快速初始化数据库。
查看当前库的表命令：show tables作用：查询test_002库下的所有表，结果显示该库已生成 4 张表。

一·聚合查询

核心特点

运算对象：针对数据表中多行数据进行运算（区别于 “表达式查询”—— 仅对同一行内的列进行运算，如 “语文成绩 + 数学成绩”）。
运算范围：所有聚合函数均针对某一列数据执行统计操作。

NULL 的影响：除COUNT(*)外，所有聚合函数都会自动忽略列值为NULL的行；
DISTINCT 的适用场景：仅当需要 “去重后统计” 时使用（如统计班级中出现过的不同分数数量）；
使用场景：聚合函数常配合GROUP BY（分组统计）、HAVING（分组后过滤）使用，用于数据统计分析（如班级平均分、月度销售总额等）。

函数：

1.COUNT 函数格式：COUNT([DISTINCT] expr)作用：统计查询结果中符合条件的记录行数。注意：①使用COUNT(*)时，会统计所有行（包含字段值为 NULL 的行）；②使用COUNT(列名)时，仅统计该列中值不为 NULL 的行数；③添加DISTINCT关键字，会统计该列中不重复的非 NULL 值对应的行数。

2.SUM 函数格式：SUM([DISTINCT] expr)作用：计算指定列的数值总和（仅对数字类型的列有效，非数字列无运算意义）。

注意：添加DISTINCT关键字时，会先对该列的值去重，再计算去重后的数值总和。

3.AVG 函数格式：AVG([DISTINCT] expr)作用：计算指定列的平均值（仅对数字类型的列有效，非数字列无运算意义）。注意：①计算逻辑为 “该列数值总和 ÷ 该列非 NULL 值的行数”（自动忽略 NULL 值）；②添加DISTINCT关键字时，会先对列值去重，再基于去重后的数据计算平均值。

4.MAX 函数格式：MAX([DISTINCT] expr)作用：获取指定列的最大值。注意：①支持数字、字符串、日期等类型（不同类型按对应规则比较，如字符串按字典序）；②添加DISTINCT关键字不影响结果（最大值具有唯一性）。

5.MIN 函数格式：MIN([DISTINCT] expr)作用：获取指定列的最小值。注意：①支持的类型及比较规则与 MAX 函数一致；②添加DISTINCT关键字不影响结果（最小值具有唯一性）。

二·分组查询

语法：Group by

示例：

问题：对分组之后的结果进行过滤，比如说，找出平均工资大于1万的角色

Having

先执行WHERE过滤行 → 再执行GROUP BY分组 → 最后执行HAVING过滤分组结果

具体执行流程

WHERE：过滤原始表的行先对表中每一行的真实数据进行条件判断，只保留符合WHERE条件的行（比如 “薪资≥5000 的员工”），过滤后的数据才会进入分组环节。
GROUP BY：对过滤后的行分组基于GROUP BY指定的字段（比如 “部门 ID”），将WHERE过滤后的行拆分为多个分组。
HAVING：过滤分组后的结果对GROUP BY计算出的分组结果（比如 “每个部门的员工数”）进行条件判断，只保留符合HAVING条件的分组（比如 “员工数≥2 的部门”）。

示例：

三.子查询

子查询是把⼀个SELECT语句的结果当做别一个SELECT语句的条件，也叫嵌套查询

实际开发中慎用！！！

1、语法

 select * from table1 where col_name1 {= | IN} (
     select col_name1  from table2 where col_name2 {= | IN} [(
         select ...)
     ] ...
 )

常用的

2、单行子查询

嵌套的查询中只返回⼀行数据

（1）示例：查询与"不想毕业"同学的同班同学

select * from student where class_id = (select class_id from student where name = '不想毕业');

3、多行子查询嵌套的查询中返回多行数据，使用[NOT]IN关键字

（1）示例：查询"MySQL"或"Java"课程的成绩信息

select * from score where course_id in (select id from course where name = 'Java' or name = 'MySQL');

# 使⽤NOT IN 可以查询除了"MySQL"或"Java"课程的成绩
select * from score where course_id not in (select id from course where name = 'Java' or name = 'MySQL');

4、多列子查询

单行子查询和多行子查询都只返回⼀列数据，多列子查询中可以返回多个列的数据，外层查询与嵌套的内层查询的列要匹配

（1）示例：查询重复录入的分数

# 插⼊重复的分数：score, student_id, course_id列重复
insert into score(score, student_id, course_id) values (70.5, 1, 1),(98.5, 1, 3),(60, 2, 1);

# ⼦查询中返回多个列

SELECT * FROM score WHERE (score, student_id, course_id ) IN ( 
    SELECT score,student_id,
    course_id FROM score GROUP BY score, student_id, course_id HAVING
    count( 0 ) > 1 
);

5、在from子句中使用子查询当⼀个查询产生结果时，MySQL自动创建⼀个临时表，然后把结果集放在这个临时表中，最终返回给用户，在from子句中也可以使用临时表进行子查询或表连接操作

（1）示例：查询所有比"Java001班"平均分高的成绩信息

# ⾸先分步进⾏，第⼀步先查出Java001班的平均分
select avg(sc.score) score from student s 
    join class c on s.class_id = c.id 
    join score sc on s.id = sc.student_id
where 
    c.name = 'Java001班'

# 把以上查询做为临时表，与真实表进⾏⽐较

select * from score s, (
    select avg(sc.score) score from student s 
    join class c on s.class_id = c.id 
    join score sc on s.id = sc.student_id
where 
    c.name = 'Java001班') tmp 
    where s.score > tmp.score;

tmp是临时表的别名

四、合并查询

在实际应用中，为了合并多个select操作返回的结果，可以使用集合操作符union，unionall

1、创建新表并初始化数据
# 创建⼀个新表并初始化数据
create table student1 like student;
 
insert into student1 (name, sno, age, gender, enroll_date, class_id) values 
('唐三藏', '100001', 18, 1, '1986-09-01', 1),
('刘备', '300001', 18, 1, '1993-09-01', 3),
('张⻜', '300002', 18, 1, '1993-09-01', 3),
('关⽻', '300003', 18, 1, '1993-09-01', 3);
 
select * from student1;

1、Union

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。

（1）示例：查询student表中id<3的同学和student1表中的所有同学

# 结果集中有两张表中的数据，但是唐三藏只返回了⼀条记录
select * from student where id < 3 union select * from student1;

2、Union all

该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。

# 结果集中有两张表中的数据，返回了所有唐三藏的记录
select * from student where id < 3 union all select * from student1;

五、插入查询结果

1、语法

INSERT INTO table_name [(column [, column ...])] SELECT ...

2、示例（1）将student表中C++001班的学生复制到student1表中

insert into student1 (name, sno, age, gender, enroll_date, class_id)
    select s.name, s.sno, s.age, s.gender, s.enroll_date, s.class_id
    from student s, class c where s.class_id = c.id and c.name = 'C++001班';
 
select * from student1;

六.详细的子查询

一、子查询的核心分类逻辑

子查询的本质是「嵌套在主查询中的小型查询」，其用法完全由两个因素决定：

结果形态：子查询返回的数据结构（单行单列 / 多行单列 / 多行多列等）；
嵌套位置：子查询在主查询中的位置（WHERE/FROM/SELECT 等）。

下面先按「结果形态」划分核心类型（最易理解），再补充「特殊嵌套位置」的子查询，最后附性能优化和避坑指南。

二、按「结果形态」划分的核心子查询类型

类型 1：标量子查询（Scalar Subquery）

定义

返回 单行单列 的结果（仅 1 个值，如 “研发部平均薪资 = 6000”），是最基础的子查询类型。

适用场景

作为主查询的「条件值」或「字段值」，配合比较运算符（=、>、<、≥、≤、≠）使用。

常用嵌套位置

WHERE、SELECT、HAVING 子句中。

示例 1：嵌套在 WHERE 中（条件判断）

需求：查询「薪资高于研发部平均薪资」的员工

SELECT emp_name, salary, dept_id
FROM employees
WHERE salary > (
  -- 子查询：返回研发部的平均薪资（单行单列，如6000）
  SELECT AVG(salary)
  FROM employees
  WHERE dept_id = (
    -- 子查询嵌套：先查研发部的dept_id（单行单列，如10）
    SELECT dept_id FROM departments WHERE dept_name = '研发部'
  )
);

示例 2：嵌套在 SELECT 中（字段值补充）

需求：查询每个员工的姓名、薪资，以及所属部门的名称

SELECT
  emp_name AS 员工姓名,
  salary AS 薪资,
  -- 子查询：按员工dept_id查部门名称（单行单列，如“研发部”）
  (SELECT dept_name 
   FROM departments d 
   WHERE d.dept_id = e.dept_id) AS 部门名称
FROM employees e;

示例 3：嵌套在 HAVING 中（分组过滤条件）

需求：查询「员工数高于公司平均部门人数」的部门

SELECT dept_id, COUNT(emp_id) AS emp_count
FROM employees
GROUP BY dept_id
HAVING COUNT(emp_id) > (
  -- 子查询1：先统计各部门人数（表子查询）
  SELECT AVG(temp.emp_count)
  FROM (
    -- 子查询2：返回各部门人数（多行单列）
    SELECT COUNT(emp_id) AS emp_count
    FROM employees
    GROUP BY dept_id
  ) AS temp  -- 表子查询必须起别名
);

注意事项

子查询必须返回「单行单列」，若返回多行 / 多列会直接报错；
支持多层嵌套（如示例 1 中 “子查询套子查询”），但建议控制在 2-3 层（性能更佳）。

类型 2：列子查询（Column Subquery）

定义

返回 多行单列 的结果（1 列多个值，如 “所有部门的 ID：10、20、30”）。

适用场景

作为主查询的「条件集合」，配合集合运算符使用。

常用运算符

IN、NOT IN、ANY（=SOME）、ALL

常用嵌套位置

WHERE 子句中。

示例 1：IN + 列子查询（匹配集合中的任意值）

需求：查询「属于研发部或销售部」的员工（子查询返回两个部门的 ID）

SELECT emp_name, dept_id
FROM employees
WHERE dept_id IN (
  -- 子查询：返回研发部、销售部的dept_id（多行单列：10、20）
  SELECT dept_id
  FROM departments
  WHERE dept_name IN ('研发部', '销售部')
);

示例 2：NOT IN + 列子查询（不匹配集合中的任何值）

需求：查询「不在研发部、销售部、人事部」的员

SELECT emp_name, dept_id
FROM employees
WHERE dept_id NOT IN (
  -- 子查询：返回3个部门的ID（多行单列：10、20、30）
  SELECT dept_id
  FROM departments
  WHERE dept_name IN ('研发部', '销售部', '人事部')
);

示例 3：ANY + 列子查询（匹配集合中任意一个满足条件的值）

ANY 含义：字段值 >/</= 子查询结果中的「任意一个值」即成立（ANY可替换为SOME，作用完全相同）。

需求：查询「薪资高于研发部任意一名员工薪资」的员工（只要比研发部最低薪资高即可）

SELECT emp_name, salary
FROM employees
WHERE salary > ANY (
  -- 子查询：返回研发部所有员工的薪资（多行单列：5000、6000、7000）
  SELECT salary
  FROM employees
  WHERE dept_id = (SELECT dept_id FROM departments WHERE dept_name='研发部')
);

👉 逻辑：若研发部薪资为 5000、6000、7000，只要员工薪资 > 5000（任意一个值），就会被选中。

示例 4：ALL + 列子查询（匹配集合中所有值）

ALL 含义：字段值 >/</= 子查询结果中的「所有值」才成立。

需求：查询「薪资高于销售部所有员工薪资」的员工（比销售部最高薪资还高）

SELECT emp_name, salary
FROM employees
WHERE salary > ALL (
  -- 子查询：返回销售部所有员工的薪资（多行单列：5500、6500、6000）
  SELECT salary
  FROM employees
  WHERE dept_id = (SELECT dept_id FROM departments WHERE dept_name='销售部')
);

👉 逻辑：若销售部最高薪资为 6500，只有员工薪资 > 6500 才会被选中。

注意事项

NOT IN 需谨慎使用：若子查询结果中包含 NULL，NOT IN 会返回空结果（因为NULL无法比较）；
ANY/ALL 仅支持「比较运算符」（>、< 等），不支持=（需用IN）。

类型 3：行子查询（Row Subquery）

定义

返回 单行多列 的结果（1 行多个值，如 “员工编号 = 1 的薪资和部门 ID：5000、10”）。

适用场景

主查询需要同时匹配多个字段的条件（如 “薪资和部门 ID 同时等于某个人的信息”）。

常用运算符

=、IN（部分数据库支持≠、NOT IN）

常用嵌套位置

WHERE 子句中。

示例 1：匹配单行多列的条件

需求：查询「薪资和部门 ID 与 “张三” 完全一致」的员工（可能有同名同薪资同部门的人）

SELECT emp_name, salary, dept_id
FROM employees
WHERE (salary, dept_id) = (
  -- 子查询：返回张三的薪资和部门ID（单行多列：5000、10）
  SELECT salary, dept_id
  FROM employees
  WHERE emp_name = '张三'
);

示例 2：IN + 行子查询（匹配多个单行多列结果）

需求：查询「薪资和部门 ID 与 “张三” 或 “李四” 一致」的员工

SELECT emp_name, salary, dept_id
FROM employees
WHERE (salary, dept_id) IN (
  -- 子查询：返回张三、李四的薪资和部门ID（多行多列？不，是“多个单行多列”组合）
  SELECT salary, dept_id FROM employees WHERE emp_name = '张三'
  UNION ALL  -- 合并两个单行多列结果
  SELECT salary, dept_id FROM employees WHERE emp_name = '李四'
);

注意事项

主查询的字段数量、顺序必须与子查询完全一致（如示例 1 中(salary, dept_id)对应子查询的(salary, dept_id)）；
兼容性：MySQL、Oracle、PostgreSQL 均支持，但 SQL Server 低版本可能需用其他方式替代。

类型 4：表子查询（Table Subquery）

定义

返回 多行多列 的结果（本质是一张「临时表」，如 “各部门员工数统计”）。

适用场景

作为主查询的「数据源」，主查询从临时表中筛选、聚合数据。

常用嵌套位置

FROM 子句中（必须给临时表起别名）。

示例 1：基础表子查询（临时表筛选）

需求：先统计各部门的平均薪资，再筛选「平均薪资≥6000」的部门

SELECT dept_id, avg_salary, emp_count
FROM (
  -- 子查询：生成临时表（多行多列），包含部门ID、平均薪资、员工数
  SELECT
    dept_id,
    AVG(salary) AS avg_salary,
    COUNT(emp_id) AS emp_count
  FROM employees
  GROUP BY dept_id
) AS temp_dept  -- 表子查询必须起别名（否则报错）
WHERE avg_salary ≥ 6000;  -- 主查询过滤临时表

示例 2：表子查询 + 连接查询（复杂统计）

需求：关联部门表，查询「平均薪资≥6000」的部门名称和员工数

SELECT
  d.dept_name AS 部门名称,
  temp.avg_salary AS 平均薪资,
  temp.emp_count AS 员工数
FROM (
  -- 子查询：统计各部门平均薪资和员工数（临时表）
  SELECT dept_id, AVG(salary) AS avg_salary, COUNT(emp_id) AS emp_count
  FROM employees
  GROUP BY dept_id
) AS temp
-- 关联部门表获取部门名称
INNER JOIN departments d
  ON temp.dept_id = d.dept_id
WHERE temp.avg_salary ≥ 6000;

注意事项

必须给临时表起别名（如temp_dept）：数据库无法识别无别名的临时表；
临时表的字段可以被主查询直接使用（如avg_salary），也可参与连接、聚合；
若临时表数据量大，建议添加索引（仅临时生效），提升查询效率。

类型 5：存在性子查询（Existential Subquery）

定义

不关注子查询返回的具体数据，只关注「子查询是否有结果返回」（返回布尔值：TRUE/FALSE）。

核心逻辑

EXISTS：子查询有至少 1 条记录 → 主查询保留当前行；
NOT EXISTS：子查询无记录 → 主查询保留当前行。

常用嵌套位置

WHERE 子句中（常与关联字段配合，实现 “存在关联记录” 的判断）。

示例 1：EXISTS（存在关联记录）

需求：查询「存在至少 1 名员工」的部门（排除无员工的空部门）

SELECT dept_name, dept_id
FROM departments d
WHERE EXISTS (
  -- 子查询：判断部门d是否有员工（只要有1条记录就返回TRUE）
  SELECT 1  -- 此处写SELECT *、SELECT 字段均可，写SELECT 1效率最高（无需读取字段值）
  FROM employees e
  WHERE e.dept_id = d.dept_id  -- 关联主查询的部门ID
);

示例 2：NOT EXISTS（无关联记录）

需求：查询「没有员工」的部门（空部门）

SELECT dept_name, dept_id
FROM departments d
WHERE NOT EXISTS (
  SELECT 1
  FROM employees e
  WHERE e.dept_id = d.dept_id
);

示例 3：EXISTS + 条件过滤

需求：查询「存在薪资≥8000 的员工」的部门

SELECT dept_name, dept_id
FROM departments d
WHERE EXISTS (
  SELECT 1
  FROM employees e
  WHERE e.dept_id = d.dept_id
    AND e.salary ≥ 8000  -- 子查询中添加过滤条件
);

注意事项

效率优势：EXISTS 是 “短路查询”—— 子查询找到 1 条记录就停止执行，比 IN 更适合大数据量场景；
关联字段：子查询中需用主查询的字段（如d.dept_id）建立关联，否则会变成 “全局存在判断”；
SELECT 1 优化：无需查询具体字段，仅判断 “是否有记录”，效率高于SELECT *。

三、按「嵌套位置」划分的特殊子查询

除了上述核心类型，子查询还可嵌套在 INSERT/UPDATE/DELETE（DML 操作）中，作为数据操作的依据，这是实际开发中高频使用的场景。

1. 嵌套在 INSERT 中（插入子查询结果）

定义

将子查询的结果直接插入到目标表中（批量插入数据）。

示例

需求：将「销售部（dept_id=20）的员工」复制到临时员工表temp_emp

-- 先创建临时表（若已存在可省略）
CREATE TABLE temp_emp (
  emp_name VARCHAR(50),
  salary INT,
  dept_id INT
);

-- 插入子查询结果
INSERT INTO temp_emp (emp_name, salary, dept_id)
SELECT emp_name, salary, dept_id  -- 子查询返回多行多列
FROM employees
WHERE dept_id = (
  SELECT dept_id FROM departments WHERE dept_name = '销售部'
);

2. 嵌套在 UPDATE 中（按子查询条件更新）

定义

用子查询的结果作为更新条件，或更新字段的值。

示例 1：按子查询条件更新

需求：给「员工数最多的部门」的所有员工涨薪 10%

UPDATE employees
SET salary = salary * 1.1
WHERE dept_id = (
  -- 子查询：查询员工数最多的部门ID
  SELECT dept_id
  FROM (
    SELECT dept_id, COUNT(emp_id) AS emp_count
    FROM employees
    GROUP BY dept_id
    ORDER BY emp_count DESC
    LIMIT 1  -- 取员工数最多的1个部门
  ) AS temp
);

示例 2：用子查询结果更新字段

需求：将「无部门员工（dept_id=NULL）」的部门 ID 更新为「人事部（dept_id=30）」

UPDATE employees
SET dept_id = (
  SELECT dept_id FROM departments WHERE dept_name = '人事部'
)
WHERE dept_id IS NULL;

3. 嵌套在 DELETE 中（按子查询条件删除）

定义

用子查询的结果作为删除条件，批量删除数据。

示例

需求：删除「属于空部门（无对应部门记录）」的员工

DELETE FROM employees
WHERE dept_id NOT IN (
  -- 子查询：返回所有有效部门的ID
  SELECT dept_id FROM departments
);

四、子查询的进阶用法：关联子查询 vs 非关联子查询

之前的示例中包含两种隐性逻辑，这是子查询的进阶分类，决定了查询效率：

1. 非关联子查询（Non-correlated Subquery）

定义

子查询可以独立运行，不需要依赖主查询的任何字段（运行顺序：先执行子查询，再执行主查询）。

示例（非关联子查询）

SELECT emp_name FROM employees
WHERE dept_id IN (
  SELECT dept_id FROM departments WHERE dept_name='研发部'  -- 无需主查询字段，可独立运行
);

2. 关联子查询（Correlated Subquery）

定义

子查询依赖主查询的字段（如d.dept_id），无法独立运行（运行顺序：主查询每一行都触发一次子查询）。

示例（关联子查询）

SELECT dept_name FROM departments d
WHERE EXISTS (
  SELECT 1 FROM employees e
  WHERE e.dept_id = d.dept_id  -- 依赖主查询的d.dept_id，无法独立运行
);

关键区别

优化建议

关联子查询尽量用 EXISTS 替代 IN；

大数据量时，可将关联子查询转为「表连接」（效率更高），如：

-- 关联子查询（EXISTS）
SELECT dept_name FROM departments d WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id=d.dept_id);

-- 等价表连接（效率更高）
SELECT DISTINCT d.dept_name FROM departments d INNER JOIN employees e ON d.dept_id=e.dept_id;

五、子查询的避坑指南（高频错误）

表子查询忘记起别名：FROM (子查询) AS 别名 是强制要求，否则报错；
标量子查询返回多行：如 WHERE salary = (SELECT salary FROM employees WHERE dept_id=10)，若子查询返回多个薪资值，会报错；
NOT IN 遇到 NULL：子查询结果含 NULL 时，NOT IN 会返回空结果（如 WHERE dept_id NOT IN (10, 20, NULL)）；
关联子查询嵌套过深：超过 3 层嵌套会导致性能急剧下降，尽量拆分为表连接；
SELECT 子查询返回多行多列：SELECT 中的子查询只能是标量子查询（单行单列），否则报错。

六、总结：子查询的核心用法图谱

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2025-12-09，如有侵权请联系 cloudcommunity@tencent.com 删除

统计

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！