sqlite3 命令行工具 · ZetCode 中文系列教程

# sqlite3 命令行工具 > 原文： [http://zetcode.com/db/sqlite/tool/](http://zetcode.com/db/sqlite/tool/) 在 SQLite 教程的这一部分中，我们介绍了`sqlite3`命令行工具。 ## sqlite3 工具 `sqlite3`工具是 SQLite 库的基于终端的前端，可以交互地求值查询并以多种格式显示结果。它也可以在脚本中使用。在终端屏幕上，我们看到`sqlite3`工具的以下提示： ```sql $ 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`命令行工具创建一个新的数据库文件。 ```sql $ sqlite3 test.db ``` 在这里，我们创建一个新的`test.db`数据库。如果该文件存在，则将其打开。 ## 基本的 sqlite3 元命令接下来，我们描述`sqlite3`工具的一些元命令。 ```sql sqlite> .tables Authors Cars Friends Reservations Books Customers Orders ``` `.tables`命令显示可用表。 ```sql 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`，分隔符为`|`。 ```sql 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`输出模式。 ```sql 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`命令调整列的大小。（此元命令仅与列模式下的表有关。） ```sql 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 ``` 列宽不足以正确显示所有数据。 ```sql 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 个字符。 ```sql sqlite> .shell clear ``` `.shell`命令执行系统程序。在本例中，我们使用`clear`命令清除屏幕。（Windows 上的等效值为`cls`。） ```sql 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`命令列出了各种设置。其中包括输出模式，列表模式中使用的分隔符以及标题是否打开。 ```sql sqlite> .schema Cars CREATE TABLE Cars(Id INTEGER PRIMARY KEY, Name TEXT, Price INTEGER); ``` `.schema`命令显示表的结构。它提供了 DDL SQL 来创建表。可以使用`.prompt`命令更改`sqlite3`的提示。 ```sql sqlite> .prompt "> " ". " > SELECT * FROM Cars . LIMIT 2; Id Name Price ---------- ---------- ---------- 1 Audi 52642 2 Mercedes 57127 > ``` 有两个提示。一个是主提示，另一个是继续提示。默认的主提示是`sqlite>`，默认的继续提示是`...>`。 ## 从 Shell 执行 SQL 我们可以从 Shell 执行 SQL 命令。 ```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 格式的表转储到磁盘上。这样，我们可以轻松地保存数据库表的结构和数据。我们有汽车表。 ```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`命令转储该表。 ```sql 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。 ```sql sqlite> .output cars2.sql sqlite> .dump Cars ``` 我们还可以将输出重定向到文件。 `.output`命令会将输出重定向到`cars2.sql`文件。 ```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 语句。 ```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`文件中。该文件位于主目录中。 ```sql $ tail -5 ~/.sqlite_history .tables SELECT * FROM Cars; .mode column .headers on .show ``` 使用`tail`命令，显示最后五个条目。 ## 资源文件 `sqlite3`工具具有一个名为`.sqliterc`的资源文件。它位于主目录中。如果没有这样的文件，我们可以简单地创建它。资源文件可以包含元命令或常规 SQL 语句。但是，我们应该避免在文件中使用 SQL。 ```sql $ cat .sqliterc .mode column .headers on .nullvalue NULL ``` 这是资源文件的简单示例。它具有三个元命令。使用资源文件，当我们启动`sqlite3`工具时，我们不必重新执行元命令。它们将在工具启动时自动执行。 ```sql $ 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. ``` 我们有一条消息说该工具从一开始就加载资源。 ## 命令行选项该工具有几个命令行选项。他们大多复制元命令。请注意，命令行选项将覆盖资源文件元命令。 ```sql $ 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`选项为我们提供了所有可用选项的列表，并带有简要说明。 ```sql $ 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`语句在启动后会重复/回显。输出处于行模式，并且没有标题显示。 ```sql $ sqlite3 -version -- Loading resources from /home/janbodnar/.sqliterc 3.16.2 2017-01-06 16:32:41 a65a62893ca8319e89e48b8a38cf8a59c69a8209 ``` 使用`-version`选项，我们得到 sqlite3 的版本。 ```sql $ 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 表。在 SQLite 教程的这一部分中，我们使用了`sqlite3`命令行工具。我们已经描述了各种元命令，展示了如何转储表以及从文件中读取 SQL。我们提到了 sqlite 的历史和资源文件。