一、SQLite 数据库介绍

SQLite 是一个轻量级的嵌入式关系型数据库管理系统，它以库的形式提供，不需要单独的服务器进程，直接访问存储在普通磁盘文件中的数据库。

主要特性

  1. 无服务器架构：SQLite 不需要单独的服务器进程

  2. 零配置：无需安装或管理

  3. 跨平台：支持所有主流操作系统

  4. 事务性：完全兼容 ACID (原子性、一致性、隔离性、持久性)

  5. 自包含：单个磁盘文件包含整个数据库

  6. 小型轻量：完整的库只有几百KB大小

  7. 公共领域：完全开源，无版权限制

数据类型

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, INTINTEGER1, -234, 123456789012345
REAL, FLOATREAL3.14, -123.456, 1.23e+10
TEXT, VARCHARTEXT'Hello', '2023-01-01'
BLOBBLOBx'53514C697465' (十六进制)
DATE, DATETIMENUMERIC'2023-05-20', '2023-05-20 12:34'
BOOLEANNUMERIC0 (false), 1 (true)

常用属性

SQLite 提供了一系列属性和编译时选项（Pragma）来控制和查询数据库的行为。以下是常用的 SQLite 属性：

数据库配置属性（PRAGMA）

数据库设置

  1. journal_mode

    • 设置事务日志模式

    • 可选值：DELETE（默认）, TRUNCATE, PERSIST, MEMORY, WAL, OFF

    • 示例：PRAGMA journal_mode=WAL; (启用Write-Ahead Logging模式)

  2. synchronous

    • 控制同步写入磁盘的级别

    • 可选值：0(OFF), 1(NORMAL), 2(FULL-默认)

    • 示例：PRAGMA synchronous=1;

  3. temp_store

    • 控制临时表的存储方式

    • 可选值：0(DEFAULT), 1(FILE), 2(MEMORY)

    • 示例：PRAGMA temp_store=2; (内存存储临时表)

  4. encoding

    • 数据库编码

    • 可选值：UTF-8, UTF-16, UTF-16le, UTF-16be

    • 示例：PRAGMA encoding="UTF-8";

查询设置

  1. cache_size

    • 设置内存缓存页数

    • 示例：PRAGMA cache_size=2000; (约2MB缓存)

  2. page_size

    • 设置数据库页面大小（创建数据库前设置）

    • 示例：PRAGMA page_size=4096;

  3. foreign_keys

    • 启用/禁用外键约束

    • 可选值：0(OFF), 1(ON)

    • 示例：PRAGMA foreign_keys=ON;

  4. auto_vacuum

    • 控制自动清理空闲空间

    • 可选值：0(NONE), 1(FULL), 2(INCREMENTAL)

    • 示例：PRAGMA auto_vacuum=1;

信息查询

  1. database_list

    • 列出所有附加的数据库

    • 示例：PRAGMA database_list;

  2. table_info(table_name)

    • 获取表的列信息

    • 示例：PRAGMA table_info(users);

  3. index_list(table_name)

    • 获取表的索引列表

    • 示例：PRAGMA index_list(users);

  4. integrity_check

    • 检查数据库完整性

    • 示例：PRAGMA integrity_check;

  5. user_version
    用户自定义版本控制属性。

    查询当前 user_version

    sql

    PRAGMA user_version;

    这将返回一个整数值，初始值为 0。

    设置 user_version

    sql

    PRAGMA user_version = 版本号;

    例如：

    sql

    PRAGMA user_version = 1;

连接属性

在编程接口中可设置的常用连接属性：

  1. SQLITE_OPEN_READONLY - 只读模式打开

  2. SQLITE_OPEN_READWRITE - 读写模式打开

  3. SQLITE_OPEN_CREATE - 不存在时创建数据库

  4. SQLITE_OPEN_URI - 允许URI文件名

  5. SQLITE_OPEN_MEMORY - 内存数据库

  6. SQLITE_OPEN_NOMUTEX - 多线程无互斥

  7. SQLITE_OPEN_FULLMUTEX - 多线程完全互斥

版本信息属性

  1. sqlite_version() - 返回SQLite版本

    • 示例：SELECT sqlite_version();

  2. sqlite_source_id() - 返回SQLite源代码ID

    • 示例：SELECT sqlite_source_id();

系统状态属性

  1. changes() - 返回最近操作影响的行数

    • 示例：SELECT changes();

  2. total_changes() - 返回连接期间总更改数

    • 示例：SELECT total_changes();

  3. 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则返回YSELECT IFNULL(NULL, 'backup') → 'backup'
NULLIF(X,Y)X=Y时返回NULL，否则返回XSELECT 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⁶³到2⁶³-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') → 下周一
unixepochUnix时间戳转换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)验证JSONSELECT 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 允许用各种编程语言扩展自定义函数：

c

