学习目标
学习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并在表中插入数据，分别指定字段和整行为缺省值
-- 1.
create table t1 (a int, b int);
insert into t1 values (1, default);
insert into t1 default values;
select * from t1;
2.创建表2并将表1的数据全部导入表2中
-- 2.
create table t2 (a int, b int);
insert into t2 select * from t1;
3.创建表3和表4，并合并两个表的数据到表3
-- 3.
create table t3 (a int, b int);
create table t4 (a int, b int);
insert into t3 values (1,31),(2,32),(3,33);
insert into t4 values (2,42),(3,43),(4,44);
merge into t3
using t4
on (t3.a = t4.a)
when matched then
update set t3.b = t4.b
when not matched then
insert values (t4.a, t4.b);
select * from t3;
4.将表3的数据输出到文件，再将文件中的数据导入到表5
-- 4.
copy t3 to '/home/omm/t3.dat';
create table t5 (like t3);
copy t5 from '/home/omm/t3.dat';
select * from t5;
学习心得
本节学习难点是 MERGE INTO 的语法语义，需要加强理解。
参考文档
松鼠镇楼，按时打卡
