不容易 ，今天学习到导入数据环节了！
1.登录数据库环境
Welcome to 墨天轮.
This is Web Terminal of modb.pro; Good Good Study, Day Day Up.
root@modb:~# su - omm
omm@modb:~$ gsql -r
gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:03:52 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
2.创建表并插入数据
omm=# CREATE TABLE reason_t1
omm-# (
omm(# r_reason_sk integer,
omm(# r_reason_id character(16),
omm(# r_reason_desc character(100)
omm(# );
omm=# CREATE TABLE
insert into reason_t1 values(1, 'AAAAAAAABAAAAAAA', 'reason1');
INSERT 0 1
omm=# insert into reason_t1 values(1, 'AAAAAAAABAAAAAAA');
INSERT 0 1
omm=# insert into reason_t1 values(1, 'AAAAAAAABAAAAAAA', DEFAULT);
INSERT 0 1
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)
创建表2并插入数据
omm=# CREATE TABLE reason_t2
omm(# r_reason_sk integer,
omm(# omm-# (
r_reason_id character(16),
omm(# r_reason_desc character(100)
omm(# );
CREATE TABLE
3.将表1数据导入到表2中
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)
4.合并数据测试merge
（1）创建表并插入数据
omm=# CREATE TABLE products
omm-# ( product_id INTEGER,
omm(# product_name VARCHAR2(60),
omm(# category VARCHAR2(60)
omm(# );
CREATE TABLE
omm-# omm=# INSERT INTO products VALUES
(1502, 'olympus camera', 'electrncs'),
omm-# (1601, 'lamaze', 'toys'),
omm-# (1666, 'harry potter', 'toys'),
omm-# (1700, 'wait interface', 'books');
INSERT 0 4
omm=# CREATE TABLE newproducts
omm-# omm(# ( product_id INTEGER,
omm(# product_name VARCHAR2(60),
category VARCHAR2(60)
omm(# );
CREATE TABLE
omm=# INSERT INTO newproducts VALUES
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=# MERGE INTO newproducts np
omm-# USING products p
omm-# WHEN MATCHED THEN
omm-# ON (np.product_id = p.product_id )
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 4
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)
5.导出数据到文件再导入数据到目标表
omm=# copy reason_t1 to stdout;
1 AAAAAAAABAAAAAAA reason1
1 AAAAAAAABAAAAAAA \N
1 AAAAAAAABAAAAAAA \N
\N \N \N
omm=# CREATE TABLE reason_t3 (LIKE reason_t1);
CREATE TABLE
omm=# copy reason_t3 from '/home/omm/reason.dat';
ERROR: could not open file "/home/omm/reason.dat" for reading: No such file or directory
omm=# select * from reason_t3;
r_reason_sk | r_reason_id | r_reason_desc
-------------+-------------+---------------
(0 rows)
omm=# copy reason_t1 to '/home/omm/reason.dat';
COPY 4
omm=# copy reason_t3 from '/home/omm/reason.dat';
COPY 4
omm=# select * from reason_t3;
r_reason_sk | r_reason_id | r_reason_desc
-------------+------------------+---------------------------------------------------------------------------------------------------
---
1 | AAAAAAAABAAAAAAA | reason1
1 | AAAAAAAABAAAAAAA |
1 | AAAAAAAABAAAAAAA |
| |
(4 rows)
omm=#
作业
root@modb:~#
root@modb:~# su - omm
omm@modb:~$ gsql -r
gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:03:52 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
omm=# omm-# (
omm(# CREATE TABLE alex_test_t1
r_reason_sk integer,
omm(# r_reason_id character(16),
omm(# r_reason_desc character(100)
omm(# );
CREATE TABLE
omm=# insert into alex_test_t1 values(1, 'alexwoo', 'test1');
INSERT 0 1
omm=# insert into alex_test_t1 values(1, 'alexwoo1');
INSERT 0 1
omm=# insert into alex_test_t1 values(1, 'alexwoo1', DEFAULT);
INSERT 0 1
omm=# insert into alex_test_t1 DEFAULT VALUES;
INSERT 0 1
omm=# select * from alex_test_t1;
r_reason_sk | r_reason_id | r_reason_desc
-------------+------------------+---------------------------------------------------------------------------------------------------
---
1 | alexwoo | test1
1 | alexwoo1 |
1 | alexwoo1 |
| |
(4 rows)
2.创建表并把表1的数据全部导入表2中
CREATE TABLE alex_test_t2
(r_reason_sk integer,
r_reason_id character(16),
r_reason_desc character(100)
);
omm=# INSERT INTO alex_test_t2 SELECT * FROM alex_test_t1;
omm=# INSERT 0 4
omm=# select * from alex_test_t2;
r_reason_sk | r_reason_id | r_reason_desc
-------------+------------------+---------------------------------------------------------------------------------------------------
---
1 | alexwoo | test1
1 | alexwoo1 |
1 | alexwoo1 |
| |
(4 rows)
3.创建表3和表4，并合并两个表的数据到表
omm=# CREATE TABLE source
omm-# ( product_id INTEGER,
omm(# product_name VARCHAR2(60),
omm(# category VARCHAR2(60)
omm(# );
CREATE TABLE
omm=# INSERT INTO source VALUES
omm-# (1502, 'olympus camera', 'electrncs'),
omm-# omm-# (1601, 'lamaze', 'toys'),
omm-# (1666, 'harry potter', 'toys'),
(1700, 'wait interface', 'books');
INSERT 0 4
omm=# CREATE TABLE target
omm-# ( product_id INTEGER,
omm(# product_name VARCHAR2(60),
omm(# category VARCHAR2(60)
omm(# );
CREATE TABLE
omm=# INSERT INTO target VALUES
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=# MERGE INTO target np
omm-# USING source 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-# omm-# WHEN NOT MATCHED THEN
INSERT VALUES (p.product_id, p.product_name, p.category) ;
MERGE 4
omm=# select * from target;
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)
4..将表3的数据输出到文件，再将文件中的数据导入到表5
omm=# CREATE TABLE t5 (LIKE source);
CREATE TABLE
表3数据输出到文件
再将文件中的数据导入到表5
copy source to '/home/omm/source.dat';
copy t5 from '/home/omm/source.dat';
omm=# copy source to '/home/omm/source.dat';
COPY 4
omm=# copy t5 from '/home/omm/source.dat';
COPY 4
omm=#
omm=# select * from t5;
product_id | product_name | category
------------+----------------+-----------
1502 | olympus camera | electrncs
1601 | lamaze | toys
1666 | harry potter | toys
1700 | wait interface | books
(4 rows)
omm=#
导入导出操作，在工作中经常会用到，比较重要！
