SQLite的SQL语法.docx

1、SQLite的SQL语法SQLite的SQL语法SQLite的SQL语法 1ALTER TABLE 2ANALYZE 3ATTACH DATABASE 3BEGIN TRANSACTION 4注释 5BEGIN TRANSACTION 6COPY 7CREATE INDEX 8CREATE TABLE 8CREATE TRIGGER 11CREATE VIEW 14DELETE 14DETACH DATABASE 15DROP INDEX 15DROP TABLE 15DROP TRIGGER 16DROP VIEW 16END TRANSACTION 16EXPLAIN 17表达式 18IN

2、SERT 24ON CONFLICT子句 24SQLite支持的编译指令(pragma) 25PRAGMA命令语法 26用于修改SQLite库的操作的Pragma 26用于查询数据库的schema的Pragma 31用于查询/更改版本信息的Pragma 32用于库debug的Pragma 32REINDEX 33REPLACE 33SELECT 34UPDATE 36VACUUM 36ALTER TABLEsql-statement:=ALTER TABLE database-name . table-name alterationalteration:=RENAME TO new-table

3、-namealteration:=ADD COLUMN column-defSQLite版本的的ALTER TABLE命令允许用户重命名或添加新的字段到已有表中，不能从表中删除字段。 RENAME TO语法用于重命名表名 database-name.table-name到new-table-name。这一命令不能用于在附加数据库之间移动表，只能在同一个数据库中对表进行重命名。若需要重命名的表有触发器或索引，在重命名后它们依然属于该表。但若定义了视图，或触发器执行的语句中有提到 表的名字，则它们不会被自动改为使用新的表名。若要进行这一类的修改，则需手工撤销并使用新的表名重建触发器或视图。ADD

4、COLUMN语法用于在已有表中添加新的字段。新字段总是添加到已有字段列表的末尾。 Column-def可以是CREATE TABLE中允许出现的任何形式，且须符合如下限制： 字段不能有主键或唯一约束。 字段不能有这些缺省值：CURRENT_TIME, CURRENT_DATE 或CURRENT_TIMESTAMP 若定义了NOT NULL约束，则字段必须有一个非空的缺省值。 ALTER TABLE语句的执行时间与表中的数据量无关，它在操作一个有一千万行的表时的运行时间与操作仅有一行的表时是一样的。 在对数据库运行ADD COLUMN之后，该数据库将无法由SQLite 3.1.3及更早版本读取，

5、除非运行VACUUM命令。ANALYZEsql-statement:=ANALYZEsql-statement:=ANALYZE database-namesql-statement:=ANALYZE database-name . table-nameANALYZE命令令集合关于索引的统计信息并将它们储存在数据库的一个特殊表中，查询优化器可以用该表来制作更好的索引选择。 若不给出参数，所有附加数据库中的所有索引被分析。若参数给出数据库名，该数据库中的所有索引被分析。若给出表名 作参数，则只有关联该表的索引被分析。最初的实现将所有的统计信息储存在一个名叫sqlite_stat1的表中。未来的加

6、强版本中可能会创建名字类似的其它表， 只是把1改为其它数字。sqlite_stat1表不能够被撤销，但其中的所有内容可以被删除，这是与撤销该表等效的行为。ATTACH DATABASEsql-statement:=ATTACH DATABASE database-filename AS database-nameATTACH DATABASE语句将一个已存在的数据库添加到当前数据库连接。若文件名含标点符号，则应用引号引起来。 数据库名main和temp代表主数据库和用于存放临时表的数据库，它们不能被拆分。拆分数据库使用 DETACH DATABASE语句。你可以读写附加数据库，或改变其结构。这

7、是SQLite3.0提供的新特性。在SQLite 2.8中，改变附加数据库的结构是不允许的。在附加数据库中添加一个与已有表同名的表是不允许的。但你可以附加带有与主数据库中的表同名的表的数据库。也可以多次附加同一数据库。使用database-name.table-name来引用附加数据库中的表。若附加数据库中的表与主数据库的表不重名，则不需加数据库名作为前缀。当数据库被附加时，它的所有不重名的表成为该名字指向的缺省表。之后附加的任意与之同名的表需要加前缀。若“缺省”表被拆分， 则最后附加的同名表变为“缺省”表。若主数据库不是:memory:，多附加数据库的事务是原子的。若主数据库是:memory

