Qt 中使用 SQLite 數據庫

一、SQLite 數據庫介紹

SQLite 是一個輕量級的嵌入式關系型數據庫管理系統，它以庫的形式提供，不需要單獨的服務器進程，直接訪問存儲在普通磁盤文件中的數據庫。

主要特性

無服務器架構：SQLite 不需要單獨的服務器進程
零配置：無需安裝或管理
跨平臺：支持所有主流操作系統
事務性：完全兼容 ACID (原子性、一致性、隔離性、持久性)
自包含：單個磁盤文件包含整個數據庫
小型輕量：完整的庫只有幾百KB大小
公共領域：完全開源，無版權限制

數據類型

SQLite 使用動態類型系統，主要有以下存儲類：

類型	描述
`NULL`	表示空值
`INTEGER`	帶符號整數，根據值大小以1、2、3、4、6或8字節存儲
`REAL`	浮點數，8字節IEEE浮點數
`TEXT`	文本字符串，使用數據庫編碼（UTF-8、UTF-16BE或UTF-16LE）存儲
`BLOB`	二進制大對象，完全按照輸入存儲

類型親和性(Type Affinity)

SQLite 支持"類型親和性"概念，即列優先以某種特定類型存儲數據。以下是五種類型親和性：

TEXT

聲明包含：TEXT,?VARCHAR,?CHAR,?CLOB?等
將所有數據轉換為文本格式存儲

NUMERIC

聲明包含：NUMERIC,?DECIMAL,?BOOLEAN,?DATE,?DATETIME?等
嘗試轉換為INTEGER或REAL，不行則存為TEXT

INTEGER

聲明包含：INTEGER,?INT,?TINYINT,?SMALLINT,?MEDIUMINT,?BIGINT?等
行為類似NUMERIC，但無小數部分

REAL

聲明包含：REAL,?DOUBLE,?FLOAT?等
轉換為REAL格式存儲

BLOB

聲明包含：BLOB, 或未指定類型
不轉換，直接存儲輸入數據

常見數據類型映射

雖然SQLite內部只有5種存儲類別，但為了兼容其他SQL數據庫，支持以下類型聲明：

聲明的類型	實際親和性	存儲形式示例
INTEGER, INT	INTEGER	1, -234, 123456789012345
REAL, FLOAT	REAL	3.14, -123.456, 1.23e+10
TEXT, VARCHAR	TEXT	'Hello', '2023-01-01'
BLOB	BLOB	x'53514C697465' (十六進制)
DATE, DATETIME	NUMERIC	'2023-05-20', '2023-05-20 12:34'
BOOLEAN	NUMERIC	0 (false), 1 (true)

常用屬性

SQLite 提供了一系列屬性和編譯時選項（Pragma）來控制和查詢數據庫的行為。以下是常用的 SQLite 屬性：

數據庫配置屬性（PRAGMA）

數據庫設置

journal_mode
- 設置事務日志模式
- 可選值：DELETE（默認）, TRUNCATE, PERSIST, MEMORY, WAL, OFF
- 示例：PRAGMA journal_mode=WAL;?(啟用Write-Ahead Logging模式)
synchronous
- 控制同步寫入磁盤的級別
- 可選值：0(OFF), 1(NORMAL), 2(FULL-默認)
- 示例：PRAGMA synchronous=1;
temp_store
- 控制臨時表的存儲方式
- 可選值：0(DEFAULT), 1(FILE), 2(MEMORY)
- 示例：PRAGMA temp_store=2;?(內存存儲臨時表)
encoding
- 數據庫編碼
- 可選值：UTF-8, UTF-16, UTF-16le, UTF-16be
- 示例：PRAGMA encoding="UTF-8";

查詢設置

cache_size
- 設置內存緩存頁數
- 示例：PRAGMA cache_size=2000;?(約2MB緩存)
page_size
- 設置數據庫頁面大小（創建數據庫前設置）
- 示例：PRAGMA page_size=4096;
foreign_keys
- 啟用/禁用外鍵約束
- 可選值：0(OFF), 1(ON)
- 示例：PRAGMA foreign_keys=ON;
auto_vacuum
- 控制自動清理空閑空間
- 可選值：0(NONE), 1(FULL), 2(INCREMENTAL)
- 示例：PRAGMA auto_vacuum=1;

信息查詢

database_list
- 列出所有附加的數據庫
- 示例：PRAGMA database_list;
table_info(table_name)
- 獲取表的列信息
- 示例：PRAGMA table_info(users);
index_list(table_name)
- 獲取表的索引列表
- 示例：PRAGMA index_list(users);
integrity_check
- 檢查數據庫完整性
- 示例：PRAGMA integrity_check;
user_version
用戶自定義版本控制屬性。

查詢當前 user_version

sql
```
PRAGMA user_version;
```
這將返回一個整數值，初始值為 0。

設置 user_version

sql
```
PRAGMA user_version = 版本號;
```
例如：

