GBase 8s SQL基础语法—数据操纵语言DML

GBase 8s SQL基础语法—DML

数据操纵语言语句

• INSERT
• UPDATE
• DELETE
• MERGE

注： MERGE 可模拟 INSERT 和 DELETE 或 UPDATE。

INSERT 语句

使用 INSERT 语句来向表或视图内插入一个或多个新行，或向 SQL 或 GBase 8s ESQL/C 集合变量内插入一个或多个元素。

语法格式

INSERT INTO <TABLE|VIEW|SYNONYM> [(COLUMN{[,COLUMN]})] <INSERT_ACTION>;
<INSERT_ACTION>::= VALUES(<值表达式>)
                |<子查询表达式>
                |EXECUTE <ROUTINE>
INSERT [ALL|FIRST] WHEN <BOOL_EXP> THEN <INSERT_INTO_LIST> {[WHEN <BOOL_EXP> THEN <INSERT_INTO_LIST>]} [ELSE <INSERT_INTO_LIST>] <INSERT_ACTION>;
<INSERT_INTO_LIST>::= INTO <TABLE|VIEW|SYNONYM> [(COLUMN{,COLUMN})]

要将数据插入到表内，您必须或拥有该表或有对该表的 Insert 权限（请参阅 GRANT 语句）。要将数据插入视图内，您必须有所需要的 Insert 权限，且该视图必须满足在通过视图插入行中说明的要求。

如果该表或视图有数据完整性约束，则被插入的行必须满足该约束条件。如果不满足，则数据库服务器返回错误。如果将检查模式设置为 IMMEDIATE，则在每一 INSERT 语句的末尾检查所有指定的约束。如果将检查模式设置为 DEFERRED，则不检查所有指定的约束，直到该事务提交为止。

举例说明

例1 在 t_user1 表中插入一条用户信息： f_userid 为1， f_username 为gbasedbt。

insert into t_user1 values(1, 'gbasedbt');

例2 将子查询语句的结果集插入 t_user1 表中，从而达到数据翻倍。

insert into t_user1 select * from t_user1;

例3 通过执行例程子句完成数据插入 t_user1 表中。

create function fn_user_add(user_num int)
returning int as userid, varchar(20) as username
define i int;
define userid int;
define username varchar(20);
for i = 1 to user_num
    let userid = i;
    let username = concat('user_', to_char(i));  
    return userid, username with resume;
end for;   
end function;
insert into t_user1 execute function fn_user_add(10);

例4 将子查询的结果集有条件的插入到符合条件的表中。f_userid 小于10的记录插入 t_user2 表中，其余的记录插入 t_user3 表中。

insert all 
when f_userid <10 then into t_user2 
else into t_user3 
select f_userid, f_username from t_user1;

UPDATE 语句

使用 UPDATE 语句来更改表或视图中一个或多个现有的行的一个或多个列中的值。

UPDATE <TABLE|VIEW|SYNONYM> [AS alias] 
SET COLUMN = <值表达式> {[,COLUMN = <值表达式>]} [WHERE <CONDITION>];
UPDATE <TABLE|VIEW|SYNONYM> [AS alias] 
SET (COL,..,COL) = (<子查询表达式>,..,<子查询表达式>) [WHERE <CONDITION>];

使用 UPDATE 语句来更新任何下列类型的数据库对象或程序对象：

• 表中的一行：单个行、行的组或表中的所有行
• 集合数据类型的列中的元素
• 在命名的或未命名的 ROW 数据类型的列中，一个字段或所有字段。

随同 GBase 8s ，您还可使用此语句来更改在 GBase 8s ESQL/C 或 SPL 集合变量或 ROW 变量中一个或多个元素的值。

您必须或拥有该表，或对该表有 Update 权限。请参阅 GRANT 语句。要更新视图中的数据，您必须有 Update 权限，且该视图必须满足通过视图更新行中说明的要求。

UPDATE 语句的目标不可为 CREATE EXTERNAL TABLE 语句定义了的表对象。

使用 SET 子句来标识要更新的列并将值指定给每一列。
SET 子句支持下列语法格式：

• 单列格式，它将每一列与单个表达式配对
• 多列格式，它将多列的列表与通过一个或多个表达式返回的值相关联

举例说明

例1 将 t_user 表中 f_userid 为 1 的记录更新 f_age 为 20 。

update t_user set f_age = 20 where f_userid = 1;

例2 通过子查询结果与直接赋值多列更新 t_user 表的记录。 t_user 表中 f_userid 为 1 的记录修改为 f_userid 为 1 , f_username 与 f_userage 为子查询语句在 info 表中的结果集。

update t_user 
set (f_userid,f_username,f_userage)=(1,(select i_username,i_userage from info where i_uid = 1))
where f_userid = 1;

