CN - 简体中文
联 系 我 们
售前咨询
产品、解决方案咨询
售后咨询
微信关注：星环科技服务号
更多联系方式 >
星环分布式数据库
5.2
星环分布式数据库
5.2
本页导航
6.7.2 数据操作语言(DML)
更新时间：3/5/2024, 8:24:31 AM

DML 用于操作现有数据库表的数据。主要涉及的 SQL 语句有： INSERT、UPDATE、DELETE、MERGE INTO

INSERT

使用 INSERT 命令执行数据插入操作，按照插入数据的场景，所涉及的 SQL 语句， 主要包含插入单条数据、批量插入多条数据、子查询插入数据、插入数据至分区。

只有 Holodesk 和 Hyperbase/ES/Transaction Orc 等事务表可以使用 INSERT VALUES 或 BATCHINSERT BATCHVALUES 语句插入数据，其他类型表只能使用 INSERT SELECT 子查询语句插入数据。

推荐阅读:

插入单条数据

功能描述

  • 使用 INSERT INTO VALUES 实现单表单条数据的插入，一般适用于少量数据的手动写入场景。

语法格式

INSERT INTO <table_name> VALUES(<value1>, <value2>,...);
复制

参数说明

  • <table_name>：表名称。

  • <value>：插入数据单个字段值。

示例

例 68. 插入单条数据 
INSERT INTO t1 VALUES(2, 1, '2014-2-1', 'discount', 'NO20140201', 10.00,10);
复制

推荐阅读

批量插入多条数据

功能描述

  • 推荐使用 BATCHINSERT 语法手动实现批量插入功能，支持百列千行级别的 BATCHINSERT。

    此外，您还可以开启 BATCHINSERT RBO 优化，通过直接写入文件的方式获得批量写入性能的极大提升，开启方法如下：

    1. 登录 Transwarp Manager 平台。

    2. 找到 Quark 服务卡片，在配置标签页设置下述参数及值。

      参数 值 说明

      crux.rewriter.enabled

      true

      开启 SQL Rewrite

      crux.rewriter.preloaded

      io.transwarp.argodb.batchinsert.ArgodbBatchInsertRBO

      开启 BATCHINSERT RBO 优化

      holodesk.compaction.enabled holodesk.fullcompaction.enabled

      false

      避免 Server 执行 Compact 任务，批量写入完成后可设置为 true

      EXTRA_DRIVER_OPTS

      -XX:NewRatio=2 -XX:NewSize=858914816

      调整 JVM 参数，即调大 New 区的值

    3. 单击页面右上角的应用配置，然后单击重启。

      请在业务低峰期操作，避免影响业务正常运行。

语法格式

BATCHINSERT INTO <table_name>
BATCHVALUES (VALUES
(<value1>, <value2>, <value3>,...),
VALUES(<value1>, <value2>, <value3>,...),
VALUES (<value1>, <value2>, <value3>,...),
...);
复制

除以上 BATCHINSERT 方法外，您还可以通过 INSERT 语法实现批量插入，语法如下：

INSERT INTO <table_name>
VALUES (<value1>, <value2>, <value3>,...), (<value1>, <value2>, <value3>,...), (<value1>, <value2>, <value3>,...),...;
复制

参数说明

  • <table_name>：表名称。

  • <value>：插入数据单个字段值。

示例

例 69. 批量写入 
-- BATCHINSERT 语法
BATCHINSERT INTO t1 BATCHVALUES (
VALUES (3, 1, '2014-2-1', 'discount', 'NO20140201', 10.00,10),
VALUES(3, 2, '2023-2-28', 'online', 'NO20230228', 12.11,10),
VALUES (3, 3, '2019-10-1', 'wholesale', 'NO20191001', 8.99,1000));
复制
例 70. INSERT 语法批量插入 
INSERT INTO t1 VALUES(4, 1, '2022-2-1', 'online', 'NO20220201', 10.24,11),(4, 2, '2023-2-11', 'shop', 'NO20230211', 15.50,22),(4, 3, '2022-2-22', 'wholesale', 'NO20220222', 9.19,33);
复制

推荐阅读

子查询插入数据

