在线午夜精品自拍小视频_无码av无码专区线_亚洲无码精品人妻_人人澡欧美一区

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

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net

大家好，我是只談技術(shù)不剪發(fā)的 Tony 老師。本文給大家介紹一下 SQLite 命令行客戶端 sqlite3 的具體使用。如果覺得文章有用，歡迎評論??、點(diǎn)贊??、推薦??

文章目錄

        下載 sqlite3
        連接數(shù)據(jù)庫
        查看幫助
        列出數(shù)據(jù)庫
        查看數(shù)據(jù)庫信息
        備份數(shù)據(jù)庫
        還原數(shù)據(jù)庫
        列出所有表
        查看表定義
        查看索引信息
        顯式/修改當(dāng)前設(shè)置
        設(shè)置輸出格式
        設(shè)置 NULL 顯示
        顯式執(zhí)行時(shí)間
        顯示執(zhí)行計(jì)劃
        執(zhí)行腳本文件
        保存查詢結(jié)果
        導(dǎo)出 SQL 文件
        恢復(fù)損壞的數(shù)據(jù)庫
        導(dǎo)入/導(dǎo)出 CSV
        SQLite 歸檔功能
        讀寫二進(jìn)制文件
        執(zhí)行系統(tǒng)命令
        退出客戶端

下載 sqlite3

很多 Linux 操作系統(tǒng)默認(rèn)會(huì)安裝一個(gè) SQLite 軟件，或者打開 SQLite 官方下載頁面，找到“Precompiled Binaries for XXX”，根據(jù)不同平臺(tái)點(diǎn)擊下載相應(yīng)的“sqlite-tools-xxx.zip”文件。
 

下載之后直接解壓 zip 文件，其中包含 3 個(gè)文件：

    sqldiff 或者 sqldiff.exe，SQLite 數(shù)據(jù)庫比較工具；
    sqlite3 或者 sqlite3.exe，SQLite 命令行客戶端；
    sqlite3_analyzer 或者 sqlite3_analyzer.exe，SQLite 數(shù)據(jù)表和索引的統(tǒng)計(jì)分析工具。

本文介紹如何 sqlite3 命令行客戶端的使用，如何管理和操作數(shù)據(jù)庫。
連接數(shù)據(jù)庫

在操作系統(tǒng)命令行中直接輸入 sqlite3 或者雙擊 sqlit3.exe 運(yùn)行客戶端工具：

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>

 

默認(rèn)情況下，SQLite 使用內(nèi)存作為數(shù)據(jù)庫的存儲(chǔ)，意味著退出之后所有的內(nèi)容都會(huì)丟失。我們可以使用.open命令打開一個(gè)新的數(shù)據(jù)庫文件，例如：

sqlite> .open hr.db

 

如果指定的數(shù)據(jù)庫文件不存在，sqlite3 會(huì)創(chuàng)建一個(gè)新的文件。

另外，我們也可以在運(yùn)行客戶端工具的時(shí)候直接打開一個(gè)數(shù)據(jù)庫文件。例如：

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>

 

查看幫助

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

 

sqlite3 工具支持一系列以點(diǎn)號（.）開始的特殊命令，通常用于設(shè)置客戶端的顯示格式，或者執(zhí)行一些預(yù)定義的查詢語句。

也可以使用.help TOPIC查看某個(gè)具體命令的幫助，例如：

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 archive

 

列出數(shù)據(jù)庫

輸入.databases命令列出當(dāng)前連接中打開的所有數(shù)據(jù)庫和對應(yīng)的文件。例如：

sqlite> .databases
main: D:\Software\sqlite-tools-win32-x86-3330000\hr.db

 

其中，main 是默認(rèn)打開的數(shù)據(jù)庫名。如果使用ATTACH語句打開了其他數(shù)據(jù)庫文件，還會(huì)顯示更多的數(shù)據(jù)庫。例如：

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

sqlite> .databases
main: D:\Software\sqlite-tools-win32-x86-3330000\hr.db
newdb: D:\Software\sqlite-tools-win32-x86-3330000\new.db

 

