Linux应用 sqlite3编程

1、概念

SQLite3是一个轻量级的、自包含的、基于文件的数据库管理系统，常用于移动设备、嵌入式设备和小型应用程序中，应用场景如下：

移动应用程序：由于SQLite3是零配置、无服务器的数据库引擎，非常适合用于移动应用程序中，如本地数据存储、离线访问等功能。
嵌入式设备：SQLite3的小型性和零配置特性使得它成为许多嵌入式设备中的理想选择，包括智能家居设备、物联网设备等。
小型应用程序：对于小型的桌面应用程序、小型网站等，SQLite3是一个不错的选择，因为它易于使用和部署。

2、常用命令

Ubuntu下使用apt-get install sqlite3安装。

2.1 创建数据库文件

直接使用sqlite3命令会将数据库创建在内存中，使用sqlite3 + 数据库名称可以将数据库文件创建到当前目录下，如果数据库文件已经存在则会打开：

2.2 创建表格

使用CREATE TABLE进行表格创建，对于以下示例：

CREATE TABLE users (id INTEGER PRIMARY KEY,name TEXT,age INTEGER);

CREATE TABLE users ：这部分语句表示要创建一个名为users的表。
id INTEGER PRIMARY KEY,：这部分定义了第一个列（或字段）id，数据类型为INTEGER，并且将其定义为主键（PRIMARY KEY）。主键是用来唯一标识每一行记录的字段，可以保证表中每一行的唯一性。
name TEXT,：这部分定义了第二个列name，数据类型为TEXT，用来存储文本字符串类型的数据。 age INTEGER: 这部分定义了第三个列age，数据类型为INTEGER，用来存储整数类型的数据。

在此SQL语句中，id列被定义为主键，所以它的值必须是唯一的，并且不能为空。其他列name和age可以包含空值（NULL）。

2.3 查看表格

.tables命令可以查看表格，.schema可以查看表结构：

2.4 插入数据

使用INSERT INFO进行数据插入：

INSERT INTO users (name, age) VALUES ('张三', 25);
INSERT INTO users (name, age) VALUES ('李四', 30);

2.5 数据查询

使用SELECT 进行数据查询：

2.6 数据更新

使用UPDATE 进行数据更新：

2.7 数据删除

使用DELETE 进行数据删除：

2.8 退出

使用.exit命令退出。

3、C语言编程常用接口

执行apt-get install libsqlite3-dev安装开发文件，libsqlite3-dev是SQLite数据库的C语言接口的开发文件。它包含了用于在C/C++程序中访问SQLite数据库的头文件和静态库文件。

开发接口在官网中有介绍：An Introduction To The SQLite C/C++ Interface

3.1 sqlite3_open

打开一个SQLite数据库文件。

原型：int sqlite3_open(const char *filename, sqlite3 **ppDb)
入参：filename 为数据库文件名，ppDb 是指向 sqlite3* 结构体指针的指针
返回值：返回 SQLITE_OK 表示成功，其它错误码表示失败

3.2 sqlite3_exec

执行一条SQL语句

原型：int sqlite3_exec(sqlite3* db, const char* sql, int (*callback)(void*,int,char**,char**), void* data, char** errmsg)
入参：db 为数据库指针，sql 为要执行的SQL语句，callback 为结果处理回调函数，data 为回调函数接受的参数
返回值：返回 SQLITE_OK 表示成功，其它错误码表示失败

回调函数的原型为 int (*callback)(void*, int, char**, char**)。具体来说：

void* 参数是通过 sqlite3_exec 的第四个参数传递给回调函数的自定义数据。
int 参数 argc 是结果集中返回的列数。
char** 参数 argv 是一个包含每一列的值的数组。
char** 参数 azColName 是一个包含每一列的列名的数组。
回调函数应该返回一个整数值，非零值表示需要终止查询，0 表示继续执行。

3.3 sqlite3_prepare_v2

编译 SQL 语句并准备好一个语句对象，以便稍后执行和绑定参数。

原型：int sqlite3_prepare_v2(sqlite3* db, const char* zSql, int nByte, sqlite3_stmt** ppStmt, const char** pzTail);
- db：SQLite 数据库对象。
- zSql：要编译的 SQL 语句。
- nByte：SQL 语句的长度（-1 表示自动计算长度）。
- ppStmt：返回的准备好的语句对象。
- pzTail：指向剩余未使用的部分（可选）。
返回值：如果 SQL 语句编译成功，则返回 SQLITE_OK，并将准备好的语句对象放入 ppStmt 中；如果出现错误，则返回对应的错误码。

3.4 sqlite3_step

用于执行准备好的语句对象，一次执行一步。

原型：int sqlite3_step(sqlite3_stmt* pStmt);
入参：准备好的语句对象。
返回值：如果执行成功，返回 SQLITE_ROW 或 SQLITE_DONE；如果出现错误，返回其他错误码。

