SQLite入门

一、SQLite3环境搭建

1、下载

在官网Download Page下载相应的源码及动态库文件，如下图所示：

DLL文件可以根据需求下载32位或64位。将64位DLL文件都解压到E：SQLite-lib-64（我的）文件夹。

2、生成lib文件

方法一：
1）在VS安装目录下找到link.exe所在目录。我的目录是：

E:\Program Files (x86)\Microsoft Visual Studio\2019\Community\VC\Tools\MSVC\14.24.28314\bin\Hostx64\x64

将以上路径添加到系统环境变量中。
2）win+r->cmd打开dos终端，输入

cd /d E：SQLite-lib-64 // 切换到解压的文件夹
LIB /DEF:sqlite3.def /MACHINE:X64

生成32位文件可输入

cd /d E：SQLite-lib-32 // 切换到解压的文件夹
LIB /DEF:sqlite3.def /MACHINE:IX86

在E：SQLite-lib-64文件夹中可看到sqlite3.lib文件。

方法二：
1）在VS安装目录下找到link.exe所在目录。我的目录是：

E:\Program Files (x86)\Microsoft Visual Studio\2019\Community\VC\Tools\MSVC\14.24.28314\bin\Hostx64\x64

。
将以下文件复制到E：SQLite-lib-64文件夹。

2）win+r->cmd打开dos终端，输入

cd /d E：SQLite-lib-64 // 切换到解压的文件夹
LIB /DEF:sqlite3.def /MACHINE:X64

生成32位文件可输入

cd /d E：SQLite-lib-32 // 切换到解压的文件夹
LIB /DEF:sqlite3.def /MACHINE:IX86

在E：SQLite-lib-64文件夹中可看到sqlite3.lib文件。

3、在项目中添加SQLite3

1）将sqlite3.h，sqlite3ext.h，sqlite3.c以及sqlite3.lib和sqlite3.dll文件拷贝至测试项目下。
2）项目右键->添加->现有项将sqlite3.h，sqlite3ext.h，sqlite3.c添加到项目中。
3）项目右键->配置，改为活动（Debug）。
4）项目右键->链接器->常规->附加库目录中把sqlite3.h文件所在路径放上去。
5）项目右键->链接器->输入-附加依赖项中加入sqlite3.lib。
6）在源文件中添加头文件并引入lib文件。

#include "sqlite3.h"
#pragma comment(lib, "sqlite3.lib")

至此，完成了VS下使用sqlite3的环境配置。

二、SQLite3简单应用

1、SQLite3 API

1）打开数据库

int sqlite3_open(
  const char *filename,   /* Database filename (UTF-8) */
  sqlite3 **ppDb          /* OUT: SQLite db handle */
);
filename：需要被打开的数据库文件的文件名，采用UTF-8编码。
ppDb：一个数据库指针句柄被返回到这个参数，即使发生错误。唯一的异常是如果sqlite不能分配内存来存放sqlite对象，ppDb将会被返回一个NULL值。
返回值：成功返回 SQLITE_OK，失败返回错误码，可以使用sqlite3_errmsg函数获取错误信息。

int sqlite3_open16(
  const void *filename,   /* Database filename (UTF-16) */
  sqlite3 **ppDb          /* OUT: SQLite db handle */
);
功能：打开或创建数据库文件，返回句柄。
filename：需要被打开的数据库文件的文件名，采用UTF-16编码。
ppDb：同上。
返回值：成功返回 SQLITE_OK，失败返回错误码，可以使用sqlite3_errmsg函数获取错误信息。

int sqlite3_open_v2(
  const char *filename,   /* Database filename (UTF-8) */
  sqlite3 **ppDb,         /* OUT: SQLite db handle */
  int flags,              /* Flags */
  const char *zVfs        /* Name of VFS module to use */
};
功能：打开或创建数据库文件，返回句柄。
filename：采用UTF-8编码的数据库文件名。
ppDb：同上。
flags：作为数据库连接的额外控制的参数。可以是以下值之一：
SQLITE_OPEN_READONLY：只读方式打开数据库。
SQLITE_OPEN_READWRITE：读写方式打开数据库。
SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE：如果数据库不存在，则创建一个新的数据库并以读写方式打开。
这些标志可以和以下标志结合使用：
SQLITE_OPEN_NOMUTEX：设置数据库连接运行在多线程模式(没有指定单线程模式的情况下)
SQLITE_OPEN_FULLMUTEX：设置数据库连接运行在串行模式
SQLITE_OPEN_SHAREDCACHE：设置运行在共享缓存模式
SQLITE_OPEN_PRIVATECACHE：设置运行在非共享缓存模式
zVfs：用于指定要使用的VFS模块的名称。如果此参数为NULL，则使用默认的VFS模块。
返回值：成功返回 SQLITE_OK，失败返回错误码，可以使用sqlite3_errmsg函数获取错误信息。
Note:
(1) filename 如果为 ":memory:" 则表示在内存中创建一个私有、临时的数据库，在数据库连接close之后会被清除；当你的数据库名称有以 ":" 作为前缀时，最好加上数据库路径名称以防止引起歧义；
(2) filename 如果为是一个空字符串，则表示在硬盘中创建一个私有、临时的数据库，在数据库连接close之后会被删除；

2）关闭数据库

int sqlite3_close(sqlite3 *pDb);
功能：关闭数据库文件。
ppDb：数据库文件句柄。
返回值：成功返回 SQLITE_OK，失败返回错误码，可以使用sqlite3_errmsg函数获取错误信息。

3）准备-执行-完成

