【SQLite数据库常规使用命令】

之前在做围绕数据库相关的一些小工具的时候，想找一款数据库作为小工具的资料库。需求是：不用复杂的安装，支持简单SQL，空间占用小，操作简单等等。
结合着之前接触到的一些研发同事做的产品的使用经验，我想到了这款轻量级的数据库----SQLite。

SQLite遵守‌ACID，它的设计目标是嵌入式的，已经被广泛用于各种嵌入式设备和应用程序中。大多数的操作系统已经预装了SQLite，如果没有，用包管理器装一下sqlite3就可以了。

如下是一部分使用SQLite的常规命令，供参考

一、登陆/创建数据库

直接执行 sqlite3 filename 打开或创建一个 SQLite 数据库。如果文件不存在，SQLite 会自动创建它。

sqlite3 db1.db

也可以首先创建一个空白文件，然后使用 sqlite3 命令打开它。

touch db02.db
sqlite3 db02.db

二、查看当前访问的数据库以及切换数据库

.database

.open database_name

三、查看帮助命令

直接.help查看所有可用的命令

.help

.help跟命令，可以只查看和这个操作的解释

.help .table

四、SQLite 增删改查

sqlite> create table t1(name text,age int);
sqlite> insert into t1 values('xmaster',20);
sqlite> insert into t1 values('xmaster2',10);
sqlite> select * from t1;
xmaster|20
xmaster2|10
//建索引
sqlite> create index user_name on t1(name);

五、显示格式

觉得上边查询的结果显示的不好看，可以用.mode改变输出格式。可用的输出格式为：ascii、box、csv、column、html、insert、json、line、list、markdown、quote、table。

sqlite> .mode insert
sqlite> select * from t1;
INSERT INTO "table"(name,age) VALUES('xmaster',20);
INSERT INTO "table"(name,age) VALUES('xmaster2',10);
sqlite> .mode table
sqlite> select * from t1;
+----------+-----+
|   name   | age |
+----------+-----+
| xmaster  | 20  |
| xmaster2 | 10  |
+----------+-----+
sqlite> .mode html
sqlite> select * from t1;
<TR><TD>xmaster</TD>
<TD>20</TD>
</TR>
<TR><TD>xmaster2</TD>
<TD>10</TD>
</TR>
sqlite> .mode json
sqlite> select * from t1;
[{"name":"xmaster","age":20},
{"name":"xmaster2","age":10}]
sqlite> .mode column
sqlite> select * from t1;
name      age
--------  ---
xmaster   20
xmaster2  10
sqlite> .mode box
sqlite> select * from t1;
┌──────────┬─────┐
│   name   │ age │
├──────────┼─────┤
│ xmaster  │ 20  │
│ xmaster2 │ 10  │
└──────────┴─────┘
sqlite>
sqlite> .mode quote
sqlite> select * from t1;
'name','age'
'xmaster',20
'xmaster2',10

六、查看对象

比较类似于PostgreSQL的元命令

.schema 查看 schema 详情

sqlite> .schema
CREATE TABLE t1(name text,age int);
CREATE INDEX user_name on t1(name);
CREATE TABLE t2(id int);

查看表

sqlite> .table
t1

对应的具体SQL为

SELECT name FROM sqlite_schema WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%';

查看索引

sqlite> .index
user_name

对应的具体SQL为

SELECT name FROM sqlite_schema WHERE type IN ('index') AND name NOT LIKE 'sqlite_%';

支持模糊查询（模糊查询是用%）

sqlite> create table t2(id int);
sqlite> .table t%
t1  t2
sqlite> .table %2
t2

也可以.table 跟表名，看是否有这张表

sqlite> .table t1
t1
sqlite> .table t3
sqlite>

sqlite_mastersqlite_schema_153">七、sqlite_master/sqlite_schema表

sqlite_master 是 SQLite 中的一个特殊表，其中包含了数据库的 schema 信息。你可以查询这个表以获取表的创建语句和索引信息。

