【SQLite学习笔记】插入数据

综述

插入命令的关键字是INSERT，插入命令的图示如下

命令图中的WIth语句信息参见文章：【SQLite学习笔记】With语句

常见用法

插入命令通常用于插入一条数据到数据库中，可以只指定其中部分列的数据。
我们通过下面命令创建一个数据表insert_study_table。

CREATE TABLE IF NOT EXISTS insert_study_table
(
    id    INTEGER PRIMARY KEY AUTOINCREMENT,
    integer_value INTEGER,
    real_value    REAL,
    text_value    TEXT
);

如下命令可以向表中插入一条数据：

INSERT INTO insert_study_table(integer_value, real_value, text_value)VALUES (1, 1.1, 'a');

插入后结果如下：

id	integer_value	real_value	text_value
1	1	1.1	a

上面命令中分几个部分

1. 先给出INSERT INTO insert_study_table表示要向insert_study_table表中插入数据。
2. 在括号中列举列名，指定数据顺序。
3. 指定通过VALUES的语法提供数据源
4. 按照列名顺序依次给出数据

上面列举列名这一步中并没有列举自增主键id列，后面的数据也相应不会赋值到id列。最终id列按照自增特性取默认值。我们也可以不列举列名，这样后面的参数需要依次填写所有四个列的值。

数据源

插入的数据有三种来源，VALUES、SELECT和DEFAULT。

VALUES数据源

通过VALUES直接指定要插入的数据在不同列的值，其它列使用默认值。这种方式灵活且实用，最为常见。

指定列

常见用法一章中给出的就是VELUES作为数据源的用法。我们只指定integer_value和 text_value，得到如下命令：

INSERT INTO insert_study_table(integer_value, text_value)VALUES (2, 'b');

结果为

id	integer_value	real_value	text_value
1	1	1.1	a
2	2	NULL	b

integer_value和 text_value两列使用了指定值，其它列使用了默认值。如果希望所有列的数据都填默认值，可以用DEFAULT数据源。如下命令会报错。

INSERT INTO insert_study_table()VALUES();

报错内容：

[SQLITE_ERROR] SQL error or missing database (near ")": syntax error)

一次添加多个数据

VALUES后面可以接多组数据，如下：

INSERT INTO insert_study_table(integer_value, real_value, text_value)VALUES (3, 1.3, 'c'),(4, 1.4, 'd'),(5, 1.5, 'e');

结果为

id	integer_value	real_value	text_value
1	1	1.1	a
2	2	NULL	b
3	3	1.3	c
4	4	1.4	d
5	5	1.5	e

SELECT数据源

把SELECT语句查询出的结果作为数据。通过UNION ALL，SELECT作数据源可以实现类型VALUES的效果。如下

INSERT INTO insert_study_table(integer_value, real_value, text_value) SELECT 6, 1.6, 'f' UNION ALL SELECT 7, 1.7, 'g';

结果

id	integer_value	real_value	text_value
1	1	1.1	a
2	2	NULL	b
3	3	1.3	c
4	4	1.4	d
5	5	1.5	e
6	6	1.6	f
7	7	1.7	g

更多的可以通过SELECT语句，插入将查出的数据。如下

INSERT INTO insert_study_table(integer_value, real_value, text_value) SELECT integer_value, NULL, text_value FROM insert_study_table WHERE id = 7;

结果

id	integer_value	real_value	text_value
1	1	1.1	a
2	2	NULL	b
3	3	1.3	c
4	4	1.4	d
5	5	1.5	e
6	6	1.6	f
7	7	1.7	g
8	7	NULL	g

DEFAULT数据源

如果希望每一列的数据都取默认值，可以使用DEFAULT指定。如下

INSERT INTO insert_study_table DEFAULT VALUES;

结果为

id	integer_value	real_value	text_value
1	1	1.1	a
2	2	NULL	b
3	3	1.3	c
4	4	1.4	d
5	5	1.5	e
6	6	1.6	f
7	7	1.7	g
8	7	NULL	g
9	NULL	NULL	NULL

注意：只有顶层INSERT语句可以使用DEFAULT VALUES，作为嵌套语句出现的INSERT语句不能使用。

冲突处理

插入命令可能和一些限制条件相冲突，SQLite支持多种处理方式，分别为ABORT（默认），FAIL，IGNORE，REPLACE和ROLLBACK。

关键字	处理方式
ABORT	1. 中止当前SQL语句； 2. 返回SQLITE_CONSTRAINT异常； 3. 回退当前SQL语句所做的任何更改，但是由同一事务中先前SQL语句引起的更改被保留，并且该事务保持活动。
ROLLBACK	1. 中止当前SQL语句； 2. 返回SQLITE_CONSTRAINT异常； 3. 除了在每个命令上创建的隐含事务外，如果没有事务处于活动状态，则与ABORT算法相同。
FAIL	1. 中止当前SQL语句； 2. 返回SQLITE_CONSTRAINT异常； 3. 不会回退先前对失败的SQL语句所做的更改，也不会结束事务。 FAIL行为仅适用于唯一性、NOT NULL和CHECK约束。违反外键约束会导致ABORT。
IGNORE	1. 跳过包含该约束冲突的一行，并继续处理SQL语句的后续行，就像没有出错一样； IGNORE不返回唯一性、NOT NULL和UNIQUE约束错误。但是处理外键约束的方法与ABORT类似。
REPLACE	1. 在插入或更新当前行之前删除导致约束冲突的预先存在的行，并且命令继续正常执行； 2. 如果发生NOT NULL约束冲突，如果该列有默认值，则将NULL值替换为该列的默认值，否则则使用ABORT算法。 3. 如果发生CHECK约束或外键约束冲突，则使用类似ABORT的方法

