SQLite 数据库操作完整指南

SQLite 数据库操作完整指南

全面的 SQLite 数据库操作手册，涵盖从基础操作到高级优化的所有内容

目录

**

SQLite

简介与特点
创建和连接数据库
创建表
数据类型和约束
插入数据
查询数据
更新数据
删除数据
多表查询
视图
索引优化
触发器
事务处理
全文搜索
JSON 支持
窗口函数
备份和恢复
性能优化
常用函数
实用技巧和最佳实践
常见问题和解决方案
版本兼容性说明

**

1. SQLite 简介与特点

SQLite 特性

SQLite 是一个轻量级的嵌入式关系型数据库，具有以下特点：

• 无服务器架构：不需要独立的服务器进程
• 零配置：无需安装和管理
• 跨平台：支持所有主流操作系统
• 单一文件：整个数据库存储在一个文件中
• 事务性：支持 ACID 事务
• 体积小：完整的 SQLite 库小于 600KB

使用场景

-- SQLite 适用场景：
-- 1. 移动应用程序
-- 2. 桌面应用程序
-- 3. 嵌入式系统
-- 4. 网站的轻量级数据存储
-- 5. 应用程序配置管理
-- 6. 测试和原型开发

2. 创建和连接数据库

创建数据库

-- 在命令行中创建数据库
sqlite3 company.db

-- 在 SQLite shell 中创建数据库
.open company.db

-- 使用 Python 创建数据库
-- import sqlite3
-- conn = sqlite3.connect('company.db')
-- conn.close()

基本命令

-- 查看 SQLite 版本
SELECT sqlite_version();

-- 查看所有表
.tables

-- 查看表结构
.schema employees

-- 查看数据库信息
.databases

-- 开启列标题显示
.headers on

-- 设置输出模式
.mode column  -- 列模式
.mode list    -- 列表模式
.mode csv     -- CSV模式
.mode table   -- 表格模式

-- 设置列宽
.width 10 20 15

-- 退出 SQLite
.quit

附加数据库

-- 附加另一个数据库
ATTACH DATABASE 'backup.db' AS backup;

-- 查看附加的数据库
.databases

-- 在附加的数据库中创建表
CREATE TABLE backup.employees_backup AS SELECT * FROM main.employees;

-- 分离数据库
DETACH DATABASE backup;

3. 创建表

基本表创建

-- 创建员工表
CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email TEXT UNIQUE,
    phone TEXT,
    hire_date DATE DEFAULT CURRENT_DATE,
    salary REAL CHECK (salary > 0),
    department_id INTEGER,
    is_active INTEGER DEFAULT 1,
    created_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    modified_date DATETIME
);

-- 创建部门表
CREATE TABLE departments (
    department_id INTEGER PRIMARY KEY AUTOINCREMENT,
    department_name TEXT NOT NULL UNIQUE,
    location TEXT,
    budget REAL,
    manager_id INTEGER,
    created_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);

-- 创建项目表
CREATE TABLE projects (
    project_id INTEGER PRIMARY KEY AUTOINCREMENT,
    project_name TEXT NOT NULL,
    description TEXT,
    start_date DATE,
    end_date DATE,
    budget REAL,
    status TEXT DEFAULT 'Planning',
    department_id INTEGER,
    FOREIGN KEY (department_id) REFERENCES departments(department_id),
    CHECK (end_date >= start_date)
);

-- 创建多对多关系表
CREATE TABLE employee_projects (
    employee_project_id INTEGER PRIMARY KEY AUTOINCREMENT,
    employee_id INTEGER NOT NULL,
    project_id INTEGER NOT NULL,
    role TEXT,
    assigned_date DATE DEFAULT CURRENT_DATE,
    hours_worked REAL DEFAULT 0,
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id),
    FOREIGN KEY (project_id) REFERENCES projects(project_id),
    UNIQUE(employee_id, project_id)
);

临时表和虚拟表

-- 创建临时表
CREATE TEMP TABLE temp_calculations (
    id INTEGER PRIMARY KEY,
    result REAL,
    calculated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 创建虚拟表（用于全文搜索）
CREATE VIRTUAL TABLE documents USING fts5(
    title,
    content,
    author,
    tags
);

-- WITHOUT ROWID 表（优化存储）
CREATE TABLE config (
    key TEXT PRIMARY KEY,
    value TEXT,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
) WITHOUT ROWID;

从查询结果创建表

-- 创建表并复制数据
CREATE TABLE high_salary_employees AS
SELECT * FROM employees WHERE salary > 50000;

-- 只创建表结构
CREATE TABLE employees_template AS
SELECT * FROM employees WHERE 1=0;

-- 使用 WITH 子句创建表
CREATE TABLE department_summary AS
WITH dept_stats AS (
    SELECT 
        department_id,
        COUNT(*) as employee_count,
        AVG(salary) as avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT 
    d.department_name,
    ds.employee_count,
    ds.avg_salary
FROM departments d
JOIN dept_stats ds ON d.department_id = ds.department_id;

4. 数据类型和约束

SQLite 数据类型

-- SQLite 使用动态类型系统，支持以下存储类：
-- NULL - 空值
-- INTEGER - 整数
-- REAL - 浮点数
-- TEXT - 文本字符串
-- BLOB - 二进制数据

-- 类型亲和性示例
CREATE TABLE type_examples (
    -- INTEGER 亲和性
    int_col INTEGER,
    id_col INT,
    bigint_col BIGINT,
    
    -- TEXT 亲和性
    text_col TEXT,
    varchar_col VARCHAR(100),
    char_col CHAR(10),
    
    -- REAL 亲和性
    real_col REAL,
    float_col FLOAT,
    double_col DOUBLE,
    decimal_col DECIMAL(10,2),
    
    -- NUMERIC 亲和性
    numeric_col NUMERIC,
    boolean_col BOOLEAN,
    date_col DATE,
    datetime_col DATETIME
);

-- 类型转换示例
SELECT 
    CAST('123' AS INTEGER) AS int_value,
    CAST(123 AS TEXT) AS text_value,
    CAST('123.45' AS REAL) AS real_value,
    typeof(123) AS type_of_int,
    typeof('123') AS type_of_text,
    typeof(123.45) AS type_of_real;

约束详解

-- PRIMARY KEY 约束
CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,  -- 自动创建 ROWID 别名
    username TEXT NOT NULL
);

-- 复合主键
CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER DEFAULT 1,
    PRIMARY KEY (order_id, product_id)
) WITHOUT ROWID;

-- UNIQUE 约束
CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    product_code TEXT UNIQUE,
    product_name TEXT,
    UNIQUE(product_name, product_code)
);

-- CHECK 约束
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    order_date DATE DEFAULT CURRENT_DATE,
    total_amount REAL CHECK (total_amount >= 0),
    status TEXT CHECK (status IN ('pending', 'processing', 'completed', 'cancelled')),
    CHECK (order_date <= CURRENT_DATE)
);

-- DEFAULT 约束
CREATE TABLE audit_log (
    log_id INTEGER PRIMARY KEY,
    action TEXT NOT NULL,
    user_id INTEGER,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    ip_address TEXT DEFAULT '0.0.0.0',
    success INTEGER DEFAULT 1
);

-- 外键约束（需要启用）
PRAGMA foreign_keys = ON;

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    customer_name TEXT NOT NULL
);

CREATE TABLE customer_orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    order_date DATE DEFAULT CURRENT_DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

修改表结构

-- 添加列
ALTER TABLE employees ADD COLUMN middle_name TEXT;
ALTER TABLE employees ADD COLUMN birth_date DATE;

-- 重命名表
ALTER TABLE employees RENAME TO staff;
ALTER TABLE staff RENAME TO employees;

-- 重命名列（SQLite 3.25.0+）
ALTER TABLE employees RENAME COLUMN phone TO phone_number;

-- 删除列（SQLite 3.35.0+）
ALTER TABLE employees DROP COLUMN middle_name;

-- 对于旧版本 SQLite，需要重建表来删除列
-- 1. 创建新表
CREATE TABLE employees_new AS 
SELECT employee_id, first_name, last_name, email, hire_date, salary, department_id 
FROM employees;

-- 2. 删除旧表
DROP TABLE employees;

-- 3. 重命名新表
ALTER TABLE employees_new RENAME TO employees;

5. 插入数据

基本插入操作

-- 插入单行数据
INSERT INTO departments (department_name, location, budget) 
VALUES ('人力资源部', '北京', 500000.00);

-- 插入多行数据
INSERT INTO departments (department_name, location, budget) VALUES
    ('技术部', '上海', 2000000.00),
    ('销售部', '广州', 1500000.00),
    ('财务部', '北京', 800000.00),
    ('市场部', '深圳', 1200000.00);

-- 插入员工数据
INSERT INTO employees (first_name, last_name, email, phone, hire_date, salary, department_id) VALUES
    ('张', '三', 'zhang.san@company.com', '13800138001', '2023-01-15', 8000.00, 2),
    ('李', '四', 'li.si@company.com', '13800138002', '2023-02-20', 12000.00, 2),
    ('王', '五', 'wang.wu@company.com', '13800138003', '2023-03-10', 7000.00, 3),
    ('赵', '六', 'zhao.liu@company.com', '13800138004', '2023-04-05', 9000.00, 1),
    ('陈', '七', 'chen.qi@company.com', '13800138005', '2023-05-12', 11000.00, 4);

-- 使用 DEFAULT 值
INSERT INTO employees (first_name, last_name, email, salary, department_id)
VALUES ('新', '员工', 'new.employee@company.com', 8500.00, 1);

高级插入操作

-- INSERT OR REPLACE（存在则更新，不存在则插入）
INSERT OR REPLACE INTO products (product_id, product_name, price)
VALUES (1, 'iPhone 15', 999.99);

-- INSERT OR IGNORE（存在则忽略）
INSERT OR IGNORE INTO departments (department_name, location)
VALUES ('技术部', '上海');

-- INSERT OR ABORT（默认行为，违反约束时中止）
INSERT OR ABORT INTO employees (employee_id, first_name, last_name)
VALUES (1, 'Test', 'User');

-- 从其他表插入数据
INSERT INTO employee_backup 
SELECT * FROM employees WHERE department_id = 2;

