oracle-函数总结

Oracle函数

一 Oracle函数概述

       Oracle数据库的强大，体现在对用户管理，pl/sql编程，函数丰富。在Oracle 数据库中主要使用两种类型的函数：

单行函数：对每一个函数应用在表的记录中时，只能输入一行结果，返回一个结果，比如：MOD(x,y)返回x 除以y 的余数（x 和y 可以是两个整数，也可以是表中的整数列）。常用的单行函数有：

字符函数：对字符串操作。

数学函数：对数字进行计算，返回一个数字。

转换函数：可以将一种数据类型转换为另外一种数据类型。

日期函数：对日期和时间进行处理。

聚合函数：聚合函数同时可以对多行数据进行操作，并返回一个结果。比如 SUM(x)

返回结果集中 x 列的总合。

二 字符函数

字符函数接受字符参数，这些参数可以是表中的列，也可以是一个字符串表达式。

函数	说明	举例	结果
ascii(x)	返回字符x对应的ascii码值	select ascii ('A') from dual;	65
chr(n)	返回整数n对应的字符	select chr (65) from dual;	A
concat(x,y)	连接字符串x,y 连接字段x,y	select concat('hell','world') from dual; 或 select concat(empno,ename) from emp;	hellworld
initcap(str)	返回字符串并将字符串的第一个字母变成大写	select initcap(‘hello WORLD’) from dual;	Hello World
instr(str1,str2[,s,n])	在一个字符串中搜索指定的字符，返回发现指定的字符的位置； str1被搜索的字符串； str2希望搜索的字符串； s搜索的开始位置，默认为1； n第n次出现的位置，默认为1；	select instr('oracle training','ra',1,2) from dual;	9
lenghh(str)	返回字符串的长度	select ename,length(ename) from emp;
lower(str)	返回字符串，并将所有字符小写	select lower(ename) from emp;
upper(str)	返回字符串，并将所有字符大写	select upper('AaBbCcDd') from dual;	AABBCCDD
rpad(str1,x[,str2])	在列或字符的右边粘贴字符;x表示字符总长度	select rpad('hello', 10, '*') from dual;	hello*****
lpad(str1,x[,str2])	在列或字符的左边粘贴字符;x表示字符总长度	select lpad('hello', 10, '*') from dual;	*****hello
ltrim(str1 [,str2])	删除左边出现的字符串, 默认为空字符串	select ltrim('   hello   ') from dual; 或 select ltrim('hello world hello','hello') from dual;	hello       world hello
rtrim(str1[,str2])	删除右边出现的字符串, 默认为空字符串	select rtrim('   hello   ') from dual;或 select rtrim('hello world hello','hello') from dual;	hello     hello world
trim(str from string)	去处string左右两边的str字符	trim(0 from 0009872348900)	98723489
substr(str,s,n)	取子字符串，从s开始，取n个	substr('130123456789',3,8)	01234567
replace(str,st1,str2)	用str2替换str中的str1	replace('boll', 'l', 'd')	bodd

三 数学函数

函数	说明	举例	结果
ceil(x)	返回大于或等于x的最小整数	ceil(3.1415)	4
floor(x)	返回小于或等于x的最大整数	floor(3.987)	3
round(x ,y)	按照四舍五入精度进行舍入，y缺省为0	round(124.16666,-2) round(124.16666,2)	100 124.17
trunc(x,y)	按照指定的精度截取x，y缺省为0	trunc(124.16666,-2) trunc(124.16666,2)	100 124.16
abs(x)	返回x的绝对值	abs(100)  abs(-100)	100   100
exp(x)	返回e的x次方	exp(2)	7.38905609
ln(x)	返回x的自然对数	ln(2) ln(exp(2))	0.69314718 2
log(x,y)	返回以x为底,y的对数	Log(2,4)	2
power(x,y)	返回x的y次幂	power(2,3)	8
sqrt(x)	返回x的平方根	sqrt(4)	2
mod(x,y)	返回x除以y的余数	mod(8,3)	2
sign(x)	返回x的符号，大于0返回1，小于0返回-1，等于0返回0	sign(5)、sign(-5)、sign(0)	1,-1,0

四 日期函数

4.1 sysdate

解释：获取当前系统日期使用sysdate

示例：

select sysdate from dual;

4.2 add_months