查看數(shù)據(jù)庫信息

輸入.dbinfo命令查看指定數(shù)據(jù)庫的狀態(tài)信息，默認(rèn)為 main 數(shù)據(jù)庫：

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

 

備份數(shù)據(jù)庫

輸入.backup命令備份指定的數(shù)據(jù)庫，默認(rèn)為 main 數(shù)據(jù)庫：

sqlite> .backup backup.hr.db

 

另外，.save命令可以將當(dāng)前內(nèi)存數(shù)據(jù)庫保存為指定數(shù)據(jù)庫文件，實(shí)現(xiàn)類似于備份的效果。例如：

sqlite> .save backup.hr.db

 

也可以使用.clone命令將當(dāng)前數(shù)據(jù)庫復(fù)制到指定數(shù)據(jù)庫文件，例如：

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... done

 

以上命令在當(dāng)前工作目錄中生成了一個(gè)新的數(shù)據(jù)庫文件 hr2.db。
還原數(shù)據(jù)庫

輸入.restore命令從備份文件中還原到指定的數(shù)據(jù)庫，默認(rèn)為 main 數(shù)據(jù)庫：

sqlite> .restore newdb backup.hr.db

 

注意，還原操作會(huì)刪除 newdb 中原有的所有對象。
列出所有表

使用.tables命令查看所有數(shù)據(jù)庫中的表，例如：

sqlite> .tables
departments  employees    jobs         newdb.t

 

sqlite3 工具支持表名的模糊查找，類似于 LIKE 運(yùn)算符。例如：

sqlite> .tables 'emp%'
employees

 

查看表定義

輸入.schema命令查看數(shù)據(jù)庫對象的定義，例如：

sqlite> .schema --indent employees
CREATE 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);

 

默認(rèn)情況下，.schema命令顯示所有對象的定義。另外，.fullschema命令可以顯示額外的 sqlite_stat 統(tǒng)計(jì)表信息。
查看索引信息

輸入.indexes命令可以列出數(shù)據(jù)庫中的所有索引：

sqlite> .indexes
dept_location_ix              emp_name_ix
emp_department_ix             sqlite_autoindex_employees_1
emp_job_ix                    sqlite_autoindex_jobs_1
emp_manager_ix

 

想要查看指定表上的索引，可以在該命令后增加一個(gè)表名。例如：

sqlite> .indexes jobs
sqlite_autoindex_jobs_1

 

上面的.schema命令也可以用于查看索引的定義。

數(shù)據(jù)庫的模式對象信息存儲(chǔ)在 sqlite_schema 系統(tǒng)表中，因此也可以查詢該表獲取相關(guān)信息。例如：

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

 

每個(gè)數(shù)據(jù)庫都有一個(gè) sqlite_schema，以上語句返回了 newdb 中的模式對象。
顯式/修改當(dāng)前設(shè)置

輸入.show命令可以查看 sqlite3 中的各種設(shè)置，例如：

sqlite> .show
        echo: off
         eqp: off
     explain: auto
     headers: off
        mode: list
   nullvalue: ""
      output: stdout
colseparator: "|"
rowseparator: "\n"
       stats: off
       width:
    filename: hr.db

 

這些設(shè)置都提供了對應(yīng)的修改命令，可以使用 .help 命令查看幫助信息，例如：

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

 

.echo命令用于設(shè)置命令的回顯，例如：

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

 

設(shè)置輸出格式

sqlite3 提供了多種不同的結(jié)果輸出格式，可以使用.mode命令進(jìn)行設(shè)置：

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 elements

 

默認(rèn)的輸出格式為 list，使用 | 作為字段的分隔符，使用發(fā)送給其他程序（例如 AWK）做進(jìn)一步的處理。例如：

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

 

使用.separator命令可以設(shè)置字段和數(shù)據(jù)行的分隔符，例如：

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

 

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

box 模式可以為輸出結(jié)果增加一個(gè)字符繪制的外框，例如：

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

 

json 模式可以用于輸出一個(gè) JSON 數(shù)組，例如：

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

 

