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;                           --条件要知道怎么计算

 　　　　　　未完待续...