指点成金-最美分享吧

登录

sqlite3接口API函数备注

佚名 举报

篇首语：本文由小编为大家整理，主要介绍了sqlite3接口API函数备注相关的知识，希望对你有一定的参考价值。

目录

一、打开数据库文件 

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

三、扩展：使用参数化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()则会同时返回这些结果码和扩展结果码。

错误码描述
SQLITE_BUSY数据库引擎无法获取执行任务所需要的数据库锁。如果错误码发生在事务之外，可以尝试再次执行语句；如果错误发生在事务之内，必须回滚事务；
SQLITE_DONE语句已经成功完成执行，sqlite3_step不能再被调用，除非调用sqlite3_reset重置回初始状态；
SQLITE_ROW如果正在执行的准备语句pStmt返回了任何数据，在每次新的行数据准备好之后，SQLITE_ROW会被返回。返回的任何数据可以通过 sqlite3_column_xxxx系列函数获取【见后面详解】；再次调用sqlite3_step可以继续获取新的行数据；
SQLITE_ERROR发生运行时错误，可通过sqlite3_errmsg获取错误信息；sqlite3_step不能再被调用；
SQLITE_MISUSE表示sqlite3_step被非法使用，可能准备语句pStmt在之前已经被finalized，也可能pStmt在前面已经发生错误或已经成功完成执行；或者是多个线程同时使用了该数据库连接； 

注意：

对所有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()去自动进行这个转换，下面是关于转换的细节表：

内部类型

请求的类型

转换

NULL

INTEGER

结果是0

NULL

FLOAT

结果是0.0

NULL

TEXT

结果是NULL

NULL

BLOB

结果是NULL

INTEGER

FLOAT

从整形转换到浮点型

INTEGER

TEXT

整形的ASCII码显示

INTEGER

BLOB

同上

FLOAT

INTEGER

浮点型转换到整形

FLOAT

TEXT

浮点型的ASCII显示

FLOAT

BLOB

同上

TEXT

INTEGER

使用atoi()

TEXT

FLOAT

使用atof()

TEXT

BLOB

没有转换

BLOB

INTEGER

先到TEXT，然后使用atoi

BLOB

FLOAT

先到TEXT，然后使用atof

BLOB

TEXT

如果需要的话添加0终止符

注：

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       | 43Bob         | 28Cindy       | 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）：

格式类型含义
%q，

%q 的工作原理像%s，从参数列表中取得一个以 NULL 结束的字符串。它会将单引号反斜杠都双写，使你更容易防范 SQL 注入式攻击;

例如：

