马克社区,马克吐温社区,马克java社区SQLite 命令行客户端 sqlite3 使用指南; sqlite 命令行客户端 sqlite3 使用指南备份恢复

SQLite 命令行客户端 sqlite3 使用指南

作者: 不剪发的Tony老师
毕业于北京航空航天大学，十多年数据库管理与开发经验，目前在一家全球性的金融公司从事数据库架构设计。CSDN学院签约讲师以及GitChat专栏作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net

大家好，我是只谈技术不剪发的 Tony 老师。本文给大家介绍一下 SQLite 命令行客户端 sqlite3 的具体使用。如果觉得文章有用，欢迎评论📝、点赞👍、推荐🎁

文章目录

        下载 sqlite3
        连接数据库
        查看帮助
        列出数据库
        查看数据库信息
        备份数据库
        还原数据库
        列出所有表
        查看表定义
        查看索引信息
        显式/修改当前设置
        设置输出格式
        设置 NULL 显示
        显式执行时间
        显示执行计划
        执行脚本文件
        保存查询结果
        导出 SQL 文件
        恢复损坏的数据库
        导入/导出 CSV
        SQLite 归档功能
        读写二进制文件
        执行系统命令
        退出客户端

下载 sqlite3

很多 Linux 操作系统默认会安装一个 SQLite 软件，或者打开 SQLite 官方下载页面，找到“Precompiled Binaries for XXX”，根据不同平台点击下载相应的“sqlite-tools-xxx.zip”文件。

下载之后直接解压 zip 文件，其中包含 3 个文件：

    sqldiff 或者 sqldiff.exe，SQLite 数据库比较工具；
    sqlite3 或者 sqlite3.exe，SQLite 命令行客户端；
    sqlite3_analyzer 或者 sqlite3_analyzer.exe，SQLite 数据表和索引的统计分析工具。

本文介绍如何 sqlite3 命令行客户端的使用，如何管理和操作数据库。
连接数据库

在操作系统命令行中直接输入 sqlite3 或者双击 sqlit3.exe 运行客户端工具：

sqlite3.exe

SQLite version 3.33.0 2020-08-14 13:23:32
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>

默认情况下，SQLite 使用内存作为数据库的存储，意味着退出之后所有的内容都会丢失。我们可以使用.open命令打开一个新的数据库文件，例如：

sqlite> .open hr.db

如果指定的数据库文件不存在，sqlite3 会创建一个新的文件。

另外，我们也可以在运行客户端工具的时候直接打开一个数据库文件。例如：

D:\Software\sqlite-tools-win32-x86-3330000\sqlite3.exe D:\Software\sqlite-tools-win32-x86-3330000\hr.db

SQLite version 3.33.0 2020-08-14 13:23:32
Enter ".help" for usage hints.
sqlite>

查看帮助

输入.help命令查看命令帮助信息：

sqlite> .help
.archive ...             Manage SQL archives
.auth ON|OFF             Show authorizer callbacks
.backup ?DB? FILE        Backup DB (default "main") to FILE
...
.vfslist                 List all available VFSes
.vfsname ?AUX?           Print the name of the VFS stack
.width NUM1 NUM2 ...     Set minimum column widths for columnar output

sqlite3 工具支持一系列以点号（.）开始的特殊命令，通常用于设置客户端的显示格式，或者执行一些预定义的查询语句。

也可以使用.help TOPIC查看某个具体命令的帮助，例如：

sqlite> .help open
.open ?OPTIONS? ?FILE?   Close existing database and reopen FILE
     Options:
        --append        Use appendvfs to append database to the end of FILE
        --new           Initialize FILE to an empty database
        --nofollow      Do not follow symbolic links
        --readonly      Open FILE readonly
        --zip           FILE is a ZIP archive

列出数据库

输入.databases命令列出当前连接中打开的所有数据库和对应的文件。例如：

sqlite> .databases
main: D:\Software\sqlite-tools-win32-x86-3330000\hr.db

其中，main 是默认打开的数据库名。如果使用ATTACH语句打开了其他数据库文件，还会显示更多的数据库。例如：

sqlite> attach database "D:\Software\sqlite-tools-win32-x86-3330000\new.db" as newdb;

sqlite> .databases
main: D:\Software\sqlite-tools-win32-x86-3330000\hr.db
newdb: D:\Software\sqlite-tools-win32-x86-3330000\new.db

查看数据库信息

输入.dbinfo命令查看指定数据库的状态信息，默认为 main 数据库：

