黑马程序员技术交流社区

标题: 【郑州校区】JavaEE之Oracle-day02（上） [打印本页]

作者: 我是色色 时间: 2018-1-3 14:27
标题: 【郑州校区】JavaEE之Oracle-day02（上）
本帖最后由我是色色于 2018-1-3 14:29 编辑

一、单表查询

（一）简单条件查询

1.精确查询

需求：查询水表编号为 30408 的业主记录

查询语句：

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

select * from T_OWNERS where watermeter='30408'

查询结果：

2.模糊查询

需求：查询业主名称包含“刘”的业主记录

查询语句：

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

select * from t_owners where name like '%刘%'

查询结果：

3. and 运算符

需求：查询业主名称包含“刘”的并且门牌号包含 5 的业主记录

查询语句：

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

select * from t_owners where name like '%刘%' and housenumber

like '%5%'

查询结果：

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wpsC1F7.tmp.png

4. or 运算符

需求：查询业主名称包含“刘”的或者门牌号包含 5 的业主记录

查询语句：

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

select * from t_owners

where name like '%刘%' or housenumber like '%5%'

查询结果：

5. and 与 or 运算符混合使用

需求：查询业主名称包含“刘”的或者门牌号包含 5 的业主记录，并且地址编号

为 3 的记录。

语句：

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

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

like '%5%') and addressid=3

查询结果：

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

6. 范围查询

需求：查询台账记录中用水字数大于等于 10000，并且小于等于 20000 的记录

我们可以用>= 和<=来实现，语句

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

select * from T_ACCOUNT

where usenum>=10000 and usenum<=20000

我们也可以用 between

.. and

..来实现

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

select * from T_ACCOUNT

where usenum between 10000 and 20000

7. 空值查询

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

语句：

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

select * from T_PRICETABLE t where maxnum is null

查询结果：

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

语句：

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

select * from T_PRICETABLE t where maxnum is not null

查询结果：

（二）去掉重复记录

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

语句：

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

select distinct addressid from T_OWNERS

（三）排序查询

1.升序排序

需求：对 T_ACCOUNT 表按使用量进行升序排序

语句：

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

select * from T_ACCOUNT order by usenum

查询结果：

2.降序排序

需求：对 T_ACCOUNT 表按使用量进行降序排序

语句：

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

select * from T_ACCOUNT order by usenum desc

查询结果：

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wpsAAF2.tmp.pngfile:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wpsAAF3.tmp.pngfile:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wpsAB03.tmp.png

（四）基于伪列的查询

在 Oracle 的表的使用过程中，实际表中还有一些附加的列，称为伪列。伪列就

像表中的列一样，但是在表中并不存储。伪列只能查询，不能进行增删改操作。

接下来学习两个伪列：ROWID 和 ROWNUM。

ROWID

表中的每一行在数据文件中都有一个物理地址，ROWID 伪列返回的就是该行的

物理地址。使用 ROWID 可以快速的定位表中的某一行。ROWID 值可以唯一的

标识表中的一行。由于 ROWID 返回的是该行的物理地址，因此使用 ROWID 可

以显示行是如何存储的。

查询语句：

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

select rowID,t.* from T_AREA t

查询结果如下：

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

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

select rowID,t.*

from T_AREA t

where ROWID='AAAM1uAAGAAAAD8AAC';

查询结果如下：

2.ROWNUM

在查询的结果集中，ROWNUM 为结果集中每一行标识一个行号，第一行返回 1，

第二行返回 2，以此类推。通过 ROWNUM 伪列可以限制查询结果集中返回的行

数。

查询语句：

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

select rownum,t.* from T_OWNERTYPE t

查询结果如下：

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

（五）聚合统计

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

1. 聚合函数

（1）求和 sum

需求：统计 2012 年所有用户的用水量总和

查询结果如下：

（2）求平均 avg

需求：统计 2012 年所有用水量（字数）的平均值

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

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

查询结果如下：

（3）求最大值 max

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

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

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wps9445.tmp.pngfile:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wps9446.tmp.pngfile:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wps9447.tmp.pngfile:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wps9448.tmp.pngfile:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wps9449.tmp.pngfile:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wps944A.tmp.pngfile:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wps944B.tmp.pngfile:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wps945C.tmp.png

需求：统计 2012 年最高用水量（字数）

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

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

查询结果如下：

（4）求最小值 min

需求：统计 2012 年最低用水量（字数）

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

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

查询结果如下：

（5）统计记录个数 count

需求：统计业主类型 ID 为 1 的业主数量

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

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

查询结果如下：

2. 分组聚合 Group by

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

语句：

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

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

查询结果：

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wps945D.tmp.pngfile:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wps945E.tmp.pngfile:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wps945F.tmp.pngfile:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wps9460.tmp.pngfile:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wps9461.tmp.pngfile:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wps9462.tmp.pngfile:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wps9463.tmp.pngfile:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wps9464.tmp.pngfile:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wps9465.tmp.png

3. 分组后条件查询 having

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

语句：

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

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

having sum(money)>169000

查询结果：

二、连接查询

（一）多表内连接查询

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

查询语句：

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

select o.id 业主编号,o.name 业主名称,ot.name 业主类型

from T_OWNERS o,T_OWNERTYPE ot

where o.ownertypeid=ot.id

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

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wps9475.tmp.pngfile:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wps9476.tmp.pngfile:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wps9477.tmp.pngfile:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wps9478.tmp.pngfile:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wps9479.tmp.pngfile:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wps947A.tmp.pngfile:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wps947B.tmp.png

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

语句：

[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

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wps947D.tmp.pngfile:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wps948E.tmp.png

（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

（二）左外连接查询

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

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

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wps9493.tmp.png

分析：我们要查询这个结果，需要用到 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(+)

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

（三）右外连接查询

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

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

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wps94A5.tmp.png

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 的语法

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

select ow.id,ow.name,ac.year,ac.month,ac.money from

T_OWNERS ow , T_ACCOUNT ac

where ow.id(+) =ac.owneruuid

作者: 我是色色 时间: 2018-1-3 14:29

作者: 我是楠楠 时间: 2018-1-3 15:05
楼主棒棒哒

欢迎光临黑马程序员技术交流社区 (http://bbs.itheima.com/)

黑马程序员IT技术论坛 X3.2