// 准备语句 
int sqlite3_prepare_v2(
  sqlite3 *db,            /* Database handle */
  const char *zSql,       /* SQL statement, UTF-8 encoded */
  int nByte,              /* Maximum length of zSql in bytes. */
  sqlite3_stmt **ppStmt,  /* OUT: Statement handle */
  const char **pzTail     /* OUT: Pointer to unused portion of zSql */
);
功能： 用于构造准备语句对象;
db：数据库连接句柄；
zSql：将要被编译的语句；
nByte：表示zSql的长度，如果为-1则表示读取直到字符串结束； 如果 pzTail 不为NULL，由于sqlite3_prepare_v2 只会编译 zSql 中的第1条语句，则pzTail 会被用来指向 zSql 中剩余未被编译的语句的首字节；
pzTail：指向SQL语句中未使用的部分，一般传NULL；
ppStmt：指向编译过的准备语句，可以直接被 sqlite3_step 执行；如果发生错误则ppStmt 为NULL；注意，调用程序需要负责删除 ppStmt ，可以通过调用 sqlite3_finalize 实现；
函数返回：打开成功则返回SQLITE_OK；否则返回错误码，通过sqlite3_errmsg可以获取到错误信息；

// 执行 
int sqlite3_step(sqlite3_stmt *pStmt);
功能：执行 sqlite3_prepare_v2 产生的准备语句pStmt，sqlite3_step会被执行一次或多次以求pStmt的值；
函数说明：sqlite3_step 的行为取决语句于pStmt如何产生，假如是使用老版本的接口sqlite3_prepare()和sqlite3_prepare16()，返回值会是 SQLITE_BUSY， SQLITE_DONE， SQLITE_ROW， SQLITE_ERROR或 SQLITE_MISUSE，而v2版本的接口sqlite3_prepare_v2()和sqlite3_prepare16_v2()则会同时返回这些结果码和扩展结果码。
Note：
对所有V3.6.23.1以及其前面的所有版本，需要在sqlite3_step()之后调用sqlite3_reset()，在后续的sqlite3_step()之前。如果调用sqlite3_reset()重置准备语句失败，将会导致sqlite3_step返回SQLITE_MISUSE，但是在V3.6.23.1以后，sqlite3_step()将会自动调用sqlite3_reset()。

// 完成
int sqlite3_finalize(sqlite3_stmt *pStmt);
功能：删除准备语句pStmt；可以在pStmt的任何生命周期使用；注意，不要使用任何已经被finalize 的准备语句pStmt；

4）使用参数化SQL

参数化SQL：在 sqlite3_prepare_v2 接口中传入的 zSql 语句，可以被遵从以下模板的参数替换：
?            // 表示任意参数
?NNN         // NNN表示integer
:VVV         // VVV表示一个字母数字标识符
@VVV
$VVV
? : @ $ 是占位符，参数值的类型需要与占位符的类型匹配。
// 部分绑定函数接口
参数化SQL 优点：
1. 参数就是占位符，可在编译后提供绑定。
2. 参数绑定的优点是无需重新编译，即可多次执行相同的语句。只需重置该语句、绑定新值，并重新执行。使用重置函数可以避免SQL编译的开销。完全避免此法分析、语法分析和代码生成开销。通过调用sqlite3_reset()实现重置。
3. 另一个优点是SQLite会处理绑定到参数的转义字符。可避免语法错误和可能的SQL注入式攻击。
4. sqlite3_reset()只释放语句资源，会保持VDBE字节代码及其参数不变，sql语句无需再次调用prepare即可再次执行。

int sqlite3_bind_double(sqlite3_stmt*, int, double);
int sqlite3_bind_int(sqlite3_stmt*, int, int);
int sqlite3_bind_text(sqlite3_stmt*,int,const char*,int,void(*)(void*));
int sqlite3_bind_value(sqlite3_stmt*, int, const sqlite3_value*);
int sqlite3_bind_parameter_index(sqlite3_stmt*, const char *zName);

第一个参数：指向sqlite3_prepare_v2（）或其变体返回的sqlite3_stmt对象的指针
第二个参数：要设置的SQL参数的索引。最左边的SQL参数的索引为1.当同一个命名的SQL参数被多次使用时，第二次和后续出现的索引与第一次出现的索引相同。如果需要，可以使用sqlite3_bind_parameter_index（） API查找命名参数的索引 。“？NNN”参数的索引是NNN的值。NNN值必须介于1和sqlite3_limit（） 参数SQLITE_LIMIT_VARIABLE_NUMBER之间（默认值：999）。
第三个参数：绑定到参数的值。
如果sqlite3_bind_text（）或sqlite3_bind_text16（）或sqlite3_bind_blob（）的第三个参数是NULL指针，则忽略第四个参数，最终结果与sqlite3_bind_null（）相同。
第四个参数：针对有这个参数的函数而言，其值是参数中的字节数。要清楚：值是字节数在值中，而不是字符数。
如果sqlite3_bind_text（）或sqlite3_bind_text16（）的第四个参数为负，则字符串的长度是直到第一个零终止符的字节数。
如果向sqlite3_bind_text（）或sqlite3_bind_text16（）或sqlite3_bind_text64（）提供了非负第四个参数，则该参数必须是假定字符串NUL终止时NUL终结符将发生的字节偏移量，如果在字节偏移小于第四个参数的值时出现任何NUL字符，则结果字符串值将包含嵌入的NUL。涉及具有嵌入式NUL的字符串的表达式的结果是未定义的。
如果sqlite3_bind_blob（）的第四个参数为负数，则行为未定义。
第五个参数：针对有第五个参数的blob而言。是一个析构函数，用于在SQLite完成后处理BLOB或字符串。即使对绑定API的调用失败，也会调用析构函数来处理BLOB或字符串，但如果第三个参数是NULL指针或第四个参数为负，则不会调用析构函数。如果第五个参数是特殊值SQLITE_STATIC，那么SQLite假定信息在静态的非托管空间中，并且不需要释放。如果第五个参数的值为SQLITE_TRANSIENT，则SQLite会在sqlite3_bind _ *（）例程返回之前立即创建自己的数据私有副本。    
第六个参数：指定编码
sqlite3_bind_text64（）的第六个参数必须是 SQLITE_UTF8，SQLITE_UTF16，SQLITE_UTF16BE或SQLITE_UTF16LE之一， 以指定第三个参数中文本的编码。如果sqlite3_bind_text64（）的第六个参数不是上面显示的允许值之一，或者如果文本编码与第六个参数指定的编码不同，则行为未定义。

