SQLite 触发器、视图和索引（附示例）

在日常使用中 SQLite，您将需要一些数据库管理工具。您还可以使用它们通过创建索引来提高数据库查询效率，或通过创建视图来提高数据库的可重用性。

SQLite 首页

视图与表非常相似。但视图是逻辑表；它们不像表那样物理存储。视图由 select 语句组成。

您可以为复杂的查询定义一个视图，并且可以通过直接调用视图来随时重用这些查询，而不必再次重写查询。

CREATE VIEW 语句

要在数据库上创建视图，您可以使用 CREATE VIEW 语句，后跟视图名称，然后在其后放置所需的查询。

计费示例： 在以下示例中，我们将创建一个名为“全部学生查看” 在示例数据库中 “教程SampleDB.db”如下：

步骤1） 打开“我的电脑”并导航到以下目录“目录:\sqlite”，然后打开“sqlite3.exe“：

步骤2） 打开数据库“教程SampleDB.db”通过以下命令：

步骤3） 以下是创建视图的 sqlite3 命令的基本语法

CREATE VIEW AllStudentsView
AS
  SELECT 
    s.StudentId,
    s.StudentName,
    s.DateOfBirth,
    d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

该命令不应该有如下输出：

步骤4） 为了确保视图已创建，您可以通过运行以下命令来选择数据库中的视图列表：

SELECT name FROM sqlite_master WHERE type = 'view';

您应该看到视图“全部学生查看” 返回：

步骤5） 现在我们的视图已创建，您可以将其用作普通表，如下所示：

SELECT * FROM AllStudentsView;

此命令将查询视图“AllStudents”并从中选择所有行，如下面的屏幕截图所示：

临时视图

临时视图对于用于创建它的当前数据库连接来说是临时的。然后，如果您关闭数据库连接，所有临时视图将被自动删除。使用以下命令之一创建临时视图：

创建临时视图，或
创建临时视图。

如果您想要暂时执行某些操作而不需要将其作为永久视图，则临时视图非常有用。因此，您只需创建一个临时视图，然后使用该视图进行处理。 Later 当你关闭与数据库的连接时，它将被自动删除。

计费示例：

在下面的例子中，我们将打开一个数据库连接，然后创建一个临时视图。

之后，我们将关闭该连接，并检查临时视图是否仍然存在。

步骤1） 从目录“打开sqlite3.exe目录:\sqlite”如前所述。

步骤2） 打开与数据库的连接“教程SampleDB.db通过运行以下命令：

.open TutorialsSampleDB.db

步骤3） 编写以下命令来创建临时视图“所有学生临时查看“

CREATE TEMP VIEW AllStudentsTempView
AS
  SELECT 
    s.StudentId,
    s.StudentName,
    s.DateOfBirth,
    d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

步骤4） 确保临时视图“所有学生临时查看”是通过运行以下命令创建的：

SELECT name FROM sqlite_temp_master WHERE type = 'view';

步骤5） 关闭sqlite3.exe并重新打开。

步骤6） 打开与数据库的连接“教程SampleDB.db”通过以下命令：

.open TutorialsSampleDB.db

步骤7） 运行以下命令获取在数据库上创建的临时视图列表：

SELECT name FROM sqlite_temp_master WHERE type = 'view';

您不应该看到任何输出，因为我们在上一步关闭数据库连接时创建的临时视图已被删除。否则，只要您保持数据库打开的连接，您就可以看到包含数据的临时视图。

备注：

您不能对视图使用语句 INSERT、DELETE 或 UPDATE，只能使用“从视图中选择”命令，如 CREATE View 示例中的步骤 5 所示。
要删除视图，可以使用“DROP VIEW”语句：

DROP VIEW AllStudentsView;

为了确保删除视图，您可以运行以下命令，该命令会为您提供数据库中的视图列表：

SELECT name FROM sqlite_master WHERE type = 'view';

您将发现由于视图已被删除，因此没有返回任何视图，如下所示：

SQLite 索引

如果你有一本书，你想搜索这本书的关键词。你会在书的索引中搜索这个关键词。然后你将导航到该关键词的页码以阅读有关该关键词的更多信息。

但是，如果这本书没有索引，也没有页码，你就得从头到尾浏览整本书，直到找到你要搜索的关键词。这非常困难，尤其是当你有索引并且搜索关键词的过程非常缓慢的时候。

索引 SQLite （同样的概念也适用于其他数据库管理系统其工作方式与书后的索引相同。

当您搜索 SQLite 带有搜索条件的表格， SQLite 会搜索表的所有行，直到找到符合搜索条件的行。当表较大时，该过程会变得非常缓慢。

