1.联表更新
UPDATE T_DEV_ASSET_ACCOUNT
SET T_DEV_ASSET_ACCOUNT.DEVICE_TYPE = ( SELECT ID FROM T_DEV_DEVICE_TYPE WHERE T_DEV_ASSET_ACCOUNT.PICTURE = T_DEV_DEVICE_TYPE.DEVICE_CATEGORY )
WHERE
id BETWEEN 40000
AND 45907
2.同一张表,用不同字段截取填充另外一字段
UPDATE T_DEV_ASSET_ACCOUNT
SET T_DEV_ASSET_ACCOUNT.DEVICE_MODAL =
SUBSTR( T_DEV_ASSET_ACCOUNT.DEVICE_CODE, 3 )
WHERE
T_DEV_ASSET_ACCOUNT.DEVICE_CODE LIKE 'B-%'
3.根据表名查询所有约束
select constraint_name,constraint_type,search_condition
from user_constraints
where table_name = 'T_SPARES_USE_BILL_DETAILED'
4.根据约束名称删除约束
alter table T_SPARES_USE_BILL_DETAILED drop constraint SYS_C00161785
5.分页查询
select * from
(
select A.*,ROWNUM RN from
(
select
a.spares_num as sparesNum,
b.spares_name as sparesName,
b.spares_type as sparesType,
b.modal as modal,
a.brand as brand,
a.spares_ownership as sparesOwnership,
a.storage_statistics as storageStatistics,
a.outbound_statistics as outboundStatistics,
a.average_monthly_consumption as averageMonthlyConsumption,
a.monthly_total_consumption as monthlyTotalConsumption,
a.safety_stock as safetyStock,
a.price as price,
a.urgency as urgency,
b.unit as unit,
a.LAST_UPDATE_DATE as lastUpdateDate
from t_spares_account a
left join t_spares b
on a.spares_num = b.spares_num
where ......
order by a.id desc
) A
where ROWNUM <= #{limit}
)
where RN > #{page}
6.短时间内数据恢复
insert into tablename select *
from tablename as of timestamp to_timestamp('2017-01-08 11:00:00','yyyy-mm-dd hh24:mi:ss')
select语句能查询到这一时间存在的数据,重新插入。