注意：
1、在批量插入数据时，执行一次sqlite3_prepare_v2比sqlite3_step消耗的时间多得多，因此应该尽量避免重复调用sqlite3_prepare_v2。在我们的实现中，如果想避免此类开销，只需将待插入的数据以变量的形式绑定到SQL语句中，这样该SQL语句仅需调用sqlite3_prepare_v2函数编译一次即可，其后的操作只是替换 不同的变量数值。
2、SQL语句"insert into testtable values(?)"中的问号(?)表示参数变量的占位符，该规则在很多关系型数据库中都是一致的，因此这对于数据库移植操作还是比较方便的。

//使用sqlite3_prepare_v2(), sqlite3_bind_...() 插入数据
const char* pzTail;
sqlite3_stmt *stmt;
ret = sqlite3_prepare_v2(pDb, "insert into myTable(id,name) values(?,:para)", -1, &stmt, &pzTail);
assert(SQLITE_OK == ret);
for (int i = 0; i < 3; i++)
{
	sqlite3_bind_int(stmt, 1, i);
	char text[50] = {0};
	sprintf(text, "yangxt%d", i);
	sqlite3_bind_text(stmt, sqlite3_bind_parameter_index(stmt, ":para"), text, -1, NULL);
	sqlite3_step(stmt);
}
sqlite3_finalize(stmt);

5）格式化SQL语句

char *sqlite3_mprintf(const char*,...);
char *sqlite3_vmprintf(const char*, va_list);
char *sqlite3_snprintf(int,char*,const char*, ...);
char *sqlite3_vsnprintf(int,char*,const char*, va_list);

sqlite3_mprintf（）的作用是将结果写入到sqlite3_malloc()获取的内存中，例程返回的字符串应该使用sqlite3_free（）进行释放，如果无法申请到足够的内存，则会返回NULL指针；
它同c库函数 sprintf（）类似，实现一些额外的格式化。对所有常用的printf（）格式化选项都适用。另外还有非常用选项：%q， %Q， %z；
％q选项的作用类似于％s，它会替换了参数列表中以空字符结尾的字符串%q，同时他会将单引号字符转义，有助于防止SQL注入攻击；

示例：
char * zText =“这是快乐的一天！”;
char * zSQL = sqlite3_mprintf（“INSERT INTO table VALUES（'％q'）”，zText）;
sqlite3_exec（db，zSQL，0,0,0）;
sqlite3_free（ZSQL）;
作为一般规则，在将文本插入字符串文字时，应始终使用％q而不是％s。
当需要插入的字符为NULL时，%s 并没有将NULL插入到表中，但是%q， %Q能够将NULL插入到表中，只是大小写不一样；

6）一步到位的函数接口

int sqlite3_exec( sqlite3* , //指向数据库连接对象
                  const char *sql , //指向要执行的SQL语句,一般不带参数。
                  int (*callback) (void *, int, char **, char **), //回调函数
                  void *arg, //这个函数将作为callback的第一个参数传入
                  char **errmsg //用来保存出错的信息        
                );
第一个参数，数据库连接对象 
第二个参数，要执行的sql语句，可以执行多条语句以;分开
第三个参数，函数指针，回调函数。一般在sql语句为select语句时，需要回调。每查询到一条结果时(一行),就调用该回调函数。
int (*callback)(
                  void *,     //sqlite3_exec的第四个参数
                  int,    //结果中有多少列 
                  char **, // char *column_values[],这一行每列的值(转化成字符串)
                  char **  //char *column_names[],这一行每列的名字(字符串)
           ), 
回调函数的返回值: 返回0表示成功，其他值表示失败，回调函数执行失败了(返回非0值),sqlite3_exec就不执行下面的语句了。
第四个参数:将作为回调函数的第一个参数.
第五个参数: *errmsg将保存执行过程中的错误信息。
返回值:  成功返回0， 失败返回其他值。
Note:
（1）sql的命令当编程创建数据库文件时时可能会重复创建，所以在前面一般加上if not exists(例："create table if not exists info (学号 interger primary key, 姓名 char(255), 性别 char(32), 年龄 interger);")
（2）每找到一次符合的数据会调用一次callback；
（3）应用程序应该要保证传给sqlite3_exec的第1个参数为成功打开和连接的数据库句柄，且在sqlite3_exec执行过程中不被关闭；
（4）应用程序应该要保证传给sqlite3_exec的第2个参数的sql语句在sqlite3_exec执行过程中不会被修改；

7）查询结果分析函数

若sql语句为SELECT语句，它的返回结果是一个结果表，则需要用额外的函数接口去获取这些结果表中的记录。

若调用sqlite3_step函数则需要判断返回值，并对执行结果表一行一行的处理，直到返回值为SQLITE_DONE为止。
int sqlite3_column_count(sqlite3_stmt *pStmt);   //返回结果行中有多少列        
int sqlite3_column_type(sqlite3_stmt *pStmt, int iCol);  //返回结果行中第iCol列的数据类型
返回值: SQLITE_INTEGER :整数类型
        SQLITE_FLOAT: 浮点类型
        SQLITE_TEXT: 文本类型，char *
        SQLITE_BLOB: blob 二进制类型
        SQLITE_NULL: 空类型