索引将加快数据搜索查询速度，并有助于从表中检索数据。索引是在表列上定义的。

使用索引提高性能：

索引可以提高在表中搜索数据的性能。在列上创建索引时， SQLite 将为该索引创建一个数据结构，其中每个字段值都有一个指向该值所属的整行的指针。

然后，如果您对索引中的列运行带有搜索条件的查询， SQLite 将首先在索引中查找值。 SQLite 不会扫描整个表格。然后它将读取表格行的值指向的位置。 SQLite 将定位该位置上的行并检索它。

但是，如果您要搜索的列不是索引的一部分， SQLite 将对列值进行扫描以查找所需的数据。如果没有索引，这个过程通常会比较慢。

想象一下，有一本书没有索引，而你需要搜索一个特定的单词。你会从第一页到最后一页扫描整本书来寻找那个单词。但是，如果这本书有索引，你会先在书上查找这个单词。获取它所在的页码，然后导航到它。这比从封面到封底扫描整本书要快得多。

SQLite 创建指数

要在列上创建索引，应使用命令 CREATE INDEX。并且应按如下方式定义它：

您必须在 CREATE INDEX 命令后指定索引的名称。
在索引名称后面，必须放置关键字“ON”，后跟将创建索引的表名。
然后是用于索引的列名列表。
您可以在任何列名后使用以下关键字“ASC”或“DESC”之一来指定用于对索引数据进行排序的排序顺序。

计费示例：

在以下示例中，我们将创建一个索引 “学生姓名索引” 在学生桌上的“学生”数据库如下：

步骤1） 导航到文件夹“目录:\sqlite”如前所述。

步骤2） 打开 sqlite3.exe。

步骤3） 打开数据库“教程SampleDB.db”通过以下命令：

.open TutorialsSampleDB.db

步骤4） 创建新索引“学生姓名索引使用以下命令：

CREATE INDEX StudentNameIndex ON Students(StudentName);

您应该看不到任何输出：

步骤5） 为了确保索引已创建，您可以运行以下查询，该查询为您提供在表 Students 中创建的索引列表：

PRAGMA index_list(Students);

您应该看到我们刚刚创建的索引返回：

备注：

索引不仅可以根据列创建，还可以基于表达式创建。如下所示：

CREATE INDEX OrderTotalIndex ON OrderItems(OrderId, Quantity*Price);

“OrderTotalIndex” 将基于 OrderId 列以及 Quantity 列值和 Price 列值的乘积。因此，任何针对“OrderId”和“Quantity*Price”的查询都将是高效的，因为查询将使用索引。

如果您在 CREATE INDEX 语句中指定了 WHERE 子句，则索引将是部分索引。在这种情况下，索引中将只有符合 WHERE 子句中条件的行才会有条目。例如，在以下索引中：
```
CREATE INDEX OrderTotalIndexForLargeQuantities ON OrderItems(OrderId, Quantity*Price)
WHERE Quantity > 10000;
```
（在上面的例子中，由于指定了 WHERE 子句，因此索引将是部分索引。在这种情况下，索引将仅适用于数量值大于 10000 的订单。请注意，此索引被称为部分索引是因为 WHERE 子句，而不是因为它上使用的表达式。但是，您可以将表达式与普通索引一起使用。）
您可以使用 CREATE UNIQUE INDEX 语句而不是 CREATE INDEX 来防止列的重复条目，因此索引列的所有值都将是唯一的。
要删除索引，请使用 DROP INDEX 命令，后跟要删除的索引名称。

SQLite 触发端口

简介 SQLite 触发端口

触发器是数据库表上发生特定操作时自动执行的预定义操作。可以定义触发器，使其在表上发生以下操作之一时触发：

插入到表中。
从表中删除行。
更新其中一个表列。

SQLite 支持FOR EACH ROW触发器，这样，触发器中预定义的操作将对表上发生的操作（无论是插入、删除还是更新）所涉及的所有行执行。

SQLite 创建触发器

要创建一个新的 TRIGGER，可以使用以下 CREATE TRIGGER 语句：

在 CREATE TRIGGER 之后，您应该指定一个触发器名称。
在触发器名称之后，您必须指定触发器名称的执行时间。您有三个选项：
BEFORE – 触发器将在指定的 INSERT、UPDATE 或 delete 语句之前执行。
After – 触发器将在指定的 INSERT、UPDATE 或 delete 语句之后执行。
INSTEAD OF – 它将用 TRIGGER 中指定的语句替换触发触发器的操作。INSTEAD OF 触发器不适用于表，仅适用于视图。

