打卡第13天| openGauss导入数据

13.1 打卡第13天| openGauss导入数据

👉openGauss SQL学习参考资料

https://opengauss.org/zh/docs/2.1.0/docs/Developerguide/SQL%E8%AF%AD%E6%B3%95.html

学习目标

学习openGauss导入数据

课程学习

连接数据库

#第一次进入等待15秒
#数据库启动中...
su - omm
gsql -r
复制

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;
复制

课程作业

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, 'AAAAAAAABAAAAAAA', 'reason1');
insert into reason_t1 values(1, 'AAAAAAAABAAAAAAA', DEFAULT);
insert into reason_t1 DEFAULT VALUES;
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 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');

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 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) ;

SELECT * FROM newproducts;
复制

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

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;
复制