1、分页

mysql：

select * from table limit m;  --查询m行

select * from table limit m, n;  --查询m+n行，撇去前面的m行，返回最后的n行

select * from table limit n offset m;

select * from table where id > 100 limit 0, 10; --第101~110行

oracle：

select * from (select T1.*, rownum rn from (select * from table) T1 where rownum <= 110) where rn > 100;

2、batchUpdateOrInsert

oracle：

    
             merge into tabel T1        using (
     
              SELECT        #{item.XX1} XX1,        #{item.XX2} XX2,        FROM DUAL    
             ) T2 on (T1.XX1 = T2.XX1)        when matched then        update set        T1.XX2 = T2.XX2,        T1.update_date = to_date(#{now}, 'yyyy-mm-dd hh24:mi:ss')        when not matched then        insert (ID, XX1, XX2, update_date)        values (SEQ_TABLE_ID.nextval, T2.XX1, T2.XX2, to_date(#{now}, 'yyyy-mm-dd hh24:mi:ss'))    
    

by the way,oracle建表,需要对自增主键另外建序列

CREATE TABLE A(  ID NUMBER(8,0) NOT NULL , USER_MOBILE VARCHAR2(11) NOT NULL , CREATE_DATE DATE NOT NULL , CREATE_BY VARCHAR2(32) NOT NULL , UPDATE_DATE DATE NOT NULL , UPDATE_BY VARCHAR2(32) NOT NULL , CONSTRAINT A_PK PRIMARY KEY   (    ID   )  ENABLE );COMMENT ON COLUMN A.ID IS '主键';COMMENT ON COLUMN A.USER_MOBILE IS '会员账号';--创建序列create sequence SEQ_A_IDminvalue 1maxvalue 9999999999999999999999999999start with 1increment by 1nocache;

mysql：Todo

3、oracle：select 1 from dual;

mysql：select 1;

4、结合mybatis，插入后返回主键

oracle单条可以返回，批量多条不能返回。

            
     
                  select SEQ_A_ID.nextval from dual        
             insert into A        
     
                  ID,            
      
                       USER_ID,            
                  
      
                       CHANGE_TYPE,            
                  
      
                       UPDATE_DATE,            
              
             
     
                  #{id,jdbcType=DECIMAL},            
      
                       #{userId,jdbcType=DECIMAL},            
                  
      
                       #{changeType,jdbcType=VARCHAR},            
                  
      
                       #{updateDate,jdbcType=TIMESTAMP},            
              
         
    

插入后，XXmodel.getId()可以得到主键id。

            
     
                  select SEQ_A_ID.nextval from dual        
             insert into A        (ID, USER_ID, CHANGE_TYPE, UPDATE_DATE)        select SEQ_A_ID.nextval, A.* from        (        
     
                  select            #{item.userId,jdbcType=DECIMAL},            #{item.changeType,jdbcType=VARCHAR},            #{item.updateDate,jdbcType=TIMESTAMP}            FROM DUAL        
             ) A    
    

oracle单条，批量多条都可以返回。Todo