-- 使用 WITH 子句插入
WITH new_projects AS (
    SELECT 
        'Project-' || department_id AS project_name,
        'Auto-generated project' AS description,
        date('now') AS start_date,
        date('now', '+6 months') AS end_date,
        budget * 0.1 AS project_budget,
        department_id
    FROM departments
    WHERE budget > 500000
)
INSERT INTO projects (project_name, description, start_date, end_date, budget, department_id)
SELECT * FROM new_projects;

-- 条件插入
INSERT INTO employees (first_name, last_name, email, salary, department_id)
SELECT '测试', '用户', 'test.user@company.com', 7500.00, 1
WHERE NOT EXISTS (
    SELECT 1 FROM employees WHERE email = 'test.user@company.com'
);

批量插入优化

-- 使用事务批量插入
BEGIN TRANSACTION;

INSERT INTO large_table (col1, col2, col3) VALUES (1, 'A', 100);
INSERT INTO large_table (col1, col2, col3) VALUES (2, 'B', 200);
-- ... 更多插入语句
INSERT INTO large_table (col1, col2, col3) VALUES (1000, 'Z', 10000);

COMMIT;

-- 使用预处理语句（在应用程序中）
-- PREPARE stmt FROM 'INSERT INTO employees (first_name, last_name, salary) VALUES (?, ?, ?)';
-- EXECUTE stmt USING @fname, @lname, @sal;

-- 关闭同步以提高性能（谨慎使用）
PRAGMA synchronous = OFF;
-- 执行批量插入
PRAGMA synchronous = NORMAL;  -- 恢复默认设置

-- 使用 UPSERT（SQLite 3.24.0+）
INSERT INTO employees (employee_id, first_name, last_name, salary)
VALUES (1, '张', '三', 9000)
ON CONFLICT(employee_id) 
DO UPDATE SET 
    salary = excluded.salary,
    modified_date = CURRENT_TIMESTAMP
WHERE excluded.salary > employees.salary;

6. 查询数据

基本查询

-- 查询所有数据
SELECT * FROM employees;

-- 查询特定列
SELECT employee_id, first_name, last_name, salary 
FROM employees;

-- 使用别名
SELECT 
    e.first_name || ' ' || e.last_name AS full_name,
    e.email AS email_address,
    e.salary AS monthly_salary,
    e.salary * 12 AS annual_salary
FROM employees e;

-- 条件查询
SELECT * FROM employees WHERE salary > 10000;
SELECT * FROM employees WHERE department_id = 2 AND is_active = 1;
SELECT * FROM employees WHERE hire_date BETWEEN '2023-01-01' AND '2023-06-30';

-- IN 和 NOT IN
SELECT * FROM employees WHERE department_id IN (1, 2, 3);
SELECT * FROM employees WHERE department_id NOT IN (4, 5);

-- LIKE 模式匹配
SELECT * FROM employees WHERE first_name LIKE '张%';
SELECT * FROM employees WHERE email LIKE '%@company.com';
SELECT * FROM employees WHERE last_name LIKE '_四';  -- 第二个字是"四"

-- GLOB 模式匹配（大小写敏感）
SELECT * FROM employees WHERE email GLOB '*@company.com';
SELECT * FROM employees WHERE first_name GLOB '[张李王]*';

-- NULL 值处理
SELECT * FROM employees WHERE phone IS NULL;
SELECT * FROM employees WHERE phone IS NOT NULL;

-- 排序
SELECT * FROM employees ORDER BY salary DESC;
SELECT * FROM employees ORDER BY department_id, salary DESC;

-- 限制结果数量
SELECT * FROM employees ORDER BY salary DESC LIMIT 5;
SELECT * FROM employees ORDER BY salary DESC LIMIT 5 OFFSET 10;

聚合函数

-- 基本聚合函数
SELECT 
    COUNT(*) AS total_employees,
    COUNT(DISTINCT department_id) AS department_count,
    AVG(salary) AS average_salary,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary,
    SUM(salary) AS total_salary_expense,
    GROUP_CONCAT(first_name || ' ' || last_name, ', ') AS all_names
FROM employees;

-- 分组统计
SELECT 
    department_id,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 1;

-- 使用 CASE 语句
SELECT 
    first_name || ' ' || last_name AS full_name,
    salary,
    CASE 
        WHEN salary < 8000 THEN '初级'
        WHEN salary BETWEEN 8000 AND 12000 THEN '中级'
        ELSE '高级'
    END AS level
FROM employees;

-- 分组统计薪资级别
SELECT 
    CASE 
        WHEN salary < 8000 THEN '初级'
        WHEN salary BETWEEN 8000 AND 12000 THEN '中级'
        ELSE '高级'
    END AS salary_level,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY salary_level;

子查询

-- 标量子查询
SELECT 
    first_name || ' ' || last_name AS full_name,
    salary,
    (SELECT AVG(salary) FROM employees) AS company_avg_salary,
    salary - (SELECT AVG(salary) FROM employees) AS salary_difference
FROM employees;

-- 相关子查询
SELECT 
    e.first_name || ' ' || e.last_name AS full_name,
    e.salary,
    d.department_name,
    (SELECT COUNT(*) FROM employees e2 
     WHERE e2.department_id = e.department_id 
     AND e2.salary > e.salary) AS higher_salary_count
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- EXISTS 子查询
SELECT department_name, budget
FROM departments d
WHERE EXISTS (
    SELECT 1 
    FROM employees e 
    WHERE e.department_id = d.department_id 
    AND e.salary > 10000
);

-- NOT EXISTS 子查询
SELECT first_name || ' ' || last_name AS full_name
FROM employees e
WHERE NOT EXISTS (
    SELECT 1 
    FROM employee_projects ep 
    WHERE ep.employee_id = e.employee_id
);

-- IN 子查询
SELECT * FROM employees
WHERE department_id IN (
    SELECT department_id 
    FROM departments 
    WHERE location = '上海'
);

WITH 子句（CTE）

-- 基本 CTE
WITH department_stats AS (
    SELECT 
        department_id,
        COUNT(*) AS employee_count,
        AVG(salary) AS avg_salary,
        SUM(salary) AS total_salary
    FROM employees
    GROUP BY department_id
)
SELECT 
    d.department_name,
    ds.employee_count,
    printf('%.2f', ds.avg_salary) AS avg_salary,
    ds.total_salary,
    d.budget,
    d.budget - ds.total_salary AS remaining_budget
FROM department_stats ds
JOIN departments d ON ds.department_id = d.department_id
WHERE ds.employee_count > 1;

-- 多个 CTE
WITH 
dept_employees AS (
    SELECT department_id, COUNT(*) AS emp_count
    FROM employees
    GROUP BY department_id
),
dept_projects AS (
    SELECT department_id, COUNT(*) AS proj_count
    FROM projects
    GROUP BY department_id
)
SELECT 
    d.department_name,
    COALESCE(de.emp_count, 0) AS employee_count,
    COALESCE(dp.proj_count, 0) AS project_count
FROM departments d
LEFT JOIN dept_employees de ON d.department_id = de.department_id
LEFT JOIN dept_projects dp ON d.department_id = dp.department_id;

-- 递归 CTE
WITH RECURSIVE employee_hierarchy(employee_id, name, level, path) AS (
    -- 初始查询：找到所有经理
    SELECT 
        e.employee_id,
        e.first_name || ' ' || e.last_name AS name,
        0 AS level,
        e.first_name || ' ' || e.last_name AS path
    FROM employees e
    WHERE e.employee_id IN (SELECT manager_id FROM departments WHERE manager_id IS NOT NULL)
    
    UNION ALL
    
    -- 递归查询：找到下属
    SELECT 
        e.employee_id,
        e.first_name || ' ' || e.last_name,
        eh.level + 1,
        eh.path || ' -> ' || e.first_name || ' ' || e.last_name
    FROM employees e
    JOIN employee_hierarchy eh ON e.department_id IN (
        SELECT department_id FROM departments WHERE manager_id = eh.employee_id
    )
    WHERE eh.level < 3  -- 限制递归深度
)
SELECT * FROM employee_hierarchy ORDER BY level, name;

7. 更新数据

基本更新操作

-- 更新单个字段
UPDATE employees 
SET salary = 9000.00 
WHERE employee_id = 1;

-- 更新多个字段
UPDATE employees 
SET 
    phone = '13900139001',
    email = 'zhang.san.new@company.com',
    modified_date = CURRENT_TIMESTAMP
WHERE employee_id = 1;

-- 条件更新
UPDATE employees 
SET salary = salary * 1.1 
WHERE department_id = 2 AND salary < 10000;

-- 使用 CASE 语句更新
UPDATE employees
SET salary = CASE 
    WHEN department_id = 1 THEN salary * 1.05
    WHEN department_id = 2 THEN salary * 1.10
    WHEN department_id = 3 THEN salary * 1.08
    ELSE salary * 1.03
END,
modified_date = CURRENT_TIMESTAMP;

高级更新操作

-- 使用子查询更新
UPDATE employees 
SET salary = (
    SELECT AVG(salary) * 1.1 
    FROM employees e2 
    WHERE e2.department_id = employees.department_id
)
WHERE salary < (
    SELECT AVG(salary) 
    FROM employees e3 
    WHERE e3.department_id = employees.department_id
);

-- 从其他表更新数据
UPDATE employees
SET salary = s.new_salary
FROM (
    SELECT employee_id, salary * 1.1 AS new_salary
    FROM employees
    WHERE department_id = 2
) s
WHERE employees.employee_id = s.employee_id;

-- 使用 CTE 更新
WITH salary_updates AS (
    SELECT 
        employee_id,
        salary * 1.15 AS new_salary
    FROM employees
    WHERE employee_id IN (
        SELECT employee_id
        FROM employees
        ORDER BY salary DESC
        LIMIT 5
    )
)
UPDATE employees
SET salary = su.new_salary,
    modified_date = CURRENT_TIMESTAMP
FROM salary_updates su
WHERE employees.employee_id = su.employee_id;

-- UPDATE OR REPLACE
UPDATE OR REPLACE employees
SET email = 'new.email@company.com'
WHERE employee_id = 1;

批量更新优化

-- 使用事务批量更新
BEGIN TRANSACTION;

UPDATE employees SET salary = salary * 1.05 WHERE department_id = 1;
UPDATE employees SET salary = salary * 1.10 WHERE department_id = 2;
UPDATE employees SET salary = salary * 1.08 WHERE department_id = 3;

COMMIT;

-- 使用临时表批量更新
-- 1. 创建临时表
CREATE TEMP TABLE salary_updates (
    employee_id INTEGER PRIMARY KEY,
    new_salary REAL
);

