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

学习目标

学习openGauss导入数据

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

omm=# create table t1(id integer ,name char(30),ndetail char(40));
CREATE TABLE
omm=# insert into t1 values(1,'aaaaaa','cj111');
INSERT 0 1
omm=# insert into t1 values(2,'bbbbbb');      
INSERT 0 1
omm=# insert into t1 values(3,'cccccc',default);
INSERT 0 1

omm=# insert into t1 default values;
omm=# INSERT 0 1

omm=# select * from t1;
 id |              name              |                 ndetail                  
----+--------------------------------+------------------------------------------
  1 | aaaaaa                         | cj111                                   
  2 | bbbbbb                         | 
  3 | cccccc                         | 
    |                                | 
(4 rows)

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

omm=# create table t2 (id integer ,name char(30),ndetail char(40));
CREATE TABLE
omm=# insert into t2 select * from t1;
INSERT 0 4
omm=# select * from t2;
 id |              name              |                 ndetail                  
----+--------------------------------+------------------------------------------
  1 | aaaaaa                         | cj111                                   
  2 | bbbbbb                         | 
  3 | cccccc                         | 
    |                                | 
(4 rows)

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

omm=# create table t3 (id integer,name varchar2(60),category varchar(60));
CREATE TABLE
omm=# INSERT INTO t3 VALUES  
omm-# (1502, 'olympus camera', 'electrncs'),
omm-# (1601, 'lamaze', 'toys'),
omm-# (1666, 'harry potter', 'toys'),
omm-# (1700, 'wait interface', 'books');
INSERT 0 4
omm=# 
omm=# 
omm=# create table t4(id integer,name varchar2(60),category varchar(60));
CREATE TABLE
omm=# 
omm=# 
omm=# INSERT INTO t4 VALUES
omm-# (1501, 'vivitar 35mm', 'electrncs'),
omm-# (1502, 'olympus ', 'electrncs'),
omm-# (1600, 'play gym', 'toys'),
omm-# (1601, 'lamaze', 'toys'),
omm-# (1666, 'harry potter', 'dvd');
INSERT 0 5

omm=# merge into t3 n3
omm-# using t4 n4omm-# 
on (n3.id=n4.id)omm-# 
when matched then
omm-# update set n3.name=n4.name,n3.category=n4.category
omm-# when not matched then
omm-# insert values (n4.id,n4.name,n4.category);
MERGE 5

omm=# select * from t3;
  id  |      name      | category  
------+----------------+-----------
 1700 | wait interface | books
 1501 | vivitar 35mm   | electrncs
 1502 | olympus        | electrncs
 1600 | play gym       | toys
 1601 | lamaze         | toys
 1666 | harry potter   | dvd
(6 rows)

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

omm=# copy t3 to stdout;
1700    wait interface  books
1501    vivitar 35mm    electrncs
1502    olympus         electrncs
1600    play gym        toys
1601    lamaze  toys
1666    harry potter    dvd
omm=# 
omm=# copy t3 to '/home/omm/t3.dat';
COPY 6

omm=# create table t5 (like t3);
CREATE TABLE
omm=# copy t5 from '/home/omm/t3.dat';
COPY 6

omm=# select * from t5;
  id  |      name      | category  
------+----------------+-----------
 1700 | wait interface | books
 1501 | vivitar 35mm   | electrncs
 1502 | olympus        | electrncs
 1600 | play gym       | toys
 1601 | lamaze         | toys
 1666 | harry potter   | dvd
(6 rows)



omm=# create table t9 like t5;
ERROR:  syntax error at or near "like"
LINE 1: create table t9 like t5;
                        ^
omm=# create table t9 (like t5);     ---创建表必须有括号
CREATE TABLE