判断出类型后，调用如下相应类型的接口获取结果集中当前行中第iCol列的值。
switch (sqlite3_column_type(stmt, 0)) {
    case SQLITE_INTEGER:
        int intValue = sqlite3_column_int(stmt, 0);
        // 处理整型值
        break;
    case SQLITE_FLOAT:
        double doubleValue = sqlite3_column_double(stmt, 0);
        // 处理浮点型值
        break;
    case SQLITE_TEXT:
        const unsigned char *textValue = sqlite3_column_text(stmt, 0);
        // 处理字符串值
        break;
    case SQLITE_BLOB:
        const void *blobValue = sqlite3_column_blob(stmt, 0);
        int blobSize = sqlite3_column_bytes(stmt, 0);
        // 处理二进制值
        break;
    case SQLITE_NULL:
        // 处理空值
        break;
    default:
        // 处理其他情况
        break;
}

使用sqlite3_get_table 函数直接获取整个结果表
int sqlite3_get_table(
  sqlite3 *db,          /* An open database */
  const char *zSql,     /* SQL to be evaluated */
  char ***pazResult,    /* Results of the query */
  int *pnRow,           /* Number of result rows written here */
  int *pnColumn,        /* Number of result columns written here */
  char **pzErrmsg       /* Error msg written here */
);
第1个参数为数据库连接对象。
第2个参数是sql 语句，跟sqlite3_exec 里的sql 是一样的。是一个很普通的以\0结尾的char*字符串。
第3个参数是查询结果（可理解为二维数组的地址）。
// 假设数据库表如下所示：
Name        | Age
-----------------------
Alice       | 43
Bob         | 28
Cindy       | 21
 
// pazResult 数组存放格式如下（column (M==2) rows (N==3)）：
azResult[0] = "Name";
azResult[1] = "Age";
azResult[2] = "Alice";
azResult[3] = "43";
azResult[4] = "Bob";
azResult[5] = "28";
azResult[6] = "Cindy";
azResult[7] = "21";
第4个参数是查询出多少条记录（即查出多少行，不包括字段名那行）。
第5个参数是多少个字段（多少列）。
第6个参数是错误信息
pazResult返回的字符串数量实际上是(*pnRow+1)*(*pnColumn),因为前(*pnColumn)个是字段名
Note:
通常用于执行返回数据的查询。可查询多个表，会返回完整的结果集。优点是一步就可执行查询并获得结果。缺点是它将结果完全存储在内存中。
sqlite3_get_table 被实现为 sqlite3_exec 的封装，不同的是sqlite3_get_table 没有调用到任何sqlite3内部数据结构， 而仅仅只是通过调用sqlite3 提供的对外接口来实现功能，故而如果sqlite3_get_table在内部调用的 sqlie3_exec 发生错误时，错误信息无法通过sqlite3_errcode/sqlite3_errmsg获取；

使用sqlite3_get_table 函数后，需要使用对应的sqlite3_free_table函数来释放查询结果所申请的内存。函数原型：
void sqlite3_free_table(char **result); //释放掉查询结果申请的内存空间

2、SQLite3 使用示例(数据表 创建/插入/查询，整型/字符串/时间/浮点数据 处理)

1）打开 / 创建 / 关闭 数据库

sqlite3* sqlite3_open_database(char* db_name)
{
    int rc = 0;  
    sqlite3* db;
    
    if (db_name == NULL || strlen(db_name) == 0 ) {
        return NULL;
    } 
    
    rc = sqlite3_open(db_name, &db);
    if (rc != SQLITE_OK) {
        printf("Open db error :%s", sqlite3_errmsg(db));
        return NULL;
    }
 
    return db;
}

void sqlite3_close_database(sqlite3 *db)
{
	if (db != NULL) {
		sqlite3_close(db);
	}
}

2）检查表 / 创建表 / 删除表

int sqlite3_IsExist_table(sqlite3* db, char* table_name)
{
    int rc = 0;
    int ret = -1;
    char sql_str[128] = {0}；
    char* err_msg = NULL;
 
    sprintf(sql_str, "SELECT * FROM %s", table_name);
    rc = sqlite3_exec(db, sql_str, 0, 0, &err_msg);
    if (rc != SQLITE_OK) {
        ret = -1;    /* 表不存在*/
        sqlite_free(err_msg);
    } else {
        ret = 0;     /* 表存在*/
    }
 
    return ret;
}

int sqlite3_Create_table(sqlite3* db, char* table_name)
{
    int rc = 0;
    int ret = -1;
    char sql_str[128] = {0};
    char* err_msg = NULL;
    
    /* 字段：ID Data RealDate*/
    sprinft(sql_str, "CREATE TABLE IF NOT EXISTS %s (""ID INTEGER PRIMARY KEY AUTOINCREMENT, ""Data TEXT, ""RealDate DATETIME)", table_name);
 
    rc = sqlite3_exec(db, sql_str, 0, 0, err_msg);
    if (rc != SQLITE_OK) {
        printf("Create table %s error :%s", table_name, err_msg);
        sqlite3_free(err_msg);
        ret = -1;
    } else {
        ret = 0;
    }
 
    return ret;
}

int sqlite3_Delete_table(sqlite3* db, char* table_name)
{
    int rc = 0;
    int ret = -1;
    char sql_str[128] = {0};
    char* err_msg = NULL;
    
    sprinft(sql_str, "DROP TABLE %s", table_name);
 
    rc = sqlite3_exec(db, sql_str, 0, 0, err_msg);
    if (rc != SQLITE_OK) {
        printf("Delete table %s error :%s", table_name, err_msg);
        sqlite3_free(err_msg);
        ret = -1;
    } else {
        ret = 0;
    }
 
    return ret;
}

3）判断数据是否存在（某字段重复）

int IsExist_cal_handle(void *flag, int argc, char** argv, char **colname)
{
    int i = *(int*)(flag);
    *(int *)(flag) = i + 1;
    return 0;
}
 
