1.模糊查詢

• Mysql

SELECT * 
FROM USER
WHERE username like CONCAT('%','王','%')

• Oracle

SELECT * 
FROM USER
WHERE username like  '%'||'王'||'%'

Oracle里面也有concat函數(shù)，如果要使用CONCAT函數(shù)的話，我們需要使用兩個CONCAT函數(shù)，對模糊查詢進(jìn)行拼接,也是可以的。這里的話，我就不演示了。

image.png

2.按照時間查詢

• Mysql

SELECT * 
FROM USER
WHERE create_time between ''2019-12-10 12:22:29" and "2019-12-11 11:22:29"

• Oracle

SELECT * 
FROM USER
WHERE create_time between TO_DATE(''2019-12-10 12:22:29" , 'yyyy-mm-dd hh24:mi:ss' ) and TO_DATE("2019-12-11 11:22:29", 'yyyy-mm-dd hh24:mi:ss' ) 

3.分頁查詢

• MySQL：

    SELECT
        sheet.* 
    FROM
        sheetA act,
        sheetB sheet,
        sheetC actop 
    WHERE
        AND sheetB.ACT_INST_ID = sheetA.ACTIVITYINSTID 
        AND sheetA.REPOID = sheetC.REPO_ID 
        Limit 0,10

• Oracle：
  單表分頁

    SELECT
    * 
FROM
    sheetA
WHERE
    ROWID NOT IN ( SELECT ROWID FROM sheetA WHERE ROWNUM <= 0 ) 
AND ROWNUM <= 10

關(guān)聯(lián)查詢分頁

SELECT
    sheets.* 
FROM
    (
    SELECT
        sheet.* ,
        ROWNUM "rownum" 
    FROM
        sheetA act,
        sheetB sheet,
        sheetC actop 
    WHERE
        AND sheetB.ACT_INST_ID = sheetA.ACTIVITYINSTID 
        AND sheetA.REPOID = sheetC.REPO_ID 
    )  sheets
WHERE
    sheets."rownum" >= 0
    AND sheets."rownum" <= 5

后續(xù)遇到在更新.....