sql
```
PRAGMA user_version = 1;
```

連接屬性

在編程接口中可設置的常用連接屬性：

SQLITE_OPEN_READONLY?- 只讀模式打開
SQLITE_OPEN_READWRITE?- 讀寫模式打開
SQLITE_OPEN_CREATE?- 不存在時創建數據庫
SQLITE_OPEN_URI?- 允許URI文件名
SQLITE_OPEN_MEMORY?- 內存數據庫
SQLITE_OPEN_NOMUTEX?- 多線程無互斥
SQLITE_OPEN_FULLMUTEX?- 多線程完全互斥

版本信息屬性

sqlite_version()?- 返回SQLite版本
- 示例：SELECT sqlite_version();
sqlite_source_id()?- 返回SQLite源代碼ID
- 示例：SELECT sqlite_source_id();

系統狀態屬性

changes()?- 返回最近操作影響的行數
- 示例：SELECT changes();
total_changes()?- 返回連接期間總更改數
- 示例：SELECT total_changes();
last_insert_rowid()?- 返回最后插入的ROWID
- 示例：SELECT last_insert_rowid();

常用 SQL 命令

數據庫操作

sql

-- 創建數據庫（文件不存在時自動創建）
sqlite3 database_name.db

表操作

sql

-- 創建表
CREATE TABLE table_name (column1 datatype PRIMARY KEY,column2 datatype NOT NULL,column3 datatype DEFAULT value,...
);-- 刪除表
DROP TABLE table_name;-- 修改表
ALTER TABLE table_name ADD COLUMN column_name datatype;

數據操作

sql

-- 插入數據
INSERT INTO table_name (column1, column2, ...) 
VALUES (value1, value2, ...);-- 查詢數據
SELECT column1, column2, ... FROM table_name 
WHERE condition 
ORDER BY column ASC|DESC 
LIMIT number;-- 更新數據
UPDATE table_name 
SET column1 = value1, column2 = value2, ...
WHERE condition;-- 刪除數據
DELETE FROM table_name WHERE condition;

常用函數

SQLite 提供了豐富的內置函數來處理和轉換數據，這些函數可以用于查詢、計算和數據處理。以下是 SQLite 函數的分類詳解和用法示例。

1. 核心函數

1.1 基本標量函數

函數	描述	示例
`COALESCE(X,Y,...)`	返回第一個非NULL參數	`SELECT COALESCE(NULL, 'default')`?→ 'default'
`IFNULL(X,Y)`	如果X為NULL則返回Y	`SELECT IFNULL(NULL, 'backup')`?→ 'backup'
`NULLIF(X,Y)`	X=Y時返回NULL，否則返回X	`SELECT NULLIF(5,5)`?→ NULL
`MAX(X,Y,...)`	返回最大值	`SELECT MAX(3,5,1)`?→ 5
`MIN(X,Y,...)`	返回最小值	`SELECT MIN(3,5,1)`?→ 1

1.2 類型轉換函數

函數	描述	示例
`CAST(expr AS type)`	類型轉換	`SELECT CAST('123' AS INTEGER)`?→ 123
`TYPEOF(expr)`	返回存儲類型	`SELECT TYPEOF(3.14)`?→ 'real'

2. 字符串處理函數

2.1 基本字符串函數

函數	描述	示例
`LENGTH(str)`	字符串長度(UTF-8字符數)	`SELECT LENGTH('SQLite')`?→ 6
`LOWER(str)`	轉換為小寫	`SELECT LOWER('SQLite')`?→ 'sqlite'
`UPPER(str)`	轉換為大寫	`SELECT UPPER('sqlite')`?→ 'SQLITE'
`TRIM(str)`	去除兩端空格	`SELECT TRIM(' SQLite ')`?→ 'SQLite'
`LTRIM(str)`	去除左端空格	`SELECT LTRIM(' SQLite')`?→ 'SQLite'
`RTRIM(str)`	去除右端空格	`SELECT RTRIM('SQLite ')`?→ 'SQLite'

2.2 高級字符串操作

函數	描述	示例
`SUBSTR(str,start,length)`	子字符串	`SELECT SUBSTR('SQLite',2,3)`?→ 'QLi'
`REPLACE(str,old,new)`	字符串替換	`SELECT REPLACE('A-B-C','-','>')`?→ 'A>B>C'
`HEX(blob)`	BLOB轉十六進制	`SELECT HEX(X'53514C697465')`?→ '53514C697465'
`INSTR(str,substr)`	子串位置(1-based)	`SELECT INSTR('SQLite','Li')`?→ 3
`PRINTF(format,...)`	格式化輸出	`SELECT PRINTF('%.2f',3.14159)`?→ '3.14'

3. 數學函數

3.1 基本數學運算

