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 的歷史和資源文件。