SQLite语法:
结构定义 | |
CREATE TABLE 创建新表 | 语法: CREATE [TEMP | TEMPORARY] TABLE table-name ( column-def [, column-def]* [, constraint]* ) 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 创建一个视图(虚拟表) | 语法: CREATE [TEMP | TEMPORARY] VIEW [database-name.] view-name AS select-statement |
CREATE TRIGGER 创建触发器 | 语法: CREATE [TEMP | TEMPORARY] TRIGGER trigger-name [ BEFORE | AFTER ] database-event ON [database-name .] table-name trigger-action 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 为表或视图创建索引 | 语法: 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 删除表定义及该表的所有索引 | 语法: DROP TABLE [database-name.] table-name |
DROP VIEW 删除一个视图 | 语法: DROP VIEW view-name |
DROP TRIGGER 删除一个触发器 | 语法: DROP TRIGGER [database-name .] trigger-name |
DROP INDEX 删除一个索引 | 语法: DROP INDEX [database-name .] index-name |
数据操作 | |
INSERT 将新行插入到表 | 语法: 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 更新表中的现有数据 | 语法: UPDATE [ OR conflict-algorithm ] [database-name .] table-name SET assignment [, assignment]* [WHERE expr] assignment ::= column-name = expr |
DELETE 从表中删除行 | 语法: DELETE FROM [database-name .] table-name [WHERE expr] |
SELECT 从表中检索数据 | 语法: 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]]
|
REPLACE 类似INSERT | 语法: REPLACE INTO [database-name .] table-name [( column-list )] VALUES ( value-list ) | REPLACE INTO [database-name .] table-name [( column-list )] select-statement |
事务处理 | |
事务开始 | 语法: BEGIN [TRANSACTION [name]] |
事务终止 | 语法: END [TRANSACTION [name]] |
事务提交 | 语法: COMMIT [TRANSACTION [name]] |
回滚事务 | 语法: ROLLBACK [TRANSACTION [name]] |
其他操作 | |
COPY 导入大量的数据 | 语法: COPY [ OR conflict-algorithm ] [database-name .] table-name FROM filename [ USING DELIMITERS delim ] 例子: COPY customers FROM customers.csv; |
ATTACH DATABASE 附加一个数据库到当前的数据库连接 | 语法: ATTACH [DATABASE] database-filename AS database-name |
DETTACH DATABASE 从当前的数据库分离一个使用ATTACH DATABASE附加的数据库 | 语法: DETACH [DATABASE] database-name |
SQLite内建函数 | |
算术函数 | |
abs(X) | 返回给定数字表达式的绝对值。 |
max(X,Y[,...]) | 返回表达式的最大值。 |
min(X,Y[,...]) | 返回表达式的最小值。 |
random(*) | 返回随机数。 |
round(X[,Y]) | 返回数字表达式并四舍五入为指定的长度或精度。 |
字符处理函数 | |
length(X) | 返回给定字符串表达式的字符个数。 |
lower(X) | 将大写字符数据转换为小写字符数据后返回字符表达式。 |
upper(X) | 返回将小写字符数据转换为大写的字符表达式。 |
substr(X,Y,Z) | 返回表达式的一部分。 |
randstr() |
|
quote(A) |
|
like(A,B) | 确定给定的字符串是否与指定的模式匹配。 |
glob(A,B) |
|
条件判断函数 | |
coalesce(X,Y[,...]) |
|
ifnull(X,Y) |
|
nullif(X,Y) |
|
集合函数 | |
avg(X) | 返回组中值的平均值。 |
count(X) | 返回组中项目的数量。 |
max(X) | 返回组中值的最大值。 |
min(X) | 返回组中值的最小值。 |
sum(X) | 返回表达式中所有值的和。 |
其他函数 | |
typeof(X) | 返回数据的类型。 |
last_insert_rowid() | 返回最后插入的数据的ID。 |
sqlite_version(*) | 返回SQLite的版本。 |
change_count() | 返回受上一语句影响的行数。 |
last_statement_change_count() |
|