openGauss每日一练第13天

学习目标
学习openGauss导入数据

课程作业
1.创建表1并在表中插入数据，分别指定字段和整行为缺省值
CREATE TABLE doom_t1
(
r_reason_sk integer,
r_reason_id character(16),
r_reason_desc character(100)
);

insert into doom_t1 values(1, 'AAAAAAAABAAAAAAA', 'reason1');
insert into doom_t1 values(1, 'AAAAAAAABAAAAAAA');
insert into doom_t1 values(1, 'AAAAAAAABAAAAAAA', DEFAULT);

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

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

–将查询结果作为插入的数据

INSERT INTO doom_t2 SELECT * FROM doom_t1;
select * from doom_t2;

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

–创建目标表doom_t3，并插入数据

CREATE TABLE doom_t3
( product_id INTEGER,
product_name VARCHAR2(60),
category VARCHAR2(60)
);

INSERT INTO doom_t3 VALUES
(1501, 'vivitar 35mm', 'electrncs'),
(1502, 'olympus ', 'electrncs'),
(1600, 'play gym', 'toys'),
(1601, 'lamaze', 'toys'),
(1666, 'harry potter', 'dvd');

–创建源表doom_t4，并插入数据

CREATE TABLE doom_t4
( product_id INTEGER,
product_name VARCHAR2(60),
category VARCHAR2(60)
);

INSERT INTO doom_t4 VALUES
(1502, 'olympus camera', 'electrncs'),
(1601, 'lamaze', 'toys'),
(1666, 'harry potter', 'toys'),
(1700, 'wait interface', 'books');

–使用MERGE INTO 语句将源表doom_t4的数据合并至目标表doom_t3

MERGE INTO doom_t3 np
USING doom_t4 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) ;

–查询合并后的目标表 doom_t3。

SELECT * FROM doom_t3;

4.将表3的数据输出到文件，再将文件中的数据导入到表5
copy doom_t3 to '/home/omm/reason.dat';
CREATE TABLE doom_t5 (LIKE doom_t3);
copy doom_t5 from '/home/omm/reason.dat';
select * from doom_t5;