SQL数据库从0到1学习指南

       学习SQL是个非常乏味且枯燥的事儿，因为都是和一堆数据打交道，但是当你用自己写的SQL语句执行出来想要的结果的时候，真的无比开心。

  闲言少叙，步入正题。

1.理解基础概念

先理解最基础的SQL数据库相关的一些概念，不用死记硬背，有个印象即可。

数据库（Database）

数据库是存储数据的容器，通常由多个表组成。每个数据库有唯一的名称，用于区分不同的数据集合。

表（Table）

表是数据库中存储数据的主要结构，由行（记录）和列（字段）组成。每个表有一个唯一的名称，列定义了数据的类型和约束。

列（Column）

列是表中的垂直数据单元，也称为字段。每列有特定的数据类型（如整数、字符串、日期等）和约束（如主键、非空等）。

行（Row）

行是表中的水平数据单元，也称为记录。每行代表表中的一条数据记录。

主键（Primary Key）

主键是表中唯一标识每行记录的列或列组合。主键的值不能重复且不能为空。

外键（Foreign Key）

外键是表中的一列或列组合，用于建立表之间的关系。外键引用另一表的主键，确保数据的引用完整性。

索引（Index）

索引是用于加快数据检索速度的数据结构。索引可以基于一列或多列创建，但会增加数据插入和更新的成本。

视图（View）

视图是基于一个或多个表的虚拟表。视图不存储数据，而是通过查询动态生成数据。

存储过程（Stored Procedure）

存储过程是预编译的SQL语句集合，存储在数据库中，可以通过名称调用执行。

触发器（Trigger）

触发器是与表关联的特殊存储过程，在特定事件（如插入、更新、删除）发生时自动执行。

事务（Transaction）

事务是一组SQL操作，要么全部执行成功，要么全部失败。事务具有ACID属性（原子性、一致性、隔离性、持久性）。

SQL语句分类

SQL语句主要分为以下几类：

• 数据查询语言（DQL）：如SELECT，用于查询数据。
• 数据操作语言（DML）：如INSERT、UPDATE、DELETE，用于操作数据。
• 数据定义语言（DDL）：如CREATE、ALTER、DROP，用于定义和修改数据库结构。
• 数据控制语言（DCL）：如GRANT、REVOKE，用于控制数据库访问权限。

数据类型

SQL支持多种数据类型，包括：

• 整数类型：INT、SMALLINT、BIGINT等。
• 浮点类型：FLOAT、DOUBLE、DECIMAL等。
• 字符串类型：CHAR、VARCHAR、TEXT等。
• 日期时间类型：DATE、TIME、DATETIME等。
• 二进制类型：BLOB、BINARY等。

二.学习基础语法

1. 基础运算符 (WHERE 子句的核心)

用于在 WHERE, HAVING, ON 等子句中构造条件表达式。

• = (等于)

  • 用法： 精确匹配值。

  • 示例： SELECT * FROM employees WHERE department_id = 10; (查找部门ID为10的所有员工)

  • 注意事项：

    • 比较字符串时，区分大小写 (取决于数据库设置 COLLATION)。'Apple' = 'apple' 可能返回 false。

    • 不能用于比较 NULL 值。用 IS NULL 或 IS NOT NULL。

  • 常见报错：

    • Operand data type ... is invalid for equal to operator. (尝试比较不兼容的数据类型，如 varchar = int， 需要转换 CAST 或确保类型匹配)。

    • Invalid column name ... (列名拼写错误或不存在)。

• != 或 <> (不等于)

  • 用法： 筛选不等于指定值的行。

  • 示例： SELECT * FROM products WHERE price != 0; (查找价格不为0的产品) 或 SELECT * FROM customers WHERE country <> 'USA'; (查找不在美国的客户)。!= 和 <> 功能相同，推荐使用 <> (标准 SQL)。

  • 注意事项/报错： 同 =，同样不适用于 NULL。

• < (小于)

  • 用法： 筛选小于指定值的行。

  • 示例： SELECT * FROM orders WHERE order_date < '2023-10-01'; (查找 2023年10月1日之前的订单)。

  • 注意事项： 适用于数字、日期、时间、字符(按字母/字典序)。

  • 报错： The data types ... and ... are incompatible in the less than operator. (类型不匹配)。

• > (大于)

  • 用法： 筛选大于指定值的行。

  • 示例： SELECT * FROM students WHERE score > 90; (查找分数大于90的学生)。

  • 注意事项/报错： 同 <。

  • IS NULL / IS NOT NULL

    • 用法： 唯一正确判断列值是否为 NULL 的方式。NULL 表示未知、缺失或不适用的值，它不是0、空字符串或False。

    • 示例：

      • SELECT * FROM employees WHERE manager_id IS NULL; (查找没有经理的CEO或高层)。

      • SELECT * FROM addresses WHERE zip_code IS NOT NULL; (查找邮编不为空的地址)。

    • 注意事项：

      • 极其重要： WHERE column = NULL 或 WHERE column != NULL 永远不会返回任何行！ 因为 NULL 与任何值(包括另一个 NULL)的比较结果都是 UNKNOWN (不是 True)。

      • 聚合函数(如 COUNT, SUM, AVG)通常忽略 NULL。COUNT(*) 计算所有行，COUNT(column) 计算该列非 NULL 的行数。

    • 常见错误： 新手最常犯的错误之一就是试图用 = 或 != 来比较 NULL，导致查询结果不符合预期。

• LIKE (模式匹配)

  • 用法： 在字符串列中进行模式匹配。通常与通配符 % (匹配任意0个或多个字符) 和 _ (匹配任意单个字符) 结合使用。

  • 示例：

    • SELECT * FROM products WHERE name LIKE '%Book%'; (查找名称中包含 "Book" 的产品，如 "Notebook", "Bookcase")。

    • SELECT * FROM users WHERE username LIKE 'J_hn'; (查找用户名类似 "John", "Jahn", "Jxhn" 的用户)。

    • SELECT * FROM files WHERE filename LIKE 'Report_2023-__-__%.xlsx'; (查找类似 "Report_2023-10-25_Final.xlsx" 的文件)。

  • 注意事项：

    • 区分大小写： 同样取决于数据库排序规则(COLLATION)。LIKE 'apple%' 可能匹配不到 "Apple"。

    • 性能： LIKE '%value' (以 % 开头) 通常无法利用索引，会导致全表扫描，在大表上性能很差。尽量避免，或考虑全文搜索功能。

    • 转义字符： 如果需要匹配 % 或 _ 本身，使用 ESCAPE 子句。例如 WHERE comment LIKE '%30\%%' ESCAPE '\'; 匹配包含 "30%" 的评论。

  • 常见报错： Pattern matching argument must be a string. (模式参数不是字符串类型)。

