Oracle数据库查询实例

–简单查询
select id,name,addressid from T_OWNERS where watermeter=‘30408’
–模糊查询（查询业主名称中包含刘的所有用户)
select * from T_OWNERS where name like ‘%刘%’

–and
select * from T_OWNERS where name like ‘%刘%’ and housenumber like ‘%5%’

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

select * from T_ACCOUNT where usenum>=10000 and usenum<=20000

select * from T_ACCOUNT where usenum between 10000 and 20000

select * from T_PRICETABLE WHERE maxnum is null
select * from T_PRICETABLE WHERE maxnum is not null

select DISTINCT addressid,ownertypeid from T_OWNERS

select DISTINCT addressid,ownertypeid from t_owners

–排序
–升序
select * from t_account order by usenum

–降序
select * from t_account order by usenum desc
–伪列 rowid
select rowid,t.* from t_owners t where rowid=‘AAAM1vAAGAAAACtAAE’

–伪列
select rownum,t.* from t_owners t where id>3

–聚合统计
select sum(usenum) from t_account where year=‘2012’

–求平均
select avg(usenum) from t_account where year=‘2012’
–求最大值
select max(usenum) from t_account where year=‘2012’
–求最小值
select min(usenum) from t_account where year=‘2012’

–求统计个数
select count(*) from t_owners where ownertypeid=1