SQLite 教程

SQLite 教程

SQLite 是一个轻量级的嵌入式关系型数据库，它不是一个独立的服务器进程，而是直接读写普通磁盘文件。SQLite 支持标准 SQL 的大部分语法，零配置、零依赖，被广泛应用于移动 App、桌面软件和小型网站。

SQLite 的特点

SQLite 简介

SQLite 由 D. Richard Hipp 于 2000 年发布，采用公共领域（Public Domain）许可，可免费用于任何项目。它是关系型数据库，数据存储在表中，表之间通过主键/外键关联。

与 MySQL 的区别

SQLite 安装

SQLite 安装非常简单：

Windows

# 从 https://www.sqlite.org/download.html 下载
# 解压后把 sqlite3.exe 加入 PATH
# 验证
sqlite3 --version

Linux

# Ubuntu/Debian
sudo apt install -y sqlite3

# CentOS
sudo yum install -y sqlite

# 验证
sqlite3 --version

macOS（自带）

# macOS 自带 sqlite3，直接可用
sqlite3 --version

# 或通过 Homebrew 升级
brew install sqlite

SQLite 命令

sqlite3 命令（命令行工具）常用操作：

# 打开/创建数据库
sqlite3 test.db

# 显示所有数据库
.databases

# 显示所有表
.tables

# 查看表结构
.schema users

# 执行 SQL
sqlite3 test.db "SELECT * FROM users;"

# 从文件执行 SQL
sqlite3 test.db < script.sql

# 导出/导入
sqlite3 test.db .dump > backup.sql
sqlite3 new.db < backup.sql

# 退出
.quit

SQLite 语法

SQLite 的 SQL 语法与其他数据库基本一致：

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

-- 插入
INSERT INTO users (name, age) VALUES ('张三', 25);

-- 查询
SELECT * FROM users;
SELECT name FROM users WHERE age > 20;

-- 更新
UPDATE users SET age = 26 WHERE name = '张三';

-- 删除
DELETE FROM users WHERE name = '张三';

-- 大小写不敏感
SELECT * FROM USERS;   -- 与 users 相同

SQLite 数据类型

SQLite 的存储类型（动态类型系统）：

-- SQLite 是"类型亲和"的：声明类型仅供参考
CREATE TABLE t (
    a INTEGER,
    b TEXT,
    c REAL
);

-- 插入不同类型的值也不会报错
INSERT INTO t VALUES ('not int', 123, 'text in real');

-- 查看类型
SELECT typeof(a), typeof(b), typeof(c) FROM t;

-- 类型转换
SELECT CAST('123' AS INTEGER);    -- 123
SELECT CAST(3.9 AS INTEGER);      -- 3（截断）

SQLite 创建数据库

SQLite 创建数据库：数据库就是一个文件，无需专门"创建"命令：

# 方式一：命令行直接创建
sqlite3 mydb.db
# 进入后执行任何 SQL 即创建文件

# 方式二：不进入交互模式
sqlite3 mydb.db "CREATE TABLE users(id INTEGER PRIMARY KEY, name TEXT);"

# Python 方式
import sqlite3
# 连接即创建（若文件不存在）
conn = sqlite3.connect('mydb.db')
conn.execute("CREATE TABLE users(id INTEGER PRIMARY KEY, name TEXT)")
conn.commit()
conn.close()

SQLite 附加数据库

ATTACH 附加数据库：允许在一个连接中同时操作多个数据库文件：

-- 附加一个数据库（别名 sales）
ATTACH DATABASE 'sales.db' AS sales;

-- 使用附加库的表（库名.表名）
SELECT * FROM sales.orders;
INSERT INTO sales.orders (item, qty) VALUES ('手机', 2);

-- 查看已附加的库
SELECT * FROM pragma_database_list;

-- 附加只读
ATTACH DATABASE 'archive.db' AS archive READ ONLY;

-- 分离数据库
DETACH DATABASE sales;

SQLite 分离数据库

DETACH 分离数据库：

-- 分离已附加的数据库
DETACH DATABASE sales;

-- 分离不存在的库会报错
DETACH DATABASE xxx;   -- error: no such database: xxx

-- 分离后无法再引用其表
SELECT * FROM sales.orders;   -- error

SQLite 创建表

CREATE TABLE 创建表：

-- 基本创建
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    age INTEGER DEFAULT 18,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

-- 带主键约束
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    amount REAL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- IF NOT EXISTS 防止重复创建
CREATE TABLE IF NOT EXISTS users (...);

-- 复制表结构
CREATE TABLE users_backup AS SELECT * FROM users;

-- 查看表结构
.schema users
-- 或
SELECT sql FROM sqlite_master WHERE name='users';

SQLite 删除表

