oracle中一些函数的操作

SQL函数带有一个或者多个参数且一定有返回值
SQL函数包括单行函数、分组函数、分析函数三大类

一、单行函数 对于从表中查询的每一行只返回一个值
可以出现在select子句和where子句中
分类：
1、字符函数
ASCII函数 ：返回给定字符的ASCII码值
CHR函数 ： 返回指定ASCII码值的字符
select ascii('a'),chr(65)from dual;

[img]http://dl2.iteye.com/upload/attachment/0112/9070/fedc1789-a363-3e4e-b5d1-3f6ed1906629.png[/img]

SUBSTR（string，start，count）函数：获取源字符串从start位置起得 count个字符
select substr('abcdef',2,3) from dual;

[img]http://dl2.iteye.com/upload/attachment/0112/9072/e66ea9cc-de86-357d-ab73-7e7a7c9a4443.png[/img]

NVL(字段，值):假如字段为空值 则用值代替它
select sal,comm,sal+nvl(comm,0) as sum from emp;

[img]http://dl2.iteye.com/upload/attachment/0112/9074/cebfbd2c-cd46-3e3b-90a0-a5f0e2daf0d6.png[/img]

NVL2(字段，value1，value2)：假如字段为空，则返回value2，若不为空，则返回value2
select sal,comm,nvl2(comm,comm,1) from emp;

[img]http://dl2.iteye.com/upload/attachment/0112/9076/6b9c81f6-3548-3b2b-907f-f12b90d94b8b.png[/img]

NULLIF(字段，value)：假如字段中的值为value，则赋为空
select comm,nullif(comm,300) from emp;

[img]http://dl2.iteye.com/upload/attachment/0112/9078/f298e0ae-4cb1-3c09-956e-c5c9bf104d2b.png[/img]

LENGTH(string):字符串的长度
select length('abcd') from dual;
select comm,length(comm) from emp;

[img]http://dl2.iteye.com/upload/attachment/0112/9080/65d4bc17-12b9-31d2-994f-b2226d16392f.png[/img]

INITCAP(string):首字母大写
select initcap（'helLo'）from dual;

[img]http://dl2.iteye.com/upload/attachment/0112/9082/a956f476-b622-38ab-a8d6-c3fddf529a5f.png[/img]

UPPER(string):全部转换成大写
select upper（'Hello'） from dual;

[img]http://dl2.iteye.com/upload/attachment/0112/9084/d7b04d23-f292-3af4-ab50-d9280148d788.png[/img]

LOWER(string):全部转换成小写
select lower（'heLLO'）from dual;

[img]http://dl2.iteye.com/upload/attachment/0112/9088/60574374-578f-30e7-9840-86991d7ba8ca.png[/img]

CONCAT(string1，string2):合并两个字符串
select concat('a','b') from dual;

[img]http://dl2.iteye.com/upload/attachment/0112/9090/c1cfbd2e-32b4-367a-bcde-446df826d0e7.png[/img]

REPLACE(string1,char1,char2):将指定字符串中char1字符用char2 (单个或者多个)字符代替
select replace（'hello'，'h','b'）from dual;
select replace（'hello'，'h','bb'）from dual;

[img]http://dl2.iteye.com/upload/attachment/0112/9092/6b7a6b59-ba45-3b2e-89a3-5f28e858bd72.png[/img]

LPAD（string,count,char）:如果string不够count位，则在string的左 边用char补齐count位
select lpad('bbb',5,'a') from dual;

[img]http://dl2.iteye.com/upload/attachment/0112/9094/c40feadf-6ab0-3302-a89b-f310aa9719f6.png[/img]

RPAD（string,count,char）:如果string不够count位，则在string的右 边用char补齐count位
select rpad('bbb',5,'a') from dual;

[img]http://dl2.iteye.com/upload/attachment/0112/9096/516591d8-79c5-375f-b1a4-67576507a39a.png[/img]

