第五章 SQLite数据库：5、SQLite 进阶用法：JOIN、UNION、TRIGGER、INDEX、ALIAS、INDEXED BY 等模块

一、JOIN：跨表查询的核心机制

1. JOIN 类型总览

JOIN 是连接多个表获取综合信息的关键手段。常见 JOIN 类型如下：

• INNER JOIN（内连接）：仅返回两个表中满足连接条件的行。
• LEFT OUTER JOIN（左连接）：返回左表所有行，右表无匹配时以 NULL 填充。
• CROSS JOIN（交叉连接）：返回两个表的笛卡尔积，即所有组合。

2. 示例：图书与分类表

-- 图书表：包含图书基本信息
CREATE TABLE Books (
    book_id INTEGER PRIMARY KEY,      -- 图书ID
    title TEXT NOT NULL,              -- 书名
    category TEXT NOT NULL            -- 分类名（字符串）
);

-- 分类表：存储分类信息
CREATE TABLE Categories (
    category_id INTEGER PRIMARY KEY,  -- 分类ID
    name TEXT NOT NULL                -- 分类名称
);

-- 插入示例数据
INSERT INTO Categories VALUES 
    (1, '数据库'), 
    (2, '计算机');
    
INSERT INTO Books VALUES 
    (1, 'SQL基础教程', '数据库'),  -- 属于“数据库”分类
    (2, '算法图解', '计算机'),    -- 属于“计算机”分类
    (3, '线性代数', '数学');      -- 无对应分类

3. INNER JOIN 示例

-- 查询有合法分类的图书（只返回分类匹配的记录）
-- 条件：Books.category = Categories.name
SELECT B.title, C.name AS category_name
FROM Books AS B
INNER JOIN Categories AS C 
    ON B.category = C.name;

4. LEFT OUTER JOIN 示例

-- 查询所有图书，如果没有匹配分类则显示 NULL
-- LEFT JOIN 保留 Books 表的所有行
SELECT B.title, C.name AS category_name
FROM Books AS B
LEFT OUTER JOIN Categories AS C 
    ON B.category = C.name;

5. CROSS JOIN 示例

-- 所有图书与所有分类的组合（笛卡尔积）
-- 注意：无连接条件会产生大量组合
SELECT B.title, C.name AS category_name
FROM Books AS B
CROSS JOIN Categories AS C;

---

二、UNION：合并多个查询结果

1. 原理说明

• UNION：合并多个 SELECT 结果，自动去重。
• UNION ALL：保留所有结果（含重复）。
• 所有 SELECT 子句字段数与类型需一致。

2. 示例数据：图书与预约图书

-- 创建预约图书表
CREATE TABLE ReservedBooks (
    reserve_id INTEGER PRIMARY KEY,   -- 预约编号
    title TEXT NOT NULL,              -- 图书标题
    category TEXT NOT NULL            -- 图书分类
);

-- 插入预约图书数据
INSERT INTO ReservedBooks VALUES 
    (1, '线性代数', '数学'),         -- 与 Books 中重复
    (2, '深入浅出SQL', '数据库');   -- 新图书

3. UNION 示例

-- 合并 Books 与 ReservedBooks，不重复
-- 同名图书如“线性代数”仅保留一条
SELECT title, category FROM Books
UNION
SELECT title, category FROM ReservedBooks;

4. UNION ALL 示例

-- 合并结果并保留重复条目
-- 可用于数据明细分析
SELECT title, category FROM Books
UNION ALL
SELECT title, category FROM ReservedBooks;

---

三、TRIGGER：自动执行的数据库事件

1. 触发器原理

• 自动响应特定数据操作（INSERT/UPDATE/DELETE）。
• 支持 BEFORE 与 AFTER 阶段。
• 可访问 NEW（新数据）和 OLD（旧数据）。

2. 示例表结构

-- 借阅记录表
CREATE TABLE BorrowRecords (
    record_id INTEGER PRIMARY KEY,  -- 记录ID
    book_id INTEGER,                -- 图书ID
    reader_name TEXT,               -- 借阅者姓名
    borrow_time TEXT                -- 借阅时间
);

-- 日志表
CREATE TABLE Log (
    log_id INTEGER PRIMARY KEY AUTOINCREMENT, -- 日志ID
    action TEXT,                              -- 动作说明
    log_time TEXT                             -- 时间戳
);

3. 示例触发器

-- 当插入借阅记录时，自动记录日志
-- 日志内容包括图书ID与当前时间
CREATE TRIGGER after_borrow_log
AFTER INSERT ON BorrowRecords
BEGIN
    INSERT INTO Log (action, log_time)
    VALUES ('借阅图书ID: ' || NEW.book_id, datetime('now'));
END;

---

四、INDEX：提升查询性能的关键工具

1. 索引原理

• 索引是加速数据检索的数据结构（如 B-tree）。
• 创建在常作为条件的列上（WHERE、JOIN、ORDER BY）。
• 索引类型包括：普通索引、唯一索引、组合索引、隐式索引。

2. 示例表结构

-- 创建读者信息表
CREATE TABLE Readers (
    reader_id INTEGER PRIMARY KEY, -- 读者ID
    name TEXT NOT NULL,            -- 姓名
    email TEXT UNIQUE,             -- 邮箱（唯一）
    phone TEXT                     -- 电话
);

3. 创建索引

-- 创建普通索引：加快 email 条件查询
CREATE INDEX idx_email ON Readers (email);

4. 删除索引

-- 删除指定索引
DROP INDEX idx_email;

5. INDEXED BY 子句

-- 强制使用 idx_email 索引
SELECT * FROM Readers INDEXED BY idx_email 
WHERE email = 'abc@example.com';

-- 禁用所有索引执行查询（PRIMARY KEY 除外）
SELECT * FROM Readers NOT INDEXED 
WHERE name = '李雷';

---

五、别名（Alias）：增强 SQL 可读性

1. 表别名

-- 为 Readers 表指定别名 R，提高书写简洁性
SELECT R.name, R.email 
FROM Readers AS R;

2. 列别名

-- 将查询结果列名改为中文展示
SELECT name AS 姓名, email AS 邮箱 
FROM Readers;

---

六、综合实战案例

需求：整合近三天图书借阅与预约记录

表结构补充

-- 创建图书预约表
CREATE TABLE Reservations (
    reserve_id INTEGER PRIMARY KEY, -- 预约记录ID
    book_id INTEGER,                -- 图书ID
    reader_name TEXT,               -- 预约人姓名
    reserve_time TEXT               -- 预约时间
);

查询语句

-- 查询三天内的借阅和预约记录
-- 合并字段名一致，字段数匹配
-- type 字段用于标识来源类型（borrow 或 reserve）
SELECT book_id, reader_name, borrow_time AS time, 'borrow' AS type
FROM BorrowRecords
WHERE borrow_time >= datetime('now', '-3 days')
UNION
SELECT book_id, reader_name, reserve_time AS time, 'reserve' AS type
FROM Reservations
WHERE reserve_time >= datetime('now', '-3 days');