sqlite> .dbinfo
database page size: 4096
write format:        1
read format:         1
reserved bytes:      0
file change counter: 161
database page count: 13
freelist page count: 0
schema cookie:       8
schema format:       4
default cache size: 0
autovacuum top root: 0
incremental vacuum: 0
text encoding:       1 (utf8)
user version:        0
application id:      0
software version:    3033000
number of tables:    3
number of indexes:   7
number of triggers: 0
number of views:     0
schema size:         1852
data version         1

备份数据库

输入.backup命令备份指定的数据库，默认为 main 数据库：

sqlite> .backup backup.hr.db

另外，.save命令可以将当前内存数据库保存为指定数据库文件，实现类似于备份的效果。例如：

sqlite> .save backup.hr.db

也可以使用.clone命令将当前数据库复制到指定数据库文件，例如：

sqlite> .clone hr2.db
departments... done
jobs... done
employees... done
sqlite_autoindex_jobs_1... done
sqlite_autoindex_employees_1... done
emp_department_ix... done
emp_job_ix... done
emp_manager_ix... done
emp_name_ix... done
dept_location_ix... done

以上命令在当前工作目录中生成了一个新的数据库文件 hr2.db。
还原数据库

输入.restore命令从备份文件中还原到指定的数据库，默认为 main 数据库：

sqlite> .restore newdb backup.hr.db

注意，还原操作会删除 newdb 中原有的所有对象。
列出所有表

使用.tables命令查看所有数据库中的表，例如：

sqlite> .tables
departments employees jobs newdb.t

sqlite3 工具支持表名的模糊查找，类似于 LIKE 运算符。例如：

sqlite> .tables 'emp%'
employees

查看表定义

输入.schema命令查看数据库对象的定义，例如：

sqlite> .schema --indent employees
CREATE TABLE employees(
employee_id INTEGER NOT NULL ,
first_name CHARACTER VARYING(20) ,
last_name CHARACTER VARYING(25) NOT NULL ,
email CHARACTER VARYING(25) NOT NULL ,
phone_number CHARACTER VARYING(20) ,
hire_date DATE NOT NULL ,
job_id CHARACTER VARYING(10) NOT NULL ,
salary NUMERIC(8,2) ,
commission_pct NUMERIC(2,2) ,
manager_id INTEGER ,
department_id INTEGER,
CONSTRAINT emp_emp_id_pk PRIMARY KEY(employee_id) ,
CONSTRAINT emp_salary_min CHECK(salary > 0) ,
CONSTRAINT emp_email_uk UNIQUE(email),
CONSTRAINT emp_dept_fk FOREIGN KEY(department_id) REFERENCES departments(department_id) ,
CONSTRAINT emp_job_fk FOREIGN KEY(job_id) REFERENCES jobs(job_id) ,
CONSTRAINT emp_manager_fk FOREIGN KEY(manager_id) REFERENCES employees(employee_id)
);
CREATE INDEX emp_department_ix ON employees(department_id);
CREATE INDEX emp_job_ix ON employees(job_id);
CREATE INDEX emp_manager_ix ON employees(manager_id);
CREATE INDEX emp_name_ix ON employees(last_name, first_name);

默认情况下，.schema命令显示所有对象的定义。另外，.fullschema命令可以显示额外的 sqlite_stat 统计表信息。
查看索引信息

输入.indexes命令可以列出数据库中的所有索引：

sqlite> .indexes
dept_location_ix              emp_name_ix
emp_department_ix             sqlite_autoindex_employees_1
emp_job_ix                    sqlite_autoindex_jobs_1
emp_manager_ix

想要查看指定表上的索引，可以在该命令后增加一个表名。例如：

sqlite> .indexes jobs
sqlite_autoindex_jobs_1

上面的.schema命令也可以用于查看索引的定义。

数据库的模式对象信息存储在 sqlite_schema 系统表中，因此也可以查询该表获取相关信息。例如：

sqlite> select * from newdb.sqlite_schema;
table|t|t|2|CREATE TABLE t(id int)

每个数据库都有一个 sqlite_schema，以上语句返回了 newdb 中的模式对象。
显式/修改当前设置

输入.show命令可以查看 sqlite3 中的各种设置，例如：

sqlite> .show
        echo: off
         eqp: off
     explain: auto
     headers: off
        mode: list
   nullvalue: ""
      output: stdout
colseparator: "|"
rowseparator: "\n"
       stats: off
       width:
    filename: hr.db

这些设置都提供了对应的修改命令，可以使用 .help 命令查看帮助信息，例如：

sqlite> .help echo
.help echo
.echo on|off Turn command echo on or off

