SQLite学习笔记（六）

概述

本篇文章的结构如下所示：

• 视图（View）
• 索引（Index）
• 触发器（Trigger）
• 事务（Transaction）

---

一、视图

视图（View）即虚拟表，也叫做派生表，它的内容都是派生自其它表的查询结果。虽然视图看起来像基本表，但是它并不是基本表，因为基本表的内容是持久的，而视图的内容是在使用时动态产生的。创建视图的语法如下：

CREATE VIEW name AS select-stmt;

视图的名称通过 name 指定，其定义由 select-stmt 定义。最终生成的视图看起来就像名为 name 的基本表。我们往往基于以下理由使用视图：

• 重用 SQL 语句。
• 简化复杂的 SQL 操作。在编写查询后，可以方便地重用它而不必知道其基本查询细节。
• 使用表的一部分而不是整个表。
• 保护数据。可以授予用于访问表的特定部分的权限，而不是整个表的访问权限。
• 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

下面先看一个检索数据的例子：

SELECT cust_name, cust_contact 
FROM Customers, Orders, OrderItems 
WHERE Customers.cust_id = Orders.cust_id 
AND OrderItems.order_num = Orders.order_num 
AND prod_id = 'RGAN01';

这个例子联结了三个表，然后检索出 prod_id 为 “RGAN01” 的顾客信息。而如果我们需要检索 prod_id 为 “DLL01” 的顾客信息，上面一大串命令不得不重新再输入一遍，这时候，我们就可以考虑用视图将可以重用的部分利用起来。如下所示：

CREATE VIEW ProductCustomers AS 
SELECT cust_name, cust_contact, prod_id  
FROM Customers, Orders, OrderItems 
WHERE Customers.cust_id = Orders.cust_id 
AND OrderItems.order_num = Orders.order_num;

在创建好该视图后，我们就可以使用该视图如下：

输入：

SELECT cust_name, cust_contact 
FROM ProductCustomers 
WHERE prod_id = 'RGAN01';

输出：

cust_name   cust_contact
----------  ------------------
Fun4All     Denise L. Stephens
The Toy St  Kim Howard

通过视图，我们可以省略一些 SQL 语句，并且提高了其复用性，如果我们想查询 prod_id 为 “DLL01” 的顾客，就不需要再像之前一样输入那么多 SQL 语句了。

注意：
SQLite 不支持可更新的视图，换句话说 SQLite 只支持在检索中使用视图。
创建视图的时候应当尽量考量其可复用性，创建不绑定特定数据的视图往往是一个好的选择。

如果需要删除视图，只需要执行如下命令：

DROP VIEW ProductsCustomers;

---

二、索引

索引（Index）是一种用来在某种条件下加速查询的结构。现有如下检索：

SELECT cust_name, cust_contact 
FROM Customers 
WHERE cust_id = '100000002';

在正常情况下，数据库会采用顺序扫描，逐个搜索表中的所有行，把符合 cust_id = ‘1000000002’ 的语句输出。但是如果表 Customers 非常地大，那么这种检索数据的方式就会显得非常的低效，此时我们可以考虑采用索引的方式来加快检索的速度，SQLite 底层采用 B-tree 做索引。

创建索引的命令如下：

CREATE INDEX [UNIQUE] index_name ON table_name (columns);

变量 index_name 是索引的名称，table_name 是包括索引所在字段的表名称，变量 columns 是一个字段或以逗号分隔的多个字段。

如果使用关键字 UNIQUE，将会在索引上添加约束，索引中的所有值必须是唯一的。这不仅适用于索引，也适用于索引所在字段。例子如下：

sqlite> CREATE TABLE Foo(a text, b text);
sqlite> CREATE UNIQUE INDEX Foo_idx ON Foo(a,b);
sqlite> INSERT INTO Foo VALUES('unique', 'value');
sqlite> INSERT INTO Foo VALUES('unique', 'value1');
sqlite> INSERT INTO Foo VALUES('unique', 'value');
Error: UNIQUE constraint failed: Foo.a, Foo.b

从错误信息可以看到，当联合字段值发生重复时，就会报错。如果要删除索引，可使用 DROP INDEX 命令：

DROP INDEX Foo_idx;

注意：

• 虽然使用索引可以加快我们的检索速度，但是索引数据可能要占据大量的存储空间，特别如果多表中的所有字段都创建索引，表的大小可能翻倍。
• 另一个需要考虑的问题是索引的维护。进行 insert、update 和 delete 操作时，除了修改表，数据库也需要修改对应的索引，所以假如索引可能会降低 insert、update 和类似操作的速度。
• 最后，在索引中定义多个列时（例如，州加上城市）。这种索引仅在以州加城市的顺序排序时有用。如果想按城市排序，则这种索引没有用处。

1. 使用索引

SQLite 中有一些具体条件来判断是否使用索引。对下面会在 WHERE 子句中出现的表达式。SQLite 将使用单个字段索引。

column { = | > | >= | <= | < } expression
expression { = | > | >= | <= | < } column
column IN (expression-list)
column IN (subquery)

