结构定义 |
CREATE TABLE | 创建新表。 语法:
sql-command ::= | CREATE [TEMP| TEMPORARY] TABLEtable-name( column-def[,column-def]* [,constraint]* ) | sql-command ::= | CREATE [TEMP| TEMPORARY] TABLE[database-name.]table-name ASselect-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 ASselect-statement | 例子: CREATE VIEW master_view AS SELECT * FROM sqlite_masterWHERE type='view'; 说明: 创建一个名为master_view的视图,其中包括sqlite_master这个表中的所有视图表。 |
CREATE TRIGGER | 创建触发器,触发器是一种特殊的存储过程,在用户试图对指定的表执行指定的数据修改语句时自动执行。 语法:
sql-statement ::= | CREATE [TEMP| TEMPORARY] TRIGGERtrigger-name [ BEFORE | AFTER] database-event ON[database-name.] table-name trigger-action |
sql-statement ::= | CREATE [TEMP| TEMPORARY] TRIGGERtrigger-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] INDEXindex-name ON [database-name.] table-name( column-name[,column-name]*) [ ON CONFLICT conflict-algorithm] | column-name ::= | name [ COLLATEcollation-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 [ORconflict-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 [ ORconflict-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 [FROMtable-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-optable join-args]* | table ::= | table-name [ASalias] | ( select) [ASalias] | 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 [ ORconflict-algorithm] [database-name.] table-name FROMfilename [ 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 ASdatabase-name | |
DETTACH DATABASE | 从当前的数据库分离一个使用ATTACH DATABASE附加的数据库。 语法:
sql-command ::= | DETACH [DATABASE]database-name | |