SQLite 命令列客戶端 sqlite3 使用指南

下載 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>1234567

預設情況下，SQLite 使用記憶體作為資料庫的儲存，意味著退出之後所有的內容都會丟失。我們可以使用 .open命令開啟一個新的資料庫檔案，例如：

sqlite> .open hr.db1

如果指定的資料庫檔案不存在，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>12345

檢視幫助

輸入 .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 output12345678

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 archive12345678

列出資料庫

輸入.databases命令列出當前連線中開啟的所有資料庫和對應的檔案。例如：

sqlite> .databasesmain: D:\Software\sqlite-tools-win32-x86-3330000\hr.db12

其中，main 是預設開啟的資料庫名。如果使用 ATTACH語句開啟了其他資料庫檔案，還會顯示更多的資料庫。例如：

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

檢視資料庫資訊

輸入 .dbinfo命令檢視指定資料庫的狀態資訊，預設為 main 資料庫：

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

備份資料庫

輸入 .backup命令備份指定的資料庫，預設為 main 資料庫：

sqlite> .backup backup.hr.db1

另外， .save命令可以將當前記憶體資料庫儲存為指定資料庫檔案，實現類似於備份的效果。例如：

sqlite> .save backup.hr.db1

也可以使用 .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... done1234567891011

以上命令在當前工作目錄中生成了一個新的資料庫檔案 hr2.db。

還原資料庫

輸入 .restore命令從備份檔案中還原到指定的資料庫，預設為 main 資料庫：

sqlite> .restore newdb backup.hr.db1

注意，還原操作會刪除 newdb 中原有的所有物件。

列出所有表

使用 .tables命令檢視所有資料庫中的表，例如：

sqlite> .tablesdepartments  employees    jobs         newdb.t12

sqlite3 工具支援表名的模糊查詢，類似於 LIKE 運算子。例如：

sqlite> .tables 'emp%'employees12

檢視錶定義

輸入 .schema命令檢視資料庫物件的定義，例如：

sqlite> .schema --indent employeesCREATE 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);123456789101112131415161718192021222324

預設情況下， .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_ix12345

想要檢視指定表上的索引，可以在該命令後增加一個表名。例如：

sqlite> .indexes jobs
sqlite_autoindex_jobs_112

上面的 .schema命令也可以用於檢視索引的定義。

資料庫的模式物件資訊儲存在  sqlite_schema 系統表中，因此也可以查詢該表獲取相關資訊。例如：

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

每個資料庫都有一個 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.db12345678910111213

這些設定都提供了對應的修改命令，可以使用 .help 命令檢視幫助資訊，例如：

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

.echo命令用於設定命令的回顯，例如：

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

設定輸出格式

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 elements1234567891011121314151617

預設的輸出格式為 list，使用 | 作為欄位的分隔符，使用傳送給其他程式（例如 AWK）做進一步的處理。例如：

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

使用 .separator命令可以設定欄位和資料行的分隔符，例如：

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

以上輸出格式類似於 csv 模式。

box 模式可以為輸出結果增加一個字元繪製的外框，例如：

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

json 模式可以用於輸出一個 JSON 陣列，例如：

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

markdown 模式可以用於輸出一個 Markdown 表格，例如：

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

insert 模式可以生成一個插入資料的語句，例如：

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

其中，product 是插入語句的目標表。

對於 column、box、table 以及 markdown 模式，可以使用 .width命令設定每個欄位的最小寬度。例如：

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

其他的輸出格式可以自行進行嘗試。

設定 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]12345678

顯式執行時間

輸入 .timer on命令可以自動顯式查詢語句消耗的時間，例如：

sqlite> select department_id,count(*) from employees group by 1;department_id|count(*)|110|120|230|640|150|4560|570|180|3490|3100|6110|2Run Time: real 0.014 user 0.000000 sys 0.000000123456789101112131415

輸入 .timer off命令可以關閉執行時間的顯式。

顯示執行計劃

使用 .eqp命令可以開啟或者關閉執行計劃的自動顯示。例如：

