SQLite 常用命令 | 速查表(Cheat Sheet)
2020-03-02
·
via 极客兔兔
SQLite 命令
安装/连接
1
2
3
4
5
6
7
8
9
| > apt-get install sqlite3
> sqlite3 -version
3.22.0 ...
> sqlite3 gee.db
sqlite> .help
.archive ... xxx
.auth ON|OFF xxx
.backup ?DB? FILE xxx
...
|
数据库操作
1
2
3
4
5
6
7
8
9
10
| > .help
...
> .databases
main: /tmp/gee.db
> .output FILE
> .show
> .dump
> .dump users
> .backup FILE
> .quit
|
表操作
1
2
3
4
5
6
7
8
9
10
| > .table
users books
> .schema users
CREATE TABLE users(name text PRIMARY KEY, age integer);
> .import FILE TABLE
> .head ON
> select * from users
name|age
Tom|18
Jack|20
|
输出模式
1
2
3
4
5
6
7
8
9
| > .mode csv
> select * from users
name,age
Tom,18
Jack,20
> .mode insert
> select * from users
INSERT INTO "table"(name,age) VALUES('Tom',18);
INSERT INTO "table"(name,age) VALUES('Jack',20);
|
.mode 支持 csv, column, html, insert, line, list, tabs, tcl 等 8 种模式。
SQL 语句
创建表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
| CREATE TABLE tab_name (
col1 col1_type PRIMARY KEY,
col2 INTEGER AUTOINCREMENT,,
col3 col3_type NOT NULL,
.....
colN colN_type,
);
|
PRIMARY KEY 标记主键，NOT NULL标记非空。AUTOINCREMENT 自增，只能用于整型。
删除/更新表
1
2
3
4
5
6
7
8
9
10
|
DROP TABLE tab_name;
ALTER TABLE ADD COLUMNS col_name col_type;
ALTER TABLE old_tab RENAME TO new_tab
ALTER TABLE tab_name RENAME COLUMN old_col TO new_col
|
新增记录
1
2
3
4
5
6
7
8
9
|
INSERT INTO tab_name VALUES (xx, xx)
INSERT INTO tab_name (col1, col3) VALUES (xx, xx)
INSERT INTO tab_name (col1, col2, col3) VALUES
(xx, xx, xx),
...
(xx, xx, xx);
|
查询记录
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
SELECT * FROM tab_name;
SELECT DISTINCT col1 FROM tab_name;
SELECT COUNT(*) FROM tab_name
SELECT col1, col2 FROM table_name;
SELECT * FROM table_name WHERE col2 >= 18;
SELECT * FROM table_name
WHERE col2 >= 18 AND col1 LIKE %stu%;
SELECT * FROM table_name LIMIT 1;
SELECT col1, count(*) FROM tab_name
WHERE [ conditions ]
GROUP BY col1
SELECT col1, count(*) FROM tab_name
WHERE [ conditions ]
GROUP BY col1
HAVING [ conditions ]
SELECT * FROM table_name ORDER BY col2 DESC;
|
删除/更新记录
1
2
3
4
5
6
7
8
|
DELETE FROM tab_name WHERE condition;
UPDATE tab_name SET col1=value1, col2=value2
UPDATE tab_name
SET col1=value1, col2=value2
WHERE [ conditions ]
|
事务(Transaction)
1
2
3
4
5
6
7
8
9
|
BEGIN;
INSERT INTO ...
...
COMMIT;
BEGIN;
...
ROLLBACK;
|
事务具有原子性（Atomicity）、一致性（Consistency)、隔离性（Isolation）、持久性（Durability）四个标准属性，缩写为 ACID。
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。
