SQLite INSERT, UPDATE, DELETE 查询及示例

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

请注意，对于以下所有示例，您都需要运行 sqlite3.exe 并按如下方式打开与示例数据库的连接

步骤 1)在此步骤中，

打开“我的电脑”并导航到目录“C:\sqlite”，然后
接着打开“sqlite3.exe”

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

.open TutorialsSampleDB.db

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

SQLite INSERT

SQLite INSERT 用于向数据库的指定表插入记录。您需要使用“INSERT”子句。INSERT 子句的语法如下：

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

SQLite 插入示例

在以下示例中，我们将向 students 表中插入 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');

这应该会成功运行，并且没有输出

这将插入两个学生

第一个学生 ID 为 11，姓名 Ahmad，所在系 ID 为 4，出生日期为 1997-10-12。
第二个学生 ID 为 12，姓名 Aly，所在系 ID 为 4，出生日期为 1996-10-12。

在第一个语句中，我们列出了列名“StudentId, StudentName, DepartmentId, DateOfBirth”。但在第二个语句中，我们没有列出。

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

现在，让我们通过运行以下查询来验证这两个学生是否已插入到 Students 表中

SELECT * FROM Students;

然后，您应该会看到返回的这两个学生如下所示

SQLite Update

SQLite UPDATE 查询用于修改表中的现有记录。您可以使用 WHERE 子句与 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 子句会将所选学生的 Department Id 值更新为 3。

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

SELECT * FROM Students WHERE StudentId = 6;

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

SQLite Delete

SQLite DELETE 查询用于从指定表中删除现有记录。您可以将 WHERE 子句与 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”对于 ID 为 11 和 12 的学生将为 true。因此，DELETE 子句将应用于两者，并且只删除它们。

此命令应成功运行，并且您不应看到任何输出，如下所示

您可以通过运行以下命令选择 Students 表中的所有记录来验证这两个学生是否已被删除

SELECT * FROM Students;

您不应该看到 ID 为 11 和 12 的这两个学生，如下所示

SQLite 冲突子句

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

例如，如果一列具有 UNIQUE 约束，并且您尝试插入一个已存在的值（重复值），这与 UNIQUE 约束冲突。那么 CONFLICT 子句允许您选择在这些情况下该怎么做以解决此冲突。

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

数据库事务

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

数据库事务示例

将钱从一个银行账户转到另一个银行账户的事务将涉及多个活动。此事务操作包括从第一个账户取款，并将其存入另一个账户。此事务必须完全完成或完全取消，不能中途失败。

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

ROLLBACK – 这将回滚当前具有冲突的 SQLite 语句所在的事务（它将取消整个事务）。例如，如果您尝试更新 10 行，而第五行中的值与约束冲突，则不会更新任何行，这 10 行将保持不变。将抛出错误。
ABORT – 这将仅中止（取消）当前具有冲突的 SQLite 语句，而不会取消事务。例如，如果您尝试更新 10 行，而第五行中的值与约束冲突，则仅第五行值不会被更新，但其他 9 行将被更新。将抛出错误。
FAIL – 中止当前具有冲突的 SQLite 语句。但是，事务不会继续，但对冲突行之前的行的更改将已提交。例如，如果您尝试更新 10 行，而第五行中的值与约束冲突，则只会更新 4 行，其他行则不会。将抛出错误。

IGNORE – 这将跳过包含约束冲突的行，并继续处理 SQLite 语句的其他后续行。例如，如果您尝试更新 10 行，而第五行中的值与约束冲突，则只会更新 4 行，其他行则不会。它不会继续更新其他行，而是在具有冲突值的行处停止。不会抛出错误。

REPLACE – 它取决于发生违反的约束类型

当发生 UNIQUE 或 PRIMARY KEY 约束冲突时，REPLACE 将用新插入或更新的行替换导致冲突的行。
当发生 NOT NULL 约束冲突时，REPLACE 子句将用该列的默认值替换 NULL 值。如果该列没有默认值，则 SQLite 将中止该语句（语句将被取消）。
如果发生 CHECK 约束冲突，则该子句将被中止。

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

如何声明 CONFLICT 子句

您可以在 CREATE TABLE 子句的列定义中声明 ON CONFLICT 子句。使用以下语法：

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

ON CONFLICT IGNORE 示例

第 1 步：像下面这样创建一个新的 subject 表

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 步：像下面这样选择表中的所有 subjects

SELECT * FROM Subjects;

这将为您提供 subjects 列表

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

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

第 4 步：删除 subject 表，以便在下一个示例中用不同的 ON CONFLICT 子句重新创建它，方法是运行以下命令

DROP TABLE Subjects;

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

ON CONFLICT REPLACE 示例

第 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');