DROP TABLE 删除表：

-- 删除表
DROP TABLE users;

-- 存在才删除（防报错）
DROP TABLE IF EXISTS users;

-- 删除多个表
DROP TABLE IF EXISTS users, orders;

-- 清空数据但保留表
DELETE FROM users;
-- 或重置自增
DELETE FROM sqlite_sequence WHERE name='users';

SQLite Insert 语句

INSERT 插入数据：

-- 插入单行
INSERT INTO users (name, age) VALUES ('张三', 25);

-- 省略列名（按表结构顺序）
INSERT INTO users VALUES (1, '李四', 30);

-- 插入多行
INSERT INTO users (name, age) VALUES
    ('王五', 22),
    ('赵六', 28),
    ('孙七', 35);

-- 插入查询结果
INSERT INTO users (name, age)
SELECT name, age FROM temp_users WHERE age > 20;

-- 插入或替换（冲突时先删后插）
INSERT OR REPLACE INTO users (id, name) VALUES (1, '新名字');

-- 插入或忽略（冲突时跳过）
INSERT OR IGNORE INTO users (id, name) VALUES (1, '不覆盖');

-- 获取最后插入的 ID
SELECT last_insert_rowid();

SQLite Select 语句

SELECT 查询数据（最常用）：

-- 查询所有列
SELECT * FROM users;

-- 查询指定列
SELECT name, age FROM users;

-- 去重
SELECT DISTINCT city FROM users;

-- 别名
SELECT name AS 姓名, age AS 年龄 FROM users;

-- 计算列
SELECT name, age, age * 2 AS double_age FROM users;

-- 条件
SELECT * FROM users WHERE age >= 18;

-- 排序 + 限制
SELECT * FROM users ORDER BY age DESC LIMIT 10;

-- 聚合
SELECT COUNT(*) FROM users;
SELECT city, AVG(age) FROM users GROUP BY city;

-- 字符串拼接
SELECT name || ' (' || age || '岁)' FROM users;

SQLite 运算符

SQLite 运算符：

-- 算术
SELECT 10 + 5, 10 - 5, 10 * 5, 10 / 3, 10 % 3;
-- 15  5  50  3  1

-- 比较（返回 1/0）
SELECT 5 > 3;    -- 1
SELECT 5 = 3;    -- 0

-- 逻辑
SELECT 1 AND 1;  -- 1
SELECT 1 OR 0;   -- 1
SELECT NOT 1;    -- 0

-- 特殊：NULL 判断用 IS
SELECT NULL = NULL;    -- NULL（不是 1！）
SELECT NULL IS NULL;   -- 1

SQLite 表达式

表达式（Expression）是 SQL 中的值计算单元：

-- 字面量
SELECT 42, 'hello', 3.14, NULL, X'0102';

-- 算术表达式
SELECT (1 + 2) * 3;         -- 9

-- 函数调用
SELECT abs(-5), length('hello'), upper('abc');

-- 列引用
SELECT name, age + 1 FROM users;

-- 条件表达式
SELECT CASE
    WHEN age >= 60 THEN '老年'
    WHEN age >= 18 THEN '成年'
    ELSE '未成年'
END FROM users;

-- COALESCE：返回第一个非 NULL
SELECT COALESCE(NULL, NULL, 'default');  -- default

-- 类型转换
SELECT CAST('123' AS INTEGER) + 1;  -- 124

SQLite Where 子句

WHERE 子句过滤行：

-- 比较
SELECT * FROM users WHERE age >= 18;

-- 范围
SELECT * FROM products WHERE price BETWEEN 10 AND 100;

-- 列表
SELECT * FROM users WHERE city IN ('北京', '上海', '广州');

-- 模糊（LIKE：% 任意，_ 单个）
SELECT * FROM users WHERE name LIKE '张%';
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- 空值
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

-- 组合条件
SELECT * FROM users
WHERE age >= 18 AND city = '北京' OR vip = 1;

-- 建议加括号明确优先级
SELECT * FROM users
WHERE (age >= 18 AND city = '北京') OR vip = 1;

SQLite AND/OR 运算符

AND / OR 逻辑运算：

-- AND：全部满足
SELECT * FROM users
WHERE age > 18 AND city = '北京';

-- OR：任一满足
SELECT * FROM users
WHERE age < 18 OR city = '北京';

-- 混合（AND 优先）
SELECT * FROM users
WHERE age > 18 OR city = '北京' AND vip = 1;
-- 等价于 age>18 OR (city='北京' AND vip=1)

-- 用括号改变优先级
SELECT * FROM users
WHERE (age > 18 OR city = '北京') AND vip = 1;

-- NOT 取反
SELECT * FROM users
WHERE NOT city = '北京';
-- 等价于 city != '北京'

