Oracle数据库教程
- 一、Oracle常用的系统函数
- 1、字符串函数：处理字符的函数
- 1.1 ASCII(c)函数和CHR(i)函数
- 1.2 CONCAT(s1,s2)函数
- 1.3 initcap()函数
- 1.4 instr(s1,s2,i,j)函数
- 1.5 length(s)函数
- 1.6 LOWER(s)函数和UPPER(s)函数
- 1.7 replace(s1,s2,s3)函数
- 1.8 substr(s,i,[j])函数
- 1.9 nvl(表达式1,表达式2)
- 2、数字类函数
- 2.1 ABS(n)
- 2.2 ROUND(n1,n2)函数
- 2.3 POWER(n,m)函数
- 3、日期和时间类函数
- 3.1 sysdate表示当前日期
- 3.2 add_months(d,i)
- 3.3 日期转换
- 3.4 获取日期时间
- 4、聚合类函数
一、Oracle常用的系统函数
1、字符串函数：处理字符的函数
1.1 ASCII©函数和CHR(i)函数
ASCII©: 返回一个字符的ASCII码,其中c表示一个字符;
CHR(i): 返回给出ASCII码值所对应的字符,i表示一个ASCII码值。
案例1:分别求得字符’D’,'Y’和空格的ASCII值
select ascii('D') D,ascii('Y') Y,ascii(' ') kg from dual;
D Y KG
---------- ---------- ----------
68 89 32
案例2：求得ASCII码值89和68对应的字符。
select CHR(89),CHR(68) from dual;
输出：
C C
- -
Y D
说明: dual表示Oracle系统内部提供的一个用于实现临时数据计算的特殊表,它只有一个列DUMMY,类型VARCHAR2(1)
1.2 CONCAT(s1,s2)函数
该函数将字符串s2连接到s1后面，如果s1为null,则返回s2; 如果s2为null,则返回s1;都为null,结果返回null。
案例:使用concat函数连接hello和world两个字符串
select concat('hello','world') from dual;
输出：
CONCAT('HE
----------
helloworld
1.3 initcap()函数
将单词第一个字母大写
Select INITCAP(‘hello’) from dual;
输出：
INITC
-----
Hello
例子:将雇员表中的雇员姓名变为开头字母大写。
select initcap(ename) from emp; --表的列不加引号
输出：
INITCAP(EN
----------
Smith
Allen
Ward
Jones
Martin
Blake
Clark
Scott
King
Turner
Adams
James
Ford
Miller
注：emp表下列值数据默认是大写，在使用initcap函数时，会把除了第一个字母全部小写。
1.4 instr(s1,s2,i,j)函数
该函数用于返回字符s2在字符串s1中第j次出现的位置。
说明:
搜索从字符串s1的第i个字符开始。当没有发现要查找到的字符时,返回0。
如果i为负数,搜索从右到左进行,但是函数返回的位置还是按从左到右来计算。s1和s2均为字符串,i和j为整数。
案例:在字符串‘Oracle 11g’中,从第三个字符开始查询字符串’1’第二次出现的位置
select instr('oracle 11g','1',3,2) abc from dual;
输出：
ABC
----------
9
1.5 length(s)函数
该函数返回字符串的长度
Select length(‘hello’) from dual;
select length('hello') from dual;
输出：
LENGTH('HELLO')
---------------
5
案例:通过使用length函数返回雇员名称长度大于5的雇员的员工编号,姓名以及所在的部门
select emp.empno,ename,dept.dname
from emp inner join dept
on emp.deptno=dept.deptno
where length(emp.ename)>5;
输出：
EMPNO ENAME DNAME
---------- ---------- --------------
7654 MARTIN SALES
7844 TURNER SALES
7934 MILLER ACCOUNTING
1.6 LOWER(s)函数和UPPER(s)函数
lower()函数将字符串变为小写字母
Select LOWER(‘HELLO WORLD’) from dual;
upper()函数将字符串变为大写字母
Select upper(‘hello world’) from dual;
练习：查询emp表中的员工姓名，全部小写显示。
select lower(ename) from emp;
1.7 replace(s1,s2,s3)函数
该函数使用s3字符串替换出现s1字符串中所有s2的字符串,并返回新的字符串。
select replace(' bad girl bad luck',‘ bad', 'good') from dual;
输出：
REPLACE('BADGIRLBAD
-------------------
good girl good luck
1.8 substr(s,i,[j])函数
该函数表示从字符串s的第i个位置开始截取长度为j的子字符串
案例:截取‘MessageBox’中从第8个位置截取长度为3的子字符串
select substr('MessageBox',8,3) from dual;
输出：
SUB
---
box
若不写第三个参数，默认字符串的截取从开始位置到字符串结束
select substr('MessageBox',5) from dual;
输出：
SUBSTR
------
agebox
1.9 nvl(表达式1,表达式2)
如果表达式1为null,则返回表达式2的值
select nvl(comm, 11) from emp;
输出：
NVL(COMM,11)
------------
11
300
500
11
1400
11
11
11
11
0
11
11
11
11
2、数字类函数
数字类函数主要用于执行各种数据的计算。
2.1 ABS(n)
返回n的绝对值
select ABS(-8) from dual;
2.2 ROUND(n1,n2)函数
该函数返回四舍五入小数点n2位的n1值,n1是小数。
案例1:返回PI的两位小数值
select round(3.1415926,3) from dual;
返回：
ROUND(3.1415926,3)
------------------
3.142
案例2：
select round(sal/20,2) from emp;
返回：
ROUND(SAL/20,2)
---------------
40
80
62.5
148.75
62.5
142.5
122.5
150
250
75
55
47.5
150
65
拓展: ceil, floor
2.3 POWER(n,m)函数
返回n的m次方,其中n和m都为实数
案例:计算2的3次方
select power(2,3) from dual;
3、日期和时间类函数
3.1 sysdate表示当前日期
select sysdate() as 系统日期 from dual;
3.2 add_months(d,i)
在指定日期d上加上指定月数i,求出之后的日期
案例:在当前的日期下加上6个月,并显示值
select ADD_MONTHS(sysdate,6) from dual;
3.3 日期转换
select to_date('2012-02-16','yyyy-mm-dd ') from dual;
3.4 获取日期时间
select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;
4、聚合类函数
• AVG(列名):求平均值
• SUM(列名):求和
• MAX(列名):求最大值
• MIN(列名):求最小值
• COUNT(列名):求查询结果中的记录数
案例:计算员工总数,使用avg计算工资,结果保留两位小数
• select count(empno),round(avg(sal),2) from emp;
输出：
COUNT(EMPNO) ROUND(AVG(SAL),2)
------------ -----------------
14 2073.21