8、:则事务在每个独立文件中依然是原子的。 但若主机在改变两个或更多数据库的COMMIT语句进行时崩溃，则可能一部分文件被改变而其他的保持原样。附加数据库的原子性的提交 是SQLite 3.0的新特性。在SQLite 2.8中，所有附加数据库的提交类似于主数据库是:memory:时的情况。 对附加数据库的数目有编译时的限制，最多10个附加数据库。BEGIN TRANSACTIONsql-statement:=BEGIN DEFERRED | IMMEDIATE | EXCLUSIVE TRANSACTION namesql-statement:=END TRANSACTION namesql-st

9、atement:=COMMIT TRANSACTION namesql-statement:=ROLLBACK TRANSACTION name从2.0版开始，SQLite支持带有回退和原子性的提交的事务处理。可选的事务名称会被忽略。SQLite目前不允许嵌套事务。在事务之外，不能对数据库进行更改。如果当前没有有效的事务，任何修改数据库的命令(基本上除了SELECT以外的所有SQL命令)会自动启动一个事务。命令结束时，自动启动的事务会被提交。 可以使用BEGIN命令手动启动事务。这样启动的事务会在下一条COMMIT或ROLLBACK命令之前一直有效。但若数据库关闭 或出现错误且选用ROLLBA

10、CK冲突判定算法时，数据库也会ROLLBACK。查看ON CONFLICT子句 获取更多关于ROLLBACK冲突判定算法的信息。 在SQLite 3.0.8或更高版本中，事务可以是延迟的，即时的或者独占的。“延迟的”即是说在数据库第一次被访问之前不获得锁。 这样就会延迟事务，BEGIN语句本身不做任何事情。直到初次读取或访问数据库时才获取锁。对数据库的初次读取创建一个SHARED锁 ，初次写入创建一个RESERVED锁。由于锁的获取被延迟到第一次需要时，别的线程或进程可以在当前线程执行BEGIN语句之后创建另外的事务 写入数据库。若事务是即时的，则执行BEGIN命令后立即获取RESERVED锁

11、，而不等数据库被使用。在执行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或更早版本中，所有的事

12、务 都是独占的。 COMMIT命令在所有SQL命令完成之前并不作实际的提交工作。这样若两个或更多个SELECT语句在进程中间而执行COMMIT时，只有全部SELECT 语句结束才进行提交。执行COMMIT可能会返回SQLITE_BUSY错误代码。这就是说有另外一个线程或进程获取了数据库的读取锁，并阻止数据库被改变。当COMMIT获得该错误代码时，事务依然是活动的，并且在COMMIT可以在当前读取的线程读取结束后再次试图读取数据库。 注释comment:=SQL-comment | C-commentSQL-comment:=- single-lineC-comment:=/* multiple

13、-lines */注释不是SQL命令，但会出现在SQL查询中。它们被解释器处理为空白部分。它们可以在任何空白可能存在的地方开始 ，即使是在跨越多行的表达式中。SQL风格的注释仅对当前行有效。C风格的注释可以跨越多行。若没有结束符号，注释的范围将一直延伸到输入末尾，且不会引起报错。 新的SQL语句可以从多行注释结束的地方开始。C风格注释可以嵌入任何空白可以出现的地方，包括表达式内，或其他SQL语句中间， 并且C风格的注释不互相嵌套。SQL风格的注释出现在C风格注释中时将被忽略。 BEGIN TRANSACTIONsql-statement:=BEGIN DEFERRED | IMMEDIATE

14、| EXCLUSIVE TRANSACTION namesql-statement:=END TRANSACTION namesql-statement:=COMMIT TRANSACTION namesql-statement:=ROLLBACK TRANSACTION name从2.0版开始，SQLite支持带有回退和原子性的提交的事务处理。可选的事务名称会被忽略。SQLite目前不允许嵌套事务。在事务之外，不能对数据库进行更改。如果当前没有有效的事务，任何修改数据库的命令(基本上除了SELECT以外的所有SQL命令)会自动启动一个事务。命令结束时，自动启动的事务会被提交。 可以使用BEG

