SQLite介绍及使用

SQLite，是一款轻型的数据库，是遵守ACID的关联式数据库管理系统，它的设计目标是嵌入式的，而且目前已经在很多嵌入式产品中使用了它，它占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了。它能够支持Windows/Linux/Unix等等主流的操作系统，同时能够跟很多程序语言相结合，比如Tcl、C#、PHP、Java等，还有ODBC接口，同样比起Mysql、PostgreSQL这两款开源世界著名的数据库管理系统来讲，它的处理速度比他们都快。SQLite第一个Alpha版本诞生于2000年5月。 至今已经有12个年头，SQLite也迎来了一个版本 SQLite 3已经发布。

SQLite官方主页
http://www.sqlite.org/

下载地址：
http://www.sqlite.org/download.html
  for 32-bit Windows (.NET Framework 4.0)
  http://system.data.sqlite.org/downloads/1.0.82.0/sqlite-netFx40-setup-bundle-x86-2010-1.0.82.0.exe
 
GUI客户端：
Sqliteman  http://sqliteman.com/
下载：http://sourceforge.net/project/platformdownload.php?group_id=189610


基本SQL命令：

D:\>sqlite3 database.db
sqlite> create table cnblogs(user text, fans integer);
sqlite> insert into cnblogs values('txw1958',36);
sqlite> select * from cnblogs;
sqlite> update cnblogs set fans=100 where user='txw1958';
sqlite> delete from cnblogs where user='txw1958';
sqlite> select * from cnblogs;
sqlite> drop table cnblogs;

 


SQLite系统命令

sqlite> .help
.backup ?DB? FILE      Backup DB (default "main") to FILE(备份数据库到文件)
.bail ON|OFF           Stop after hitting an error.  Default OFF(遇到错误退出，默认关闭)
.databases             List names and files of attached databases (查看目前挂的数据库的名字与文件)
.dump ?TABLE? ...      Dump the database in an SQL text format(以SQL格式输出表结构)
                         If TABLE specified, only dump tables matching
                         LIKE pattern TABLE.
.echo ON|OFF           Turn command echo on or off 打开/关闭命令行回显
.exit                  Exit this program(退出程序)
.explain ?ON|OFF?      Turn output mode suitable for EXPLAIN on or off.
                         With no args, it turns EXPLAIN on.
.header(s) ON|OFF      Turn display of headers on or off 打开|关闭头显示
.help                  Show this messfans(显示帮助信息)
.import FILE TABLE     Import data from FILE into TABLE(把文件中的数据导入到表中,各字段用separator的值为分隔符)
.indices ?TABLE?       Show names of all indices 显示索引名字
                         If TABLE specified, only show indices for tables
                         matching LIKE pattern TABLE.
.load FILE ?ENTRY?     Load an extension library 加载扩展库
.log FILE|off          Turn logging on or off.  FILE can be stderr/stdout 打开|关闭日志记录
.mode MODE ?TABLE?     Set output mode where MODE is one of: (设置输出格式)
                         csv      Comma-separated values (各字段以逗号为分隔符输出)
                         column   Left-aligned columns.  (See .width)(以.width设置的宽度显示各字段)
                         html     HTML <table> code (html表格格式输出)
                         insert   SQL insert statements for TABLE (以insert SQL语句形式输出)
                         line     One value per line (field = value的形式逐行输出)
                         list     Values delimited by .separator string (各字段以separator的值为分隔符输出)
                         tabs     Tab-separated values
                         tcl      TCL list elements
.nullvalue STRING      Print STRING in place of NULL values (输出字符串取代空值)
.output FILENAME       Send output to FILENAME (设置输出到文件)
.output stdout         Send output to the screen (设置输出到屏幕,默认)
.prompt MAIN CONTINUE  Replace the standard prompts (修改提示符)
.quit                  Exit this program  (修改分隔符)
.read FILENAME         Execute SQL in FILENAME (执行文件中的SQL)
.restore ?DB? FILE     Restore content of DB (default "main") from FILE 
.schema ?TABLE?        Show the CREATE statements
                         If TABLE specified, only show tables matching
                         LIKE pattern TABLE.
.separator STRING      Change separator used by output mode and .import (改变输出模式分隔符)
.show                  Show the current values for various settings (显示各项配置信息)
.stats ON|OFF          Turn stats on or off 
.tables ?TABLE?        List names of tables (列出表名)
                         If TABLE specified, only list tables matching
                         LIKE pattern TABLE.
.timeout MS            Try opening locked tables for MS milliseconds (超时时间,单位:毫秒)
.width NUM1 NUM2 ...   Set column widths for "column" mode(设置列宽)
.timer ON|OFF          Turn the CPU timer measurement on or off
sqlite>

 

支持的SQL：

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

 

ALTER TABLE

sql-statement ::= ALTER TABLE [database-name .]table-namealteration
alteration ::= RENAME TO new-table-name
alteration ::= ADD [COLUMN]column-def

SQLite版本的的ALTER TABLE命令允许用户重命名或添加新的字段到已有表中，不能从表中删除字段。

RENAME TO语法用于重命名表名[database-name.]table-name到new-table-name。这一命令不能用于在附加数据库之间移动表，只能在同一个数据库中对表进行重命名。

若需要重命名的表有触发器或索引，在重命名后它们依然属于该表。但若定义了视图，或触发器执行的语句中有提到 表的名字，则它们不会被自动改为使用新的表名。若要进行这一类的修改，则需手工撤销并使用新的表名重建触发器或视图。

ADD [COLUMN]语法用于在已有表中添加新的字段。新字段总是添加到已有字段列表的末尾。Column-def可以是CREATE TABLE中允许出现的任何形式，且须符合如下限制：

  • 字段不能有主键或唯一约束。
  • 字段不能有这些缺省值：CURRENT_TIME, CURRENT_DATE或CURRENT_TIMESTAMP
  • 若定义了NOT NULL约束，则字段必须有一个非空的缺省值。

ALTER TABLE语句的执行时间与表中的数据量无关，它在操作一个有一千万行的表时的运行时间与操作仅有一行的表时是一样的。

在对数据库运行ADD COLUMN之后，该数据库将无法由SQLite 3.1.3及更早版本读取，除非运行VACUUM命令。

ANALYZE

sql-statement ::= ANALYZE
sql-statement ::= ANALYZE database-name
sql-statement ::= ANALYZE [database-name .]table-name

ANALYZE命令令集合关于索引的统计信息并将它们储存在数据库的一个特殊表中，查询优化器可以用该表来制作更好的索引选择。若不给出参数，所有附加数据库中的所有索引被分析。若参数给出数据库名，该数据库中的所有索引被分析。若给出表名 作参数，则只有关联该表的索引被分析。

最初的实现将所有的统计信息储存在一个名叫sqlite_stat1的表中。未来的加强版本中可能会创建名字类似的其它表，只是把“1”改为其它数字。sqlite_stat1表不能够被撤销，但其中的所有内容可以被删除，这是与撤销该表等效的行为。

ATTACH DATABASE

ATTACH DATABASE语句将一个已存在的数据库添加到当前数据库连接。若文件名含标点符号，则应用引号引起来。数据库名’main’和’temp’代表主数据库和用于存放临时表的数据库，它们不能被拆分。拆分数据库使用DETACH DATABASE语句。

你可以读写附加数据库，或改变其结构。这是SQLite 3.0提供的新特性。在SQLite 2.8中，改变附加数据库的结构是不允许的。

在附加数据库中添加一个与已有表同名的表是不允许的。但你可以附加带有与主数据库中的表同名的表的数据库。也可以多次附加同一数据库。

使用database-name.table-name来引用附加数据库中的表。若附加数据库中的表与主数据库的表不重名，则不需加数据库名作为前缀。当数据库被附加时，它的所有不重名的表成为该名字指向的缺省表。之后附加的任意与之同名的表需要加前缀。若“缺省”表被拆分，则最后附加的同名表变为“缺省”表。

若主数据库不是“:memory:”，多附加数据库的事务是原子的。若主数据库是“:memory:”则事务在每个独立文件中依然是原子的。但若主机在改变两个或更多数据库的COMMIT语句进行时崩溃，则可能一部分文件被改变而其他的保持原样。附加数据库的原子性的提交 是SQLite 3.0的新特性。在SQLite 2.8中，所有附加数据库的提交类似于主数据库是“:memory:”时的情况。

