MySQL和oracle比较

2019独角兽企业重金招聘Python工程师标准>>>

1、判断字符串为空串

--Mysql：在MySQL中，空值(Null)与空字符(’’)是不相同的
select '' is null;  
+------------+
| '' is null |
+------------+
|          0 |
+------------+
select trim(' ')='';
+--------------+
| trim(' ')='' |
+--------------+
|            1 |
+--------------+
--所以在mysql中可以这样来判断空串
select *
  from table
 where trim(col) = '';
--***************************
--而在oracle，则空值(Null)与空字符(’’)是一样的
select *
  from table
 where trim(col) is null;

2、虚表dual，oracle和mysql均存在该虚表，但对于下面语句：

select * from dual;
-- mysql执行会报错
--oracle执行会查出如下：
D
-
X

3、关联表进行删除

-- mysql
delete a from hs_sett.fusettleholdsinfo a, hs_sett.fusettarg b
where a.exchange_type = b.exchange_type
  and b.exchange_type = 'F1';

--oracle
delete from hs_futuvip.fusettleholdsinfo a
where exists(select 1
               from hs_futuvip.fusettarg b
              where a.futu_exch_type = b.futu_exch_type
                and b.futu_exch_type = 'F1');

4、关联表更新

-- mysql
update futransfertotal a, fusettarg b 
   set a.clear_balance=0,a.active_flag='1'
 where a.exchange_type = b.exchange_type
   and b.asset_kind = '1';

-- oralce
update futransfertotal a
   set a.clear_balance=0,a.active_flag='1'
 where exists(select 1
                from fusettarg b
               where a.futu_exch_type = b.futu_exch_type
                 and b.futu_exch_type = 'F1')

 

转载于:https://my.oschina.net/u/2332532/blog/1862674