数据库之SQLite的介绍与使用20180705

 

一、SQLite 简介

1.介绍

SQLite，是一款轻型的数据库，是遵守ACID的关系型数据库管理系统，它包含在一个相对小的C库中。它是D.RichardHipp建立的公有领域项目。它的设计目标是嵌入式的，

而且目前已经在很多嵌入式产品中使用了它，它占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了。它能够支持Windows/Linux/Unix等等主流的操作系统，

同时能够跟很多程序语言相结合，比如 Tcl、C#、PHP、Java等，还有ODBC接口，同样比起Mysql、PostgreSQL这两款开源的世界著名数据库管理系统来讲，它的处理速度比他们都快。

SQLite第一个Alpha版本诞生于2000年5月。 至2015年已经有15个年头，SQLite也迎来了一个版本 SQLite 3已经发布

 

SQLite是一个进程内的库，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。它是一个零配置的数据库，这意味着与其他数据库一样，您不需要在系统中配置。

就像其他数据库，SQLite 引擎不是一个独立的进程，可以按应用程序需求进行静态或动态连接。SQLite 直接访问其存储文件。

 

2.特点

1).不需要一个单独的服务器进程或操作的系统（无服务器的）。

2).SQLite 不需要配置，这意味着不需要安装或管理。

3).一个完整的 SQLite 数据库是存储在一个单一的跨平台的磁盘文件。

4).SQLite 是非常小的，是轻量级的，完全配置时小于 400KiB，省略可选功能配置时小于250KiB。

5).SQLite 是自给自足的，这意味着不需要任何外部的依赖。

6).SQLite 事务是完全兼容 ACID 的，允许从多个进程或线程安全访问。

7).SQLite 支持 SQL92（SQL2）标准的大多数查询语言的功能。

8).SQLite 使用 ANSI-C 编写的，并提供了简单和易于使用的 API。

9).SQLite 可在 UNIX（Linux, Mac OS-X, Android, iOS）和 Windows（Win32, WinCE, WinRT）中运行。

10).数据库文件可以在不同字节顺序的机器间自由的共享

11).支持数据库大小至2TB

12).足够小, 大致13万行C代码, 4.43M

13).比一些流行的数据库在大部分普通数据库操作要快

14).简单, 轻松的API

15).包含TCL绑定, 同时通过Wrapper支持其他语言的绑定

16).良好注释的源代码, 并且有着90%以上的测试覆盖率

17).独立: 没有额外依赖

18).源码完全的开源, 你可以用于任何用途, 包括出售它

19).支持多种开发语言，C, C++, PHP, Perl, Java, C#,Python, Ruby等

 

3.支持的SQL

SQLite虽然很小巧，但是支持的SQL语句不会逊色于其他开源数据库，

它支持的SQL主要包括：

 

DDL - 数据定义语言

CREATE    创建一个新的表，一个表的视图，或者数据库中的其他对象。

ALTER       修改数据库中的某个已有的数据库对象，比如一个表。

DROP        删除整个表，或者表的视图，或者数据库中的其他对象。

 

DML - 数据操作语言

INSERT     创建一条记录。

UPDATE   修改记录。

DELETE    删除记录。

 

DQL - 数据查询语言

SELECT     从一个或多个表中检索某些记录。

 

具体包括：

ATTACH DATABASE

BEGIN TRANSACTION

comment

COMMIT TRANSACTION

COPY

CREATE INDEX

CREATE TABLE

CREATE TRIGGER

CREATE VIEW

DELETE

DETACH DATABASE

DROP INDEX

DROP TABLE

DROP TRIGGER

DROP VIEW

END TRANSACTION

EXPLAIN

expression

INSERT

ON CONFLICT clause

PRAGMA

REPLACE

ROLLBACK TRANSACTION

SELECT

UPDATE

 

同时它还支持事务处理功能等等。也有人说它象Microsoft的Access，有时候真的觉得有点象，但是事实上它们区别很大。

比如SQLite 支持跨平台，操作简单，能够使用很多语言直接创建数据库，而不象Access一样需要Office的支持。

