oracle常规使用速成(二)

一、单表查询

（一）简单条件查询

精确查询：查询水表编号为30408的业主记录

select * from T_OWNERS where watermeter='30408'

模糊查询:查询业主包含“”刘“”的业主记录

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

and运算符:查询业主包含“”刘“”的并且门牌号包含5的业主记录

select * from T_OWNERS where name like '%刘%' and housenumber like '%5%'

or 运算符：查询业主名称包含“”刘“”的或者门牌号包含5的业主记录

select * from T_OWNERS where name like '%刘%' or housenumber like '%5%'

and与or运算符混合使用：查询业主名名称包含“”刘“”的或者门牌号包含5的业主记录，并且地址编号为3的记录

select *  from T_OWNERS where addressid='3' and (name like '%刘%' or housenumber like '%5%' )

范围查询：查询台账记录中用水字数大于等于10000，并且小于等于20000的记录，我们可以用>=和<=来实现

--第一种
select * from T_ACCOUNT where 10000<=usenum and usenum<=20000
--第二种
select * from T_ACCOUNT where usenum between 10000 and 20000```

空值查询：查询价格表中maxnum为空的记录

select * from T_PRICETABLE where maxnum is null 

（二）去掉重复记录

去掉重复：查询业主表中的地址ID，不重复显示

select distinct addressid from T_OWNERS 

（三）排序查询

升序查询：对T_ACCOUNT表按使用量进行升序倒序

select *  from T_ACCOUNT  order by usenum

降序：降序排毒

select *  from T_ACCOUNT  order by usenum desc

（四）基于伪列的查询

伪列：Oracle中，实际表中还有一些附加的列，就像表中的列一样，但是在表中并不存储，位列只能查询，不能进行增删改操作，

rowid

表中每一行在数据文件中都有一个物理地址，rowid伪列返回的就是该行的物理地址，使用rowid可以快速的定位某一行

rownum

在查询的结果集中，rownum为结果集中每一行标识一个行号，第一行返回1，第二行返回2，通过rownum伪列可以限制查询结果集中返回的行数

（五）聚合统计

1.求和 sum

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

2.求平均 avg

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 where ownertypeid='1'

6.分组聚合组合 group by

select sum(money) from T_ACCOUNT group by areaid

7.分组后条件查询 having

select areaid,sum(money) from T_ACCOUNT group by areaid having sum(money) > 169000

连接查询（多表查询）

（一）多表内连接查询

示例：查询业主的账务记录，显示业主编号、名称、年、月、金额。如果此业主没有账务记录也要列出姓名

内连接写法
select ow.id 业主编号,ow.name 名称,year 年,month 月,money 金额
from T_OWNERS ow left join T_ACCOUNT ac
on ow.id=ac.owneruuid(+)

（二）左外连接查询

示例：查询业主的账务记录，显示业主编号、名称、年、月、金额。如果此业主没有账务记录也要列出姓名

左外连接在条件语句右边(+)
在有些数据没显示全，比如值为null 这样情况下，可以使用左右外连接
select ow.id 业主编号,ow.name 名称,year 年,month 月,money 金额
from T_OWNERS ow,T_ACCOUNT ac
where ow.id=ac.owneruuid(+)

（三）右外连接查询

示例：查询业主的账务记录，显示业主编号、名称、年、月、金额。如果账务记录没有对应业主信息，也要列出

右外连接在条件语句左边(+)
select ow.id 业主编号,ow.name 名称,year 年,month 月,money 金额
from T_OWNERS ow,T_ACCOUNT ac
where ow.id=ac.owneruuid(+)

子查询 （嵌套查询）

（一）where子句中的子查询

单行子查询

• 只返回一条记录
• 单行操作符 = < > <= >= <>

示例：查询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')

多行子查询

• 返回了多条记录
• 多行操作符 in(等于列表中的任何一个) any(和子查询返回的任意一个值比较) all（和子查询返回的所有值比较）

select * from student where id in (1,3)

（二）from子句中的子查询

select id from (select id from student)

（三）select子句中的子查询

select id,(select score from score) from student

分页查询

（一）简单分页

查询值大于10小于等于20的的数据

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

（二）基于排序的分页

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

单行函数

（一）字符函数

1.求字符串长度 length
2.求字符串的子串 substr
3.字符串拼接 concat 和 ||

（二）数值函数

1.四舍五入 round
2.截取函数 trunc
3.取模 mod

（三）日期函数

1.获取系统日期

select sysdate from dual

2.加月函数 add_months ：在当前日期基础上加指定的月

select add_months(sysdate,2) from  dual

3.求所在月最后一天 last_day

select last_day(sysdate) from dual

4.日期截取 trunc

select trunc(sysdate) from dual --按日截取
select trunc(sysdate,'mm') from dual --按月截取
select trunc(sysdate,'yyyy') from dual --按年截取
select trunc(sysdate,hh'') from dual --按小时截取
select trunc(sysdate,'mi') from dual --按分钟截取

（四）转换函数

1.数字转字符串 to_char
2.日期转字符串 to_char
3.字符串转日期 to_date
4.字符串转数字 to_number

（五）其他函数

1.空值处理函数 nvl(检测的值,如果为null的值) 把null转换成设置的值

select nvl(null,0) from student

2.空值处理函数 nvl2(检测的值,如果不为null的值,如果为null的值) 把null转换成设置的值

3.条件取值 decode(条件,值1,翻译值1,条件,值2,翻译值2) 根据条件返回相应值=case when then

行列转换

分析函数

集合运算

交集：intersect

并集：union

差集：minus