SQL_单行函数

单行函数        
4.1 程序员对数据库的常用操作：
    1.数据库的打开、关闭、查看表信息等操作。
    2.SQL 语句，在编写程序中需要程序人员动手编写的命令语句。
    3.函数，每个数据库都有自己的函数支持，利用这些函数可以更加方便地完成所需的功能。对于所有函数，暂不需要了解其内部工作原理，只需要清楚每个函数的作用。

4.2 Oracle 单行函数语法
    funcation_name(列 | 表达式[,参数1,参数2])
    在调用单行函数的时候，函数可以接收一个数据表中的操作列，也可以接收一个具体的计算结果，同时设置若干个函数运行时所需的函数，根据作用不同，单行函数分为：
    字符函数：接收数据返回具体的字符信息。
    数值函数：对数字进行处理，例如四舍五入。
    日期函数：直接对日期进行相关操作。
    转换函数：日期、字符、数字之间可以完成相互转换功能。
    通用函数：Oracle 自己提供的有特色的函数
    

4.3字符型函数有:接收数据返回具体的字符信息。
    UPPER(列|字符串)：将字符串的内容全部转大写。
    LOWER(列|字符串)：将字符串的内容全部转小写。
    INITCAP(列|字符串)：将字符串的开头首字母大写。
    REPLACE(列|字符串,新的字符串)：使用新的字符串替换旧的字符串。
    LENGTH(列|字符串)：求出字符串长度。
    SUBSTR(列|字符串,开始点[,长度])：字符串截取。
    INSTR(列|字符串,要查找的字符串,开始位置,出现位置)：查找一个子字符串是否是在指定的位置上出现
    LPAD(列|字符串,长度,填充字符)：在左填充指定长度字符串。
    RPAD(列|字符串,长度,填充字符)：在右填充指定长度字符串。
    TRIM(列|字符串)：去掉左右空格
    LTRIM(字符串)：去掉左空格。
    RTRIM(字符串)：去掉右空格。
    ASCII(字符)：返回与制定字符对应的十进制数字。
    CHR(数字)：给出一个整数，并返回与之对应的字符。

用例：
SELECT UPPER('WenXuHong'),LOWER('MLDN') FROM dual;
SELECT * FROM emp WHERE ename=UPPER('smith');
SELECT ename 原始姓名,INITCAP(ename) 姓名开头首字母大写 FROM emp;
SELECT ename ,REPLACE(ename,'A','_')  FROM emp;
SELECT *  FROM emp WHERE LENGTH(ename)=5;
SELECT ASCII('L')  FROM dual;  /*76*/
SELECT CHR(76)  FROM dual;  /*L*/
SELECT '    Wenxuhong  ' 原始字符串,LTRIM('    Wenxuhong  ') 去掉左空格,RTRIM('    Wenxuhong  '),去掉右空格  FROM dual;
SELECT '    Wenxuhong  ' 原始字符串,TRIM('    Wenxuhong  ') 去掉左右两边空格   FROM dual;

--截取部分的字符串：
SELECT *  FROM emp WHERE SUBSTR(ename,0,3)='JAM';

--从指定位置截取到结尾
SELECT ename 原始姓名,SUBSTR(ename,3) 截取之后的姓名 FROM emp WHERE deptno=10;

--显示每个雇员姓名及其姓名的后三个字母
SELECT ename,SUBSTR(ename,LENGTH(ename)-2)  FROM emp;
SELECT ename,SUBSTR(ename,-3)  FROM emp;

--字符串左、右填充函数
SELECT LPAD('MLDN',10,'*') LPAD函数使用，
       RPAD('MLDN',10,'*') RPAD函数使用，
       LPAD(RPAD('MLDN',10,'*'),16,'*') 组合使用 FROM dual;
--字符串查找函数
SELECT INSTR('MLDN Java','MLDN') 查找得到返回1,
       INSTR('MLDN Java','Java') 查找得到返回6,
       INSTR('MLDN Java','JAVA') 查找不到返回0 FROM dual;




4.4 数值型函数有:对数字进行处理，例如四舍五入。
    ROUND(数字,[,保留位数]) ： 对小数进行四舍五入，可以指定保留位数，如果不指定，则表示小数点后的数字全部进行四舍五入。
    TRUNC(数字,[,截取位数])：保留指定位数的小数，如果不指定，则表示不保留小数。
    MOD(数字,数字)：取模。