语法：add_months(d,n)

参数：d日期类型，n增减的月份值

解释：返回在日期d基础上再加n个月后新的日期

示例：

select add_months(sysdate,2) from dual;

select hiredate,add_months(hiredate,2) from emp where ename='SMITH';

练习：

请查找最近半年入职的员工

select ename,hiredate from emp where add_months(hiredate,6)>=sysdate;

4.3 last_day

语法：last_day(d)

参数：d日期类型

解释：返回日期d所在月的最后一天；

示例：

select ast_day(sysdate) from dual;

4.4 months_between

语法：months_between(d2,d1)

参数：d2日期类型，d1日期类型

解释：给出d2-d1的月份，共有多少个月；

示例：

select months_between('19-12月-1999','19-3月-1999') mon_between from dual;

select months_between(to_date('2000-05-20','yyyy-mm-dd'),to_date('2005-05-20','yyyy-mm-dd')) from dual;

4.5 next_day

语法：next_day(d,w)

参数：d日期，w星期

解释：给出日期d和星期w之后计算下一个星期的日期；

示例：

select next_day('26-6月-2017','星期五') next_day from dual;

例子：

问题：查找已经入职8个月多的员工

select * from emp where sysdate>=add_months(hiredate,8);

问题：显示满10年服务年限的员工的姓名和受雇日期。

select ename, hiredate from emp where sysdate>=add_months(hiredate,12*10);

问题：对于每个员工，显示其加入公司的天数。（掌握）

select floor(sysdate-hiredate) "入职天数",ename from emp;

select trunc(sysdate-hiredate) "入职天数",ename from emp;

问题：找出各月倒数第3天受雇的所有员工。

select hiredate,ename from emp where last_day(hiredate)-2=hiredate;

五 转换类型函数

5.1 to_char

语法：to_char(d,formate)

参数：d日期类型或者数值类型，formate字符格式

解释：将日期类型或者数值类型转换成指定字符串格式

示例：

select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;

select to_char(sal,'$9999.99') from emp;

特别说明：

日期格式：

yy:两位数字的年份 2004--04

yyyy:四位数字的年份 2004年

mm:两位数字的月份 8月--08

dd:两位数字的天数 30号--30

hh24:二十四小时制8点--20

hh12:十二小时制8点--08

mi:显示分钟

ss:显示秒

day:显示星期几

数字格式：

9:显示数字，并忽略前面0

0:显示数字，如位数不足，则用0补齐

.:在指定位置显示小数点

,:在指定位置显示逗号

$:在数字前加美元符号

L:在数字前加本地货币符号

C:在数字前加国际货币符号

G:在指定位置显示组分隔符

D:在指定位置显示小数点符号(.)

说明：,逗号和.小数点可以合在一起使用，G分隔符和D小数点符可以合在一起使用，但,.不能和GD综合使用，否则报错。

5.2 to_date

语法：to_date(str,formate)

参数：str字符类型，formate日期格式

解释：将字符串转换成日期

示例：

select to_date('2004-05-07 1:23:44','yy-mm-dd hh:mi:ss') from dual;

5.3 to_number

语法：to_number(str)

参数：str字符类型

解释：将给出的字符转换为数字；

示例：select to_number('1999') year from dual;

 

例子：

问题：显示薪水的时候，把本地货币单位加在前面

select ename, to_char(hiredate, 'yyyy-mm-dd hh24:mi:ss'), to_char(sal,'L99999.99') from emp;

问题：显示1980年入职的所有员工

select * from emp where to_char(hiredate, 'yyyy')=1980;

问题：显示所有12月份入职的员工

select * from emp where to_char(hiredate, 'mm')=12;

六 聚合函数

聚合函数：一组值进行计算，并返回计算后的值，具有统计数据的作用。分组函数会自动忽略空值。

6.1 sum

语法：sum(distinct|all)

参数：all表示对所有的值 (缺省),distinct只对不同的值

解释：求总和

示例：

select sum(distinct sal) from emp;

select sum(all sal) from emp;

6.2 avg

语法：avg(distinct|all)

参数：all表示对所有的值 (缺省),distinct只对不同的值

解释：求平均值

示例：

select avg(distinct sal) from emp;

select avg(all sal) from emp;

6.3 max

语法：max(distinct|all)

参数：all表示对所有的值 (缺省),distinct只对不同的值