函數	描述	示例
`ABS(X)`	絕對值	`SELECT ABS(-5)`?→ 5
`ROUND(X)`	四舍五入	`SELECT ROUND(3.14159,2)`?→ 3.14
`CEIL(X)`/`CEILING(X)`	向上取整	`SELECT CEIL(3.2)`?→ 4
`FLOOR(X)`	向下取整	`SELECT FLOOR(3.9)`?→ 3
`RANDOM()`	隨機整數(-2?3到2?3-1)	`SELECT RANDOM()`?→ 隨機數

3.2 三角函數和高級運算

函數	描述	示例
`PI()`	π值	`SELECT PI()`?→ 3.141592653589793
`SIN(X)`	正弦(弧度)	`SELECT SIN(PI()/2)`?→ 1.0
`COS(X)`	余弦(弧度)	`SELECT COS(PI())`?→ -1.0
`TAN(X)`	正切(弧度)	`SELECT TAN(PI()/4)`?≈ 1.0
`ASIN(X)`	反正弦	`SELECT ASIN(1)`?→ 1.5707963267948966
`ACOS(X)`	反余弦	`SELECT ACOS(0)`?→ 1.5707963267948966
`ATAN(X)`	反正切	`SELECT ATAN(1)`?≈ 0.7853981633974483
`LOG(X)`/`LOG10(X)`	自然對數/10為底對數	`SELECT LOG10(100)`?→ 2.0
`EXP(X)`	e的X次方	`SELECT EXP(1)`?≈ 2.718281828459045
`POWER(X,Y)`/`POW(X,Y)`	X的Y次方	`SELECT POWER(2,3)`?→ 8
`SQRT(X)`	平方根	`SELECT SQRT(9)`?→ 3.0

4. 日期和時間函數

4.1 核心日期函數

函數	描述	示例
`date(timestr,modifier,...)`	提取日期部分	`SELECT date('now')`?→ '2023-05-20'
`time(timestr,modifier,...)`	提取時間部分	`SELECT time('now')`?→ '14:30:45'
`datetime(timestr,modifier,...)`	日期時間	`SELECT datetime('now')`?→ '2023-05-20 14:30:45'
`julianday(timestr,modifier,...)`	Julian Day數	`SELECT julianday('2023-01-01')`?→ 2459945.5
`strftime(format,timestr,...)`	自定義格式	`SELECT strftime('%Y年%m月%d日','now')`?→ '2023年05月20日'

4.2 日期修飾符

修飾符	描述	示例
`N days`	加減天數	`SELECT date('now','+7 days')`
`N hours`	加減小時	`SELECT time('now','-3 hours')`
`start of month`	當月第一天	`SELECT date('now','start of month')`
`weekday N`	下一個周N(0=周日)	`SELECT date('now','weekday 1')`?→ 下周一
`unixepoch`	Unix時間戳轉換	`SELECT datetime(1640995200,'unixepoch')`?→ '2022-01-01 00:00:00'

5. 聚合函數

5.1 基本聚合函數

函數	描述	示例
`COUNT(X)`	計數	`SELECT COUNT(*) FROM users`
`SUM(X)`	求和	`SELECT SUM(salary) FROM employees`
`AVG(X)`	平均值	`SELECT AVG(score) FROM tests`
`MIN(X)`	最小值	`SELECT MIN(price) FROM products`
`MAX(X)`	最大值	`SELECT MAX(age) FROM customers`

5.2 高級聚合函數

函數	描述	示例
`GROUP_CONCAT(X[,sep])`	連接字符串	`SELECT GROUP_CONCAT(name,', ') FROM users`
`TOTAL(X)`	總是返回浮點和	`SELECT TOTAL(quantity) FROM orders`
`STDEV(X)`	樣本標準差(擴展)	`SELECT STDEV(score) FROM tests`
`VARIANCE(X)`	樣本方差(擴展)	`SELECT VARIANCE(price) FROM stocks`

6. 窗口函數 (SQLite 3.25.0+)

6.1 排名函數

函數	描述	示例
`ROW_NUMBER()`	行號	`SELECT name, ROW_NUMBER() OVER(ORDER BY score DESC) FROM students`
`RANK()`	排名(有間隔)	`SELECT name, RANK() OVER(ORDER BY score DESC) FROM students`
`DENSE_RANK()`	密集排名	`SELECT name, DENSE_RANK() OVER(ORDER BY score DESC) FROM students`
`NTILE(N)`	分組排名	`SELECT name, NTILE(4) OVER(ORDER BY score DESC) FROM students`

6.2 分析函數

函數	描述	示例
`LEAD(expr[,offset])`	后行值	`SELECT date, LEAD(date) OVER(ORDER BY date) FROM events`
`LAG(expr[,offset])`	前行值	`SELECT price, LAG(price) OVER(ORDER BY date) FROM stocks`
`FIRST_VALUE(expr)`	窗口首值	`SELECT name, FIRST_VALUE(score) OVER(PARTITION BY class)`
`LAST_VALUE(expr)`	窗口尾值	`SELECT name, LAST_VALUE(score) OVER(PARTITION BY class)`