int sqlite3_IsExist_field(sqlite3* db, char* table_name, char* data_buf)
{
    int ret = -1;
    int rc = 0;
    char sql_str[128] = {0};
    char* err_msg = NULL;
    int flag = 0;    /* 用于回调函数计数*/
 
    sprintf(sql_str, "Select * FROM %s WHERE Data = '%s'", table_name, data_buf); 
    rc = sqlite3_exec(db, sql_str, IsExist_cal_handle, &flag, &err_msg);
    if (rc != SQLITE_OK) {    /* 查询失败*/
        printf("Check data exist error :%s", err_msg);
        return ret;
    } else {
        if (flag == 0 ) {     /* 结果不存在*/
            printf("Check data Not exist");
            return flag;
        } else {              /* 结果存在*/
            printf("Check data exist, num is %d", flag);
            return flag;
        }
    }
}

4）添加数据

/* 添加单条数据 利用sqlite3_exec*/
int sqlite3_insert_single(sqlite3* db, char* table_name, char* data_buf)
{
    int rc = 0;
    int ret = -1;
    char sql_str[128] = {0};
    char* err_msg = NULL;
    
    sprintf(sql_str, "INSERT INTO %s (ID, Data, RealDate) values(NULL, '%s', DATETIME('now', 'localtime'))", table_name, data_buf);
    
    sqlite3_busy_timeout(db, 30*1000);
    rc = sqlite3_exec(db, sql_str, 0, 0, &err_msg);
    if (rc != SQLITE_OK) {
        printf("Insert error :%s", err_msg);
        sqlite3_free(err_msg);
        ret = -1;
    } else {
        ret = 0;
    }
 
    return ret;
}

/* 添加多条数据, 利用显示事务机制 利用sqlite3_exec*/
/* 依次插入数据,若出现插入失败,则回滚事务;若全部成功,则提交事务*/
int sqlite3_Insert_multidata(sqlite3* db, const char* table_name, char* *buff, int len)
{
    int rc = 0；
    int ret = -1;
    int i = 0;
    char sql_str[128] = {0};
    char* err_msg = NULL;
    
    rc = sqlite3_exec(db, "BEGIN;", 0, 0, 0);
    if (rc != SQLITE_OK) {
        return ret;
    }
 
    while (buff[i] != NULL && i < len) {
        memset(sql_str, 0, sizeof(sql_str));
        sprintf(sql_str, "INSERT INTO %s (ID, Data, RealDate) values(NULL, '%s', DATETIME('now', 'localtime'))", table_name, buff[i]);
        sqlite3_busy_timeout(db, 30*1000);
        rc = sqlite3_exec(db, sql_str, 0, 0, &err_msg);
        if (rc != SQLITE_OK) {
            printf("Insert No%d data error: %s", i + 1, err_msg);
            sqlite3_free(err_msg);
            break;
        } else {
            i++;
        }
    }
    if (i < len) {
        sqlite3_exec(db, "ROLLBACK;", 0, 0, 0);
        ret = -1;
    } 
    if (i == len) {
        sqlite3_exec(db, "COMMIT;", 0, 0, 0);
        ret = 0;
    }
 
    return ret;
}

/* 添加多条数据, 利用sqlite3_prepare*/
int sqlite3_Insert_multidata_v2 (sqlite3* db, const char* table_name, char* *buff, int len)
{
    int rc = 0；
    int ret = -1;
    int i = 0;
    char sql_str[128] = {0};
    char* err_msg = NULL;
    sqlite3_stmt* stmt;
    
    sprintf(sql_str, "INSERT INTO %s (ID, Data, RealDate) values (?, ?, DATETIME('now', 'localtime'))");
    
    sqlite3_exec(db, "BEGIN;", 0, 0, 0);
    rc = sqlite3_prepare(db, sql_str, strlen(sql_str), &stmt, 0);
    if (rc != SQLITE_OK) {
        return ret;
    }
    while(buff[i] != NULL && i < len) {
        sqlite3_bind_int(stmt, 1, NULL);
        sqlite3_bind_text(stmt, 2, buff[i], strlen(buff), NULL);
        rc = sqlite3_step(stmt);
        if ((rc != SQLITE_OK) && (rc != SQLITE_DONE)) {
            break;
        }
        sqlite3_reset(stmt);
        i++;
    }
    sqlite3_finalize(stmt);
    if (i < len) {
        sqlite3_exec(db, "ROLLBACK;", 0, 0, 0);
        ret = -1;
    } 
    if (i == len) {
        sqlite3_exec(db, "COMMIT;", 0, 0, 0);
        ret = 0;
    }
 
    return ret;
}

5）更新数据

/* 更新单条数据 利用sqlite3_exec*/
int sqlite3_Update_single(sqlite3* db, char* table_name, char* data_buf)
{
    int rc = 0;
    int ret = -1;
    char sql_str[128] = {0};
    char* err_msg = NULL;
    
    sprintf(sql_str, "UPDATE %s SET RealDate = DATETIME('now', 'localtime') WHERE Data = '%s'", table_name, data_buf);
    
    sqlite3_busy_timeout(db, 30*1000);
    rc = sqlite3_exec(db, sql_str, 0, 0, &err_msg);
    if (rc != SQLITE_OK) {
        printf("Update error :%s", err_msg);
        sqlite3_free(err_msg);
        ret = -1;
    } else {
        ret = 0;
    }
    return ret;
}