#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_timerecord_time, created_at, update_time
current_daterecord_date, transaction_date
current_timestamplast_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);
}

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如若转载，请注明出处：http://www.pswp.cn/bicheng/89242.shtml
繁体地址，请注明出处：http://hk.pswp.cn/bicheng/89242.shtml
英文地址，请注明出处：http://en.pswp.cn/bicheng/89242.shtml

如若内容造成侵权/违法违规/事实不符，请联系英文站点网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

【Unity】IL2CPP相关理论知识学习

【Unity】IL2CPP相关理论知识学习

一种编译技术。优点&#xff1a;性能优化&#xff1a;IL2CPP生成C代码后由本地编译器优化&#xff0c;一般在CPU性能和GC方面都优于Mono。特别在移动端或主机平台&#xff0c;性能差距更加明显。跨平台支持&#xff1a;Unity作为跨平台引擎&#xff0c;IL2CPP是支持iOS、Androi…
阅读更多...
一个用于在 Ubuntu 22.04.3 LTS 上显示文件系统超级块信息的 C 程序

一个用于在 Ubuntu 22.04.3 LTS 上显示文件系统超级块信息的 C 程序

1.程序#include <stdio.h> #include <sys/statvfs.h> #include <errno.h>int main(int argc, char *argv[]) {const char *path;struct statvfs fs_info;// 检查参数if (argc ! 2) {fprintf(stderr, "用法: %s <挂载点或路径>\n", argv[0]);…
阅读更多...
Git未检测到文件更改

Git未检测到文件更改

背景 在本地仓库改动文件发现git检测不到修改了的文件&#xff0c;安装有Git状态可视化工具&#xff0c;文件改动后应该是红色标记&#xff0c;但是仍然是绿色的 git status&#xff0c;git diff等也都没有显示文件改动 原因 1.可能是文件命中了.gitignore文件过滤条件 检查后发…
阅读更多...
Golang学习之常见开发陷阱完全手册

Golang学习之常见开发陷阱完全手册

1. 指针的“温柔陷阱”&#xff1a;空指针与野指针的致命一击Go语言的指针虽然比C/C简单&#xff0c;但照样能让你“痛不欲生”。新手常觉得Go的指针“安全”&#xff0c;但真相是&#xff1a;Go并不会帮你完全规避指针相关的Bug。空指针&#xff08;nil pointer&#xff09;和…
阅读更多...
【python】sys.executable、sys.argv、Path(__file__) 在PyInstaller打包前后的区别

【python】sys.executable、sys.argv、Path(__file__) 在PyInstaller打包前后的区别

文章目录sys.executable 的区别打包前打包后sys.argv 的区别打包前打包后Path(__file__) 的区别打包前打包后应用场景与解决方案总结在使用 PyInstaller 将 Python 脚本打包为独立可执行文件时&#xff0c; sys.executable、 sys.argv 和 Path(__file__) 的行为会发生变化。理…
阅读更多...
JWT基础详解

JWT基础详解

JSON Web Token 简称JWT 一、起源&#xff1a; 这一切的起源都源于网景公司的一个天才程序员&#xff0c;为了解决http协议无状态问题&#xff0c;就让浏览器承担了一部分“记忆”责任&#xff08;每次客户端&#xff0c;访问服务器&#xff0c;自身就携带cookie&#xff0c;…
阅读更多...
【Unity】MiniGame编辑器小游戏（十四）基础支持模块（游戏窗口、游戏对象、物理系统、动画系统、射线检测）

【Unity】MiniGame编辑器小游戏（十四）基础支持模块（游戏窗口、游戏对象、物理系统、动画系统、射线检测）

更新日期：2025年7月15日。 项目源码：获取项目源码 索引 基础支持模块一、游戏窗口 MiniGameWindow1.窗体属性2.快速退出键3.模拟帧间隔时间4.生命周期函数5.游戏状态二、游戏对象 MiniGameObject1.位置2.激活状态3.碰撞器4.限制游戏对象的位置5.生命周期函数6.移动三、物理系…
阅读更多...
Swift6.0 - 5、基本运算符

Swift6.0 - 5、基本运算符

目录1、术语2、赋值运算符&#xff08;a b&#xff09;3、算术运算符&#xff08;、-、*、/&#xff09;3.1、余数运算符&#xff08;%&#xff09;3.2、一元负号运算符&#xff08;-a&#xff09;3.3、一元正号运算符&#xff08;a&#xff09;4、复合赋值运算符&#xff08;…
阅读更多...
DataWhale AI夏令营 Task2.2笔记

DataWhale AI夏令营 Task2.2笔记

本次代码改进主要集中在聚类算法和主题词提取方法的优化上&#xff0c;主要包含三个关键修改&#xff1a;首先&#xff0c;将聚类算法从KMeans替换为DBSCAN。这是因为原KMeans方法需要预先指定聚类数量&#xff0c;而实际评论数据中的主题分布难以预测。DBSCAN算法能够自动确定…
阅读更多...
自启动策略调研

自启动策略调研