7. JSON函數 (SQLite 3.38.0+)

7.1 JSON處理函數

函數	描述	示例
`JSON(json)`	驗證JSON	`SELECT JSON('{"name":"John"}')`?→ 1
`JSON_ARRAY(...)`	創建JSON數組	`SELECT JSON_ARRAY(1,2,3)`?→ '[1,2,3]'
`JSON_OBJECT(...)`	創建JSON對象	`SELECT JSON_OBJECT('id',1,'name','John')`?→ '{"id":1,"name":"John"}'
`JSON_EXTRACT(json,path)`	提取JSON值	`SELECT JSON_EXTRACT('{"id":1}','$.id')`?→ 1
`JSON_INSERT(json,path,value,...)`	插入值	`SELECT JSON_INSERT('{}','$.id',1)`?→ '{"id":1}'
`JSON_REPLACE(json,path,value,...)`	替換值	`SELECT JSON_REPLACE('{"id":1}','$.id',2)`?→ '{"id":2}'
`JSON_SET(json,path,value,...)`	設置值	`SELECT JSON_SET('{}','$.id',1)`?→ '{"id":1}'
`JSON_REMOVE(json,path,...)`	刪除值	`SELECT JSON_REMOVE('{"id":1}','$.id')`?→ '{}'

8. 自定義函數

SQLite 允許用各種編程語言擴展自定義函數：

#include <sqlite3.h>
#include <string.h>static void reverseFunc(sqlite3_context *context, int argc, sqlite3_value **argv) {const char *input = (const char *)sqlite3_value_text(argv[0]);int len = strlen(input);char *result = malloc(len + 1);for (int i = 0; i < len; i++) {result[i] = input[len - 1 - i];}result[len] = '\0';sqlite3_result_text(context, result, len, free);
}// 注冊函數
sqlite3_create_function(db, "reverse", 1, SQLITE_UTF8, NULL, &reverseFunc, NULL, NULL);

事務控制

sql

-- 開始事務
BEGIN TRANSACTION;-- 提交事務
COMMIT;-- 回滾事務
ROLLBACK;

BEGIN TRANSACTION;-- 設置錯誤處理
PRAGMA foreign_keys = ON;  -- 確保外鍵約束生效BEGIN TRY-- 刪除第一張表數據DELETE FROM orders;-- 刪除第二張表數據DELETE FROM order_items;-- 提交事務COMMIT;SELECT '數據刪除成功' AS result;
END TRY
BEGIN CATCH-- 回滾事務ROLLBACK;SELECT '刪除失敗: ' || sqlite_error() AS result;
END CATCH;

索引

sql

-- 創建索引
CREATE INDEX index_name ON table_name (column_name);-- 刪除索引
DROP INDEX index_name;

視圖

sql

-- 創建視圖
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;-- 刪除視圖
DROP VIEW view_name;

觸發器

sql

CREATE TRIGGER trigger_name 
[BEFORE|AFTER] [INSERT|UPDATE|DELETE] ON table_name
BEGIN-- 觸發器邏輯
END;

二、SQLite 加密

SQLCipher 是 SQLite 的一個開源擴展，提供了透明的 256 位 AES 數據庫加密功能。以下是關于 SQLCipher 的詳細介紹和使用方法。

1. SQLCipher 核心特性

透明的加密：使用與標準 SQLite 相同的 API
256 位 AES 加密：支持 CBC 和 GCM 模式
完整的數據庫加密：包括元數據和臨時文件
性能優化：加密操作對性能影響最小化
開源：基于 BSD 許可證
跨平臺：支持 Android、iOS、Windows、macOS、Linux

2. 安裝 SQLCipher

2.1 Linux/macOS 編譯安裝

bash

# 下載源碼
git clone https://github.com/sqlcipher/sqlcipher.git
cd sqlcipher# 編譯安裝
./configure --enable-tempstore=yes CFLAGS="-DSQLITE_HAS_CODEC" \
LDFLAGS="-lcrypto"
make
sudo make install

2.2 Windows 預編譯版本

從官方 GitHub 發布頁面下載預編譯的二進制文件：
https://github.com/sqlcipher/sqlcipher/releases

2.3 Android 集成

在 build.gradle 中添加依賴：

gradle

implementation 'net.zetetic:android-database-sqlcipher:4.5.0'

2.4 iOS 集成

使用 CocoaPods：

ruby

pod 'SQLCipher', '~> 4.5'

3. 基本使用方法

3.1 命令行使用

bash

# 創建加密數據庫
sqlcipher encrypted.db
sqlite> PRAGMA key = 'my-secret-key';
sqlite> CREATE TABLE secret_data(id INTEGER PRIMARY KEY, data TEXT);
sqlite> .quit# 重新打開加密數據庫
sqlcipher encrypted.db
sqlite> PRAGMA key = 'my-secret-key';
sqlite> .tables

3.2?Qt/C++ 使用