对附加数据库的数目有编译时的限制，最多10个附加数据库。

BEGIN TRANSACTION

sql-statement ::= BEGIN [ DEFERRED | IMMEDIATE | EXCLUSIVE ][TRANSACTION [name]]
sql-statement ::= END [TRANSACTION [name]]
sql-statement ::= COMMIT [TRANSACTION [name]]
sql-statement ::= ROLLBACK [TRANSACTION [name]]

从2.0版开始，SQLite支持带有回退和原子性的提交的事务处理。

可选的事务名称会被忽略。SQLite目前不允许嵌套事务。

在事务之外，不能对数据库进行更改。如果当前没有有效的事务，任何修改数据库的命令（基本上除了SELECT以外的所有SQL命令）会自动启动一个事务。命令结束时，自动启动的事务会被提交。

可以使用BEGIN命令手动启动事务。这样启动的事务会在下一条COMMIT或ROLLBACK命令之前一直有效。但若数据库关闭或出现错误且选用ROLLBACK冲突判定算法时，数据库也会ROLLBACK。查看ON CONFLICT子句获取更多关于ROLLBACK冲突判定算法的信息。

在SQLite 3.0.8或更高版本中，事务可以是延迟的，即时的或者独占的。“延迟的”即是说在数据库第一次被访问之前不获得锁。这样就会延迟事务，BEGIN语句本身不做任何事情。直到初次读取或访问数据库时才获取锁。对数据库的初次读取创建一个SHARED锁 ，初次写入创建一个RESERVED锁。由于锁的获取被延迟到第一次需要时，别的线程或进程可以在当前线程执行BEGIN语句之后创建另外的事务 写入数据库。若事务是即时的，则执行BEGIN命令后立即获取RESERVED锁，而不等数据库被使用。在执行BEGIN IMMEDIATE之后，你可以确保其它的线程或进程不能写入数据库或执行BEGIN IMMEDIATE或BEGIN EXCLUSIVE，但其它进程可以读取数据库。独占事务在所有的数据库获取EXCLUSIVE锁，在执行BEGIN EXCLUSIVE之后，你可以确保在当前事务结束前没有任何其它线程或进程 能够读写数据库。

有关SHARED、RESERVED和EXCLUSIVE锁可以参见这里。

SQLite 3.0.8的默认行为是创建延迟事务。SQLite 3.0.0到3.0.7中延迟事务是唯一可用的事务类型。SQLite 2.8或更早版本中，所有的事务都是独占的。

COMMIT命令在所有SQL命令完成之前并不作实际的提交工作。这样若两个或更多个SELECT语句在进程中间而执行COMMIT时，只有全部SELECT语句结束才进行提交。

执行COMMIT可能会返回SQLITE_BUSY错误代码。这就是说有另外一个线程或进程获取了数据库的读取锁，并阻止数据库被改变。当COMMIT获得该错误代码时，事务依然是活动的，并且在COMMIT可以在当前读取的线程读取结束后再次试图读取数据库。

END TRANSACTION

sql-statement ::= BEGIN [ DEFERRED | IMMEDIATE | EXCLUSIVE ][TRANSACTION [name]]
sql-statement ::= END [TRANSACTION [name]]
sql-statement ::= COMMIT [TRANSACTION [name]]
sql-statement ::= ROLLBACK [TRANSACTION [name]]

从2.0版开始，SQLite支持带有回退和原子性的提交的事务处理。

可选的事务名称会被忽略。SQLite目前不允许嵌套事务。

在事务之外，不能对数据库进行更改。如果当前没有有效的事务，任何修改数据库的命令（基本上除了SELECT以外的所有SQL命令）会自动启动一个事务。命令结束时，自动启动的事务会被提交。

可以使用BEGIN命令手动启动事务。这样启动的事务会在下一条COMMIT或ROLLBACK命令之前一直有效。但若数据库关闭或出现错误且选用ROLLBACK冲突判定算法时，数据库也会ROLLBACK。查看ON CONFLICT子句获取更多关于ROLLBACK冲突判定算法的信息。

在SQLite 3.0.8或更高版本中，事务可以是延迟的，即时的或者独占的。“延迟的”即是说在数据库第一次被访问之前不获得锁。这样就会延迟事务，BEGIN语句本身不做任何事情。直到初次读取或访问数据库时才获取锁。对数据库的初次读取创建一个SHARED锁，初次写入创建一个RESERVED锁。由于锁的获取被延迟到第一次需要时，别的线程或进程可以在当前线程执行BEGIN语句之后创建另外的事务写入数据库。若事务是即时的，则执行BEGIN命令后立即获取RESERVED锁，而不等数据库被使用。在执行BEGIN IMMEDIATE之后，你可以确保其它的线程或进程不能写入数据库或执行BEGIN IMMEDIATE或BEGIN EXCLUSIVE，但其它进程可以读取数据库。独占事务在所有的数据库获取EXCLUSIVE锁，在执行BEGIN EXCLUSIVE之后，你可以确保在当前事务结束前没有任何其它线程或进程能够读写数据库。

有关SHARED、RESERVED和EXCLUSIVE锁可以参见这里。

SQLite 3.0.8的默认行为是创建延迟事务。SQLite 3.0.0到3.0.7中延迟事务是唯一可用的事务类型。SQLite 2.8或更早版本中，所有的事务都是独占的。

COMMIT命令在所有SQL命令完成之前并不作实际的提交工作。这样若两个或更多个SELECT语句在进程中间而执行COMMIT时，只有全部SELECT语句结束才进行提交。

执行COMMIT可能会返回SQLITE_BUSY错误代码。这就是说有另外一个线程或进程获取了数据库的读取锁，并阻止数据库被改变。当COMMIT获得该错误代码时，事务依然是活动的，并且在COMMIT可以在当前读取的线程读取结束后再次试图读取数据库。

注释

comment ::= SQL-comment|C-comment
SQL-comment ::= -- single-line
C-comment ::= /* multiple-lines[*/]

注释不是SQL命令，但会出现在SQL查询中。它们被解释器处理为空白部分。它们可以在任何空白可能存在的地方开始 ，即使是在跨越多行的表达式中。

SQL风格的注释仅对当前行有效。

C风格的注释可以跨越多行。若没有结束符号，注释的范围将一直延伸到输入末尾，且不会引起报错。新的SQL语句可以从多行注释结束的地方开始。C风格注释可以嵌入任何空白可以出现的地方，包括表达式内，或其他SQL语句中间， 并且C风格的注释不互相嵌套。SQL风格的注释出现在C风格注释中时将被忽略。

COPY

sql-statement ::= COPY [ OR conflict-algorithm][database-name .]table-name FROM filename
[ USING DELIMITERS delim]

COPY命令在SQLite 2.8及更早的版本中可用。SQLite 3.0删除了这一命令，因为在混合的UTF-8/16环境中对它进行支持是很复杂的。在3.0版本中，命令行解释器包含新的.import命令，用以替代COPY。

COPY命令是用于将大量数据插入表的一个插件。它模仿PostgreSQL中的相同命令而来。事实上，SQLite的COPY 命令就是为了能够读取PostgreSQL的备份工具pg_dump的输出从而能够将PostgreSQL的数据轻松转换到SQLite中而设计的。

table-name是将要导入数据的一个已存在的表的名字。filename是一个字符串或标识符，用于说明作为数据来源的文件。filename可以使用STDIN从标准输入流中获取数据。

输入文件的每一行被转换成一条单独的记录导入表中。字段用制表符分开。若某个字段的数据中出现制表符，则前面被添加反斜线“/”符号。数据中的反斜线则被替换为两条反斜线。可选的USING DELIMITERS子句可给出一个与制表符不同 的分界符。

若字段由“/N”组成，则被赋以空值NULL。

使用这一命令时，利用可选的ON CONFLICT子句可以定义替代的约束冲突判定算法。更多信息，参见 ON CONFLICT。

当输入数据源是STDIN，输入将终止于一行仅包含一个反斜线和一个点的输入：“/.”。

CREATE INDEX

sql-statement ::= CREATE [UNIQUE] INDEX [IF NOT EXISTS][database-name .]index-name
ON table-name ( column-name[, column-name]* )
column-name ::= name[ COLLATE collation-name][ ASC | DESC ]

