oracle–单行函数和多行函数
- 2019 年 10 月 10 日
- 笔记
单行函数
1、字符函数
|
函 数 |
功 能 |
示 例 |
结 果 |
|---|---|---|---|
|
INITCAP (char) |
首字母大写 |
initcap ('hello') |
Hello |
|
LOWER (char) |
转换为小写 |
lower ('FUN') |
fun |
|
UPPER (char) |
转换为大写 |
upper ('sun') |
SUN |
|
LTRIM (char, set) |
左剪裁 |
ltrim ('xyzadams', 'xyz') |
adams |
|
RTRIM (char, set) |
右剪裁 |
rtrim ('xyzadams', 'ams') |
xyzad |
|
TRANSLATE (char, from, to) |
按字符翻译 |
translate ('jack', 'abcd', '1234') |
j13k |
|
REPLACE (char, search_str, replace_str) |
字符串替换 |
replace ('jack and jue', 'j', 'bl') |
black and blue |
|
INSTR (char, substr[, pos]) |
查找子串位置 |
instr ('worldwide', 'd') |
5 |
|
SUBSTR (char, pos, len) |
取子字符串 |
substr ('abcdefg',3,2) |
cd |
|
CONCAT (char1, char2) |
连接字符串 |
concat ('Hello', 'world') |
Helloworld |
2、数值函数
|
函 数 |
功 能 |
示 例 |
结 果 |
|---|---|---|---|
|
ABS(n) |
取绝对值 |
abs(-15) |
15 |
|
CEIL(n ) |
向上取整 |
ceil(44.778) |
45 |
|
SIN(n) |
正弦 |
sin(1.571) |
.999999979 |
|
COS(n) |
余弦 |
cos(0) |
1 |
|
SIGN(n) |
取符号 |
sign(-32) |
-1 |
|
FLOOR(n) |
向下取整 |
floor(100.2) |
100 |
|
POWER(m,n ) |
m的n次幂 |
power(4,2) |
16 |
|
MOD(m,n) |
取余数 |
mod(10,3) |
1 |
|
ROUND(m,n) |
四舍五入 |
round(100.256,2) |
100.26 |
|
TRUNC(m,n) |
截断 |
trunk(100.256,2) |
100.25 |
|
SQRT(n) |
平方根 |
sqrt(4) |
2 |
3、日期函数
|
函 数 |
功 能 |
示 例 |
结 果 |
|---|---|---|---|
|
MONTHS_BETWEEN |
返回两个日期间的月份 |
months_between ('04-11月-05','11-1月-01') |
57.7741935 |
|
ADD_MONTHS |
返回把月份数加到日期上的新日期 |
add_months('06-2月-03',1) add_months('06-2月-03',-1) |
06-3月-03 06-1月-03 |
|
NEXT_DAY |
返回指定日期后的星期对应的新日期 |
next_day('06-2月-03','星期一') |
10-2月-03 |
|
LAST_DAY |
返回指定日期所在的月的最后一天 |
last_day('06-2月-03') |
28-2月-03 |
|
ROUND |
按指定格式对日期进行四舍五入 |
round(to_date('13-2月-03'),'YEAR') round(to_date('13-2月-03'),'MONTH') round(to_date('13-2月-03'),'DAY') |
01-1月-03 01-2月-03 16-2月-03 |
|
TRUNC |
对日期按指定方式进行截断 |
trunc(to_date('06-2月-03'),'YEAR') trunc(to_date('06-2月-03'),'MONTH') trunc(to_date('06-2月-03'),'DAY') |
01-1月-03 01-2月-03 02-2月-03 |
4、转换函数
|
日期格式元素 |
含义 |
|---|---|
|
YYYY、YY |
代表四位、两位数字的年份 |
|
MM |
用数字表示的月份 |
|
MON |
月份的缩写、对中文月份来说就是全称 |
|
DD |
数字表示的日 |
|
DY |
星期的缩写，对中文的星期来说就是全称 |
|
HH24，HH12 |
12小时或者24小时进制下的时间 |
|
MI |
分钟数 |
|
SS |
秒数 |
|
数字格式元素 |
含义 |
|---|---|
|
9 |
代表一位数字，如果该位没有数字则不进行显示，但对于小数点后面的部分仍会强制显示 |
|
0 |
代表一位数字，如果该位没有数字则强制显示0 |
|
$ |
显示美元符号 |
|
L |
显示本地货币符号 |
|
. |
显示小数点 |
|
, |
显示千分位符号 |
其他函数
nvl() nvl2() decode()
|
函 数 |
功 能 |
|---|---|
|
NVL(EXP1, EXP2) |
如果exp1的值为null，则返回exp2的值，否则返回exp1的值 |
|
NVL2(EXP1, EXP2, EXP3) |
如果exp1的值为null，则返回exp2的值，否则返回exp3的值 |
|
DECODE(VALUE,IF1,THEN1, IF2,THEN2,……,ELSE) |
如果value的值为if1，则返回then1的值，如果value的值为if2，则返回then2的值，……，否则返回else值 |
多行函数
单行函数学习 --查询工作为SALESMAN，MANAGER并且工资大于2500的员工信息 --1、使用小括号提升where筛选条件的执行优先级别 --2、and的优先级别高于or select * from emp select * from emp where job='SALESMAN' or job='MANAGER' and sal>2500 select * from emp where (job='SALESMAN' or job='MANAGER') and sal>2500 ----------------------------------------------------------------------------------------------------------- 使用函数 单行函数 多行函数 转换函数 其他函数 --单行函数学习：不改变原始数据，只改变结果 ---1、字符函数 select * from emp; select INITCAP(ename) from emp;--initcap函数将首字母大写 select lower(ename)from emp;--lower 字母小写 select replace(ename,'S','M') from emp;--replace 替换 --2、数值函数--Math ----伪表 dual select * from dual select abs(-3) 绝对值，ceil(3.1415926)向上取整,floor(3.1415926)向下取整,power(2,3)幂,round(3.4)四舍五入 from dual --3、日期函数 select months_between('13-12月-2016','13-10月-2016') from dual--months_between两个日期之间的月份数 多行函数学习 多行函数 ： max: max(字段名) 返回此字段的最大值 min：min(字段名) 返回此字段的最小值 avg：avg(字段名) 返回平均值 sum：sum（字段名） 返回字段的和 count:count --count(*)，用来查询表中有多少条记录 --count（字段名），用来查询某个字段有值的个数 --count(distinct 字段名),可以先去除重复再计数。 注意： --多行函数不能和普通字段直接出现在查询语句中，除非group by --多行函数和单行函数不能直接出现在查询语句中，除非group by 查看员工的最高工资 select max(sal),ename from emp--多行函数不能和普通字段直接出现在查询语句中，除非group by select max(sal)，lower(ename) from emp--多行函数和单行函数不能直接出现在查询语句中，除非group by select ename from emp 查看员工的最低工资 select min(sal) from emp 查看员工的平均工资 select avg(sal) from emp 查看所有的员工工资之和 select sum(sal) from emp 查询公司有多少员工 select * from emp select count(*) from emp--使用count(*)来查看一张表中有多少条记录 查询有津贴的员工人数 select count(comm) from emp--使用count(字段名),查询该字段有值的记录数 select count(ename) from emp 查询公司有多少工作种类 select count(job) from emp select count(distinct job) from emp
转换函数学习 转换函数:在转换的时候改变的是数据的类型，数据内容不会改变，可以指定格式。 1、to_number：将数字字符转换为数字类型的数值，to_number(数字字符) 2、to_char：将数字转换为字符类型，将日期转换为字符类型 to_char(数字/日期) 3、to_date：将字符类型的日期转换为日期类型： to_date(char) -------------------------------------------------------------------------------------- number-->char 转换的时候使用的是默认格式， select to_char(123) from dual--将数字转换为字符类型，使用to_char(number) select sal, to_char(sal) from emp number-->char 使用指定的格式 to_char(number,'格式')，格式： $代表美元符，9代码数字占位。例如，L999,999,999表示三位一组的显示方式L代表人民币符号，0代表保留数字个数，不足使用0补充。 select to_char(sal),to_char(sal,'L999,999,999') from emp select to_char(sal),to_char(sal,'L0000.00') from emp char--->number to_number(数字字符) select to_number('123') from dual char--->date，转换的字符必须是日期格式的字符串，默认格式 dd-mm-yyyy 注意：因为字符串有很多，所以在字符转换为日期的时候，需要指定格式，因为日期是具备一定格式的字符组合。 字符转换为日期的时候，指定的格式为字符的日期顺序，无需指定间隔符。 yyyy：表示年 mm:表示月 dd表示日 select to_date('05-12月-2016') from dual; select to_date('2016-05-12','yyyy-mm-dd') from dual--使用指定的格式将指定的日期字符串转换为日期 select to_date('12-05-2016','mm/dd/yyyy') from dual date--->char 注意：因为日期本身就具备一定的格式在不是指定格式的情况下会默认使用dd-mm-yyyy格式显示数据 指定的格式会作为日期转换为字符串类型的显示格式存在。例如： yyyy-mm-dd' ‘2016-12-05’ yyyy/mm/dd' '2016/12/05' yyyy"年"mm"月"dd"日"' 2016年12月05日 select hiredate,to_char(hiredate) from emp--日期转换为字符的时候，不指定格式使用默认格式：dd-mm-yyyy select hiredate,to_char(hiredate,'yyyy-mm-dd') from emp;--使用指定格式将日期转换为字符串类型 select hiredate,to_char(hiredate,'yyyy/mm/dd') from emp;--使用指定格式将日期转换为字符串类型 select hiredate,to_char(hiredate,'yyyy"年"mm"月"dd"日"') from emp;--使用指定格式将日期转换为字符串类型 查询入职日期在81年10月20日后的员工信息 第一种：自动转型 select * from emp where hiredate>'20-10月-1981' 第二种：将日期转换为字符串 select * from emp where to_char(hiredate,'yyyy-mm-dd')>'1981-10-20' 第三种： select * from emp where hiredate>to_date('1981-10-20','yyyy/mm/dd') 其他函数： 单行函数：lower 多行函数:min max sum avg count 转换函数:to_number to_char to_date ----------------------------------------------------------------------------------------- 其他函数： 1、nvl():nvl(字段名，执行)--相当于java中的if条件判断 2、nvl2():nvl2(字段名，值，值)--相当于java中的If(){}else{}判断 3、decode():decode(字段名，条件1，执行内容1，条件2，执行内容2，条件3，执行内容3，默认执行内容) 相当于java中if(){}else if(){}else if(){}...else{} 查询所有员工的月薪及姓名和工作 select * from emp select ename,sal 基本工资,comm 绩效,sal+comm 月薪 from emp select ename,sal 基本工资,comm 绩效,sal+nvl(comm,0)月薪 from emp 查询所有员工的月薪及姓名和工作 select ename,sal 基本工资,comm 绩效,nvl2(comm,sal+comm,sal)月薪 from emp 显示员工的职称 select ename,job,decode(job,'MANAGER','经理','SALESMAN','销售人员','普通员工') from emp
单行函数分类 字符函数 数值函数 日期函数 转换函数 通用函数 多行函数 sum() avg() 仅适用数值型 count() max() min() 适用任何类型数据
