DML

(Data Manipulation Language)：数据操作语言，用来操作和业务相关的记录。

INSERT

该INSERT语句将一行或多行插入到表中。

执行一个单独的多行INSERT语句比多个单行的INSERT语句更快。往一张现有表中插入大量的数据时，建议使用多行INSERT语句代替多个单行INSERT语句。
往一张新表写入数据时，IMPORT语句性能比INSERT语句更好。
在传统的SQL数据库中，生成和回收唯一性的ID需要使用INSERT和SELECT。在hubble中，用RETURNING分句和INSERT代替。

所需权限

用户必须拥有该表的INSERT权限，要使用ON CONFLICT，用户还必须具有该表的SELECT权限。要使用ON CONFLICT DO UPDATE，用户必须另外拥有该表的UPDATE权限。

语法图

InsertIntoStmt
IntoOpt
TableName
AliasName
InsertValues
ReturnList
Target_List

参数介绍

参数	简介
`TableName`	写入数据的表
`table_alias_name`	表的别名
`column_name`	插入期间要填充的列的名称
`RETURNING`	根据插入的行返回值
`target_elem`	其中`target_elem`可以是表中的特定列名

示例

插入单行

insert into accounts (id, name, balance, flag, is_disabled) values (100, 'manus', 2000.00, 'false', '1');

select * from accounts;

  id  | name  | balance | flag  | is_disabled | is_status
------+-------+---------+-------+-------------+------------
   11 | mike  | 1000.00 | false | 1           | 1
   12 | jack  | 2000.00 | true  | 0           | 1
   13 | allen | 5555.30 | false | 1           | 0
   14 | oslwn | 6321.00 | true  | 0           | 0
   15 | ding  | 6984.00 | true  | 1           | 1
   16 | mark  | 9840.50 | true  | 0           | 0
   17 | jamms | 6530.20 | false | 0           | 0
   18 | haden | 1520.00 | true  | 1           | 1
  100 | manus | 2000.00 | false | 1           | NULL

插入多行

insert into accounts (id, name, balance, flag, is_disabled) values 
  (999, 'sinal', 7600.20, 'false', '0'), (19, 'feina', 5500.20, 'false', '1');

select * from accounts where id in (999, 19);

  id  | name  | balance | flag  | is_disabled | is_status
------+-------+---------+-------+-------------+------------
   19 | feina | 5500.20 | false | 1           | NULL
  999 | sinal | 7600.20 | false | 0           | NULL

ON CONFLICT更新值

当遇到唯一的冲突时，Hubble将该行存储在称为excluded的临时表中。以下示例演示在发生冲突时，如何使用临时表excluded中的列来应用更新。

insert into accounts (id, name, balance, flag, is_disabled) 
  values (999, 'sinal', 7600.20, 'false', '0') 
  on conflict (id) 
  do update set id =excluded.id;

使用现有值更新

insert into  accounts (id, name,balance, flag, is_disabled) 
  values (999, 'sinal', 7600.20, 'false', '0') 
  on conflict (id) 
  do update set balance=accounts.balance+excluded.balance;

select * from accounts where id=999;

  id  | name  | balance | flag  | is_disabled | is_status
------+-------+---------+-------+-------------+------------
  999 | sinal | 7600.20 | false | 0           | NULL

ON CONFLICT语句下使用WHERE条件

insert into accounts (id, name,balance, flag, is_disabled) 
  values (999, 'sinal', 7600.20, 'false', '0') 
  on conflict (id) 
  do update set balance=excluded.balance 
  where excluded.balance < accounts.balance;

select * from  accounts where id=999;

  id  | name  | balance | flag  | is_disabled | is_status
------+-------+---------+-------+-------------+------------
  999 | sinal | 7600.20 | false | 0           | NULL

ON CONFLICT不更新值

多行插入时，不冲突的值可被插入，冲突的不被执行

insert into accounts (id, name, balance, flag, is_disabled ) 
    values (321, 'alian', 76, 'false', '1'), (999, 'sinal', 7600.20, 'false', '0') 
    on conflict (id) 
    do nothing;

性能最佳实践

现有表

表已经存在的条件下