用例：
--ROUND
SELECT ROUND(789.654) 不保留小数, /*790*/
       ROUND(789.654,2) 保留两位小数,/*789.65*/
       ROUND(789.654,-1) 处理整数进位  /*790*/
FROM dual;
       
SELECT ROUND(sal/30,2) FROM emp;

--TRUNC
SELECT TRUNC(789.654) 截取小数 ,/*789*/
       TRUNC(789.654,2) 截取两位小数,/*789.65*/
       TRUNC(789.654,-2) 取整  /*700*/
FROM dual;      
 
--MOD   
SELECT MOD(10,3) /*10除以3结果是商余1，所以模是1 */ FROM dual  ;  




4.5 时间型函数有:
    通过SYSDATE 伪列取得的只有年、月、日、时、分、秒等数据，如果想精确到毫秒则应该使用 SYSTIMESTAMP 伪列。
    
    ADD_MONTHS(日期,数字): 在指定的日期上加入指定的月数，求出新的日期。
    MONTHS_BETWEEN(日期1，日期2)：求出两个日期间的月数。
    LAST_DAY(日期)：求出下一个星期X的具体日期。
    NEXT_DAY(日期,星期数)：求出指定日期的最后一天日期。
    EXTRACT(格式 FROM 数据)：日期时间分割，或计算给定两个日期的间隔。

用例：
--修改日期显示格式 ,Oracle 中默认的日期显示格式为 '日-月-年'：
ALTER SESSION SET NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss';

--ORACLE 中三个日期操作公式：
日期 - 数字 = 日期
日期 + 数字 = 日期
日期 - 日期 = 数字(天数)
日期 + 日期 = 不符合逻辑

--查询距离今天为止3天之后及3天之前的日期
SELECT SYSDATE+3 三天后,SYSDATE-3 三天前 FROM dual;

--查询结果中使用TRUNC完成小数点之后的内容全部清除
SELECT TRUNC(SYSDATE-hiredate) 雇佣天数,TRUNC((SYSDATE-10)-hiredate) 十天前雇佣天数 FROM emp ;

--ADD_MONTHS
SELECT SYSDATE,
       ADD_MONTHS(SYSDATE,3) 三个月之后的日期,
       ADD_MONTHS(SYSDATE,-3) 三个月之前的日期,
       ADD_MONTHS(SYSDATE,60)六十个月之前的日期
FROM dual;

--NEXT_DAY
SELECT SYSDATE,
       NEXT_DAY(SYSDATE,'星期日') 下一个星期日,
       NEXT_DAY(SYSDATE,'星期日') 下一个星期一
FROM dual;

--LAST_DAY ：求出指定日期的最后一天日期。
SELECT SYSDATE,
       LAST_DAY(SYSDATE) 
FROM dual;

--MONTHS_BETWEEN
SELECT TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) 已雇佣年数,
       TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,hiredate)/12)) 已雇佣月数
       TRUNC(sysdate - ADD_MONTHS(hiredate,MONTHS_BETWEEN(SYSDATE - hiredate))) 已雇佣天数
FROM emp;

--EXTRACT 函数 （抽取） ，9i 后增加的功能。
语法：
EXTRACT([YEAR|MONTH|DAY|HOUR|MINUTE|SECOND]
        |[TIMEZONE_HOUR|TIMEZONE_MINUTE]
        |[TIMEZONE_REGION|TIMEZONE_ABBR]
FROM [日期（date_value）|时间间隔（interval_value）])

---从日期时间中取出年、月、日数据：
SELECT EXTRACT(YEAR FROM DATE '2001-09-19') years,
       EXTRACT(MONTH FROM DATE '2001-09-19') months,
       EXTRACT(DAY FROM DATE '2001-09-19') days
FROM dual;

---从时间戳中取出年、月、日、时、分、秒等数据
SELECT EXTRACT(YEAR FROM SYSTIMESTAMP) years,
       EXTRACT(MONTH FROM SYSTIMESTAMP) months,
       EXTRACT(DAY FROM SYSTIMESTAMP) days,
       EXTRACT(HOUR FROM SYSTIMESTAMP) hours,
       EXTRACT(MINUTE FROM SYSTIMESTAMP) minutes,
       EXTRACT(SECOND FROM SYSTIMESTAMP) seconds
FROM dual;

