第5章SQLite基础

通常情况下,数据库作为软件应用程序的主要组成部分,与数据库管理系统一样,非常的庞大,并且占用了相当多的系统资源,增加了管理的复杂性。随着嵌入式系统的飞速发展以及软件应用程序逐渐模块化,采用嵌入式特点的新型数据库管理系统要比大型复杂的传统数据库管理系统更为适用。SQLite就是这样一个十分优秀的嵌入式数据库系统。在PHP5中已经集成了SQLite的嵌入式数据库产品。SQLite也被用于很多航空电子设备、建模和仿真程序、工业控制、智能卡、决策支持包、医药信息系统等。
SQLite简单易用，同时提供了丰富的数据库接口。它的设计思想是小型、快速和最小化的管理。这对于需要一个数据库用于存储数据但又不想花太多时间来调整数据性能的开发人员很适用。实际上在很多情况下嵌入式系统的数据管理并不需要存储程序或复杂的表之间的关联。SQLite在数据库容量大小和管理功能之间找到了理想的平衡点。而且SQLite的版权允许无任何限制的应用，包括商业性的产品。完全的开源代码这一特点更使其可以称得上是理想的“嵌入式数据库”。部分开源嵌入式数据库性能对比见表51。


表51嵌入式数据库性能对比表



产 品 名 称速度稳定性数据库
容量SQL支持Win32平台
下最小体积数据操纵

SQLite最快好2TB大部分SQL92374KBSQL
Berkeley DB快好256TB不支持840KB仅应用程序接口
Firebird嵌入式服务器快好64TB完全SQL92与大部分SQL993.68MBSQL

本章主要介绍SQLite数据库的基础知识，包括数据类型、常用命令、API函数、工具和实例。目前SQLite已经于2020年1月27日进入SQLite3.31.1版本。


视频讲解



5.1SQLite的特点及适用场景
SQLite是一个开源的、内嵌式的关系型数据库。它是D.Richard Hipp采用C语言开发出来的、完全独立的、不具有外部依赖性的嵌入式数据库引擎。SQLite第一个版本发布于2000年5月，在便携性、易用性、紧凑性、有效性和可靠性方面有突出的表现。

SQLite能够运行在Windows、Linux、UNIX等各种操作系统，同时支持多种编程语言如Java、PHP、TCL、Python等。SQLite主要特点如下。
（1） 支持ACID(Atomic,Consistent,Isolated,and Durable)事务。
（2） 零配置，即无须安装和管理配置。
（3） 储存在单一磁盘文件中的一个完整的数据库。
（4） 数据文件可在不同字节顺序的机器间自由共享。
（5） 支持数据库大小至2TB。
（6） 程序体积小，全部C语言代码约3万行(核心软件，包括库和工具)，250KB大小。
（7） 相对于目前其他嵌入式数据库具有更快捷的数据操作。
（8） 支持事务功能和并发处理。
（9） 程序完全独立，不具有外部依赖性。
（10） 支持多种硬件平台，如ARM/Linux、SPARC/Solaris等。
SQLite不同于其他大部分的SQL数据库引擎,因为它的首要设计目标就是尽量简单化以达到易于管理、易于使用、易于嵌入到其他的大型程序中、易于维护和配置的目的。SQLite比较适用的场合主要包括网站,嵌入式设备和应用软件,应用程序文件格式,替代某些特别的文件格式,内部的或临时的数据库,命令行数据集分析工具,企业级数据库的替代品,数据库教学等。
5.2SQLite的存储种类和数据类型
与其他传统关系型数据库使用的是静态数据类型不同的是，SQLite3采用的是动态数据类型，即字段可以存储的数据类型是在表声明时就确定的，因此它们在数据存储方面存在着很大的差异。
SQLite将数据值的存储划分为以下几种存储类型。
(1) NULL，空值。
(2) INTEGER，整型，根据大小可以使用1、2、3、4、6、8字节来存储。
(3) REAL，浮点型，用来存储8字节的IEEE浮点。
(4) TEXT，文本字符串，使用UTF8、UTF16、UTF32等保存数据。
(5) BLOB(Binary Large Objects)，二进制类型，按照二进制存储，不做任何改变。
在SQLite中，存储种类和数据类型有一定的差别，如INTEGER存储类别可以包含6种不同长度的Integer数据类型，然而这些INTEGER数据一旦被读入到内存后，SQLite会将其全部视为占用8字节的无符号整型。因此对于SQLite而言，即使在表声明中明确了字段类型，仍然可以在该字段中存储其他类型的数据。然而需要特别说明的是，尽管SQLite提供了这种方便，但是一旦考虑到数据库平台的可移植性问题，用户在实际的开发中还是应该尽可能地保证数据类型的存储和声明的一致性。除非有极为充分的理由，同时又不再考虑数据库平台的移植问题，在此种情况下确实可以使用SQLite提供的此种特征。
需要注意的是，实际上，SQLite3也接受如下的扩展的数据类型，见表52。


表52SQLite3接受的数据类型(扩展)



数据类型说明

smallint16位元的整数
integer32位元的整数
decimal(p，s)精确值p是指该数根据权的大小排列的数位集合，s是指小数点后有几位数。如未特别指定，则默认设为 p=5; s=0
float32位元的实数
double64位元的实数
char(n)n长度的字串，n不能超过254
varchar(n)长度不固定且其最大长度为n的字串，n不能超过4000
graphic(n)类似char(n)，但其单位是两个字元doublebytes，n不能超过127
vargraphic(n)可变长度且其最大长度为n的双字元字串，n不能超过2000
date包含年份、月份、日期
time包含小时、分钟、秒

为了最大化SQLite和其他数据库引擎之间的数据类型兼容性，SQLite提出了“类型亲和性”(Type Affinity)的概念。所谓“类型亲和性”指的是在表字段被声明之后，SQLite都会根据该字段声明时的类型为其选择一种亲和类型，当数据插入时，该字段的数据将会优先采用亲和类型作为该值的存储方式，除非亲和类型不匹配或无法转换当前数据到该亲和类型，这样SQLite才会考虑其他更适合该值的类型存储该值。
在SQLite3版数据库中的列类型有五种类型亲和性： 文本类型、数字类型、整数类型、浮点类型、NULL无类型。
(1) 一个具有文本类型亲和性的列，可以使用NULL、TEXT、BLOB值类型保存数据。例如，数字数据被插入一个具有文本类型亲和性的列，在存储之前数字将被转换成文本。
(2) 一个具有数字类型亲和性的列，可以使用NULL、INTEGER、REAL、TEXT、BLOB五种值类型保存数据。例如，一个文本类型数据被插入到一个具有数字类型亲和性的列，在存储之前将被转变成整型或浮点型。
(3) 一个具有整数亲和性的列，在转换方面和具有数字亲和性的列是相同的，但也有些区别，如浮点型的值将被转换成整型。
(4) 一个具有浮点亲和性的列，可以使用REAL、FLOAT、DOUBLE值类型保存数据。
(5) 一个具有无类型亲和性的列，不会选择用哪个类型保存数据，数据不会进行任何转换。
5.3SQLite语法
SQLite库可以解析大部分的标准SQL语言,但同时它也省去了一些原有的特性,并且加入了一些自己的新特性。SQLite的语法主要针对数据表、视图、索引、事务等对象设计规则，包括结构定义、结构删除、数据操作、事务处理、其他操作等几个部分。
5.3.1数据表操作
1. 创建表

该命令的语法规则和使用方式与大多数关系型数据库基本相同，因此本章还是以示例的方式来演示SQLite中创建表的各种规则。但是对于一些SQLite特有的规则，本章会给予额外的说明。
1)  最简单的数据表



sqlite> CREATE TABLE testable (num integer);




这里需要说明的是，对于自定义数据表表名，如testtable，不能以sqlite_开头，因为以该前缀定义的表名都用于SQLite内部。
2) 创建带有默认值的数据表



sqlite> CREATE TABLE testtable (num integer DEFAULT 0, description varchar 

DEFAULT 'hello');




3) 在指定数据库创建表



sqlite> ATTACH DATABASE '/home/work/proj/mydb.db' AS mydb;

sqlite> CREATE TABLE mydb.testtable (num integer);




这里先通过ATTACH DATABASE命令将一个已经存在的数据库文件attach到当前的连接中(attach命令将在后文介绍)，之后再通过指定数据库名的方式在目标数据库中创建数据表，如mydb.testtable。关于该规则还需要给出一些额外的说明，如果我们在创建数据表时没有指定数据库名，那么将会在当前连接的main数据库(主数据库)中创建该表，在一个连接中只能有一个main数据库。如果需要创建临时表，就无须指定数据库名，见如下示例。
创建两个表，一个临时表和普通表。



sqlite> CREATE TEMP TABLE temptable(num integer);

sqlite> CREATE TABLE testtable (num integer);




将当前连接中的缓存数据导出到本地文件，同时退出当前连接。



sqlite> .backup /home/work/proj/mydb.db

sqlite> .exit




重新建立SQLite的连接，并将刚刚导出的数据库作为主库重新导入。
查看该数据库中的表信息，通过结果可以看出临时表并没有被持久化到数据库文件中。



sqlite> .tables

testtable




4)  IF NOT EXISTS从句
如果当前创建的数据表名已经存在，即与已经存在的表名、视图名或索引名冲突，那么本次创建操作将失败并报错。然而如果在创建表时加上IF NOT EXISTS从句，那么本次创建操作将不会有任何影响，即不会有错误抛出，除非当前的表名或某一索引名冲突。



sqlite> CREATE TABLE testtable (num integer);

Error: table testtable already exists

sqlite> CREATE TABLE IF NOT EXISTS testtable (numl integer);




5)  主键约束
直接在字段的定义上指定主键。



sqlite> CREATE TABLE testtable (num integer PRIMARY KEY ASC);




在所有字段已经定义完毕后，再定义表的数约束，这里定义的是基于num和description的联合主键。



