南瓜慢说知识库

# SQL语句

SQL 是操作和查询关系型数据库的核心语言，以下是其主要知识点的系统总结，适合学习和复习使用：

# SQL基础知识

# 一、基础语法

  1. 数据定义语言 (DDL)

    • CREATE: 创建数据库、表、索引等
    • ALTER: 修改表结构（添加/删除列、修改数据类型）
    • DROP: 删除数据库、表、索引
    • TRUNCATE: 快速清空表数据（不可回滚）

  2. 数据操作语言 (DML)

    • INSERT INTO: 插入数据
    • DELETE: 删除数据（可回滚）
    • UPDATE: 修改数据
    • SELECT: 数据查询（核心功能）

# 二、数据查询（SELECT 核心）

  1. 基础查询

    • SELECT * FROM table
    • SELECT column1, column2
    • DISTINCT: 去重
    • AS: 别名（列或表）

  2. 条件过滤

    • WHERE: 条件筛选
    • 运算符：=, <>, >, <, BETWEEN, IN, LIKE（通配符 % 和 _）
    • 逻辑运算符：AND, OR, NOT

  3. 排序与分页

    • ORDER BY column ASC/DESC
    • 分页语法：
      • MySQL: LIMIT n OFFSET m
      • SQL Server: OFFSET m ROWS FETCH NEXT n ROWS ONLY
      • Oracle: ROWNUM

  4. 聚合与分组

    • 聚合函数：COUNT(), SUM(), AVG(), MAX(), MIN()
    • GROUP BY: 按列分组
    • HAVING: 分组后过滤（与 WHERE 区别：WHERE 在聚合前过滤，HAVING 在聚合后）

# 三、多表操作

  1. JOIN 连接

    • INNER JOIN: 返回匹配的行
    • LEFT/RIGHT JOIN: 保留左/右表全部数据
    • FULL OUTER JOIN: 返回所有匹配和不匹配的行
    • CROSS JOIN: 笛卡尔积
    • NATURAL JOIN: 自动按同名列连接（慎用）

  2. 子查询（嵌套查询）

    • 标量子查询（返回单值，如 SELECT (SELECT ...)）
    • 关联子查询（依赖外层查询，如 EXISTS）
    • IN, ANY, ALL, EXISTS 的应用

# 四、数据控制

  1. 事务控制 (TCL)

    • COMMIT: 提交事务
    • ROLLBACK: 回滚事务
    • SAVEPOINT: 设置保存点
    • ACID 特性：原子性、一致性、隔离性、持久性

  2. 权限管理 (DCL)

    • GRANT: 授予权限（如 GRANT SELECT ON table TO user）
    • REVOKE: 撤销权限

# 五、高级功能

  1. 窗口函数

    • ROW_NUMBER(), RANK(), DENSE_RANK()
    • OVER(PARTITION BY ... ORDER BY ...)

  2. 公共表表达式 (CTE)

    • WITH cte_name AS (SELECT ...): 临时结果集复用

  3. 索引优化

    • 作用：加速查询（B-tree、Hash、全文索引）
    • 创建：CREATE INDEX index_name ON table(column)
    • 注意：索引的维护成本和适用场景

  4. 存储过程 & 触发器

    • 存储过程：预编译的SQL代码块（CREATE PROCEDURE）
    • 触发器：自动执行的操作（如 BEFORE INSERT）

# 六、其他关键点

  1. 约束

    • PRIMARY KEY（主键）
    • FOREIGN KEY（外键约束）
    • UNIQUE（唯一值）
    • NOT NULL（非空）
    • CHECK（条件检查，如 CHECK(age > 0)）

  2. NULL 处理

    • IS NULL / IS NOT NULL
    • COALESCE(): 返回第一个非NULL值
    • NULLIF(): 两值相等时返回NULL

  3. 性能优化

    • 避免 SELECT *，减少不必要的数据传输
    • 使用 EXPLAIN 分析执行计划
    • 警惕全表扫描和笛卡尔积

# 七、常见问题示例

  1. JOIN 与 WHERE 的执行顺序

    SELECT a.id, b.name 
FROM table_a a
LEFT JOIN table_b b ON a.id = b.a_id
WHERE b.date > '2023-01-01';

  2. 窗口函数排名

    SELECT name, salary, 
       RANK() OVER (ORDER BY salary DESC) as rank
FROM employees;

  3. 递归查询（树形结构）

    WITH RECURSIVE cte AS (
  SELECT id, parent_id FROM tree WHERE parent_id IS NULL
  UNION ALL
  SELECT t.id, t.parent_id FROM tree t JOIN cte ON t.parent_id = cte.id
)
SELECT * FROM cte;

掌握以上知识点后，可通过实际项目练习（如复杂查询优化、数据库设计）加深理解。注意不同数据库（MySQL、PostgreSQL、Oracle）的语法差异。

# 索引

在 SQL 中，有效创建和使用索引是优化查询性能的关键。以下是针对不同场景的索引设计策略、使用技巧以及避免全表扫描的实践方法：

# 一、有效创建索引的核心原则

  1. 选择需要索引的列

    • 高频查询条件：为 WHERE、JOIN、ORDER BY、GROUP BY 中频繁出现的列创建索引。
    • 高选择性列：列的取值越唯一（如主键、用户 ID），索引效果越好。
    • 避免过度索引：索引会占用存储并降低写入性能，只为必要的列创建。

  2. 复合索引的列顺序

    • 最左前缀原则：复合索引 (a, b, c) 对 a、a AND b、a AND b AND c 生效，但对 b 或 c 单独查询无效。
    • 高频列优先：将查询中使用频率更高的列放在复合索引左侧。
    • 排序优化：若需对某列排序（ORDER BY），将该列放在复合索引中。

  3. 索引类型选择

    • B-tree 索引：默认类型，支持范围查询（>、<、BETWEEN）和排序。
    • Hash 索引：仅支持精确匹配（=），适用于内存表或等值查询。
    • 全文索引：针对大文本字段（如 TEXT）的关键词搜索（如 MATCH ... AGAINST）。
    • 空间索引：用于地理数据（如 GEOMETRY）。

