SQLite入门之基本语法

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