SQLite Update 语句

UPDATE 更新数据：

-- 更新单列
UPDATE users SET age = 26 WHERE name = '张三';

-- 更新多列
UPDATE users
SET age = 30, city = '上海'
WHERE id = 1;

-- 无条件更新（全表！）
UPDATE users SET vip = 1;

-- 带子查询更新
UPDATE users
SET city = (SELECT city FROM temp_city WHERE temp_city.id = users.id);

-- 限制更新条数
UPDATE users SET age = 20 WHERE age < 18 LIMIT 5;

SQLite Delete 语句

DELETE 删除数据：

-- 删除指定行
DELETE FROM users WHERE id = 5;

-- 带条件批量删除
DELETE FROM users WHERE age < 18;

-- 删除全部（保留表）
DELETE FROM users;

-- 带子查询
DELETE FROM users
WHERE id IN (SELECT id FROM banned_users);

-- 限制条数
DELETE FROM users WHERE vip = 0 LIMIT 10;

SQLite Like 子句

LIKE 模糊匹配：

-- % 匹配任意个字符（含 0 个）
SELECT * FROM users WHERE name LIKE '张%';   -- 张开头
SELECT * FROM users WHERE name LIKE '%三';   -- 三结尾
SELECT * FROM users WHERE name LIKE '%小%';  -- 含"小"

-- _ 匹配单个字符
SELECT * FROM users WHERE name LIKE '张_';   -- 张X（单字名）
SELECT * FROM users WHERE name LIKE '_明';   -- X明

-- 转义（匹配含 % 的文本）
SELECT * FROM logs WHERE msg LIKE '100\%' ESCAPE '\\';

-- NOT LIKE
SELECT * FROM users WHERE name NOT LIKE '张%';

-- 大小写不敏感（ASCII 默认）
SELECT * FROM users WHERE email LIKE '%GMAIL%';

SQLite Glob 子句

GLOB 子句：大小写敏感的类 Unix 通配符匹配：

-- * 匹配任意字符（类似 %）
SELECT * FROM users WHERE name GLOB '张*';

-- ? 匹配单个字符（类似 _）
SELECT * FROM users WHERE name GLOB '张?';

-- 字符集
SELECT * FROM users WHERE name GLOB '[张李王]*';   -- 张/李/王开头
SELECT * FROM users WHERE name GLOB '[a-z]*';      -- 小写字母开头
SELECT * FROM users WHERE name GLOB '[^张]*';      -- 非张开头

-- GLOB 大小写敏感
SELECT * FROM users WHERE email GLOB '*@GMAIL.COM';  -- 只匹配大写

SQLite Limit 子句

LIMIT 限制返回行数（分页）：

-- 取前 10 条
SELECT * FROM users LIMIT 10;

-- 跳过前 20 条，取 10 条（分页）
SELECT * FROM users LIMIT 10 OFFSET 20;

-- 简写：LIMIT 偏移量, 数量
SELECT * FROM users LIMIT 20, 10;

-- 配合排序取 TOP N
SELECT * FROM users ORDER BY age DESC LIMIT 5;

-- 分页公式：page=3, size=10
SELECT * FROM users LIMIT 10 OFFSET (3-1)*10;
-- 注意：SQLite 不支持 OFFSET 中的表达式，需先算好

SQLite Order By

ORDER BY 排序：

-- 升序（默认）
SELECT * FROM users ORDER BY age;

-- 降序
SELECT * FROM users ORDER BY age DESC;

-- 多列排序
SELECT * FROM users ORDER BY city, age DESC;

-- 按表达式/别名排序
SELECT name, age * 2 AS d FROM users ORDER BY d DESC;

-- 按列序号排序
SELECT name, age FROM users ORDER BY 2;   -- 按第2列(age)

-- NULL 排序（默认 NULL 最小）
SELECT * FROM users ORDER BY email;        -- NULL 在前
SELECT * FROM users ORDER BY email NULLS LAST;

-- 随机排序（测试用）
SELECT * FROM users ORDER BY RANDOM() LIMIT 3;

SQLite Group By

GROUP BY 分组聚合：

-- 按城市分组统计人数
SELECT city, COUNT(*) AS cnt
FROM users
GROUP BY city;

-- 多列分组
SELECT city, vip, COUNT(*)
FROM users
GROUP BY city, vip;

-- 常用聚合函数
SELECT
    COUNT(*),            -- 总数
    AVG(age),            -- 平均
    SUM(amount),         -- 求和
    MIN(age),            -- 最小
    MAX(age)             -- 最大
FROM orders;

-- 分组后过滤用 HAVING（不是 WHERE）
SELECT city, AVG(age) AS avg_age
FROM users
GROUP BY city
HAVING avg_age > 25;