然后，您必须指定操作的类型，触发器将在操作发生时触发。DELETE、INSERT 或 UPDATE。

您可以选择一个可选的列名，这样除非操作发生在该列上，否则触发器就不会触发。

然后您必须指定将创建触发器的表名。

在触发器的主体内，您应该指定在触发触发器时应对每一行执行的语句。

触发器将仅根据创建触发器命令中指定的语句类型来激活（触发）。例如：

BEFORE INSERT 触发器将在任何插入语句之前被激活（触发）。
AFTER UPDATE 触发器将在任何更新语句之后被激活（触发），...等等。

在触发器内部，可以使用“new”关键字引用新插入的值。此外，还可以使用 old 关键字引用已删除或更新的值。如下所示：

在 INSERT 触发器中 – 可以使用新关键字。
在 UPDATE 触发器内部 – 可以使用 new 和 old 关键字。
在 DELETE 触发器中 – 可以使用 old 关键字。

例如：

接下来，我们将创建一个触发器，该触发器将在将新学生插入“学生“ 桌子。

它会将新插入的学生记录到表中“学生日志”并自动为插入语句发生的当前日期时间添加时间戳。如下所示：

步骤1） 导航到目录“目录:\sqlite”并运行sqlite3.exe。

步骤2） 打开数据库“教程SampleDB.db通过运行以下命令：

.open TutorialsSampleDB.db

步骤3） 创建触发器“插入学生触发器通过运行以下命令：

CREATE TRIGGER InsertIntoStudentTrigger 
       BEFORE INSERT ON Students
BEGIN
  INSERT INTO StudentsLog VALUES(new.StudentId, datetime(), 'Insert');
END;

该功能 “约会时间（）” 将为您提供插入语句发生的当前日期时间戳。这样我们就可以记录插入事务，并为每个事务添加自动时间戳。

该命令应该成功运行，并且您没有得到任何输出：

触发 ”插入学生触发器”将在每次向学生表中插入新学生时触发。“新”关键字表示将插入的值。例如，“新的学生编号” 将是要插入的学生 ID。

现在，我们将测试插入新学生时触发器的行为。

步骤4） 编写以下命令，在学生表中插入一名新学生：

INSERT INTO Students VALUES(11, 'guru11', 1, '1999-10-12');

步骤5） 编写以下命令，它将从“学生日志“ 桌子：

SELECT * FROM StudentsLog;

您应该看到我们刚刚插入的新学生返回了一行新行：

此行是在插入 ID 为 11 的新学生之前由触发器插入的。

在此示例中，我们使用了触发器“ 插入学生触发器 “我们创建了，用于记录表中任何插入事务”学生日志”自动记录。您可以用同样的方式记录任何更新或删除语句。

使用触发器防止意外更新：

在表上使用 BEFORE UPDATE 触发器，您可以根据表达式阻止对列的更新语句。

例如：

在下面的例子中，我们将阻止任何更新语句更新 Students 表中的“studentname”列：

步骤1） 导航到目录“目录:\sqlite”并运行sqlite3.exe。

步骤2） 打开数据库“教程SampleDB.db通过运行以下命令：

.open TutorialsSampleDB.db

步骤3） 创建新触发器“阻止更新学生姓名“ 在桌子上 ”学生通过运行以下命令

CREATE TRIGGER preventUpdateStudentName
BEFORE UPDATE OF StudentName ON Students
FOR EACH ROW
BEGIN
    SELECT RAISE(ABORT, 'You cannot update studentname');
END;

“”提高“命令将引发错误并显示错误消息“ 您无法更新学生姓名 ”，那么就会阻止更新语句的执行。

现在，我们将验证触发器是否运行良好，并且它可以阻止对 studentname 列的任何更新。

步骤4） 运行以下更新命令，它将更新学生姓名“插口“ 成为 ”Jack1“。

UPDATE Students SET StudentName = 'Jack1' WHERE StudentName = 'Jack';

您应该会收到我们在触发器上指定的错误消息，提示“您无法更新学生姓名”如下：

步骤5） 运行以下命令，它将从学生表中选择学生姓名列表。

SELECT StudentName FROM Students;

您应该看到学生姓名“Jack”仍然相同并且没有改变：

结语

视图、索引和触发器是管理 SQLite 数据库。您可以跟踪表上发生的数据修改操作。您还可以通过创建索引来优化数据库数据检索操作。

SQLite 首页

相关文章

SQLite 索引

SQLite 触发端口

结语

注册简报