SQL 核心语法与逻辑学习总结报告

一、 表别名 (Table Aliases)

1. 语法规则

  • 作用：简化代码，区分不同表中的同名字段。
  • 写法：FROM employees e 或 FROM employees AS e（AS 关键字可省略，推荐加上以增强可读性）。
  • 注意：表别名不能用单引号 'e'，只能直接写 e。

2. 常见误区

  • 别名的作用域仅在当前查询语句内。
  • 一旦起了别名，在 SELECT、WHERE、ON 等后续子句中，必须使用别名，不能再写原表名（如 employees.name 会报错，必须写 e.name）。

二、 常量列与 SELECT 基础

1. 核心概念

  • SELECT 后面跟的东西，就是结果表里的列。
  • 这些“列”可以是三种东西：表里真实存在的字段、自己写的常量、现场计算的表达式。
  • 常量列既不是数据库里存好的数据，也不是创建了新表，只是临时计算并显示的结果。

2. 常见误区

  • 误以为 SELECT 200, '篮球' 会在数据库里建表或存数据，实际上查询完就没了，数据库什么都没变。
  • 混淆“列名（表头）”和“列的值”：200 是值，AS hobby 里的 hobby 才是列名。

三、 CASE WHEN 条件判断

1. 语法规则

  • 语法：CASE WHEN (条件1) THEN 结果1 WHEN (条件2) THEN 结果2 ... ELSE 其他结果 END
  • CASE 是开头，END 是结尾，两个都不能漏。
  • 判断顺序从上到下，满足一个就停。
  • 推荐用 AS 给这列起别名，如 END AS age_level。

2. 常见误区

  • 漏写 CASE，直接写 WHEN，报 syntax error near "when"。
  • 把原字段（如 age）直接放 SELECT 后面，而不是用 CASE WHEN 表达式替代。
  • 字符串必须用英文单引号，不能用中文引号。

四、 BETWEEN 与不等号

1. 核心概念

  • BETWEEN 20 AND 60 等价于 >= 20 AND <= 60，是闭区间，包含两端。
  • 也可以用两个不等号代替，效果完全一样。
  • 只想匹配离散值时用 IN，BETWEEN 做不到。

2. 常见误区

  • 误以为 BETWEEN 是开区间（不包含两端），实际上包含。
  • 写成 > 20 AND < 60 会排除 20 和 60，结果与 BETWEEN 不同。

五、 GROUP BY 分组聚合

1. 核心概念

  • GROUP BY 就是合并同类项：按某个字段把数据分成一堆一堆，然后对每一堆用聚合函数计算。
  • 常用聚合函数：COUNT（数数）、SUM（求和）、AVG（平均）。
  • 分组后，SELECT 后面只能出现分组字段和聚合函数，不能出现其他未分组的字段。

2. 常见误区

  • SELECT 后面写了既没分组也没聚合的字段（如 order_id），数据库不知道该显示哪一个。
  • 过滤分组后的结果要用 HAVING，不能用 WHERE。
  • WHERE 在分组前过滤，不能跟聚合函数；HAVING 在分组后过滤，可以跟聚合函数。

六、 SQL 书写顺序与执行顺序

1. 书写顺序
SELECT → FROM → JOIN → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT

2. 执行顺序
FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

3. 常见误区

  • WHERE 里不能用 SELECT 的别名，因为 WHERE 执行在 SELECT 之前。
  • ORDER BY 里可以用 SELECT 的别名，因为 ORDER BY 执行在 SELECT 之后。
  • GROUP BY 之后，SELECT 只能写分组字段和聚合函数。

七、 笛卡尔积 (CROSS JOIN / 逗号连接)

1. 核心概念

  • 笛卡尔积 = 两张表里的每一行都和另一张表的每一行“强行配对”一次。
  • 结果行数 = 表 A 行数 × 表 B 行数。
  • 产生原因：多表查询时没有指定关联条件（ON 或 WHERE）。

2. 常见误区

  • 写多表查询时漏了连接条件，导致数据量爆炸、查询变慢、结果毫无意义。
  • 误以为笛卡尔积一定是错误，实际上在需要生成全排列组合时是故意使用的。

八、 多表连接 JOIN 进阶

1. 内连接（INNER JOIN）

  • 语法：FROM student s INNER JOIN class c ON s.class_id = c.id（INNER 可省略）。
  • 本质：交集，只保留两张表中关联字段完全匹配的行。
  • 不符合关联条件的数据会被直接过滤掉。

2. 左连接（LEFT JOIN）

  • 语法：FROM student s LEFT JOIN class c ON s.class_id = c.id。
  • 本质：以左表为主，左表的数据必须全部保留。
  • 右表没有匹配的数据时，右表字段显示为 NULL。
  • 通常通过互换表的位置，统一使用 LEFT JOIN 来实现右连接效果。

九、 高级子查询：EXISTS 与 NOT EXISTS

1. 核心本质

  • EXISTS 返回布尔值（True/False），相当于“门卫”：遍历主查询每一行，代入子查询验证。为 True 则保留该行，为 False 则过滤该行。
  • 子查询中 SELECT 后面写什么无所谓，通常写 SELECT 1，性能最高。

2. EXISTS 与 IN 的区别

对比维度 EXISTS IN
执行顺序 先执行主查询，再逐行代入子查询验证 先执行子查询，缓存结果集，再匹配主查询
适用场景 主表数据量小、子表数据量大时效率高 子表数据量小、主表数据量大时效率高
NULL 值处理 不受 NULL 影响 子查询结果含 NULL 会导致整个查询返回空
子查询字段 可以返回多列 必须且只能返回一列

3. NOT EXISTS 经典应用

  • 业务场景：查询“不存在关联数据”的记录（如没有班级的学生、没有下过订单的客户）。

  • 经典写法：

    sql

    1
    2
    3
    4
    5
    SELECT name, age, class_id
    FROM student
    WHERE NOT EXISTS (
        SELECT 1 FROM class WHERE class.id = student.class_id
    );

  • 优势：完美避开 NOT IN 遇到 NULL 值导致结果错误的坑，逻辑上等同于 LEFT JOIN ... WHERE 右表.id IS NULL。

十、 窗口函数 (Window Functions)

1. 核心本质

  • OVER() 就像一个“透视镜”，它不压缩数据行，而是在保留原始明细的同时，在旁边增加一列统计结果。
  • 对比 GROUP BY：GROUP BY 是把多行“压扁”成一行；OVER() 是保留多行，增加列。

2. 核心语法拆解

  • PARTITION BY 字段：分堆/分组。把数据按指定字段分成独立的“小房间”，排名或计算在各自房间内独立进行。
  • ORDER BY 字段：在堆里排队。
    • 配合 SUM()：会变成累计求和（Running Total）。
    • 配合 RANK()：决定排名的顺序。
  • 如果不写 ORDER BY：SUM() 计算的是整个分区的总和；RANK() 无法使用。

3. 常用窗口函数对比

  • ROW_NUMBER()：强行发连号（1, 2, 3, 4）。即使分数相同，也按顺序给不同的号。（常用于去重取第一条）。
  • RANK()：并列排名，会跳号（1, 1, 3）。分数相同给相同号，下一名跳过。
  • DENSE_RANK()：并列排名，不跳号（1, 1, 2）。分数相同给相同号，下一名接着数。
  • LAG(字段, N)：往前看 N 行（取上一名/上一单）。
  • LEAD(字段, N)：往后看 N 行（取下一名/下一单）。
    (注：LAG 和 LEAD 的第三个参数默认为 NULL，通常可省略不写)