sqlite3接口API函数备注（2）_sqlite3的函数说明

目录

一、打开数据库文件 

二、预查询过程：准备-执行-完成 

三、扩展：使用参数化SQL (sqlite3_prepare_v2 )

sqlite3_prepare_v2 和  参数化SQL 使用示例：

四、封装查询 sqlite3_exec

五、遗留查询接口(不推荐使用) sqlite3_get_table

六、格式化SQL语句 sqlite3_mprintf

非标准格式化（%q, %Q, %w, %z）：

七、SQLite数据类型

八、SQLite约束

九、SQLite Join（数据库 链接）

十、SQLite Trigger（触发器）

6、 sqlite_master 内置表

十一、SQLite 日期 & 时间

5、strftime 格式化时间

十二、SQLite 常用函数

十三、Like 模糊匹配 和 match（测试不通过，todo）

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

---

 

一、打开数据库文件 

函数原型：

int sqlite3_open(
  const char *filename,   /* Database filename (UTF-8) */
  sqlite3 **ppDb          /* OUT: SQLite db handle */
);
int sqlite3_open16(
  const void *filename,   /* Database filename (UTF-16) */
  sqlite3 **ppDb          /* OUT: SQLite db handle */
);
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 */
);

函数功能：打开数据库文件，如果不存在则创建；

参数传入：sqlite3_open和sqlite3_open_v2以UTF-8解析filename；sqlite3_open16则以UTF-16解析；

参数传出：即使发生错误也会传出数据库连接句柄 ppDb，除非分配内存失败则返回NULL；

函数返回：打开成功则返回SQLITE_OK；否则返回错误码，通过sqlite3_errmsg可以获取到错误信息；

句柄释放：数据库连接句柄 ppDb 通过传给 sqlite3_close释放；

sqlite3_open_v2 提供了 flags 参数，可以用来在打开数据库时设置某些控制属性，flags 的值可以是以下三个值之一：

• SQLITE_OPEN_READONLY
• SQLITE_OPEN_READWRITE
• SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE

上面三个值可以随意组合以下属性值：

•  SQLITE_OPEN_NOMUTEX            // multi-thread threading mode
•  SQLITE_OPEN_FULLMUTEX            // serialized threading mode
•  SQLITE_OPEN_SHAREDCACHE
•  SQLITE_OPEN_PRIVATECACHE
•  SQLITE_OPEN_URI

sqlite3_open_v2 提供了 zVfs 参数，是一个 sqlite3_vfs 对象(定义了数据库使用的操作系统接口)，如果为NULL则使用默认值；

Note:

(1) filename 如果为 ":memory:" 则表示在内存中创建一个私有、临时的数据库，在数据库连接close之后会被清除；当你的数据库名称有以 ":" 作为前缀时，最好加上数据库路径名称以防止引起歧义；

(2) filename 如果为是一个空字符串，则表示在硬盘中创建一个私有、临时的数据库，在数据库连接close之后会被删除；

 

二、预查询过程：准备-执行-完成 

1、准备函数 sqlite3_prepare_v2

函数原型

// 准备语句 
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 */
);
// 执行 
int sqlite3_step(sqlite3_stmt*);
// 完成
int sqlite3_finalize(sqlite3_stmt *pStmt);

函数功能： 官方推荐使用 sqlite3_prepare_v2 ，用于构造 准备语句对象；使用不带"vX"标志的遗留版本，在随后调用sqlite3_step 时会返回多个错误码，需要程序自己选择可能符合实际问题的错误信息；而新的带有"vX"标志的版本在随后的sqlite3_step函数调用中会直接返回明确的错误码；

参数传入：db 表示数据库连接句柄；zSql 表示将要被编译的语句；nByte 表示 zSql 的长度，如果为-1则表示读取直到字符串结束； 如果 pzTail 不为NULL，由于sqlite3_prepare_v2 只会编译 zSql 中的第1条语句，则pzTail 会被用来指向 zSql 中剩余未被编译的语句的首字节；pzTail 一般 传NULL；