如果你是个很小型的应用，或者你想做嵌入式开发，没有合适的数据库系统，那么你可以考虑使用SQLite。

到2013年10月17日最新版本是 3.8.1 。能在上面获得源代码和文档。同时因为数据库结构简单，系统源代码也不是很多，

也适合想研究数据库系统开发的专业人士.

 

二、SQLite 安装

SQLite 的一个重要的特性是零配置的，这意味着不需要复杂的安装或管理。本章将讲解 Windows、Linux 和 Mac OS X 上的安装设置。

 

1.在 Windows 上安装 SQLite

请访问 https://www.sqlite.org/download.html，从 Windows 区下载预编译的二进制文件。

您需要下载 sqlite-tools-win32-*.zip 和 sqlite-dll-win32-*.zip 压缩文件。

创建文件夹 C:\sqlite，并在此文件夹下解压上面两个压缩文件，将得到 sqlite3.def、sqlite3.dll 和 sqlite3.exe 文件。

添加 C:\sqlite 到 PATH 环境变量，最后在命令提示符下，使用 sqlite3 命令，将显示如下结果。

C:\>sqlite3

SQLite version 3.7.15.2 2013-01-09 11:53:05

Enter ".help" for instructions

Enter SQL statements terminated with a ";"

sqlite>

 

2.在 Linux 上安装 SQLite

目前，几乎所有版本的 Linux 操作系统都附带 SQLite。所以，只要使用下面的命令来检查您的机器上是否已经安装了 SQLite。

$sqlite3

SQLite version 3.7.15.2 2013-01-09 11:53:05

Enter ".help" for instructions

Enter SQL statements terminated with a ";"

sqlite>

如果没有看到上面的结果，那么就意味着没有在 Linux 机器上安装 SQLite。因此，让我们按照下面的步骤安装 SQLite：

请访问 https://www.sqlite.org/download.html，从源代码区下载 sqlite-autoconf-*.tar.gz。

步骤如下：

$tar xvfz sqlite-autoconf-3071502.tar.gz

$cd sqlite-autoconf-3071502

$./configure --prefix=/usr/local

$make

$make install

上述步骤将在 Linux 机器上安装 SQLite，您可以按照上述讲解的进行验证。

 

3.在 Mac OS X 上安装 SQLite

最新版本的 Mac OS X 会预安装 SQLite，但是如果没有可用的安装，只需按照如下步骤进行：

请访问 https://www.sqlite.org/download.html，从源代码区下载 sqlite-autoconf-*.tar.gz。

步骤如下：

$tar xvfz sqlite-autoconf-3071502.tar.gz

$cd sqlite-autoconf-3071502

$./configure --prefix=/usr/local

$make

$make install

上述步骤将在 Mac OS X 机器上安装 SQLite，您可以使用下列命令进行验证：

$sqlite3

SQLite version 3.7.15.2 2013-01-09 11:53:05

Enter ".help" for instructions

Enter SQL statements terminated with a ";"

sqlite>

最后，在 SQLite 命令提示符下，使用 SQLite 命令做练习。

 

三、SQLite的数据类型

首先你会接触到一个让你惊讶的名词: Typelessness(无类型). 对! SQLite是无类型的. 这意味着你可以保存任何类型的数据到你所想要保存的任何表的任何列中, 无论这列声明的数据类型是什么(只有在一种情况下不是, 稍后解释). 对于SQLite来说对字段不指定类型是完全有效的. 如:

Create Table ex1(a, b, c);

诚然SQLite允许忽略数据类型, 但是仍然建议在你的Create Table语句中指定数据类型. 因为数据类型对于你和其他的程序员交流, 或者你准备换掉你的数据库引擎时能起到一个提示或帮助的作用. SQLite支持常见的数据类型, 如:

CREATE TABLE ex2

a VARCHAR(10),

b NVARCHAR(15),

c TEXT,

d INTEGER,

e FLOAT,

f BOOLEAN,

g CLOB,

h BLOB,

i TIMESTAMP,

j NUMERIC(10,5)

