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

作者： Marcus Allen Marcus Allen
时间 - 权威教程 更新于

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

SQLite 视图

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

您可以为复杂查询定义一个视图，并且每次需要时都可以通过直接调用视图来重用这些查询，而无需再次重写查询。

CREATE VIEW 语句

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

示例： 在以下示例中，我们将在名为 “TutorialsSampleDB.db” 的示例数据库中创建一个名为 “AllStudentsView” 的视图，如下所示：

步骤 1) 打开我的电脑，导航到目录 “C:\sqlite”，然后打开 “sqlite3.exe“。

SQLite View

第 2 步：使用以下命令打开数据库“TutorialsSampleDB.db”

SQLite View

步骤 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;

命令应该没有类似这样的输出：

SQLite View

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

SELECT name FROM sqlite_master WHERE type = 'view';

您应该会看到返回的视图 “AllStudentsView”。

SQLite View

步骤 5) 现在我们的视图已创建，您可以像普通表一样使用它，如下所示：

SELECT * FROM AllStudentsView;

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

SQLite View

临时视图

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

  • CREATE TEMP VIEW，或
  • CREATE TEMPORARY VIEW。

如果您想暂时进行某些操作并且不需要永久视图，临时视图就很有用。因此，您只需创建一个临时视图，然后使用该视图进行处理。之后，当您关闭与数据库的连接时，它将自动删除。

示例

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

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

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

步骤 2) 运行以下命令打开与数据库 “TutorialsSampleDB.db” 的连接：

.open TutorialsSampleDB.db

步骤 3) 写入以下命令，它将创建一个名为 “AllStudentsTempView” 的临时视图：

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;

SQLite View

步骤 4) 运行以下命令，确保已创建名为 “AllStudentsTempView” 的临时视图：

SELECT name FROM sqlite_temp_master WHERE type = 'view';

SQLite View

步骤 5) 关闭 sqlite3.exe，然后重新打开它。

步骤 6) 运行以下命令打开与数据库 “TutorialsSampleDB.db” 的连接：

.open TutorialsSampleDB.db

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

SELECT name FROM sqlite_temp_master WHERE type = 'view';

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

SQLite View

备注

  • 您不能对视图使用 INSERT、DELETE 或 UPDATE 语句，只能使用 “select from views” 命令，如 CREATE View 示例的步骤 5 所示。
  • 要删除 VIEW，您可以使用 “DROP VIEW” 语句。
DROP VIEW AllStudentsView;

为确保视图已被删除，您可以运行以下命令，该命令会列出数据库中的视图：

SELECT name FROM sqlite_master WHERE type = 'view';

您将找不到任何返回的视图，因为该视图已被删除，如下所示：

SQLite View

相关文章

SQLite 索引

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

但是，如果书中没有索引也没有页码，您将从头到尾扫描整本书，直到找到您正在搜索的关键字。这非常困难，尤其是当您有索引时，而且搜索关键字的过程非常缓慢。

SQLite 中的索引（以及其他 数据库管理系统 的概念也相同）的工作方式与书本后面的索引相同。

当您使用搜索条件在 SQLite 表中搜索某些行时，SQLite 将搜索表中的所有行，直到找到与搜索条件匹配的所需行。当表很大时，此过程会变得非常缓慢。

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

通过索引提高性能

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

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

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

想象一本书没有索引，您需要搜索一个特定的单词。您将从第一页到最后一页扫描整本书来查找该单词。但是，如果您有该书的索引，您将首先查找该单词。获取其所在页码，然后导航到它。这比从头到尾扫描整本书要快得多。

SQLite CREATE INDEX

要在列上创建索引，您应该使用 CREATE INDEX 命令。并应如下定义：

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

示例

在下面的示例中，我们将在 “Students” 数据库的 students 表上创建一个名为 “StudentNameIndex” 的索引，如下所示：

步骤 1) 如前所述，导航到 “C:\sqlite” 文件夹。

步骤 2) 打开 sqlite3.exe。

步骤 3) 运行以下命令打开 “TutorialsSampleDB.db” 数据库：

.open TutorialsSampleDB.db

步骤 4) 使用以下命令创建一个新索引 “StudentNameIndex”：