-- 2. 插入更新数据
INSERT INTO salary_updates (employee_id, new_salary) VALUES
    (1, 9500.00),
    (2, 13000.00),
    (3, 7700.00);

-- 3. 执行批量更新
UPDATE employees
SET salary = su.new_salary,
    modified_date = CURRENT_TIMESTAMP
FROM salary_updates su
WHERE employees.employee_id = su.employee_id;

-- 4. 清理临时表
DROP TABLE salary_updates;

8. 删除数据

基本删除操作

-- 删除单条记录
DELETE FROM employees WHERE employee_id = 100;

-- 条件删除
DELETE FROM employees 
WHERE salary < 5000 AND is_active = 0;

-- 删除所有记录（保留表结构）
DELETE FROM temp_table;

-- 使用子查询删除
DELETE FROM employee_projects
WHERE project_id IN (
    SELECT project_id 
    FROM projects 
    WHERE status = 'cancelled'
);

-- 删除重复数据
DELETE FROM employees
WHERE rowid NOT IN (
    SELECT MIN(rowid)
    FROM employees
    GROUP BY email
);

高级删除操作

-- 使用 CTE 删除
WITH inactive_employees AS (
    SELECT employee_id
    FROM employees
    WHERE is_active = 0
    AND date(modified_date) < date('now', '-2 years')
)
DELETE FROM employee_projects
WHERE employee_id IN (SELECT employee_id FROM inactive_employees);

-- 限制删除数量
DELETE FROM log_table
WHERE log_id IN (
    SELECT log_id
    FROM log_table
    WHERE timestamp < date('now', '-30 days')
    ORDER BY timestamp
    LIMIT 1000
);

-- 级联删除（需要开启外键约束）
PRAGMA foreign_keys = ON;

-- 删除部门（会级联删除相关员工）
DELETE FROM departments WHERE department_id = 5;

清空和删除表

-- 删除表中所有数据（快速，重置自增ID）
DELETE FROM employees;
VACUUM;  -- 回收空间

-- 删除表
DROP TABLE IF EXISTS temp_table;

-- 删除多个表
DROP TABLE IF EXISTS table1, table2, table3;

-- 删除视图
DROP VIEW IF EXISTS employee_view;

-- 删除索引
DROP INDEX IF EXISTS idx_employees_email;

-- 删除触发器
DROP TRIGGER IF EXISTS update_modified_date;

9. 多表查询

内连接（INNER JOIN）

-- 基本内连接
SELECT 
    e.first_name || ' ' || e.last_name AS employee_name,
    e.email,
    e.salary,
    d.department_name,
    d.location
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

-- 使用 USING 子句
SELECT 
    e.first_name || ' ' || e.last_name AS employee_name,
    d.department_name
FROM employees e
INNER JOIN departments d USING (department_id);

-- 多表连接
SELECT 
    e.first_name || ' ' || e.last_name AS employee_name,
    d.department_name,
    p.project_name,
    ep.role,
    ep.hours_worked
FROM employees e
INNER JOIN employee_projects ep ON e.employee_id = ep.employee_id
INNER JOIN projects p ON ep.project_id = p.project_id
INNER JOIN departments d ON e.department_id = d.department_id
WHERE ep.hours_worked > 100;

左连接（LEFT JOIN）

-- 查询所有员工及其部门（包括未分配部门的员工）
SELECT 
    e.first_name || ' ' || e.last_name AS employee_name,
    e.email,
    e.salary,
    COALESCE(d.department_name, '未分配部门') AS department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

-- 查询所有部门及其员工数量
SELECT 
    d.department_name,
    d.location,
    d.budget,
    COUNT(e.employee_id) AS employee_count,
    COALESCE(AVG(e.salary), 0) AS avg_salary
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name, d.location, d.budget;

-- 查找没有员工的部门
SELECT d.*
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
WHERE e.employee_id IS NULL;

交叉连接（CROSS JOIN）

-- 生成所有可能的员工-项目组合
SELECT 
    e.first_name || ' ' || e.last_name AS employee_name,
    p.project_name
FROM employees e
CROSS JOIN projects p
WHERE e.department_id = p.department_id
AND NOT EXISTS (
    SELECT 1 FROM employee_projects ep 
    WHERE ep.employee_id = e.employee_id 
    AND ep.project_id = p.project_id
);

-- 生成日期序列
WITH RECURSIVE dates(date) AS (
    SELECT date('now', '-30 days')
    UNION ALL
    SELECT date(date, '+1 day')
    FROM dates
    WHERE date < date('now')
)
SELECT * FROM dates;

自连接（Self JOIN）

-- 查找同部门的员工配对
SELECT 
    e1.first_name || ' ' || e1.last_name AS employee1,
    e2.first_name || ' ' || e2.last_name AS employee2,
    d.department_name
FROM employees e1
INNER JOIN employees e2 ON e1.department_id = e2.department_id 
    AND e1.employee_id < e2.employee_id
INNER JOIN departments d ON e1.department_id = d.department_id;

-- 查找员工的上级
SELECT 
    e.first_name || ' ' || e.last_name AS employee_name,
    m.first_name || ' ' || m.last_name AS manager_name,
    d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
LEFT JOIN employees m ON d.manager_id = m.employee_id;

UNION 操作

-- UNION（去重）
SELECT first_name || ' ' || last_name AS name, 'Employee' AS type, salary AS amount
FROM employees
UNION
SELECT department_name AS name, 'Department' AS type, budget AS amount
FROM departments
ORDER BY amount DESC;

-- UNION ALL（保留重复）
SELECT department_id, 'Employee' AS source FROM employees
UNION ALL
SELECT department_id, 'Project' AS source FROM projects
ORDER BY department_id, source;

-- EXCEPT（差集）
SELECT employee_id FROM employees
EXCEPT
SELECT DISTINCT employee_id FROM employee_projects;

-- INTERSECT（交集）
SELECT department_id FROM employees
INTERSECT
SELECT department_id FROM projects;

10. 视图

创建基本视图

-- 创建简单视图
CREATE VIEW employee_basic_info AS
SELECT 
    employee_id,
    first_name || ' ' || last_name AS full_name,
    email,
    phone,
    hire_date
FROM employees
WHERE is_active = 1;

-- 创建带 JOIN 的视图
CREATE VIEW employee_department_info AS
SELECT 
    e.employee_id,
    e.first_name || ' ' || e.last_name AS full_name,
    e.email,
    e.salary,
    d.department_name,
    d.location,
    m.first_name || ' ' || m.last_name AS manager_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
LEFT JOIN employees m ON d.manager_id = m.employee_id
WHERE e.is_active = 1;

-- 使用视图
SELECT * FROM employee_department_info WHERE salary > 10000;

创建高级视图

-- 带聚合的视图
CREATE VIEW department_statistics AS
SELECT 
    d.department_id,
    d.department_name,
    d.location,
    d.budget,
    COUNT(e.employee_id) AS employee_count,
    COALESCE(AVG(e.salary), 0) AS average_salary,
    COALESCE(SUM(e.salary), 0) AS total_salary_expense,
    d.budget - COALESCE(SUM(e.salary), 0) AS remaining_budget
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id AND e.is_active = 1
GROUP BY d.department_id, d.department_name, d.location, d.budget;

-- 带 CTE 的视图
CREATE VIEW employee_ranking AS
WITH salary_ranking AS (
    SELECT 
        employee_id,
        first_name || ' ' || last_name AS full_name,
        salary,
        department_id,
        ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_salary_rank,
        RANK() OVER (ORDER BY salary DESC) AS company_salary_rank
    FROM employees
    WHERE is_active = 1
)
SELECT * FROM salary_ranking;

-- 带 UNION 的视图
CREATE VIEW all_contacts AS
SELECT 
    'Employee' AS contact_type,
    employee_id AS contact_id,
    first_name || ' ' || last_name AS name,
    email,
    phone
FROM employees
WHERE is_active = 1
UNION ALL
SELECT 
    'Manager' AS contact_type,
    m.employee_id AS contact_id,
    m.first_name || ' ' || m.last_name AS name,
    m.email,
    m.phone
FROM departments d
INNER JOIN employees m ON d.manager_id = m.employee_id;

临时视图

-- 创建临时视图
CREATE TEMP VIEW temp_high_salary AS
SELECT * FROM employees WHERE salary > 10000;

-- 使用临时视图
SELECT * FROM temp_high_salary;

-- 临时视图在会话结束时自动删除

视图管理

-- 查看所有视图
SELECT name, sql 
FROM sqlite_master 
WHERE type = 'view';

-- 查看特定视图的定义
SELECT sql 
FROM sqlite_master 
WHERE type = 'view' AND name = 'employee_department_info';

-- 替换视图
DROP VIEW IF EXISTS employee_basic_info;
CREATE VIEW employee_basic_info AS
SELECT 
    employee_id,
    first_name || ' ' || last_name AS full_name,
    email,
    phone,
    hire_date,
    department_id  -- 新增字段
FROM employees
WHERE is_active = 1;

-- 删除视图
DROP VIEW IF EXISTS employee_basic_info;

11. 索引优化

创建索引

-- 创建简单索引
CREATE INDEX idx_employees_last_name ON employees(last_name);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_employees_email ON employees(email);

-- 创建复合索引
CREATE INDEX idx_employees_dept_salary ON employees(department_id, salary DESC);

-- 创建条件索引（部分索引）
CREATE INDEX idx_active_employees_salary ON employees(salary) 
WHERE is_active = 1;

-- 创建表达式索引
CREATE INDEX idx_employees_full_name ON employees(first_name || ' ' || last_name);

-- 创建覆盖索引
CREATE INDEX idx_employees_email_covering ON employees(email) 
WHERE email IS NOT NULL;

索引分析

-- 查看所有索引
SELECT 
    m.name AS table_name,
    il.name AS index_name,
    il.unique,
    il.origin,
    il.partial
FROM sqlite_master m
JOIN pragma_index_list(m.name) il
WHERE m.type = 'table'
ORDER BY m.name, il.name;

-- 查看索引详细信息
SELECT 
    m.name AS table_name,
    ii.name AS index_name,
    ii.seqno AS column_position,
    ii.cid AS column_id,
    ii.name AS column_name,
    ii.desc,
    ii.coll,
    ii.key
FROM sqlite_master m,
     pragma_index_list(m.name) il,
     pragma_index_info(il.name) ii
