Create
用法一
1
2
3
4
5
6
7
| CREATE TABLE [table] AS [select statement];
#样例
CREATE TABLE cities AS
SELECT 38 AS altitude, 122 AS longitude, “Berkeley” AS name UNION
SELECT 42, 71, "Cambridge" UNION
SELECT 45, 93, "Minneapolis";
|
用法二
1
2
3
4
5
6
| CREATE TABLE [table] [(create_definition,...)]
#用法样例
CREATE TABLE numbers (n, note);
CREATE TABLE numbers (n UNIQUE, note);
CREATE TABLE numbers (n, note DEFAULT "No comment");
|
UNIQUE: 使得n是唯一的
DEFAULT: 设置默认值
Drop
1
| DROP TABLE [table]; #删除表
|
INSERT
1
2
3
4
5
6
7
8
9
10
11
12
13
| INSERT INTO [table] VALUES [(value)]; #对一个表进行一整行的插入
#example
INSERT INTO numbers VALUES (4, "a"), (5, "b");
INSERT INTO [table(key)] VALUES [(value)]; #对一个表的某几列进行插入，剩下默认为null
#example
INSERT INTO numbers(n) VALUES (0), (1), (2), (3);
INSERT INTO [table] [select statement]; #用select来插入
#example
INSERT INTO numbers(n) SELECT n+8 from numbers;
|
UPDATE
1
2
3
4
| UPDATE [table] SET [statement] WHERE [statement]; #通过where筛选需要更新的信息
# example
UPDATE numbers SET note = "aaa" WHERE n = 0;
|
DELETE
1
2
3
4
| DELETE FROM [table] WHERE [statement]; #通过where筛选需要删除的信息
# example
DELETE FROM numbers WHERE note = "No comment";
|
SELECT
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
| SELECT [expression] AS [name], [expression] AS [name], …;
SELECT [columns] FROM [table] WHERE [condition] ORDER BY [order] LIMIT [number];
# exapmle 1
SELECT a.parent, b.child
FROM parents AS a, parents AS b
WHERE a.child = b.parent;
# example 2
CREATE TABLE grandparents AS
SELECT a.parent AS grandog, b.child AS granpup
FROM parents AS a, parents AS b
WHERE b.parent = a.child;
|
ORDER BY
1
2
3
4
5
6
| DESC #降序
ASC #升序（默认）
order by A,B #这个时候都是默认按升序排列
order by A desc,B #这个时候 A 降序，B 升序排列
order by A ,B desc #这个时候 A 升序，B 降序排列
|
Aggregation
1
2
3
4
5
| max()
min()
avg()
sum()
count()
|
GROUP BY
1
| select [columns] from [table] group by [expression] having [expression];
|
Numerical Expressions
Combine values: +, -, *, /, %, and, or
Transform values: abs, round, not, -
Compare values: <, <=, >, >=, <>, !=, =
String Expressions
Combine: || 字符串拼接
1
2
| sqlite> SELECT "hello," || " world";
hello, world
|
substr(string, start_index, length) 获取字串，start_index从1开始
instr(string, substring) 查找substring在string中的index
1
2
3
| sqlite> CREATE TABLE phrase AS SELECT "hello, world" AS s;
sqlite> SELECT substr(s, 4, 2) || substr(s, instr(s, " ")+1, 1) FROM phrase;
low
|
搬运自CSDN：https://blog.csdn.net/yueyue200830/article/details/90105733