Oracle 学习第五篇
Day05-Oracle 常用函数
学习目标：
字符函数
数学函数
日期函数
转换函数
系统函数
聚合函数
Oralce函数概述
Oracle 数据库的强大，体现在对用户管理，pl/sql编程，函数丰富。
函数的分类：单行函数, 多行函数;
单行函数：对每个行输入值进行计算，得到相应的计算结果，返回给用户，也就是说，
每行作为一个输入参数，经过函数计算得到每行的计算结果。单行函数分为：字符函数、
数字函数、日期函数、转换函数、系统函数。比如length、to_date、to_char;
多行函数：对多行输入值进行计算，得到多行对应的单个结果。比如 max,min 等分组
函数；
字符函数
Ascii(掌握)
返回与指定的字符对应的十进制数；
SQL>select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual;
结果：
A A ZERO SPACE
---------- ---------- ---------- ----------
65 97 48 32
chr(掌握)
给出整数，返回对应的字符；
结果：
SQL>select chr(54740) zhao,chr(65) chr65 from dual;
ZHAO CHR65
---- -----
赵 A
concat(掌握)
连接两个字符串；
SQL>select concat('hello','world') from dual;
结果：
CONCAT('HELLO','WORLD')
-----------------------
Helloworld
特别说明：concat(字串 1 或字段 1,字串 2 或字段 2)也可以书写成 字串 1 或字段 1 || 字串 2 或字段 2
initcap(掌握)
返回字符串并将字符串的第一个字母变成大写；
SQL>select initcap('smith') upp from dual;
结果：
UPP
-----
Smith
instr(C1,C2,I,J) (掌握)
在一个字符串中搜索指定的字符，返回发现指定的字符的位置；
C1 被搜索的字符串；
C2 希望搜索的字符串；
I 搜索的开始位置，默认为 1；
J 第 j 次出现的位置，默认为 1；
SQL>select instr('oracle training','ra',1,2) instring from dual;
结果：
INSTRING
----------
9
length(掌握)
返回字符串的长度；
SQL>select ename, length(ename) , job , length(job), sal, length(to_char(sal))
from emp where ename='SMITH';
结果：
ENAME LENGTH(ENAME) JOB LENGTH(JOB) SAL LENGTH(TO_CHAR(SAL))
SMITH 5 CLERK 5 800.00 3
特别说明：在 oracle 中单个汉字、字母、还是特殊符号都认为是长度为 1
lower(掌握)
返回字符串，并将所有的字符小写；
SQL>select lower('AaBbCcDd') "lower-AaBbCcDd" from dual;
结果：
lower-AaBbCcDd
--------------
aabbccdd
upper(掌握)
返回字符串，并将所有的字符大写；
SQL>select upper('AaBbCcDd') "upper-AaBbCcDd" from dual;
结果：
upper-AaBbCcDd
--------------
AABBCCDD
rpad和 lpad(粘贴字符)
rpad 在列的右边粘贴字符 rpad('显示内容'或字段,显示长度,'填充占位符')
lpad 在列的左边粘贴字符 lpad('显示内容'或字段,显示长度,'填充占位符')
SQL>select lpad(rpad('htf',10,'*'),17,'*') from dual;
LPAD(RPAD('htf',10,'*'),17,'*'
------------------------------
*******htf*******
ltrim 和 rtrim
ltrim 删除左边出现的字符串 ltrim('原内容'或字段,'要删除的字符串')
rtrim 删除右边出现的字符串 rtrim('原内容'或字段,'要删除的字符串')
SQL>select ltrim(rtrim(' han teng fei ',' '),' ') from dual;
结果：
LTRIM(RTRIM('hantengfei',''),
------------------------------
han teng fei
SQL>select rtrim('**han teng fei**','*') from dual;
结果：
RTRIM('**HANTENGFEI**','*')
----------------------------
**han teng fei
SQL>select ltrim('**han teng fei**','*') from dual;
结果：
LTRIM('**HANTENGFEING**','*')
----------------------------
han teng fei**
substr(string,start,count) (掌握)
取子字符串，从 start 开始，取 count 个
SQL>select substr('13088888888',3,8) from dual;
结果：
SUBSTR('13088888888',3,8)
-------------------------
08888888
replace('string','s1','s2') (掌握)
string 希望被替换的字符或变量
s1 被替换的字符串
s2 要替换的字符串
SQL>select replace('he love you','he','i') from dual;
结果：
REPLACE('HELOVEYOU','HE','I')
-----------------------------
i love you
trim('s' from 'string')
如果不指定参数，默认为空格符。
SQL>select trim(0 from 0009872348900) "trim example" from dual;
结果：
trim example
------------
98723489
例子：
问题：将所有员工的名字按小写的方式显示
SQL> select lower(ename) from emp;
问题：将所有员工的名字按大写的方式显示。
SQL> select upper(ename) from emp;
问题：显示正好为 5个字符的员工的姓名。
SQL> select * from emp where length(ename)=5;
问题：显示所有员工姓名的前三个字符。
SQL> select substr(ename,1,3) from emp;
问题：以首字母大写,后面小写的方式显示所有员工的姓名。
SQL> select upper(substr(ename,1,1)) || lower(
substr(ename,2,length(ename)-1)) from emp;
问题：以首字母小写后面大写方式显示所有员工姓名。
SQL> select lower(substr(ename,1,1)) ||
upper(substr(ename,2,length(ename)-1)) from emp;
问题：显示所有员工的姓名，用“我是老虎”替换所有“A”
SQL> select replace(ename,'A', '我是老虎') from emp;
案例 1：
问题：
某公司印了一批充值卡，卡的密码是随机生成的，现在出现这个问题：卡里面的
“O和 0”（哦和零） “i和 1”（哎和一），用户反映说看不清楚，公司决定，把存储在数
据库中的密码中所有的“哦”都改成“零”，把所有的“i”都改成“1”；
请编写 SQL语句实现以上要求；数据库表名：Card；密码字段名：PassWord
分析：
1）这是更新语句，需要使用 UPDATE语句
2）牵涉到字符串的替换，需要使用到 Oracle中的函数 Replace
答案：
1）UPDATE Card SET PassWord = Replace(PassWord ,'O','0')
UPDATE Card SET PassWord = Replace(PassWord ,'i','1')
2）UPDATE Card SET PassWord =
Replace(Replace(PassWord ,'O','0'),'i','1')
案例 2：
问题：
在数据库表中有以下字符数据，如：
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
请编写 SQL语句实现以上要求；数据库表名：SellRecord；字段名：ListNumber
分析：
1）查询语句：使用 SELECT语句
2）排序：ORDER BY：在 ORDER BY的排序列中，需要重新计算出排序的数字
3）前半部分的数字：
找到“-”符号的位置
取其左半部分
使用 Convert函数将其转换为数字：
to_number(substr(list_number,1,instr(list_number,'-')-1))
4）后半部分的数字：
找到“-”符号的位置
把从第一个位置到该位置的全部字符替换为空格
使用 Convert函数将其转换为数字：
to_number(substr(list_number,instr(list_number,'-')+1))
答案：
SELECT ListNumber
FROM SellRecord
ORDER BY to_number(substr(list_number,1,instr(list_number,'-')-1)),
to_number(substr(list_number,instr(list_number,'-')+1))
数学函数
ceil（向上取整）(掌握)
返回大于或等于给出数字的最小整数；
SQL>select ceil(3.14159265) from dual;
结果：
CEIL(3.14159265)
----------------
4
floor（向下取整）(掌握)
对给定的数字取整数；
SQL>select floor(2345.67) from dual;
结果：
FLOOR(2345.67)
--------------
2345
trunc(掌握)
按照指定的精度截取一个数；
SQL>select trunc(124.1666,-2),trunc(124.16666,2) from dual;
结果：
TRUNC(124.1666,-2) TRUNC(124.16666,2)
------------------ ------------------
100 124.16
round 和 trunc(掌握)
按照指定的精度进行舍入；
round 函数为四舍五入
trunc（直接截取）
SQL>select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual;
结果：
ROUND(55.5) ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5)
----------- ------------ ----------- ------------
56 -55 55 -55
abs
返回指定值的绝对值；
SQL>select abs(100),abs(-100) from dual;
结果：
ABS(100) ABS(-100)
---------- ----------
100 100
以下为了解内容（知道有）
acos
给出反余弦的值；
SQL>select acos(-1) from dual;
结果：
ACOS(-1)
----------
3.14159265
asin
给出反正弦的值；
SQL>select asin(0.5) from dual;
ASIN(0.5)
----------
0.52359877
atan
返回一个数字的反正切值；
SQL>select atan(1) from dual;
ATAN(1)
----------
0.78539816
cos
返回一个给定数字的余弦；
SQL>select cos(-3.14159265) from dual;
COS(-3.14159265)
----------------
-1
cosh
返回一个数字反余弦值；
SQL>select cosh(20) from dual;
COSH(20)
----------
242582597.
exp
返回一个数字 e的 n 次方根；
SQL>select exp(2),exp(1) from dual;
EXP(2) EXP(1)
---------- ----------
7.38905609 2.71828182
ln
返回一个数字的对数值；
SQL>select ln(1),ln(2),ln(2.7182818) from dual;
LN(1) LN(2) LN(2.7182818)
---------- ---------- -------------
0 0.69314718 0.99999998953
log(n1,n2)
返回一个以 n1 为底 n2 的对数；
SQL>select log(2,1),log(2,4) from dual;
LOG(2,1) LOG(2,4)
---------- ----------
0 2
mod(n1,n2)
返回一个 n1 除以 n2 的余数；(取模函数)
SQL>select mod(10,3),mod(3,3),mod(2,3) from dual;
MOD(10,3) MOD(3,3) MOD(2,3)
---------- ---------- ----------
1 0 2
power
返回 n1的 n2 次方根；
SQL>select power(2,10),power(3,3) from dual;
POWER(2,10) POWER(3,3)
----------- ----------
1024 27
sign
取数字 n 的符号，大于 0 返回 1，小于 0 返回-1，等于 0 返回 0；
SQL>select sign(123),sign(-100),sign(0) from dual;
SIGN(123) SIGN(-100) SIGN(0)
---------- ---------- ----------
1 -1 0
sin
返回一个数字的正弦值；
SQL>select sin(1.57079) from dual;
SIN(1.57079)
------------
0.9999999999
sinh
返回双曲正弦的值；
SQL>select sin(20),sinh(20) from dual;
SIN(20) SINH(20)
---------- ----------
0.91294525 242582597.
sqrt
返回数字 n 的根；
SQL>select sqrt(64),sqrt(10) from dual;
SQRT(64) SQRT(10)
---------- ----------
8 3.16227766
tan
返回数字的正切值；
SQL>select tan(20),tan(10) from dual;
TAN(20) TAN(10)
---------- ----------
2.23716094 0.64836082
tanh
返回数字的 n 的双曲正切值；
SQL>select tanh(20),tan(20) from dual;
TANH(20) TAN(20)
---------- ----------
1 2.23716094
日期函数
介绍 :日期函数用于处理 date类型的数据。
默认情况下日期格式是 dd-mon-yy 即 12-7月-78
add_months(掌握)
add_months(日期值,增加(减少)值)
增加或减去月份；
SQL>select to_char(add_months(to_date('199912','yyyymm'),2),'yyyymm') from dual;
结果：
TO_CHAR(ADD_MONTHS(TO_DATE('19
------------------------------
200002
SQL>select hiredate,add_months(hiredate,2) from emp where ename='SMITH';
结果：
HIREDATE ADD_MONTHS(HIREDATE,2)
----------- ----------------------
1980/12/17 1981/2/17
请查找最近 350个月入职的员工
SQL>select ename,hiredate from emp where add_months(hiredate,350)>=sysdate;
结果：
ENAME HIREDATE
---------- -----------
SCOTT 1987/4/19
ADAMS 1987/5/23
last_day(掌握)
返回日期的最后一天；
SQL>select to_char(sysdate,'yyyy-mm-dd'),to_char((sysdate)+1,'yyyy-mm-dd') from dual;
结果：
TO_CHAR(SYSDATE,'YYYY-MM-DD') TO_CHAR((SYSDATE)+1,'YYYY-MM-DD
结果：
----------------------------- ------------------------------
2017-03-01 2017-03-02
SQL>select to_char(last_day(sysdate),'yyyy-mm-dd') from dual;
结果：
TO_CHAR(LAST_DAY(SYSDATE),'YYYY-MM-DD
------------------------------
2017-03-31
months_between(date2,date1) (掌握)
给出 date2-date1 的月份，共有多少个月；
例子：
SQL>select months_between('19-12 月-1999','19-3 月-1999') mon_between from dual;
结果：
MON_BETWEEN
-----------
9
SQL>select months_between(to_date('2000-05-20','yyyy-mm-dd'),to_date('2005-05-20','yyyy-mm-dd'))
mon_betw from dual;
结果：
MON_BETW
----------
-60
next_day(date,'day') (掌握)
给出日期 date 和星期 X（day）之后计算下一个星期的日期；
SQL>select next_day('1-3月-2017','星期五') next_day from dual;
结果：
NEXT_DAY
-----------
2017/3/3
sysdate(掌握)
用来得到系统的当前日期；
SQL>select to_char(sysdate,'day') from dual;
结果：
TO_CHAR(SYSDATE,'DAY')
----------------------
星期四
例子：
问题：查找已经入职 8 个月多的员工
SQL> select * from emp where sysdate>=add_months(hiredate,8);
问题：显示满 10年服务年限的员工的姓名和受雇日期。
SQL> select ename, hiredate from emp
where sysdate>=add_months(hiredate,12*10);
问题：对于每个员工，显示其加入公司的天数。 （掌握）
SQL> select floor(sysdate-hiredate) "入职天数",ename from emp;
SQL> select trunc(sysdate-hiredate) "入职天数",ename from emp;
问题：找出各月倒数第 3 天受雇的所有员工。
SQL> select hiredate,ename from emp
where last_day(hiredate)-2=hiredate;
转换类型函数
to_char(date,'format') (掌握)
日期类型转换成字符串格式(主要用于将日期以习惯的格式输出显示)
SQL>select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;
结果：
TO_CHAR(SYSDATE,'YYYY/MM/DDHH2
------------------------------
2014/04/24 16:19:34
to_char(掌握)
可以使用 select ename,hiredate,sal from emp where deptno=10;
显示信息，可是在某些情况下，这个并不能满足你的需求。
日期是否可以显示时/分/秒？
select to_char(hiredate,'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显示星期几
month 显示几月
year 显示年
数字格式：
9:显示数字，并忽略前面 0
0:显示数字，如位数不足，则用 0 补齐
.:在指定位置显示小数点
,:在指定位置显示逗号
$:在数字前加美元符号
L:在数字前加本地货币符号
C:在数字前加国际货币符号
G:在指定位置显示组分隔符
D:在指定位置显示小数点符号(.)
说明：,逗号.和小数点可以合在一起使用，G分隔符和 D 小数点符可以合在一起使用，但,.不能和 GD
综合使用，否则报错。
to_date(string,'format')
将字符串转换成日期(主要用于将日期按习惯的格式输入到 oracle 数据库中)
to_number(掌握)
将给出的数字类型的字符转换为数字；
SQL>select to_number('1999') year from dual;
结果：
YEAR
----------
1999
例子：
问题：显示薪水的时候，把本地货币单位加在前面
SQL> select ename, to_char(hiredate, 'yyyy-mm-dd hh24:mi:ss'),
to_char(sal,'L99999.99') from emp;
问题：显示 1980年入职的所有员工
SQL> select * from emp where to_char(hiredate, 'yyyy')=1980;
问题：显示所有 12月份入职的员工
SQL> select * from emp where to_char(hiredate, 'mm')=12;
系统函数
decode 函数类似于 java的switch case 分支语句
SQL>select ename||decode(deptno,
10,'在 10号部门',
20,'在 20号部门',
30,'在 30号部门')
from emp where ename='SCOTT' order by deptno;
结果：
ENAME||DECODE(DEPTNO,10,'在 10?
------------------------------
SCOTT在 20 号部门
聚合函数
聚合函数：一组值进行计算，并返回计算后的值 ，具有统计数据的作用(必须掌握)
avg(distinct|all)
all表示对所有的值求平均值,distinct只对不同的值求平均值
SQL>select chr(54740) zhao,chr(65) chr65 from dual;
AVG(DISTINCTSAL)
----------------
2064.58333333333
SQL> select avg(all sal) from emp;
AVG(ALLSAL)
-----------
2073.214285
max(distinct|all)
求最大值,ALL 表示对所有的值求最大值,DISTINCT 表示对不同的值求最大值,相同的
只取一次
SQL>select max(distinct sal) from emp;
MAX(DISTINCTSAL)
----------------
5000
min(distinct|all)
求最小值,ALL 表示对所有的值求最小值,DISTINCT 表示对不同的值求最小值,相同的
只取一次
SQL>select min(all sal) from emp;
MIN(ALLSAL)
-----------
800
COUNT(DISTINCT|ALL)
求记录、数据个数。 ALL对所有记录，数组做统计， DISTINCT只对不同值统计(相同
值只取一次)
SELECT COUNT(SAL) FROM EMP;
COUNT(SAL)
----------
14
SELECT COUNT(DISTINCT SAL) FROM EMP;
COUNT(DISTINCTSAL)
------------------
12
/*********************************************************/
分组查询
group by
主要用来对一组数进行统计
SQL> select deptno,count(*),sum(sal) from emp group by deptno;
DEPTNO COUNT(*) SUM(SAL)
------ ---------- ----------
30 6 9400
20 5 10875
10 3 8750
分组后过滤
having
对分组统计再加限制条件
SQL> select deptno,count(*),sum(sal) from emp group by deptno having
count(*)>=5;
DEPTNO COUNT(*) SUM(SAL)
------ ---------- ----------
30 6 9400
20 5 10875
SQL> select deptno,count(*),sum(sal) from emp group by
deptno having count(*)>=5;
DEPTNO COUNT(*) SUM(SAL)
------ ---------- ----------
30 6 9400
20 5 10875
1、H3C交换机配置命令大全
2、激活Win10内置版Linux （ubuntu）
3、敏捷开发
4、E. K Balanced Teams
5、Lambda表达式