结构定义 |
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 |
|