修改 .pro 文件：

qmake

LIBS += -lsqlcipher

代碼示例：

#include <QSqlDatabase>
#include <QSqlQuery>
#include <QDebug>int main() {QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");db.setDatabaseName("encrypted.db");if (!db.open()) {qDebug() << "Error opening database:" << db.lastError().text();return -1;}QSqlQuery query;query.exec("PRAGMA key = 'my-secret-key'");if (!query.exec("CREATE TABLE IF NOT EXISTS confidential (""id INTEGER PRIMARY KEY, ""data TEXT NOT NULL)")) {qDebug() << "Create table error:" << query.lastError().text();}db.close();return 0;
}

4. 高級功能

4.1 密碼變更

sql

PRAGMA rekey = 'new-secret-key';

4.2 加密現有數據庫

bash

sqlcipher plaintext.db
sqlite> ATTACH DATABASE 'encrypted.db' AS encrypted KEY 'secret-key';
sqlite> SELECT sqlcipher_export('encrypted');
sqlite> DETACH DATABASE encrypted;

4.3 性能優化設置

sql

PRAGMA cipher_memory_security = OFF;  -- 提高性能但降低安全性
PRAGMA cipher_page_size = 4096;       -- 設置加密頁大小
PRAGMA kdf_iter = 64000;              -- 密鑰派生迭代次數

三、SQLite 數據庫管理工具

1. 跨平臺工具

DB Browser for SQLite (SQLite Browser)

官網:?DB Browser for SQLite
特點:
- 開源免費
- 圖形化界面操作簡單
- 支持數據庫設計、數據瀏覽、SQL查詢等功能
- 支持 Windows、macOS 和 Linux

DBeaver

官網:?DBeaver Community | Free Universal Database Tool
特點:
- 開源社區版免費
- 支持多種數據庫(包括SQLite)
- 強大的SQL編輯器和數據可視化功能
- 跨平臺支持

SQLiteStudio

官網:?SQLiteStudio
特點:
- 完全免費開源
- 便攜版無需安裝
- 內置SQL編輯器、表設計器、數據導入導出工具
- 支持插件擴展

2. 命令行工具

SQLite CLI (sqlite3)

包含于: SQLite官方發行版
特點:
- 官方命令行工具
- 輕量無需安裝
- 支持所有SQLite功能
- 適合開發者和高級用戶

基本用法:

bash

sqlite3 database.db
SQLite version 3.37.0 2021-12-09 14:00:00
Enter ".help" for usage hints.
sqlite> .tables  # 顯示所有表
sqlite> .schema users  # 顯示表結構
sqlite> SELECT * FROM users;  # 執行查詢

四、Qt 中使用 SQLite 數據庫

SQLite 是 Qt 應用程序中最常用的嵌入式數據庫解決方案，它輕量、高效且無需服務器。以下是 Qt 中使用 SQLite 的詳細指南。

1. SQLite 基本使用

1.1 創建并連接數據庫

#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlError>
#include <QDebug>// 創建數據庫連接
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName("my_database.db");  // 可以是內存數據庫 ":memory:"if (!db.open()) {qCritical() << "Database connection error:" << db.lastError().text();return;
}

1.2 創建表

QSqlQuery query;
if (!query.exec("CREATE TABLE IF NOT EXISTS users (""id INTEGER PRIMARY KEY AUTOINCREMENT, ""username TEXT UNIQUE NOT NULL, ""password TEXT NOT NULL, ""created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)")) {qDebug() << "Create table error:" << query.lastError().text();
}

2. 基本 CRUD 操作

2.1 插入數據

// 方法1：直接執行SQL
query.exec("INSERT INTO users (username, password) VALUES ('admin', '123456')");// 方法2：使用預處理語句（推薦）
query.prepare("INSERT INTO users (username, password) VALUES (?, ?)");
query.addBindValue("user1");
query.addBindValue("654321");
if (!query.exec()) {qDebug() << "Insert error:" << query.lastError().text();
}// 方法3：使用命名占位符
query.prepare("INSERT INTO users (username, password) VALUES (:user, :pass)");
query.bindValue(":user", "user2");
query.bindValue(":pass", "qwerty");
query.exec();

2.2 查詢數據

// 查詢單條記錄
query.prepare("SELECT username, created_at FROM users WHERE id = ?");
query.addBindValue(1);
if (query.exec() && query.next()) {QString username = query.value(0).toString();QDateTime createdAt = query.value(1).toDateTime();qDebug() << "User:" << username << "Created at:" << createdAt;
}// 查詢多條記錄
if (query.exec("SELECT id, username FROM users")) {while (query.next()) {int id = query.value(0).toInt();QString name = query.value(1).toString();qDebug() << "ID:" << id << "Name:" << name;}
}

2.3 更新數據

query.prepare("UPDATE users SET password = ? WHERE username = ?");
query.addBindValue("newpassword");
query.addBindValue("admin");
if (!query.exec()) {qDebug() << "Update error:" << query.lastError().text();
}

