qileilove

blog已经转移至github,大家请访问 http://qaseven.github.io/

Oracle单行函数和多行函数实例

  单行函数和多行函数示意图:

  单行函数分为五种类型:字符函数、数值函数、日期函数、转换函数、通用函数

  单行函数:

--大小写控制函数
select lower('Hello World') 转小写, upper('Hello World') 转大写 from dual;
--initcap: 首字母大写
select initcap('hello world') 首字符大写 from dual;

--字符控制函数
-- concat: 字符连接函数, 等同于  ||
select concat('Hello',' World') from dual;
--substr:求母串中的某个子串
select substr('Hello World',3) from dual;
select substr('Hello World',3,4) from dual;
--length和lengthb: 字符数和字节数
select length('China') 字符数, lengthb('China') 字节数  from dual;
--instr:在母串中,查找子串的位置
select instr('Hello World','ll') from dual;
--lpad,rpad: 左右填充,将abcd用*填充到10位
select lpad('abcd',10,'*') 左填充, rpad('abcd',10,'*') 右填充 from dual;
--trim: 去掉字符串前后指定的字符
select trim('H' from 'Hello WorldH') from dual;
--replace:字符串替换函数
select replace('Hello Wordl','l','*') from dual;

--数字函数
select round(45.926,2) 四舍五入, trunc(45.926,2)  截断 ,mod(1600,300) 求于 from dual;
--ROUND函数
select round(45.923,0) 整数位, round(45.923,-1) 十位,round(45.923,-2) 百位 from dual;

--日期函数
--显示当前日期
select sysdate from dual;
--显示时间部分
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
--显示昨天,今天和明天,加减数字仍未日期
select sysdate-1 昨天, sysdate 今天, sysdate+1 明天 from dual;
--两个日期相减,结果为相差的天数,查询员工信息,显示员工工龄。两个日期不能相加
select empno,ename, sysdate-hiredate 天 from emp;
--查询员工信息,显示员工工龄,分别按照天,星期,月显示
select empno,ename,sysdate-hiredate 天,(sysdate-hiredate)/7 星期, (sysdate-hiredate)/30 月 from emp;
--months_between:两个日期相差的月数
select (sysdate-hiredate)/30 方式一, months_between(sysdate,hiredate) 方式二 from emp;
--add_months:在指定日期上加上若干个月
select add_months(sysdate,1)  下个月, add_months(sysdate,123) "123个月后" from dual
--last_day: 某个日期当月的最后一天
select last_day(sysdate) from dual;
--next_day:下周六
select next_day(sysdate,'星期五') from dual;
--对日期进行四舍五入
select round(sysdate,'MONTH')  月,round(sysdate,'YEAR') from dual; 
--对日期进行截断
select trunc(sysdate,'MONTH')  月,trunc(sysdate,'YEAR') from dual;
--日期格式
select * from emp where hiredate=to_date('1982-01-23','yyyy-mm-dd');
-- 查询当前日期:显示:  2011-09-17 15:12:15今天是星期六
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss"今天是"day') from dual;
--查询员工信息,显示员工的编号,姓名,月薪,要求有货币代码(L),千位符(,),小数点(.),
select empno,ename,to_char(sal,'L9,999.99') from emp;

--通用函数
--nvl(exp1,exp2):当exp1为空时,返回exp2
--nvl2(exp1,exp2,exp3):当exp1为空时,返回exp3;否则返回exp2
select ename,sal*12+nvl2(comm,comm,0) 年收入 from emp;
--NULLIF (expr1, expr2),如果expr1=expr2,返回null;否则,返回expr1
select nullif('abc','abc') from dual;
select nullif('abc','abcaa') from dual;
--COALESCE :找到参数列表中,第一个不为空的值
select ename,comm,sal,COALESCE(comm,sal) from emp;
--给员工涨工资,根据职位涨,总裁涨1000,经理涨600 其他人员涨400
select ename,job,sal 涨前工资, case job when 'PRESIDENT' then sal+1000
                                         when 'MANAGER'   then sal+600
                                         else sal+400
                                 end 涨后工资
from emp;

select ename,job,sal 涨前工资, decode(job,'PRESIDENT',sal+1000,
                                            'MANAGER',sal+600,
                                             sal+400) 涨后工资
from emp;

  多行函数

  和单行函数相比,oracle提供了丰富的基于组的,多行的函数。这些函数能在select或select的having子句中使用,当用于select子串时常常都和GROUP BY一起使用。多行函数分为接收多个输入,返回一个输出。

  组函数:

--求员工的工资总和
select sum(sal) from emp;
--求个数
select count(*) from emp;
--求平均工资
select sum(sal)/count(*) 方式一, avg(sal) 方式二 from emp;
--关于空值:组函数会自动滤空
select count(*), count(comm) from emp;
--max和min:求最高工资和最低工资
select max(sal) 最高工资,min(sal) 最低工资 from emp;
--分组数据:求各个部门的平均工资
select deptno,avg(sal) from emp group by deptno;
--group by作用于多列: 按部门,不同的工种,统计平均工资
--group by作用于多列:先按照第一列分组;如果相同,再按照第二列分组
select deptno,job,avg(sal) from emp group by deptno,job;
--:求部门的平均工资大于2000的部门
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
--group by的增强
select deptno,job,sum(sal) from emp group by rollup(deptno,job);
--不同的deptno空两行/取消设置
break on deptno skip 2/break on null

posted on 2012-04-11 09:31 顺其自然EVO 阅读(3689) 评论(0)  编辑  收藏 所属分类: 数据库


只有注册用户登录后才能发表评论。


网站导航:
 
<2012年4月>
25262728293031
1234567
891011121314
15161718192021
22232425262728
293012345

导航

统计

常用链接

留言簿(55)

随笔分类

随笔档案

文章分类

文章档案

搜索

最新评论

阅读排行榜

评论排行榜