数据增删

大约 3 分钟

数据增删

1. 数据写入

语法：

INSERT INTO <TABLE_NAME> [(COLUMN_NAME[, COLUMN_NAME]*)]? VALUES (COLUMN_VALUE[, COLUMN_VALUE]*)

更多详细语法说明请参考：写入语法

示例一：指定列写入

INSERT INTO table1(region, plant_id, device_id, time, temperature, humidity) VALUES ('北京', '1001', '100', '2025-11-26 13:37:00', 90.0, 35.1);

INSERT INTO table1(region, plant_id, device_id, time, temperature) VALUES ('北京', '1001', '100', '2025-11-26 13:38:00', 91.0);

示例二：空值写入

上述部分列写入等价于如下的带空值写入

INSERT INTO table1(region, plant_id, device_id, model_id, maintenance, time, temperature, humidity) VALUES ('北京', '1001', '100', null, null, '2025-11-26 13:37:00', 90.0, 35.1);

INSERT INTO table1(region, plant_id, device_id, model_id, maintenance, time, temperature, humidity) VALUES ('北京', '1001', '100', null, null, '2025-11-26 13:38:00', 91.0, null);

示例三：多行写入

INSERT INTO table1
VALUES 
('2025-11-26 13:37:00', '北京', '1001', '100', 'A', '180', 90.0, 35.1, true, '2025-11-26 13:37:34'),
('2025-11-26 13:38:00', '北京', '1001', '100', 'A', '180', 90.0, 35.1, true, '2025-11-26 13:38:25');

INSERT INTO table1
(region, plant_id, device_id, model_id, maintenance, time, temperature, humidity, status, arrival_time) 
VALUES 
('北京', '1001', '100', 'A', '180', '2025-11-26 13:37:00', 90.0, 35.1, true, '2025-11-26 13:37:34'),
('北京', '1001', '100', 'A', '180', '2025-11-26 13:38:00', 90.0, 35.1, true, '2025-11-26 13:38:25');

示例四：查询写回

insert into target_table select time,region,device_id,temperature from table1 where region = '北京';

insert into target_table(time,device_id,temperature) table table3;

insert into target_table (select t1.time, t1.region as region, t1.device_id as device_id, t1.temperature as temperature from table1 t1 where t1.time in (select t2.time from table2 t2 where t2.region = '上海'));

2. 数据更新

语法：

UPDATE <TABLE_NAME> SET updateAssignment (',' updateAssignment)* (WHERE where=booleanExpression)?

updateAssignment
    : identifier EQ expression
    ;

更多详细语法说明请参考：更新语法

示例：

update table1 set b = a where substring(a, 1, 1) like '%';

3. 数据删除

语法:

DELETE FROM <TABLE_NAME> [WHERE_CLAUSE]?

WHERE_CLAUSE:
    WHERE DELETE_CONDITION

DELETE_CONDITION:
    SINGLE_CONDITION
    | DELETE_CONDITION AND DELETE_CONDITION
    | DELETE_CONDITION OR DELETE_CONDITION

SINGLE_CONDITION:
    TIME_CONDITION | ID_CONDITION | ATTRIBUTE_CONDITION

TIME_CONDITION:
    time TIME_OPERATOR LONG_LITERAL

TIME_OPERATOR:
    < | > | <= | >= | =

ID_CONDITION:
    identifier = STRING_LITERAL

ATTRIBUTE_CONDITION:
    attribute_identifier (= | != | > | >= | < | <= | LIKE) STRING_LITERAL
    | attribute_identifier IS [NOT] NULL
    | attribute_identifier IN (STRING_LITERAL [, STRING_LITERAL] ...)

支持时间条件、标签条件和属性条件，条件之间仅可使用 AND 和 OR 运算符连接。
时间条件支持 >、<、=、<=、>= 五种运算符；标签条件目前仅支持 = 运算符。
自 V2.0.12.1 起，属性条件支持 =、!=、IS NULL、IS NOT NULL、>、>=、<、<=、LIKE 和 IN。左值须直接使用属性列名，不支持包含属性列的表达式；右值仅支持字符串常量。
每个包含属性过滤的合取式（由 AND 连接的一组条件）最多匹配 1000 个设备，超过时拒绝整条删除语句。可增加属性过滤条件以缩小范围，或移除所有属性过滤条件后重新确认删除范围。

示例一: 删除全表数据

全表删除

DELETE FROM table1;

示例二：删除一段时间范围内的数据

单时间段数据删除

DELETE FROM table1 WHERE time <= 2024-11-29 00:00:00;

多时间段数据删除

DELETE FROM table1  WHERE time >= 2024-11-27 00:00:00  and time <= 2024-11-29 00:00:00;

示例三：删除指定设备的数据

删除指定设备的数据

DELETE FROM table1 WHERE device_id='101' and model_id = 'B';

删除指定设备及时间段的数据

DELETE FROM table1 
    WHERE time >= 2024-11-27 16:39:00  and time <= 2024-11-29 16:42:00 
    AND device_id='101' and model_id = 'B';

删除指定类型设备的数据

DELETE FROM table1 WHERE model_id = 'B';

自 V2.0.12.1 起，也可以将属性列与时间条件组合使用：

DELETE FROM table1
WHERE model_id = 'B' AND time >= 2024-11-27 16:39:00;

4. 设备删除

语法：

DELETE DEVICES FROM tableName=qualifiedName (WHERE booleanExpression)?

示例：删除指定设备及其相关的所有数据

DELETE DEVICES FROM table1 WHERE device_id = '101';