SQL-查询

魔镜魔镜_谁是世界上最漂亮的小仙女
461 阅读22分钟

【SQL 教程】基础查询入门：从表数据查询到连接测试全解析

一、SELECT语句基础用法

2.1 查询表中所有数据

语法格式：

SELECT * FROM <表名>;
  • SELECT：关键字，标识查询操作
  • *：通配符，表示查询所有列
  • FROM：指定数据来源表

示例演示：

-- 查询学生表所有数据
SELECT * FROM students;

-- 查询班级表所有数据
SELECT * FROM classes;

执行结果：
返回包含列名和数据的二维表结构，例如classes表查询结果：

id,name
1,一班
2,二班
3,三班
4,四班

2.2 不带FROM子句的特殊用法

场景 1：表达式计算
-- 直接计算数值表达式
SELECT 100 + 200;

结果：

100 + 200
300
场景 2：数据库连接测试

核心用途：检测工具通过执行SELECT 1;验证数据库连接有效性

-- 经典连接测试语句
SELECT 1;

原理：无需操作具体表，仅验证数据库服务响应能力

三、关键概念总结

知识点说明
查询本质SELECT语句返回的结果是一个二维表，包含列定义和行数据
通配符*代表表中所有列，生产环境建议明确指定列名以提升性能
无表查询利用SELECT直接计算或测试连接，是 SQL 灵活性的体现

四、开发建议

  1. 避免滥用SELECT * ：明确列出所需列可减少数据传输量，提升查询效率
  2. 连接测试最佳实践：使用轻量语句SELECT 1而非复杂查询，降低资源消耗
  3. 养成注释习惯：如示例中通过--添加注释，增强 SQL 可读性

SQL 进阶之路：WHERE 条件查询全解析，轻松筛选有效数据

前言

在关系型数据库中，精准筛选数据是高频需求。

一、基础语法：从全表到精准筛选

核心作用

通过WHERE子句过滤出符合条件的记录，避免返回全表数据，提升查询效率。

语法结构：

SELECT 字段列表 FROM 表名 WHERE 条件表达式;

示例：查询高分学生

需求：获取分数≥80 分的学生记录

SELECT * FROM students WHERE score >= 80;

执行结果：

id,class_id,name,gender,score
1,1,小明,M,90
2,1,小红,F,95
3,1,小军,M,88
...

二、逻辑运算符：组合条件的核心

1. AND（逻辑与）

作用：同时满足多个条件
示例：查询分数≥80 分的男生

SELECT * FROM students 
WHERE score >= 80 AND gender = 'M';

2. OR（逻辑或）

作用：满足任意一个条件
示例：查询分数≥80 分或性别为男的学生

SELECT * FROM students 
WHERE score >= 80 OR gender = 'M';

3. NOT（逻辑非）

作用：排除符合条件的记录
示例：查询非 2 班的学生（等价于class_id <> 2）

SELECT * FROM students 
WHERE NOT class_id = 2;

三、条件表达式：丰富的筛选维度

条件类型表达式示例说明
等值判断score = 80字符串需用单引号（如name = '小明'）
范围判断score > 90支持>、<、>=、<=
不等值判断score <> 60等价于!=
模糊查询name LIKE '小%'%匹配任意字符，_匹配单个字符（如'_明'匹配 “小明”“A 明”）
区间查询score BETWEEN 60 AND 90闭区间，等价于score >=60 AND score <=90
枚举查询class_id IN (1, 3)匹配列表中的任意值，等价于多个OR组合

四、优先级与括号：避免逻辑歧义

运算符优先级（从高到低）

  1. NOT
  2. AND
  3. OR

括号改变优先级

示例：查询分数 <80 或> 90 的男生

-- 正确写法（先算括号内逻辑）
SELECT * FROM students 
WHERE (score < 80 OR score > 90) AND gender = 'M';

-- 错误写法（实际等价于 score <80 OR (score>90 AND gender='M')）
SELECT * FROM students 
WHERE score < 80 OR score > 90 AND gender = 'M';

五、实战演练：经典场景解析

场景 1：查询 60-90 分之间的学生