# 二、不同数据类型的索引优化

  1. 字符串类型（CHAR/VARCHAR/TEXT）

    • 前缀索引：对长字符串（如 VARCHAR(255)）只索引前 N 个字符，节省空间。
      CREATE INDEX idx_name_prefix ON users (name(20)); -- 仅索引前 20 个字符
    • 全文索引：适用于模糊搜索（如 LIKE '%keyword%'）。
      CREATE FULLTEXT INDEX idx_content ON articles (content);

  2. 数值类型（INT/FLOAT/DECIMAL）

    • 直接使用 B-tree 索引，注意避免在查询中对字段进行运算。
    • 示例：WHERE age + 1 > 20 会导致索引失效，应改写为 WHERE age > 19。

  3. 日期与时间（DATE/DATETIME/TIMESTAMP）

    • 常用于范围查询（如按时间段过滤），适合 B-tree 索引。
    • 避免函数操作：
      -- 索引失效的写法
SELECT * FROM orders WHERE YEAR(order_date) = 2023;
-- 优化为范围查询
SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

  4. 枚举类型（ENUM）或低基数列

    • 基数低（如性别只有男/女）的列索引效果差，需谨慎使用。

  5. JSON/XML 类型

    • 提取特定字段生成虚拟列，并为虚拟列创建索引（MySQL 支持）：
      ALTER TABLE products ADD COLUMN price DECIMAL(10,2) AS (JSON_EXTRACT(data, '$.price'));
CREATE INDEX idx_price ON products (price);

# 三、确保索引命中的关键技巧

  1. 避免索引失效的写法

    • 在索引列上使用函数或计算：
      -- 索引失效
SELECT * FROM users WHERE UPPER(name) = 'ALICE';
-- 优化：提前处理输入，保持列原始值
SELECT * FROM users WHERE name = 'alice';
    • 隐式类型转换：
      -- 假设 user_id 是字符串类型，传入数字会导致索引失效
SELECT * FROM users WHERE user_id = 123; -- 错误
SELECT * FROM users WHERE user_id = '123'; -- 正确
    • LIKE 左模糊匹配：
      -- 索引失效
SELECT * FROM products WHERE name LIKE '%apple%';
-- 仅右模糊可以使用索引
SELECT * FROM products WHERE name LIKE 'apple%';

  2. 利用覆盖索引（Covering Index）

    • 索引包含查询所需的所有列，避免回表查询数据行。
    • 示例：
      -- 创建覆盖索引
CREATE INDEX idx_covering ON orders (user_id, total_amount);
-- 查询只需访问索引
SELECT user_id, total_amount FROM orders WHERE user_id = 100;

  3. 强制使用索引（谨慎使用）

    • 某些情况下优化器可能未选择最优索引，可手动指定：
      SELECT * FROM users FORCE INDEX (idx_email) WHERE email = 'alice@example.com';

# 四、避免全表扫描的实战方法

  1. 确保 WHERE 条件有索引

    • 若查询未命中索引，会触发全表扫描。
    • 示例：
      -- 无索引时全表扫描
SELECT * FROM logs WHERE message LIKE 'error%';
-- 添加索引后优化
CREATE INDEX idx_message_prefix ON logs (message(50));

  2. 避免 OR 连接多个条件

    • 使用 OR 可能导致索引失效，可改写为 UNION：
      -- 低效写法
SELECT * FROM users WHERE age > 30 OR country = 'US';
-- 优化为 UNION
SELECT * FROM users WHERE age > 30 
UNION 
SELECT * FROM users WHERE country = 'US';

  3. 分页查询优化

    • 避免 OFFSET 过大时扫描大量数据，改用游标分页（基于有序列）：
      -- 低效写法
SELECT * FROM orders ORDER BY id LIMIT 1000 OFFSET 100000;
-- 高效写法（记录上次查询的最后一个 id）
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 1000;

  4. 定期分析索引使用情况

    • 使用数据库工具（如 MySQL 的 EXPLAIN 或 SHOW INDEX）检查索引是否被命中：
      EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';

# 五、总结

  • 索引是双刃剑：合理设计可提升查询速度，但过多索引会增加写入开销。
  • 理解数据分布：通过 SELECT COUNT(DISTINCT column)/COUNT(*) 计算列的选择性。
  • 动态调整：随着数据量和查询模式变化，定期审查和优化索引。

通过以上策略，可以显著提高查询效率，减少全表扫描的发生。

# B+树索引

# 1. B+ Tree 的核心结构

B+ Tree 是一种平衡多路搜索树，是 B Tree 的变种，广泛应用于数据库索引和文件系统。其核心特点如下：

# 1.1 B+ Tree 的层级结构

  1. 非叶子节点（索引节点）

    • 仅存储键值（Key），不存储实际数据。
    • 每个节点包含 n 个键值和 n+1 个子节点指针，键值用于导航到子节点。

  2. 叶子节点（数据节点）

    • 存储键值和对应的数据（如数据行地址或完整数据）。
    • 所有叶子节点通过指针形成有序双向链表，支持高效的范围查询。

# 1.2 B+ Tree 的特性

  • 平衡性：所有叶子节点位于同一层，保证查询稳定性。
  • 高扇出（Fan-out）：每个节点可存储大量键值，降低树的高度。
  • 有序性：叶子节点链表天然支持顺序访问。

# 2. 为什么数据库选择 B+ Tree 做索引？

