创建表，并复制数据
create table table1 as select * from table2;
只创建表结构，不复制数据
create table table1 as select * from table2 where 1=2;
复制表数据，两张表字段相同：(table1必须存在)
insert into table1 select * from table2;
复制表数据，两张表字段不相同：(table1必须存在)
insert into table1(field1,field2,field3) select field1,field2,field3 from table2;
删除表：
drop talbe table_name;
删除表数据：
truncate table table_name;
如果xxx表不存在某条数据，就从xxx表插入该条数据INSERTINTOtb_cablecheck_dtsj
SELECT
od.*,
''ro_type_id
FROM
osspad.tb_cablecheck_dtsjod
WHERE
NOTEXISTS(SELECT1FROMtb_cablecheck_dtsjdWHEREd.id=od.id)
带自增长id，需要写成子查询INSERTINTOtb_cablecheck_dtsjSELECT
SEQ_CABLECHECK_DTSJ_ID.nextval,
t.*
FROM
(
SELECT
d.dzid,
d.dzbm,
d.sbid,
d.sbbm,
d.glmc,
d.h,
d.install_sbid,
''ro_type_id
FROM
osspad.tb_cablecheck_dtsjd,
areaa
WHERE
d.areaid=a.area_id
ANDa.parent_area_id=20
ANDd.bdsj>=TO_DATE('-12-01','yyyy-mm-dd')
ANDd.bdsj<=TO_DATE('-12-31','yyyy-mm-dd')
)t
备份原表数据
create table tb_cablecheck_equipment_bak as select * from tb_cablecheck_equipment;
删除原表
drop table tb_cablecheck_equipment;
创建临时表createtabletb_equipment_0112as
selecte.equipment_id,
e.equipment_code,
e.equipment_name,
e.area_id,
e.address,
e.parent_area_id,
e.grid_id,
e.install_sbid,
e.install_sbbm,
e.install_dzbm,
decode(e.longitude,null,b.longitude,e.longitude)longitude,
decode(e.latitude,null,b.latitude,e.latitude)latitude,
e.station_id,
e.update_time
fromtb_cablecheck_equipmenteleftjointb_base_equipmentb
one.equipment_id=b.phy_eqp_idande.equipment_code=b.point_no
恢复原表并插入数据createtabletb_cablecheck_equipment
as
select*fromtb_equipment_0112
查询原表
select * from tb_cablecheck_equipment
select count(1) from tb_cablecheck_equipment