#include <QSqlDatabase>
#include <QSqlQuery>
#include <QDebug>QSqlDatabase db = QSqlDatabase::database();
QSqlQuery query;// 方法1：使用命名占位符
query.prepare(R"(UPDATE employeesSET salary = :salary,position = :position,department = :departmentWHERE employee_id = :id
)");query.bindValue(":salary", 75000.00);
query.bindValue(":position", "Senior Developer");
query.bindValue(":department", "Engineering");
query.bindValue(":id", 1001);if (!query.exec()) {qDebug() << "更新失敗:" << query.lastError().text();
}// 方法2：使用位置占位符
query.prepare(R"(UPDATE productsSET price = ?,stock = ?,last_updated = ?WHERE product_id = ?
)");query.addBindValue(29.99);
query.addBindValue(50);
query.addBindValue(QDateTime::currentDateTime());
query.addBindValue(2005);if (!query.exec()) {qDebug() << "更新失敗:" << query.lastError().text();
}

2.4 刪除數據

query.prepare("DELETE FROM users WHERE id = ?");
query.addBindValue(5);
if (!query.exec()) {qDebug() << "Delete error:" << query.lastError().text();
}

3. 高級特性

3.1 事務處理

db.transaction();  // 開始事務QSqlQuery query;
query.prepare("INSERT INTO users (username, password) VALUES (?, ?)");// 批量插入
QVariantList usernames, passwords;
usernames << "user3" << "user4" << "user5";
passwords << "pass3" << "pass4" << "pass5";query.addBindValue(usernames);
query.addBindValue(passwords);if (!query.execBatch()) {db.rollback();  // 回滾qDebug() << "Batch insert error:" << query.lastError().text();
} else {db.commit();  // 提交
}

#include <QSqlDatabase>
#include <QSqlQuery>
#include <QDebug>bool deleteTablesData() {QSqlDatabase db = QSqlDatabase::database();bool success = false;// 開始事務if (!db.transaction()) {qDebug() << "開始事務失敗:" << db.lastError();return false;}QSqlQuery query;// 刪除第一張表數據if (!query.exec("DELETE FROM orders")) {qDebug() << "刪除orders表失敗:" << query.lastError();db.rollback();return false;}// 刪除第二張表數據if (!query.exec("DELETE FROM order_items")) {qDebug() << "刪除order_items表失敗:" << query.lastError();db.rollback();return false;}// 提交事務if (!db.commit()) {qDebug() << "提交事務失敗:" << db.lastError();db.rollback();return false;}qDebug() << "數據刪除成功";return true;
}

3.2 使用外鍵約束

// 啟用外鍵支持（SQLite默認關閉）
query.exec("PRAGMA foreign_keys = ON");// 創建有外鍵的表
query.exec("CREATE TABLE IF NOT EXISTS posts (""id INTEGER PRIMARY KEY AUTOINCREMENT, ""user_id INTEGER NOT NULL, ""title TEXT NOT NULL, ""content TEXT, ""FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE)");

4. 數據庫管理

4.1 數據庫初始化封裝

class DatabaseManager {
public:static DatabaseManager& instance() {static DatabaseManager instance;return instance;}bool initialize() {db = QSqlDatabase::addDatabase("QSQLITE");db.setDatabaseName("app_data.db");if (!db.open()) {qCritical() << "Cannot open database:" << db.lastError();return false;}QSqlQuery query;if (!query.exec("PRAGMA foreign_keys = ON")) {qWarning() << "Failed to enable foreign keys:" << query.lastError();}return createTables();}QSqlDatabase& database() { return db; }private:DatabaseManager() {}~DatabaseManager() { db.close(); }bool createTables() {QSqlQuery query;return query.exec("CREATE TABLE IF NOT EXISTS users (""id INTEGER PRIMARY KEY AUTOINCREMENT, ""username TEXT UNIQUE NOT NULL, ""password TEXT NOT NULL)");}QSqlDatabase db;
};

4.2 數據庫遷移

bool migrateDatabase() {QSqlQuery query;query.exec("PRAGMA user_version");  // SQLite特有的版本控制int version = 0;if (query.next()) {version = query.value(0).toInt();}// 版本1遷移if (version < 1) {if (!query.exec("ALTER TABLE users ADD COLUMN email TEXT")) {return false;}query.exec("PRAGMA user_version = 1");}// 版本2遷移if (version < 2) {if (!query.exec("CREATE TABLE IF NOT EXISTS settings (""user_id INTEGER PRIMARY KEY, ""theme TEXT DEFAULT 'light', ""FOREIGN KEY(user_id) REFERENCES users(id))")) {return false;}query.exec("PRAGMA user_version = 2");}return true;
}

5. 性能優化

5.1 批量插入優化

