三,数据操作
1.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
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:更新表中的现有数据。
语法:
sql - statement :: = UPDATE [ OR conflict-algorithm ] [ database-name . ] table - name
SET assignment [ , assignment ] *
[ WHERE expr ]
assignment :: = column - name = expr
sql - statement :: = UPDATE [ OR conflict-algorithm ] [ database-name . ] table - name
SET assignment [ , assignment ] *
[ WHERE expr ]
assignment :: = column - name = expr
3.DELETE:从表中删除行。
语法:
sql - statement :: = DELETE FROM [ database-name . ] table - name [ WHERE expr ]
sql - statement :: = DELETE FROM [ database-name . ] table - name [ WHERE expr ]
4.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
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。
语法:
sql - statement :: = REPLACE INTO [ database-name . ] table - name [ ( column-list ) ] VALUES ( value - list ) |
REPLACE INTO [ database-name . ] table - name [ ( column-list ) ] select - statement
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:标记一个事务的起始点。
语法:
sql - statement :: = BEGIN [ TRANSACTION [name ] ]
sql - statement :: = BEGIN [ TRANSACTION [name ] ]
2.END TRANSACTION:标记一个事务的终止。
语法:
sql - statement :: = END [ TRANSACTION [name ] ]
sql - statement :: = END [ TRANSACTION [name ] ]
3.COMMIT TRANSACTION:标志一个事务的结束。
语法:
sql - statement :: = COMMIT [ TRANSACTION [name ] ]
sql - statement :: = COMMIT [ TRANSACTION [name ] ]
4.ROLLBACK TRANSACTION:将事务回滚到事务的起点.
语法:
sql - statement :: = ROLLBACK [ TRANSACTION [name ] ]
sql - statement :: = ROLLBACK [ TRANSACTION [name ] ]
五。其他操作
1.COPY:主要用于导入大量的数据。
语法:
sql - statement :: = COPY [ OR conflict-algorithm ] [ database-name . ] table - name FROM filename
[ USING DELIMITERS delim ]
例子:
COPY customers FROM customers.csv;
sql - statement :: = COPY [ OR conflict-algorithm ] [ database-name . ] table - name FROM filename
[ USING DELIMITERS delim ]
例子:
COPY customers FROM customers.csv;
2.ATTACH DATABASE:附加一个数据库到当前的数据库连接。
语法:
sql - statement :: = ATTACH [ DATABASE ] database - filename AS database - name
sql - statement :: = ATTACH [ DATABASE ] database - filename AS database - name
3.DETTACH DATABASE:从当前的数据库分离一个使用ATTACH DATABASE附加的数据库。
语法:
sql - command :: = DETACH [ DATABASE ] database - name
sql - command :: = 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() |