,,

SQLite 入門(mén)教程

當(dāng)前位置：點(diǎn)晴教程→知識(shí)管理交流 →『技術(shù)文檔交流』

freeflydom

2024年2月24日 16:12 本文熱度 1108

不是 MySQL 用不起，而是 SQLite 更有性?xún)r(jià)比，絕大多數(shù)的 Web 應(yīng)用 SQLite 都可以滿(mǎn)足。

SQLite 是一個(gè)用 C 語(yǔ)言編寫(xiě)的開(kāi)源、輕量級(jí)、快速、獨(dú)立且高可靠性的 SQL 數(shù)據(jù)庫(kù)引擎，它提供了功能齊全的數(shù)據(jù)庫(kù)解決方案。SQLite 幾乎可以在所有的手機(jī)和計(jì)算機(jī)上運(yùn)行，它被嵌入到無(wú)數(shù)人每天都在使用的眾多應(yīng)用程序中。

此外，SQLite 還具有穩(wěn)定的文件格式、跨平臺(tái)能力和向后兼容性等特點(diǎn)。SQLite 的開(kāi)發(fā)者承諾，至少在 2050 年之前保持該文件格式不變。

本文將介紹 SQLite 的基礎(chǔ)知識(shí)和使用方法。

SQLite 安裝

在 SQLite 官方頁(yè)面(https://sqlite.org/download.html) 下載適合你目標(biāo)系統(tǒng)的壓縮包。

下載并解壓后，無(wú)論是在 Windows、Linux 還是 Mac OS 系統(tǒng)上，你都可以得到一個(gè) sqlite3 命令行工具。

以下是在 Mac OS 上解壓后得到的命令行工具示例：

➜  sqlite-tools-osx-x64-3450100 ls -l
total 14952
-rwxr-xr-x@ 1 darcy  staff  1907136  1 31 00:27 sqldiff
-rwxr-xr-x@ 1 darcy  staff  2263792  1 31 00:25 sqlite3
-rwxr-xr-x@ 1 darcy  staff  3478872  1 31 00:27 sqlite3_analyzer

SQLite 使用場(chǎng)景

SQLite 與客戶(hù)端/服務(wù)器類(lèi)型的 SQL 數(shù)據(jù)庫(kù)引擎（例如 MySQL、Oracle、PostgreSQL 或 SQL Server）不同，它們解決的問(wèn)題也不同。

服務(wù)器端的 SQL 數(shù)據(jù)庫(kù)引擎旨在實(shí)現(xiàn)企業(yè)級(jí)數(shù)據(jù)的共享存儲(chǔ)，它們強(qiáng)調(diào)的是可擴(kuò)展性、并發(fā)性、集中化和控制性。相比之下，SQLite 通常用于為個(gè)人應(yīng)用程序和設(shè)備提供本地?cái)?shù)據(jù)存儲(chǔ)，它強(qiáng)調(diào)的是經(jīng)濟(jì)、高效、可靠、獨(dú)立和簡(jiǎn)單。

SQLite 的使用場(chǎng)景：

嵌入式設(shè)備和物聯(lián)網(wǎng)

SQLite 不需要額外的管理或服務(wù)啟動(dòng)，非常適合用在手機(jī)、電視、機(jī)頂盒、游戲機(jī)、相機(jī)、手表等智能設(shè)備上。
網(wǎng)站

多數(shù)低流量網(wǎng)站可以使用 SQLite 作為數(shù)據(jù)庫(kù)。根據(jù)官方網(wǎng)站的介紹，通常每天訪(fǎng)問(wèn)量少于 10 萬(wàn)次的網(wǎng)站都可以很好地運(yùn)行 SQLite。SQLite 的官方網(wǎng)站（https://www.sqlite.org/）自身就使用 SQLite 作為數(shù)據(jù)庫(kù)引擎，每天處理大約 50 萬(wàn) HTTP 請(qǐng)求，其中約 15-20% 的請(qǐng)求涉及數(shù)據(jù)庫(kù)查詢(xún)
數(shù)據(jù)分析

SQLite3 命令行工具能方便地與 CSV 和 Excel 文件進(jìn)行交互操作，適合分析大數(shù)據(jù)集。同時(shí)，許多語(yǔ)言（如 Python）都內(nèi)置了 SQLite 支持，可以輕松編寫(xiě)腳本進(jìn)行數(shù)據(jù)操作。
緩存

SQLite 可以作為應(yīng)用服務(wù)的緩存，減輕對(duì)中心數(shù)據(jù)庫(kù)的壓力。
內(nèi)存或者臨時(shí)數(shù)據(jù)庫(kù)

