SQLite3 数据库入门

最近更新于 2022-04-05 12:37

发布于：blog.iyatt.com

简介

SQLite 是一款轻量级的开源嵌入式数据库，使用方便，性能出众，广泛应用于消费电子、医疗、工业控制和军事等领域。SQLite 具有以下特点。

• 性能：SQLite 对数据库的访问性能很高，其运行速度比 MySQL 等开源数据库要快很多。
• 体积：非常小巧，最低只需要几百KB的内存就可以运行。
• 可移植性：支持 32 位和 64 位体系的硬件平台，也能在 Windows、Linux、BSD、Mac OS 和 Solaries 等平台上运行。
• 稳定性：支持 ACID 特性，即原子性、一致性、隔离性和持久性。
• SQL 支持：支持 ANSI SQL92 中的大多数标准，提供了对子查询、视图和触发器机制的支持。
• 接口：为 C、Java、PHP、Python、Tcl 等多种语言提供了 API 接口。

• 接口：接口由 SQLite C API 函数组成。所有的应用程序都必须通过接口访问 SQLite 数据库。
• 编译器：编译器由词法分析、语法分析和中间代码生成 3 个模块组成。其中，词法分析模块和语法分析模块负责检查 SQL 语句的语法，然后把生成的语法树传递给中间代码生成模块。中间代码生成模块负责生成 SQLite 引擎可以识别的中间代码。
• 数据库引擎：数据库引擎是 SQlite 的核心，负责运行中间代码，指挥数据库的具体操作。
• 后台：后台由 B 树、页缓冲和系统调用 3 个模块组成。其中，B模块负责维护索引，页缓冲负责页面数据的传输，系统调用负责和操作系统交互，最终实现数据库的访问。

SQLite 支持的基本数据类型主要有：NULL、NUMERIC、INTEGER、REAL 和 TEXT。SQLite 会自动把其它数据类型转换成以上 5 类基本数据类型，转换规则如下：

官网：https://www.sqlite.org/

项目地址：https://github.com/sqlite/sqlite

安装

Debian 系 Linux

sudo apt install -y sqlite3 libsqlite3-dev

Windows 在 msys2 环境中安装

pacman -S mingw64/mingw-w64-x86_64-sqlite3

演示环境

Windows 11 专业工作站版 21H2

msys2：

• GNU gcc 11.2.0
• cmake 3.22.3
• GNU make 4.3
• sqlite 3.38.0 2022-02-22

SQLite3 常用命令及语句

注：

• SQLite3 语句不区分大小写，不过一般语句都是大写，命令对大小写敏感
• SQLite3 每个语句以半角分号结束
• 终端执行 sqlite3 可以进入交互环境，在交互环境执行 .help 可以查看命令说明，执行 .q 退出交互环境

（1）新建或打开一个数据库

存在则打开，不存在则创建

方式一：

sqlite3 【数据库名.db】

比如这里我创建一个数据库名为 test.db（只有真正对数据库写操作后才会创建）

执行命令后会进入交互环境

方式二：

先进入交互环境，然后执行 .oepn 【数据库名】，该方式会直接创建数据库文件

——————————————————————————————————

数据床创建好后，执行 .databases 命令可以查看

（2）导出数据库到文本文件或导入

使用 .dump 命令可以将数据库导出到文本文件（文本文件会以将数据以 sqlite3 语句的形式保存）

sqlite3 【数据库文件名】 .dump > 【文本文件.sql】

由文本文件导入到数据库文件中

sqlite3 【数据库文件名】 < 【文本文件】

之前我是在 Linux 下用过 SQLite3，这次演示是在 Windows 下也是第一次，这里就遇到了几个问题：

① 半角小于符号居然是 PowerShell 保留的，无法使用，我也是无语了

② SQLite3 本来也是在 msys2 中安装的，但是无法进入交互环境，会阻塞无反应，这也是我为什么用 PowerShell 操作的原因（将 msys2 的 bin 目录设置环境变量到 Windows 中）

