SQLite学习笔记（六）

学习笔记（六）"/>

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', '