Oracle # Sql语句-蒲公英云

Oracle # Sql语句

1.SQL中，如何查询存在一个表而不在另一个表中的数据记录

select * from A where not exists(select 1 from B where A.ID=B.ID)

2.oracle update更新语句（sql将一个表中的某一列数据更新到另一个表中）

说明：以下的示例是多列数据

update WORKER_INFO set(WORKER_INFO.CLASS_NAME,WORKER_INFO.CLASS_ID,
WORKER_INFO.ORG_ID,WORKER_INFO.TEAM,WORKER_INFO.GROP)=
(select WORK_INFOKEEP.CLASS_NAME,WORK_INFOKEEP.CLASS_ID,
WORK_INFOKEEP.ORG_ID,WORK_INFOKEEP.TEAM,WORK_INFOKEEP.GROP 
from WORK_INFOKEEP  where WORK_INFOKEEP.WORK_ID=WORKER_INFO.WORK_ID)

3.新增一个表，通过另一个表的结构和数据

create table XTHAME.tab1 as select * from DSKNOW.COMBDVERSION
1、创建一张和已经存在的表一样结构的表，同时复制数据 
create table newTableName as select * from oldTableName;
//--newTableName 新建表的表名 oldTableName：系统中已经存在的表  
truncate table tablename; --删除表数据
1. 复制表结构及其数据：   
create table table_name_new as select * from table_name_old   
2. 只复制表结构：   
create table table_name_new as select * from table_name_old where 1=2;   
或者：   
create table table_name_new like table_name_old

附加：

①oracle 复制表到另一张，如何把表注释也一并弄过去？

select 'comment on column '||table_name||'.'||column_name||' is '||''''||comments||''';' from user_col_comments where table_name='XXXX'

②获取表注释：

select * from user_tab_comments;

③添加表注释：

comment on table DINGSCHEDULINGHIS is '注释';

4.如果表存在（两个表的表结构一样）

insert into tab1 select * from tab2;

5.两个表的表结构不一样

insert into WORK_ATTENDANCE_DINGANDHANHIS(
WORK_ID,
WORK_NAME,
WORK_ATTENDANCE_DATE,
IS_DAY_WORKER,
IS_MIDDLE_WORKER,
IS_NIGHT_WORKER,
PREPARATION_ONE,
PREPARATION_TWO,
PREPARATION_THREE,
PREPARATION_FOUR,
PREPARATION_FIVE,
REMARK,
ACQUISITIONTIME,
PREPARATION_SIX,
PREPARATION_SEVEN,
PREPARATION_EIGHT
)
select WORK_ID,
WORK_NAME,
WORK_ATTENDANCE_DATE,
IS_DAY_WORKER,
IS_MIDDLE_WORKER,
IS_NIGHT_WORKER,
PREPARATION_ONE,
PREPARATION_TWO,
PREPARATION_THREE,
PREPARATION_FOUR,
PREPARATION_FIVE,
REMARK,
ACQUISITIONTIME,
PREPARATION_SIX,
PREPARATION_SEVEN,
PREPARATION_EIGHT from WORK_ATTENDANCE_DINGANDHAN;--将WORK_ATTENDANCE_DINGANDHAN表中的数据新增至WORK_ATTENDANCE_DINGANDHANHIS表