oracle的学习2-单表查询&链接查询&子查询&分页查询
查询
一、单表查询
(一)简单条件查询
1.精确查询
需求:查询水表编号为 30408 的业主记录
查询语句:
select * from T_OWNERS where watermeter='30408'
2.模糊查询
需求:查询业主名称包含“刘”的业主记录
查询语句:
select * from t_owners where name like '%刘%'
3. and 运算符
需求:查询业主名称包含“刘”的并且门牌号包含 5 的业主记录
查询语句:
select * from t_owners where name like '%刘%' and housenumber like '%5%'
4. or 运算符
需求:查询业主名称包含“刘”的或者门牌号包含 5 的业主记录
查询语句:
select * from t_owners where name like '%刘%' or housenumber like '%5%'
5. and 与 or 运算符混合使用
需求:查询业主名称包含“刘”的或者门牌号包含 5 的业主记录,并且地址编号为 3 的记录。
语句:
select * from t_owners where (name like '%刘%' or housenumber like '%5%') and addressid=3
6. 范围查询
需求:查询台账记录中用水字数大于等于 10000,并且小于等于 20000 的记录
我们可以用>= 和<=来实现,语句
select * from T_ACCOUNT where usenum>=10000 and usenum<=20000
我们也可以用 between .. and ..来实现
select * from T_ACCOUNT where usenum between 10000 and 20000
7. 空值查询
需求:查询 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
(二)去掉重复记录
需求:查询业主表中的地址 ID,不重复显示
语句:
select distinct addressid from T_OWNERS
(三)排序查询
1.升序排序
需求:对 T_ACCOUNT 表按使用量进行升序排序
语句:
select * from T_ACCOUNT order by usenum
2.降序排序
需求:对 T_ACCOUNT 表按使用量进行降序排序
语句:
select * from T_ACCOUNT order by usenum desc ;
(四)基于伪列的查询
在 Oracle 的表的使用过程中,实际表中还有一些附加的列,称为伪列。伪列就
像表中的列一样,但是在表中并不存储。伪列只能查询,不能进行增删改操作。
接下来学习两个伪列:ROWID 和 ROWNUM。
1 ROWID
表中的每一行在数据文件中都有一个物理地址,ROWID 伪列返回的就是该行的
物理地址。使用 ROWID 可以快速的定位表中的某一行。ROWID 值可以唯一的
标识表中的一行。由于 ROWID 返回的是该行的物理地址,因此使用 ROWID 可
以显示行是如何存储的。
查询语句:
select rowID,t.* from T_AREA t
我们可以通过指定 ROWID 来查询记录
select rowID,t.* from T_AREA t where ROWID='AAAM1uAAGAAAAD8AAC';
2 ROWNUM
在查询的结果集中,ROWNUM 为结果集中每一行标识一个行号,第一行返回 1,
第二行返回 2,以此类推。通过 ROWNUM 伪列可以限制查询结果集中返回的行数.
查询语句:
select rownum,t.* from T_OWNERTYPE t
(五)聚合统计
ORACLE 的聚合统计是通过分组函数来实现的,与 MYSQL 一致。
1. 聚合函数
(1)求和 sum
需求:统计 2012 年所有用户的用水量总和
select sum(usenum) from t_account where year='2012'
(2)求平均 avg
需求:统计 2012 年所有用水量(字数)的平均值
select avg(usenum) from T_ACCOUNT where year='2012'
(3)求最大值 max
需求:统计 2012 年最高用水量(字数)
select max(usenum) from T_ACCOUNT where year='2012'
(4)求最小值 min
需求:统计 2012 年最低用水量(字数)
select min(usenum) from T_ACCOUNT where year='2012'
(5)统计记录个数 count
需求:统计业主类型 ID 为 1 的业主数量
select count(*) from T_OWNERS t where ownertypeid=1
2. 分组聚合 Group by
需求:按区域分组统计水费合计数
语句:
select areaid,sum(money) from t_account group by areaid
查询结果:
3. 分组后条件查询 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
(2)需求:查询显示业主编号,业主名称、地址和业主类型,如下图
分析:此查询需要三表关联查询。分别是业主表,业主分类表和地址表
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)
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_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 提供的语法,就很简单了:
SELECT ow.id,ow.name,ac.year ,ac.month,ac.money FROM T_OWNERS ow,T_ACCOUNT ac
WHERE ow.id=ac.owneruuid(+)
如果是左外连接,就在右表所在的条件一端填上(+)
(三)右外连接查询
需求:查询业主的账务记录,显示业主编号、名称、年、月、金额。如果账务记
录没有对应的业主信息,也要列出记录。如下图:
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. 单行子查询
只返回一条记录
单行操作符
需求:查询 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 运算符
(1)需求:查询地址编号为 1 、3、4 的业主记录
分析:如果我们用 or 运算符编写,SQL 非常繁琐,所以我们用 in 来进行查询
语句如下:
select * from T_OWNERS
where addressid in ( 1,3,4 )
(2)需求:查询地址含有“花园”的业主的信息
select * from T_OWNERS
where addressid in
( select id from t_address where name like '%花园%' )
(3)需求:查询地址不含有“花园”的业主的信息
语句:
select * from T_OWNERS
where addressid not in
( select id from t_address where name like '%花园%' )
(二)from 子句中的子查询
from 子句的子查询为多行子查询
需求:查询显示业主编号,业主名称,业主类型名称,条件为业主类型为”居民”,
使用子查询实现。
select * from (select o.id 业主编号,o.name 业主名称,ot.name 业主类型
from T_OWNERS o,T_OWNERTYPE ot where o.ownertypeid=ot.id) where 业主类型='居民'
(三)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;
四、分页查询
(一)简单分页
需求:分页查询台账表 T_ACCOUNT,每页 10 条记录
分析:我们在 ORACLE 进行分页查询,需要用到伪列 ROWNUM 和嵌套查询
我们首先显示前 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
嗯?怎么没有结果?
这是因为 rownum 是在查询语句扫描每条记录时产生的,所以不能使用“大于”
符号,只能使用“小于”或“小于等于” ,只用“等于”也不行。
那怎么办呢?我们可以使用子查询来实现
select * from (select rownum r,t.* from T_ACCOUNT t where rownum<=20) where r>10
(二)基于排序的分页
需求:分页查询台账表 T_ACCOUNT,每页 10 条记录,按使用字数降序排序。
我们查询第 2 页数据,如果基于上边的语句添加排序,语句如下:
select * from
(select rownum r,t.* from T_ACCOUNT t where rownum<=20 order
by usenum desc)
where r>10
你会发现排序后的 R 是乱的。这是因为 ROWNUM 伪列的产生是在表记录扫描
是产生的,而排序是后进行的,排序时 R 已经产生了,所以排序后 R 是乱的。
那该如何写呢?
很简单,我们只要再嵌套一层循环(一共三层),让结果先排序,然后对排序后
的结果再产生 R,这样就不会乱了。
语句如下:
select * from
(select rownum r,t.* from
(select * from T_ACCOUNT order by usenum desc) t
where rownum<=20 )
where r>10
五、单行函数
(一)字符函数
常用字符函数讲解:
(1)求字符串长度 LENGTH
语句:
select length('ABCD') from dual;
(2)求字符串的子串 SUBSTR
语句:
select substr('ABCD',2,2) from dual;
(3)字符串拼接 CONCAT
语句:
select concat('ABC','D') from dual;
(3)字符串拼接 CONCAT
语句:
select concat('ABC','D') from dual;
(二)数值函数
常用数值函数讲解:
(1)四舍五入函数 ROUND
语句:
select round(100.567) from dual;
语句: 跟参数 是保留两位小数的意思
select round(100.567,2) from dual;
(2)截取函数 TRUNC 不跟参数表示小数点后截取0位,跟参数代表多少位
语句:
select trunc(100.567) from dual ;
(3)取模 MOD (取余数的意思)
语句:
select mod(10,3) from dual;
(三)日期函数
我们用 sysdate 这个系统变量来获取当前日期和时间
语句如下:
select sysdate from dual;
常用日期函数讲解:
(1)加月函数 ADD_MONTHS :在当前日期基础上加指定的月(现在7月参数2 ,就是九月)
语句:
select add_months(sysdate,2) from dual;
(2) 求所在月最后一天 LAST_DAY
语句:
select last_day(sysdate) from dual;
(3)日期截取 TRUNC
语句:
select TRUNC(sysdate) from dual;
语句:
select TRUNC(sysdate,'yyyy') from dual
select TRUNC(sysdate,'mm') from dual;
(四)转换函数
常用转换函数讲解:
(1)数字转字符串 TO_CHAR
语句:
select TO_CHAR(1024) from dual ;
(2) 日期转字符串 TO_CHAR
语句:
select TO_CHAR(sysdate,'yyyy-mm-dd') from dual;
select TO_CHAR(sysdate,'yyyy-mm-dd hh:mi:ss') from dual ;
(3) 字符串转日期 TO_DATE
语句:
select TO_DATE('2017-01-01','yyyy-mm-dd') from dual;
查询结果如下:
(4)字符串转数字 TO_NUMBER
语句:
select to_number('100') from dual;
(五)其它函数
(1)空值处理函数 NVL
用法:
NVL(检测的值,如果为 null 的值);
语句:
select NVL(NULL,0) from dual;
需求:
显示价格表中业主类型ID为1的价格记录,如果上限值为NULL,则显示9999999
语句:
select PRICE,MINNUM,NVL(MAXNUM,9999999)
from T_PRICETABLE where OWNERTYPEID=1;
如果最大值是null的话,就显示9999999;
(2)空值处理函数 NVL2
用法:
NVL2(检测的值,如果不为 null 的值,如果为 null 的值);
需求:显示价格表中业主类型 ID 为 1 的价格记录,如果上限值为 NULL,显示“不
限”.
语句:
select PRICE,MINNUM,NVL2(MAXNUM,to_char(MAXNUM) , '不限')
from T_PRICETABLE where OWNERTYPEID=1;
(3)条件取值 decode
语法:
decode(条件,值 1,翻译值 1,值 2,翻译值 2,...值 n,翻译值 n,缺省值)
【功能】根据条件返回相应值
需求:显示下列信息(不要关联查询业主类型表,直接判断 1 2 3 的值)
select name,decode( ownertypeid,1,'居民',2,'行政事业单位',3,'商业') as 类型 from T_OWNERS
上边的语句也可以用 case when then 语句来实现
select name ,(case ownertypeid
when 1 then '居民'
when 2 then '行政事业单位'
when 3 then '商业'
else '其它'
end
) from T_OWNERS
六、行列转换
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;
七、分析函数
以下三个分析函数可以用于排名使用。
下图为三种排名方式的举例
(1) RANK 相同的值排名相同,排名跳跃
需求:对 T_ACCOUNT 表的 usenum 字段进行排序,相同的值排名相同,排名跳跃
语句:
select rank() over(order by usenum desc ),usenum from T_ACCOUNT ;
(2) DENSE_RANK 相同的值排名相同,排名连续
需求:对 T_ACCOUNT 表的 usenum 字段进行排序,相同的值排名相同,排名连
续
语句:
select dense_rank() over(order by usenum desc ),usenum from T_ACCOUNT;
(3) ROW_NUMBER 返回连续的排名,无论值是否相等
需求:对 T_ACCOUNT 表的 usenum 字段进行排序,返回连续的排名,无论值是
否相等
语句:
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;
八、集合运算
(一)什么是集合运算
集合运算,集合运算就是将两个或者多个结果集组合成为一个结果集。集合运算
包括:
··UNION ALL(并集),返回各个查询的所有记录,包括重复记录。
··UNION(并集),返回各个查询的所有记录,不包括重复记录。
··INTERSECT(交集),返回两个查询共有的记录。
··MINUS(差集),返回第一个查询检索出的记录减去第二个查询检索出的记录之
后剩余的记录。
UNIONALL 不去掉重复记录
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;
select * from t_owners where id<=7
minus
select * from t_owners where id>=5;
如果我们用 minus 运算符来实现分页,语句如下:
select rownum,t.* from T_ACCOUNT t where rownum<=20
minus
select rownum,t.* from T_ACCOUNT t where rownum<=10;