char* before = "Hey, at least %q no pig-man.";
char* after = sqlite3_mprintf(before, "\he"s\");
上述程序执行后 after 的值为"Hey, at least \\he""s\\ no pig-man"。

如果指向字符串的指针是NULL，%q不会产生任何输出，所以一个NULL指针相当于一个空字符串；

%Q

%Q做了%q所做的所有事情，额外的，%Q会在字符串首尾添加单引号；如果指向字符串的指针是NULL，%Q会产生一个不带引号的NULL，换句话说，一个NULL指针会产生一个SQL NULL;

%w

类似%q的功能，不同%w用来将所有的双引号(")双写；

%z%s一样都是用来显示以NULL结束的字符串，不同的是%z会在字符串拷贝到输出的之后调用sqlite3_free来释放字符串所占用的内存；
  

七、SQLite数据类型

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

数据类型亲缘（和）类型
INT
INTEGER
TINYINT
SMALLINT
MEDIUMINT
BIGINT
UNSIGNED BIG INT
INT2
INT8		INTEGER
CHARACTER(20)
VARCHAR(255)
VARYING CHARACTER(255)
NCHAR(55)
NATIVE CHARACTER(70)
NVARCHAR(100)
TEXT
CLOB		TEXT
BLOB
no datatype specified		NONE
REAL
DOUBLE
DOUBLE PRECISION
FLOAT		REAL
NUMERIC
DECIMAL(10,5)
BOOLEAN
DATE
DATETIME		NUMERIC

Note:

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

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

亲缘类型描述
TEXT数值型数据在被插入之前，需要先被转换为文本格式，之后再插入到目标字段中。
NUMERIC当文本数据被插入到亲缘性为NUMERIC的字段中时，如果转换操作不会导致数据信息丢失以及完全可逆，那么SQLite就会将该文本数据转换为INTEGER或REAL类型的数据，如果转换失败，SQLite仍会以TEXT方式存储该数据。对于NULL或BLOB类型的新数据，SQLite将不做任何转换，直接以NULL或BLOB的方式存储该数据。需要额外说明的是，对于浮点格式的常量文本，如"30000.0"，如果该值可以转换为INTEGER同时又不会丢失数值信息，那么SQLite就会将其转换为INTEGER的存储方式。
INTEGER对于亲缘类型为INTEGER的字段，其规则等同于NUMERIC，唯一差别是在执行CAST表达式时。
REAL其规则基本等同于NUMERIC，唯一的差别是不会将"30000.0"这样的文本数据转换为INTEGER存储方式。
NONE不做任何的转换，直接以该数据所属的数据类型进行存储。

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

存储类描述
NULL值是一个 NULL 值。
INTEGER值是一个带符号的整数，根据值的大小存储在 1、2、3、4、6 或 8 字节中。
REAL值是一个浮点值，存储为 8 字节的 IEEE 浮点数字。
TEXT值是一个文本字符串，使用数据库编码（UTF-8、UTF-16BE 或 UTF-16LE）存储。
BLOB是一个 blob 数据，完全根据它的输入存储。

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.02           Allen       25          Texas       15000.03           Teddy       23          Norway      20000.04           Mark        25          Rich-Mond   65000.05           David       27          Texas       85000.06           Kim         22          South-Hall  45000.07           James       24          Houston     10000.0// DEPARTMENT表ID          DEPT        EMP_ID----------  ----------  ----------1           IT Billing  12           Engineerin  23           Finance     7// CROSS JOIN：sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;EMP_ID      NAME        DEPT----------  ----------  ----------1           Paul        IT Billing2           Paul        Engineerin7           Paul        Finance1           Allen       IT Billing2           Allen       Engineerin7           Allen       Finance1           Teddy       IT Billing2           Teddy       Engineerin7           Teddy       Finance1           Mark        IT Billing2           Mark        Engineerin7           Mark        Finance1           David       IT Billing2           David       Engineerin7           David       Finance1           Kim         IT Billing2           Kim         Engineerin7           Kim         Finance1           James       IT Billing2           James       Engineerin7           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 Billing2           Allen       Engineerin7           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 Billing2           Allen       Engineerin            Teddy            Mark            David            Kim7           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_nameFOR EACH ROW   -- 可选，不写也一样BEGIN -- Trigger logic goes here....END;-- 在 UPDATE 操作上在表的一个或多个指定列上创建触发器（Trigger）的语法：CREATE  TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name ON table_nameBEGIN -- 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:57sqlite>

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/triggername TEXT,   -- 表名称tbl_name TEXT,     -- index/trigger所属的表名称rootpage INTEGER, sql TEXT    -- 表 创建语句);

注：

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

 

十一、SQLite 日期 & 时间

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

序号函数实例
1date(timestring, modifier, modifier, ...)以 YYYY-MM-DD 格式返回日期。
2time(timestring, modifier, modifier, ...)以 HH:MM:SS 格式返回时间。
3datetime(timestring, modifier, modifier, ...)以 YYYY-MM-DD HH:MM:SS 格式返回。
4julianday(timestring, modifier, modifier, ...)这将返回从格林尼治时间的公元前 4714 年 11 月 24 日正午算起的天数。
5strftime(format, timestring, modifier, modifier, ...)这将根据第一个参数指定的格式字符串返回格式化的日期。具体格式见下边讲解。

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

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

2、timestring 时间字符串

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

序号时间字符串实例
1YYYY-MM-DD2010-12-30
2YYYY-MM-DD HH:MM2010-12-30 12:10
3YYYY-MM-DD HH:MM:SS.SSS2010-12-30 12:10:04.100
4MM-DD-YYYY HH:MM30-12-2010 12:10
5HH:MM12:10
6YYYY-MM-DDTHH:MM2010-12-30 12:10
7HH:MM:SS12:10:01
8YYYYMMDD HHMMSS20101230 121001
9now2013-05-07

您可以使用 "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.20Enter ".help" for instructionsEnter SQL statements terminated with a ";"sqlite> select date("2019-04-07");  2019-04-07sqlite> select datetime("2010-12-30 12:10");   -- 时间字符串 转换输出2010-12-30 12:10:00sqlite> select datetime("now");   -- now 可以获取当前时间，相对unixepoch2019-04-07 06:17:52sqlite> select datetime("now","localtime");  -- 相对本地的当前时间2019-04-07 14:18:17sqlite> select datetime("2010-12-30T12:10");2010-12-30 12:10:00sqlite> -- 计算当前月份的最后一天：sqlite> SELECT date("now","start of month","+1 month","-1 day");2019-04-30sqlite> -- 计算当年 10 月的第一个星期二的日期：sqlite> select datetime("now");2019-04-07 06:32:03sqlite> SELECT date("now","start of year","+9 months","weekday 2");2019-10-01sqlite> sqlite> select datetime("now", "localtime");2019-04-09 15:36:04sqlite> 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:43sqlite> select strftime("%s", "2019-04-09 15:36:43", "utc");1554795403sqlite> select strftime("%s", "2019-04-09 15:36:43");1554824203sqlite> SELECT datetime(1554824203, "unixepoch");2019-04-09 15:36:43sqlite> SELECT datetime(1554824203, "unixepoch", "localtime");2019-04-09 23:36:43sqlite>

5、strftime 格式化时间

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

替换描述
%d一月中的第几天，01-31
%f带小数部分的秒，SS.SSS
%H小时，00-23
%j一年中的第几天，001-366
%J儒略日数，DDDD.DDDD
%m月，00-12
%M分，00-59
%s从 1970-01-01 算起的秒数
%S秒，00-59
%w一周中的第几天，0-6 (0 is Sunday)
%W一年中的第几周，01-53
%Y年，YYYY
%%% symbol

6、strftime 使用示例：

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


十二、SQLite 常用函数

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

序号函数 & 描述
1SQLite COUNT 函数
SQLite COUNT 聚集函数是用来计算一个数据库表中的行数。
2SQLite MAX 函数
SQLite MAX 聚合函数允许我们选择某列的最大值。
3SQLite MIN 函数
SQLite MIN 聚合函数允许我们选择某列的最小值。
4SQLite AVG 函数
SQLite AVG 聚合函数计算某列的平均值。
5SQLite SUM 函数
SQLite SUM 聚合函数允许为一个数值列计算总和。
6SQLite RANDOM 函数
SQLite RANDOM 函数返回一个介于 -9223372036854775808 和 +9223372036854775807 之间的伪随机整数。
7SQLite ABS 函数
SQLite ABS 函数返回数值参数的绝对值。
8SQLite UPPER 函数
SQLite UPPER 函数把字符串转换为大写字母。
9SQLite LOWER 函数
SQLite LOWER 函数把字符串转换为小写字母。
10SQLite LENGTH 函数
SQLite LENGTH 函数返回字符串的长度。
11SQLite sqlite_version 函数
SQLite sqlite_version 函数返回 SQLite 库的版本。

2、使用示例：

[root@localhost sqlite3_yxt]# cat company.txt 1|Paul|32|California|20000.02|Allen|25|Texas|15000.03|Teddy|23|Norway|20000.04|Mark|25|Rich-Mond|65000.05|David|27|Texas|85000.06|Kim|22|South-Hall|45000.07|James|24|Houston|10000.0[root@localhost sqlite3_yxt]# sqlite3 yangxt.db SQLite version 3.6.20Enter ".help" for instructionsEnter SQL statements terminated with a ";"sqlite> .tableAUDIT    COMPANY  yangxt sqlite> select * from company;sqlite> .import company.txt companysqlite> .header onsqlite> .mode columsqlite> 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              ---------------------5534641662674898091sqlite> 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> .tableyangxtsqlite> .headersqlite> .header onsqlite> .mode columsqlite> 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 #include #include #include #include #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= "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 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)//打开数据库失败// writeLogprintf("sqlite3_open, 打开数据库失败: %s --------------------\n", sqlite3_errmsg(pDB));break;// 清除 数据库表 test_tablesqlite3_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);elseprintf("sqlite3_prepare_v2, 准备语句失败 : %s --------------------\n", sqlite3_errmsg(pDB));sqlite3_finalize(pStmt);    while(0);//关闭数据库sqlite3_close(pDB);pDB = NULL;if (pcErrMsg != NULL)sqlite3_free(pcErrMsg); //释放内存pcErrMs
以上是关于sqlite3接口API函数备注的主要内容，如果未能解决你的问题，请参考以下文章

上一篇知识大全 银行卡号大全6212262201023557228

下一篇idea查看继承关系

相关文章