SQL语法-单行函数

　　单行函数严格来讲并不属于SQL语法，但是针对不同的数据库，首先SQL这个标准一定会共同遵守的，但是每个数据库都有每一个数据库自己定义的函数，利用函数，可以完成一些指定的操作功能。那么在Oracle之中单行函数一共分为5类：字符串函数，数字函数，日期函数，转换函数，通用函数。

字符串函数：

　　字符串函数指的是针对于字符串数据进行的处理，常见的字符串函数有: UPPER()、LOWER()、INITCAP()、LENGTH()、REPLACE()、SUBSTR()、INSTR()。
　
1、转大写/小写函数，格式要求: “返回值 函数名称(输入参数)”

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

　　为了操作方便，在Oracle中专门提供了一张dual的虚拟表，直接利用此表作为查询的数据源即可。
　　 现在考虑这样一种情况，有这样一种情况，由用户输入雇员姓名，而后查询出雇员的信息。

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

　　在实际开发中，有些程序是不区分大小写的，如果不区分大小写，则在数据保存的时候就可以将其全部自动转变为大写或小写。当然，这种转换不一定要在数据库之中，也可以在程序之中完成。
　　
2、开头首字母大写
　　· 语法： 字符串 INITCAP(字符串 | 列)
范例：将每一个雇员姓名的首字母大写。

SELECT INITCAP(ename) FROM emp;

3、计算字符串函数
　　· 语法： 数字 LENGTH(字符串 | 列)
范例：查询出每一位雇员的姓名，以及其姓名的长度。

SELECT ename,LENGTH(ename) FROM emp;

范例：查询出姓名长度为5的全部雇员信息。

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

4、字符串替换操作
　　· 语法：字符串 REPLACE(字符串 | 列， 要替换的旧内容， 要显示的新内容)
范例：将’hello world’ 中所有的 ‘l’ 替换为 ‘_’;

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

5、字符串截取
　　·　语法，从指定位置截取到结尾, 字符串 SUBSTR (字符串 | 列，截取开发点)。
　　·　语法，截取部分字符串： 字符串 SUBSTR (字符串 | 列，截取开始点，截取结束点)。
　　

SELECT SUBSTR('hello world',7), SUBSTR('hello world',0,6), SUBSTR('hello world',1,6) FROM dual;

　　一定要记住，在Oracle之中为了开发方便，所有的字符串都是有1开始，如果用户设置了0，那么实际上也表示由1开始，系统会帮助用户自动处理了。

范例：查询出每一位雇员姓名的前3个字母

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

范例：查询每一位雇员的姓名的后3个字母

SELECT SUBSTR(ename,LENGTH(ename)-2) FROM emp; 

面试题：请问Oracle的SUBSTR()函数是从下标0还是从1开始？
　　　SUBSTR()函数从0或者是1开始都是一样的，因为Oracle中字符串下表索引从1开始，如果设置为0，也会自动将其变为1，或者设置为负数，由后开始截取。

6、字符串查找 : INSTR()
　　· 语法： 数字 INSTR(字符串 | 列 , 查找的字符串[,超找开始点])，如果找不到内容返回0，如果找到了返回对应的位置。

SELECT INSTR('hello', 'l') FROM dual;

数值函数

　　 数值函数一共分为三种: ROUND(), TRUNC(), MOD()。
　
　1、四舍五入 : ROUND()
　　· 语法：数字 ROUND(数字 | 列 [保留小数位])
　　
　 　如果没有设置保留小数位，则表示所有的小数直接进位。

SELECT 
    ROUND(197.57898456),
    ROUND(197.57898456,2),
    ROUND(197.57898456,-2) 
FROM dual;

　2、截取小数 : TRUNC()
　 　· 语法：数字 TRUNC(数字 | 列 [保留小数位])

SELECT 
    TRUNC(197.57898456),
    TRUNC(197.57898456, 2) ,
    TRUNC(197.57898456, -2)
FROM dual;

　3、求模 (求余数): MOD()
　 　· 语法：数字 MOD(数字 | 列 , 数字 | 列)

SELECT 
    MOD(10,3)
FROM dual;

日期函数 :

　　
　　日期函数的主要功能是为了解决闰年的问题。但是如果要想操作日期函数，那么必须知道当前的日期是什么，或者理解为如何确定一个日期的开始点。为了解决取得当前日期的问题，在Oracle中专门提供了一个SYSDATE的伪列。可以直接查询此列进行操作。

范例：如果要想确定当前日期，使用dual会比较的方便

那么取得了当前日期之后，实际上就可以利用以下的三个公式进行计算操作：
　　·　日期 + 数字 = 日期，表示若干天之后的日期；
　　·　日期 - 数字 = 日期，表示若干天之前的日期；
　　·　日期 - 日期 = 日期， 表示2个日期之间所经历的天数。

范例：计算每一位雇员到今天为止的雇佣天数，天数的计算只有日期 - 日期

SELECT ename,SYSDATE-hiredate FROM emp;

　　除了以上的基本公式之外，还提供了四个日期操作函数：MONTHS_BETWEEN(), ADD_MONTHS(), NEXT_DAY(), LAST_DAY()

1、计算两个日期之间的月数
　 ·语法: 数字 MONTHS_BETWEEN(日期1|列， 日期2|列)）;
范例: 查询出每一位雇员的编号、姓名、雇佣的月数和年数

