SQLite3基本介紹與常用語句匯總(最新整理)

更新時間：2025年06月16日 10:46:37 作者：jjkkzzzz

SQLite3是一款輕量級嵌入式數(shù)據(jù)庫,無需服務(wù)器,單文件存儲,支持標(biāo)準(zhǔn)SQL與ACID事務(wù),適用于移動應(yīng)用、嵌入式系統(tǒng)及快速開發(fā)場景,具備跨平臺、易部署、高效管理的特點,本文給大家介紹SQLite3介紹與常用語句匯總,感興趣的朋友一起看看吧

SQLite3簡介

SQLite3是一款輕量級的、基于文件的開源關(guān)系型數(shù)據(jù)庫引擎，由 D. Richard Hipp 于 2000 年首次發(fā)布。它遵循 SQL 標(biāo)準(zhǔn)，但與傳統(tǒng)的數(shù)據(jù)庫系統(tǒng)不同，SQLite 并不運行在獨立的服務(wù)器進程中，而是作為一個嵌入式數(shù)據(jù)庫引擎直接集成到應(yīng)用程序中。其所有的數(shù)據(jù)結(jié)構(gòu)（包括表、索引、事務(wù)日志等）都保存在一個單一的 .db 文件中。

SQLite 的設(shè)計理念是“零配置、開箱即用”，開發(fā)者只需將其動態(tài)庫鏈接進應(yīng)用程序，就可以直接進行數(shù)據(jù)庫操作，無需安裝數(shù)據(jù)庫服務(wù)或進行網(wǎng)絡(luò)配置。

SQLite3 是 SQLite 的第三個主要版本，相較前代有更強的兼容性和更完整的 SQL 支持，是目前最常用的版本。

SQLite3的特點

輕量嵌入式設(shè)計

SQLite3 不依賴服務(wù)器進程，僅作為應(yīng)用的一部分存在；編譯后的庫小于 1MB，運行開銷極低。

單文件存儲結(jié)構(gòu)

所有數(shù)據(jù)庫內(nèi)容都保存在一個磁盤文件中，便于復(fù)制、遷移和版本控制。

跨平臺支持廣泛

可以在 Linux、Windows、macOS、Android、iOS 等操作系統(tǒng)中運行，源代碼可編譯到幾乎所有主流平臺。

兼容標(biāo)準(zhǔn) SQL92

盡管體積小，但 SQLite3 支持大部分標(biāo)準(zhǔn) SQL 語法，如事務(wù)、子查詢、視圖、觸發(fā)器、聚合函數(shù)等。

零配置，無需安裝

無需安裝或初始化數(shù)據(jù)庫，只要程序能訪問數(shù)據(jù)庫文件就可以使用。

事務(wù)完整性（ACID）支持

SQLite3 保證事務(wù)的原子性、一致性、隔離性和持久性，適用于數(shù)據(jù)完整性要求較高的應(yīng)用。

SQLite3的適用場景

SQLite3 由于其嵌入式、便攜、小巧的特性，特別適用于以下場景：

移動應(yīng)用開發(fā)（Android/iOS）

SQLite3 是 Android 系統(tǒng)默認(rèn)數(shù)據(jù)庫，適合存儲用戶數(shù)據(jù)、緩存內(nèi)容、離線功能等。

嵌入式系統(tǒng) / IoT 設(shè)備

如智能電視、車載系統(tǒng)、傳感器節(jié)點等設(shè)備內(nèi)存和性能有限，SQLite 是輕量數(shù)據(jù)存儲的理想方案。

桌面軟件

常用于辦公類軟件（如記事本、財務(wù)管理工具）中提供本地數(shù)據(jù)存儲功能。

瀏覽器或前端環(huán)境

Web 應(yīng)用中的 IndexedDB/LocalStorage 常借助 SQLite 作為底層數(shù)據(jù)庫。

單用戶或低并發(fā)系統(tǒng)

適合使用場景為單人或單線程訪問，例如個人記賬軟件、本地日志記錄系統(tǒng)等。

快速原型開發(fā)和測試

因為免安裝、部署簡單，SQLite 常被用于開發(fā)早期快速迭代和測試環(huán)境中。

嵌套系統(tǒng)中的緩存數(shù)據(jù)庫

可作為大型數(shù)據(jù)庫系統(tǒng)的本地緩存，提升訪問性能，降低服務(wù)器負(fù)載。

SQLite 命令行工具（sqlite3 shell） 中的內(nèi)置命令