LTRIM(string,char):如果char中任意一个字符出现在string左边，就去掉
select ltrim('abcdef','ac') from dual;
select ltrim('abcdef','acb') from dual;

[img]http://dl2.iteye.com/upload/attachment/0112/9098/23fa8df1-2e9e-39f4-b7b4-729e896eb407.png[/img]

RTRIM(string,char):如果char中任意一个字符出现在string右边，就去掉
select rtrim('abcdef','df') from dual;
select rtrim('abcdef','fed') from dual;

[img]http://dl2.iteye.com/upload/attachment/0112/9100/59bca849-55f0-3092-b1bc-2879ed2f8bf2.png[/img]

2、数字函数
ABS(value):返回value的绝对值
select abs(-1) from dual;

[img]http://dl2.iteye.com/upload/attachment/0112/9104/3dd57808-94f9-3dc5-ad94-8ab7c3d54620.png[/img]

CEIL（value）：返回大于或等于value的最小整数
select ceil(4.0) from dual;

[img]http://dl2.iteye.com/upload/attachment/0112/9106/c49c1409-6289-3bac-89ec-46ecf8b347d1.png[/img]

select ceil(4.1) from dual;

[img]http://dl2.iteye.com/upload/attachment/0112/9108/5d233972-7ded-354d-a9b6-91869d3dba22.png[/img]

select ceil(4.5) from dual;

[img]http://dl2.iteye.com/upload/attachment/0112/9110/a4ee95dd-c904-3414-af31-65422c571487.png[/img]

FLOOR（value）：返回小于或等于value的最大整数
select floor(4.0) from dual;

[img]http://dl2.iteye.com/upload/attachment/0112/9114/5c87fcf8-3b15-3eb3-8246-aad0bd5cf7ac.png[/img]

select floor(4.1) from dual;

[img]http://dl2.iteye.com/upload/attachment/0112/9116/e378e44b-6c8d-3444-b8d4-4a27cac49a40.png[/img]

select floor(4.9) from dual;

[img]http://dl2.iteye.com/upload/attachment/0112/9118/e3646292-c9e8-3c32-8fc3-2502e92e67b2.png[/img]

ROUND（value，precious）：按精度四舍五入
select round(4.77,0) from dual;

[img]http://dl2.iteye.com/upload/attachment/0112/9126/1bd0b592-0dc1-35e8-bee0-ec3208570971.png[/img]

select round（4.77,1）from dual;

[img]http://dl2.iteye.com/upload/attachment/0112/9124/24cf0b13-4c25-307f-bff8-930a07ed7e1c.png[/img]

TRUNC（value，可选参数）：按精度截断
select trunc(4.77,0) from dual;

[img]http://dl2.iteye.com/upload/attachment/0112/9130/ac8f3937-270b-36ef-a201-31245c77202a.png[/img]

select trunc(4.77,1) from dual;

[img]http://dl2.iteye.com/upload/attachment/0112/9128/54738618-5219-3257-9f68-36cd87c32944.png[/img]

select trunc(4.1,0) from dual;

[img]http://dl2.iteye.com/upload/attachment/0112/9136/f7e54de4-9c32-3dbd-b72f-bbd851c9b8bf.png[/img]

select trunc(4.77,-1) from dual;

[img]http://dl2.iteye.com/upload/attachment/0112/9134/7a88be94-7762-3545-91ee-0c188f4d0ec7.png[/img]

MOD（value1，divisor）：取余
select mod(3,2) from dual;

[img]http://dl2.iteye.com/upload/attachment/0112/9138/e16cbfce-e555-372b-b951-b380d9175ed7.png[/img]

SQRT（value）：返回value的平方根
select sqrt(4) from dual;

[img]http://dl2.iteye.com/upload/attachment/0112/9140/682461cc-0b5c-3fa9-9540-346e8149a5e3.png[/img]