得益于 SQLite 的簡(jiǎn)單快速，非常使用程序演示或者日常測(cè)試。

SQLite 不適合的場(chǎng)景包括：

需要通過(guò)網(wǎng)絡(luò)訪(fǎng)問(wèn)數(shù)據(jù)庫(kù)的情況。SQLite 是一個(gè)本地文件數(shù)據(jù)庫(kù)，沒(méi)有提供遠(yuǎn)程訪(fǎng)問(wèn)功能。
要求高可用性和可擴(kuò)展性的場(chǎng)合。SQLite 簡(jiǎn)單易用，但不可擴(kuò)展。
數(shù)據(jù)量極大時(shí)。盡管 SQLite 數(shù)據(jù)庫(kù)大小的限制高達(dá) 281 TB，但所有數(shù)據(jù)都必須存儲(chǔ)在單個(gè)磁盤(pán)上。
寫(xiě)入操作高并發(fā)時(shí)。SQLite 在任何時(shí)刻只允許一個(gè)寫(xiě)入操作執(zhí)行，其他寫(xiě)入操作需要排隊(duì)。

SQLite3 命令操作

SQLite 提供了 sqlite3（在windows 為 sqlite3.exe）命令行工具，通過(guò)該工具可以執(zhí)行 SQLite 數(shù)據(jù)庫(kù)操作和 SQL 語(yǔ)句。

直接在命令提示符下執(zhí)行 ./sqlite3 啟動(dòng) sqlite3 程序，然后可以通過(guò)輸入 .help 查看幫助指南，或者輸入 .help 關(guān)鍵詞 獲取特定關(guān)鍵詞的幫助信息。

部分命令列表如下：

sqlite> .help.databases               List names and files of attached databases
.dbconfig ?op? ?val?     List or change sqlite3_db_config() options
.dbinfo ?DB?             Show status information about the database
.excel                   Display the output of next command in spreadsheet
.exit ?CODE?             Exit this program with return-code CODE
.expert                  EXPERIMENTAL. Suggest indexes for queries
.explain ?on|off|auto?   Change the EXPLAIN formatting mode.  Default: auto
.help ?-all? ?PATTERN?   Show help text for PATTERN
.hex-rekey OLD NEW NEW   Change the encryption key using hexadecimal
.indexes ?TABLE?         Show names of indexes
.mode MODE ?OPTIONS?     Set output mode
.open ?OPTIONS? ?FILE?   Close existing database and reopen FILE
.output ?FILE?           Send output to FILE or stdout if FILE is omitted
.quit                    Exit this program
.read FILE               Read input from FILE or command output
.schema ?PATTERN?        Show the CREATE statements matching PATTERN
.show                    Show the current values for various settings
.tables ?TABLE?          List names of tables matching LIKE pattern TABLE
.......

sqlite3 只是讀取輸入行信息，然后傳遞給 SQLite 庫(kù)來(lái)執(zhí)行，SQL 語(yǔ)句都要以分號(hào) ; 結(jié)尾才會(huì)開(kāi)始執(zhí)行，因此你可以自由的分行輸入。

在 sqlite3 中，SQL 語(yǔ)句需以分號(hào) ; 結(jié)尾才會(huì)執(zhí)行，允許跨行輸入。特殊的點(diǎn)命令（如 .help 和 .tables）以小數(shù)點(diǎn) . 開(kāi)頭，不需要分號(hào)。

SQLite 新建數(shù)據(jù)庫(kù)

直接執(zhí)行 sqlite3 filename 打開(kāi)或創(chuàng)建一個(gè) SQLite 數(shù)據(jù)庫(kù)。如果文件不存在，SQLite 會(huì)自動(dòng)創(chuàng)建它。

示例：打開(kāi)或創(chuàng)建名為 my_sqlite.db 的 SQLite 數(shù)據(jù)庫(kù)文件。

$ sqlite3 my_sqlite.dbSQLite version 3.39.5 2022-10-14 20:58:05
Enter ".help" for usage hints.sqlite>

也可以首先創(chuàng)建一個(gè)空白文件，然后使用 sqlite3 命令打開(kāi)它。接下來(lái)使用 CREATE TABLE 命令創(chuàng)建一個(gè)名為 user 的表，用 .tables 命令查看現(xiàn)有表格，使用 .exit 退出 sqlite3 工具。

$ touch test.db
$ sqlite3 test.db
SQLite version 3.39.5 2022-10-14 20:58:05Enter ".help" for usage hints.
sqlite> create table user(name text,age int);
sqlite> .tablesusersqlite>

SQLite 查看當(dāng)前數(shù)據(jù)庫(kù)

使用點(diǎn)命令 .databases 查看當(dāng)前打開(kāi)的數(shù)據(jù)庫(kù)。