③ 在 PowerShell 中导出数据库到文本，不知道为什么文本的字符编码是 UTF-16 LE（通过 vscode 打开文本，自动识别出的字符编码），然后我在 msys2 中尝试从文本导入到数据库文件就会失败，我猜测的原因是 SQLite3 默认的字符编码是 UTF-8，这边不统一会无法识别。然后我直接在 msys2 中导出文本，再导入就没有问题了。

所以经过几次尝试后，总结出使用方式，涉及数据库文件创建以及读写数据库内容的在 PowerShell 操作，涉及数据库文件导出到文本以及文本到数据库的操作在 msys2 中进行。

额，突然发现 cmd 反而完全正常使用（只是不知道和这个 UTF-8 设置有没有关系：https://blog.iyatt.com/?p=3437）。现在突然发现 cmd 比 PowerShell 更好用了，因为把 msys2 的 bin 目录设置到 Windows 的环境变量里了，在 cmd 中也能用 msys2 的命令，那么 PowerShell 也没啥优势了，cmd + msys2 后十分好用。

那么这里加一个题外话，将 cmd 添加到右键中

将注册表文件下载后，在文件管理器中双击打开，一路同意继续就行。

在文件管理器中某个路径下右键，就能看到 cmd，打开的 cmd 运行路径就在这里了

Win11 把右键菜单弄成折叠了，这个挺烦的，恢复 Win10 菜单：https://blog.iyatt.com/?p=3600

（3）创建表

使用 CREATE TABLE 【表名】【表头】在数据库中创建表

这里我在数据库 test.db 中创建两张表：my_table1，my_table2

创建第一个表 my_table1，表头有 id、name、age、address 和 salary，后面注明了表头对应的数据类型，NOT NULL 代表创建记录时不能为 NULL 的项。

简单的创建第二个表 my_table2，表头有 name 和 sex

查看数据库中所有表名 .table

（4）删除表

DROP TABLE 【表名】删除指定的表

删除 my_table2

（5）向表中插入数据

向 column1、column2、column3 列分别插入 value1、value2、value3

INSERT INTO 【表名】(column1, column2, column3) VALUES (value1, value2, value3)

依次插入

INSERT INTO 【表名】 VALUES (value1, value2, value3)

不指定插入的列名，那么有多少列，就要插入多少列的数据，如果没有实际的数据，可以插入 NULL，但是创建表时设定了前三列不能为 NULL，所以最多只有后面两列可以设置 NULL，否则插入数据失败。

（6）查看数据

查看指定列的数据

SELECT column1, column2, column3 FROM 【表名】

查看所有列的数据

SELECT * FROM 【表名】

（7）运算符

① 算术运算

② 比较运算符

这里新弄了一个表有 5 个名字和对应的年龄

③ 逻辑运算符

这里我又重新建了一个表，分别为 id、name、age

假如我要筛选 age 在 （18,25）区间的

④ 位运算符

（8）更新数据

UPDATE 【表名】 SET 【列名】=【新值】 WHERE 【条件】

将 name 为 D 的 age 改为10

注意不设置条件会更改所有行，如

（9）删除表中的数据

DELETE FROM 【表名】 WHERE 【条件】

这里删除 id 为 3 的记录

不加条件时，删除表种所有记录

（10）文本匹配

① LIKE （大小写不敏感）

有两个通配符配合一起使用

这里有一张表

假如我要筛选 name 列以 “Xiao” 开头的

假如我要筛选 age 以 1 开头的

② GLOB （大小写敏感）

有两个通配符配合使用

用法和 LIKE 类似，只是大小写敏感

（11）限制 SELECT 返回记录数量

只返回 n 条记录

xxxx LIMIT n

OFFSET 指定偏移量，从下标 m 开始的 n 条记录

xxxx LIMIT n OFFSET m

（12）返回排序后的记录

# 升序/降序（不指定默认升序）
XXXX ORDER BY 【列名】 ASC/DESC

按 age 升序

按 id 降序

（13）数据分组

GROUP BY 用于对相同数据进行分组，该子句应位于 WHERE 后，ORDER BY 之前。

这里创建了一张表，第一列为 name，第二列为 nums