# 2.1 对比其他数据结构

  1. 哈希表

    • 优点：等值查询 O(1) 时间复杂度。
    • 缺点：不支持范围查询，哈希冲突处理复杂。
    • B+ Tree 胜在支持范围查询和排序。

  2. 二叉搜索树（BST）

    • 缺点：树高与数据量呈对数关系（O(log N)），数据量大时深度过高，导致磁盘 I/O 次数多。
    • B+ Tree 通过多路分支显著降低树高（如 3 层 B+ Tree 可存储千万级数据）。

  3. B Tree

    • B Tree 的节点既存键值也存数据，而 B+ Tree 的数据仅存于叶子节点。
    • B+ Tree 的优势：
      • 非叶子节点不存数据，每个节点可容纳更多键值，进一步减少树高。
      • 叶子节点链表支持高效的范围查询和全表遍历。

# 2.2 适合磁盘存储的设计

  • 磁盘 I/O 友好：
    数据库数据通常存储在磁盘上，而磁盘访问以「块」为单位（如 4KB）。
    B+ Tree 的节点大小通常设计为磁盘块大小，单次 I/O 可读取一个节点的全部键值，减少磁盘寻道次数。
  • 示例：
    假设每个节点存储 100 个键值，3 层 B+ Tree 可索引 100^3 = 1,000,000 条数据，仅需 3 次磁盘 I/O。

# 2.3 高效的范围查询

  • 叶子节点的双向链表允许快速遍历某个范围的数据（如 WHERE id BETWEEN 100 AND 200）。
  • B Tree 的范围查询需回溯父节点，效率低于 B+ Tree。

# 2.4 稳定的插入和删除性能

  • 分裂与合并：B+ Tree 通过节点的分裂（插入时）和合并（删除时）维持平衡，操作复杂度为 O(log N)。
  • 与 LSM Tree 对比：LSM Tree 更适合写多读少场景（如日志系统），而 B+ Tree 在读多写少的 OLTP 场景中更优。

# 3. B+ Tree 的典型应用场景

  1. 数据库索引

    • MySQL InnoDB 的主键索引（聚簇索引）直接存储数据行，二级索引存储主键值。
    • 范围查询（如 ORDER BY、GROUP BY）依赖叶子节点的有序性。

  2. 文件系统

    • 如 NTFS、ReiserFS 使用 B+ Tree 管理文件和目录的元数据。

# 4. B+ Tree 的缺陷与应对

  1. 写放大问题

    • 频繁的插入/删除可能导致节点分裂与合并，影响写入性能。
    • 优化：使用缓冲机制（如 InnoDB 的 Change Buffer）延迟写入操作。

  2. 内存依赖

    • 若索引无法完全缓存到内存，频繁的磁盘 I/O 会降低性能。
    • 优化：通过增大内存或使用 SSD 提升 I/O 速度。

# 5. 总结：B+ Tree 的不可替代性

  • 核心优势：
    1. 多路平衡设计减少磁盘 I/O；
    2. 叶子节点链表支持高效范围查询；
    3. 适合高并发随机读和顺序扫描。
  • 现代数据库（如 MySQL、PostgreSQL）的默认选择，除非特定场景（如纯等值查询）才考虑哈希索引。

# 常用场景及SQL语句

以下是 20 个常见的 SQL 面试需求及实现语句，涵盖基本查询、聚合、连接、窗口函数等核心知识点，附详细注释：

# 1. 找出工资最高的前3名员工，并显示部门

WITH ranked_employees AS (
  SELECT 
    e.name, 
    e.salary, 
    d.department_name,
    ROW_NUMBER() OVER (ORDER BY e.salary DESC) AS rn
  FROM employees e
  JOIN departments d ON e.department_id = d.id
)
SELECT name, salary, department_name
FROM ranked_employees
WHERE rn <= 3;

# 2. 统计每个部门的平均工资

SELECT 
  d.department_name, 
  AVG(e.salary) AS avg_salary
FROM employees e
JOIN departments d ON e.department_id = d.id
GROUP BY d.department_name;

# 3. 查找没有员工的部门

SELECT d.department_name
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
WHERE e.id IS NULL;

# 4. 找出工资高于部门平均工资的员工

SELECT 
  e.name, 
  e.salary, 
  d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id
JOIN (
  SELECT 
    department_id, 
    AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department_id
) dept_avg ON e.department_id = dept_avg.department_id
WHERE e.salary > dept_avg.avg_salary;

# 5. 计算每个员工的工资排名（按部门分组）

SELECT 
  name, 
  department_name,
  salary,
  RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank
FROM employees e
JOIN departments d ON e.department_id = d.id;

# 6. 查询每个部门工资最高的员工

WITH dept_max AS (
  SELECT 
    department_id, 
    MAX(salary) AS max_salary
  FROM employees
  GROUP BY department_id
)
SELECT 
  e.name, 
  d.department_name, 
  e.salary
FROM employees e
JOIN departments d ON e.department_id = d.id
JOIN dept_max dm ON e.department_id = dm.department_id AND e.salary = dm.max_salary;

# 7. 找出入职时间最早的3名员工

SELECT 
  name, 
  hire_date
FROM employees
ORDER BY hire_date
LIMIT 3;  -- MySQL
-- SQL Server: SELECT TOP 3 ...
-- Oracle: WHERE ROWNUM <= 3

# 8. 查询工资高于经理的员工

SELECT 
  e.name AS employee_name,
  e.salary AS employee_salary,
  m.name AS manager_name,
  m.salary AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;

# 9. 统计每年入职的员工数量

SELECT 
  EXTRACT(YEAR FROM hire_date) AS hire_year,
  COUNT(*) AS employee_count
FROM employees
GROUP BY hire_year
ORDER BY hire_year;

# 10. 删除重复记录（保留一条）

DELETE FROM employees
WHERE id NOT IN (
  SELECT MIN(id)
  FROM employees
  GROUP BY email  -- 假设 email 是唯一标识
);

# 11. 查询连续3天登录的用户

SELECT 
  user_id,
  login_date