• BETWEEN ... AND ... (范围查询)

  • 用法： 筛选值在指定范围(包含边界)内的行。等价于 value >= low AND value <= high。

  • 示例： SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31'; (查找2023年1月的所有订单)。

  • 注意事项：

    • 包含边界： BETWEEN 10 AND 20 包含 10 和 20。

    • 数据类型： low 和 high 必须与列的数据类型兼容(数字、日期等)。

    • 顺序： low 必须小于或等于 high。BETWEEN 20 AND 10 不会返回任何结果。

  • 常见错误： 误以为不包含边界，或数据类型不匹配导致范围无效。

2. SELECT ... FROM ... WHERE ... (查询基石)

• SELECT: 指定要从表中检索哪些列。* 表示所有列。可以使用表达式、函数(COUNT, UPPER等)、别名(AS)。SELECT 决定了结果集的形状(有哪些列)。

• FROM: 指定要查询的表（或视图、子查询）。FROM 决定了数据的来源。

• WHERE: 指定过滤条件，决定哪些行会被包含在结果集中。它在数据分组(GROUP BY)和聚合函数计算之前进行过滤。

• 逻辑顺序(不是执行顺序)：

  1. FROM (确定数据源)

  2. WHERE (过滤行)

  3. GROUP BY (分组)

  4. HAVING (过滤分组)

  5. SELECT (选择列/计算表达式)

  6. ORDER BY (排序)

  7. LIMIT/OFFSET (限制行数)

• 示例：

  sql

  SELECT employee_id, first_name, last_name, salary * 1.1 AS proposed_salary -- SELECT (选择列和计算)
  FROM employees -- FROM (来源表)
  WHERE department_id = 50 AND salary < 6000; -- WHERE (过滤行：部门50且薪水<6000)

• 注意事项：

  • WHERE 条件中引用的列必须存在于 FROM 指定的表（或其JOIN的表）中。

  • 避免在 SELECT 中使用 * 在生产代码中，明确列出所需列可提高可读性和性能(尤其当表有 BLOB/TEXT 等大字段时)。

• 常见报错：

  • Invalid object name 'table_name'. (表名拼写错误或不存在)。

  • Ambiguous column name 'column_name'. (多个表有相同列名，需用表名或别名限定 table.column 或 alias.column)。

  • Column 'column_name' cannot be used in the WHERE clause because it is not contained in an aggregate function or the GROUP BY clause. (在 WHERE 中错误引用了聚合列或未分组的列，应使用 HAVING 或调整 GROUP BY)。

3. DISTINCT (去重)

• 用法： 放在 SELECT 关键字后，用于消除结果集中重复的行。它作用于 SELECT 后面列出的所有列的组合。

• 示例：

  • SELECT DISTINCT department_id FROM employees; (找出所有存在的、不重复的部门ID)。

  • SELECT DISTINCT city, country FROM customers; (找出所有唯一的城市-国家组合)。

• 注意事项：

  • 性能开销： DISTINCT 需要对结果集进行排序或哈希计算去重。在大数据集上可能很慢。考虑是否真的需要所有列组合的唯一性，或者是否可以通过更精确的 WHERE 条件或 GROUP BY 达到目的。

  • NULL 行为： 在 DISTINCT 的上下文中，所有的 NULL 值被认为是彼此相等的（即多个 NULL 只算一个“重复”）。

• 常见报错： 通常没有特定语法报错，但可能导致性能问题。逻辑错误是误以为 SELECT DISTINCT column1 后，column2 的值也是唯一的（实际不是，DISTINCT 作用于所有列组合）。

4. JOIN ... ON ... (表连接 - 核心！)

用于从多个相关表中组合数据。ON 指定连接条件。

• INNER JOIN (内连接 - 最常用)

  • 用法： 仅返回两个表中连接条件匹配的行。如果左表的某行在右表中没有匹配项，或者右表的某行在左表中没有匹配项，则该行不会出现在结果中。

  • 示例：

    sql

    SELECT e.first_name, e.last_name, d.department_name
    FROM employees e -- e 是 employees 表的别名
    INNER JOIN departments d -- d 是 departments 表的别名
    ON e.department_id = d.department_id; -- 连接条件：员工的部门ID = 部门的ID

    (获取所有有部门的员工及其部门名称。没有分配部门的员工和没有员工的部门不会出现)。

  • 注意事项： 确保 ON 条件能有效关联两张表，通常是外键关系。使用表别名(AS 或直接空格)提高可读性。小心多对多关系可能导致笛卡尔积爆炸。

• LEFT JOIN (左外连接)

  • 用法： 返回左表的所有行，即使在右表中没有匹配的行。对于左表有而右表无匹配的行，右表的列将填充为 NULL。

  • 示例：

    sql

    SELECT e.first_name, e.last_name, d.department_name
    FROM employees e
    LEFT JOIN departments d
    ON e.department_id = d.department_id;

    (获取所有员工，包括那些没有分配部门的员工。没有部门的员工，其 department_name 将为 NULL)。

  • 注意事项： 常用于查找“缺失”的关系（例如，WHERE d.department_id IS NULL 可以找出没有分配部门的员工）。右表条件应放在 ON 中，左表条件放在 WHERE 中（理解 ON 和 WHERE 在 LEFT JOIN 中的区别至关重要）。

• RIGHT JOIN (右外连接)

  • 用法： 返回右表的所有行，即使在左表中没有匹配的行。对于右表有而左表无匹配的行，左表的列将填充为 NULL。逻辑上是 LEFT JOIN 的反转。实践中较少使用，因为通常可以通过调换表顺序使用 LEFT JOIN 实现。

• FULL JOIN / FULL OUTER JOIN (全外连接)

  • 用法： 返回左表和右表的所有行。当某行在另一个表中没有匹配时，另一个表的列填充 NULL。它是 LEFT JOIN 和 RIGHT JOIN 结果的并集。

  • 示例： SELECT * FROM table1 FULL JOIN table2 ON ... (获取两个表的所有记录，不管有没有匹配)。

  • 注意事项： 使用相对较少。数据库支持度可能略有差异（MySQL 不支持 FULL JOIN，需用 LEFT JOIN ... UNION ... RIGHT JOIN 模拟）。

• CROSS JOIN (交叉连接 / 笛卡尔积)

  • 用法： 返回两个表中所有可能的行组合。没有 ON 子句（即使写了 ON 1=1，优化器也可能将其视为 CROSS JOIN）。结果行数 = 左表行数 * 右表行数。

  • 示例： SELECT * FROM colors CROSS JOIN sizes; (生成所有颜色和尺寸的组合)。

  • 注意事项： 极其危险！ 除非你明确需要笛卡尔积（如生成测试数据、组合所有可能性），否则永远不要在生产查询中遗漏 ON 条件。两个大表的 CROSS JOIN 会瞬间产生海量数据，导致数据库崩溃或性能急剧下降。