下面通过几个例子说明，假设有表定义如下：

CREATE TABLE foo (a,b,c,d);

此时创建如下多个字段索引：

create index foo_idx on foo(a,b,c,d);

现做如下检索：

SELECT * FROM foo WHERE a=1 AND b=2 AND d=3;

只有第一个和第二个条件将使用索引。不使用第三个条件的原因是没有条件使用c去缩小到d 的差距。SQLite在使用多字段索引的时候会智能地从左到右使用字段，即从左边的字段开始，查询使用字段的条件，然后移动到第二个字段，依此类推，直到 WHERE 子句中无法找出有效条件。

SELECT * FROM foo WHERE a>1 AND b=2 AND c=3 AND d=4;

SQLite 会在字段a上进行索引扫描，表达式 a>1 称为最右边的索引字段，因为它使用了不等号。类似的，下面语句会在 b>2 处停止：

SELECT * FROM foo WHERE a=1 AND b>2 AND c=3 AND d=4;

总而言之，创建索引一定要有充足的理由确保可以获得性能的改善，否则也有可能出现负优化。

---

三、触发器

触发器（Trigger）在特定的数据库活动发生时自动执行。触发器可以与特定表上的 INSERT、UPDATE 和 DELETE 操作（或组合）相关联。创建触发器的一般命令如下：

CREATE [TEMP|TEMPORARY] TRIGGER trigger_name 
[BEFORE|AFTER] [INSERT|DELETE|UPDATE|UPDATE OF columns] ON table_name 
BEGIN 
	-- trigger logic goes here...
END;

触发器是通过名称、行为和表定义的。行为由一系列 SQL 命令组成，当某些事件发生时，触发器负责启动这些命令。此外，还可以通过关键字 before 或 after 来指定这些操作是在事件发生前还是发生后执行。其中 UPDATE 可以在表上的一个列或多个指定字段上创建触发器。

下面是触发器的一些常见用途：

• 保证数据一致。例如在 INSERT 或 UPDATE 操作中将所有州名转化为大写。
• 基于某个表的变动在其他表上执行活动。例如，每当更新或删除一行时将审计跟踪记录写入某个日志表。
• 进行额外的验证并根据需要回退数据。例如，保证某个顾客的可用资金不超过限定，如果已经超出，则阻塞插入。
• 计算列的值或更新时间戳。

下面是一个简单的触发器示例：

CREATE TABLE Company
(
	id      integer PRIMARY KEY,
	name    text NOT NULL,
	address text NOT NULL
);

CREATE TABLE log(
	emp_id integer, 
	emp_date text
);

创建两个表，一个基本表 Company 和一个用于记录操作的表 log。接下来创建触发器：

CREATE TRIGGER log_trigger AFTER INSERT ON Company 
BEGIN 
	INSERT INTO log VALUES(new.id, datetime('now'));
END;

创建完触发器后我们尝试插入几条数据验证下效果：

INSERT INTO Company VALUES(null, 'Baidu', 'Beijing');
INSERT INTO Company VALUES(null, 'Alibaba', 'HangZhou');
INSERT INTO Company VALUES(null, 'Tencent', 'ShenZhen');

SELECT * FROM log;

emp_id      emp_date
----------  -------------------
1           2019-04-23 01:18:30
2           2019-04-23 01:18:45
3           2019-04-23 01:18:47

可以看到，通过触发器我们可以记录插入操作的时间，将这些日志信息保存到相应的表中。

注意：
在其它的DBMS中可以即支持 for each row trigger 又支持 for each statement trigger。但 SQLite 只支持 for each row trigger，即操作语句每影响到一行的时候就触发一次。

如果要列出触发器，可以借助 sqlite_master：

SELECT name FROM sqlite_master WHERE type = 'trigger';

name
-----------
log_trigger

删除触发器同样简单：

DROP TRIGGER log_trigger;

---

四、事务

SQLite 支持事务操作，支持事务操作也就意味着该数据库必须支持事务的 ACID 这四大特性。

特性	描述
原子性(Atomicity)	原子性指的是事务包含的所有操作要么全部成功，要么全部失败回滚，即事务的操作如果成功就必须完全应用到数据库，否则就不能对数据库有任何影响
一致性(Consustency)	一致性指的是事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是一个事务执行之前和执行之后都必须处于一致性状态
隔离性(Isolation)	隔离性是当多个用户并发访问数据库时，比如操作同一张表，数据库为每一个用户开启的事务，不能被其它事务所干扰，多个并发事务之间要相互隔离
持久性(Durability)	持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变是永久性的，即便在数据库系统遭到故障的情况下也不会丢失提交事务的操作

1. 事务的命令

事务由 3 个命令控制：begin、commit 和 rollback。begin 开启一个事务，begin 之后的所有操作都可以取消，如果连接终止前没有发出 commit，也会被取消。commit 提交事务开始后所执行的所有操作。而 rollback 则是还原 begin 之后的所有操作。例如：

sqlite> begin;
sqlite> DELETE FROM Products;
sqlite> rollback;
sqlite> SELECT count(*) FROM Products;