参数传出：ppStmt 用来指向编译过的准备语句，可以直接被 sqlite3_step 执行；如果发生错误则ppStmt 为NULL；注意，调用程序需要负责删除 ppStmt ，可以通过调用 sqlite3_finalize 实现；

函数返回：打开成功则返回SQLITE_OK；否则返回错误码，通过sqlite3_errmsg可以获取到错误信息；

2、执行函数 sqlite3_step

函数原型

// 单步执行函数
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()则会同时返回这些结果码和扩展结果码。

注意：

对所有V3.6.23.1以及其前面的所有版本，需要在sqlite3_step()之后调用sqlite3_reset()，在后续的sqlite3_ step之前。如果调用sqlite3_reset重置准备语句失败，将会导致sqlite3_step返回SQLITE_MISUSE，但是在V3.6.23.1以后，sqlite3_step()将会自动调用sqlite3_reset。

3、准备语句 完成 sqlite3_finalize

函数原型

// 准备语句 完成
int sqlite3_finalize(sqlite3_stmt *pStmt);

函数功能：删除准备语句pStmt；可以在pStmt的任何生命周期使用；注意，不要使用任何已经被finalize 的准备语句pStmt；

示例：

4、 sqlite3_column_xxxx系列函数

函数原型

const void *sqlite3_column_blob(sqlite3_stmt*, int iCol);
double sqlite3_column_double(sqlite3_stmt*, int iCol);
int sqlite3_column_int(sqlite3_stmt*, int iCol);
sqlite3_int64 sqlite3_column_int64(sqlite3_stmt*, int iCol);
const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol);
const void *sqlite3_column_text16(sqlite3_stmt*, int iCol);
sqlite3_value *sqlite3_column_value(sqlite3_stmt*, int iCol);
 
// 返回对应列的内容的字节数，这个字节数不包括后面类型转换过程中加上的0终止符。
int sqlite3_column_bytes(sqlite3_stmt*, int iCol);
int sqlite3_column_bytes16(sqlite3_stmt*, int iCol);
 
// 得到数据行中某个列的数据的类型
int sqlite3_column_type(sqlite3_stmt*, int iCol);
 
// 获取行的列数
int sqlite3_column_count(sqlite3_stmt *pStmt);

第一个参数为从sqlite3_prepare返回来的preparedstatement对象的指针，第二参数指定这一行中的想要被返回的列的索引。最左边的一列的索引号是0，行的列数可以使用sqlite3_colum_count()获得。

这些过程会根据情况去转换数值的类型，sqlite内部使用sqlite3_snprintf()去自动进行这个转换，下面是关于转换的细节表：

注：

BLOB数据类型是指二进制的数据块，比如要在数据库中存放一张图片，这张图片就会以二进制形式存放，在sqlite中对应的数据类型就是BLOB.

5、使用示例

 

 

三、扩展：使用参数化SQL (sqlite3_prepare_v2 )

函数原型：

// 部分绑定函数接口
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_reset(sqlite3_stmt *pStmt);

参数化SQL：在 sqlite3_prepare_v2 接口中传入的 zSql 语句，可以被遵从以下模板的参数替换：

• ?                 // 表示任意参数
• ?NNN         // NNN表示integer
• :VVV
• @VVV
• $VVV

参数传入：第1个参数就是 sqlite3_prepare_v2 返回的 sqlite3_stmt 对象；第2个参数表示需要被设置的 SQL参数的下标； 第3个表示需要设置的参数的值；对于 sqlite3_bind_text 第4个参数表示设置参数的值的长度，如果为-1则表示直到字符串结束；第5个参数用来在sqlite完成处理之后销毁 sqlite3_bind_text 设置参数所使用到的字符串，如果需要销毁的话；如果不需要销毁，则传入NULL；

参数化SQL 优点：

