SQLite高级查询技巧从基础到进阶实战解析与常见问题解决方案

SQLite是一个轻量级、零配置的嵌入式数据库，广泛应用于移动应用、桌面软件和小型Web应用中。尽管它简单易用，但掌握其高级查询技巧能显著提升数据处理效率和应用性能。本文将从基础查询出发，逐步深入到进阶技巧，并结合实战案例和常见问题解决方案，帮助你全面掌握SQLite的高级查询能力。

1. 基础查询回顾：构建坚实基础

在深入高级技巧之前，我们先快速回顾SQLite的基础查询语法。这些是后续所有高级操作的基石。

1.1 基本SELECT语句

SQLite的查询以SELECT语句为核心，用于从数据库中检索数据。

-- 基本语法 SELECT column1, column2, ... FROM table_name WHERE condition ORDER BY column_name LIMIT number;

示例：假设我们有一个employees表，包含id, name, department, salary字段。

-- 查询所有员工的姓名和部门 SELECT name, department FROM employees; -- 查询工资高于5000的员工 SELECT name, salary FROM employees WHERE salary > 5000; -- 按工资降序排列，只显示前5条记录 SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 5;

1.2 常用聚合函数

聚合函数用于对一组值进行计算，返回单个结果。

-- 计算员工总数 SELECT COUNT(*) AS total_employees FROM employees; -- 计算平均工资 SELECT AVG(salary) AS avg_salary FROM employees; -- 计算各部门的总工资 SELECT department, SUM(salary) AS total_salary FROM employees GROUP BY department;

1.3 多表连接（JOIN）

当数据分布在多个表中时，使用JOIN操作将它们关联起来。

-- 假设有departments表：id, name -- employees表有department_id字段 -- 内连接：只返回匹配的记录 SELECT e.name, d.name AS department_name FROM employees e INNER JOIN departments d ON e.department_id = d.id; -- 左连接：返回左表所有记录，右表不匹配的返回NULL SELECT e.name, d.name AS department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.id;

实战案例：创建一个简单的销售数据库，包含customers、orders和products表。

-- 创建表 CREATE TABLE customers ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE ); CREATE TABLE products ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, price REAL NOT NULL ); CREATE TABLE orders ( id INTEGER PRIMARY KEY, customer_id INTEGER, product_id INTEGER, quantity INTEGER, order_date DATE, FOREIGN KEY (customer_id) REFERENCES customers(id), FOREIGN KEY (product_id) REFERENCES products(id) ); -- 插入示例数据 INSERT INTO customers VALUES (1, 'Alice', 'alice@example.com'); INSERT INTO customers VALUES (2, 'Bob', 'bob@example.com'); INSERT INTO products VALUES (1, 'Laptop', 1200.00); INSERT INTO products VALUES (2, 'Mouse', 25.00); INSERT INTO orders VALUES (1, 1, 1, 1, '2023-01-15'); INSERT INTO orders VALUES (2, 1, 2, 2, '2023-01-20'); INSERT INTO orders VALUES (3, 2, 1, 1, '2023-02-01');

2. 高级查询技巧：提升查询能力

掌握了基础后，我们进入高级查询技巧，这些技巧能处理更复杂的数据分析需求。

2.1 子查询（Subqueries）

子查询是嵌套在另一个查询中的查询，可用于过滤、计算或作为临时表。

类型1：标量子查询（返回单个值）

-- 查询工资高于平均工资的员工 SELECT name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

类型2：行子查询（返回一行多列）

-- 查询工资最高的员工信息 SELECT name, salary, department FROM employees WHERE (salary, department) = ( SELECT MAX(salary), department FROM employees GROUP BY department HAVING department = 'Engineering' );

类型3：表子查询（作为临时表）

-- 查询各部门工资高于该部门平均工资的员工 SELECT e.name, e.department, e.salary FROM employees e INNER JOIN ( SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department ) dept_avg ON e.department = dept_avg.department WHERE e.salary > dept_avg.avg_salary;

实战案例：使用子查询分析销售数据。