命令	作用說明
`.open filename.db`	打開或創(chuàng)建一個 SQLite 數(shù)據(jù)庫文件
`.tables`	列出當(dāng)前數(shù)據(jù)庫中的所有表
`.schema [table]`	查看某個表或所有表的建表語句（DDL）
`.headers ON/OFF`	開啟或關(guān)閉結(jié)果顯示中的列標(biāo)題
`.read filename.sql`	執(zhí)行指定的 SQL 文件內(nèi)容
`.exit` / `.quit`	退出 SQLite 命令行
`.databases`	查看當(dāng)前連接的數(shù)據(jù)庫文件
`.nullvalue NULL_REPLACEMENT`	設(shè)置 NULL 顯示為什么字符串
`.output filename.txt`	將查詢結(jié)果輸出到文件

基本操作語句

1.打開/創(chuàng)建數(shù)據(jù)庫文件

SQLite 使用命令行或程序語言（如 Python、C 等）調(diào)用 SQLite 引擎來打開或創(chuàng)建數(shù)據(jù)庫文件。文件不存在時會自動創(chuàng)建。

sqlite3 mydatabase.db

該命令會在當(dāng)前目錄中創(chuàng)建一個名為 mydatabase.db 的數(shù)據(jù)庫文件（如果尚不存在），并進入 SQLite 的交互式終端。你可以在里面執(zhí)行 SQL 命令。

2. 查看數(shù)據(jù)庫中所有表

SELECT name FROM sqlite_master WHERE type='table';

或者使用 SQLite 命令行工具提供的快捷命令：

.tables

3. 查看表結(jié)構(gòu)（PRAGMA 語句）

PRAGMA table_info(table_name);

示例：

PRAGMA table_info(users);

cid | name  | type    | notnull | dflt_value | pk
----+-------+---------+---------+------------+----
0   | id    | INTEGER | 0       | NULL       | 1
1   | name  | TEXT    | 0       | NULL       | 0
2   | age   | INTEGER | 0       | NULL       | 0

表相關(guān)操作

1. 創(chuàng)建表（CREATE TABLE）

CREATE TABLE table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
);

用于定義一個新的數(shù)據(jù)表，并指定字段名、數(shù)據(jù)類型和約束（如主鍵、非空等）。

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER
);

補充：查看某個表的建表語句. schema 表名

sqlite> .schema users
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER
);

2. 修改表結(jié)構(gòu)（ALTER TABLE）

SQLite 支持的 ALTER TABLE 功能比較有限，主要包括：

#修改表名
ALTER TABLE table_name RENAME TO new_table_name;
#新增列
ALTER TABLE table_name ADD COLUMN column_def;

示例：添加一個 email 字段

ALTER TABLE users ADD COLUMN email TEXT;
sqlite> .schema users
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER,
    email TEXT
);

你會發(fā)現(xiàn) email 字段已經(jīng)添加在表結(jié)構(gòu)末尾。注意：SQLite 不支持刪除列或修改列類型。

3. 刪除表（DROP TABLE）

DROP TABLE [IF EXISTS] table_name;

DROP TABLE IF EXISTS users;

4. 復(fù)制表結(jié)構(gòu)與數(shù)據(jù)

SQLite 沒有 CREATE TABLE ... LIKE 語法，可以用以下方式復(fù)制結(jié)構(gòu)和數(shù)據(jù)：

CREATE TABLE new_table AS SELECT * FROM old_table;

如果只想復(fù)制結(jié)構(gòu)（不含數(shù)據(jù)）：

CREATE TABLE new_table AS SELECT * FROM old_table WHERE 0;

數(shù)據(jù)操作語句

1. 插入數(shù)據(jù)（INSERT INTO）

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

也可以省略列名（前提是所有列都有值）：

INSERT INTO table_name VALUES (value1, value2, ...);

示例：

INSERT INFO uesrs (id, name, age, email) VALUES (1, "alice", 25, "a@.com");
sqlite> SELECT * FROM users;
id | name  | age | email
---+-------+-----+--------------------
1  | Alice | 25  | a@.com

2. 更新數(shù)據(jù)（UPDATE）

UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

示例：

UPDATE users SET age = 26 WHRER id = 1;
sqlite> SELECT * FROM users;
id | name  | age | email
---+-------+-----+--------------------
1  | Alice | 26  | a@.com

3. 刪除數(shù)據(jù)（DELETE）

DELETE FROM table_name WHERE condition;
注意：如果不加 WHERE，會刪除整張表的數(shù)據(jù)

示例：

sqlite> SELECT * FROM users;
id | name  | age | email
---+-------+-----+--------------------
1  | Alice | 26  | a@.com
DELETE FROM users WHERE id = 1;
sqlite> SELECT * FROM users;
-- 空表，無結(jié)果

4. 查詢數(shù)據(jù)（SELECT）

SELECT column1, column2, ... FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...];

SELECT * 表示查詢所有列。

查詢進階

1. 條件篩選（WHERE）

SELECT column1, column2 FROM table_name WHERE condition;

常用操作符包括：=, !=, >, <, >=, <=, LIKE, IN, BETWEEN, IS NULL 等。

示例：