DELETE 语句

使用 DELETE 语句从表中删除一行或多行，或者在 SPL 或 GBase 8s ESQL/C 的集合变量中删除一个或多个元素。

DELETE FROM <TABLE|VIEW|SYNONYM> [AS alias] [WHERE <CONDITION>];

使用 DELETE 语句移除以下任意类型的数据库对象或程序对象：

• 表中或视图中的行：一行，一组行或所有的行
• 集合数据类型的列中的元素
• 已命名或未命名 ROW 数据类型的列、一个字段或所有字段。

您还可以使用此语句移除 GBase 8s ESQL/C 或 SPL 集合变量或 ROW 变量中的一个或多个元素的值。

举例说明

例1 删除 t_user1 表中 f_userid 为 1 的记录。

delete from t_user where f_userid = 1;

MERGE 语句

使用 MERGE 语句，通过在单个 SQL 语句内综合 UPDATE 或 DELETE 操作与 INSERT 操作，来将数据从源表转移到目标表内。您还可使用此语句来将源表与目标表合并，然后对目标表仅执行 UPDATE 操作，仅执行 DELETE 操作，或仅执行 INSERT 操作。

MERGE 语句以 GBase 8s 扩展支持 SQL 的 ANSI/ISO 标准。

MERGE INTO <TARGET_TABLE|TARGET_VIEW|TARGET_SYNONYM> [AS alias] 
USING <SOURCE_TABLE|SOURCE_VIEW|<子查询表达式>> [AS alias] ON <CONDITION>
WHEN MATCHED THEN <UPDATE|DELETE> <SET_CLAUSE>
WHEN NOT MATCHED THEN INSERT [(COLUMN{[,COLUMN]})] VALUES(<值表达式>);

GBase 8s 的 MERGE 语句是数据操纵语言（DML）语句，将源表对象与目标表或视图结合。您在 ON 关键字之后指定的 condition 决定在目标上的 UPDATE 或 DELETE 操作中使用源对象的哪一行，以及在目标上的 INSERT 操作中使用哪一行。MERGE 语句不更改源对象。

• 如果您指定 Update 和 Insert 两个子句，则 MERGE 语句可在目标对象上执行 INSERT 和 UPDATE 两个操作。
• 如果您指定 Delete 和 Insert 两个子句，则 MERGE 语句可在目标对象上执行 INSERT 和 DELETE 两个操作。
• 如果您未指定 Insert 子句，则不执行 INSERT 操作，但 Update 子句必须在满足该条件的源行的目标对象上指定 UPDATE 操作（抑或 Delete 子句必须指定 DELETE 操作）。
• 如果您未指定 Update 子句且未指定 Delete 子句，则不执行 UPDATE 或 DELETE 操作，但 Insert 子句必须为不匹配该对象的源行指定目标对象上的 INSERT 操作。
• 如果未指定 Delete 子句，未指定 Update 子句，且未指定 Insert 子句，则 MERGE 语句失败并提示错误。

MERGE 语句可对目标对象产生下列影响：

• 如果包括 Update 子句，则 MERGE 语句根据 SET 子句的规范，以源表中该条件取值为真的行的数据更新目标表或视图中的行。
• 如果包括 Delete 子句，则 MERGE 语句从目标表或视图删除该条件取值为真的行。
• 如果包括 Insert 子句，则 MERGE 语句根据 VALUES 子句的规范，以源表中该条件取值为假的行数据，向目标表或视图内插入新行。

然而，单个 MERGE 语句仅可有这三个影响中的两个，因为 Delete 子句与 Update 子句是互斥的。

对于大型表上的操作，请确保在您的系统上可获得这些资源：

• 足够数量的锁
• 用于中间的结合结果的足够的临时 dbspace 存储
• 用于 MERGE 语句的结果的充足的 dbspace 存储。

在高可用性集群配置中，您可从主服务器或从可更新的辅助服务器发出 MERGE 语句。

举例说明

例1 已有用户表 t_user1 和用户表 t_user2。将 t_user1 表作为目标表，将 t_user2 表作为源表，连接条件为 t_user1 表的 f_userid 与 t_user2 表的 f_userid 相等。当记录匹配连接条件时，将 t_user2 表的 f_age 更新到 t_user1 表的 f_age； 当记录不匹配连接条件时，将 t_user2 表的 f_userid、f_username、f_age 插入一条新记录到 t_user1 表。

merge into t_user1 a
using t_user2 b
on a.f_userid = b.f_userid
when matched then update set a.f_age = b.f_age
when not matched then insert (a.f_userid, a.f_username, a.f_age) values(b.f_userid, b.f_username, b.f_age);