• 连接条件 (ON) vs 过滤条件 (WHERE):

  • ON: 定义表之间如何连接。在 JOIN 操作过程中应用。

  • WHERE: 对连接后的结果集进行过滤。在 JOIN 操作完成后应用。

  • 关键区别 (LEFT JOIN):

    • 条件放在 ON 中：影响连接过程本身。右表不匹配的行会被保留（右表列置为 NULL），然后 WHERE 再过滤。

    • 条件放在 WHERE 中：发生在连接之后。如果条件引用了右表列，那些因为不匹配而 NULL 的行会被 WHERE 条件过滤掉（因为 NULL != some_value 是 UNKNOWN/FALSE），将 LEFT JOIN 的效果变成了 INNER JOIN！这是非常常见的逻辑错误。

    sql

  • -- 错误：想找所有员工及其部门(包括没部门的)，但只想要IT部门的 -> 结果漏掉了没部门的员工！ SELECT e.*, d.* FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id WHERE d.department_name = 'IT'; -- WHERE 过滤掉了 d.department_name 为 NULL (没部门) 的行 -- 正确：把部门条件放在 ON 里 SELECT e.*, d.* FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id AND d.department_name = 'IT'; -- 现在结果包含所有员工，IT部门的显示部门信息，非IT部门或没部门的 d.* 列为 NULL

• 常见报错：

  • The multi-part identifier "table.column" could not be bound. (连接条件 ON 中引用了不存在的表或列，或别名写错)。

  • Ambiguous column name 'column_name' in select list. (SELECT 列表中的列名在多个连接表中都存在，需用别名限定)。

  • Out of memory / Query execution time exceeded (通常是意外 CROSS JOIN 导致笛卡尔积爆炸)。

  • 逻辑错误：混淆 ON 和 WHERE 导致连接结果不符合预期（尤其是 LEFT JOIN 时）。

5. GROUP BY (分组)

• 用法： 将结果集按一个或多个列的值分成不同的组。通常与聚合函数 (COUNT, SUM, AVG, MAX, MIN) 一起使用，对每个组进行计算。

• 示例：

  sql

  SELECT department_id, COUNT(*) AS num_employees, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department_id;

  (按部门分组，统计每个部门的员工数量和平均工资)。

• 注意事项：

  • SELECT 子句中出现的所有非聚合函数的列，必须出现在 GROUP BY 子句中。聚合函数列(COUNT(*), SUM(salary))不需要在 GROUP BY 中。

  • GROUP BY 后面列的顺序通常不影响分组结果（组是相同的），但可能影响某些数据库的排序。

  • 可以对表达式结果分组：SELECT YEAR(order_date), ... GROUP BY YEAR(order_date);。

  • GROUP BY 隐含着排序（按分组列），但不要依赖于此。如果需要特定顺序，总是使用 ORDER BY。

• 常见报错：

  • Column 'table.column' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. (最经典错误：SELECT 中的非聚合列未包含在 GROUP BY 中)。

  • Cannot use an aggregate or a subquery in an expression used for the group-by list. (尝试在 GROUP BY 中使用聚合函数或子查询，这是不允许的)。

6. HAVING (分组后过滤)

• 用法： 对 GROUP BY 产生的分组结果进行过滤。它作用于组的聚合值上。类似于 WHERE，但 WHERE 在分组前过滤行，HAVING 在分组后过滤组。

• 示例：

  sql

  SELECT department_id, COUNT(*) AS num_employees, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department_id
  HAVING COUNT(*) > 5 AND AVG(salary) > 5000; -- 过滤：只显示员工数>5且平均工资>5000的部门

• 注意事项：

  • HAVING 条件中通常包含聚合函数 (COUNT, SUM, AVG 等) 或分组列。

  • 可以在 HAVING 中使用别名 (num_employees > 5)，但并非所有数据库都支持（标准 SQL 不允许）。为了可移植性，最好重复聚合函数 (COUNT(*) > 5) 或使用子查询。

  • 不是所有查询都需要 HAVING。如果过滤条件不涉及聚合值，应优先使用 WHERE（性能更好，因为它减少了需要分组的数据量）。

• 常见报错： Invalid column name 'alias'. (在 HAVING 中使用 SELECT 中定义的别名，但数据库不支持)。

7. IN / EXISTS (子查询条件)

• IN

  • 用法： 检查某个值是否存在于子查询返回的结果集或显式列表中。NOT IN 表示不存在。

  • 示例：

    sql

    -- 查找在特定部门的员工 (显式列表)
    SELECT * FROM employees WHERE department_id IN (10, 20, 30);
    
    -- 查找有订单的客户 (子查询)
    SELECT * FROM customers
    WHERE customer_id IN (
        SELECT DISTINCT customer_id FROM orders
    );

  • 注意事项：

    • 子查询必须只返回一列。

    • 如果子查询可能返回 NULL，NOT IN 的结果可能出人意料（因为 value NOT IN (list) 等价于 value <> item1 AND value <> item2 AND ...，如果 list 中有 NULL，整个表达式结果是 UNKNOWN/FALSE）。谨慎使用 NOT IN 配合可能返回 NULL 的子查询。

    • 显式列表很方便，但当列表很大时，可能影响性能。对于大列表，JOIN 或 EXISTS 有时是更好的选择。

• EXISTS

  • 用法： 检查子查询是否至少返回一行。NOT EXISTS 表示子查询没有返回任何行。它更关注是否存在，不关心具体值。

  • 示例：

    sql

    -- 查找有订单的客户 (使用 EXISTS)
    SELECT * FROM customers c
    WHERE EXISTS (
        SELECT 1 FROM orders o
        WHERE o.customer_id = c.customer_id -- 关联子查询：引用外层 customers 表的 c.customer_id
    );

  • 注意事项：

    • 子查询通常是关联子查询 (Correlated Subquery)，引用了外层查询的列 (o.customer_id = c.customer_id)。

    • 子查询的 SELECT 列表通常写 SELECT 1 或 SELECT *，因为 EXISTS 只关心是否有行返回，不关心内容。

    • 性能：对于判断存在性，EXISTS 通常比 IN 性能更好，尤其是在子查询结果集较大时。数据库优化器可以在找到第一个匹配行后就停止执行子查询。NOT EXISTS 也比 NOT IN 处理 NULL 值更安全、更高效。

• IN vs EXISTS 选择：

  • 需要检查值是否在一个静态列表或非关联子查询结果中 -> IN 通常更直观。

  • 需要检查基于关联的存在性 -> EXISTS 通常是性能更好且更安全 (NOT EXISTS 尤其如此) 的选择。

  • 子查询结果集小 -> IN 可能不错。

  • 子查询结果集大或外层表大 -> 优先考虑 EXISTS。

  • 涉及 NOT 逻辑 -> 强烈优先考虑 NOT EXISTS (避免 NOT IN 的 NULL 陷阱和潜在性能问题)。

• 常见报错：

  • Subquery returned more than 1 value. (在 IN 子查询中，子查询返回了多列，但 IN 要求只返回一列)。

  • Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. (类似上一条)。

  • 逻辑错误：NOT IN 子查询返回 NULL 导致整个条件返回 FALSE/UNKNOWN，没有行被选中。

8. ORDER BY ... ASC/DESC (排序)

• 用法： 对最终结果集按一个或多个列进行排序。ASC (Ascending) 表示升序 (默认)，DESC (Descending) 表示降序。

