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%'`）。  
```sql  
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';  
``` - **隐式类型转换**：  
```sql  
-- 假设 user_id 是字符串类型，传入数字会导致索引失效  
SELECT * FROM users WHERE user_id = 123; -- 错误  
SELECT * FROM users WHERE user_id = '123'; -- 正确  
``` - **LIKE 左模糊匹配**：  
```sql  
-- 索引失效  
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_rankFROM 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_salaryFROM 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_countFROM 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_dateFROM (  
 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_salaryFROM employees;  

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

SELECT   
  department_name,  
 total_salary, total_salary / SUM(total_salary) OVER () AS salary_ratioFROM (  
 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_countFROM 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.salaryFROM ranked r  
JOIN departments d ON r.department_id = d.id  
WHERE rk = 2;  

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

SELECT   
  user_id,  
 last_active_dateFROM 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_countFROM 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：安装依赖**  
```bash  
pip install pytest psycopg2-binary```  
  
**步骤2：编写测试用例**  
```python  
# 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`启动临时数据库。  
  ```python  
  from testcontainers.postgres import PostgresContainer  
  
 def test_with_container(): with PostgresContainer("postgres:13") as postgres: conn = psycopg2.connect(postgres.get_connection_url()) # 运行测试...  
 ```- **性能测试**：使用`EXPLAIN ANALYZE`验证查询计划。  
  ```sql  
  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  

四、工具推荐

五、总结

• 单元测试：聚焦单一对象，快速反馈逻辑错误。
• 集成测试：验证组件协作，确保数据一致性。
• 自动化：通过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 OUTPUTAS  
BEGIN  
 -- SQL 逻辑代码  
END  

3. PostgreSQL

CREATE OR REPLACE PROCEDURE procedure_name (  
 parameter1 datatype, INOUT parameter2 datatype DEFAULT NULL)  
LANGUAGE plpgsql  
AS $$  
BEGIN  
 -- SQL 逻辑代码  
END;  
$$;  

三、参数类型

四、参数化存储过程示例

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) OUTPUTAS  
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; -- 输出 6END $$;  

五、参数化的核心优势

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 函数分类及示例，涵盖字符串处理、数值计算、日期时间、聚合、条件逻辑等核心操作：

一、字符串函数

二、数值函数

三、日期与时间函数

四、聚合函数

五、条件逻辑函数

六、窗口函数

七、JSON 处理函数

八、其他实用函数

使用示例模板

-- 示例：统计每个部门平均工资并格式化  
SELECT   
  department_id,  
 TO_CHAR(AVG(salary), '999,999.99') AS avg_salaryFROM 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

Oracle常用函数

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

一、字符串函数

二、数值函数

三、日期与时间函数

四、聚合函数

五、条件逻辑函数

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

七、层次查询函数

八、其他实用函数

使用示例模板

-- 示例：统计每个部门平均工资并格式化  
SELECT   
  department_id,  
 TO_CHAR(AVG(salary), '999,999.99') AS avg_salaryFROM 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.