Oracle数据库的查询之单行函数查询(三)

缺乏、安全感 2022-07-05 15:01 237阅读 0赞

一.单行函数  
1.单行函数介绍:

*  操作数据对象
 *  接受参数返回一个结果
 *  只对一行进行变换
 *  每行返回一个结果
 *  可以转换数据类型
 *  可以嵌套
 *  参数可以是一列或者是一个值

2.格式:  
function\_name \[(arg1, arg2,…)\]  
3.注意事项:  
函数可以没有参数,但必须要有返回值  
4.单行函数分类

*  字符函数
 *  数值函数
 *  日期函数
 *  转换函数
 *  通用函数

--------------------

二.字符函数  
1.大小写控制函数  
LOWER :将字符转为小写  
UPPER:将字符转为大写  
INITCAP:将每个单词的首字母转为大写  
![这里写图片描述][SouthEast]

2.字符控制函数  
CONCAT:拼接字符,和”||” 作用相同  
SUBSTR:截取指定的字符串  
LENGTH/LENGTHB:字符的长度/字节长度  
INSTR:指定字符在母串中的位置  
LPAD | RPAD:在左边添加字符/在右边添加字符  
TRIM:去掉前后的字符  
REPLACE:替换指定的字符

![这里写图片描述][SouthEast 1]

--------------------

三.数值函数  
1.ROUND: 四舍五入  
ROUND(45.926, 2) 45.93  
ROUND(45.926, 1) 45.9  
ROUND(45.926, 0) 46  
ROUND(45.926, -1) 50  
ROUND(45.926, -2) 0  
2.TRUNC: 截断  
TRUNC(45.926, 2) 45.92  
TRUNC(45.926, 1) 45.9  
TRUNC(45.926,0) 45  
TRUNC(45.926, -1) 40  
TRUNC(45.926, -2) 0  
3.MOD: 求余  
MOD(1600, 300) 100

--------------------

四.日期函数  
1.注意事项:

Oracle 中的日期型数据实际含有两个值: 日期和时间。  
默认的日期格式是 DD-MON-RR.

select sysdate from dual;显示当前系统时间
    select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
    格式化时间

2.日期的数学运算  
在日期上加上或减去一个数字结果仍为日期。  
两个日期相减返回日期之间相差的天数。  
可以用数字除24来向日期中加上或减去小时。  
不允许日期+日期

查询员工的工作多少周.多少月,多少年
    select ename,hiredate,(sysdate-hiredate) 天,(sysdate-hiredate)/7 星期,(sysdate-hiredate)/30 月,(sysdate-hiredate)/365 年
    from emp;

3.日期函数的使用:  
![这里写图片描述][SouthEast 2]

例如:

select months_between(sysdate,hiredate) from emp;
    select add_months(sysdate,12) from dual;
    select last_day(sysdate) from dual;
    select next_day(sysdate,'星期五') from dual;

select round(sysdate,'year') from dual;
    一年如果过了一半的话结果就会为下一年的1月1号,如果没有就为本年的1月1号
    select round(sysdate,'month') from dual;
    一个月如果过了一半就为下一个月的1号,如果没有就为本月的1号
    select round(sysdate,'day') from dual;

select trunc(sysdate,'year') from dual;
    相当于重置月份和日为1月1号
    select trunc(sysdate,'month') from dual;
    相当于重置日为1号
    select trunc(sysdate,'day') from dual;

4.next\_day的应用：  
每个星期一自动备份表中的数据  
1. 分布式数据库  
2. 触发器 快照

--------------------

五.转换函数  
1.隐性转换  
\*字符串到数值  
\*字符串到日期  
\*数值到字符串  
\*日期到字符串  
2.显式转换  
![这里写图片描述][SouthEast 3]

3.TO\_CHAR 函数对日期的转换  
格式:TO\_CHAR(date, ‘format\_model’)

注意事项:  
必须包含在单引号中而且大小写敏感。  
可以包含任意的有效的日期格式。  
日期之间用逗号隔开。

日期格式的元素  
![这里写图片描述][SouthEast 4]

![这里写图片描述][SouthEast 5]

例如:
     select hiredate,sal,to_char(hiredate,'yyyy "年" mm"月" dd"日"') from emp;

4.TO\_CHAR 函数对数字的转换  
格式:TO\_CHAR(number, ‘format\_model’)  
![这里写图片描述][SouthEast 6]

select to_char(sal,'L9,999.99') from emp;

5.![这里写图片描述][SouthEast 7]

--------------------

六.通用函数  
这些函数适用于任何数据类型，同时也适用于空值：  
NVL (expr1, expr2)  
NVL2 (expr1, expr2, expr3)  
NULLIF (expr1, expr2)  
COALESCE (expr1, expr2, …, exprn)

1.NVL函数  
将空值转换成一个已知的值  
可以使用的数据类型有日期、字符、数字。

函数的一般形式:
    NVL(commission_pct,0)
    NVL(hire_date,'01-JAN-97')
    NVL(job_id,'No Job Yet')

2.NVL2函数  
NVL2 (expr1, expr2, expr3)  
expr1不为空,返回expr2,为空,返回expr3

3.NULLIF (expr1, expr2)函数  
相等返回NULL，不等返回expr1

4.COALESCE(联合,合并) 函数  
找参数中第一个不为空的值。

select sal,comm,coalesce(comm,sal,10) from emp;

--------------------

七.条件表达式  
使用两种方法:  
CASE 表达式：SQL99的语法，类似Basic，比较繁琐  
DECODE 函数：Oracle自己的语法，类似Java，比较简介

1.case表达式的使用:

select t.empno, t.ename, case when t.job = 'CLERK' then '业务员' when t.job = 'MANAGER' then '经理' when t.job = 'ANALYST' then '分析员' when t.job = 'PRESIDENT' then '总裁' when t.job = 'SALESMAN' then '销售' else '无业' end from emp t

2.decode函数的使用

select ename, decode(job, 'clerk','业务员', 'salesman','销售', 'president','总裁', 'manager','经理', '无业') from emp;

3.嵌套函数  
![这里写图片描述][SouthEast 8]

[SouthEast]: /images/20220705/836c9641af38421ca30ebf96688ef4ef.png
[SouthEast 1]: /images/20220705/1e8ad619c46b45c2aaaa46a13cb7a9c7.png
[SouthEast 2]: /images/20220705/30b65198fbc34498b3ba3996a1bd404f.png
[SouthEast 3]: /images/20220705/77cb8d9b7da84716b6f031cb85752b8d.png
[SouthEast 4]: /images/20220705/b18345129daa4bd2805a9ff604898048.png
[SouthEast 5]: /images/20220705/709978b475244071aebfa7e57f8e85ab.png
[SouthEast 6]: /images/20220705/a5d52799d7dc49a9be2b27ccfa49954f.png
[SouthEast 7]: /images/20220705/0bd82e643a0b48c69f0aa6a76b8a7094.png
[SouthEast 8]: /images/20220705/7d2bf0262a7c49209840e92fc3efca4d.png