人大金仓数据库KingbaseES INSERT…SET…语法介绍

数据猿 2023-11-09

1760

金仓数据库KingbaseES INSERT… SET … 语法介绍

关键字：

KingbaseES、INSERT、MYSQL、人大金仓

INSERT … SET …

INSERT命令可以往表中插入新的一行或多行数据。通常大家熟悉的插入语法如下：

INSERT [INTO] table_name(col_name,col_name,…) VALUES(value, value,…);

有所不同的是在MYSQL数据库中支持了INSERT INTO … SET …语法，可以对待插入的单行数据进行逐项赋值。MYSQL官方文档语法如下：

INSERT [INTO] table_name

SET assignment_list

[AS row_alias[(col_alias[, col_alias]…)]]

[ON DUPLICATE KEY UPDATE assignment_list]

在kingbaseES数据中也支持相应的操作，有所不同的是MYSQL只支持SET语句的一列一列区赋值，在KingbaseES中支持多列同时SET赋值。在KingbaseES的手册中相应的语法如下：

INSERT INTO table_name

SET { column_name = { expression | DEFAULT } |

( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |

( column_name [, ...] ) = ( sub-SELECT )} [, ...]

[AS row_alias[col_name_alias]]

[ON DUPLICATE KEY UPDATE expression[RETURNING col_name_list]];

可以看到在KingbaseES中支持在INSERT...SET…中多列同时采用表达式或子查询的方式赋值。下面使用测试用例对比说一下区别。

与MYSQL对比

首先使用下面的命令在MYSQL和KingbaseES数据库中建立相应的表：

Create table test_table(c1 int primary key, c2 text, c3 text);

INSERT…SET…逐列插入

MYSQL数据库

Insert into test_table set c1=1, c2='this is c2', c3='this is c3';

Select * from test_table;

KingbaseES数据库

Insert into test_table set c1=1, c2='this is c2', c3='this is c3';

Select * from test_table;

可以看出在执行单列逐项插入时，MYSQL和KingbaseES数据没有差异。

多列采用表达式的方式赋值

MYSQL数据库

Insert into test_table set c1=2, (c2,c3)=('mulit_insert_c2', 'mulit_insert_c3');

Select * from test_table;

KingbaseES数据库

Insert into test_table set c1=2, (c2,c3)=('mulit_insert_c2', 'mulit_insert_c3');

Select * from test_table;

这里可以看出明显差异，在MYSQL数据库中并不支持这种同时对多列进行set的方式，而在KingBaseES数据库中支持这种赋值方式。这也仅仅是语法差异而已，只是MYSQL使用这种SET的方式赋值比较繁琐。

（3）子查询方式赋值

在进行进行子查询赋值之前建立一个待查询的表，并往表中插入相应数据。

Create table tt(c1 int, col2 text, col3 text);

Insert into tt values(3, 'sub_select2', 'sub-select3');

MYSQL

Inser into test_table set c1=(select c1 from tt);

Insert into test_table set (c1, c2, c3)=(select * from tt);

Select * from test_table;

KingbaseES

Insert into test_table set (c1, c2, c3)=(select * from tt);

这可以看出mysql并不支持子查询的方式去set赋值，而在KingbaseES数据库中支持了子查询的方式赋值。Insert…set…子查询的赋值方式更加灵活，你可以在查询语句中使用任何select语句的，你只需要确保查询结果只有一行，数据类型和数据数量与等号前面的列相符。

子查询的INSERT…SET…

现在，来测试一下支持什么样的子查询吧。首先使用下述命令建立多个表，并插入相应数据。

create table emp1(id int primary key, grade varchar(100) default('a_sbkdg'));

create table emp2(id int primary key, name_str varchar(100) default('TOM'));

create table emp3(name_str varchar(100), grade varchar(100) default('a_sdad'));

insert into emp2 values(1,'sdsd'), (2, default), (3, 'john');

insert into emp3 values('sdsd', 'msdajnh'), ('TOM', default), ('john', '15646487'), ('TOM', '1841.684');

上述命令，建立了3个表，并往其中插入了相应的数据。首先测试最简单的查询语句

insert into emp1 set (id,grade)=(select 0, 'set subselect');

然后是where条件语句和join连接语句

insert into emp1 set (id,grade)=(select id,grade from emp2 left join emp3 on emp2.name_str=emp3.name_str where emp2.name_str = 'sdsd');

排序和limit语句

insert into emp1 set (id,grade)=(select id,grade from emp2 left join emp3 on emp2.name_str=emp3.name_str order by emp2.id limit 1 offset 1);

Group和聚集函数

insert into emp1 set (id,grade)=(select count(*)+1, cast(count(*) as varchar) FROM emp2 FULL OUTER JOIN ONLY emp3 ON emp2.name_str=emp3.name_str group by emp2.name_str having emp2.name_str='TOM' limit 1);

窗函数

insert into emp1 set (id,grade)=(select emp2.id+2,cast(count (*) OVER win as varchar) as _count FROM emp2 FULL OUTER JOIN ONLY emp3 ON emp2.name_str=emp3.name_str where emp2.name_str='TOM' WINDOW win AS(PARTITION BY emp3.name_str) limit 1);

Insert冲突语句

insert into emp1 set (id,grade)=(select id,grade from emp2 left join emp3 on emp2.name_str=emp3.name_str where emp2.name_str = 'sdsd') as prev on duplicate key update (id,grade)=(prev.id, prev.grade) returning *;