/* 更新多条数据----事务法 利用sqlite3_exec*/
int sqlite3_Update_multidata(sqlite3* db, const char* table_name, char* *buff, int len)
{
    int rc = 0；
    int ret = -1;
    int i = 0;
    char sql_str[128] = {0};
    char* err_msg = NULL;
    
    rc = sqlite3_exec(db, "BEGIN;", 0, 0, 0);
    if (rc != SQLITE_OK) {
        return ret;
    }
 
    while (buff[i] != NULL && i < len) {
        memset(sql_str, 0, sizeof(sql_str));
        sprintf(sql_str, "UPDATE %s SET RealDate = DATETIME('now', 'localtime') WHERE Data = '%s'", table_name, buff[i]);
        sqlite3_busy_timeout(db, 30*1000);
        rc = sqlite3_exec(db, sql_str, 0, 0, &err_msg);
        if (rc != SQLITE_OK) {
            printf("Update No%d data error: %s", i + 1, err_msg);
            sqlite3_free(err_msg);
            break;
        } else {
            i++;
        }
    }
    if (i < len) {
        sqlite3_exec(db, "ROLLBACK;", 0, 0, 0);
        ret = -1;
    } 
    if (i == len) {
        sqlite3_exec(db, "COMMIT;", 0, 0, 0);
        ret = 0;
    }
 
    return ret;
}

6）删除数据

原理同上

7）查询数据

/* 回调函数, 返回单次执行结果*/
int exec_handle(void *data, int argc, char** argv, char **colname)
{
    /* 计数器*/
	int i = *(int *)(data);
	*(int *)(data) = i + 1;
 
    /* 取出结果*/
    printf("NO.%d message: [%s] is [%s], [%s] is [%s]...", *(int*)(data), colname[0], colname[1], argv[0], argv[1]);
	return 0;
}
 
/* exec查找完成无论是否有结果返回的都是SQLITE_OK, 所以判断查询结果得用计数器判断*/
int sqlite3_select_exec (sqlite *db, char *table_name)
{
    char sql[MAX_INPUT] = {0};
    char *err_msg = NULL;
    int data = 0;
    int ret;
 
    sprintf(sql, "select * from %s Where ID > 10", table_name);
    sqlite3_busy_timeout(db, 30*1000);
    ret = sqlite3_exec(db, sql, exec_handle, &data, &err_msg);
    if (ret != SQLITE_OK) {
        /* 查询失败*/
        printf("exec error is:%s\n", err_msg);
        return -1;
    } else {
        if (data == 0) {
            /* 查询成功，无查询结果*/
            return 0;
        } else {
            /* 查询成功，有查询结果*/
            return 1;
        }
    }
}

/* 执行sql, 将结果集的某一字段进行存储 利用sqlite3_get_table*/
int sqlite3_select_table(sqlite3* db, char* table_name)
{
    int ret = 0;
    char sql_str[MAX_INPUT] = {0};
    char **res;
    char **data;
    int* Idata;
    int row, col;
    char* err_msg = NULL;
    int i, j;
    
    /* 执行sql, 判断执行结果*/
    sprintf(sql_str, "select * from %s", table_name);
    sqlite3_busy_timeout(db, 30*1000);
    ret = sqlite3_get_table(db, sql_str, &res, &row, &col, &err_msg);
    if (ret != SQLITE_OK) {
        printf("error msg is %s", err_msg);
        sqlite3_close_database(db);
        sqlite3_free(err_msg);
        return -1;
    } else if (row == 0 || col == 0){
        sqlite3_close_database(db);
        return 0;
    } 
    
    /* 初始化动态数组*/
    Idata = (int*)malloc(sizeof(int) * (row + 1));
    data = (char **)malloc(sizeof(char *) * (row + 1));
    if (data == NULL) {
        /* fails to malloc */
        sqlite3_close_database(db);
        return -1;
    } else {
        for (i = 0; i <= row; i++) {
            data[i] = (char *)malloc(1024);
            if (data[i] == NULL) {
                /* fails to malloc*/
                sqlite3_close_database(db);
                return -1;
            } else {
                memset(data[i], 0, 1024);
            }
        }
    }
    
    /* 接收返回数据*/
    for (j = 1; j <= row; j++) {
        /* 接收字符数据*/
        strcpy(data[j], res[col * (row + 1) - 1]);
        /* 接收整型数据*/
        Idata[j] = atoi(res[col * (row + 1) - 1]);
    }
    
    /* 释放空间*/
    sqlite3_free_table(res);
    for (i = 0; i<= row; i++) {
        memset(data[i], 0, 1024);
        if (data[i] != NULL) {
            free(data[i]);
        }
    }
    free(data);
    free(Idata);
    sqlite3_close_database(db);
    return 1;
}