// 方法1：使用事務+批量插入
db.transaction();
QSqlQuery query;
query.prepare("INSERT INTO large_data (value) VALUES (?)");for (int i = 0; i < 10000; ++i) {query.addBindValue(QString::number(i));if (!query.exec()) {db.rollback();break;}
}
db.commit();// 方法2：使用execBatch（更高效）
db.transaction();
query.prepare("INSERT INTO large_data (value) VALUES (?)");QVariantList values;
for (int i = 0; i < 10000; ++i) {values << QString::number(i);
}query.addBindValue(values);
if (!query.execBatch()) {db.rollback();
} else {db.commit();
}

QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName("test.db");
db.open();QSqlQuery query;
db.transaction();  // 開始事務
query.prepare("INSERT INTO table VALUES (?, ?, ?)");  // 預處理QVariantList ids, names, ages;
ids << 1 << 2 << 3;
names << "Alice" << "Bob" << "Charlie";
ages << 25 << 30 << 28;query.addBindValue(ids);
query.addBindValue(names);
query.addBindValue(ages);if(!query.execBatch()) {qDebug() << "Batch insert failed:" << query.lastError();
}
db.commit();  // 提交事務

性能優化建議：
1）單次批量操作建議控制在100-1000條記錄
2）清空綁定緩存后需調用clearBindings()
3）可調整PRAGMA設置如synchronous=OFF提升速度（但降低安全性）

5.2 索引優化

// 創建索引
query.exec("CREATE INDEX IF NOT EXISTS idx_username ON users(username)");// 查看查詢計劃（調試性能）
query.exec("EXPLAIN QUERY PLAN SELECT * FROM users WHERE username = 'admin'");
while (query.next()) {qDebug() << query.value(3).toString();  // 顯示查詢計劃
}

6. 安全注意事項

6.1 防止SQL注入

// 錯誤方式（易受SQL注入攻擊）
QString username = getUsernameFromInput();  // 用戶輸入
query.exec("SELECT * FROM users WHERE username = '" + username + "'");// 正確方式：使用預處理語句
query.prepare("SELECT * FROM users WHERE username = ?");
query.addBindValue(username);
query.exec();

6.2 敏感數據加密

// 使用QCryptographicHash加密密碼
#include <QCryptographicHash>QString password = "user_password";
QByteArray hashed = QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha256
);
QString hashedPassword = QString(hashed.toHex());// 存儲加密后的密碼
query.prepare("INSERT INTO users (username, password) VALUES (?, ?)");
query.addBindValue(username);
query.addBindValue(hashedPassword);
query.exec();

7. 與Qt模型/視圖集成

7.1 使用QSqlTableModel

QSqlTableModel *model = new QSqlTableModel(this);
model->setTable("users");
model->setEditStrategy(QSqlTableModel::OnManualSubmit);
model->select();// 自定義列顯示
model->setHeaderData(0, Qt::Horizontal, tr("ID"));
model->setHeaderData(1, Qt::Horizontal, tr("Username"));
model->setHeaderData(2, Qt::Horizontal, tr("Password"));// 過濾數據
model->setFilter("username LIKE '%admin%'");
model->select();// 綁定到視圖
QTableView *view = new QTableView;
view->setModel(model);
view->show();

7.2 自定義SQL查詢模型

class CustomSqlModel : public QSqlQueryModel {
public:explicit CustomSqlModel(QObject *parent = nullptr) : QSqlQueryModel(parent) {}QVariant data(const QModelIndex &index, int role) const override {if (role == Qt::BackgroundRole && index.column() == 2) {return QBrush(Qt::lightGray);}return QSqlQueryModel::data(index, role);}
};// 使用自定義模型
CustomSqlModel *model = new CustomSqlModel;
model->setQuery("SELECT id, username, password FROM users");

8. 在 SQLite 中插入記錄并返回對應的主鍵

8.1. 使用 SQLite 的 last_insert_rowid() 函數

-- 插入記錄
INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com');-- 獲取最后插入的ID
SELECT last_insert_rowid();

8.2. Qt/C++ (QSqlQuery)

#include <QSqlDatabase>
#include <QSqlQuery>
#include <QDebug>QSqlDatabase db = QSqlDatabase::database();
QSqlQuery query;// 插入記錄
query.prepare("INSERT INTO users (name, email) VALUES (:name, :email)");
query.bindValue(":name", "John Doe");
query.bindValue(":email", "john@example.com");if (query.exec()) {// 獲取最后插入的IDQVariant id = query.lastInsertId();if (id.isValid()) {qDebug() << "插入的記錄ID:" << id.toInt();} else {qDebug() << "無法獲取插入ID";}
} else {qDebug() << "插入失敗:" << query.lastError().text();
}

9. SQLite 存儲 UTF-8 中文數據

9.1?創建 UTF-8 編碼的數據庫連接