-- GROUP BY + 排序
SELECT city, COUNT(*) cnt
FROM users
GROUP BY city
ORDER BY cnt DESC;

SQLite Having 子句

HAVING 子句：对分组结果过滤：

-- 找出订单总额超过 1000 的用户
SELECT user_id, SUM(amount) AS total
FROM orders
GROUP BY user_id
HAVING total > 1000;

-- WHERE + GROUP BY + HAVING 组合
SELECT city, AVG(age) AS avg_age
FROM users
WHERE age > 0              -- 先过滤行
GROUP BY city
HAVING COUNT(*) >= 3       -- 再过滤组
ORDER BY avg_age DESC;

SQLite Distinct 关键字

DISTINCT 去重：

-- 单列去重
SELECT DISTINCT city FROM users;

-- 多列组合去重
SELECT DISTINCT city, vip FROM users;

-- 与聚合结合
SELECT COUNT(DISTINCT city) FROM users;   -- 不同城市数

-- 与 GROUP BY 对比
SELECT DISTINCT city FROM users;
SELECT city FROM users GROUP BY city;
-- 结果类似，但 GROUP BY 可用于聚合

-- DISTINCT 注意 NULL
SELECT DISTINCT email FROM users;
-- 多个 NULL 只算一个（DISTINCT 认为 NULL 相同）

SQLite PRAGMA

PRAGMA 是 SQLite 特有的"控制命令"，查看或修改数据库设置：

-- 查看版本
PRAGMA user_version;

-- 设置版本（用于迁移管理）
PRAGMA user_version = 1;

-- 查看/设置 外键约束
PRAGMA foreign_keys;            -- 查询（默认 0）
PRAGMA foreign_keys = ON;       -- 开启

-- 查看/设置 自动递增
PRAGMA auto_vacuum;
PRAGMA auto_vacuum = FULL;

-- 查看表信息
PRAGMA table_info(users);       -- 列信息
PRAGMA table_list;              -- 所有表（新版本）

-- 查看索引
PRAGMA index_list(users);
PRAGMA index_info(idx_name);

-- 检查完整性
PRAGMA integrity_check;         -- ok

-- 性能相关
PRAGMA cache_size;              -- 页缓存
PRAGMA journal_mode = WAL;      -- 并发更佳
PRAGMA synchronous = NORMAL;

SQLite 约束

约束（Constraints）保证数据完整性：

CREATE TABLE students (
    id INTEGER PRIMARY KEY,            -- 主键：唯一 + 非空
    name TEXT NOT NULL,                -- 非空
    email TEXT UNIQUE,                 -- 唯一
    age INTEGER CHECK (age >= 0),      -- 检查条件
    class_id INTEGER DEFAULT 1,        -- 默认值
    FOREIGN KEY (class_id) REFERENCES classes(id)  -- 外键
);

-- 命名约束（便于错误提示）
CREATE TABLE t (
    a INTEGER CONSTRAINT pk_a PRIMARY KEY,
    b TEXT CONSTRAINT uq_b UNIQUE,
    c INTEGER CONSTRAINT ck_c CHECK (c > 0)
);

-- 违反约束报错
INSERT INTO students (name) VALUES (NULL);  -- NOT NULL constraint failed

SQLite Join

JOIN 多表关联查询：

-- 建表示例
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, amount REAL);

-- INNER JOIN：只返回匹配行
SELECT users.name, orders.amount
FROM users
INNER JOIN orders ON users.id = orders.user_id;

-- LEFT JOIN：保留左表全部行
SELECT users.name, orders.amount
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

-- CROSS JOIN：笛卡尔积
SELECT * FROM users CROSS JOIN orders;

-- 多表 JOIN
SELECT u.name, o.amount, p.name AS product
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN products p ON o.product_id = p.id;

-- 表别名
SELECT u.name, o.amount
FROM users AS u
JOIN orders AS o ON u.id = o.user_id;

SQLite Unions 子句

UNION 合并查询结果：

-- UNION：合并去重
SELECT name FROM users_a
UNION
SELECT name FROM users_b;

-- UNION ALL：合并不去重（更快）
SELECT name FROM users_a
UNION ALL
SELECT name FROM users_b;

-- 要求：列数一致、类型兼容
-- 错误示例
SELECT id, name FROM users_a
UNION
SELECT name FROM users_b;   -- 列数不同会报错

-- 与 ORDER BY 组合
SELECT name FROM users_a
UNION ALL
SELECT name FROM users_b
ORDER BY name;

-- 与 WHERE 组合
SELECT name FROM users_a WHERE age > 20
UNION
SELECT name FROM users_b WHERE age > 20;

SQLite NULL 值

NULL 值处理：

