sqlite3 命令行工具|极客教程

本章我们介绍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>