Oracle学习之单行函数_统计函数

gscuedcgco

　　一、Single-Row Functions(单行函数)
　　
　　  单行函数为每一行查询的表或视图返回一个单独的结果行，单行函数可出现          在SELECT列中、WHERE子句中、START WITH 和 CONNECT BY子句            中、HAVING子句中。
　　
　　单行函数可操作多种数据类型：
　　
　　 1、Numeric Functions(数值函数)：用于操作数值类型的函数
　　
　　常用的函数如下：
　　1.1、ABS(n)：该函数返回一个数值n的绝对值
　　

SQL> SELECT ABS(-10) FROM dual;
ABS(-10)
----------
10
　　
　　 1.2、FLOOR(n)：该函数返回一个最大的等于或小于n的数值
　　

SQL> SELECT FLOOR(12.8) FROM DUAL;
FLOOR(12.8)
-----------
12
SQL> SELECT FLOOR(-12.8) FROM DUAL;
FLOOR(-12.8)
------------
-13
　　
　　1.3、MOD(n2,n1)：该函数返回n2除以n1所得的余数，如果n1为0
　　则返回n2

SQL> SELECT MOD(11,4) FROM DUAL;
MOD(11,4)
----------
3
SQL> SELECT MOD(11,0) FROM DUAL;
MOD(11,0)
----------
11
　　1.4、ROUND(n,integer)：该函数对数值n进行四舍五入运算后返回

SQL> SELECT ROUND(13.265,1) FROM DUAL;
ROUND(13.265,1)
---------------
13.3
SQL> SELECT ROUND(13.265,-1) FROM DUAL;
ROUND(13.265,-1)
----------------
10
SQL> SELECT ROUND(13.265,0) FROM DUAL;
ROUND(13.265,0)
---------------
13
SQL> SELECT ROUND(3.6) FROM DUAL;
ROUND(3.6)
----------
4
　　1.5、TRUNC(n1,n2)：该函数对数值n进行截断操作
　　

SQL> SELECT TRUNC(13.68,1) FROM DUAL;
TRUNC(13.68,1)
--------------
13.6
SQL> SELECT TRUNC(13.68,-1) FROM DUAL;
TRUNC(13.68,-1)
---------------
10
SQL> SELECT TRUNC(13.68) FROM DUAL;
TRUNC(13.68)
------------
13
SQL> SELECT TRUNC(13.68,0) FROM DUAL;
TRUNC(13.68,0)
--------------
13
　　
　　1.6、LOG(n2,n1)：该函数返回以n2为底n1的对数，其中n1必须
　　 是一个大于0的正数，n2必须是大于1的正数

SQL> SELECT LOG(10,100) FROM DUAL;
LOG(10,100)
-----------
2
SQL>
SQL> SELECT LOG(10,1) FROM DUAL;
LOG(10,1)
----------
0
　　1.7、POWER(n2,n1)：该函数用于求n2的n1次方
　　

SQL> SELECT POWER(3,3) FROM DUAL;
POWER(3,3)
----------
27
SQL>
SQL> SELECT POWER(-3,3) FROM DUAL;
POWER(-3,3)
-----------
-27
　　
　　 2、Character Functions Returning Character Values(字符函
　　数)：用于操作字符类型的函数并返回字符数据
　　
　　            常用的函数如下：
　　2.1、CONCAT(char1,char2)：该函数用于连接char1和char2
　　两个串，可嵌套使用
　　

SQL> SELECT CONCAT('Hello',' World') "message" FROM DUAL;
message
-----------
Hello World
SQL>
SQL> SELECT CONCAT(CONCAT(ename,'的工作是'),job) "job"
2  FROM emp
3  WHERE empno='7369';
job
---------------------------
SMITH的工作是CLERK
　　2.2、POWER(char)：该函数将字符串首字母变为大写
　　

