SQLite3 實踐教程

應用程序經常需要保存數據。無論你的用戶是創建簡單的文本文檔、複雜的圖形布局、遊戲進度還是錯綜複雜的客戶和訂單號列表，軟體通常都意味著生成數據。有很多方法可以存儲數據以供重複使用。你可以將文本轉儲為 INI、YAML、XML 或 JSON 等配置格式，可以輸出原始的二進位數據，也可以將數據存儲在結構化資料庫中。SQLite 是一個自包含的、輕量級資料庫，可輕鬆創建、解析、查詢、修改和傳輸數據。

• 下載 SQLite3 備忘錄

SQLite 專用於 公共領域，從技術上講，這意味著它沒有版權，因此不需要許可證。如果你需要許可證，則可以 購買所有權擔保。SQLite 非常常見，大約有 1 萬億個 SQLite 資料庫正在使用中。在每個基於 Webkit 的 Web 瀏覽器，現代電視機，汽車多媒體系統以及無數其他軟體應用程序中，Android 和 iOS 設備， macOS 和 Windows 10 計算機，大多數 Linux 系統上都包含多個這種資料庫。

總而言之，它是用於存儲和組織數據的一個可靠而簡單的系統。

安裝

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

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

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

使用 SQLite

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

與 SQLite 交互

你可以使用 sqlite3 命令與 SQLite 進行交互。 該命令提供了一個互動式的 shell 程序，以便你可以查看和更新資料庫。

$ 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 中，因此現在的提示符是 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
[...]

這些命令中的其中一些是二進位的，而其他一些則需要唯一的參數（如文件名、路徑等）。這些是 SQLite Shell 的管理命令，不是用於資料庫查詢。資料庫以結構化查詢語言（SQL）進行查詢，許多 SQLite 查詢與你從 MySQL 和 MariaDB 資料庫中已經知道的查詢相同。但是，數據類型和函數有所不同，因此，如果你熟悉另一個資料庫，請特別注意細微的差異。

創建資料庫

啟動 SQLite 時，可以打開內存資料庫，也可以選擇要打開的資料庫：

$ sqlite3 mydatabase.db

如果還沒有資料庫，則可以在 SQLite 提示符下創建一個資料庫：

sqlite> .open mydatabase.db

現在，你的硬碟驅動器上有一個空文件，可以用作 SQLite 資料庫。 文件擴展名 .db 是任意的。你也可以使用 .sqlite 或任何你想要的後綴。

創建一個表

資料庫包含一些 表 table ，可以將其可視化為電子表格。有許多的行（在資料庫中稱為 記錄 record ）和列。行和列的交集稱為 欄位 field 。

結構化查詢語言（SQL）以其提供的內容而命名：一種以可預測且一致的語法查詢資料庫內容以接收有用的結果的方法。SQL 讀起來很像普通的英語句子，即使有點機械化。當前，你的資料庫是一個沒有任何錶的空資料庫。

你可以使用 CREATE 來創建一個新表，你可以和 IF NOT EXISTS 結合使用。以便不會破壞現在已有的同名的表。

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

• 唯一標識符
• 人名
• 記錄創建的時間和日期

唯一標識符

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

無需 SQL 語句即可創建此欄位。

數據類型

對於我的示例表中，我正在創建一個 name 列來保存 TEXT 類型的數據。為了防止在沒有指定欄位數據的情況下創建記錄，可以添加 NOT NULL 指令。

用 name TEXT NOT NULL 語句來創建。

SQLite 中有五種數據類型（實際上是 儲存類別）：

• TEXT：文本字元串
• INTEGER：一個數字
• REAL：一個浮點數（小數位數無限制）
• BLOB：二進位數據（例如，.jpeg 或 .webp 圖像）
• NULL：空值

日期和時間戳

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

創建此欄位的 SQL 語句是：datestamp DATETIME DEFAULT CURRENT_TIMESTAMP。

創建表的語句

在 SQLite 中創建此示例表的完整 SQL：

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

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

你可以使用 SQLite 命令 .tables 驗證表是否已創建：

sqlite> .tables
member

查看錶中的所有列

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

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

數據輸入

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

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

查看錶中的數據：

> 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

添加多行數據

現在創建第二個表：

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

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

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

修改表結構

你現在有兩個表，但是到目前為止，兩者之間沒有任何關係。它們每個都包含獨立的數據，但是可能你可能需要將第一個表的成員與第二個表中列出的特定項相關聯。

為此，你可以為第一個表創建一個新列，該列對應於第二個表。由於兩個表都設計有唯一標識符（這要歸功於 SQLite 的自動創建），所以連接它們的最簡單方法是將其中一個的 rowid 欄位用作另一個的選擇器。

在第一個表中創建一個新列，以存儲第二個表中的值：

> ALTER TABLE member ADD os INT;

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

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

對 member 表中的其他行重複相同的過程。更新 os 欄位，為了數據多樣性，在四行記錄上分配三種不同的發行版（其中一種加倍）。

聯接表

現在，這兩個表相互關聯，你可以使用 SQL 顯示關聯的數據。資料庫中有多種 聯接方式，但是一旦掌握了基礎知識，就可以嘗試所有的聯接形式。這是一個基本聯接，用於將 member 表的 os 欄位中的值與 linux 表的 rowid 欄位相關聯：

> 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 欄位形成了關聯。

在一個圖形應用程序中，你可以想像 os 欄位是一個下拉選項菜單，其中的值是 linux 表中 distro 欄位中的數據。將相關的數據集通過唯一的欄位相關聯，可以確保數據的一致性和有效性，並且藉助 SQL，你可以在以後動態地關聯它們。

了解更多

SQLite 是一個非常有用的自包含的、可移植的開源資料庫。學習以交互方式使用它是邁向針對 Web 應用程序進行管理或通過編程語言庫使用它的重要的第一步。

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

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

via: https://opensource.com/article/21/2/sqlite3-cheat-sheet

作者：Klaatu 選題：lujun9972 譯者：amwps290 校對：wxy

本文由 LCTT 原創編譯，Linux中國 榮譽推出

本文轉載來自 Linux 中國: https://github.com/Linux-CN/archive