sqlite> CREATE TABLE testtable2 (

...>num integer,

...>description integer,

...>PRIMARY KEY (num,description)

...> );




和其他关系型数据库一样，主键必须是唯一的。
6)  唯一性约束
直接在字段的定义上指定唯一性约束。



sqlite> CREATE TABLE testtable (num integer UNIQUE);




在所有字段已经定义完毕后，再定义表的唯一性约束，这里定义的是基于两个列的唯一性约束。



sqlite> CREATE TABLE testtable2 (

...>num integer,

...>description integer,

...>UNIQUE (num, description)

...> );




在SQLite中，NULL值被视为和其他任何值都是不同的，如下例： 



sqlite> DELETE FROM testtable;

sqlite> SELECT count(*) FROM testtable;

count(*)

----------

0

sqlite> INSERT INTO testtable VALUES(NULL);

sqlite> INSERT INTO testtable VALUES(NULL);

sqlite> SELECT count(*) FROM testtable;

count(*)

----------

2




由此可见，两次插入的NULL值均插入成功。
2. 表的修改
SQLite对ALTER TABLE命令支持得非常有限，仅支持修改表名和添加新字段。其他的功能，如重命名字段、删除字段和添加、删除约束等均未提供支持。
1) 修改表名
需要先说明的是，SQLite中表名的修改只能在同一个数据库中，不能将其移动到Attached数据库中。而且一旦表名被修改后，该表已存在的索引将不会受到影响，然而依赖该表的视图和触发器将不得不重新修改其定义。



sqlite> CREATE TABLE testtable (num integer);

sqlite> ALTER TABLE testtable RENAME TO testtable2;

sqlite> .tables

testtable2




通过.tables命令的输出可以看出，表testtable已经被修改为testtable2。
2)  新增字段



sqlite> CREATE TABLE testtable (num integer);

sqlite> ALTER TABLE testtable ADD COLUMN description integer;

sqlite> .schema testtable

CREATE TABLE "testtable" (num integer, description integer);




通过.schema命令的输出可以看出，表testtable的定义中已经包含了新增字段。关于ALTER TABLE最后需要说明的是，在SQLite中该命令的执行时间不会受到当前表行数的影响。
3. 表的删除
在SQLite中如果某个表被删除了，那么与之相关的索引和触发器也会被随之删除。而在很多其他的关系型数据库中是不可以这样的，如果必须要删除相关对象，只能在删除表语句中加入WITH CASCADE从句。见如下示例： 




sqlite> CREATE TABLE testtable (num integer);

sqlite> DROP TABLE testtable;

sqlite> DROP TABLE testtable;

Error: no such table: testtable

sqlite> DROP TABLE IF EXISTS testtable;




从上面的示例中可以看出，如果删除的表不存在，SQLite将会报错并输出错误信息。如果希望在执行时不抛出异常，可以添加IF EXISTS从句，该从句的语义和CREATE TABLE中的完全相同。
5.3.2视图的操作
1. 创建视图

这里只是给出简单的SQL命令示例，具体的含义和技术细节可以参照上面的创建数据表部分，如临时视图、IF NOT EXISTS从句等。
1) 最简单的视图



sqlite> CREATE VIEW testview AS SELECT * FROM testtable WHERE num > 10;




2) 创建临时视图



sqlite> CREATE TEMP VIEW tempview AS SELECT * FROM testtable WHERE num > 10;




3) IF NOT EXISTS从句



sqlite> CREATE VIEW testview AS SELECT * FROM testtable WHERE num > 10;

Error: table testview already exists

sqlite> CREATE VIEW IF NOT EXISTS testview AS SELECT * FROM testtable WHERE 

numl > 10;




2. 删除视图
该操作的语法和删除表基本相同，因此这里只是给出示例： 



sqlite> DROP VIEW testview;

sqlite> DROP VIEW testview;

Error: no such view: testview

sqlite> DROP VIEW IF EXISTS testview;




5.3.3索引的操作
1. 创建索引

在SQLite中，创建索引的SQL语法和其他大多数关系型数据库基本相同，因而这里也仅仅是给出示例用法，在命令行中输入以下指令，首先创建表。



sqlite> CREATE TABLE testtable (name text,num integer);




创建最简单的索引，该索引基于某个表的一个字段。



sqlite> CREATE INDEX testtable_idx ON testtable(name);




创建联合索引，该索引基于某个表的多个字段，同时可以指定每个字段的排序规则(升序/降序)。



sqlite> CREATE INDEX testtable_idx2 ON testtable(name ASC,num DESC);




创建唯一性索引，该索引规则和数据表的唯一性约束的规则相同，即NULL和任何值都不同，包括NULL本身。



sqlite> CREATE UNIQUE INDEX testtable_idx3 ON testtable(num DESC);

sqlite> .indices testtable




上述命令的输出结果如图51所示。



图51创建索引


在图51中从.indices命令的输出可以看出，三个索引均已成功创建。
2. 删除索引
索引的删除和视图的删除非常相似，含义也是如此，因此这里也只是给出示例： 



sqlite> DROP INDEX testtable_idx;




如果删除的索引不存在将会导致操作失败，在不确定索引是否存在但又不希望错误被抛出的情况下，可以使用IF EXISTS从句。



sqlite> DROP INDEX testtable_idx;

Error: no such index: testtable_idx

sqlite> DROP INDEX IF EXISTS testtable_idx;




3. 重建索引
重建索引用于删除已经存在的索引，同时基于其原有的规则重建该索引。这里需要说明的是，如果在REINDEX语句后面没有给出数据库名，那么当前连接下所有Attached数据库中所有索引都会被重建。如果指定了数据库名和表名，那么该表中的所有索引都会被重建，如果只是指定索引名，那么当前数据库的指定索引被重建。
当前连接Attached所有数据库中的索引都被重建。



sqlite> REINDEX;




重建当前主数据库中testtable表的所有索引。



sqlite> REINDEX testtable;




重建当前主数据库中名称为testtable_idx2的索引。



sqlite> REINDEX testtable_idx2;




5.3.4触发器的操作
触发器是一种特殊的存储过程，在用户试图对指定的表执行指定的数据修改语句时自动执行。SQLite的触发器操作见表53。


表53SQLite的触发器操作




名称描述基 本 语 法
Create trigger创建一个触发器Create triggertrigger_name

Database evevt ON ［database_name］ table_name trigger_action

Database evevt： insert/delete/update/update of

trigger_action： BEGIN/select_statement/ insert_statement/delete_statement/update_statement/END
Drop trigger删除一个触发器Drop trigger ［database_name］ trigger_name

5.3.5日期和时间函数
SQLite主要支持以下四种与日期和时间相关的函数。
(1)  date(timestring, modifier, modifier, ...)。
(2)  time(timestring, modifier, modifier, ...)。
(3) datetime(timestring, modifier, modifier, ...)。
(4) strftime(format, timestring, modifier, modifier, ...)。
以上四个函数都接受一个时间字符串作为参数，其后再跟有0个或多个修改符。其中，strftime()函数还接受一个格式字符串作为其第一个参数。strftime()和C运行时库中的同名函数完全相同。至于其他三个函数，date函数的默认格式为： “YYYYMMDD”，time函数的默认格式为： “H:MM:SS”，datetime函数的默认格式为： “YYYYMMDD HH:MM:SS”。
1.  strftime函数的格式信息
strftime函数的格式见表54。


表54strftime函数的格式




strftime函数的格式描述
%dday of month：  00
%ffractional seconds：  SS.SSS
%Hhour：  0024
%jday of year：  001366
%JJulian day number
%mmonth：  0112
%Mminute：  0059
%sseconds since 19700101
%Sseconds：  0059
%wday of week 06 with Sunday==0
%Wweek of year：  0053
%Yyear： 00009999
%%%

需要指出的是，其余三个时间函数均可用strftime来表示，如： 
date(...)trftime('%Y%m%d', ...)

time(...)strftime('%H： %M： %S', ...)

datetime(...)strftime('%Y%m%d %H： %M： %S', ...)。
2.  时间字符串的格式
时间字符串的格式如下。
(1) YYYYMMDD。
(2) YYYYMMDD HH:MM。
(3)  YYYYMMDD HH:MM:SS。
(4)  YYYYMMDD HH:MM:SS.SSS。
(5)  HH:MM。
(6)  HH:MM:SS。
(7)  HH:MM:SS.SSS。
(8)  now。
说明： (5)~(7)中只是包含了时间部分，SQLite将假设日期为20000101。(8)表示当前时间。
3.  修改符
修改符有如下格式。
(1)  NNN days。
(2)  NNN hours。
(3)  NNN minutes。
(4)  NNN.NNNN seconds。
(5) NNN months。
(6)  NNN years。
(7) start of month。
(8) start of year。
(9) start of day。
(10) weekday N。
其中，(1)~(6)将只是简单地加减指定数量的日期或时间值，如果NNN的值为负数，则减，否则加。(7)~(9)则将时间串中的指定日期部分设置到当前月、年或日的开始。(10)则将日期前进到下一个星期N，其中星期日为0。需要说明的是： 修改符的顺序极为重要，SQLite将会按照从左到右的顺序依次执行修改符。
4.  示例
返回当前日期。



sqlite> SELECT date('now');

2020-02-28




返回当前月的最后一天。



sqlite> SELECT date('now','start of month','1 month','-1 day');

2020-02-29




返回从19700101 00： 00： 00到当前时间所流经的秒数。



sqlite> SELECT strftime('%s','now');

1582877455




返回当前年中10月份的第一个星期二的日期。



sqlite> SELECT date('now','start of year','+9 months','weekday 2');

2020-10-06




5.3.6数据库和事物
1. Attach数据库