.echo命令用于设置命令的回显，例如：

sqlite> .echo on
.echo on
sqlite> select 1 as id;
select 1 as id;
1

设置输出格式

sqlite3 提供了多种不同的结果输出格式，可以使用.mode命令进行设置：

sqlite> .help mode
.mode MODE ?TABLE?       Set output mode
   MODE is one of:
     ascii     Columns/rows delimited by 0x1F and 0x1E
     box       Tables using unicode box-drawing characters
     csv       Comma-separated values
     column    Output in columns. (See .width)
     html      HTML <table> code
     insert    SQL insert statements for TABLE
     json      Results in a JSON array
     line      One value per line
     list      Values delimited by "|"
     markdown Markdown table format
     quote     Escape answers as for SQL
     table     ASCII-art table
     tabs      Tab-separated values
     tcl       TCL list elements

默认的输出格式为 list，使用 | 作为字段的分隔符，使用发送给其他程序（例如 AWK）做进一步的处理。例如：

sqlite> select 1 as id, "apple" as name;
id|name
1|apple

使用.separator命令可以设置字段和数据行的分隔符，例如：

sqlite> .separator ", "
sqlite> select 1 as id, "apple" as name;
id, name
1, apple

以上输出格式类似于 csv 模式。

box 模式可以为输出结果增加一个字符绘制的外框，例如：

sqlite> .mode box
sqlite> select 1 as id, "apple" as name;
┌────┬───────┐
│ id │ name │
├────┼───────┤
│ 1 │ apple │
└────┴───────┘

json 模式可以用于输出一个 JSON 数组，例如：

sqlite> .mode json
sqlite> select 1 as id, "apple" as name;
[{"id":1,"name":"apple"}]

markdown 模式可以用于输出一个 Markdown 表格，例如：

sqlite> .mode markdown
sqlite> select 1 as id, "apple" as name;
| id | name |
|----|-------|
| 1 | apple |

insert 模式可以生成一个插入数据的语句，例如：

sqlite> .mode insert product
sqlite> select 1 as id, "apple" as name;
INSERT INTO product(id,name) VALUES(1,'apple');

其中，product 是插入语句的目标表。

对于 column、box、table 以及 markdown 模式，可以使用.width命令设置每个字段的最小宽度。例如：

sqlite> .mode markdown
sqlite> .width 10 20
sqlite> select 1 as id, "apple" as name;
| id | name |
|------------|----------------------|
| 1 | apple |

其他的输出格式可以自行进行尝试。
设置 NULL 显示

默认情况下，NULL 值显示为空，和空白字符很难区分。sqlite3 提供了.nullvalue命令，可以设置 NULL 值的显示内容。例如：

sqlite> select "" as id, null as name;
id|name
|

sqlite> .nullvalue '[NULL]'
sqlite> select "" as id, null as name;
id|name
|[NULL]

显式执行时间

输入.timer on命令可以自动显式查询语句消耗的时间，例如：

sqlite> select department_id,count(*) from employees group by 1;
department_id|count(*)
|1
10|1
20|2
30|6
40|1
50|45
60|5
70|1
80|34
90|3
100|6
110|2
Run Time: real 0.014 user 0.000000 sys 0.000000

输入.timer off命令可以关闭执行时间的显式。
显示执行计划

使用.eqp命令可以打开或者关闭执行计划的自动显示。例如：

