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 )
WHEREid 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 ) WHERET_DEV_ASSET_ACCOUNT.DEVICE_CODE LIKE 'B-%'
3.根据表名查询所有约束
select constraint_name,constraint_type,search_conditionfrom user_constraintswhere 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(selecta.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 lastUpdateDatefrom t_spares_account aleft join t_spares bon a.spares_num = b.spares_numwhere ......order by a.id desc) Awhere 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语句能查询到这一时间存在的数据，重新插入。
本文链接：https://my.lmcjl.com/post/3024.html
展开阅读全文
4 评论