SQLite 视图、触发器、事务

最后修改于 2020 年 7 月 6 日

在本 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 会还原所有更改。

在本 SQLite 教程中，我们使用了 SQLite 中的视图、触发器和事务。

电子书

SQLite 视图、触发器、事务

SQLite 视图

SQLite 触发器

SQLite 事务