SQLite3 實踐教程

作者：Klaatu 2021-02-15 15:40:28

SQLite 是一個自包含的、輕量級數(shù)據(jù)庫，可輕松創(chuàng)建、解析、查詢、修改和傳輸數(shù)據(jù)。SQLite 專用于公共領(lǐng)域，從技術(shù)上講，這意味著它沒有版權(quán)，因此不需要許可證。

[[381815]]

開始使用這個功能強大且通用的數(shù)據(jù)庫吧。

應(yīng)用程序經(jīng)常需要保存數(shù)據(jù)。無論你的用戶是創(chuàng)建簡單的文本文檔、復(fù)雜的圖形布局、游戲進(jìn)度還是錯綜復(fù)雜的客戶和訂單號列表，軟件通常都意味著生成數(shù)據(jù)。有很多方法可以存儲數(shù)據(jù)以供重復(fù)使用。你可以將文本轉(zhuǎn)儲為 INI、YAML、XML 或 JSON 等配置格式，可以輸出原始的二進(jìn)制數(shù)據(jù)，也可以將數(shù)據(jù)存儲在結(jié)構(gòu)化數(shù)據(jù)庫中。SQLite 是一個自包含的、輕量級數(shù)據(jù)庫，可輕松創(chuàng)建、解析、查詢、修改和傳輸數(shù)據(jù)。

下載 SQLite3 備忘錄

SQLite 專用于公共領(lǐng)域，從技術(shù)上講，這意味著它沒有版權(quán)，因此不需要許可證。如果你需要許可證，則可以購買所有權(quán)擔(dān)保。SQLite 非常常見，大約有 1 萬億個 SQLite 數(shù)據(jù)庫正在使用中。在每個基于 Webkit 的 Web 瀏覽器，現(xiàn)代電視機，汽車多媒體系統(tǒng)以及無數(shù)其他軟件應(yīng)用程序中，Android 和 iOS 設(shè)備， macOS 和 Windows 10 計算機，大多數(shù) Linux 系統(tǒng)上都包含多個這種數(shù)據(jù)庫。

總而言之，它是用于存儲和組織數(shù)據(jù)的一個可靠而簡單的系統(tǒng)。

安裝

你的系統(tǒng)上可能已經(jīng)有 SQLite 庫，但是你需要安裝其命令行工具才能直接使用它。在 Linux上，你可能已經(jīng)安裝了這些工具。該工具提供的命令是 sqlite3 （而不僅僅是 sqlite）。

如果沒有在你的 Linux 或 BSD 上安裝 SQLite，你則可以從軟件倉庫中或 ports 樹中安裝 SQLite，也可以從源代碼或已編譯的二進(jìn)制文件進(jìn)行下載并安裝。

在 macOS 或 Windows 上，你可以從 sqlite.org 下載并安裝 SQLite 工具。

使用 SQLite

通過編程語言與數(shù)據(jù)庫進(jìn)行交互是很常見的。因此，像 Java、Python、Lua、PHP、Ruby、C++ 以及其他編程語言都提供了 SQLite 的接口（或“綁定”）。但是，在使用這些庫之前，了解數(shù)據(jù)庫引擎的實際情況以及為什么你對數(shù)據(jù)庫的選擇很重要是有幫助的。本文向你介紹 SQLite 和 sqlite3 命令，以便你熟悉該數(shù)據(jù)庫如何處理數(shù)據(jù)的基礎(chǔ)知識。

與 SQLite 交互

你可以使用 sqlite3 命令與 SQLite 進(jìn)行交互。該命令提供了一個交互式的 shell 程序，以便你可以查看和更新數(shù)據(jù)庫。

$ sqlite3
SQLite version 3.34.0 2020-12-01 16:14:00
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>

該命令將你使你處于 SQLite 的子 shell 中，因此現(xiàn)在的提示符是 SQLite 的提示符。你以前使用的 Bash 命令在這里將不再適用。你必須使用 SQLite 命令。要查看 SQLite 命令列表，請輸入 .help：

sqlite> .help
.archive ...             Manage SQL archives
.auth ON|OFF             SHOW authorizer callbacks
.backup ?DB? FILE        Backup DB (DEFAULT "main") TO FILE
.bail ON|off             Stop after hitting an error.  DEFAULT OFF
.binary ON|off           Turn BINARY output ON OR off.  DEFAULT OFF
.cd DIRECTORY            CHANGE the working directory TO DIRECTORY
[...]