• 示例：

  sql

  SELECT first_name, last_name, salary
  FROM employees
  ORDER BY salary DESC, last_name ASC; -- 先按工资降序(高到低)，工资相同再按姓氏升序(A-Z)

• 注意事项：

  • 可以按 SELECT 列表中的列名、别名、列位置(如 ORDER BY 3 表示按第三列排序，但不推荐，可读性差且易错)或表达式排序。

  • NULL 值的排序位置：通常被视为最小值（在 ASC 排序中排在最前，在 DESC 排序中排在最后），但这可能因数据库设置而异。

  • 排序是资源密集型操作，尤其对大结果集。确保只对必要的数据排序。索引可以加速排序 (ORDER BY 列上的索引)。

  • 如果使用了 LIMIT，ORDER BY 是必须的，否则返回的行是不确定的（数据库可能按物理存储顺序返回）。

• 常见报错： Invalid column name 'alias'. / Column 'column_name' is invalid in the ORDER BY clause. (排序列名/别名不存在或拼写错误)。Ambiguous column name ... (多个表有相同列名，需用别名限定)。

9. LIMIT / FETCH FIRST ... ROWS ONLY (限制返回行数) / OFFSET (分页)

• 用法 (LIMIT - MySQL, PostgreSQL, SQLite 等):

  • LIMIT n: 只返回查询结果的前 n 行。

  • LIMIT offset, n 或 LIMIT n OFFSET offset: 跳过 offset 行，然后返回接下来的 n 行。常用于分页。

• 用法 (FETCH FIRST - 标准 SQL, DB2, Oracle 12c+, PostgreSQL, SQL Server):

  • OFFSET m ROWS FETCH NEXT n ROWS ONLY: 跳过 m 行，返回接下来的 n 行。

• 示例：

  sql

  -- MySQL/PostgreSQL/SQLite: 获取第3页，每页10条 (跳过前20条，取21-30条)
  SELECT * FROM products ORDER BY product_id LIMIT 10 OFFSET 20;
  
  -- 标准 SQL / SQL Server / Oracle 12c+ / PostgreSQL: 同上
  SELECT * FROM products ORDER BY product_id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

• 注意事项：

  • ORDER BY 是必须的！ 没有 ORDER BY，LIMIT/OFFSET 返回的行是不确定的（依赖于数据库内部实现和物理存储），每次执行可能不同。这会导致分页结果混乱。

  • OFFSET 在大偏移量时性能较差（数据库需要扫描并跳过前 offset 行）。考虑基于键的分页 (WHERE id > last_seen_id ORDER BY id LIMIT n)。

  • 分页时，通常需要知道总行数以计算总页数，这需要额外的 COUNT(*) 查询。

• 常见报错： Incorrect syntax near 'OFFSET'. / LIMIT clause cannot be used without ORDER BY clause in this context. (语法错误或不带 ORDER BY 使用 LIMIT/OFFSET)。性能问题在大偏移量时常见。

10. CREATE TABLE (创建表)

• 用法： 定义一个新表的结构，包括表名、列名、列的数据类型、约束(PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, DEFAULT)等。

• 示例：

  sql

  CREATE TABLE customers (
      customer_id INT PRIMARY KEY AUTO_INCREMENT, -- 主键，自增 (MySQL)
      first_name VARCHAR(50) NOT NULL,
      last_name VARCHAR(50) NOT NULL,
      email VARCHAR(100) UNIQUE, -- 唯一约束
      phone VARCHAR(20),
      registration_date DATE NOT NULL DEFAULT CURRENT_DATE, -- 默认值
      active BOOLEAN NOT NULL DEFAULT TRUE,
      CHECK (LENGTH(phone) >= 10) -- 检查约束
  );

• 注意事项：

  • 数据类型： 为每列选择最合适、最节省空间的数据类型 (INT, VARCHAR(n), DATE, DATETIME/TIMESTAMP, DECIMAL(p, s), BOOLEAN 等)。

  • 主键 (PRIMARY KEY)： 唯一标识表中每一行。不能 NULL，值必须唯一。通常选择无业务意义、自增的数字列 (AUTO_INCREMENT / IDENTITY / SERIAL)。

  • 外键 (FOREIGN KEY REFERENCES ...)： 强制引用完整性，确保该列的值存在于另一个表的主键中。定义关系。可选，但强烈推荐用于维护数据一致性。

  • NOT NULL： 强制该列必须有值。根据业务逻辑决定。

  • UNIQUE： 确保该列（或列组合）的值在表中唯一。

  • CHECK： 定义自定义条件约束列值 (如 age >= 18, salary > 0)。

  • DEFAULT： 为列指定默认值，当插入行未提供该列值时使用。

  • 命名规范： 使用清晰一致的命名（表名复数或单数？列名小写加下划线 snake_case 还是驼峰 camelCase？）。

• 常见报错：

  • Table 'table_name' already exists. (表已存在)。

  • Incorrect column name 'column_name'. / Invalid data type. (列名或数据类型错误)。

  • There is already an object named 'constraint_name' in the database. (约束名重复)。

  • Foreign key constraint is incorrectly formed / Cannot add foreign key constraint (外键定义错误：数据类型不匹配、引用的表/列不存在、引擎不支持 - MyISAM vs InnoDB)。

11. INSERT INTO ... VALUES ... (插入数据)

• 用法： 向表中插入一行或多行新数据。

• 示例：

  sql

  -- 插入单行，指定列名和值 (推荐)
  INSERT INTO customers (first_name, last_name, email)
  VALUES ('John', 'Doe', 'john.doe@example.com');
  
  -- 插入单行，省略列名 (需按表定义顺序提供所有列的值，包括自增主键(通常设为NULL/DEFAULT)和非空列，不推荐)
  INSERT INTO customers VALUES (NULL, 'Jane', 'Smith', 'jane.smith@example.com', NULL, DEFAULT, TRUE);
  
  -- 插入多行 (高效)
  INSERT INTO products (name, price)
  VALUES
      ('Product A', 19.99),
      ('Product B', 29.99),
      ('Product C', 39.99);
  
  -- 插入查询结果 (从另一个表复制数据)
  INSERT INTO active_customers (customer_id, name)
  SELECT customer_id, CONCAT(first_name, ' ', last_name)
  FROM customers
  WHERE active = TRUE;

• 注意事项：

  • 推荐显式指定列名： 提高可读性、可维护性，避免因表结构变更(如新增列)导致插入失败。确保只提供必要的列。

  • 值列表必须与列列表匹配： 数量、顺序、数据类型都要匹配。

  • 处理自增列： 插入时通常不需要指定自增主键列的值，数据库会自动生成。可以指定为 NULL 或 DEFAULT，或直接省略该列。

  • NOT NULL 约束： 必须为标记为 NOT NULL 且没有 DEFAULT 的列提供值。

  • DEFAULT： 可以使用 DEFAULT 关键字显式使用列的默认值。

  • 事务： 插入操作通常在事务中进行，确保数据一致性。可以一次插入多条记录。