-- 查询购买过Laptop的客户信息 SELECT c.name, c.email FROM customers c WHERE c.id IN ( SELECT o.customer_id FROM orders o INNER JOIN products p ON o.product_id = p.id WHERE p.name = 'Laptop' ); -- 查询总消费金额超过1000的客户 SELECT c.name, SUM(o.quantity * p.price) AS total_spent FROM customers c INNER JOIN orders o ON c.id = o.customer_id INNER JOIN products p ON o.product_id = p.id GROUP BY c.id HAVING total_spent > 1000;

2.2 窗口函数（Window Functions）

窗口函数是SQLite 3.25.0+引入的强大功能，允许在不改变行数的情况下进行聚合计算。

基本语法：

function_name() OVER ( [PARTITION BY column] [ORDER BY column [ASC|DESC]] [ROWS/RANGE BETWEEN ... AND ...] )

常用窗口函数：

ROW_NUMBER()：为每行分配唯一序号
RANK()：相同值分配相同排名，跳过后续排名
DENSE_RANK()：相同值分配相同排名，不跳过后续排名
LEAD()/LAG()：访问前后行的值
SUM()/AVG()等聚合函数的窗口版本

示例：为员工按部门分区，按工资排序分配排名。

SELECT name, department, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank_with_ties, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_dense_rank FROM employees;

实战案例：销售数据分析。