FROM (
  SELECT 
    user_id,
    login_date,
    LAG(login_date, 2) OVER (PARTITION BY user_id ORDER BY login_date) AS prev2_date
  FROM logins
) t
WHERE login_date = prev2_date + INTERVAL '2 days';

# 12. 计算累计工资（按入职时间排序）

SELECT 
  name,
  hire_date,
  salary,
  SUM(salary) OVER (ORDER BY hire_date) AS cumulative_salary
FROM employees;

# 13. 查询每个部门的工资总和，并显示总占比

SELECT 
  department_name,
  total_salary,
  total_salary / SUM(total_salary) OVER () AS salary_ratio
FROM (
  SELECT 
    d.department_name,
    SUM(e.salary) AS total_salary
  FROM employees e
  JOIN departments d ON e.department_id = d.id
  GROUP BY d.department_name
) dept_salary;

# 14. 找出工资超过公司平均工资的部门

SELECT 
  department_name
FROM (
  SELECT 
    d.department_name,
    AVG(e.salary) OVER () AS company_avg,
    AVG(e.salary) AS dept_avg
  FROM employees e
  JOIN departments d ON e.department_id = d.id
  GROUP BY d.department_name
) t
WHERE dept_avg > company_avg;

# 15. 按工资区间统计员工数量（如 0-5000, 5001-10000）

SELECT 
  CASE 
    WHEN salary <= 5000 THEN '0-5000'
    WHEN salary <= 10000 THEN '5001-10000'
    ELSE '10000+'
  END AS salary_range,
  COUNT(*) AS employee_count
FROM employees
GROUP BY salary_range;

# 16. 查询每个部门工资第二高的员工

WITH ranked AS (
  SELECT 
    department_id,
    name,
    salary,
    DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rk
  FROM employees
)
SELECT 
  d.department_name,
  r.name,
  r.salary
FROM ranked r
JOIN departments d ON r.department_id = d.id
WHERE rk = 2;

# 17. 查询最近30天活跃的用户

SELECT 
  user_id,
  last_active_date
FROM user_activity
WHERE last_active_date >= CURRENT_DATE - INTERVAL '30 days';

# 18. 将行数据转换为列（如按部门显示男女员工数量）

SELECT 
  department_name,
  SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) AS male_count,
  SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS female_count
FROM employees e
JOIN departments d ON e.department_id = d.id
GROUP BY department_name;

# 19. 递归查询员工及其下属层级