k VARYING CHARACTER (24),

l NATIONAL VARYING CHARACTER(16)

前面提到在某种情况下, SQLite的字段并不是无类型的. 即在字段类型为”Integer Primary Key”时.

 

四、SQLite3 C语言操作

SQLite提供了一系列接口供用户访问数据库，主要包括连接数据库，处理SQL，迭代查询结果等

我们下载sqlite源码包(https://www.sqlite.org/sqlite-amalgamation-3071400.zip)，只需要其中的sqlite3.c、sqlite.h即可。

（说明：libsqlite3.so是从sqlite3.c编译而来的， sqlite3可运行文件是从shell.c编译而来的）

 

1.数据库的操作

1).打开数据库

可以使用函数sqlite3_open_v2( )、 sqlite3_open( ) 、sqlite3_ open16( )。

其中sqlite3_open_v2( )函数功能最强大，也是最新的函数,尽量使用这个函数

 

sqlite3_open_v2

原型：

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 */

);

作用：打开一个数据库连接

关键的参数：flags

SQLITE_OPEN_NOMUTEX: 设置数据库连接运行在多线程模式(没有指定单线程模式的情况下)

SQLITE_OPEN_FULLMUTEX：设置数据库连接运行在串行模式。

SQLITE_OPEN_SHAREDCACHE：设置运行在共享缓存模式。

SQLITE_OPEN_PRIVATECACHE：设置运行在非共享缓存模式。

SQLITE_OPEN_READWRITE：指定数据库连接可以读写。

SQLITE_OPEN_CREATE：如果数据库不存在，则创建。

 

 

2).关闭数据库

sqlite3_close_v2

原型：

int sqlite3_close_v2(sqlite3*);

作用：关闭数据库连接，若关闭时连接上有未提交的事务，该事务会自动回滚。

 

3).例子：打开关闭数据库连接

sqlite3* pDb;

char* filename="/u01/sqlite/test.db";

sqlite3_open_v2(filename, &pDb, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_NOMUTEX | SQLITE_OPEN_SHAREDCACHE, NULL);

....

....

sqlite3_close_v2(pDb);

 

打开数据库文件test.db,对应的数据库连接可读可写，以多线程模式运行，并且运行在共享缓存模式，

执行完操作后，关闭数据库连接。

 

2.表的操作

1).更新SQL主要包括创建表，插入，删除，更新记录等

SQLite中常用的更新API有两个，一个是sqlite3_exec，另外一个是sqlite3_prepare_v2。

(1).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 */

);

 

其中参数sql可以包含多个SQL命令，语句之间以分号隔开，sqlite3_exec()将解析和执行sql字符串中的每个命令，直到到达该字符串的末尾或遇到错误。

对于运行修改数据库的命令(创建，插入，删除，更新)非常适合，一个函数调用就可以完成全部操作。

需要注意的是，虽然sqlite3_exec()可以执行多个SQL命令，但是函数不保证事务，即已执行成功的语句，不会因为后面执行失败的语句而回滚。

 

(2).sqlite3_perpare_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 */

);

 

sqlite3_exec实际上是将编译，执行进行了封装，与之等价的一组函数是 sqlite3_prepare_v2(), sqlite3_step()和sqlite3_finalize()。

sqlite3_prepare_v2()编译SQL语句生成VDBE执行码，sqlite3_step()执行，sqlite3_finalize()关闭语句句柄，释放资源。

两种方式，都可以通过调用sqlite3_changes(pdb)，得到语句影响的行数。

 

(3).两种方式比较

.sqlite3_exec方式接口使用很简单，实现同样的功能，比sqlite3_perpare_v2接口代码量少。

.sqlite3_prepare方式更高效，因为只需要编译一次，就可以重复执行N次。

.sqlite3_prepare方式支持参数化SQL。

 

鉴于两种方式的差异，对于简单的PRAGMA设置语句(PRAGMA cache_size=2000)，事务设置语句(BEGIN TRANSACTION,COMMIT,ROLLBACK)使用sqlite3_exec方式，更简单；