sqlite> .eqp on
sqlite> select count(*) from employees;
QUERY PLAN
`--SCAN TABLE employees USING COVERING INDEX emp_manager_ix
count(*)
107

打开该设置相当于执行了一次 EXPLAIN QUERY PLAN query 语句。
执行脚本文件

输入.read命令读取并执行 SQL 文件中的语句。例如：

其中，get_employees.sql 文件中的内容如下：

select first_name, last_name
from employees
limit 5;

保存查询结果

输入.output命令将查询结果输出到指定文件，例如：

sqlite> .output result.txt
sqlite> select * from jobs;
...
sqlite> .output
sqlite> select 1 as id;
id
1

执行 .output 命令之后的查询结果都会写入 result.txt 文件，直接输入 .output 命令表示将结果打印到标准错误输出。

另外，.once命令也可以将查询结果输出到文件，但是它只对随后的一次 SQL 命令有效。
导出 SQL 文件

.dump命令可以将当前数据库中的所有内容导出为 SQL 语句，例如：

sqlite3.exe hr.db .dump > hr.sql

以上命令将 hr.db 中的所有对象和数据导出到 hr.sql 文件中。
恢复损坏的数据库

.recover命令和 .dump 命令类似，也可以用于将整个数据库的内容导出为 SQL 语句；但是它不是通过 SQL 接口导出数据，而是直接扫描物理数据页获取所有内容。对于损坏的数据库文件，.recover 命令可以尝试恢复尽可能多的数据。例如：

sqlite3.exe hr.db .recover > hr.sql

导入/导出 CSV

输入.import命令从 CSV 文件中导入数据到 SQLite 表中，在此之前需要将 mode 变量设置为 csv。例如：

sqlite> .mode csv
sqlite> .import product.csv product
sqlite> select * from product;
id,name
1,apple
2,banana
3,orange

其中，product 是数据库中的表名。如果该表不存在，使用 CSV 文件中的第一行内容作为字段创建表；如果该表已经存在，CSV 文件中的所有内容都被看做数据；如果第一行是标题，可以使用 --skip 1 选项跳过一行数据。

如果想要将查询结果导出到 CSV 文件，可以先将 mode 变量设置为 csv，然后使用 .once 命令导出结果：

sqlite> .headers on
sqlite> .mode csv
sqlite> .once employees.csv
sqlite> SELECT * FROM employees;
sqlite> .system employees.csv

最后的 .system 命令用于执行操作系统命令，在 Windows 中相当于双击打开 employees.csv 文件。

除此之外，也可以使用.excel命令将下一次查询结果导出到系统默认的电子表格程序（例如 Excel 或者 LibreOffice）：

sqlite> .headers on
sqlite> .excel
sqlite> select * from employees;

在 Windows 中，以上命名最终会打开一个 Excel 文件，其中包含了查询结果。该命令相当于上面的 .csv、.once 以及.system 命令组合，或者 .once -x 命令。
SQLite 归档功能

SQLite 支持类似于 zip 归档或者 tar 归档的功能，通过.archive命令或者 -A 命令行参数实现。.archive 支持以下选项之一：

例如，以下语句都可以将 3 个文件归档为 new_archive.db：

sqlite3 new_archive.db -Acv file1.txt file2.txt file3.txt

sqlite> .ar -cv -f new_archive.db file1.txt file2.txt file3.txt
file1.txt
file2.txt
file3.txt

以下语句用于列出 new_archive.db 中的文件：

sqlite> .ar --list -f new_archive.db
file1.txt
file2.txt
file3.txt

以下命令从归档中提取 file1.txt 文件到目录 dir1 中：

sqlite> .ar -x -f new_archive.db -C dir1 file1.txt

sqlite3 通过 zipfile 扩展支持 zip 文件的压缩和解压，例如：

sqlite> .ar -c -f archive.zip file1.txt file2.txt file3.txt

读写二进制文件

sqlite3 提供了两个应用程序定义的 SQL 函数，可以用于读取二进制文件到表中，或者将表中的内容写入二进制文件。

readfile(X)函数可以将整个文件内容读取为 BLOB 数据，然后存入表中。例如：

sqlite> CREATE TABLE images(name TEXT, type TEXT, img BLOB);
sqlite> INSERT INTO images(name,type,img)
...> VALUES('icon','jpeg',readfile('icon.jpg'));

writefile(X,Y)函数可以将二进制内容 Y 写入文件 X，并且返回写入的字节数。例如：

sqlite> SELECT writefile('icon.jpg',img) FROM images WHERE name='icon';
writefile('icon.jpg',img)
343618

这两个函数没有包含在 SQLite 核心代码库中，而是通过可加载的扩展 ext/misc/fileio.c 文件提供。

sqlite3 还提供了一个内置的函数edit()，可以通过调用操作系统中的软件编辑字段内容。例如：

sqlite> UPDATE docs SET body=edit(body, 'WINWORD.EXE') WHERE name='report-15';

以上命令调用 Word 处理 body 字段中的内容，编辑完成后保存退出，SQLite 自动更新相应的字段内容。
执行系统命令

输入.shell或者.system命令执行操作系统的命令并返回 sqlite3，例如：

sqlite> .shell cd
D:\Software\sqlite-tools-win32-x86-3330000

sqlite> .system dir /B
get_employees.sql
hr.db
new.db
result.txt
sqldiff.exe
sqlite3.exe
sqlite3_analyzer.exe

退出客户端

输入.exit或者.quit命令退出 sqlite3 命令行：

sqlite> .exit

.exit code命令可以返回一个退出码，通常用于编写脚本程序。

📝更多的 sqlite3 命令可以通过 .help 命令查看相应的使用说明。