sqlite> .eqp onsqlite> select count(*) from employees;QUERY PLAN`--SCAN TABLE employees USING COVERING INDEX emp_manager_ixcount(*)107123456

開啟該設定相當於執行了一次 EXPLAIN QUERY PLAN query 語句。

執行指令碼檔案

輸入 .read命令讀取並執行 SQL 檔案中的語句。例如：

sqlite> .read get_employees.sqlfirst_name|last_name
Ellen|Abel
Sundar|Ande
Mozhe|Atkinson
David|Austin
Hermann|Baer1234567

其中，get_employees.sql 檔案中的內容如下：

select first_name, last_namefrom employeeslimit 5;123

儲存查詢結果

輸入 .output命令將查詢結果輸出到指定檔案，例如：

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

執行 .output 命令之後的查詢結果都會寫入 result.txt 檔案，直接輸入 .output 命令表示將結果列印到標準錯誤輸出。

另外， .once命令也可以將查詢結果輸出到檔案，但是它只對隨後的一次 SQL 命令有效。

匯出 SQL 檔案

.dump命令可以將當前資料庫中的所有內容匯出為 SQL 語句，例如：

sqlite3.exe hr.db .dump > hr.sql1

以上命令將 hr.db 中的所有物件和資料匯出到 hr.sql 檔案中。

恢復損壞的資料庫

.recover命令和 .dump 命令類似，也可以用於將整個資料庫的內容匯出為 SQL 語句；但是它不是透過 SQL 介面匯出資料，而是直接掃描物理資料頁獲取所有內容。對於損壞的資料庫檔案，.recover 命令可以嘗試恢復儘可能多的資料。例如：

sqlite3.exe hr.db .recover > hr.sql1

匯入/匯出 CSV

輸入 .import命令從 CSV 檔案中匯入資料到 SQLite 表中，在此之前需要將 mode 變數設定為 csv。例如：

sqlite> .mode csv
sqlite> .import product.csv product
sqlite> select * from product;id,name1,apple2,banana3,orange1234567

其中，product 是資料庫中的表名。如果該表不存在，使用 CSV 檔案中的第一行內容作為欄位建立表；如果該表已經存在，CSV 檔案中的所有內容都被看做資料；如果第一行是標題，可以使用 --skip 1 選項跳過一行資料。

如果想要將查詢結果匯出到 CSV 檔案，可以先將 mode 變數設定為 csv，然後使用 .once 命令匯出結果：

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

最後的 .system 命令用於執行作業系統命令，在 Windows 中相當於雙擊開啟 employees.csv 檔案。

除此之外，也可以使用 .excel命令將下一次查詢結果匯出到系統預設的電子表格程式（例如 Excel 或者 LibreOffice）：

sqlite> .headers onsqlite> .excel
sqlite> select * from employees;123

在 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.txt123456

以下語句用於列出 new_archive.db 中的檔案：

sqlite> .ar --list -f new_archive.dbfile1.txt
file2.txt
file3.txt1234

以下命令從歸檔中提取 file1.txt 檔案到目錄 dir1 中：

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

sqlite3 透過 zipfile 擴充套件支援 zip 檔案的壓縮和解壓，例如：

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

讀寫二進位制檔案

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'));123

writefile(X,Y)函式可以將二進位制內容 Y 寫入檔案 X，並且返回寫入的位元組數。例如：

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

這兩個函式沒有包含在 SQLite 核心程式碼庫中，而是透過可載入的擴充套件 ext/misc/fileio.c 檔案提供。

sqlite3 還提供了一個內建的函式 edit()，可以透過呼叫作業系統中的軟體編輯欄位內容。例如：

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

以上命令呼叫 Word 處理 body 欄位中的內容，編輯完成後儲存退出，SQLite 自動更新相應的欄位內容。

執行系統命令 xinxiang/

輸入 .shell或者 .system命令執行作業系統的命令並返回 sqlite3，例如：

sqlite> .shell cd
D:\Software\sqlite-tools-win32-x86-3330000sqlite> .system dir /B
get_employees.sqlhr.db
new.db
result.txt
sqldiff.exe
sqlite3.exe
sqlite3_analyzer.exe1234567891011

退出客戶端 liaoning/

輸入 .exit或者 .quit命令退出 sqlite3 命令列：

sqlite> .exit1

.exit code命令可以返回一個退出碼，通常用於編寫指令碼程式。