CREATE INDEX StudentNameIndex ON Students(StudentName);

这应该没有输出。

SQLite Index

步骤 5) 为确保索引已创建，您可以运行以下查询，该查询会列出 Students 表中创建的索引：

PRAGMA index_list(Students);

您应该会看到刚刚创建的索引被返回。

SQLite Index

备注

  • 索引不仅可以基于列创建，还可以基于表达式创建。例如：
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 触发器简介

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

  • INSERT 到表中。
  • 从表中 DELETE 行。
  • UPDATE 表的一个列。

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

SQLite CREATE TRIGGER

要创建新的 TRIGGER，您可以使用 CREATE TRIGGER 语句，如下所示：

  • 在 CREATE TRIGGER 之后，您应该指定一个触发器名称。
  • 在触发器名称之后，您必须指定触发器名称应在何时执行。您有三个选项：
  • BEFORE – 触发器将在指定的 INSERT、UPDATE 或 delete 语句之前执行。
  • After – 触发器将在指定的 INSERT、UPDATE 或 delete 语句之后执行。
  • INSTEAD OF – 它将用 TRIGGER 中指定的语句替换触发了触发器发生的动作。INSTEAD OF 触发器不适用于表，仅适用于视图。
  • 然后，您必须指定触发器将在发生时触发的操作类型。是 DELETE、INSERT 还是 UPDATE。
  • 您可以选择一个可选的列名，这样除非在该列上发生了操作，否则触发器不会被触发。
  • 然后，您必须指定创建触发器的表名。
  • 在触发器的主体内，您应该指定触发器被触发时每行应执行的语句。

    • 触发器将仅根据 create trigger 命令上指定的语句类型被激活（触发）。例如：

    • BEFORE INSERT 触发器将在任何 insert 语句之前激活（触发）。
    • AFTER UPDATE 触发器将在任何 update 语句之后激活（触发），等等。

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

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

    示例

    在下面的示例中，我们将创建一个触发器，该触发器将在将新学生插入 “Students” 表之前触发。

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

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

    步骤 2) 运行以下命令打开数据库 “TutorialsSampleDB.db”：

    .open TutorialsSampleDB.db

    步骤 3) 运行以下命令创建触发器 “InsertIntoStudentTrigger”：

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

    “datetime()” 函数将在 insert 语句发生时提供当前日期和时间戳。这样，我们就可以通过为每笔交易添加自动时间戳来记录插入事务。

    该命令应该成功运行，并且没有输出。

    SQLite Trigger

    每次在 students 表中插入新学生时，都会触发 “InsertIntoStudentTrigger” 触发器。“new” 关键字指的是将被插入的值。例如，“new.StudentId” 将是被插入的学生 ID。

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

    步骤 4) 写入以下命令，该命令将在 students 表中插入一个新学生：

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

    步骤 5) 写入以下命令，该命令将从 “StudentsLog” 表中选择所有行：

    SELECT * FROM StudentsLog;

    您应该会看到为刚刚插入的新学生返回的新行。

    SQLite Trigger

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

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

    使用触发器防止意外更新

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

    示例

    在接下来的示例中，我们将阻止任何 update 语句更新 Students 表中的 “studentname” 列。

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

    步骤 2) 运行以下命令打开数据库 “TutorialsSampleDB.db”：

    .open TutorialsSampleDB.db

    步骤 3) 在 “Students” 表上创建一个名为 “preventUpdateStudentName” 的新触发器，运行以下命令：

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

    “RAISE” 命令将引发一个错误，并附带错误消息 “You cannot update studentname” ，然后它将阻止 update 语句的执行。

    现在，我们将验证触发器是否正常工作，以及它是否阻止了 studentname 列的任何更新。

    步骤 4) 运行以下 update 命令，该命令将把学生姓名 “Jack” 更新为 “Jack1“。

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

    您应该会收到触发器中指定的错误消息，说明 “You cannot update studentname” ，如下所示：

    SQLite Trigger

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

    SELECT StudentName FROM Students;

    您应该会看到学生姓名 “Jack” 保持不变，没有改变。

    SQLite Trigger

    摘要

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