ATTACH DATABASE语句添加另外一个数据库文件到当前的连接中，如果文件名为“:memory:”，我们可以将其视为内存数据库，内存数据库无法持久化到磁盘文件上。如果操作Attached数据库中的表，则需要在表名前加数据库名，如dbname.table_name。最后需要说明的是，如果一个事务包含多个Attached数据库操作，那么该事务仍然是原子的。
见如下示例： 



sqlite> CREATE TABLE testtable (first_col integer);

sqlite> INSERT INTO testtable VALUES(1);

sqlite> .backup 'D:/mydb.db'--将当前连接中的主数据库备份到指定文件

sqlite> .exit




重新登录SQLite命令行工具： 



sqlite> CREATE TABLE testtable (first_col integer);

sqlite> INSERT INTO testtable VALUES(2);

sqlite> INSERT INTO testtable VALUES(1);

sqlite> ATTACH DATABASE 'D:/mydb.db' AS mydb;

sqlite> .header on--查询结果将字段名作为标题输出

sqlite> SELECT t1.first_col FROM testtable t1, mydb.testtable t2 WHERE t.first_col = 

t2.first_col;

first_col

----------

1




2. Detach数据库
DETACH DATABASE语句卸载当前连接中的指定数据库，注意main和temp数据库无法被卸载。
该示例承载上面示例的结果，即mydb数据库已经被Attach到当前的连接中。



sqlite> DETACH DATABASE mydb;

sqlite> SELECT t1.first_col FROM testtable t1, mydb.testtable t2 WHERE t.first_col = 

t2.first_col;

Error: no such table: mydb.testtable




3. 事务
事务处理是由一条或多条SQL语句序列结合在一起所形成的一个逻辑处理单元。事务处理中的每条语句都只完成整个任务中的一部分工作,所有的语句组织在一起才能够完成某个特定的任务。支持事务处理以保证一个事务内的所有操作都能完成,否则就全部取消并回复到原状态。事务处理的语法包括事务开始、事务终止、事务结束以及事务回滚四个主要部分,其具体内容见表55。


表55SQLite事务处理语法表



事 务 分 类作用基 本 语 法
Begin transaction标记一个事务的起点BEGIN［TRANSACTION ［name］］
End transaction标记一个事务的结束END［TRANSACTION ［name］］
Commit transaction标记一个事务的结束，功能同End transactionCOMMIT［TRANSACTION ［name］］
Roll back transaction将事务回滚到起点ROLL BACK［TRANSACTION ［name］］

在SQLite中，如果没有为当前的SQL命令(SELECT除外)显式地的指定事务，那么SQLite会自动为该操作添加一个隐式的事务，以保证该操作的原子性和一致性。当然，SQLite也支持显式的事务，其语法与大多数关系型数据库相比基本相同。见如下示例： 



sqlite> BEGIN TRANSACTION;

sqlite> INSERT INTO testtable VALUES(1);

sqlite> INSERT INTO testtable VALUES(2);

sqlite> COMMIT TRANSACTION;--显式事务被提交，数据表中的数据也发生了变化

sqlite> SELECT COUNT(*) FROM testtable;

COUNT(*)

----------

2

sqlite> BEGIN TRANSACTION;

sqlite> INSERT INTO testtable VALUES(1);

sqlite> ROLLBACK TRANSACTION; --显式事务被回滚，数据表中的数据没有发生变化

sqlite> SELECT COUNT(*) FROM testtable;

COUNT(*)

----------

2



5.4SQLite的内置函数
本节介绍SQLite的内置函数。SQLite本身集成了大量的内置函数以方便使用者快捷地操作、使用数据库。其内置函数(主要函数)主要分为算术函数、字符串处理函数、条件判断函数、聚合函数以及其他函数五大类,其声明和描述见表56。


表56典型内置函数



函数说明
算术函数
abs(X)该函数返回数值参数X的绝对值，如果X为NULL，则返回NULL； 如果X为不能转换成数值的字符串，则返回0； 如果X值超出Integer的上限，则抛出Integer Overflow的异常
max(X,Y,...)返回函数参数中的最大值，如果有任何一个参数为NULL，则返回NULL
min(X,Y,...)返回函数参数中的最小值，如果有任何一个参数为NULL，则返回NULL
round(X［,Y］)返回数值参数X被四舍五入到Y刻度的值，如果参数Y不存在，默认参数值为0
random()返回整型的伪随机数
条件判断函数
coalesce(X,Y,...)返回函数参数中第一个非NULL的参数，如果参数都是NULL，则返回NULL。该函数至少2个参数
ifnull(X,Y)该函数等同于两个参数的coalesce()函数，即返回第一个不为NULL的函数参数。如果两个均为NULL，则返回NULL
nullif(X,Y)如果函数参数相同，返回NULL，否则返回第一个参数
字符串处理函数
length(X)如果参数X为字符串，则返回字符的数量； 如果为数值，则返回该参数的字符串表示形式的长度； 如果为NULL，则返回NULL
lower(X)返回函数参数X的小写形式，默认情况下，该函数只能应用于ASCII字符
ltrim(X［,Y］)如果没有可选参数Y，该函数将移除参数X左侧的所有空格符； 如果有参数Y，则移除X左侧的任意在Y中出现的字符，最后返回移除后的字符串
replace(X,Y,Z)字符串类型的函数参数X中所有子字符串Y替换为字符串Z，最后返回替换后的字符串，源字符串X保持不变
rtrim(X［,Y］)如果没有可选参数Y，该函数将移除参数X右侧的所有空格符； 如果有参数Y，则移除X右侧的任意在Y中出现的字符，最后返回移除后的字符串
substr(X,Y［,Z］)返回函数参数X的子字符串，从X中截取Z长度的字符，如果忽略Z参数，则取第Y个字符后面的所有字符。如果Z的值为负数，则从第Y位开始，向左截取abs(Z)个字符。如果Y值为负数，则从X字符串的尾部开始计数到第abs(Y)的位置开始
trim(x［,y］)如果没有可选参数Y，该函数将移除参数X两侧的所有空格符； 如果有参数Y，则移除X两侧的任意在Y中出现的字符，最后返回移除后的字符串
upper(X)返回函数参数X的大写形式，默认情况下，该函数只能应用于ASCII字符
聚合函数
avg(x)该函数返回在同一组内参数字段的平均值。对于不能转换为数字值的STRING和BLOB类型的字段值，如‘HELLO’，SQLite会将其视为0。avg函数的结果总是浮点型，唯一的例外是所有的字段值均为NULL，那样该函数的结果也为NULL
count(x|*)count(x)函数返回在同一组内，x字段中值不等于NULL的行数。count(*)函数返回在同一组内的数据行数
group_concat(x［,y］)该函数返回一个字符串，该字符串将会连接所有非NULL的x值。该函数的y参数将作为每个x值之间的分隔符，如果在调用时忽略该参数，在连接时将使用默认分隔符“，”
续表



函数说明
聚合函数
max(x)该函数返回同一组内的x字段的最大值，如果该字段的所有值均为NULL，该函数也返回NULL
min(x)该函数返回同一组内的x字段的最小值，如果该字段的所有值均为NULL，该函数也返回NULL
sum(x)该函数返回同一组内的x字段值的总和，如果字段值均为NULL，该函数也返回NULL。如果所有的x字段值均为整型或者NULL，该函数返回整型值，否则就返回浮点型数值。如果所有的数据值均为整型，一旦结果超过上限时将会抛出integer overflow的异常
total(x)该函数不属于标准SQL，其功能和sum基本相同，只是计算结果比sum更为合理。例如，当所有字段值均为NULL时，和sum不同的是，该函数返回0.0。另外该函数始终返回浮点型数值。该函数始终都不会抛出异常
其他函数
changes()该函数返回最近执行的INSERT、UPDATE和DELETE语句所影响的数据行数，也可以通过执行C/C++函数sqlite3_changes()得到相同的结果
total_changes()该函数返回自从该连接被打开时起，INSERT、UPDATE和DELETE语句总共影响的行数，也可以通过C/C++接口函数sqlite3_total_changes()得到相同的结果
typeof(X)返回函数参数数据类型的字符串表示形式，如INTEGER、TEXT、REAL、NULL等


这里还需要进一步说明的是，对于所有聚合函数而言，distinct关键字可以作为函数参数字段的前置属性，以便在进行计算时忽略所有重复的字段值，如count(distinct x)。图52显示了SQLite官网上对主要内置函数的列表显示。



图52SQLite的内置函数


5.5SQLite的运算符
SQLite的运算符是一个保留字或字符，主要用于在SQLite语句中的WHERE子句中执行操作，如比较和算术运算等。SQLite主要有数学运算符、比较运算符、逻辑运算符和位运算符四种。
5.5.1数学运算符
所有的数学运算符(+,-,*,/,%,<<,>>,&和|)在执行之前都会先将操作数转换为NUMERIC存储类型，即使在转换过程中可能会造成数据信息的丢失。此外，如果其中一个操作数为NULL，那么它们的结果也为NULL。


图53SQLite的数学运算符应用

在数学操作符中，如果其中一个操作数看上去并不像数值类型，那么它们结果为0或0.0。图53显示了使用了数学运算符的实例图。
5.5.2比较运算符
在SQLite中支持的比较运算符有=,==,<,<=,>,>=,!=,<>,IN,NOT IN,BETWEEN,IS和IS NOT。
数据的比较结果主要依赖于操作数的存储方式，其规则如下。
(1) 存储方式为NULL的数值小于其他存储类型的值。
(2) 存储方式为INTEGER和REAL的数值小于TEXT或BLOB类型的值，如果同为INTEGER或REAL，则基于数值规则进行比较。
(3) 存储方式为TEXT的数值小于BLOB类型的值，如果同为TEXT，则基于文本规则(ASCII值)进行比较。
(4) 如果是两个BLOB类型的数值进行比较，其结果为C运行时函数memcmp()的结果。
下面给出一个实例。假设变量a=10，变量b=20，则表57列举了各比较运算符的应用情况。


表57比较运算符



运算符描述实例