-- 插入 NULL
INSERT INTO users (name, email) VALUES ('张三', NULL);

-- 判断
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

-- 错误写法（不会报错但结果为空）
SELECT * FROM users WHERE email = NULL;   -- 永远无结果！

-- 填充默认值
SELECT name, COALESCE(email, '未填写') FROM users;
SELECT name, IFNULL(email, '未填写') FROM users;

-- 排序时 NULL 处理
SELECT * FROM users ORDER BY email NULLS FIRST;
SELECT * FROM users ORDER BY email NULLS LAST;

-- 聚合忽略 NULL
SELECT AVG(age) FROM users;   -- NULL 不参与计算
SELECT COUNT(email) FROM users;  -- 只计非 NULL

SQLite 别名

别名（Alias）简化列名与表名：

-- 列别名
SELECT name AS 姓名, age AS 年龄 FROM users;
SELECT name 姓名, age 年龄 FROM users;   -- AS 可省略

-- 表别名
SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id;

-- 别名用于表达式
SELECT amount * 0.1 AS tax FROM orders;

-- 别名在 ORDER BY / HAVING 中使用
SELECT name, age * 2 AS double_age
FROM users
ORDER BY double_age DESC;

-- 别名与聚合
SELECT city AS c, COUNT(*) cnt
FROM users
GROUP BY c
HAVING cnt > 2;

SQLite 触发器

触发器（Trigger）：在表操作时自动执行 SQL：

-- 创建日志触发器：删除用户时记录
CREATE TABLE user_logs (action TEXT, user_name TEXT, ts TEXT);

CREATE TRIGGER log_user_delete
AFTER DELETE ON users
BEGIN
    INSERT INTO user_logs (action, user_name, ts)
    VALUES ('DELETE', OLD.name, datetime('now'));
END;

-- 删除用户自动记录
DELETE FROM users WHERE id = 1;

-- BEFORE 触发器：插入前校验
CREATE TRIGGER prevent_negative_age
BEFORE INSERT ON users
WHEN NEW.age < 0
BEGIN
    SELECT RAISE(ABORT, '年龄不能为负');
END;

-- 查看触发器
SELECT * FROM sqlite_master WHERE type='trigger';

-- 删除触发器
DROP TRIGGER log_user_delete;

SQLite 索引

索引（Index）加速查询：

-- 创建索引
CREATE INDEX idx_users_name ON users(name);

-- 唯一索引
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- 复合索引
CREATE INDEX idx_orders_user_amount
ON orders(user_id, amount);

-- 条件索引（部分索引）
CREATE INDEX idx_vip ON users(age) WHERE vip = 1;

-- 查看索引
PRAGMA index_list(users);

-- 删除索引
DROP INDEX idx_users_name;

-- 使用 EXPLAIN 检查是否走索引
EXPLAIN QUERY PLAN SELECT * FROM users WHERE name = '张三';
-- SEARCH users USING INDEX idx_users_name (name=?)

SQLite Indexed By

INDEXED BY 强制指定索引：

-- 创建两个索引
CREATE INDEX idx_name ON users(name);
CREATE INDEX idx_name_age ON users(name, age);

-- 强制使用指定索引（查询优化器可能选错）
SELECT * FROM users
INDEXED BY idx_name
WHERE name = '张三';

-- 强制使用复合索引
SELECT * FROM users
INDEXED BY idx_name_age
WHERE name = '张三' AND age > 20;

-- 不存在的索引报错
SELECT * FROM users INDEXED BY no_such_idx;  -- error

-- 避免强制索引：让优化器自行选择
SELECT * FROM users WHERE name = '张三';

SQLite Alter 命令

ALTER 命令修改表结构：

-- 添加列
ALTER TABLE users ADD COLUMN phone TEXT;

-- 添加带默认值的列
ALTER TABLE users ADD COLUMN vip INTEGER DEFAULT 0;

-- 添加带约束的列（不能加 UNIQUE/主键）
ALTER TABLE users ADD COLUMN birthday TEXT NOT NULL DEFAULT '2000-01-01';

-- 重命名列（SQLite 3.25+）
ALTER TABLE users RENAME COLUMN phone TO mobile;

-- 重命名表
ALTER TABLE users RENAME TO members;

-- 注意：SQLite 不支持删除列/修改列类型
-- 需要：新建表 + 复制数据 + 删旧表 + 重命名
CREATE TABLE users_new (...);
INSERT INTO users_new SELECT ... FROM users;
DROP TABLE users;
ALTER TABLE users_new RENAME TO users;

SQLite Truncate Table

SQLite 没有 TRUNCATE 命令，清空表的正确方式：

-- 方式一：DELETE FROM（慢，但可回滚）
DELETE FROM users;

