8 Oracle查询

单表查询

(一)简单条件查询

1.精确查询
需求:查询水表编号为 30408 的业主记录

select * from T_OWNERS where watermeter='30408'

2.模糊查询
需求:查询业主名称包含“刘”的业主记录

select * from name like '%刘%'

3.and 运算符
需求:查询业主名称包含“刘”的并且门牌号包含 5 的业主记录

select * from name like '%刘%' and housenumber like '%5%'

4.or 运算符
需求:查询业主名称包含“刘”的或者门牌号包含 5 的业主记录

select * from t_owners where name like '%刘%' or housenumber like '%5%'

5.and 与 or 运算符混合使用
需求:查询业主名称包含“刘”的或者门牌号包含 5 的业主记录，并且地址编号 为 3 的记录。

select * from (name like '%刘%' or houseumber like '%5%') and addressid = 3;

6.范围查询
需求:查询台账记录中用水字数大于等于 10000，并且小于等于 20000 的记录我们可以用>= 和<=来实现，语句

select * from t_account where usenum >= 10000 and usenum <= 20000

我们也可以用 between … and …来实现

select * from t_account where usenum between 10000 and 20000

7.空值查询
需求:查询 T_PRICETABLE 表中 MAXNUM 为空的记录

select * from T_PRICETABLE t where maxnum is null

需求:查询 T_PRICETABLE 表中 MAXNUM 不为空的记录

select * from T_PRICETABLE t where maxnum is not null

(二)去掉重复记录

需求:查询业主表中的地址 ID,不重复显示

select distinct addressid from t_owners

(三)排序查询

1.升序排序
需求:对 T_ACCOUNT 表按使用量进行升序排序

select * from T_ACCOUNT order by usenum

2.降序排序
需求:对 T_ACCOUNT 表按使用量进行降序排序

select * from T_ACCOUNT order by usenum desc

(四)基于伪列的查询

在 Oracle 的表的使用过程中，实际表中还有一些附加的列，称为伪列。伪列就 像表中的列一样，但是在表中并不存储。伪列只能查询，不能进行增删改操作。
接下来学习两个伪列:ROWID 和 ROWNUM。

ROWID

表中的每一行在数据文件中都有一个物理地址，ROWID 伪列返回的就是该行的
物理地址。使用 ROWID 可以快速的定位表中的某一行。ROWID 值可以唯一的 标识表中的一行。由于 ROWID 返回的是该行的物理地址，因此使用 ROWID 可
以显示行是如何存储的。
查询语句:

select rowID, t.* from t_area t

我们可以通过指定 ROWID 来查询记录:

select rowid, t.* from t_area t WHERE rowid = 'AAASOUAAGAAAAF+AAB'

ROWNUM

在查询的结果集中，ROWNUM 为结果集中每一行标识一个行号，第一行返回 1，第二行返回 2，以此类推。通过 ROWNUM 伪列可以限制查询结果集中返回的行数。

select rownum, t.* from t_ownertype t

我们的分页查询需要用到此伪列，在本章第四小节详细讲解。

(五)聚合统计

ORACLE 的聚合统计是通过分组函数来实现的，与 MYSQL 一致。

聚合函数

(1)求和 sum
需求:统计 2012 年所有用户的用水量总和

select sum(usenum) from t_account where year = '2012'

(2)求平均 avg
需求:统计 2012 年所有用水量(字数)的平均值

select avg(usenum) from t_account where year='2012'

(3)求最大值 max
需求:统计 2012 年最高用水量(字数)

select max(usenum) from t_account where year='2012'

(4)求最小值 min
需求:统计 2012 年最低用水量(字数)

select min(usenum) from t_account where year='2012'

(5)统计记录个数 count

select count(*) from t_owners t where ownertypeid=1

分组聚合 Group by

需求:按区域分组统计水费合计数

select areaid, sum(money) from t_account group by areaid

分组后条件查询 having

需求:查询水费合计大于 16900 的区域及水费合计

select 
	areaid, sum(money) 
from 
	t_account 
group by 
	areaid 
having 
	sum(money) > 16900

连接查询

(一)多表内连接查询

(1)需求:查询显示业主编号，业主名称，业主类型名称，如下图:

select 
	o.id 业务编号, o.name 业主名称, ot.name 业主类型 
from 
	t_owners o, t_owners ot 
where
	 o.ownertypeid=ot.id

(2)需求:查询显示业主编号，业主名称、地址和业主类型，如下图
分析:此查询需要三表关联查询。分别是业主表，业主分类表和地址表

select 
	o.id 业主编号, o.name 业主名称, ad.name 地址, ot.name 业主类型
from
	t_owners o, t_ownertype ot, t_address ad
where
	o.ownertypeid = o.id and o.addressid = ad.id

(3)需求:查询显示业主编号、业主名称、地址、所属区域、业主分类，如下
分析:这里需要四个表关联查询，比上边多了一个区域表(T_AREA)

select 
	o.id 业主编号, o.name 业主名称, ad.name 地址, ot.name 业主类型, ar.name 区域
from
	t_owners o, t_ownertype ot, t_address ad, t_area ar
where
	o.ownertypeid = o.id and o.addressid = ad.id and ad.areaid=ar.id

多加一张表，where多加一个and就完了
分析:此查询比上边又多了一个表 T_OPERATOR

select 
	o.id 业主编号, o.name 业主名称, ad.name 地址, ot.name 业主类型, ar.name 区域, op.name 收费员