正确写法：

-- 方式1：AND组合
SELECT * FROM students 
WHERE score >= 60 AND score <= 90;

-- 方式2：BETWEEN（更简洁）
SELECT * FROM students 
WHERE score BETWEEN 60 AND 90;

场景 2：查询姓名含 “小” 的女生

SELECT * FROM students 
WHERE name LIKE '%小%' AND gender = 'F';

六、注意事项

  1. 字符串单引号：文本值必须用单引号包裹（如gender = 'M'），数值型无需引号
  2. NULL 值处理：判断空值用IS NULL（如comment IS NULL），不能用=
  3. 性能优化：对查询条件字段添加索引（如score、name），提升大数据量查询速度

【SQL 教程】投影查询：精准筛选列数据的核心技巧

一、什么是投影查询？

定义：通过SELECT语句指定需要查询的列，而非返回表的所有列（SELECT *），这种操作称为投影查询。
核心作用：

  • 减少返回数据量，提升查询效率；
  • 聚焦业务所需字段，简化结果集结构。

基础语法：

SELECT 列1, 列2, 列3 FROM 表名;

示例：从students表中查询id、score、name三列：

SELECT id, score, name FROM students;

二、给列起别名：优化结果可读性

通过别名（Alias）可以自定义结果集的列名，语法如下：

SELECT 列名 别名, 列名 AS 别名 FROM 表名;

说明：

  • AS关键字可选，省略时直接写别名（如score points）；
  • 别名建议见名知意，方便后续处理（如报表展示）。

示例：将score列重命名为points：

SELECT id, score AS points, name FROM students;

三、投影查询 + WHERE 条件：精准过滤数据

投影查询可与WHERE子句结合，实现 “列筛选 + 行过滤” 的双重条件查询。
语法：

SELECT 列1, 列2 FROM 表名 WHERE 条件;

示例：查询男生的id、score（别名points）和name：

SELECT id, score points, name FROM students WHERE gender = 'M';

结果集：

id,points,name
1,90,小明
3,88,小军
...

