一、结构定义
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
|
|
说明：
创建一个名为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
|
|
例子：
|
1
|
DROP
TABLE
customers;
|
2、DROP VIEW
删除一个视图。
语法：
|
1
|
|
例子：
|
1
|
DROP
VIEW
master_view;
|
3、DROP TRIGGER
删除一个触发器。
语法：
|
1
|
|
例子：
|
1
|
DROP
TRIGGER
update_customer_address;
|
4、DROP INDEX
删除一个索引。
语法：
|
1
|
|
例子：
|
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
|
|
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
|
|
2、END TRANSACTION
标记一个事务的终止。
语法：
|
1
|
|
3、COMMIT TRANSACTION
标志一个事务的结束。
语法：
|
1
|
|
4、ROLLBACK TRANSACTION
将事务回滚到事务的起点。
语法：
|
1
|
|
五、其他操作
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时执行该命令所需要使用的虚拟机指令序列，而不是真正执行该命令。
语法：
3、PRAGMA
用于修改sqlite库或查询sqlite库内部数据(non-table)的特殊命令。
语法：
|
1
|
|
使用整数值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
|
|
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:”时的情况。
语法：
|
1
|
|
6、DETACH DATABASE
拆分一个之前使用ATTACH DATABASE语句附加的数据库连接。可以使用不同的名字多次附加同一数据库，并且拆分一个连接不会影响其他连接。若sqlite在事务进行中，该语句不起作用。
语法：
|
1
|
|