其中INSERT OR REPLACE INTO可以简化为REPLACE INTO。

表所属的数据库

schema-name表示要创建的表所属的数据库。只能填main, temp和附加数据库。其中main对应正式表；temp对应临时表；附加数据库对应在某一个附加数据库中创建的表。执行下述命令

INSERT INTO main.insert_study_table(integer_value, real_value, text_value) VALUES (10, 2.0, 'j');

结果

id	integer_value	real_value	text_value
1	1	1.1	a
2	2	NULL	b
3	3	1.3	c
4	4	1.4	d
5	5	1.5	e
6	6	1.6	f
7	7	1.7	g
8	7	NULL	g
9	NULL	NULL	NULL
10	10	2	j

注意：只有顶层INSERT语句可以使用schema-name，作为嵌套语句出现的INSERT语句不能使用。

UPSERT

UPSERT语句的关键字是ON CONFLICT，如果插入语句中指定列的数据和数据表原有数据之间有唯一性冲突（如PRIMARY KEY和UNIQUE），则不再插入一行，转为修改指定行的数据。可以单独指定需要修改的数据，不一定是INSERT语句中的字段。
UPSERT语句的命令图如下所示：

大致可以拆分为confict target和UPDATE语句两部分，通过格式

ON CONFLICT <conflict target> DO <UPDATE语句>

组成一个ON CONFLICT语句。所有ON CONFLICT语句组合起来，构成了UPSERT语句。
注意：最后一个ON CONFLICT语句中可以不包括conflict target，其它必须包括。

简单用法

如果希望插入一条数据，要求id为7，text_value为gg，但是如果已经存在id为7的列，则更新text_value为gg on conflict。可以执行如下命令

INSERT INTO insert_study_table(id, text_value) VALUES (7, 'gg') ON CONFLICT (id) DO UPDATE SET text_value = 'gg on conflict';

结果：

id	integer_value	real_value	text_value
1	1	1.1	a
2	2	NULL	b
3	3	1.3	c
4	4	1.4	d
5	5	1.5	e
6	6	1.6	f
7	7	1.7	gg on conflict
8	7	NULL	g
9	NULL	NULL	NULL
10	10	2	j

这里INSERT语句中需要指定id列的值，id列是一个PRIMARY KEY，具有唯一性，因此可以作为ON CONFLICT的判断对象。

conflict target

conflict target语句用于唯一的确认一个唯一性约束。其中支持填入多个indexed-column。indexed-column的命令图如下：

其中包括了列名，COLLATE和排序顺序。这里和创建约束时填写的一样就行。

UPDATE语句

UPSERT语句中的UPDATE子句只能做SET和WHERE子句，使用方式和顶层UPDATE语句相同。

使用多个conflict target

当需要对多个唯一性约束做校验时，可以使用多个conflict target，如果满足了前面的conflict target，那么后面的conflict target就不会被判断了，对应的更新语句也不会执行。
我们为表格增加一个联合唯一索引如下：

create unique index insert_study_table_integer_value_real_value_uindex
    on insert_study_table (integer_value, real_value);

则integer_value和real_value两列的组合必须是唯一的（各自可以重复）。执行下面几个命令：

仅id冲突，先校验id

INSERT INTO insert_study_table(id, integer_value, real_value)
VALUES (3, 3, 2.3)
ON CONFLICT(id) DO UPDATE SET text_value='on conflict id' ON CONFLICT(integer_value, real_value) DO UPDATE SET text_value='on conflict int_real';

仅id冲突，先校验联合索引

INSERT INTO insert_study_table(id, integer_value, real_value)
VALUES (4, 4, 2.4)
ON CONFLICT(integer_value ASC, real_value ASC) DO UPDATE SET text_value='on conflict int_real' ON CONFLICT(id) DO UPDATE SET text_value='on conflict id';

id和联合索引都冲突，先校验id

INSERT INTO insert_study_table(id, integer_value, real_value)
VALUES (5, 5, 1.5)
ON CONFLICT(id) DO UPDATE SET text_value='on conflict id' ON CONFLICT(integer_value, real_value) DO UPDATE SET text_value='on conflict int_real';

id和联合索引都冲突，先校验联合索引

INSERT INTO insert_study_table(id, integer_value, real_value)
VALUES (6, 6, 1.6)
ON CONFLICT(integer_value ASC, real_value ASC) DO UPDATE SET text_value='on conflict int_real' ON CONFLICT(id) DO UPDATE SET text_value='on conflict id';

结果：

id	integer_value	real_value	text_value
1	1	1.1	a
2	2	NULL	b
3	3	1.3	on conflict id
4	4	1.4	on conflict id
5	5	1.5	on conflict id
6	6	1.6	on conflict int_real
7	7	1.7	gg on conflict
8	7	NULL	g
9	NULL	NULL	NULL
10	10	2	j