• 常见报错：

  • Column count doesn't match value count. (列数和值数不匹配)。

  • Field 'column_name' doesn't have a default value (没有为 NOT NULL 且无 DEFAULT 的列提供值)。

  • Duplicate entry '...' for key 'PRIMARY' / Duplicate entry '...' for key 'unique_key_name' (违反主键或唯一约束，插入了重复值)。

  • Data truncation: Data too long for column 'column_name' (插入的字符串长度超过 VARCHAR(n) 定义的长度)。

  • Cannot add or update a child row: a foreign key constraint fails (插入的外键值在父表中不存在)。

12. UPDATE ... SET ... WHERE ... (更新数据)

• 用法： 修改表中已有行的列值。WHERE 子句指定要更新哪些行（极其重要！ 避免全表更新）。

• 示例：

  sql

  -- 更新特定员工工资 (WHERE 条件精确)
  UPDATE employees
  SET salary = salary * 1.05 -- 涨薪5%
  WHERE employee_id = 1001;
  
  -- 更新特定部门所有员工的经理
  UPDATE employees
  SET manager_id = 200
  WHERE department_id = 40;
  
  -- 更新多个列
  UPDATE customers
  SET email = 'new.email@example.com', phone = '123-456-7890'
  WHERE customer_id = 5001;

• 注意事项：

  • WHERE 子句是生命线！ 在运行 UPDATE 语句前，务必先写一个 SELECT ... WHERE ... 语句确认 WHERE 条件是否精确匹配到你想要更新的行。遗漏 WHERE 会更新整个表！这是灾难性的。

  • 事务： 在事务中进行更新，以便在出错时可以回滚 (ROLLBACK)。

  • 性能： 更新大量行可能很慢。确保 WHERE 条件中的列有索引。避免更新索引列，代价高。

  • 约束： 更新后的值必须满足所有约束 (PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK, NOT NULL)。例如，不能将主键更新为已存在的值。

  • JOIN 更新： 某些数据库支持在 UPDATE 中使用 JOIN (语法各异)。

    sql

    -- SQL Server / PostgreSQL (部分) 示例: 基于另一表更新
    UPDATE orders o
    SET o.status = 'Shipped'
    FROM shipments s -- 假设 shipments 表有发货信息
    WHERE o.order_id = s.order_id AND s.ship_date IS NOT NULL;

• 常见报错：

  • You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. (MySQL 安全模式阻止不带 WHERE 或 WHERE 不使用键列的更新)。

  • Duplicate entry '...' for key '...' (更新后的值违反了唯一约束)。

  • Cannot add or update a child row: a foreign key constraint fails (更新后的外键值在父表中不存在)。

  • Data truncation: Data too long for column (更新值过长)。

  • 最严重错误：忘记 WHERE 导致全表被更新！ (预防：事务 + 先 SELECT 验证)。

13. DROP TABLE (删除表)

• 用法： 永久删除数据库中的一张表及其所有数据、结构、索引、约束、触发器等。操作不可逆！

• 示例： DROP TABLE old_log_data;

• 注意事项：

  • 极度危险！ 操作前务必确认表名无误，并且你真的不再需要该表及其数据。在生产环境执行需极其谨慎，通常需要审批流程。

  • 如果表被其他表的外键引用，直接 DROP 可能会失败（违反引用完整性）。需要先删除依赖它的外键约束或子表，或者使用 CASCADE 选项（如果数据库支持）自动删除依赖对象。

    sql

    DROP TABLE parent_table CASCADE; -- PostgreSQL: 级联删除依赖对象

  • 考虑备份：在删除前，如果数据可能还有用，务必进行备份 (mysqldump, pg_dump, 导出等)。

• 常见报错：

  • Cannot drop table 'table_name' because it is being referenced by a FOREIGN KEY constraint 'fk_name'. (存在外键引用)。

  • Database backup/restore operation is in progress. / Table 'table_name' is locked. (表被占用)。

  • 最严重后果：误删重要生产表！ (强调备份和权限管理的重要性)。

14. DELETE FROM ... WHERE ... (删除数据)

• 用法： 从表中删除行。WHERE 子句指定要删除哪些行（极其重要！ 避免全表删除）。

• 示例：

  sql

  -- 删除特定订单
  DELETE FROM orders WHERE order_id = 12345;
  
  -- 删除所有取消的订单
  DELETE FROM orders WHERE status = 'Cancelled';
  
  -- 使用子查询删除 (谨慎！)
  DELETE FROM customers
  WHERE customer_id NOT IN (
      SELECT DISTINCT customer_id FROM orders
  ); -- 删除从未下过订单的客户

• 注意事项：

  • WHERE 子句是生命线！ 在运行 DELETE 语句前，务必先写一个 SELECT ... WHERE ... 语句确认 WHERE 条件是否精确匹配到你想要删除的行。遗漏 WHERE 会删除整个表的所有数据！这也是灾难性的。

  • 与 DROP TABLE 区别： DELETE 只删除数据，表结构（列、约束、索引等）依然存在。DROP TABLE 连表结构一起删除。

  • 事务： 在事务中进行删除，以便可以回滚 (ROLLBACK)。

  • 性能： 删除大量行可能很慢，并产生大量事务日志。对于超大表删除所有行，有时 TRUNCATE TABLE (见下文) 是更快、资源消耗更少的选择（但它不记录日志且不可回滚）。

  • 外键约束： 如果删除的行被其他表的外键引用，删除可能会失败（违反引用完整性）。需要先删除子表的引用行，或者设置外键约束为 ON DELETE CASCADE (自动删除子表相关行) 或 ON DELETE SET NULL。

  • TRUNCATE TABLE：

    • 用法：TRUNCATE TABLE table_name;

    • 作用：更快地删除表中的所有数据。它通过释放存储表数据的数据页来实现，而不是逐行删除。

    • 与 DELETE FROM table_name (无 WHERE) 的区别：

      • TRUNCATE 是 DDL (数据定义语言) 操作（通常，但实现有差异），DELETE 是 DML (数据操作语言)。

      • TRUNCATE 不能带 WHERE 子句，只能删全表。

      • TRUNCATE 不记录单个行的删除日志（只记录页释放），因此更快、使用更少的事务日志空间。

      • TRUNCATE 重置表的自增计数器 (AUTO_INCREMENT / IDENTITY)。

      • TRUNCATE 不能被回滚 (在大多数数据库的事务中，一旦执行，即使 ROLLBACK 也无法恢复数据)。MySQL 的 InnoDB 支持事务性 TRUNCATE。

      • TRUNCATE 需要更高的权限 (通常是 DDL 权限)。

      • TRUNCATE 不会激活 DELETE 触发器。

    • 何时使用：当你确实需要快速清空整个表且不需要回滚时。

• 常见报错：

  • You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. (MySQL 安全模式)。

  • Cannot delete or update a parent row: a foreign key constraint fails (要删除的行被子表外键引用)。

  • 最严重错误：忘记 WHERE 导致全表数据被删除！ (预防：事务 + 先 SELECT 验证)。

