SQLite语法

最新推荐文章于 2024-04-05 14:29:52 发布

zouyuefu

最新推荐文章于 2024-04-05 14:29:52 发布

阅读量637

点赞数

文章标签： sqlite 数据库 conflict insert table sql

本文链接：https://blog.csdn.net/zouyuefu/article/details/6419824

版权

ALTER TABLE

sql-statement ::=	ALTER TABLE [ database-name . ] table-name alteration
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 语句。

你可以读写附加数据库，或改变其结构。这是 SQLite3.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 目前不允许嵌套事务。

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

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

注释

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 语句。

源文档 < http://apps.hi.baidu.com/share/detail/14947163#content >

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 语句不会出错，而会触发关联的触发器。视图关联的表不会被修改。 ( 除了由触发器进行的修改操作 ) 。

Example:

假设 “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 时执行该命令所需要使用的虚拟机指令序列，而不是真正执行该命令。关于虚拟机指令的更多信息参见系统结构描述或关于虚拟机的可用代码。

源文档 < http://apps.hi.baidu.com/share/detail/14947173#content >

表达式

expr ::=

expr binary-op expr |

expr [ NOT ] like-op expr [ ESCAPE expr ] |

unary-op expr |

( 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” 开头的含有十六进制文本信息的文本值 For example:

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() 返回接近真和的浮点数。

源文档 < http://apps.hi.baidu.com/share/detail/14947188#content >

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 命令 (e.g. SELECT, INSERT) 相同的接口，但在如下重要方面与其它命令不同 :

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

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

用于察看当前数据库的模式。
用于修改 SQLite 库的操作或查询当前的操作模式。
用于查询或修改两个数据库的版本号， schema- version 和 user-version.
用于调试库和校验数据库文件。

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 倍甚至更多。
在 SQLite 2 中，缺省值为 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_STORE PRAGMA
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 文档。

源文档 < http://apps.hi.baidu.com/share/detail/14947203#content >

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-op select ]* [ 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-op table join-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 数据库，该命令无效。

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

源文档 < http://apps.hi.baidu.com/share/detail/14947218#content >

SQLite 的查询优化

SQLite 是个典型的嵌入式 DBMS ，它有很多优点，它是轻量级的，在编译之后很小，其中一个原因就是在查询优化方面比较简单，它只是运用索引机制来进行优化的：

一、影响查询性能的因素：

1 ．　对表中行的检索数目，越小越好

2 ．　排序与否。

3 ．　是否要对一个索引。

4 ．　查询语句的形式

二、几个查询优化的转换

1 ．　对于单个表的单个列而言，如果都有形如 T.C=expr 这样的子句，并且都是用 OR 操作符连接起来，形如：

x = expr1 OR expr2 = x OR x = expr3 此时 由于对于 OR ，在 SQLite 中不能利用索引来优化，所以可以将它转换成带有 IN 操作符的子句 ： x IN(expr1,expr2,expr3) 这样就可以用索引进行优化，效果很明显，但是如果在都没有索引的情况下 OR 语句执行效率会稍优于 IN 语句的效率。

2 ． 如果一个子句的操作符是 BETWEEN ，在 SQLite 中同样不能用索引进行优化 ，所以也要进行相应的等价转换：

如： a BETWEEN b AND c 可以转换成： (a BETWEEN b AND c) AND (a>=b) AND (a<=c) 。在上面这个子句中， (a>=b) AND (a<=c) 将被设为 dynamic 且是 (a BETWEEN b AND c) 的子句，那么如果 BETWEEN 语句已经编码，那么子句就忽略不计，如果存在可利用的 index 使得子句已经满足条件，那么父句则被忽略。

3 ．　如果一个单元的操作符是 LIKE ，那么将做下面的转换： x LIKE ‘abc%’ ，转换成： x>=‘abc’ AND x<‘abd’ 。

因为在 SQLite 中的 LIKE 是不能用索引进行优化的，所以如果存在索引的话，则转换后和不转换相差很远，因为对 LIKE 不起作用，但如果不存在索引，那么 LIKE 在效率方面也还是比不上转换后的效率的。

三、几种查询语句的处理（复合查询）

1 ．查询语句为： <SelectA> <operator> <selectB> ORDER BY <orderbylist> ORDER BY

执行方法：

is one of UNION ALL, UNION, EXCEPT, or INTERSECT.

这个语句的执行过程是先将 select A 和 select B 执行并且排序，再对两个结果扫描处理，对上面四种操作是不同的，将执行过程分成七个子过程：

outA: 将 selectA 的结果的一行放到最终结果集中

outB: 将 selectA 的结果的一行放到最终结果集中 ( 只有 UNION 操作和 UNION ALL 操作，其它操作都不放入最终结果集中 )

AltB: 当 selectA 的当前记录小于 selectB 的当前记录

AeqB: 当 selectA 的当前记录等于 selectB 的当前记录

AgtB: 当 selectA 的当前记录大于 selectB 的当前记录

EofA: 当 selectA 的结果遍历完

EofB: 当 selectB 的结果遍历完

下面就是四种操作的执行过程：

执行顺序	UNION ALL	UNION	EXCEPT	INTERSECT
AltB:	outA, nextA	outA, nextA	outA,nextA	nextA
AeqB:	outA, nextA	nextA	nextA	outA, nextA
AgtB:	outB, nextB	outB, nextB	nextB	nextB
EofA:	outB, nextB	outB, nextB	halt	halt
EofB:	outA, nextA	outA, nextA	outA,nextA	halt

2 ．　如果可能的话，可以把一个用到 GROUP BY 查询的语句转换成 DISTINCT 语句来查询，因为 GROUP BY 有时候可能会用到 index ，而对于 DISTINCT 都不会用到索引的。

四、子查询扁平化

例子： SELECT a FROM (SELECT x+y AS a FROM t1 WHERE z<100) WHERE a>5

对这个 SQL 语句的执行一般默认的方法就是先执行内查询，把结果放到一个临时表中，再对这个表进行外部查询，这就要对数据处理两次，另外这个临时表没有索引，所以对外部查询就不能进行优化了 .

如果对上面的 SQL 进行处理后可以得到如下 SQL 语句：

SELECT x+y AS a FROM t1 WHERE z<100 AND a>5 ，这个结果显然和上面的一样，但此时只需要对　　数据进行查询一次就够了，另外如果在表 t1 上有索引的话就避免了遍历整个表。

运用 flatten 方法优化 SQL 的条件 ：

1. 子查询和外查询没有都用集函数

2. 子查询没有用集函数或者外查询不是个表的连接

3. 子查询不是一个左外连接的右操作数

4. 子查询没有用 DISTINCT 或者外查询不是个表的连接

5. 子查询没有用 DISTINCT 或者外查询没有用集函数

6. 子查询没有用集函数或者外查询没有用关键字 DISTINCT

7. 子查询有一个 FROM 语句

8. 子查询没有用 LIMIT 或者外查询不是表的连接

9. 子查询没有用 LIMIT 或者外查询没有用集函数

10. 子查询没有用集函数或者外查询没用 LIMIT

11. 子查询和外查询不是同时是 ORDER BY 子句

12. 子查询和外查询没有都用 LIMIT

13. 子查询没有用 OFFSET

14. 外查询不是一个复合查询的一部分或者子查询没有同时用关键字 ORDER BY 和 LIMIT

15. 外查询没有用集函数子查询不包含 ORDER BY

16. 复合子查询的扁平化：子查询不是一个复合查询，或者他是一个 UNION ALL 复合查询，但他是都由若干个非集函数的查询构成，他的父查询不是一个复合查询的子查询，也没有用集函数或者是 DISTINCT 查询，并且在 FROM 语句中没有其它的表或者子查询，父查询和子查询可能会包含 WHERE 语句，这些都会受到上面 11 、 12 、 13 条件的限制。

例：

SELECT a+1 FROM (

SELECT x FROM tab

UNION ALL

SELECT y FROM tab

UNION ALL

SELECT abs(z*2) FROM tab2

) WHERE a!=5 ORDER BY 1

转换为：

SELECT x+1 FROM tab WHERE x+1!=5

UNION ALL

SELECT y+1 FROM tab WHERE y+1!=5

UNION ALL

SELECT abs(z*2)+1 FROM tab2 WHERE abs(z*2)+1!=5

ORDER BY 1

17. 如果子查询是一个复合查询，那么父查询的所有的 ORDER BY 语句必须是对子查询的列的简单引用

18. 子查询没有用 LIMIT 或者外查询不具有 WHERE 语句子查询扁平化是由专门一个函数实现的，函数为：

static int flattenSubquery(

Parse *pParse, /* Parsing context */

Select *p, /* The parent or outer SELECT statement */

int iFrom, /* Index in p->pSrc->a[] of the inner subquery */

int isAgg, /* True if outer SELECT uses aggregate functions */

int subqueryIsAgg /* True if the subquery uses aggregate functions */

)

它是在 Select.c 文件中实现的。显然对于一个比较复杂的查询，如果满足上面的条件时对这个查询语句进行扁平化处理后就可以实现对查询的优化。如果正好存在索引的话效果会更好！

五、连接查询

在返回查询结果之前，相关表的每行必须都已经连接起来，在 SQLite 中，这是用嵌套循环实现的，在早期版本中，最左边的是最外层循环，最右边的是最内层循环，连接两个或者更多的表时，如果有索引则放到内层循环中，也就是放到 FROM 最后面，因为对于前面选中的每行，找后面与之对应的行时，如果有索引则会很快，如果没有则要遍历整个表，这样效率就很低，但在新版本中，这个优化已经实现。

优化的方法如下：

对要查询的每个表，统计这个表上的索引信息，首先将代价赋值为 SQLITE_BIG_DBL （一个系统已经定义的常量）：

1) 如果没有索引，则找有没有在这个表上对 rowid 的查询条件：

