insert ,update 以及merge 的使用

本次将用到的几个表,以及字段
> emp表
empno, ename,job,mgr,hiredate,sal,comm,deptno
> dept表
deptno,dname,loc
> emp_bonus表
empno, received,type
>new_sal表
demptno,sal

/*-----------------  INSERT 的使用 --------------------------------*/
--1. 插入到一个表
insert into dept
(deptno,dname,loc)
vaules(1,"A","C"),
      (2,"V","T")

insert into dept
values(1,"lily","NEW YORK")

-- 2. 从一个表向另外一个表复制行
insert into dept_east
(deptno,dname,loc)
select  deptno,
        dname,
        loc
from dept
where loc in ("NEW YORK")

-- 3. 复制表定义（只复制表的结构，没有数据）
 -- In Mysql
create table dept_2 like dept   
  
 --In MSSQL or oracle
select *
into dept_2
from dept
where 1=0   /*没有这个限制条件将连数据一起复制*/

create table dept_2
as
select *
from dept
where 1=0

--4. 一次向多个表中插入记录
  --In oracle
insert all
    when loc in ("NEW YORK") then
        into dept_east(dept_no,dname,loc)
        vaules(dept_no,dname,loc)
    when loc in ("CHICAGO") then
        into dept_mid(dept_no,dname,loc)
        vaules(dept_no,dname,loc)
    else
        into dept_west(dept_no,dname,loc)
        vaules(dept_no,dname,loc)
select dept_no,dname,loc
from dept

/*----------------------update 的使用------------------------------------*/

--5. 在表中更新记录
update emp
    set sal=sal*1.1
    where deptno=20
            
update emp
    set sal=sal*1.2
    where empno in
              (select empno from emp_bonus)

update emp
    set sal=sal*1.2
   where exists(
       select null
       from emp_bonus as eb
       where emp.empno=eb.empno
      )

-- 6. 用其他表更新
update emp
    　set sal=ns.sal
          comm=ns.sal/2
　　　from emp e,
     new_sal ns
     where ns.deptno=e.deptno    

/*-----------------------------merge 函数的使用------------------------------------ */
/*
-- 简单来说就是 ： 如果记录存在则更新，不存在则插入
>emp_commission 表
empno,sal,comm,deptno
>emp表
empno, ename,job,mgr,hiredate,sal,comm,deptno
1. 如果emp_commission 表员工也存在于emp表中，那么将emp_commission 的提成(comm)更新为1000
    对于更新到1000的员工，SAL少于2000则删除
2. 否则，从emp表取出empno，sal,comm,deptno，插入到emp_commission
*/
 -- in  oracle, MSSQL

merge into emp_commission ec
using (select * from emp) emp
    on(ec.empno=emp.empno)
    when matched then
        update set ec.comm=1000
        delete where(sal<2000)
    when not matched then
        insert (ec.empno,ec.name,ec.comm)
        values(emp.empno,emp.name,emp.comm)