> 准备工作
🍩连接openGauss
su - omm
gsql -r复制
> 作业打卡
1.创建表1并在表中插入数据，分别指定字段和整行为缺省值
CREATE TABLE t1
(sn integer,
id character(16),
des character(20));
insert into t1 values(1, 'AAAAAAAABAAAAAAA', 'reason1');
insert into t1 values(1, 'AAAAAAAABAAAAAAA'); -- 没有数值的字段将被填充为字段的缺省值
insert into t1 values(1, 'AAAAAAAABAAAAAAA', DEFAULT);-- 明确字段为缺省值
insert into t1 DEFAULT VALUES;-- 明确整行为缺省值
select * from t1;复制
CREATE TABLE t1
(sn integer,
id character(16),
des character(20));
insert into t1 values(1, 'AAAAAAAABAAAAAAA', 'reason1');
insert into t1 values(1, 'AAAAAAAABAAAAAAA'); -- 没有数值的字段将被填充为字段的缺省值
insert into t1 values(1, 'AAAAAAAABAAAAAAA', DEFAULT);-- 明确字段为缺省值
insert into t1 DEFAULT VALUES;-- 明确整行为缺省值
select * from t1;复制
CREATE TABLE t2
(sn integer,
id character(16),
des character(20));
INSERT INTO t2 SELECT * FROM t1;
select * from t2;复制
CREATE TABLE t2
(sn integer,
id character(16),
des character(20));
INSERT INTO t2 SELECT * FROM t1;
select * from t2;复制
CREATE TABLE t3
(sn integer,
id character(16),
des character(20));
INSERT INTO t3 SELECT * FROM t1;
CREATE TABLE t4
(sn integer,
id character(16),
des character(20));
insert into t4 values(1, 'AAAAAAAABAAAAAAA', 'reason1');
insert into t4 values(2, 'BBBBBBBBBBBBBBBB', 'reason1');
insert into t4 values(3, 'CCCCCCCCCCCCCCCC', 'reason1');
MERGE INTO t3 USING t4
ON (t3.sn = t4.sn )
WHEN MATCHED THEN
UPDATE SET t3.id = t4.id, t3.des = t4.des
WHEN NOT MATCHED THEN
INSERT VALUES (t4.sn, t4.id, t4.des);
select * from t3;复制
CREATE TABLE t3
(sn integer,
id character(16),
des character(20));
INSERT INTO t3 SELECT * FROM t1;
CREATE TABLE t4
(sn integer,
id character(16),
des character(20));
insert into t4 values(1, 'AAAAAAAABAAAAAAA', 'reason1');
insert into t4 values(2, 'BBBBBBBBBBBBBBBB', 'reason1');
insert into t4 values(3, 'CCCCCCCCCCCCCCCC', 'reason1');
MERGE INTO t3 USING t4
ON (t3.sn = t4.sn )
WHEN MATCHED THEN
UPDATE SET t3.id = t4.id, t3.des = t4.des
WHEN NOT MATCHED THEN
INSERT VALUES (t4.sn, t4.id, t4.des);
select * from t3;复制
copy t3 to '/tmp/t3_out.txt';
CREATE TABLE t5 (LIKE t1);
copy t5 from '/tmp/t3_out.txt';
select * from t5;复制
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
目录
