Oracle # Sql语句

阳光穿透心脏的1/2处 2024-04-19 10:23 15阅读 0赞

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表

6.同一个表中，将A字段的指赋给B字段

update table_name set B =  A;