openGauss每日一练第 13天|导入数据

学习目标

学习openGauss导入数据

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

CREATE TABLE su_t1                                                 
(                                                                    
su_sk integer,                                                     
su_id character(16),                                               
su_desc character(100)                                             
);                                                                   
insert into su_t1 values(1, 'AAA', 'BBB');                  

–没有数值的字段将被填充为字段的缺省值

insert into su_t1 values(2, 'CCC');                             

–明确字段为缺省值

insert into su_t1 values(3, 'DDD', DEFAULT);                    

–明确整行为缺省值

insert into su_t1 DEFAULT VALUES;                                  
select * from su_t1;                                               

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

CREATE TABLE su_t2
(
su_sk integer,
su_id character(16),
su_desc character(100)
);
insert into su_t2 select * from su_t1;

select * from su_t2;

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

CREATE TABLE t3                                                      
(                                                                    
su_id INTEGER,                                                  
su_name VARCHAR2(60),                                           
su_ca VARCHAR2(60)                                                
);                                                                   
INSERT INTO t3 VALUES                                                
(1001, 'XIAOMING', 'BOY'),                                        
(1002, 'XIAOHONG', 'GRIL'),                                              
(1003, 'XIAOHUA', 'GRIL'),                                            
(1004, 'XIAOLI', 'BUY')
(1011, 'LISHA', 'GRIL'),;                                           
                                                                     

CREATE TABLE t4                                                      
(                                                                    
su_id INTEGER,                                                  
su_name VARCHAR2(60),                                           
su_ca VARCHAR2(60)                                                
);                                                                   
INSERT INTO t4 VALUES                                                
(1007, 'XIAOPING', 'BOY'),                                     
(1002, 'XIAOHONG ', 'GRIL'),                                      
(1003, 'XIAOHUA', 'GRIL'),                                             
(1006, 'Lily', 'BOY'),                                              
(1009, 'HUAHUA', 'BOY');                                             
                                                                     

MERGE INTO t3 np                                                     
USING t4 p                                                           
ON (np.su_id = p.su_id )                                   
WHEN MATCHED THEN                                                    
UPDATE SET np.su_name = p.su_name, np.su_ca = p.su_ca
WHEN NOT MATCHED THEN                                                
INSERT VALUES (p.su_id, p.su_name, p.su_ca) ;                                                                    
SELECT * FROM t3;                                                    

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

–将表数据输出

copy t3 to stdout;                                                   

–将表数据拷贝到文件

copy t3 to '/home/omm/t3.dat';                                       
CREATE TABLE t5 (LIKE t3);                                           

–将数据从文件拷贝到表

copy t5 from '/home/omm/t3.dat';                                     
select * from t5;