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。