SELECT empno,ename,MONTHS_BETWEED(SYSDATE,hiredate) ,
    TRUNC(MONTHS_BETWEED(SYSDATE,hiredate)/12) FROM emp ;

　　通过此操作函数的确可以解决闰年的计算问题。

2、增加指定月后的日期：
　　 ·语法: 日期 ADD_MONTHS(日期, 要增加的月份数字);
范例：

SELECT ADD_MONTHS(SYSDATE, 5) from dual;

　　可以发现，ADD_MONTHS()函数后面增加的月可以很多，并且自动的累加成年。

3、 求出下一个指定的一周时间数日期
　·语法：NEXT_DAY(日期 | 列 , ‘一周时间数’)
范例：求出下一个星期一

SELECT NEXT_DAY(SYSDATE,'星期一') FROM dual;

4、 求出一个指定日期所在月的最后一天
　　·语法：日期 LAST_DAY(日期 | 列)
范例:求出本月的最后一天

SELECT LAST_DAY(SYSDATE) from dual;

　　当然，对于日期函数，本身也是可以进行嵌套操作的。

范例：日期嵌套，计算出20个月之后的那个月的最后一天

SELECT LAST_DAY(ADD_MONTHS(SYSDATE,20)) FROM dual;

　　所有的日期函数操作是可以解决闰年的问题，为了准确，只要是牵扯到了日期的计算操作问题，都应该优先考虑以上的函数，但是对于天数，只有一种求法“日期 – 日期”。

转换函数

　　到现在为止已经接触到了Oracle数据库中的三类数据类型：数字、字符串、日期，那么这三类数据类型之间也是可以互选转换的，为此提供了三个转换函数：TO_CHAR(),TO_DATE(),TO_NUMBER()。

1、将日期或数字变为字符串(格式化操作)：
　　·语法：　字符串 TO_CHAR(数字|日期|列,转换格式)；对于转换格式主要针对于两种数据：
　　· 日期：年(yyyy)、月(mm)、日(dd)、时(hh)、分(mi)、秒(ss)、前导(fm);
　　· 数字：每一位数字都使用一个”9”表示，如果表示货币用”L”表示。

范例: 格式化日期时间

SELECT 
    TO_CHAR(SYSDATE,'yyyy-mm-dd') ,
    TO_CHAR(SYSDATE,'yyyy-mm-dd hh:mi:ss') ,
    TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss') ,
    TO_CHAR(SYSDATE,'fmyyyy-mm-dd hh24:mi:ss') 
FROM dual;

　　对于前导0，建议保留的，因为这样可以保证数据的长度统一，同时使用TO_CHAR()函数还可以从一个DATE型数据之中，拆分出年、月、日的信息。

范例：拆分数据

SELECT 
    TO_CHAR(SYSDATE,'yyyy') 年 ,     
    TO_CHAR(SYSDATE,'mm') 月,    
    TO_CHAR(SYSDATE,'dd') 日,      
    TO_CHAR(SYSDATE,'hh24') 时, 
    TO_CHAR(SYSDATE,'mi') 分, 
    TO_CHAR(SYSDATE,'ss') 秒
FROM dual ;

除了针对于日期进行格式化操作之外，也可以针对于数字格式化：

SELECT TO_CHAR(1234810298509234,'999,999,999,999,999,999') from dual;

　　所有使用TO_CHAR()函数转换完成的数据类型全部都是字符串了。

2、将字符串格式化为日期型数据
　　·语法： 日期 TO_DATE(字符串 | 列， 转换格式)；
范例：将字符串变为日期

SELECT TO_DATE('2011-11-11 11:11:11', 'yyyy-mm-dd hh:mi:ss') FROM dual;

3、将字符串变为数字
　　·语法： 数字 TO_NUMBER(字符串 | 列， 转换格式)；
范例：将字符串变为数字

SELECT TO_NUMBER('1') + TO_NUMBER('1') FROM dual;
// 等于
SELECT '1' + '1' FROM dual;

　　实际上针对数据类型的转换，在Oracle之中是默认支持的一种方式，例如：最早的时候字符串可以变为日期，字符串如果有数字组成也可以直接变为数据，这些都是自动完成的。

通用函数

　　通用函数主要学习两个： NVL(), DECODE()。
1、针对NULL处理
　　·语法：数字　NVL(列吗, 默认值);
范例：现在要求计算出每一位雇员的年薪(工资+佣金)

SELECT empno,ename,sal,comm,(sal+comm)*12 FROM emp;

　 发现所有没有佣金的雇员在计算年薪的时候最终的结果都是NULL,因为 NULL 进行任何数学计算之后结果都是NULL, 但是现在如果计算准确，就希望判断一下，如果内容是NULL，那么按照 0 计算最合适，现在就可以利用NVL函数。

// comm 等于NULL时，使用 0 代替
SELECT empno,ename,sal,comm,(sal+NVL(comm,0))*12 FROM emp;

2、 多数值判断
　　·语法： 数据类型 DECEDE(值|列, 判断值1, 显示内容1, 判断值2, 显示内容2, …..)
范例：将每一位雇员的职位都更改为中文显示
　

SELECT empno,ename,job, 
    DECODE(
    job,
    'CLERK','办事员',
    'SALESMAN','销售',
    'MANAGER','经理',
    'ANALYST','分析员',
    'PRESIDENT','总裁'
    )
FROM emp; 

　　这2个函数是Oracle中的特色函数，需要记下来.