oracle SQL 语法

最新推荐文章于 2022-04-07 11:47:05 发布

原创最新推荐文章于 2022-04-07 11:47:05 发布 · 569 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#database

oracle技术专栏收录该内容

58 篇文章

订阅专栏

这篇博客探讨了SQL中的关键操作，包括从employees2表中删除department_id为90的记录，使用子查询进行复杂更新，根据条件分批插入数据到不同表中，以及利用merge语句进行匹配更新与插入。这些技巧对于数据库管理和数据操作至关重要。

DELETE

delete from ( select * from employees2 where department_id=90);

UPDATE

update ( select e1.salary,e2.salary new_sal
         from  employees e1,employees2 e2
         where e1.emp_id=e2.emp_id)
set salary=new_sal;

INSERT

insert all
when sum_orders <10000 then
into t1
when  sum_orders >=10000 and  sum_orders<20000 then
into t2
else
int t3
select customer_id,sum(order_total) sum_orders
from oe.orders
group by customer_id

MERGE

merge into t1 b
using(
     select tmp_id,salary,department_id from employees where dep_id=60) e
     on (b.emp_id=e.emp_id)

when matched then 
     update set b.col2=e.col2*0.2 where b.col1=0
     delete where (e.col3>7000)

when not matched then
      insert (b.emp_id,b.bonus_amt)  values (e.tmp_id,e.salary*0.1)
      where (e.salary<7500)