count(*)
----------
9

这个例子开启了一个事务，在删除了 Products 中的所有数据后又进行了 rollback，所以再次查询 Products 时数据仍然存在。

在默认情况下，SQLite中每条 SQL 语句自成事务（自动提交模式）。也就是说，SQLite 默认每条单独的 SQL 语句就有 begin…commit/rollback 的事务，这种情况下，所有成功的命令将自动提交，失败的命令则回滚，这种操作模式也称为自动提交模式。

2. 使用保留点

SQLite 支持 savepoint 和 release 命令，包含多个语句的工作体可以设置 savepoint，回滚可以返回到某个 savepoint。创建 savepoint 的命令如下：

savepoint savepoint_name;

如果意识到需要回退到某个地方，不用回滚整个事务，可以使用下列命令回滚：

rollback [transaction] to savepoint_name;

通过一个例子来展示下它的使用，首先我们先来查看一下 Customers 表的信息用于后续的比较：

sqlite> select cust_id, cust_name from Customers;
cust_id     cust_name
----------  ------------
1000000001  Village Toys
1000000002  Kids Place
1000000003  Fun4All
1000000004  Fun4All
1000000005  The Toy Stor

接着我们执行含有保留点的事务：

begin;
INSERT INTO Customers(cust_id, cust_name) VALUES('1000000010', 'Toys Emporium');
savepoint one;
INSERT INTO  Customers(cust_id, cust_name) VALUES('1000000008', 'Toys Moyu');
INSERT INTO  Customers(cust_id, cust_name) VALUES('1000000007', 'Toys JKLove');
rollback to one;
commit;

理论上该事务执行完后会回滚到 one 这个保留点，也就是实际上只插入了 cust_id 为 1000000010 的这条数据，我们通过查询语句来查看是否是如此：

sqlite> select cust_id, cust_name from Customers;
cust_id     cust_name
----------  ------------
1000000001  Village Toys
1000000002  Kids Place
1000000003  Fun4All
1000000004  Fun4All
1000000005  The Toy Stor
1000000010  Toys Emporiu

可以看到确实只新插入了一条语句，这就是 savepoint 的大致用法。

release 命令用于释放保留点，release 需要在 savepoint 确定不会再被使用的时候执行，例如下面的事务就会产生错误：

begin;
INSERT INTO Customers(cust_id, cust_name) VALUES('1000000010', 'Toys Emporium');
savepoint one;
INSERT INTO  Customers(cust_id, cust_name) VALUES('1000000008', 'Toys Moyu');
INSERT INTO  Customers(cust_id, cust_name) VALUES('1000000007', 'Toys JKLove');
release one;
rollback to one;
commit;

Error: no such savepoint: one

在后面还会使用到保留点 one 的情况下 one 先被 release 了，就会造成下面的 rollback 语句无法找到 one 的情况。

3. 冲突解决

当违反约束时会导致事务的终止。终止造成的后果除了简单地将之前的修改全部取消之外（这是大部分数据库的处理方式），SQLIte 提供了以下选择：

策略	描述
replace	当违反唯一性约束时，SQLite将造成这种违反的记录删除，以插入或修改新的记录替代，SQLite继续执行；如果违反的是not null约束并且该字段没有默认值，SQLite应用abort策略
ignore	当约束违反发生时，SQLite允许命令继续执行，违反约束的行保持不变，而它之前和之后的记录将继续修改
fail	当约束违反发生时，SQLite终止命令，但是不恢复约束违反之前已经修改的记录
abort	当约束违反发生时，SQLite恢复命令所做的所有改变并终止命令。abort是SQLite中所有操作的默认解决方法
rollback	当约束违反发生时，SQLite执行回滚——终止当前命令和整个事务

冲突解决的语法形式如下：

INSERT OR resolution INTO table_name (column_list) VALUES (value_list);
UPDATE OR resolution table_name SET (value_list) WHERE predicate;

下面通过一个例子来对它的使用来做一个示范，假设我们有如下表：

CREATE TABLE test(id integer PRIMARY KEY);
SELECT count(*) FROM test;

count(*)
----------
412

它里面共有412行数据，数据为1~412。现做如下更新操作：

UPDATE test SET id=800-id;
Error: UNIQUE constraint failed: test.id

可以看到它违反了唯一性约束，因为当 UPDATE 语句更新到第388个记录时，它视图将id更新为 800-388=412，但是412 在 test 中已经存在，所以该命令终止。根据前面的分析，我们知道它默认执行的是 abort 策略，也就是说前面更新成功的 377 条记录都会被取消掉。

如果我们希望保留前面的可以更新成功的 377 条记录，我们可以输入命令如下：

UPDATE OR FAIL test SET id=800-id;

通过设置 fail 策略，就可以将前面的 377 条记录的更改保留下来了。

---

参考

《SQLite权威指南》

• 第4章 sqlite中的高级sql

《SQL必知必会》

• 第18课 使用视图
• 第20课 管理事务处理
• 第22课 高级SQL特性

---

希望这篇文章对你有所帮助~