openGauss | 导入数据

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

案例复制

1.通过INSERT语句直接写入数据
CREATE TABLE reason_t1
(
r_reason_sk integer,
r_reason_id character(16),
r_reason_desc character(100)
);
insert into reason_t1 values(1, 'AAAAAAAABAAAAAAA', 'reason1');
–没有数值的字段将被填充为字段的缺省值

insert into reason_t1 values(1, 'AAAAAAAABAAAAAAA');
–明确字段为缺省值

insert into reason_t1 values(1, 'AAAAAAAABAAAAAAA', DEFAULT);
–明确整行为缺省值

insert into reason_t1 DEFAULT VALUES;
select * from reason_t1;
–指定表插入数据到当前表

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;
2.使用合并方式更新和插入数据
–创建源表products，并插入数据

CREATE TABLE products
( product_id INTEGER,
product_name VARCHAR2(60),
category VARCHAR2(60)
);
INSERT INTO products VALUES
(1502, 'olympus camera', 'electrncs'),
(1601, 'lamaze', 'toys'),
(1666, 'harry potter', 'toys'),
(1700, 'wait interface', 'books');
–创建目标表newproducts，并插入数据

CREATE TABLE newproducts
( product_id INTEGER,
product_name VARCHAR2(60),
category VARCHAR2(60)
);
INSERT INTO newproducts VALUES
(1501, 'vivitar 35mm', 'electrncs'),
(1502, 'olympus ', 'electrncs'),
(1600, 'play gym', 'toys'),
(1601, 'lamaze', 'toys'),
(1666, 'harry potter', 'dvd');
–使用MERGE INTO 语句将源表products的数据合并至目标表newproducts

MERGE INTO newproducts np
USING products p
ON (np.product_id = p.product_id )
WHEN MATCHED THEN
UPDATE SET np.product_name = p.product_name, np.category = p.category
WHEN NOT MATCHED THEN
INSERT VALUES (p.product_id, p.product_name, p.category) ;
–查询合并后的目标表newproducts。

SELECT * FROM newproducts
3.使用COPY实现表和文件间的拷贝
–将表数据输出到stdout

copy reason_t1 to stdout;
–将表数据拷贝到文件

copy reason_t1 to '/home/omm/reason.dat';
CREATE TABLE reason_t3 (LIKE reason_t1);
–将数据从文件拷贝到表

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

学习内容

–使用MERGE INTO 语句将源表products的数据合并至目标表newproducts
MERGE INTO 待插入表名 np
USING 源数据表名 p
ON (np.product_id = p.product_id )	//一对一对应
WHEN MATCHED THEN
UPDATE SET np.product_name = p.product_name, np.category = p.category
WHEN NOT MATCHED THEN
INSERT VALUES (p.product_id, p.product_name, p.category) ;

作业内容

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

create table table1 (
	id1 int,
	name1 char(20),
	detail char(30)
);
insert into table1 values (1,'kimi','esagahgrh');
insert into table1 values (2, 'daming' , default);
insert into table1 default values;	/*注意顺序*/

omm=# create table table1 (
omm(# id1 int,
omm(# name1 char(20),
omm(# detail char(30)
omm(# );
CREATE TABLE
omm=# insert into table1 values (1,'kimi','esagahgrh');
INSERT 0 1
omm=# insert into table1 values (2, 'daming' , default);
INSERT 0 1
omm=# insert into table1 default values;
INSERT 0 1

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

create table table2 (
	id1 int,
	name1 char(20),
	detail char(30)
);
insert into table2 select * from table1;
select * from table2;

omm=# create table table2 (
omm(# id1 int,
omm(# name1 char(20),
omm(# detail char(30)
omm(# );
CREATE TABLE
omm=# insert into table2 as select * from table1;
ERROR:  syntax error at or near "as"
LINE 1: insert into table2 as select * from table1;
                           ^
omm=# 
omm=# insert into table2 select * from table1;
INSERT 
omm=# select * from table2;
 id1 |        name1         |             detail             
-----+----------------------+--------------------------------
   1 | kimi                 | esagahgrh                     
   2 | daming               | 
     |                      | 
(3 rows)

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

create table table3 (
	id1 int,
	name1 char(20),
	detail char(30)
);
create table table4 (
	id1 int,
	name1 char(20),
	detail char(30)
);
未用merge into的效果是直接插入
insert into table3 select * from table1;
insert into table3 select * from table2;
select * from table3;

下面用merge into(ntb -> new table)
merge into table3 ntb
using table1 tb
on(ntb.id1 = tb.id1)
when matched then
update set ntb.name1 = tb.name1, ntb.detail = tb.detail
when not matched then
insert values(tb.id1, tb.name1, tb.detail);

select * from table3;
从结果可以看出，并没有新插入，维持了原状

create table table3 (
omm(# id1 int,
omm(# name1 char(20),
omm(# detail char(30)
omm(# );
CREATE TABLE
omm=# create table table4 (
omm(# id1 int,
omm(# name1 char(20),
omm(# detail char(30)
omm(# );
CREATE TABLE
omm=# insert into table3 select * from table1;
INSERT 0 3
omm=# insert into table3 select * from table2;
INSERT 0 3
omm=# select * from table3;
 id1 |        name1         |             detail             
-----+----------------------+--------------------------------
   1 | kimi                 | esagahgrh                     
   2 | daming               | 
     |                      | 
   1 | kimi                 | esagahgrh                     
   2 | daming               | 
     |                      | 
(6 rows)

omm=# merge into table3 ntb
omm-# using table1 tb
omm-# on(ntb.id1 = tb.id1)
omm-# when matched then
omm-# update set ntb.name1 = tb.name1, ntb.detail = tb.detail
omm-# when not matched then
omm-# insert values(tb.id1, tb.name1, tb.detail);
MERGE 5
omm=# select * from table3;
 id1 |        name1         |             detail             
-----+----------------------+--------------------------------
     |                      | 
     |                      | 
   1 | kimi                 | esagahgrh                     
   1 | kimi                 | esagahgrh                     
   2 | daming               | 
   2 | daming               | 
     |                      | 
(7 rows)

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

copy table3 to '/home/omm/tbb.dat';
CREATE TABLE table5 (LIKE table3);

copy table5 from '/home/omm/tbb.dat';
select * from table5;

omm=# copy table3 to '/home/omm/tbb.dat';
COPY 7
omm=# CREATE TABLE table5 (LIKE table3);
CREATE TABLE
omm=# 
omm=# copy table5 from '/home/omm/tbb.dat';
COPY 7
omm=# select * from table5;
 id1 |        name1         |             detail             
-----+----------------------+--------------------------------
     |                      | 
     |                      | 
   1 | kimi                 | esagahgrh                     
   1 | kimi                 | esagahgrh                     
   2 | daming               | 
   2 | daming               | 
     |                      | 
(7 rows)

omm=#