在事务中的一个语句中执行多行INSERT。
不要使用100000行或更多行的大批量，这可能会导致长时间运行的事务，从而导致事务重试错误。如果多行INSERT导致40001带有消息"transaction deadline exceeded"的错误代码，hubble建议将INSERT分成更小的批次。
通过监控隐式事务中不同批处理大小（1、10、100、1000）行的性能，通过实验确定应用程序的最佳批处理大小。
可以使用该IMPORT INTO语句批量插入CSV数据。

新表

表不存在

hubble建议您使用该IMPORT语句，因为它的性能优于INSERT。

DELETE

DELETE语句删除一个表中行的数据

如果你要删除由foreign key constraint引用的行，而且有on delete action，所有依赖这行数据的行将被删除或更新。

所需权限

要执行DELETE操作，用户必须拥有DELETE与SELECT权限

语法图

DeleteFromStmt
WhereClause
OrderByOpt
ReturnList
Target_List

参数介绍

参数	简介
`TableName`	包含要更新的行的表的名称
`TableAName`	包含要更新的行的表的别名
`a_expr`	要使用的值或要使用的标量表达式
`RETURNING target_list`	根据删除的行返回值，其中`target_list`可以是表中的特定列名、所有列或使用标量表达式的计算

删除后的磁盘空间使用情况

删除一行不会立即释放磁盘空间。这是因为hubble保留了历史查询表的能力。

如果磁盘使用是一个问题，解决方案是通过设置gc.ttlseconds为较低的值来减少区域的生存时间(TTL) ，这将导致垃圾收集更频繁地清理已删除的对象（行、表）。

选择已删除行的性能

扫描具有大量已删除行的表的查询将不得不扫描尚未被垃圾收集的删除。某些频繁扫描并删除大量行的数据库使用模式将希望减少生存时间值以更频繁地清理已删除的行。

对删除的输出进行排序

要对DELETE语句的输出进行排序，请使用：

WITH a AS (DELETE ... RETURNING ...)
  SELECT ... FROM a ORDER BY ...

示例

返回删除数据，为了知道删除了哪些数据，可以使用RETURNING字段取回已删除的数据。

返回所有列数据

delete from accounts where id < 12 returning * ;

  id | name | balance | flag  | is_disabled | is_status
-----+------+---------+-------+-------------+------------
  11 | mike | 1000.00 | false | 1           | 1

返回特定列的数据

delete from accounts where id < 13 returning id,name;

  id | name
-----+-------
  12 | jack

使用主键或者唯一列删除行

delete from accounts where id = 7369;

使用非唯一列删除行

delete from accounts where id < 1000;

UPDATE

UPDATE语句用于更新表中的数据。

所需权限

用户需要拥有表的SELECT和UPDATE权限。

语法图

UpdateTableStmt
TableRef
SelectOpt
WhereClause
ReturnList
Target_List

参数介绍

参数	简介
`TableName`	包含要更新的行的表的名称
`column_name`	要更新其值的列的名称
`a_expr`	要使用的新值、要执行的聚合函数或要使用的标量表达式
`select_stmt`	选择查询，每个值必须与其左侧列的数据类型`=`相匹配
`WHERE a_expr`	`a_expr`必须是使用列，例如 :返回布尔值的标量表达式`<column> = <value>`
`limit_clause`	一个`LIMIT`子句。有关更多详细信息，请参阅限制查询结果。
`RETURNING target_list`	根据更新的行返回值，其中`target_list`可以是表中的特定列名、所有列或使用标量表达式的计算

示例

根据单个字段进行更新

update accounts set balance=9856 where id =19;

select * from accounts where id=19;

  id | name  | balance | flag  | is_disabled | is_status
-----+-------+---------+-------+-------------+------------
  19 | feina | 9856.00 | false | 1           | NULL

根据多个字段进行更新

update accounts set balance=9856,name='anna' where id =10;

使用SELECT语句更新

update accounts 
  set (name, balance)=
  (select name, balance from accounts where  id=100) 
  where id=18;

select * from accounts where id=18;

  id | name  | balance | flag | is_disabled | is_status
-----+-------+---------+------+-------------+------------
  18 | manus | 2000.00 | true | 1           | 1

更新后返回值

update  accounts set balance=9986.8 where  name='jamms' returning id,name,balance;

  id | name  | balance
-----+-------+----------
  17 | jamms | 9986.80

在更新中引用其他表

