oracle查詢相關註意點

單表查詢:

　　1.or 和 and 混合使用 

　　　　需求:查詢業主名稱包含'劉'或門牌號包含'5'的,並且地址編號為3的記錄

SELECT * FROM T_OWNERS WHERE ("NAME" LIKE '%劉%' or HOUSENUMBER LIKE '%5%') AND ADDRESSID = 3

　　and 的許可權優先於 or 所以需要在or的兩邊添加()

　　2. 範圍查詢

　　　　　除了傳統的 where usenum >=100 and usenum <=200,還可以用between and 查詢

SELECT * from T_ACCOUNT WHERE USENUM BETWEEN 100 and 200   --只適合>= 或<=

　　3.去空查詢

SELECT * FROM T_PRICETABLE WHERE MAXNUM IS NULL;
SELECT * FROM T_PRICETABLE WHERE MAXNUM IS NOT null;

　 4.去掉重覆記錄

SELECT ADDRESSID FROM T_OWNERS GROUP BY ADDRESSID;
SELECT DISTINCT ADDRESSID FROM T_OWNERS; --執行效率高

　  5.基於偽列的查詢

　　　　rowid : 表中每一行在數據文件中都有一個物理地址,rowid返回就是該行的物理地址

SELECT ROWID , t.* FROM T_AREA t;

　　　　rownum : 通過rownum偽列可以限制查詢結果集中返回的行數,可用於分頁

SELECT ROWNUM , t.* FROM T_OWNERS t;

　　6.聚合函數

　　　　sum(),avg(),max(),min(),count() 括弧內傳遞條件,如:

SELECT "COUNT"(*) FROM T_OWNERS t WHERE OWNERTYPEID = 1;

　　　　分組聚合group by 如果後面加 having條件,即在分組後過濾數據

SELECT AREAID , "SUM"(MONEY) FROM T_ACCOUNT GROUP BY AREAID HAVING "SUM"(MONEY) > 169000

多表查詢:

連接規定 能用內連接 就不用外鏈接 能用左外 就 不用右外

　　1.內連接

　　　　需求:查詢顯示業主編號,業主名稱,業主類型名稱

SELECT
    t1."ID",
    T1."NAME",
    T2."NAME"
FROM
    T_OWNERS t1,
    T_OWNERTYPE t2
WHERE
    T1.OWNERTYPEID = T2."ID"

　　　　需求:查詢顯示業主編號,業主名稱,地址,業主類型名稱

SELECT
    T1."ID",
    T1."NAME",
    t2."NAME",
    T3."NAME"
FROM
    T_OWNERS t1,
    T_OWNERTYPE t2,
    T_ADDRESS t3
WHERE
    T1.OWNERTYPEID = T2."ID"
AND t1.ADDRESSID = T3."ID";

 　　註意:多表的內連接,可以先寫from xxx ,xxx ,xxx 以及where xx=yy,aa=bb,xx=zz,最後再寫select * 需要顯示的內容

　　2.左外連接

　　　　需求:查詢業主的賬務記錄,顯示業主的編號,名稱,年,月,金額,沒有賬務信息也要列出

SELECT                   --這是SQL1999標準的語法
    A1."ID",
    A1."NAME",
    B1."YEAR",
    B1."MONTH",
    B1.MONEY
FROM
    T_OWNERS a1
LEFT JOIN T_ACCOUNT b1 ON A1."ID" = b1.OWNERUUID

SELECT 　　　　　　　　　　　--這是ORACLE提供的語法
    A1."ID",
    A1."NAME",
    B1."YEAR",
    B1."MONTH",
    B1.MONEY
FROM
    T_OWNERS a1,
    T_ACCOUNT b1
WHERE
    A1."ID" = B1.OWNERUUID (+);

　　註意:left join 左邊的是主表,右邊的是輔表,on後面是條件;ORACLE提供的,誰是輔表就在誰身上

　　　　加 (+) .

　　3.子查詢

　　　　單行子查詢:

　　　　　　　需求:查詢2012年01月用水量大於台賬記錄平均值的數據.分析:先查平均值,再傳入

SELECT
    *
FROM
    T_ACCOUNT
WHERE
    "YEAR" = '2012'
AND "MONTH" = '01'
AND USENUM > (
    SELECT　　　　　　　　　　　　--查平均值
        "AVG" (USENUM)
    FROM
        T_ACCOUNT
    WHERE
        "YEAR" = '2012'
    AND "MONTH" = '01'
)

　　　　多行子查詢:

　　　　　　需求:查詢地址編號為1,3,4的業主記錄.分析:如果用or來編寫太繁瑣,所以用in

SELECT * FROM T_OWNERS WHERE ADDRESSID IN (1,3,4);

　　　　　　　需求:查詢地址含有"花園"的業主信息.分析:先模糊查詢,再傳入

SELECT
    *
FROM
    T_OWNERS
WHERE
    ADDRESSID IN (
        SELECT
            "ID" 　　　　　　　　--外查詢需要addressID,所以內查詢要查id
        FROM
            T_ADDRESS
        WHERE
            "NAME" LIKE '%花園%'
    );

　　註意:not in 不包含  is not null 代表不為空;子查詢 查詢的結果作為條件傳入另一個SQL語句

 分頁查詢:

　　簡單分頁查詢:

　　　　需求:分頁查詢台賬表,每頁10條記錄.分析:rownum不能使用>符號,只能<或<=或=1,怎麼辦?

SELECT ROWNUM,A1.* FROM T_ACCOUNT a1 WHERE ROWNUM <=10;

　　使用子查詢:

SELECT
    *
FROM
    (                        
        SELECT                                
            ROWNUM r,
            A1.*
        FROM
            T_ACCOUNT a1
        WHERE
            ROWNUM <= 20  　　　　--如果要嵌套排序查,代碼該如何寫 ?
    )
WHERE
    r > 10;

　　基於排序的分頁:

SELECT
    *
FROM
    (
        SELECT
            A .*, ROWNUM RN
        FROM
            (
                SELECT
                    *
                FROM
                    T_ACCOUNT
                WHERE
                    YEAR = '2012'
                ORDER BY
                    USENUM DESC
            ) A　　　　　　　　　　　　--A表代表排序,也是三層中的數據層,第一步
    ) B　　　　　　　　　　　　　　　　  --B表代表基於偽列查詢,也是三層中的偽列層,第二步
WHERE　　　　　　　　　　　　　　　　　　 --最後就是分頁查詢了,也是三層中的分頁層,第三步
    B.RN BETWEEN (3 - 1) * 5 + 1　　　　--ORACLE中索引是從1開始的
AND 3 * 5;                           --條件要知道怎麼計算

 　　　　　　未完待續...