一天学会sqlite--Chapter 8: 插入、更新、删除

SQLite 中的数据修改子句是 INSERT、UPDATE 和 DELETE 语句。它们用于插入新行、更新现有值或从数据库中删除行。

请注意，以下所有示例都必须运行 sqlite3.exe，并按流程打开与示例数据库的连接：

步骤 1） 在此步骤中、

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

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

.open TutorialsSampleDB.db

现在，您可以在数据库中运行任何类型的查询。

SQLite INSERT

要在表中插入记录，必须使用 “INSERT ”子句。INSERT 子句的语法如下：

• 在 INSERT 子句之后，应说明需要将值插入哪个表。
• 在表名之后，写入要插入数值的列列表。
• 你可以忽略列名，不写它们。
• 如果不写入列名，值将按照表中定义的列的顺序插入到表中的所有列中。
• 在 VALUES 子句之后，应列出要插入的值。
• 每个 INSERT 子句只插入一条记录。如果要插入多条记录，则应编写多个 INSERT 子句，每条记录一个 INSERT 子句。

SQLite 插入示例

在下面的示例中，我们将在学生表中插入 2 条记录，每个学生一条：

INSERT INTO Students( StudentId , StudentName , DepartmentId , DateOfBirth )
VALUES(11, 'Ahmad', 4, '1997-10-12');
INSERT INTO Students VALUES(12, 'Aly', 4, '1996-10-12');

该操作应能成功运行，但没有输出结果：

这将插入两名学生：

• 第一个学生的 StudentId =11，StudentName = Ahmad，DepartmentId = 4，DateOfBirth = 1997-10-12。
• 第二个学生的 StudentId =12，StudentName = Aly，DepartmentId = 4，DateOfBirth = 1996-10-12'。

在第一条语句中，我们列出了列名 “ StudentId , StudentName , DepartmentId , DateOfBirth ”。

这四个值 “ 12, ‘Aly’, 4, ‘1996-10-12’ ” 将按照列定义的顺序插入到学生表的所有四列中。

现在，让我们运行下面的查询来验证这两名学生是否已插入到 Students 表中：

SELECT * FROM Students；

然后，你会看到从该查询返回的两个学生如下所示：

SQLite Update

如果要更改任何列的值，必须使用 UPDATE 子句。UPDATE 子句通过更改特定列的值来更新表。以下是 UPDATE 子句的语法：

如下所示：

• 在 “更新子句 ”之后，应写入要更新的表名。
• 必须写入 “SET 子句”，用于写入要更新的列名和要更新的值。
• 您可以更新多个列。每行之间可以使用逗号。
• 可以指定 WHERE 子句，以便只指定某些记录。只有表达式求值为 “true ”的记录才会被更新。如果没有指定 WHERE 子句，则会更新所有记录。

SQLite 更新示例

在下面的 UPDATE 语句中，我们将把 StudentId = 6 的学生的 DepartmentId 更新为 3：

UPDATE Students
SET DepartmentId = 3
WHERE StudentId = 6;

运行应该会成功，而且不会有任何输出：

在 UPDATE 子句中，我们指定要更新表 Students。

• 在 WHERE 子句中，我们过滤了所有学生，只选择 StudentId = 6 的记录。
• SET 子句将更新所选学生的系 Id 值为 3。

现在，让我们运行以下命令来验证 ID 为 6 的学生是否已更新：

SELECT * FROM Students WHERE StudentId = 6;

现在你应该看到，部门 Id 值现在是 3，如下所示：

SQLite Delete

如果要在 SQLite 中删除一条或多条记录，必须使用 DELETE 子句。DELETE 子句的语法如下：

