# SQL语句

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

# SQL基础知识

# 一、基础语法

数据定义语言 (DDL)
- CREATE: 创建数据库、表、索引等
- ALTER: 修改表结构（添加/删除列、修改数据类型）
- DROP: 删除数据库、表、索引
- TRUNCATE: 快速清空表数据（不可回滚）
数据操作语言 (DML)
- INSERT INTO: 插入数据
- DELETE: 删除数据（可回滚）
- UPDATE: 修改数据
- SELECT: 数据查询（核心功能）

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

基础查询
- SELECT * FROM table
- SELECT column1, column2
- DISTINCT: 去重
- AS: 别名（列或表）
条件过滤
- WHERE: 条件筛选
- 运算符：=, <>, >, <, BETWEEN, IN, LIKE（通配符 % 和 _）
- 逻辑运算符：AND, OR, NOT
排序与分页
- ORDER BY column ASC/DESC
- 分页语法：
  - MySQL: LIMIT n OFFSET m
  - SQL Server: OFFSET m ROWS FETCH NEXT n ROWS ONLY
  - Oracle: ROWNUM
聚合与分组
- 聚合函数：COUNT(), SUM(), AVG(), MAX(), MIN()
- GROUP BY: 按列分组
- HAVING: 分组后过滤（与 WHERE 区别：WHERE 在聚合前过滤，HAVING 在聚合后）

# 三、多表操作

JOIN 连接
- INNER JOIN: 返回匹配的行
- LEFT/RIGHT JOIN: 保留左/右表全部数据
- FULL OUTER JOIN: 返回所有匹配和不匹配的行
- CROSS JOIN: 笛卡尔积
- NATURAL JOIN: 自动按同名列连接（慎用）
子查询（嵌套查询）
- 标量子查询（返回单值，如 SELECT (SELECT ...)）
- 关联子查询（依赖外层查询，如 EXISTS）
- IN, ANY, ALL, EXISTS 的应用

# 四、数据控制

事务控制 (TCL)
- COMMIT: 提交事务
- ROLLBACK: 回滚事务
- SAVEPOINT: 设置保存点
- ACID 特性：原子性、一致性、隔离性、持久性
权限管理 (DCL)
- GRANT: 授予权限（如 GRANT SELECT ON table TO user）
- REVOKE: 撤销权限

# 五、高级功能

窗口函数
- ROW_NUMBER(), RANK(), DENSE_RANK()
- OVER(PARTITION BY ... ORDER BY ...)
公共表表达式 (CTE)
- WITH cte_name AS (SELECT ...): 临时结果集复用
索引优化
- 作用：加速查询（B-tree、Hash、全文索引）
- 创建：CREATE INDEX index_name ON table(column)
- 注意：索引的维护成本和适用场景
存储过程 & 触发器
- 存储过程：预编译的SQL代码块（CREATE PROCEDURE）
- 触发器：自动执行的操作（如 BEFORE INSERT）

# 六、其他关键点

约束
- PRIMARY KEY（主键）
- FOREIGN KEY（外键约束）
- UNIQUE（唯一值）
- NOT NULL（非空）
- CHECK（条件检查，如 CHECK(age > 0)）
NULL 处理
- IS NULL / IS NOT NULL
- COALESCE(): 返回第一个非NULL值
- NULLIF(): 两值相等时返回NULL
性能优化
- 避免 SELECT *，减少不必要的数据传输
- 使用 EXPLAIN 分析执行计划
- 警惕全表扫描和笛卡尔积

# 七、常见问题示例

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';

窗口函数排名

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

递归查询（树形结构）

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

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

选择需要索引的列
- 高频查询条件：为 WHERE、JOIN、ORDER BY、GROUP BY 中频繁出现的列创建索引。
- 高选择性列：列的取值越唯一（如主键、用户 ID），索引效果越好。
- 避免过度索引：索引会占用存储并降低写入性能，只为必要的列创建。
复合索引的列顺序
- 最左前缀原则：复合索引 (a, b, c) 对 a、a AND b、a AND b AND c 生效，但对 b 或 c 单独查询无效。
- 高频列优先：将查询中使用频率更高的列放在复合索引左侧。
- 排序优化：若需对某列排序（ORDER BY），将该列放在复合索引中。
索引类型选择
- B-tree 索引：默认类型，支持范围查询（>、<、BETWEEN）和排序。
- Hash 索引：仅支持精确匹配（=），适用于内存表或等值查询。
- 全文索引：针对大文本字段（如 TEXT）的关键词搜索（如 MATCH ... AGAINST）。
- 空间索引：用于地理数据（如 GEOMETRY）。

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