CREATE INDEX命令由“CREATE INDEX”关键字后跟新索引的名字，关键字“ON”，待索引表的名字，以及括弧内的用于索引键的字段列表构成。每个字段名可以跟随“ASC”或“DESC”关键字说明排序法则，但在当前版本中排序法则被忽略。排序总是按照上升序。

每个字段名后跟COLLATE子句定义文本记录的比较顺序。缺省的比较顺序是由CREATE TABLE语句说明的比较顺序。若不定义比较顺序，则使用内建的二进制比较顺序。

附加到单个表上的索引数目没有限制，索引中的字段数也没有限制。

若UNIQUE关键字出现在CREATE和INDEX之间，则不允许重名的索引记录。试图插入重名记录将会导致错误。

每条CREATE INDEX语句的文本储存于sqlite_master或sqlite_temp_master表中，取决于被索引的表是否临时表。 每次打开数据库时，所有的CREATE INDEX语句从sqlite_master表中读出，产生SQLite的索引样式的内部结构。

若使用可选的IF NOT EXISTS子句，且存在同名索引，则该命令无效。

使用DROP INDEX命令删除索引。

CREATE TABLE

sql-command ::= CREATE [TEMP | TEMPORARY] TABLE [IF NOT EXISTS]table-name (
    column-def[, column-def]*
    [, constraint]*
)
sql-command ::= CREATE [TEMP | TEMPORARY] TABLE [database-name.]table-name AS select-statement
column-def ::= name[type][[CONSTRAINT name]column-constraint]*
type ::= typename|
typename ( number ) |
typename ( number , number )
column-constraint ::= NOT NULL [conflict-clause]|
PRIMARY KEY [sort-order][conflict-clause][AUTOINCREMENT]|
UNIQUE [conflict-clause]|
CHECK ( expr ) |
DEFAULT value|
COLLATE collation-name
constraint ::= PRIMARY KEY ( column-list ) [conflict-clause]|
UNIQUE ( column-list ) [conflict-clause]|
CHECK ( expr )
conflict-clause ::= ON CONFLICT conflict-algorithm

CREATE TABLE语句基本上就是“CREATE TABLE”关键字后跟一个新的表名以及括号内的一堆定义和约束。表名可以是字符串或者标识符。以“sqlite_”开头的表名是留给数据库引擎使用的。

每个字段的定义是字段名后跟字段的数据类型，接着是一个或多个的字段约束。字段的数据类型并不限制字段中可以存放的数据。可以查看SQLite3的数据类型获取更多信息。UNIQUE约束为指定的字段创建索引，该索引须含有唯一键。COLLATE子句说明在比较字段的 文字记录时所使用的排序函数。缺省使用内嵌的BINARY排序函数。

DEFAULT约束说明在使用INSERT插入字段时所使用的缺省值。该值可以是NULL，字符串常量或一个数。从3.1.0版开始，缺省值也可以是以下特殊的与事件无关的关键字CURRENT_TIME、CURRENT_DATE或CURRENT_TIMESTAMP。若缺省值为NULL、字符串常量或数，在执行未指明字段值的INSERT语句的时候它被插入字段。若缺省值是CURRENT_TIME、CURRENT_DATE或CURRENT_TIMESTAMP，则当前UTC日期和/或时间被插入字段。CURRENT_TIME的格式为“HH:MM:SS”，CURRENT_DATE为“YYYY-MM-DD”，而CURRENT_TIMESTAMP是“YYYY-MM-DD HH:MM:SS”。

正常情况下定义PRIMARY KEY只是在相应字段上创建一个UNIQUE索引。然而，若主键定义在单一的INTEGER类型的字段上，则该字段在内部被用作表的B-Tree键。这即是说字段仅能容纳唯一整数值。（在除此之外的其它情况下，SQLite忽略数据类型的说明 ，允许任何类型的数据放入字段中，不管该字段被声明为什么数据类型。）若一个表中不含一个INTEGER PRIMARY KEY字段，则B-Tree键为自动产生的整数。一行的B-Tree键可以通过如下特殊的名字“ROWID”、“OID”或“_ROWID_”进行访问，不论是否有INTEGER PRIMARY KEY存在。INTEGER PRIMARY KEY字段可以使用关键字AUTOINCREMENT声明。AUTOINCREMENT关键字修改了B-Tree键自动产生的方式。B-Tree键的生成的其它信息可以在这里找到。

若“TEMP”或“TEMPORARY”关键字出现在“CREATE”和“TABLE”之间，则所建立的表仅在当前数据库连接可见，并在断开连接时自动被删除。在临时表上建立的任何索引也是临时的。临时表和索引单独存储在与主数据库文件不同的文件中。

若说明了，则表在该数据库中被创建。同时声明和TEMP关键字会出错，除非 是“temp”。若不声明数据库名，也不使用TEMP关键字，则表创建于主数据库中。

在每个约束后跟可选的ON CONFLICT子句可以定义替代的约束冲突判定算法。 缺省为ABORT。同一个表中的不同约束可以使用不同的缺省冲突判定算法。若一条COPY、INSERT或UPDATE命令指定了不同的冲突判定算法，则该算法将替代CREATE TABLE语句中说明的缺省算法。更多信息，参见ON CONFLICT。

3.3.0版支持CHECK约束。在3.3.0之前，CHECK约束被解析但不执行。

表中的字段数或约束数没有任何限制。在2.8版中，单行数据的总数被限制为小于1 megabytes。而在3.0中则消除了限制。

CREATE TABLE AS形式定义表为一个查询的结果集。表的字段名字即是结果中的字段名字。

每条CREATE TABLE语句的文本都储存在sqlite_master表中。每当数据库被打开，所有的CREATE TABLE语句从 sqlite_master表中读出，构成表结构的SQLite内部实现。若原始命令为CREATE TABLE AS则合成出等效的CREATE TABLE语句并储存于sqlite_master表中代替原命令。CREATE TEMPORARY TABLE语句文本储存于sqlite_temp_master表中。

若在命令中使用可选的IF NOT EXISTS子句且存在同名的另一个表，则当前的命令无效。

删除表可以使用DROP TABLE语句。

 

CREATE TRIGGER

sql-statement ::= CREATE [TEMP | TEMPORARY] TRIGGER trigger-name[ BEFORE | AFTER ]
database-event ON [database-name .]table-name
trigger-action
sql-statement ::= CREATE [TEMP | TEMPORARY] TRIGGER trigger-name INSTEAD OF
database-event ON [database-name .]view-name
trigger-action
database-event ::= DELETE |
INSERT |
UPDATE |
UPDATE OF column-list
trigger-action ::= [ FOR EACH ROW | FOR EACH STATEMENT ][ WHEN expression]
BEGIN
    trigger-step ; [trigger-step ; ]*
END
trigger-step ::= update-statement|insert-statement|
delete-statement|select-statement

CREATE TRIGGER语句用于向数据库schema中添加触发器。触发器是一些在特定的数据库事件（database-event）发生时自动进行的数据库操作（trigger-action）。

触发器可由在特殊表上执行的DELETE、INSERT、UPDATE等语句触发，或UPDATE表中特定的字段时触发。

现在SQLite仅支持FOR EACH ROW触发器，不支持FOR EACH STATEMENT触发。因此可以不用明确说明FOR EACH ROW。FOR EACH ROW的意思是由trigger-steps说明的SQL语句可能在（由WHEN子句决定的）数据库插入，更改或删除的每一行触发trigger。

WHEN子句和trigger-steps可以使用“NEW.column-name”和“OLD.column-name”的引用形式访问正在被插入，更改或删除的行的元素，column-name是触发器关联的表中的字段名。OLD和NEW引用只在触发器与之相关的trigger-event处可用，例如：

INSERT NEW可用
UPDATE NEW和OLD均可用
DELETE OLD可用

当使用WHEN子句，trigger-steps只在WHEN子句为真的行执行。不使用WHEN时则在所有行执行。

trigger-time决定了trigger-steps执行的时间，它是相对于关联行的插入、删除和修改而言的。

作为的一部分trigger-step的UPDATE或INSERT可以使用ON CONFLICT子句。但若触发trigger的语句使用了ON CONFLICT子句，则覆盖前述的ON CONFLICT子句所定义的冲突处理方法。

