1、更新表的不同
oracle
??? <update id="updateBroadcastIpIdAndResult" parameterClass="map">
??? ??? update IP_GN_BAXX_GBXX_LS
??? ??? <dynamic prepend="set">
??? ??? ??? <isNotNull prepend="," property="provinceSystemId">
??? ??? ??? ??? SJXT_ID = #provinceSystemId#
??? ??? ??? ??? <isNotNull prepend="," property="broadcastIpId">
??? ??? ??? ??? ??? JLID = #broadcastIpId#
??? ??? ??? ??? </isNotNull>
??? ??? ??? </isNotNull>
??? ??? ??? <isNotNull prepend="," property="operatingResult">
??? ??? ??? ??? CZJG = #operatingResult#
??? ??? ??? </isNotNull>
??? ??? ??? <isNotNull prepend="," property="resultInfo">
??? ??? ??? ??? JGMS = #resultInfo#
??? ??? ??? </isNotNull>
??? ??? </dynamic>
??? ??? where RBSJ = ( select max(RBSJ) from IP_GN_BAXX_GBXX_LS where
??? ??? JLID = #tempBroadcastIpId# ) and JLID = #tempBroadcastIpId#
??? </update>
mysql
??? <update id="updateSourceIpIdAndResult" parameterClass="map">
??? ??? update IP_GN_LY_BAXX_LS?
??? ??? <dynamic prepend="set">
??? ??? ??? <isNotNull prepend="," property="provinceSystemId">
??? ??? ??? ??? SJXT_ID = #provinceSystemId#
??? ??? ??? ??? <isNotNull prepend="," property="sourceIpId">
??? ??? ??? ??? ??? JLID = #sourceIpId#
??? ??? ??? ??? </isNotNull>
??? ??? ??? </isNotNull>
??? ??? ??? <isNotNull prepend="," property="operatingResult">
??? ??? ??? ??? CZJG = #operatingResult#
??? ??? ??? </isNotNull>
??? ??? ??? <isNotNull prepend="," property="resultInfo">
??? ??? ??? ??? JGMS = #resultInfo#
??? ??? ??? </isNotNull>
??? ??? </dynamic>
??? ??? where id in(
??? ??? ??? SELECT c.id FROM (
??? ??? ??? ??? SELECT id FROM IP_GN_LY_BAXX_LS WHERE
??? ??? ??? ??? ??? RBSJ = (SELECT
??????????????? ??? ??? ??? ??? MAX(RBSJ)
????????????? ??? ??? ??? ??? FROM IP_GN_LY_BAXX_LS
????????????? ??? ??? ??? ??? where JLID = #tempSourceIpId#
????????????? ??? ??? ??? ??? )
??? ??? ??? ??? ??? AND JLID = #tempSourceIpId#
? ??? ??? ??? ??? ) c?
??? ??? )???
??? </update>
?? 1. 一般而言，不能更改表，并从子查询内的相同表进行选择。
?? 2. 例如，该限制适用于具有下述形式的语句：
?? 3. DELETE FROM t WHERE ... (SELECT ... FROM t ...);
?? 4. UPDATE t ... WHERE col = (SELECT ... FROM t ...);
?? 5. {INSERT|REPLACE} INTO t (SELECT ... FROM t ...);
?? 6. 例外：如果为FROM子句中更改的表使用子查询，前述禁令将不再适用。
?? 7. 例如：
?? 8. UPDATE t ... WHERE col = (SELECT (SELECT ... FROM t...)
?? 9. AS _t ...);
? 10. 禁令在此不适用，这是因为FROM中的子查询已被具体化为临时表，因此 “t”中的相关行已在满足“t”条件的情况下、在更新时被选中。
看了上面的说明顿悟，于是再加上了一个IN的子查询


DELETE 不同

oracle
??? DELETE 表名 或DELETE FROM 表名

MYSQL?? 只能是DELETE FROM 表名

?

insert? delete 操作是不允许使用表别名的。

update? where条件中不允许自连接

子查询别名
mysql 子查询返回的临时表必须要起别名
oracle 没有限制

group by 多列时
oracle可以对多列用括号括起来
mysql则不行，否则会报Operand should contain 1 column(s) error

create table
oracle null,not null 放在default 值后面
mysql default 值 null，not null