WITH RECURSIVE employee_hierarchy AS (
  SELECT 
    id, 
    name, 
    manager_id,
    1 AS level
  FROM employees
  WHERE manager_id IS NULL  -- 假设顶层经理的 manager_id 为 NULL
  UNION ALL
  SELECT 
    e.id, 
    e.name, 
    e.manager_id,
    eh.level + 1
  FROM employees e
  JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;

# 20. 分页查询（第4页，每页10条）

SELECT *
FROM employees
ORDER BY id
LIMIT 10 OFFSET 30;  -- MySQL
-- SQL Server: OFFSET 30 ROWS FETCH NEXT 10 ROWS ONLY
-- Oracle: WHERE ROWNUM BETWEEN 31 AND 40

# 提示

  1. 理解需求：明确问题中的过滤条件、排序规则和分组逻辑。
  2. 优化性能：使用 EXPLAIN 分析执行计划，避免全表扫描。
  3. 注意语法差异：不同数据库（MySQL、PostgreSQL、Oracle）的分页、日期函数等可能不同。

# SQL的测试

以下是SQL单元测试和集成测试的详细分步指南：

# 一、SQL单元测试

目标：验证单个SQL对象（存储过程、函数、视图、触发器等）的独立功能。

# 1. 选择测试框架

  • 数据库专用工具：
    • pgTAP（PostgreSQL）：支持TAP协议的测试框架。
    • tsqlt（SQL Server）：集成SQL Server的单元测试框架。
    • utPLSQL（Oracle）：针对PL/SQL的测试框架。
  • 通用语言工具：
    • Python：unittest + pytest + 数据库驱动（如psycopg2、sqlalchemy）。
    • Java：JUnit + Testcontainers + JDBC。
    • Node.js：Jest + node-postgres/mysql2。

# 2. 测试用例设计

  • 覆盖场景：
    • 正常输入验证（如正确参数返回预期结果）。
    • 边界条件（如空值、极值、超长字符串）。
    • 异常输入（如非法参数触发错误处理）。
    • 副作用验证（如触发器更新相关表）。

# 3. 实现步骤示例（以PostgreSQL + pgTAP为例）

步骤1：安装pgTAP

CREATE EXTENSION IF NOT EXISTS pgtap;

步骤2：编写测试脚本

BEGIN;

-- 测试存储过程：add_employee
SELECT plan(2);  -- 计划运行2个测试

-- 准备测试数据
INSERT INTO departments (id, name) VALUES (1, 'IT');

-- 测试1：添加员工并验证结果
SELECT lives_ok(
  $$CALL add_employee('Alice', 1, 50000)$$,
  '添加员工应成功'
);

-- 测试2：验证员工表是否插入新记录
SELECT results_eq(
  $$SELECT name FROM employees WHERE department_id = 1$$,
  ARRAY['Alice'],
  '员工表中应有Alice'
);

SELECT * FROM finish();
ROLLBACK;

步骤3：运行测试

pg_prove -d your_dbname tests/test_employees.sql

# 4. 最佳实践

  • 隔离性：每个测试用例在事务中运行，结束后回滚（BEGIN/ROLLBACK）。
  • 数据准备：使用INSERT直接构造测试数据，或用COPY导入CSV。
  • 清理资源：避免残留数据影响其他测试。

# 二、SQL集成测试

目标：验证多个SQL对象协同工作的正确性（如事务、跨表操作）。

# 1. 测试场景设计

  • 跨表事务（如转账操作：账户A扣款，账户B加款）。
  • 复杂查询（如多表JOIN后的聚合结果）。
  • 并发操作（如同时读写时的锁机制）。

# 2. 实现步骤示例（以Python + pytest为例）

步骤1：安装依赖

pip install pytest psycopg2-binary

步骤2：编写测试用例

# test_transactions.py
import pytest
import psycopg2

@pytest.fixture
def db_connection():
    conn = psycopg2.connect("dbname=testdb user=postgres")
    yield conn
    conn.close()

def test_transfer_transaction(db_connection):
    cursor = db_connection.cursor()
    # 初始化测试数据
    cursor.execute("INSERT INTO accounts (id, balance) VALUES (1, 100), (2, 0)")
    db_connection.commit()

    try:
        # 执行转账事务
        cursor.execute("BEGIN")
        cursor.execute("UPDATE accounts SET balance = balance - 50 WHERE id = 1")
        cursor.execute("UPDATE accounts SET balance = balance + 50 WHERE id = 2")
        cursor.execute("COMMIT")
    except:
        cursor.execute("ROLLBACK")

    # 验证结果
    cursor.execute("SELECT balance FROM accounts WHERE id IN (1, 2) ORDER BY id")
    balances = cursor.fetchall()
    assert balances == [(50,), (50,)], "转账后余额应正确更新"

步骤3：运行测试

pytest test_transactions.py -v

# 3. 高级技巧

  • 测试容器化：使用Testcontainers启动临时数据库。
    from testcontainers.postgres import PostgresContainer

def test_with_container():
    with PostgresContainer("postgres:13") as postgres:
        conn = psycopg2.connect(postgres.get_connection_url())
        # 运行测试...
  • 性能测试：使用EXPLAIN ANALYZE验证查询计划。
    EXPLAIN ANALYZE SELECT * FROM large_table WHERE condition;

# 三、持续集成（CI）集成

步骤1：配置CI文件（以GitHub Actions为例）

# .github/workflows/sql-tests.yml
name: SQL Tests

on: [push]

jobs:
  test:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:13
        env:
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: postgres
        ports:
          - 5432:5432
    steps:
    - uses: actions/checkout@v2
    - name: Install dependencies
      run: pip install pytest psycopg2-binary
    - name: Run tests
      env:
        DB_URL: postgresql://postgres:postgres@localhost:5432/postgres
      run: pytest -v

# 四、工具推荐

工具 适用场景 特点
pgTAP PostgreSQL单元测试 原生SQL语法，深度集成
tSQLt SQL Server单元测试 可视化支持（SSMS插件）
DBUnit Java数据库测试 XML/Excel管理测试数据
Great Expectations 数据质量验证 支持复杂数据规则断言

# 五、总结

  • 单元测试：聚焦单一对象，快速反馈逻辑错误。
  • 集成测试：验证组件协作，确保数据一致性。
  • 自动化：通过CI/CD实现持续验证，降低回归风险。

# 存储过程

# SQL 存储过程的定义与参数化详解

# 一、存储过程的定义

存储过程（Stored Procedure） 是预编译的 SQL 代码块，可接受输入参数、执行逻辑操作并返回结果。它通常用于封装复杂业务逻辑，提升代码复用性和安全性。

# 二、存储过程的基本语法（以常见数据库为例）

# 1. MySQL
DELIMITER //

CREATE PROCEDURE procedure_name (
    [IN | OUT | INOUT] parameter1 datatype,
    [IN | OUT | INOUT] parameter2 datatype
)
BEGIN
    -- SQL 逻辑代码
END //

DELIMITER ;
# 2. SQL Server
CREATE PROCEDURE procedure_name 
    @parameter1 datatype [ = default_value ],
    @parameter2 datatype OUTPUT
AS
BEGIN
    -- SQL 逻辑代码
END
# 3. PostgreSQL
CREATE OR REPLACE PROCEDURE procedure_name (
    parameter1 datatype,
    INOUT parameter2 datatype DEFAULT NULL
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- SQL 逻辑代码
END;
$$;

# 三、参数类型

类型 说明
IN 输入参数（默认），调用时传入值，过程内部不可修改。
OUT 输出参数，过程内部修改后返回给调用者。
INOUT 输入输出参数，调用时传入初始值，过程可修改并返回新值。

# 四、参数化存储过程示例

# 1. MySQL 示例：带输入参数的查询
DELIMITER //

CREATE PROCEDURE GetEmployeeByDepartment (
    IN dept_id INT
)
BEGIN
    SELECT name, salary 
    FROM employees 
    WHERE department_id = dept_id;
END //

DELIMITER ;

-- 调用
CALL GetEmployeeByDepartment(2);
# 2. SQL Server 示例：带输入和输出参数的统计
CREATE PROCEDURE GetDepartmentSalaryStats 
    @dept_id INT,
    @avg_salary DECIMAL(10,2) OUTPUT,
    @max_salary DECIMAL(10,2) OUTPUT
AS
BEGIN
    SELECT 
        @avg_salary = AVG(salary),
        @max_salary = MAX(salary)
    FROM employees 
    WHERE department_id = @dept_id;
END

-- 调用
DECLARE @avg DECIMAL(10,2), @max DECIMAL(10,2);
EXEC GetDepartmentSalaryStats 2, @avg OUTPUT, @max OUTPUT;
SELECT @avg AS AvgSalary, @max AS MaxSalary;
# 3. PostgreSQL 示例：带 INOUT 参数的计数器
CREATE OR REPLACE PROCEDURE IncrementCounter (
    INOUT counter INT DEFAULT 0
)
LANGUAGE plpgsql
AS $$
BEGIN
    counter := counter + 1;
END;
$$;

-- 调用
DO $$
DECLARE
    cnt INT := 5;
BEGIN
    CALL IncrementCounter(cnt);
    RAISE NOTICE 'New Counter Value: %', cnt; -- 输出 6
END $$;

# 五、参数化的核心优势

  1. 防止 SQL 注入
    参数化强制将输入值与 SQL 逻辑分离，避免恶意字符串拼接。

    -- 错误写法（易受注入攻击）
CREATE PROCEDURE UnsafeSearch (IN keyword VARCHAR(100))
BEGIN
    SET @sql = CONCAT('SELECT * FROM products WHERE name LIKE "%', keyword, '%"');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
END

-- 正确写法（参数化过滤）
CREATE PROCEDURE SafeSearch (IN keyword VARCHAR(100))
BEGIN
    SELECT * FROM products WHERE name LIKE CONCAT('%', keyword, '%');
END

  2. 提升性能
    存储过程预编译后减少解析时间，重复调用时效率更高。

  3. 简化代码维护
    业务逻辑集中存储在数据库，应用层只需调用接口。

# 六、调用存储过程的常见方式

  1. 按位置传递参数

    -- MySQL/SQL Server
CALL ProcedureName(1, 'Alice', @output);

-- PostgreSQL
CALL procedure_name(1, 'Alice');

  2. 按名称传递参数

    -- SQL Server（命名参数）
EXEC ProcedureName @param1 = 1, @param2 = 'Alice';

-- PostgreSQL（命名参数）
CALL procedure_name(param1 => 1, param2 => 'Alice');

  3. 处理输出参数

    -- SQL Server
DECLARE @result INT;
EXEC ProcedureName @input = 10, @output = @result OUTPUT;

-- MySQL
SET @result = 0;
CALL ProcedureName(10, @result);
SELECT @result;

# 七、注意事项

  1. 参数数据类型匹配
    确保传入参数的类型与定义一致，避免隐式转换错误。

  2. 参数作用域

    • 参数名避免与表字段名冲突。
    • 在存储过程内部修改 IN 参数的值（如 MySQL）可能导致不可预期行为。

  3. 动态 SQL 中的参数化
    使用 EXECUTE 或 sp_executesql（SQL Server）时仍需绑定参数：

    -- SQL Server 动态 SQL 示例
CREATE PROCEDURE DynamicSearch 
    @column_name NVARCHAR(100),
    @search_value NVARCHAR(100)
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX);
    SET @sql = N'SELECT * FROM employees WHERE ' + QUOTENAME(@column_name) + ' = @value';
    EXEC sp_executesql @sql, N'@value NVARCHAR(100)', @value = @search_value;
END

# 八、总结

  • 定义存储过程：使用 CREATE PROCEDURE 并指定参数类型（IN/OUT/INOUT）。
  • 参数化核心：分离数据与逻辑，提升安全性和性能。
  • 灵活调用：支持按位置或名称传递参数，处理输入输出值。

通过合理设计参数化存储过程，可显著优化数据库操作的安全性和效率。

# PostgreSQL常用函数

以下是 PostgreSQL 中常用的 SQL 函数分类及示例，涵盖字符串处理、数值计算、日期时间、聚合、条件逻辑等核心操作：

# 一、字符串函数

函数 说明 示例
CONCAT(str1, str2, ...) 连接字符串 SELECT CONCAT('Hello', ' ', 'World') → 'Hello World'
SUBSTRING(str FROM start [FOR length]) 截取子串 SELECT SUBSTRING('PostgreSQL' FROM 5 FOR 3) → 'gre'
LENGTH(str) 字符串长度 SELECT LENGTH('abc') → 3
UPPER(str) / LOWER(str) 大小写转换 SELECT UPPER('test') → 'TEST'
TRIM([LEADING | TRAILING | BOTH] chars FROM str) 去除首尾字符 SELECT TRIM(BOTH 'x' FROM 'xxSQLxx') → 'SQL'
SPLIT_PART(str, delimiter, field) 按分隔符拆分 SELECT SPLIT_PART('a,b,c', ',', 2) → 'b'
POSITION(substr IN str) 子串位置 SELECT POSITION('ql' IN 'PostgreSQL') → 9
REPLACE(str, old, new) 替换子串 SELECT REPLACE('foo bar', 'bar', 'baz') → 'foo baz'
LEFT(str, n) / RIGHT(str, n) 取左/右侧字符 SELECT LEFT('database', 4) → 'data'
STRING_AGG(expr, delimiter) 分组字符串聚合 SELECT STRING_AGG(name, ', ') FROM employees

# 二、数值函数

函数 说明 示例
ROUND(num [, decimals]) 四舍五入 SELECT ROUND(3.1415, 2) → 3.14
CEIL(num) / FLOOR(num) 向上/向下取整 SELECT CEIL(2.3) → 3
ABS(num) 绝对值 SELECT ABS(-10) → 10
RANDOM() 生成 0~1 随机数 SELECT RANDOM() → 0.1234...
POWER(base, exponent) 幂运算 SELECT POWER(2, 3) → 8
SQRT(num) 平方根 SELECT SQRT(25) → 5
MOD(n, m) 取模 SELECT MOD(10, 3) → 1
GENERATE_SERIES(start, end [, step]) 生成数值序列 SELECT * FROM GENERATE_SERIES(1, 5) → 1,2,3,4,5

# 三、日期与时间函数

函数 说明 示例
CURRENT_DATE / CURRENT_TIME / NOW() 当前日期/时间 SELECT NOW() → '2023-10-10 15:30:00'
EXTRACT(field FROM date) 提取日期部分 SELECT EXTRACT(YEAR FROM NOW()) → 2023
DATE_TRUNC('unit', date) 截断日期到指定精度 SELECT DATE_TRUNC('month', NOW()) → '2023-10-01 00:00:00'
AGE(timestamp1, timestamp2) 计算时间差 SELECT AGE('2023-10-10', '2000-01-01') → '23 years 9 mons 9 days'
TO_CHAR(date, format) 日期格式化 SELECT TO_CHAR(NOW(), 'YYYY-MM-DD') → '2023-10-10'
INTERVAL 'value' 时间间隔 SELECT NOW() + INTERVAL '1 day'
DATE_PART('field', date) 类似 EXTRACT SELECT DATE_PART('dow', NOW()) → 1 (Monday)

# 四、聚合函数

函数 说明 示例
COUNT(*) 统计行数 SELECT COUNT(*) FROM employees
SUM(expr) 求和 SELECT SUM(salary) FROM employees
AVG(expr) 平均值 SELECT AVG(age) FROM users
MIN(expr) / MAX(expr) 最小/最大值 SELECT MIN(price) FROM products
ARRAY_AGG(expr) 聚合为数组 SELECT ARRAY_AGG(name) FROM departments
JSON_AGG(expr) 聚合为 JSON 数组 SELECT JSON_AGG(name) FROM departments

# 五、条件逻辑函数

函数 说明 示例
CASE WHEN ... THEN ... ELSE ... END 条件判断 SELECT CASE WHEN age > 18 THEN 'Adult' ELSE 'Minor' END
COALESCE(val1, val2, ...) 返回第一个非 NULL 值 SELECT COALESCE(NULL, 'default') → 'default'
NULLIF(a, b) 若 a = b 则返回 NULL SELECT NULLIF(10, 10) → NULL
GREATEST(val1, val2, ...) 返回最大值 SELECT GREATEST(3, 5, 1) → 5
LEAST(val1, val2, ...) 返回最小值 SELECT LEAST(3, 5, 1) → 1

# 六、窗口函数

函数 说明 示例
ROW_NUMBER() 行号 SELECT ROW_NUMBER() OVER (ORDER BY salary)
RANK() / DENSE_RANK() 排名 SELECT RANK() OVER (PARTITION BY dept ORDER BY salary)
LAG(expr [, offset]) / LEAD(expr [, offset]) 前/后行值 SELECT LAG(salary) OVER (ORDER BY id)
SUM(expr) OVER (...) 窗口累加 SELECT SUM(sales) OVER (ORDER BY date)
NTILE(n) 分桶 SELECT NTILE(4) OVER (ORDER BY score)

# 七、JSON 处理函数

函数 说明 示例
JSON_EXTRACT_PATH(json, path) 提取 JSON 值 SELECT JSON_EXTRACT_PATH('{"a": {"b": 1}}', 'a', 'b') → '1'
JSONB_SET(jsonb, path, new_value) 修改 JSONB 字段 SELECT JSONB_SET('{"a": 1}', '{a}', '2') → '{"a": 2}'
JSONB_ARRAY_ELEMENTS(jsonb) 展开 JSON 数组为行 SELECT * FROM JSONB_ARRAY_ELEMENTS('[1, 2]')
TO_JSONB(expr) 转换为 JSONB SELECT TO_JSONB(ROW(1, 'test'))
JSONB_PATH_EXISTS(jsonb, path) 检查 JSON 路径是否存在 SELECT JSONB_PATH_EXISTS('{"a": 1}', '$.a') → true

# 八、其他实用函数

函数 说明 示例
CAST(expr AS type) 类型转换 SELECT CAST('123' AS INTEGER) → 123
COALESCE 同前述条件逻辑
GENERATE_SUBSCRIPTS(array, dim) 生成数组索引 SELECT GENERATE_SUBSCRIPTS(ARRAY['a','b'], 1) → 1,2
PG_TYPEOF(expr) 获取数据类型 SELECT PG_TYPEOF(42) → integer
MD5(str) 计算 MD5 哈希 SELECT MD5('text') → '1cb251ec0d568de6a929b520c4aed8d1'

# 使用示例模板

-- 示例：统计每个部门平均工资并格式化
SELECT 
  department_id,
  TO_CHAR(AVG(salary), '999,999.99') AS avg_salary
FROM employees
GROUP BY department_id;

-- 示例：递归查询树形结构
WITH RECURSIVE tree AS (
  SELECT id, parent_id, name FROM nodes WHERE parent_id IS NULL
  UNION ALL
  SELECT n.id, n.parent_id, n.name FROM nodes n JOIN tree ON n.parent_id = tree.id
)
SELECT * FROM tree;

# 总结

  • 按需选择函数：根据场景选择字符串、数值、日期等函数简化操作。
  • 组合使用：如 STRING_AGG + OVER 实现分组字符串聚合。
  • 性能注意：JSON 处理函数可能影响查询效率，合理使用索引（如 GIN 索引优化 JSONB）。

PostgreSQL 官方文档是终极参考：Functions and Operators (opens new window)

# Oracle常用函数

以下是 Oracle 数据库常用 SQL 函数分类及示例，涵盖字符串处理、数值计算、日期时间、聚合分析、条件逻辑等核心操作：

# 一、字符串函数

函数 说明 示例
CONCAT(str1, str2) 连接两个字符串 SELECT CONCAT('Hello', 'World') → 'HelloWorld'
SUBSTR(str, start [, length]) 截取子字符串 SELECT SUBSTR('Oracle', 2, 3) → 'rac'
LENGTH(str) 返回字符串长度 SELECT LENGTH('SQL') → 3
UPPER(str) / LOWER(str) 大小写转换 SELECT UPPER('test') → 'TEST'
TRIM([LEADING | TRAILING | BOTH] chars FROM str) 去除首尾字符 SELECT TRIM('x' FROM 'xxSQLxx') → 'SQL'
INSTR(str, substr [, start [, occurrence]]) 查找子串位置 SELECT INSTR('Oracle', 'a') → 5
REPLACE(str, old, new) 替换子字符串 SELECT REPLACE('foo bar', 'bar', 'baz') → 'foo baz'
LPAD(str, len [, pad_str]) / RPAD 左/右填充字符串 SELECT LPAD('7', 3, '0') → '007'
LISTAGG(expr, delimiter) WITHIN GROUP (ORDER BY ...) 分组字符串聚合 SELECT LISTAGG(name, ', ') WITHIN GROUP (ORDER BY id) FROM employees

# 二、数值函数

函数 说明 示例
ROUND(num [, decimals]) 四舍五入 SELECT ROUND(3.1415, 2) → 3.14
CEIL(num) / FLOOR(num) 向上/向下取整 SELECT CEIL(2.3) → 3
ABS(num) 绝对值 SELECT ABS(-10) → 10
MOD(n, m) 取模运算 SELECT MOD(10, 3) → 1
POWER(base, exponent) 幂运算 SELECT POWER(2, 3) → 8
SQRT(num) 平方根 SELECT SQRT(25) → 5
TRUNC(num [, decimals]) 截断数值 SELECT TRUNC(3.1415, 2) → 3.14
SIGN(num) 符号函数（-1/0/1） SELECT SIGN(-5) → -1

# 三、日期与时间函数

函数 说明 示例
SYSDATE 当前系统日期时间 SELECT SYSDATE FROM DUAL → 2023-10-10 15:30:00
TO_DATE(str, format) 字符串转日期 SELECT TO_DATE('20231010', 'YYYYMMDD') → 2023-10-10
TO_CHAR(date, format) 日期格式化 SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') → '2023-10-10'
ADD_MONTHS(date, n) 添加月份 SELECT ADD_MONTHS('2023-01-31', 1) → 2023-02-28
MONTHS_BETWEEN(date1, date2) 计算月份差 SELECT MONTHS_BETWEEN('2023-10-10', '2023-01-01') → 9.29
LAST_DAY(date) 返回月份最后一天 SELECT LAST_DAY('2023-02-15') → 2023-02-28
EXTRACT(field FROM date) 提取日期部分 SELECT EXTRACT(YEAR FROM SYSDATE) → 2023
NEXT_DAY(date, weekday) 下一周某天的日期 SELECT NEXT_DAY(SYSDATE, 'MONDAY') → 2023-10-16

# 四、聚合函数

函数 说明 示例
COUNT(*) 统计行数 SELECT COUNT(*) FROM employees
SUM(expr) 求和 SELECT SUM(salary) FROM employees
AVG(expr) 平均值 SELECT AVG(age) FROM users
MIN(expr) / MAX(expr) 最小/最大值 SELECT MIN(price) FROM products
STDDEV(expr) 标准差 SELECT STDDEV(sales) FROM orders
VARIANCE(expr) 方差 SELECT VARIANCE(salary) FROM employees

# 五、条件逻辑函数

函数 说明 示例
CASE WHEN ... THEN ... ELSE ... END 条件判断 SELECT CASE WHEN age > 18 THEN 'Adult' ELSE 'Minor' END
NVL(expr1, expr2) 替换 NULL 值 SELECT NVL(NULL, 'N/A') → 'N/A'
NVL2(expr1, expr2, expr3) 三值逻辑替换 SELECT NVL2(NULL, 'Not Null', 'Null') → 'Null'
COALESCE(expr1, expr2, ...) 返回第一个非 NULL 值 SELECT COALESCE(NULL, 'default') → 'default'
DECODE(value, match1, result1, ..., default) 简单条件分支 SELECT DECODE(gender, 'M', 'Male', 'F', 'Female', 'Unknown')

# 六、分析函数（窗口函数）

函数 说明 示例
ROW_NUMBER() 行号 SELECT ROW_NUMBER() OVER (ORDER BY salary)
RANK() / DENSE_RANK() 排名 SELECT RANK() OVER (PARTITION BY dept ORDER BY salary)
LAG(expr [, offset]) / LEAD(expr [, offset]) 前/后行值 SELECT LAG(salary) OVER (ORDER BY id)
SUM(expr) OVER (...) 窗口累加 SELECT SUM(sales) OVER (ORDER BY date)
NTILE(n) 分桶 SELECT NTILE(4) OVER (ORDER BY score)
FIRST_VALUE(expr) / LAST_VALUE(expr) 首/末行值 SELECT FIRST_VALUE(name) OVER (PARTITION BY dept)

# 七、层次查询函数

函数 说明 示例
CONNECT_BY_ROOT 根节点值 SELECT name, CONNECT_BY_ROOT name AS root FROM employees START WITH manager_id IS NULL CONNECT BY PRIOR id = manager_id
LEVEL 当前层级 SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 5
SYS_CONNECT_BY_PATH 路径聚合 SELECT SYS_CONNECT_BY_PATH(name, '/') FROM employees ...

# 八、其他实用函数

函数 说明 示例
CAST(expr AS type) 类型转换 SELECT CAST('123' AS NUMBER) → 123
GREATEST(val1, val2, ...) 返回最大值 SELECT GREATEST(3, 5, 1) → 5
LEAST(val1, val2, ...) 返回最小值 SELECT LEAST(3, 5, 1) → 1
USER / UID 当前用户/用户 ID SELECT USER FROM DUAL → 'SCOTT'
ROWNUM 返回行号（伪列） SELECT * FROM employees WHERE ROWNUM <= 10

# 使用示例模板

-- 示例：统计每个部门平均工资并格式化
SELECT 
  department_id,
  TO_CHAR(AVG(salary), '999,999.99') AS avg_salary
FROM employees
GROUP BY department_id;

-- 示例：递归查询员工层级
SELECT 
  name, 
  LEVEL, 
  SYS_CONNECT_BY_PATH(name, ' -> ') AS hierarchy
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR id = manager_id;

# 总结

  • 按需选择函数：根据场景选择字符串、数值、日期等函数简化操作。
  • 注意 Oracle 特性：如 NVL、DECODE、层次查询函数（CONNECT BY）。
  • 性能优化：分析函数（如 ROW_NUMBER）可替代复杂子查询，提升效率。

Oracle 官方文档是终极参考：SQL Functions (opens new window).

上次更新: 2025/4/19 22:57:58

← JSON格式 →