相信看了我博客的上篇的博友或路过者，都会觉得，sqlite小巧，易学，简单。下面是我再把我的笔记上传博客中，以帮助一些人和我自己，今后翻阅方便。因为我知道有这回事。
前几天看到有人回复帖子问了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*, int, sqlite3_stmt**, const char**);
int sqlite3_prepare16(sqlite3*, const void*, int, sqlite3_stmt**, 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, 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()之后所有的通配符都可以被重新赋值。
在 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;
