暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / openGauss每日一练 第13天 导入数据

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

原创 Lily_tj 2021-12-21
924

学习目标

学习openGauss导入数据

课程学习

连接openGauss
#第一次进入等待15秒
#数据库启动中…
su - omm
gsql -r

课后作业

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


CREATE TABLE reason_t1
(
r_reason_sk integer,
r_reason_id character(16),
r_reason_desc character(100)
);
insert into reason_t1 values(1, 'AAAAAAAA', 'reason1');

insert into reason_t1 DEFAULT VALUES;


insert into reason_t1 values(2, 'BAAAAAAA');
insert into reason_t1 values(3, 'CAAAAAAA', DEFAULT);

select * from reason_t1;

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

CREATE TABLE reason_t2
(
r_reason_sk integer,
r_reason_id character(16),
r_reason_desc character(100)
);


INSERT INTO reason_t2 SELECT * FROM reason_t1;
select * from reason_t2;

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

CREATE TABLE reason_t3
(
r_reason_sk integer,
r_reason_id character(16),
r_reason_desc character(100)
);
INSERT INTO reason_t3 VALUES
(1502, 'olympus camera', 'electrncs'),
(1601, 'lamaze', 'toys'),
(1666, 'harry potter', 'toys'),
(1700, 'wait interface', 'books');

CREATE TABLE reason_t4
(
r_reason_sk integer,
r_reason_id character(16),
r_reason_desc character(100)
);
INSERT INTO reason_t4 VALUES
(1501, 'vivitar 35mm', 'electrncs'),
(1502, 'olympus ', 'electrncs'),
(1600, 'play gym', 'toys'),
(1601, 'lamaze', 'toys'),
(1666, 'harry potter', 'dvd');

MERGE INTO reason_t3 np
USING reason_t4 p
ON (np.r_reason_sk = p.r_reason_sk)
WHEN MATCHED THEN
UPDATE SET np.r_reason_id = p.r_reason_id, np.r_reason_desc = p.r_reason_desc
WHEN NOT MATCHED THEN
INSERT VALUES (p.r_reason_isk, p.r_reason_id, p.r_reason_desc) ;

SELECT * FROM reason_t3;

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


copy reason_t1 to stdout;

copy reason_t1 to '/home/omm/reason.dat';
CREATE TABLE reason_t5 (LIKE reason_t1);

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

作业执行结果