-- 方式二：删除重建（快，且重置自增）
DROP TABLE users;
CREATE TABLE users (...);   -- 重新建表

-- 方式三：DELETE + 重置自增序列
DELETE FROM users;
DELETE FROM sqlite_sequence WHERE name = 'users';

-- 验证自增
INSERT INTO users (name) VALUES ('张三');
SELECT last_insert_rowid();   -- 若重置过则是 1

SQLite 视图

视图（View）：封装查询为"虚拟表"：

-- 创建视图
CREATE VIEW adult_users AS
SELECT id, name, age, city
FROM users
WHERE age >= 18;

-- 查询视图（就像查表）
SELECT * FROM adult_users;

-- 视图 + 聚合
CREATE VIEW city_stats AS
SELECT city, COUNT(*) cnt, AVG(age) avg_age
FROM users
GROUP BY city;

-- 查看视图定义
SELECT sql FROM sqlite_master WHERE type='view';

-- 删除视图
DROP VIEW adult_users;

-- 临时视图（连接结束消失）
CREATE TEMP VIEW tmp_view AS SELECT 1;

-- 视图缺点：不能修改数据（部分可更新）
-- INSERT INTO adult_users ...  -- 可能报错

SQLite 事务

事务（Transaction）：保证多条操作原子性：

-- 手动事务
BEGIN TRANSACTION;
INSERT INTO orders (user_id, amount) VALUES (1, 100);
UPDATE users SET balance = balance - 100 WHERE id = 1;
COMMIT;   -- 提交

-- 出错回滚
BEGIN;
INSERT INTO orders (user_id, amount) VALUES (2, 200);
-- 模拟出错
ROLLBACK;   -- 撤销以上所有操作

-- 保存点（部分回滚）
BEGIN;
INSERT INTO a VALUES (1);
SAVEPOINT sp1;
INSERT INTO b VALUES (2);
ROLLBACK TO sp1;    -- 撤销 sp1 之后
RELEASE sp1;
COMMIT;

# Python 中的事务
import sqlite3
conn = sqlite3.connect('test.db')
try:
    conn.execute("BEGIN")
    conn.execute("INSERT INTO users (name) VALUES ('张三')")
    conn.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
    conn.commit()
except Exception:
    conn.rollback()
conn.close()

SQLite 子查询

子查询（Subquery）：嵌套在 SQL 中的查询：

-- WHERE 中的子查询
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);

-- 标量子查询（返回单值）
SELECT name,
       (SELECT MAX(amount) FROM orders WHERE orders.user_id = users.id) AS max_order
FROM users;

-- FROM 中的子查询（派生表）
SELECT city, cnt
FROM (SELECT city, COUNT(*) cnt FROM users GROUP BY city)
WHERE cnt > 2;

-- 相关子查询
SELECT * FROM products p
WHERE price > (SELECT AVG(price) FROM products WHERE category = p.category);

-- EXISTS 子查询
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

SQLite Autoincrement

AUTOINCREMENT 自增主键：

-- 方式一：INTEGER PRIMARY KEY（rowid 别名）
CREATE TABLE t1 (id INTEGER PRIMARY KEY, name TEXT);

-- 方式二：加 AUTOINCREMENT（禁止重用 ID）
CREATE TABLE t2 (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT
);

-- 区别演示
-- 删除最大 ID 后：
-- t1: INSERT 会重用被删的 ID
-- t2: INSERT 从 max+1 开始，ID 永不重用

-- 插入
INSERT INTO t2 (name) VALUES ('A');  -- id=1
INSERT INTO t2 (name) VALUES ('B');  -- id=2
DELETE FROM t2 WHERE id = 2;
INSERT INTO t2 (name) VALUES ('C');  -- id=3（不重用 2）

-- 查看当前自增值
SELECT seq FROM sqlite_sequence WHERE name='t2';

SQLite 注入

SQL 注入是常见安全漏洞，SQLite 同样需要防范：

# 危险写法（直接拼接用户输入）
name = input("输入姓名: ")
sql = "SELECT * FROM users WHERE name = '" + name + "'"
# 若输入: ' OR '1'='1
# SQL 变成: SELECT * FROM users WHERE name = '' OR '1'='1'  （返回全部！）

# 安全写法：参数化查询（占位符 ?）
conn = sqlite3.connect('test.db')
cur = conn.cursor()
cur.execute("SELECT * FROM users WHERE name = ?", (name,))

# 多参数
cur.execute(
    "INSERT INTO users (name, age) VALUES (?, ?)",
    (name, age)
)

# 命名参数（Python sqlite3 用 :name）
cur.execute(
    "SELECT * FROM users WHERE name = :n AND age > :a",
    {"n": name, "a": 18}
)

