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_salaryFROM employeesGROUP 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 CASCADEON 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_idFROM departmentsWHERE 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_salaryFROM employeesGROUP 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_countFROM employeesGROUP BY department_id
),
dept_projects AS (SELECT department_id, COUNT(*) AS proj_countFROM projectsGROUP 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 pathFROM employees eWHERE 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_nameFROM employees eJOIN 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.05WHEN department_id = 2 THEN salary * 1.10WHEN department_id = 3 THEN salary * 1.08ELSE 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_salaryFROM employeesWHERE department_id = 2
) s
WHERE employees.employee_id = s.employee_id;-- 使用 CTE 更新
WITH salary_updates AS (SELECT employee_id,salary * 1.15 AS new_salaryFROM employeesWHERE employee_id IN (SELECT employee_idFROM employeesORDER BY salary DESCLIMIT 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 employeesGROUP BY email
);

高級刪除操作

-- 使用 CTE 刪除
WITH inactive_employees AS (SELECT employee_idFROM employeesWHERE is_active = 0AND 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_idFROM log_tableWHERE timestamp < date('now', '-30 days')ORDER BY timestampLIMIT 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 ALLSELECT date(date, '+1 day')FROM datesWHERE 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_rankFROM employeesWHERE 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
BEGINSELECT RAISE(ABORT, '薪資必須大于0');
END;-- AFTER INSERT 觸發器
CREATE TRIGGER log_new_employee
AFTER INSERT ON employees
FOR EACH ROW
BEGININSERT 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
BEGINUPDATE 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
BEGININSERT 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)
BEGINSELECT RAISE(ABORT, '不能刪除部門經理');
END;-- AFTER DELETE 觸發器
CREATE TRIGGER archive_deleted_employee
AFTER DELETE ON employees
FOR EACH ROW
BEGININSERT 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 CASEWHEN NEW.email NOT LIKE '%_@_%.__%' THENRAISE(ABORT, '郵箱格式無效')END;-- 驗證部門存在SELECT CASEWHEN NOT EXISTS (SELECT 1 FROM departments WHERE department_id = NEW.department_id) THENRAISE(ABORT, '部門不存在')END;-- 驗證薪資范圍SELECT CASEWHEN NEW.salary < 3000 OR NEW.salary > 100000 THENRAISE(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_TIMESTAMPWHERE 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
BEGINUPDATE employees SET salary = NEW.salaryWHERE 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
BEGINSELECT 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 BEGININSERT 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'
BEGINSELECT 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_dateRANGE 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 DESCROWS 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_salesFROM ordersGROUP 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 rankFROM 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
BEGINDELETE 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 pgsizeFROM dbstatJOIN 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 ALLSELECT date(date, '+1 day')FROM datesWHERE 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,  -- 579123 || 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
BEGINUPDATE products SET stock = stock - NEW.quantityWHERE product_id = NEW.product_id;-- 檢查庫存SELECT CASEWHEN (SELECT stock FROM products WHERE product_id = NEW.product_id) < 0THEN 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
BEGININSERT 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 1UNION ALLSELECT value + 1 FROM seriesWHERE 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 ALLSELECT 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_namesWHERE 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 valueFROM employeesUNION ALLSELECT 'Average Salary',AVG(salary)FROM employeesUNION ALLSELECT '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 pgsizeFROM dbstatWHERE 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 timedef execute_with_retry(conn, query, max_retries=3):for i in range(max_retries):try:cursor = conn.execute(query)return cursorexcept sqlite3.OperationalError as e:if "locked" in str(e) and i < max_retries - 1:time.sleep(0.1 * (i + 1))  # 指數退避continueraise
*/

性能問題診斷

-- 問題：查詢速度慢
-- 診斷步驟：-- 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;

編碼和字符集問題

-- 問題：中文亂碼
-- 解決方案：-- 1. 確保使用 UTF-8 編碼
PRAGMA encoding = 'UTF-8';-- 2. 檢查當前編碼
PRAGMA encoding;-- 3. 轉換編碼（在應用程序中處理）
-- Python 示例：
/*
text = text.encode('utf-8').decode('utf-8')
*/-- 4. 使用 BLOB 存儲二進制數據
CREATE TABLE files (id INTEGER PRIMARY KEY,filename TEXT,content BLOB
);-- 5. 正確處理特殊字符
-- 使用參數化查詢避免編碼問題

并發訪問問題

-- 問題：多用戶并發訪問沖突
-- 解決方案：-- 1. 使用 WAL 模式
PRAGMA journal_mode = WAL;
PRAGMA wal_autocheckpoint = 1000;-- 2. 實現樂觀鎖
ALTER TABLE employees ADD COLUMN version INTEGER DEFAULT 1;-- 更新時檢查版本
UPDATE employees 
SET salary = 12000,version = version + 1
WHERE employee_id = 1 
AND version = 5;  -- 檢查版本是否匹配-- 3. 使用行級時間戳
ALTER TABLE employees 
ADD COLUMN last_modified DATETIME DEFAULT CURRENT_TIMESTAMP;CREATE TRIGGER update_last_modified
AFTER UPDATE ON employees
FOR EACH ROW
BEGINUPDATE employees SET last_modified = CURRENT_TIMESTAMPWHERE employee_id = NEW.employee_id;
END;-- 4. 連接池配置（應用層）
-- 限制最大連接數
-- 設置連接超時
-- 實現連接重試機制

22. 版本兼容性說明

SQLite 版本特性對照

-- SQLite 3.35.0+ (2021-03)
-- 新增功能：
-- 1. ALTER TABLE DROP COLUMN
ALTER TABLE employees DROP COLUMN unused_column;-- 2. 內置數學函數
SELECT sin(0.5), cos(0.5), tan(0.5), sqrt(16), log(10), exp(1);-- SQLite 3.33.0+ (2020-08)
-- 新增功能：UPDATE FROM
UPDATE employees
SET salary = ns.new_salary
FROM (SELECT employee_id, salary * 1.1 AS new_salary FROM employees) ns
WHERE employees.employee_id = ns.employee_id;-- SQLite 3.32.0+ (2020-05)
-- 新增功能：IIF() 函數
SELECT IIF(salary > 10000, 'High', 'Normal') AS category FROM employees;-- SQLite 3.31.0+ (2020-01)
-- 新增功能：生成列 (Generated Columns)
CREATE TABLE products (id INTEGER PRIMARY KEY,price REAL,tax_rate REAL DEFAULT 0.1,total_price REAL GENERATED ALWAYS AS (price * (1 + tax_rate)) STORED
);-- SQLite 3.30.0+ (2019-10)
-- 新增功能：NULLS FIRST/LAST
SELECT * FROM employees 
ORDER BY department_id NULLS LAST, salary DESC NULLS FIRST;-- SQLite 3.28.0+ (2019-04)
-- 新增功能：窗口函數改進
SELECT employee_id,salary,NTH_VALUE(salary, 2) OVER (ORDER BY salary DESC) AS second_highest
FROM employees;-- SQLite 3.25.0+ (2018-09)
-- 新增功能：ALTER TABLE RENAME COLUMN
ALTER TABLE employees RENAME COLUMN phone TO phone_number;-- SQLite 3.24.0+ (2018-06)
-- 新增功能：UPSERT
INSERT INTO employees (employee_id, email, salary)
VALUES (1, 'new@email.com', 15000)
ON CONFLICT(employee_id) DO UPDATE 
SET email = excluded.email,salary = excluded.salary;

功能兼容性檢查

-- 檢查 SQLite 版本
SELECT sqlite_version();-- 檢查編譯選項
SELECT * FROM pragma_compile_options();-- 檢查特定功能是否可用
-- 檢查 JSON 支持
SELECT json('{"test": true}');-- 檢查全文搜索支持
CREATE VIRTUAL TABLE test_fts USING fts5(content);
DROP TABLE test_fts;-- 檢查 RTREE 支持
CREATE VIRTUAL TABLE test_rtree USING rtree(id, minX, maxX, minY, maxY);
DROP TABLE test_rtree;-- 創建版本兼容性視圖
CREATE VIEW version_info AS
SELECT sqlite_version() AS version,(SELECT COUNT(*) FROM pragma_compile_options() WHERE compile_options LIKE '%ENABLE_JSON%') AS has_json,(SELECT COUNT(*) FROM pragma_compile_options() WHERE compile_options LIKE '%ENABLE_FTS5%') AS has_fts5,(SELECT COUNT(*) FROM pragma_compile_options() WHERE compile_options LIKE '%ENABLE_RTREE%') AS has_rtree;

向后兼容性策略

-- 1. 條件創建（檢查版本）
-- 創建兼容性函數
CREATE TABLE IF NOT EXISTS db_version (version TEXT PRIMARY KEY,applied_date DATETIME DEFAULT CURRENT_TIMESTAMP
);-- 2. 特性檢測和降級方案
-- 例如：如果不支持 DROP COLUMN，使用重建表方式
-- 創建版本檢查函數（應用層實現）-- 3. 使用視圖抽象版本差異
CREATE VIEW employee_info AS
SELECT employee_id,first_name || ' ' || last_name AS full_name,email,salary,-- 使用 CASE 代替 IIF（兼容舊版本）CASE WHEN salary > 10000 THEN 'High'ELSE 'Normal'END AS salary_category
FROM employees;-- 4. 數據庫遷移腳本模板
-- migration_001.sql
BEGIN TRANSACTION;-- 檢查是否已應用
INSERT OR IGNORE INTO db_version (version) VALUES ('001');-- 如果是新遷移，執行變更
-- ... 數據庫變更語句 ...COMMIT;-- 5. 功能降級示例
-- 新版本使用 UPDATE FROM
-- UPDATE employees SET ... FROM ...;-- 舊版本兼容寫法
UPDATE employees 
SET salary = (SELECT salary * 1.1 FROM employees e2 WHERE e2.employee_id = employees.employee_id
)
WHERE EXISTS (SELECT 1 FROM employees e3 WHERE e3.employee_id = employees.employee_id
);

升級建議

-- 1. 升級前備份
VACUUM INTO 'backup_before_upgrade.db';-- 2. 檢查數據完整性
PRAGMA integrity_check;
PRAGMA foreign_key_check;-- 3. 測試兼容性
-- 在測試環境運行所有查詢和操作-- 4. 漸進式升級
-- 先升級開發環境
-- 再升級測試環境
-- 最后升級生產環境-- 5. 監控升級后的性能
CREATE TABLE upgrade_metrics (metric_name TEXT PRIMARY KEY,before_value REAL,after_value REAL,change_percent REAL GENERATED ALWAYS AS ((after_value - before_value) * 100.0 / before_value) STORED,measured_at DATETIME DEFAULT CURRENT_TIMESTAMP
);-- 記錄升級前后的性能指標
INSERT INTO upgrade_metrics (metric_name, before_value, after_value)
VALUES ('avg_query_time_ms', 45.2, 38.7),('db_size_mb', 156.3, 148.9),('index_count', 12, 15);