一天学会sqlite--Chapter 9: 索引、触发器和视图

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

SQLite 视图

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

你可以为复杂的查询定义一个视图，然后通过直接调用视图来重复使用这些查询，而不用再次重写查询。

创建视图语句

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

**示例

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

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

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

步骤 3） 现在，我们将使用以下命令创建视图：

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”，并从中选择所有行，如下图所示：

临时视图

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

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

临时视图非常有用，如果你想在这段时间内进行一些操作，而不需要它成为永久视图。因此，只需创建一个临时视图，然后使用该视图进行处理即可。之后，当你关闭与数据库的连接时，它就会被自动删除。

示例：

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

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

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

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

.open TutorialsSampleDB.db

第 3 步） 编写以下命令，创建临时视图 “ AllStudentsTempView ” ： E

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 步） 现在我们创建了视图，你可以像使用普通表格一样使用它，就像这样：

SELECT * FROM AllStudentsView ;

该命令将查询视图 “AllStudents”，并从中选择所有行，如下图所示：

临时视图

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

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

临时视图非常有用，如果你想在这段时间内进行一些操作，而不需要它成为永久视图。因此，只需创建一个临时视图，然后使用该视图进行处理即可。之后，当你关闭与数据库的连接时，它就会被自动删除。

**示例

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

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

第 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 语句，只能使用 “select from views ”命令，如 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 将执行列值扫描，以找到您要搜索的数据。如果没有索引，这个过程通常会比较慢。

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

创建索引

要为列创建索引，应使用 CREATE INDEX 命令。并按以下方式定义索引：

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

示例：

在下面的示例中，我们将在 “Students”数据库中的 student 表上创建一个索引 “ 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 列以及数量列值和价格列值的乘积。因此，对 “OrderId ”和 “Quantity*Price ”的任何查询都将是高效的，因为查询将使用索引。

• 如果在 CREATE INDEX 语句中指定了 WHERE 子句，那么该索引将是部分索引。在这种情况下，索引中将只包含与 WHERE 子句中的条件相匹配的记录。例如，在以下索引中：

• sqlite 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 CREATE TRIGGER

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

• 在 CREATE TRIGGER 之后，应指定一个触发器名称。
• 在触发器名称之后，必须指定触发器名称的确切执行时间。有三个选项
• BEFORE - 触发器将在指定的 INSERT、UPDATE 或删除语句之前执行。
• After - 触发器将在指定的 INSERT、UPDATE 或删除语句之后执行。

• INSTEAD OF - 它将用 TRIGGER 中指定的语句替换触发触发器的操作。INSTEAD OF 触发器不适用于表，只适用于视图。

• 然后，您必须指定动作类型，触发器将在动作发生时触发。可以是 DELETE、INSERT 或 UPDATE。

• 你可以选择一个可选的列名，这样触发器就不会触发，除非操作发生在该列上。
• 然后，必须指定创建触发器的表名。
• 在触发器正文中，应指定触发器触发后每条记录应执行的语句。

触发器的激活（触发）仅取决于创建触发器命令中指定的语句类型。例如

• BEFORE INSERT 触发器将在任何插入语句之前激活（触发）。

• AFTER UPDATE 触发器将在任何更新语句后激活（触发），......以此类推。

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

• 在 INSERT 触发器中，可以使用 new 关键字。
• 在 UPDATE 触发器中，可以使用新关键字和旧关键字。
• 在 DELETE 触发器中 - 可以使用旧关键字。

**示例

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

它将把新插入的学生记录到 “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 触发器，可以根据表达式防止列上的更新语句。

示例

在下面的示例中，我们将防止任何更新语句更新学生表中的 “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 "，然后它将阻止更新语句的执行。

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

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

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

您应该会收到我们在触发器上指定的错误信息，提示 “您无法更新 studentname ”，如下所示：

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

SELECT StudentName FROM Students;

你会发现学生的名字 “Jack ”还是那个名字，没有任何变化：

摘要：

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