-- 其他语言同样用占位符
-- Java:  PreparedStatement ps = conn.prepareStatement("SELECT * FROM users WHERE name = ?");
-- PHP:   $stmt = $pdo->prepare("SELECT * FROM users WHERE name = ?"); $stmt->execute([$name]);

SQLite Explain

EXPLAIN 查看查询执行计划（性能分析）：

-- 查看执行计划
EXPLAIN QUERY PLAN SELECT * FROM users WHERE name = '张三';
-- 输出: SEARCH users USING INDEX idx_users_name (name=?)
-- 或: SCAN users  （全表扫描，性能差）

-- 不带索引的查询
EXPLAIN QUERY PLAN SELECT * FROM users WHERE age = 25;
-- SCAN users

-- JOIN 的执行计划
EXPLAIN QUERY PLAN
SELECT u.name FROM users u JOIN orders o ON u.id = o.user_id;
-- 看是否使用索引、扫描顺序

-- 详细指令级
EXPLAIN SELECT * FROM users;

-- 分析表更新统计信息（帮助优化器）
ANALYZE;

SQLite Vacuum

VACUUM 重建数据库文件、回收空间：

-- 手动清理（删除大量数据后空间不释放）
VACUUM;

-- 查看数据库大小（文件）
-- ls -lh test.db

-- 开启自动清理
PRAGMA auto_vacuum = FULL;
-- 注意：要在建表前设置才有效（或 VACUUM 后生效）

-- 增量模式（平衡性能与空间）
PRAGMA auto_vacuum = INCREMENTAL;
PRAGMA incremental_vacuum;   -- 手动执行增量清理

-- 重建索引（VACUUM 同时做）
VACUUM;

SQLite 日期 & 时间

SQLite 日期与时间：

-- 常用函数
SELECT date('now');              -- 2026-08-12
SELECT time('now');              -- 10:30:00
SELECT datetime('now');          -- 2026-08-12 10:30:00
SELECT julianday('now');         -- 儒略日
SELECT strftime('%Y-%m-%d %H:%M:%S', 'now');

-- 格式符号
-- %Y 年 %m 月 %d 日 %H 时 %M 分 %S 秒
-- %w 星期(0-6) %j 年内第几天 %W 年内第几周

-- 日期运算
SELECT date('now', '+1 day');        -- 明天
SELECT date('now', '-1 month');      -- 上个月
SELECT date('now', '+2 years');
SELECT date('2026-01-01', '+10 days');

-- 时间差
SELECT julianday('2026-08-12') - julianday('2026-01-01');  -- 天数

-- 存储建议：TEXT ISO 格式（可排序可比较）
-- 'YYYY-MM-DD HH:MM:SS'

-- 修改器
SELECT datetime('now', 'start of month');
SELECT datetime('now', 'start of year');
SELECT datetime('now', 'localtime');     -- 本地时区

SQLite 常用函数

SQLite 常用函数：

-- 字符串函数
SELECT upper('abc'), lower('ABC'), length('hello');
SELECT substr('hello', 2, 3);        -- ell
SELECT replace('hello world', 'o', '0');
SELECT trim('  hi  '), ltrim(' hi'), rtrim('hi ');
SELECT instr('hello', 'll');         -- 3

-- 数学函数
SELECT abs(-5), round(3.14159, 2), ceil(4.2), floor(4.8);
SELECT random();                     -- 随机整数
SELECT min(3, 1, 2), max(3, 1, 2);

-- 聚合函数
SELECT COUNT(*), SUM(x), AVG(x), MIN(x), MAX(x) FROM t;

-- 其他
SELECT typeof('str');                -- text
SELECT quote(x'0102');               -- X'0102'
SELECT hex('ABC');                   -- 414243
SELECT char(65);                     -- A

-- 判断
SELECT NULLIF(5, 5);                 -- NULL
SELECT IFNULL(NULL, 0);              -- 0
SELECT COALESCE(NULL, NULL, 9);      -- 9

-- 字符串拼接
SELECT 'a' || 'b';                   -- ab

SQLite - C/C++

SQLite - C/C++ 使用：

// 需要 sqlite3.h 和 libsqlite3
#include <stdio.h>
#include <sqlite3.h>

int main() {
    sqlite3 *db;
    char *err_msg = 0;

    // 打开数据库（不存在则创建）
    int rc = sqlite3_open("test.db", &db);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "无法打开数据库: %s\n", sqlite3_errmsg(db));
        return 1;
    }

    // 执行 SQL
    char *sql = "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT);"
                "INSERT INTO users (name) VALUES ('张三');";
    rc = sqlite3_exec(db, sql, 0, 0, &err_msg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL 错误: %s\n", err_msg);
        sqlite3_free(err_msg);
    }

    // 查询（回调方式）
    sqlite3_exec(db, "SELECT * FROM users;",
        callback, 0, 0);

    sqlite3_close(db);
    return 0;
}

