Oracle学习

最新推荐文章于 2025-07-15 22:53:59 发布

Comma_Zhang

最新推荐文章于 2025-07-15 22:53:59 发布

阅读量132

点赞数

CC 4.0 BY-SA版权

文章标签： sql 数据库

本文链接：https://blog.youkuaiyun.com/Comma_Zhang/article/details/105549710

本文深入探讨Oracle数据库的高级SQL技巧，包括更新语句、表备份、数据迁移及复杂查询等核心操作。通过实例演示如何高效地进行数据更新、创建备份表、数据插入以及跨表查询，特别聚焦于日期计算和条件匹配的细节处理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Oracle 学习

1.update

*******UPDATE T1 
SET T1.FMONEY = (select T2.FMONEY from t2 where T2.FNAME = T1.FNAME)
WHERE EXISTS(SELECT 1 FROM T2 WHERE T2.FNAME = T1.FNAME);
*

2.备份数据库表

*create table
JHTJ_USER.SB_MTBW_MTBW_BF as select * from JHTJ_USER.SB_MTBW_MTBW*

*******`

3.将一个表的数据插入另一个表

INSERT INTO
JHTJ_USER.SB_MTBW_MTBW_BF1

SELECT * FROM
JHTJ_USER.SB_MTBW_MTBW

INSERT
INTO table2

(column_name(s))

SELECT column_name(s)

FROM table1;






#

4.查询语句，两个表结构基本相同

select m.* from(

select
YEARMONTH,ORG,ZYSJ,HWZL,ZYQDLB,ZYZDLB,ZYGCQD,ZYGCZD,HZ,

    ( ( add_months(to_date(yearmonth,
'YYYYMM'),1)-to_date(yearmonth, 'YYYYMM'))-to_number(SUBSTR(zysj,7,2))+1)*HZ as
DT,

     add_months(to_date(yearmonth,
'YYYYMM'),1)-to_date(yearmonth, 'YYYYMM') AS BYTS

    from JSY_SZH_CZL 

    where YEARMONTH = '${$t}' and ORG =
'${$id}'

union all

select
YEARMONTH,ORG,ZYSJ,HWZL,ZYQDLB,ZYZDLB,ZYGCQD,ZYGCZD,ZZ AS HZ,

     ( ( add_months(to_date(yearmonth,
'YYYYMM'),1)-to_date(yearmonth, 'YYYYMM'))-to_number(SUBSTR(zysj,7,2))+1)*HZ AS
DT,

      add_months(to_date(yearmonth,
'YYYYMM'),1)-to_date(yearmonth, 'YYYYMM') AS BYTS

    from JSY_JZX_CZL

    where YEARMONTH = '${$t}' and ORG =
'${$id}'

)m