==检查两个操作数的值是否相等，如果相等则条件为真(a==b)不为真
=检查两个操作数的值是否相等，如果相等则条件为真(a=b)不为真
!=检查两个操作数的值是否相等，如果不相等则条件为真(a!=b)为真
<>检查两个操作数的值是否相等，如果不相等则条件为真(a<>b)为真
>检查左操作数的值是否大于右操作数的值，如果是则条件为真(a>b)为假
<检查左操作数的值是否小于右操作数的值，如果是则条件为真(a<b)为真
>=检查左操作数的值是否大于或等于右操作数的值如果是则条件为真(a>=b)为假
<=检查左操作数的值是否小于或等于右操作数的值，如果是则条件为真(a<=b)为真
!<检查左操作数的值是否不小于右操作数的值，如果是则条件为真(a!<b)为假
!>检查左操作数的值是否不大于右操作数的值，如果是则条件为真(a!>b)为真

5.5.3逻辑运算符
SQLite逻辑运算符见表58。


表58逻辑运算符




运算符描述
ANDAND运算符允许在一个SQL语句的WHERE子句中的多个条件的存在
BETWEENBETWEEN运算符用于在给定最小值和最大值范围内的一系列值中搜索值
EXISTSEXISTS运算符用于在满足一定条件的指定表中搜索行的存在
ININ运算符用于把某个值与一系列指定列表的值进行比较
NOT ININ运算符的对立面，用于把某个值与不在一系列指定列表的值进行比较
LIKELIKE运算符用于把某个值与使用通配符运算符的相似值进行比较
GLOBGLOB运算符用于把某个值与使用通配符运算符的相似值进行比较。GLOB与LIKE不同之处在于，它是大小写敏感的
NOTNOT运算符是所用的逻辑运算符的对立面，如NOT EXISTS、NOT BETWEEN、NOT IN，等，它是否定运算符
OROR运算符用于结合一个SQL语句的WHERE子句中的多个条件
IS NULLNULL运算符用于把某个值与NULL值进行比较
ISIS运算符与=相似
IS NOTIS NOT运算符与!=相似
||连接两个不同的字符串，得到一个新的字符串
UNIQUEUNIQUE运算符搜索指定表中的每一行，确保唯一性(无重复)

5.5.4位运算符
如果A=60，且B=13，则表59列举了各位运算符的应用情况。


表59位运算符




运算符描述实例
&如果同时存在于两个操作数中，二进制AND运算符复制一位到结果中(A&B)将得到12，即为0000 1100
|如果存在于任一操作数中，二进制OR运算符复制一位到结果中(A|B)将得到61，即为0011 1101
~二进制补码运算符是一元运算符，具有“翻转”位效应。(~A)将得到-61，即为1100 0011，2的补码形式，带符号的二进制数
<<二进制左移运算符。左操作数的值向左移动右操作数指定的位数A<<2将得到240，即为1111 0000
>>二进制右移运算符。左操作数的值向右移动右操作数指定的位数A>>2将得到15，即为0000 1111

5.6SQLite的常用命令
用户可以在任何时候输入“.help”，列出可用的命令。



sqlite> .help

.bail ON|OFFStop after hitting an error. Default OFF

.databasesList names and files of attached databases

.dump ?TABLE? ...Dump the database in an SQL text format

.echo ON|OFFTurn command echo on or off

.exit Exit this program

.explain ON|OFF Turn output mode suitable for EXPLAIN on or off.

.header(s) ON|OFF Turn display of headers on or off

.help Show this message

.import FILE TABLEImport data from FILE into TABLE

.indices TABLEShow names of all indices on TABLE

.load FILE ?ENTRY?Load an extension library

.mode MODE ?TABLE?Set output mode where MODE is one of:

csvComma-separated values

columnLeft-aligned columns. (See .width)

htmlHTML <table> code

insertSQL insert statements for TABLE

lineOne value per line

listValues delimited by .separator string

tabsTab-separated values

tcl TCL list elements

.nullvalue STRINGPrint STRING in place of NULL values

.output FILENAMESend output to FILENAME

.output stdoutSend output to the screen

.prompt MAIN CONTINUE Replace the standard prompts

.quit Exit this program

.read FILENAMEExecute SQL in FILENAME

.schema ?TABLE? Show the CREATE statements

.separator STRING Change separator used by output mode and .import

.show Show the current values for various settings

.tables ?PATTERN? List names of tables matching a LIKE pattern

.timeout MS Try opening locked tables for MS milliseconds

.width NUM NUM ...Set column widths for "column" mode




表510给出SQLite一些常用命令及说明。


表510SQLite常用命令及说明




命令说明
.backup ?DB? FILE备份DB数据库(默认是“main”)到FILE文件
.bail ON|OFF发生错误后停止，默认为OFF
.databases列出附加数据库的名称和文件
.dump ?TABLE?以SQL文本格式转储数据库。如果指定了TABLE表，则只转储匹配LIKE模式的TABLE表
.echo ON|OFF开启或关闭echo命令
.exit退出SQLite提示符
.explain ON|OFF开启或关闭适合于EXPLAIN的输出模式。如果没有带参数，则为EXPLAIN on，即开启EXPLAIN
.header(s) ON|OFF开启或关闭头部显示
续表




命令说明
.help显示帮助消息
.import FILE TABLE导入来自FILE文件的数据到TABLE表中
.indices ?TABLE?显示所有索引的名称。如果指定了TABLE表，则只显示匹配LIKE模式的TABLE表的索引
.load FILE ?ENTRY?加载一个扩展库
.log FILE|off开启或关闭日志。FILE文件可以是stderr(标准错误)/stdout(标准输出)
.mode MODE设置输出模式，MODE可以是下列之一： 

csv逗号分隔的值； 

column左对齐的列； 

htmlHTML的<table>代码； 

insertTABLE表的SQL插入(insert)语句； 

line每行一个值； 

list由.separator字符串分隔的值； 

tabs由Tab分隔的值； 

tcl TCL列表元素
.nullvalue STRING在NULL值的地方输出STRING字符串
.output FILENAME发送输出到FILENAME文件
.output stdout发送输出到屏幕
.print STRING逐字地输出STRING字符串
.prompt MAIN CONTINUE替换标准提示符
.quit退出SQLite提示符
.read FILENAME执行FILENAME文件中的SQL
.schema ?TABLE?显示CREATE语句。如果指定了TABLE表，则只显示匹配LIKE模式的TABLE表
.separator STRING改变输出模式和.import所使用的分隔符
.show显示各种设置的当前值
.stats ON|OFF开启或关闭统计
.tables ?PATTERN?列出匹配LIKE模式的表的名称
.timeout MS尝试打开锁定的表MS（μs）
.width NUM NUM为column模式设置列宽度
.timer ON|OFF 开启或关闭CPU定时器测量

值得注意的是，确保sqlite>提示符与点命令之间没有空格，否则将无法正常工作。
下面举几个命令行的例子(本章例子均在Linux下运行，内核版本2.6.38)。
(1) 备份和还原数据库。
在当前连接的main数据库中创建一个数据表，之后再通过.backup命令将main数据库备份到/sqlite1/my.db文件中。



sqlite> CREATE TABLE mytable (first_col integer);

sqlite> .backup '/sqlite1/my.db '

sqlite> .exit




(2)  DUMP数据表的创建语句到指定文件。
先将命令行当前的输出重定向到/sqlite1/myoutput.txt，之后再将之前创建的mytable表的声明语句输出到该文件。



sqlite> .output /sqlite1/myoutput.txt

sqlite> .dump mytabl%

sqlite> .exit




(3)  显示当前连接的所有Attached数据库和main数据库。



sqlite> ATTACH DATABASE '/sqlite1/my.db ' AS mydb;

sqlite> .databases

seqnamefile

------------------------------------------

0main

2mydb/sqlite1/my.db




(4)  显示main数据库中的所有数据表。



sqlite> .tables

mytable




(5)  显示匹配表名mytabl%的数据表的所有索引。



sqlite> CREATE INDEX myindex on mytable(first_col);

sqlite> .indices mytabl%

myindex




(6) 显示匹配表名mytable%的数据表的Schema信息，依赖该表的索引信息也被输出。



sqlite> .schema mytabl%

CREATE TABLE mytable (first_col integer);

CREATE INDEX myindex on mytable(first_col);




5.7SQLite的C/C++接口
本节介绍C/C++接口的相关知识。SQLite有超过225个API，此外还有一些数据结构和预定义。然而，大多数API是可选的，非常专业。核心应用编程接口小，简单易于学习。
从功能的角度来区分，SQLite的API可分为两类： 核心API和扩充API。核心API由所有完成基本数据库操作的函数构成，主要包括连接数据库、执行SQL和遍历结果集等。它还包括一些功能函数，用来完成字符串格式化、操作控制、调试和错误处理等任务。扩充API提供不同的方法来扩展SQLite，它向用户提供创建自定义的SQL扩展，并与SQLite本身的SQL相集成等功能。
以下2个对象和8个函数构成了SQLite接口的关键元素。
sqlite3： 数据库连接对象。由sqlite3_open()创建，由sqlite3_close()销毁。
sqlite3_stmt： 准备好的语句对象。由sqlite3_prepare()创建，并由sqlite3_finalize()销毁。sqlite3_stmt对象的一个实例表示一条已编译成二进制形式并准备好进行计算的SQL语句。也就是说，把每一条SQL语句看作一个独立的计算机程序，原始的SQL文本是源代码，准备好的语句对象sqlite3_stmt是编译后的目标代码，在运行之前，所有的SQL都必须转换成一个准备好的语句。
sqlite3_open()： 打开与新的或现有的SQLite数据库的连接。SQLite3的构造函数。
sqlite3_prepare()： 将SQL文本编译成字节码，用于查询或更新数据库。sqlite3_stmt的构造函数。
sqlite3_bind()： 将应用程序数据存储到原始SQL的参数中。
sqlite3_step()： 将sqlite3_stmt推进到下一个结果行或完成。
SqLite3_column()： SQLite3_stmt的当前结果行中的列值。
Sqlite3_finalize()： SQLite3_stmt的析构函数。
SqLite3_close()： SQLite3的析构函数。
sqlite3_exec()： 为一个或多个SQL语句的字符串执行sqlite3_prepare()、sqlite3_step()、sqlite3_column()和sqlite3_finalize()的包装函数。


