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

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

SQLite 查看

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

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

CREATE VIEW 语句

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

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

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

步骤2） 使用以下命令打开数据库“TutorialsSampleDB.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';

你应该会看到返回的视图是“AllStudentsView”：

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

SELECT * FROM AllStudentsView;

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

临时视图

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

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

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

计费示例：

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

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

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

步骤4） 请运行以下命令，确保创建临时视图“AllStudentsTempView”：

SELECT name FROM sqlite_temp_master WHERE type = 'view';

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

步骤6） 使用以下命令打开与数据库“TutorialsSampleDB.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 将对列值进行扫描以查找所需的数据。如果没有索引，这个过程通常会比较慢。

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

SQLite 创建指数

要在列上创建索引，应使用命令 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);

您应该看不到任何输出：

步骤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 触发端口

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

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

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 关键字。

例如：

接下来，我们将创建一个触发器，该触发器将在向“学生”表中插入新学生之前触发。

它会将新插入的学生信息记录到“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()”函数会返回插入语句执行时的当前日期时间戳。这样我们就可以记录插入事务，并自动为每个事务添加时间戳。

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

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

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

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

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

步骤5） 编写以下命令，选择“StudentsLog”表中的所有行：

SELECT * FROM StudentsLog;

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

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

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

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

在表上使用 BEFORE 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”命令会引发错误，并显示错误消息“您无法更新学生姓名”，然后阻止更新语句的执行。

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

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

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

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

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

SELECT StudentName FROM Students;

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

常见问题