-- 为每个客户的订单按日期排序，计算累计消费 SELECT c.name, o.order_date, p.name AS product, p.price, o.quantity, SUM(p.price * o.quantity) OVER ( PARTITION BY c.id ORDER BY o.order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS cumulative_spent FROM customers c JOIN orders o ON c.id = o.customer_id JOIN products p ON o.product_id = p.id ORDER BY c.name, o.order_date; -- 计算每个产品在不同日期的销售排名 SELECT p.name, o.order_date, o.quantity, RANK() OVER (PARTITION BY p.id ORDER BY o.quantity DESC) AS daily_rank FROM products p JOIN orders o ON o.product_id = p.id;

2.3 公用表表达式（CTE）

CTE提供了一种更清晰、可读性更高的方式来编写复杂查询，特别适合递归查询。

普通CTE：

WITH dept_stats AS ( SELECT department, AVG(salary) AS avg_salary, COUNT(*) AS emp_count FROM employees GROUP BY department ) SELECT e.name, e.salary, e.department, ds.avg_salary, e.salary - ds.avg_salary AS salary_diff FROM employees e JOIN dept_stats ds ON e.department = ds.department WHERE e.salary > ds.avg_salary;

递归CTE（用于处理层次结构数据）：

-- 假设有员工层级表：id, name, manager_id CREATE TABLE employees_hierarchy ( id INTEGER PRIMARY KEY, name TEXT, manager_id INTEGER ); -- 插入示例数据 INSERT INTO employees_hierarchy VALUES (1, 'CEO', NULL); INSERT INTO employees_hierarchy VALUES (2, 'CTO', 1); INSERT INTO employees_hierarchy VALUES (3, 'Dev Manager', 2); INSERT INTO employees_hierarchy VALUES (4, 'Developer', 3); INSERT INTO employees_hierarchy VALUES (5, 'Developer', 3); -- 递归查询：从CEO开始，查找所有下属 WITH RECURSIVE employee_tree AS ( -- 基础查询：从CEO开始 SELECT id, name, manager_id, 0 AS level FROM employees_hierarchy WHERE manager_id IS NULL UNION ALL -- 递归部分：查找下属 SELECT e.id, e.name, e.manager_id, et.level + 1 FROM employees_hierarchy e INNER JOIN employee_tree et ON e.manager_id = et.id ) SELECT * FROM employee_tree ORDER BY level, id;

2.4 条件表达式（CASE WHEN）

CASE表达式允许在查询中实现条件逻辑，类似于编程中的if-else。

-- 根据工资范围分类员工 SELECT name, salary, CASE WHEN salary < 3000 THEN '低收入' WHEN salary BETWEEN 3000 AND 7000 THEN '中等收入' ELSE '高收入' END AS income_level FROM employees; -- 多条件分类 SELECT name, department, salary, CASE department WHEN 'Engineering' THEN '技术部门' WHEN 'Sales' THEN '销售部门' WHEN 'HR' THEN '人力资源' ELSE '其他' END AS dept_category FROM employees;

实战案例：销售数据分类分析。

-- 根据订单金额和数量进行客户分级 SELECT c.name, SUM(o.quantity * p.price) AS total_spent, COUNT(o.id) AS order_count, CASE WHEN SUM(o.quantity * p.price) > 2000 THEN 'VIP客户' WHEN SUM(o.quantity * p.price) BETWEEN 1000 AND 2000 THEN '重要客户' ELSE '普通客户' END AS customer_level, CASE WHEN COUNT(o.id) >= 5 THEN '高频客户' WHEN COUNT(o.id) >= 2 THEN '中频客户' ELSE '低频客户' END AS frequency_level FROM customers c JOIN orders o ON c.id = o.customer_id JOIN products p ON o.product_id = p.id GROUP BY c.id;

2.5 复杂连接技巧

除了基本的INNER JOIN和LEFT JOIN，SQLite还支持其他连接类型和技巧。

交叉连接（CROSS JOIN）：

-- 生成所有可能的组合（笛卡尔积） SELECT e.name, d.name AS department FROM employees e CROSS JOIN departments d;

自连接（Self Join）：

-- 查找同一部门中工资相近的员工（相差不超过10%） SELECT e1.name AS employee1, e2.name AS employee2, e1.department, e1.salary AS salary1, e2.salary AS salary2 FROM employees e1 JOIN employees e2 ON e1.department = e2.department WHERE e1.id < e2.id AND ABS(e1.salary - e2.salary) / e1.salary <= 0.1;

多表连接优化：

-- 复杂的多表连接查询 SELECT c.name AS customer, p.name AS product, o.quantity, o.order_date, d.name AS department FROM customers c JOIN orders o ON c.id = o.customer_id JOIN products p ON o.product_id = p.id JOIN employees e ON o.salesperson_id = e.id JOIN departments d ON e.department_id = d.id WHERE o.order_date >= '2023-01-01' AND p.price > 100 ORDER BY o.order_date DESC;

3. 实战案例：综合应用

让我们通过一个完整的实战案例，将上述技巧综合应用。

3.1 案例背景：电商数据分析系统

假设我们有一个电商数据库，包含以下表：

users：用户信息
products：商品信息
orders：订单信息
order_items：订单明细
categories：商品分类

3.2 数据库结构

-- 创建表 CREATE TABLE users ( id INTEGER PRIMARY KEY, username TEXT UNIQUE, email TEXT, join_date DATE ); CREATE TABLE categories ( id INTEGER PRIMARY KEY, name TEXT, parent_id INTEGER, FOREIGN KEY (parent_id) REFERENCES categories(id) ); CREATE TABLE products ( id INTEGER PRIMARY KEY, name TEXT, category_id INTEGER, price REAL, stock INTEGER, FOREIGN KEY (category_id) REFERENCES categories(id) ); CREATE TABLE orders ( id INTEGER PRIMARY KEY, user_id INTEGER, order_date DATE, total_amount REAL, status TEXT, FOREIGN KEY (user_id) REFERENCES users(id) ); CREATE TABLE order_items ( id INTEGER PRIMARY KEY, order_id INTEGER, product_id INTEGER, quantity INTEGER, price REAL, FOREIGN KEY (order_id) REFERENCES orders(id), FOREIGN KEY (product_id) REFERENCES products(id) );

3.3 高级查询实战

需求1：分析用户购买行为，找出高价值用户

WITH user_purchase_stats AS ( SELECT u.id, u.username, COUNT(DISTINCT o.id) AS order_count, SUM(oi.quantity * oi.price) AS total_spent, AVG(oi.quantity * oi.price) AS avg_order_value, MIN(o.order_date) AS first_order_date, MAX(o.order_date) AS last_order_date, COUNT(DISTINCT p.category_id) AS categories_count FROM users u JOIN orders o ON u.id = o.user_id JOIN order_items oi ON o.id = oi.order_id JOIN products p ON oi.product_id = p.id WHERE o.status = 'completed' GROUP BY u.id, u.username ), user_segments AS ( SELECT *, CASE WHEN total_spent > 5000 THEN 'VIP' WHEN total_spent > 1000 THEN '重要用户' ELSE '普通用户' END AS user_segment, CASE WHEN order_count >= 10 THEN '高频用户' WHEN order_count >= 3 THEN '中频用户' ELSE '低频用户' END AS frequency_segment FROM user_purchase_stats ) SELECT username, order_count, total_spent, avg_order_value, categories_count, user_segment, frequency_segment, DENSE_RANK() OVER (ORDER BY total_spent DESC) AS spending_rank FROM user_segments WHERE user_segment IN ('VIP', '重要用户') ORDER BY total_spent DESC;

需求2：商品销售趋势分析

WITH monthly_sales AS ( SELECT p.id AS product_id, p.name AS product_name, c.name AS category_name, strftime('%Y-%m', o.order_date) AS month, SUM(oi.quantity) AS total_quantity, SUM(oi.quantity * oi.price) AS total_revenue, LAG(SUM(oi.quantity * oi.price), 1) OVER ( PARTITION BY p.id ORDER BY strftime('%Y-%m', o.order_date) ) AS prev_month_revenue FROM products p JOIN categories c ON p.category_id = c.id JOIN order_items oi ON p.id = oi.product_id JOIN orders o ON oi.order_id = o.id WHERE o.status = 'completed' GROUP BY p.id, p.name, c.name, strftime('%Y-%m', o.order_date) ), trend_analysis AS ( SELECT *, CASE WHEN prev_month_revenue IS NULL THEN '新商品' WHEN total_revenue > prev_month_revenue * 1.2 THEN '快速增长' WHEN total_revenue > prev_month_revenue THEN '稳定增长' WHEN total_revenue < prev_month_revenue * 0.8 THEN '快速下降' ELSE '波动' END AS trend_status, ROUND((total_revenue - prev_month_revenue) / prev_month_revenue * 100, 2) AS growth_rate FROM monthly_sales ) SELECT product_name, category_name, month, total_quantity, total_revenue, trend_status, growth_rate, RANK() OVER (PARTITION BY month ORDER BY total_revenue DESC) AS monthly_rank FROM trend_analysis WHERE month >= '2023-01' ORDER BY month, total_revenue DESC;

需求3：库存预警与补货建议

WITH product_sales AS ( SELECT p.id, p.name, p.stock, p.price, c.name AS category, SUM(oi.quantity) AS total_sold, AVG(oi.quantity) AS avg_daily_sales, MAX(o.order_date) AS last_sale_date FROM products p JOIN categories c ON p.category_id = c.id LEFT JOIN order_items oi ON p.id = oi.product_id LEFT JOIN orders o ON oi.order_id = o.id AND o.status = 'completed' GROUP BY p.id, p.name, p.stock, p.price, c.name ), inventory_analysis AS ( SELECT *, CASE WHEN stock <= 0 THEN '缺货' WHEN stock <= avg_daily_sales * 7 THEN '低库存' WHEN stock <= avg_daily_sales * 30 THEN '中等库存' ELSE '充足' END AS inventory_status, CASE WHEN stock <= 0 THEN '紧急补货' WHEN stock <= avg_daily_sales * 7 THEN '建议补货' WHEN stock <= avg_daily_sales * 30 THEN '监控库存' ELSE '正常' END AS recommendation, CASE WHEN last_sale_date IS NULL THEN '未销售' WHEN julianday('now') - julianday(last_sale_date) > 90 THEN '滞销' WHEN julianday('now') - julianday(last_sale_date) > 30 THEN '慢销' ELSE '正常' END AS sales_status FROM product_sales ) SELECT name, category, stock, total_sold, avg_daily_sales, inventory_status, recommendation, sales_status, CASE WHEN inventory_status = '缺货' OR sales_status = '滞销' THEN '高优先级' WHEN inventory_status = '低库存' OR sales_status = '慢销' THEN '中优先级' ELSE '低优先级' END AS priority FROM inventory_analysis ORDER BY priority, stock;

4. 常见问题与解决方案

4.1 性能问题

问题1：查询速度慢 原因分析：

缺少索引
复杂的子查询或连接
大量数据扫描
不合理的查询结构

解决方案：

-- 1. 创建合适的索引 CREATE INDEX idx_orders_user_date ON orders(user_id, order_date); CREATE INDEX idx_order_items_product ON order_items(product_id); CREATE INDEX idx_products_category ON products(category_id); -- 2. 使用EXPLAIN分析查询计划 EXPLAIN QUERY PLAN SELECT u.username, COUNT(o.id) AS order_count FROM users u JOIN orders o ON u.id = o.user_id WHERE o.order_date >= '2023-01-01' GROUP BY u.id; -- 3. 优化查询结构 -- 优化前：嵌套子查询 SELECT * FROM users WHERE id IN ( SELECT user_id FROM orders WHERE order_date >= '2023-01-01' ); -- 优化后：使用JOIN SELECT DISTINCT u.* FROM users u JOIN orders o ON u.id = o.user_id WHERE o.order_date >= '2023-01-01'; -- 4. 限制结果集大小 SELECT * FROM large_table WHERE condition LIMIT 1000 OFFSET 0;

问题2：内存不足 原因分析：

查询返回大量数据
复杂的窗口函数或CTE使用过多内存
临时表过大

解决方案：

-- 1. 分页查询 SELECT * FROM orders WHERE user_id = 123 ORDER BY order_date DESC LIMIT 50 OFFSET 0; -- 每次获取50条 -- 2. 使用游标（在应用层实现） -- 在Python中使用游标示例 import sqlite3 conn = sqlite3.connect('database.db') cursor = conn.cursor() cursor.execute("SELECT * FROM large_table") for row in cursor: # 逐行处理，不一次性加载所有数据 process_row(row) -- 3. 优化窗口函数 -- 避免在大数据集上使用窗口函数 -- 使用子查询先过滤数据 WITH filtered_data AS ( SELECT * FROM large_table WHERE date >= '2023-01-01' ) SELECT *, ROW_NUMBER() OVER (ORDER BY value) AS row_num FROM filtered_data;

4.2 数据完整性问题

问题1：外键约束失败 原因分析：

插入数据时违反外键约束
删除数据时存在依赖关系

解决方案：

-- 1. 检查外键约束 PRAGMA foreign_keys; -- 确保外键约束已启用 -- 2. 安全删除数据 -- 使用级联删除 CREATE TABLE orders ( id INTEGER PRIMARY KEY, user_id INTEGER, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ); -- 或使用事务确保数据一致性 BEGIN TRANSACTION; DELETE FROM order_items WHERE order_id = 123; DELETE FROM orders WHERE id = 123; COMMIT; -- 3. 插入前检查 INSERT INTO orders (user_id, order_date) SELECT 123, '2023-01-01' WHERE EXISTS (SELECT 1 FROM users WHERE id = 123);

问题2：重复数据 原因分析：

缺少唯一约束
并发插入导致重复

解决方案：

-- 1. 添加唯一约束 CREATE TABLE users ( id INTEGER PRIMARY KEY, email TEXT UNIQUE, username TEXT UNIQUE ); -- 2. 使用INSERT OR REPLACE INSERT OR REPLACE INTO users (id, email, username) VALUES (1, 'new@example.com', 'newuser'); -- 3. 使用INSERT OR IGNORE INSERT OR IGNORE INTO users (email, username) VALUES ('existing@example.com', 'existinguser'); -- 4. 使用UPSERT（SQLite 3.24.0+） INSERT INTO users (email, username) VALUES ('user@example.com', 'username') ON CONFLICT(email) DO UPDATE SET username = excluded.username;

4.3 复杂查询错误

问题1：窗口函数版本兼容性 原因分析：

SQLite版本低于3.25.0不支持窗口函数
语法错误

解决方案：

-- 1. 检查SQLite版本 SELECT sqlite_version(); -- 2. 降级方案（使用子查询模拟窗口函数） -- 原始窗口函数查询 SELECT name, department, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank FROM employees; -- 降级方案：使用子查询 SELECT e.name, e.department, e.salary, (SELECT COUNT(*) + 1 FROM employees e2 WHERE e2.department = e.department AND e2.salary > e.salary) AS dept_rank FROM employees e; -- 3. 使用临时表 CREATE TEMPORARY TABLE temp_ranks AS SELECT department, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank FROM employees; SELECT e.name, e.department, e.salary, tr.rank FROM employees e JOIN temp_ranks tr ON e.department = tr.department AND e.salary = tr.salary;

问题2：递归CTE深度限制 原因分析：

SQLite默认递归深度限制为1000
处理非常深的层次结构时可能超出限制

解决方案：

-- 1. 增加递归深度限制 PRAGMA recursive_triggers = ON; PRAGMA max_page_count = 1000000; -- 增加页面限制 -- 2. 优化递归查询 -- 使用迭代而非深度优先 WITH RECURSIVE employee_tree AS ( -- 基础查询 SELECT id, name, manager_id, 0 AS level FROM employees_hierarchy WHERE manager_id IS NULL UNION ALL -- 递归部分：限制每次递归的深度 SELECT e.id, e.name, e.manager_id, et.level + 1 FROM employees_hierarchy e INNER JOIN employee_tree et ON e.manager_id = et.id WHERE et.level < 100 -- 限制递归深度 ) SELECT * FROM employee_tree; -- 3. 分批处理 -- 对于极深的层次，可以分批处理 -- 例如，先处理前10层，再处理11-20层，以此类推

4.4 事务与并发问题

问题1：死锁 原因分析：

多个事务同时修改相同数据
锁竞争

解决方案：

-- 1. 使用事务隔离级别 BEGIN IMMEDIATE TRANSACTION; -- 立即获取锁 -- 2. 按固定顺序访问资源 -- 始终按相同顺序访问表 BEGIN TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT; -- 3. 设置超时 PRAGMA busy_timeout = 5000; -- 5秒超时 -- 4. 使用乐观锁 ALTER TABLE products ADD COLUMN version INTEGER DEFAULT 0; -- 更新时检查版本 UPDATE products SET stock = stock - 1, version = version + 1 WHERE id = 123 AND version = 5; -- 假设当前版本是5

问题2：长时间运行的查询阻塞其他操作 原因分析：

大型查询占用数据库资源
缺乏适当的索引

解决方案：

-- 1. 使用临时表分步处理 CREATE TEMPORARY TABLE temp_results AS SELECT * FROM large_table WHERE condition; -- 2. 使用游标逐行处理 -- 在应用层实现 import sqlite3 conn = sqlite3.connect('database.db') cursor = conn.cursor() cursor.execute("SELECT * FROM large_table") for row in cursor: # 处理每一行 process_row(row) # 可以在这里提交事务，释放锁 if row_count % 1000 == 0: conn.commit() -- 3. 使用异步查询（如果支持） -- 在支持异步的SQLite扩展中 -- 或者在应用层使用多线程/异步IO

5. 性能优化最佳实践

5.1 索引策略

-- 1. 复合索引 CREATE INDEX idx_orders_user_date_status ON orders(user_id, order_date, status); -- 2. 覆盖索引 CREATE INDEX idx_order_items_covering ON order_items(order_id, product_id, quantity, price); -- 3. 部分索引（SQLite 3.8.0+） CREATE INDEX idx_active_users ON users(email) WHERE status = 'active'; -- 4. 索引使用分析 EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id = 123 AND order_date >= '2023-01-01'; -- 5. 索引维护 -- 定期分析表 ANALYZE; -- 重建索引（删除并重新创建） DROP INDEX IF EXISTS idx_old; CREATE INDEX idx_new ON table(column);

5.2 查询优化技巧

-- 1. 避免SELECT * SELECT id, name, email FROM users WHERE condition; -- 2. 使用EXISTS代替IN -- 优化前 SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE status = 'completed'); -- 优化后 SELECT * FROM users u WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 'completed' ); -- 3. 避免在WHERE子句中使用函数 -- 优化前 SELECT * FROM users WHERE DATE(join_date) = '2023-01-01'; -- 优化后 SELECT * FROM users WHERE join_date = '2023-01-01'; -- 4. 使用LIMIT限制结果 SELECT * FROM large_table WHERE condition LIMIT 100; -- 5. 预编译查询 -- 在应用层使用参数化查询 -- Python示例 cursor.execute("SELECT * FROM users WHERE email = ?", (email,))