而对于批量的更新、查询语句，则使用sqlite3_prepare方式，更高效。

 

(4).例子：prepare方式执行多sql的例子，pNext初始化在sql语句首部，执行完一个sql后，移动到下一个sql首部

const char *pNext = (const char *)sql;

while (pNext && strlen(pNext) > 0) {

　　rc = sqlite3_prepare_v2(pDb, pNext, -1, &pStmt, &pNext);

　　if(SQLITE_OK != rc){

　　　　错误处理

　　　　break;

　　} 

    rc = sqlite3_step(pStmt);

    if(SQLITE_OK != rc && SQLITE_DONE != rc){

　　     错误处理

　　     break;

    }

    rc = SQLITE_OK;

    /*统计影响记录数目*/

    resultCount += sqlite3_changes(pDb);

    /* 清理语句句柄，准备执行下一个语句*/

    sqlite3_finalize(pStmt);

}

 

2).创建表

#include <stdio.h>

#include "sqlite3.h"

 

int main(int argc,char *argv[]){

    const char *sql_create_table="create table t(id int primary key,msg varchar(128))";

    char *errmsg = 0;

    int ret = 0;

 

    sqlite3 *db = 0;

    ret = sqlite3_open("./sqlite3-demo.db",&db);

    if(ret != SQLITE_OK){

        fprintf(stderr,"Cannot open db: %s\n",sqlite3_errmsg(db));

        return 1;

    }

    printf("Open database\n");

 

    ret = sqlite3_exec(db,sql_create_table,NULL,NULL,&errmsg);

    if(ret != SQLITE_OK){

        fprintf(stderr,"create table fail: %s\n",errmsg);

    }

    sqlite3_free(errmsg);

    sqlite3_close(db);

 

    printf("Close database\n");

 

    return 0;

}

 

3.记录的操作

1).执行查询

执行查询和前面创建表说的一样，使用的api可以是两个中的一个：

一个是sqlite3_exec，另外一个是sqlite3_prepare_v2

然后api中传入sql语句即可。

 

2).获取查询结果集

(1).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 */

);

 

该函数接收SQL语句返回的所有记录，使用sqlite内部分配的内存，将其存储在参数resultp中，

必须使用sqlite3_free_table()释放内存。由于结果集可能非常大，会导致内存撑爆，因此对于大结果集的查询，不建议采用这种方式。

 

(2).sqlite3_prepare_v2

prepare方式同样支持查询语句，主要分为3个阶段，编译，执行和结果集处理。

前面更新SQL部分已经描述了prepare的基本步骤，这里主要讲结果集处理部分。

首先通过sqlite3_column_count()可以得到结果集的列数目，

通过sqlite3_column_type()可以得到具体某列的存储类型，方便我们调用合适的sqlite3_column_xxx接口处理字段值。

主要有以下几类：

sqlite3_column_int

sqlite3_column_int64

sqlite3_column_double

sqlite3_column_text

sqlite3_column_blob

 

(3).例子：遍历结果集

int rc = sqlite3_prepare_v2(pDb, sql, -1, &pStmt, NULL);

//获取列数目

int n_columns = sqlite3_column_count(pStmt);

do{

　　ret = sqlite3_step(stmt);

　　if (ret == SQLITE_ROW)

　　{

　　　　//处理每一列

　　　　for (i = 0; i < n_columns; i++)

　　　　{

          /*获取列存储类型*/

　　　　　　type = sqlite3_column_type(stmt,i);

　　　　　　switch(type)

　　　　　　{

　　　　　　　　case SQLITE_INTEGER:

　　　　　　　　 /*处理整型*/

　　　　　　　　sqlite3_column_int(stmt,i);

　　　　　　　　break;

　　　　　　　　case SQLITE_FLOAT:

　　　　　　　　/*处理浮点数*/

　　　　　　　　sqlite3_column_double(stmt,i);

　　　　　　　　break;

　　　　　　　　case SQLITE_TEXT:

　　　　　　　　/*处理字符串*/

　　　　　　　　sqlite3_column_text(stmt,i);

              break;

　　　　　　　　case SQLITE_BLOB:

　　　　　　　　/*处理二进制*/

　　　　　　　　sqlite3_column_blob(stmt, i));

　　　　　　　  break;

　　　　　　　　case SQLITE_NULL:

　　　　　　　　/*处理空*/

　　　　　　}

　　　　}

　　}