15. ALTER TABLE (修改表结构)

• 用法： 修改现有表的结构，例如添加列、删除列、修改列数据类型、添加/删除约束、重命名列/表等。

• 常见操作示例：

  sql

  -- 添加新列
  ALTER TABLE employees ADD COLUMN middle_name VARCHAR(50);
  
  -- 删除列 (危险！数据丢失！)
  ALTER TABLE employees DROP COLUMN fax_number;
  
  -- 修改列数据类型 (小心！可能导致数据丢失或转换错误)
  ALTER TABLE products ALTER COLUMN price TYPE DECIMAL(10, 2); -- PostgreSQL
  ALTER TABLE products MODIFY COLUMN price DECIMAL(10, 2); -- MySQL
  
  -- 添加主键约束
  ALTER TABLE orders ADD PRIMARY KEY (order_id);
  
  -- 添加外键约束
  ALTER TABLE order_items ADD CONSTRAINT fk_order
      FOREIGN KEY (order_id) REFERENCES orders(order_id);
  
  -- 添加唯一约束
  ALTER TABLE users ADD CONSTRAINT uq_username UNIQUE (username);
  
  -- 添加 NOT NULL 约束 (通常要求列先没有NULL值)
  ALTER TABLE customers ALTER COLUMN email SET NOT NULL; -- PostgreSQL
  ALTER TABLE customers MODIFY COLUMN email VARCHAR(100) NOT NULL; -- MySQL
  
  -- 删除约束
  ALTER TABLE order_items DROP CONSTRAINT fk_order; -- 按约束名
  -- MySQL: ALTER TABLE order_items DROP FOREIGN KEY fk_order;
  
  -- 重命名列
  ALTER TABLE employees RENAME COLUMN phone_number TO phone; -- PostgreSQL / MySQL 8.0+
  -- MySQL (<8.0): ALTER TABLE employees CHANGE phone_number phone VARCHAR(20);
  
  -- 重命名表
  ALTER TABLE old_cust RENAME TO customers; -- PostgreSQL / MySQL
  -- SQL Server: sp_rename 'old_cust', 'customers';

• 注意事项：

  • 影响生产： ALTER TABLE 操作，尤其在大表上，可能锁表，导致应用程序在操作期间无法访问该表。在低峰期执行，评估影响。

  • 数据类型修改： 修改数据类型可能导致现有数据被截断或转换失败。务必先备份数据并测试。例如，将 VARCHAR(255) 改为 VARCHAR(10)，超长的数据会被截断。

  • 删除列/约束： 永久删除数据和结构。确认不再需要。

  • 添加 NOT NULL： 如果列中已有 NULL 值，添加 NOT NULL 约束会失败。需要先更新所有 NULL 值为有效值。

  • 外键/唯一约束： 添加约束前，确保现有数据满足约束条件，否则操作失败。

  • 重命名： 重命名表或列可能会破坏依赖它们的视图、存储过程、应用程序代码。需要同步修改。

• 常见报错：

  • Column 'column_name' cannot be converted to type 'new_type' (数据类型转换失败)。

  • Column 'column_name' contains null values. (添加 NOT NULL 时列存在 NULL 值)。

  • Duplicate entry '...' for key '...' (添加唯一/主键约束时数据已存在重复)。

  • Foreign key constraint 'fk_name' is incorrectly formed (添加外键失败：类型不匹配、引用的列不存在等)。

  • Table 'table_name' is locked. (表被其他操作锁定)。

  • Error on rename of '...' to '...' (errno: 150 - Foreign key constraint is incorrectly formed) (MySQL 重命名表时外键依赖问题)。

16. CASE ... WHEN ... THEN ... ELSE ... END (条件表达式)

• 用法： SQL 中的 "if-then-else" 逻辑。根据条件返回不同的值。可以在 SELECT, WHERE, ORDER BY, UPDATE SET 等几乎任何地方使用。

• 两种形式：

  1. 简单 CASE： 将表达式与一组简单值比较。

     sql

     CASE expression
         WHEN value1 THEN result1
         WHEN value2 THEN result2
         ...
         [ELSE else_result]
     END

  2. 搜索 CASE： 计算一组布尔表达式。

     sql

     CASE
         WHEN condition1 THEN result1
         WHEN condition2 THEN result2
         ...
         [ELSE else_result]
     END

• 示例：

  sql

  -- 简单 CASE：将订单状态码转换为描述
  SELECT order_id,
      CASE status
          WHEN 'P' THEN 'Pending'
          WHEN 'S' THEN 'Shipped'
          WHEN 'C' THEN 'Cancelled'
          ELSE 'Unknown'
      END AS status_description
  FROM orders;
  
  -- 搜索 CASE：根据分数划分等级
  SELECT student_name, score,
      CASE
          WHEN score >= 90 THEN 'A'
          WHEN score >= 80 THEN 'B'
          WHEN score >= 70 THEN 'C'
          WHEN score >= 60 THEN 'D'
          ELSE 'F'
      END AS grade
  FROM exam_results;
  
  -- 在 UPDATE 中使用：给不同部门的员工涨不同比例的薪水
  UPDATE employees
  SET salary = salary *
      CASE department_id
          WHEN 10 THEN 1.10 -- 部门10涨10%
          WHEN 20 THEN 1.08  -- 部门20涨8%
          ELSE 1.05          -- 其他部门涨5%
      END;

• 注意事项：

  • CASE 表达式会按顺序评估 WHEN 条件。第一个满足条件的 THEN 结果会被返回，后续条件不再评估。将最可能匹配或最严格的条件放在前面。

  • ELSE 子句是可选的。如果没有 ELSE 且所有 WHEN 条件都不满足，则 CASE 表达式返回 NULL。建议总是包含 ELSE 以确保预期结果。

  • 结果的数据类型：所有 THEN 和 ELSE 结果的数据类型应该兼容或可以隐式转换。

• 常见报错： 通常是逻辑错误导致结果不符合预期（条件顺序不对、漏掉某些情况、忘记 ELSE）。语法错误较少。

17. 字符串函数 (UPPER, LOWER, LENGTH / LEN)

• 用法： 处理字符串数据。