1 ．如果有 Rowid=EXPR ，如果有的话则返回对这个表代价估计，代价计为零，查询得到的记录数为 1 ，并完成对这个表的代价估计，

2 ．如果没有 Rowid=EXPR 但有 rowid IN (…) ，而 IN 是一个列表，那么记录返回记录数为 IN 列表中元素的个数，估计代价为 NlogN,

3 ．如果 IN 不是一个列表而是一个子查询结果，那么由于具体这个子查询不能确定，所以只能估计一个值，返回记录数为 100 ，代价为 200 。

4 ．如果对 rowid 是范围的查询，那么就估计所有符合条件的记录是总记录的三分之一，总记录估计为 1000000 ，并且估计代价也为记录数。

5 ．如果这个查询还要求排序，则再另外加上排序的代价 NlogN

6 ．如果此时得到的代价小于总代价，那么就更新总代价，否则不更新。

2) 如果 WHERE 子句中存在 OR 操作符，那么要把这些 OR 连接的所有子句分开再进行分析。

1 ．如果有子句是由 AND 连接符构成，那么再把由 AND 连接的子句再分别分析。

2 ．如果连接的子句的形式是 X<op><expr> ，那么就再分析这个子句。

3 ．接下来就是把整个对 OR 操作的总代价计算出来。

