这篇博客的话我们主要是想介绍 Oracle 数据库的查询，主要是各种查询方式和函数的学习，掌握了这些之后，我们就能熟练地进行 Oracle 的查询，完成相关的需求任务，学会这些知识，也能让我们对 Oracle 的使用有更深入的把握。
1.单表查询
1.1 简单条件查询
我们先来学习简单的条件查询，主要是包括精确查询，模糊查询，and 运算符和 or 运算符的使用，范围查询以及空值查询，我们直接通过 SQL 语句来进行熟悉。
1.精确查询
select * from t_owners where watermeter='30408'
2.模糊查询
select * from t_owners where name like '%刘%'
3.and运算符
select * from t_owners where name like '%刘%' and housenumber like '%5%'
4.or运算符
select * from t_owners where name like '%刘%' or housenumber like '%5%'
5.and与or运算符混合使用
select * from t_owners where (name like '%刘%' or housenumber like '%5%') and addressid=3
这里需要说明的是，当我们记不清 and 运算符和 or 运算符两者之间的运算优先级的时候，我们就可以使用小括号来进行区分，这样也便于我们理解。
6.范围查询
select * from t_account where usenum>=10000 and usenum<=20000
select * from t_account where usenum between 10000 and 20000
7.空值查询
select * from t_pricetable t where maxnum is null
select * from t_pricetable t where maxnum is not null
1.2 去掉重复记录
当我们查询出来数据，却发现根据业务需求来说，某些记录是重复的，那这时我们就可以使用 distinct 关键字去除掉重复记录。
select distinct addressid from t_owners
上面这是根据一个字段来进行去重，那如果要根据多个字段进行去重呢？其实只需要在 distinct 关键字后面把多个字段加上就可以了，字段间以逗号分隔。
1.3 排序查询
查询出来的数据我们也可以根据某些字段进行排序操作，升序和降序都可以。
select * from t_account order by usenum asc
上面这条语句便是以 usenum 字段进行排序，asc 则是指定数据按升序排列，降序排列则是如下：
select * from t_account order by usenum desc
这里我们使用 desc 关键字指定数据降序排列。
1.4 基于伪列的查询
在 Oracle 的数据表中，还有一些附加的列，这些列便是伪列，伪列和数据表中其它的列是一样的，但是伪列在数据表中并不存储，只能查询，也不能进行增删改的操作，我们下面将会学习 rowid 和 rownum 这两个伪列。
1.rowid
数据表中的每一行数据在数据文件当中都有一个物理地址，rowid 伪列返回的便是该行数据的物理地址，我们也可以通过 rowid 快速地查询得到数据表中的某一行记录。
select rowid,t.* from t_area t
select rowid,t.* from t_area t where rowid='AAAM1uAAGAAAAD8AAC'
2.rownum
rownum 伪列则为查询结果集中每一行标识的行号，第一列的行号为 1，第二列的行号为 2，以此类推，我们也可以通过 rownum 伪列限制查询结果集返回的行数，以此来实现分页查询。
select rownum,t.* from t_ownertype t
1.5 聚合统计
Oracle 中的聚合统计是通过分组函数来实现的，下面我们具体来看。
1.5.1 聚合函数
1.求和sum
select sum(usenum) from t_account where year='2012'
2.求平均
select avg(usenum) from t_account where year='2012'
3.求最大值max
select max(usenum) from t_account where year='2012'
4.求最小值min
select min(usenum) from t_account where year='2012'
5.统计记录个数count
select count(*) from t_owners t where ownertypeid=1
1.5.2 分组聚合group by
分组聚合则是按照某些字段进行分组，然后再统计该分组中的数据情况，主要是通过 group by 字段来实现。
select areaid,sum(money) from t_account group by areaid
需要注意的是，进行分组聚合时，select 后面的查询字段只能是分组字段或者是聚合函数，不能是其它字段。
1.5.3 分组后条件查询having
having 关键字主要是对分组后的数据进行条件过滤，而 where 关键字则是在分组前对数据进行条件过滤。
select areaid,sum(money) from t_account group by areaid having sum(money)>169000
2.连接查询
有时候需求需要用到多个表中的数据，我们就需要用到多表连接查询，而连接查询又分为内连接与外连接，下面我们具体来看。
2.1 多表内连接查询
我们以两张表的内连接查询作说明，更多张表的情况类似，两张表做内连接查询，相当于是对两张表的数据做了一个笛卡尔积，第一张表有 10 条数据，第二张表有 5 条数据，直接内连接查询则会产生 50 条数据记录，具体使用如下：
select * from t_owners o,t_ownertype ot
通常情况下，两张表连接都会根据具体业务进行连接，也就是根据两张表中的具体字段进行匹配连接，那这时候只需要直接在 where 后面加匹配条件就好了，其实还是相当于先做笛卡尔积，然后根据条件筛选出符合条件的记录。
select * from t_owners o,t_ownertype ot where o.ownertypeid=ot.id
2.2 左外连接查询
在介绍外连接查询之前，我们先说明一下为什么需要外连接？这肯定是因为内连接有满足不了我们需要的场景了，比如说我们需要统计客户的账务信息，第一张表是客户信息表，第二张表是客户账务表，那我们可以对这两张表进行内连接查询，但是如果这时候要求，就算客户现在还没有账务信息，也需要在查询出来的结果中有客户信息，只是客户账务信息的那几列是空的，这时候内连接就满足不了需求了，因为两张表根据相关字段进行内连接查询时，如果第二张表中和第一张表根据相关字段没有对应的记录，那第一张表中的记录也不会查询出来了，也就是被过滤掉了，这种情况我们就可以使用左外连接查询了，也就是以左边的表为主表，然后右边的表根据相关字段和左边的表进行连接，当左边的表中有记录而右边的表没有对应记录的话，这时候也会显示左边表中的记录数据，而右边表对应的栏位数据显示为空。
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
上面的 SQL 语句是 SQL1999 标准的语法，也就是说除了在 Oracle 数据库中可以执行外，其它关系型数据库也是可以执行的，比如 MySQL 数据库，上面的 SQL 用 left join 关键字进行两张表的关联，该关键字左边的表就是主表了，也就意味着即使右边的表没有相应的记录与之对应，左边表的数据也会显示出来，on 关键字则是两张表关联的具体条件，也就是根据哪些字段进行关联。除了 SQL1999 标准的语法之外，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 提供的实现和内连接的写法很类似，只是在连接条件的部分有些不同，如果是左外连接，则需要在右边表的连接条件字段上面加 (+)。
2.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
Oracle 数据库所提供的右外连接查询实现和内连接查询的写法也很像，区别就在于当为右外连接查询时，则需要在左边表的连接查询条件字段上面加 (+)。
3.子查询
子查询主要是一个 SQL 语句中包含多个查询语句，一个查询语句的结果是另一个查询语句的条件，这样就形成了子查询。
3.1 where子句中的子查询
where 子句中的子查询其实就是 where 关键字的查询条件不是直接指定的值，而是靠另外一个查询语句查询出来的，因为查询语句查询出来的值可能是单个，也可能是多个，这样的话这种类型的子查询也就分为两类了，第一类便是单行子查询，也就是 where 关键后面的查询语句只返回一个值：
select * from t_account where year='2012' and month='01' and usenum > (select avg(usenum) from t_account where year='2012' and month='01')
第二类便是多行子查询了，where 关键字后面的查询语句可能会返回多个值。
select * from t_owners where addressid in ( select id from t_address where name like '%花园%' )
3.2 from子句中的子查询
from 子句中的子查询则是表示 from 关键字后面是一个子查询，之前都是一张数据表的，这里的话相当于是我们在一个查询结果中再进行查询，from 子句中的子查询一般为多行子查询。
select * from (select o.id 业主编号,o.name 业主名称,ot.name 业主类型 from t_owners o,t_ownertype ot where o.ownertypeid=ot.id) where 业主类型='居民'
3.3 select子句中的子查询
select 子句中的子查询表示的是 select 关键字后面列出的不是一个一个具体的字段，而是一个子查询，当然，子查询其实也是查询其它表获取的字段值，select 子句中的子查询只能是单行子查询，因为如果返回多行的话那就没法显示了。
select id,name, (select name from t_address where id=addressid) addressname from t_owners
当需求的业务比较复杂时，写子查询也许需要嵌套很多层，显得比较复杂，其实可以从最简单的写起，然后将它们拼接起来，形成最后我们需要的结果，这样就可以达到我们的要求了。
4.分页查询
我们再来看如何在 Oracle 数据库中实现分页查询，MySQL 数据库是使用 limit 关键字来实现的，但是 Oracle 不是这样实现的，我们具体来看。
4.1 简单分页
先看简单分页，就是一张表数据量太大，我们不可能一次性全部查出来，因此选择每次查询 10 行记录，第一次是 1到 10 条记录，第二次则是 11 到第 20 条记录，以此类推，我们可以使用 rownum 伪列来实现，比如第 1 到第 10 条记录，我们可以通过如下 SQL 查询：
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
这样的话就可以了吧，但是我们执行 SQL 的时候却发现并没有查询出来记录，这是为什么呢？其实这里就需要说明下 rownum 这个伪列是在什么时候产生的了，rownum 伪列是在我们查询数据表，扫描表中每条记录时产生的，每扫描一条记录则产生对应记录的 rownum，那为什么我们上面的 SQL 没有查询出来结果呢？就是因为上面 SQL 中的查询条件中 rownum 使用了大于符号来判断，rownum 是在扫描每条记录时产生的，后续的记录还没扫描到，rownum 也还没产生，这时候使用 rownum 大于来查询当然就是查不到记录了，因此需要记住使用 rownum 来判断时只能使用小于或者小于等于，这样才能正常地查询出来记录。那回到最开始的问题，我们应该如何实现分页呢？其实可以借助我们上面已经学过的子查询来完成，就是在查询 SQL 的 from 部分不是直接查表，而是查询已经生成了 rownum 的记录结果，这样的话就可以实现我们想要的目标了。
select * from (select rownum r,t.* from t_account t where rownum<=20) where r>10
4.2 基于排序的分页
上面我们介绍了简单分页，下面我们再看基于字段排序的分页，基于上面简单分页的结果，如果我们要实现字段排序后的分页，那就可以这样：
select * from (select rownum r,t.* from t_account t where rownum<=20 order by usenum desc) where r>10
先根据 usenum 字段进行排序，然后得到对应的 rownum 字段，最后根据子查询来实现分页，就可以得到结果了，但事实真是这样的吗？其实不是的，上面我们已经说过，rownum 是在扫描表时产生的，因此下面这条 SQL 会先得到 rownum，然后再根据 usenum 字段进行排序。
select rownum r,t.* from t_account t where rownum<=20 order by usenum desc
这样的话，这时候我们得到的 rownum 其实已经是乱序的了，因为是先获取的 rownum，然后再排的序，那如果想得到正确的结果应该怎么办呢？就是应该先根据 usenum 字段进行排序，然后获取得到 rownum 字段，最后再使用子查询进行分页，这样就可以实现我们的要求了。
select * from (select rownum r,t.* from (select * from t_account order by usenum desc) t where rownum<=20 ) where r>10
5.单行函数
Oracle 还提供了许多函数供我们使用，像字符函数、数值函数、日期函数和转换函数这样的，通过这些函数我们可以实现更多复杂的功能，当然这些函数也可以在 Oracle 提供的文档中进行查询，我们下面挑选几个进行说明。
5.1 字符函数
5.1.1 求字符串长度函数length
select length('ABCD') from dual;
这里返回的结果就是 4 了，这个函数的作用就是返回字符串的长度。
5.1.2 求字符串的子串substr
select substr('ABCD',2,2) from dual;
这个函数其实是用来截取字符串的，该函数有 3 个参数，第 1 个为原字符串，第 2 个参数为从哪一位开始截取，这里以 1 开始计数，第 3 个参数则是截取字符串的长度，因此上面的 SQL 返回的结果应该是字符串 BC。
5.1.3 字符串拼接concat
select concat('ABC','D') from dual;
上面 SQL 的作用便是将两个字符串进行拼接，得到的结果就是 ABCD 这个字符串了，其实我们也可以使用 || 符号来做，那 SQL 应该为：
select 'ABC'||'D' from dual;
结果是一样的，|| 符号可以拼接多个字符串，而 concat 函数只能拼接两个字符串。
5.2 数值函数
Oracle 中同样提供了许多数学函数以便我们使用，下面我们也列举一些进行说明。
5.2.1 四舍五入函数round
select round(100.456) from dual
select round(100.567) from dual
上面的两个 SQL 返回的结果分别为 100 和 101，这样的话就可以看出默认情况下该函数是进行的整数的四舍五入操作，那如果我们想进行小数位的四舍五入操作呢？比如保留两位小数，那我们就可以在函数中再增加一个参数，使用如下：
select round(100.456,2) from dual
select round(100.567,2) from dual
这时候我们得到的结果就是 100.46 和 100.57 了。
5.2.2 截取函数trunc
select trunc(100.567) from dual
上面 SQL 返回的结果为 100，也就是直接截取整数位，那如果想保留小数呢？其实和上面 round 函数类似，也可以增加一个参数，指定保留小数位的位数。
select trunc(100.567,2) from dual
这时候返回的结果就是 100.56 了。
5.2.3 取模函数mod
select mod(10,3) from dual
这便是我们数学上面的取模运算了，很显然的是返回 1 这个结果了。
5.3 日期函数
Oracle 中同样为我们提供了很多的日期函数供我们使用，下面我们选取一些进行说明。
5.3.1 加月函数add_months
select add_months(sysdate,2) from dual
首先需要说明的是 sysdate 表示的是当前时间，这里为了方便说明，我们暂定当前时间为 2018-06-04 16:09:23，那上面 SQL 返回的结果应该是什么呢？使用 add_months 函数加 2 个月，也就是当前时间往后推两个月，因此我们得到的应该是 `2018-08-04 16:09:23’。
5.3.2 求所在月最后一天last_day
select last_day(sysdate) from dual
上面的 SQL 便是返回当前时间所在月的最后一天的时间了。
5.3.3 日期截取trunc
最后我们要介绍的这个日期函数为日期截取函数 trunc，为了方便进行说明，我们还是暂定当前时间为 2018-06-04 16:09:23，然后再讲解下面的 SQL。
select trunc(sysdate) from dual
上面的 SQL 便是对当前日期进行截取，默认是对日进行截取，那就是表示日以下的时间单位全部置为最小值，那得到的值就应该是 2018-06-04 00:00:00，那除了对日进行截取，还能不能对其它单位进行截取呢？当然是可以的，比如对年进行截取。
select trunc(sysdate,'yyyy') from dual
对年进行截取，那就是表示年以下单位都设置为最小值，得到的结果应该为 2018-01-01。同理按月进行截取呢？
select trunc(sysdate,'mm') from dual
同理对月以下单位都设置为最小值，得到的结果为 2018-06-01。
5.4 转换函数
转换函数主要是各种数据类型之间的转换，比如数值类型转换为字符串类型，日期类型数据转换为字符串类型等，下面我们具体说明几种。
5.4.1 数字转字符串to_char
select to_char(1024) from dual
上面的 SQL 便是将数字 1024 转换为字符串 1024。
5.4.2 日期转字符串to_char
select to_char(sysdate,'yyyy-mm-dd') from dual
上面的 SQL 则是表示将当前时间以指定格式返回，我们这时候就能够得到当天的日期了，下面的 SQL 则是可以得到日期加时间的格式：
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual
5.4.3 字符串转日期to_date
select to_date('2017-01-01','yyyy-mm-dd') from dual
上面的 SQL 则是将字符串转换为日期，然后返回，当然需要注意的一点就是，字符串和给定的日期转换格式必须是匹配的，这样才能转换成功。
5.4.4 字符串转数字to_number
select to_number('100') from dual
上面的 SQL 便是将字符串 100 转换成了数字 100。
5.5 其它函数
下面我们再来看看 Oracle 为我们提供的一些其它函数，比如空值处理函数和条件取值函数，下面具体来看。
5.5.1 空值处理函数NVL
空值处理函数 NVL 的语法如下：
NVL(检测的值,如果为null的值);
也就是说如果检测的值为 null 的话，那就会返回函数中的第 2 个参数，如果不为 null 的话，那就直接返回检测的值。
select NVL(NULL,0) from dual
select NVL(100,0) from dual
因此上面的 SQL 的返回值应该是 0 和 100。
5.5.2 空值处理函数NVL2
空值处理函数 NVL2 的语法如下：
NVL2(检测的值,如果不为null的值,如果为null的值);
当检测的值不为 null 时返回函数中的第 2 个参数，当检测的值为 null 时返回函数中的第 3 个参数。
select PRICE,MINNUM,NVL2(MAXNUM,to_char(MAXNUM),'不限') from t_pricetable where OWNERTYPEID=1
5.5.3 条件取值函数decode
条件取值函数 decode 的语法如下：
decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)
该函数的作用主要就是检测条件是否与后面的值相匹配，如果和值 1 匹配，那就返回翻译值 1，如果和值 2 匹配，就返回翻译值 2，以此类推下去，如果都没有匹配到，那就返回缺省值，当然缺省值是可以没有的，当没有缺省值，又没有值与条件相匹配的时候，就会返回 null。
select decode(100,1,2,3,4,100,200,400) from dual
select decode(300,1,2,3,4,100,200,400) from dual
上面的 SQL 则会返回 200 和 400。
select name,decode(ownertypeid,1,'居民',2,'行政事业单位',3,'商业') as 类型 from t_owners
上面的 SQL 也是同样的使用了，当 ownertypeid 和 1 匹配时返回居民，和 2 匹配时返回行政事业单位，和 3 匹配时返回商业。其实上面的 SQL 我们也可以使用 case when then 语句来实现。
select name ,(case ownertypeid when 1 then '居民' when 2 then '行政事业单位' when 3 then '商业' else '其它' end ) from t_owners
还有另外一种写法：
select name,(case when ownertypeid= 1 then '居民' when ownertypeid= 2 then '行政事业' when ownertypeid= 3 then '商业' end ) from t_owners
6.行列转换
行列转换主要是当我们有些数据需要统计时，将一些列维度的数据转换到行的维度进行展示，便于我们进行查看，这里我们主要是通过 case when then 语句来实现。
需求：按月份统计2012年各个地区的水费
select (select name from T_AREA where id=areaid ) 区域,
sum( case when month='01' then money else 0 end) 一月,
sum( case when month='02' then money else 0 end) 二月,
sum( case when month='03' then money else 0 end) 三月,
sum( case when month='04' then money else 0 end) 四月,
sum( case when month='05' then money else 0 end) 五月,
sum( case when month='06' then money else 0 end) 六月,
sum( case when month='07' then money else 0 end) 七月,
sum( case when month='08' then money else 0 end) 八月,
sum( case when month='09' then money else 0 end) 九月,
sum( case when month='10' then money else 0 end) 十月,
sum( case when month='11' then money else 0 end) 十一月,
sum( case when month='12' then money else 0 end) 十二月
from t_account where year='2012' group by areaid
需求：按季度统计2012年各个地区的水费
select (select name from T_AREA where id=areaid ) 区域,
sum( case when month>='01' and month<='03' then money else 0 end) 第一季度,
sum( case when month>='04' and month<='06' then money else 0 end) 第二季度,
sum( case when month>='07' and month<='09' then money else 0 end) 第三季度,
sum( case when month>='10' and month<='12' then money else 0 end) 第四季度
from t_account where year='2012' group by areaid
7.分析函数
下面我们开始介绍 Oracle 为我们提供的 3 种分析函数，都是用于进行排名的，为了方便进行说明，我们先根据数据说明 3 中不同的排序方法。
分数 排法1 排法2 排法3
100 1 1 1
90 2 2 2
90 2 2 3
90 2 2 4
88 5 3 5
86 6 4 6
85 7 5 7
85 7 5 8
84 9 6 9
82 10 7 10
其实上面的这三种排序方法便是我们今天要来介绍的三种分析函数，下面一一具体介绍。
7.1 rank
rank() 函数的排名效果为值相同时排名相同，而排名是跳跃的，也就是我们上面的排法 1。
select rank() over(order by usenum desc ),usenum from t_account
7.2 dense_rank
dense_rank() 函数的排名效果为相同的值排名相同，排名连续，也就是我们上面的排法 2。
select dense_rank() over(order by usenum desc ),usenum from t_account
7.3 row_number
row_number() 函数的排名效果为返回连续的排名，无论值是否相等，也就是我们上面的排法 3。
select row_number() over(order by usenum desc ),usenum from t_account
其实我们还可以使用 row_number() 函数来实现分页查询。
select * from (select row_number() over(order by usenum desc) rownumber,usenum from t_account) where rownumber>10 and rownumber<=20
这也就是我们想要介绍的三个分析函数了。
8.集合运算
我们都知道数学上面的集合运算，无外乎是并集、交集和差集，那 Oracle 提供的集合运算又是怎样的呢？其实也是这三种操作，不过是对记录之间的运算。
UNION ALL(并集)，返回各个查询的所有记录，包括重复记录。
UNION(并集)，返回各个查询的所有记录，不包括重复记录。
INTERSECT(交集)，返回两个查询共有的记录。
MINUS(差集)，返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录。
这便是我们将要介绍的集合运算的内容。
8.1 并集运算
UNION ALL 不去掉重复记录
select * from t_owners where id<=7
union all
select * from t_owners where id>=5
UNION 去掉重复记录
select * from t_owners where id<=7
union
select * from t_owners where id>=5
8.2 交集运算
select * from t_owners where id<=7
intersect
select * from t_owners where id>=5
8.3 差集运算
select * from t_owners where id<=7
minus
select * from t_owners where id>=5
上面就是我们想要介绍的集合运算的全部内容了，最后想要说明的一点就是，使用差集运算也能实现分页，而且还不用使用子查询。
select rownum,t.* from t_account t where rownum<=20
minus
select rownum,t.* from t_account t where rownum<=10
9.总结
这篇博客主要是记录的关于 Oracle 查询相关的内容，整理完之后对 Oracle 的使用也有了更多的把握，知道了更多的使用技能和原则，收获很多了。