15、IN命令手动启动事务。这样启动的事务会在下一条COMMIT或ROLLBACK命令之前一直有效。但若数据库关闭 或出现错误且选用ROLLBACK冲突判定算法时，数据库也会ROLLBACK。查看ON CONFLICT子句 获取更多关于ROLLBACK冲突判定算法的信息。 在SQLite 3.0.8或更高版本中，事务可以是延迟的，即时的或者独占的。“延迟的”即是说在数据库第一次被访问之前不获得锁。 这样就会延迟事务，BEGIN语句本身不做任何事情。直到初次读取或访问数据库时才获取锁。对数据库的初次读取创建一个SHARED锁 ，初次写入创建一个RESERVED锁。由于锁的获取被延迟到第一次需要时，别的

16、线程或进程可以在当前线程执行BEGIN语句之后创建另外的事务 写入数据库。若事务是即时的，则执行BEGIN命令后立即获取RESERVED锁，而不等数据库被使用。在执行BEGIN IMMEDIATE之后， 你可以确保其它的线程或进程不能写入数据库或执行BEGIN IMMEDIATE或BEGIN EXCLUSIVE. 但其它进程可以读取数据库。 独占事务在所有的数据库获取EXCLUSIVE锁，在执行BEGIN EXCLUSIVE之后，你可以确保在当前事务结束前没有任何其它线程或进程 能够读写数据库。有关SHARED, RESERVED, 和EXCLUSIVE锁可以参见这里。SQLite 3.0.8

17、的默认行为是创建延迟事务。SQLite 3.0.0 到3.0.7中延迟事务是唯一可用的事务类型。SQLite 2.8或更早版本中，所有的事务 都是独占的。 COMMIT命令在所有SQL命令完成之前并不作实际的提交工作。这样若两个或更多个SELECT语句在进程中间而执行COMMIT时，只有全部SELECT 语句结束才进行提交。执行COMMIT可能会返回SQLITE_BUSY错误代码。这就是说有另外一个线程或进程获取了数据库的读取锁，并阻止数据库被改变。当COMMIT获得该错误代码时，事务依然是活动的，并且在COMMIT可以在当前读取的线程读取结束后再次试图读取数据库。 COPYsql-state

18、ment:=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_

19、dump的输出从而能够将PostgreSQL的数据轻松转换到SQLite中而设计的。table-name是将要导入数据的一个已存在的表的名字。filename是一个字符串或标识符，用于说明作为数据来源的文件。 filename可以使用STDIN从标准输入流中获取数据。输入文件的每一行被转换成一条单独的记录导入表中。字段用制表符分开。若某个字段的数据中出现制表符， 则前面被添加反斜线符号。数据中的反斜线则被替换为两条反斜线。可选的USING DELIMITERS子句可给出一个与制表符不同 的分界符。若字段由N组成，则被赋以空值NULL.使用这一命令时，利用可选的ON CONFLICT子句可以定义

20、替代的约束冲突判定算法。更多信息，参见 ON CONFLICT。当输入数据源是STDIN，输入将终止于一行仅包含一个反斜线和一个点的输入: .CREATE INDEXsql-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，

21、待索引表的名字，以及括弧内的用于索引键的字段列表构成。每个字段名可以跟随ASC 或DESC关键字说明排序法则，但在当前版本中排序法则被忽略。排序总是按照上升序。每个字段名后跟COLLATE子句定义文本记录的比较顺序。缺省的比较顺序是由CREATE TABLE语句说明的比较顺序。 若不定义比较顺序，则使用内建的二进制比较顺序。附加到单个表上的索引数目没有限制，索引中的字段数也没有限制。若UNIQUE关键字出现在CREATE和INDEX之间，则不允许重名的索引记录。试图插入重名记录将会导致错误。每条CREATE INDEX语句的文本储存于sqlite_master或sqlite_temp_mast