WHERE m.type = 'table'
AND m.name = 'employees';

-- 分析表和索引
ANALYZE;
ANALYZE employees;

-- 查看统计信息
SELECT * FROM sqlite_stat1;
SELECT * FROM sqlite_stat4;

查询计划分析

-- 使用 EXPLAIN QUERY PLAN
EXPLAIN QUERY PLAN
SELECT * FROM employees WHERE department_id = 2;

-- 分析复杂查询
EXPLAIN QUERY PLAN
SELECT 
    e.first_name || ' ' || e.last_name AS employee_name,
    d.department_name,
    e.salary
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 10000
ORDER BY e.salary DESC;

-- 使用 EXPLAIN（详细执行计划）
EXPLAIN
SELECT * FROM employees WHERE email = 'zhang.san@company.com';

索引优化策略

-- 删除未使用的索引
DROP INDEX IF EXISTS idx_unused_index;

-- 重建索引
REINDEX;  -- 重建所有索引
REINDEX employees;  -- 重建特定表的索引
REINDEX idx_employees_email;  -- 重建特定索引

-- 优化数据库
VACUUM;  -- 清理并重组数据库
VACUUM INTO 'backup.db';  -- 创建优化后的副本

-- 自动索引
-- SQLite 会为以下情况自动创建索引：
-- 1. PRIMARY KEY 列
-- 2. UNIQUE 约束列

-- 禁用自动索引（用于测试）
PRAGMA automatic_index = OFF;

-- 查看查询优化器的选择
.eqp on  -- 在 SQLite shell 中启用
SELECT * FROM employees WHERE salary > 10000;
.eqp off

12. 触发器

创建基本触发器

-- BEFORE INSERT 触发器
CREATE TRIGGER validate_employee_salary
BEFORE INSERT ON employees
FOR EACH ROW
WHEN NEW.salary <= 0
BEGIN
    SELECT RAISE(ABORT, '薪资必须大于0');
END;

-- AFTER INSERT 触发器
CREATE TRIGGER log_new_employee
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (action, table_name, record_id, user, timestamp)
    VALUES ('INSERT', 'employees', NEW.employee_id, 'system', CURRENT_TIMESTAMP);
END;

-- BEFORE UPDATE 触发器
CREATE TRIGGER update_modified_date
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    UPDATE employees 
    SET modified_date = CURRENT_TIMESTAMP 
    WHERE employee_id = NEW.employee_id;
END;

-- AFTER UPDATE 触发器
CREATE TRIGGER log_salary_changes
AFTER UPDATE OF salary ON employees
FOR EACH ROW
WHEN OLD.salary != NEW.salary
BEGIN
    INSERT INTO salary_history (employee_id, old_salary, new_salary, change_date)
    VALUES (NEW.employee_id, OLD.salary, NEW.salary, CURRENT_TIMESTAMP);
END;

-- BEFORE DELETE 触发器
CREATE TRIGGER prevent_manager_delete
BEFORE DELETE ON employees
FOR EACH ROW
WHEN EXISTS (SELECT 1 FROM departments WHERE manager_id = OLD.employee_id)
BEGIN
    SELECT RAISE(ABORT, '不能删除部门经理');
END;

-- AFTER DELETE 触发器
CREATE TRIGGER archive_deleted_employee
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employees_archive 
    SELECT *, CURRENT_TIMESTAMP AS deleted_date 
    FROM employees 
    WHERE employee_id = OLD.employee_id;
END;

高级触发器

-- 多条件触发器
CREATE TRIGGER complex_validation
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    -- 验证邮箱格式
    SELECT CASE
        WHEN NEW.email NOT LIKE '%_@_%.__%' THEN
            RAISE(ABORT, '邮箱格式无效')
    END;
    
    -- 验证部门存在
    SELECT CASE
        WHEN NOT EXISTS (SELECT 1 FROM departments WHERE department_id = NEW.department_id) THEN
            RAISE(ABORT, '部门不存在')
    END;
    
    -- 验证薪资范围
    SELECT CASE
        WHEN NEW.salary < 3000 OR NEW.salary > 100000 THEN
            RAISE(ABORT, '薪资超出有效范围')
    END;
END;

-- 级联更新触发器
CREATE TRIGGER cascade_department_update
AFTER UPDATE OF department_name ON departments
FOR EACH ROW
BEGIN
    -- 更新相关日志
    INSERT INTO audit_log (action, details, timestamp)
    VALUES ('部门更名', '从 ' || OLD.department_name || ' 改为 ' || NEW.department_name, CURRENT_TIMESTAMP);
    
    -- 通知相关员工（模拟）
    UPDATE notifications 
    SET message = '您的部门已更名为：' || NEW.department_name,
        created_at = CURRENT_TIMESTAMP
    WHERE employee_id IN (
        SELECT employee_id FROM employees WHERE department_id = NEW.department_id
    );
END;

-- INSTEAD OF 触发器（用于视图）
CREATE VIEW employee_summary AS
SELECT 
    e.employee_id,
    e.first_name || ' ' || e.last_name AS full_name,
    e.salary,
    d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

CREATE TRIGGER update_employee_summary
INSTEAD OF UPDATE ON employee_summary
FOR EACH ROW
BEGIN
    UPDATE employees 
    SET salary = NEW.salary
    WHERE employee_id = NEW.employee_id;
END;

触发器管理

-- 查看所有触发器
SELECT name, sql 
FROM sqlite_master 
WHERE type = 'trigger';

-- 查看特定表的触发器
SELECT name, sql 
FROM sqlite_master 
WHERE type = 'trigger' 
AND tbl_name = 'employees';

-- 删除触发器
DROP TRIGGER IF EXISTS update_modified_date;

-- 临时禁用触发器（通过重命名）
ALTER TABLE employees RENAME TO employees_temp;
CREATE TABLE employees AS SELECT * FROM employees_temp;
-- 执行不需要触发器的操作
DROP TABLE employees_temp;
-- 重新创建触发器

13. 事务处理

基本事务操作

-- 开始事务
BEGIN TRANSACTION;
-- 或
BEGIN;

-- 执行操作
UPDATE employees SET salary = salary * 1.1 WHERE department_id = 2;
INSERT INTO salary_history (employee_id, old_salary, new_salary, change_date)
SELECT employee_id, salary / 1.1, salary, CURRENT_TIMESTAMP
FROM employees WHERE department_id = 2;

-- 提交事务
COMMIT;

-- 回滚事务
BEGIN TRANSACTION;
DELETE FROM employees WHERE employee_id = 1;
-- 发现错误，回滚
ROLLBACK;

事务隔离

-- 查看当前隔离级别
PRAGMA read_uncommitted;

-- 设置隔离级别
PRAGMA read_uncommitted = TRUE;  -- 允许脏读
PRAGMA read_uncommitted = FALSE; -- 默认，不允许脏读

-- 事务模式
BEGIN DEFERRED;    -- 默认，延迟获取锁
BEGIN IMMEDIATE;   -- 立即获取保留锁
BEGIN EXCLUSIVE;   -- 立即获取排他锁

-- IMMEDIATE 事务示例
BEGIN IMMEDIATE;
UPDATE employees SET salary = salary * 1.05;
-- 其他连接无法写入，但可以读取
COMMIT;

-- EXCLUSIVE 事务示例
BEGIN EXCLUSIVE;
-- 批量数据导入或大规模更新
DELETE FROM old_data;
INSERT INTO new_data SELECT * FROM temp_import;
COMMIT;

保存点（Savepoint）

BEGIN TRANSACTION;

-- 第一阶段操作
UPDATE employees SET salary = salary * 1.1 WHERE department_id = 1;
SAVEPOINT stage1;

-- 第二阶段操作
UPDATE employees SET salary = salary * 1.1 WHERE department_id = 2;
SAVEPOINT stage2;

-- 第三阶段操作出错
UPDATE employees SET salary = 'invalid';  -- 这会出错

-- 回滚到 stage2
ROLLBACK TO SAVEPOINT stage2;

-- 继续其他操作
UPDATE employees SET salary = salary * 1.05 WHERE department_id = 2;

-- 提交事务
COMMIT;

-- 释放保存点
RELEASE SAVEPOINT stage1;

事务错误处理

-- 使用触发器实现约束和错误处理
CREATE TRIGGER enforce_salary_increase
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
WHEN NEW.salary < OLD.salary
BEGIN
    SELECT RAISE(ABORT, '不允许降低薪资');
END;

-- 在应用程序中处理事务错误（伪代码）
/*
try {
    db.execute("BEGIN TRANSACTION");
    db.execute("UPDATE employees SET salary = salary * 1.1");
    db.execute("INSERT INTO salary_log ...");
    db.execute("COMMIT");
} catch (error) {
    db.execute("ROLLBACK");
    console.error("事务失败:", error);
}
*/

-- 使用 ON CONFLICT 处理冲突
INSERT INTO employees (employee_id, first_name, last_name, email)
VALUES (1, '张', '三', 'new.email@company.com')
ON CONFLICT(employee_id) DO UPDATE 
SET email = excluded.email;

WAL 模式（Write-Ahead Logging）

-- 启用 WAL 模式
PRAGMA journal_mode = WAL;

-- 查看当前日志模式
PRAGMA journal_mode;

-- WAL 检查点
PRAGMA wal_checkpoint;  -- 手动检查点
PRAGMA wal_checkpoint(PASSIVE);  -- 被动模式
PRAGMA wal_checkpoint(FULL);     -- 完全模式
PRAGMA wal_checkpoint(RESTART);  -- 重启模式
PRAGMA wal_checkpoint(TRUNCATE); -- 截断模式

-- 设置自动检查点阈值
PRAGMA wal_autocheckpoint = 1000;  -- 1000 页后自动检查点

-- 查看 WAL 状态
PRAGMA wal_checkpoint;
-- 返回值：busy, checkpointed, total

14. 全文搜索

创建 FTS5 表

-- 创建基本的全文搜索表
CREATE VIRTUAL TABLE articles USING fts5(
    title, 
    content, 
    author, 
    tags
);

-- 插入数据
INSERT INTO articles (title, content, author, tags) VALUES
    ('SQLite 入门指南', 'SQLite 是一个轻量级的嵌入式数据库...', '张三', 'SQLite,数据库,教程'),
    ('高级 SQL 技巧', '本文介绍一些高级的 SQL 查询技巧...', '李四', 'SQL,查询,优化'),
    ('数据库性能优化', '如何优化数据库查询性能...', '王五', '性能,优化,索引');