• 1. 参数就是占位符，可在编译后提供绑定。
• 2. 参数绑定的优点是无需重新编译，即可多次执行相同的语句。只需重置该语句、绑定新值，并重新执行。使用重置函数可以避免SQL编译的开销。完全避免此法分析、语法分析和代码生成开销。通过调用sqlite3_reset()实现重置。
• 3. 另一个优点是SQLite会处理绑定到参数的转义字符。可避免语法错误和可能的SQL注入式攻击。
• 4. sqlite3_reset()只释放语句资源，会保持VDBE字节代码及其参数不变，sql语句无需再次调用prepare即可再次执行。

sqlite3_prepare_v2 和  参数化SQL 使用示例：

// 参数化SQL 示例
sqlite3_stmt *stmt;
ret = sqlite3_prepare_v2(pDb, "insert into myTable(id,name) values(?,?)", -1, &stmt, NULL);
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, 2, text, -1, NULL);
	sqlite3_step(stmt);
	sqlite3_reset(stmt);
}
sqlite3_finalize(stmt);
assert(SQLITE_OK == ret);

 

四、封装查询 sqlite3_exec

函数原型：

int sqlite3_exec(
  sqlite3*,                                  /* An open database */
  const char *sql,                           /* SQL to be evaluated */
  int (*callback)(void*,int,char**,char**),  /* Callback function */
  void *,                                    /* 1st argument to callback */
  char **errmsg                              /* Error msg written here */
);

函数功能：对 准备-执行-完成(sqlite3_prepare_v2 -> sqlite3_step -> sqlite3_finalize) 过程的封装，方便应用一次调用执行多条SQL语句；

参数传入： 第1个参数表示数据库连接第2个参数句柄；

参数传入：第2个参数 sql 表示待执行的SQL语句，多条语句使用 ";" 分隔；如果sql中某一条语句执行错误，则该语句后续语句都会被跳过；

参数传入：callback回调函数

• 第3个参数 callback 表示回调函数，在sql语句执行的每一行返回结果都会回调此函数，同时如果callback有传入参数，则通过sqlite3_exec 的第4个参数传递给callback的第1个参数；
• callback的第2个参数表示sqlite3_exec 执行返回的每行结果的列数；第3个参数表示指向列字符串的指针数组，每个元素都是一个指针，指向相应列字符串数据，如果返回的行中的列元素为空，相应的第3个参数的指针数组中的指针元素也为NULL；
• callback的第4个参数表示指向列名称的指针数组，数组的每一个元素都表示相应列的名称；
• 如果callback为NULL，则sql语句执行的每一个返回结果都会被忽略；如果callback返回非0，那么sqlite3_exec会返回SQLITE_ABORT，且不会再调用callback和继续执行随后的 sql语句；

参数传入：第5个参数表示执行的错误信息，如果errmsg非NULL，如果执行错误则错误信息会写入到errmsg，注意，错误信息是通过sqlite3_malloc分配的内存，需要使用sqlite3_free释放错误信息字符串所占用的内存空间，防止内存泄露；如果执行没有发生错误，在 sqlite3_exec 返回之前就会把 errmsg 设置为NULL；

注意：

• 应用程序应该要保证传给sqlite3_exec的第1个参数为成功打开和连接的数据库句柄，且在sqlite3_exec执行过程中不被关闭；
• 应用程序应该要保证传给sqlite3_exec的第2个参数的sql语句在sqlite3_exec执行过程中不会被修改；

 

五、遗留查询接口(不推荐使用) 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 */
);
void sqlite3_free_table(char **result);

函数功能：

• 通常用于执行返回数据的查询。可查询多个表，会返回完整的结果集。优点是一步就可执行查询并获得结果。缺点是它将结果完全存储在内存中。
• sqlite3_get_table 被实现为 sqlite3_exec 的封装，不同的是sqlite3_get_table 没有调用到任何sqlite3内部数据结构， 而仅仅只是通过调用sqlite3 提供的对外接口来实现功能，故而如果sqlite3_get_table在内部调用的 sqlie3_exec 发生错误时，错误信息无法通过sqlite3_errcode/sqlite3_errmsg获取；
• sqlite3_exec 通常用于执行不返回数据的查询，如insert、update、delete；sqlite3_get_table  通常用于执行返回数据的查询，3.24版本中不推荐此函数，但是没说用哪个函数替代；