功能描述

  • 使用 INSERT INTO SELECT 将子查询结果数据从源表写入目标表中。

  • 支持使用一个数据源将多个查询结果分别插入不同表中。

语法格式

INSERT INTO <table_name1>
(<column_name1>, <column_name1>,...)
SELECT <select_statement> FROM <source>;
复制

参数说明

  • <table_name>：表名称。

  • <column_name>：列名。

  • <select_statement>：子查询语句结果。其中的列需要和 <table_name> 的列数量一致且数据类型一一对应。

  • <source>：数据源。

示例

例 71. 子查询插入数据 
INSERT INTO t8_sub_t1 select * from t1;
复制

推荐阅读

插入数据至分区

  • 分区表中插入数据适用场景为：

    • 表的大小超过 2GB。

    • 表中包含历史数据，新的数据被增加到新的分区中。

功能描述

  • 使用 INSERT INTO TABLE PARTITION 的方式将数据插入指定的分区中

  • 支持手动插入的方式将数据写入进静态分区表中，只需要注意写入的数据符合与分区表的分区列相对应 。

  • 指定单值分区插入数据，分区不存在时，默认开启以下参数自动创建指定单值分区：

    set quark.partition.create.on.insert=true; -- 默认为 true
    复制

注意事项

  • 在插入数据时，需手动指定分区。

  • 在向单值静态分区表中插入数据时，由于已经指定了分区键的具体值，所以不需要查询分区列插入。

    但在向范围静态分区表中插入数据时，由于并没有指定分区列的具体值，所以需要查询分区列插入，并且分区列应放置在最后且该值需要处于范围分区以内。

语法格式

  • 数据写入单值静态分区表

    INSERT INTO TABLE <table_name>
PARTITION (<partition_key1> = <value>[, <partition_key2> = <value>, ...])
SELECT <select_statement> FROM <source>;
    复制

  • 数据写入范围静态分区表

    INSERT INTO TABLE <table_name>
PARTITION <partition_name>
SELECT <select_statement> FROM <source> WHERE <filter_statement>
    复制

参数说明

  • <table_name>：表名称。

  • <select_statement>：子查询语句结果，其中的列需要和 <table_name> 的列数量一致且数据类型一一对应。

  • <partition_key>：分区键。

  • <partition_name>：范围分区中的指定某个分区名称。

  • <value>：对应取值。

  • <filter_statement>：过滤条件语句，即需要过滤符合分区范围的值。

示例

例 72. 数据写入单值静态分区 
-- 指定分区插入，分区不存在自动创建
set quark.partition.create.on.insert=true;

-- 子查询写入
INSERT INTO TABLE t3_partition
PARTITION (trans_time = '2014-2-11')
SELECT t1.trans_id, t1.acc_num, t1.trans_type, t1.stock_id, t1.price, t1.amount FROM t1;

-- 手动写入数据
INSERT INTO t3_partition
PARTITION (trans_time = '2014-04-30')
VALUES(5, 1, 'discount', 'NO20140201', 10.00,10),(6, 1, 'sales', 'NO20140201', 10.00,10), (6, 2, 'sales', 'NO20230228', 12.11,10),(6, 3, 'sales', 'NO20191001', 8.99,1000);
复制
例 73. 数据写入范围静态分区 
--添加范围分区
ALTER TABLE t4_range_partition ADD PARTITION before2014_05 VALUES LESS THAN ('2014-05-30');

--子查询写入
INSERT INTO TABLE t4_range_partition
PARTITION before2014_05
SELECT t1.trans_id, t1.acc_num, t1.trans_type, t1.stock_id, t1.price, t1.amount, t1.trans_time FROM t1 WHERE t1.trans_time >= '2014-04-30' and t1.trans_time < '2014-05-30';

-- 手动写入单条数据
INSERT INTO t4_range_partition
PARTITION before2014_05
VALUES (3, 1, 'discount', 'NO20140201', 10.00,10, '2014-5-3');

--手动批量写入
INSERT INTO t3_partition
PARTITION before2014_05
VALUES(4, 1, 'sales', 'NO20140201', 10.00,10, '2014-5-4'), (4, 2, 'sales', 'NO20230228', 12.11,10, '2014-5-5'),(6, 3, 'sales', 'NO20191001', 8.99,1000, '2014-5-6');
复制