-- 创建带列权重的 FTS 表
CREATE VIRTUAL TABLE weighted_articles USING fts5(
    title, 
    content, 
    author, 
    tags,
    tokenize = 'unicode61'
);

-- 创建带外部内容的 FTS 表
CREATE TABLE article_data (
    id INTEGER PRIMARY KEY,
    title TEXT,
    content TEXT,
    author TEXT,
    created_date DATE
);

CREATE VIRTUAL TABLE article_fts USING fts5(
    title, 
    content,
    content = article_data,
    content_rowid = id
);

全文搜索查询

-- 基本搜索
SELECT * FROM articles WHERE articles MATCH 'SQLite';

-- 搜索特定列
SELECT * FROM articles WHERE title MATCH 'SQLite';
SELECT * FROM articles WHERE content MATCH '优化';

-- 短语搜索
SELECT * FROM articles WHERE articles MATCH '"数据库 性能"';

-- 布尔搜索
SELECT * FROM articles WHERE articles MATCH 'SQLite AND 教程';
SELECT * FROM articles WHERE articles MATCH 'SQLite OR MySQL';
SELECT * FROM articles WHERE articles MATCH 'SQLite NOT MySQL';

-- 前缀搜索
SELECT * FROM articles WHERE articles MATCH 'SQL*';

-- NEAR 搜索
SELECT * FROM articles WHERE articles MATCH 'NEAR(SQLite 数据库, 5)';

-- 使用 rank 函数
SELECT 
    title,
    content,
    rank
FROM articles 
WHERE articles MATCH 'SQLite' 
ORDER BY rank;

-- 高亮显示搜索结果
SELECT 
    highlight(articles, 0, '<b>', '</b>') AS highlighted_title,
    snippet(articles, 1, '<b>', '</b>', '...', 10) AS content_snippet
FROM articles 
WHERE articles MATCH 'SQLite';

FTS5 高级功能

-- 自定义分词器
CREATE VIRTUAL TABLE chinese_articles USING fts5(
    title, 
    content,
    tokenize = 'unicode61 remove_diacritics 2'
);

-- 使用辅助函数
-- bm25() - 相关性评分
SELECT 
    title,
    bm25(articles, 1.2, 0.75) AS score
FROM articles 
WHERE articles MATCH 'SQLite'
ORDER BY score DESC;

-- 获取匹配信息
SELECT 
    title,
    length(matchinfo(articles, 'pcnalx')) AS match_info
FROM articles 
WHERE articles MATCH 'SQLite';

-- 列权重配置
INSERT INTO articles(articles, rank) VALUES('rank', 'bm25(10.0, 5.0, 1.0, 0.5)');

-- 同义词支持（使用触发器）
CREATE TRIGGER articles_ai AFTER INSERT ON articles BEGIN
    INSERT INTO articles(articles) VALUES('insert', NEW.rowid || ' ' || 
        REPLACE(REPLACE(NEW.content, 'database', 'database db'), 'SQL', 'SQL Structured Query Language'));
END;

15. JSON 支持

JSON 数据存储

-- 创建包含 JSON 列的表
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    preferences JSON,
    metadata JSON DEFAULT '{}'
);

-- 插入 JSON 数据
INSERT INTO users (name, email, preferences, metadata) VALUES
    ('张三', 'zhang@example.com', 
     '{"theme": "dark", "language": "zh-CN", "notifications": true}',
     '{"login_count": 5, "last_login": "2024-01-15"}'),
    ('李四', 'li@example.com',
     '{"theme": "light", "language": "en-US", "notifications": false}',
     '{"login_count": 10, "last_login": "2024-01-16"}');

-- 创建订单表（嵌套 JSON）
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    order_date DATE DEFAULT CURRENT_DATE,
    items JSON,
    shipping_address JSON,
    total_amount REAL
);

INSERT INTO orders (customer_id, items, shipping_address, total_amount) VALUES
    (1, 
     '[{"product_id": 101, "name": "iPhone 15", "quantity": 1, "price": 999.99},
       {"product_id": 102, "name": "AirPods", "quantity": 2, "price": 199.99}]',
     '{"street": "123 Main St", "city": "Beijing", "postal_code": "100000"}',
     1399.97);

JSON 查询函数

-- json_extract() - 提取 JSON 值
SELECT 
    name,
    json_extract(preferences, '$.theme') AS theme,
    json_extract(preferences, '$.language') AS language,
    json_extract(metadata, '$.login_count') AS login_count
FROM users;

-- 使用 -> 和 ->> 操作符（SQLite 3.38.0+）
SELECT 
    name,
    preferences -> '$.theme' AS theme_json,
    preferences ->> '$.theme' AS theme_text,
    metadata ->> '$.last_login' AS last_login
FROM users;

-- 查询嵌套 JSON
SELECT 
    order_id,
    json_extract(items, '$[0].name') AS first_item_name,
    json_extract(items, '$[0].price') AS first_item_price,
    json_extract(shipping_address, '$.city') AS city
FROM orders;

-- json_type() - 获取 JSON 值类型
SELECT 
    name,
    json_type(preferences) AS pref_type,
    json_type(preferences, '$.notifications') AS notif_type
FROM users;

-- json_array_length() - 获取数组长度
SELECT 
    order_id,
    json_array_length(items) AS item_count
FROM orders;

JSON 修改函数

-- json_set() - 设置或更新值
UPDATE users 
SET preferences = json_set(preferences, '$.theme', 'dark')
WHERE id = 2;

-- json_insert() - 仅插入新值
UPDATE users 
SET preferences = json_insert(preferences, '$.new_feature', true)
WHERE id = 1;

-- json_replace() - 仅替换现有值
UPDATE users 
SET preferences = json_replace(preferences, '$.language', 'ja-JP')
WHERE id = 1;

-- json_remove() - 删除值
UPDATE users 
SET preferences = json_remove(preferences, '$.notifications')
WHERE id = 2;

-- json_patch() - 应用 JSON 补丁
UPDATE users 
SET metadata = json_patch(metadata, '{"login_count": 15, "vip": true}')
WHERE id = 1;

-- 更新嵌套数组
UPDATE orders 
SET items = json_set(
    items, 
    '$[0].quantity', 
    json_extract(items, '$[0].quantity') + 1
)
WHERE order_id = 1;

JSON 聚合和表函数

-- json_group_array() - 聚合为 JSON 数组
SELECT 
    department_id,
    json_group_array(first_name || ' ' || last_name) AS employee_names
FROM employees
GROUP BY department_id;

-- json_group_object() - 聚合为 JSON 对象
SELECT json_group_object(
    email,
    json_object('name', first_name || ' ' || last_name, 'salary', salary)
) AS employee_map
FROM employees;

-- json_each() - 遍历 JSON 对象
SELECT 
    u.name,
    j.key,
    j.value
FROM users u,
     json_each(u.preferences) j;

-- json_tree() - 遍历整个 JSON 结构
SELECT 
    key,
    value,
    type,
    path
FROM orders,
     json_tree(orders.items)
WHERE type != 'object';

-- 查询包含特定项目的订单
SELECT DISTINCT order_id
FROM orders, json_each(orders.items)
WHERE json_extract(value, '$.product_id') = 101;

JSON 索引和性能

-- 为 JSON 提取值创建索引
CREATE INDEX idx_user_theme ON users(json_extract(preferences, '$.theme'));
CREATE INDEX idx_user_language ON users(json_extract(preferences, '$.language'));

-- 使用生成列优化查询
ALTER TABLE users ADD COLUMN theme TEXT 
    GENERATED ALWAYS AS (json_extract(preferences, '$.theme')) STORED;

CREATE INDEX idx_theme ON users(theme);

-- 查询优化后的列
SELECT * FROM users WHERE theme = 'dark';

-- 创建 JSON 验证触发器
CREATE TRIGGER validate_json_preferences
BEFORE INSERT ON users
FOR EACH ROW
WHEN json_type(NEW.preferences) != 'object'
BEGIN
    SELECT RAISE(ABORT, 'preferences 必须是有效的 JSON 对象');
END;

16. 窗口函数

排名函数

-- ROW_NUMBER() - 行号
SELECT 
    first_name || ' ' || last_name AS full_name,
    salary,
    department_id,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

-- RANK() - 排名（有并列）
SELECT 
    first_name || ' ' || last_name AS full_name,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

-- DENSE_RANK() - 密集排名
SELECT 
    first_name || ' ' || last_name AS full_name,
    salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

-- NTILE() - 分组
SELECT 
    first_name || ' ' || last_name AS full_name,
    salary,
    NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;

-- PERCENT_RANK() - 百分比排名
SELECT 
    first_name || ' ' || last_name AS full_name,
    salary,
    ROUND(PERCENT_RANK() OVER (ORDER BY salary DESC) * 100, 2) AS percentile
FROM employees;

分区窗口函数

-- 部门内排名
SELECT 
    first_name || ' ' || last_name AS full_name,
    salary,
    department_id,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank
FROM employees;

-- 多个窗口函数
SELECT 
    first_name || ' ' || last_name AS full_name,
    salary,
    department_id,
    ROW_NUMBER() OVER w AS dept_row_num,
    RANK() OVER w AS dept_rank,
    DENSE_RANK() OVER w AS dept_dense_rank,
    ROUND(PERCENT_RANK() OVER w * 100, 2) AS dept_percentile
FROM employees
WINDOW w AS (PARTITION BY department_id ORDER BY salary DESC);

聚合窗口函数

-- 累计聚合
SELECT 
    employee_id,
    first_name || ' ' || last_name AS full_name,
    hire_date,
    salary,
    SUM(salary) OVER (ORDER BY hire_date) AS running_total,
    AVG(salary) OVER (ORDER BY hire_date) AS running_avg,
    COUNT(*) OVER (ORDER BY hire_date) AS running_count
FROM employees;