3.5 sqlite3_bind_int

将整数值绑定到 SQL 语句中的参数位置。

原型：int sqlite3_bind_int(sqlite3_stmt* pStmt, int idx, int val);
- pStmt：准备好的语句对象。
- idx：参数的索引（1-based）。
- val：要绑定的整数值。
返回值：执行成功时返回 SQLITE_OK，否则返回对应的错误码。

3.6 sqlite3_finalize

用于释放准备好的语句对象。

原型：int sqlite3_finalize(sqlite3_stmt* pStmt);
入参：要释放的准备好的语句对象。
返回值：成功时返回 SQLITE_OK，失败时返回其他错误码

3.7 sqlite3_close

关闭一个打开的SQLite数据库

原型：int sqlite3_close(sqlite3*)
入参：数据库指针
返回值：返回 SQLITE_OK 表示成功，其它错误码表示失败

3.8 说明

sqlite3_finalize：释放由 sqlite3_prepare 或 sqlite3_prepare_v2 分配的准备语句对象。
sqlite3_free_table：释放通过 sqlite3_get_table 函数获得的结果表。
sqlite3_free_stmt：释放通过 sqlite3_prepare_v2 函数获得的语句对象。

理论上，可以说 sqlite3_exec 可以实现 sqlite3_prepare_v2 的功能，但是它们的设计和用途有所不同。

sqlite3_exec 主要用于执行一次性、不需要绑定参数的 SQL 语句操作，并且适合用于执行不返回结果集的 SQL 操作，比如创建表、插入数据、更新数据等。其优点在于简单直接，适用于简单的 SQL 操作。
sqlite3_prepare_v2 主要用于执行需要绑定参数、需要多次执行、或者需要获取结果集的 SQL 操作。通过预编译 SQL 语句，可以提高执行效率，并且可以使用参数绑定来防止 SQL 注入攻击。虽然在一些情况下可以通过拼接 SQL 语句并结合 sqlite3_exec 来模拟其功能，但不如 sqlite3_prepare_v2 灵活和安全。

4、编程示例

编写如下测试代码：

#include <sqlite3.h>
#include <stdio.h>

// 回调函数，用于在 sqlite3_exec 中处理查询结果
static int callback(void* NotUsed, int argc, char** argv, char** azColName) 
{
    for (int i = 0; i < argc; i++) 
    {
        printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
    }
    printf("\n");
    return 0;
}

int main() 
{
    sqlite3* db;
    char* errMsg = 0;

    // 打开数据库连接
    int rc = sqlite3_open("usr.db", &db);
    if (rc) 
    {
        fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
        return rc;
    }

    // 创建表
    const char* createTableSQL = "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER);";
    rc = sqlite3_exec(db, createTableSQL, NULL, 0, &errMsg);
    if (rc != SQLITE_OK) 
    {
        fprintf(stderr, "SQL error: %s\n", errMsg);
        sqlite3_free(errMsg);
        sqlite3_close(db);
        return rc;
    }

    // 插入数据
    const char* insertDataSQL = "INSERT OR IGNORE INTO users (name, age) VALUES ('zhao', 30), ('qian', 25), ('sun', 35);";
    rc = sqlite3_exec(db, insertDataSQL, NULL, 0, &errMsg);
    if (rc != SQLITE_OK) 
    {
        fprintf(stderr, "SQL error: %s\n", errMsg);
        sqlite3_free(errMsg);
        sqlite3_close(db);
        return rc;
    }

    // 使用 sqlite3_exec 查询所有数据
    const char* selectDataSQL = "SELECT * FROM users;";
    rc = sqlite3_exec(db, selectDataSQL, callback, 0, &errMsg);
    if (rc != SQLITE_OK) 
    {
        fprintf(stderr, "SQL error: %s\n", errMsg);
        sqlite3_free(errMsg);
        sqlite3_close(db);
        return rc;
    }

    // 使用 sqlite3_prepare_v2 查询数据
    sqlite3_stmt* stmt;
    const char* selectDataSQL2 = "SELECT * FROM users WHERE age > ?;";
    rc = sqlite3_prepare_v2(db, selectDataSQL2, -1, &stmt, NULL);
    if (rc != SQLITE_OK) 
    {
        fprintf(stderr, "SQL error: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        return rc;
    }

    // 检索年龄大于25的行
    int minAge = 25;
    sqlite3_bind_int(stmt, 1, minAge);

    while (sqlite3_step(stmt) == SQLITE_ROW)
    {
        printf("id = %d, name = %s, age = %d\n", sqlite3_column_int(stmt, 0), sqlite3_column_text(stmt, 1), sqlite3_column_int(stmt, 2));
    }

    sqlite3_finalize(stmt);

    // 关闭数据库连接
    sqlite3_close(db);

    return 0;
}

测试结果如下：