Oracle数据库——单行函数-04

单行函数（重点，背）

单行函数是完成某些特定功能的工具，这个部分还是要求进行一些巩固的记忆，单行函数的基本语法如下
返回值类型 单行函数名称（参数|列,...）
而根据使用的环境不同单行函数分为如下几种：字符串函数，数值函数，日期函数，转换函数，通用函数

字符串函数
字符串函数的主要功能是进行字符串数据的处理的，常用的字符串函数有如下几个：UPPER(),LOWER(),
INITCAP(),SUBSTR(),TRIM(),LENGTH(),REPLACE()
1.大小写转换函数

字符串转大写：字符串UPPER（列|字符串）；
字符串转小写：字符串LOWER(列|字符串)；

范例：观察使用
SELECT UPPER('Hello') FROM emp ;
为了验证函数要求必须是完整的SQL语句，但是以上语句执行之后发现返回的数据14行记录，而且造成这
种情况，主要是因为emp表有14行记录，那么为了验证函数方便，特意使用一张系统的临时表：dual;

SELECT UPPER('Hello') FROM dual;
SELECT LOWER('Hello') FROM dual;

在一些系统之中如果存在有不区分大小写的操作，那么几乎都是会统一的将数据变为大写或小写，为了演示
此功能，做一个简单的应用（没意义），由用户输入一个雇佣姓名，而后查询此雇佣的完整信息
范例：完成代码

SELECT *
FROM emp
WHERE ename='&inputname';

用户输入数据的时候很少会取考虑大小写问题，所以必须由系统自动处理，使用一个UPPER函数

SELECT *
FROM emp
WHERE ename=UPPER('&inputname');

2.首字母大写
语法：字符串INITCAP(列|字符串)
范例：显示每个雇员的编号，姓名，职位，其中对于姓名要求首字母大写

SELECT empno,ename,INITCAP(ename),job
FROM emp;

3.计算字符串长度
语法：数字LENGTH(列|字符串)
范例：查询出雇员姓名长度为5的全部雇员信息

SELECT * FROM emp WHERE LENGTH(ename)=5;

4.去掉左右空格
语法：字符串TRIM(列|字符串)
范例：验证函数

SELECT TRIM( '     hello          '),
        LENGTH(TRIM( '     hello           ')) FROM dual;

5.字符串截取函数

从指定位置截取到结尾：字符串SUBSTR(列|字符串，截取开始点)；
截取部分字符串：字符串SUBSTR(列|字符串，截取开始点，截取长度)

范例：观察截取操作
SELECT 'helloworld',SUBSTR('helloworld',0,5),SUBSTR('helloworld',1,5),SUBSTR('helloworld',6)FROM dual;

在进行字符串截取的时候Oracle和程序设计思路不一同的地方在于，oracle中的字符串下标设置为0，实际
上也是1，
而且在Oracle中的SUBSTR（）函数里面最有特点的一个功能：也可以设置为负数，表示由后的指定位置
开始截取

范例：截取每位雇员姓名的后三个字母

SELECT ename,SUBSTR(ename,-3)FROM emp;

面试题;请问Oracle中的SUBSTR()函数的下标是从0还是从1开始？
Oracle中的SUBSTR()函数的下标是从1开始，如果设置为0也表示从1开始，或者设置为负数，表示由后的指定位置开始截取
6.字符串替换
语法：字符串REPLACE(列|字符串，要替换的原始内容，新的内容)

SELECT REPLACE('hello world','l','_')FROM dual;

从实际来讲，以上的这些函数功能在日后的程序开发之中也一定会用到

数值函数

数字函数的功能是进行数字处理的，由三个函数：ROUND(),TRUNC(),MOD()
1.四舍五入函数

语法：数字ROUND(列|数字[, 保留的小数位])
范例：验证ROUND函数

SELECT ROUND(789.537),ROUND(789.537,2),ROUND(789.537,-2),ROUND(719.537,-2)FROM dual;

 

如果没有编写保留位数，那么直接将小数点后面的数据进行进位
2.截取小数函数

语法：数字TRUNC(列|数字[, 保留的小数位])
SELECT TRUNC(789.537),
    TRUNC(789.537,2),
    TRUNC(789.537,-2),
    TRUNC(719.537,-2)FROM dual;

3.求模（余数）

语法：数字MOD(列|数字，列|数字)
范例：使用MOD（）函数
SELECT MOD(10,3)from dual;

以上的三个函数的功能很简单，但是在这之中对于ROUND这个单间一定要重点记住，以后在程序中看见就表示
四舍五入。

日期函数
如果要想进行日期的操作，那么首先一定要取得当前的日期时间，在Oracle中为了方便用户取得当前日期
时间提供有一个SYSDATE伪列，所谓的伪列指的是不是真实存在于表上的列，但是又可以直接使用的列。
范例：验证SYSDATE
SELECT SYSDATE,SYSTIMESTAMP FROM dual;
但是取得了当前的日期时间之后，那么后面可以以下计算公式进行日期时间的操作
日期+数字=日期，表示若干天之后的日期
日期-数字=日期，表示若干天之前的日期
日期-日期=数字，表示两个日期之间所经历的天数
范例：观察公式的使用

SELECT SYSDATE-10 ,SYSDATE+120,SYSDATE+2000 FROM dual;

范例：计算天数——计算每一位雇员到今天为止雇佣的天数

SELECT ename,hiredate,SYSDATE-hiredate FROM emp;