5.3 数据库设计优化

-- 1. 规范化与反规范化权衡 -- 对于频繁查询的关联数据，可以适当反规范化 ALTER TABLE orders ADD COLUMN customer_name TEXT; -- 2. 使用视图简化复杂查询 CREATE VIEW user_purchase_summary AS SELECT u.id, u.username, COUNT(DISTINCT o.id) AS order_count, SUM(oi.quantity * oi.price) AS total_spent FROM users u JOIN orders o ON u.id = o.user_id JOIN order_items oi ON o.id = oi.order_id GROUP BY u.id, u.username; -- 3. 使用物化视图（通过临时表实现） CREATE TEMPORARY TABLE materialized_view AS SELECT * FROM complex_query; -- 4. 数据分区（通过表设计实现） -- 按时间分区 CREATE TABLE orders_2023_q1 ( CHECK (order_date BETWEEN '2023-01-01' AND '2023-03-31') ) INHERITS (orders);

6. 高级技巧进阶

6.1 JSON数据处理（SQLite 3.9.0+）

-- 创建包含JSON的表 CREATE TABLE products_json ( id INTEGER PRIMARY KEY, attributes TEXT -- 存储JSON字符串 ); -- 插入JSON数据 INSERT INTO products_json VALUES (1, '{"color": "red", "size": "M", "tags": ["summer", "casual"]}'); -- 查询JSON字段 SELECT id, json_extract(attributes, '$.color') AS color, json_extract(attributes, '$.size') AS size, json_extract(attributes, '$.tags[0]') AS first_tag FROM products_json; -- JSON数组操作 SELECT id, json_array_length(attributes, '$.tags') AS tag_count, json_group_array(json_extract(attributes, '$.tags[*]')) AS all_tags FROM products_json GROUP BY id; -- JSON更新 UPDATE products_json SET attributes = json_set(attributes, '$.color', 'blue') WHERE id = 1; -- JSON查询条件 SELECT * FROM products_json WHERE json_extract(attributes, '$.color') = 'red';