　　else if (ret == SQLITE_DONE) //结束

　　{

　　　　break;

　　}

}while(true);

 

 

4.预编译操作

SQLite通过prepare接口可以支持参数化的SQL语句，即带问号的SQL语句。比如查询语句select * from t where id=?，

或者插入语句 insert into t(a,b,c) values(?,?,?)。通过参数化SQL，可以实现一次编译多次执行的目的

 

1).预编译操作的流程

预编译操作比较麻烦的，完整的预编译操作的流程是：

通过sqlite3_prepare_v2()创建一个sqlite3_stmt对象

通过sqlite3_bind_*()绑定预编译字段的值

通过sqlite3_step()执行SQL语句

通过sqlite3_reset()重置预编译语句，重复操作2多次

通过sqlite3_finalize()销毁资源

 

其中，

调用sqlite3_bind_xxx接口来绑定具体的参数。主要有以下几类：

sqlite3_bind_int

sqlite3_bind_int64

sqlite3_bind_double

sqlite3_bind_text

sqlite3_bind_blob

sqlite3_bind_null

关于绑定参数这里提一点，对于sqlite3_bind_text和sqlite3_bind_blob接口，绑定参数占据的存储空间是否可以被SQLite重用。接口中通过最后一个参数指定，参数值可以为SQLITE_STATIC和SQLITE_TRANSIENT。

SQLITE_STATIC：通知bind函数，参数使用空间是常量，不会改变，sqlite内部无需拷贝副本。

SQLITE_TRANSIENT:通知bind函数，参数使用空间可能会改变，sqlite内部需要有自己的副本

 

2).预编译SQL语句中可以包含如下几种形式：

?

?NNN

:VVV

@VVV

$VVV

NNN代表数字，VVV代表字符串。

 

如果是?或者?NNN，那么可以直接sqlite3_bind_*()进行操作，如果是字符串，还需要通过sqlite3_bind_parameter_index()获取对应的index，

然后再调用sqlite3_bind_*()操作。这通常用于构造不定条件的SQL语句（动态SQL语句）。

 

3).例子：批量导入

//begin a transaction

if(sqlite3_exec(pdb, "begin", NULL, NULL, &errmsg) != SQLITE_OK)

{

　　错误处理

　　return ERROR;

}

sqlite3_prepare_v2(pdb, "insert into t1 values(?,?,?);", &stmt);

for (i = 0; i < n_rows; i++)

{

　　for (j = 0; j < n_columns; j++)

　　{

　　　　switch(type)

　　　　{

　　　　　　case SQLITE_INTEGER:

　　　　　　/*处理整型*/

　　　　　　sqlite3_bind_int()

　　　　　　break;

　　　　　　case SQLITE_FLOAT:

　　　　　　/*处理浮点型*/

　　　　　　sqlite3_bind_double()

　　　　　　break;

　　　　　　case SQLITE_TEXT:

　　　　　　/*处理字符串类型*/

　　　　　　sqlite3_bind_text()

　　　　　　break;

　　　　　　case SQLITE_BLOB:

　　　　　　/*处理二进制类型*/

　　　　　　sqlite3_bind_blob

　　　　　　break;

　　　　　　case SQLITE_NULL:

　　　　　　sqlite3_bind_null(stmt, index);

　　　　　　break;  

　　　　}   

　　}

　　sqlite3_step(stmt);  //执行

　　sqlite3_reset(stmt); //将已编译的SQL语句恢复到初始状态，保留语句相关的资源

}

sqlite3_finalize(stmt); //结束语句，释放语句句柄

if(sqlite3_exec(pdb, "commit", NULL, NULL, &errmsg) != SQLITE_OK)

{

　　错误处理　　    return ERROR;

}

 

5.错误与异常：

错误处理：