参数传入：第1个参数db表示数据库句柄；第2个参数 zSql 表示待执行的SQL语句；

参数传出：第3个参数 pazResult 表示返回的结果集，以行优先存放在，格式类型如下例子：

// 假设数据库表如下所示：
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";

参数传出：pnRow 表示结果的列数；pnColumn 表示结果的行数；

参数传出：pzErrmsg表示错误信息；

结果内存释放：在使用完pazResult 结果集之后，需要调用sqlite3_free_table释放内存，注意，不能直接调用sqlite3_free 来释放内存；

 

六、格式化SQL语句 sqlite3_mprintf

函数原型：

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_xxprintf 系列函数功能类似于C库中的"printf"函数，支持printf()中大多数常见格式选项，以及其他一些非标准的格式（%q, %Q, %w, %z）；
• sqlite3_mprintf 和 sqlite3_vmprintf 函数将返回的字符串写入到(由sqlite3_malloc64)动态分配的内存中，在使用完字符串之后需要调用sqlite3_free释放内存空间；如果内存分配失败，则返回NULL；
• sqlite3_snprintf 和 sqlite3_vsnprintf 函数类似C库中的"snprintf"函数，返回的字符串会存放在第2个参数传入的指针所指向的内存空间中，第1个参数指示了缓冲区的大小；注意，sqlite3_snprintf 返回的不再是写入缓冲区的字节数，而是指向缓冲区的指针，这是sqlite3历史遗留问题，为了兼容性考虑也一直沿用返回指针的方式；

非标准格式化（%q, %Q, %w, %z）：

七、SQLite数据类型

1、在sqlite中创建数据库表会使用到数据类型，用于指定每个字段的 数据类型，sqlite对外提供如下类型：

Note:

左边 数据类型 表示我们在创建表的时候对字段可以设置的数据类型；亲缘类型 是指，当我们在插入字段数据的时候，该字段的数据将会优先采用亲缘类型作为该值的存储方式；

2、SQLite亲缘类型：SQLite目前的版本支持以下五种亲缘类型：

3、 SQLite存储类：在SQLite内部，每个存储在 SQLite 数据库中的值都具有以下存储类之一：

4、SQLite数据类型表示在创建数据库表时可以用来指定字段的数据类型；亲缘类型表示在插入数据值时，数据值可能被转换成的数据格式，本质上和数据类型作用一样；SQLite存储类表示是SQLite内部对数据的存储管理方式，作为应用程序可以不用关注；（个人理解）

5、数据类型使用 示例

sqlite> CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

八、SQLite约束

1、约束是在表的数据列上强制执行的规则。这些是用来限制可以插入到表中的数据类型。这确保了数据库中数据的准确性和可靠性。约束可以是列级或表级。列级约束仅适用于列，表级约束被应用到整个表。

以下是在 SQLite 中常用的约束：

• NOT NULL 约束：确保某列不能有 NULL 值。
• DEFAULT 约束：当某列没有指定值时，为该列提供默认值。
• UNIQUE 约束：确保某列中的所有值是不同的。
• PRIMARY Key 约束：唯一标识数据库表中的各行/记录。
• CHECK 约束：CHECK 约束确保某列中的所有值满足一定条件。

参见：SQLite 约束

SQLite 约束 示例：

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL UNIQUE,
   AGE            INT     CHECK(AGE > 0),
   ADDRESS        CHAR(50),
   SALARY         REAL    DEFAULT 50000.00
);

九、SQLite Join（数据库 链接）

1、目前SQLite支持 CROSS JOIN（交叉链接）、INNER JOIN（内链接）和 LEFT OUTER JOIN（左外链接）（SQLite 不支持右外链接和全链接），如下：

