oracle查询注意点,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 BYADDRESSID;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.内连接

需求:查询显示业主编号,业主名称,业主类型名称

SELECTt1."ID",

T1."NAME",

T2."NAME"FROMT_OWNERS t1,

T_OWNERTYPE t2WHERET1.OWNERTYPEID= T2."ID"

需求:查询显示业主编号,业主名称,地址,业主类型名称

SELECTT1."ID",

T1."NAME",

t2."NAME",

T3."NAME"FROMT_OWNERS t1,

T_OWNERTYPE t2,

T_ADDRESS t3WHERET1.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

FROMT_OWNERS a1LEFT JOIN T_ACCOUNT b1 ON A1."ID" = b1.OWNERUUID

SELECT 　　　　　　　　　　　--这是ORACLE提供的语法A1."ID",

A1."NAME",

B1."YEAR",

B1."MONTH",

B1.MONEY

FROMT_OWNERS a1,

T_ACCOUNT b1WHEREA1."ID"= B1.OWNERUUID (+);

注意:left join 左边的是主表,右边的是辅表,on后面是条件;ORACLE提供的,谁是辅表就在谁身上

加 (+) .

3.子查询

单行子查询:

需求:查询2012年01月用水量大于台账记录平均值的数据.分析:先查平均值,再传入

SELECT

*

FROMT_ACCOUNTWHERE"YEAR" = ‘2012‘

AND "MONTH" = ‘01‘

AND USENUM >(SELECT　　　　　　　　　　　　--查平均值"AVG" (USENUM)FROMT_ACCOUNTWHERE"YEAR" = ‘2012‘

AND "MONTH" = ‘01‘)

多行子查询:

需求:查询地址编号为1,3,4的业主记录.分析:如果用or来编写太繁琐,所以用in

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

需求:查询地址含有"花园"的业主信息.分析:先模糊查询,再传入

SELECT

*

FROMT_OWNERSWHEREADDRESSIDIN(SELECT"ID" 　　　　　　　　--外查询需要addressID,所以内查询要查idFROMT_ADDRESSWHERE"NAME"LIKE ‘%花园%‘);

注意:not in 不包含  is not null 代表不为空;子查询 查询的结果作为条件传入另一个SQL语句

分页查询:

简单分页查询:

需求:分页查询台账表,每页10条记录.分析:rownum不能使用>符号,只能

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

使用子查询:

SELECT

*

FROM(SELECTROWNUM r,

A1.*

FROMT_ACCOUNT a1WHEREROWNUM<= 20 　　　　--如果要嵌套排序查,代码该如何写 ?)WHEREr> 10;

基于排序的分页:

SELECT

*

FROM(SELECTA .*, ROWNUM RNFROM(SELECT

*

FROMT_ACCOUNTWHERE

YEAR = ‘2012‘

ORDER BYUSENUMDESC) A　　　　　　　　　　　　--A表代表排序,也是三层中的数据层,第一步

) B　　　　　　　　　　　　　　　　 --B表代表基于伪列查询,也是三层中的伪列层,第二步WHERE　　　　　　　　　　　　　　　　　　 --最后就是分页查询了,也是三层中的分页层,第三步B.RNBETWEEN (3 - 1) * 5 + 1　　　　--ORACLE中索引是从1开始的

AND 3 * 5; --条件要知道怎么计算

未完待续...

原文：https://www.cnblogs.com/byw-/p/9270729.html