openGauss每日一练第13天 加油

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;
omm=# select * from reason_t1;
 r_reason_sk |   r_reason_id    |                                            r_reaso
n_desc                                             
-------------+------------------+---------------------------------------------------
---------------------------------------------------
           1 | AAAAAAAABAAAAAAA | reason1                                           
                                                  
           1 | AAAAAAAABAAAAAAA | 
           1 | AAAAAAAABAAAAAAA | 
             |                  | 
(4 rows)

–指定表插入数据到当前表

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;
select * from reason_t2;
 r_reason_sk |   r_reason_id    |                                            r_reaso
n_desc                                             
-------------+------------------+---------------------------------------------------
---------------------------------------------------
           1 | AAAAAAAABAAAAAAA | reason1                                           
                                                  
           1 | AAAAAAAABAAAAAAA | 
           1 | AAAAAAAABAAAAAAA | 
             |                  | 
(4 rows)

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。

omm=# SELECT * FROM newproducts
omm-# ;
 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)

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;
 select * from reason_t3;
 r_reason_sk |   r_reason_id    |                                            r_reaso
n_desc                                             
-------------+------------------+---------------------------------------------------
---------------------------------------------------
           1 | AAAAAAAABAAAAAAA | reason1                                           
                                                  
           1 | AAAAAAAABAAAAAAA | 
           1 | AAAAAAAABAAAAAAA | 
             |                  | 
(4 rows)

课程作业

1.创建表1并在表中插入数据，分别指定字段和整行为缺省值

omm=#  create table table1(id int,name char(20));
CREATE TABLE
omm=# insert into table1 values(1,'aaaa');
INSERT 0 1
omm=# insert into table1 values(1);
INSERT 0 1
omm=# insert into table DEFAULT  values;
ERROR:  syntax error at or near "table"
LINE 1: insert into table DEFAULT  values;
                    ^
omm=# insert into table1 DEFAULT  values;
INSERT 0 1
omm=# select * from table1;
omm=#  id |         name         
----+----------------------
  1 | aaaa                
  1 | 
    | 
(3 rows)

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

omm=# create table table2(id int,name char(20));
CREATE TABLE
omm=# insert into table2 select * from table1;
INSERT 0 3
omm=# select * from table2;
 id |         name         
----+----------------------
  1 | aaaa                
  1 | 
    | 
(3 rows)

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

omm=# create table table3(id int,name char(20));
omm=# CREATE TABLE

omm=# create table table4(id int,name char(20));
CREATE TABLE
omm=# insert into table3 values(1,'aaa');
INSERT 0 1
omm=# insert into table3 values(,'aaa');2,'aaa';
omm=# 
omm=# 
omm=# omm=# 

omm=# 
omm=# select * from table3;
(1 row)

omm=#  id |         name         
----+----------------------
  1 | aaa                 

omm=# insert into table3 values(2,'bbb');
omm=# INSERT 0 1

omm=# insert into table3 values(3,'ccc');
INSERT 0 1
omm=# 
omm=# insert into table4 values(4,'ddd');
INSERT 0 1
omm=# insert into table4 values(5,'eee');
INSERT 0 1
omm=# insert into table4 values(1,'aaa');
omm=# INSERT 0 1

omm=# select * from table3;

  3 | ccc                 
(3 rows)

omm=#  id |         name         
----+----------------------
  1 | aaa                 
  2 | bbb                 
omm=# 
omm=# select * from table4;
 id |         name         
----+----------------------
  4 | ddd                 
  5 | eee                 
  1 | aaa                 
(3 rows)

omm=# 
omm=# merge into table3 a using table4 b on(a.id=b.id) when matched then update set a.name=b.name when not matched then insert values(b.id,b.name);
MERGE 3
omm=# 
omm=# select * from table3;
 id |         name         
----+----------------------
  2 | bbb                 
  3 | ccc                 
  4 | ddd                 
  5 | eee                 
  1 | aaa                 
(5 rows)

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

omm=# copy table3 to '/home/omm/table3.dat';
COPY 5
omm=# create table table5(id int,name char(20));
CREATE TABLE
omm=# copy table5 from '/home/omm/table3.dat';
COPY 5
omm=# select * from table5;
 id |         name         
----+----------------------
  2 | bbb                 
  3 | ccc                 
  4 | ddd                 
  5 | eee                 
  1 | aaa                 
(5 rows)