• 常见函数：

  • UPPER(string) / UCASE(string) (MySQL): 将字符串转换为大写。 SELECT UPPER('Hello World'); -- 'HELLO WORLD'

  • LOWER(string) / LCASE(string) (MySQL): 将字符串转换为小写。 SELECT LOWER('Hello World'); -- 'hello world'

  • LENGTH(string) / LEN(string) (SQL Server) / CHAR_LENGTH(string) / CHARACTER_LENGTH(string): 返回字符串的字符数。 SELECT LENGTH('你好'); -- 2 (2个字符)

  • DATALENGTH(string) (SQL Server) / OCTET_LENGTH(string) (PostgreSQL): 返回字符串的字节数。对于单字节字符集(如ASCII)，LENGTH 和 DATALENGTH 相同。对于多字节字符集(如UTF-8)，'你好' 的 LENGTH 是2 (字符数)，DATALENGTH 可能是6 (字节数，UTF-8中每个中文字符通常3字节)。

  • CONCAT(string1, string2, ...): 连接多个字符串。 SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees; (比 + 更标准，且能处理 NULL - 在标准 SQL 中 CONCAT('a', NULL) 通常返回 'a'，而 'a' + NULL 返回 NULL)。

  • SUBSTRING(string, start, length) / SUBSTR(string, start, [length]): 提取子字符串。索引通常从 1 开始。 SELECT SUBSTRING('Hello World', 7, 5); -- 'World'

  • TRIM([LEADING | TRAILING | BOTH] [characters] FROM string): 去除字符串首尾的指定字符（默认为空格）。 SELECT TRIM(' Hello '); -- 'Hello', SELECT TRIM(BOTH 'x' FROM 'xxxHelloxxx'); -- 'Hello'.

  • REPLACE(string, old_substring, new_substring): 替换字符串中的子串。 SELECT REPLACE('foo bar foo', 'foo', 'baz'); -- 'baz bar baz'.

• 注意事项：

  • 区分大小写： 字符串比较和函数（如 REPLACE）是否区分大小写取决于数据库的排序规则(COLLATION)。

  • 索引： 在函数处理过的列（如 WHERE UPPER(name) = 'JOHN'）上查询通常无法利用索引。如果经常需要不区分大小写搜索，考虑使用不区分大小写的排序规则，或在存储数据时统一大小写（如存储小写），或创建函数索引（如果数据库支持）。

  • NULL： 如果输入字符串是 NULL，大多数字符串函数返回 NULL。

  • 字符集： 了解数据库和列的字符集编码（如 UTF-8, Latin1），处理多语言数据时至关重要，影响 LENGTH/DATALENGTH 和字符处理。

• 常见报错： Invalid length parameter passed to the SUBSTRING function. (起始位置或长度参数无效)。Function 'xxx' not found. (函数名拼写错误或不支持)。隐式转换错误。

18. 聚合函数 (COUNT, SUM, AVG, MAX, MIN)

• 用法： 对一组值执行计算并返回单个值。通常与 GROUP BY 子句一起使用，对分组数据进行计算。也可以在 SELECT 中单独使用（对整个表或满足 WHERE 条件的行进行聚合）。

• 核心函数：

  • COUNT():

    • COUNT(*): 计算所有行数，包括包含 NULL 值的行。

    • COUNT(expression): 计算 expression 非 NULL 的行数。 COUNT(column_name) 计算该列非 NULL 值的个数。

    • 示例：

      sql

      SELECT COUNT(*) AS total_employees FROM employees; -- 员工总数
      SELECT department_id, COUNT(employee_id) AS emp_count FROM employees GROUP BY department_id; -- 每个部门的员工数
      SELECT COUNT(DISTINCT department_id) AS num_departments FROM employees; -- 不重复的部门数量

  • SUM(expression): 计算数值列 expression 的总和。忽略 NULL 值。

    • 示例：SELECT SUM(salary) AS total_payroll FROM employees WHERE department_id = 30;

  • AVG(expression): 计算数值列 expression 的平均值。忽略 NULL 值。等价于 SUM(expression) / COUNT(expression)。

    • 示例：SELECT AVG(salary) AS avg_salary FROM employees;

  • MAX(expression): 返回表达式 expression 的最大值。可用于数字、日期、字符串（按排序规则）。忽略 NULL。

    • 示例：SELECT MAX(hire_date) AS latest_hire FROM employees;

  • MIN(expression): 返回表达式 expression 的最小值。可用于数字、日期、字符串（按排序规则）。忽略 NULL。

    • 示例：SELECT MIN(price) AS cheapest_product FROM products;

• 注意事项：

  • NULL 处理： 聚合函数（除 COUNT(*) 外）都忽略 NULL 值。

  • DISTINCT： 可以在聚合函数内使用 DISTINCT 先对值去重再聚合（如 COUNT(DISTINCT department_id)）。

  • GROUP BY： 如果在 SELECT 列表中同时包含聚合函数和非聚合列，必须使用 GROUP BY 指定非聚合列的分组依据（除非整个表作为一个组）。

  • 数据类型： SUM 和 AVG 只能用于数值类型。MAX/MIN 可用于可排序的类型（数字、日期、时间、字符串）。

  • 空组： 如果组内没有行（或所有行的聚合列为 NULL），COUNT 返回 0，其他聚合函数返回 NULL。

• 常见报错：

  • Column 'table.column' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. (经典错误：非聚合列未在 GROUP BY 中列出)。

  • An aggregate may not appear in the WHERE clause unless it is in a subquery... (不能在 WHERE 中使用聚合函数过滤分组结果，应使用 HAVING)。

  • Operand data type ... is invalid for sum operator. (尝试对非数值列使用 SUM 或 AVG)。

19. 窗口函数 (ROW_NUMBER(), RANK(), DENSE_RANK() OVER(PARTITION BY ... ORDER BY ...))

• 用法： 对查询结果的一个窗口（一组行） 执行计算。与聚合函数不同，窗口函数不会将多行合并为一行，而是为每一行返回一个基于其所在窗口的计算结果。这是进行复杂排名、计算移动平均、累计求和等的强大工具。

• 核心语法：

  sql

  function_name([arguments]) OVER (
      [PARTITION BY partition_expression, ...] -- 将结果集划分为分区(组)，函数在每个分区内独立计算
      [ORDER BY sort_expression [ASC | DESC], ...] -- 定义分区内的排序顺序，对排名、累计算至关重要
      [window_frame_clause] -- 定义窗口框架(ROWS/RANGE BETWEEN ... AND ...)，指定计算时考虑分区内的哪些行(如前N行、当前行及之前所有行等)。默认是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  )

• 常用函数：

  • ROW_NUMBER(): 为分区内的每一行分配一个唯一的、连续的整数序号，从1开始。根据 ORDER BY 排序。相同值也会获得不同序号。

    • 示例：SELECT name, score, ROW_NUMBER() OVER (ORDER BY score DESC) AS rank FROM students; (按分数降序排名，分数相同但名次不同)。

  • RANK(): 为分区内的行分配排名。相同值的行获得相同排名，并留下空位。例如：1, 2, 2, 4, ...

    • 示例：SELECT name, score, RANK() OVER (ORDER BY score DESC) AS rank FROM students; (分数相同的并列第二，下一个是第四名)。

  • DENSE_RANK(): 为分区内的行分配排名。相同值的行获得相同排名，且排名是连续的，没有空位。例如：1, 2, 2, 3, ...

    • 示例：SELECT name, score, DENSE_RANK() OVER (ORDER BY score DESC) AS rank FROM students; (分数相同的并列第二，下一个是第三名)。

  • 聚合函数作为窗口函数： SUM(), AVG(), MIN(), MAX(), COUNT() 也可以配合 OVER 子句使用。

    • 示例：

      sql

      -- 计算每个部门的累计工资 (按员工ID排序)
      SELECT employee_id, department_id, salary,
             SUM(salary) OVER (PARTITION BY department_id ORDER BY employee_id) AS running_total
      FROM employees;
      
      -- 计算每个员工在其部门的工资排名
      SELECT employee_id, department_id, salary,
             RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_salary_rank
      FROM employees;

