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

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

CREATE TABLE student
(
id integer,
name character(16),
sex character(10),
birth date
);
复制

insert into student values(1,'lucy','female','2000-06-12');
insert into student values(2,'jack','male','2001-11-05');
insert into student values(3,'tom','male');
select * from student;
复制

ALTER TABLE student ALTER COLUMN birth SET DEFAULT '2001-01-01';

insert into student values(4,'jordon','male',default);

insert into student default values;

select * from student;
复制

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

CREATE TABLE student_2
(
id integer,
name character(16),
sex character(10),
birth date
);

INSERT INTO student_2 SELECT * FROM student;

复制

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

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

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

MERGE INTO product_3 p3
USING product_4 p4
ON (p3.product_id = p4.product_id )
WHEN MATCHED THEN
UPDATE SET p3.product_name = p4.product_name, p3.category = p4.category
WHEN NOT MATCHED THEN
INSERT VALUES (p4.product_id, p4.product_name, p4.category) ;

select * from product_3;
复制

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

copy product_3 to '/home/omm/product_3.dat';
CREATE TABLE product_5 (LIKE product_3);


copy product_5 from '/home/omm/product_3.dat';
select * from product_5;
复制