/* 利用sqlite3_prepare_v2 */
int sqlite3_select_prepare(sqlite3* db)
{
    int ret, rc, i, j;
	int send_flag = 0;
	int record_num;
	char package_id[16] = {0};
	int msg_len;
	char db_name[32] = "/data/device_property";
	char db_table[32] = "device";
	char sql_str[128] = {0};
	char* err_msg = NULL;
	char* *res_data;
	struct deviceNumber* *device;
	sqlite3* db;
	sqlite3_stmt *stmt;
 
	record_num = sqlite3_get_record_num(db_name, db_table);
	if (record_num == 0) {
		return -1;
	}
 
	db = sqlite3_open_database(db_name);
	if (db == NULL) {
		return -1;
	}
 
	memset(sql_str, 0, sizeof(sql_str));
	sprintf(sql_str, "select * from %s", db_table);
	rc = sqlite3_prepare_v2(db, sql_str, sizeof(sql_str), &stmt, NULL);
	if (rc != SQLITE_OK) {
		DEBUG_INFO("[read sqlite3 device] Select error");
		sqlite3_close_database(db);
		return -1;
	}
 
	/* 申请内存*/
        /* 字符串数组*/
	res_data = (char**)malloc(sizeof(char*) * record_num);
	if (res_data == NULL) {
		DEBUG_INFO("[read sqlite3 device] malloc error");
		sqlite3_close_database(db);
		return -1;
	}
	for (i = 0; i < record_num; i++) {
		res_data[i] = (char*)malloc(MAX_DEVICE_PROPERTY_SIZE);
		if (res_data[i] == NULL) {
			sqlite3_close_database(db);
			DEBUG_INFO("[read sqlite3 device] malloc2 error");
			return -1;
		}
	}
        /* 结构体内存*/
	device = (struct deviceNumber**)malloc(sizeof(struct deviceNumber) * record_num);
	if (device == NULL) {
		DEBUG_INFO("[read sqlite3 device malloc error]");
		sqlite3_close_database(db);
		return -1;
	}
	for (i = 0; i < record_num; i++) {
		device[i] = (struct deviceNumber*)malloc(sizeof(struct deviceNumber));
		if (device[i] == NULL) {
			sqlite3_close_database(db);
			DEBUG_INFO("[read sqlite3 device] malloc2 error");
			return -1;
		}
	}
	
	/* 获取数据*/
	j = 0;
	while(sqlite3_step(stmt) == SQLITE_ROW) {
		strcpy(res_data[j] ,sqlite3_column_text(stmt, 2));
		strcpy(device[j]->product_key, sqlite3_column_text(stmt,3));
		strcpy(device[j]->device_number, sqlite3_column_text(stmt,4));
		strcpy(device[j]->device_secret, sqlite3_column_text(stmt,5));
		device[j]->reporting_center = sqlite3_column_int(stmt, 1);
		if (res_data[j] != NULL) {
			//printf("%s\n", res_data[j]);
		} else {
			printf("get history text error\n");
		}
		j ++;
	}
	
	/* 删除数据*/
	if (record_num > 0 && send_flag > 0) {
		ret = sqlite3_delete_device_property(db_table, record_num);	
		if (ret < 0) {
			DEBUG_INFO("sqlite3_delete device property error");
		}
	}
 
	/* 释放内存*/
	for (i = 0; i < record_num; i++) {
		if (res_data[i] != NULL) {
			free(res_data[i]);
		}
		if (device[i] != NULL) {
			free(device[i]);
		}
 
	}
 
	if (res_data != NULL) {
		free(res_data);
	}
	if (device != NULL) {
		free(device);
	}
 
	sqlite3_finalize(stmt);
	sqlite3_close_database(db);
	return 0;
}

8）管理数据库容量

/* 按照容量进行管理*/
void sqlite3_check_database(char* db_name, char* table_name, int size)
{
    int ret;
    int rc;
    int record_num;
    struct stat fstat;
    char sql_str[128] = {0};
    char* err_msg = NULL;    
    sqlite3* db;
    
    ret = stat(db_name, &fstat);
    if (ret == 0) { 
        /*数据库文件存在 */
        if (fstat.st_size >= size)  /* st_size 单位为byte*/
        {
            record_num = sqlite3_get_record_num(db_name, table_name);
            
            db = sqlite3_open_database(db_name);
            if (db != NULL && record_num > 0) {
                memset(sql_str, 0, sizeof(sql_str));
                sprintf(sql_str, "DELETE FROM realtime WHERE ID in(SELECT ID FROM realtime LIMIT %d);", record_num*0.4);
                sqlite3_busy_timeout(db, 30*1000);
                rc = sqlite3_exec(db, sql_str, &err_msg);
                if (rc != SQLITE_OK) {
                    printf("Delete error :%s", err_msg);
                    sqlite3_free(err_msg);
                }
                
                sqlite3_busy_timeout(db, 50*1000);
                rc = sqlite3_exec(db, "Vacuum", 0, 0, &err_msg);
                if (rc != SQLITE_OK) {
                    printf("error msg is %s", err_msg);
                    sqlite3_free(err_msg);
                }
                sqlite3_close_database(db);
            }
        } else {
            /* 未达到清理容量*/
        }
    }
}

9）完整例子

#include <vector>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <assert.h>
#include "sqlite3.h"
 
#include <iostream>
using namespace std;
 
#define DB_PATHNAME    "./yangxt.db"
#define _Version  "0.0.2"
 
typedef unsigned char uchar;
 
//数据库表test_table中行结构体
typedef struct DB_DataFormat
{
    int	 	nID;
    char 	cName[50];
    char 	cCreateTime[15];    // YYYYMMDDHHMMSS
    uchar 	ucSeq;
    double 	dMoney;
}DB_Data_Row, *PDB_Data_Row;
 
 
// 20190409153643(Hex) -> "2019-04-09 15:36:43"
void _BCDTimeToDBTime(uchar *BCDTime_in, short BCDTime_len, char *DBTime_out, short DBTime_len)
{
	assert(BCDTime_len == 7);
 
	snprintf(DBTime_out, DBTime_len, "%02X%02X-%02X-%02X %02X:%02X:%02X", BCDTime_in[0], BCDTime_in[1],
			BCDTime_in[2], BCDTime_in[3], BCDTime_in[4], BCDTime_in[5], BCDTime_in[6]);
}
 
// 20190409153643(char) -> "2019-04-09 15:36:43"
void _cTimeToDBTime(char *cTime_in, short cTime_len, char *DBTime_out, short DBTime_len)
{
	assert(cTime_len == 14);
 
	snprintf(DBTime_out, DBTime_len, "%c%c%c%c-%c%c-%c%c %c%c:%c%c:%c%c", cTime_in[0], cTime_in[1],
			cTime_in[2], cTime_in[3], cTime_in[4], cTime_in[5], cTime_in[6], cTime_in[7],
			cTime_in[8], cTime_in[9], cTime_in[10], cTime_in[11], cTime_in[12], cTime_in[13]);
}
 
// "2019-04-09 15:36:43" -> 20190409153643(char)
void _DBTimeTocTime(char *DBTime_in, short DBTime_len, char *cTime_out)
{
	assert(DBTime_len == 19);
 
	int i = 0, cTime_len = 0;
	for(i=0; i<DBTime_len; i++)
	{
		// 只存放数字字符
		if(DBTime_in[i] >= '0' && DBTime_in[i] <= '9')
		{
			cTime_out[cTime_len] = DBTime_in[i];
			cTime_len++;
		}
	}
	cTime_out[cTime_len] = '\0';
}
 