sqlite> select * from sqlite_schema;
+-------+-----------+----------+----------+------------------------------------+
| type  |   name    | tbl_name | rootpage |                sql                 |
+-------+-----------+----------+----------+------------------------------------+
| table | t1        | t1       | 2        | CREATE TABLE t1(name text,age int) |
| index | user_name | t1       | 3        | CREATE INDEX user_name on t1(name) |
| table | t2        | t2       | 4        | CREATE TABLE t2(id int)            |
+-------+-----------+----------+----------+------------------------------------+
sqlite> select * from sqlite_master;
+-------+-----------+----------+----------+------------------------------------+
| type  |   name    | tbl_name | rootpage |                sql                 |
+-------+-----------+----------+----------+------------------------------------+
| table | t1        | t1       | 2        | CREATE TABLE t1(name text,age int) |
| index | user_name | t1       | 3        | CREATE INDEX user_name on t1(name) |
| table | t2        | t2       | 4        | CREATE TABLE t2(id int)            |
+-------+-----------+----------+----------+------------------------------------+

八、结果输出到文件(.output filename)

使用 .output filename 命令将查询结果写入指定文件。（类似于PostgreSQL的\o）
.exit是退出数据库，类似于PostgreSQL的\q

sqlite> .output ysla.log
sqlite> select * from t1;
sqlite> .exit
root@ubuntu-linux-22-04-desktop:~# cat ysla.log
+----------+-----+
|   name   | age |
+----------+-----+
| xmaster  | 20  |
| xmaster2 | 10  |
+----------+-----+

九、读取运行 SQL 脚本（.read filename.sql）

root@ubuntu-linux-22-04-desktop:~# cat a.sql
select * from t1;
root@ubuntu-linux-22-04-desktop:~# sqlite3 db02.db
SQLite version 3.37.2 2022-01-06 13:25:41
Enter ".help" for usage hints.
sqlite> .mode table
sqlite> .read a.sql
+----------+-----+
|   name   | age |
+----------+-----+
| xmaster  | 20  |
| xmaster2 | 10  |
+----------+-----+

十、SQLite 备份与恢复

（1）备份

使用.output backup.sql结合.dump

sqlite> .database
main: /root/db02.db r/w
sqlite> .output backup.sql
sqlite> .dump
sqlite> .exit
root@ubuntu-linux-22-04-desktop:~# cat backup.sql
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE t1(name text,age int);
INSERT INTO t1 VALUES('xmaster',20);
INSERT INTO t1 VALUES('xmaster2',10);
CREATE TABLE t2(id int);
CREATE INDEX user_name on t1(name);
COMMIT;

（2）恢复

主要利用read命令应用

root@ubuntu-linux-22-04-desktop:~# sqlite3 db3.db
SQLite version 3.37.2 2022-01-06 13:25:41
Enter ".help" for usage hints.
sqlite> .read  backup.sql
sqlite> .table
t1  t2
sqlite> select * from t1;
xmaster|20
xmaster2|10

(3)备份单个对象

sqlite> .output backup.sql
sqlite> .dump object_name
sqlite> .exit

（4）获取对象的结构/创建语句

sqlite> select * from t1;
xmaster|20
xmaster2|10
sqlite> .dump t1
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE t1(name text,age int);
INSERT INTO t1 VALUES('xmaster',20);
INSERT INTO t1 VALUES('xmaster2',10);
COMMIT;
sqlite> .index
user_name
sqlite> .dump user_name
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE INDEX user_name on t1(name);
COMMIT;

十一、SQL 函数

支持的SQL函数可以参考这个链接 https://www.sqlite.org/lang_corefunc.html

sqlitesql_273">十二、如何不sqlite进入数据库，在外边执行sql

以 sqlite3 dbname sql的格式执行即可

root@ubuntu-linux-22-04-desktop:~# sqlite3 db02.db "select * from t1;"
xmaster|20
xmaster2|10

可以跟上显示的格式

root@ubuntu-linux-22-04-desktop:~# sqlite3 db02.db "select * from t1;" -table
+----------+-----+
|   name   | age |
+----------+-----+
| xmaster  | 20  |
| xmaster2 | 10  |
+----------+-----+
root@ubuntu-linux-22-04-desktop:~# sqlite3 db02.db "select * from t1;" -json
[{"name":"xmaster","age":20},
{"name":"xmaster2","age":10}]
root@ubuntu-linux-22-04-desktop:~# sqlite3 db02.db "select * from t1;" -box
┌──────────┬─────┐
│   name   │ age │
├──────────┼─────┤
│ xmaster  │ 20  │
│ xmaster2 │ 10  │
└──────────┴─────┘

sqlitesql_301">十三、如何不sqlite进入数据库，在外边执行sql脚本

root@ubuntu-linux-22-04-desktop:~# sqlite3 db02.db ".read a.sql" -table
+----------+-----+
|   name   | age |
+----------+-----+
| xmaster  | 20  |
| xmaster2 | 10  |
+----------+-----+