视频讲解



5.7.1核心C API函数
1. 预编译查询

核心C API主要与执行SQL命令有关。核心C API大约有10个，它们分别如下。



sqlite3_open()

sqlite3_prepare()

sqlite3_step()

sqlite3_column()

sqlite3_finalize()

sqlite3_close()

sqlite3_exec()

sqlite3_get_table()

sqlite3_reset()

sqlite3_bind()




有两种方法执行SQL语句： 预编译查询(Prepared Query)和封装查询。预编译查询由三个阶段构成： 准备(preparation)、执行(execution)和定案(finalization)。封装查询只是对预编译查询的三个过程进行了封装，最终也会转化为预编译查询来执行。
预处理查询是SQLite执行所有SQL命令的方式，主要包括以下三个步骤： 
1)  准备
分词器、分析器和代码生成器把SQL语句编译成虚拟机字节码，编译器会创建一个语句句柄(sqlite3_stmt)，它包括字节码以及其他执行命令和遍历结果集所需的全部资源。相应的C API为sqlite3_prepare()，位于prepare.c文件中，有多种类似的形式，如sqlite3_prepare()、sqlite3_prepare16()、sqlite3_prepare_v2()等。完整的API语法如下： 



int sqlite3_prepare(

sqlite3 *db,

/* db为sqlite3的句柄*/

const char *zSql,

/* zSql为要执行的SQL语句 */

int nByte,

/* nByte为要执行语句在zSql中的最大长度，如果是负数，那么就需要重新自动计算 */

sqlite3_stmt **ppStmt,/* ppStmt为预编译后的句柄 */

const char **pzTail /* pzTail预编译后剩下的字符串（未预编译成功或者多余的)的指
针，一般传入0或者NULL即可*/

);




sqlite3_prepare 接口把一条SQL语句编译成字节码留给后面的执行函数。使用该接口访问数据库是当前比较好的一种方法。
sqlite3_prepare16()原型如下： 



int sqlite3_prepare16(sqlite3*，const void*，int，sqlite3_stmt**，const void**)； 




sqlite3_prepare()处理的SQL语句是UTF8编码的，而sqlite3_prepare16()则要求是UTF16编码的。
相对于sqlite3_prepare()来说，sqlite3_prepare_v2()提供了一个更好的接口，保留旧的sqlite3_prepare()是为了向后兼容。建议使用sqlite3_prepare_v2()，而不是sqlite3_prepare()。
sqlite3_prepare_v2()原型如下： 



int sqlite3_prepare_v2(

sqlite3 *db,/* db为sqlite3的句柄*/

const char *zSql, /* zSql为要执行的SQL语句*/

int nByte,/* nByte为要执行语句在zSql中的最大长度，如果是负数，那么就需
要重新自动计算*/

sqlite3_stmt **ppStmt,/* ppStmt为预编译后的句柄*/

const char **pzTail /* pzTail预编译后剩下的字符串(未预编译成功或者多余的)的指针*/

);




2)  执行
虚拟机执行字节码的执行过程是一个步进(stepwise)的过程，每一步由sqlite3_step()启动，并由虚拟机执行一段字节码。当第一次调用sqlite3_step()时，一般会获得一种锁，锁的种类由命令要做什么(读或写)决定。对于SELECT语句，每次调用sqlite3_step()使用语句句柄的游标移到结果集的下一行。对于其他SQL语句(INSERT、UPDATE、DELETE等)，第一次调用sqlite3_step()会导致VDBE执行整个命令。在SQL声明准备好之后，需要调用以下的方法来执行： 



int sqlite3_step(sqlite3_stmt*);




如果SQL返回了一个单行结果集，sqlite3_step() 函数将返回 SQLITE_ROW； 如果SQL语句执行成功或者正常将返回 SQLITE_DONE，否则将返回错误代码。 如果不能打开数据库文件，则会返回 SQLITE_BUSY； 如果函数的返回值是 SQLITE_ROW，那么下列方法可以用来获得记录集行中的数据。



const void *sqlite3_column_blob(sqlite3_stmt*， int iCol);

int sqlite3_column_bytes(sqlite3_stmt*， int iCol);

int sqlite3_column_bytes16(sqlite3_stmt*， int iCol);

int sqlite3_column_count(sqlite3_stmt*);

const char *sqlite3_column_decltype(sqlite3_stmt *， int iCol);

const void *sqlite3_column_decltype16(sqlite3_stmt *， int iCol);

double sqlite3_column_double(sqlite3_stmt*， int iCol);

int sqlite3_column_int(sqlite3_stmt*， int iCol);

long long int sqlite3_column_int64(sqlite3_stmt*， int iCol);

const char *sqlite3_column_name(sqlite3_stmt*， int iCol);

const void *sqlite3_column_name16(sqlite3_stmt*， int iCol);

const unsigned char *sqlite3_column_text(sqlite3_stmt*， int iCol);

const void *sqlite3_column_text16(sqlite3_stmt*， int iCol);

int sqlite3_column_type(sqlite3_stmt*， int iCol);




sqlite3_column_count()函数返回结果集中包含的列数。sqlite3_column_count()可以在执行了sqlite3_prepare()之后的任何时刻调用。sqlite3_data_count()除了必须要在sqlite3_step()之后调用之外，其他与sqlite3_column_count()大同小异。如果调用sqlite3_step()返回值是SQLITE_DONE或者一个错误代码，则此时调用sqlite3_data_count()将返回0，然而sqlite3_column_count()仍然会返回结果集中包含的列数。
返回的记录集通过使用其他的几个sqlite3_column_*()函数来提取。所有的这些函数都把列的编号作为第二个参数。列编号从左到右以零起始，请注意它和之前那些从1起始的参数的不同。
sqlite3_column_type()函数返回第N列的值的数据类型。具体的返回值如下： 



#define SQLITE_INTEGER1

#define SQLITE_FLOAT 2

#define SQLITE_TEXT3

#define SQLITE_BLOB4

#define SQLITE_NULL 5




sqlite3_column_decltype()用来返回该列在CREATE TABLE语句中声明的类型。它可以用在当返回类型是空字符串时，sqlite3_column_name()返回第N列的字段名。sqlite3_column_bytes()用来返回UTF8编码的BLOBs列的字节数或者TEXT字符串的字节数。sqlite3_column_bytes16()对于BLOBs列返回同样的结果，但是对于TEXT字符串则按UTF16的编码来计算字节数。sqlite3_column_blob()返回BLOB数据。sqlite3_column_text()返回UTF8编码的TEXT数据。sqlite3_column_text16()返回UTF16编码的TEXT数据。sqlite3_column_int()以本地主机的整数格式返回一个整数值。sqlite3_column_int64()返回一个64位的整数。最后sqlite3_column_double()返回浮点数。
需要注意的是，不一定非要按照sqlite3_column_type()接口返回的数据类型来获取数据，数据类型不同时软件将自动转换。
3)  定案
虚拟机关闭语句，释放资源。相应的C API为sqlite3_finalize()，它导致虚拟机结束程序运行并关闭语句句柄。如果事务是由人工控制开始的，它必须由人工控制进行提交或回卷，否则sqlite3_finalize()会返回一个错误。当sqlite3_finalize()执行成功，所有与语句对象关联的资源都将被释放。在自动提交模式下，还会释放关联的数据库锁。
综合来看，预编译查询的执行流程如图54所示。



图54预编译查询执行流程图


封装查询简单地将上述三个步骤封装成一个函数引用,使得系统在某些环境下执行特定指令时非常便利。但无论是哪一种方法,都遵循这样一个规则： 方法的封装性越强,它在执行和获取结果方面上的控制性将越差。因此,预编译查询与封装查询相比,提供了更多的特征、更多的控制以及更多的信息。
另一方面,在实际应用中,每一种查询都有适合它自己的用途。函数更适合执行如创建、删除、插入以及更新等修改类命令,而预编译查询则更为适合执行查询类的命令。
2. sqlite3_open()或sqlite3_open16()函数
打开数据库用sqlite3_open()或sqlite3_open16()函数，它们的声明如下： 



int sqlite3_open(

const char *filename，/* 数据库文件名 (UTF8) */

sqlite3 **ppDb 		　　/* 输出数据库句柄 */

); 

int sqlite3_open16( 

const void *filename，	　　/* 数据库文件名 (UTF16) */

sqlite3 **ppDb 		　　/* 输出数据库句柄 */

);




其中，filename参数可以是一个操作系统文件名，或字符串，或一个空指针(NULL)。如果使用后两者将创建内存数据库。当filename不为空时，函数先尝试打开，如果文件不存在，则用该名字创建一个新的数据库。
在SQLite中，数据库通常是存储在磁盘文件中的。然而在有些情况下，可以让数据库始终驻留在内存中。最常用的一种方式是在调用sqlite3_open()时，数据库文件名参数传递“:memory:”，如： 



rc = sqlite3_open(":memory:", &db);




在调用完以上函数后，不会有任何磁盘文件被生成，取而代之的是，一个新的数据库在纯内存中被成功创建了。由于没有持久化，该数据库在当前数据库连接被关闭后就会立刻消失。需要注意的是，尽管多个数据库连接都可以通过上面的方法创建内存数据库，然而它们却是不同的数据库，相互之间没有任何关系。事实上，我们也可以通过ATTACH命令将内存数据库像其他普通数据库一样，附加到当前的连接中，如： 



ATTACH DATABASE ':memory:' AS aux1;




