openGauss每日一练第13天

今天学习openGauss导入数据
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并在表中插入数据，分别指定字段和整行为缺省值

omm=# create table t1(id integer,name char(30));
CREATE TABLE
omm=# select * from t1;
id | name
----±-----
(0 rows)

omm=# insert into t1 values
omm-# (1,‘tim’),
omm-# (2,default);
INSERT 0 2
omm=# select * from t1;
id | name
----±-------------------------------
1 | tim
2 |
(2 rows)

omm=# insert into t1 default values;
INSERT 0 1
omm=# select * from t1;
id | name
----±-------------------------------
1 | tim
2 |
|
(3 rows)

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

omm=# create table t2 (id integer,name char(30));
CREATE TABLE
omm=# insert into t2 select * from t1;
INSERT 0 3
omm=# select * from t2;
id | name
----±-------------------------------
1 | tim
2 |
|
(3 rows)

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

omm=# create table t3(id integer,username VARCHAR2(30));
CREATE TABLE
omm=# insert into t3 values
omm-# (1,‘tim’),
omm-# (2,‘rose’),
omm-# (3,‘tom’),
omm-# (4,‘jerry’);
INSERT 0 4
omm=# select * from t3;
id | username
----±---------
1 | tim
2 | rose
3 | tom
4 | jerry
(4 rows)

omm=# create table t4(id integer,username VARCHAR2(30));
CREATE TABLE
omm=# insert into t4 values
omm-# (1,‘tim_t4’),
omm-# (2,‘lily’),
omm-# (3,‘tom_t4’),
omm-# (4,‘jerry_t4’),
omm-# (5,‘sun’);
INSERT 0 5
omm=# select * from t4;
id | username
----±---------
1 | tim_t4
2 | lily
3 | tom_t4
4 | jerry_t4
5 | sun
(5 rows)

omm=# merge into t3
omm-# using t4
omm-# on (t3.id = t4.id)
omm-# when matched then
omm-# update set t3.username = t4.username
omm-# when not matched then
omm-# insert values (t4.id,t4.username);
MERGE 5

omm=# select * from t3;
id | username
----±---------
1 | tim_t4
2 | lily
3 | tom_t4
4 | jerry_t4
5 | sun
(5 rows)

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

omm=# select * from t3;
id | username
----±---------
1 | tim_t4
2 | lily
3 | tom_t4
4 | jerry_t4
5 | sun
(5 rows)

omm=# copy t3 to ‘/home/omm/t3.dat’;
COPY 5
omm=# \q
omm@modb:~$ cd /home/omm/
omm@modb:~$ ls
reason.dat t3.dat
omm@modb:~$ cat t3.dat
1 tim_t4
2 lily
3 tom_t4
4 jerry_t4
5 sun

omm=# create table t5 (like t3);
CREATE TABLE
omm=# \d t5
Table “public.t5”
Column | Type | Modifiers
----------±----------------------±----------
id | integer |
username | character varying(30) |

omm=# copy t5 from ‘/home/omm/t3.dat’;
COPY 5
omm=# select * from t5;
id | username
----±---------
1 | tim_t4
2 | lily
3 | tom_t4
4 | jerry_t4
5 | sun
(5 rows)

omm=#