前几天看到有人回复帖子问了sqlite数据库用在哪些方面，其实这样的问题，都可以维基或是百度下的，什么都会出来，不过sqlite是小型的数据库，主要用在嵌入式设备中如手机等。下面是对这sqlite3的一些注意和高级部分的笔记：

　　1、类型自动分类：具体的值比如sql语句部分的带双引号或单引号的文字被定为Text;如果文字没带引号和小数点或指数则被定义为INTEGER;如果文字没带引号但有小数点或指数则被定义为REAL;如果值是空则被定义为空值;BLOB数据使用符号X'ABCD'标识。

　　2、sqlite3.0中，值被定义为什么类型只和值自身有关，和列、变量都没关系,我们称其其为“弱类型”。所有其他的数据库引擎都收静态类型系统的限制，其中的所有值的类型是由其所属列的属性决定的，而和值无关。不过为了最大限度的增加sqlite和其他数据库的兼容性，sqlite提出了一个“类型亲和性”，意思就是说它支持列的“类型亲和性”。列的“类型亲和性”是为该列所存储的数据建议一个类型。我们还要注意是建议不是强迫。在理论上讲，任何列上可以存储任何类型的数据。只是针对某些列，如果给建议类型的话，数据库将按所建议的类型存储，而这个被优先使用的数数据类型则被称为“亲和类型”。

　　（1）、sqlite3.0中，数据库的每一列都被定义为以下亲和类型的一种：文本、数字（实数）、整数、无。（这点的理解是这样的，就是说我给一个整数类型的值填充到文本字段上，此时整数类型的数据被当作文本存储。）

　　　　规则：

　　　　（1）、 如果数据类型包括字符串"INT"那么它被定义成具有整数亲和性.

　　 （2）、 如果列中的数据类型包括以下任何的字符串 "CHAR","CLOB",or "TEXT" 那么这个列则具有文本亲和性.要注意VARCHAR 类型包括字符串"CHAR"因此也具有文本类型亲和性.

（3）、如果一个列的数据类型包括字符串"BLOB"或者如果数据类型被具体化了,那么这个列具有无类型亲和性.

（4）、否则就具有数字(实数)类型亲和性.

　　3、 运算符

　　所有的数学运算符(所有的运算符而不是连锁作用标记符"||")运算对象首先具有数字亲和性,如果一个或是两个都不能被转换为数字那么操作的结果将是空值。 对于连接作用操作符，所有操作符将首先具有文本亲和性。如果其中任何一个操作符不能被转换为文本（因为它是空值或是 BLOB）连接作用操作符将是空值。

　　4、 分类，排序，混合挑选

　　当用子句 ORDER挑选值时，空值首先被挑选出来,然后是整数和实数按顺序被挑选出来,然后是文本值按 memcmp()顺序被挑选出来,最后是BLOB值按memcmp()顺序被挑选出来.在挑选之前,没有存储类型的值都被转换了。

　　5、用户定义的校对顺序

　　BINARY -使用memcmp()比较字符串数据,不考虑文本编码。（默认比较顺序）

　　REVERSE -用倒序比较二进制文本。

　　NOCASE - 和二进制一样,但在比较之前,26 位的大写字母要被转换成小写字母。【这个改变可以通过编写sqlite的自定义函数自己实现一些数据库函数就可以，而且这些自定义函数，在sqlite api中都有。写好了后，进行编译，生成.exe文件就可以。】

　　对于二进制比较符(=,<,>,<= and >=),如果操作数是一列的话,那么该列的默认比较类型决定于所使用的比较顺序. 如果两个操作数都是列的话,那么左边的操作数的默认比较类型决定了所要使用的比较顺序.如果两个操作数都不是一列,将使用二进制来比较。

　　6、sqlite 不支持的 sql92 特性

　　这个列表的顺序关系到何时一个特性可能被加入到 sqlite。接近列表顶部的特性更可能在不远的将来加入。接近列表底部的特性尚且没有直接的计划。　

　　7、sqlite不支持成群的索引(简单来说，就是索引使数据库中的数据存入时索引的顺序是什么样，数据怎样放置)，这意味着，如果你的索引的是整数顺序，记录就会把数据库中的数据按整数顺序安排，先1后2后3。（设置sqlite页面缓存： pragma page_size=大小;）

　　　　看个例子：create table wibble2 as select * from wibble;

　　　　　　　　　　Delete from wibble;

　　　　　　　　　　Insert into wibble select * from wibble2 order by key;(这行的写法，在其他数据库试过，可行(Oracle/sqlServer2012)，sqlite3中也支持)

　　　　　　　　　　Drop table wibble2;

