Oracle--查询语句

一、单表查询查询水表记录为30408的业主记录select * from T_OWNERS where watermeter30408--只查id,name,addressidselect id,name,addressid from t_Owners where watermeter30408二、模糊查询查询业主名称包含刘的业主记录select * from T_OWNERS where name like %刘%;三、多条件查询--and运算符查询业主名称包含刘并且门牌号包含5的业主记录select * from T_OWNERS WHERE NAME LIKE %刘% and housenumber like %5%;--and运算符和or运算符and的优先级比or大所以要用来改变优先级查询业主名称包含刘或门牌号包含5的业主记录并且地址编号为3的记录select * from T_OWNERS WHERE (NAME LIKE %刘% or housenumber like %5%) and ADDRESSID 3;四、范围查询和查询台账记录中用水字数大于等于10000并且小于等于20000的记录select * from T_ACCOUNT WHERE usenum 10000 and usenum 20000;select * from T_ACCOUNT WHERE usenum between 10000 and 20000;四、空值查询 is null查询T_PRICETABLE表中maxnum为空值的数据select * from T_PRICETABLE WHERE MAXNUM IS NULL;查询T_PRICETABLE表中maxnum为不空值的数据select * from T_PRICETABLE WHERE MAXNUM IS not NULL;五、去除重复记录查询业主表中的地址id不重复显示select DISTINCT addressid from t_owners;把addressid,ownertypeid看成一对去重select DISTINCT addressid,ownertypeid from t_owners;六、保留重复的记录SELECT 列名FROM 表名GROUP BY 要判断重复的列HAVING COUNT(*) 1;七、排序查询--升序排序对T_ACCOUNT表按使用量进行升序排序select * from T_ACCOUNT order by usenum;--降序排序对T_ACCOUNT表按使用量进行升序排序select * from T_ACCOUNT order by usenum desc;八、基于伪列的查询--oracle表的使用过程中实际表中还有一些附加的列称为伪列像在表中的列一样但在表中不存储不能增删改查--ROWID,表中的每行数据都有一个物理地址ROWID伪列返回的就是这个行的物理地址--ROWID可以快速定位到表中的某一行其值可以唯一识别表中的一行--用*的话不能直接写*要加别名select rowid,t.* from t_owners tselect rowid,t.* from t_owners t where rowid AAASB0AAFAAAADWAAC--rownum--在查询结果集中rownum为结果中每一行标识一个行号第一行返回1第二行为2--rownum是在where之后order by 排序之前生成的select rownum,t.* from T_OWNERS t;九、聚合统计--是通过分组函数来实现的与mysql一致1.求和 sum统计2012年所有用户的用水量总和select sum(usenum) from t_owners where year 20122.求平均 avgselect avg(usenum) from t_owners where year 20123.求最大值select max(usenum) from t_owners where year 20124.求最小值select min(usenum) from t_owners where year 20125.统计个数select count(*)from t_owners where ownertypeid1--分组聚合--group by先分组注意select后一定是分组聚合的条件或者是聚合函数按区域分组统计水费合计数select areaid,sum(money) from t_account group by areaid--having分组后对结果查询查询水费合计大于169000的区域及水费合计select areaid,sum(money) from t_account group by areaid having sum(money) 169000;十、多表连接查询1.多表内连接查询查询显示业主编号业主名称业主类型select * from T_OWNERSSELECT * FROM T_ownertypeSELECT os.id,os.name,ot.name FROM T_ownertype ot,T_OWNERS os where os.ownertypeid ot.id--显示内连接 inner join 表 on 条件SELECT os.id,os.name,ot.name FROM T_ownertype ot inner join T_OWNERS os on os.ownertypeid ot.id查询显示业主编号业主名称地址和业主类型select * fRom T_ADDRESSSELECT os.id,os.name,ad.name,ot.name from T_ownertype ot,T_OWNERS os,t_address ad where os.ownertypeid ot.id and os.addressid ad.id查询显示业主编号业主名称地址所属区域业主类型select * from T_AREASELECT os.id,os.name,ad.name,ot.namear.name from T_ownertype ot,T_OWNERS os,t_address ad,T_AREA ar where os.ownertypeid ot.id and os.addressid ad.id and ar.id ad.areaid查询显示业主编号业主名称地址所属区域收费员业主类型select * from t_operatorSELECT os.id,os.name,ad.name,ot.namear.name,op.name from T_ownertype ot,T_OWNERS os,t_address ad,T_AREA ar,t_operator opwhere os.ownertypeid ot.id and os.addressid ad.id and ar.id ad.areaid and op.id ad.operatorid2.左外连接查询业主的账务记录显示业主编号名称年月金额如果业主没有账务记录也要列出姓名select * from T_ACCOUNTselect * from T_OWNERSselect os.id,os.name,year,month,money from T_OWNERS os left join T_ACCOUNT ac on ac.owneruuid os.id--oracle特别写法select os.id,os.name,year,month,money from T_OWNERS os, T_ACCOUNT ac where os.id ac.owneruuid()--右外连接查询业主的账务记录显示业主编号名称年月金额如果没有对应的业主消息也要列出记录select os.id,os.name,year,month,money from T_OWNERS os right join T_ACCOUNT ac on ac.owneruuid os.id--oracle特别写法select os.id,os.name,year,month,money from T_OWNERS os, T_ACCOUNT ac where os.id() ac.owneruuid十一、子查询子查询内部字段优先匹配自己的表找不到的字段才会去外面找1.where子句中的查询--单行子查询(指括号语句里的数据只返回一个数据)只返回一条记录单行操作符查询2012年一月用水量大于平均值的台账记录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查询地址编号为1、3、4的业主记录select * from t_owners where addressid in (1,3,4)查询地址中含有花园的业主信息select * from t_owners where addressid in (select id from t_address where name like %花园%)查询地址不含有花园的业主的消息select * from t_owners where addressid not in (select id from t_address where name like %花园%)2.from语句子查询--子查询为多行子查询把查询结果看作一张表from后面的子查询是一个临时表建议起别名查询显示业主编号业主名称业主类型名称条件为业主类型为居民select * from(select os.id ,os.name,ot.name 业主类型名称 from T_OWNERS os,t_ownertype ot where os.ownertypeid ot.id) t where 业主类型名称 居民3.select子句中的子查询--select子句中的子查询必须是单行子查询--主表每走一行子查询就执行一次列出业主信息包括id,名称所属地址select * from t_ownersselect * from t_addressselect id,name,(select name from t_address where id addressid) addressname from t_owners也可以用多表查询实现select t_owners.id,t_owners.name,t_address.name from t_owners,t_address where t_owners.addressid t_address.id列出业主信息包括id,名称所属地址所属区域select * from t_areaselect id,name,(select name from t_address where id addressid) addressname,(select select t_area.name from t_area,t_address where t_area.id t_address.areaid from t_address where id addressid) areanamefrom t_owners十二、分页查询1.简单分页分页查询台账表t_account每页10行记录在oracle分页查询需要用伪列rownum和嵌套查询--这个rownum只能是小于等于不能是大于select rownum,t.* from t_account t where rownum 10可以把rownum变成相当于有值用子查询实现,就可以用大于号select * from (select rownum r,t.* from t_account t )where r 20 and r 10--基于排序的分页select * from t_account order by usenum desc--rownum是在where之后order by 排序之前生成的--要是实现基于上面语句的分页排序就要先排序然后基于结果rownumselect * from (select rownum r,t.* from (select * from t_account t order by usenum desc)t) where r20 and r10;