关联表被撤销时触发器被自动删除。

不仅在表上，在视图上一样可以创建触发器，在CREATE TRIGGER语句中使用INSTEAD OF即可。若视图上定义了一个或多个ON INSERT、ON DELETE、ON UPDATE触发器，则相应地对视图执行INSERT、DELETE或UPDATE语句不会出错，而会触发关联的触发器。视图关联的表不会被修改。（除了由触发器进行的修改操作）。

例子：

假设“customers”表存储了客户信息，“orders”表存储了订单信息，下面的触发器确保当用户改变地址时所有的关联订单地址均进行相应改变：

 
CREATE TRIGGER update_customer_address UPDATE OF address ON customers    
    BEGIN 
      UPDATE orders SET address = new.address WHERE customer_name = old.name; 
    END;

定义了该触发器后执行如下语句：

 
UPDATE customers SET address = ’1 Main St.’ WHERE name = ’Jack Jones’;

会使下面的语句自动执行：

 
UPDATE orders SET address = ’1 Main St.’ WHERE customer_name = ’Jack Jones’;

注意，目前在有INTEGER PRIMARY KEY域的表上触发器可能工作不正常。若BEFORE触发器修改了一行的INTEGER PRIMARY KEY域，而该域将由触发该触发器的语句进行修改，则可能根本不会修改该域。可以用PRIMARY KEY字段代替INTEGER PRIMARY KEY字段来解决上述问题。

一个特殊的SQL函数RAISE()可用于触发器程序，使用如下语法：

raise-function ::= RAISE ( ABORT, error-message ) |
RAISE ( FAIL, error-message ) |
RAISE ( ROLLBACK, error-message ) |
RAISE ( IGNORE )

当触发器程序执行中调用了上述前三个之一的形式时，则执行指定的ON CONFLICT进程（ABORT、FAIL或者ROLLBACK）且终止当前查询，返回一个SQLITE_CONSTRAINT错误并说明错误信息。

当调用RAISE(IGNORE)，当前触发器程序的余下部分，触发该触发器的语句和任何之后的触发器程序被忽略并且不恢复对数据库的已有改变。若触发触发器的语句是一个触发器程序本身的一部分，则原触发器程序从下一步起继续执行。

使用DROP TRIGGER删除触发器。

CREATE VIEW

sql-command ::= CREATE [TEMP | TEMPORARY] VIEW [database-name.]view-name AS select-statement

CREATE VIEW命令为一个包装好的SELECT语句命名。当创建了一个视图，它可以用于其他SELECT的FROM字句中代替表名。

若“TEMP”或“TEMPORARY”关键字出现在“CREATE”和“VIEW”之间，则创建的视图仅对打开数据库的进程可见，且在数据库关闭时自动删除。

若指定了则视图在指定的数据库中创建。同时使用和TEMP关键字会导致错误，除非是“temp”。若不声明数据库名，也不使用TEMP关键字，则视图创建于主数据库中。

你不能对视图使用COPY、DELETE、INSERT或UPDATE，视图在SQLite中是只读的。多数情况下你可以在视图上创建TRIGGER来达到相同目的。用DROP VIEW命令来删除视图。

DELETE

sql-statement ::= DELETE FROM [database-name .]table-name[WHERE expr]

DELETE命令用于从表中删除记录。命令包含“DELETE FROM”关键字以及需要删除的记录所在的表名。

若不使用WHERE子句，表中的所有行将全部被删除。否则仅删除符合条件的行。

DETACH DATABASE

sql-command ::= DETACH [DATABASE]database-name

该语句拆分一个之前使用ATTACH DATABASE语句附加的数据库连接。可以使用不同的名字多次附加同一数据库，并且拆分一个连接不会影响其他连接。

若SQLite在事务进行中，该语句不起作用。

DROP INDEX

sql-command ::= DROP INDEX [IF EXISTS][database-name .]index-name

DROP INDEX语句删除由CREATE INDEX语句创建的索引。索引将从数据库结构和磁盘文件中完全删除，唯一的恢复方法是重新输入相应的CREATE INDEX命令。

DROP TABLE语句在缺省模式下不减小数据库文件的大小。空间会留给后来的INSERT语句使用。要释放删除产生的空间，可以使用VACUUM命令。若AUTOVACUUM模式开启，则空间会自动被DROP INDEX释放。

DROP TABLE

sql-command ::= DROP TABLE [IF EXISTS][database-name.]table-name

DROP TABLE语句删除由CREATE TABLE语句创建的表。表将从数据库结构和磁盘文件中完全删除，且不能恢复。该表的所有索引也同时被删除。

DROP TABLE语句在缺省模式下不减小数据库文件的大小。空间会留给后来的INSERT语句使用。要释放删除产生的空间，可以使用VACUUM命令。若AUTOVACUUM模式开启，则空间会自动被DROP TABLE释放。

若使用可选的IF EXISTS子句，在删除的表不存在时就不会报错。

DROP TRIGGER

sql-statement ::= DROP TRIGGER [database-name .]trigger-name

DROP TRIGGER语句删除由CREATE TRIGGER创建的触发器。触发器从数据库的schema中删除。注意当关联的表被撤消时触发器自动被删除。

DROP VIEW

sql-command ::= DROP VIEW view-name

DROP VIEW语句删除由CREATE VIEW创建的视图。视图从数据库的schema中删除，表中的数据不会被更改。

EXPLAIN

sql-statement ::= EXPLAIN sql-statement

EXPLAIN命令修饰语是一个非标准的扩展功能，灵感来自PostgreSQL中的相同命令，但操作完全不同。

若EXPLAIN关键字出现在任何SQLite的SQL命令之前，则SQLite库返回不加EXPLAIN时执行该命令所需要使用的虚拟机指令序列，而不是真正执行该命令。关于虚拟机指令的更多信息参见系统结构描述或关于虚拟机的可用代码。

表达式

expr ::= exprbinary-opexpr|
expr[NOT]like-opexpr[ESCAPE expr]|
unary-opexpr|
( expr ) |
column-name|
table-name . column-name|
database-name . table-name . column-name|
literal-value|
parameter|
function-name ( expr-list| * ) |
expr ISNULL |
expr NOTNULL |
expr[NOT] BETWEEN expr AND expr|
expr[NOT] IN ( value-list ) |
expr[NOT] IN ( select-statement ) |
expr[NOT] IN [database-name .]table-name|
[EXISTS] ( select-statement ) |
CASE [expr]( WHEN expr THEN expr)+[ELSE expr] END |
CAST ( expr AS type )
like-op ::= LIKE | GLOB | REGEXP

这一节与其它的各节有所不同。我们讨论的不是一个单一的SQL命令，而是作为其他大部分命令的一部分的表达式。

SQLite支持如下的二元运算符，按优先级由高至低排列：

||
*     /     %
+     -
<<    >>    &     |
<     <=    >     >=
=     ==    !=    <>   IN
AND    
OR

所支持的一元运算符：

-     +     !     ~

注意等号和“不等”号的两个变种。等号可以是 =或==. “不等”号可以是!=或<>. ||为“连接符”——它将两个字符串连接起来。 %输出左边部分以右边部分为模取模得到的余数。

二元运算符的结果均为数字，除了||连接符，它给出字符串结果。

文本值（literal value）是一个整数或浮点数。可以使用科学计数法。“.”符号总是被当作小数点即使本地设定中用“,”来表示小数点——用“,”表示小数点会造成歧义。字符串常量由字符串加单引号“'”构成。字符串内部的单引号可像Pascal中一样用两个单引号来表示。C风格的加反斜线的表示法由于不是标准SQL而不被支持。BLOB文本是以“x”或“X”开头的含有十六进制文本信息的文本值。例如：

X'53514697465'

文本值同样可以为“NULL”。

表达式中插入文本值占位符的参数可以使用sqlite3_bind API函数在运行时插入。参数可以是如下几种形式：