--复杂计算时间间隔（天数）
SELECT 
      EXTRACT(DAY FROM TO_TIMESTAMP('1982-08-13 12:17:57','yyyy-mm-dd hh24:mi:ss')
          - TO_TIMESTAMP('1981-09-27 12:17:57','yyyy-mm-dd hh24:mi:ss')
          ) days
FROM dual;

--取得时间间隔  （顺便学习 TO_TIMESTAMP 用法
SELECT EXTRACT(DAY FROM TO_TIMESTAMP('1982-08-13 12:17:57','yyyy-mm-dd hh24:mi:ss')
          - TO_TIMESTAMP('1981-09-27 12:17:57','yyyy-mm-dd hh24:mi:ss')
          ) days ,
       EXTRACT(HOUR FROM datetime_one - datetime_two) hours,
       EXTRACT(MINUTE FROM datetime_one - datetime_two) minutes,
       EXTRACT(SECOND FROM datetime_one - datetime_two) seconds
FROM (
    SELECT TO_TIMESTAMP('1982-08-13 12:17:57','yyyy-mm-dd hh24:mi:ss') datetime_one,
           TO_TIMESTAMP('1981-09-27 12:17:57','yyyy-mm-dd hh24:mi:ss') datetime_two 
    FROM dual
) 
WHERE 1=1;




4.6 转换函数: 
主要功能是将一个指定的数据类型变为另一种数据类型。
    TO_CHAR(日期|数字|列,转换格式)： 将指定的数据按照指定的格式变为字符串类型。
    TO_DATE(字符串|列,转换格式)：将指定的字符串按照指定的格式变为 DATE 型。
    TO_NUMBER(字符串|列)： 将指定的数据类型变为数字型。



《TO_CHAR日期格式化标记》
    1. YYYY ：完整的年份数字表示，年有四位，所以使用四个Y。
    2. Y,YYY ：带逗号的年。
    3. YYY ：年的后三位。
    4. YY ：年的后两位。
    5. Y ：年的最后一位。
    6. YEAR ：年份的文字表示，直接表示四位的年。
    7. MONTH ：月份的文字表示，直接表示两位的月。
    8. MM ：用两位数字来表示月份，月有两位数，所以使用两个M。
    9. DAY ：天数的文字表示。
    10. DDD ：表示一年里的天数（001-366）。
    11. DD  ：表示一月里的天数（01-31）。
    12. D ：表示一周里的天数（1-7）。
    13. DY ：用文字表示星期几。
    14. WW ：表示一年里的周数。
    15. W ：表示一月里的周数。
    16. HH  ：表示12小时制，小时是两位数字，使用两个H。
    17. HH24 ：表示24小时制。
    18. MI  ：表示分钟。
    19. SS ：表示秒，秒是两位数字，使用两个S。
    20. SSSSS ：午夜之后的秒数字表示（0-86399）。
    21. AM|PM(A.M.|P.M.) ：表示上午或下午。
    22. FM ：去掉查询后的前导0，该标记用于时间模板的后缀。

《TO_CHAR数字格式化标记》
    1. 9 ：表示一位数字
    2. 0 ：显示前导0
    3. $ : 将货币的符号显示为美元符号。
    4. L ：根据语言环境不同，自动选择货币符号。
    5. . : 显示小数点。
    6. , : 显示千位数。
    
    

--ORACEL 中数据的隐式转换操作：
   1. 字符型（VARCHAR2、VARCHAR）如果有数字组成，则可以直接转换成数字(NUMBER)。
   2. 字符型（VARCHAR2、VARCHAR） 如果按照指定的日期格式（例如：'08-9月-81'），则可以自动转换为 DATE 型数据。
   3. 数字型（NUMBER）和日期型（DATE）之间不能直接进行转换。   

--格式化当前的日期时间
SELECT SYSDATE 当前系统时间,
       TO_CHAR(SYSDATE,'YYYY-MM-DD') 格式化日期,
       TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') 格式化日期时间,
       TO_CHAR(SYSDATE,'FMYYYY-MM-DD HH24:MI:SS') 去掉前导0的日期时间
FROM  dual;

SELECT SYSDATE 当前系统时间,
       TO_CHAR(hiredate,'YYYY-MM-DD') 格式化雇佣日期,
       TO_CHAR(hiredate,'YYYY') 年,
       TO_CHAR(hiredate,'MM') 月,
       TO_CHAR(hiredate,'DD') 日