sqlite> .databases
main: /Users/darcy/develop/sqlite-tools-osx-x86-3420000/my_sqlite.db r/w
sqlite>

SQLite 增刪改查

SQLite 幾乎完全兼容常見(jiàn)的 SQL 語(yǔ)句規(guī)范，因此可以直接編寫(xiě)和執(zhí)行標(biāo)準(zhǔn)的 SQL 語(yǔ)句。

創(chuàng)建表：

sqlite> create table user(name text,age int);
sqlite>

插入數(shù)據(jù)：

sqlite> insert into user values('aLang',20);
sqlite> insert into user values('Darcy',30);
sqlite> insert into user values('XiaoMing',40);

查詢(xún)數(shù)據(jù)：

sqlite> select * from user;
aLang|20Darcy|30XiaoMing|40

添加索引，為 user 表的 name 創(chuàng)建名為 user_name 的索引：

sqlite> create index user_name on user(name);

SQLite 更改輸出格式

在查詢(xún)數(shù)據(jù)時(shí)，SQLite 默認(rèn)使用 | 分割每列數(shù)據(jù)，這可能不便于閱讀。實(shí)際上，sqlite3 工具支持多種輸出格式，默認(rèn)為 list 模式。

以下是可用的輸出格式：ascii、box、csv、column、html、insert、json、line、list、markdown、quote、table。

可以使用 .mode 命令更改輸出格式。

Box 格式：

sqlite> .mode box
sqlite> select * from user;
┌──────────┬─────┐
│   name   │ age │
├──────────┼─────┤
│ aLang    │ 20  │
│ Darcy    │ 30  │
│ XiaoMing │ 40  │
└──────────┴─────┘

json 格式：

sqlite> .mode json
sqlite> select * from user;
[{"name":"aLang","age":20},
{"name":"Darcy","age":30},
{"name":"XiaoMing","age":40}]

column 格式：

sqlite> .mode columnsqlite> select * from user;
name      age--------  ---aLang     20Darcy     30XiaoMing  40

table 格式：

sqlite> .mode tablesqlite> select * from user;+----------+-----+|   name   | age |+----------+-----+| aLang    | 20  || Darcy    | 30  || XiaoMing | 40  |+----------+-----+sqlite>

查詢(xún) Schema

sqlite3 工具提供了幾個(gè)方便的命令，可用于查看數(shù)據(jù)庫(kù)的 schema ，這些命令純粹作為快捷方式提供。

例如，.table 查看數(shù)據(jù)庫(kù)中的所有表：

sqlite> .tableuser

點(diǎn)命令 .table 相當(dāng)于下面的查詢(xún)語(yǔ)句。

sqlite> SELECT name FROM sqlite_schema
   ...> WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%'
   ...> ;user

sqlite_master 是 SQLite 中的一個(gè)特殊表，其中包含了數(shù)據(jù)庫(kù)的 schema 信息。你可以查詢(xún)這個(gè)表以獲取表的創(chuàng)建語(yǔ)句和索引信息。

sqlite> .mode tablesqlite> select * from sqlite_schema;+-------+-----------+----------+----------+--------------------------------------+| type  |   name    | tbl_name | rootpage |                 sql                  |+-------+-----------+----------+----------+--------------------------------------+| table | user      | user     | 2        | CREATE TABLE user(name text,age int) || index | user_name | user     | 3        | CREATE INDEX user_name on user(name) |+-------+-----------+----------+----------+--------------------------------------+

使用 .indexes 查看索引，使用 .schema 查看 schema 詳情。

sqlite> .indexes
user_name
sqlite> .schemaCREATE TABLE user(name text,age int);CREATE INDEX user_name on user(name);

結(jié)果寫(xiě)出到文件

使用 .output filename 命令將查詢(xún)結(jié)果寫(xiě)入指定文件。

下面是一個(gè)示例，先使用 .mode json 更改輸出為 JSON 格式，然后在查詢(xún)表寫(xiě)出到 sql_result.json。

sqlite> .output sql_result.json
sqlite> .mode json
sqlite> select * from user;
sqlite> .exit
$ cat sql_result.json
[{"name":"aLang","age":20},
{"name":"Darcy","age":30},
{"name":"XiaoMing","age":40}]

**寫(xiě)出并打開(kāi) EXCEL **

使用 .excel 會(huì)讓下一個(gè)查詢(xún)語(yǔ)句輸出到 Excel 中。

sqlite> .excel
sqlite> select * from sqlite_schema;

結(jié)果寫(xiě)出到文件

sqlite> .output sql_result.txt
sqlite> select * from sqlite_schema;
sqlite> select * from user;

