[技术笔记] 【郑州校区】Oracle-day02 中

本帖最后由我是楠楠于 2018-5-25 14:42 编辑

【郑州校区】Oracle-day02 中

二、连接查询

（一）多表内连接查询

（1）需求：查询显示业主编号，业主名称，业主类型名称，如下图：

查询语句：

[AppleScript] 纯文本查看 复制代码

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

（2）需求：查询显示业主编号，业主名称、地址和业主类型，如下图

分析：此查询需要三表关联查询。分别是业主表，业主分类表和地址表

语句：

[AppleScript] 纯文本查看 复制代码

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

（3）需求：查询显示业主编号、业主名称、地址、所属区域、业主分类，如下图：

分析：这里需要四个表关联查询，比上边多了一个区域表（T_AREA）

查询语句：

[AppleScript] 纯文本查看 复制代码

select o.id 业主编号,o.name 业主名称,ar.name 区域, ad.name 地
址, ot.name 业主类型

[AppleScript] 纯文本查看 复制代码

from T_OWNERS o ,T_OWNERTYPE ot,T_ADDRESS ad,T_AREA ar
where o.ownertypeid=ot.id and o.addressid=ad.id
ad.areaid=ar.id

and

（4）需求：查询显示业主编号、业主名称、地址、所属区域、收费员、业主分类，如下图：

分析：此查询比上边又多了一个表 T_OPERATOR

语句：

[AppleScript] 纯文本查看 复制代码

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

（二）左外连接查询

需求：查询业主的账务记录，显示业主编号、名称、年、月、金额。如果此业主

没有账务记录也要列出姓名。

分析：我们要查询这个结果，需要用到 T_OWNERS（业主表），T_ACCOUNT

（台账表）按照查询结果，业主表为左表、账务表为右表。

按照 SQL1999 标准的语法，查询语句如下：

[AppleScript] 纯文本查看 复制代码

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 提供的语法，就很简单了：

[AppleScript] 纯文本查看 复制代码

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

如果是左外连接，就在右表所在的条件一端填上（+）

（三）右外连接查询

需求：查询业主的账务记录，显示业主编号、名称、年、月、金额。如果账务记

录没有对应的业主信息，也要列出记录。如下图：

SQL1999 标准的语句

[AppleScript] 纯文本查看 复制代码

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. 单行子查询

l 只返回一条记录

l 单行操作符

需求：查询 2012 年 1 月用水量大于平均值的台账记录

语句：

[AppleScript] 纯文本查看 复制代码

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

[AppleScript] 纯文本查看 复制代码

month='01' )

查询结果：

平均值为：

2. 多行子查询

l 返回了多条记录

l 多行操作符

in 运算符

（1）需求：查询地址编号为 1 、3、4 的业主记录

分析：如果我们用 or 运算符编写，SQL 非常繁琐，所以我们用 in 来进行查询

语句如下：

[AppleScript] 纯文本查看 复制代码

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

查询结果如下：

（2）需求：查询地址含有“花园”的业主的信息

语句：

[AppleScript] 纯文本查看 复制代码

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

查询结果：

（3）需求：查询地址不含有“花园”的业主的信息语句：

[AppleScript] 纯文本查看 复制代码

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

查询结果：

（二）from 子句中的子查询

from 子句的子查询为多行子查询

需求：查询显示业主编号，业主名称，业主类型名称，条件为业主类型为”居民”，

使用子查询实现。

语句：

[AppleScript] 纯文本查看 复制代码

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，名称，所属地址。

语句：

[AppleScript] 纯文本查看 复制代码

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

查询结果如下：

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

语句:

[AppleScript] 纯文本查看 复制代码

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;

查询结果如下：

四、分页查询

（一）简单分页

需求：分页查询台账表 T_ACCOUNT，每页 10 条记录

分析：我们在 ORACLE 进行分页查询，需要用到伪列 ROWNUM 和嵌套查询

我们首先显示前 10 条记录，语句如下：

[AppleScript] 纯文本查看 复制代码

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

显示结果如下：

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

[AppleScript] 纯文本查看 复制代码

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

查询结果：

嗯？怎么没有结果？

这是因为 rownum 是在查询语句扫描每条记录时产生的，所以不能使用“大于”

符号，只能使用“小于”或“小于等于” ，只用“等于”也不行。

那怎么办呢？我们可以使用子查询来实现

[AppleScript] 纯文本查看 复制代码

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

查询结果如下：

（二）基于排序的分页

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

我们查询第 2 页数据，如果基于上边的语句添加排序，语句如下：

[AppleScript] 纯文本查看 复制代码

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

查询结果如下：

经过验证，我们看到第 2 页的结果应该是下列记录

所以推断刚才的语句是错误的！那为什么是错误的呢？

我们可以先单独执行嵌套查询里面的那句话

[AppleScript] 纯文本查看 复制代码

select rownum r,t.* from T_ACCOUNT t
where rownum<=20 order by usenum desc

你会看到查询结果如下：

你会发现排序后的 R 是乱的。这是因为 ROWNUM 伪列的产生是在表记录扫描

是产生的，而排序是后进行的，排序时 R 已经产生了，所以排序后 R 是乱的。

那该如何写呢？

很简单，我们只要再嵌套一层循环（一共三层），让结果先排序，然后对排序后

的结果再产生 R，这样就不会乱了。

语句如下：

[AppleScript] 纯文本查看 复制代码

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

结果如下：

更多

【郑州校区】Oracle-day01 上

【郑州校区】Oracle-day01 中

【郑州校区】Oracle-day01 下

【郑州校区】Oracle-day02 上传智播客·黑马程序员郑州校区地址
河南省郑州市高新区长椿路11号大学科技园（西区）东门8号楼三层
联系电话0371-56061160 / 61/62

来校路线地铁一号线梧桐街站A口出

帐号		自动登录	找回密码
密码			加入黑马

[技术笔记] 【郑州校区】Oracle-day02 中

0 个回复