　　　8、sqlite中如何使用触发器执行取消和重做逻辑：思路是创建一个特殊表格， 保存数据库撤销和重做变化所需的信息。因为数据库中的每个表格都需要参与撤销和重做，每个delete、insert、update都生成了触发器，而且它们可以在撤销日记表格中生成登记项，这个登记项将撤销操作。撤销表格中的登记项由一般的sql语句组成，为了完成撤销，sql语句可以重新运行。

CREATE TRIGGER 触发器名称 [BEFORE|AFTER] 数据库事件 ON [数据库名称].表名 [FOR EACH ROW][ WHEN expression] BEGIN 触发器执行动作 END

数据库事件： DELETE INSERT UPDATE UPDATE OF 字段列表

　　　　下面写个脚本：

　　　　　　Create table ex1(a INTEGER,b TEXT,c REAL);

　　　　　　Create trigger trigger_ex1_it

　　　　　　after insert on ex1

　　　　　　Begin

　　　　　　　　Insert into undolog values(NULL,'delete from ex1 where rowid='||new.rowid);

　　　　　　End;

　　　　　 Create trigger trigger_ex1_ut

　　　　　　after update on ex1

　　　　　　Begin

　　　　　　　　Insert into undolog values(NULL,'update ex1 set a='||quote(old.a)||',b='||quote(old.b)||',c='||quote(old.c)||' where rowid='||old.rowid);

　　　　　　End;

　　　　　　Create trigger trigger_ex1_dt

　　　　　　Before delete on ex1

　　　　　　Begin

　　　　　　　　Insert into undolog values(NULL,'insert into ex1(rowid,a,b,c) values('||old.rowid||','||quote(old.a)||','||quote(old.b)||','||quote(old.c)　　　　　　　　　　　　 ||')');

　　　　　　End;

　　在ex1 表格中执行每个INSERT 后,the _ex1_it 触发器生成 DELETE语句的文本，它将撤销 INSERT操作。The _ex1_ut触发器生成 UPDATE语句，这语句将取消一个 UPDATE所产生的作用。trigger_ex1_dt触发器生成一个语句，这语句将取消一个 DELETE所具有的作用。

要注意 quote()函数在这些触发器中的使用。quote()函数在 sqlite中是标准的。它把它的参数转换成一种适合被包含在 sql 语句中的形式。数字值不改变。单个的 quotes 被加在字符串之前或之后，任何内在的单个quotes 都被逃逸。quote()函数被加入 sqlite是 为了执行撤销和重做操作。

　　9、sqlite3 C/C++ 开发接口（API 函数）

　　sqlite3是建立在sqlite2.8之上开发的，是为了支持UTF-16编码、自定义的文本排序方法、对blobs字段建立索引。3.0版和2.x版的api非常相似，只不过前缀改成sqlite3。

sqlite使用了普通的 void* 类型来指向 UTF-16编码的字符串. 客户端使用过程中可以把 void*映射成适合他们的系统的任何数据类型。