int main()
{
	printf("_Version = %s \n", _Version);
    vector<DB_Data_Row> testVec;
    char* pcErrMsg = NULL;
    sqlite3_stmt * pStmt = NULL;
	sqlite3* pDB = NULL;
	int nRes = 0;
	// 格式化SQL语句
	char cSql[512] = {0};
	// 测试 时间数据
	char cDBTime[32] = {0};
	uchar bBCDTime[7] = {0};
	memcpy(bBCDTime, "\x20\x19\x04\x09\x15\x36\x43", sizeof(bBCDTime));
 
    do
    {
		//打开数据库
		nRes = sqlite3_open(DB_PATHNAME, &pDB);
		if (nRes != SQLITE_OK)
		{
			//打开数据库失败
			// writeLog
			printf("sqlite3_open, 打开数据库失败: %s --------------------\n", sqlite3_errmsg(pDB));
			break;
		}
 
		// 清除 数据库表 test_table
		sqlite3_snprintf(512, cSql, "drop table if exists test_table");
		sqlite3_exec(pDB, cSql, NULL, NULL, &pcErrMsg);
		if (nRes != SQLITE_OK)
		{
			printf("清除数据库表test_table 失败: %s --------------------\n", pcErrMsg);
			break;
		}
		printf("Clear test_table successful. \n");
 
		// 创建一个表,如果该表存在，则不创建，并给出提示信息，存储在 zErrMsg 中
		sqlite3_snprintf(512, cSql, "CREATE TABLE test_table(\
				nID INTEGER PRIMARY KEY,\
				cName VARCHAR(50),\
				cCreateTime TEXT NOT NULL DEFAULT (datetime('now', 'localtime')),\
				ucSeq INTEGER, \
				dMoney DOUBLE DEFAULT 15.5 \
			);");
		nRes = sqlite3_exec(pDB, cSql, NULL, NULL, &pcErrMsg);
		if (nRes != SQLITE_OK)
		{
			printf("创建数据库表test_table 失败: %s --------------------\n", pcErrMsg);
			break;
		}
		printf("create test_table successful. \n");
 
		// 插入数据
		memset(cDBTime, 0x00, sizeof(cDBTime));
		_BCDTimeToDBTime(bBCDTime, sizeof(bBCDTime), cDBTime, sizeof(cDBTime));
		sqlite3_snprintf(512, cSql, "INSERT INTO test_table(cName, ucSeq) VALUES('当前时间', 8); \
				INSERT INTO test_table(cName, cCreateTime, ucSeq, dMoney) VALUES('%s', '%s', %d, %f)", "InputTime", cDBTime, 10, 16.5);
		nRes = sqlite3_exec(pDB, cSql, NULL, NULL, &pcErrMsg);
		if (nRes != SQLITE_OK)
		{
			printf("插入数据库表test_table 失败: %s --------------------\n", pcErrMsg);
			break;
		}
		printf("insert test_table successful. \n");
 
		// 执行操作  "order by cCreateTime ASC"
		sqlite3_snprintf(512, cSql, "select * from test_table order by ucSeq DESC");
		if (sqlite3_prepare_v2(pDB, cSql, -1, &pStmt, NULL) == SQLITE_OK)
		{
			// 单步处理返回的每个行结果
			while (sqlite3_step(pStmt) == SQLITE_ROW)
			{
				// 整型数据 处理
				DB_Data_Row rowData;
				printf("------------------------------\n");
				rowData.nID = sqlite3_column_int(pStmt, 0);
				printf("rowData.nID = %d\n", rowData.nID);
 
				// 字符串数据 处理
				memcpy(rowData.cName, "123456789012345", 16);
				strcpy(rowData.cName, (const char*)sqlite3_column_text(pStmt, 1));
				printf("rowData.cName = %s\n", rowData.cName);
				// 验证 strcpy 复制会把'\0' 结束字符也复制过去
				for(int idx=0;idx<16;idx++)
					printf("%c", rowData.cName[idx]);
				printf("\n");
 
				// 时间数据 处理
				_DBTimeTocTime((char*)sqlite3_column_text(pStmt, 2), (short)sqlite3_column_bytes(pStmt, 2), rowData.cCreateTime);
				printf("cCreateTime_len = %d, rowData.cCreateTime = %s\n", strlen(rowData.cCreateTime), rowData.cCreateTime);
				memset(cDBTime, 0x00, sizeof(cDBTime));
				_cTimeToDBTime(rowData.cCreateTime, strlen(rowData.cCreateTime), cDBTime, sizeof(cDBTime));
				printf("cDBTime_len = %d, cDBTime = %s\n", strlen(cDBTime), cDBTime);
 
				// 单字节数据  处理
				rowData.ucSeq = sqlite3_column_int(pStmt, 3);
				printf("rowData.ucSeq = %d\n", rowData.ucSeq);
 
				// 浮点数据 处理,格式化显示2位小数
				rowData.dMoney = sqlite3_column_double(pStmt, 4);
				printf("rowData.dMoney = %.2f\n", rowData.dMoney);
 
				testVec.push_back(rowData);
			}
		}
		else
		{
			printf("sqlite3_prepare_v2, 准备语句失败 : %s --------------------\n", sqlite3_errmsg(pDB));
		}
		sqlite3_finalize(pStmt);
 
    }while(0);
 
	//关闭数据库
	sqlite3_close(pDB);
	pDB = NULL;
 
	if (pcErrMsg != NULL)
	{
		sqlite3_free(pcErrMsg); //释放内存
		pcErrMsg = NULL;
	}
 
	return 0;
}

三、数据库管理工具

SQLiteExpert：SQLite administration | SQLite Expert