-- CROSS JOIN：返回被连接的两个表所有数据行的笛卡尔积，结果行数等于第1个表中符合查询条件的数据行数乘以第2个表中符合查询条件的数据行数。这有可能产生非常大的表，使用时必须谨慎，只在适当的时候使用。
-- 示例 语句如下格式：
SELECT ... FROM table1 CROSS JOIN table2 ...
 
-- INNER JOIN(默认链接类型)：根据连接谓词结合两个表（table1 和 table2）的列值来创建一个新的结果表
SELECT ... FROM table1 [INNER] JOIN table2 ON conditional_expression ...
 
-- OUTER JOIN：外连接（OUTER JOIN）声明条件的方法与内连接（INNER JOIN）是相同的，使用 ON、USING 或 NATURAL 关键字来表达。最初的结果表以相同的方式进行计算。一旦主连接计算完成，外连接（OUTER JOIN）将从一个或两个表中任何未连接的行合并进来，外连接的列使用 NULL 值，将它们附加到结果表中。
SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...

查询结果 示例：

// COMPANY表
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0
 
// DEPARTMENT表
ID          DEPT        EMP_ID
----------  ----------  ----------
1           IT Billing  1
2           Engineerin  2
3           Finance     7
 
// CROSS JOIN：sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;
EMP_ID      NAME        DEPT
----------  ----------  ----------
1           Paul        IT Billing
2           Paul        Engineerin
7           Paul        Finance
1           Allen       IT Billing
2           Allen       Engineerin
7           Allen       Finance
1           Teddy       IT Billing
2           Teddy       Engineerin
7           Teddy       Finance
1           Mark        IT Billing
2           Mark        Engineerin
7           Mark        Finance
1           David       IT Billing
2           David       Engineerin
7           David       Finance
1           Kim         IT Billing
2           Kim         Engineerin
7           Kim         Finance
1           James       IT Billing
2           James       Engineerin
7           James       Finance
 
// INNER JOIN: sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
EMP_ID      NAME        DEPT
----------  ----------  ----------
1           Paul        IT Billing
2           Allen       Engineerin
7           James       Finance
 
// OUTER JOIN:sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
EMP_ID      NAME        DEPT
----------  ----------  ----------
1           Paul        IT Billing
2           Allen       Engineerin
            Teddy
            Mark
            David
            Kim
7           James       Finance

参见：SQLite Join

 

十、SQLite Trigger（触发器）

1、触发器作用：数据库的回调函数，它会在指定的数据库事件发生时自动执行/调用；

2、 关于触发器的几点说明：

• SQLite 的触发器（Trigger）可以指定在特定的数据库表发生 DELETE、INSERT 或 UPDATE 时触发，或在一个或多个指定表的列发生更新时触发。
• SQLite 只支持 FOR EACH ROW 触发器（Trigger），没有 FOR EACH STATEMENT 触发器（Trigger）。因此，明确指定 FOR EACH ROW 是可选的。
• WHEN 子句和触发器（Trigger）动作可能访问使用表单 NEW.column-name 和 OLD.column-name 的引用插入、删除或更新的行元素，其中 column-name 是从与触发器关联的表的列的名称。
• 如果提供 WHEN 子句，则只针对 WHEN 子句为真的指定行执行 SQL 语句。如果没有提供 WHEN 子句，则针对所有行执行 SQL 语句。
• BEFORE 或 AFTER 关键字决定何时执行触发器动作，决定是在关联行的插入、修改或删除之前或者之后执行触发器动作。
• 当触发器相关联的表删除时，自动删除触发器（Trigger）。
• 要修改的表必须存在于同一数据库中，作为触发器被附加的表或视图，且必须只使用 tablename，而不是database.tablename。
• 一个特殊的 SQL 函数 RAISE() 可用于触发器程序内抛出异常。

3、 触发器 基本语法：

