GBase 8t数据更新

插入数据

插入元组

insert into 表名(列名...) values (值....)
insert into customer values(106,"王伟")
insert into customer (customer_num,cname) values(106,"王伟");

插入子查询

insert into 表名(列名...) 子查询;
insert into customerChina(customer_num,cname,phone)
select customer_num,cname,phone from customer where state = "中国"；

修改数据

update 表名
	set 列名1 = 新值[,列名2 = 新值2....]
where条件

删除数据

delete from 表名
where 条件

删除王伟的信息
delete from customer where cname  = '王伟';
删除所有人的信息
delete from customer;

清空表数据

truncate table customer;

merge 语句

merge用法示例：在条件满足时更新特定的行，并且在条件不满足的时候想同一个表插入行。

	create table customer_source(
		customer_num INTEGER not null,
		cname CHAR(15),
		company CHAR(20),
		primary key(customer_num) constraint PK_CUSTOMER_SOURCE
	);

	create table customer_target(
		customer_num INTEGER not null,
		cname CHAR(15),
		company CHAR(20),
		primary key(customer_num) constraint PK_CUSTOMER_TARGET
	);

-- 分别插入两条数据
insert into customer_source values(101,'刘洋','GBase');
insert into customer_source values(102,'宋飞','GBase');
insert into customer_target values(101,'刘洋','IBM');
insert into customer_target values(103,'李佳','GBase');

-- 用源表customer_source对目的表customer_target执行merge，主键匹配则更新，否则插入
merge into customer_target as ct
using customer_source as cs
on ct.customer_num = cs.cusomer_num
when matched then update set ct.cname = cs.cname,ct.company=cs.company
when not matched then insert values(cs.customer_num,cs.cname.cs.comppany);

-- 执行merge后的目的表数据为：
select * from customer_target;

customer_num	cname	company
101	刘洋	GBase
103	李佳	GBase
102	宋飞	GBase