可是除了以上的SYSDATE伪列之外还有一些函数支持：MONTHS_BETWEEN(),ADD_MONTHS(),NEXT_DAY(),LAST_DAY()四个函数
1.计算两个日期之间所经历的月数总和

语法：数字MONTHS_BETWEEN(列|日期，列|日期)
范例：计算每位雇员到今天为止的雇佣总月数
SELECT ename,hiredate,TRUNC(MONTHS_BETWEEN（SYSDATE，hiredate）) FROM emp;

2.增加指定月之后的日期

语法：日期ADD_MONTHS(列|日期，月数)
范例：验证ADD_MONTHS()
SELECT ADD_MONTHS(SYSDATE,4),ADD_MONTHS(SYSDATE,900)FROM dual;

3.求出下一个指定的星期X（一周时间数）

语法：日期NEXT_DAY(列|日期，'星期X')
范例：验证NEXT_DAY（）
SELECT NEXT_DAY(SYSDATE,'星期一'),NEXT_DAY(SYSDATE,'星期二')FROM dual;

4.求出给出日期所在月的最后一天日期

语法：日期LAST_DAY(列|日期)
范例：验证LAST_DAY
SELECT LAST_DAY(SYSDATE)FROM dual;

以上的四个函数在编写的时候往往会和时间日期的加减计算一起操作

转换函数

现在为止已经接触了Oracle中的字符串，数字，日期，那么这三种数据类型也可以实现互相的转换操作
函数：TO_CHAR(),TO_DATE(),TO_NUMBER()
1.日期或数字转字符串

语法：字符串TO_CHAR(列|日期|数字，转换格式)
在之前使用SYSDATE求出的日期时间按照"24-3月-18"这样的格式排列，但是这样做法不太习惯，所以就可以
使用TO_CHAR()进行格式化显示，但是如果要想格式化，还需要几个转换标记的支持：年(yyyy),月（mm），日（dd），
时（hh,hh24），分（mi），秒（ss）
SELECT TO_CHAR(SYSDATE,'yyyy')from dual;

范例：格式化日期时间显示
SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd'),TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss')FROM dual;

如果在格式化标记之中使用了FM，则表示自动取消前导0，但是从实际的开发来讲，建议保留前导0
如果在进行格式化的过程之中，有两个标记：数字（9），货币（L）

范例：格式化数字
STLECT TO_CHAR(335454580808,'999,999,999,999,999,999')FROM dual;

2.字符串转日期

语法：日期TO_DATE(列|日期，转换格式)
SELECT TO_DATE('1989-09-23','yyyy-mm-dd')FROM dual;

3.字符串转数字

语法：数字TO_NUMBER(列|字符串)
范例：验证
SELECT TO_NUMBER('1')+TO_NUMBER('2')FROM dual;
SELECT '1'+'2'FROM dual;

对于Oracle数据库而言，各个数据类型之间存在一种自动的转换原则，如果现在字符串是由数字组成，执行数学
计算时，自动变为数字，如果字符串的格式符合日期格式，也会在运算时自动变为日期

通用函数
以上的部分函数大部分的数据库都是支持的，但是Oracle有一些自己的特色，有两个代表函数：NVL(),
DECODE()
1.处理NULL数据
语法：数字NVL（列，默认值）；
在讲解此函数之前首先完成一个功能

范例：现在要求显示每个的雇员的编号，姓名，职位，基本工资，佣金，年薪
SELECT empno,ename,job,sal,comm,(sal+comm)*12 income FROM emp;

现在发现只要是没有佣金的雇员在计算年薪的时候都会出现问题，结果都是null，之所以出现这样的情况，
主要是因为 null与任何数值执行数学计算最终结果永远都是null。
那么为了在数据为null的时候程序依然可以得到正确的结果，就可以通过NVL()处理

范例：使用NVL()处理
SELECT empno,ename,job,sal,comm,(sal+NVL(comm,0))*12 income FROM emp;

2.多数值判断函数

语法：数据 DECODE(列|数据，比较数据1，显示数据1，比较数据2，显示数据2，...[，默认返回])；
范例：使用DECODE()函数
SELECT DECODE(1,0,'内容为0',1,'内容为1','都不满足条件')FROM dual;

DECODE()只能够针对于数据判断，不能针对逻辑条件判断。

总结：给出的SQL语法：

SELECT [DISTINCT] * | 列名称 [别名],...  ==<3.确定数据显示列
FROM 表名称 [别名]    ==<1.确定数据来源
WHERE 限定条件(s)    ==<2.针对于数据行进行筛选
[ORDER BY 排序字段[ASC|DESC],排序字段[ASC|DESC],...];  ==<4.数据列排序

对于此时给出的SQL的执行顺序：FROM==<WHERE==<SELECT==<ORDER BY
2.所有给出的单行函数只是给出了其语法和验证代码，具体的使用慢慢观察
任务
1.多表查询
2.统计查询
3.子查询、
作业：
1找出各月倒数第3天受雇的所有员工
2.找出早于12年前受雇的员工
3.找出在（任何年份的）2月受聘的所有员工
4.显示满10年服务年限的员工的姓名和受雇日期
5.显示员工的姓名和受雇日期，根据其服务年限，将最老的员工排在最前面
6.显示所有员工姓名，加入公司的年份和月份，接受雇日期所在月排序，若月份相同则将最早年限排在前面
7.显示在一个月为30天的情况所有员工的日薪金，忽略余数
8.以年月日的方式显示所有员工的服务年限