解释：求最大值

示例：

select max(distinct sal) from emp;

6.4 min

语法：min(distinct|all)

参数：all表示对所有的值 (缺省),distinct只对不同的值

解释：求最小值

示例：

select min(all sal) from emp;

6.5 count

语法：count(distinct|all)

参数：all表示对所有的值 (缺省),distinct只对不同的值(相同值只取一次)

解释：求记录、数据个数。记的是多少行

示例：

select count(sal) from emp;

select count(distinct sal) from emp;

七 分组查询

       在实际SQL应用中，经常需要进行分组聚合，即将查询对象按一定条件分组，然后对每一个组进行聚合分析。创建分组是通过GROUP BY子句实现的。与WHERE子句不同，GROUP BY子句用于归纳信息类型，以汇总相关数据。GROUP BY的作用是通过一定的规则将一个数据集划分成若干个小的区域，然后针对若干个小区域进行数据处理。

7.1 group by

语法：select 字段名,聚合函数()  

from 表名

group by 字段名;

解释：主要用来对一组数进行统计

示例：

求每个部门的平均工资，要求显示：部门号，部门的平均工资

select deptno ,avg(sal) from emp group by deptno;

说明：

在使用group by关键字时，在select列表中可以指定的项目是有限制的，select语句中仅许以下几项：

（1）被分组的列

（2）为每个分组返回一个值得表达式，例如用一个列名作为参数的聚合函数

group by 有一个原则，就是 select 后面的所有列中，没有使用聚合函数的列，必须出现在 group by 后面。

当GROUP BY子句中用于分组的列中出现NULL值时，将所有的NULL值分在同一组，即认为它们是“相等”的。

7.2 having

语法：SELECT column, SUM(column) 

FROM table

GROUP BY column

HAVING SUM(column) condition value

解释：

对分组统计再加限制条件进行筛选

示例：

select deptno,count(*),sum(sal)

from emp

group by deptno

having count(*)>=5;

说明： HAVING通常与GROUP BY子句同时使用。当然sum()也可以是其他聚合函数。

注意事项：

（1）聚合函数不能出现在from后面，where子句，group by后面，

        --聚合函数可以出现在select子句，having子句，order by子句

 （2）where子句中放的是普通的条件，不涉及到聚合函数的条件

 （3）having子句中不能放普通条件，必须放与聚合函数有关的条件

HAVING子句与WHERE子句的区别

having子句和where子句的相似之处在于，它也定义搜索条件。都是对查询结果的一种筛选。不同之处在于：

1、    where不能跟在group by子句后面，having是跟group by子句一起连用的，跟在其后面。

2、where条件中不能有聚合函数，而having可以有。

3、where子句只能接收from子句输出的数据，对其进行筛选；having对使用group by进行分组统计后的结果进行进一步的筛选。

八 语句执行顺序

select 列 from 表列表名/视图列表名

where 条件

group by (列)

having 条件

order by 列列表

执行顺序：

先where 再group 再having 再select 后order

九 案例分析

9.1 案例一

l  某公司印了一批充值卡，卡的密码是随机生成的，现在出现这个问题：

    卡里面的“O和0”（哦和零）“i和1”（哎和一），用户反映说看不清楚，公司决定，把存储在数据库中的密码中所有的“哦”都改成“零”，把所有的“i”都改成“1”；

       请编写SQL语句实现以上要求；

l  数据库表名：card

l  密码字段名：password

update card set password= replace(password,’o’,’0’);

update card set password= replace(password,’i’,’1’);

或

update card set password=replace(replace(password,’o’,’0’),’i’,’1’);

9.2 案例二

l  在数据库表中有以下字符数据，如：

       13-1、13-2、13-3、13-10、13-100、13-108、13-18、13-11、13-15、14-1、14-2

       现在希望通过SQL语句进行排序，并且首先要按照前半部分的数字进行排序，然后再按照后半部分的数字进行排需，输出要排成这样：

       13-1、13-2、13-3、13-10、13-11、13-15、13-18、13-100、13-108、14-1、14-2

l  数据库表名：sellRecord

l  字段名：listNumber

select listNumber

from sellRecord

order by

to_number(substr(listNumber,1, instr(listNumber,’-’)-1))

to_number(substr(listNumber, instr(listNumber,’-’)+1))