Sqlite2009-06-29 22:22:25阅读5评论0字号：大中小
| 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语句带有ORDERBY子句，ORDER BY会被忽略。
在使用这一命令时，利用可选的ON CONFLICT子句可以定义替代的约束冲突判定算法。更多信息，参见 ON CONFLICT 。为了兼容MySQL，可以使用REPLACE代替"INSERT OR REPLACE".
| conflict-clause ::= | ON CONFLICT conflict-algorithm |
| conflict-algorithm ::= | ROLLBACK | ABORT | FAIL | IGNORE | REPLACE |
ON CONFLICT子句不是独立的SQL命令。这是一条可以出现在许多其他SQL命令中的非标准的子句。由于它并不是标准的SQL语言，这里单独介绍它。
ONCONFLICT子句的语法在如上的CREATE TABLE命令中示出。对于INSERT和UPDATE,关键词"ONCONFLICT"由"OR"替代，这样语法显得自然。例如，不用写"INSERT ON CONFLICT IGNORE"而是"INSERT ORIGNORE".二者表示相同的意思。
ON CONFLICT子句定义了解决约束冲突的算法。有五个选择：ROLLBACK, ABORT, FAIL, IGNORE, 和REPLACE.缺省方案是ABORT.选项含义如下：
当发生约束冲突，立即ROLLBACK，即结束当前事务处理，命令中止并返回SQLITE_CONSTRAINT代码。若当前无活动事务(除了每一条命令创建的默认事务以外)，则该算法与ABORT相同。
当发生约束冲突，命令收回已经引起的改变并中止返回SQLITE_CONSTRAINT。但由于不执行ROLLBACK，所以前面的命令产生的改变将予以保留。缺省采用这一行为。
当发生约束冲突，命令中止返回SQLITE_CONSTRAINT。但遇到冲突之前的所有改变将被保留。例如，若一条UPDATE语句在100行遇到冲突100th，前99行的改变将被保留，而对100行或以后的改变将不会发生。
当发生约束冲突，发生冲突的行将不会被插入或改变。但命令将照常执行。在冲突行之前或之后的行将被正常的插入和改变，且不返回错误信息。
当发生UNIQUE约束冲突，先存在的，导致冲突的行在更改或插入发生冲突的行之前被删除。这样，更改和插入总是被执行。命令照常执行且不返回错误信息。当发生NOT NULL约束冲突，导致冲突的NULL值会被字段缺省值取代。若字段无缺省值，执行ABORT算法。
当冲突应对策略为满足约束而删除行时，它不会调用删除触发器。但在新版中这一特性可能被改变。
INSERT或UPDATE的OR子句定义的算法会覆盖CREATE TABLE所定义的。ABORT算法将在没有定义任何算法时缺省使用。
PRAGMA命令是用于修改SQlite库或查询SQLite库内部数据(non-table)的特殊命令。PRAGMA 命令使用与其它SQLite命令(e.g. SELECT, INSERT)相同的接口，但在如下重要方面与其它命令不同:
可用的pragma命令有如下四个基本类型：
| sql-statement ::= | PRAGMA name [= value] | PRAGMA function(arg) |
使用整数值value的pragma也可以使用符号表示，字符串"on", "true",和 "yes" 等同于1，"off", "false",和 "no"等同于0. 这些字符串大小写不敏感且无须进行引用。无法识别的字符串被当作1且不会报错。value返回时是整数。
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_sizepragma类似，只是它永久性地改变缓存大小。 利用该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语句将两个或多个表连接时， 这类结果字段的返回名为
若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语句将两个或多个表连接时， 这类结果字段的返回名为
若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命令看到临时文件。
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 [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内部使用，任何程序可以用它来做任何事。
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文档。
| sql-statement ::= | REINDEX collation name |
| sql-statement ::= | REINDEX [database-name .] table/index-name |
REINDEX命令用于删除并从草稿重建索引。当比较顺序改变时该命令显得很有效。
在第一种形式中，所有附加数据库中使用该比较顺序的索引均被重建。在第二种形式中， [database-name.]table/index-name标识出一个表，所有关联该表的索引被重建。若标识出索引，则仅仅该索引被删除并重建。
若不指定database-name而指定表/索引名以及比较顺序，只有关联该比较顺序的索引被重建。在重建索引时总是指定database-name可以消除这一歧义。
| 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命令文档获取更多信息。
| 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子句可能包含值，甚至是不出现在结果中的聚集函数。
ORDERBY子句对所得结果根据表达式排序。表达式无须是简单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需指定相同个数的结果字段。仅允许一个ORDERBY子句出现在SELECT的末尾。UNION和UNIONALL运算符从左至右将所有SELECT的结果合成一个大的表。二者的区别在于UNION的所有结果行是不相同的而 UNIONALL允许重复行。INTERSECT运算符取左右两个SELECT结果的交。EXCEPT从左边SELECT的结果中除掉右边SELECT的结果。三个或更多SELECT复合时，它们从左至右结合。
联系客服