SELECT * FROM users WHERE age > 25;
id | name  | age | email
---+-------+-----+---------------------
2  | Bob   | 30  | bob@example.com
3  | Carol | 28  | carol@example.com

2. 排序（ORDER BY）

SELECT * FROM table_name ORDER BY column [ASC|DESC];

示例：

SELECT * FROM users ORDER BY age DESC;
id | name  | age | email
---+-------+-----+---------------------
2  | Bob   | 30  | bob@example.com
3  | Carol | 28  | carol@example.com
1  | Alice | 25  | alice@example.com

3. 分組與過濾（GROUP BY + HAVING）

SELECT group_column, aggregate_function(...) FROM table_name GROUP BY group_column [HAVING condition];

示例：

SELECT age, COUNT(*) FROM users GROUP BY age HAVING COUNT(*) > 1;
假設(shè)有兩名用戶都 30 歲
age | COUNT(*)
----+----------
30  | 2

4. 多表連接（JOIN）

SELECT columns FROM table1
JOIN table2 ON table1.column = table2.column;

示例：

表users
id | name  | age | email
---+-------+-----+---------------------
2  | Bob   | 30  | bob@example.com
3  | Carol | 28  | carol@example.com
1  | Alice | 25  | alice@example.com
表orders
user_id | amount
--------+--------
2       | 100
3       | 150
SELECT users.name, orders.amount
FROM users
JOIN orders ON users.id = orders.user_id;
-- 輸出：
name  | amount
------+--------
Bob   | 100
Carol | 150

5. 子查詢與嵌套查詢

SELECT * FROM table WHERE column IN (SELECT ... FROM ... WHERE ...);

示例：

SELECT name FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);
輸出：
name
-----
Carol

6. 分頁查詢（LIMIT / OFFSET）

SELECT * FROM table_name LIMIT 限制行數(shù) OFFSET 起始行偏移量;
指令說明
   LIMIT：限制最多返回多少行結(jié)果。
   OFFSET：跳過前面多少行數(shù)據(jù)再開始返回（可選）。

例如在一個頁面中只顯示 10 條數(shù)據(jù)，就可以：

SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 0;   -- 第1頁
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 10;  -- 第2頁
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;  -- 第3頁

或者用更常見的公式：

LIMIT 每頁條數(shù) OFFSET (頁碼 - 1) * 每頁條數(shù)

示例：

原始數(shù)據(jù)為：
id | name  | age
---+-------+-----
1  | Alice | 25
2  | Bob   | 30
3  | Carol | 28
SELECT * FROM users ORDER BY id LIMIT 2 OFFSET 1;
按 id 排序后，跳過第1條數(shù)據(jù)，從第2條開始取，最多取2條。
執(zhí)行結(jié)果：
id | name  | age
---+-------+-----
2  | Bob   | 30
3  | Carol | 28

索引與性能

1.sqlite3中的索引是什么？

在 SQLite 中，索引是一種數(shù)據(jù)庫對象，它的作用類似于書本的目錄，可以加快查詢特定數(shù)據(jù)的速度。索引會為一個或多個列生成一個排序的數(shù)據(jù)結(jié)構(gòu)（通常是 B-tree），從而使查詢更快。

2.索引的特性？

加速查詢（尤其是 WHERE、JOIN、ORDER BY 等）

當(dāng)你查詢某張表時：

SELECT * FROM users WHERE age > 25;

如果 age 上有索引，SQLite 會用索引快速定位符合條件的數(shù)據(jù)，而不用全表掃描。

提升排序效率

SELECT * FROM users ORDER BY name;

如果 name 列已建索引，排序可以直接利用索引順序完成，而無需臨時排序。

加速多表連接（JOIN）

SELECT * FROM users JOIN orders ON users.id = orders.user_id;

如果 orders.user_id 建了索引，那么連接時匹配效率會更高。

不適用于頻繁變動的字段

索引雖然能加速查詢，但會減慢 INSERT、UPDATE、DELETE 的性能，因為每次數(shù)據(jù)改動，索引也要同步更新。

3. 創(chuàng)建索引（CREATE INDEX）

為單列創(chuàng)建索引

CREATE [UNIQUE] INDEX index_name ON table_name(column_name);
UNIQUE 表示不允許重復(fù)值（可選）。

示例：

CREATE INDEX idx_users_age ON users(age);
#查看是否命中索引
EXPLAIN QUERY PLAN SELECT * FROM users WHERE age > 25;
#輸出
SEARCH TABLE users USING INDEX idx_users_age (age>?)
說明查詢使用了你創(chuàng)建的索引。

為多列創(chuàng)建聯(lián)合索引

CREATE INDEX index_name ON table_name(column1, column2, ...);

適用于查詢中使用多個字段組合的情況。

遵守“最左前綴原則”

示例：