• 关键概念：

  • PARTITION BY: 定义窗口的划分依据（类似于 GROUP BY，但不聚合）。函数在每个分区内独立计算。如果省略，整个结果集作为一个分区。

  • ORDER BY: 定义分区内的行顺序。对于 ROW_NUMBER(), RANK(), DENSE_RANK() 和需要顺序的聚合（如累加）是必需的。它决定了函数计算的顺序。

  • 窗口框架 (ROWS/RANGE BETWEEN ... AND ...): 精确定义分区内哪些行参与当前行的计算。常见选项：

    • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW - 默认值，从分区开始到当前行（累计）。

    • ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING - 当前行前3行、当前行、当前行后1行。

    • ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING - 整个分区。

    • RANGE 与 ROWS 类似，但基于值范围（例如 RANGE BETWEEN 100 PRECEDING AND 200 FOLLOWING 考虑值在 [当前值-100, 当前值+200] 范围内的行）。

• 注意事项：

  • 执行顺序： 窗口函数在 WHERE, GROUP BY, HAVING 之后，ORDER BY 之前计算。

  • 性能： 窗口函数可能很强大，但也可能很消耗资源，尤其是在大结果集上进行复杂分区和排序时。合理使用索引（特别是 PARTITION BY 和 ORDER BY 涉及的列）能提升性能。

  • ORDER BY 的重要性： 对于排名函数和累积聚合，ORDER BY 决定了顺序逻辑。没有 ORDER BY，排名是任意的（通常按物理顺序），累积值无意义。

• 常见报错：

  • Windowed functions can only appear in the SELECT or ORDER BY clauses. (不能在 WHERE, GROUP BY, HAVING 中直接使用窗口函数的结果。需用子查询包装)。

  • The function 'RANK' must have an OVER clause. (忘记写 OVER 子句)。

  • Window functions not supported in this context. (在旧版本或不支持窗口函数的数据库中使用)。

  • The ORDER BY clause is invalid in views, inline functions... (在视图或内联函数中定义窗口函数时，ORDER BY 可能有特殊限制，除非有 TOP, OFFSET-FETCH 或 FOR XML 等)。

  • Incorrect syntax near 'ROWS'. (窗口框架语法错误)。

20. UNION / UNION ALL (结果集合并)

• 用法： 将两个或多个 SELECT 语句的结果集垂直合并成一个结果集。要求各结果集的列数、对应列的数据类型必须兼容。

• 区别：

  • UNION: 合并结果集并自动去除重复行。会进行排序去重操作，可能有性能开销。

  • UNION ALL: 合并结果集并保留所有行，包括重复行。性能更好，因为它不进行去重排序。

• 示例：

  sql

  -- 获取所有员工和客户的姓名和类型 (去重)
  SELECT first_name AS name, 'Employee' AS type FROM employees
  UNION
  SELECT customer_name, 'Customer' FROM customers;
  
  -- 获取过去3年和未来1年的年份列表 (允许重复)
  SELECT YEAR(CURRENT_DATE) - 2 AS year
  UNION ALL
  SELECT YEAR(CURRENT_DATE) - 1
  UNION ALL
  SELECT YEAR(CURRENT_DATE)
  UNION ALL
  SELECT YEAR(CURRENT_DATE) + 1;

• 注意事项：

  • 列兼容性： 各 SELECT 语句的列数必须相同，且对应位置列的数据类型必须兼容或可隐式转换。列名通常取自第一个 SELECT。

  • ORDER BY： 整个 UNION 结果集的 ORDER BY 子句只能出现在最后一个 SELECT 语句之后。

  • 性能： 优先使用 UNION ALL，除非确实需要去重。UNION 的去重操作在大数据集上可能很慢。

  • NULL 值： NULL 值在 UNION 去重时被认为是彼此相等的（即多个 NULL 会被视为重复而去除一个）。

• 常见报错：

  • All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists. (列数不相等)。

  • Conversion failed when converting the varchar value '...' to data type int. (对应列数据类型不兼容)。

  • Incorrect syntax near the keyword 'UNION'. (通常是在 UNION 中间的 SELECT 语句后写了 ORDER BY)。

三、总结与最佳实践

1. 理解 NULL： 时刻牢记 NULL 的特殊性（IS NULL / IS NOT NULL， 聚合函数忽略 NULL， NOT IN 子查询的陷阱）。这是 SQL 中最常见的错误来源之一。

2. 明确 JOIN 类型： 深刻理解 INNER JOIN, LEFT JOIN, RIGHT JOIN, CROSS JOIN 的区别和适用场景。LEFT JOIN 和 ON vs WHERE 是另一个高频错误点。画维恩图帮助理解！

3. GROUP BY 与聚合： 牢记 SELECT 中的非聚合列必须出现在 GROUP BY 中。理解 WHERE (行过滤) 和 HAVING (组过滤) 的区别。

4. UPDATE/DELETE 的 WHERE： 永远在执行前用 SELECT 验证 WHERE 条件！遗漏 WHERE 会导致灾难性后果。使用事务。

5. DROP / TRUNCATE / DELETE： 理解它们的区别和危险性。操作前备份，操作时谨慎。

6. 性能意识：

   • 避免 SELECT *，只选择需要的列。

   • 为 WHERE, JOIN ON, ORDER BY, GROUP BY 的列创建合适索引。

   • 小心 LIKE '%prefix'（前导通配符）和函数包装的列（UPPER(column)），它们通常使索引失效。

   • 优先使用 EXISTS / NOT EXISTS 代替 IN / NOT IN（尤其在大数据集或 NOT 逻辑时）。

   • 优先使用 UNION ALL 代替 UNION（除非明确需要去重）。

   • 理解窗口函数的开销。

   • 使用 EXPLAIN / 执行计划分析查询性能。

7. 可读性与维护性：

   • 使用表别名和列别名。

   • 格式化你的 SQL（缩进、换行）。

   • 添加注释说明复杂逻辑。

   • 使用显式的 JOIN 语法（INNER JOIN, LEFT JOIN）而不是老式的逗号分隔和 WHERE 连接条件。

8. 安全：

   • 防止 SQL 注入：永远不要直接拼接用户输入到 SQL 语句中。使用参数化查询 (Prepared Statements) 或 ORM 框架。

   • 最小权限原则：应用程序连接数据库的用户只应拥有执行其功能所需的最小权限。

9. 测试： 在开发环境和预生产环境充分测试你的 SQL，特别是 DDL (CREATE, ALTER, DROP) 和 DML (UPDATE, DELETE) 语句。

10. 学习资源： 官方文档是最好的朋友！(MySQL, PostgreSQL, SQL Server, Oracle 各有特色)。多练习，动手写比只看更重要。