讀取運(yùn)行 SQL 腳本

使用 .read 可以讀取指定文件中的 SQL 語(yǔ)句并運(yùn)行，這在需要批量執(zhí)行 SQL 腳本的場(chǎng)景中非常有用。

創(chuàng)建SQL文件：

$ echo "select * from user" > sql_query.sql$ cat sql_query.sqlselect * from user$ ./sqlite3 my_sqlite.dbSQLite version 3.42.0 2023-05-16 12:36:15
Enter ".help" for usage hints.sqlite> .mode tablesqlite> .read sql_query.sql+----------+-----+
|   name   | age |
+----------+-----+
| aLang    | 20  |
| Darcy    | 30  |
| XiaoMing | 40  |
+----------+-----+sqlite>

SQLite 備份與恢復(fù)

在涉及數(shù)據(jù)庫(kù)操作時(shí)，備份和恢復(fù)是至關(guān)重要的步驟，它們用于防止數(shù)據(jù)丟失并確保數(shù)據(jù)的持續(xù)性。SQLite 提供了簡(jiǎn)單的方法來(lái)備份和恢復(fù)你的數(shù)據(jù)庫(kù)。

在 SQLite 中可以通過(guò)導(dǎo)出整個(gè)數(shù)據(jù)庫(kù)為一個(gè) SQL 腳本來(lái)備份數(shù)據(jù)庫(kù)。此功能使用 .dump 命令實(shí)現(xiàn)。

$ ./sqlite3 my_sqlite.db
SQLite version 3.42.0 2023-05-16 12:36:15Enter ".help" for usage hints.
sqlite> .output backup.sql
sqlite> .dump
sqlite> .exit
$ cat backup.sql
PRAGMA foreign_keys=OFF;BEGIN TRANSACTION;CREATE TABLE user(name text,age int);INSERT INTO user VALUES('aLang',20);INSERT INTO user VALUES('Darcy',30);INSERT INTO user VALUES('XiaoMing',40);CREATE INDEX user_name on user(name);COMMIT;

這將導(dǎo)出整個(gè) my_sqlite.db 數(shù)據(jù)庫(kù)到 backup.sql 文件中。此 SQL 文件包含了重建數(shù)據(jù)庫(kù)所需的所有 SQL 語(yǔ)句。要恢復(fù)數(shù)據(jù)庫(kù)，只需在 sqlite3 中運(yùn)行這個(gè)腳本。

示例：恢復(fù)數(shù)據(jù)到庫(kù) my_sqlite_2 中。

$ ./sqlite3 my_sqlite_2.db
SQLite version 3.42.0 2023-05-16 12:36:15Enter ".help" for usage hints.
sqlite> .read backup.sql
sqlite> select * from user;
aLang|20Darcy|30XiaoMing|40

這將執(zhí)行 backup.sql 文件中的所有 SQL 語(yǔ)句，重建數(shù)據(jù)庫(kù)。通過(guò)以上的備份與恢復(fù)方法，你可以確保你的 SQLite 數(shù)據(jù)庫(kù)資料得到可靠的保護(hù)，且在需要時(shí)能夠迅速恢復(fù)。

SQLite 可視化工具

命令行操作總歸不太直觀，如果你喜歡可視化操作，可以下載 SQLite Database Browser 進(jìn)行操作。

下載頁(yè)面：https://sqlitebrowser.org/dl/

附錄

SQLite 常用函數(shù)列表，見(jiàn)名知意不寫(xiě)注釋了。

Function 1	Function 2	Function 3	Function 4
abs(X)	changes()	char(X1,X2,...,XN)	coalesce(X,Y,...)
concat(X,...)	concat_ws(SEP,X,...)	format(FORMAT,...)	glob(X,Y)
hex(X)	ifnull(X,Y)	iif(X,Y,Z)	instr(X,Y)
last_insert_rowid()	length(X)	like(X,Y)	like(X,Y,Z)
likelihood(X,Y)	likely(X)	load_extension(X)	load_extension(X,Y)
lower(X)	ltrim(X)	ltrim(X,Y)	max(X,Y,...)
min(X,Y,...)	nullif(X,Y)	octet_length(X)	printf(FORMAT,...)
quote(X)	random()	randomblob(N)	replace(X,Y,Z)
round(X)	round(X,Y)	rtrim(X)	rtrim(X,Y)
sign(X)	soundex(X)	sqlite_compileoption_get(N)	sqlite_compileoption_used(X)
sqlite_offset(X)	sqlite_source_id()	sqlite_version()	substr(X,Y)
substr(X,Y,Z)	substring(X,Y)	substring(X,Y,Z)	total_changes()
trim(X)	trim(X,Y)