4 ．如果这个查询要求排序，则再在上面总代价上再乘上排序代价 NlogN

5 ．如果此时得到的代价小于总代价，那么就更新总代价，否则不更新。

3) 如果有索引，则统计每个表的索引信息，对于每个索引：

1 ．　先找到这个索引对应的列号，再找到对应的能用到（操作符必须为 = 或者是 IN （…））这个索引的 WHERE 子句，如果没有找到，则退出对每个索引的循环，如果找到，则判断这个子句的操作符是什么，如果是 = ，那么没有附加的代价，如果是 IN （ sub-select ），那么估计它附加代价 inMultiplier 为 25 ，如果是 IN （ list ），那么附加代价就是 N （ N 为 list 的列数）。

2 ．　再计算总的代价和总的查询结果记录数和代价。

3 ． nRow = pProbe->aiRowEst[i] * inMultiplier;/* 计算行数 */

4 ． cost = nRow * estLog(inMultiplier);/* 统计代价 */

5 ．　如果找不到操作符为 = 或者是 IN （…）的子句，而是范围的查询，那么同样只好估计查询结果记录数为 nRow/3 ，估计代价为 cost /3 。

6 ．　同样，如果此查询要求排序的话，再在上面的总代价上加上 NlogN

7 ．　如果此时得到的代价小于总代价，那么就更新总代价，否则不更新。

4) 通过上面的优化过程，可以得到对一个表查询的总代价（就是上面各个代价的总和），再对第二个表进行同样的操作，这样如此直到把 FROM 子句中所有的表都计算出各自的代价，最后取最小的，这将作为嵌套循环的最内层，依次可以得到整个嵌套循环的嵌套顺序，此时正是最优的，达到了优化的目的。

5) 所以循环的嵌套顺序不一定是与 FROM 子句中的顺序一致，因为在执行过程中会用索引优化来重新排列顺序。

六、索引

在 SQLite 中，有以下几种索引：

1) 单列索引

2) 多列索引

3) 唯一性索引

4) 对于声明为：

INTEGER PRIMARY KEY 的主键来说，这列会按默认方式排序，所以虽然在数据字典中没有对它生成索引，但它的功能就像个索引。所以如果在这个主键上在单独建立索引的话，这样既浪费空间也没有任何好处。

运用索引的注意事项：

1) 对于一个很小的表来说没必要建立索引

2) 在一个表上如果经常做的是插入更新操作，那么就要节制使用索引

3) 也不要在一个表上建立太多的索引，如果建立太多的话那么在查询的时候 SQLite 可能不会选择最好的来执行查询，一个解决办法就是建立聚蔟索引

索引的运用时机：

1) 操作符： = 、 > 、 < 、 IN 等

2) 操作符 BETWEEN 、 LIKE 、 OR 不能用索引，

如 BETWEEN ： SELECT * FROM mytable WHERE myfield BETWEEN 10 and 20;