SQL> SELECT INITCAP('HELLO WorLD') FROM DUAL;
INITCAP('HE
-----------
Hello World
　　2.3、LOWER(char)：该函数将字符串中的所有大写字母转化为
　　                                               小写字母
　　

SQL> SELECT LOWER('HELLO WORLD') FROM DUAL;
LOWER('HELL
-----------
hello world
　　2.4、UPPER(char)：该函数将字符串中的所有小写字母转化为
　　                                               大写字母
　　

SQL> SELECT LOWER('hello world') FROM DUAL;
LOWER('HELL
-----------
hello world
　　2.5、SUBSTR(char,position,substring_length)：该函数截
　　                     取一个char的字串，指定截取的开始位置，截取长度，如果不
　　指定截取长度则会从开始位置截取到字符串末尾，如果指定的
　　开始位置是一个正数，则从左边起第一个为1，如果指定的开
　　始位置是负数，则从右边起向左数第一个为-1
　　

SQL> SELECT SUBSTR('HELLO WORLD',7) FROM DUAL;
SUBST
-----
WORLD
SQL>
SQL> SELECT SUBSTR('HELLOWORLD',1,3) FROM DUAL;
SUB
---
HEL
SQL>
SQL> SELECT SUBSTR('HELLOWORLD',-3,3) FROM DUAL;
SUB
---
RLD
　　2.6、TRIM(trim_character FROM trim_source)：该函数从
　　trim_source字符串中消除指定的trim_character字符串
　　但只能消除开头和末尾的指定字符
　　

SQL> SELECT TRIM('H' FROM 'HELLO WORLD') FROM DUAL;
TRIM('H'FR
----------
ELLO WORLD
SQL> SELECT TRIM('D' FROM 'HELLO WORLD') FROM DUAL;
TRIM('D'FR
----------
HELLO WORL
 　　
　　2.7、LPAD(exp1,n,exp2)：该函数使用exp2表达式的值从左边
　　                     开始填充exp1表达式，填充后的总长度为n
　　

SQL> SELECT LPAD('WORLD',10,'*') FROM DUAL;
LPAD('WORL
----------
*****WORLD
　　2.8、RPAD(exp1,n,exp2)：该函数使用exp2表达式的值从右边
　　
                     开始填充exp1表达式，填充后的总长度为n

 　　

SQL> SELECT RPAD('WORLD',10,'*') FROM DUAL;
RPAD('WORL
----------
WORLD*****
 　　
　　
　　 3、Character Functions Returning Number Values(字符函
　　数)：用于操作字符类型的函数并返回数字类型的结果
　　
　　            常用的函数如下：
　　
　　
　　3.1、INSTR(string,substring,position,occurrence)：该函数
　　                        根据指定的position位置开始string中搜索第occurrence次                       出现substring的位置
　　
　　3.1.1、position：必须是一个数据类型为NUMBER的数或者
　　能够被隐式的转换为NUMBER类型，该值指明了
　　Oracle从string中搜索substring的起始位置，如果
　　                                指定的position是一个负数，那么Oracle将会从
　　                                string的最后一个字符开始计算起始位置（string的
　　                                最后一个字符索引为-1），如果不设置该position值
　　则默认值为1
　　
　　3.1.2、occurrence：必须是一个数据类型为NUMBER的数
　　或者可以被隐式转化为NUMBER类型，该值指明了
　　Oracle从string中查找第position次出现substring
　　的位置，如果不设置该occurrence值则默认值为1(
　　该occurrence的值必须大于0，否则会报参数超出范
　　                                围的错误)
　　
　　如果查找到指定的substring，则返回该字串第occurrence次出现
　　的位置，若不存在则返回0
　　

SQL> SELECT INSTRB('CORPORATE FLOOR','OR',3,2) "Substring occurrence index"
2     FROM DUAL;
Substring occurrence index
--------------------------
14
SQL> SELECT INSTRB('CORPORATE FLOOR','OR') "Substring occurrence index"
2     FROM DUAL;
Substring occurrence index
--------------------------
2
SQL> SELECT INSTRB('CORPORATE FLOOR','OR',-2) "Substring occurrence index"
2     FROM DUAL;
Substring occurrence index
--------------------------
14
 　　
　　3.2、INSTR(char)：该函数用于计算char字符串的长度，如果char是
　　null，则会返回一个null
　　

SQL> SELECT LENGTH('HELLO WORLD') "Length in char" FROM DUAL;
Length in char
--------------
11
SQL> SELECT LENGTH('') "Length in char" FROM DUAL;
Length in char
--------------

 　　 4、Datetime Functions(日期函数)：用于操作日期类型的数据 
　　
　　              常用的函数如下：
　　
　　4.1、ADD_MONTHS(date,integer)：该函数返回一个在date
　　日期上加上一个integer类型的月的日期类型，如果date这个
　　日期是本月的最后一天或者加上integer月后生成的这个月的
　　天数比date中月的天数少，则返回生成的新月份的最后一天(
　　                               比如：'31-1月-12'，31是本月的最后一天，如果让该日期加
　　上1个月则到了2月，2月有29天，则显示效果为'29-2月12')
　　否则，返回与date的天数相同的日期类型
　　

SQL> SELECT ADD_MONTHS('31-1月-12',1) "Next Month" FROM DUAL;
Next Month
--------------
29-2月 -12
SQL> SELECT ADD_MONTHS('23-1月-12',1) "Next Month" FROM DUAL;
Next Month
--------------
23-2月 -12
 　　
　　4.2、LAST_DAY(date)：该函数用于返回date这个日期中当前月
　　的最后一天所在的日期值
　　

SQL> SELECT LAST_DAY(SYSDATE) FROM DUAL;
LAST_DAY(SYSDA
--------------
31-3月 -12
　　
　　4.3、MONTHS_BETWEEN(date1,date2)：该函数返回date1                       到date2之间相隔的月份值，如果date1迟于date2则返回一
　　个正数，如果date1早于date2则返回一个负数；如果date1
　　                                  和date2是它们各自所在日期的月份中的同一天或者是各自
　　                                 月份中的最后一天，则会返回一个integer类型的数字，否则
　　会返回一个小数，Oracle数据库会以每月31天为基准来计算
　　这个小数代表的月份
　　
　　

SQL> SELECT MONTHS_BETWEEN(
2             TO_DATE('2012-06-12','YYYY-MM-DD'),
3             TO_DATE('2012-04-12','YYYY-MM-DD')
4  ) "Months"
5  FROM DUAL;
Months
----------
2
SQL> SELECT MONTHS_BETWEEN(
2             TO_DATE('2012-04-30','YYYY-MM-DD'),
3             TO_DATE('2012-03-31','YYYY-MM-DD')
4  ) "Months"
5  FROM DUAL;
Months
----------
1
SQL> SELECT MONTHS_BETWEEN(
2             TO_DATE('2012-04-23','YYYY-MM-DD'),
3             TO_DATE('2012-03-12','YYYY-MM-DD')
4  ) "Months"
5  FROM DUAL;
Months
----------
1.35483871
SQL> SELECT MONTHS_BETWEEN(
2             TO_DATE('2012-03-23','YYYY-MM-DD'),
3             TO_DATE('2012-05-12','YYYY-MM-DD')
4  ) "Months"
5  FROM DUAL;
Months
----------
-1.6451613
 　　
　　4.4、NEXT_DAY(date,char)：该函数用于返回date这个日期中
　　指定月份的下一个工作日所在的日期
　　

SQL> SELECT NEXT_DAY('02-2月-2001','星期二') "NEXT DAY"
2       FROM DUAL;
NEXT DAY
--------------
06-2月 -01
SQL> SELECT NEXT_DAY('04-3月-2012','星期日') "NEXT DAY"
2       FROM DUAL;
NEXT DAY
--------------
11-3月 -12
 　　
　　4.5、SYSDATE(date)：该函数返回当前操作系统的日期
　　

SQL> SELECT SYSDATE FROM DUAL;
SYSDATE
--------------
22-3月 -12
　　
　　4.6、ROUND(date,format)：该函数返回对当前date日期进行
　　格式处理后的日期：
　　
　　4.6.1、format的值为'YYYY'时，如果date日期中的月份
　　大于6月，则会将该date日期置为下一年的1月1日
　　否则，会将date日期置为该年的1月1日
　　
　　4.6.2、format的值为'MONTH'时，如果date日期中的天
　　                                  数大于15，则会将该date日期置为该年的该月的下
　　一个月的1号；否则，会将该date日期置为该年该月
　　的1号
　　

SQL> select round(to_date('2012-06-12','YYYY-MM-DD'),'YYYY') from dual;
ROUND(TO_DATE(
--------------
01-1月 -12
SQL> select round(to_date('2012-07-12','YYYY-MM-DD'),'YYYY') from dual;
ROUND(TO_DATE(
--------------
01-1月 -13
SQL> select round(to_date('2012-03-15','YYYY-MM-DD'),'MONTH') from dual;
ROUND(TO_DATE(
--------------
01-3月 -12
SQL> select round(to_date('2012-03-16','YYYY-MM-DD'),'MONTH') from dual;
ROUND(TO_DATE(
--------------
01-4月 -12
 　　
　　4.7、TRUNC(date,format)：该函数返回当前date日期进行格式
　　处理后的日期
　　
　　4.7.1、format值为'YYYY'时，此时会将该date日期置为该
　　                                 年的1月1日
　　
　　4.7.2、format值为'MONTH'时，此时会将date日期置为该
　　该年该月的1号
　　

SQL> SELECT TRUNC(TO_DATE('2012-04-13','YYYY-MM-DD'),'YYYY') FROM DUAL;
TRUNC(TO_DATE(
--------------
01-1月 -12
SQL> SELECT TRUNC(TO_DATE('2012-07-13','YYYY-MM-DD'),'YYYY') FROM DUAL;
TRUNC(TO_DATE(
--------------
01-1月 -12
SQL> SELECT TRUNC(TO_DATE('2012-03-13','YYYY-MM-DD'),'MONTH') FROM DUAL;
TRUNC(TO_DATE(
--------------
01-3月 -12
SQL> SELECT TRUNC(TO_DATE('2012-08-13','YYYY-MM-DD'),'MONTH') FROM DUAL;
TRUNC(TO_DATE(
--------------
01-8月 -12
 　　4.8、TO_CHAR(date,format)：该函数返回当前date日期进行
　　格式处理后的日期
　　

SQL> SELECT TO_CHAR(SYSDATE,'DD-MM-YYYY') FROM DUAL;
TO_CHAR(SY
----------
22-03-2012
SQL> SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD') FROM DUAL;
TO_CHAR(SY
----------
2012-03-22
SQL> SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH:SS') FROM DUAL;
TO_CHAR(SYSDATE,
----------------
2012-03-22 12:20
SQL> SELECT TO_CHAR(SYSDATE,'HH24:MI:SS AM') FROM DUAL;
TO_CHAR(SYSDA
-------------
00:49:25 上午
SQL> SELECT TO_CHAR(SYSDATE,'HH24:MI:SS AM') FROM DUAL;
TO_CHAR(SYSDA
-------------
00:49:30 上午
　　
　　
　　
　　 5、Conversion Functions(日期函数)：用于完成数据类型的转换
　　  转换的方式有一下两种：
　　5.1、显示转换：
　　5.2、隐式转换：
　　常用的转化函数如下：
　　
　　5.1.1、TO_NUMBER(expr,format)：使用指定的format格式
　　格式化expr，返回一个number值，该函数属于显示显示
　　转换函数

SQL> SELECT TO_NUMBER('300.0','9G999D99') FROM DUAL;
TO_NUMBER('300.0','9G999D99')
-----------------------------
300
　　5.1.2、TO_DATE(expr,format)：同4.8中，该函数属于显示
　　转换函数
　　
　　
　　 6、Generation Functions(通用函数)：这些函数适用于任何数据类
　　型，同时也适用于空值
　　


　　 6.1、NVL(expr1,expr2)：下面举例将emp表中得comm为空
　　
　　的值替换为0
　　

SQL> SELECT empno,ename,job,sal,NVL(comm,0) FROM EMP;
EMPNO ENAME      JOB              SAL NVL(COMM,0)
---------- ---------- --------- ---------- -----------
7369 SMITH      CLERK            800           0
7499 ALLEN      SALESMAN        1600         300
7521 WARD       SALESMAN        1250         500
7566 JONES      MANAGER         2975           0
7654 MARTIN     SALESMAN        1250        1400
7698 BLAKE      MANAGER         2850           0
7782 CLARK      MANAGER         2450           0
7788 SCOTT      ANALYST         3000           0
7839 KING       PRESIDENT       5000           0
7844 TURNER     SALESMAN        1500           0
7876 ADAMS      CLERK           1100           0
7900 JAMES      CLERK            950           0
7902 FORD       ANALYST         3000           0
7934 MILLER     CLERK           1300           0
已选择14行。
　　
　　 6.2、NVL2(expr1,expr2,expr3)：该函数对NVL进行了增强
　　 当expr1=null时，返回expr3;否则返回expr2
　　

SQL> SELECT NVL2(NULL,'A','B') FROM DUAL;
N
-
B
SQL> SELECT NVL2('HH','A','B') FROM DUAL;
N
-
A
　　
　　 6.3、NULLIF(expr1,expr2)：当expr1=expr2时，返回null
　　否则返回expr1
　　

SQL> SELECT NULLIF('A','A') FROM DUAL;
N
-

SQL> SELECT NULLIF('A','B') FROM DUAL;
N
-
A
　　
　　6.4、COALESCE(expr1,expr2)：如果expr1不为null，则显示
　　expr1否则显示expr2

SQL> SELECT COALESCE(NULL,'A') FROM DUAL;
C
-
A
SQL> SELECT COALESCE('S','C') FROM DUAL;
C
-
S
　　

SQL> select ename,sal,comm,coalesce(comm,sal) from emp;
ENAME             SAL       COMM COALESCE(COMM,SAL)
---------- ---------- ---------- ------------------
SMITH             800                           800
ALLEN            1600        300                300
WARD             1250        500                500
JONES            2975                          2975
MARTIN           1250       1400               1400
BLAKE            2850                          2850
CLARK            2450                          2450
SCOTT            3000                          3000
KING             5000                          5000
TURNER           1500          0                  0
ADAMS            1100                          1100
JAMES             950                           950
FORD             3000                          3000
MILLER           1300                          1300
已选择14行。
　　
　　
　　二、Aggregate Functions(统计函数)
　　
　　统计函数基于一组数据返回一个值，统计函数可以出现在SELECT列表、
　　ORDER BY 子句、HAVING子句   
　　
　　     常用的统计函数如下：
　　
　　

　　1、AVG(expr)：该函数用于求出指定的expr表达式的平均值，expr
　　必须是一个数字类型或者可以被隐式的转换为数字类型
　　


SQL> select deptno,avg(sal+nvl(comm,0))
2  from emp
3  group by deptno;
DEPTNO AVG(SAL+NVL(COMM,0))
---------- --------------------
30           1933.33333
20                 2191
10           2916.66667
    2、COUNT(expr)：该函数用于求出指定的expr表达式出现的次数　　


SQL> select deptno,count(*) "人数"
2  from emp
3  group by deptno
4  order by deptno;
DEPTNO       人数
---------- ----------
10          3
20          5
30          6
　　  3、MAX(expr)：该函数用于求出指定的expr表达式的最大值
　　


SQL> select empno,ename,job,max(sal) "最高工资"
2  from emp
3  where sal=(select max(sal) from emp)
4  group by empno,ename,job;
EMPNO ENAME      JOB         最高工资
---------- ---------- --------- ----------
7839 KING       PRESIDENT       5000
　　  4、MIN(expr)：该函数用于求出指定的expr表达式的最小值
　　


SQL> select empno,ename,job,min(sal) "最低工资"
2  from emp
3  where sal=(select min(sal) from emp)
4  group by empno,ename,job;
EMPNO ENAME      JOB         最低工资
---------- ---------- --------- ----------
7369 SMITH      CLERK            880
　　  5、SUM(expr)：该函数用于对给出的expr表达式求和
　　


SQL> select deptno,sum(sal+nvl(comm,0)) "总工资"
2  from emp
3  group by deptno;
DEPTNO     总工资
---------- ----------
30      11600
20      10955
10       8750