C语言操作SQLite3简明教程

SQLite3是个非常好用的数据库，官方介绍如下，

SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. SQLite is the most used database engine in the world. SQLite is built into all mobile phones and most computers and comes bundled inside countless other applications that people use every day.

本文主要讲述如何使用C语言去操作SQLite3，OS环境是Debian10，其它OS也是差不多的。

---

一 下载SQLite3

点击https://www.sqlite.org/download.html进入下载界面，下载3.35.5版本的源码，

下载后解压，

---

二 准备工程

使用CMake进行代码编译，先建立一个工程，结构如下，

CMakeLists.txt如下，

cmake_minimum_required (VERSION 3.5)

project(demo)

include_directories (sqlite)

add_executable(main ${PROJECT_SOURCE_DIR}/src/main.c ${PROJECT_SOURCE_DIR}/sqlite/sqlite3.c)
target_link_libraries (main pthread dl)

add_executable(sqliteShell ${PROJECT_SOURCE_DIR}/sqlite/shell.c ${PROJECT_SOURCE_DIR}/sqlite/sqlite3.c)
target_link_libraries (sqliteShell pthread dl)

运行时，先cd进入build目录，然后执行

cmake .. && make
./main

---

三 操作

本节主要讲述SQLite3的常用操作，获取版本号，CRUD等，从易入难，主要参考了这篇文章

1. 获取SQLite3版本号

main.c如下，

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


int main(void)
{
    printf("%s\n", sqlite3_libversion());

    return 0;
}

编译运行后，输出如下，

2. 创建表并插入数据

main.c如下，

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


int main(void) 
{
    sqlite3 *db = NULL;
    char *err_msg = NULL;

    int rc = sqlite3_open("test.db", &db);

    if (rc != SQLITE_OK)
    {
        fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        
        return 1;
    }

    const char *sql = "DROP TABLE IF EXISTS Cars;" 
                      "CREATE TABLE Cars(Id INT, Name TEXT, Price INT);" 
                      "INSERT INTO Cars VALUES(1, 'Audi', 52642);" 
                      "INSERT INTO Cars VALUES(2, 'Mercedes', 57127);" 
                      "INSERT INTO Cars VALUES(3, 'Skoda', 9000);" 
                      "INSERT INTO Cars VALUES(4, 'Volvo', 29000);" 
                      "INSERT INTO Cars VALUES(5, 'Bentley', 350000);" 
                      "INSERT INTO Cars VALUES(6, 'Citroen', 21000);" 
                      "INSERT INTO Cars VALUES(7, 'Hummer', 41400);" 
                      "INSERT INTO Cars VALUES(8, 'Volkswagen', 21600);";

    rc = sqlite3_exec(db, sql, NULL, NULL, &err_msg);
    if (rc != SQLITE_OK)
    {
        fprintf(stderr, "SQL error: %s\n", err_msg);
        sqlite3_free(err_msg);
        sqlite3_close(db);

        return 1;
    }

    sqlite3_close(db);

    return 0;
}

代码解释，

• 通过sqlite3_open()打开一个数据库文件，如果不存在就创建
• 通过sql来指定需要的SQL语句，这里的SQL语句意义是创建一个名叫Cars的表，有三列，第一列是车的Id，第二列是车名字，第三列是车的价格
• 通过sqlite3_exec()来执行这些SQL语句

编译运行后，生成test.db

写完之后，可以使用生成的sqliteShell去查看这个表，

3. 查看表中数据

主要通过SQL语句SELECT * FROM Cars来获取表中数据，然后通过回调去打印出来，

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

int callback(void *, int, char **, char **);


int main(void)
{
    sqlite3 *db = NULL;
    char *err_msg = NULL;

    int rc = sqlite3_open("test.db", &db);
    if (rc != SQLITE_OK) {
        
        fprintf(stderr, "Cannot open database: %s\n", 
                sqlite3_errmsg(db));
        sqlite3_close(db);
        
        return 1;
    }

    const char *sql = "SELECT * FROM Cars";

    rc = sqlite3_exec(db, sql, callback, NULL, &err_msg);
    if (rc != SQLITE_OK ) {
        
        fprintf(stderr, "Failed to select data\n");
        fprintf(stderr, "SQL error: %s\n", err_msg);

        sqlite3_free(err_msg);
        sqlite3_close(db);
        
        return 1;
    }

    sqlite3_close(db);

    return 0;
}