推荐阅读

UPDATE

功能描述

  • 使用 UPDATE TABLE 修改指定表指定列中的数据。

  • 本产品既支持对单列数据进行修改，也可以使用查询语句对多列数据批量修改。

注意事项

  • 如果需要对分区表中的数据进行修改时，需要修改参数 hive.crud.dynamic.partition=true（默认 false）。

语法格式

  • 修改单列的值

UPDATE <table_name>
[PARTITION <partition_name>] --[1]
[PARTITION (<partition_key> = <value>] --[2]
SET <column_name> = <value>
[PARTITION <partition_name>]
[PARTITION (<partition_key> = <value>]
[WHERE <filter_statement>];
复制

  • 修改多列的值

UPDATE  <table_name>
[PARTITION <partition_name>]
[PARTITION (<partition_key> = <value>]
SET (<column_name1>, <column_name2>, ...) = (SELECT <select_statement>)
[WHERE <filter_statement>];
复制

参数说明

  • <table_name>：表名称。

  • <column_name>：列名。

  • <select_statement>：子查询语句结果。其中的列需要和 <table_name> 的列数量一致且数据类型一一对应。

  • <filter_statement>：过滤条件语句。

  • <partition_name>：范围分区表中的指定分区的名称。

  • <partition_key>：分区键。

  • <value>:分区键对应值。

  • 可选项[1]，可以对范围分区表的指定分区中的数据进行修改。

  • 可选项[2]，可以对单值分区表的指定分区中的数据进行修改。

示例

例 74. 修改单列数据 
--普通表
UPDATE TABLE t1  SET trans_type = 'update';

--单值分区表
UPDATE t3_partition partition (trans_time='2014-2-11') SET trans_id=7 WHERE amount= 10;

--范围分区表
UPDATE t4_range_partition partition before2014_05 SET trans_id=7 WHERE amount= 10;
复制
例 75. 修改多列数据 
--普通表
UPDATE t1 SET trans_id=10, amount=1000 || 'changed' WHERE trans_date > '2014-2';

--范围分区表
UPDATE t4_range_partition partition before2014_05 SET trans_id=9, trans_type=  trans_type|| ' changed' WHERE amount= 10;

--单值分区表
UPDATE t3_partition partition (trans_time='2014-2-11') SET trans_id=8, trans_type=  trans_type|| ' changed' WHERE amount= 10;
复制

推荐阅读

MERGE INTO

功能描述

  • 对一个表同时执行 INSERT 和 UPDATE 操作。MERGE 命令从一个或多个数据源中选择行来 UPDATE 或 INSERT 到一个或多个表。

  • MERGE INTO 的作用就是解决用 B 表更新 A 表的数据。如果A表中没有，则把 B 表的数据插入 A 表或向一个表中插入数据，如果该表已有该数据则更新，反之新增数据。

注意事项

  • MERGE 的源表必须有化名。

  • 两表连接判定条件 <merge_condition> 的对应关系，必须为一一对应。

  • UPDATE 和 INSERT 子句是可选的，也可以加 WHERE 子句。

  • 在 ON 条件中使用常量过滤谓词来 INSERT 所有的行到目标表中,不需要连接源表和目标表。

  • MERGE INTO 中所有的 UPDATE、INSERT 都是针对目标表来操作的。由于 MERGE INTO 已经指定了操作的表，所以 UPDATE、INSERT 都不需要再显示指定表名，具体请参考相关示例。

语法格式

  • 非分区表

    MERGE INTO <table_name1> [table_rename1] --[1]
USING [table|view|subquery] <table_name2> <table_reanme2> --[2]
ON (<merge_condition>)
WHEN MATCHED THEN <update_statement>
WHEN NOT MATCHED THEN <insert_statement>;
    复制

  • 单值分区表

    MERGE INTO <table_name1>
PARTITION (<partition_key> = <value>)
USING [table|view|subquery] <table_name2> <table_reanme>
ON (<merge_condition>)
WHEN MATCHED THEN <update_statement>
WHEN NOT MATCHED THEN <insert_statement>;
    复制

  • 范围分区表

    MERGE INTO <table_name1>
partition <patition_name>
USING [table|view|subquery] <table_name2> <table_reanme>
ON (<merge_condition>)
WHEN MATCHED THEN <update_statement>
WHEN NOT MATCHED THEN <insert_statement>;
    复制

参数说明

  • <table_name1>：目标表名称。

  • <table_name2>：源表名称。

  • <table_reanme>：必须需对源表指定别名。

  • <patition_name>：范围分区分区名称。

  • <partition_key>：分区键。

  • <value>：分区键对应值。

  • <merge_condition>：两表连接判定条件，需要注意此处必须为一一对应。

  • <update_statement>：更新目标表数据语句。

  • <insert_statement>：目标表插入数据语句。

  • 可选项[1]，目标表也可以指定别名。

  • 可选项[2]，选择源数据类型为表，视图或是子查询语句。

示例

例 76. 插入并更新非分区表 
--创建源表并插入数据
CREATE TABLE t11_source(trans_id INT, acc_num INT,  trans_type STRING, stock_id STRING, price DECIMAL, amount INT, trans_time DATE)
STORED AS HOLODESK;
BATCHINSERT INTO t11_source BATCHVALUES (VALUES (7, 1, 'discount', 'NO2019098', 10.00,10,'2014-04-30'),VALUES(7, 2, 'online', 'NO20237889', 12.11,10,'2014-04-30'),VALUES (7, 3, 'wholesale', 'NO20140430', 8.99,1000,'2014-04-30'));

-- merge into 普通表
MERGE INTO t8_sub_t1 target
USING t1 source ON (target.stock_id=source.stock_id)
WHEN matched THEN UPDATE SET target.trans_type = source.trans_type || ' MERGE update'
WHEN NOT MATCHED THEN INSERT (trans_id, acc_num, trans_type, stock_id, price, amount) VALUES (source.trans_id, source.acc_num, source.trans_type || ' MERGE update', source.stock_id, source.price, source.amount);
复制
例 77. 插入并更新单值分区表 
MERGE INTO t3_partition PARTITION (trans_time='2014-2-11') target
USING t11_source source ON (target.stock_id=source.stock_id)
WHEN matched THEN UPDATE SET target.trans_type = source.trans_type || ' MERGE update'
WHEN NOT matched THEN INSERT (trans_id, acc_num, trans_type, stock_id, price, amount) VALUES (source.trans_id, source.acc_num, source.trans_type || ' MERGE update', source.stock_id, source.price, source.amount);
复制
例 78. 插入并更新范围分区表 
MERGE INTO t4_range_partition PARTITION before2014_05 target
USING t11_source source ON (target.stock_id=source.stock_id)
WHEN matched THEN UPDATE SET target.trans_type = source.trans_type || ' MERGE update'
WHEN NOT matched THEN INSERT (trans_id, acc_num, trans_type, stock_id, price, amount, trans_time) VALUES (source.trans_id, source.acc_num, source.trans_type || ' MERGE update', source.stock_id, source.price, source.amount, source.trans_time);
复制

推荐阅读

DELETE

功能描述

  • 使用 DELETE FROM WHERE 删除指定表中的对应数据。

语法格式

-- 非分区表
DELETE FROM <table_name> WHERE <filter_statement>;

-- 删除单值分区表中一个分区的部分内容
DELETE FROM <table_name> partition (<partition_key> = <value>) WHERE <filter_statement>;

-- 删除范围分区表中一个分区的部分内容
DELETE FROM <table_name> partition <partition_name> WHERE <filter_statement>;
复制

参数说明

  • <table_name>：表名称。

  • <column_name>：列名。

  • <partition_key>：分区键。

  • <partition_name>：范围分区中的指定某个分区名称。

  • <filter_statement>：过滤条件语句。

  • <value>：分区对应取值。

示例

例 79. 删除数据 
-- 非分区表
DELETE FROM t1 WHERE acc_num >= 2;

-- 删除单值分区表中一个分区的部分内容
DELETE FROM t3_partition PARTITION (trans_time = '2014-04-30')
WHERE trans_id = 2;

-- 删除范围分区表中一个分区的部分内容
DELETE FROM t4_range_partition PARTITION before2014_04 WHERE trans_id != 2;
复制

推荐阅读

本页导航