mysql,sqlserver,oracle各自的存在更新不存在添加写法

在向表中插入数据的时候，经常遇到这样的情况：

  1. 首先判断数据是否存在；
  2. 如果不存在，则插入:
  3. 如果存在，则更新。

SQL server

脚本先查询，没有数据再进行数据插入，有数据就走更新

if not exists (select 1 from t where id = 1)

 


      insert into t(id, update_time) values(1, getdate())

 


   else


 


      update t set update_time = getdate() where id = 1


 


或者

 



if exists  (select 1 from t where id = 1)

 


      insert into t(id, update_time) values(1, getdate())

 


   else


 


      update t set update_time = getdate() where id = 1

mysql

replace into 跟 insert 功能类似，不同点在于：replace into 首先尝试插入数据到表中。

  1. 如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据。
  2. 否则，直接插入新数据。
    Ps：要注意的是：插入数据的表必须有主键或者是唯一索引！否则的话，replace into 会直接插入数据，这将导致表中出现重复的数据。
###插入或替换 -- 没有就插入，有就先删除再插入

 



REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

 


若id=1的记录不存在，REPLACE语句将插入新记录，否则，当前id=1的记录将被删除，然后再插入新记录。

 


###插入或更新 -- 如果没有数据就行新增，有数据就更新处理

 


INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;

 


###插入或忽略 -- 如果已有id为1的数据本次数据就不会再插入，会忽略本次sql操作

 


INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

oracle

脚本先查询，如果有数据表先进行删除操作，然后在进行新增表操作。
同样的，如果是数据，会先查询数据，如果有就进行删除，删除后再进行插入，如果没有就直接进行插入

declare num number;   

 


begin

 


    select count(1) into num from user_tables where table_name='ACCOUNT';   

 


    if num > 0 then   

 


      dbms_output.put_line('存在!');

 


      execute immediate 'drop table ACCOUNT '; 

 


    end if;   

 


      execute immediate 'create table Account


 


                        (

 


                                AccountID nvarchar2(50) primary key,

 


                                AccountName nvarchar2(50) 

 



                        )';  

 


      dbms_output.put_line('成功创建表!');

 



end;

1：隐式游标法 SQL%NOTFOUND SQL%FOUND
SQL%NOTFOUND 是SQL中的一个隐式游标，在增删查改的时候自动打开，如果有至少有一条记录受影响，都会返回false，这就就巧妙的构思出了第一种解决方案：

begin

 



update account set AccountName = '修改-a' where AccountID = '5';

 


IF SQL%NOTFOUND THEN


 


   insert into account(AccountID,AccountName) values('5','添加-b');

 



END IF;

 



end;

 


先根据唯一ID到数据表中修改一条记录，如果这条记录在表中存在，则修改，并且SQL%NOTFOUND返回false。如果修改的记录不存在，SQL%NOTFOUND返回true，并且执行插入语句。

2：异常法 DUP_VAL_ON_INDEX
当Oracle语句执行时，发生了异常exception进行处理，需要唯一索引id，重复插入出现异常

begin

 



insert into account(AccountID,AccountName) values('6','添加-b');

 


exception 

 



when DUP_VAL_ON_INDEX then 

 



begin 

 



update account set AccountName = '修改-b' where AccountID = '6';

 



end;

 



end;

3：虚拟表法 dual：
dual是一个虚拟表，用来构成select的语法规则，oracle保证dual里面永远只有一条记录。

declare t_count number;

 


begin

 



select count(*) into t_count from dual where exists(select 1 from account where AccountID='11');

 



if t_count< 1 then


 


  dbms_output.put_line('添加');

 


  insert into account(AccountID,AccountName) values('11','添加-11');

 


else

 


  dbms_output.put_line('修改');

 


  update account set AccountName = '修改-11' where AccountID = '11';

 


  end if;

 



end;

4：no_data_found法
先查找要插入的记录是否存在，存在则修改，不存在则插入。具体的实现如下：

declare t_cols number;

 


begin

 



select AccountName into t_cols from account where AccountID = '8';

 



exception 

 



when no_data_found then begin 

 


   --dbms_output.put_line('添加');

 


   insert into account(AccountID,AccountName) values('8','添加-8');

 



end;

 



when others then 

 


  begin

 


    --dbms_output.put_line('修改');

 


    update account set AccountName = '修改-8' where AccountID = '8';

 



end;

 



end;

5：merge法
先来看一下merge的语法，

MERGE INTO table_name alias1   

 



USING (table|view|sub_query) alias2  

 



ON (join condition)   

 



WHEN MATCHED THEN   

 


    UPDATE table_name SET col1 = col_val1


 



WHEN NOT MATCHED THEN   

 


    INSERT (column_list) VALUES (column_values);

 


模仿

 



merge into Account t1  

 



using (select '3' AccountID,'肖文博' AccountName from dual) t2  

 



on (t1.AccountID = t2.AccountID)  

 



when matched then  

 


     update set t1.AccountName = t2.AccountName

 



when not matched then  

 


     insert values (t2.AccountID, t2.AccountName);  

 



commit;