-- 创建 触发器（Trigger） 的基本语法如下：
CREATE  TRIGGER trigger_name [BEFORE|AFTER] event_name -- INSERT/DELETE/UPDATE 
ON table_name
FOR EACH ROW   -- 可选，不写也一样
BEGIN
 -- Trigger logic goes here....
END;
 
-- 在 UPDATE 操作上在表的一个或多个指定列上创建触发器（Trigger）的语法：
CREATE  TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name 
ON table_name
BEGIN
 -- Trigger logic goes here....
END;

4、触发器 使用示例：

-- 示例1 目的：要为被插入到新创建的 COMPANY 表（如果已经存在，则删除重新创建）中的每一个记录保持审计试验：
-- 创建 COMPANY表
sqlite> CREATE TABLE COMPANY(
   ...>    ID INT PRIMARY KEY     NOT NULL,
   ...>    NAME           TEXT    NOT NULL,
   ...>    AGE            INT     NOT NULL,
   ...>    ADDRESS        CHAR(50),
   ...>    SALARY         REAL
   ...> );
-- 创建 AUDIT表
sqlite> CREATE TABLE AUDIT(
   ...>     EMP_ID INT NOT NULL,
   ...>     ENTRY_DATE TEXT NOT NULL
   ...> );
-- 创建 audit_log触发器
sqlite> CREATE TRIGGER audit_log AFTER INSERT 
   ...> ON COMPANY
   ...> BEGIN
   ...>    INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));
   ...> END;
-- 在 COMPANY表 中插入 数据
sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
   ...> VALUES (1, 'Paul', 32, 'California', 20000.00 );
-- 查询 company表
sqlite> select * from company;
ID          NAME        AGE         ADDRESS     SALARY    
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0   
-- 查询 audit表(在company表插入数据时，由触发器插入审计记录)
sqlite> select * from audit;
EMP_ID      ENTRY_DATE         
----------  -------------------
1           2019-04-08 02:08:57
sqlite> 

5、 触发器中使用 WHEN子句 示例：

-- 使用 WHEN 子句 示例：
-- for each row 是操作语句每影响到一行的时候就触发一次，也就是删了 10 行就触发 10 次，而 for each state 一条操作语句就触发一次，有时没有被影响的行也执行。sqlite 只实现了 for each row 的触发
CREATE TRIGGER trigger_name 
AFTER UPDATE OF id ON table_1 
FOR EACH ROW 
WHEN new.id>30 
BEGIN 
UPDATE table_2 SET id=new.id WHERE table_2.id=old.id;
END;

 注意：

• 其中作用表的新旧数据使用new和old进行指向，例如insert时new指向插入的行数据，delete时old指向删除的行数据，update时类推。
• 注意：sqlite3的触发器语法跟SqlServer等大型数据库不太一样，参照SqlServer的方式来写会无法执行，例如不需要关键字as，也不能用关键字for（只能用before/after），一定要有begin/end，一定要do something语句后加分号；
   

5、列出/删除 触发器

-- 从 sqlite_master 表中列出所有触发器，如下所示：
sqlite> SELECT name FROM sqlite_master WHERE type = 'trigger';
 
-- 列出特定表上的触发器，则使用 AND 子句连接表名，如下所示：
sqlite> SELECT name FROM sqlite_master WHERE type = 'trigger' AND tbl_name = 'COMPANY';
 
-- DROP 命令，可用于删除已有的触发器：
sqlite> DROP TRIGGER trigger_name;

6、 sqlite_master 内置表

（1）sqlite_master 表作用：

1. SQLite数据库中有一个内置表，名为SQLITE_MASTER，此表中存储着当前数据库中所有表的相关信息，比如表的名称、用于创建此表的sql语句、索引、索引所属的表、创建索引的sql语句等。
2. SQLITE_MASTER表示只读的，只能对他进行读操作，写操作只能由系统自身触发，使用者没有权限。所有对用户自定义表的结构修改操作，会自定更新到此表。

（2）sqlite_master 表结构