?NNN   问号跟随数字NNN为第NNN个参数占位。NNN需介于1和999之间。
?   不加数字的问号为下一个参数占位。
:AAAA   冒号带一个标识符名称为一个名为AAAA的参数占位。命名的参数同样可以使用序号占位，被赋予的参数序号为下一个尚未被使用的序号。建议不要混合使用命名代表的参数和序号代表的参数以免引起混淆。
$AAAA   $符号带一个标识符名称也可以为一个名为AAAA的参数占位。在这一情况下标识符名称可以包括一个或更多的“::”以及包含任何文本的“(...)”后缀。该语法是Tcl编程语言中的一个可变形式。

不使用sqlite3_bind赋值的参数被视为NULL。

LIKE运算符进行模式匹配比较。运算符右边为进行匹配的模式而左边为需进行匹配的字符串。模式中的百分号%匹配结果中的零或任意多个字符。下划线_匹配任意单个字符。其他的任意字符匹配本身或等同的大/小写字符。（即不区分大小写的匹配）。（一个bug：SQLite仅对7-bit拉丁字符支持不区分大小写匹配。这是由于LIKE运算符对8-bit ISO8859字符或UTF-8字符是大小写敏感的。例如，表达式'a' LIKE 'A'的值为真而'æ' LIKE 'Æ'为假）。

如果使用可选的ESCAPE子句，则跟随ESCAPE关键字的必须是一个有一个字符的字符串。这一字符（逃逸字符）可用于LIKE模式中，以代替百分号或下划线。逃逸字符后跟百分号，下划线或它本身代表字符串中的百分号，下划线或逃逸字符。插入的LIKE运算符功能通过调用用户函数like(X,Y)来实现。

当使用可选的ESCAPE子句，它对函数给出第三个参数，LIKE的功能可以通过重载SQL函数like()进行改变。

GLOB运算符与LIKE相似，但它使用Unix文件globbing语法作为通配符。还有一点不同是GLOB对大小写敏感。GLOB和LIKE都可以前缀NOT关键字构成相反的意思。插入的GLOB运算符功能通过调用用户函数glob(X,Y)可以通过重载函数改变GLOB的功能。

REGEXP运算符是用户函数regexp()的一个特殊的代表符号。缺省情况下regexp()函数不被定义，所以使用REGEXP运算符会报错。当运行时存在用户定义的“regexp”函数的定义，则调用该函数以实现REGEXP运算符功能。

字段名可以是CREATE TABLE语句定义的任何名字或如下几个特殊标识符之一“ROWID”、“OID”以及“_ROWID_”。这些特殊标识符均代表每个表每一行关联的那个唯一随机整数键“row key”。仅仅在CREATE TABLE语句没有对这些特殊标识符的真实字段予以定义的情况下，它们才代表“row key”。它们与只读字段类似，可以像任何正常字段一样使用，除了在UPDATE或INSERT语句中（即是说你不能添加或更改row key）。“SELECT * ...”不返回row key。

SELECT语句可以在表达式中出现，作为IN运算符的右边运算量，作为一个纯量，或作为EXISTS运算符的运算量。当作纯量或IN的运算量时，SELECT语句的结果仅允许有一个字段，可以使用复合的SELECT（用UNION或 EXCEPT等关键字连接）。作为EXISTS运算符的运算量时，SELECT结果中的字段被忽略，在结果为空时表达式为假，反之为真。若SELECT表达式代表的查询中不含有引用值的部分，则它将在处理其它事务之前被计算，并且结果在必要时会被重复使用。若SELECT表达式含从其它查询中得到的变量，在每一次使用时该表达式均被重新计算。

当SELECT作为IN运算符的右运算量，在左边的运算量是SELECT产生的任意一个值时，表达式返回TRUE。IN运算符前可以加NOT构成相反的意思。

当SELECT与表达式一同出现且不在IN的右边，则SELECT结果的第一行作为表达式中使用的值。SELECT返回的结果在第一行以后的部分被忽略。返回结果为空时SELECT语句的值为NULL。

CAST表达式将的数据类型改为声明的类型。可以是CREATE TABLE语句字段定义部分定义的对该字段有效的任意非空数据类型。

表达式支持简单函数和聚集函数。简单函数直接从输入获得结果，可用于任何表达式中。聚集函数使用结果集中的所有行计算结果，仅用于SELECT语句中。

T下面这些函数是缺省可用的。可以使用C语言写出其它的函数然后使用sqlite3_create_function() API函数添加到数据库引擎中。

abs(X) 返回参数X的绝对值。
coalesce(X,Y,...) 返回第一个非空参数的副本。若所有的参数均为NULL，返回NULL。至少2个参数。
glob(X,Y) 用于实现SQLite的 "X GLOB Y"语法。可使用 sqlite3_create_function() 重载该函数从而改变GLOB运算符的功能。
ifnull(X,Y) 返回第一个非空参数的副本。 若两个参数均为NULL，返回NULL。与上面的 coalesce()类似。
last_insert_rowid() 返回当前数据库连接最后插入行的ROWID。sqlite_last_insert_rowid() API函数同样可用于得到该值。
length(X) 返回X的长度，以字符计。如果SQLite被配置为支持UTF-8，则返回UTF-8字符数而不是字节数。
like(X,Y [,Z]) 用于实现SQL语法"X LIKE Y [ESCAPE Z]".若使用可选的ESCAPE子句，则函数被赋予三个参数，否则只有两个。可使用sqlite3_create_function() 重载该函数从而改变LIKE运算符的功能。
lower(X) 返回X字符串的所有字符小写化版本。这一转换使用C语言库的tolower()函数，对UTF-8字符不能提供好的支持。
max(X,Y,...) 返回最大值。参数可以不仅仅为数字，可以为字符串。大小顺序由常用的排序法则决定。注意，max()在有2个或更多参数时为简单函数，但当仅给出一个参数时它变为聚集函数。
min(X,Y,...) 返回最小值。与max()类似。
nullif(X,Y) 当两参数不同时返回X，否则返回NULL.
quote(X) 返回参数的适于插入其它SQL语句中的值。字符串会被添加单引号，在内部的引号前会加入逃逸符号。 BLOB被编码为十六进制文本。当前的VACUUM使用这一函数实现。在使用触发器实现撤销/重做功能时这一函数也很有用。
random(*) 返回介于-2147483648和 +2147483647之间的随机整数。
round(X)
round(X,Y)		 将X四舍五入，保留小数点后Y位。若忽略Y参数，则默认其为0。
soundex(X) 计算字符串X的soundex编码。参数为NULL时返回字符串“?000”。缺省的SQLite是不支持该函数的，当编译时选项 -DSQLITE_SOUNDEX=1 时该函数才可用。
sqlite_version(*) 返回所运行的SQLite库的版本号字符串。如 "2.8.0"。
substr(X,Y,Z) 返回输入字符串X中以第Y个字符开始，Z个字符长的子串。X最左端的字符序号为1。若Y为负，则从右至左数起。若SQLite配置支持UTF-8，则“字符”代表的是UTF-8字符而非字节。
typeof(X) 返回表达式X的类型。返回值可能为“null”、“integer”、“real”、“text”以及“blob”。SQLite的类型处理参见SQLite3的数据类型。
upper(X) 返回X字符串的所有字符大写化版本。这一转换使用C语言库的toupper()函数，对UTF-8字符不能提供好的支持。

以下是缺省可用的聚集函数列表。可以使用C语言写出其它的聚集函数然后使用sqlite3_create_function() API函数添加到数据库引擎中。

在单参数聚集函数中，参数可以加前缀DISTINCT。这时重复参数会被过滤掉，然后才穿入到函数中。例如，函数“count(distinct X)”返回字段X的不重复非空值的个数，而不是字段X的全部非空值。

avg(X) 返回一组中非空的X的平均值。非数字值作0处理。avg()的结果总是一个浮点数，即使所有的输入变量都是整数。

 
count(X)
count(*)		 返回一组中X是非空值的次数的第一种形式。第二种形式（不带参数）返回该组中的行数。
max(X) 返回一组中的最大值。大小由常用排序法决定。
min(X) 返回一组中最小的非空值。大小由常用排序法决定。仅在所有值为空时返回NULL。
sum(X)
total(X)		 返回一组中所有非空值的数字和。若没有非空行，sum()返回NULL而total()返回0.0。NULL通常情况下并不是对于“没有行”的和的一个有意义的结果，但SQL标准如此要求，且大部分其它SQL数据库引擎这样定义sum()，所以SQLite 也如此定义以保证兼容。我们提供非标准的total()函数作为解决该SQL语言设计问题的一个简易方法。