POWER（value，exponent）：返回value的exponent的指数值
select power(2,3) from dual;

[img]http://dl2.iteye.com/upload/attachment/0112/9142/2ddc51ba-309a-37c5-ba09-bb63912ebca6.png[/img]

3、日期函数
ADD_MONTHS(date,count) 指定日期加上count个月
--15个月后是什么时候
select add_months(sysdate,15) from dual;

[img]http://dl2.iteye.com/upload/attachment/0112/9145/d3fa2057-9a95-3b50-8480-c29940665ed0.png[/img]

SYSDATE() 获取当前系统日期
4、转换函数
TO_DATE(2015-10-12'','YYYY-MM-DD') 将字符串类型转换成日期类型
select to_date('2015-11-08','yyyy-mm-dd');

[img]http://dl2.iteye.com/upload/attachment/0112/9149/ae264e67-ad05-33d1-89fa-b5ffb1d198cf.png[/img]

TO_CHAR(date,'yyyy-mm-dd') 将日期类型转换成字符串类型
select to_char(sysdate，'yyyy-mm-dd') from dual;
TO_NUMBER('1234') 将字符串转换成数字
select to_number('1234') from dual;


二、分组函数 对整个结果集进行处理，不能与字段、单行函数一起用
select ename,sum(sal) from emp;//错误

[img]http://dl2.iteye.com/upload/attachment/0112/9159/e4e8a975-8f22-33c1-ba87-6533f6737cff.png[/img]

1、SUM 函数：求和
select sum(sal) from emp;

[img]http://dl2.iteye.com/upload/attachment/0112/9161/d0a4bc82-6fb7-3a59-8a46-e5eddc8fac00.png[/img]

2、AVG 函数：求平均值
select avg(sal) from emp;

[img]http://dl2.iteye.com/upload/attachment/0112/9163/69d5c899-b6d0-3e87-9c9e-b779f41a2fd1.png[/img]

3、MAX 函数：求最大值
select max(sal) from emp;

[img]http://dl2.iteye.com/upload/attachment/0112/9165/88bdda79-043b-3952-9d68-ccc880cf17cf.png[/img]

4、MIN 函数：求最小值
select min(sal) from emp;

[img]http://dl2.iteye.com/upload/attachment/0112/9167/06ab8e8e-7df3-3c30-b170-598ace9283f4.png[/img]

5、COUNT 函数：统计数据（count（）括号里面可以是*也可以是字段名）
select count(*) from emp;

[img]http://dl2.iteye.com/upload/attachment/0112/9169/6244dbf7-3ff5-30ac-a3cc-6fee30097e66.png[/img]

select count(deptno) from emp where deptno=10;

[img]http://dl2.iteye.com/upload/attachment/0112/9171/a1fcebb1-4fb5-3e38-8482-2344a0d778f6.png[/img]


分组函数经常与分组语句连用
select deptno,sum(sal) from emp group by deptno;

[img]http://dl2.iteye.com/upload/attachment/0112/9175/cd4e2772-e895-3c41-81c6-7ad9bbb89c86.png[/img]


三、分析函数
row_number:得到连续的数字
rank：相同的值排位相同，随后跳跃
dense_rank：相同的值排位相同，随后接着往后排
select empno,ename,sal,row_number() over(order by sal) from emp;

[img]http://dl2.iteye.com/upload/attachment/0112/9151/e3e1d3fd-a624-35bc-b910-5981f686549d.png[/img]

select empno,ename,sal,rank() over(order by sal desc) from emp;

[img]http://dl2.iteye.com/upload/attachment/0112/9157/45e0b0f0-9fa9-3b55-b83f-736ba25434ab.png[/img]

select empno,ename,sal,dense_rank() over(order by sal desc)from emp;

[img]http://dl2.iteye.com/upload/attachment/0112/9155/1d49b02c-ea28-3114-98e4-dc4d0897cf96.png[/img]