SQLite 笔记之注意和高级篇
相信看了我博客的上篇的博友或路过者,都会觉得,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;