total()的返回值式中为浮点数。sum()可以为整数，当所有非空输入均为整数时，和是精确的。若sum()的任意一个输入既非整数也非NULL或计算中产生整数类型的溢出时，sum()返回接近真和的浮点数。

INSERT

sql-statement ::= INSERT [OR conflict-algorithm] INTO [database-name .]table-name[(column-list)] VALUES(value-list) |
INSERT [OR conflict-algorithm] INTO [database-name .]table-name[(column-list)]select-statement

INSERT语句有两种基本形式。一种带有“VALUES”关键字，在已有表中插入一个新的行。若不定义字段列表，那么值的数目将与表中的字段数目相同。否则值的数目须与字段列表中的字段数目相同。不在字段列表中的字段被赋予缺省值或NULL（当未定义缺省值）。

INSERT的第二种形式从SELECT语句中获取数据。若未定义字段列表，则从SELECT得到的字段的数目必须与表中的字段数目相同，否则应与定义的字段列表中的字段数目相同。SELECT的每一行结果在表中插入一个新的条目。SELECT可以是简单的或者复合的。如果SELECT语句带有ORDER BY子句，ORDER BY会被忽略。

在使用这一命令时，利用可选的ON CONFLICT子句可以定义替代的约束冲突判定算法。更多信息，参见ON CONFLICT。为了兼容MySQL，可以使用REPLACE代替“INSERT OR REPLACE”。

ON CONFLICT子句

conflict-clause ::= ON CONFLICT conflict-algorithm
conflict-algorithm ::= ROLLBACK | ABORT | FAIL | IGNORE | REPLACE

ON CONFLICT子句不是独立的SQL命令。这是一条可以出现在许多其他SQL命令中的非标准的子句。由于它并不是标准的SQL语言，这里单独介绍它。

ON CONFLICT子句的语法在如上的CREATE TABLE命令中示出。对于INSERT和UPDATE，关键词“ON CONFLICT”由“OR”替代，这样语法显得自然。例如，不用写“INSERT ON CONFLICT IGNORE”而是“INSERT OR IGNORE”。二者表示相同的意思。

ON CONFLICT子句定义了解决约束冲突的算法。有五个选择：ROLLBACK、ABORT、FAIL、IGNORE和REPLACE，缺省方案是ABORT。选项含义如下：

ROLLBACK

当发生约束冲突，立即ROLLBACK，即结束当前事务处理，命令中止并返回SQLITE_CONSTRAINT代码。若当前无活动事务（除了每一条命令创建的默认事务以外），则该算法与ABORT相同。

ABORT

当发生约束冲突，命令收回已经引起的改变并中止返回SQLITE_CONSTRAINT。但由于不执行ROLLBACK，所以前面的命令产生的改变将予以保留。缺省采用这一行为。

FAIL

当发生约束冲突，命令中止返回SQLITE_CONSTRAINT。但遇到冲突之前的所有改变将被保留。例如，若一条UPDATE语句在100行遇到冲突100th，前99行的改变将被保留，而对100行或以后的改变将不会发生。

IGNORE

当发生约束冲突，发生冲突的行将不会被插入或改变。但命令将照常执行。在冲突行之前或之后的行将被正常的插入和改变，且不返回错误信息。

REPLACE

当发生UNIQUE约束冲突，先存在的，导致冲突的行在更改或插入发生冲突的行之前被删除。这样，更改和插入总是被执行。命令照常执行且不返回错误信息。当发生NOT NULL约束冲突，导致冲突的NULL值会被字段缺省值取代。若字段无缺省值，执行ABORT算法。

当冲突应对策略为满足约束而删除行时，它不会调用删除触发器。但在新版中这一特性可能被改变。

INSERT或UPDATE的OR子句定义的算法会覆盖CREATE TABLE所定义的。ABORT算法将在没有定义任何算法时缺省使用。

SQLite支持的编译指令（Pragma）

PRAGMA命令是用于修改SQlite库或查询SQLite库内部数据（non-table）的特殊命令。PRAGMA 命令使用与其它SQLite命令（例如：SELECT、INSERT）相同的接口，但在如下重要方面与其它命令不同：

  • 在未来的SQLite版本中部分Pragma可能被删除或添加，要小心使用。
  • 当使用未知的Pragma语句时不产生报错。未知的Pragma仅仅会被忽略，即是说若是打错了Pragma语句SQLite不会提示用户。
  • 一些Pragma在SQL编译阶段生效而非执行阶段。即是说若使用C语言的sqlite3_compile()、sqlite3_step()、sqlite3_finalize() API（或类似的封装接口中），Pragma可能在调用sqlite3_compile()期间起作用。
  • Pragma命令不与其它SQL引擎兼容。

可用的pragma命令有如下四个基本类型：

PRAGMA命令语法

sql-statement ::= PRAGMA name[= value]|
PRAGMA function(arg)

使用整数值value的pragma也可以使用符号表示，字符串“on”、“true”和“yes”等同于1，“off”、“false”和“no”等同于0。这些字符串大小写不敏感且无须进行引用。无法识别的字符串被当作1且不会报错。value返回时是整数。