　　sqlite 3.0 一共有83个API函数,此外还有一些数据结构和预定义(#defines). (完整的API介绍请参看另一份文档.) 不过你们可以放心,这些接口使用起来不会像它的数量所暗示的那么复杂. 最简单的程序仍然使用三个函数就可以完成: sqlite3_open(),sqlite3_exec(),和 sqlite3_close()。

　　要是想更好的控制数据库引擎的执行,可以使用提供的 sqlite3_prepare()函数把 sql语句编译成字节码,然后在使用 sqlite3_step()函数来执行编译后的字节码。以sqlite3_column_开头的一组API函数用来获取查询结果集中的信息. 许多接口函数都是成对出现的,同时有UTF-8和UTF-16两个版本。 并且提供了一组函数用来执行用户自定义的 sql函数和文本排序函数。【附加体外话：其实有关数据库的操作，java的api或是c/c++库中都供普通操作数据库的api或是方法。因为我对这几门语言熟悉点，所以就顺便说下，这语言之间的一些共性。】

　　9.1 如何打开关闭数据库

　　 typedef struct sqlite3 sqlite3;

　　int sqlite3_open(const char*,sqlite3**);

　　int sqlite3_open16(const void*,sqlite3**);

　　 int sqlite3_close(sqlite3*);

　　 const char *sqlite3_errmsg(sqlite3*);

　　const void *sqlite3_errmsg16(sqlite3*);

　　 int sqlite3_errcode(sqlite3*);

　　说明：sqlite3_open() 函数返回一个整数来标识状态,而不是像第二版中一样返回一个指向 sqlite3结构体的指针。 sqlite3_open() 和 sqlite3_open16() 的不同之处在于 sqlite3_open16() 使用UTF-16编码(使用本地主机字节顺序)传递数据库文件名。 如果要连接新数据库,sqlite3_open16() 将内部文本转换为 UTF-16编码,反之 sqlite3_open() 将文本转换为 UTF-8编码。

　　打开或者创建数据库的命令会被缓存,直到这个数据库真正被调用的时候才会被执行。而且允许使用PRAGMA 声明来设置如本地文本编码或默认内存页面大小等选项和参数。

sqlite3_errcode() 通常用来获取最近调用的 API接口返回的错误代码。sqlite3_errmsg() 则用来得到这些错误代码所对应的文字说明。这些错误信息将以 UTF-8 的编码返回,并且在下一次调用任何sqlite API 函数的时候被清除。sqlite3_errmsg16() 和 sqlite3_errmsg() 大体上相同,除了返回的错误信息将以 UTF-16 本机字节顺序编码。

　　sqlite3 的错误代码相比sqlite2 没有任何的改变,它们分别是:

　　#define sqlITE_OK 0 /* Successful result */

　　#define sqlITE_ERROR 1 /* sql error or missing database */

　　#define sqlITE_INTERNAL 2 /* An internal logic error in sqlite */

　　#define sqlITE_PERM 3 /* Access permission denied */

　　#define sqlITE_ABORT 4 /* Callback routine requested an abort */

　　#define sqlITE_BUSY 5 /* The database file is locked */

　　#define sqlITE_LOCKED 6 /* A table in the database is locked */

　　#define sqlITE_NOMEM 7 /* A malloc() Failed */

　　#define sqlITE_READONLY 8 /* Attempt to write a readonly database */

　　#define sqlITE_INTERRUPT 9 /* Operation terminated by sqlite_interrupt()

　　#define sqlITE_IOERR 10 /* Some kind of disk I/O error occurred */

　　#define sqlITE_CORRUPT 11 /* The database disk image is malformed */

　　#define sqlITE_NOTFOUND 12 /* (Internal Only) Table or record not found

　　#define sqlITE_FULL 13 /* Insertion Failed because database is full

　　#define sqlITE_CANTOPEN 14 /* Unable to open the database file */

　　#define sqlITE_PROTOCOL 15 /* Database lock protocol error */

　　#define sqlITE_EMPTY 16 /* (Internal Only) Database table is empty */

　　#define sqlITE_SCHEMA 17 /* The database schema changed */

　　#define sqlITE_TOOBIG 18 /* Too much data for one row of a table */

　　#define sqlITE_CONSTRAINT 19 /* Abort due to contraint violation */

　　#define sqlITE_MISMATCH 20 /* Data type mismatch */

　　#define sqlITE_MISUSE 21 /* Library used incorrectly */

　　#define sqlITE_NOLFS 22 /* Uses OS features not supported on host */

　　#define sqlITE_AUTH 23 /* Authorization denied */

　　#define sqlITE_ROW 100 /* sqlite_step() has another row ready */

　　#define sqlITE_DONE 101 /* sqlite_step() has finished executing */

　　9.2 执行 sql 语句

　　 typedef int (*sqlite_callback)(void*,int,char**,char**);

　　int sqlite3_exec(sqlite3*,const char * sql,sqlite_callback,void*,char**);

　　sqlite3_exec 函数依然像它在 sqlite2 中一样承担着很多的工作。 该函数的第二个参数中可以编译和执行零个sql 语句. 查询的结果返回给回调函数。 更多地信息可以查看 API 参考。

　　typedef struct sqlite3_stmt sqlite3_stmt;

　　int sqlite3_prepare(sqlite3*,const char*,sqlite3_stmt**,const char**);

　　int sqlite3_prepare16(sqlite3*,const void*,const void**);

　　int sqlite3_finalize(sqlite3_stmt*);

　　int sqlite3_reset(sqlite3_stmt*);

　　sqlite3_prepare 接口把一条 sql 语句编译成字节码留给后面的执行函数sqlite3—_exec()。 使用该接口访问数据库是当前比较好的的一种方法。

sqlite3_prepare() 处理的sql语句应该是UTF-8编码的。而sqlite3_prepare16() 则要求是UTF-16编码的。输入的参数中只有第一个 sql 语句会被编译。 第四个参数则用来指向输入参数中下一个需要编译的 sql 语句存放的 sqlite statement 对象的指针,任何时候如果调用 sqlite3_finalize() 将销毁一个准备好的 sql 声明。 在数据库关闭之前，所有准备好的声明都必须被释放销毁。 sqlite3_reset() 函数用来重置一个 sql 声明的状态，使得它可以被再次执行。

　　sql 声明可以包含一些型如"?" 或 "?nnn" 或 ":aaa"的标记， 其中"nnn" 是一个整数，"aaa" 是一个字符串。这些标记代表一些不确定的字符值（或者说是通配符），可以在后面用 sqlite3_bind 接口来填充这些值。每一个通配符都被分配了一个编号（由它在 sql 声明中的位置决定，从 1 开始），此外也可以用 "nnn" 来表示 "?nnn" 这种情况。允许相同的通配符在同一个 sql 声明中出现多次,在这种情况下所有相同的通配符都会被替换成相同的值。没有被绑定的通配符将自动取 NULL 值。

　　int sqlite3_bind_blob(sqlite3_stmt*,int n,void(*)(void*));

　　int sqlite3_bind_double(sqlite3_stmt*,double);

　　int sqlite3_bind_int(sqlite3_stmt*,int);

　　int sqlite3_bind_int64(sqlite3_stmt*,long long int);

　　int sqlite3_bind_null(sqlite3_stmt*,int);

　　int sqlite3_bind_text(sqlite3_stmt*,void(*)(void*));

　　int sqlite3_bind_text16(sqlite3_stmt*,void(*)(void*));

　　int sqlite3_bind_value(sqlite3_stmt*,const sqlite3_value*);

　　以上是 sqlite3_bind 所包含的全部接口，它们是用来给 sql 声明中的通配符赋值的。没有绑定的通配符则被认为是空值。绑定上的值不会被 sqlite3_reset()函数重置。但是在调用了 sqlite3_reset()之后所有的通配符都可以被重新赋值。

在 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);