SELECT * FROM users WHERE name = 'Alice' AND age = 25;
#查看是否命中索引
EXPLAIN QUERY PLAN SELECT * FROM users WHERE name = 'Alice' AND age = 25;
#輸出
SEARCH TABLE users USING INDEX idx_users_name_age (name=? AND age=?)

最左前綴原則： 復(fù)合索引只有在查詢中使用了從左到右的“最前面的列”時，SQLite 才會使用該索引來優(yōu)化查詢。

示例：

id | name  | age
---+-------+-----
1  | Alice | 25
2  | Bob   | 30
3  | Carol | 28
#創(chuàng)建復(fù)合索引
CREATE INDEX idx_name_age ON users(name, age);

分別執(zhí)行以下查詢并查看是否命中索引

①使用 name（最左列），可以命中索引

EXPLAIN QUERY PLAN SELECT * FROM users WHERE name = 'Alice';
SEARCH TABLE users USING INDEX idx_name_age (name=?)

②使用 name + age（最左列 + 第二列），仍命中索引

EXPLAIN QUERY PLAN SELECT * FROM users WHERE name = 'Alice' AND age = 30;
SEARCH TABLE users USING INDEX idx_name_age (name=? AND age=?)

③只使用 age，不命中索引

EXPLAIN QUERY PLAN SELECT * FROM users WHERE age = 30;
SCAN TABLE users

④使用 age + name（第二列 + 最左列），仍命中索引，順序不影響

EXPLAIN QUERY PLAN SELECT * FROM users WHERE age = 30 AND name = 'Alice';
SEARCH TABLE users USING INDEX idx_name_age (age=? AND name=?)

注意：復(fù)合索引 idx_name_age(name, age) 是一棵按 name 排序、再按 age 排序的 B 樹結(jié)構(gòu)。查詢必須從最左的列開始匹配，否則無法用上這個索引。

4. 刪除索引（DROP INDEX）

DROP INDEX [IF EXISTS] index_name;

事務(wù)控制

1. 開始事務(wù)（BEGIN）

BEGIN;

用于開始一個事務(wù)。在事務(wù)開始后，所有的操作（如 INSERT、UPDATE、DELETE）都將在這個事務(wù)中進行。
如果事務(wù)內(nèi)的操作沒有出現(xiàn)錯誤，事務(wù)可以被提交（COMMIT）。如果出錯，可以回滾（ROLLBACK）整個事務(wù)。

2. 提交事務(wù)（COMMIT）

COMMIT;

提交當(dāng)前事務(wù)所做的所有更改。這會將事務(wù)中所有修改的數(shù)據(jù)寫入數(shù)據(jù)庫并使它們永久生效。

示例：提交事務(wù)

BEGIN;
INSERT INTO users (name, age, city) VALUES ('Eve', 40, 'Chengdu');
UPDATE users SET age = 45 WHERE name = 'Alice';
COMMIT;

3. 回滾事務(wù)（ROLLBACK）

ROLLBACK;

如果在事務(wù)中執(zhí)行某些操作時發(fā)生錯誤，可以使用 ROLLBACK 來撤銷所有在當(dāng)前事務(wù)中的操作，恢復(fù)到事務(wù)開始前的狀態(tài)。

示例：回滾事務(wù)

BEGIN;
INSERT INTO users (name, age, city) VALUES ('Eve', 40, 'Chengdu');
UPDATE users SET age = 45 WHERE name = 'Alice';
-- 假設(shè)此時發(fā)生了錯誤，我們決定回滾事務(wù)
ROLLBACK;

事務(wù)回滾后，Eve 和 Alice 的更新都將被撤銷，users 表中的數(shù)據(jù)保持不變。

4. 自動提交模式

在默認(rèn)情況下，SQLite 在每個獨立的 SQL 語句后自動提交。也就是說，每次執(zhí)行一條語句時，SQLite 會自動把它作為一個單獨的事務(wù)提交。
為了防止自動提交，可以顯式地使用 BEGIN 開始事務(wù)，直到使用 COMMIT 或 ROLLBACK。

5. 提交或回滾事務(wù)的應(yīng)用場景

批量操作： 比如一次性插入大量數(shù)據(jù)，使用事務(wù)能夠保證所有數(shù)據(jù)同時插入成功，避免數(shù)據(jù)不一致。
處理失敗的操作： 在多步操作中，如果中途某一步失敗，ROLLBACK 可以保證整個操作的原子性。

事務(wù)控制的典型應(yīng)用場景：

假設(shè)有一個轉(zhuǎn)賬操作，其中兩個表分別是 accounts（賬戶余額）和 transactions（交易記錄），我們需要確保轉(zhuǎn)賬操作成功或者完全回滾。