#include <QSqlDatabase>
#include <QSqlQuery>
#include <QDebug>bool createConnection()
{QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");db.setDatabaseName("mydatabase.db");if (!db.open()) {qDebug() << "無法打開數據庫";return false;}// 設置UTF-8編碼QSqlQuery query;if (!query.exec("PRAGMA encoding = \"UTF-8\"")) {qDebug() << "設置編碼失敗:" << query.lastError();return false;}return true;
}

9.2?插入中文數據

bool insertUser(const QString &name, const QString &address, const QString &comment)
{QSqlQuery query;query.prepare("INSERT INTO users (name, address, comment) ""VALUES (:name, :address, :comment)");query.bindValue(":name", name.toUtf8());query.bindValue(":address", address.toUtf8());query.bindValue(":comment", comment.toUtf8());if (!query.exec()) {qDebug() << "插入數據失敗:" << query.lastError();return false;}return true;
}// 調用示例
insertUser("張三", "北京市海淀區", "這是UTF-8編碼的中文注釋");

9.3?查詢中文數據

void queryUsers()
{QSqlQuery query;if (!query.exec("SELECT id, name, address, comment FROM users")) {qDebug() << "查詢失敗:" << query.lastError();return;}while (query.next()) {int id = query.value(0).toInt();QString name =  QString::fromUtf8(query.value(1).toByteArray());QString address =  QString::fromUtf8(query.value(2).toByteArray());QString comment =  QString::fromUtf8(query.value(3).toByteArray());qDebug() << "ID:" << id << "姓名:" << name << "地址:" << address<< "備注:" << comment;}
}// 條件查詢
void queryUserByName(const QString &name)
{QSqlQuery query;query.prepare("SELECT id, name FROM users WHERE name = :name");query.bindValue(":name", name);if (!query.exec()) {qDebug() << "條件查詢失敗:" << query.lastError();return;}while (query.next()) {qDebug() << "找到用戶:" << QString::fromUtf8(query.value(1).toByteArray());}
}

10.?日期時間處理

基本函數

-- 當前日期時間
SELECT datetime('now');        -- 'YYYY-MM-DD HH:MM:SS'
SELECT date('now');            -- 'YYYY-MM-DD'
SELECT time('now');            -- 'HH:MM:SS'
SELECT strftime('%Y-%m-%d %H:%M:%S', 'now');  -- 自定義格式-- 時間計算
SELECT datetime('now', '+1 day');      -- 加1天
SELECT datetime('now', '-3 hours');    -- 減3小時
SELECT datetime('now', '+2 months');   -- 加2個月

時間戳轉換

-- 時間戳轉日期時間
SELECT datetime(1640995200, 'unixepoch');  -- '2022-01-01 00:00:00'-- 日期時間轉時間戳
SELECT strftime('%s', '2022-01-01 00:00:00');  -- 1640995200

?QT實例

#include <QSqlDatabase>
#include <QSqlQuery>
#include <QDateTime>
#include <QDebug>// 插入當前時間
QSqlQuery query;
query.prepare("INSERT INTO orders (product, order_date) VALUES (?, ?)");
query.addBindValue("Keyboard");
query.addBindValue(QDateTime::currentDateTime().toString("yyyy-MM-dd HH:mm:ss"));
query.exec();// 查詢日期數據
if (query.exec("SELECT product, order_date FROM orders")) {while (query.next()) {QString product = query.value(0).toString();QDateTime orderDate = QDateTime::fromString(query.value(1).toString(), "yyyy-MM-dd HH:mm:ss");qDebug() << product << "ordered at" << orderDate;}
}

在 SQLite 中使用?current_time?作為字段名確實可能引起問題，因為它與 SQLite 的內置時間函數同名。

SQLite 保留的時間函數

以下是與時間相關的內置函數，最好避免用作字段名：

current_time
current_date
current_timestamp
date()
time()
datetime()
strftime()
julianday()

不推薦	推薦替代
`current_time`	`record_time`,?`created_at`,?`update_time`
`current_date`	`record_date`,?`transaction_date`
`current_timestamp`	`last_updated`,?`modified_at`

11. 數據庫被鎖定

// 設置繁忙超時時間（毫秒）
QSqlDatabase::database().setConnectOptions("QSQLITE_BUSY_TIMEOUT=30000");// 或者使用重試機制
int retries = 3;
while (retries-- > 0) {if (query.exec("SOME SQL")) break;if (query.lastError().text().contains("locked")) {QThread::msleep(100);continue;}break;
}

12. 處理大型BLOB數據

// 存儲圖片
QFile imageFile("photo.jpg");
if (imageFile.open(QIODevice::ReadOnly)) {QByteArray imageData = imageFile.readAll();query.prepare("INSERT INTO images (name, data) VALUES (?, ?)");query.addBindValue("profile.jpg");query.addBindValue(imageData);query.exec();
}// 讀取圖片
query.exec("SELECT data FROM images WHERE name = 'profile.jpg'");
if (query.next()) {QByteArray imageData = query.value(0).toByteArray();QPixmap pixmap;pixmap.loadFromData(imageData);
}