openGauss每日一练第13天|课后作业

课程作业

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

CREATE TABLE student
(
id integer,
name character(15),
grade varchar(15) 
);
insert into student values(1, 'wz', '2019');

insert into student values(2, 'lihua', DEFAULT);

insert into student DEFAULT VALUES;
select * from student;

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

CREATE TABLE t_2
(
id integer,
name character(15),
grade varchar(15) 
);

INSERT INTO t_2 SELECT * FROM student;
select * from t_2;

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

CREATE TABLE t_3
(
id integer,
name character(15),
grade varchar(15) 
);
insert into t_3 values(3, 'xiaoli', '2019');

CREATE TABLE t_4
(
id integer,
name character(15),
grade varchar(15) 
);
insert into t_3 values(4, 'xiaowang', '2020');

MERGE INTO t_3 
USING t_4 
ON (t_3.id = t_4.id )
WHEN MATCHED THEN
UPDATE SET t_3.name = t_4.name, t_3.grade = t_4.grade
WHEN NOT MATCHED THEN
INSERT VALUES (t_4.id, t_4.name, t_4.grade) ;
SELECT * FROM t_3;

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

copy t_3 to '/home/omm/reason.dat';
CREATE TABLE t_5 (LIKE t_3);

copy t_5 from '/home/omm/reason.dat';
select * from t_5;