广播拦截策略1.流程图广播发送├─ 特权进程&#xff08;Root/Shell&#xff09; → 放行├─ 系统进程&#xff08;UID≤1000&#xff09; → 自动启动校验 → 非法广播&#xff1f; → 拦截│ ├─ 黑名单匹配 → 拦截│ └─ 用户/白名单校验 → 受限用户&#xff1f; →…
阅读更多...
MFC/C++语言怎么比较CString类型最后一个字符

MFC/C++语言怎么比较CString类型最后一个字符

文章目录&#x1f527; 1. 直接下标访问&#xff08;高效首选&#xff09;&#x1f50d; 2. ReverseFind 反向定位&#xff08;语义明确&#xff09;✂️ 3. Right 提取子串&#xff08;需临时对象&#xff09;⚙️ 4. 封装工具函数&#xff08;推荐健壮性场景&#xff09;⚠️…
阅读更多...
【Cortex-M】异常中断时的程序运行指针SP获取，及SCB寄存器错误类型获取

【Cortex-M】异常中断时的程序运行指针SP获取，及SCB寄存器错误类型获取

【Cortex-M】异常中断时的程序运行指针SP获取&#xff0c;及SCB寄存器错误类型获取 更新以gitee为准&#xff1a; gitee 文章目录异常中断异常的程序运行指针SP获取SCB寄存器错误类型获取硬件错误异常 Hard fault status register (SCB->HFSR)存储器管理错误异常 SCB->C…
阅读更多...
项目流程管理系统使用建议：推荐13款

项目流程管理系统使用建议：推荐13款

本文分享了13款主流的项目流程管理系统&#xff0c;包括&#xff1a;1.PingCode&#xff1b;2.Worktile&#xff1b;3.泛微 E-Office&#xff1b;4.Microsoft Project&#xff1b;5.简道云&#xff1b;6.Zoho Projects&#xff1b;7.Tita 项目管理&#xff1b;8.Oracle Primave…
阅读更多...
neovim的文件结构

neovim的文件结构

在 Linux 系统中&#xff0c;Neovim 的配置文件主要存放在以下目录结构中&#xff1a; &#x1f4c1; 核心配置目录路径内容描述~/.config/nvim/主配置目录 (Neovim 的标准配置位置)~/.local/share/nvim/Neovim 运行时数据&#xff08;插件、会话等&#xff09; &#x1f5c2;️…
阅读更多...
【网易云-header】

【网易云-header】

网易云静态页面&#xff08;1&#xff09;效果htmlcss效果 html <!DOCTYPE html> <html lang"en"><head><meta charset"UTF-8"><meta name"viewport" content"widthdevice-width, initial-scale1.0">&…
阅读更多...
Android开发知识点总结合集

Android开发知识点总结合集

初级安卓开发需要掌握的知识点主要包括安卓四大组件、Context、Intent、Handler、Fragment、HandlerThread、AsyncTask、IntentService、Binder、AIDL、SharedPreferences、Activity、Window、DecorView以及ViewRoot层级关系、触摸事件分发机制、View绘制流程、自定义View。 1…
阅读更多...
如何通过域名白名单​OVP防盗链加密视频？

如何通过域名白名单​OVP防盗链加密视频？

文章目录前言一、什么是域名白名单​OVP防盗链二、域名白名单​OVP防盗链的实现原理三、如何实现域名白名单​OVP防盗链加密视频总结前言 用户原创视频资源面临被非法盗链、恶意嵌入的严峻挑战&#xff0c;盗用行为不仅侵蚀创作者收益&#xff0c;更扰乱平台生态秩序。域名白名…
阅读更多...
密码学系列文(2)--流密码

密码学系列文(2)--流密码

一、流密码的基本概念RC4&#xff08;Rivest Cipher 4&#xff09;是由密码学家 Ron Rivest&#xff08;RSA 算法发明者之一&#xff09;于 1987 年设计的对称流加密算法。它以简单、高效著称&#xff0c;曾广泛应用于网络安全协议&#xff08;如 SSL/TLS、WEP/WPA&#xff09;…
阅读更多...
Drools‌业务引擎

Drools‌业务引擎

drools引擎使用 官网介绍 一、底层原理 ReteOO 网络 • 本质是一张“有向无环图”&#xff0c;节点类型&#xff1a; – Root / ObjectTypeNode&#xff1a;按 Java 类型分发事实 – AlphaNode&#xff1a;单对象约束&#xff08;age > 18&#xff09; – BetaNode&#xf…
阅读更多...
linux的磁盘满了清理办法

linux的磁盘满了清理办法

今天测试系统的某个磁盘满了&#xff0c;需要看一下&#xff0c;可以看到的是&#xff0c;已经被占用百分之百了&#xff0c;某些服务运行不了了&#xff0c;需要清一下&#xff0c;这个我熟看哪个目录占用空间大cd / du -sh * ##找到占用最大&#xff0c;比如cd /home cd /hom…
阅读更多...
最新文章

Copyright @ 2022~2023