例子: 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-opselect]*[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-optablejoin-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