22、er表中，取决于被索引的表是否临时表。 每次打开数据库时，所有的CREATE INDEX语句从sqlite_master表中读出，产生SQLite的索引样式的内部结构。若使用可选的IF NOT EXISTS子句，且存在同名索引，则该命令无效。使用DROP INDEX命令删除索引。CREATE TABLEsql-command:=CREATE TEMP | TEMPORARY TABLE IF NOT EXISTS table-name (column-def , column-def*, constraint*)sql-command:=CREATE TEMP | TEMPORARY TABL

23、E database-name. table-name AS select-statementcolumn-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 ( e

24、xpr ) |DEFAULT value |COLLATE collation-nameconstraint:=PRIMARY KEY ( column-list ) conflict-clause |UNIQUE ( column-list ) conflict-clause |CHECK ( expr )conflict-clause:=ON CONFLICT conflict-algorithmCREATE TABLE语句基本上就是CREATE TABLE关键字后跟一个新的表名以及括号内的一堆 定义和约束。 表名可以是字符串或者标识符。以sqlite_开头的表名是留给数据库引擎使用的。

25、每个字段的定义是字段名后跟字段的数据类型，接着是一个或多个的字段约束。字段的 数据类型并不限制 字段中可以存放的数据。可以查看SQLite3的数据类型获取 更多信息。 UNIQUE约束为指定的字段创建索引，该索引须含有唯一键。COLLATE子句说明在比较字段的 文字记录时所使用的排序函数。缺省使用内嵌的BINARY排序函数。 DEFAULT约束说明在使用INSERT插入字段时所使用的缺省值。 该值可以是NULL，字符串常量或一个数。从3.1.0版开始，缺省值也可以是以下特殊的与事件无关的关键字CURRENT_TIME, CURRENT_DATE或CURRENT_TIMESTAMP.若缺省值为N

26、ULL,字符串常量或数，在执行未指明字段值的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键。这即是说字段仅能容纳唯一整

27、数值。(在除此之外的其它情况下，SQLite忽略数据类型的说明 ，允许任何类型的数据放入字段中，不管该字段被声明为什么数据类型。) 若一个表中不含一个INTEGER PRIMARY KEY字段，则B-Tree键 为自动产生的整数。一行的B-Tree键可以通过如下特殊的名字ROWID, OID, 或 _ROWID_ 进行访问，不论是否有INTEGER PRIMARY KEY存在。INTEGER PRIMARY KEY字段可以使用关键字AUTOINCREMENT声明。AUTOINCREMENT关键字修改了B-Tree键自动产生的方式。B-Tree键的生成 的其它信息可以在这里找到。若TEMP或TE

28、MPORARY关键字出现在CREATE和TABLE之间，则所建立的表仅在当前数据库连接可见，并在断开连接时自动被删除。在临时表上建立的任何索引也是临时的。临时表和索引单独存储在与主数据库文件不同的文件中。若说明了，则表在该数据库中被创建。同时声明和TEMP关键字会出错，除非 是temp.若不声明数据库名，也不使用TEMP关键字，则表创建于主数据库中。在每个约束后跟可选的ON CONFLICT子句可以定义替代的约束冲突判定算法。 缺省为ABORT。同一个表中的不同约束可以使用不同的缺省冲突判定算法。若一条COPY, INSERT, 或 UPDATE 命令指定了不同的冲突判定算法，则该算法将替代C

29、REATE 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内部实现。若原始命令为C

30、REATE TABLE AS则合成出等效的 CREATE TABLE语句并储存于sqlite_master表中代替原命令。CREATE TEMPORARY TABLE语句文本储存于 sqlite_temp_master表中。 若在命令中使用可选的IF NOT EXISTS子句且存在同名的另一个表，则当前的命令无效。删除表可以使用DROP TABLE语句。CREATE TRIGGERsql-statement:=CREATE TEMP | TEMPORARY TRIGGER trigger-name BEFORE | AFTER database-event ON database-name . table-nametrigger-actionsql-statement:=CREATE TEMP | TEMPORARY TRIGGER trigger-name INSTEAD OFdatabase-event ON database-name . view-nametrigger-actiondatabase-event:=DELETE | INSERT | UPDATE | UPDATE OF column-listtrigger-action:= FOR E