可以看到第一列有重复的 name，假如我用统计每个 name 对应的数字之和

如果不计算和，那么就是返回每个 name 的第一条记录

HAVING 用于在 GROYUP BY 的基础上过滤结果

假如这里我要获取 name 至少出现两次的 nums 总和

(14）去重

DISTINCT 配合 select 一起使用，消除重复记录，比如下面筛选 name，重复的不再显示

（15）附加数据库和分离数据库

可以将其它数据库关联到自己上面，然后实现同时访问自己和别的数据库。

比如这里我创建了两个数据库，

第一个为 database1.db，含有一张表叫 table1

第二个为 database2.db，含有一张表叫 table2

然后这里我先打开 database1.db，然后将 database2.db 附加到 database1.db 里，并命名为 database2

ATTACH DATABASE 'database2.db' AS database2

查看当前附加的数据

.database

然后可以查看数据库中有哪些表，可以看到 database2.db 数据库中的表，也能访问

注意，只要退出交互环境，那么附加关系就解除了。或者设置了附加，要分离数据库，可以执行

DETACH DATABASE 【附加数据库的命名】

C 语言开发

CMakeLists.txt

cmake_minimum_required(VERSION 3.16.0)
project(sqlite3_test VERSION 0.1 LANGUAGES C)

set(CMAKE_C_STANDARD 17)
set(CMAKE_C_FLAGS_RELEASE "-no-pie -O3")
set(CMAKE_C_FLAGS_DEBUG "-no-pie -Wall -Werror=return-type -Werror=address -Werror=sequence-point -Werror=format-security -Wextra -pedantic -Wimplicit-fallthrough -Wsequence-point -Wswitch-unreachable -Wswitch-enum -Wstringop-truncation -Wbool-compare -Wtautological-compare -Wfloat-equal -Wshadow=global -Wpointer-arith -Wpointer-compare -Wcast-align -Wcast-qual -Wwrite-strings -Wdangling-else -Wlogical-op -Wconversion -g -O0 -DDEBUG")
set(EXECUTABLE_OUTPUT_PATH ${CMAKE_SOURCE_DIR}/bin)

aux_source_directory(${CMAKE_SOURCE_DIR} SRC)
add_executable(sqlite3_test ${SRC})

find_package(SQLite3 3.30.0 REQUIRED)
target_link_libraries(sqlite3_test ${SQLite3_LIBRARIES})

（1）连接数据库

不存在就创建

#include "sqlite3.h"

#include <stdio.h>


int main()
{
    sqlite3 *db;  // 句柄
    if (sqlite3_open("test.db", &db) != SQLITE_OK)  // 打开数据库
    {
        fprintf(stderr, "打开数据库失败：%s\n", sqlite3_errmsg(db));  // 错误信息
        return 1;
    }
    else
    {
        printf("连接数据库成功！\n");
    }
    sqlite3_close(db);  // 关闭数据库
}

（2）创建表

#include "sqlite3.h"

#include <stdio.h>


int main()
{
    sqlite3 *db;  // 句柄
    if (sqlite3_open("test.db", &db) != SQLITE_OK)  // 打开数据库
    {
        fprintf(stderr, "打开数据库失败：%s\n", sqlite3_errmsg(db));
        return 1;
    }

    const char *create_table = "CREATE TABLE my_table(" \
                        "name TEXT NOT NULL," \
                        "age INT NOT NULL);";  // sql 语句
    
    char *err_msg = NULL;  // 用于存放错误信息
    if (sqlite3_exec(db, create_table, NULL, NULL, &err_msg) != SQLITE_OK)  // 执行 SQL 语句
    {
        fprintf(stderr, "SQL 语句执行错误: %s\n", err_msg);
        sqlite3_free(err_msg);
    }

    sqlite3_close(db);
}

（3）插入数据

基本上各种操作都是基于 SQL 语句的，就是使用 sqlite3_exec() 执行语句，包括查询 SELECT、更新记录 UPDATE，删除记录 DELETE 等等。

#include "sqlite3.h"

#include <stdio.h>


int main()
{
    sqlite3 *db;  // 句柄
    if (sqlite3_open("test.db", &db) != SQLITE_OK)  // 打开数据库
    {
        fprintf(stderr, "打开数据库失败：%s\n", sqlite3_errmsg(db));
        return 1;
    }

    const char *insert_values = "INSERT INTO my_table VALUES ('Xiao Hong', 20);" \
                                    "INSERT INTO my_table VALUES ('Xiao Zhang', 18);" \
                                    "INSERT INTO my_table VALUES ('Zhang Fei', 34);";  // sql 语句
    
    char *err_msg = NULL;  // 用于存放错误信息
    if (sqlite3_exec(db, insert_values, NULL, NULL, &err_msg) != SQLITE_OK)  // 执行 SQL 语句
    {
        fprintf(stderr, "SQL 语句执行错误: %s\n", err_msg);
        sqlite3_free(err_msg);
    }

    sqlite3_close(db);
}

（4）SELECT 获取记录

方法一：sqlite3_exec()

#include "sqlite3.h"

#include <stdio.h>


/**
 * @brief sqlite3_exec() 的回调函数
 * @param data sqlite3_exec() 的第 4 个参数会传递到这里
 * @param argc SELECT 获取的记录的总列数
 * @param argv SELECT 获取的记录有多少行，callback() 就会被调用多少次，
 *              每一次调用传入一行记录到 argv，argv[0] 就是该行的第一列数据，
 *              argv[1] 就是改行的第二列数据......
 * @param col_name 列名，col_name[0] 为第一列的名字, col_name[1] 为第二列的名字......
 */
int callback(void *data, int argc, char **argv, char **col_name)
{
    (void)data;  // 未传入参数，这里不使用
    for (int i = 0; i < argc; ++i)
    {
        printf("%s=%s\n", col_name[i], argv[i]);
    }
    return 0;
}


int main()
{
    sqlite3 *db;  // 句柄
    if (sqlite3_open("test.db", &db) != SQLITE_OK)  // 打开数据库
    {
        fprintf(stderr, "打开数据库失败：%s\n", sqlite3_errmsg(db));
        return 1;
    }

    char *err_msg = NULL;  // 用于存放错误信息
    if (sqlite3_exec(db, "SELECT * FROM my_table", callback, NULL, &err_msg) != SQLITE_OK)  // 执行 SQL 语句
    {
        fprintf(stderr, "SQL 语句执行错误: %s\n", err_msg);
        sqlite3_free(err_msg);
    }

    sqlite3_close(db);
}

方法二：sqlite3_get_table()

#include "sqlite3.h"

#include <stdio.h>


int main()
{
    sqlite3 *db;  // 句柄
    if (sqlite3_open("test.db", &db) != SQLITE_OK)  // 打开数据库
    {
        fprintf(stderr, "打开数据库失败：%s\n", sqlite3_errmsg(db));
        return 1;
    }

    char *err_msg = NULL;  // 用于存放错误信息
    char **result = NULL;  // 用于存放查询结果
    int row, col;  // 用于存访查询结果的行列数
    if (sqlite3_get_table(db, "SELECT * FROM my_table", &result, &row, &col, &err_msg) != SQLITE_OK)  // 查询记录
    {
        fprintf(stderr, "SQL 语句执行错误: %s\n", err_msg);
        sqlite3_free(err_msg);
    }
    else
    {
        // 查询的结果会以一维数组的形式存访到 result 中
        // 查询记录有 col 列，则 result 的 [0，col）区间下标的元素为列名，即第一行（下标 0）
        // 行循环从下标 1 开始，即数据的开始位置是 i *col + j = 1 * col + 0 = col
        // row 本身是指查询数据的行数，没有计算列名那行，所以行循环下标截止位置是 row，而不是 row - 1
        for (int i = 1; i <= row; ++i)
        {
            for (int j = 0; j < col; ++j)
            {
                printf("%s=%s\n", result[j], result[i * col + j]);
            }
        }
    }

    sqlite3_free_table(result);
    sqlite3_close(db);
}