一、单行函数常用的1.字符函数--dual是伪表--求字符串长度select length(ABCD)from dual求字符串子串原字符串从第几位截取截取字符数select substr(abcd,2,2)from dual--字符串拼接,一次只能拼接一次select concat(abc,d) from dual--可以用||拼接可拼接多个select abc||Dfrom dual二、数值函数--四舍五入select round (100.56,2)from dual--数字截取select trunc(100.567,2) from dual--取模select mod(10,3)from dual三、日期函数--时间函数select sysdate hz from dual--加月函数select add_months(sysdate,2)from dual--求所在月的最后一天select last_day(sysdate) from dual--日期截取,trunc按日截取select trunc(sysdate) from dual按月截取select trunc(sysdatemm) from dual按年截取select trunc(sysdateyyyy) from dual小时就hh分钟就mi四、转换函数--数字转字符串select to_char(100) from dual;--拼接字符串也可以转为字符串select 100 || 分 from dual;--日期转字符串select to char(sysdate,yyyy-mm-dd)--字符串转日期select to_date(2026-03-10,yyyy-mm-dd)from dual--字符串转数值select to_number(1100) from dual五、其他函数--空值处理函数nullselect nvl(null,0)from dual -- 第一个参数为null就返回后面第二个参数0否则返回第一个参数查询ownertypeid 1的t_pricetable,如果maxnum为null就变成999select nvl(maxnum,999) from t_pricetable where ownertypeid 1--nvl2有三个参数第一个检测是否为null,是null就返回第三个不是就返回第二个select * from t_pricetable where ownertypeid 1select nvl2(maxnum,to_char(999),为空) from t_pricetable where ownertypeid 1--条件判断decode--语法decode(条件,值1,翻译值1,值2,翻译值2,....,缺省值) 根据条件返回相应的值都不符合就是缺省值缺省值可以省略select decode(400,1,2,3,4,100,200,300) from dual -- 返回的是300查业主表1就是居民select * from t_ownersselect name,decode(ownertypeid,1,居民,2,行政,其他)from t_owners--case when thenselect name,(casewhen ownertypeid1 then 居民when ownertypeid2 then 行政when ownertypeid3 then 其他else 不存在end) from t_owners--行列转换按月份统计2012,1月到4月各地区的水费select * from t_accountselect t_area.name,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 )from t_account left join t_area on t_area.id t_account.areaidwhere t_account.year 2012group by t_account.areaid,t_area.name六、窗口函数(分析函数)--排名函数必须搭配over函数--over()就是用来指定在什么数据里按什么顺序做求和/计算//排名但不减少行值相同排名相同序号跳跃 rank()select rank() over(order by usenum desc) 排名, t.* from t_account t值相同排名相同序号连续 dense_rank()select dense_rank() over(order by usenum desc) 排名, t.* from t_account t序号连续不管值是否相同 row_number()select row_number() over(order by usenum desc) 排名, t.* from t_account t用分析函数实现分页select * from(select row_number() over (order by usenum desc) rownumber,t.* from t_account t)where rownumber 10 and rownumber 20七、集合运算--将两个或多个结果集合成为一个结果集集合运算--并集包括重复的记录union allselect * from t_owners where id 5union allselect * from t_owners where id 8--并集去掉重复的记录unionselect * from t_owners where id 5unionselect * from t_owners where id 8--交集两个结果集的重复部分intersectselect * from t_owners where id 5intersectselect * from t_owners where id 8--差集minusselect * from t_owners where id 5minusselect * from t_owners where id 8select * from t_owners where id 8minusselect * from t_owners where id 5差集的分页查询select rownum,t.* from t_account t where rownum 20minusselect rownum,t.* from t_account t where rownum 10