Qt 中使用 SQLite 数据库-EW帮帮网

一、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 类型转换函数

函数	描述	示例

Qt 中使用 SQLite 数据库