在调用sqlite3_open()函数或执行ATTACH命令时，如果数据库文件参数传的是空字符串，那么一个新的临时文件将被创建作为临时数据库的底层文件，如： 



rc = sqlite3_open("", &db);




或



ATTACH DATABASE '' AS aux2;




和内存数据库非常相似，两个数据库连接创建的临时数据库也是各自独立的，在连接关闭后，临时数据库将自动消失，其底层文件也将被自动删除。尽管磁盘文件被创建用于存储临时数据库中的数据信息，但是实际上临时数据库也会和内存数据库一样通常驻留在内存中。唯一不同的是，当临时数据库中数据量过大时，SQLite为了保证有更多的内存可用于其他操作，会将临时数据库中的部分数据写到磁盘文件中，而内存数据库则始终会将数据存放在内存中。
3. sqlite3_close()函数
关闭数据库用sqlite3_close()函数，声明如下： 



int sqlite3_close(sqlite3*); 




为了sqlite3_close()能够成功执行，所有与连接所关联的且已编译的查询必须被定案。如果仍然有查询没有定案，sqlite3_close()将返回SQLITE_BUSY和错误信息。
4. sqlite3_exec()函数
对于用户而言，在SQLite C/C+ API中使用频率最高的3个函数是： sqlite3_open()，sqlite3_close()和sqlite3_exec()。sqlite3_exec()的作用是解析并执行由SQL参数所给的每个命令，直到字符串结束或者遇到错误为止。大部分SQL操作都可以通过sqlite3_exec来完成，它的API形式如下： 



int sqlite3_exec(

sqlite3*,/* 数据库句柄 */

const char *sql, /* 要执行的SQL语句*/

int (*callback)(void*,int,char**,char**),/* callback回调函数 */

void *,/* void *回调函数的第一个参数 */

char **errmsg/* errmsg错误信息，如果没有SQL问题则值为NULL */

);




回调函数是一个比较复杂的函数。原型如下： 



int callback(void *params,int column_size,char **column_value,char **column_name)




参数说明如下。
params是sqlite3_exec传入的第四个参数。
column_size是结果字段的个数。
column_value是返回记录的一位字符数组指针。
column_name是结果字段的名称。
通常情况下callback在select操作中会使用到，尤其是处理每一行记录数时。返回的结果每一行记录都会调用下“回调函数”。如果回调函数返回了非0，那么sqlite3_exec将返回SQLITE_ABORT，并且之后的回调函数也不会执行，同时未执行的子查询也不会继续执行。
对于更新、删除、插入等不需要回调函数的操作，sqlite3_exec的第三、第四个参数可以传入0或者NULL。
通常情况下，sqlite3_exec返回SQLITE_OK=0的结果，非0结果可以通过errmsg来获取对应的错误描述。在SQLite3里sqlite3_exec可以被接口封装起来使用。
5.  sqlite3_bind
SQL声明可以包含一些型如“?”或“?nnn”或“:aaa”的标记，其中“nnn”是一个整数，“aaa”是一个字符串。这些标记代表一些不确定的字符值(或者通配符)，用户可以在后面用sqlite3_bind接口来填充这些值。每一个通配符都被分配了一个编号(由它在SQL声明中的位置决定，从1开始)。相同的通配符可以在同一个SQL声明中出现多次。在这种情况下所有相同的通配符都会被替换成相同的值，没有被绑定的通配符将自动取NULL值。



int sqlite3_bind_blob(sqlite3_stmt*， int， const void*， int n， void(*)(void*));

int sqlite3_bind_double(sqlite3_stmt*， int， double);

int sqlite3_bind_int(sqlite3_stmt*， int， int);

int sqlite3_bind_int64(sqlite3_stmt*， int， long long int);

int sqlite3_bind_null(sqlite3_stmt*， int);

int sqlite3_bind_text(sqlite3_stmt*， int， const char*， int n， void(*)(void*));

int sqlite3_bind_text16(sqlite3_stmt*， int， const void*， int n， void(*)(void*));

int sqlite3_bind_value(sqlite3_stmt*， int， const sqlite3_value*);




以上是 sqlite3_bind 所包含的全部接口，其功能是给SQL声明中的通配符赋值。没有绑定的通配符则被认为是空值。绑定上的值不会被sqlite3_reset()函数重置，但是在调用了sqlite3_reset()之后所有的通配符都可以被重新赋值。sqlite3_reset()函数用来重置一个SQL声明的状态，使得它可以被再次执行。
6.  sqlite3_get_table()函数
sqlite3_get_table的说明如下： 



int sqlite3_get_table(

sqlite3 *db,

/* db是sqlite3的句柄*/

const char *zSql,

/* zSql是要执行的sql语句 */

char ***pazResult,

/* pazResult是执行查询操作的返回结果集 */

int *pnRow,

/* pnRow是记录的行数*/

int *pnColumn, /* pnColumn是记录的字段个数 */

char **pzErrmsg /* pzErrmsg是错误信息 */

);




由于sqlite3_get_table是sqlite3_exec的包装，因此返回的结果和sqlite3_exec类似。
5.7.2扩充C API 函数
SQLite的扩充API用来支持用户自定义的函数、聚合和排序法。用户自定义函数是一个SQL函数，它对应于用C语言或其他语言实现的函数的句柄。使用C API时，这些句柄用C/C++实现。用户自定义函数可以在注册之后像系统内置函数一样应用于语句中。自定义函数的使用类似于存储过程,不但方便了用户对常见功能的调用,也使得数据库执行的速度得到了较大的提高。
1.  简单函数和聚合函数
用户自定义函数从整体上可以分为两类： 简单函数和聚合函数。其中,简单函数可以用在任何的表达式中,聚合函数经常用在select语句中。
sqlite3_create_function()函数用于注册或者删除用户自定义函数。其声明如下所示： 



typedef struct sqlite3_value sqlite3_value;

int sqlite3_create_function(

sqlite3 *，

const char *zFunctionName，

int nArg，

int eTextRep，

void*，

void (*xFunc)(sqlite3_context*，int，sqlite3_value**)，

void (*xStep)(sqlite3_context*，int，sqlite3_value**)，

void (*xFinal)(sqlite3_context*)

);

int sqlite3_create_function16(

sqlite3*，

const void *zFunctionName，

int nArg，

int eTextRep，

void*，






void (*xFunc)(sqlite3_context*，int，sqlite3_value**)，

void (*xStep)(sqlite3_context*，int，sqlite3_value**)，

void (*xFinal)(sqlite3_context*)

);

#define SQLITE_UTF81

#define SQLITE_UTF162

#define SQLITE_UTF16BE3

#define SQLITE_UTF16LE4

#define SQLITE_ANY5




sqlite3_create_function16()和sqlite_create_function()的不同就在于自定义的函数名一个要求是UTF16编码，而另一个则要求是UTF8编码。
自定义函数传递参数有两种方式，第一种是在注册时用pUserData传入，第二种是在调用已经注册的函数时传入参数。
对于简单函数而言，只需要设置xFunc参数，而把xStep和xFinal设为NULL。但是对于聚合函数而言，则需要设置xStep和xFinal参数，而把xFunc设为NULL。
其他的用户自定义函数接口主要还有如下几种。
Void(*func)(sqlite3_context*，int，sqlite3_value**)是一个回调函数，第一个参数表示用户自定义函数的格式，第二个参数表示自定义函数的参数个数，第三个参数表示自定义函数的值。
Void*sqlite3_user_data(sqlite3_context*)函数用以返回用户注册函数时传入的参数void*pUserData。
用户自定义聚合函数的代码部分主要包含了两个回调函数的编写以及聚合函数的注册，其基本步骤如下： 用户自定义所用聚合函数的状态结构，利用sqlite3_aggregate_context(sqlite3_context*，sizeof(struct_custom_agg))分配状态结构空间，多次调用xStep()，在查询结果的每一行上运行xStep()进行数据处理，在xFinal()中，利用sqlite3_aggregate_context(sqlite3_context*，0)得到状态结构，并且设置返回值，利用sqlite3_create_function()注册聚合函数，在SQL语句中调用聚合函数。
下面的函数用来从sqlite3_value结构体中提取数据： 



const void *sqlite3_value_blob(sqlite3_value*);

int sqlite3_value_bytes(sqlite3_value*);

int sqlite3_value_bytes16(sqlite3_value*);

double sqlite3_value_double(sqlite3_value*);

int sqlite3_value_int(sqlite3_value*);

long long int sqlite3_value_int64(sqlite3_value*);

const unsigned char *sqlite3_value_text(sqlite3_value*);

const void *sqlite3_value_text16(sqlite3_value*);

int sqlite3_value_type(sqlite3_value*);




上面的函数调用以下的API来获得上下文内容和返回结果： 



void *sqlite3_aggregate_context(sqlite3_context*， int nbyte);

void *sqlite3_user_data(sqlite3_context*);

void sqlite3_result_blob(sqlite3_context*， const void*， int n， void(*)(void*));






void sqlite3_result_double(sqlite3_context*， double);

void sqlite3_result_error(sqlite3_context*， const char*， int);

void sqlite3_result_error16(sqlite3_context*， const void*， int);

void sqlite3_result_int(sqlite3_context*， int);

void sqlite3_result_int64(sqlite3_context*， long long int);

void sqlite3_result_null(sqlite3_context*);

void sqlite3_result_text(sqlite3_context*， const char*， int n， void(*)(void*));

void sqlite3_result_text16(sqlite3_context*， const void*， int n， void(*)(void*));

void sqlite3_result_value(sqlite3_context*， sqlite3_value*);

void *sqlite3_get_auxdata(sqlite3_context*， int);

void sqlite3_set_auxdata(sqlite3_context*， int， void*， void (*)(void*))




用户自定义函数注册的流程如图55所示。


图55用户自定义函数注册流程图