FROM  emp;

SELECT SYSDATE 当前系统时间, TO_CHAR(SYSDATE,'YEAR-MONTH-DY') 格式化日期 FROM dual;
SELECT * FROM emp  WHERE TO_CHAR(hiredate,'MM')='02';
SELECT * FROM emp  WHERE TO_CHAR(hiredate,'MM')=2;

--格式化数字显示
SELECT TO_CHAR(987654321.789,'999,999,999,999.99999') 格式化数字1,  /*987,654,321.78900*/
       TO_CHAR(987654321.789,'000,000,000,000.00000') 格式化数字2   /*000,987,654,321.78900*/
FROM dual;

--格式化数字显示
SELECT TO_CHAR(987654321.789,'L999,999,999,999.99999') 显示货币，
       TO_CHAR(987654321.789,'$999,999,999,999.99999') 显示美元
FROM dual;

--TO_DATE 函数
SELECT TO_DATE('1979-09-19','YYYY-MM-DD') FROM dual;

--TO_TIMESTAMP 函数 将数据变为时间戳。
SELECT TO_TIMESTAMP('1979-09-19 18:07:10','YYYY-MM-DD HH24:MI:SS') datetime1 FROM dual;

--TO_number : 
SELECT TO_NUMBER('09')+TO_NUMBER('19') 加法运算,
       TO_NUMBER('09')+TO_NUMBER('19') 乘法运算
FROM dual;

--sysdate :
SELECT sysdate FROM dual;

--systimestamp :
SELECT systimestamp FROM dual;

4.7 特有函数: 
    NVL(数字|列,默认值) ：如果显示的数字是 NULL 的话，则使用默认数值表示。
    NVL2(数字|列,返回结果1(不为空显示),返回结果2(为空显示)) ：判断指定的列是否为 NULL ，如果不为 NULL 则返回结果1，如果为空则返回结果 2 。
    NVLLIF(表达式1,表达式2)  ：比较表达式1 和表达式2 的结果是否相等，如果相等返回 NULL，如果不等返回表达式1。
    CASE 列|值 WHEN 表达式1 THEN 显示结果1 ...ELSE 表达式n ... END ：用于实现多条件判断，在WHEN 之后编写条件，而在THEN 之后编写条件满足的显示操作，如果都不满足则使用ELSE中的表达式处理。
    DECODE(列|值,判断值1，显示结果1,判断值2,显示结果2,...,默认值)  ： 多值判断，如果某一列(或某一个值)与判断值相同，则使用指定的显示结果输出，如果没有满足条件，则显示默认值。
    COALESCE(表达式1，表达式2，...表达式n) ：将表达式逐个判断，如果表达式1的内容是 null ，则显示表达式2，如果表达式2的内容是null，则显示表达式3，以此类推，如果表达式n 的结果还是null，则返回null。


--用例：
--NVL 运算，一个数学结算中如果存在 null ，则最后的结果也肯定是null。
SELECT NVL(null,0),NVL(3,0) FROM emp;
SELECT (SAL+NVL(COMM,0)) FROM emp;

--NVL2 : 可以同时对 null 或 非null 的值进行判断，并返回不同结果。如果 comm 为不为空，则返回 sal+comm ，comm 为空 则返回sal。
SELECT NVL2(comm,sal+comm,sal) FROM emp ;

--NULLIF 函数 
SELECT NULLIF(1,1),NULLIF(1,2) FROM dual ;
SELECT NULLIF(LENGTH(ename),LENGTH(job)) nullif FROM emp ;

--DECODE 函数
SELECT DECODE(2,1,'内容为1',2,'内容为2'),DECODE(2,1,'内容为1','没有满足条件') FROM dual ;

SELECT DECODE(job,
              'CLERK','业务员',
              'SALESMAN','销售人员',
              'MANAGER','经理',
              'ANALYST','分析员',
              'PRESIDENT','总裁',
              ) job
FROM emp;

--CASE 表达式：
SELECT CASE job WHEN 'CLERK' THEN sal*1.1
                WHEN 'SALESMAN' THEN sal*1.2
                WHEN 'MANAGER' THEN sal*1.3
       ELSE sal*1.5
       END 新工资
FROM emp;

--COALESCE 函数
SELECT COALESCE(comm,100,2000),COALESCE(comm,NULL,NULL) FROM emp;