// 编译: gcc test.c -lsqlite3 -o test

// 查询回调
int callback(void *data, int argc, char **argv, char **col_name) {
    for (int i = 0; i < argc; i++) {
        printf("%s = %s\n", col_name[i], argv[i] ? argv[i] : "NULL");
    }
    printf("---\n");
    return 0;
}

SQLite - Java

SQLite - Java 使用：

// 需要 sqlite-jdbc 依赖
// Maven: <dependency>
//   <groupId>org.xerial</groupId>
//   <artifactId>sqlite-jdbc</artifactId>
//   <version>3.45.0.0</version>
// </dependency>

import java.sql.*;

public class SqliteDemo {
    public static void main(String[] args) throws Exception {
        // 连接（不存在则创建）
        Connection conn = DriverManager.getConnection(
            "jdbc:sqlite:test.db");

        // 建表
        Statement stmt = conn.createStatement();
        stmt.execute("CREATE TABLE IF NOT EXISTS users (" +
            "id INTEGER PRIMARY KEY, name TEXT)");

        // 插入（预处理防注入）
        PreparedStatement ps = conn.prepareStatement(
            "INSERT INTO users (name) VALUES (?)");
        ps.setString(1, "张三");
        ps.executeUpdate();

        // 查询
        ResultSet rs = stmt.executeQuery("SELECT * FROM users");
        while (rs.next()) {
            System.out.println(rs.getInt("id") + ": " + rs.getString("name"));
        }

        // 内存数据库
        // Connection mem = DriverManager.getConnection("jdbc:sqlite::memory:");

        rs.close();
        stmt.close();
        conn.close();
    }
}

SQLite - PHP

SQLite - PHP 使用：

<?php
// 需要启用 pdo_sqlite 扩展

// 连接（不存在则创建）
$pdo = new PDO("sqlite:test.db");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// 建表
$pdo->exec("CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER
)");

// 插入（预处理）
$stmt = $pdo->prepare("INSERT INTO users (name, age) VALUES (?, ?)");
$stmt->execute(["张三", 25]);

// 查询
$rows = $pdo->query("SELECT * FROM users")->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $row) {
    echo $row["name"] . " - " . $row["age"] . "<br>";
}

// 事务
$pdo->beginTransaction();
$pdo->exec("INSERT INTO users (name, age) VALUES ('李四', 30)");
$pdo->commit();

// 内存数据库
// $pdo = new PDO("sqlite::memory:");
?>

SQLite - Perl

SQLite - Perl 使用：

# 需要 DBI 和 DBD::SQLite 模块
# cpan install DBI DBD::SQLite

use strict;
use warnings;
use DBI;

# 连接（不存在则创建）
my $dbh = DBI->connect(
    "dbi:SQLite:dbname=test.db",
    "", "",
    { RaiseError => 1 }
);

# 建表
$dbh->do("CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    name TEXT
)");

# 插入（占位符）
my $stmt = $dbh->prepare("INSERT INTO users (name) VALUES (?)");
$stmt->execute("张三");

# 查询
my $rows = $dbh->selectall_arrayref(
    "SELECT * FROM users", { Slice => {} });
foreach my $row (@$rows) {
    print "$row->{id}: $row->{name}\n";
}

# 事务
eval {
    $dbh->begin_work;
    $dbh->do("INSERT INTO users (name) VALUES ('李四')");
    $dbh->commit;
};
if ($@) { $dbh->rollback; }

$dbh->disconnect;

SQLite - Python

SQLite - Python 使用（最常用）：

import sqlite3

# 连接（不存在则创建）
conn = sqlite3.connect('test.db')
# 内存数据库
# conn = sqlite3.connect(':memory:')

cur = conn.cursor()

# 建表
cur.execute('''CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER
)''')

# 插入（参数化防注入）
cur.execute('INSERT INTO users (name, age) VALUES (?, ?)', ('张三', 25))

# 批量插入
users = [('李四', 30), ('王五', 22)]
cur.executemany('INSERT INTO users (name, age) VALUES (?, ?)', users)
conn.commit()

# 查询
cur.execute('SELECT * FROM users WHERE age >= ?', (18,))
for row in cur.fetchall():
    print(row)  # (1, '张三', 25)

# 返回字典
conn.row_factory = sqlite3.Row
cur.execute('SELECT * FROM users')
for row in cur.fetchall():
    print(row['name'], row['age'])

# 事务
try:
    cur.execute('INSERT INTO users (name) VALUES (?)', ('赵六',))
    conn.commit()
except:
    conn.rollback()

conn.close()