openGauss每日一练第13天 | 学习心得体会

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

create table employees (emp_id int, name char(10), hire_date date default sysdate);
insert into employees values(1,'aa','2021-12-13');
insert into employees values(2,'bb',default);
insert into employees default values;
omm=# select * from employees ;
(3 rows)

omm=#  emp_id |    name    | hire_date  
--------+------------+------------
      1 | aa         | 2021-12-13
      2 | bb         | 2021-12-13
        |            | 2021-12-13

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

create table employees2 as select * from employees;
omm=# select * from employees2; 
 emp_id |    name    | hire_date  
--------+------------+------------
      1 | aa         | 2021-12-13
      2 | bb         | 2021-12-13
        |            | 2021-12-13
(3 rows)

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

create table employees3 (like employees);
create table employees4 (like employees);
insert into employees3 select * from employees;
insert into employees4 values(1,'aa','2021-12-13');
insert into employees4 values(2,'bb','2021-12-14');
insert into employees4 values(4,'dd',default);
omm=# select * from employees3;
        |            | 2021-12-13
(3 rows)

omm=#  emp_id |    name    | hire_date  
--------+------------+------------
      1 | aa         | 2021-12-13
      2 | bb         | 2021-12-13

omm=# 
omm=# 
omm=# 
omm=# select * from employees;4;
 emp_id |    name    | hire_date  
--------+------------+------------
      1 | aa         | 2021-12-13
      2 | bb         | 2021-12-14
      4 | dd         | 
(3 rows)
merge into employees3 emp3
using employees4 emp4
on (emp3.emp_id=emp4.emp_id)
when matched then
update set emp3.name=emp4.name,emp3.hire_date=emp4.hire_date
when not matched then
insert values (emp4.emp_id,emp4.name,emp4.hire_date);
omm=# select * from employees3;
 emp_id |    name    | hire_date  
--------+------------+------------
        |            | 2021-12-13
      1 | aa         | 2021-12-13
      2 | bb         | 2021-12-14
      4 | dd         | 
(4 rows)

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

create table employees5 (like employees3);
copy employees3 to '/home/omm/employees3.dat';
copy employees5 from '/home/omm/employees3.dat';
omm=# select * from employees5;
 emp_id |    name    | hire_date  
--------+------------+------------
        |            | 2021-12-13
      1 | aa         | 2021-12-13
      2 | bb         | 2021-12-14
      4 | dd         | 
(4 rows)