Unit02：Oracle字符串、数值、日期、空值操作

1.字符串函数

1）CONCAT()：用来连接字符串，等价于 ||

SELECT CONCAT(ename, CONCAT(',', sal))FROM emp;

SELECT ename ||','|| sal FROM emp;

2）LENGTH()：查看字符串长度

SELECT ename, LENGTH(ename)FROM emp;

3）UPPER()    全大写,  LOWER()    全小写,  ININCAP()    首字母大写

SELECT UPPER('hello world'), LOWER(HELLO WORLD), INITCAP('hello world')

FROM DUAL;

4）TRIM, LTRIM, RTRIM

定义：去除当前字符串中两边的指定重复字符,LTRIM仅去除左侧的, RTRIM仅去除右侧的.

SELECT TRIM('e'FROM'eeeeliteee')FROM dual;   --lit

SELECT LTRIM('resressrerliteee', 'res')FROM dual; --liteee

SELECT RTRIM('eeelitresressrer', 'res')FROM dual; --eeelit

5）LPAD, RPAD    补位函数

SELECT LPAD(sal, 5, '$')FROM emp;

SELECT RPAD(sal, 5, ' ')FROM emp;

6）SUBSTR 截取字符串

注意：数据库中的下标是从1开始的

SELECT SUBSTR('thinking in java', 13, 4)FROM dual; --java

SELECT SUBSTR('thinking in java', -4, 4)FROM dual; --java

7）INSTR(char1, char2[n,m])  

查找char2在char1中的位置,n为从第几个字符开始检索，m为第几次出现，n,m不写则默认都是1

SELECT INSTR('thinking in java', 'in', 4, 2)FROM dual; --10

2.数值函数

2.1）ROUND(n, m)    四舍五入

2.2）TRUNC(n, m)     截取数字，舍位
      2.3）MOD(m, n)        求余数

2.4）CELL(number)    向上取整，FLOOR(number)    向下取整

2.5）

3.日期类型函数

3.1）SYSDATE, SYSTIMESTAMP

SYSDATE对应数据库一个内部函数,该函数返回一个表示当前系统时间的DATE类型值;

SYSTIMESTAMP返回的是一个表示当前系统时间的时间戳类型的值;

3.2）TO_DATE()：可以将字符串按照给定的日期格式接卸为一个DATE类型的值

SELECT

    TO_DATE('2008-08-08 20:08:98', 'YYYY-MM-DD HH24:MI:SS')

FROM dual;

/*在日期格式字符串中，凡不是英文,符号,数字的其他字符,都需要使用双引号括起来*/

SELECT

    TO_DATE('2008年08月08日', 'YYYY"年"MM"月"DD"日" HH24:MI:SS')

FROM daul;

日期的计算：

SELECT SYADATE - TO_DATE('1990-11-08', 'YYYY-MM-DD')FROM dual;

3.3）TO_CHAR()：

定义：可以将DATE按照给定的格式转换为字符串

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM dual; --2020-10-02 21:40:33

3.4）LAST_DAY(date): 返回给定日期所在月的月底日期

SELECT LAST_DAY(SYSDATE)FROM dual;    --2020/10/31 21:42:28

SELECT TO_CHAR(LAST_DAY(SYSDATE), 'YYYY-MM-DD')FROM dual; --2020-10-31

3.5）ADD_MONTHS(date, i)：

定义：对给定日期加上指定的月,若i为负数,则是减去

--查看每个员工入职20周年纪念日

SELECT ename, ADD_MONTH(hiredate, 12*20)from emp;

3.6）MONTHS_BETWEEN(date1, date2)

定义：计算两个日期之间相差的月，计算式根据date1 - date2得到的

--查看每个员工入职了多少个月？

SELECT ename, MONTHS_BETWEEN(SYSDATE, hiredate)FROM emp;

3.7）NEXT_DAY(date, i)

定义：返回给定日期的第二天开始，一周之内指定周几的日期

i：1表示周日, 2表示周一,一次类推，最好使用英文表示，语义清晰，避免歧义

SELECT NEXT_DATE(SYSDATE, FRIDAY)FROM dual; --今天20201002周五, 下一个周五2020/10/9 21:50:55

3.8）LEAST, GREATEST

定义：求最小值与最大值，除日期外，常用的数字也可以比较大小

SELECT LEAST(SYSDATE, TO_DATE('2008-08-08', 'YYYY-MM-DD'))FROM dual; --2008-08-08

SELECT GREATEST(SYSDATE, TO_DATE('20200901', 'YYYYMMDD'))FROM dual; --2020/10/2 21:56:15

SELECT GREATEST(1, 2, 3, 4, 5)FROM DUAL; --5

3.9）EXTRACT()

定义：提取给定日期中指定时间分量的值

SELECT EXTRACT(YEARFROM SYSDATE)FROM dual; --2020

--查看1980年入职的员工

SELECT ename, hiredate

FROM emp

WHERE EXTRACT(YEARFROM hiredate)=1980;

4.NULL值

4.1）插入NULL值

4.2）更新NULL值

4.3）IS NULL,    IS NOT NULL    判断字段是否为NULL值

4.4）NULL值的运算操作

NULL值与任何数字运算，结果还是NULL

NULL值与字符串拼接等于什么都没干

4.5）空值函数

1>NVL(arg1, arg2)

作用：当arg1为NULL，函数返回arg2的值，若不为NULL，则返回arg1本身.所以该函数的作用是将NULL值替换为一个非NULL值

SELECT ename, sal, comm, sal+NVL(comm, 0)FROM emp;

2>NVL2(arg1, arg2, arg3)

作用：当arg1不为NULL，则函数返回arg2; 当arg1为NULL，则函数返回arg3;该函数是根据一个值是否为NULL来返回两个不同的结果；

/*

查看每个人的绩效情况,即：

有绩效的,显示为"有绩效"

绩效为NULL的，则显示为"没有绩效"

*/

SELECT eanem, comm, NVL2(comm, '有绩效', '没有绩效')FROM emp;

--NVL2可以实现NVL的功能

SELECT ename, sal, comm, NVL2(comm, sal+comm, sal)FROM emp;

/**

日期格式：DD-MM-RR 判断世纪

*/