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 |
无不读书神仙,有打瞌睡豪杰。