2. 排序
总体来说,排序包含了对字符的排序以及对字符串的排序。排序通常采用排序序列的方法。一个排序序列就是一个字符清单,清单中的字符已经由确定位置的数字值安排好。这个按顺序排列的清单用于指明字符是如何排序的。排序可以辨别出系统任意给定的两个字符或字符串的先后顺序。
下面的函数用来实现用户自定义的排序规则： 



int sqlite3_create_collation(

sqlite3* db，

const char*zName，

int pref16，

void* pUserData，

int(*xcompare)(void*，int，const void*，int，const void*)

);




sqlite3_create_collation()函数主要用来声明一个排序序列以及实现它的比较函数。其中，比较函数int(*xcompare)(void*，int，const void*，int，const void*)只能用来进行文本的比较。
5.8SQLite工具
SQLite提供了7个工具帮助用户更好地使用SQLite。它们分别是： 命令行Shell(在Windows中是sqlite3.exe，以下均是)、数据分析器Analyzer(sqlite3_analyzer.exe)、RBU、数据库文件比较程序(sqldiff.exe)、数据库哈希(dbhash.exe)、Fossil以及SQLite存档程序(sqlar.exe)。下面分别简要介绍这些工具。
5.8.1命令行Shell
启动sqlite3程序，仅仅需要敲入带有SQLite数据库名字的“sqlite3”命令即可。如果文件不存在，则创建一个新的数据库文件。然后sqlite3程序将提示输入SQL，输入SQL语句(以分号“； ”结束)并按Enter键之后，SQL语句就会执行。
例如，创建一个名字为“test”的SQLite数据库，如下： 



sqlite3 test

SQLite version 3.7.14

Enter ".help" for instructions

sqlite>




5.8.2数据分析器
和PostgreSQL非常相似，SQLite中的数据分析器sqlite3_analyzer也同样用于分析数据表和索引中的数据，并将统计结果存放于SQLite的内部系统表中，以便根据分析后的统计数据选择最优的查询执行路径，从而提高整个查询的效率。sqlite3_analyzer程序是一个TCL程序，它使用dbstat虚拟表来收集关于数据库文件的信息，然后巧妙地格式化这些信息。该工具可以携带参数，如图56所示。


图56sqlite3_analyzer参数



图57~图59显示了对本章使用的mydb1数据库的分析信息。



图57sqlite3_analyzer对mydb1数据库分析情况(1)





图58sqlite3_analyzer对mydb1数据库分析情况(2)





图59sqlite3_analyzer对mydb1数据库分析情况(3)


5.8.3可恢复批量更新
可恢复批量更新(Resumable Bulk Update,RBU)实用程序允许以可恢复且不中断正在进行的操作的方式，将一批更改应用于运行在嵌入式硬件上的远程数据库。
RBU扩展是SQLite的一个附加工具，设计用于网络边缘的低功耗设备上的大型SQLite数据库文件。RBU可以用于以下两个不同的任务。
（1） RBU更新操作。RBU更新是数据库文件的批量更新，可包括对一个或多个表的许多插入、更新和删除操作。
（2） RBU Vacuum操作。RBU Vacuum优化和重建整个数据库文件，其结果类似于SQLite的本机Vacuum命令。
5.8.4数据库文件比较程序
数据库文件比较程序（SQLite Database Diff）比较两个SQLite数据库文件，并输出将一个数据库文件转换成另一个数据库文件所需的SQL脚本。下面是它的指令格式： 



sqldiff ［options］ database1.sqlite database2.sqlite




通常的输出是一个将database1.sqlite(源数据库)转换成database2.sqlite(目标数据库)的SQL脚本。
SQLite Database Diff的参数情况如图510所示。


图510SQLite Database Diff的参数


5.8.5数据库哈希
数据库哈希(Database Hash，Dbhash)程序演示了如何计算SQLite数据库内容的散列。Dbhash实用程序是一个命令行程序，用于计算SQLite数据库的模式和内容的SHA1哈希。
Dbhash忽略无关的格式细节，只散列数据库模式和内容。因此，即使数据库文件被修改为： 



VACUUM

PRAGMA page_size

PRAGMA journal_mode

REINDEX

ANALYZE

copied via the backup API

...




上述操作可能会导致原始数据库文件发生巨大变化，并因此导致文件级别的SHA1哈希非常不同。由于数据库文件中表示的内容通过这些操作没有改变，因此由Dbhash计算的散列也没有改变。
Dbhash可以用来比较两个数据库，以确认它们是等价的，即使它们在磁盘上的表示是完全不同的。Dbhash也可以用来验证远程数据库的内容，而不必通过慢速链接传输远程数据库的全部内容。
5.8.6Fossil
Fossil版本控制系统是一个分布式VCS，专门设计用于支持SQLite开发。Fossil使用SQLite作为存储。
5.8.7SQLite存档程序
SQLite存档程序(SQLite Archiver)是一个使用SQLite进行存储的类似ZIP的归档程序。该程序(名为“sqlar”)的操作非常类似于“zip”，只是它构建的压缩存档文件存储在SQLite数据库中，而不是ZIP存档文件中。
5.9实例代码
本节介绍几个SQLite的实例。
5.9.1获取表的Schema信息
主要步骤如下。
(1)  动态创建表。
(2)  根据SQLite3提供的API，获取表字段的信息，如字段数量以及每个字段的类型。
(3)  删除该表。
过程见以下代码及关键性注释： 



#include <sqlite3.h>

#include <string>

using namespace std;

 void doTest() {

sqlite3* conn = NULL;

//1. 打开数据库

int result = sqlite3_open("/sqlite1/mytest.db",&conn);

if (result != SQLITE_OK) {

sqlite3_close(conn);

return;

}

const char* createTableSQL ="CREATE TABLE TESTTABLE (int_col INT, float_col 

REAL, string_col TEXT)";

sqlite3_stmt* stmt = NULL;

int len = strlen(createTableSQL);

/*2. 准备创建数据表，如果创建失败，需要用sqlite3_finalize释放sqlite3_stmt对象，以防止内
存泄漏*/

if (sqlite3_prepare_v2(conn,createTableSQL,len,&stmt,NULL) != SQLITE_OK) {

if (stmt)

sqlite3_finalize(stmt);

sqlite3_close(conn);

return;

}

/*3. 通过sqlite3_step命令执行创建表的语句。对于DDL和DML语句而言，sqlite3_step执行正确
的返回值只有SQLITE_DONE，对于SELECT查询而言，如果有数据返回SQLITE_ROW，当到达结果集末尾
时则返回SQLITE_DONE*/

if (sqlite3_step(stmt) != SQLITE_DONE) {

sqlite3_finalize(stmt);

sqlite3_close(conn);

return;

}

//4. 释放创建表语句对象的资源

sqlite3_finalize(stmt);

printf("Succeed to create test table now.＼n");

//5. 构造查询表数据的sqlite3_stmt对象

const char* selectSQL = "SELECT * FROM TESTTABLE WHERE 1 = 0";

sqlite3_stmt* stmt2 = NULL;

if (sqlite3_prepare_v2(conn,selectSQL,strlen(selectSQL),&stmt2,NULL) 

!= SQLITE_OK) {

if (stmt2)

sqlite3_finalize(stmt2);

sqlite3_close(conn);

return;

}

//6. 根据select语句的对象，获取结果集中的字段数量

int fieldCount = sqlite3_column_count(stmt2);

printf("The column count is %d.＼n",fieldCount);

//7. 遍历结果集中每个字段meta信息，并获取其声明时的类型

for (int i = 0; i < fieldCount; ++i) {






/*由于此时Table中并不存在数据，再有就是SQLite中的数据类型本身是动态的，所以在没有数据
//时无法通过sqlite3_column_type函数获取，此时sqlite3_column_type只会返回SQLITE_NULL，直
//到有数据时才能返回具体的类型，因此这里使用了sqlite3_column_decltype函数来获取表声明时
//给出的声明类型*/


string stype = sqlite3_column_decltype(stmt2,i);

stype = strlwr((char*)stype.c_str());

if (stype.find("int") != string::npos) {

printf("The type of %dth column is INTEGER.＼n",i);

} else if (stype.find("char") != string::npos || stype.find("text") != string::npos) {

printf("The type of %dth column is TEXT.＼n",i);

} else if (stype.find("real") != string::npos || stype.find("floa") != string::npos

|| stype.find("doub") != string::npos ) {

printf("The type of %dth column is DOUBLE.＼n",i);

}

}

sqlite3_finalize(stmt2);

/*8. 为了方便下一次测试运行，我们这里需要删除该函数创建的数据表，否则在下次运行时将无法
创建该表，因为它已经存在*/

const char* dropSQL = "DROP TABLE TESTTABLE";

sqlite3_stmt* stmt3 = NULL;

if (sqlite3_prepare_v2(conn,dropSQL,strlen(dropSQL),&stmt3,NULL) != SQLITE_OK) 

{

if (stmt3)

sqlite3_finalize(stmt3);

sqlite3_close(conn);

return;

}

if (sqlite3_step(stmt3) == SQLITE_DONE) {

printf("The test table has been dropped.＼n");

}

sqlite3_finalize(stmt3);

sqlite3_close(conn);

}

int main() {

doTest();

return 0;

}

//输出结果为:

//Succeed to create test table now.

//The column count is 3.

//The type of 0th column is INTEGER.

//The type of 1th column is DOUBLE.

//The type of 2th column is TEXT.

//The test table has been dropped.




5.9.2数据插入
主要步骤如下。
(1) 创建测试数据表。
(2)  通过INSERT语句插入测试数据。
(3)  删除测试表。
过程见以下代码及关键性注释： 



#include <sqlite3.h>

#include <string>

#include <stdio.h>

 using namespace std;

 void doTest() {

sqlite3* conn = NULL;

//1. 打开数据库

int result = sqlite3_open("/sqlite1/mytest.db ",&conn);

if (result != SQLITE_OK) {

sqlite3_close(conn);

return;

}

const char* createTableSQL ="CREATE TABLE TESTTABLE (int_col INT, 

float_col REAL, string_col TEXT)";