-- 移动窗口
SELECT 
    employee_id,
    hire_date,
    salary,
    -- 前2行到当前行的平均值
    AVG(salary) OVER (
        ORDER BY hire_date 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3,
    -- 前1行到后1行的和
    SUM(salary) OVER (
        ORDER BY hire_date 
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS centered_sum_3
FROM employees;

-- 范围窗口
SELECT 
    employee_id,
    hire_date,
    salary,
    -- 过去30天内的平均薪资
    AVG(salary) OVER (
        ORDER BY hire_date
        RANGE BETWEEN INTERVAL '30' DAY PRECEDING AND CURRENT ROW
    ) AS avg_30_days
FROM employees;

值函数

-- LAG() 和 LEAD()
SELECT 
    employee_id,
    first_name || ' ' || last_name AS full_name,
    salary,
    LAG(salary, 1, 0) OVER (ORDER BY salary) AS prev_salary,
    LEAD(salary, 1, 0) OVER (ORDER BY salary) AS next_salary,
    salary - LAG(salary, 1, 0) OVER (ORDER BY salary) AS salary_gap
FROM employees;

-- FIRST_VALUE() 和 LAST_VALUE()
SELECT 
    first_name || ' ' || last_name AS full_name,
    salary,
    department_id,
    FIRST_VALUE(salary) OVER (
        PARTITION BY department_id 
        ORDER BY salary DESC
    ) AS dept_max_salary,
    LAST_VALUE(salary) OVER (
        PARTITION BY department_id 
        ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS dept_min_salary
FROM employees;

-- NTH_VALUE()
SELECT 
    first_name || ' ' || last_name AS full_name,
    salary,
    department_id,
    NTH_VALUE(salary, 2) OVER (
        PARTITION BY department_id 
        ORDER BY salary DESC
    ) AS second_highest_salary
FROM employees;

实际应用示例

-- 计算同比增长
WITH monthly_sales AS (
    SELECT 
        strftime('%Y-%m', order_date) AS month,
        SUM(total_amount) AS total_sales
    FROM orders
    GROUP BY month
)
SELECT 
    month,
    total_sales,
    LAG(total_sales, 12) OVER (ORDER BY month) AS last_year_sales,
    ROUND(
        (total_sales - LAG(total_sales, 12) OVER (ORDER BY month)) * 100.0 / 
        LAG(total_sales, 12) OVER (ORDER BY month), 
        2
    ) AS yoy_growth_percent
FROM monthly_sales;

-- 查找每个部门薪资前3名
WITH ranked_employees AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
    FROM employees
)
SELECT 
    first_name || ' ' || last_name AS full_name,
    salary,
    department_id,
    rank
FROM ranked_employees
WHERE rank <= 3;

-- 计算移动平均线
SELECT 
    date,
    value,
    AVG(value) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7,
    AVG(value) OVER (ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS ma30
FROM daily_metrics;

17. 备份和恢复

数据库备份方法

-- 方法1：使用 .backup 命令（在 SQLite shell 中）
.backup backup.db
.backup main backup.db  -- 备份主数据库

-- 方法2：使用 VACUUM INTO
VACUUM INTO 'backup.db';

-- 方法3：复制数据库文件（确保没有活动连接）
-- cp company.db company_backup.db

-- 方法4：使用 SQL 导出
.output backup.sql
.dump
.output stdout

-- 导出特定表
.output employees_backup.sql
.dump employees
.output stdout

数据恢复

-- 从备份文件恢复
.restore backup.db

-- 从 SQL 文件恢复
.read backup.sql

-- 使用 ATTACH 恢复特定表
ATTACH DATABASE 'backup.db' AS backup;

-- 恢复单个表
DROP TABLE IF EXISTS employees;
CREATE TABLE employees AS SELECT * FROM backup.employees;

-- 恢复多个表
INSERT INTO employees SELECT * FROM backup.employees;

DETACH DATABASE backup;

增量备份

-- 创建备份日志表
CREATE TABLE backup_log (
    backup_id INTEGER PRIMARY KEY,
    backup_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_modified_rowid INTEGER
);

-- 记录最后修改的 rowid
INSERT INTO backup_log (last_modified_rowid)
SELECT MAX(rowid) FROM employees;

-- 增量备份（仅备份新增/修改的记录）
ATTACH DATABASE 'incremental_backup.db' AS incr;

CREATE TABLE incr.employees_delta AS
SELECT * FROM main.employees
WHERE rowid > (SELECT last_modified_rowid FROM backup_log ORDER BY backup_id DESC LIMIT 1);

DETACH DATABASE incr;

自动备份脚本

-- 创建备份存储过程（使用触发器模拟）
CREATE TABLE backup_schedule (
    id INTEGER PRIMARY KEY,
    next_backup DATETIME,
    interval_hours INTEGER DEFAULT 24
);

-- 备份验证
-- 检查备份文件完整性
PRAGMA integrity_check;

-- 检查外键约束
PRAGMA foreign_key_check;

-- 验证备份数据
ATTACH DATABASE 'backup.db' AS backup;
SELECT 
    (SELECT COUNT(*) FROM main.employees) AS main_count,
    (SELECT COUNT(*) FROM backup.employees) AS backup_count,
    CASE 
        WHEN (SELECT COUNT(*) FROM main.employees) = (SELECT COUNT(*) FROM backup.employees)
        THEN '备份完整'
        ELSE '备份不完整'
    END AS status;
DETACH DATABASE backup;

18. 性能优化

查询优化

-- 使用 EXPLAIN QUERY PLAN 分析查询
EXPLAIN QUERY PLAN
SELECT e.*, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 10000;

-- 优化前：使用 OR
SELECT * FROM employees 
WHERE department_id = 1 OR department_id = 2 OR department_id = 3;

-- 优化后：使用 IN
SELECT * FROM employees 
WHERE department_id IN (1, 2, 3);

-- 优化前：使用 NOT IN 子查询
SELECT * FROM employees 
WHERE employee_id NOT IN (SELECT employee_id FROM inactive_employees);

-- 优化后：使用 LEFT JOIN
SELECT e.* 
FROM employees e
LEFT JOIN inactive_employees ie ON e.employee_id = ie.employee_id
WHERE ie.employee_id IS NULL;

-- 优化前：多次查询
SELECT COUNT(*) FROM employees WHERE department_id = 1;
SELECT AVG(salary) FROM employees WHERE department_id = 1;
SELECT MAX(salary) FROM employees WHERE department_id = 1;

-- 优化后：一次查询
SELECT 
    COUNT(*) AS emp_count,
    AVG(salary) AS avg_salary,
    MAX(salary) AS max_salary
FROM employees 
WHERE department_id = 1;

PRAGMA 优化设置

-- 查询优化器设置
PRAGMA optimize;  -- 运行 ANALYZE
PRAGMA analysis_limit = 1000;  -- 限制分析的行数

-- 内存和缓存设置
PRAGMA cache_size = -64000;  -- 64MB 缓存（负值表示 KB）
PRAGMA temp_store = MEMORY;  -- 临时表存储在内存中
PRAGMA mmap_size = 268435456;  -- 256MB 内存映射

-- 日志和同步设置
PRAGMA journal_mode = WAL;  -- Write-Ahead Logging
PRAGMA synchronous = NORMAL;  -- 平衡性能和安全性
PRAGMA wal_autocheckpoint = 1000;  -- 1000页后自动检查点

-- 查询性能设置
PRAGMA threads = 4;  -- 使用4个线程（如果编译时启用）
PRAGMA query_only = FALSE;  -- 允许写操作

-- 页面设置
PRAGMA page_size = 4096;  -- 4KB 页面大小（创建数据库时设置）
PRAGMA auto_vacuum = INCREMENTAL;  -- 增量自动清理

-- 编译选项
PRAGMA compile_options;  -- 查看编译选项

索引优化策略

-- 分析表统计信息
ANALYZE;
ANALYZE employees;

-- 查看查询计划中的索引使用
.eqp on
SELECT * FROM employees WHERE email = 'test@example.com';
.eqp off

-- 强制使用特定索引
SELECT * FROM employees INDEXED BY idx_employees_email 
WHERE email = 'test@example.com';

-- 禁止使用索引
SELECT * FROM employees NOT INDEXED 
WHERE employee_id = 1;

-- 创建覆盖索引减少表访问
CREATE INDEX idx_emp_dept_salary ON employees(department_id, salary, first_name, last_name);

-- 部分索引优化
CREATE INDEX idx_active_employees ON employees(department_id, salary) 
WHERE is_active = 1;

-- 表达式索引
CREATE INDEX idx_employees_lower_email ON employees(lower(email));
SELECT * FROM employees WHERE lower(email) = 'test@example.com';

批量操作优化

-- 批量插入优化
PRAGMA synchronous = OFF;
PRAGMA journal_mode = MEMORY;
BEGIN TRANSACTION;

-- 大量 INSERT 操作
INSERT INTO large_table VALUES (...);
-- ... 更多插入

COMMIT;
PRAGMA synchronous = NORMAL;
PRAGMA journal_mode = WAL;

-- 使用预处理语句（在应用程序中）
-- 创建一次，多次执行

-- 批量更新优化
CREATE TEMP TABLE updates (id INTEGER PRIMARY KEY, new_value TEXT);
INSERT INTO updates VALUES (1, 'value1'), (2, 'value2');

UPDATE main_table 
SET value = updates.new_value
FROM updates
WHERE main_table.id = updates.id;

DROP TABLE updates;

-- 批量删除优化
DELETE FROM large_table
WHERE id IN (
    SELECT id FROM large_table 
    WHERE condition 
    ORDER BY id 
    LIMIT 10000
);

查询结果缓存

-- 使用临时表缓存复杂查询结果
CREATE TEMP TABLE cached_summary AS
SELECT 
    department_id,
    COUNT(*) AS emp_count,
    AVG(salary) AS avg_salary,
    MAX(salary) AS max_salary
FROM employees
GROUP BY department_id;

-- 多次使用缓存结果
SELECT * FROM cached_summary WHERE avg_salary > 10000;
SELECT * FROM cached_summary ORDER BY emp_count DESC;

-- 使用视图缓存常用查询
CREATE VIEW employee_summary AS
SELECT 
    e.*,
    d.department_name,
    m.first_name || ' ' || m.last_name AS manager_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
LEFT JOIN employees m ON d.manager_id = m.employee_id;

-- 定期更新统计信息
CREATE TABLE statistics_cache (
    stat_name TEXT PRIMARY KEY,
    stat_value TEXT,
    last_updated DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER update_statistics
AFTER INSERT ON employees
BEGIN
    DELETE FROM statistics_cache WHERE stat_name = 'employee_count';
    INSERT INTO statistics_cache (stat_name, stat_value)
    VALUES ('employee_count', (SELECT COUNT(*) FROM employees));
END;

监控和诊断

-- 数据库统计信息
SELECT * FROM sqlite_stat1;  -- 表和索引统计
SELECT * FROM sqlite_stat4;  -- 详细统计（如果可用）

-- 查看数据库大小
SELECT 
    page_count * page_size / 1024.0 / 1024.0 AS size_mb,
    page_count,
    page_size
FROM pragma_page_count(), pragma_page_size();

-- 查看表大小
SELECT 
    name,
    SUM(pgsize) / 1024.0 / 1024.0 AS size_mb
FROM (
    SELECT name, pageno * page_size AS pgsize
    FROM dbstat
    JOIN pragma_page_size()
)
GROUP BY name
ORDER BY size_mb DESC;

-- 检查碎片
SELECT 
    name,
    100.0 * (1.0 - CAST(used AS REAL) / CAST(pgcnt AS REAL)) AS fragmentation_percent
FROM dbstat
WHERE aggregate = TRUE
ORDER BY fragmentation_percent DESC;

-- 慢查询日志（需要在应用层实现）
CREATE TABLE query_log (
    id INTEGER PRIMARY KEY,
    query TEXT,
    execution_time_ms INTEGER,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 数据库健康检查
PRAGMA integrity_check;
PRAGMA foreign_key_check;
PRAGMA quick_check;

19. 常用函数

字符串函数

-- 字符串连接
SELECT 
    first_name || ' ' || last_name AS full_name,
    printf('%s %s', first_name, last_name) AS formatted_name
FROM employees;

-- 大小写转换
SELECT 
    upper(first_name) AS upper_name,
    lower(email) AS lower_email
FROM employees;

-- 字符串长度
SELECT 
    length(first_name) AS name_length,
    length(email) - length(replace(email, '@', '')) AS at_count
FROM employees;

-- 子字符串
SELECT 
    substr(email, 1, instr(email, '@') - 1) AS username,
    substr(email, instr(email, '@') + 1) AS domain
FROM employees;

-- 字符串替换
SELECT 
    replace(phone, '-', '') AS cleaned_phone,
    replace(replace(phone, '-', ''), ' ', '') AS fully_cleaned_phone
FROM employees;

-- 去除空格
SELECT 
    trim(first_name) AS trimmed_name,
    ltrim(first_name) AS left_trimmed,
    rtrim(first_name) AS right_trimmed,
    trim(first_name, '张') AS custom_trim
FROM employees;

-- 字符串查找
SELECT 
    instr(email, '@') AS at_position,
    CASE 
        WHEN instr(email, '.com') > 0 THEN 'COM域名'
        WHEN instr(email, '.cn') > 0 THEN 'CN域名'
        ELSE '其他域名'
    END AS domain_type
FROM employees;

数学函数

-- 基本数学函数
SELECT 
    abs(-10) AS absolute_value,
    round(3.14159, 2) AS rounded,
    ceil(3.14) AS ceiling,
    floor(3.14) AS floor_value,
    mod(10, 3) AS modulo,
    power(2, 10) AS power_result;

-- 聚合数学函数
SELECT 
    avg(salary) AS average,
    sum(salary) AS total,
    min(salary) AS minimum,
    max(salary) AS maximum,
    count(*) AS count,
    count(DISTINCT department_id) AS unique_departments
FROM employees;

-- 随机数
SELECT 
    random() AS random_number,
    abs(random() % 100) AS random_0_to_99,
    abs(random() % 100) + 1 AS random_1_to_100;

-- 随机选择记录
SELECT * FROM employees ORDER BY random() LIMIT 5;

-- 数学计算
SELECT 
    salary,
    salary * 0.1 AS tax,
    salary * 0.9 AS after_tax,
    round(salary * 12 / 365, 2) AS daily_salary
FROM employees;

日期时间函数

-- 当前日期时间
SELECT 
    date('now') AS current_date,
    time('now') AS current_time,
    datetime('now') AS current_datetime,
    datetime('now', 'localtime') AS local_datetime;

-- 日期格式化
SELECT 
    strftime('%Y-%m-%d', 'now') AS formatted_date,
    strftime('%H:%M:%S', 'now') AS formatted_time,
    strftime('%Y年%m月%d日', 'now') AS chinese_date,
    strftime('%w', 'now') AS day_of_week,  -- 0=周日
    strftime('%W', 'now') AS week_of_year;

-- 日期计算
SELECT 
    date('now', '+1 day') AS tomorrow,
    date('now', '-1 day') AS yesterday,
    date('now', '+1 month') AS next_month,
    date('now', 'start of month') AS month_start,
    date('now', 'start of month', '+1 month', '-1 day') AS month_end;

-- 日期差异
SELECT 
    employee_id,
    hire_date,
    julianday('now') - julianday(hire_date) AS days_employed,
    CAST((julianday('now') - julianday(hire_date)) / 365.25 AS INTEGER) AS years_employed
FROM employees;

-- 工作日计算（排除周末）
WITH RECURSIVE dates(date) AS (
    SELECT date('2024-01-01')
    UNION ALL
    SELECT date(date, '+1 day')
    FROM dates
    WHERE date < date('2024-01-31')
)
SELECT COUNT(*) AS workdays
FROM dates
WHERE strftime('%w', date) NOT IN ('0', '6');  -- 不是周六周日

类型转换函数

-- CAST 函数
SELECT 
    CAST('123' AS INTEGER) AS int_value,
    CAST(123 AS TEXT) AS text_value,
    CAST('123.45' AS REAL) AS real_value,
    CAST(1 AS BOOLEAN) AS bool_value;

-- 类型检查
SELECT 
    typeof(123) AS type_int,
    typeof('123') AS type_text,
    typeof(123.45) AS type_real,
    typeof(NULL) AS type_null,
    typeof(x'0123') AS type_blob;

-- 隐式类型转换
SELECT 
    '123' + 456 AS implicit_sum,  -- 579
    123 || 456 AS concat_numbers,  -- '123456'
    '123' > 45 AS text_compare;  -- 文本比较

-- 数值格式化
SELECT 
    printf('%.2f', 123.456) AS formatted_decimal,
    printf('%04d', 42) AS padded_integer,
    printf('%e', 123456.789) AS scientific,
    hex(255) AS hexadecimal;

条件和逻辑函数

-- CASE 表达式
SELECT 
    first_name,
    salary,
    CASE 
        WHEN salary < 5000 THEN '低'
        WHEN salary < 10000 THEN '中'
        ELSE '高'
    END AS salary_level
FROM employees;

-- COALESCE（返回第一个非NULL值）
SELECT 
    first_name,
    COALESCE(phone, email, '无联系方式') AS contact
FROM employees;

-- NULLIF（相等时返回NULL）
SELECT 
    salary,
    bonus,
    NULLIF(bonus, 0) AS non_zero_bonus,
    salary / NULLIF(bonus, 0) AS salary_bonus_ratio
FROM employee_compensation;

-- IIF（内联IF，SQLite 3.32.0+）
SELECT 
    first_name,
    salary,
    IIF(salary > 10000, '高薪', '普通') AS salary_category
FROM employees;

-- MIN/MAX 用于非数值比较
SELECT 
    MIN('apple', 'banana', 'cherry') AS first_alphabetically,
    MAX('apple', 'banana', 'cherry') AS last_alphabetically;

系统函数

-- SQLite 版本和编译信息
SELECT 
    sqlite_version() AS version,
    sqlite_source_id() AS source_id;

-- 最后插入的 ROWID
INSERT INTO employees (first_name, last_name) VALUES ('测试', '用户');
SELECT last_insert_rowid() AS last_id;

-- 受影响的行数
UPDATE employees SET salary = salary * 1.05 WHERE department_id = 1;
SELECT changes() AS rows_affected;

-- 总变更数
SELECT total_changes() AS total_changes_in_connection;

-- 数据库列表
SELECT * FROM pragma_database_list();

-- 表信息
SELECT * FROM pragma_table_info('employees');

-- 外键信息
SELECT * FROM pragma_foreign_key_list('employees');

20. 实用技巧和最佳实践

数据库设计最佳实践

-- 1. 始终使用主键
CREATE TABLE best_practices (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    -- 其他列
);

-- 2. 适当使用索引
-- 为经常查询的列创建索引
CREATE INDEX idx_email ON users(email);
-- 为外键创建索引
CREATE INDEX idx_user_id ON orders(user_id);

-- 3. 规范化设计
-- 避免数据冗余
-- 不好的设计
CREATE TABLE orders_bad (
    order_id INTEGER PRIMARY KEY,
    customer_name TEXT,
    customer_email TEXT,
    customer_phone TEXT,
    product_name TEXT,
    product_price REAL
);

-- 好的设计
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT,
    phone TEXT
);

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    name TEXT,
    price REAL
);

CREATE TABLE orders_good (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id),
    product_id INTEGER REFERENCES products(product_id),
    quantity INTEGER,
    order_date DATE
);

-- 4. 使用合适的数据类型
CREATE TABLE data_types_example (
    -- 使用 INTEGER 作为主键
    id INTEGER PRIMARY KEY,
    -- 使用 TEXT 存储字符串
    name TEXT NOT NULL,
    -- 使用 REAL 存储小数
    price REAL CHECK (price >= 0),
    -- 使用 INTEGER 存储布尔值
    is_active INTEGER DEFAULT 1 CHECK (is_active IN (0, 1)),
    -- 使用 TEXT 存储日期（ISO 8601格式）
    created_date TEXT DEFAULT CURRENT_TIMESTAMP
);

查询优化技巧

-- 1. 使用 EXPLAIN QUERY PLAN
EXPLAIN QUERY PLAN
SELECT * FROM large_table WHERE column1 = 'value';

-- 2. 避免 SELECT *
-- 不好
SELECT * FROM employees;

-- 好
SELECT employee_id, first_name, last_name, salary 
FROM employees;

-- 3. 使用 LIMIT 限制结果集
SELECT * FROM large_table 
ORDER BY created_date DESC 
LIMIT 100;

-- 4. 优化 JOIN 顺序（小表在前）
-- 假设 departments 表比 employees 表小
SELECT e.*, d.department_name
FROM departments d
JOIN employees e ON d.department_id = e.department_id;

-- 5. 使用子查询代替 JOIN（当只需要存在性检查时）
-- 使用 EXISTS
SELECT * FROM employees e
WHERE EXISTS (
    SELECT 1 FROM departments d 
    WHERE d.department_id = e.department_id 
    AND d.location = 'Beijing'
);

-- 6. 批量操作使用事务
BEGIN TRANSACTION;
-- 多个操作
COMMIT;

数据完整性保护

-- 1. 启用外键约束
PRAGMA foreign_keys = ON;

-- 2. 使用触发器维护数据一致性
CREATE TRIGGER update_inventory_on_order
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
    UPDATE products 
    SET stock = stock - NEW.quantity
    WHERE product_id = NEW.product_id;
    
    -- 检查库存
    SELECT CASE
        WHEN (SELECT stock FROM products WHERE product_id = NEW.product_id) < 0
        THEN RAISE(ABORT, '库存不足')
    END;
END;

-- 3. 使用 CHECK 约束
CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL CHECK (price > 0),
    stock INTEGER CHECK (stock >= 0),
    created_date TEXT CHECK (created_date IS datetime(created_date))
);

-- 4. 定期检查数据完整性
PRAGMA integrity_check;
PRAGMA foreign_key_check;

安全性建议

-- 1. 使用参数化查询（在应用程序中）
-- 不要这样做：
-- query = "SELECT * FROM users WHERE name = '" + userName + "'";

-- 应该这样做：
-- query = "SELECT * FROM users WHERE name = ?";
-- 然后绑定参数

-- 2. 限制数据库文件权限
-- chmod 600 database.db  # 仅所有者可读写

-- 3. 加密敏感数据
-- 使用 SQLCipher 或在应用层加密

-- 4. 定期备份
-- 创建备份计划脚本

-- 5. 审计日志
CREATE TABLE audit_log (
    log_id INTEGER PRIMARY KEY,
    table_name TEXT,
    operation TEXT,
    user TEXT,
    timestamp TEXT DEFAULT CURRENT_TIMESTAMP,
    old_value TEXT,
    new_value TEXT
);

-- 创建审计触发器
CREATE TRIGGER audit_employees_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (table_name, operation, user, old_value, new_value)
    VALUES (
        'employees',
        'UPDATE',
        'current_user',
        json_object('salary', OLD.salary),
        json_object('salary', NEW.salary)
    );
END;

开发和调试技巧

-- 1. 使用 .mode 和 .headers 改善输出
.mode column
.headers on
.width 20 30 15

-- 2. 保存查询结果
.output results.csv
.mode csv
SELECT * FROM employees;
.output stdout

-- 3. 使用 .timer 测量查询时间
.timer on
SELECT COUNT(*) FROM large_table;
.timer off
-- 4. 创建测试数据
-- 创建数字序列
WITH RECURSIVE series(value) AS (
    SELECT 1
    UNION ALL
    SELECT value + 1 FROM series
    WHERE value < 1000
)
INSERT INTO test_table (id, random_value)
SELECT 
    value,
    abs(random() % 1000)
FROM series;

-- 生成随机字符串
WITH RECURSIVE random_names(id, name) AS (
    SELECT 
        1,
        substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ', abs(random() % 26) + 1, 1) ||
        substr('abcdefghijklmnopqrstuvwxyz', abs(random() % 26) + 1, 1) ||
        substr('abcdefghijklmnopqrstuvwxyz', abs(random() % 26) + 1, 1)
    UNION ALL
    SELECT 
        id + 1,
        substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ', abs(random() % 26) + 1, 1) ||
        substr('abcdefghijklmnopqrstuvwxyz', abs(random() % 26) + 1, 1) ||
        substr('abcdefghijklmnopqrstuvwxyz', abs(random() % 26) + 1, 1)
    FROM random_names
    WHERE id < 100
)
INSERT INTO users_test (username)
SELECT name FROM random_names;

-- 5. 使用 WITH 子句进行复杂调试
WITH debug_info AS (
    SELECT 
        'Total Employees' AS metric,
        COUNT(*) AS value
    FROM employees
    UNION ALL
    SELECT 
        'Average Salary',
        AVG(salary)
    FROM employees
    UNION ALL
    SELECT 
        'Departments',
        COUNT(DISTINCT department_id)
    FROM employees
)
SELECT * FROM debug_info;

-- 6. 创建调试视图
CREATE VIEW debug_employee_stats AS
SELECT 
    e.employee_id,
    e.first_name || ' ' || e.last_name AS full_name,
    e.salary,
    d.department_name,
    e.salary - (SELECT AVG(salary) FROM employees) AS salary_diff_from_avg,
    RANK() OVER (ORDER BY e.salary DESC) AS salary_rank
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

-- 7. 使用 PRAGMA 进行诊断
PRAGMA table_info(employees);
PRAGMA index_list(employees);
PRAGMA foreign_key_list(employees);
PRAGMA database_list;
PRAGMA compile_options;

性能监控和分析

-- 创建性能监控表
CREATE TABLE query_performance (
    id INTEGER PRIMARY KEY,
    query_text TEXT,
    execution_time_ms INTEGER,
    rows_affected INTEGER,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 创建慢查询视图
CREATE VIEW slow_queries AS
SELECT 
    query_text,
    execution_time_ms,
    rows_affected,
    timestamp
FROM query_performance
WHERE execution_time_ms > 100
ORDER BY execution_time_ms DESC;

-- 分析表大小和增长
CREATE VIEW table_sizes AS
SELECT 
    name AS table_name,
    SUM(pgsize) AS size_bytes,
    SUM(pgsize) / 1024.0 AS size_kb,
    SUM(pgsize) / 1024.0 / 1024.0 AS size_mb,
    COUNT(*) AS page_count
FROM (
    SELECT name, pageno, pageno * (SELECT page_size FROM pragma_page_size()) AS pgsize
    FROM dbstat
    WHERE pageno > 0
)
GROUP BY name
ORDER BY size_bytes DESC;

-- 索引使用率分析
CREATE VIEW index_usage AS
SELECT 
    m.name AS table_name,
    i.name AS index_name,
    i.unique,
    CASE 
        WHEN i.origin = 'pk' THEN 'PRIMARY KEY'
        WHEN i.origin = 'u' THEN 'UNIQUE'
        ELSE 'NORMAL'
    END AS index_type
FROM sqlite_master m
JOIN pragma_index_list(m.name) i
WHERE m.type = 'table'
ORDER BY m.name, i.name;

21. 常见问题和解决方案

数据库锁定问题

-- 问题：database is locked
-- 解决方案：

-- 1. 检查活动连接
PRAGMA busy_timeout = 5000;  -- 设置5秒超时

-- 2. 使用 WAL 模式减少锁定
PRAGMA journal_mode = WAL;

-- 3. 优化长事务
-- 将长事务拆分为多个短事务
BEGIN IMMEDIATE;
-- 执行部分操作
COMMIT;

BEGIN IMMEDIATE;
-- 执行下一部分操作
COMMIT;

-- 4. 处理死锁
-- 在应用程序中实现重试机制
-- Python 示例：
/*
import sqlite3
import time

def execute_with_retry(conn, query, max_retries=3):
    for i in range(max_retries):
        try:
            cursor = conn.execute(query)
            return cursor
        except sqlite3.OperationalError as e:
            if "locked" in str(e) and i < max_retries - 1:
                time.sleep(0.1 * (i + 1))  # 指数退避
                continue
            raise
*/

性能问题诊断

-- 问题：查询速度慢
-- 诊断步骤：

-- 1. 分析查询计划
EXPLAIN QUERY PLAN
SELECT e.*, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 10000;

-- 2. 检查索引
-- 查看现有索引
SELECT sql FROM sqlite_master 
WHERE type = 'index' AND tbl_name = 'employees';

-- 创建缺失的索引
CREATE INDEX idx_employees_salary ON employees(salary);

-- 3. 更新统计信息
ANALYZE;

-- 4. 检查表碎片
SELECT 
    name,
    100.0 * (pgsize - used) / pgsize AS fragmentation_percent
FROM dbstat
WHERE aggregate = TRUE
ORDER BY fragmentation_percent DESC;

-- 5. 优化查询
-- 使用覆盖索引
CREATE INDEX idx_covering ON employees(department_id, salary, first_name, last_name);

-- 6. 批量处理
-- 将多个小查询合并为一个大查询

数据完整性问题

-- 问题：外键约束失败
-- 解决方案：

-- 1. 检查外键约束
PRAGMA foreign_key_check;

-- 2. 找出违反约束的记录
SELECT e.*
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
WHERE d.department_id IS NULL AND e.department_id IS NOT NULL;

-- 3. 修复数据
-- 选项1：删除无效记录
DELETE FROM employees 
WHERE department_id NOT IN (SELECT department_id FROM departments);

-- 选项2：更新为有效值
UPDATE employees 
SET department_id = NULL
WHERE department_id NOT IN (SELECT department_id FROM departments);

-- 选项3：添加缺失的父记录
INSERT INTO departments (department_id, department_name)
SELECT DISTINCT e.department_id, 'Unknown Department ' || e.department_id
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
WHERE d.department_id IS NULL AND e.department_id IS NOT NULL;

内存使用问题

-- 问题：内存使用过高
-- 解决方案：

-- 1. 调整缓存大小
PRAGMA cache_size = -2000;  -- 2MB 缓存

-- 2. 限制临时存储
PRAGMA temp_store = FILE;  -- 使用磁盘而非内存

-- 3. 使用流式查询（在应用程序中）
-- 避免一次加载所有结果

-- 4. 释放未使用的内存
PRAGMA shrink_memory;

-- 5. 限制内存映射大小
PRAGMA mmap_size = 0;  -- 禁用内存映射

数据迁移问题

-- 问题：大数据量迁移
-- 解决方案：

-- 1. 使用附加数据库
ATTACH DATABASE 'source.db' AS source;
ATTACH DATABASE 'target.db' AS target;

-- 2. 批量迁移
-- 创建目标表
CREATE TABLE target.employees AS 
SELECT * FROM source.employees WHERE 1=0;

-- 分批迁移数据
INSERT INTO target.employees
SELECT * FROM source.employees
ORDER BY employee_id
LIMIT 10000 OFFSET 0;

-- 3. 使用事务优化
BEGIN;
PRAGMA target.synchronous = OFF;
PRAGMA target.journal_mode = MEMORY;

-- 执行迁移
INSERT INTO target.employees SELECT * FROM source.employees;

COMMIT;
PRAGMA target.synchronous = NORMAL;
PRAGMA target.journal_mode = WAL;

-- 4. 验证数据完整性
SELECT 
    (SELECT COUNT(*) FROM source.employees) AS source_count,
    (SELECT COUNT(*) FROM target.employees) AS target_count;