　　nt 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_***() 函数来提取,所有的这些函数都把列的编号作为第二个参数。列编号从左到右以零起始。

　　　　sqlite3_column_type()函数返回第 N 列的值的数据类型。 具体的返回值如下:

#define sqlITE_INTEGER 1

#define sqlITE_FLOAT 2

#define sqlITE_TEXT 3

#define sqlITE_BLOB 4

#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() 返回浮点数。

　　10、sqlite 常见问题解答

　　（1）、如何建立自动增长的字段：可以在字段上声明为INTEGER PRIMARY KEY AUTOINCREATE。

　　（2）、sqlite 支持何种数据类型：参见 http://www.sqlite.org/datatype3.html。

　　（3）、sqlite允许一个列中插入任何类型的字段，（前面我们说过sqlite是种弱类型，不想其他的语言，如java）,但任何类型的字段会被自动转换该列所需的类型，转换不了的则按任何类型对应的类型插入。（这就是我在前面说的类型亲和性）有一种除外，就是标志为INTEGER PRIMARY KEY 的列只能存储 64位整数， 当向这种列中插数据除整数以外的数据时，将会产生错误。

　　（4）、sqlite 不允许在同一个表不同的两行上使用 0 和0.0 作主键，因为这两者sqlite认为是相等，既然相等就是不唯一。

　　（5）、多个进程可同时打开同一个数据库。多个进程可以同时进行 SELECT (读)操作，但在任一时刻，只能有一个进程对数据库进行更改(写)。看到这么一句话这样说的，我觉得很有借鉴性，所以摘入到此，虽不是原话，大致意思是这样的， 对于网络文件，文件锁的实现有好多 Bug，是靠不住的。如果他们说的是对的， 那么在两台或多台 Windows机器间共享数据库可能会引起不期望的问题（关系数据库两外算）。sqlite允许多个进程同时打开一个数据库， 同时读一个数据库。当有任何进程想要写时，它必须在更新过程中锁住数据库文件。 但那通常只是几毫秒的时间。其它进程只需等待写进程干完活结束。 当sqlite 试图访问一个被其它进程锁住的文件时，缺省的行为是返回 sqlITE_BUSY。(sqlite3是线程安全的)

　　（6）、在 sqlite 数据库中如何列出所有的表和索引：.tables和.schema和数据字典sqlite_master; 字典结构: type TEXT,name TEXT,tbl_name TEXT,rootpage INTEGER,sql TEXT 。

　　（7）、sqlite 数据库有已知的大小限制吗？ 见 limits.html 。

　　（8）、在 sqlite 中，如何在一个表上添加或删除一列？sqlite没有提供完整的alter支持，可以使用它来在表的末尾增加一列，可更改表的名称。 如果需要对表结构做更复杂的改变，则必须重新建表。 重建时可以先将已存在的数据放到一个临时表中，删除原表， 创建新表，然后将数据从临时表中复制回来。 如，假设有一个 t1 表，其中有 "a","b","c" 三列， 如果要删除列 c ，以下过程描述如何做:

　　BEGIN TRANSACTION;

　　CREATE TEMPORARY TABLE t1_backup(a,b); 临时表

　　INSERT INTO t1_backup SELECT a,b FROM t1; 临时表拷贝表t1数据

　　DROP TABLE t1;

　　CREATE TABLE t1(a,b);

　　INSERT INTO t1 SELECT a,b FROM t1_backup;

　　DROP TABLE t1_backup;

　　END TRANSACTION;

　　COMMIT;