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

包括表字段缺省值的设置，表数据的导入

课程作业：

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

CREATE TABLE sym_t1 
( sym integer, 
  sym_id character(16),
  sym_desc character(100)  default 'defaultValue'
); 

INSERT INTO sym_t1 values
(1, 'AAAAAAAABAAAAAAA', 'reason1');

--指定值添加

–没有数值的字段将被填充为字段的缺省值

INSERT INTO  sym_t1  values(1, 'AAAAAAAABAAAAAAA');

–明确字段为缺省值

INSERT INTO  sym_t1  values(1, 'AAAAAAAABAAAAAAA', DEFAULT);

–明确整行为缺省值

 INSERT INTO sym_t1 DEFAULT VALUES; 

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

CREATE TABLE sym_t2
(
sym_sk integer,
sym_id character(16),
sym_desc character(100)
);

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

INSERT INTO sym_t2 SELECT * FROM sym_t1;
select * from sym_t2;

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

CREATE TABLE sym_t3
( product_id INTEGER,
product_name VARCHAR2(60),
category VARCHAR2(60)
);
INSERT INTO sym_t3 VALUES
(1502, 'olympus camera', 'electrncs'),
(1601, 'lamaze', 'toys'),
(1666, 'harry potter', 'toys'),
(1700, 'wait interface', 'books');

CREATE TABLE sym_t4
( product_id INTEGER,
product_name VARCHAR2(60),
category VARCHAR2(60)
);
INSERT INTO sym_t4 VALUES
(1501, 'vivitar 35mm', 'electrncs'),
(1502, 'olympus ', 'electrncs'),
(1600, 'play gym', 'toys'),
(1601, 'lamaze', 'toys'),
(1666, 'harry potter', 'dvd');

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

MERGE INTO sym_t3 t3
USING sym_t4 t4
ON (t3.product_id = t4.product_id )
WHEN MATCHED THEN
UPDATE SET t3.product_name = t4.product_name, t3.category = t4.category
WHEN NOT MATCHED THEN
INSERT VALUES (t4.product_id, t4.product_name, t4.category) ;

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

SELECT * FROM sym_t3

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

copy sym_t3 to '/home/omm/t3.dat';
create table sym_t5 (like sym_t3);
copy sym_t5 from '/home/omm/t3.dat';
select * from sym_t5;