十二、Oracle 查询

• 一、Oracle 操作集
• 二、Oracle 数据类型
• 三、Oracle 函数
• 四、Oracle 视图
• 五、Oracle 序列
• 六、Oracle 同义词
• 七、Oracle 索引
• 八、Oracle PL/SQL
• 九、Oracle 存储函数
• 十、Oracle 存储过程
• 十一、Oracle 触发器
• 十二、Oracle 查询

---

一、单表查询

---

1. 精确查询

-- 查询水表编号为`30408`的业主记录
select * from T_OWNERS where watermeter = '30408';

---

2. 模糊查询

-- 查询业主名称包含`刘`的业主记录
select * from t_owners where name like '%刘%';

---

3. AND 与 OR 运算符

• and 运算符。

-- 查询业主名称包含`刘`的，并且门牌号包含`5`的业主记录
select * from t_owners where name like '%刘%' and housenumber like '%5%';

---

• or 运算符。

-- 查询业主名称包含`刘`的，或者门牌号包含`5`的业主记录
select * from t_owners where name like '%刘%' or housenumber like '%5%'; 

---

• 因为 and 的优先级比 or 大，所以需要用 () 来改变优先级。

-- 查询业主名称包含`刘`的，或者门牌号包含`5`的业主记录，并且地址编号为`3`的记录
select * from t_owners where (name like '%刘%' or housenumber like '%5%') and addressid = 3; 

---

4. 范围查询

-- 查询台账记录中用水字数大于等于`10000`，并且小于等于`20000`的记录
select * from T_ACCOUNT where usenum >= 10000 and usenum <= 20000;

select * from T_ACCOUNT where usenum between 10000 and 20000;

---

5. 空值查询

-- 查询`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;

---

6. 去掉重复记录

-- 查询业主表中的地址ID，不重复显示
select distinct addressid from T_OWNERS;

-- 查询业主表中的地址ID和业主类型，不重复显示（两列只要一边不重复）
select distinct addressid, ownertypeid from T_OWNERS;

---

7. 排序查询

-- 对`T_ACCOUNT`表按使用量，进行升序排序
select * from T_ACCOUNT order by usenum asc; 

-- 对`T_ACCOUNT`表按使用量，进行降序排序
select * from T_ACCOUNT order by usenum desc;

---

8. ROWID 和 ROWNUM 伪列查询

---

• Oracle 两个伪列：ROWID 和 ROWNUM。

1. 在 Oracle 表的使用过程中，实际表中还有一些附加的列，称为 伪列。
2. 伪列 就像表中的列一样，但是在表中并不存储。
3. 伪列 只能查询，不能进行增删改操作。

---

• ROWID 伪列。

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

select rowID, t.* from T_AREA t;

-- 通过指定`ROWID`来查询记录
select rowID, t.* from T_AREA t where ROWID = 'AAAM1uAAGAAAAD8AAC'; 

---

• ROWNUM 伪列。

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

select rownum, t.* from T_OWNERTYPE t;

---

9. 聚合统计

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

---

• count 统计记录个数。

-- 统计业主类型ID为`1`的业主数量   
select count(*) from T_OWNERS t where ownertypeid = 1; 

---

• sum 求和。

-- 统计`2012`年所有用户的用水量总和
select sum(usenum) from t_account where year = '2012';

---

• avg 求平均。

-- 统计`2012`年所有用水量（字数）的平均值    
select avg(usenum) from T_ACCOUNT where year = '2012';

---

• max 求最大值。

-- 统计`2012`年最高用水量（字数）
select max(usenum) from T_ACCOUNT where year = '2012';

---

• min 求最小值。

-- 统计`2012`年最低用水量（字数） 
select min(usenum) from T_ACCOUNT where year = '2012';

---

10. 分组聚合

• 注意：select 后一定是分组聚合（GROUP BY）的 条件 或者是 聚合函数。

-- 按区域分组，统计水费合计数
select areaid, sum(money) 
from t_account 
group by areaid;

---

11. 分组后条件查询

• having 过滤。

-- 查询水费合计大于`16900`的区域及水费合计
select areaid, sum(money) 
from t_account 
group by areaid 
having sum(money) > 169000; 

---

二、连接查询

---

1. 多表内连接查询

• 需求：查询显示业主编号，业主名称，业主类型名称。

select o.id '业主编号', o.name '业主名称', ot.name '业主类型'
from T_OWNERS o, T_OWNERTYPE ot
where o.ownertypeid = ot.id;

---

• 需求：查询显示业主编号，业主名称、地址和业主类型。
• 分析：此查询需要三表关联查询，分别是业主表，业主分类表和地址表。

select o.id '业主编号', o.name '业主名称', ad.name '地址', ot.name '业主类型'
from T_OWNERS o, T_OWNERTYPE ot, T_ADDRESS ad
where o.ownertypeid = ot.id and o.addressid = ad.id;  

---

• 需求：查询显示业主编号、业主名称、地址、所属区域、业主分类。
• 分析：这里需要四个表关联查询，比上边多了一个 T_AREA（区域表）。

select o.id '业主编号', o.name '业主名称', ar.name '区域', ad.name '地址', ot.name '业主类型'
from T_OWNERS o, T_OWNERTYPE ot, T_ADDRESS ad, T_AREA ar
where o.ownertypeid = ot.id and o.addressid = ad.id and ad.areaid = ar.id;

---

• 需求：查询显示业主编号、业主名称、地址、所属区域、收费员、业主分类。
• 分析：此查询比上边又多了一个表 T_OPERATOR（操作员）。

select ow.id '业主编号', ow.name '业主名称', ad.name '地址', ar.name '所属区域', op.name '收费员', ot.name '业主类型'
from T_OWNERS ow, T_OWNERTYPE ot, T_ADDRESS ad, T_AREA ar, T_OPERATOR op
where ow.ownertypeid = ot.id and ow.addressid = ad.id and ad.areaid = ar.id and ad.operatorid = op.id;

---

2. 左外连接查询

• 需求：查询业主的账务记录，显示业主编号、名称、年、月、金额。
  如果此业主没有账务记录也要列出姓名。
• 分析：我们要查询这个结果，需要用到 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.owneruuid

• Oracle 语法。

-- 按照`Oracle`提供的语法，查询语句
SELECT ow.id, ow.name, ac.year, ac.month, ac.money 
FROM T_OWNERS ow, T_ACCOUNT ac
WHERE ow.id = ac.owneruuid(+)    
-- 如果是左外连接，就在右表所在的条件一端填上(+)   

---

3. 右外连接查询

• 需求：查询业主的账务记录，显示业主编号、名称、年、月、金额。
  如果账务记录没有对应的业主信息，也要列出记录。

-- 按照`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