用于修改SQLite库的操作的Pragma

  • PRAGMA auto_vacuum;
    PRAGMA auto_vacuum =     0 | 1;

    查询或设置数据库的auto-vacuum标记。

    正常情况下，当提交一个从数据库中删除数据的事务时，数据库文件不改变大小。未使用的文件页被标记并在以后的添加操作中再次使用。这种情况下使用VACUUM命令释放删除得到的空间。

    当开启auto-vacuum，当提交一个从数据库中删除数据的事务时，数据库文件自动收缩，（VACUUM命令在auto-vacuum开启的数据库中不起作用）。数据库会在内部存储一些信息以便支持这一功能，这使得数据库文件比不开启该选项时稍微大一些。

    只有在数据库中未建任何表时才能改变auto-vacuum标记。试图在已有表的情况下修改不会导致报错。

  • PRAGMA cache_size;
    PRAGMA cache_size =     Number-of-pages;

    查询或修改SQLite一次存储在内存中的数据库文件页数。每页使用约1.5K内存，缺省的缓存大小是2000。若需要使用改变大量多行的UPDATE或DELETE命令，并且不介意SQLite使用更多的内存的话，可以增大缓存以提高性能。

    当使用cache_size pragma改变缓存大小时，改变仅对当前对话有效，当数据库关闭重新打开时缓存大小恢复到缺省大小。要想永久改变缓存大小，使用default_cache_size pragma。

  • PRAGMA case_sensitive_like;
    PRAGMA case_sensitive_like =     0 | 1;

    LIKE运算符的缺省行为是忽略latin1字符的大小写。因此在缺省情况下'a' LIKE 'A'的值为真。可以通过打开case_sensitive_like pragma来改变这一缺省行为。当启用case_sensitive_like，'a' LIKE 'A'为假而 'a' LIKE 'a'依然为真。

  • PRAGMA count_changes;
    PRAGMA count_changes =     0 | 1;

    查询或更改count-changes标记。正常情况下INSERT, UPDATE和DELETE语句不返回数据。当开启count-changes，以上语句返回一行含一个整数值的数据——该语句插入、修改或删除的行数。返回的行数不包括由触发器产生的插入、修改或删除等改变的行数。

  • PRAGMA default_cache_size;
    PRAGMA default_cache_size =     Number-of-pages;

    查询或修改SQLite一次存储在内存中的数据库文件页数。每页使用约1.5K内存，它与cache_size pragma类似，只是它永久性地改变缓存大小。利用该pragma，你可以设定一次缓存大小，并且每次重新打开数据库时都继续使用该值。

  • PRAGMA default_synchronous;

    该语句在2.8版本中可用，但在3.0版中被去掉了。这条pragma很危险且不推荐使用，安全起见在该文档中不涉及此pragma的用法。

  • PRAGMA empty_result_callbacks;
    PRAGMA empty_result_callbacks =     0 | 1;

    查询或更改empty-result-callbacks标记。

    empty-result-callbacks标记仅仅影响sqlite3_exec API函数。正常情况下，empty-result-callbacks标记清空，则对返回0行数据的命令不调用sqlite3_exec()的回叫函数，当设置了empty-result-callbacks，则调用回叫 函数一次，置第三个参数为0（NULL）。这使得使用sqlite3_exec() API的程序即使在一条查询不返回数据时依然检索字段名。

  • PRAGMA encoding;
    PRAGMA encoding = "UTF-8";
    PRAGMA encoding = "UTF-16";
    PRAGMA encoding = "UTF-16le";
    PRAGMA encoding = "UTF-16be";

    在第一种形式中，若主数据库已创建，这条pragma返回主数据库使用得文本编码格式，为“UTF-8”、“UTF-16le”（little-endian UTF-16 encoding）或者“UTF-16be”（big-endian UTF-16 encoding）中的一种。 若主数据库未创建，返回值为当前会话创建的主数据库将要使用的文本编码格式。

    第二种及以后几种形式只在主数据库未创建时有效。这时该pragma设置当前会话创建的主数据库将要使用的文本编码格式。“UTF-16”表示“使用本机字节顺序的UTF-16编码”。若这些形式在主数据库创建后使用，将被忽略且不产生任何效果。

    数据库的编码格式设置后不能够被改变。

    ATTACH命令创建的数据库使用与主数据库相同的编码格式。

  • PRAGMA full_column_names;
    PRAGMA full_column_names =     0 | 1;

    查询或更改the full-column-names标记。该标记影响SQLite命名SELECT语句（当字段表达式为表-字段或通配符"*"时）返回的字段名的方式。正常情况下，当SELECT语句将两个或多个表连接时，这类结果字段的返回名为，当SELECT语句查询一个单独的表时，返回字段名为。当设置了full-column-names标记，返回的字段名将统一为 不管是否对表进行了连接。

    若short-column-names和full-column-names标记同时被设置，则使用full-column-names方式。

  • PRAGMA fullfsync
    PRAGMA fullfsync =     0 | 1;

    查询或更改fullfsync标记。该标记决定是否在支持的系统上使用F_FULLFSYNC同步模式。缺省值为off。截至目前（2006-02-10）只有Mac OS X系统支持F_FULLFSYNC。

  • PRAGMA page_size;
    PRAGMA page_size =     bytes;

    查询或设置page-size值。只有在未创建数据库时才能设置page-size。页面大小必须是2的整数倍且大于等于512小于等于8192。上限可以通过在编译时修改宏定义SQLITE_MAX_PAGE_SIZE的值来改变。上限的上限是32768。

  • PRAGMA read_uncommitted;
    PRAGMA read_uncommitted =     0 | 1;

    查询，设置或清除READ UNCOMMITTED isolation（读取未授权的分隔符）。缺省的SQLite分隔符等级是SERIALIZABLE。任何线程或进程可选用READ UNCOMMITTED isolation，但除了共享公共页和schema缓存的连接之间以外的地方也会使用SERIALIZABLE。缓存共享通过sqlite3_enable_shared_cache() API开启，且只在运行同一线程的连接间有效。缺省情况下缓存共享是关闭的。

  • PRAGMA short_column_names;
    PRAGMA short_column_names =     0 | 1;

    查询或更改the short-column-names标记。该标记影响SQLite命名SELECT语句（当字段表达式为表-字段或通配符"*"时）返回的字段名的方式。正常情况下，当SELECT语句将两个或多个表连接时，这类结果字段的返回名为，当SELECT语句查询一个单独的表时，返回字段名为。当设置了full-column-names标记，返回的字段名将统一为不管是否对表进行了连接。

    若short-column-names和full-column-names标记同时被设置，则使用full-column-names方式。

  • PRAGMA synchronous;
    PRAGMA synchronous = FULL;     (2)
    PRAGMA synchronous = NORMAL;     (1)
    PRAGMA synchronous = OFF;     (0)

    查询或更改“synchronous”标记的设定。第一种形式（查询）返回整数值。 当synchronous设置为FULL(2)，SQLite数据库引擎在紧急时刻会暂停以确定数据已经写入磁盘。这使系统崩溃或电源出问题时能确保数据库在重起后不会损坏。FULL synchronous很安全但很慢。当synchronous设置为NORMAL，SQLite数据库引擎在大部分紧急时刻会暂停，但不像FULL模式下那么频繁。NORMAL模式下有很小的几率（但不是不存在）发生电源故障导致数据库损坏的情况。但实际上，在这种情况下很可能你的硬盘已经不能使用，或者发生了其他的不可恢复的硬件错误。设置为synchronous OFF(0)时，SQLite在传递数据给系统以后直接继续而不暂停。若运行SQLite的应用程序崩溃，数据不会损伤，但在系统崩溃或写入数据时意外断电的情况下数据库可能会损坏。另一方面，在synchronous OFF时一些操作可能会快50倍甚至更多。

    在SQLite2中，缺省值为NORMAL，而在3中修改为FULL。

  • PRAGMA temp_store;
    PRAGMA temp_store = DEFAULT;     (0)
    PRAGMA temp_store = FILE;     (1)
    PRAGMA temp_store = MEMORY;     (2)

    查询或更改“temp_store”参数的设置。当temp_store设置为DEFAULT(0)，使用编译时的C预处理宏TEMP_STORE来定义储存临时表和临时索引的位置。当设置为MEMORY(2)临时表和索引存放于内存中。当设置为FILE(1)则存放于文件中。temp_store_directory pragma可用于指定存放该文件的目录。当改变temp_store设置，所有已存在的临时表、索引、触发器及视图将被立即删除。

    库中的编译时C预处理标志TEMP_STORE可以覆盖该pragma设置。下面的表给出TEMP_STORE预处理宏和temp_store pragma交互作用的总结：

    TEMP_STOREPRAGMA
    temp_store    		临时表和索引
    使用的存储方式
    0 any 文件
    1 0 文件
    1 1 文件
    1 2 内存
    2 0 内存
    2 1 文件
    2 2 内存
    3 any 内存

  • PRAGMA temp_store_directory;
    PRAGMA temp_store_directory = 'directory-name';

    查询或更改“temp_store_directory”设置——存储临时表和索引的文件所在的目录。仅在当前连接有效，在建立新连接时重置为缺省值。

    当改变了temp_store_directory设置，所有已有的临时表、索引、触发器、视图会被直接删除。建议在数据库一打开时就设置好temp_store_directory。

    directory-name需用单引号引起来。要想恢复缺省目录，把directory-name设为空字符串。例如 PRAGMA temp_store_directory = ''。若directory-name未找到或不可写会引发错误。

    临时文件的缺省目录与主机的系统有关，使用Unix/Linux/OSX系统的主机，缺省目录是如下序列之中第一个可写的/var/tmp、/usr/tmp、/tmp、current-directory。对于Windows NT，缺省目录由Windows决定，一般为C:/Documents and Settings/user-name/Local Settings/Temp/。SQLite创建的临时文件在使用完毕时就被unlink，所以操作系统可以在SQLite进程进行中自动删除临时文件。于是，正常情况下不能通过ls或dir命令看到临时文件。

用于查询数据库的schema的Pragma

  • PRAGMA database_list;

    对每个打开的数据库，使用该数据库的信息调用一次回叫函数。使用包括附加的数据库名和索引名在内的参数。第一行用于主数据库，第二行用于存放临时表的临时数据库。

  • PRAGMA foreign_key_list(table-name);

    对于参数表中每个涉及到字段的外键，使用该外键的信息调用一次回叫函数。每个外键中的每个字段都将调用一次回叫函数。

  • PRAGMA index_info(index-name);

    对该索引涉及到的每个字段，使用字段信息（字段名、字段号）调用一次回叫函数。

  • PRAGMA index_list(table-name);

    对表中的每个索引，使用索引信息调用回叫函数。参数包括索引名和一个指示索引是否唯一的标志。

  • PRAGMA table_info(table-name);

    对于表中的每个字段，使用字段信息（字段名、数据类型、可否为空、缺省值）调用回叫函数。

