学习心得

COPY
MERGE INTO

0.进入系统

su - omm
gsql -r

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

创建表1

create table product_t1(
product_id integer,
product_name char(30),
product_sk char(20),
inventory integer DEFAULT 0
);

• 回显

插入值

CREATE TABLE

INSERT INTO product_t1 values(1, 'box', 'misc', 200);
INSERT INTO product_t1 values(2, 'needle', 'misc', 200);
INSERT INTO product_t1 DEFAULT VALUES;

• 回显

INSERT 0 1

查看

select * from product_t1;

• 回显

 product_id |          product_name          |      product_sk      | inventory
------------+--------------------------------+----------------------+-----------
          1 | box                            | misc                 |       200
          2 | needle                         | misc                 |       200
            |                                |                      |         0
(3 rows)

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

create table product_t2(
product_id integer,
product_name char(30),
product_sk char(20),
inventory integer DEFAULT 0
);

• 回显

CREATE TABLE

INSERT INTO product_t2 SELECT * FROM product_t1;
SELECT * FROM product_t2;

• 回显

 product_id |          product_name          |      product_sk      | inventory
------------+--------------------------------+----------------------+-----------
          1 | box                            | misc                 |       200
          2 | needle                         | misc                 |       200
            |                                |                      |         0
(3 rows)

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

create table product_t3(
product_id integer,
product_name char(30),
product_sk char(20),
inventory integer DEFAULT 0
);
create table product_t4(
product_id integer,
product_name char(30),
product_sk char(20),
inventory integer DEFAULT 0
);
INSERT INTO product_t4 values(5, 'apple', 'fruit', 1200);
INSERT INTO product_t4 values(6, 'orange', 'fruit', 2000);

• 回显

CREATE TABLE
INSERT 0 1

MERGE INTO product_t3 np
USING product_t1 p
ON (np.product_id = p.product_id )
WHEN MATCHED THEN
UPDATE SET np.product_name = p.product_name, np.product_sk = p.product_sk, np.inventory = p.inventory
WHEN NOT MATCHED THEN
INSERT VALUES (p.product_id, p.product_name, p.product_sk, p.inventory) ;

• 回显

MERGE 3

MERGE INTO product_t3 np
USING product_t4 p
ON (np.product_id = p.product_id )
WHEN MATCHED THEN
UPDATE SET np.product_name = p.product_name, np.product_sk = p.product_sk, np.inventory = p.inventory
WHEN NOT MATCHED THEN
INSERT VALUES (p.product_id, p.product_name, p.product_sk, p.inventory) ;

• 回显

MERGE 2

SELECT * FROM product_t3;

• 回显

 product_id |          product_name          |      product_sk      | inventory
------------+--------------------------------+----------------------+-----------
          1 | box                            | misc                 |       200
          2 | needle                         | misc                 |       200
            |                                |                      |         0
          5 | apple                          | fruit                |      1200
          6 | orange                         | fruit                |      2000
(5 rows)

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

copy product_t3 to '/home/omm/product_t3.dat';

• 回显

COPY 5

CREATE TABLE product_t5 (LIKE product_t3);

• 回显

CREATE TABLE

–导入

COPY product_t5 '/home/omm/product_t3.dat';
SELECT * FROM product_t5;

• 回显

 product_id |          product_name          |      product_sk      | inventory
------------+--------------------------------+----------------------+-----------
          1 | box                            | misc                 |       200
          2 | needle                         | misc                 |       200
            |                                |                      |         0
          5 | apple                          | fruit                |      1200
          6 | orange                         | fruit                |      2000
(5 rows)