A股上市公司传智教育(股票代码 003032)旗下技术交流社区北京昌平校区

 找回密码
 加入黑马

QQ登录

只需一步,快速开始

本帖最后由 我是楠楠 于 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-day02 上
传智播客·黑马程序员郑州校区地址
河南省郑州市高新区长椿路11号大学科技园(西区)东门8号楼三层
联系电话0371-56061160 / 61/62
来校路线地铁一号线梧桐街站A口出

0 个回复

您需要登录后才可以回帖 登录 | 加入黑马