omm=# CREATE TABLE reason_t1
omm-# (
omm(# r_reason_sk integer,
r_reason_id character(16),
r_reason_desc character(100)
omm(# omm(# omm(# );
CREATE TABLE
omm=# insert into reason_t1 values(1, 'AAAAAAAA', 'reason1');
INSERT 0 1
omm=# 
omm=# insert into reason_t1 DEFAULT VALUES;
INSERT 0 1
omm=# 
omm=# 
omm=# insert into reason_t1 values(2, 'BAAAAAAA');
INSERT 0 1
omm=# insert into reason_t1 values(3, 'CAAAAAAA', DEFAULT);
INSERT 0 1
omm=# 
omm=# select * from reason_t1;
 r_reason_sk |   r_reason_id    |                                            r_reason_desc       
                                      
-------------+------------------+----------------------------------------------------------------
--------------------------------------
           1 | AAAAAAAA         | reason1                                                        
                                     
             |                  | 
           2 | BAAAAAAA         | 
           3 | CAAAAAAA         | 
(4 rows)

omm=# 
omm=# omm-# (
omm(# r_reason_sk integer,
omm(# CREATE TABLE reason_t2
r_reason_id character(16),
omm(# r_reason_desc character(100)
omm(# );
ERROR:  relation "reason_t2" already exists
omm=# 
omm=# 
omm=# INSERT INTO reason_t2 SELECT * FROM reason_t1;
INSERT 0 4
omm=# select * from reason_t2;
 r_reason_sk |   r_reason_id    |                                            r_reason_desc       
                                      
-------------+------------------+----------------------------------------------------------------
--------------------------------------
           1 | AAAAAAAABAAAAAAA | reason1                                                        
                                     
           1 | AAAAAAAABAAAAAAA | 
           1 | AAAAAAAABAAAAAAA | 
             |                  | 
           1 | AAAAAAAA         | reason1                                                        
                                     
             |                  | 
           2 | BAAAAAAA         | 
           3 | CAAAAAAA         | 
(8 rows)

omm=# 
omm=# CREATE TABLE reason_t3
omm-# (
omm(# r_reason_sk integer,
omm(# r_reason_id character(16),
omm(# r_reason_desc character(100)
omm(# );
ERROR:  relation "reason_t3" already exists
omm=# INSERT INTO reason_t3 VALUES
omm-# (1502, 'olympus camera', 'electrncs'),
omm-# (1601, 'lamaze', 'toys'),
omm-# omm-# (1666, 'harry potter', 'toys'),
(1700, 'wait interface', 'books');
INSERT 0 4
omm=# 
omm=# CREATE TABLE reason_t4
omm-# (
omm(# r_reason_sk integer,
omm(# omm(# r_reason_id character(16),
r_reason_desc character(100)
);
omm(# CREATE TABLE
omm=# INSERT INTO reason_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=# 
omm=# MERGE INTO reason_t3 np
omm-# omm-# USING reason_t4 p
ON (np.r_reason_sk = p.r_reason_sk)
omm-# WHEN MATCHED THEN
omm-# UPDATE SET np.r_reason_id = p.r_reason_id, np.r_reason_desc = p.r_reason_desc
omm-# WHEN NOT MATCHED THEN
omm-# INSERT VALUES (p.r_reason_isk, p.r_reason_id, p.r_reason_desc) ;
ERROR:  column p.r_reason_isk does not exist
LINE 7: INSERT VALUES (p.r_reason_isk, p.r_reason_id, p.r_reason_des...
                       ^
omm=# 
SELECT * FROM reason_t3;
omm=#  r_reason_sk |   r_reason_id    |                                            r_reason_desc       
                                      
-------------+------------------+----------------------------------------------------------------
--------------------------------------
           1 | AAAAAAAABAAAAAAA | reason1                                                        
                                     
           1 | AAAAAAAABAAAAAAA | 
           1 | AAAAAAAABAAAAAAA | 
             |                  | 
        1502 | olympus camera   | electrncs                                                      
        1666 | harry potter     | toys                                                           
                                     
        1700 | wait interface   | books                                                          
                                     
        1601 | lamaze           | toys                                                           
                                     
                                     
(8 rows)

omm=# 
omm=# 
omm=# CREATE TABLE reason_t3
omm-# (
omm(# r_reason_sk integer,
omm(# r_reason_id character(16),
omm(# r_reason_desc character(100)
omm(# );
ERROR:  relation "reason_t3" already exists
omm=# INSERT INTO reason_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 reason_t4
omm(# r_reason_sk integer,
omm(# r_reason_id character(16),
omm(# r_reason_desc character(100)
omm(# );
ERROR:  relation "reason_t4" already exists
omm=# INSERT INTO reason_t4 VALUES
omm-# (1501, 'vivitar 35mm', 'electrncs'),
omm-# (1502, 'olympus ', 'electrncs'),
omm-# omm-# (1600, 'play gym', 'toys'),
(1601, 'lamaze', 'toys'),
omm-# (1666, 'harry potter', 'dvd');
INSERT 0 5
omm=# omm=# 
MERGE INTO reason_t3 np
omm-# USING reason_t4 p
omm-# ON (np.r_reason_sk = p.r_reason_sk)
omm-# WHEN MATCHED THEN
omm-# UPDATE SET np.r_reason_id = p.r_reason_id, np.r_reason_desc = p.r_reason_desc
omm-# WHEN NOT MATCHED THEN
omm-# INSERT VALUES (p.r_reason_isk, p.r_reason_id, p.r_reason_desc) ;
ERROR:  column p.r_reason_isk does not exist
LINE 7: INSERT VALUES (p.r_reason_isk, p.r_reason_id, p.r_reason_des...
                       ^
omm=# 
omm=# SELECT * FROM reason_t3;
 r_reason_sk |   r_reason_id    |                                            r_reason_desc       
                                      
-------------+------------------+----------------------------------------------------------------
--------------------------------------
           1 | AAAAAAAABAAAAAAA | reason1                                                        
                                     
           1 | AAAAAAAABAAAAAAA | 
           1 | AAAAAAAABAAAAAAA | 
             |                  | 
        1502 | olympus camera   | electrncs                                                      
                                     
        1601 | lamaze           | toys                                                           
                                     
        1666 | harry potter     | toys                                                           
                                     
        1700 | wait interface   | books                                                          
                                     
        1502 | olympus camera   | electrncs                                                      
        1601 | lamaze           | toys                                                           
                                     
        1666 | harry potter     | toys                                                           
                                     
                                     
        1700 | wait interface   | books                                                          
                                     
(12 rows)

omm=# omm=# 
omm=# 

omm=# 
omm=# copy reason_t1 to stdout;
1       AAAAAAAA                reason1                                                                                             
\N      \N      \N
2       BAAAAAAA                \N
3       CAAAAAAA                \N

omm=# omm=# copy reason_t1 to '/home/omm/reason.dat';
COPY 4
omm=# CREATE TABLE reason_t5 (LIKE reason_t1);
CREATE TABLE
omm=# 
omm=# copy reason_t5 from '/home/omm/reason.dat';
COPY 4
omm=# select * from reason_t5;
           1 | AAAAAAAA         | reason1                                                        
                                     
             |                  | 
           2 | BAAAAAAA         | 
           3 | CAAAAAAA         | 
(4 rows)

 r_reason_sk |   r_reason_id    |                                            r_reason_desc       
                                      
-------------+------------------+----------------------------------------------------------------
--------------------------------------
opengaussopengauss训练营opengauss每日一练
最后修改时间：2021-12-22 14:47:36
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

文章被以下合辑收录

openGauss每日一练（共41篇）
openGauss每日一练-21天在线实训课程，包括第一届和第二届课程

评论