openGauss每日一练第13天 | 学习openGauss导入数据

今天进入第十三天的学习，学习了openGauss导入数据及从表导到文件或从文件导入到表等等操作。

以下是我今天的作业打卡情况，请老师审阅：

1.创建表1并在表中插入数据，分别指定字段和整行为缺省值

su - omm
gsql -r
create table tony1
(
id1 integer,
id2 character(16),
id3 character(100)
);

insert into tony1 values(1, 'AAAAAAAA', 'tony1');

insert into tony1 values(1, 'AAAAAAAA', DEFAULT);

insert into tony1 DEFAULT VALUES;

select * from tony1;

2.创建表2并将表1的数据全部导入表2中

create table tony2
(
id1 integer,
id2 character(16),
id3 character(100)
);

insert into tony2 select * from tony1;
select * from tony2;

3.创建表3和表4，并合并两个表的数据到表3

create table tony3
(id1 INTEGER,
id2 VARCHAR2(60),
id3 VARCHAR2(60)
);

create table tony4
(id1 INTEGER,
id2 VARCHAR2(60),
id3 VARCHAR2(60)
);

insert into tony3 values
(1502, 'olympus camera', 'electrncs'),
(1601, 'lamaze', 'toys'),
(1666, 'harry potter', 'toys'),
(1700, 'wait interface', 'books');

insert into tony4 values
(1501, 'vivitar 35mm', 'electrncs'),
(1502, 'olympus ', 'electrncs'),
(1600, 'play gym', 'toys'),
(1601, 'lamaze', 'toys'),
(1666, 'harry potter', 'dvd');

MERGE INTO tony3 np
USING tony4 p
ON (np.id1 = p.id1 )
WHEN MATCHED THEN
UPDATE SET np.id2 = p.id2, np.id3 = p.id3
WHEN NOT MATCHED THEN
INSERT VALUES (p.id1, p.id2, p.id3) ;

select * from tony3;

4.将表3的数据输出到文件，再将文件中的数据导入到表5

copy tony3 to stdout;

create table tony5 (LIKE tony1);

copy tony3 to '/home/omm/tony.dat';

copy tony5 from '/home/omm/tony.dat';

drop table tony1;

drop table tony2;

drop table tony3;

drop table tony4;

drop table tony5;