四、实战注意事项（来自评论区经验）

  1. AS 关键字是否必须？

    • 标准 SQL 中AS可选，但显式使用更易读（如score AS points），推荐保留。

  2. 中文别名如何处理？

    • 部分数据库（如 MySQL）支持中文别名，需用反引号`包裹：

      SELECT id `编号`, name `姓名` FROM students;

    • 若出现乱码，需检查表 / 字段的字符集设置（如 UTF-8）。

  3. 别名能否用于 WHERE 子句？

    • 不能。别名是SELECT阶段生成的结果集标识，WHERE执行时别名尚未生效。
    • 若需过滤别名数据，需用原列名或嵌套子查询。

五、小结

核心知识点说明
投影查询用SELECT 列1, 列2替代SELECT *，仅返回指定列
列别名用AS或直接指定别名，优化结果集列名可读性
条件组合结合WHERE子句，实现列与行的双重筛选
最佳实践显式使用AS增强可读性，中文别名用反引号包裹，避免在WHERE中使用别名

SQL 进阶之路：如何用 ORDER BY 让查询结果 “井然有序”？

在 SQL 查询中，原始结果集的顺序往往由数据库引擎决定（通常按主键排序），但实际开发中我们经常需要自定义排序规则。本文将结合实例，详解ORDER BY子句的核心用法，让你的查询结果 “按需排列”。

一、基础排序：按单列升序排列

核心语法

SELECT 列1, 列2,...
FROM 表名
ORDER BY 排序列; -- 默认为升序（ASC）

实战案例

查询学生表，按成绩从低到高排序：

SELECT id, name, gender, score 
FROM students 
ORDER BY score;
执行结果（部分）：
idnamegenderscore
6小兵M55
4小米F73
5小白F81
............

二、倒序排列：用 DESC 实现降序

核心语法

SELECT 列1, 列2,...
FROM 表名
ORDER BY 排序列 DESC; -- DESC表示倒序

实战案例

按成绩从高到低排序：

SELECT id, name, gender, score 
FROM students 
ORDER BY score DESC;
执行结果（部分）：
idnamegenderscore
2小红F95
8小新F91
1小明M90
............

三、多列排序：复杂场景下的精准控制

当排序列存在重复值时，可通过多列排序进一步细化规则。

核心语法

SELECT 列1, 列2,...
FROM 表名
ORDER BY 排序列1 [ASC/DESC], 排序列2 [ASC/DESC],...;

实战案例

先按成绩降序，若成绩相同则按性别升序（F 在前，M 在后）：

SELECT id, name, gender, score 
FROM students 
ORDER BY score DESC, gender;
执行结果（部分）：
idnamegenderscore
2小红F95-- 成绩最高，性别 F 优先
8小新F91-- 同成绩下 F 在前
1小明M90-- 同成绩下 M 在后
............

四、关键细节：默认规则与执行顺序

  1. 默认排序规则

    • 未指定ASC/DESC时，默认使用ASC（升序）。
    • 例：ORDER BY score 等价于 ORDER BY score ASC。

  2. 与 WHERE 子句的配合

    • 执行顺序：先过滤（WHERE），再排序（ORDER BY）。

    • 案例：查询一班学生，按成绩降序排列：

      SELECT id, name, gender, score 
FROM students 
WHERE class_id = 1  -- 先过滤班级
ORDER BY score DESC; -- 再排序

五、最佳实践建议

  1. 索引优化

    • 对排序列添加索引（如score），可显著提升排序性能。
    • 多列排序时，索引顺序应与ORDER BY列顺序一致（如(score DESC, gender)）。

  2. 避免 SELECT

    • 仅查询必要字段，减少排序数据量，提升效率。

  3. 谨慎使用表达式排序

    • 若需按计算结果排序（如ORDER BY price * quantity），建议先通过AS命名别名：

      SELECT id, price, quantity, price*quantity AS total
FROM orders
ORDER BY total DESC;

SQL 分页查询全解析：LIMIT/OFFSET 用法与性能优化指南

一、引言：为什么需要分页查询？

在数据量较大的场景下（如数万条记录），一次性返回所有数据会导致：

  • 前端渲染性能下降
  • 网络传输效率低下
  • 用户体验差

分页查询通过 "结果集切片" 技术，实现数据的分批展示，典型应用场景包括：

  • 网站列表分页（如商品列表、文章列表）
  • API 接口数据分页（如分页加载更多）
  • 大数据量报表的分段展示

二、核心语法：LIMIT/OFFSET 的使用

2.1 基础语法结构

SELECT column1, column2,...
FROM table_name
[WHERE condition]
ORDER BY column [ASC/DESC]
LIMIT offset, limit;
-- 或
LIMIT limit OFFSET offset;
  • LIMIT：指定返回记录的最大数量（必选）
  • OFFSET：指定查询结果的起始偏移量（可选，默认 0）

2.2 分页查询示例（每页 3 条记录）

表数据排序（按成绩降序）：

SELECT id, name, gender, score 
FROM students 
ORDER BY score DESC;

第 1 页（偏移 0，取前 3 条） ：

SELECT ... LIMIT 3 OFFSET 0;
-- 或简写：LIMIT 0, 3;

执行结果：

id,name,gender,score
2,小红,F,95
8,小新,F,91
1,小明,M,90

第 2 页（偏移 3，取第 4-6 条） ：

SELECT ... LIMIT 3 OFFSET 3;
-- 简写：LIMIT 3, 3;

执行结果：

id,name,gender,score
9,小王,M,89
3,小军,M,88
10,小丽,F,88

2.3 分页参数计算公式

设：

  • pageSize：每页显示记录数（如 3）

  • pageIndex：当前页码（从 1 开始）

则：

  • LIMIT = pageSize

  • OFFSET = (pageIndex - 1) * pageSize

示例：第 4 页参数计算

pageSize = 3, pageIndex = 4
OFFSET = (4-1)*3 = 9
-- 查询语句：LIMIT 3 OFFSET 9;

三、关键注意事项

3.1 OFFSET 的可选性

  • 当OFFSET=0时可省略，直接写LIMIT 10（等价于LIMIT 10 OFFSET 0）

3.2 MySQL 的简写语法

LIMIT offset, limit; -- 等价于 LIMIT limit OFFSET offset;

注意：第一个参数是偏移量，第二个是数量

3.3 大偏移量的性能问题

  • 当OFFSET值较大时（如OFFSET 100000），数据库需先扫描前 100000 条记录再丢弃，导致性能下降
  • 优化建议：避免深度分页（如超过 1000 页），或采用索引优化（见下文）

四、性能优化技巧

4.1 基于索引的分页优化

场景：当数据按自增主键（如 id）排序时，可利用索引快速定位起始点

优化方法：

  1. 第一页查询：
SELECT * FROM students 
WHERE id >= 0 
ORDER BY id ASC 
LIMIT 101; -- 多取1条判断是否有下一页
  1. 下一页查询（用上一页最后一条 id 作为起始点）：
SELECT * FROM students 
WHERE id >= last_id 
ORDER BY id ASC 
LIMIT 101;
  • 若返回记录数 = 101：存在下一页，取前 100 条，记录最后一条 id
  • 若返回记录数 < 101：最后一页

优势：避免使用大OFFSET，直接通过索引定位起始行

4.2 避免 SELECT *

  • 只查询需要的字段，减少数据传输量
-- 优化前
SELECT * FROM students ...

-- 优化后
SELECT id, name, score FROM students ...

五、总页数计算方法

5.1 精确计算法（适用于需要显示总页数的场景）

  1. 获取总记录数：
SELECT COUNT(*) AS total_records FROM students;
  1. 计算总页数（使用 CEIL 函数向上取整）：
SET @page_size = 10;
SELECT CEIL(total_records / @page_size) AS total_pages;

示例：总记录数 123，每页 10 条 → 总页数 13

5.2 注意事项

  • 避免使用 COUNT (id) ：若存在主键缺失（如删除记录），会导致结果不准确
  • 推荐使用 COUNT (*) : 统计所有行，性能更优

5.3 非精确计算法（适用于无限滚动场景）

  • 不计算总页数，通过前端 "加载更多" 按钮动态获取下一页
  • 后台逻辑：每次查询多取 1 条，判断是否还有更多数据

六、常见问题与讨论

6.1 主键不连续问题

  • 自增主键可能存在断号（如删除记录后），因此不能通过主键最大值计算总记录数
  • 廖雪峰建议：自增主键仅用于唯一标识，绝不参与业务逻辑

6.2 同分数排序的重复问题

  • 当排序字段存在相同值时（如并列分数），需添加辅助排序字段（如 id）保证唯一性
ORDER BY score DESC, id ASC;

一文搞懂 SQL 聚合查询：从基础函数到分组统计实战

引言

在 SQL 查询中，统计数据是日常开发中高频需求。

一、什么是聚合查询？

聚合查询是通过 SQL 内置的聚合函数，对表中数据进行统计计算的查询方式。
核心作用：

  • 快速统计数据总量、平均值、最值等
  • 结合分组功能，实现多维度数据分析
    与普通查询的区别：
    普通查询返回每行具体数据，而聚合查询返回统计后的单行或分组统计结果。

二、常用聚合函数详解

函数作用示例场景
COUNT()统计行数统计学生总数：COUNT(*)
SUM()计算某列数值总和计算总分：SUM(score)
AVG()计算某列数值平均值计算平均分：AVG(score)
MAX()获取某列最大值（支持数值 / 字符类型）最高成绩：MAX(score)
MIN()获取某列最小值（支持数值 / 字符类型）最低年龄：MIN(age)

2.1 COUNT() 函数深度解析

1. COUNT(*) vs COUNT(列名)

  • COUNT(*) ：统计所有行（包括NULL值）

    SELECT COUNT(*) FROM students; -- 返回总记录数

  • COUNT(列名) ：统计该列非NULL的行数

    SELECT COUNT(name) FROM students; -- 统计有姓名的学生数
2. 结合WHERE条件过滤统计
-- 统计男生人数
SELECT COUNT(*) boys FROM students WHERE gender = 'M';

2.2 数值型聚合函数（SUM/AVG）

-- 计算男生平均成绩
SELECT AVG(score) average FROM students WHERE gender = 'M';

-- 计算班级总分
SELECT SUM(score) total FROM students WHERE class_id = 1;

2.3 最值函数（MAX/MIN）


-- 获取最高成绩
SELECT MAX(score) highest_score FROM students;

-- 获取最晚注册时间（字符类型按排序规则处理）
SELECT MAX(register_time) FROM users;

三、分组查询：GROUP BY 的使用

3.1 单字段分组

需求：统计每个班级的学生人数

SELECT class_id, COUNT(*) num 
FROM students 
GROUP BY class_id;

结果解读：

class_idnum
14
23
33

3.2 多字段分组

需求：统计每个班级男女生人数

SELECT class_id, gender, COUNT(*) num 
FROM students 
GROUP BY class_id, gender;

结果解读：

class_idgendernum
1M2
1F2
2M2
.........

3.3 分组查询注意事项

  1. SELECT 子句限制：

    • 非聚合列必须出现在GROUP BY中（MySQL 默认宽松模式除外，需注意ONLY_FULL_GROUP_BY模式）
    -- 错误示例（name未分组）
SELECT name, class_id, COUNT(*) FROM students GROUP BY class_id;

  2. 分组顺序影响结果：
    多字段分组时，按分组字段顺序层级分组（先按class_id，再按gender）。

四、实战练习

练习 1：查询每个班级的平均分

SELECT class_id, AVG(score) average_score 
FROM students 
GROUP BY class_id;

预期结果：

class_idaverage_score
186.5
273.67
389.33

练习 2：查询每个班级男女生的平均分

SELECT class_id, gender, AVG(score) average_score 
FROM students 
GROUP BY class_id, gender;

预期结果：

class_idgenderaverage_score
1M89.0
1F84.0
2M70.0
.........

五、高级技巧：聚合函数的特殊行为

5.1 空值处理

  • COUNT(*)：返回 0（无匹配行时）
  • SUM/AVG/MAX/MIN：返回NULL（无匹配行时）
-- 无女生的班级，AVG返回NULL
SELECT AVG(score) FROM students WHERE gender = 'X';

5.2 性能优化建议

  • 避免在COUNT()中使用不必要的列（COUNT(*)效率更高）
  • 对分组字段添加索引（提升GROUP BY性能）

六、常见问题与解决方案

6.1 MySQL 中分组查询非聚合列报错

现象：

ERROR 1055: Expression #1 is not in GROUP BY clause...

原因：MySQL 启用了ONLY_FULL_GROUP_BY模式（严格模式）。
解决方案：

  1. 将非聚合列加入GROUP BY

  2. 使用聚合函数包裹（如MAX(name)）

  3. 临时关闭严格模式（不推荐生产环境）：

    SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES';

七、总结

核心知识点回顾：

  1. 聚合函数：COUNT/SUM/AVG/MAX/MIN的用法与场景
  2. 分组查询：GROUP BY单字段 / 多字段分组逻辑
  3. 数据过滤：结合WHERE条件实现精准统计
  4. 数据库差异：MySQL 与标准 SQL 在分组查询中的行为差异

SQL 多表查询深度解析：从笛卡尔积到实战优化

在关系型数据库的查询操作中，多表查询是整合多维度数据的核心技能。

一、笛卡尔积查询：多表查询的基础形态

1. 笛卡尔积的本质

当使用 SELECT * FROM table1, table2 语法执行多表查询时，数据库会返回两张表的笛卡尔积结果：即 table1 每一行与 table2 每一行的全组合。结果集的行数为两表行数的乘积，列数为两表列数之和。

示例代码：

-- 无过滤条件的笛卡尔积查询
SELECT * FROM students, classes;

结果特征（以示例数据为例）：

  • 学生表（10 行）× 班级表（4 行）= 40 行结果
  • 列名冲突问题：两表均包含id和name列，结果集出现重复列名

2. 列别名与表别名的使用

为解决列名冲突问题，可通过投影查询为列设置别名，或为表设置别名简化引用：

（1）列别名优化
SELECT
  students.id AS sid,    -- 学生表ID别名
  students.name,
  classes.id AS cid,     -- 班级表ID别名
  classes.name AS cname
FROM students, classes;
（2）表别名简化
SELECT
  s.id sid,
  s.name,
  c.id cid,
  c.name cname
FROM students s, classes c;  -- 表别名语法：FROM 表名 别名

二、条件过滤：缩小笛卡尔积结果集

通过 WHERE 子句添加过滤条件，可大幅减少笛卡尔积的无效数据。例如，查询男生（gender='M'）所在的 1 班（classes.id=1）数据：

SELECT
  s.id sid,
  s.name,
  c.id cid,
  c.name cname
FROM students s, classes c
WHERE s.gender = 'M' AND c.id = 1;

执行逻辑：

  1. 先生成两表的笛卡尔积（40 行）
  2. 应用 WHERE 条件过滤，仅保留同时满足 gender='M' 和 classes.id=1 的行（5 行）

三、笛卡尔积的局限性与实战陷阱

1. 性能风险：数据爆炸问题

  • 当两表数据量较大时（如各 1 万行），笛卡尔积将生成1 亿行数据，严重消耗数据库资源
  • 典型场景：未加关联条件的多表查询、误将逗号分隔的多表语法当作连接查询使用

2. 业务逻辑错误

笛卡尔积默认不关联表间业务关系（如学生与班级的所属关系）。若未通过外键关联（如 students.class_id = classes.id），结果将包含大量无意义的组合（如学生属于不存在的班级）。

四、正确姿势：基于外键的关联查询

实际开发中，多表查询需通过主键 - 外键关系建立有效关联。以学生 - 班级场景为例，正确做法是通过 class_id 外键关联两表：

SELECT
  s.name 学生姓名,
  c.name 班级名称,
  s.score 成绩
FROM students s, classes c
WHERE s.class_id = c.id;  -- 关键：通过外键建立关联

对比笛卡尔积：

  • 结果集仅包含实际存在的学生 - 班级组合（如示例中 10 行，而非 40 行）
  • 避免无效数据，提升查询效率

五、开发者常见问题与社区讨论

问题 1：笛卡尔积的实际应用场景？

  • 极少直接使用，但可用于：

    • 理解连表查询底层逻辑（如自连接场景）
    • 生成测试数据或枚举组合（需严格控制数据量）

问题 2：如何避免列名冲突？

  • 强制使用 表名.列名 或别名，如 s.id 而非直接写 id
  • 工具层面：使用 SQL 客户端（如 Navicat）的字段别名提示功能

一文搞懂 SQL 连接查询：从内连接到外连接的全面解析

引言

在 SQL 查询中，多表关联是处理复杂业务场景的核心能力。本文将通过实际案例和图示，详细解析内连接（INNER JOIN） 、左外连接（LEFT OUTER JOIN） 、** 右外连接（RIGHT OUTER JOIN）和全外连接（FULL OUTER JOIN）** 的原理与用法，帮助你快速掌握多表数据关联的核心逻辑。

一、为什么需要连接查询？

假设我们有两张表：

  • students表（学生信息）：包含id、name、class_id（班级 ID）等字段

  • classes表（班级信息）：包含id、name（班级名称）等字段

需求：查询学生信息时，需要同时显示对应的班级名称。
此时，仅凭单表查询无法获取classes表中的班级名称，必须通过连接查询将两张表的数据按关联字段（class_id和id）拼接起来。

二、内连接（INNER JOIN）

核心逻辑

  • 仅返回两张表中满足连接条件的行（交集部分）。

  • 语法：

    SELECT 字段列表
FROM 主表 AS 别名
INNER JOIN 关联表 AS 别名
ON 连接条件; -- 例如：主表.外键 = 关联表.主键

示例

查询所有学生及其班级名称（仅显示存在对应班级的学生）：

SELECT 
  s.id, s.name, s.class_id, 
  c.name AS class_name,  -- 别名简化字段名
  s.gender, s.score
FROM students AS s
INNER JOIN classes AS c
ON s.class_id = c.id;

结果：

idnameclass_idclass_namegenderscore
1小明1一班M90
2小红1一班F95
..................

三、外连接（OUTER JOIN）

外连接会保留某张表的全部数据，缺失的关联数据用NULL填充，分为三种类型：

1. 左外连接（LEFT OUTER JOIN）

核心逻辑

  • 保留左表的全部行，右表中无匹配的行用NULL填充。

  • 语法：

    SELECT ...
FROM 左表 AS s
LEFT OUTER JOIN 右表 AS c
ON s.class_id = c.id;
示例

假设新增一名班级 ID 为 5 的学生（classes表中无班级 ID=5 的记录）：

INSERT INTO students (class_id, name, gender, score) 
VALUES (5, '新生', 'M', 88);

查询结果：

SELECT s.id, s.name, s.class_id, c.name AS class_name
FROM students AS s
LEFT OUTER JOIN classes AS c
ON s.class_id = c.id;

结果：

idnameclass_idclass_namegenderscore
..................
11新生5NULLM88-- 左表独有数据，右表字段为 NULL

2. 右外连接（RIGHT OUTER JOIN）

核心逻辑

  • 保留右表的全部行，左表中无匹配的行用NULL填充。

  • 语法：

    SELECT ...
FROM 左表 AS s
RIGHT OUTER JOIN 右表 AS c
ON s.class_id = c.id;
示例

查询所有班级及其学生（包括没有学生的班级 “四班”）：

SELECT s.id, s.name, s.class_id, c.name AS class_name
FROM students AS s
RIGHT OUTER JOIN classes AS c
ON s.class_id = c.id;

结果：

idnameclass_idclass_namegenderscore
..................
NULLNULLNULL四班NULLNULL-- 右表独有数据，左表字段为 NULL

3. 全外连接（FULL OUTER JOIN）

核心逻辑

  • 保留两张表的全部行，无匹配的行用NULL填充（等价于左外连接 + 右外连接的并集）。

  • 注意：MySQL 不直接支持FULL OUTER JOIN，可通过UNION组合左右外连接结果实现。

    SELECT ... FROM A LEFT JOIN B ON ...
UNION
SELECT ... FROM A RIGHT JOIN B ON ...;
示例（假设数据库支持）：
SELECT s.id, s.name, s.class_id, c.name AS class_name
FROM students AS s
FULL OUTER JOIN classes AS c
ON s.class_id = c.id;

结果：
包含左表独有行（班级 ID=5 的学生）和右表独有行（四班），缺失字段为NULL。

四、连接类型对比图

用维恩图直观表示不同连接的结果集范围：

+-----------------+        +-----------------+
|      tableA     |        |      tableB     |
+--------+--------+        +--------+--------+
| id=1   | name=A |        | id=1   | name=X |
| id=2   | name=B |        | id=2   | name=Y |
| id=3   | name=C |        | id=4   | name=Z |
+-----------------+        +-----------------+

- INNER JOIN：仅返回A和B中id相同的行（id=1, 2）
- LEFT JOIN：返回A的全部行（id=1,2,3），B中无匹配的行用NULL填充
- RIGHT JOIN：返回B的全部行（id=1,2,4），A中无匹配的行用NULL填充
- FULL JOIN：返回A和B的全部行（id=1,2,3,4），缺失数据用NULL填充

五、实战技巧

  1. 别名优化：
    用短别名（如s代表students，c代表classes）简化语句，提高可读性。
  2. 连接条件：
    始终使用ON子句指定连接条件（如主表.外键 = 关联表.主键），避免使用WHERE子句导致性能问题。
  3. 优先使用内连接：
    除非需要保留某张表的全部数据，否则优先用内连接，避免返回冗余的NULL行。

六、小结

连接类型核心作用数据保留策略
INNER JOIN查询两张表的交集数据仅保留匹配行
LEFT JOIN保留左表全部数据，补全右表关联数据左表全保留，右表匹配或 NULL
RIGHT JOIN保留右表全部数据，补全左表关联数据右表全保留，左表匹配或 NULL
FULL JOIN保留两张表全部数据
avatar
文章
阅读
粉丝