sqlite3_stmt* stmt = NULL;

int len = strlen(createTableSQL);

/*2. 准备创建数据表，如果创建失败，需要用sqlite3_finalize释放sqlite3_stmt对象，以防止内存泄漏*/

if (sqlite3_prepare_v2(conn,createTableSQL,len,&stmt,NULL) != SQLITE_OK) {

if (stmt)

sqlite3_finalize(stmt);

sqlite3_close(conn);

return;

}

/*3. 通过sqlite3_step命令执行创建表的语句。对于DDL和DML语句而言，
sqlite3_step执行正确的返回值只有SQLITE_DONE，对于SELECT查询而言，如果有数据返回SQLITE_ROW，当
到达结果集末尾时则返回SQLITE_DONE*/

if (sqlite3_step(stmt) != SQLITE_DONE) {

sqlite3_finalize(stmt);

sqlite3_close(conn);

return;

}

//4. 释放创建表语句对象的资源

sqlite3_finalize(stmt);

printf("Succeed to create test table now.＼n");

 int insertCount = 10;

//5. 构建插入数据的sqlite3_stmt对象

const char* insertSQL = "INSERT INTO TESTTABLE VALUES(%d,%f,'%s')";

const char* testString = "this is a test.";

char sql［1024］;

sqlite3_stmt* stmt2 = NULL;

for (int i = 0; i < insertCount; ++i) {

sprintf(sql,insertSQL,i,i * 1.0,testString);

if (sqlite3_prepare_v2(conn,sql,strlen(sql),&stmt2,NULL) != SQLITE_OK) {

if (stmt2)






sqlite3_finalize(stmt2);

sqlite3_close(conn);

return;

}

if (sqlite3_step(stmt2) != SQLITE_DONE) {

sqlite3_finalize(stmt2);

sqlite3_close(conn);

return;

}

printf("Insert Succeed.＼n");

}

sqlite3_finalize(stmt2);

/*6. 为了方便下一次测试运行，我们这里需要删除该函数创建的数据表，否则在下次运行时将无法创建该表，因为它已经存在*/

const char* dropSQL = "DROP TABLE TESTTABLE";

sqlite3_stmt* stmt3 = NULL;

if (sqlite3_prepare_v2(conn,dropSQL,strlen(dropSQL),&stmt3,NULL) != SQLITE_OK) 

{

if (stmt3)

sqlite3_finalize(stmt3);

sqlite3_close(conn);

return;

}

if (sqlite3_step(stmt3) == SQLITE_DONE) {

printf("The test table has been dropped.＼n");

}

sqlite3_finalize(stmt3);

sqlite3_close(conn);

}

 int main() {

doTest();

return 0;

}

//输出结果如下:

//Succeed to create test table now.

//Insert Succeed.

//Insert Succeed.

//Insert Succeed.

//Insert Succeed.

//Insert Succeed.

//Insert Succeed.

//Insert Succeed.

//Insert Succeed.

//Insert Succeed.

//Insert Succeed.

//The test table has been dropped.




5.9.3数据查询
数据查询是每个关系型数据库都会提供的最基本功能，下面的代码示例将给出如何通过
SQLite API获取数据。
(1)  创建测试数据表。
(2)  插入一条测试数据到该数据表，以便于后面的查询。
(3)  执行SELECT语句检索数据。
(4)  删除测试表。
见以下示例代码和关键性注释： 



#include <sqlite3.h>

#include <string>

#include <stdio.h>

 using namespace std;

 void doTest() {

sqlite3* conn = NULL;

//1. 打开数据库

int result = sqlite3_open("/sqlite1/mytest.db ",&conn);

if (result != SQLITE_OK) {

sqlite3_close(conn);

return;

}

const char* createTableSQL =

"CREATE TABLE TESTTABLE (int_col INT, float_col REAL, string_col TEXT)";

sqlite3_stmt* stmt = NULL;

int len = strlen(createTableSQL);

/*2. 准备创建数据表，如果创建失败，需要用sqlite3_finalize释放sqlite3_stmt对象，以防止内存泄漏*/

if (sqlite3_prepare_v2(conn,createTableSQL,len,&stmt,NULL) != SQLITE_OK) {

if (stmt)

sqlite3_finalize(stmt);

sqlite3_close(conn);

return;

}

/*3. 通过sqlite3_step命令执行创建表的语句。对于DDL和DML语句而言，sqlite3_step执行正确的返回值只有SQLITE_DONE，对于SELECT查询而言，如果有数据返回SQLITE_ROW，当到达结果集末尾时则返回SQLITE_DONE*/

if (sqlite3_step(stmt) != SQLITE_DONE) {

sqlite3_finalize(stmt);

sqlite3_close(conn);

return;

}

//4. 释放创建表语句对象的资源

sqlite3_finalize(stmt);

printf("Succeed to create test table now.＼n");

 //5. 为后面的查询操作插入测试数据






sqlite3_stmt* stmt2 = NULL;

const char* insertSQL = "INSERT INTO TESTTABLE VALUES(20,21.0,'this is a 

test.')";

if (sqlite3_prepare_v2(conn,insertSQL,strlen(insertSQL),&stmt2,NULL) != 

SQLITE_OK) {

if (stmt2)

sqlite3_finalize(stmt2);

sqlite3_close(conn);

return;

}

if (sqlite3_step(stmt2) != SQLITE_DONE) {

sqlite3_finalize(stmt2);

sqlite3_close(conn);

return;

}

printf("Succeed to insert test data.＼n");

sqlite3_finalize(stmt2);

 //6. 执行SELECT语句查询数据

const char* selectSQL = "SELECT * FROM TESTTABLE";

sqlite3_stmt* stmt3 = NULL;

if (sqlite3_prepare_v2(conn,selectSQL,strlen(selectSQL),&stmt3,NULL) != 

SQLITE_OK) {

if (stmt3)

sqlite3_finalize(stmt3);

sqlite3_close(conn);

return;

}

int fieldCount = sqlite3_column_count(stmt3);

do {

int r = sqlite3_step(stmt3);

if (r == SQLITE_ROW) {

for (int i = 0; i < fieldCount; ++i) {

/*这里需要先判断当前记录当前字段的类型，再根据返回的类型使用不同的API函数获取实际的数据值*/

int vtype = sqlite3_column_type(stmt3,i);

if (vtype == SQLITE_INTEGER) {

int v = sqlite3_column_int(stmt3,i);

printf("The INTEGER value is %d.＼n",v);

} else if (vtype == SQLITE_FLOAT) {

double v = sqlite3_column_double(stmt3,i);

printf("The DOUBLE value is %f.＼n",v);

} else if (vtype == SQLITE_TEXT) {

const char* v = (constch

ar*)sqlite3_column_text(stmt3,i);

printf("The TEXT value is %s.＼n",v);

} else if (vtype == SQLITE_NULL) {

printf("This value is NULL.＼n");

}

}

} else if (r == SQLITE_DONE) {






printf("Select Finished.＼n");

break;

} else {

printf("Failed to SELECT.＼n");

sqlite3_finalize(stmt3);

sqlite3_close(conn);

return;

}

} while (true);

sqlite3_finalize(stmt3);

 /*7. 为了方便下一次测试运行，我们这里需要删除该函数创建的数据表，否则在下次运行时将无法创建该表，因为它已经存在*/

const char* dropSQL = "DROP TABLE TESTTABLE";

sqlite3_stmt* stmt4 = NULL;

if (sqlite3_prepare_v2(conn,dropSQL,strlen(dropSQL),&stmt4,NULL) != SQLITE_OK) 

{

if (stmt4)

sqlite3_finalize(stmt4);

sqlite3_close(conn);

return;

}

if (sqlite3_step(stmt4) == SQLITE_DONE) {

printf("The test table has been dropped.＼n");

}

sqlite3_finalize(stmt4);

sqlite3_close(conn);

}

 int main() {

doTest();

return 0;

}

//输出结果如下:

//Succeed to create test table now.

//Succeed to insert test data.

//The INTEGER value is 20.

//The DOUBLE value is 21.000000.

//The TEXT value is this is a test..

//Select Finished.

//The test table has been dropped.




5.10小结
本章介绍了嵌入式数据库SQLite的功能、特点和SQLite数据库的相关基础应用情况。当前嵌入式系统软件开发的重要环节之一就是对各种数据的管理，而嵌入式数据库是实现该目标的重要手段。SQLite数据库的特点十分适合嵌入式产品开发，而且完全免费开源，值得在日常学习中多实践多研究。
习题
1. 简要叙述SQLite数据库的主要特点。
2. 下载SQLite源码并尝试在指定嵌入式系统中安装SQLite。
3. SQLite与其他数据库最大的不同是它对数据类型的支持，简述SQLite数据库支持的数据类型。
4. SQLite拥有一个模块化的体系结构，请简述它的构成子系统。
5.  设计一个数据库，包含学生信息表、课程信息表和成绩信息表。请写出各个表的数据结构的SQL语句，以CREATE TABLE开头。
6.  向学生信息表和课程信息表各增加5条记录数据。请写出增加数据的SQL语句，以INSERT INTO开头。
7.  删除学生信息表和课程信息表的个别记录数据。请写出删除数据的SQL语句，以DELETE FROM开头。
8.  修改学生信息表和课程信息表的个别记录数据。请写出修改数据的SQL语句，以UPDATE开头。
9.  向成绩信息表增加10条记录数据。写出增加数据的SQL语句，以INSERT INTO开头。
10.  完成以下查询，请写出SQL语句，以SELECT FROM开头： 
(1) 学生信息表中有几位学生； 
(2) 成绩信息表中有几位学生是满分； 
(3) 没有成绩的学生有哪些； 
(4) 至少有一位学生选的课程有哪些； 
(5) 查询选了三门课并且平均成绩在85分以上的学生名单。