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

作业：

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

omm=# create table student (id int,name char(10),age char(2));

CREATE TABLE
omm=# insert into student values(1,'lili','20');
INSERT 0 1
omm=# insert into student values(2,'popo');
INSERT 0 1
omm=# insert into student values(2,'popo',default);
INSERT 0 1
omm=# insert into student default values;
omm=# INSERT 0 1
omm=# select * from student;
id | name | age
----+------------+-----
1 | lili | 20
2 | popo |
2 | popo |
| |
(4 rows)

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

omm=# create table student2 (id int,name char(20),age char(2));
CREATE TABLE
omm=# insert into student2 select * from student;
INSERT 0 4
omm=# select * from student2;
id | name | age
----+----------------------+-----
1 | lili | 20
2 | popo |
2 | popo |
| |
(4 rows)

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

omm=# create table t3 (id int,name char(20),age char(2));
CREATE TABLE
omm=# insert into t3 values(1,'lili','20'),(2,'pipi','12'),(3,'kiki','17'),(4,'lolo','22');
INSERT 0 4

omm=# create table t3 (id int,name char(20),age char(2));
CREATE TABLE
omm=# insert into t3 values(1,'lili','20'),(2,'pipi','12'),(3,'kiki','17'),(4,'lolo','22');
INSERT 0 4
omm=# create table t4 (id int,name char(20),age char(2));
omm=# CREATE TABLE

omm=# insert into t4 values(2,'pipi','99'),(3,'kiki','17'),(4,'lolo','88'),(5,'bibi','99');
INSERT 0 4
omm=# select * from t3;
id | name | age
----+----------------------+-----
1 | lili | 20
2 | pipi | 12
3 | kiki | 17
4 | lolo | 22
(4 rows)

omm=# select * from t4;
omm=# id | name | age
----+----------------------+-----
2 | pipi | 99
3 | kiki | 17

4 | lolo | 88
5 | bibi | 99
(4 rows)

omm=# MERGE INTO t3
omm-# USING t4
omm-# WHEN MATCHED THEN
omm-# ON (t3.id = t4.id )
omm-# omm-# WHEN NOT MATCHED THEN
omm-# UPDATE SET t3.name = t4.name, t3.age = t4.age
INSERT VALUES (t4.id, t4.name, t4.age);
MERGE 4
omm=# select * from t3;
id | name | age
----+----------------------+-----
1 | lili | 20
2 | pipi | 99
3 | kiki | 17
4 | lolo | 88
5 | bibi | 99
(5 rows)

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

omm=# copy t3 to '/home/omm/t3.dat';
COPY 5
omm=# create table t5 (id int,name char(20),age char(2));
CREATE TABLE
omm=# copy t5 from '/home/omm/t3.dat';
COPY 5
omm=# select * from t5;
id | name | age
----+----------------------+-----
1 | lili | 20
2 | pipi | 99
3 | kiki | 17
4 | lolo | 88
5 | bibi | 99
(5 rows)