这时就应该将其转换成：

SELECT * FROM mytable WHERE myfield >= 10 AND myfield <= 20;

此时如果在 myfield 上有索引的话就可以用了，大大提高速度

再如 LIKE ： SELECT * FROM mytable WHERE myfield LIKE ‘sql%’;

此时应该将它转换成：

SELECT * FROM mytable WHERE myfield >= ‘sql’ AND myfield < ‘sqm’;

此时如果在 myfield 上有索引的话就可以用了，大大提高速度

再如 OR ： SELECT * FROM mytable WHERE myfield = ‘abc’ OR myfield = ‘xyz’;

此时应该将它转换成：

SELECT * FROM mytable WHERE myfield IN (‘abc’, ‘xyz’);

此时如果在 myfield 上有索引的话就可以用了，大大提高速度

3) 有些时候索引都是不能用的，这时就应该遍历全表（程序演示）

SELECT * FROM mytable WHERE myfield % 2 = 1;

SELECT * FROM mytable WHERE substr(myfield, 0, 1) = ‘w’;

SELECT * FROM mytable WHERE length(myfield) < 5;

源文档 < http://apps.hi.baidu.com/share/detail/14947301#content >

Mysql 到 Sqlite 的 SQL 语句转换程序

一个用 Perl 写的 Mysql 到 Sqlite 的 SQL 语句转换程序，可以将 Mysql 生成的数据库 SQL 语句转换为 SQLite 支持的格式。

sqlite.pl

#!/usr/bin/perl -w

use strict;

use DBI;

use Getopt::Std;

use vars qw(%opts);

getopts(‘u:s:d:h’,/%opts);

my ($user,$host,$db,$help) = parse_args(/%opts);

USAGE() and exit unless $user and $host and $db and not $help;

my $table = join(‘ ‘,@ARGV);

open(DUMP, “mysqldump -u $user -p -h $host $db $table |”);

my $sql = do {local $/; <DUMP>};

$sql =~ s/^#.*$//mg; # chokes on comments

$sql =~ s/auto_increment//g; # on ‘auto_increment’

$sql =~ s/TYPE=/w+;/;/g; # and on ‘TYPE=____’

$sql =~ s///’/”/g; # and on escaped ‘

my @table = $sql =~ /CREATE/s+TABLE/s+(/w+)/g;

print “creating tables: “,join(‘ ‘,@table),”/n”;

my $dbh = DBI->connect(

(“DBI:SQLite:dbname=$db.dbm”),

{RaiseError=>1}

);

$dbh->do($sql);

sub parse_args {

my %opt = %{+shift};

return @opt{qw(u s d h)};

}

sub USAGE {print “USAGE: $0 -u user -s server(host) -d database/n”}

=pod

=head1 NAME

mysql2sqlite.pl – MySQL database migration script

=head1 DESCRIPTION

This is a simple Perl DBI script for use with the MySQL

and SQLite database drivers. The script opens a pipe to

the mysqldump program to retrieve CREATE and INSERT

statements for the specified tables. This data is then

munged to conform with SQLite, and then fed to a dbm

file named the same as the database.

=head1 SYNOPSIS

./mysql2sqlite.pl -u user -s host -d dbase table1 table2 table3

This will create a dbm named ‘dbase.dbm’ with three tables

(table1, table2, table3) provided that they all exist in

the MySQL database. If tables are not supplied, then ALL

TABLES in the database will be migrated. If a table already

exists in the dbm file, then the script will stop execution

before that table’s data is migrated (simplicity vs.

robustness, i chose simplicity).

=head1 LEGAL STUFF

Mi casa su casa, but if you get hurt or someone gets hurt

from this casa, then it’s your casa, not mine.

=cut

源文档 < http://apps.hi.baidu.com/share/detail/14947238#content >

zouyuefu

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

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

SQLite语法

ALTER TABLE

ANALYZE

ATTACH DATABASE

BEGIN TRANSACTION

END TRANSACTION

注释

COPY

CREATE INDEX

CREATE TABLE

CREATE TRIGGER

CREATE VIEW

DELETE

DETACH DATABASE

DROP INDEX

DROP TABLE

DROP TRIGGER

DROP VIEW

EXPLAIN

表达式

INSERT

ON CONFLICT 子句

SQLite 支持的编译指令(pragma)

PRAGMA 命令语法

用于修改SQLite 库的操作的Pragma

用于查询数据库的schema 的Pragma

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

用于库debug 的Pragma

REINDEX

REPLACE

SELECT

UPDATE

VACUUM

一、影响查询性能的因素：

二、几个查询优化的转换

三、 几种查询语句的处理（复合查询）

四、子查询扁平化

五、连接查询

三、几种查询语句的处理（复合查询）