-- 按照`ORACLE`的语法
select ow.id, ow.name, ac.year, ac.month, ac.money 
from T_OWNERS ow, T_ACCOUNT ac
where ow.id(+) = ac.owneruuid  

---

三、子查询

• where 子句中的子查询。

---

1. 单行子查询

运算符	说明
=	Equal to 等于
>	Greater than 大于
>=	Greater than or equal to 大于或等于
<	Less than 少于
<=	Less than or equal to 小于或等于
<>	Not equal to 不等于

---

• 需求：查询 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' );

---

2. 多行子查询

运算符	说明
IN	等于列表中的任何一个
ANY	和子查询返回的任意一个值比较
ALL	和子查询返回的所有值比较

---

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

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

---

• 需求：查询地址含有 花园 的业主的信息。

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

---

• 需求：查询地址不含有 花园 的业主的信息。

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

-- 使用量必须大于子查询所有值
SELECT USENUM FROM T_ACCOUNT
WHERE USENUM > ALL(10000, 20000);

-- 使用量大于子查询任意值
SELECT USENUM FROM T_ACCOUNT
WHERE USENUM > ANY(10000, 20000);
-- `SOME`和`ANY`大致相同
SELECT USENUM FROM T_ACCOUNT
WHERE USENUM > SOME(10000, 20000);

---

3. from 子句中的子查询

• from 子句的子查询为 多行子查询。
• 需求：查询显示业主编号，业主名称，业主类型名称，条件为业主类型为 居民。

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

---

4. select 子句中的子查询

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

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

---

• 需求：列出业主信息，包括 ID，名称，所属地址，所属区域。

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

---

四、分页查询

---

1. 简单分页

• 需求：分页查询 T_ACCOUNT（台账表），每页 10 条记录。
• 分析：我们在 Oracle 进行分页查询，需要用到伪列 ROWNUM 和 嵌套查询。

-- 1. 首先显示前`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; 

• 上面第2页查询没有结果？

1. 这是因为 rownum 是在查询语句扫描每条记录时产生的。
2. 所以不能使用 大于 符号，只能使用 小于 或 小于等于，只用 等于 也不行。

• 子查询 实现分页。

select * from ( 
	select rownum r, t.* from T_ACCOUNT t where rownum <= 20 
) where r > 10;

---

2. 基于排序的分页

• 需求：分页查询 T_ACCOUNT（台账表），每页 10 条记录，按使用字数降序排序。

-- 如果基于排序的分页
select * from ( 
	select rownum r, t.* from T_ACCOUNT t where rownum <= 20 order by usenum desc 
) where r > 10;

-- 先单独执行嵌套查询里面的查询语句
select rownum r, t.* from T_ACCOUNT t where rownum <= 20 order by usenum desc;

• 发现排序后的 rownum 是乱的。

1. 这是因为 rownum 伪列的产生是在表记录扫描是产生的，而排序是后进行的。
2. 排序时 rownum 已经产生了，所以排序后 rownum 是乱的。

• 如何改写呢？

1. 只要再嵌套一层循环（一共三层）。
2. 让结果先排序，然后对排序后的结果再产生 rownum，这样就不会乱了。

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

---