6.2 全文搜索（FTS5）

-- 创建全文搜索虚拟表 CREATE VIRTUAL TABLE articles USING fts5( title, content, content='articles_content', tokenize='porter' ); -- 插入数据 INSERT INTO articles (title, content) VALUES ('SQLite Tutorial', 'Learn SQLite with this comprehensive tutorial.'), ('Advanced Queries', 'Master advanced SQL queries in SQLite.'); -- 基本搜索 SELECT * FROM articles WHERE articles MATCH 'SQLite'; -- 短语搜索 SELECT * FROM articles WHERE articles MATCH '"comprehensive tutorial"'; -- 布尔搜索 SELECT * FROM articles WHERE articles MATCH 'SQLite AND tutorial'; -- 模糊搜索 SELECT * FROM articles WHERE articles MATCH 'tutor*'; -- 排序相关性 SELECT rank, title, snippet(articles, -1, '<b>', '</b>', '...', 10) AS highlighted_content FROM articles WHERE articles MATCH 'SQLite' ORDER BY rank;

6.3 自定义函数与聚合

-- 创建自定义标量函数（Python示例） import sqlite3 import hashlib def md5_hash(text): return hashlib.md5(text.encode()).hexdigest() conn = sqlite3.connect('database.db') conn.create_function("md5", 1, md5_hash) -- 使用自定义函数 SELECT md5(email) AS email_hash FROM users; -- 创建自定义聚合函数 class MedianAggregate: def __init__(self): self.values = [] def step(self, value): if value is not None: self.values.append(value) def finalize(self): if not self.values: return None self.values.sort() n = len(self.values) if n % 2 == 1: return self.values[n//2] else: return (self.values[n//2-1] + self.values[n//2]) / 2 conn.create_aggregate("median", 1, MedianAggregate) -- 使用自定义聚合 SELECT department, median(salary) AS median_salary FROM employees GROUP BY department;