• 您必须在 DELETE FROM 子句后写入要删除记录的表名。(注意： *DELETE子句用于删除表中的部分记录或全部记录，不会删除表本身。然而，DROP 子句 用于删除整个表及其上的所有记录）。
• 如果将 DELETE 子句写成 “DELETE FROM guru”，就会删除表 “guru ”中的所有记录。
• 如果想删除某些特定记录，可以用表达式指定 WHERE 条件。只有表达式求值为 “true ”的记录才会被删除。例如，“DELETE FROM guru WHERE id > 5” - 这将只删除 id 大于 5 的记录。

示例

在下面的语句中，我们将删除两个 StudentId 为 11 和 12 的学生：

DELETE FROM Students WHERE StudentId = 11 OR StudentId = 12;

表达式 “StudentId = 11 OR StudentId = 12” 将只对编号为 11 和 12 的学生为真。因此，DELETE 子句将同时应用于这两个学生，并只删除他们。

该命令应能成功运行，并且不会有如下输出：

从 “学生 ”表中选择所有记录，验证这两名学生是否已被删除：

SELECT * FROM Students;

您不应该看到以下两个 ID 分别为 11 和 12 的学生：

SQLite 冲突子句

假设有一列具有以下列约束之一： UNIQUE、NOT NULL、CHECK 或 PRIMARY KEY。然后，您试图在该列插入或更新一个与该约束冲突的值。

例如，如果某列有 UNIQUE 约束，而你试图插入一个已经存在的值（重复值），这与 UNIQUE 约束相冲突。在这种情况下，CONFLICT 子句可以让你选择如何解决冲突。

在继续解释 CONFLICT 子句如何解决冲突之前。你应该了解什么是数据库事务。

数据库事务：

术语数据库事务是一系列 SQLite 操作（插入、更新或删除）。数据库事务必须作为一个整体执行，要么成功执行所有操作，要么完全不执行。如果其中一个操作执行失败，所有操作都将被取消。

数据库事务示例：

从一个银行账户向另一个银行账户转账的事务将涉及几个活动。该交易操作包括从第一个账户取款，然后存入另一个账户。该交易必须全部完成或全部取消，不得中途失败。

以下是您可以在 “冲突 ”条款中选择的五项决议：

1. ROLLBACK – 这将回滚发生冲突的当前 SQLite 语句所在的事务（取消整个事务）。例如，如果试图更新 10 行，而第五行的值与约束冲突，则不会更新任何行，10 行将保持不变。系统将抛出一个错误。
2. ABORT – 这将回滚发生冲突的当前 SQLite 语句所在的事务（取消整个事务）。例如，如果试图更新 10 行，而第五行的值与约束冲突，则不会更新任何行，10 行将保持不变。系统将抛出一个错误。
3. FAIL – 终止存在冲突的当前 SQLite 语句。不过，事务不会继续，但在发生冲突的行之前对该行所做的更改将被提交。例如，如果试图更新 10 行，而第五行的值与约束冲突，那么只有这 4 行会被更新，其他行不会被更新。此时将出现错误。
4. IGNORE – 这将跳过包含违反约束的记录，继续处理 SQLite 语句的其他后续记录。例如，如果试图更新 10 行，而第五行的值与约束冲突，那么只有这 4 行会被更新，其他行不会被更新。它不会继续更新其他行，而是停在有冲突值的那一行。不会出现错误。

5. REPLACE – 这取决于发生冲突的约束类型：

6. 当 UNIQUE 或 PRIMARY KEY 约束出现约束违规时。REPLACE 将用新插入或更新的记录替换导致违规的记录。

7. 如果违反了 NOT NULL 约束，REPLACE 子句将用该列的默认值替换 NULL 值。如果列没有默认值，SQLite 将中止语句（语句将被取消）。
8. 如果违反了 CHECK 约束，子句将被中止。

注： 以上 5 种解决方案是您希望如何解决冲突的选项。适用于解决某一冲突的方法不一定适用于解决其他类型的冲突。

如何声明 CONFLICT 子句

在 CREATE TABLE 子句中为列定义定义约束时，可以声明 ON CONFLICT 子句。语法如下

如前所述，您可以从五种解决方案中选择一种来解决冲突。

冲突忽略示例

步骤 1） 创建一个新的表主题，如下所示：

CREATE TABLE [Subjects] (
[ SubjectId ] INTEGER NOT NULL PRIMARY KEY ON CONFLICT IGNORE,
[ SubjectName ] NVARCHAR NOT NULL
);

请注意，我们在 SubjectId 列上定义了 PRIMARY KEY 约束。主键约束不允许在 SubjectId 列中插入两个重复的值，因此该列中的所有值都应该是唯一的。另外，请注意我们选择的冲突解决方式是 “IGNORE”。

命令应该可以成功运行，而且不会出现任何错误：

步骤 2） 现在，让我们在新表 subjects 中插入一些值，但这些值要违反主键约束：

INSERT INTO Subjects VALUES(1, 'Algebra');
INSERT INTO Subjects VALUES(2, 'Database Course');
INSERT INTO Subjects VALUES(2, 'Data Structures');
INSERT INTO Subjects VALUES(4, 'Algorithms');

在这些 INSERT 语句中，我们试图插入两个具有相同主键 Subject id 2 的课程，这违反了主键约束。

这些命令应该运行正常，不会出现任何错误。如下所示：

步骤 3） 从表格中选择所有主题，如下所示：

SELECT * FROM Subjects;

这将为您提供学科列表：

请注意，只插入了三个科目 “代数、数据库课程和算法”，而不是 4 行。

具有违反主键约束的值的记录，即 “数据结构 ”被忽略，没有插入。但是，SQLite 会在该行之后继续执行其他语句。

步骤 4） DELETE 表主题，在下面的示例中使用不同的 ON CONFLICT 子句重新创建表，运行以下命令：

DROP TABLE Subjects;

drop 命令会删除整个表。现在表 Subjects 已不存在。

冲突替换示例

步骤 1） 创建一个新表 Subject，如下所示：

CREATE TABLE [Subjects] (
[ SubjectId ] INTEGER NOT NULL PRIMARY KEY ON CONFLICT REPLACE,
[ SubjectName ] NVARCHAR NOT NULL
);

请注意，我们在 SubjectId 列上定义了 PRIMARY KEY 约束。主键约束不允许在 SubjectId 列中插入两个重复的值，因此该列中的所有值都应该是唯一的。

另外，请注意我们选择的冲突解决选项是 “REPLACE”。命令应该可以成功运行，而且不会出现任何错误：

步骤 2） 现在，让我们在新表 Subjects 中插入一些值，但这些值要违反主键约束：

INSERT INTO Subjects VALUES(1, 'Algebra');
INSERT INTO Subjects VALUES(2, 'Database Course');
INSERT INTO Subjects VALUES(2, 'Data Structures');
INSERT INTO Subjects VALUES(4, 'Algorithms');

在这些 INSERT 语句中，我们试图插入两个具有相同主键 Subject id 2 的课程，这违反了主键约束。

这些命令应该运行正常，不会出现任何错误。如下所示：

步骤 3） 从表格中选择所有主题，如下所示：

SELECT * FROM Subjects;

这将为您提供学科列表：

请注意，只插入了三个科目 “代数、数据结构和算法”，而我们试图插入 4 行。

有违反主键约束的值的记录 “数据结构”替换了值 “数据库课程”，如下所示：

• 前两条插入语句运行正常，没有问题。两个科目 “代数 ”和 “数据库课程 ”将以 id 1、2 插入。
• 当 SQLite 尝试以 SubjectId 2 和 SubjectName “ Data Structures ” 运行第三条插入语句时，发现已经有一个 SubjectId = 2 的主题。这违反了 SubjectId 列上定义的主键约束。
• SQLite 将选择 REPLACE 来解决这一冲突。它会用插入语句中的新值替换 subject 表中已经存在的值。因此，“数据库课程”主题名将被替换为 “数据结构”主题名。

摘要：

INSERT、UPDATE 和 DELETE 子句用于修改 SQLite 数据库中的数据。CONFLICT 子句是一个功能强大的子句，用于解决数据与要修改的数据之间的任何冲突。