SQLite 视图，触发器和事务|极客教程

本章我们将介绍 SQLite 视图，触发器和事务。

SQLite 视图

视图是对来自一个或多个表的数据的特定外观。它可以按特定顺序排列数据，或突出显示或隐藏某些数据。视图由存储的查询组成，该查询可作为由查询结果集组成的虚拟表访问。与普通表不同，视图不构成物理模式的一部分。它是根据数据库中的数据计算或整理的动态虚拟表。

在下一个示例中，我们创建一个简单的视图。

sqlite> SELECT * FROM Cars;
Id           Name         Price     
-----------  -----------  ----------
1            Audi         52642     
2            Mercedes     57127     
3            Skoda        9000      
4            Volvo        29000     
5            Bentley      350000    
6            Citroen      21000     
7            Hummer       41400     
8            Volkswagen   21600

这是我们的数据，我们在该数据上创建视图。

sqlite> CREATE VIEW CheapCars AS SELECT Name FROM Cars WHERE Price < 30000;
sqlite> SELECT * FROM CheapCars;
Name       
-----------
Skoda      
Volvo      
Citroen    
Volkswagen

CREATE VIEW语句用于创建视图。

sqlite> .tables
Books         CheapCars     Friends       Names         Reservations
Cars          Customers     Log           Orders        Testing     
sqlite> DROP VIEW CheapCars;
sqlite> .tables
Books         Customers     Log           Orders        Testing     
Cars          Friends       Names         Reservations

从技术上讲，视图是虚拟表。因此，我们可以使用.tables命令列出所有视图。要删除视图，我们使用DROP VIEW语句。

SQLite 触发器

触发器是在发生指定的数据库事件时自动执行的数据库操作。

在下面的示例中，我们将使用Friends表并创建一个新的Log表。

sqlite> CREATE TABLE Log(Id INTEGER PRIMARY KEY, OldName TEXT, 
   ...> NewName TEXT, Date TEXT);

Log表的一列中包含朋友的旧名称和新名称。它还有一个时间戳列。

CREATE TRIGGER mytrigger UPDATE OF Name ON Friends
BEGIN
INSERT INTO Log(OldName, NewName, Date) VALUES (old.Name, new.Name, datetime('now'));
END;

我们使用CREATE TRIGGER语句创建一个名为mytrigger的触发器。每当我们更新Friends表的Name列时，此触发器将启动INSERT语句。 INSERT语句会将旧名称，新名称和时间戳插入Log表中。 old和new是对要修改的行的引用。

sqlite> SELECT name, tbl_name FROM sqlite_master WHERE type='trigger';
name         tbl_name   
-----------  -----------
mytrigger    Friends

为了列出可用的触发器及其对应的表，我们查询sqlite_master表。

sqlite> SELECT * FROM Friends;
Id          Name        Sex       
----------  ----------  ----------
1           Jane        F         
2           Thomas      M         
3           Franklin    M         
4           Elisabeth   F         
5           Mary        F         
6           Lucy        F         
7           Jack        M

这是我们的数据。

接下来，我们将更新Friends表的一行。

sqlite> UPDATE Friends SET Name='Frank' WHERE Id=3;

我们更新表的第三行。触发器启动。

sqlite> SELECT * FROM Log;
Id           OldName      NewName     Date               
-----------  -----------  ----------  -------------------
1            Franklin     Frank       2014-11-18 10:58:46

我们检查Log表。此日志确认我们执行的更新操作。

SQLite 事务

事务是针对一个或多个数据库中数据的数据库操作的基本单位。事务中所有 SQL 语句的影响可以全部提交给数据库，也可以全部回滚。

在 SQLite 中，除SELECT以外的任何命令都将启动隐式事务。手动事务以BEGIN TRANSACTION语句开始，并以COMMIT或ROLLBACK语句结束。

BEGIN TRANSACTION;
CREATE TABLE Test(Id INTEGER NOT NULL);
INSERT INTO Test VALUES(1);
INSERT INTO Test VALUES(2);
INSERT INTO Test VALUES(3);
INSERT INTO Test VALUES(NULL);
COMMIT;

在这里，我们有一个示例事务。事务以BEGIN TRANSACTION开头，以COMMIT结束。

我们在 ID 列上设置了NOT NULL约束。因此，第四次插入将不会成功。 SQLite 会逐案管理事务。对于某些错误，它将还原所有更改。对于其他用户，它仅还原最后一条语句，而其他更改保持不变。在我们的情况下，将创建表并将前三个插入内容写入表中。第四个不是。

说，我们已经有一个名为 Test 的空表。执行上述事务将完全失败。不会写入任何更改。如果我们将CREATE TABLE语句更改为CREATE TABLE IF NOT EXISTS,，则前三个语句将执行。

BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS Test(Id INTEGER NOT NULL);
INSERT INTO Test VALUES(11);
INSERT INTO Test VALUES(12);
INSERT INTO Test VALUES(13);
INSERT INTO Test VALUES(NULL);
ROLLBACK;

事务可以以COMMIT或ROLLBACK语句结束。 ROLLBACK恢复所有更改。