markdown 模式可以用于輸出一個(gè) Markdown 表格，例如：

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

 

insert 模式可以生成一個(gè)插入數(shù)據(jù)的語句，例如：

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

 

其中，product 是插入語句的目標(biāo)表。

對于 column、box、table 以及 markdown 模式，可以使用.width命令設(shè)置每個(gè)字段的最小寬度。例如：

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

 

其他的輸出格式可以自行進(jìn)行嘗試。
設(shè)置 NULL 顯示

默認(rèn)情況下，NULL 值顯示為空，和空白字符很難區(qū)分。sqlite3 提供了.nullvalue命令，可以設(shè)置 NULL 值的顯示內(nèi)容。例如：

sqlite> select "" as id, null as name;
id|name
|

sqlite> .nullvalue '[NULL]'
sqlite> select "" as id, null as name;
id|name
|[NULL]

 

顯式執(zhí)行時(shí)間

輸入.timer on命令可以自動(dòng)顯式查詢語句消耗的時(shí)間，例如：

sqlite> select department_id,count(*) from employees group by 1;
department_id|count(*)
|1
10|1
20|2
30|6
40|1
50|45
60|5
70|1
80|34
90|3
100|6
110|2
Run Time: real 0.014 user 0.000000 sys 0.000000

 

輸入.timer off命令可以關(guān)閉執(zhí)行時(shí)間的顯式。
顯示執(zhí)行計(jì)劃

使用.eqp命令可以打開或者關(guān)閉執(zhí)行計(jì)劃的自動(dòng)顯示。例如：