from
	t_owners o, t_ownertype ot, t_address ad, t_area ar, t_operator op
where
	o.ownertypeid = o.id and o.addressid = ad.id and ad.areaid=ar.id and op.id = ad.operatorid

(二)左外连接查询

需求：查询业主的账务记录，显示业主编号、名称、年、月、金额。如果此业主没有账务记录也要列出姓名
分析：我们要查询这个结果，需要用到t_owners（业主表），t_account（台账表）按照查询结果，业主表为左表，账务表为右表。
按照 SQL1999 标准的语法，查询语句如下:

select 
	ow.id, ow.name, ac.year, ac.month, ac.money
from 
	t_owners ow
left join 
	t_account ac
on
 ow.id = ac.ownerxuuid

其实from里面的才是主表
按照ORACLE的语法，就很简单了

SELECT
	ow.id, ow.name, ac.year, ac.month, ac.money 
FROM
	t_account ac, t_owners ow
WHERE
ow.id = ac.owneruuid(+)

查询结果所有主表都有值，有些副表是空
如果是左外连接，就在右表所在的条件一端填上(+)

(三)右外连接查询

需求:查询业主的账务记录，显示业主编号、名称、年、月、金额。如果账务记录没有对应的业主信息，也要列出记录。如下图:
SQL1999 标准的语句:

SELECT 
	ow.id, ow.name, ac.year, ac.month, ac.money
from
	t_owners ow
right join
	t_account ac
on
	ow.id = ac.owneruuid

其实右连接中，right join后面的才是主表
ORACLE语法：

select
	ow.id, ow.name, ac.year, ac.month, ac.money
from
	t_owners ow, t_account ac
where
	ow.id(+) = ac.owneruuid

使用ORACLE语法，不管是左连接查还是右连接查询，在副表的where条件的一端加上(+)就可以

子查询

(一)where 子句中的子查询

单行子查询：只返回一条记录、单行操作符（= > >= < <= <>）都是单行操作符
需求:查询 2012 年 1 月用水量大于平均值的台账记录

SELECT * from t_account where year = '2012' and month = '01' and usenum > 
(select avg(usenum) from t_account where year='2012' and month='01')

多行子查询：返回了多条记录，多行操作符(in any all)

in：等于列表中的其中一个
any：和子查询返回的任意一个值比较
all：和子查询返回的所有值比较

in 运算符

(1)需求:查询地址编号为 1 、3、4 的业主记录
分析:如果我们用 or 运算符编写，SQL 非常繁琐，所以我们用 in 来进行查询

select * from t_owners where addressid in (1, 3, 4)

(2)需求:查询地址含有“花园”的业主的信息

select * from t_owners where addressid in 
(select id from t_address where name like '%花园%')

(3)需求:查询地址不含有“花园”的业主的信息

select * from t_owners where addressid not in 
(select id from t_address where name like '%花园%')

(二)from 子句中的子查询

from 子句的子查询为多行子查询
需求:查询显示业主编号，业主名称，业主类型名称，条件为业主类型为”居民”，使用子查询实现。

select * 
from 
	(select o.id 业务编号, o.name 业主名称, ot.name 业主类型 from t_owners o, t_ownertype ot where o.ownertypeid=ot.id) 
where
	业主类型 = '居民'

(三)select 子句中的子查询

select 子句的子查询必须为单行子查询
(1)需求:列出业主信息，包括 ID，名称，所属地址。

select id, name, (select name from t_address where id  = addressid) addressname 
from 
	t_owners

(2)需求:列出业主信息，包括 ID，名称，所属地址，所属区域。

select id,name,
( name from t_address where id=addressid )
addressname,
( name from )     where id=areaid ) from
adrename
from t_owners;

分页查询

(一)简单分页

需求:分页查询台账表 T_ACCOUNT，每页 10 条记录
分析:我们在 ORACLE 进行分页查询，需要用到伪列 ROWNUM 和嵌套查询
我们首先显示前 10 条记录，语句如下:

select rownum, t.* from t_account t where rownum <= 10

那么我们显示第 11 条到第 20 条的记录呢?编写语句:

select rownum, t.* from t_account t where rownum > 10 and rownum <= 20

嗯?怎么没有结果?
这是因为 rownum 是在查询语句扫描每条记录时产生的，所以不能使用“大于”符号，只能使用“小于”或“小于等于” ，只用“等于”也不行。
那怎么办呢?我们可以使用子查询来实现

select * from (select rownum r, t.* from t_account t where rownum < 20) where r > 10

(二)基于排序的分页

需求:分页查询台账表 T_ACCOUNT，每页 10 条记录，按使用字数降序排序。
我们查询第 2 页数据，如果基于上边的语句添加排序，语句如下:

select * from 
	(select rownum r, t.* from t_account t where rownum < 20 order by usenum desc) 
where r > 10

你会发现排序后的 R 是乱的。这是因为 ROWNUM 伪列的产生是在表记录扫描时产生的，而排序是后进行的，排序时 R 已经产生了，所以排序后 R 是乱的。
那该如何写呢?
很简单，我们只要再嵌套一层循环(一共三层)，让结果先排序，然后对排序后的结果再产生 R，这样就不会乱了。

select * from      
	(select rownum r, t.* from
		(select * from t_account order by usenum desc) t
			where rownum <= 20)
where r>10