-- 表/索引/触发器 信息
CREATE TABLE sqlite_master ( 
	type TEXT,   -- 表类型：table/index/trigger
	name TEXT,   -- 表名称
	tbl_name TEXT,     -- index/trigger所属的表名称
	rootpage INTEGER, 
	sql TEXT    -- 表 创建语句
);
 

注：

临时表信息：临时表不包含在SQLITE_MASTER表中，SQLITE_TEMP_MASTER专门用来存储临时表的信息，此表和SQLITE_MASTER表的结构一致。

 

十一、SQLite 日期 & 时间

1、SQLite 支持以下五个日期和时间函数：

上述五个日期和时间函数把时间字符串作为参数。时间字符串后跟零个或多个 modifier 修饰符。

strftime() 函数也可以把格式字符串 format 作为其第一个参数。下面将为您详细讲解不同类型的时间字符串和修饰符。

2、timestring 时间字符串

一个时间字符串可以采用下面任何一种格式：

您可以使用 "T" 作为分隔日期和时间的文字字符。

3、modifer 修饰符

时间字符串后边可跟着零个或多个的修饰符，这将改变有上述五个函数返回的日期和/或时间。任何上述五大功能返回时间。修饰符应从左到右使用，下面列出了可在 SQLite 中使用的修饰符：

• NNN days     // 
• NNN hours
• NNN minutes
• NNN.NNNN seconds
• NNN months
• NNN years
• start of month
• start of year
• start of day
• weekday N
• unixepoch
• localtime
• utc

4、使用 示例：

[root@localhost sqlite3_yxt]# sqlite3 yangxt.db 
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select date('2019-04-07');  
2019-04-07
sqlite> select datetime('2010-12-30 12:10');   -- 时间字符串 转换输出
2010-12-30 12:10:00
sqlite> select datetime('now');   -- now 可以获取当前时间，相对unixepoch
2019-04-07 06:17:52
sqlite> select datetime('now','localtime');  -- 相对本地的当前时间
2019-04-07 14:18:17
sqlite> select datetime('2010-12-30T12:10');
2010-12-30 12:10:00
sqlite> 
-- 计算当前月份的最后一天：
sqlite> SELECT date('now','start of month','+1 month','-1 day');
2019-04-30
sqlite> 
-- 计算当年 10 月的第一个星期二的日期：
sqlite> select datetime('now');
2019-04-07 06:32:03
sqlite> SELECT date('now','start of year','+9 months','weekday 2');
2019-10-01
sqlite> 
 
sqlite> select datetime('now', 'localtime');
2019-04-09 15:36:04
sqlite> select strftime('%s', 'now');
1554795403
-- 计算给定 UNIX 时间戳 1554795403 的日期和时间：
sqlite> SELECT datetime(1554795403, 'unixepoch');
2019-04-09 07:36:43
-- 计算给定 UNIX 时间戳 1554795403 相对本地时区的日期和时间：
sqlite> SELECT datetime(1554795403, 'unixepoch', 'localtime');
2019-04-09 15:36:43
sqlite> select strftime('%s', '2019-04-09 15:36:43', 'utc');
1554795403
sqlite> select strftime('%s', '2019-04-09 15:36:43');
1554824203
sqlite> SELECT datetime(1554824203, 'unixepoch');
2019-04-09 15:36:43
sqlite> SELECT datetime(1554824203, 'unixepoch', 'localtime');
2019-04-09 23:36:43
sqlite> 

5、strftime 格式化时间

SQLite 提供了非常方便的函数 strftime() 来格式化任何日期和时间。您可以使用以下的替换来格式化日期和时间：

6、strftime 使用示例：

-- 计算从 2004 年某一特定时刻以来的秒数：
sqlite> select datetime('now');
2019-04-07 06:29:49
sqlite> SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');
481694097
sqlite> 

十二、SQLite 常用函数

1、 SQLite 有许多内置函数用于处理字符串或数字数据，部分列举如下：

2、使用示例：