sqlite> .eqp on
sqlite> select count(*) from employees;
QUERY PLAN
`--SCAN TABLE employees USING COVERING INDEX emp_manager_ix
count(*)
107

 

打開該設(shè)置相當(dāng)于執(zhí)行了一次 EXPLAIN QUERY PLAN query 語句。
執(zhí)行腳本文件

輸入.read命令讀取并執(zhí)行 SQL 文件中的語句。例如：

sqlite> .read get_employees.sql
first_name|last_name
Ellen|Abel
Sundar|Ande
Mozhe|Atkinson
David|Austin
Hermann|Baer

 

其中，get_employees.sql 文件中的內(nèi)容如下：

select first_name, last_name
from employees
limit 5;

 

保存查詢結(jié)果

輸入.output命令將查詢結(jié)果輸出到指定文件，例如：

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

 

執(zhí)行 .output 命令之后的查詢結(jié)果都會(huì)寫入 result.txt 文件，直接輸入 .output 命令表示將結(jié)果打印到標(biāo)準(zhǔn)錯(cuò)誤輸出。

另外，.once命令也可以將查詢結(jié)果輸出到文件，但是它只對隨后的一次 SQL 命令有效。
導(dǎo)出 SQL 文件

.dump命令可以將當(dāng)前數(shù)據(jù)庫中的所有內(nèi)容導(dǎo)出為 SQL 語句，例如：

sqlite3.exe hr.db .dump > hr.sql

 

以上命令將 hr.db 中的所有對象和數(shù)據(jù)導(dǎo)出到 hr.sql 文件中。
恢復(fù)損壞的數(shù)據(jù)庫

.recover命令和 .dump 命令類似，也可以用于將整個(gè)數(shù)據(jù)庫的內(nèi)容導(dǎo)出為 SQL 語句；但是它不是通過 SQL 接口導(dǎo)出數(shù)據(jù)，而是直接掃描物理數(shù)據(jù)頁獲取所有內(nèi)容。對于損壞的數(shù)據(jù)庫文件，.recover 命令可以嘗試恢復(fù)盡可能多的數(shù)據(jù)。例如：

sqlite3.exe hr.db .recover > hr.sql

 

導(dǎo)入/導(dǎo)出 CSV

輸入.import命令從 CSV 文件中導(dǎo)入數(shù)據(jù)到 SQLite 表中，在此之前需要將 mode 變量設(shè)置為 csv。例如：

sqlite> .mode csv
sqlite> .import product.csv product
sqlite> select * from product;
id,name
1,apple
2,banana
3,orange

 

其中，product 是數(shù)據(jù)庫中的表名。如果該表不存在，使用 CSV 文件中的第一行內(nèi)容作為字段創(chuàng)建表；如果該表已經(jīng)存在，CSV 文件中的所有內(nèi)容都被看做數(shù)據(jù)；如果第一行是標(biāo)題，可以使用 --skip 1 選項(xiàng)跳過一行數(shù)據(jù)。

如果想要將查詢結(jié)果導(dǎo)出到 CSV 文件，可以先將 mode 變量設(shè)置為 csv，然后使用 .once 命令導(dǎo)出結(jié)果：

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

 

最后的 .system 命令用于執(zhí)行操作系統(tǒng)命令，在 Windows 中相當(dāng)于雙擊打開 employees.csv 文件。

除此之外，也可以使用.excel命令將下一次查詢結(jié)果導(dǎo)出到系統(tǒng)默認(rèn)的電子表格程序（例如 Excel 或者 LibreOffice）：

sqlite> .headers on
sqlite> .excel
sqlite> select * from employees;

 

在 Windows 中，以上命名最終會(huì)打開一個(gè) Excel 文件，其中包含了查詢結(jié)果。該命令相當(dāng)于上面的 .csv、.once 以及.system 命令組合，或者 .once -x 命令。
SQLite 歸檔功能

SQLite 支持類似于 zip 歸檔或者 tar 歸檔的功能，通過.archive命令或者 -A 命令行參數(shù)實(shí)現(xiàn)。.archive 支持以下選項(xiàng)之一：
 

例如，以下語句都可以將 3 個(gè)文件歸檔為 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.txt

 

以下語句用于列出 new_archive.db 中的文件：

sqlite> .ar --list -f new_archive.db
file1.txt
file2.txt
file3.txt

 

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

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

 

sqlite3 通過 zipfile 擴(kuò)展支持 zip 文件的壓縮和解壓，例如：

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

 

讀寫二進(jìn)制文件

sqlite3 提供了兩個(gè)應(yīng)用程序定義的 SQL 函數(shù)，可以用于讀取二進(jìn)制文件到表中，或者將表中的內(nèi)容寫入二進(jìn)制文件。

readfile(X)函數(shù)可以將整個(gè)文件內(nèi)容讀取為 BLOB 數(shù)據(jù)，然后存入表中。例如：

sqlite> CREATE TABLE images(name TEXT, type TEXT, img BLOB);
sqlite> INSERT INTO images(name,type,img)
   ...>   VALUES('icon','jpeg',readfile('icon.jpg'));

 

writefile(X,Y)函數(shù)可以將二進(jìn)制內(nèi)容 Y 寫入文件 X，并且返回寫入的字節(jié)數(shù)。例如：

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

 

這兩個(gè)函數(shù)沒有包含在 SQLite 核心代碼庫中，而是通過可加載的擴(kuò)展 ext/misc/fileio.c 文件提供。

sqlite3 還提供了一個(gè)內(nèi)置的函數(shù)edit()，可以通過調(diào)用操作系統(tǒng)中的軟件編輯字段內(nèi)容。例如：

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

 

以上命令調(diào)用 Word 處理 body 字段中的內(nèi)容，編輯完成后保存退出，SQLite 自動(dòng)更新相應(yīng)的字段內(nèi)容。
執(zhí)行系統(tǒng)命令

輸入.shell或者.system命令執(zhí)行操作系統(tǒng)的命令并返回 sqlite3，例如：

sqlite> .shell cd
D:\Software\sqlite-tools-win32-x86-3330000

sqlite> .system dir /B
get_employees.sql
hr.db
new.db
result.txt
sqldiff.exe
sqlite3.exe
sqlite3_analyzer.exe

 

退出客戶端

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

sqlite> .exit

 

.exit code命令可以返回一個(gè)退出碼，通常用于編寫腳本程序。

    ??更多的 sqlite3 命令可以通過 .help 命令查看相應(yīng)的使用說明。