try {
        executeOrThrow(db, "BEGIN;");
        executeOrThrow(db, "UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A001';");
        // 故意出錯：列名 balxxx 不存在
        executeOrThrow(db, "UPDATE accounts SET balxxx = balance + 100 WHERE account_id = 'A002';");
        executeOrThrow(db, "INSERT INTO transactions (from_account, to_account, amount) VALUES ('A001', 'A002', 100);");
        //沒問題就提交
        executeOrThrow(db, "COMMIT;");
        std::cout << "Transaction committed.\n";
    } catch (const std::exception& ex) {
        std::cerr << ex.what() << "\n";
        //出錯回滾
        sqlite3_exec(db, "ROLLBACK;", nullptr, nullptr, nullptr);
        std::cout << "Transaction rolled back.\n";
    }

視圖與臨時表

視圖（VIEW）

視圖是 虛擬的表，本質(zhì)上是對一個 SELECT 查詢結(jié)果的封裝，它本身不存儲數(shù)據(jù)，而是每次訪問時執(zhí)行背后的查詢語句。它的存在意義主要在于以下幾點：

1. 簡化復(fù)雜查詢

當(dāng)你有一些經(jīng)常要執(zhí)行的復(fù)雜 JOIN、子查詢 或 聚合查詢 時，把它們寫進視圖，可以像操作普通表一樣簡單調(diào)用：

-- 查詢最近30天訂單金額前10的用戶
SELECT * FROM top_users_last_30_days;

而不用每次都寫長查詢。

一個視圖可以作為多個后續(xù)查詢的中間層，避免重復(fù) JOIN 和 GROUP BY 邏輯，提高可復(fù)用性和效率。

2. 增強可讀性與可維護性

把復(fù)雜查詢邏輯隱藏到視圖中后，業(yè)務(wù) SQL 更清晰：

-- 直接查視圖
SELECT * FROM user_purchases_summary WHERE total_spent > 1000;

而不是寫重復(fù)的 SQL 邏輯多處維護。

3.提高安全性

你可以只授予用戶對視圖的訪問權(quán)限，而非對底層表的權(quán)限，從而達到權(quán)限隔離的效果。

創(chuàng)建視圖：

CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT ...;
view_name：視圖名稱
SELECT ...：視圖對應(yīng)的查詢語句
TEMP：可選，創(chuàng)建臨時視圖，僅在當(dāng)前連接中可見

示例：創(chuàng)建一個只讀用戶信息視圖

CREATE VIEW active_users AS
SELECT id, name, email
FROM users
WHERE status = 'active';

你可以像查詢普通表一樣使用視圖：

SELECT * FROM active_users;

假設(shè) users 表如下：
| id | name  | email                                   | status   |
| -- | ----- | --------------------------------------- | -------- |
| 1  | Alice | [alice@mail.com](mailto:alice@mail.com) | active   |
| 2  | Bob   | [bob@mail.com](mailto:bob@mail.com)     | inactive |
| 3  | Carol | [carol@mail.com](mailto:carol@mail.com) | active   |
那么 active_users 視圖返回：
| id | name  | email                                   |
| -- | ----- | --------------------------------------- |
| 1  | Alice | [alice@mail.com](mailto:alice@mail.com) |
| 3  | Carol | [carol@mail.com](mailto:carol@mail.com) |

注意：如果你沒有顯式使用 TEMP 或 TEMPORARY 關(guān)鍵字，那么你創(chuàng)建的視圖就是持久視圖

刪除視圖：

DROP VIEW active_users;

臨時表（TEMP TABLE）

臨時表是只在當(dāng)前數(shù)據(jù)庫連接中可見的表，連接關(guān)閉后自動銷毀。它們的主要目的是用于臨時數(shù)據(jù)的存儲與處理，不污染正式的數(shù)據(jù)表結(jié)構(gòu)。它的存在意義主要在于以下幾點：

1. 存放中間結(jié)果，簡化復(fù)雜操作

在處理多步 SQL 邏輯（如報表、分析、批量更新）時，臨時表可以存放中間結(jié)果，讓后續(xù)查詢更清晰：

CREATE TEMP TABLE temp_summary AS
SELECT user_id, SUM(amount) AS total
FROM orders
GROUP BY user_id;

然后你可以繼續(xù)基于 temp_summary 做篩選、排序等操作。

2. 提高性能，避免重復(fù)計算

有些數(shù)據(jù)在多個地方會用到，而計算代價較高（例如聚合、大量連接），你可以先寫入臨時表，然后反復(fù)查詢：

-- 避免重復(fù) JOIN 操作，提高整體查詢速度
SELECT * FROM temp_result WHERE score > 80;

3.并發(fā)安全，每個連接互不干擾

SQLite 的臨時表是連接隔離的：

多個連接可以使用同名臨時表
它們之間的數(shù)據(jù)互不影響

這使得臨時表非常適合多線程/多連接場景下的臨時數(shù)據(jù)隔離處理