[root@localhost sqlite3_yxt]# cat company.txt 
1|Paul|32|California|20000.0
2|Allen|25|Texas|15000.0
3|Teddy|23|Norway|20000.0
4|Mark|25|Rich-Mond|65000.0
5|David|27|Texas|85000.0
6|Kim|22|South-Hall|45000.0
7|James|24|Houston|10000.0
[root@localhost sqlite3_yxt]# sqlite3 yangxt.db 
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .table
AUDIT    COMPANY  yangxt 
sqlite> select * from company;
sqlite> .import company.txt company
sqlite> .header on
sqlite> .mode colum
sqlite> select * from company;
ID          NAME        AGE         ADDRESS     SALARY    
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0   
2           Allen       25          Texas       15000.0   
3           Teddy       23          Norway      20000.0   
4           Mark        25          Rich-Mond   65000.0   
5           David       27          Texas       85000.0   
6           Kim         22          South-Hall  45000.0   
7           James       24          Houston     10000.0   
sqlite> SELECT count(*) FROM COMPANY;
count(*)  
----------
7         
sqlite> SELECT max(salary) FROM COMPANY;
max(salary)
-----------
85000.0    
sqlite> SELECT random() AS Random;
Random              
--------------------
-5534641662674898091
sqlite> SELECT abs(5), abs(-15), abs(NULL), abs(0), abs("ABC");
abs(5)      abs(-15)    abs(NULL)   abs(0)      abs("ABC")
----------  ----------  ----------  ----------  ----------
5           15                      0           0.0       
sqlite> SELECT upper(name) FROM COMPANY;
upper(name)
-----------
PAUL       
ALLEN      
TEDDY      
MARK       
DAVID      
KIM        
JAMES      
sqlite> SELECT name, length(name) FROM COMPANY;
NAME        length(name)
----------  ------------
Paul        4           
Allen       5           
Teddy       5           
Mark        4           
David       5           
Kim         3           
James       5           
sqlite> SELECT sqlite_version() AS 'SQLite Version';
SQLite Version
--------------
3.6.20        
sqlite> 

 

十三、Like 模糊匹配 和 match（测试不通过，todo）

1、Like：用like替代等号并使用通配符%（表示任何字符）或？（表示单个字符），示例：

sqlite> .table
yangxt
sqlite> .header
sqlite> .header on
sqlite> .mode colum
sqlite> select * from yangxt;
ID          SensorID    SiteNum     Time          SensorParameter
----------  ----------  ----------  ------------  ---------------
1           1           1           200605011306  16.4           
2           1           1           200605011206  18.9           
sqlite> select * from yangxt where Time like '2006%';
ID          SensorID    SiteNum     Time          SensorParameter
----------  ----------  ----------  ------------  ---------------
1           1           1           200605011306  16.4           
2           1           1           200605011206  18.9           
sqlite> 

2、 match

 

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

//============================================================================
// Name        : test_sqlite3_cpp.cpp
// Author      : yangxt
// Version     :
// Copyright   : Your copyright notice
// Description : Hello World in C++, Ansi-style
//============================================================================
 
#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;
}

程序运行结果：

[root@AT91SAM9 /app/yangxt_sqlite]# ./test_sqlite3_cpp;
_Version = 0.0.2 
Clear test_table successful. 
create test_table successful. 
insert test_table successful. 
------------------------------
rowData.nID = 2
rowData.cName = InputTime
InputTime12345
cCreateTime_len = 14, rowData.cCreateTime = 20190409153643
cDBTime_len = 19, cDBTime = 2019-04-09 15:36:43
rowData.ucSeq = 10
rowData.dMoney = 16.50
------------------------------
rowData.nID = 1
rowData.cName = 当前时间
当前时间012345
cCreateTime_len = 14, rowData.cCreateTime = 19700102084235
cDBTime_len = 19, cDBTime = 1970-01-02 08:42:35
rowData.ucSeq = 8
rowData.dMoney = 15.50
[root@AT91SAM9 /app/yangxt_sqlite]#