极客教程本章我们介绍sqlite3命令行工具。
sqlite3 工具
sqlite3工具是 SQLite 库的基于终端的前端，可以交互地评估查询并以多种格式显示结果。 它也可以在脚本中使用。
在终端屏幕上，我们看到sqlite3工具的以下提示：
$ sqlite3
SQLite version 3.16.2 2017-01-06 16:32:41
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
.help命令是sqlite3工具的元命令之一； 它列出了所有元命令。 .exit和.quit命令退出sqlite3会话。 我们还可以使用Ctrl + D组合键退出sqlite3。 .databases命令显示附加的数据库。 .tables命令列出了可用的表。
Ctrl + L清除屏幕，Ctrl + U清除当前行。 （使用 readline 库构建时。）
使用 sqlite3 创建数据库
完整的 SQLite 数据库存储在单个跨平台磁盘文件中。 我们使用sqlite3命令行工具创建一个新的数据库文件。
$ sqlite3 test.db
在这里，我们创建一个新的test.db数据库。 如果该文件存在，则将其打开。
基本的 sqlite3 元命令
接下来，我们描述sqlite3工具的一些元命令。
sqlite> .tables
Authors Cars Friends Reservations
Books Customers Orders
.tables命令显示可用表。
sqlite> SELECT * FROM Friends;
1|Jane|F
2|Thomas|M
3|Franklin|M
4|Elisabeth|F
5|Mary|F
6|Lucy|F
7|Jack|M
在这里，我们得到SELECT语句的输出。 默认情况下，输出模式为line，分隔符为|。
sqlite> .separator :
sqlite> SELECT * FROM Friends;
1:Jane:F
2:Thomas:M
3:Franklin:M
4:Elisabeth:F
5:Mary:F
6:Lucy:F
7:Jack:M
在这里，我们使用了一个新的冒号分隔符。
还有其他几种输出模式可用。 以下示例显示column输出模式。
sqlite> .mode column
sqlite> .headers on
sqlite> SELECT * FROM Friends;
Id Name Sex
---------- ---------- ----------
1 Jane F
2 Thomas M
3 Franklin M
4 Elisabeth F
5 Mary F
6 Lucy F
7 Jack M
本示例说明如何在 sqlite 的列模式下格式化数据。 .headers命令也已用于显示列标题。 默认情况下，标题是隐藏的。
.width命令调整列的大小。 （此 meta 命令仅与列模式下的表有关。）
sqlite> SELECT Name, Title FROM Authors NATURAL JOIN Books;
Name Title
----------- ----------
Jane Austen Emma
Leo Tolstoy War and Pe
Joseph Hell Catch XII
Charles Dic David Copp
Joseph Hell Good as Go
Leo Tolstoy Anna Karen
列宽不足以正确显示所有数据。
sqlite> .width 15 18
sqlite> SELECT Name, Title FROM Authors NATURAL JOIN Books;
Name Title
--------------- ------------------
Jane Austen Emma
Leo Tolstoy War and Peace
Joseph Heller Catch XII
Charles Dickens David Copperfield
Joseph Heller Good as Gold
Leo Tolstoy Anna Karenia
在这里，我们更改列宽。 第一列为 15 个字符，第二列为 18 个字符。
sqlite> .shell clear
.shell命令执行系统程序。 在本例中，我们使用clear命令清除屏幕。 （Windows 上的等效值为cls。）
sqlite> .show
echo: off
eqp: off
explain: auto
headers: on
mode: column
nullvalue: ""
output: stdout
colseparator: "|"
rowseparator: "\n"
stats: off
width: 15 18
filename: testdb
.show命令列出了各种设置。 其中包括输出模式，列表模式中使用的分隔符以及标题是否打开。
sqlite> .schema Cars
CREATE TABLE Cars(Id INTEGER PRIMARY KEY, Name TEXT, Price INTEGER);
.schema命令显示表的结构。 它提供了 DDL SQL 来创建表。
可以使用.prompt命令更改sqlite3的提示。
sqlite> .prompt "> " ". "
> SELECT * FROM Cars
. LIMIT 2;
Id Name Price
---------- ---------- ----------
1 Audi 52642
2 Mercedes 57127
>
有两个提示。 一个是主提示，另一个是继续提示。 默认的主提示是sqlite>，默认的继续提示是...>。
从 Shell 执行 SQL
我们可以从 Shell 执行 SQL 命令。
$ sqlite3 test.db "SELECT * FROM Cars;"
1|Audi|52642
2|Mercedes|57127
3|Skoda|9000
4|Volvo|29000
5|Bentley|350000
6|Citroen|21000
7|Hummer|41400
8|Volkswagen|21600
在这里，我们非交互地执行了SELECT语句； 从Cars表中选择了所有汽车。
转储表
可以将 SQL 格式的表转储到磁盘上。 这样，我们可以轻松地保存数据库表的结构和数据。
我们有汽车表。
sqlite> SELECT * FROM Cars;
Id Name Price
---------- ---------- ----------
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000
6 Citroen 21000
7 Hummer 41400
8 Volkswagen 21600
我们将使用.dump命令转储该表。
sqlite> .dump Cars
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE Cars(Id INTEGER PRIMARY KEY, Name TEXT, Price INTEGER);
INSERT INTO "Cars" VALUES(1,'Audi',52642);
INSERT INTO "Cars" VALUES(2,'Mercedes',57127);
INSERT INTO "Cars" VALUES(3,'Skoda',9000);
INSERT INTO "Cars" VALUES(4,'Volvo',29000);
INSERT INTO "Cars" VALUES(5,'Bentley',350000);
INSERT INTO "Cars" VALUES(6,'Citroen',21000);
INSERT INTO "Cars" VALUES(7,'Hummer',41400);
INSERT INTO "Cars" VALUES(8,'Volkswagen',21600);
COMMIT;
.dump命令向我们显示了创建Cars表所需的 SQL。
sqlite> .output cars2.sql
sqlite> .dump Cars
我们还可以将输出重定向到文件。 .output命令会将输出重定向到cars2.sql文件。
$ cat cars2.sql
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE Cars(Id INTEGER PRIMARY KEY, Name TEXT, Price INTEGER);
INSERT INTO "Cars" VALUES(1,'Audi',52642);
...
我们使用cat命令显示cars2.sql文件的内容。
读取 SQL
我们可以使用.read命令从文件名读取 SQL 语句。
sqlite> .tables
Authors Cars Friends Reservations
Books Customers Orders
sqlite> DROP TABLE Cars;
sqlite> .tables
Authors Customers Orders
Books Friends Reservations
sqlite> .read cars.sql
sqlite> .tables
Authors Cars Friends Reservations
Books Customers Orders
sqlite> SELECT * FROM Cars WHERE Id=1;
Id Name Price
---------- ---------- ----------
1 Audi 52642
在这里，我们执行了一系列命令。 我们放下表并从cars.sql中读取它。
.sqlite_history文件
命令和语句存储在.sqlite_history文件中。 该文件位于主目录中。
$ tail -5 ~/.sqlite_history
.tables
SELECT * FROM Cars;
.mode column
.headers on
.show
使用tail命令，显示最后五个条目。
资源文件
sqlite3工具具有一个名为.sqliterc的资源文件。 它位于主目录中。 如果没有这样的文件，我们可以简单地创建它。 资源文件可以包含元命令或常规 SQL 语句。 但是，我们应该避免在文件中使用 SQL。
$ cat .sqliterc
.mode column
.headers on
.nullvalue NULL
这是资源文件的简单示例。 它具有三个 meta 命令。 使用资源文件，当我们启动sqlite3工具时，我们不必重新执行 meta 命令。 它们将在工具启动时自动执行。
$ sqlite3 test.db
-- Loading resources from /home/janbodnar/.sqliterc
SQLite version 3.16.2 2017-01-06 16:32:41
Enter ".help" for usage hints.
我们有一条消息说该工具从一开始就加载资源。
命令行选项
该工具有几个命令行选项。 他们大多复制 meta 命令。 请注意，命令行选项将覆盖资源文件元命令。
$ sqlite3 --help
-- Loading resources from /home/janbodnar/.sqliterc
Usage: sqlite3 [OPTIONS] FILENAME [SQL]
FILENAME is the name of an SQLite database. A new database is created
if the file does not previously exist.
OPTIONS include:
-ascii set output mode to 'ascii'
-bail stop after hitting an error
-batch force batch I/O
-column set output mode to 'column'
-cmd COMMAND run "COMMAND" before reading stdin
-csv set output mode to 'csv'
-echo print commands before execution
-init FILENAME read/process named file
-[no]header turn headers on or off
-help show this message
-html set output mode to HTML
-interactive force interactive I/O
-line set output mode to 'line'
-list set output mode to 'list'
-lookaside SIZE N use N entries of SZ bytes for lookaside memory
-mmap N default mmap size set to N
-newline SEP set output row separator. Default: '\n'
-nullvalue TEXT set text string for NULL values. Default ''
-pagecache SIZE N use N slots of SZ bytes each for page cache memory
-scratch SIZE N use N slots of SZ bytes each for scratch memory
-separator SEP set output column separator. Default: '|'
-stats print memory stats before each finalize
-version show SQLite version
-vfs NAME use NAME as the default VFS
--help选项为我们提供了所有可用选项的列表，并带有简要说明。
$ sqlite3 -echo -line -noheader test.db
-- Loading resources from /home/janbodnar/.sqliterc
SQLite version 3.16.2 2017-01-06 16:32:41
Enter ".help" for usage hints.
sqlite> SELECT * FROM Cars LIMIT 2;
SELECT * FROM Cars LIMIT 2;
Id = 1
Name = Audi
Price = 52642
Id = 2
Name = Mercedes
Price = 57127
我们使用-echo，-line和-noheader选项启动sqlite3工具。 SELECT语句在启动后会重复/回显。 输出处于行模式，并且没有标题显示。
$ sqlite3 -version
-- Loading resources from /home/janbodnar/.sqliterc
3.16.2 2017-01-06 16:32:41 a65a62893ca8319e89e48b8a38cf8a59c69a8209
使用-version选项，我们得到 sqlite3 的版本。
$ sqlite3 -html test.db
-- Loading resources from /home/janbodnar/.sqliterc
SQLite version 3.16.2 2017-01-06 16:32:41
Enter ".help" for usage hints.
sqlite> SELECT * FROM Cars LIMIT 2;
<TR><TH>Id</TH>
<TH>Name</TH>
<TH>Price</TH>
</TR>
<TR><TD>1</TD>
<TD>Audi</TD>
<TD>52642</TD>
</TR>
<TR><TD>2</TD>
<TD>Mercedes</TD>
<TD>57127</TD>
</TR>
使用-html选项，我们可以将结果输出为简单的 HTML 表。