`
yanguz123
  • 浏览: 557311 次
  • 性别: Icon_minigender_1
  • 来自: 杭州
社区版块
存档分类
最新评论

SQLite 语法

 
阅读更多

SQLite库可以解析大部分标准SQL语言。但它也省去了一些特性并且加入了一些自己的新特性。这篇文档就是试图描述那些SQLite支持/不支持的SQL语法的。查看关键字列表。

如下语法表格中，纯文本用蓝色粗体显示。非终极符号为斜体红色。作为语法一部分的运算符用黑色Roman字体表示。

这篇文档只是对SQLite实现的SQL语法的综述，有所忽略。想要得到更详细的信息，参考源代码和语法文件“parse.y”。

SQLite执行如下的语法：

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

你可以读写附加数据库，或改变其结构。这是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 ::= 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”开头的含有十六进制文本信息的文本值。例如：

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_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文档。

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数据库，该命令无效。

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

 

 

 

 

 

 

 

 

 

 

 

 

 

SQLite虽然很小巧，但是支持的SQL语句不会逊色于其他开源数据库，同时它还支持事务处理功能等等。我觉得它像MySQL，但SQLite 支持跨平台，操作简单，能够使用很多语言直接创建数据库。

一、结构定义

1、CREATE TABLE

创建新表。

语法：

?
1
2
3
4
5
6
7
sql-command ::= CREATE [TEMP | TEMPORARY] TABLE 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] | UNIQUE [conflict-clause] | CHECK (expr) [conflict-clause] | DEFAULT value | COLLATE collation-name
constraint ::= PRIMARY KEY (column-list) [conflict-clause] | UNIQUE (column-list) [conflict-clause] | CHECK (expr) [conflict-clause]
conflict-clause ::= ON CONFLICT conflict

例子：

?
1
create table film(title, length, year, starring);

说明：

建立了一个名叫film的资料表，里面有name、length、year、starring四个字段。

2、CREATE VIEW

创建一个视图（虚拟表），该表以另一种方式表示一个或多个表中的数据。

语法：

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

例子：

?
1
CREATE VIEW master_view AS SELECT * FROM sqlite_master WHERE type='view';

说明：

创建一个名为master_view的视图，其中包括sqlite_master这个表中的所有视图表。

3、CREATE TRIGGER

创建触发器，触发器是一种特殊的存储过程，在用户试图对指定的表执行指定的数据修改语句时自动执行。

语法：

?
1
2
3
4
5
6
7
8
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

例子：

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

说明：

创建了一个名为update_customer_address的触发器，当用户更新customers表中的address字段时，将触发并更新orders表中的address字段为新的值。

比如执行如下一条语句：

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

数据库将自动执行如下语句：

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

4、CREATE INDEX

为给定表或视图创建索引。

语法：

?
1
2
sql-statement ::= CREATE [UNIQUE] INDEX index-name ON [database-name.]table-name (column-name [, column-name]*) [ON CONFLICT conflict-algorithm]
column-name ::= name [COLLATE collation-name] [ASC | DESC]

例子：

?
1
CREATE INDEX idx_email ON customers (email);

说明：

为customers表中的email创建一个名为idx_email的索引。

二、结构删除

1、DROP TABLE

删除表定义及该表的所有索引。

语法：

?
1
sql-command ::= DROP TABLE [database-name.]table-name

例子：

?
1
DROP TABLE customers;

2、DROP VIEW

删除一个视图。

语法：

?
1
sql-command ::= DROP VIEW view-name

例子：

?
1
DROP VIEW master_view;

3、DROP TRIGGER

删除一个触发器。

语法：

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

例子：

?
1
DROP TRIGGER update_customer_address;

4、DROP INDEX

删除一个索引。

语法：

?
1
sql-command ::= DROP INDEX [database-name.]index-name

例子：

?
1
DROP INDEX idx_email;

三、数据操作

1、INSERT

将新行插入到表。

语法：

?
1
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

2、UPDATE

更新表中的现有数据。

语法：

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

3、DELETE

从表中删除行。

语法：

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

4、SELECT

从表中检索数据。

语法：

?
1
2
3
4
5
6
7
8
9
10
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

5、REPLACE

用于替代INSERT的“INSERT OR REPLACE”变体，以更好的兼容MySQL。

语法：

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

四、事务处理

1、BEGIN TRANSACTION

标记一个事务的起始点。

语法：

?
1
sql-statement ::= BEGIN [TRANSACTION [name]]

2、END TRANSACTION

标记一个事务的终止。

语法：

?
1
sql-statement ::= END [TRANSACTION [name]]

3、COMMIT TRANSACTION

标志一个事务的结束。

语法：

?
1
sql-statement ::= COMMIT [TRANSACTION [name]]

4、ROLLBACK TRANSACTION

将事务回滚到事务的起点。

语法：

?
1
sql-statement ::= ROLLBACK [TRANSACTION [name]]

五、其他操作

1、COPY

主要用于导入大量的数据。

语法：

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

例子：

?
1
COPY customers FROM customers.csv;

2、EXPLAIN

EXPLAIN命令修饰语是一个非标准的扩展功能，灵感来自PostgreSQL中的相同命令，但操作完全不同。若EXPLAIN关键字出现在任何SQLite的SQL命令之前，则SQLite库返回不加EXPLAIN时执行该命令所需要使用的虚拟机指令序列，而不是真正执行该命令。

语法：

?
1
sql-statement ::= EXPLAIN sql-statement

3、PRAGMA

用于修改SQlite库或查询SQLite库内部数据(non-table)的特殊命令。

语法：

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

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

4、VACUUM

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开启该模式。

语法：

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

5、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个附加数据库。

语法：

?
1
sql-statement ::= ATTACH [DATABASE] database-filename AS database-name

6、DETACH DATABASE

拆分一个之前使用ATTACH DATABASE语句附加的数据库连接。可以使用不同的名字多次附加同一数据库，并且拆分一个连接不会影响其他连接。若SQLite在事务进行中，该语句不起作用。

语法：

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

id字段自增

sql="CREATETABLEIFNOTEXISTS MusicList (id integerprimarykey AutoIncrement,name varchar(20),path varchar(20))";

　　

<br>常用Select语句

复制代码
desc<table>//查看表结构 
select*from<table>//查询所有更
select , fromtable ;//查看指定列
selectdistinct , fromtable ;//非重复查询
insertinto users(_id,username,password) select*from users;//复制
select username from users where username like'S%' ;//非重名字首字母为大写S的用户
select username from users where username like'__S%' ;//非重名字第三个字母为大写S的用户
select*from users where _id in(001,220,230);
select*fromuserorderby _id;//以id的顺序排列
select*fromuserorderby _id desc;//以id反的顺序排
复制代码

分页

当数据库数据量很大时，分页显示是个很简单且符合人们习惯的一种处理方法。

获取数据行总数:

最简单的方法是：SELECT所有的行，再调用curosr.getCount() ;取得行数，但这样效率会很低。 SQLite为我们提供了一个函数很容易查出总共有多少行。有个名为cet6_table的表格，我们想知道总共有多少行的word;可以使用如下 语句来完成:
SELECTcount(word) ASnumberFROM cet6_table;

count()函数为我们返回一个Int整形，代表有多少行数据。返回的列的名字叫count(word)，为了方便阅读和处理用as number给这个列取个 别名number;

SELECT[word],[explanation]FROM cet6_table ORDERBY word LIMIT 100 OFFSET 200"

上语句，返回自第200行的最多100条数据。分页时我们只要修改offset 后面的数据即可取得对应页面的数据。　　

SQLite内建语法表

结构定义
CREATE TABLE

创建新表。

语法：

sql-command ::= CREATE [TEMP | TEMPORARY] TABLE 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 ] |
UNIQUE [ conflict-clause ] |
CHECK ( expr ) [ conflict-clause ] |
DEFAULT value |
COLLATE collation-name
constraint ::= PRIMARY KEY ( column-list ) [ conflict-clause ] |
UNIQUE ( column-list ) [ conflict-clause ] |
CHECK ( expr ) [ conflict-clause ]
conflict-clause ::= ON CONFLICT conflict-algorithm
CREATE VIEW

创建一个视图（虚拟表），该表以另一种方式表示一个或多个表中的数据。

语法：

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

例子：
CREATE VIEW master_view AS
    SELECT * FROM sqlite_master WHERE type='view';
说明：
创建一个名为master_view的视图，其中包括sqlite_master这个表中的所有视图表。
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 update_customer_address UPDATE OF address ON customers 
  BEGIN
    UPDATE orders SET address = new.address WHERE customer_name = old.name;
  END;
说明：
创建了一个名为update_customer_address的触发器，当用户更新customers表中的address字段时，将触发并更新orders表中的address字段为新的值。
比如执行如下一条语句：
UPDATE customers SET address = '1 Main St.' WHERE name = 'Jack Jones';
数据库将自动执行如下语句：
UPDATE orders SET address = '1 Main St.' WHERE customer_name = 'Jack Jones';
CREATE INDEX

为给定表或视图创建索引。

语法：

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

例子：
CREATE INDEX idx_email ON customers (email);
说明：
为customers表中的email创建一个名为idx_email的字段。
结构删除
DROP TABLE

删除表定义及该表的所有索引。

语法：

sql-command ::= DROP TABLE [database-name.] table-name
例子：
DROP TABLE customers;
DROP VIEW

删除一个视图。

语法：

sql-command ::= DROP VIEW view-name

例子：
DROP VIEW master_view;
DROP TRIGGER

删除一个触发器。

语法：

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

例子：
DROP TRIGGER update_customer_address;
DROP INDEX

删除一个索引。

语法：

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

例子：
DROP INDEX idx_email;
数据操作
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
UPDATE

更新表中的现有数据。

语法：

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

从表中删除行。

语法：

sql-statement ::= DELETE FROM [database-name .] table-name [WHERE expr]
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
REPLACE

类似INSERT

语法：

sql-statement ::= REPLACE INTO [database-name .] table-name [( column-list )] VALUES ( value-list ) |
REPLACE INTO [database-name .] table-name [( column-list )] select-statement
事务处理
BEGIN TRANSACTION

标记一个事务的起始点。

语法：

sql-statement ::= BEGIN [TRANSACTION [name]]
END TRANSACTION

标记一个事务的终止。

语法：

sql-statement ::= END [TRANSACTION [name]]
COMMIT TRANSACTION

标志一个事务的结束。

语法：

sql-statement ::= COMMIT [TRANSACTION [name]]
ROLLBACK TRANSACTION

将事务回滚到事务的起点。

语法：

sql-statement ::= ROLLBACK [TRANSACTION [name]]
其他操作
COPY

主要用于导入大量的数据。

语法：

sql-statement ::= COPY [ OR conflict-algorithm ] [database-name .] table-name FROM filename
[ USING DELIMITERS delim ]
例子：
COPY customers FROM customers.csv;
EXPLAIN

语法：

sql-statement ::= EXPLAIN sql-statement
PRAGMA

语法：

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

语法：

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

附加一个数据库到当前的数据库连接。

语法：

sql-statement ::= ATTACH [DATABASE] database-filename AS database-name
DETTACH DATABASE

从当前的数据库分离一个使用ATTACH DATABASE附加的数据库。

语法：

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

SQLite内建函数表

算术函数
abs(X) 返回给定数字表达式的绝对值。
max(X,Y[,...]) 返回表达式的最大值。
min(X,Y[,...]) 返回表达式的最小值。
random(*) 返回随机数。
round(X[,Y]) 返回数字表达式并四舍五入为指定的长度或精度。
字符处理函数
length(X) 返回给定字符串表达式的字符个数。
lower(X) 将大写字符数据转换为小写字符数据后返回字符表达式。
upper(X) 返回将小写字符数据转换为大写的字符表达式。
substr(X,Y,Z) 返回表达式的一部分。
randstr()  
quote(A)  
like(A,B) 确定给定的字符串是否与指定的模式匹配。
glob(A,B)  
条件判断函数
coalesce(X,Y[,...])  
ifnull(X,Y)  
nullif(X,Y)  
集合函数
avg(X) 返回组中值的平均值。
count(X) 返回组中项目的数量。
max(X) 返回组中值的最大值。
min(X) 返回组中值的最小值。
sum(X) 返回表达式中所有值的和。
其他函数
typeof(X) 返回数据的类型。
last_insert_rowid() 返回最后插入的数据的ID。
sqlite_version(*) 返回SQLite的版本。
change_count() 返回受上一语句影响的行数。
last_statement_change_count()  

 

 

 

 

 

 

 

 

 

 

 

1. SQL语法关键字

关键字 描述
Create Table 创建数据表
Alter Table 修改数据表
Drop Table 删除数据表
Create Index 创建索引
Drop Index 删除索引
Create Trigger 创建触发器
Drop Trigger 删除触发器
Create View 创建视图
Drop View 删除视图
Insert 插入数据
Delete 删除数据
Update 更新数据
Select 查询数据
Begin 启动事务
Commit 提交事务
Rollback 回滚事务

2. SQL数据类型  2.1. 本地类型--5种基本类型

数据名称 说明
INTEGER 整数值是全数字(包括正和负)。整数可以是1, 2, 3, 4, 6或 8字节。整数的最大范围(8 bytes)是{-9223372036854775808, 0, +9223372036854775807}。SQLite根据数字的值自动控制整数所占的字节数。空注：参可变长整数的概念。
REAL 实数是10进制的数值。SQLite使用8字节的符点数来存储实数。
TEXT 文本(TEXT)是字符数据。SQLite支持几种字符编码，包括UTF-8和UTF-16。字符串的大小没有限制。
BLOB 二进制大对象(BLOB)是任意类型的数据。BLOB的大小没有限制。
NULL NULL表示没有值。SQLite具有对NULL的完全支持。

  　SQLite通过值的表示法来判断其类型，下面就是SQLite的推理方法：  l SQL语句中用单引号或双引号括起来的文字被指派为TEXT。  l 如果文字是未用引号括起来的数据，并且没有小数点和指数，被指派为INTEGER。  l 如果文字是未用引号括起来的数据，并且带有小数点或指数，被指派为REAL。  l 用NULL说明的值被指派为NULL存储类。  l 如果一个值的格式为X’ABCD’，其中ABCD为16进制数字，则该值被指派为BLOB。X前缀大小写皆可。  　2.2. 兼容的SQL92类型

数据类型 类型描述 对应类型
integer(size) int(size) smallint(size) tinyint(size) 仅容纳整数。在括号内规定数字的最大位数。 INTEGER
decimal(size,d) numeric(size,d) 容纳带有小数的数字。"size" 规定数字的最大位数。"d" 规定小数点右侧的最多位数。 REAL
char(size) 容纳固定长度的字符串（可容纳字母、数字以及特殊字符）。在括号中规定字符串的长度。 TEXT
varchar(size) 容纳可变长度的字符串（可容纳字母、数字以及特殊的字符）。在括号中规定字符串的最大长度。 TEXT
date(yyyymmdd) 容纳日期。 TEXT

3. 表-Table  3.1. Create Table    3.1.1. 语法格式

CREATE TABLE [数据库名.]表名(字段名称1 字段类型 字段约束，字段名称2 字段类型 字段约束，字段名称3 字段类型 字段约束，字段名称4 字段类型 字段约束，… …分组约束1,分组约束2,… …);

    3.1.2. 字段约束

约束名称 约束说明
NOT NULL 非空，约束强制列不接受 NULL 值，约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新纪录或者更新记录。
PRIMARY KEY 主键，约束唯一标识数据库表中的每条记录。主键必须包含唯一的值。主键列不能包含 NULL 值。每个表应该都一个主键，并且每个表只能有一个主键。
FOREIGN KEY 外键，约束本字段的值必须存在于另一个表中主键字段，当使用外键约束时，如果外键在其所依赖的表中不存在，则记录插入失败。
UNIQUE 唯一性，约束唯一标识数据库表中的每条记录，即插入的字段值不可重复，唯一性约束可以包含NULL值，但每张表也只能有一个记录为NULL值。
DEFAULT 默认值，约束字段的默认值，如果插入数据时没有提供该字段的数值，则会使用默认值进行填充。

    3.1.3. 数据字典    表名：tbl_goods_category    描述：商品类别

字段名称 字段类型 字段长度 字段描述
category_code CHAR 8 类别编码
category_name VARCHAR 128 类别名称
category_desc VARCHAR 255 类别描述

    　表名：tbl_goods_info    描述：商品信息

字段名称 字段类型 字段长度 字段描述
category_code CHAR 8 类别编码
goods_id CHAR 16 商品编码
goods_name VARCHAR 128 商品名称
goods_unit VARCHAR 8 商品单位
prime_cost NUMBER (6,2) 进货价格
sale_price NUMBER (6,2) 零售价格
vip_price NUMBER (6,2) 会员价格
remark VARCHAR 255 备注信息

    　表名：tbl_stock_bill    描述：商品入库单

字段名称 字段类型 字段长度 字段描述
goods_id CHAR 16 商品编码
bill_id CHAR 20 进货单单号
stock_time DateTime   入库时间
stock_amount NUMBER (6,2) 入库数量
prime_cost NUMBER (6,2) 进货价格
       

    3.1.4. 脚本示例    Ø 创建商品类别表tbl_goods_category

--创建商品类别表--类别编码为主键--类别名称必须具有唯一性create table tbl_goods_category(category_code CHAR(8) primary key,  --类别编码category_name VARCHAR(128) UNIQUE, --类别名称category_desc VARCHAR(255)); --类别描述

    　Ø 创建商品信息表tbl_goods_info

--创建商品信息表--商品编码为主键--商品名称必须具有唯一性create table tbl_goods_info(category_code CHAR(8) , --类别编码goods_id CHAR(16) primary key, --商品编码goods_name VARCHAR(128) UNIQUE, --商品名称goods_unit VARCHAR(8), --商品单位prime_cost NUMBER(6,2), --进货价格sale_price NUMBER(6,2), --零售价格vip_price NUMBER(6,2), --会员价格remark VARCHAR(255)，FOREIGN KEY (category_code)REFERENCES tbl_goods_category(category_code)); --备注信息

      　Ø 创建商品入库单tbl_stock_bill

--创建商品入库单--商品编码和入库时间为组合主键create table tbl_stock_bill(goods_id CHAR(16), --商品编码stock_time DateTime, --入库时间stock_amount NUMBER(6,2), --入库数量prime_cost NUMBER(6,2),  --进货价格primary key(goods_id, stock_time),FOREIGN KEY (goods_id)REFERENCES tbl_goods_info(goods_id));

3.2. Alter Table    3.2.1. 语法格式    表重命名

ALTER TABLE [数据库名.]表名 RENAME TO 新表名

    　添加字段

ALTER TABLE [数据库名.]表名 ADD 字段名称 字段类型 字段约束

    　3.2.2. 脚本示例    Ø 创建一个学生信息表tbl_student

create table tbl_student(        std_id char(20) primary key,        std_name varchar(16),std_age integer);

    　Ø 修改学生信息表名为tbl_student_info

alter table tbl_student rename tbl_student_info

    　Ø 为学生信息表添加班级字段

alter table tbl_student_info add class char(8)

3.3. Drop Table    3.3.1. 语法格式

DROP TABLE [数据库名.]表名

    　3.3.2. 脚本示例    删除一个名为tbl_student_info的数据表

DROP TABLE tbl_student_info

4. 索引-Index  　4.1. Create Index    4.1.1. 语法格式

CREATE INDEX [数据库名.]索引名称 ON 表名(字段名称1 [ASC/DESC]，字段名称1 [ASC/DESC],… …);//在相应的表的列字段或多个列字段上建立相应的索引

    4.1.2. 脚本示例    创建一个名为tbl_student的学生信息表，并为该学生信息表创建索引

create table tbl_student(        std_id char(20) primary key,        std_name varchar(16),        std_age integer); --为学号创建升序索引create index idx_std_id ON tbl_student(std_id ASC); --为姓名和年龄创建姓名为升序，年龄为降序的索引create index idx_name_age ON tbl_student(std_name ASC, std_age DESC);

  　4.2. Drop Index      4.2.1. 语法格式

DROP INDEX [数据库名.]索引名

      　4.2.2. 脚本示例      删除一个名为idx_name_age的索引

DROP INDEX idx_name_age

5. 触发器-Trigger  　5.1. Create Trigger    5.1.1. 语法格式

CREATE TRIGGER 触发器名称[BEFORE|AFTER] 数据库事件 ON [数据库名称].表名[FOR EACH ROW][ WHEN expression]BEGIN触发器执行动作END

    　数据库事件：    DELETE INSERT    UPDATE    UPDATE OF 字段列表    　5.1.2. 脚本示例    　假设"customers"表存储了****，"orders"表存储了订单信息，下面的触发器确保当用户改变地址时所有的 关联订单地址均进行相应改变：

CREATE TRIGGER trg_on_update_customer_address AFTER UPDATE OF address ON customers  BEGIN UPDATE orders SET address = new.address WHERE customer_name = old.name; （DML语句）END;

  　5.2. Drop Trigger    5.2.1. 语法格式

DROP TRIGGER触发器名称

    　5.2.2. 脚本示例    删除一个名为trg_on_update_customer_address的触发器

DROP TRIGGER trg_on_update_customer_address

6. 视图-View在 SQL 中，视图是基于SQL 语句的结果集的可视化的表。视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加SQL 函数、WHERE 以及 JOIN 语句，我们也可以提交数据，就像这些来自于某个单一的表。  　6.1. Create View    6.1.1. 语法格式

CREATE VIEW [数据库名称].视图名称 AS Select查询语句

    注释：视图总是显示最近的数据。每当用户查询视图时，数据库引擎通过使用 SQL 语句来重建数据。    6.1.2. 脚本示例    假设有一个学生数据库，其中有班级表，还有学生信息表    　以学生信息表为基本，创建一个班级编号WF1103班的学生信息视图

CREATE VIEW view_student_wf1103 as select * from student where class = ‘WF1103’

  　6.2. Drop View    6.2.1. 语法格式

DROP VIEW [数据库名称].视图名称

    　6.2.2. 脚本示例    删除一个名为view_student_wf1103的学生信息表

DROP VIEW view_student_wf1103

7. 数据操作-Insert,Update,Delete  7.1. Insert    7.1.1. 语法格式

INSERT INTO [数据库名称].表名 VALUES(记录内容)//对应表的顺序进行添加和加入值INSERT INTO [数据库名称].表名(字段列表) VALUES(对应字段内容)//按字段列表添加值

    　7.1.2. 脚本示例

create table tbl_student(        std_id char(20) primary key,        std_name varchar(16),        std_age integer); INSERT INTO tbl_student VALUES（‘WF110301’,’张三’,23）;INSERT INTO tbl_student(std_id,std_name,std_age) VALUES（‘WF110301’,’张三’,23）;

  　7.2. Update    7.2.1. 语法格式

UPDATE [数据库名称].表名 SET 字段1=字段1值,字段2=字段2值… where 条件表达式

    　7.2.2. 脚本示例

UPDATE tbl_student SET std_age=24 where std_id=‘WF110301’

  　7.3. Delete    7.3.1. 语法格式

DELETE FROM [数据库名称].表名 where 条件表达式

    　7.3.2. 脚本示例

DELETE FROM tbl_student where std_id=‘WF110301’

8. 数据查询-Select

create table tbl_class(class_id varchar(8) primary key,class_name varchar(64),class_desc varchar(128)); create table tbl_student(class_id varchar(8),std_id varchar(16) primary key,std_name varchar(8),std_age integer,std_phone varchar(16),std_school varchar(40));

  　8.1. 基本查询    8.1.1. 语法格式

SELECT [DISTINCT] [*|字段列表] FROM [数据库名称].表名

    　8.1.2. 脚本示例

--查询学生信息表中的所有信息Select * from tbl_student --查询学生信息表中所学生的姓名及年龄的信息Select std_name, std_age from tbl_student

  　8.2. Where子句    Where子句通过条件表达式筛选满足条件的记录,条件表达式可以使用SQLite中的各种逻辑运算符号对字段进行筛选。  Where的操作符

操作符 描述
= 等于
<> 不等于
> 大于
< 小于
>= 大于等于
<= 小于等于
BETWEEN 在某个范围内
LIKE 搜索某种模式

    　8.2.1. 语法格式

SELECT [DISTINCT] [*|字段列表]FROM [数据库名称].表名 WHERE 条件表达式

    　8.2.2. 脚本示例

--查询学生信息表中学号等于WF110301的学号Select * from tbl_student where std_id = ‘WF110301’ --查询学生信息表中所年龄大于23的学生Select * from tbl_student where std_age > 23 --查询学生信息表中所有名字中姓‘刘’的学生或着名字最后一个字为‘刚’的学生Select * from tbl_student where std_name like ’刘%’ or std_name like ’%刚’ --查询学生信息表中农林大学，并且手机**中带有6的学生Select * from tbl_student where std_school = ’农林大学’ and std_phone like ’%6%’

  　8.3. Group by子句  GROUP BY子句可以在查询将指定的字段表数值相同的记录合并成一条输出， 它与count(*)函数相结合，可以统计在列表中指字段表数值相同的记录的条数。    8.3.1. 语法格式

SELECT [DISTINCT] [*|字段列表] FROM [数据库名称].表名 GROUP BY 字段1，字段2，… … SELECT [*|字段列表] [,count(*) as 新的字段名] FROM [数据库名称].表名 GROUP BY 字段1，字段2，… …

    　8.3.2. 脚本示例

--统计各个学校的学生数Select std_school, count(*) as std_count from tbl_student group by std_school, std_age

  　8.4. Order By子句  ORDER BY子句对所得结果根据表达式排序。    8.4.1. 语法格式

SELECT [DISTINCT] [*|字段列表] FROM [数据库名称].表名 ORDER BY 字段名1 [ASC/DESC],字段名2 [ASC/DESC],… …

    　8.4.2. 脚本示例

--查询学生信息，并按年龄递减，学号递增的排序方式显示Select * from tbl_student order by std_age DESC,std_id ASC

  　8.5. Limit Offset子句  LIMIT子句限定行数的最大值。负的LIMIT表示无上限。后跟可选的OFFSET说明跳过结果集中的前多少行,Limit Offset在分页显示中十分有用。  8.5.1. 语法格式

SELECT [DISTINCT] [*|字段列表] FROM [数据库名称].表名 Limit 单次最多读取行数 offset 跳过前面行数 SELECT [DISTINCT] [*|字段列表] FROM [数据库名称].表名 Limit跳过前面行数，单次最多读取行数

    　8.5.2. 脚本示例

--查询学生信息，跳过前面10行，获取5 条记录Select * from tbl_student Limit 5 offset 10或 跳过前面10行，获取5 条记录Select * from tbl_student Limit 10,5

8.6.  多表联合查询  8.6.1. 语法格式

SELECT [DISTINCT] [*|字段列表] FROM [数据库名称].表名1, [数据库名称].表名2 … WHERE 条件表达式      一般的连接

  　8.6.2. 脚本示例

--查询学生信息表中所有班级为WF1103的学生信息，并在结果中输出班级名称和学员姓名Select tbl_class.class_name,tbl_student.std_name from tbl_student, tbl_classwhere tbl_student.class_id = tbl_class.class_id

8.7. Join子句  JOIN用于根据两个或多个表中的列之间的关系，从这些表中查询数据,JOIN会将两个表的数据合并起来，输出具有两个表有字段的记录。  JOIN对于两个表格来说是相乘的关系，（inner join 与join就是普通的连接）    8.7.1. 语法格式

--在两个表中存在至少都存在一个能够满足条件表达式的匹配时--INNER JOIN 关键字返回行。INNER JOIN 与 JOIN 是相同的。 SELECT [DISTINCT] [*|字段列表] FROM [数据库名称].左表名… INNER JOIN [数据库名称].右表名ON 条件表达式 SELECT [DISTINCT] [*|字段列表] FROM [数据库名称].左表名… JOIN [数据库名称].右表名ON 条件表达式 --以左表为主，当左表有存在满足条件的记录时，就会从左表返回所有的行--即使右表都没有满足条件。SELECT [DISTINCT] [*|字段列表] FROM [数据库名称].左表名… LEFT JOIN [数据库名称].右表名ON 条件表达式

    　8.7.2. 脚本示例  例如，学生信息管理**中有学生信息和课程两张表，它们的格式和内容分别如下：  Tbl_student    Tbl_course    Ø INNER JOIN示例，查询班级表中的每一位学生需要学习的课程内容：

Select Tbl_student.class_id, Tbl_student.std_id, Tbl_student.std_name, Tbl_course.course, TBL_course.teacher from Tbl_student INNER JOIN Tbl_course ON Tbl_student.class_id = Tbl_course.class_id

    　注：由于课程表中没有WF1104班的课程，学生信息表中没有WF1105班的学生，所以使用INNER JOIN时，只会返回两个表同时存在的WF1103班中的学生信息      Ø LEFT JOIN示例，查询班级表中的每一位学生需要学习的课程内容：

Select Tbl_student.class_id, Tbl_student.std_id, Tbl_student.std_name, Tbl_course.course, TBL_course.teacher from Tbl_student LEFT JOIN Tbl_course ON Tbl_student.class_id = Tbl_course.class_id

    　注：采用LEFT JOIN无论如何都会返回左表的所有记录，即使右表中没有满足条件的记录，所以，即使课程表中没有WF1104班的课程，在左表中的WF1104班的学生信息      仍然会被返回。  8.8. Union子句  UNION 操作符用于合并两个或多个 SELECT 语句的结果集。请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。（个数和类型相同）    8.8.1.  语法格式

SELECT [*|字段列表] FROM [数据库名称].表名1UNIONSELECT [*|字段列表] FROM [数据库名称].表名2  SELECT [*|字段列表] FROM [数据库名称].表名1UNION ALLSELECT [*|字段列表] FROM [数据库名称].表名2

    　8.8.2. 脚本示例

 

  　9. 事务和锁-Transaction and Lock  9.1. Begin（事务启动）  9.2. Commit（提交）  9.3. Rollback（回滚）  9.4. Sqlite  old.db.dump | sqlite3 new.db(这样可以3<-->2.8)  9.5. 运算符号

操作符 类型 作用
|| String Concatenation
* Arithmetic Multiply
/ Arithmetic Divide
% Arithmetic Modulus
+ Arithmetic Add
– Arithmetic Subtract
<< Bitwise Right shift
>> Bitwise Left shift
& Logical And
| Logical Or
< Relational Less than
<= Relational Less than or equal to
> Relational Greater than
>= Relational Greater than or equal to
= Relational Equal to
== Relational Equal to
<> Relational Not equal to
!= Relational Not equal to
IN Logical In
AND Logical And
OR Logical Or
LIKE Relational String matching
GLOB Relational Filename matching




10. 内建函数

SQLite内建函数表 算术函数 abs(X)  返回给定数字表达式的绝对值。 max(X,Y[,...])  返回表达式的最大值。 min(X,Y[,...])  返回表达式的最小值。 random(*)  返回随机数。 round(X[,Y])  返回数字表达式并四舍五入为指定的长度或精度。 字符**函数 length(X)  返回给定字符串表达式的字符个数。 lower(X)  将大写字符数据转换为小写字符数据后返回字符表达式。 upper(X)  返回将小写字符数据转换为大写的字符表达式。 substr(X,Y,Z)  返回表达式的一部分。 randstr()  quote(A)  like(A,B)  确定给定的字符串是否与指定的模式匹配。 glob(A,B)  条件判断函数 coalesce(X,Y[,...])

ifnull(X,Y)

nullif(X,Y)  集合函数 avg(X)  返回组中值的平均值。 count(X)  返回组中项目的数量。 max(X)  返回组中值的最大值。 min(X)  返回组中值的最小值。 sum(X)  返回表达式中所有值的和。 其他函数 typeof(X)  返回数据的类型。 last_insert_rowid()  返回最后插入的数据的ID。 sqlite_version(*)  返回SQLite的版本。 change_count()  返回受上一语句影响的行数。 last_statement_change_count()

 

 

 

 

 

 

 

 

 

 

 

qlite数据库只用一个文件就ok，小巧方便，所以是一个非常不错的嵌入式数据库，SQLite大量的被用于手机，PDA，MP3播放器以及机顶盒设备。
    Mozilla Firefox使用SQLite作为数据库。
    Mac计算机中的包含了多份SQLite的拷贝，用于不同的应用。
    PHP将SQLite作为内置的数据库。
    Skype客户端软件在内部使用SQLite。
    SymbianOS(智能手机操作平台的领航)内置SQLite。
    AOL邮件客户端绑定了SQLite。
    Solaris 10在启动过程中需要使用SQLite。
    McAfee杀毒软件使用SQLite。
    iPhones使用SQLite。
    Symbian和Apple以外的很多手机生产厂商使用SQLite。
下面就sqlite中的常用命令和语法介绍
http://www.sqlite.org/download.html可下载不同操作系统的相关版本sqlite  gedit
也可以使用火狐中的插件sqlite manager  


新建数据库
sqlite3 databasefilename
检查databasefilename是否存在，如果不存在就创建并进入数据库（如果直接退出，数据库文件不会创建）  如果已经存在直接进入数据库 对数据库进行操作


sqlite中命令:
以.开头,大小写敏感（数据库对象名称是大小写不敏感的）
.exit
.help 查看帮助 针对命令
.database 显示数据库信息；包含当前数据库的位置
.tables 或者 .table 显示表名称  没有表则不显示
.schema 命令可以查看创建数据对象时的SQL命令；
.schema databaseobjectname查看创建该数据库对象时的SQL的命令；如果没有这个数据库对象就不显示内容，不会有错误提示


.read FILENAME 执行指定文件中的SQL语句
.headers on/off  显示表头 默认off


.mode list|column|insert|line|tabs|tcl|csv   改变输出格式，具体如下


sqlite> .mode list
sqlite> select * from emp;
7369|SMITH|CLERK|7902|17-12-1980|800||20
7499|ALLEN|SALESMAN|7698|20-02-1981|1600|300|30
如果字段值为NULL 默认不显示 也就是显示空字符串


sqlite> .mode column
sqlite> select * from emp;
7369        SMITH       CLERK       7902        17-12-1980  800                     20        
7499        ALLEN       SALESMAN    7698        20-02-1981  1600        300         30        
7521        WARD        SALESMAN    7698        22-02-1981  1250        500         30 


sqlite> .mode insert
sqlite> select * from dept;
INSERT INTO table VALUES(10,'ACCOUNTING','NEW YORK');
INSERT INTO table VALUES(20,'RESEARCH','DALLAS');
INSERT INTO table VALUES(30,'SALES','CHICAGO');
INSERT INTO table VALUES(40,'OPERATIONS','BOSTON');


sqlite> .mode line
sqlite> select * from dept;
DEPTNO = 10
 DNAME = ACCOUNTING
   LOC = NEW YORK


DEPTNO = 20
 DNAME = RESEARCH
   LOC = DALLAS


DEPTNO = 30
 DNAME = SALES
   LOC = CHICAGO


DEPTNO = 40
 DNAME = OPERATIONS
   LOC = BOSTON


sqlite> .mode tabs
sqlite> select * from dept;
10ACCOUNTING  NEW YORK
20RESEARCH  DALLAS
30SALES  CHICAGO
40OPERATIONS  BOSTON


sqlite> .mode tcl
sqlite> select * from dept;
"10""ACCOUNTING""NEW YORK"
"20""RESEARCH""DALLAS"
"30""SALES"  "CHICAGO"
"40""OPERATIONS""BOSTON"


sqlite> .mode csv
sqlite> select * from dept;
10,ACCOUNTING,"NEW YORK"
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON


.separator "X" 更改分界符号为X
sqlite> .separator '**'  
sqlite> select * from dept;
10**ACCOUNTING**"NEW YORK"
20**RESEARCH**DALLAS
30**SALES**CHICAGO
40**OPERATIONS**BOSTON


.dump ?TABLE?            生成形成数据库表的SQL脚本
.dump 生成整个数据库的脚本在终端显示
.output stdout           将输出打印到屏幕  默认
.output filename  将输出打印到文件（.dump  .output 结合可将数据库以sql语句的形式导出到文件中）
.nullvalue STRING        查询时用指定的串代替输出的NULL串 默认为.nullvalue ''


字段类型：


数据库中存储的每个值都有一个类型,都属于下面所列类型中的一种,(被数据库引擎所控制)
NULL: 这个值为空值
INTEGER: 值被标识为整数,依据值的大小可以依次被存储为1,2,3,4,5,6,7,8个字节
REAL: 所有值都是浮动的数值,被存储为8字节的IEEE浮动标记序号.
TEXT: 文本. 值为文本字符串,使用数据库编码存储(TUTF-8, UTF-16BE or UTF-16-LE).
BLOB: 值是BLOB数据,如何输入就如何存储,不改变格式.


值被定义为什么类型只和值自身有关,和列没有关系,和变量也没有关系.所以sqlite被称作 弱类型 数据库
数据库引擎将在执行时检查、解析类型，并进行数字存储类型(整数和实数)和文本类型之间的转换.
SQL语句中部分的带双引号或单引号的文字被定义为文本,
如果文字没带引号并没有小数点或指数则被定义为整数,
如果文字没带引号但有小数点或指数则被定义为实数, 
如果值是空则被定义为空值.
BLOB数据使用符号X'ABCD'来标识.


但实际上，sqlite3也接受如下的数据类型： 
smallint 16位的整数。 
interger 32位的整数。 
decimal(p,s) 精确值p是指全部有几个十进制数,s是指小数点后可以有几位小数。如果没有特别指定，则系统会默认为p=5 s=0 。 
float  32位元的实数。 
double  64位元的实数。 
char(n)  n 长度的字串，n不能超过 254。 
varchar(n) 长度不固定且其最大长度为 n 的字串，n不能超过 4000。 
graphic(n) 和 char(n) 一样，不过其单位是两个字节， n不能超过127。这个形态是为了支持两个字节长度的字体，如中文字。 
vargraphic(n) 可变长度且其最大长度为n的双字元字串，n不能超过2000 
date  包含了 年份、月份、日期。 
time  包含了 小时、分钟、秒。 
timestamp 包含了 年、月、日、时、分、秒、千分之一秒。


SQLite包含了如下时间/日期函数： 
datetime() 产生日期和时间    无参数表示获得当前时间和日期
sqlite> select datetime();
2012-01-07 12:01:32
有字符串参数则把字符串转换成日期
sqlite> select datetime('2012-01-07 12:01:30'); 
2012-01-07 12:01:30


select date('2012-01-08','+1 day','+1 year');
2013-01-09


select datetime('2012-01-08 00:20:00','+1 hour','-12 minute');
2012-01-08 01:08:00


select datetime('now','start of year');
2012-01-01 00:00:00


select datetime('now','start of month');
2012-01-01 00:00:00


select datetime('now','start of day');
2012-01-08 00:00:00


select datetime('now','start of week');错误


select datetime('now','localtime');
结果：2006-10-17 21:21:47


date()产生日期    
sqlite> select date('2012-01-07 12:01:30'); 
2012-01-07
同理 有参和无参
select date('now','start of year');
2012-01-01


select date('2012-01-08','+1 month');
2012-02-08


time() 产生时间 
select time();
03:14:30


select time('23:18:59');
23:18:59


select time('23:18:59','start of day');
00:00:00


select time('23:18:59','end of day');错误


在时间/日期函数里可以使用如下格式的字符串作为参数： 
YYYY-MM-DD 
YYYY-MM-DD HH:MM 
YYYY-MM-DD HH:MM:SS 
YYYY-MM-DD HH:MM:SS.SSS 
HH:MM 
HH:MM:SS 
HH:MM:SS.SSS 
now 
其中now是产生现在的时间。


日期不能正确比较大小,会按字符串比较，日期默认格式 dd-mm-yyyy
select hiredate from emp order by hiredate;


17-11-1981
17-12-1980
19-04-1987
20-02-1981
22-02-1981


strftime() 对以上三个函数产生的日期和时间进行格式化
strftime()函数可以把YYYY-MM-DD HH:MM:SS格式的日期字符串转换成其它形式的字符串。 strftime(格式, 日期/时间, 修正符, 修正符, …)  select strftime('%d',datetime());
它可以用以下的符号对日期和时间进行格式化： 
%d 在该月中的第几天, 01-31 
%f 小数形式的秒，SS.SSS 
%H 小时, 00-23 
%j 算出某一天是该年的第几天，001-366 
%m 月份，00-12 
%M 分钟, 00-59 
%s 从1970年1月1日到现在的秒数 
%S 秒, 00-59 
%w 星期, 0-6 (0是星期天) 
%W 算出某一天属于该年的第几周, 01-53 
%Y 年, YYYY 
%% 百分号


select strftime('%Y.%m.%d %H:%M:%S','now'); 
select strftime('%Y.%m.%d %H:%M:%S','now','localtime'); 
结果：2006.10.17 21:41:09


select hiredate from emp 
order by strftime('%Y.%m.%d %H:%M:%S',hiredate); 正确


select strftime('%Y.%m.%d %H:%M:%S',hiredate) from emp 
order by strftime('%Y.%m.%d %H:%M:%S',hiredate); 错误




算术函数 
abs(X) 返回给定数字表达式的绝对值。 
max(X,Y[,...]) 返回表达式的最大值。  组函数 max(列名)
sqlite> select max(2,3,4,5,6,7,12);
12


min(X,Y[,...]) 返回表达式的最小值。 
random() 返回随机数。
sqlite> select random();
3224224213599993831
 
round(X[,Y]) 返回数字表达式并四舍五入为指定的长度或精度。 


字符处理函数 
length(X) 返回给定字符串表达式的字符个数。 
lower(X) 将大写字符数据转换为小写字符数据后返回字符表达式。 
upper(X) 返回将小写字符数据转换为大写的字符表达式。 
substr(X,Y,Z) 返回表达式的一部分。  从Y开始读Z个字符  Y最小值1
sqlite> select substr('abcdef',3,3);            
cde


quote(A) 给字符串加引号
 sqlite> select quote('aaa');
'aaa'


条件判断函数 
ifnull(X,Y)  如果X为null 返回Y
select ifnull(comm,0) from emp;
0
300
500
0
1400




集合函数 
avg(X) 返回组中值的平均值。 
count(X) 返回组中项目的数量。 
max(X) 返回组中值的最大值。 
min(X) 返回组中值的最小值。 
sum(X) 返回表达式中所有值的和。 


其他函数 
typeof(X) 返回数据的类型。 
sqlite> select typeof(111);
integer
sqlite> select typeof('233');
text
sqlite> select typeof('2012-12-12');
text
sqlite> select typeof('223.44');
text
sqlite> select typeof(223.44);
real


last_insert_rowid() 返回最后插入的数据的ID。 
sqlite_version() 返回SQLite的版本。 
sqlite> select sqlite_version();
3.7.9


change_count() 返回受上一语句影响的行数。 
last_statement_change_count()


create table emp_bak select * from EMP;不能在sqlite中使用


插入记录
insert into table_name values (field1, field2, field3...);
查询
select * from table_name;查看table_name表中所有记录；
select * from table_name where field1='xxxxx'; 查询符合指定条件的记录；


select ..... 
from table_name[,table_name2,...]
where .....
group by....
having ....
order by ...


select ..... 
from table_name  inner join | left outer join | right outer join table_name2
on ...
where .....
group by....
having ....
order by ...


子查询：
select * 
from EMP m
where SAL>
(select avg(SAL) from EMP where DEPTNO=m.DEPTNO);  


支持case when then 语法
update EMP
set SAL=
(
case
when DEPTNO=10 and JOB='MANAGER' then SAL*1.1
when DEPTNO=20 and JOB='CLERK' then SAL*1.2
when DEPTNO=30  then SAL*1.1
when DEPTNO=40  then SAL*1.2
else SAL
END
);


select ENAME, 
case DEPTNO
when 10 then '后勤部'
when 20 then '财务部'
when 30 then '内务部门'
else '其他部门'
end as dept
from EMP;


支持关联子查询  in后面的语法中可以有limit（mysql不可以）
select *
from emp e
where e.EMPNO in 
(
select empno  
from EMP
where deptno=e.DEPTNO
order by SAL desc
limit 0,2
);


支持表和表之间的数据合并等操作
union 去重复  union all 不去掉重复
select deptno from emp
union 
select deptno from dept;


select deptno from emp
union all
select deptno from dept;


在列名前加distinct也是去重复
sqlite> select distinct deptno from emp;




删除
delete from table_name where ...


删除表
drop table_name;     删除表；
drop index_name;     删除索引；


修改
update table_name
set xxx=value[, xxx=value,...]
where ...


建立索引


如果资料表有相当多的资料，我们便会建立索引来加快速度。好比说：


create index film_title_index on film(title);
意思是针对film资料表的name字段，建立一个名叫film_name_index的索引。这个指令的语法为


CREATE [ UNIQUE ]  NONCLUSTERED  INDEX index_name
    ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
create index index_name on table_name(field_to_be_indexed);
一旦建立了索引，sqlite3会在针对该字段作查询时，自动使用该索引。这一切的操作都是在幕后自动发生的，无须使用者特别指令。


其他sqlite的特别用法


sqlite可以在shell底下直接执行命令：
sqlite3 film.db "select * from emp;"


输出 HTML 表格：
sqlite3 -html film.db "select * from film;"
将数据库「倒出来」：


sqlite3 film.db ".dump" > output.sql
利用输出的资料，建立一个一模一样的数据库（加上以上指令，就是标准的SQL数据库备份了）：


sqlite3 film.db < output.sql
在大量插入资料时，你可能会需要先打这个指令：


begin;
插入完资料后要记得打这个指令，资料才会写进数据库中：
commit;


sqlite> begin;
sqlite> insert into aaaa values('aaa','333');
sqlite> select * from aaaa;
2|sdfds
sdfsd|9
2012-12-12|13:13:13
aaa|333
sqlite> rollback;
sqlite> select * from aaaa;
2|sdfds
sdfsd|9
2012-12-12|13:13:13


创建和删除视图
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
DROP VIEW view_name


create view  e as
select avg(SAL) avgsal,DEPTNO
from EMP
group by DEPTNO;


select ENAME,EMP.DEPTNO,SAL,avgsal
from EMP inner join e
on EMP.DEPTNO=e.DEPTNO
where SAL>avgsal;

练习员工表：

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE DEPT
(
DEPTNO int(2) not null,
DNAME varchar(14),
LOC    varchar(13)
);
INSERT INTO "DEPT" VALUES(10,'ACCOUNTING','NEW YORK');
INSERT INTO "DEPT" VALUES(20,'RESEARCH','DALLAS');
INSERT INTO "DEPT" VALUES(30,'SALES','CHICAGO');
INSERT INTO "DEPT" VALUES(40,'OPERATIONS','BOSTON');
CREATE TABLE EMP
(
EMPNO    int(4) not null,
ENAME    varchar(10),
JOB      varchar(9),
MGR      int(4),
HIREDATE date,
SAL      int(7 ),
COMM     int(7 ),
DEPTNO   int(2)
);
INSERT INTO "EMP" VALUES(7369,'SMITH','CLERK',7902,'17-12-1980',800,NULL,20);
INSERT INTO "EMP" VALUES(7499,'ALLEN','SALESMAN',7698,'20-02-1981',1600,300,30);
INSERT INTO "EMP" VALUES(7521,'WARD','SALESMAN',7698,'22-02-1981',1250,500,30);
INSERT INTO "EMP" VALUES(7566,'JONES','MANAGER',7839,'02-04-1981',2975,NULL,20);
INSERT INTO "EMP" VALUES(7654,'MARTIN','SALESMAN',7698,'28-09-1981',1250,1400,30);
INSERT INTO "EMP" VALUES(7698,'BLAKE','MANAGER',7839,'01-05-1981',2850,NULL,30);
INSERT INTO "EMP" VALUES(7782,'CLARK','MANAGER',7839,'09-06-1981',2450,NULL,10);
INSERT INTO "EMP" VALUES(7788,'SCOTT','ANALYST',7566,'19-04-1987',3000,NULL,20);
INSERT INTO "EMP" VALUES(7839,'KING','PRESIDENT',NULL,'17-11-1981',5000,NULL,10);
INSERT INTO "EMP" VALUES(7844,'TURNER','SALESMAN',7698,'08-09-1981',1500,0,30);
INSERT INTO "EMP" VALUES(7876,'ADAMS','CLERK',7788,'23-05-1987',1100,NULL,20);
INSERT INTO "EMP" VALUES(7900,'JAMES','CLERK',7698,'03-12-1981',950,NULL,30);
INSERT INTO "EMP" VALUES(7902,'FORD','ANALYST',7566,'03-12-1981',3000,NULL,20);
INSERT INTO "EMP" VALUES(7934,'MILLER','CLERK',7782,'23-01-1982',1300,NULL,10);
CREATE TABLE SALGRADE
(
GRADE int,
LOSAL int,
HISAL int
);
INSERT INTO "SALGRADE" VALUES(1,700,1200);
INSERT INTO "SALGRADE" VALUES(2,1201,1400);
INSERT INTO "SALGRADE" VALUES(3,1401,2000);
INSERT INTO "SALGRADE" VALUES(4,2001,3000);
INSERT INTO "SALGRADE" VALUES(5,3001,9999);
COMMIT;

分享到：
| jcifs技术实现单点登录
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

Magicbox
Global site tag (gtag.js) - Google Analytics