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

一.单行函数

1.单行函数介绍:

操作数据对象

接受参数返回一个结果

只对一行进行变换

每行返回一个结果

可以转换数据类型

可以嵌套

参数可以是一列或者是一个值

2.格式:

function_name [(arg1, arg2,…)]

3.注意事项:

函数可以没有参数,但必须要有返回值

4.单行函数分类

字符函数

数值函数

日期函数

转换函数

通用函数

二.字符函数

1.大小写控制函数

LOWER :将字符转为小写

UPPER:将字符转为大写

INITCAP:将每个单词的首字母转为大写

2.字符控制函数

CONCAT:拼接字符,和”||” 作用相同

SUBSTR:截取指定的字符串

LENGTH/LENGTHB:字符的长度/字节长度

INSTR:指定字符在母串中的位置

LPAD | RPAD:在左边添加字符/在右边添加字符

TRIM:去掉前后的字符

REPLACE:替换指定的字符

三.数值函数

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.日期函数的使用:

例如:

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.显式转换

3.TO_CHAR 函数对日期的转换

格式:TO_CHAR(date, ‘format_model’)

注意事项:

必须包含在单引号中而且大小写敏感。

可以包含任意的有效的日期格式。

日期之间用逗号隔开。

日期格式的元素

例如:

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

4.TO_CHAR 函数对数字的转换

格式:TO_CHAR(number, ‘format_model’)

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

5.

六.通用函数

这些函数适用于任何数据类型，同时也适用于空值：

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.嵌套函数