總結(jié)：臨時表的作用是為當(dāng)前連接提供一個安全、高效、隔離的臨時數(shù)據(jù)空間，專注于中間處理、性能優(yōu)化與調(diào)試而不影響正式數(shù)據(jù)庫結(jié)構(gòu)與數(shù)據(jù)。

創(chuàng)建臨時表：

CREATE TEMP TABLE temp_table_name (
    column1 TYPE,
    column2 TYPE,
    ...
);

臨時表只在當(dāng)前數(shù)據(jù)庫連接中有效
連接關(guān)閉后自動刪除
臨時表與視圖不同，它是真實存儲數(shù)據(jù)的表，只是生命周期短

示例：創(chuàng)建并使用一個臨時表

CREATE TEMP TABLE temp_orders (
    id INTEGER,
    product TEXT,
    quantity INTEGER
);
INSERT INTO temp_orders VALUES (1, 'Book', 2);
INSERT INTO temp_orders VALUES (2, 'Pen', 5);
SELECT * FROM temp_orders;
查詢結(jié)果（臨時表內(nèi)容）：
| id | product | quantity |
| -- | ------- | -------- |
| 1  | Book    | 2        |
| 2  | Pen     | 5        |

視圖 vs 臨時表

項目	視圖（VIEW）	臨時表（TEMP TABLE）
本質(zhì)	基于 `SELECT` 的虛擬表，不存儲數(shù)據(jù)	存儲真實數(shù)據(jù)的臨時性表
是否持久存在	是（除非使用 `TEMP` 創(chuàng)建）	否，只在當(dāng)前連接中存在，斷開即銷毀
數(shù)據(jù)存儲	不存儲數(shù)據(jù)，每次使用實時查詢底層表	存儲數(shù)據(jù)，像普通表一樣支持增刪改查
創(chuàng)建語法	`CREATE [TEMP] VIEW view_name AS ...`	`CREATE TEMP TABLE table_name (...)`
刪除方式	`DROP VIEW view_name;`	自動銷毀（連接關(guān)閉）或手動 `DROP TABLE`
生命周期	持久（數(shù)據(jù)庫文件的一部分）	連接會話級，連接斷開即清除
可更新性	只讀（除非符合可更新視圖條件）	可讀可寫，完全等同于普通表
典型用途	封裝復(fù)雜查詢、簡化 SQL、權(quán)限控制	存儲中間數(shù)據(jù)、性能優(yōu)化、測試臨時數(shù)據(jù)
是否支持索引	否（依賴底層表索引）	是（可為臨時表單獨建索引）
作用范圍	所有連接（持久視圖）或當(dāng)前連接（TEMP）	當(dāng)前連接
是否寫入磁盤	是（除 `TEMP VIEW`）	否（僅存儲在內(nèi)存或臨時磁盤空間）

常用函數(shù)與表達式

字符串處理函數(shù)

函數(shù)名	功能說明	示例 SQL	返回結(jié)果
`length(X)`	返回字符串 `X` 的字符長度	`SELECT length('SQLite');`	`6`
`substr(X,Y,Z)`	提取 `X` 中從第 `Y` 位開始的 `Z` 個字符	`SELECT substr('SQLite3', 2, 4);`	`'QLit'`
`lower(X)` / `upper(X)`	轉(zhuǎn)換為小寫 / 大寫	`SELECT upper('abc');`	`'ABC'`
`trim(X)`	去除前后空白字符	`SELECT trim(' abc ');`	`'abc'`
`replace(X,Y,Z)`	將 `X` 中所有 `Y` 替換為 `Z`	`SELECT replace('hello', 'l', 'L');`	`'heLLo'`
`instr(X, Y)`	查找 `Y` 在 `X` 中首次出現(xiàn)的位置（1 開始）	`SELECT instr('abcdef', 'cd');`	`3`
`printf(FMT, ...)`	格式化字符串，類似 C 的 `printf`	`SELECT printf('%.2f', 3.14159);`	`'3.14'`
`hex(X)`	將字符串或 BLOB 轉(zhuǎn)為十六進制表示	`SELECT hex('abc');`	`'616263'`

數(shù)值函數(shù)

函數(shù)名	功能說明	示例 SQL	返回結(jié)果
`abs(X)`	絕對值	`SELECT abs(-10);`	`10`
`round(X[,Y])`	四舍五入到 `Y` 位小數(shù)，默認(rèn) 0	`SELECT round(3.14159, 2);`	`3.14`
`random()`	返回一個大范圍隨機整數(shù)	`SELECT random();`	隨機整數(shù)
`random() % N`	控制隨機值范圍（常配合 `abs` 使用）	`SELECT abs(random() % 10);`	`0 ~ 9`
`typeof(X)`	返回數(shù)據(jù)類型（如 integer, text）	`SELECT typeof(3.14);`	`'real'`
`coalesce(X, Y, ...)`	返回第一個非 NULL 的值	`SELECT coalesce(NULL, '', 'abc');`	`''`
`nullif(X, Y)`	如果 `X == Y`，返回 NULL，否則返回 X	`SELECT nullif(5, 5);`	`NULL`
`sign(X)`	不內(nèi)置，可用 CASE 模擬，判斷數(shù)正負(fù)	`SELECT CASE WHEN X > 0 THEN 1 WHEN X < 0 THEN -1 ELSE 0 END`	`-1 / 0 / 1`