要引用正在更新的表以外的表中的值，请添加一个FROM子句，指定一个或多个表。子句中指定的表中的值FROM可用于UPDATE表达式和WHERE子句中。

当使用子句执行UPDATE时，hubble将目标表（即正在更新的表）连接到子句中引用的表。此连接的输出应该与目标表中正在更新的行具有相同的行数，因为hubble使用连接输出中的单行来更新目标表中的给定行。如果连接产生的行多于目标表中正在更新的行，则无法预测连接输出中的哪一行将用于更新目标表中的行。

使用来自不同表的值进行更新

update cust c set name ='mike' from cust_info ci where c.id=ci.id ;

批量更新数据

要更新大量行(即数万行或更多行)，我们建议迭代更新要更新的行的子集，直到所有行都已更新。可以编写一个脚本来执行此操作，或者您可以在您的应用程序中编写一个循环。

TRUNCATE

TRUNCATE语句用于从表中删除所有行。在高层次上，它通过删除表并重新创建具有相同名称的新表来工作，可认为TRUNCATE 语句同 DROP TABLE + CREATE TABLE 组合在语义上相同。

TRUNCATE TABLE tableName 和 TRUNCATE tableName 均为有效语法

所需权限

用户需要拥有表的DROP权限

语法图

TruncateTableStmt
OptTable
Constrints

参数介绍

参数	简介
`TableName`	包含要更截断的表的名称
`CASCADE`	在被截断的表上截断所有具有外键依赖项的表
`RESTRICT`	默认）如果任何其他表具有外键依赖项，则不要截断该表

清空一张客户表

truncate dates;

等同于

truncate table dates;

表与emp表具有外键关系dept。因此，只能在截断dept表的同时截断从属emp表

对于含有外键的表，需要使用CASCADE关键字删除数据

truncate dept ;

pq: "dept" is referenced by foreign key from table "emp"

引入CASCADE关键字，进行清表

truncate dept cascade;

UPSERT

UPSERT语句在语义上等效于INSERT ON CONFLICT，但是两者的性能特征可能略有不同。该UPSERT语句在指定值不违反唯一性约束的情况下插入行，并在值确实违反唯一性约束的情况下更新行。

同INSERT类似，单个多行UPSERT语句比多个单行UPSERT语句快。尽可能使用多行UPSERT而不是多个单行UPSERT语句。

所需权限

用户需要拥有表的 INSERT ， SELECT 和 UPDATE 权限。

语法图

UpsertIntoStmt
IntoOpt
TableName
AliasName
NameList
UpdateValues
ReturnList
Target_List

参数介绍

参数	简介
`TableName`	写入数据的表
`table_alias_name`	表的别名
`column_name`	插入期间要填充的列的名称
`SelectStmt`	选择查询，每个值必须与其列的数据类型匹配。
`DEFAULT VALUES`	要使用默认值填充所有列
`RETURNING`	根据插入的行返回值
`target_elem`	其中`target_elem`可以是表中的特定列名

示例数据：

create table a (id int primary key, name string);
insert into  a  values (1,'zhangsan'),(2,'mike');

id列是主键，因为插入的id值不与id任何现有行的值冲突，所以该UPSERT语句将新行插入到表中，如下语句：

upsert into a (id, name) values (3, 'lisi');

select  * from a;

 id |   name
-----+-----------
   1 | zhangsan
   2 | mike
   3 | lisi

若插入的id值不是唯一的，所以该UPSERT语句使name更新，语句如下：

upsert into a (id, name) values (3, 'liuli');

select * from a;

  id |   name
-----+-----------
   1 | zhangsan
   2 | mike
   3 | liuli

当唯一性冲突不在主键中的列上时，UPSERT将不会更新。

下表中id列是主键，但是aml列具有唯一约束，UPSERT将不会生效。

create table b (id int primary key, aml int, unique (aml));
insert into  b  values (1,1000),(2,2000);

upsert into  b (id, aml) values (3, 1000);

duplicate key value violates unique constraint "b_aml_key"

由于aml字段创建了唯一性约束，所以更新失败

如果非主键字段有唯一约束，不能使用UPSERT INTO语法，使用INSERT INTO ... ON CONFLICT ... DO UPDATE ...语句进行操作。

下载PDF

本页导航

INSERT
- 示例
- 性能最佳实践
DELETE
UPDATE
TRUNCATE
UPSERT