SQLite 插入、更新、删除查询示例

插入、更新和删除查询 SQLite

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

请注意，对于以下所有示例，您必须运行 sqlite3.exe 并打开与示例数据库的连接，如下所示：

步骤1） 在此步骤中，

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

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

.open TutorialsSampleDB.db

现在您已准备好在数据库上运行任何类型的查询。

SQLite 插入

SQLite INSERT 用于将记录插入数据库的指定表中。您必须使用“INSERT”子句。INSERT 子句语法如下：

• 在 INSERT 子句之后，您应该说明需要将值插入到哪个表中。
• 在表名之后写下要插入值的列表。
• 您可以忽略列名并且不向其写入内容。
• 如果不写列名，则值将以相同的顺序插入到表中找到的所有列中，这些列在表中定义。
• 在 VALUES 子句之后，您应该列出要插入的值。
• 每个 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 更新

SQLite UPDATE 查询用于修改表中的现有记录。您可以使用 WHERE 子句和 UPDATE 查询来更新选定的行。UPDATE 子句通过更改特定列的值来更新表。以下是 UPDATE 子句的语法：

如下：

• 在“更新子句”之后，你应该写上要更新的表名。
• 您必须编写“SET 子句”，用于写入要更新的列名和要更新的值。
• 您可以更新多个列。每行之间可以使用逗号。
• 您可以指定 WHERE 子句来仅指定某些行。只有表达式求值为真的行才会更新。如果您未指定 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;

现在您应该看到 Department Id 值现在为 3，如下所示：

SQLite 删除

SQLite DELETE 查询用于从指定表中删除现有记录。您可以将 WHERE 子句与 DELETE 查询一起使用来删除选定的行。

DELETE 子句具有以下语法：

• 在 DELETE FROM 子句之后，必须指定要从中删除记录的表名。（注意：DELETE 子句用于从表中删除部分或全部记录，但不会删除表本身。而 DROP 子句用于删除包含所有记录的整个表。）
• 如果您编写这样的 DELETE 子句“DELETE FROM guru”，这将从表“guru”中删除所有记录。
• 如果要删除某些特定行，可以使用表达式指定 WHERE 条件。只有表达式计算结果为真的行才会被删除。例如，“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”仅对 ID 为 11 和 12 的学生成立。因此，DELETE 子句将应用于这两个学生，并且只会删除它们。

此命令应该成功运行，并且您不应该得到如下任何输出：

您可以通过从学生表中选择所有记录来验证这两名学生是否已被删除，如下所示：

SELECT * FROM Students;

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

SQLite 冲突条款

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

例如，如果某列具有 UNIQUE 约束，而您尝试插入已存在的值（重复值），而该值与 UNIQUE 约束相冲突。那么 CONFLICT 子句会让您选择在这种情况下应采取何种措施来解决此冲突。

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

数据库事务

数据库事务是指 SQLite 操作（插入、更新或删除）。数据库事务必须作为一个单元执行，要么所有操作都成功执行，要么全部不执行。如果其中一个操作执行失败，则所有操作都将被取消。

数据库事务的示例

将资金从一个银行账户转移到另一个银行账户的交易将涉及几个活动。此交易操作包括从第一个账户中提取资金并将其存入另一个账户。此交易必须完全完成或完全取消，不能中途失败。

以下是您可以在 CONFLICT 子句中选择的五种解决方案的列表：

1. 回滚 – 这将回滚当前交易。 SQLite 存在冲突的语句（它将取消整个事务）。例如，如果您尝试更新 10 行，而第五行的值与约束冲突，则不会更新任何行，10 行将保持不变。将引发错误。
2. 中止 – 这将中止（取消）当前操作。 SQLite 仅更新存在冲突的语句，事务不会被取消。例如，如果您尝试更新 10 行，而第五行的值与约束冲突，则只有第五行的值不会被更新，而其他 9 行将被更新。将引发错误。
3. 失败——中止当前操作。 SQLite 存在冲突的语句。但是，事务不会继续，但对存在冲突的行之前的行所做的更改将被提交。例如，如果您尝试更新 10 行，而第五行的值与约束冲突，则只有 4 行会被更新，而其他行不会更新。将引发错误。
4. 忽略 – 这将跳过包含约束冲突的行，并继续处理后续的其他行。 SQLite 语句。例如，如果您尝试更新 10 行，而第五行的值与约束冲突，则只有 4 行会被更新，其他行不会更新。它不会继续更新其他行并停止在具有冲突值的行。不会抛出任何错误。
5. 替换——这取决于违反约束的类型：
   • 当 UNIQUE 或 PRIMARY KEY 约束违反约束时。 REPLACE 将使用新插入或更新的行替换导致违反的行。
   • 当违反 NOT NULL 约束时，REPLACE 子句将用该列的默认值替换 NULL 值。如果该列没有默认值，则 SQLite 将中止该语句（语句将被取消）
   • 如果发生 CHECK 约束违规，该子句将被中止。

注：以上五种解决方案仅供参考，并非解决冲突的通用方法。适用于解决一种冲突的方法未必适用于解决其他类型的冲突。

如何声明冲突条款

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

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

冲突时忽略示例

步骤1） 创建新表subject如下：

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

请注意，我们已在 SubjectId 列上定义了主键约束。主键约束不允许在 SubjectId 列中插入两个重复值，从而确保该列中的所有值都是唯一的。另外，请注意，我们选择的冲突解决方式为“忽略”。

该命令应该成功运行并且您不应该收到任何错误：

步骤2） 现在，让我们在新表主题中插入一些值，但其中一个值违反了主键约束：

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 语句中，我们尝试插入两个具有相同主键主题 ID 2 的课程，这违反了主键约束。

命令应该可以正常运行，并且不会出现任何错误。如下所示：

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

SELECT * FROM Subjects;

这将为您提供主题列表：

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

具有违反主键约束（即“数据结构”）的值的行被忽略并且未插入。但是， SQLite 继续执行该行之后的其他语句。

步骤4） 通过运行以下命令，删除表主题并使用以下示例的不同 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 列中，因此该列中的所有值都应该是唯一的。

另外，请注意，我们选择的冲突解决选项是“替换”。该命令应该能够成功运行，您不会收到任何错误信息：

步骤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 语句中，我们尝试插入两个具有相同主键主题 ID 2 的课程，这违反了主键约束。

命令应该可以正常运行，并且不会出现任何错误。如下所示：

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

SELECT * FROM Subjects;

这将为您提供主题列表：

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

违反主键约束的行（值为“数据结构”）已按如下方式替换了值“数据库课程”：

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

常见问题