日期與時間函數(shù)

函數(shù)名	功能說明	示例 SQL	返回結(jié)果
`date('now')`	當(dāng)前日期	`SELECT date('now');`	`2025-05-08`
`datetime('now')`	當(dāng)前日期時間	`SELECT datetime('now');`	`2025-05-08 13:50:00`
`time('now')`	當(dāng)前時間（不含日期）	`SELECT time('now');`	`13:50:00`
`strftime('%Y-%m-%d', 'now')`	日期格式化輸出	`SELECT strftime('%Y-%m-%d', 'now');`	`2025-05-08`
`strftime('%s', 'now')`	當(dāng)前時間戳（秒）	`SELECT strftime('%s', 'now');`	`UNIX 時間戳`
`strftime('%w', 'now')`	星期幾（0 表示周日）	`SELECT strftime('%w', 'now');`	`4`（周四）
`julianday('now')`	當(dāng)前日期的儒略日表示法（浮點）	`SELECT julianday('now');`	`2460451.08`
`datetime('now', '+7 days')`	時間加減（也支持 `-2 hours`, `+1 month` 等）	`SELECT datetime('now', '-1 day');`	昨天的時間

聚合函數(shù)

函數(shù)名	功能說明	示例 SQL	返回結(jié)果
`COUNT(X)`	非 NULL 值數(shù)量	`SELECT COUNT(name) FROM users;`	`42`（示例）
`COUNT(*)`	所有行數(shù)量	`SELECT COUNT(*) FROM users;`	`100`
`SUM(X)`	求和	`SELECT SUM(price) FROM orders;`	`2300.50`
`AVG(X)`	平均值	`SELECT AVG(score) FROM exams;`	`82.5`
`MAX(X)`	最大值	`SELECT MAX(age) FROM people;`	`64`
`MIN(X)`	最小值	`SELECT MIN(age) FROM people;`	`18`

條件表達式

表達式	功能說明	示例 SQL	返回結(jié)果
`CASE WHEN ... THEN ...`	條件判斷（if-else）	`SELECT CASE WHEN score > 90 THEN '優(yōu)' WHEN score > 60 THEN '中' ELSE '差' END`	`'優(yōu)' / '中' / '差'`
`CASE X WHEN A THEN ...`	值匹配（更緊湊形式）	`SELECT CASE grade WHEN 'A' THEN 4 WHEN 'B' THEN 3 ELSE 0 END`	`4 / 3 / 0`
`coalesce(X, Y, Z)`	返回第一個非 NULL 值	`SELECT coalesce(NULL, NULL, 'hello');`	`'hello'`
`nullif(X, Y)`	如果 `X == Y` 則返回 NULL，否則返回 X	`SELECT nullif(5, 5);`	`NULL`
`IFNULL(X, Y)`	如果 X 是 NULL，則返回 Y，否則返回 X（別名）	`SELECT ifnull(NULL, 'default');`	`'default'`

示例：

下面是一個綜合性 SQL 示例，它模擬了一個電商訂單分析的場景

建表：

-- 創(chuàng)建客戶表
CREATE TABLE customers (
    id INTEGER PRIMARY KEY,           -- 客戶 ID，主鍵
    name TEXT,                        -- 客戶名稱
    email TEXT                        -- 客戶郵箱
);
-- 創(chuàng)建訂單表
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,           -- 訂單 ID，主鍵
    customer_id INTEGER,              -- 關(guān)聯(lián)客戶 ID
    product_name TEXT,                -- 商品名稱
    price REAL,                       -- 商品單價
    quantity INTEGER,                 -- 購買數(shù)量
    order_date TEXT,                  -- 下單時間（格式：YYYY-MM-DD HH:MM:SS）
    FOREIGN KEY (customer_id) REFERENCES customers(id)  -- 外鍵關(guān)聯(lián)客戶表
);

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

-- 插入客戶
INSERT INTO customers (id, name, email) VALUES
(1, 'Alice', 'alice@example.com'),
(2, 'Bob', 'bob@example.net'),
(3, 'Charlie', 'charlie@example.org');
-- 插入訂單
INSERT INTO orders (customer_id, product_name, price, quantity, order_date) VALUES
(1, 'Laptop', 899.99, 1, '2025-01-15 10:00:00'),
(1, 'Mouse', 19.99, 2, '2025-02-10 12:30:00'),
(2, 'Keyboard', 49.99, 1, '2025-03-05 14:20:00'),
(2, 'Monitor', 199.99, 1, '2025-03-06 15:10:00'),
(2, 'USB Cable', 9.99, 3, '2025-04-01 09:00:00'),
(3, 'Desk Chair', 129.99, 1, '2025-01-22 16:00:00');