用于查询/更改版本信息的Pragma

  • PRAGMA [database.]schema_version;
    PRAGMA [database.]schema_version =     integer ;
    PRAGMA [database.]user_version;
    PRAGMA [database.]user_version =     integer ;

    这两条pragma分别用于设置schema-version和user-version的值。schema-version和user-version均为32位有符号整数，存放于数据库头中。

    schema-version通常只由SQLite内部操作。每当数据库的Schema改变时（创建或撤消表或索引），SQLite将这个值增大。Schema版本在每一次Query被执行时被SQLite所使用，以确定编译SQL Query时内部Cache的Schema与编译后的Query实际执行时数据库的Schema相匹配。使用“PRAGMA schema_version”更改schema-version会破坏这一机制，有导致程序崩溃或数据库损坏的潜在危险。请小心使用！

    user-version不在SQLite内部使用，任何程序可以用它来做任何事。

用于库Debug的Pragma

  • PRAGMA integrity_check;

    该命令对整个数据库进行完整性检查，查找次序颠倒的记录，丢失的页，残缺的记录以及损坏的索引。若发现任何问题则返回一形容问题所在的字符串，若一切正常返回“ok”。

  • PRAGMA parser_trace = ON; (1)
    PRAGMA parser_trace = OFF;     (0)

    打开或关闭SQLite库中的SQL语法分析追踪，用于Debug。只有当SQLite不使用NDEBUG宏进行编译时该pragma才可用。

  • PRAGMA vdbe_trace = ON; (1)
    PRAGMA vdbe_trace = OFF;     (0)

    打开或关闭SQLite库中的虚拟数据库引擎追踪，用于Debug。更多信息，查看 VDBE文档。

  • PRAGMA vdbe_listing = ON; (1)
    PRAGMA vdbe_listing = OFF;     (0)

    打开或关闭虚拟机程序列表，当开启列表功能，整个程序的内容在执行前被打印出来，就像在每条语句之前自动执行EXPLAIN。语句在打印列表之后正常执行。用于Debug。更多信息，查看VDBE文档。

REINDEX

sql-statement ::= REINDEX collation name
sql-statement ::= REINDEX [database-name .]table/index-name

REINDEX命令用于删除并从草稿重建索引。当比较顺序改变时该命令显得很有效。

在第一种形式中，所有附加数据库中使用该比较顺序的索引均被重建。在第二种形式中，[database-name.]table/index-name标识出一个表，所有关联该表的索引被重建。若标识出索引，则仅仅该索引被删除并重建。

若不指定database-name而指定表/索引名以及比较顺序，只有关联该比较顺序的索引被重建。在重建索引时总是指定database-name可以消除这一歧义。

REPLACE

sql-statement ::= REPLACE INTO [database-name .]table-name[( column-list )] VALUES ( value-list ) |
REPLACE INTO [database-name .]table-name[( column-list )]select-statement

REPLACE命令用于替代INSERT的“INSERT OR REPLACE”变体，以更好的兼容MySQL。查看INSERT命令文档获取更多信息。

SELECT

sql-statement ::= SELECT [ALL | DISTINCT]result[FROM table-list]
[WHERE expr]
[GROUP BY expr-list]
[HAVING expr]
[compound-opselect]*
[ORDER BY sort-expr-list]
[LIMIT integer[( OFFSET | , )integer]]
result ::= result-column[, result-column]*
result-column ::= * |table-name . * |expr[[AS]string]
table-list ::= table[join-optablejoin-args]*
table ::= table-name[AS alias]|
( select ) [AS alias]
join-op ::= , |[NATURAL][LEFT | RIGHT | FULL][OUTER | INNER | CROSS] JOIN
join-args ::= [ON expr][USING ( id-list )]
sort-expr-list ::= expr[sort-order][, expr[sort-order]]*
sort-order ::= [ COLLATE collation-name][ ASC | DESC ]
compound_op ::= UNION | UNION ALL | INTERSECT | EXCEPT

SELECT语句用于查询数据库。一条SELECT命令的返回结果是零或多行每行有固定字段数的数据。字段的数目由在SELECT和FROM之间的表达式列表定义。任意的表达式都可以被用作结果。若表达式是 *则表示所有表的所有字段。若表达式是表的名字后接.*则结果为该表中的所有字段。

DISTINCT关键字的使用会使返回的结果是原结果的一个不含相同行的子集。NULL值被认为是相同的。缺省行为是返回所有的行，为清楚起见可以使用关键字ALL。

查询对FROM之后定义的一个或多个表进行。若多个表用逗号连接，则查询针对它们的交叉连接。所有的SQL-92连接语法均可以用于定义连接。圆括号中的副查询可能被FROM子句中的任意表名替代。当结果中仅有一行包含表达式列表中的结果的行时，整个的FROM子句会被忽略。

WHERE子句可以限定查询操作的行数目。

GROUP BY子句将一行或多行结果合成单行输出。当结果有聚集函数时这将尤其有用。GROUP BY子句的表达式不须是出现在结果中的表达式。HAVING子句与WHERE相似，只是HAVING用于过滤分组创建的行。HAVING子句可能包含值，甚至是不出现在结果中的聚集函数。

ORDER BY子句对所得结果根据表达式排序。表达式无须是简单SELECT的结果，但在复合SELECT中每个表达式必须精确对应一个结果字段。每个表达式可能跟随一个可选的COLLATE关键字以及用于排序文本的比较函数名称和/或关键字ASC或DESC，用于说明排序规则。

LIMIT子句限定行数的最大值。负的LIMIT表示无上限。后跟可选的OFFSET说明跳过结果集中的前多少行。在一个复合查询中，LIMIT子句只允许出现在最终SELECT语句中。限定对于所有的查询均适用，而不仅仅是添加了LIMIT子句的那一行。注意OFFSET关键字用于LIMIT子句中，则限制值是第一个数字，而偏移量（offset）是第二个数字。若用逗号替代OFFSET关键字，则偏移量是第一个数字而限制值是第二个数字。这是为了加强对遗留的SQL数据库的兼容而有意造成的矛盾。

复合的SELECT由两个或更多简单SELECT经由UNION、UNION ALL、INTERSECT、EXCEPT中的一个运算符连接而成。在一个复合SELECT中，各个SELECT需指定相同个数的结果字段。仅允许一个ORDER BY子句出现在SELECT的末尾。UNION和UNION ALL运算符从左至右将所有SELECT的结果合成一个大的表。二者的区别在于UNION的所有结果行是不相同的而UNION ALL允许重复行。INTERSECT运算符取左右两个SELECT结果的交。EXCEPT从左边SELECT的结果中除掉右边SELECT的结果。三个或更多SELECT复合时，它们从左至右结合。

UPDATE

sql-statement ::= UPDATE [ OR conflict-algorithm][database-name .]table-name
SET assignment[, assignment]*
[WHERE expr]
assignment ::= column-name = expr

UPDATE语句用于改变表中所选行的字段值。每个UPDATE的赋值的等号左边为字段名而右边为任意表达式。表达式可以使用其它字段的值。所有的表达式将在赋值之前求出结果。可以使用WHERE子句限定需要改变的行。

在使用这一命令时，利用可选的ON CONFLICT子句可以定义替代的约束冲突判定算法。更多信息，参见ON CONFLICT。

VACUUM

sql-statement ::= VACUUM [index-or-table-name]

VACUUM命令是SQLite的一个扩展功能，模仿PostgreSQL中的相同命令而来。若调用VACUUM带一个表名或索引名，则将整理该表或索引。在SQLite 1.0中，VACUUM命令调用gdbm_reorganize()整理后端数据库文件。

SQLITE 2.0.0中去掉了GDBM后端，VACUUM无效。在2.8.1版中，VACUUM被重新实现。现在索引名或表名被忽略。

当数据库中的一个对象（表、索引或触发器）被撤销，会留下空白的空间。它使数据库比需要的大小更大，但能加快插入速度。实时的插入和删除会使得数据库文件结构混乱，减慢对数据库内容访问的速度。VACUUM命令复制主数据库文件到临时数据库并从临时数据库重新载入主数据库，以整理数据库文件。这将除去空白页，使表数据彼此相邻排列，并整理数据库文件结构。不能对附加数据库文件进行以上操作。

若当前有活动事务，该命令无法起作用。对于In-Memory数据库，该命令无效。

SQLite 3.1中，可以通过使用auto-vacuum模式取代VACUUM命令，使用auto_vacuum pragma开启该模式。

posted @ 2012-11-16 15:57  方倍工作室  阅读(17563)  评论(0)  编辑  收藏  举报