SQLite3基本介紹與常用語(yǔ)句匯總(最新整理)
SQLite3簡(jiǎn)介
SQLite3是一款輕量級(jí)的、基于文件的開(kāi)源關(guān)系型數(shù)據(jù)庫(kù)引擎，由 D. Richard Hipp 于 2000 年首次發(fā)布。它遵循 SQL 標(biāo)準(zhǔn)，但與傳統(tǒng)的數(shù)據(jù)庫(kù)系統(tǒng)不同，SQLite 并不運(yùn)行在獨(dú)立的服務(wù)器進(jìn)程中，而是作為一個(gè)嵌入式數(shù)據(jù)庫(kù)引擎直接集成到應(yīng)用程序中。其所有的數(shù)據(jù)結(jié)構(gòu)（包括表、索引、事務(wù)日志等）都保存在一個(gè)單一的 .db 文件中。
SQLite 的設(shè)計(jì)理念是“零配置、開(kāi)箱即用”，開(kāi)發(fā)者只需將其動(dòng)態(tài)庫(kù)鏈接進(jìn)應(yīng)用程序，就可以直接進(jìn)行數(shù)據(jù)庫(kù)操作，無(wú)需安裝數(shù)據(jù)庫(kù)服務(wù)或進(jìn)行網(wǎng)絡(luò)配置。
SQLite3 是 SQLite 的第三個(gè)主要版本，相較前代有更強(qiáng)的兼容性和更完整的 SQL 支持，是目前最常用的版本。
SQLite3的特點(diǎn)
- 輕量嵌入式設(shè)計(jì)
SQLite3 不依賴服務(wù)器進(jìn)程，僅作為應(yīng)用的一部分存在；編譯后的庫(kù)小于 1MB，運(yùn)行開(kāi)銷極低。
- 單文件存儲(chǔ)結(jié)構(gòu)
所有數(shù)據(jù)庫(kù)內(nèi)容都保存在一個(gè)磁盤文件中，便于復(fù)制、遷移和版本控制。
- 跨平臺(tái)支持廣泛
可以在 Linux、Windows、macOS、Android、iOS 等操作系統(tǒng)中運(yùn)行，源代碼可編譯到幾乎所有主流平臺(tái)。
- 兼容標(biāo)準(zhǔn) SQL92
盡管體積小，但 SQLite3 支持大部分標(biāo)準(zhǔn) SQL 語(yǔ)法，如事務(wù)、子查詢、視圖、觸發(fā)器、聚合函數(shù)等。
- 零配置，無(wú)需安裝
無(wú)需安裝或初始化數(shù)據(jù)庫(kù)，只要程序能訪問(wèn)數(shù)據(jù)庫(kù)文件就可以使用。
- 事務(wù)完整性（ACID）支持
SQLite3 保證事務(wù)的原子性、一致性、隔離性和持久性，適用于數(shù)據(jù)完整性要求較高的應(yīng)用。
SQLite3的適用場(chǎng)景
SQLite3 由于其嵌入式、便攜、小巧的特性，特別適用于以下場(chǎng)景：
- 移動(dòng)應(yīng)用開(kāi)發(fā)（Android/iOS）
SQLite3 是 Android 系統(tǒng)默認(rèn)數(shù)據(jù)庫(kù)，適合存儲(chǔ)用戶數(shù)據(jù)、緩存內(nèi)容、離線功能等。
- 嵌入式系統(tǒng) / IoT 設(shè)備
如智能電視、車載系統(tǒng)、傳感器節(jié)點(diǎn)等設(shè)備內(nèi)存和性能有限，SQLite 是輕量數(shù)據(jù)存儲(chǔ)的理想方案。
- 桌面軟件
常用于辦公類軟件（如記事本、財(cái)務(wù)管理工具）中提供本地?cái)?shù)據(jù)存儲(chǔ)功能。
- 瀏覽器或前端環(huán)境
Web 應(yīng)用中的 IndexedDB/LocalStorage 常借助 SQLite 作為底層數(shù)據(jù)庫(kù)。
- 單用戶或低并發(fā)系統(tǒng)
適合使用場(chǎng)景為單人或單線程訪問(wèn)，例如個(gè)人記賬軟件、本地日志記錄系統(tǒng)等。
- 快速原型開(kāi)發(fā)和測(cè)試
因?yàn)槊獍惭b、部署簡(jiǎn)單，SQLite 常被用于開(kāi)發(fā)早期快速迭代和測(cè)試環(huán)境中。
- 嵌套系統(tǒng)中的緩存數(shù)據(jù)庫(kù)
可作為大型數(shù)據(jù)庫(kù)系統(tǒng)的本地緩存，提升訪問(wèn)性能，降低服務(wù)器負(fù)載。
SQLite 命令行工具（sqlite3 shell） 中的內(nèi)置命令
| 命令 | 作用說(shuō)明 |
|---|---|
.open filename.db | 打開(kāi)或創(chuàng)建一個(gè) SQLite 數(shù)據(jù)庫(kù)文件 |
.tables | 列出當(dāng)前數(shù)據(jù)庫(kù)中的所有表 |
.schema [table] | 查看某個(gè)表或所有表的建表語(yǔ)句（DDL） |
.headers ON/OFF | 開(kāi)啟或關(guān)閉結(jié)果顯示中的列標(biāo)題 |
.read filename.sql | 執(zhí)行指定的 SQL 文件內(nèi)容 |
.exit / .quit | 退出 SQLite 命令行 |
.databases | 查看當(dāng)前連接的數(shù)據(jù)庫(kù)文件 |
.nullvalue NULL_REPLACEMENT | 設(shè)置 NULL 顯示為什么字符串 |
.output filename.txt | 將查詢結(jié)果輸出到文件 |
基本操作語(yǔ)句
1.打開(kāi)/創(chuàng)建數(shù)據(jù)庫(kù)文件
SQLite 使用命令行或程序語(yǔ)言（如 Python、C 等）調(diào)用 SQLite 引擎來(lái)打開(kāi)或創(chuàng)建數(shù)據(jù)庫(kù)文件。文件不存在時(shí)會(huì)自動(dòng)創(chuàng)建。
sqlite3 mydatabase.db
該命令會(huì)在當(dāng)前目錄中創(chuàng)建一個(gè)名為 mydatabase.db 的數(shù)據(jù)庫(kù)文件（如果尚不存在），并進(jìn)入 SQLite 的交互式終端。你可以在里面執(zhí)行 SQL 命令。
2. 查看數(shù)據(jù)庫(kù)中所有表
SELECT name FROM sqlite_master WHERE type='table';
或者使用 SQLite 命令行工具提供的快捷命令：
.tables
3. 查看表結(jié)構(gòu)（PRAGMA 語(yǔ)句）
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],
...
);用于定義一個(gè)新的數(shù)據(jù)表，并指定字段名、數(shù)據(jù)類型和約束（如主鍵、非空等）。
CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
age INTEGER
);補(bǔ)充：查看某個(gè)表的建表語(yǔ)句. 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;
示例：添加一個(gè) 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
);你會(huì)發(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 沒(méi)有 CREATE TABLE ... LIKE 語(yǔ)法，可以用以下方式復(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ù)操作語(yǔ)句
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，會(huì)刪除整張表的數(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; -- 空表，無(wú)結(jié)果
4. 查詢數(shù)據(jù)（SELECT）
SELECT column1, column2, ... FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...];
SELECT * 表示查詢所有列。
查詢進(jìn)階
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. 分組與過(guò)濾（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. 分頁(yè)查詢（LIMIT / OFFSET）
SELECT * FROM table_name LIMIT 限制行數(shù) OFFSET 起始行偏移量; 指令說(shuō)明 LIMIT：限制最多返回多少行結(jié)果。 OFFSET：跳過(guò)前面多少行數(shù)據(jù)再開(kāi)始返回（可選）。
例如在一個(gè)頁(yè)面中只顯示 10 條數(shù)據(jù)，就可以：
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 0; -- 第1頁(yè) SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 10; -- 第2頁(yè) SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20; -- 第3頁(yè)
或者用更常見(jiàn)的公式：
LIMIT 每頁(yè)條數(shù) OFFSET (頁(yè)碼 - 1) * 每頁(yè)條數(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 排序后，跳過(guò)第1條數(shù)據(jù)，從第2條開(kāi)始取，最多取2條。 執(zhí)行結(jié)果： id | name | age ---+-------+----- 2 | Bob | 30 3 | Carol | 28
索引與性能
1.sqlite3中的索引是什么？
在 SQLite 中，索引是一種數(shù)據(jù)庫(kù)對(duì)象，它的作用類似于書(shū)本的目錄，可以加快查詢特定數(shù)據(jù)的速度。索引會(huì)為一個(gè)或多個(gè)列生成一個(gè)排序的數(shù)據(jù)結(jié)構(gòu)（通常是 B-tree），從而使查詢更快。
2.索引的特性？
加速查詢（尤其是 WHERE、JOIN、ORDER BY 等）
當(dāng)你查詢某張表時(shí)：
SELECT * FROM users WHERE age > 25;
如果 age 上有索引，SQLite 會(huì)用索引快速定位符合條件的數(shù)據(jù)，而不用全表掃描。
提升排序效率
SELECT * FROM users ORDER BY name;
如果 name 列已建索引，排序可以直接利用索引順序完成，而無(wú)需臨時(shí)排序。
加速多表連接（JOIN）
SELECT * FROM users JOIN orders ON users.id = orders.user_id;
如果 orders.user_id 建了索引，那么連接時(shí)匹配效率會(huì)更高。
不適用于頻繁變動(dòng)的字段
索引雖然能加速查詢，但會(huì)減慢 INSERT、UPDATE、DELETE 的性能，因?yàn)?strong>每次數(shù)據(jù)改動(dòng)，索引也要同步更新。
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>?) 說(shuō)明查詢使用了你創(chuàng)建的索引。
為多列創(chuàng)建聯(lián)合索引
CREATE INDEX index_name ON table_name(column1, column2, ...);
適用于查詢中使用多個(gè)字段組合的情況。
遵守“最左前綴原則”
示例：
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ù)合索引只有在查詢中使用了從左到右的“最前面的列”時(shí)，SQLite 才會(huì)使用該索引來(lái)優(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 樹(shù)結(jié)構(gòu)。查詢必須從最左的列開(kāi)始匹配，否則無(wú)法用上這個(gè)索引。
4. 刪除索引（DROP INDEX）
DROP INDEX [IF EXISTS] index_name;
事務(wù)控制
1. 開(kāi)始事務(wù)（BEGIN）
BEGIN;
- 用于開(kāi)始一個(gè)事務(wù)。在事務(wù)開(kāi)始后，所有的操作（如
INSERT、UPDATE、DELETE）都將在這個(gè)事務(wù)中進(jìn)行。 - 如果事務(wù)內(nèi)的操作沒(méi)有出現(xiàn)錯(cuò)誤，事務(wù)可以被提交（
COMMIT）。如果出錯(cuò)，可以回滾（ROLLBACK）整個(gè)事務(wù)。
2. 提交事務(wù)（COMMIT）
COMMIT;
提交當(dāng)前事務(wù)所做的所有更改。這會(huì)將事務(wù)中所有修改的數(shù)據(jù)寫入數(shù)據(jù)庫(kù)并使它們永久生效。
示例：提交事務(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í)行某些操作時(shí)發(fā)生錯(cuò)誤，可以使用 ROLLBACK 來(lái)撤銷所有在當(dāng)前事務(wù)中的操作，恢復(fù)到事務(wù)開(kāi)始前的狀態(tài)。
示例：回滾事務(wù)
BEGIN;
INSERT INTO users (name, age, city) VALUES ('Eve', 40, 'Chengdu');
UPDATE users SET age = 45 WHERE name = 'Alice';
-- 假設(shè)此時(shí)發(fā)生了錯(cuò)誤，我們決定回滾事務(wù)
ROLLBACK;事務(wù)回滾后，Eve 和 Alice 的更新都將被撤銷，users 表中的數(shù)據(jù)保持不變。
4. 自動(dòng)提交模式
- 在默認(rèn)情況下，SQLite 在每個(gè)獨(dú)立的 SQL 語(yǔ)句后自動(dòng)提交。也就是說(shuō)，每次執(zhí)行一條語(yǔ)句時(shí)，SQLite 會(huì)自動(dòng)把它作為一個(gè)單獨(dú)的事務(wù)提交。
- 為了防止自動(dòng)提交，可以顯式地使用
BEGIN開(kāi)始事務(wù)，直到使用COMMIT或ROLLBACK。
5. 提交或回滾事務(wù)的應(yīng)用場(chǎng)景
- 批量操作： 比如一次性插入大量數(shù)據(jù)，使用事務(wù)能夠保證所有數(shù)據(jù)同時(shí)插入成功，避免數(shù)據(jù)不一致。
- 處理失敗的操作： 在多步操作中，如果中途某一步失敗，
ROLLBACK可以保證整個(gè)操作的原子性。
事務(wù)控制的典型應(yīng)用場(chǎng)景：
假設(shè)有一個(gè)轉(zhuǎn)賬操作，其中兩個(gè)表分別是 accounts（賬戶余額）和 transactions（交易記錄），我們需要確保轉(zhuǎn)賬操作成功或者完全回滾。
try {
executeOrThrow(db, "BEGIN;");
executeOrThrow(db, "UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A001';");
// 故意出錯(cuò)：列名 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);");
//沒(méi)問(wèn)題就提交
executeOrThrow(db, "COMMIT;");
std::cout << "Transaction committed.\n";
} catch (const std::exception& ex) {
std::cerr << ex.what() << "\n";
//出錯(cuò)回滾
sqlite3_exec(db, "ROLLBACK;", nullptr, nullptr, nullptr);
std::cout << "Transaction rolled back.\n";
}視圖與臨時(shí)表
視圖（VIEW）
視圖是 虛擬的表，本質(zhì)上是對(duì)一個(gè) SELECT 查詢結(jié)果的封裝，它本身不存儲(chǔ)數(shù)據(jù)，而是每次訪問(wèn)時(shí)執(zhí)行背后的查詢語(yǔ)句。它的存在意義主要在于以下幾點(diǎn)：
1. 簡(jiǎn)化復(fù)雜查詢
當(dāng)你有一些經(jīng)常要執(zhí)行的復(fù)雜 JOIN、子查詢 或 聚合查詢 時(shí)，把它們寫進(jìn)視圖，可以像操作普通表一樣簡(jiǎn)單調(diào)用：
-- 查詢最近30天訂單金額前10的用戶 SELECT * FROM top_users_last_30_days;
而不用每次都寫長(zhǎng)查詢。
一個(gè)視圖可以作為多個(gè)后續(xù)查詢的中間層，避免重復(fù) JOIN 和 GROUP BY 邏輯，提高可復(fù)用性和效率。
2. 增強(qiáng)可讀性與可維護(hù)性
把復(fù)雜查詢邏輯隱藏到視圖中后，業(yè)務(wù) SQL 更清晰：
-- 直接查視圖 SELECT * FROM user_purchases_summary WHERE total_spent > 1000;
而不是寫重復(fù)的 SQL 邏輯多處維護(hù)。
3.提高安全性
你可以只授予用戶對(duì)視圖的訪問(wèn)權(quán)限，而非對(duì)底層表的權(quán)限，從而達(dá)到權(quán)限隔離的效果。
創(chuàng)建視圖：
CREATE [TEMP | TEMPORARY] VIEW view_name AS SELECT ...; view_name：視圖名稱 SELECT ...：視圖對(duì)應(yīng)的查詢語(yǔ)句 TEMP：可選，創(chuàng)建臨時(shí)視圖，僅在當(dāng)前連接中可見(jiàn)
示例：創(chuàng)建一個(gè)只讀用戶信息視圖
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) |
注意：如果你沒(méi)有顯式使用 TEMP 或 TEMPORARY 關(guān)鍵字，那么你創(chuàng)建的視圖就是持久視圖
刪除視圖：
DROP VIEW active_users;
臨時(shí)表（TEMP TABLE）
臨時(shí)表是只在當(dāng)前數(shù)據(jù)庫(kù)連接中可見(jiàn)的表，連接關(guān)閉后自動(dòng)銷毀。它們的主要目的是用于臨時(shí)數(shù)據(jù)的存儲(chǔ)與處理，不污染正式的數(shù)據(jù)表結(jié)構(gòu)。它的存在意義主要在于以下幾點(diǎn)：
1. 存放中間結(jié)果，簡(jiǎn)化復(fù)雜操作
在處理多步 SQL 邏輯（如報(bào)表、分析、批量更新）時(shí)，臨時(shí)表可以存放中間結(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ù)計(jì)算
有些數(shù)據(jù)在多個(gè)地方會(huì)用到，而計(jì)算代價(jià)較高（例如聚合、大量連接），你可以先寫入臨時(shí)表，然后反復(fù)查詢：
-- 避免重復(fù) JOIN 操作，提高整體查詢速度 SELECT * FROM temp_result WHERE score > 80;
3.并發(fā)安全，每個(gè)連接互不干擾
SQLite 的臨時(shí)表是連接隔離的：
- 多個(gè)連接可以使用同名臨時(shí)表
- 它們之間的數(shù)據(jù)互不影響
這使得臨時(shí)表非常適合多線程/多連接場(chǎng)景下的臨時(shí)數(shù)據(jù)隔離處理
總結(jié)：臨時(shí)表的作用是為當(dāng)前連接提供一個(gè)安全、高效、隔離的臨時(shí)數(shù)據(jù)空間，專注于中間處理、性能優(yōu)化與調(diào)試而不影響正式數(shù)據(jù)庫(kù)結(jié)構(gòu)與數(shù)據(jù)。
創(chuàng)建臨時(shí)表：
CREATE TEMP TABLE temp_table_name (
column1 TYPE,
column2 TYPE,
...
);- 臨時(shí)表只在當(dāng)前數(shù)據(jù)庫(kù)連接中有效
- 連接關(guān)閉后自動(dòng)刪除
- 臨時(shí)表與視圖不同，它是真實(shí)存儲(chǔ)數(shù)據(jù)的表，只是生命周期短
示例：創(chuàng)建并使用一個(gè)臨時(shí)表
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é)果（臨時(shí)表內(nèi)容）：
| id | product | quantity |
| -- | ------- | -------- |
| 1 | Book | 2 |
| 2 | Pen | 5 |視圖 vs 臨時(shí)表
| 項(xiàng)目 | 視圖（VIEW） | 臨時(shí)表（TEMP TABLE） |
|---|---|---|
| 本質(zhì) | 基于 SELECT 的虛擬表，不存儲(chǔ)數(shù)據(jù) | 存儲(chǔ)真實(shí)數(shù)據(jù)的臨時(shí)性表 |
| 是否持久存在 | 是（除非使用 TEMP 創(chuàng)建） | 否，只在當(dāng)前連接中存在，斷開(kāi)即銷毀 |
| 數(shù)據(jù)存儲(chǔ) | 不存儲(chǔ)數(shù)據(jù)，每次使用實(shí)時(shí)查詢底層表 | 存儲(chǔ)數(shù)據(jù)，像普通表一樣支持增刪改查 |
| 創(chuàng)建語(yǔ)法 | CREATE [TEMP] VIEW view_name AS ... | CREATE TEMP TABLE table_name (...) |
| 刪除方式 | DROP VIEW view_name; | 自動(dòng)銷毀（連接關(guān)閉）或手動(dòng) DROP TABLE |
| 生命周期 | 持久（數(shù)據(jù)庫(kù)文件的一部分） | 連接會(huì)話級(jí)，連接斷開(kāi)即清除 |
| 可更新性 | 只讀（除非符合可更新視圖條件） | 可讀可寫，完全等同于普通表 |
| 典型用途 | 封裝復(fù)雜查詢、簡(jiǎn)化 SQL、權(quán)限控制 | 存儲(chǔ)中間數(shù)據(jù)、性能優(yōu)化、測(cè)試臨時(shí)數(shù)據(jù) |
| 是否支持索引 | 否（依賴底層表索引） | 是（可為臨時(shí)表單獨(dú)建索引） |
| 作用范圍 | 所有連接（持久視圖）或當(dāng)前連接（TEMP） | 當(dāng)前連接 |
| 是否寫入磁盤 | 是（除 TEMP VIEW） | 否（僅存儲(chǔ)在內(nèi)存或臨時(shí)磁盤空間） |
常用函數(shù)與表達(dá)式
字符串處理函數(shù)
| 函數(shù)名 | 功能說(shuō)明 | 示例 SQL | 返回結(jié)果 |
|---|---|---|---|
length(X) | 返回字符串 X 的字符長(zhǎng)度 | SELECT length('SQLite'); | 6 |
substr(X,Y,Z) | 提取 X 中從第 Y 位開(kāi)始的 Z 個(gè)字符 | 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 開(kāi)始） | SELECT instr('abcdef', 'cd'); | 3 |
printf(FMT, ...) | 格式化字符串，類似 C 的 printf | SELECT printf('%.2f', 3.14159); | '3.14' |
hex(X) | 將字符串或 BLOB 轉(zhuǎn)為十六進(jìn)制表示 | SELECT hex('abc'); | '616263' |
數(shù)值函數(shù)
| 函數(shù)名 | 功能說(shuō)明 | 示例 SQL | 返回結(jié)果 |
|---|---|---|---|
abs(X) | 絕對(duì)值 | SELECT abs(-10); | 10 |
round(X[,Y]) | 四舍五入到 Y 位小數(shù)，默認(rèn) 0 | SELECT round(3.14159, 2); | 3.14 |
random() | 返回一個(gè)大范圍隨機(jī)整數(shù) | SELECT random(); | 隨機(jī)整數(shù) |
random() % N | 控制隨機(jī)值范圍（常配合 abs 使用） | SELECT abs(random() % 10); | 0 ~ 9 |
typeof(X) | 返回?cái)?shù)據(jù)類型（如 integer, text） | SELECT typeof(3.14); | 'real' |
coalesce(X, Y, ...) | 返回第一個(gè)非 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ù)
| 函數(shù)名 | 功能說(shuō)明 | 示例 SQL | 返回結(jié)果 |
|---|---|---|---|
date('now') | 當(dāng)前日期 | SELECT date('now'); | 2025-05-08 |
datetime('now') | 當(dāng)前日期時(shí)間 | SELECT datetime('now'); | 2025-05-08 13:50:00 |
time('now') | 當(dāng)前時(shí)間（不含日期） | 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)前時(shí)間戳（秒） | SELECT strftime('%s', 'now'); | UNIX 時(shí)間戳 |
strftime('%w', 'now') | 星期幾（0 表示周日） | SELECT strftime('%w', 'now'); | 4（周四） |
julianday('now') | 當(dāng)前日期的儒略日表示法（浮點(diǎn)） | SELECT julianday('now'); | 2460451.08 |
datetime('now', '+7 days') | 時(shí)間加減（也支持 -2 hours, +1 month 等） | SELECT datetime('now', '-1 day'); | 昨天的時(shí)間 |
聚合函數(shù)
| 函數(shù)名 | 功能說(shuō)明 | 示例 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 |
條件表達(dá)式
| 表達(dá)式 | 功能說(shuō)明 | 示例 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) | 返回第一個(gè)非 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' |
示例：
下面是一個(gè)綜合性 SQL 示例，它模擬了一個(gè)電商訂單分析的場(chǎng)景
建表：
-- 創(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, -- 商品單價(jià)
quantity INTEGER, -- 購(gòu)買數(shù)量
order_date TEXT, -- 下單時(shí)間（格式：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, -- 總消費(fèi)金額
round(AVG(o.price * o.quantity), 2) AS avg_order_value, -- 平均訂單金額（保留2位小數(shù)）
MAX(o.order_date) AS last_order_date, -- 最后一筆訂單的時(shí)間
strftime('%Y-%m', o.order_date) AS order_month, -- 訂單月份（用于聚合）
-- 消費(fèi)金額區(qū)間分級(jí)：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個(gè)月的訂單
GROUP BY
c.id
HAVING
total_orders > 0 -- 排除沒(méi)有訂單的客戶
ORDER BY
total_spent DESC -- 按總消費(fèi)金額降序排列
LIMIT 10; -- 僅顯示前10個(gè)客戶SQLite 專有特性
AUTOINCREMENT 和 INTEGER PRIMARY KEY
INTEGER PRIMARY KEY是 SQLite 中用于定義主鍵并且自動(dòng)增長(zhǎng)的特殊類型。- 如果你定義了某個(gè)列為
INTEGER PRIMARY KEY，當(dāng)向表中插入一行數(shù)據(jù)時(shí)SQLite 會(huì)自動(dòng)為該列賦值（自增），無(wú)需顯式使用AUTOINCREMENT。 AUTOINCREMENT是一種“更嚴(yán)格”的版本，它會(huì)防止重復(fù)使用已刪除的 ID。
| 特性 | INTEGER PRIMARY KEY | INTEGER PRIMARY KEY AUTOINCREMENT |
|---|---|---|
| 自動(dòng)增長(zhǎng) | 是 | 是 |
| 會(huì)復(fù)用已刪除的 ID？ | 會(huì) | 不會(huì) |
| 是否推薦？ | 推薦（性能更好） | 不推薦，除非必須保證唯一不復(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)使用一個(gè)隱藏的 rowid 來(lái)標(biāo)識(shí)每一行。但你可以顯式使用 WITHOUT ROWID 表來(lái)：
- 減少存儲(chǔ)開(kāi)銷（適合復(fù)合主鍵場(chǎng)景）
- 提高某些查詢性能（尤其當(dāng)不需要 rowid 時(shí)）
示例：
-- 默認(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;說(shuō)明：WITHOUT ROWID 表要求必須有主鍵，且主鍵不可為 ROWID。
PRAGMA 指令
PRAGMA 是 SQLite 的一組特殊命令，用于查看或設(shè)置數(shù)據(jù)庫(kù)的內(nèi)部參數(shù)或行為。
| 指令 | 用途說(shuō)明 | 示例 |
|---|---|---|
PRAGMA table_info(table_name) | 查看表結(jié)構(gòu)（字段信息） | PRAGMA table_info(users); |
PRAGMA foreign_keys | 查看外鍵是否啟用（1 為開(kāi)啟） | PRAGMA foreign_keys; |
PRAGMA foreign_keys = ON; | 啟用外鍵約束 | PRAGMA foreign_keys = ON; |
PRAGMA database_list | 查看當(dāng)前連接的數(shù)據(jù)庫(kù)列表 | PRAGMA database_list; |
PRAGMA index_list(table_name) | 查看表上的索引列表 | PRAGMA index_list(users); |
PRAGMA cache_size | 設(shè)置或查看內(nèi)存頁(yè)緩存大小 | PRAGMA cache_size = 2000; |
PRAGMA journal_mode | 設(shè)置事務(wù)日志模式（如 WAL） | PRAGMA journal_mode = WAL; |
PRAGMA synchronous | 控制同步級(jí)別（性能 vs 安全） | PRAGMA synchronous = NORMAL; |
相關(guān)文章
sQlite常用語(yǔ)句以及sQlite developer的使用與注冊(cè)
sQlite數(shù)據(jù)庫(kù)對(duì)大家來(lái)說(shuō)應(yīng)該都不陌生，下面這篇文章主要給大家介紹了關(guān)于sQlite常用語(yǔ)句以及sQlite developer使用與注冊(cè)的相關(guān)資料，文中通過(guò)示例代碼與圖片給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，下面來(lái)一起看看吧。2017-10-10
這篇文章主要介紹了SQLite教程（十一）：臨時(shí)文件,本文講解了七種臨時(shí)文件并對(duì)它們一一具體說(shuō)明,并相關(guān)的編譯時(shí)參數(shù)和指令、其它優(yōu)化策略等內(nèi)容,需要的朋友可以參考下2015-05-05
這兩天用了用SQLite,感覺(jué)比SQL CE好用多了,最主要的是性能好多了.這一點(diǎn)對(duì)手持設(shè)備來(lái)講很重要.閑話少說(shuō),下來(lái)將怎么在WM用.NET CF訪問(wèn)SQLite.2009-11-11
這篇文章主要介紹了SQLite中重置自動(dòng)編號(hào)列的方法,本文講解了3種情況和其對(duì)應(yīng)解決方法,需要的朋友可以參考下2015-03-03
Sqlite3 的確很好用。小巧、速度快。但是因?yàn)榉俏④浀漠a(chǎn)品，幫助文檔總覺(jué)得不夠。這些天再次研究它，又有一些收獲，這里把我對(duì) sqlite3 的研究列出來(lái)，以備忘記。也希望能對(duì)大家有所幫助。2014-08-08
SQLite教程（五）：索引和數(shù)據(jù)分析/清理
這篇文章主要介紹了SQLite教程（五）：索引和數(shù)據(jù)分析/清理,本文講解了創(chuàng)建索引、刪除索引、重建索引、數(shù)據(jù)分析、數(shù)據(jù)清理等內(nèi)容,需要的朋友可以參考下2015-05-05
基于sqlite特殊字符轉(zhuǎn)義的實(shí)現(xiàn)方法
本篇文章是對(duì)sqlite特殊字符轉(zhuǎn)義的實(shí)現(xiàn)方法進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下2013-05-05
這篇文章主要介紹了SQLite中的WAL機(jī)制詳細(xì)介紹,本文講解了什么是WAL、WAL如何工作、WAL的優(yōu)點(diǎn)與缺點(diǎn)、WAL引入的兼容性問(wèn)題、WAL引入的性能問(wèn)題等內(nèi)容,需要的朋友可以參考下2015-01-01
SQLite3數(shù)據(jù)庫(kù)訪問(wèn)性能優(yōu)化7個(gè)建議
SQLite的PRAGMA命令為開(kāi)發(fā)者提供了靈活的配置選項(xiàng),可以顯著提升數(shù)據(jù)庫(kù)的查詢和寫入性能,通過(guò)調(diào)整緩存大小、同步模式、WAL 模式、索引優(yōu)化等配置,你可以針對(duì)特定的應(yīng)用場(chǎng)景對(duì)SQLite進(jìn)行定制化優(yōu)化,合理使用PRAGMA命令,確保數(shù)據(jù)庫(kù)在高負(fù)載環(huán)境下的穩(wěn)定性和可靠性2025-03-03
SQLite學(xué)習(xí)手冊(cè)(SQLite在線備份)
在SQLite中提供了一組用于在線數(shù)據(jù)庫(kù)備份的APIs函數(shù)(C接口)，可以很好的解決上述方法存在的不足。通過(guò)該組函數(shù)，可以將源數(shù)據(jù)庫(kù)中的內(nèi)容拷貝到另一個(gè)數(shù)據(jù)庫(kù)，同時(shí)覆蓋目標(biāo)數(shù)據(jù)庫(kù)中的數(shù)據(jù)2013-12-12