int callback(void *NotUsed, int argc, char **argv, char **azColName)
{
    NotUsed = NULL;

    for (int i = 0; i < argc; ++i)
    {
        printf("%s = %s\n", azColName[i], (argv[i] ? argv[i] : "NULL"));
    }

    printf("\n");

    return 0;
}

通过在sqlite3_exec()里传递回调去打印获取的数据，每调用一次callback就会打印一行数据，编译运行后如下，

4. 插入结构体数据

使用Blob类型进行存储，

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


typedef struct {
    int64_t time;
    int32_t value;
} myData;


int main(void)
{
    sqlite3 *db = NULL;
    char *err_msg = NULL;

    int rc = sqlite3_open("test.db", &db);
    if (rc != SQLITE_OK) {
        
        fprintf(stderr, "Cannot open database: %s\n", 
                sqlite3_errmsg(db));
        sqlite3_close(db);
        
        return 1;
    }
    
    // 创建名为Images的表
    const char *sql = "DROP TABLE IF EXISTS Images;" 
                      "CREATE TABLE Images(Id INTEGER PRIMARY KEY, Data BLOB);";

    rc = sqlite3_exec(db, sql, NULL, NULL, &err_msg);
    if (rc != SQLITE_OK ) {
        
        fprintf(stderr, "Failed to select data\n");
        fprintf(stderr, "SQL error: %s\n", err_msg);

        sqlite3_free(err_msg);
        sqlite3_close(db);
        
        return 1;
    }


    sqlite3_stmt *pStmt = NULL;
    const char *newSql = "INSERT INTO Images(Data) VALUES(?)"; // 向Data列插入新值
    rc = sqlite3_prepare(db, newSql, -1, &pStmt, NULL);
    if (rc != SQLITE_OK) {
        
        fprintf(stderr, "Cannot prepare statement: %s\n", sqlite3_errmsg(db));
        
        return 1;
    }

    myData data = {10000, 200}; // 准备要写入的值

    sqlite3_bind_blob(pStmt, 1, &data, sizeof(data), SQLITE_STATIC); // 绑定需要写入的值
    rc = sqlite3_step(pStmt);
    if (rc != SQLITE_DONE)
    {
        printf("execution failed: %s", sqlite3_errmsg(db));
    }

    sqlite3_finalize(pStmt);    

    sqlite3_close(db);

    return 0;

}

5. 读取插入的结构体值

读取第4步写入的结构体值，

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



typedef struct {
    int64_t time;
    int32_t value;
} myData;


int main(void)
{
    sqlite3 *db = NULL;
    char *err_msg = NULL;

    int rc = sqlite3_open("test.db", &db);
    if (rc != SQLITE_OK) {
        
        fprintf(stderr, "Cannot open database: %s\n", 
                sqlite3_errmsg(db));
        sqlite3_close(db);
        
        return 1;
    }

    char *sql = "SELECT Data FROM Images WHERE Id = 1";

    sqlite3_stmt *pStmt = NULL;
    rc = sqlite3_prepare_v2(db, sql, -1, &pStmt, NULL);
     if (rc != SQLITE_OK ) {
        
        fprintf(stderr, "Failed to prepare statement\n");
        fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
        
        sqlite3_close(db);
        
        return 1;
    } 
    
    rc = sqlite3_step(pStmt);

    int bytes = 0;

    if (rc == SQLITE_ROW)
    {
        bytes = sqlite3_column_bytes(pStmt, 0);
    }

    myData *pData = (myData*)sqlite3_column_blob(pStmt, 0);

    printf("bytes: %d, %lld, %d\n", bytes, pData->time, pData->value);

    rc = sqlite3_finalize(pStmt);   

    sqlite3_close(db);

    return 0;

}

执行，

6. 其它操作

其它操作都是类似，只要熟悉了SQL语句就可以。