sql

SELECT
    c.name AS customer_name,  -- 客戶名稱
    upper(substr(c.email, 1, instr(c.email, '@') - 1)) AS email_user,  
        -- 提取 email @ 前部分并轉(zhuǎn)為大寫
    COUNT(o.id) AS total_orders,  -- 訂單總數(shù)
    SUM(o.price * o.quantity) AS total_spent,  -- 總消費金額
    round(AVG(o.price * o.quantity), 2) AS avg_order_value,  -- 平均訂單金額（保留2位小數(shù)）
    MAX(o.order_date) AS last_order_date,  -- 最后一筆訂單的時間
    strftime('%Y-%m', o.order_date) AS order_month,  -- 訂單月份（用于聚合）
    -- 消費金額區(qū)間分級：VIP / Gold / Regular
    CASE 
        WHEN SUM(o.price * o.quantity) > 1000 THEN 'VIP'
        WHEN SUM(o.price * o.quantity) > 500 THEN 'Gold'
        ELSE 'Regular'
    END AS customer_level
FROM
    customers c
LEFT JOIN
    orders o ON c.id = o.customer_id  -- 關(guān)聯(lián)訂單表
WHERE
    o.order_date >= date('now', '-6 months')  -- 僅查詢最近6個月的訂單
GROUP BY
    c.id
HAVING
    total_orders > 0  -- 排除沒有訂單的客戶
ORDER BY
    total_spent DESC  -- 按總消費金額降序排列
LIMIT 10;  -- 僅顯示前10個客戶

SQLite 專有特性

AUTOINCREMENT 和 INTEGER PRIMARY KEY

INTEGER PRIMARY KEY 是 SQLite 中用于定義主鍵并且自動增長的特殊類型。
如果你定義了某個列為 INTEGER PRIMARY KEY，當(dāng)向表中插入一行數(shù)據(jù)時SQLite 會自動為該列賦值（自增），無需顯式使用 AUTOINCREMENT。
AUTOINCREMENT 是一種“更嚴(yán)格”的版本，它會防止重復(fù)使用已刪除的 ID。

特性	`INTEGER PRIMARY KEY`	`INTEGER PRIMARY KEY AUTOINCREMENT`
自動增長	是	是
會復(fù)用已刪除的 ID？	會	不會
是否推薦？	推薦（性能更好）	不推薦，除非必須保證唯一不復(fù)用

示例：

-- 普通自增主鍵
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT
);
-- 帶 AUTOINCREMENT 的主鍵
CREATE TABLE logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    message TEXT
);

WITHOUT ROWID 表

SQLite 默認(rèn)使用一個隱藏的 rowid 來標(biāo)識每一行。但你可以顯式使用 WITHOUT ROWID 表來：

減少存儲開銷（適合復(fù)合主鍵場景）
提高某些查詢性能（尤其當(dāng)不需要 rowid 時）

示例：

-- 默認(rèn)帶有 rowid
CREATE TABLE cities (
    name TEXT PRIMARY KEY,
    population INTEGER
);
-- 不使用 rowid
CREATE TABLE cities_norowid (
    name TEXT PRIMARY KEY,
    population INTEGER
) WITHOUT ROWID;

說明：WITHOUT ROWID 表要求必須有主鍵，且主鍵不可為 ROWID。

PRAGMA 指令

PRAGMA 是 SQLite 的一組特殊命令，用于查看或設(shè)置數(shù)據(jù)庫的內(nèi)部參數(shù)或行為。

指令	用途說明	示例
`PRAGMA table_info(table_name)`	查看表結(jié)構(gòu)（字段信息）	`PRAGMA table_info(users);`
`PRAGMA foreign_keys`	查看外鍵是否啟用（1 為開啟）	`PRAGMA foreign_keys;`
`PRAGMA foreign_keys = ON;`	啟用外鍵約束	`PRAGMA foreign_keys = ON;`
`PRAGMA database_list`	查看當(dāng)前連接的數(shù)據(jù)庫列表	`PRAGMA database_list;`
`PRAGMA index_list(table_name)`	查看表上的索引列表	`PRAGMA index_list(users);`
`PRAGMA cache_size`	設(shè)置或查看內(nèi)存頁緩存大小	`PRAGMA cache_size = 2000;`
`PRAGMA journal_mode`	設(shè)置事務(wù)日志模式（如 WAL）	`PRAGMA journal_mode = WAL;`
`PRAGMA synchronous`	控制同步級別（性能 vs 安全）	`PRAGMA synchronous = NORMAL;`