這些命令中的其中一些是二進(jìn)制的，而其他一些則需要唯一的參數(shù)（如文件名、路徑等）。這些是 SQLite Shell 的管理命令，不是用于數(shù)據(jù)庫查詢。數(shù)據(jù)庫以結(jié)構(gòu)化查詢語言（SQL）進(jìn)行查詢，許多 SQLite 查詢與你從 MySQL 和 MariaDB 數(shù)據(jù)庫中已經(jīng)知道的查詢相同。但是，數(shù)據(jù)類型和函數(shù)有所不同，因此，如果你熟悉另一個數(shù)據(jù)庫，請?zhí)貏e注意細(xì)微的差異。

創(chuàng)建數(shù)據(jù)庫

啟動 SQLite 時，可以打開內(nèi)存數(shù)據(jù)庫，也可以選擇要打開的數(shù)據(jù)庫：

$ sqlite3 mydatabase.db

如果還沒有數(shù)據(jù)庫，則可以在 SQLite 提示符下創(chuàng)建一個數(shù)據(jù)庫：

sqlite> .open mydatabase.db

現(xiàn)在，你的硬盤驅(qū)動器上有一個空文件，可以用作 SQLite 數(shù)據(jù)庫。文件擴展名 .db 是任意的。你也可以使用 .sqlite 或任何你想要的后綴。

創(chuàng)建一個表

數(shù)據(jù)庫包含一些表table，可以將其可視化為電子表格。有許多的行（在數(shù)據(jù)庫中稱為記錄record）和列。行和列的交集稱為字段field。

結(jié)構(gòu)化查詢語言（SQL）以其提供的內(nèi)容而命名：一種以可預(yù)測且一致的語法查詢數(shù)據(jù)庫內(nèi)容以接收有用的結(jié)果的方法。SQL 讀起來很像普通的英語句子，即使有點機械化。當(dāng)前，你的數(shù)據(jù)庫是一個沒有任何表的空數(shù)據(jù)庫。

你可以使用 CREATE 來創(chuàng)建一個新表，你可以和 IF NOT EXISTS 結(jié)合使用。以便不會破壞現(xiàn)在已有的同名的表。

你無法在 SQLite 中創(chuàng)建一個沒有任何字段的空表，因此在嘗試 CREATE 語句之前，必須考慮預(yù)期表將存儲的數(shù)據(jù)類型。在此示例中，我將使用以下列創(chuàng)建一個名為 member 的表：

唯一標(biāo)識符
人名
記錄創(chuàng)建的時間和日期

唯一標(biāo)識符

最好用唯一的編號來引用記錄，幸運的是，SQLite 認(rèn)識到這一點，創(chuàng)建一個名叫 rowid 的列來為你自動實現(xiàn)這一點。

無需 SQL 語句即可創(chuàng)建此字段。

數(shù)據(jù)類型

對于我的示例表中，我正在創(chuàng)建一個 name 列來保存 TEXT 類型的數(shù)據(jù)。為了防止在沒有指定字段數(shù)據(jù)的情況下創(chuàng)建記錄，可以添加 NOT NULL 指令。

用 name TEXT NOT NULL 語句來創(chuàng)建。

SQLite 中有五種數(shù)據(jù)類型（實際上是 儲存類別）：

TEXT：文本字符串
INTEGER：一個數(shù)字
REAL：一個浮點數(shù)（小數(shù)位數(shù)無限制）
BLOB：二進(jìn)制數(shù)據(jù)（例如，.jpeg 或 .webp 圖像）
NULL：空值

日期和時間戳

SQLite 有一個方便的日期和時間戳功能。它本身不是數(shù)據(jù)類型，而是 SQLite 中的一個函數(shù)，它根據(jù)所需的格式生成字符串或整數(shù)。在此示例中，我將其保留為默認(rèn)值。

創(chuàng)建此字段的 SQL 語句是：datestamp DATETIME DEFAULT CURRENT_TIMESTAMP。

創(chuàng)建表的語句

在 SQLite 中創(chuàng)建此示例表的完整 SQL：

sqlite> CREATE TABLE
...> IF NOT EXISTS
...> member (name TEXT NOT NULL,
...> datestamp DATETIME DEFAULT CURRENT_TIMESTAMP);

在此代碼示例中，我在語句的分句后按了回車鍵。以使其更易于閱讀。除非以分號（;）終止，否則 SQLite 不會運行你的 SQL 語句。

你可以使用 SQLite 命令 .tables 驗證表是否已創(chuàng)建：

sqlite> .tables
member

查看表中的所有列

你可以使用 PRAGMA 語句驗證表包含哪些列和行：

sqlite> PRAGMA table_info(member);
0|name|TEXT|1||0
1|datestamp|DATETIME|0|CURRENT_TIMESTAMP|0