字符串类型（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);
```
数值类型（INT/FLOAT/DECIMAL）
- 直接使用 B-tree 索引，注意避免在查询中对字段进行运算。
- 示例：WHERE age + 1 > 20 会导致索引失效，应改写为 WHERE age > 19。

日期与时间（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';

枚举类型（ENUM）或低基数列
- 基数低（如性别只有男/女）的列索引效果差，需谨慎使用。

JSON/XML 类型

提取特定字段生成虚拟列，并为虚拟列创建索引（MySQL 支持）：

ALTER TABLE products ADD COLUMN price DECIMAL(10,2) AS (JSON_EXTRACT(data, '$.price'));
CREATE INDEX idx_price ON products (price);

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

避免索引失效的写法

在索引列上使用函数或计算：

-- 索引失效
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%';

利用覆盖索引（Covering Index）

索引包含查询所需的所有列，避免回表查询数据行。

示例：

-- 创建覆盖索引
CREATE INDEX idx_covering ON orders (user_id, total_amount);
-- 查询只需访问索引
SELECT user_id, total_amount FROM orders WHERE user_id = 100;

强制使用索引（谨慎使用）
- 某些情况下优化器可能未选择最优索引，可手动指定：
```
SELECT * FROM users FORCE INDEX (idx_email) WHERE email = 'alice@example.com';
```

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

确保 WHERE 条件有索引

若查询未命中索引，会触发全表扫描。

示例：

-- 无索引时全表扫描
SELECT * FROM logs WHERE message LIKE 'error%';
-- 添加索引后优化
CREATE INDEX idx_message_prefix ON logs (message(50));

避免 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';

分页查询优化

避免 OFFSET 过大时扫描大量数据，改用游标分页（基于有序列）：

-- 低效写法
SELECT * FROM orders ORDER BY id LIMIT 1000 OFFSET 100000;
-- 高效写法（记录上次查询的最后一个 id）
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 1000;

定期分析索引使用情况
- 使用数据库工具（如 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 的层级结构

非叶子节点（索引节点）
- 仅存储键值（Key），不存储实际数据。
- 每个节点包含 n 个键值和 n+1 个子节点指针，键值用于导航到子节点。
叶子节点（数据节点）
- 存储键值和对应的数据（如数据行地址或完整数据）。
- 所有叶子节点通过指针形成有序双向链表，支持高效的范围查询。

# 1.2 B+ Tree 的特性

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

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

# 2.1 对比其他数据结构

哈希表
- 优点：等值查询 O(1) 时间复杂度。
- 缺点：不支持范围查询，哈希冲突处理复杂。
- B+ Tree 胜在支持范围查询和排序。
二叉搜索树（BST）
- 缺点：树高与数据量呈对数关系（O(log N)），数据量大时深度过高，导致磁盘 I/O 次数多。
- B+ Tree 通过多路分支显著降低树高（如 3 层 B+ Tree 可存储千万级数据）。
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 的典型应用场景

数据库索引
- MySQL InnoDB 的主键索引（聚簇索引）直接存储数据行，二级索引存储主键值。
- 范围查询（如 ORDER BY、GROUP BY）依赖叶子节点的有序性。
文件系统
- 如 NTFS、ReiserFS 使用 B+ Tree 管理文件和目录的元数据。

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

写放大问题
- 频繁的插入/删除可能导致节点分裂与合并，影响写入性能。
- 优化：使用缓冲机制（如 InnoDB 的 Change Buffer）延迟写入操作。
内存依赖
- 若索引无法完全缓存到内存，频繁的磁盘 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

# 提示

理解需求：明确问题中的过滤条件、排序规则和分组逻辑。
优化性能：使用 EXPLAIN 分析执行计划，避免全表扫描。
注意语法差异：不同数据库（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 $$;

# 五、参数化的核心优势

防止 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

提升性能
存储过程预编译后减少解析时间，重复调用时效率更高。
简化代码维护
业务逻辑集中存储在数据库，应用层只需调用接口。

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

按位置传递参数

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

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

按名称传递参数

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

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

处理输出参数

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

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

# 七、注意事项

参数数据类型匹配
确保传入参数的类型与定义一致，避免隐式转换错误。
参数作用域
- 参数名避免与表字段名冲突。
- 在存储过程内部修改 IN 参数的值（如 MySQL）可能导致不可预期行为。

动态 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).

← BigData JSON格式 →