oracle之SQL的基本函数
SQL的基本函数
2.1 单行函数与多行函数
单行函数：指一行数据输入，返回一个值的函数。所以查询一个表时，对选择的每一行数据都返回一个结果。
SQL>select empno,lower(ename) from emp;
多行函数：指多行数据输入，返回一个值的函数。所以对表的群组进行操作，并且每组返回一个结果。（典型的是聚合函数）
SQL>select sum(sal) from emp;
2.2 单行函数的几种类型
2.2.1 字符函数
lower('SQL Course')----->sql course 返回小写
upper('sql course')----->SQL COURSE 返回大写
initcap('SQL course')-----> Sql Course 返回首字母大写
concat('good','string')---->good string 拼接 //只能拼接2个字符串
substr('String',1,3)---->Str 从第1位开始截取3位数
instr('t#i#m#r#a#n#','#',3) --->从第3位起始找#字符在那个绝对位置
length('String')---->6 长度
lpad('first',10,'$')左填充
rpad(676768,10,'*')右填充
replace('JACK and JUE','J','BL')---->BLACK and BLUE
trim('m' from 'mmtimranm')---->timran
2.2.2 数值函数
round 对指定的值做四舍五入,round(p,s) s为正数时，表示小数点后要保留的位数，s也可以为负数，但意义不大。
round:按指定精度对十进制数四舍五入,如:round(45.923, 1),结果,45.9
round(45.923, 0),结果,46
round(45.923, -1),结果,50
trunc 对指定的值取整 trunc(p,s)
trunc:按指定精度截断十进制数,如:trunc(45.923, 1),结果,45.9
trunc(45.923),结果,45
trunc(45.923, -1),结果, 40
mod 返回除法后的余数
SQL> select mod(100,12) from dual;
MOD(100,12)
-----------
4
2.2.3 日期函数
因为日期在oracle里是以数字形式存储的，所以可对它进行加减运算，计算是以天为单位。
缺省格式：DD-MON-RR.
可以表示日期范围：（公元前）4712 至（公元）9999
时间格式
SQL>select to_date('2003-11-04 00:00:00' ,'YYYY-MM-DD HH24:MI:SS') FROM dual;
SQL> select sysdate+2 from dual; //当前时间+2day
SQL> select sysdate+2/24 from dual; //当前时间+2hour
SQL> select (sysdate-hiredate)/7 week from emp; //两个date类型差，结果是以天为整数位的实数。
MONTHS_BETWEEN //计算两个日期之间的月数
SQL>select months_between('1994-04-01','1992-04-01') mm from dual;
查找emp表中参加工作时间>30年的员工
SQL>select * from emp where months_between(sysdate,hiredate)/12>30;
考点：很容易认为单行函数返回的数据类型与函数类型一致，对于数值函数类型而言的确如此，但字符和日期函数可以返回任何数据类型的值。比如instr函数是字符型的，months_between函数是日期型的，但它们返回的都是数值。
ADD_MONTHS //给日期增加月份
SQL>select add_months('1992-03-01',4) am from dual;
LAST_DAY //日期当前月份的最后一天
SQL>select last_day('1989-03-28') l_d from dual;
NEXT_DAY //NEXT_DAY的第2个参数可以是数字1-7，分别表示周日--周六(考点），比如要取下一个星期六，则应该是：
SQL>select next_day(sysdate,7) FROM DUAL;
ROUND(p,s)，TRUNC(p,s)在日期中的应用，如何舍入要看具体情况，s是MONTH按30天计，应该是15舍16入，s是YEAR则按6舍7入计算。
SQL>
SELECT empno, hiredate,
round(hiredate,'MONTH') AS round,
trunc(hiredate,'MONTH') AS trunc
FROM emp
WHERE empno=7844;
SQL>
SELECT empno, hiredate,
round(hiredate,'YEAR') AS round,
trunc(hiredate,'YEAR') AS trunc
FROM emp
WHERE empno=7839;
2.2.4 几个有用的函数
1）decode函数和case表达式：
实现sql语句中的条件判断语句，具有类似高级语言中的if语句的功能。
decode函数源自oracle, case表达式源自sql标准，实现功能类似，decode语法更简单些。
decode函数用法：
SQL>
SELECT job, sal,
DECODE(job, 'ANALYST', SAL*1.1,
'CLERK', SAL*1.15,
'MANAGER', SAL*1.20,
SAL)
REVISED_SALARY
FROM emp
/
case表达式第一种用法：
SQL>
select job, sal,case job
when 'ANALYST' then SAL*1.1
when 'CLERK' then SAL*1.15
when 'MANAGER' then SAL*1.20
else sal end
REVISED_SALARY
from emp
/
case表达式第二种用法：
SQL>
select job, sal,
case when job='ANALYST' then SAL*1.1
when job='CLERK' then SAL*1.15
when job='MANAGER' then SAL*1.20
else sal end
REVISED_SALARY
from emp
/
以上三种写法结果都是一样的：
JOB SAL REVISED_SALARY
--------- ---------- --------------
CLERK 800 920
SALESMAN 1600 1600
SALESMAN 1250 1250
MANAGER 2975 3570
SALESMAN 1250 1250
MANAGER 2850 3420
MANAGER 2450 2940
ANALYST 3000 3300
PRESIDENT 5000 5000
SALESMAN 1500 1500
CLERK 1100 1265
CLERK 950 1092.5
ANALYST 3000 3300
CLERK 1300 1495
case第二种语法比第一种语法增加了搜索功能。形式上第一种when后跟定值，而第二种还可以使用比较符。看一个例子
SQL>
select ename,sal,
case when sal>=3000 then '高级'
when sal>=2000 then '中级'
else '低级' end
级别
from emp
/
ENAME SAL 级别
---------- ---------- ----
SMITH 800 低级
ALLEN 1600 低级
WARD 1250 低级
JONES 2975 中级
MARTIN 1250 低级
BLAKE 2850 中级
CLARK 2450 中级
SCOTT 3000 高级
KING 5000 高级
TURNER 1500 低级
ADAMS 1100 低级
JAMES 950 低级
FORD 3000 高级
MILLER 1300 低级
2）DISTINCT(去重)限定词的用法： //distinct貌似多行函数，严格来说它不是函数而是select子句中的关键字。
SQL> select distinct job from emp; //消除表行重复值。
JOB
---------
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST
SQL> select distinct job,deptno from emp; //重复值是后面的字段组合起来考虑的
JOB DEPTNO
--------- ----------
MANAGER 20
PRESIDENT 10
CLERK 10
SALESMAN 30
ANALYST 20
MANAGER 30
MANAGER 10
CLERK 30
CLERK 20
3）CHR()函数和ASCII()函数
chr()函数将ASCII码转换为字符： ASCII码 –》 字符
ascii()函数将字符转换为ASCII码：字符 –》 ASCII码
在oracle中chr()函数和ascii()是一对反函数。
求字符对应的ASCII值
SQL> select ASCII('A') FROM dual;
ASCII('A')
----------
65
SQL> select chr(65) from dual;
C
-
A
4) sys_context 获取环境上下文的函数（很有用）
scott远程登录
select SYS_CONTEXT('USERENV','IP_ADDRESS') from dual;
--------------------------------------------------------------------------------
192.168.0.136
SQL> select sys_context('userenv','sid') from dual;
SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------------
129
SQL> select sys_context('userenv','terminal') from dual;
SYS_CONTEXT('USERENV','TERMINAL')
--------------------------------------------------------------------------------
TIMRAN-222C75E5
5）处理空值的几种函数（见第四章）
6）转换函数TO_CHAR、TO_DATE、TO_NUMBER （见第三章）
