第十三课 openGauss导入数据
学习目标
学习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并在表中插入数据，分别指定字段和整行为缺省值
2.创建表2并将表1的数据全部导入表2中
3.创建表3和表4，并合并两个表的数据到表3
4.将表3的数据输出到文件，再将文件中的数据导入到表5
CREATE TABLE t1
(
r_reason_sk integer,
r_reason_id character(16),
r_reason_desc character(100)
);
insert into t1 values(1, 'AAAAAAAABAAAAAAA', 'reason1');
insert into t1 values(1, 'AAAAAAAABAAAAAAA');
insert into t1 values(1, 'AAAAAAAABAAAAAAA', DEFAULT);
insert into t1 DEFAULT VALUES;
select * from t1;
CREATE TABLE t2
(
r_reason_sk integer,
r_reason_id character(16),
r_reason_desc character(100)
);
INSERT INTO t2 SELECT * FROM t1;
select * from t2;
CREATE TABLE t3
( product_id INTEGER,
product_name VARCHAR2(60),
category VARCHAR2(60)
);
INSERT INTO t3 VALUES
(1502, 'olympus camera', 'electrncs'),
(1601, 'lamaze', 'toys'),
(1666, 'harry potter', 'toys'),
(1700, 'wait interface', 'books');
CREATE TABLE t4
( product_id INTEGER,
product_name VARCHAR2(60),
category VARCHAR2(60)
);
INSERT INTO t4 VALUES
(1501, 'vivitar 35mm', 'electrncs'),
(1502, 'olympus ', 'electrncs'),
(1600, 'play gym', 'toys'),
(1601, 'lamaze', 'toys'),
(1666, 'harry potter', 'dvd');
MERGE INTO t3 np
USING 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) ;
SELECT * FROM t3;
copy t1 to '/home/omm/t1.dat';
#CREATE TABLE t5 LIKE t1; #该sql语法错误，无法在opengauss中执行
CREATE TABLE t5 (LIKE t1);
copy t5 from '/home/omm/t1.dat';
select * from t5;
复制课后作业记录
omm=#
omm=#
omm=#
omm=# CREATE TABLE reason_t1
omm-# (
omm(# r_reason_sk integer,
omm(# r_reason_id character(16),
omm(# r_reason_desc character(100)
omm(# );
CREATE TABLE
omm=# insert into reason_t1 values(1, 'AAAAAAAABAAAAAAA', 'reason1');
omm=# INSERT 0 1
omm=# insert into reason_t1 values(1, 'AAAAAAAABAAAAAAA');
omm=# INSERT 0 1
omm=# insert into reason_t1 values(1, 'AAAAAAAABAAAAAAA', DEFAULT);
INSERT 0 1
omm=#
omm=# insert into reason_t1 DEFAULT VALUES;
INSERT 0 1
omm=# select * from reason_t1;
r_reason_sk | r_reason_id | r_reason_desc
-------------+------------------+------------------------------------------------------------------------------------------------------
1 | AAAAAAAABAAAAAAA | reason1
1 | AAAAAAAABAAAAAAA |
1 | AAAAAAAABAAAAAAA |
| |
(4 rows)
omm=#
omm=# omm=#
omm=# omm-# (
omm(# r_reason_sk integer,
omm(# CREATE TABLE reason_t2
r_reason_id character(16),
omm(# r_reason_desc character(100)
omm(# );
CREATE TABLE
omm=#
omm=#
omm=# INSERT INTO reason_t2 SELECT * FROM reason_t1;
INSERT 0 4
omm=# select * from reason_t2;
r_reason_sk | r_reason_id | r_reason_desc
-------------+------------------+------------------------------------------------------------------------------------------------------
1 | AAAAAAAABAAAAAAA | reason1
1 | AAAAAAAABAAAAAAA |
1 | AAAAAAAABAAAAAAA |
| |
(4 rows)
omm=#
omm=# CREATE TABLE products
( product_id INTEGER,
omm(# omm-# product_name VARCHAR2(60),
omm(# category VARCHAR2(60)
omm(# );
CREATE TABLE
omm=# INSERT INTO products VALUES
omm-# omm-# (1502, 'olympus camera', 'electrncs'),
(1601, 'lamaze', 'toys'),
omm-# (1666, 'harry potter', 'toys'),
omm-# (1700, 'wait interface', 'books');
omm=# INSERT 0 4
omm=#
omm=# CREATE TABLE newproducts
omm-# ( product_id INTEGER,
omm(# product_name VARCHAR2(60),
omm(# category VARCHAR2(60)
omm(# );
CREATE TABLE
omm=# INSERT INTO newproducts VALUES
omm-# (1501, 'vivitar 35mm', 'electrncs'),
omm-# (1502, 'olympus ', 'electrncs'),
omm-# omm-# (1600, 'play gym', 'toys'),
(1601, 'lamaze', 'toys'),
omm-# (1666, 'harry potter', 'dvd');
INSERT 0 5
omm=#
omm=# omm=#
MERGE INTO newproducts np
omm-# USING products p
omm-# ON (np.product_id = p.product_id )
omm-# omm-# WHEN MATCHED THEN
UPDATE SET np.product_name = p.product_name, np.category = p.category
omm-# WHEN NOT MATCHED THEN
omm-# INSERT VALUES (p.product_id, p.product_name, p.category) ;
MERGE 4
omm=#
omm=#
omm=# SELECT * FROM newproducts
omm-#
omm-# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+-------------+-------------+-------------------
omm | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/omm +
| | | | | omm=CTc/omm
template1 | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/omm +
| | | | | omm=CTc/omm
(4 rows)
omm-#
omm-# SELECT * FROM newproducts;
ERROR: syntax error at or near "SELECT"
LINE 2: SELECT * FROM newproducts;
^
omm=#
omm=# SELECT * FROM newproducts;
product_id | product_name | category
------------+----------------+-----------
1501 | vivitar 35mm | electrncs
1600 | play gym | toys
1502 | olympus camera | electrncs
1601 | lamaze | toys
1666 | harry potter | toys
1700 | wait interface | books
(6 rows)
omm=#
omm=# copy reason_t1 to stdout;
\N
omm=# 1 AAAAAAAABAAAAAAA reason1
1 AAAAAAAABAAAAAAA \N
1 AAAAAAAABAAAAAAA \N
\N \N omm=#
omm=#
omm=# copy reason_t1 to '/home/omm/reason.dat';
COPY 4
omm=# CREATE TABLE reason_t3 (LIKE reason_t1);
CREATE TABLE
omm=#
omm=#
omm=# copy reason_t3 from '/home/omm/reason.dat';
select * from reason_t3;COPY 4
omm=#
r_reason_sk | r_reason_id | r_reason_desc
-------------+------------------+------------------------------------------------------------------------------------------------------
1 | AAAAAAAABAAAAAAA | reason1
1 | AAAAAAAABAAAAAAA |
1 | AAAAAAAABAAAAAAA |
| |
(4 rows)
omm=#
omm=#
omm=# \d
List of relations
Schema | Name | Type | Owner | Storage
--------+-------------+-------+-------+----------------------------------
public | newproducts | table | omm | {orientation=row,compression=no}
public | products | table | omm | {orientation=row,compression=no}
public | reason_t1 | table | omm | {orientation=row,compression=no}
public | reason_t2 | table | omm | {orientation=row,compression=no}
public | reason_t3 | table | omm | {orientation=row,compression=no}
public | t1_compfoo | table | omm | {orientation=row,compression=no}
public | t1_fh | table | omm | {orientation=row,compression=no}
(7 rows)
omm=#
omm=#
omm=#
omm=# CREATE TABLE t1
omm-# (
omm(# r_reason_sk integer,
omm(# r_reason_id character(16),
omm(# r_reason_desc character(100)
omm(# );
CREATE TABLE
omm=# insert into t1 values(1, 'AAAAAAAABAAAAAAA', 'reason1');
INSERT 0 1
omm=#
omm=# insert into t1 values(1, 'AAAAAAAABAAAAAAA');
INSERT 0 1
omm=# insert into t1 values(1, 'AAAAAAAABAAAAAAA', DEFAULT);
INSERT 0 1
omm=# insert into t1 DEFAULT VALUES;
INSERT 0 1
omm=# select * from t1;
r_reason_sk | r_reason_id | r_reason_desc
-------------+------------------+------------------------------------------------------------------------------------------------------
1 | AAAAAAAABAAAAAAA | reason1
1 | AAAAAAAABAAAAAAA |
1 | AAAAAAAABAAAAAAA |
| |
(4 rows)
omm=#
omm=#
omm=#
omm=# CREATE TABLE t2
omm-# (
omm(# r_reason_sk integer,
omm(# r_reason_id character(16),
omm(# r_reason_desc character(100)
omm(# );
CREATE TABLE
omm=#
omm=# INSERT INTO t2 SELECT * FROM t1;
INSERT 0 4
omm=# select * from t2;
r_reason_sk | r_reason_id | r_reason_desc
-------------+------------------+------------------------------------------------------------------------------------------------------
1 | AAAAAAAABAAAAAAA | reason1
1 | AAAAAAAABAAAAAAA |
1 | AAAAAAAABAAAAAAA |
| |
(4 rows)
omm=#
omm=# omm=#
omm=#
omm=# omm(# CREATE TABLE t3
omm-# ( product_id INTEGER,
product_name VARCHAR2(60),
omm(# category VARCHAR2(60)
omm(# );
CREATE TABLE
omm=# INSERT INTO t3 VALUES
omm-# (1502, 'olympus camera', 'electrncs'),
omm-# (1601, 'lamaze', 'toys'),
omm-# (1666, 'harry potter', 'toys'),
omm-# (1700, 'wait interface', 'books');
INSERT 0 4
omm=#
omm=# CREATE TABLE t4
omm-# ( product_id INTEGER,
omm(# product_name VARCHAR2(60),
omm(# category VARCHAR2(60)
omm(# );
INSERT INTO t4 VALUES
CREATE TABLE
omm=# omm-# (1501, 'vivitar 35mm', 'electrncs'),
omm-# (1502, 'olympus ', 'electrncs'),
omm-# (1600, 'play gym', 'toys'),
omm-# (1601, 'lamaze', 'toys'),
omm-# (1666, 'harry potter', 'dvd');
INSERT 0 5
omm=#
omm=#
omm=#
omm=# MERGE INTO t3 np
omm-# USING t4 p
omm-# ON (np.product_id = p.product_id )
omm-# WHEN MATCHED THEN
omm-# UPDATE SET np.product_name = p.product_name, np.category = p.category
omm-# WHEN NOT MATCHED THEN
omm-# INSERT VALUES (p.product_id, p.product_name, p.category) ;
MERGE 5
omm=#
omm=# SELECT * FROM t3;
product_id | product_name | category
------------+----------------+-----------
1700 | wait interface | books
1501 | vivitar 35mm | electrncs
1502 | olympus | electrncs
1600 | play gym | toys
1601 | lamaze | toys
1666 | harry potter | dvd
(6 rows)
omm=#
omm=#
omm=# copy t1 to '/home/omm/t1.dat';
omm=# COPY 4
omm=# CREATE TABLE t5 LIKE t1;
ERROR: syntax error at or near "LIKE"
LINE 1: CREATE TABLE t5 LIKE t1;
^
omm=#
omm=# CREATE TABLE t5 (LIKE t1);
CREATE TABLE
omm=#
omm=# copy t5 from '/home/omm/t1.dat';
COPY 4
omm=# select * from t5;
r_reason_sk | r_reason_id | r_reason_desc
-------------+------------------+------------------------------------------------------------------------------------------------------
1 | AAAAAAAABAAAAAAA | reason1
1 | AAAAAAAABAAAAAAA |
1 | AAAAAAAABAAAAAAA |
| |
(4 rows)
omm=#
omm=#
omm=# \d
List of relations
Schema | Name | Type | Owner | Storage
--------+-------------+-------+-------+----------------------------------
public | newproducts | table | omm | {orientation=row,compression=no}
public | products | table | omm | {orientation=row,compression=no}
public | reason_t1 | table | omm | {orientation=row,compression=no}
public | reason_t2 | table | omm | {orientation=row,compression=no}
public | reason_t3 | table | omm | {orientation=row,compression=no}
public | t1 | table | omm | {orientation=row,compression=no}
public | t1_compfoo | table | omm | {orientation=row,compression=no}
public | t1_fh | table | omm | {orientation=row,compression=no}
public | t2 | table | omm | {orientation=row,compression=no}
public | t3 | table | omm | {orientation=row,compression=no}
public | t4 | table | omm | {orientation=row,compression=no}
public | t5 | table | omm | {orientation=row,compression=no}
(12 rows)
omm=#
复制最后修改时间：2021-12-30 21:43:20
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
相关阅读
openGauss荣获中国软件行业协会多奖项，技术升级再创行业新高度
openGauss
497次阅读
2025-04-30 14:30:58
MogDB 发布更新，解决 openGauss 数据库在长事务情况下Ustore表膨胀问题
MogDB
304次阅读
2025-04-17 10:41:41
MogDB 发布更新，解决 openGauss 数据库在长事务情况下Ustore表膨胀问题
云和恩墨
201次阅读
2025-04-16 09:52:02
GitCode 成 openGauss 新归宿，国产开源数据库里程碑事件
严少安
163次阅读
2025-04-27 11:37:53
荣誉时刻！openGauss认证证书快递已发，快来看看谁榜上有名！
墨天轮小教习
156次阅读
2025-04-23 17:39:13
单个执行机并行执行MySQL到openGauss数据迁移子任务
Clipnosis
139次阅读
2025-04-30 16:39:58
openGauss6.0.0适配操作系统自带的软件，不依赖三方库
来杯拿铁
93次阅读
2025-04-18 10:49:53
Postgresql数据库单个Page最多存储多少行数据
maozicb
85次阅读
2025-04-23 16:02:19
openGauss新特性 | openGauss-DataVec向量数据库特性介绍
openGauss
57次阅读
2025-04-17 10:41:47
RISC-V 首迎 openGauss 7.0.0-RC1 全量版适配！数据库核心功能完整落地开源架构
openGauss
48次阅读
2025-04-16 10:33:59