API可能返回错误的整数结果代码。比如：sqlite3_open_v2() 、sqlite3_prepare()sqlite3_exec()、sqlite3_bind_xxx()、sqlite3_close() 、sqlite3_create_collation()、sqlite3_collation_needed()、sqlite3_create_function()、sqlite3_finalize()、sqlite3_sqlite3_get_table()、sqlite3_reset() 、sqlite3_step() 等。

获取错误的函数信息函数：sqlite3_errmsg().

    声明：const   char * sqlite3_errmsg(sqlite3 *) ；   语句句柄是唯一参数，返回该链接上API调用产生的最近的错误，无错误才返回“not  an  error”.

    SQLite 结果代码：《SQLite权威指南》page195.

    繁忙情况处理：sqlite3_busy_handler() , sqlite3_busy_timeout( ).注意应急计划的启动，设置合理的超时时间。

    模式改变处理：从锁定的角度来看，模式改变的时间在sqlite3_prepare()调用和sqlite3_step()调用之间。应对的方法就是处理改变重新开始。导致SQLITE_SCHEMA存在的原因：分离数据库、修改或者安装用户自定义的函数或者聚合、修改或者安装用户自定义的排序规则、修改或者安装授权函数、清理数据库空间等。这种错误情况与VDBE有关。

   

    追踪SQL：使用函数sqlite3_trace( ) .

    函数声明：void   *sqlite3_trace(sqlite* ,   void(*xTrace)(void  * , const char *) , void * );

 

6.操作控制

基本都是监视数据库的连接和事务的函数。

 

提交钩子：sqlite3_commit_hook()监视连接上的事务提交事件。

          声明:  void  *  sqlite_commit_hook(sqlite * cnx ,  /*数据库句柄*/

                                                                int  (*xCallback )(void  * data) , /*回调函数*/

                                            　　　　　  void  *data ,  /*应用程序数据*/);

                                                                                                      

回滚钩子：sqlite3_rollback_hook()监视连接上的事务提交事件。

          声明:  void  *  sqlite_rollback_hook(sqlite * cnx ,  /*数据库句柄*/

                                                                  int  (*xCallback )(void  * data) , /*回调函数*/

                                                                  void  *data ,  /*应用程序数据*/);

          注意自动回滚无法触发回调函数。

 

更新钩子：sqlite3_update_hook()监视连接上的事务提交事件。

          声明:  void  *  sqlite_update_hook(sqlite * cnx ,  /*数据库句柄*/

                                                                int  (*xCallback )(void  * data) , /*回调函数*/

                                                                void  *data ,  /*应用程序数据*/);

          此时，回调函数的行式：void callback( void     * data,/*更新钩子的第三个参数*/

                                                                       int       operation_code,/*与sqlite_update、sqlite_insert、sqlite_delete操作对应*/

                                                                       char  const    *db_name,char  const    *table_name,sqlite_int64     rowid,);   

        

7.授权函数

事件过滤器：sqlite3_set_authorizer()

声明：    int   sqlite3_set_authorizer(sqlite3  * ,int  (*xAuth)(void  *,int , const  char * , const  char * ,const  char * , const  char * ,) ,void  *pUserData);

注意：授权函数是存在回调函数的：

          int  auth(void *,   /*用户数据*/

                        int,        /*事件代码*/

                        const  char *,         /*事件具体相关的参数*/

                        const  char *,         /*事件具体相关的参数*/

                        const  char *,          /*数据库名称*/

                        const  char *,          /*触发器或者视图名称*/)

授权函数的返回值：SQLITE_OK、SQLITE_DENY(终止整个sql语句)、SQLITE_IGNORE。注意交互式程序的帮助。page209。        

        

        

五、参考的原文：

http://www.runoob.com/sqlite/sqlite-tutorial.html

https://baike.baidu.com/item/SQLite/375020?fr=aladdin

https://blog.csdn.net/mao834099514/article/details/51680855

https://www.cnblogs.com/cchust/p/5121559.html

https://blog.csdn.net/zgrjkflmkyc/article/details/45150951