數(shù)據(jù)輸入

你可以使用 INSERT 語句將一些示例數(shù)據(jù)填充到表中：

> INSERT INTO member (name) VALUES ('Alice');
> INSERT INTO member (name) VALUES ('Bob');
> INSERT INTO member (name) VALUES ('Carol');
> INSERT INTO member (name) VALUES ('David');

查看表中的數(shù)據(jù)：

> SELECT * FROM member;
Alice|2020-12-15 22:39:00
Bob|2020-12-15 22:39:02
Carol|2020-12-15 22:39:05
David|2020-12-15 22:39:07

添加多行數(shù)據(jù)

現(xiàn)在創(chuàng)建第二個表：

> CREATE TABLE IF NOT EXISTS linux (
...> distro TEXT NOT NULL);

填充一些示例數(shù)據(jù)，這一次使用小的 VALUES 快捷方式，因此你可以在一個命令中添加多行。關(guān)鍵字 VALUES 期望以括號形式列出列表，而用多個逗號分隔多個列表：

> INSERT INTO linux (distro)
...> VALUES ('Slackware'), ('RHEL'),
...> ('Fedora'),('Debian');

修改表結(jié)構(gòu)

你現(xiàn)在有兩個表，但是到目前為止，兩者之間沒有任何關(guān)系。它們每個都包含獨立的數(shù)據(jù)，但是可能你可能需要將第一個表的成員與第二個表中列出的特定項相關(guān)聯(lián)。

為此，你可以為第一個表創(chuàng)建一個新列，該列對應(yīng)于第二個表。由于兩個表都設(shè)計有唯一標(biāo)識符（這要歸功于 SQLite 的自動創(chuàng)建），所以連接它們的最簡單方法是將其中一個的 rowid 字段用作另一個的選擇器。

在第一個表中創(chuàng)建一個新列，以存儲第二個表中的值：

> ALTER TABLE member ADD os INT;

使用 linux 表中的唯一標(biāo)識符作為 member 表中每一條記錄中 os 字段的值。因為記錄已經(jīng)存在。因此你可以使用 UPDATE 語句而不是使用 INSERT 語句來更新數(shù)據(jù)。需要特別注意的是，你首先需要選中特定的一行來然后才能更新其中的某個字段。從句法上講，這有點相反，更新首先發(fā)生，選擇匹配最后發(fā)生：

> UPDATE member SET os=1 WHERE name='Alice';

對 member 表中的其他行重復(fù)相同的過程。更新 os 字段，為了數(shù)據(jù)多樣性，在四行記錄上分配三種不同的發(fā)行版（其中一種加倍）。

聯(lián)接表

現(xiàn)在，這兩個表相互關(guān)聯(lián)，你可以使用 SQL 顯示關(guān)聯(lián)的數(shù)據(jù)。數(shù)據(jù)庫中有多種 聯(lián)接方式，但是一旦掌握了基礎(chǔ)知識，就可以嘗試所有的聯(lián)接形式。這是一個基本聯(lián)接，用于將 member 表的 os 字段中的值與 linux 表的 rowid 字段相關(guān)聯(lián)：

> SELECT * FROM member INNER JOIN linux ON member.os=linux.rowid;
Alice|2020-12-15 22:39:00|1|Slackware
Bob|2020-12-15 22:39:02|3|Fedora
Carol|2020-12-15 22:39:05|3|Fedora
David|2020-12-15 22:39:07|4|Debian

os 和 rowid 字段形成了關(guān)聯(lián)。

在一個圖形應(yīng)用程序中，你可以想象 os 字段是一個下拉選項菜單，其中的值是 linux 表中 distro 字段中的數(shù)據(jù)。將相關(guān)的數(shù)據(jù)集通過唯一的字段相關(guān)聯(lián)，可以確保數(shù)據(jù)的一致性和有效性，并且借助 SQL，你可以在以后動態(tài)地關(guān)聯(lián)它們。

了解更多

SQLite 是一個非常有用的自包含的、可移植的開源數(shù)據(jù)庫。學(xué)習(xí)以交互方式使用它是邁向針對 Web 應(yīng)用程序進(jìn)行管理或通過編程語言庫使用它的重要的第一步。

如果你喜歡 SQLite，也可以嘗試由同一位作者 Richard Hipp 博士的 Fossil。

在學(xué)習(xí)和使用 SQLite 時，有一些常用命令可能會有所幫助，所以請立即下載我們的 SQLite3 備忘單！

責(zé)任編輯：龐桂玉來源： Linux中國

SQLite3 數(shù)據(jù)庫