对Oracle数据库的一些基本总结（二）

1.Oracle字符串操作

1.1CONCAT和‘||’

CONCAT是字符串连接函数，语法如下：

CONCAT(char1,char2)

例如：

select concat(sno,sname) from w_stu;

注释：concat只能有两个参数

连接两个以上的参数时可以采用：‘||’例如：

SELECT sid||sno||sname from w_stu;

在连接时，如果任何一个参数是NULL，相当于连接了一个空格。

1.2LENGTH

LENGTH(char)用于返回参数字符串的长度。如果字符串是varchar2，返回字符串的实际长度，如果是CHAR类型，长度还包括后补的空格。例如：

select length(sno),length(sname) from w_stu ;

 

1.3UPPER,LOWER和INITCAP

UPPER(char)用于将字符转换为大写形式

LOWER(char)用于将字符转换为小写形式

INITCAP(char)用于将字符串中每个单词的首字符大写，其它字符小写，单词之间用空格和非字母字符分隔

注：如果这三个函数的输入参数是NULL值，仍然返回NULL值。

1.4TRIM、LTRIM,RTRIM

这三个TRIM函数的作用都是截去子字符串。例如:

select trim('e' from 'ebbbebbee'),ltrim('ebbbebbee','e'),rtrim('ebbbebbee','e') from dual;

结果：

1.5LPAD、RPAD

PAD意即补丁，LPAD和RPAD两个函数都叫做补位函数，LPAD表示LEFT PAD，在左边打补丁，RPAD表示RIGHT PAD，在右边打补丁。语法如下：

LPAD(char1, n, char2) 左补位函数

RPAD(char1, n, char2) 右补位函数 其中char代表参数 n代表补足到n位  ，char2为补充单位

例如：

select lpad(sno,5,'#'),rpad(sname,30,'-') from w_stu;

 

1.6SUBSTR

SUBSTR表示在一个字符串中截取子串，语法是: SUBSTR(CHAR,m,n)

用于返回char中从m位开始取n个字符的子串，字符串的首位计数从1开始。参数含义如下：

如果m = 0，则从首字符开始，如果m取负数，则从尾部开始

如果没有设置n，或者n的长度超过了char的长度，则取到字符串末尾为止

例如：

select substr('abcdefghijklmn',1,5) from dual;

 

select substr('abcdefghijklmn',-3) from dual;

1.7INSTR

用来返回在一个字符串中子串的位置。语法是：

INSTR(char,char2,n,m)

参数的含义：

返回子串char2在源字符串char1中的位置

从n的位置开始搜索，没有指定n，从第1个字符开始搜索

m用于指定子串的第m次出现次数，如果不指定取值1

如果在char1中没有找到子串char2 ，返回0

例如：

select instr('my name is jojo','jo') from dual;   

2.数值函数

2.1ROUND

数值函数指参数是数值类型的函数。常用的有ROUND、TRUNC、MOD、CEIL和FLOOR。其中ROUND用来四舍五入，语法如下：

ROUN(n,m)用于将参数n按照m的数字要求四舍五入。其中：

 

参数中的n可以是任何数字，指要被处理的数字

m必须是整数

m取正数则四舍五入到小数点后第m位

m取0值则四舍五入到整数位

m取负数，则四舍五入到小数点前m位

m缺省，默认值是0

例如：

select round(22.5563,2) from dual;

2.2TRUNC

TRUNC(n[, m])的功能是截取，其中n和m的定义和ROUND(n[, m])相同，不同的是功能上按照截取的方式处理数字n。例如:

SELECT TRUNC(45.678, 2) FROM DUAL; --45.67

SELECT TRUNC(45.678, 0) FROM DUAL;--45

SELECT TRUNC(45.678, -1) FROM DUAL;--40

2.3MOD

MOD(m, n)是取模函数，返回m除以n后的余数，如果n为0则直接返回m。例如：

SELECT MOD(100,3) FROM DUAL;--1

SELECT MOD(100,0) FROM DUAL;--100

2.4CEIL和FLOOR

CEIL(n)、FLOOR(n)这两个函数顾名思义，一个是天花板，就是取大于或等于n的最小整数值，一个是地板，就是取小于或等于n的最大整数值。比如数字n = 4.5，那么它的CEIL是5.0，它的FLOOR是4.0。在SQL语句中的例子如下：

 

SELECT ceil(44.33) from dual;--45

select floor(44.66) from dual;--44

3.Oracle日期操作

3.1DATE

DATE和TIMESTAMP是ORACLE中最常用的日期类型。DATE用来保存日期和时间。表示范围从是公元前4712年1月1日至公元9999年12月31日。

DATE类型在数据库中的实际存储固定为7个字节，格式分别为：

第1字节：世纪+100

第2字节：年

第3字节：月

第4字节：天

第5字节：小时+1

第6字节：分+1

第7字节：秒+1

3.2TIMESTAMP

TIMESTAMP表示时间戳，与DATE的区别是不仅可以保存日期和时间，还能保存小数秒，可指定为0-9位，默认6位，最高精度可以到ns(纳秒)级别。

数据库内部用7或者11个字节存储，精度为0时，用7字节存储，与DATE功能相同，精度大于0则用11字节存储。格式为：

第1字节-第7字节：和DATE相同

第8-11字节：纳秒，采用4个字节存储，内部运算类型为整型

3.3日期关键字

3.3.1 SYSDATE

SYSDATE本质是一个Oracle的内部函数，用来返回当前的系统时间，精确到秒，默认显示格式是DD-MON-RR，只有年月日并不显示时间。例如：

SELECT sysdate from dual;

3.3.2 SYSTIMESTAMP

         select systimestamp from dual;

 

3.4日期转化函数

3.4.1 TO_DATE

日期数据有时需要和字符串数据相互转换，需要用到日期转换函数，包括TO_CHAR和TO_DATE。TO_DATE的功能是将字符串按照定制格式转换为日期类型，语法格式是：

TO_DATE(char,formt)

其中char代表时间字符串，format为要转化格式。

例如：

select to_date('2018-08-22 17:27:33','yyyy-mm-dd hh24:mi:ss') from dual

 

 

3.4.2 TO_CHAR

TO_CHAR的作用是将其它类型（日期，数值）的数据转换为字符类型，主要应用在日期类型上。语法格式：

   TO_CHAR(date,format)

其中date代表日期，format代表转化格式

3.5日期常用函数

3.5.1LAST_DAY

LAST_DAY(date)：返回日期date所在月的最后一天，一般是在按照自然月计算某些业务逻辑，或者安排月末周期性活动时很有用处。例子：

select last_day(sysdate) from dual;

3.5.2ADD_MONTHS

ADD_MONTHS(date, i)：返回日期date加上i个月后的日期值.

其中：

 

参数i可以是任何数字，大部分时候取正值整数

如果i是小数，将会被截取整数后再参与运算

如果i是负数，则获得的是减去i个月后的日期值

select add_months(sysdate,-12) from dual;--查询一年前的这天

select add_months(sysdate,1) from dual;--查询一个月后的这天

 

3.5.3MONTHS_BETWEEN

MONTHS_BETWEEN(date1, date2)：计算date1和date2两个日期值之间间隔了多少个月，实际运算是date1-date2，如果date2时间比date1晚，会得到负值。

除非两个日期间隔是整数月，否则会得到带小数位的结果，例如2018-08-23与2018-01-02相隔7.6个月

select months_between(to_date('2018-08-23','yyyy-mm-dd'),to_date('2018-01-02','yyyy-mm-dd')) from dual;--7.6

 

3.5.4NEXT_DAY

NEXT_DAY(date, char)：返回date日期数据的下一个周几，周几是由参数char来决定的。在中文环境下，直接使用”星期三”这种形式，英文环境下，需要使用”WEDNESDAY”这种英文的周几。

为避免麻烦，可以直接用数字1-7表示周日-周六。需要注意的是NEXT_DAY不要按字面意思理解为明天。查询下个周三是几号：

select next_day(sysdate,4) from dual;

 

3.5.5比较函数LEAST和GREATEST

两个函数都可以有多个参数值，但参数类型必须一致，返回结果是参数列表中最大或最小的值。

在比较之前，在参数列表中第二个以后的参数会被隐含的转换为第一个参数的数据类型，所以如果可以转换，则继续比较，如果不能转换将会报错。

SELECT LEAST(SYSDATE, '10-10月 -08') FROM DUAL;--比较那个日期最小 可以有多个参数

select greatest(SYSDATE, '10-10月-02') from dual;--比较那个日期最大 可以有多个参数

 

3.5.6EXTRACT

EXTRACT直译是抽取或提取：

EXTRACT(date FROM datetime)：从参数datetime中提取参数date指定的数据，比如提取年、月、日。例如取出当前日期的年：

select extract(year from sysdate),extract(month from sysdate), extract(day from sysdate) from dual;

4.空值函数

4.1NVL

NVL(expr1, expr2)：将NULL转变为非NULL值。如果expr1为NULL，则取值expr2, expr2是非空值。

其中expr1和expr2可以是任何数据类型，但两个参数的数据类型必须是一致的。例如：

select nvl(3000,0),nvl(null,0) from dual

 

4.2NVL2

NVL2(expr1, expr2, expr3)：和NVL函数功能类似，都是将NULL转变为非空值。NVL2用来判断expr1是否为NULL，如果不是NULL，返回expr2，如果是NULL，返回expr3。例如：

select nvl2(null,3,6),nvl2(1,3+1,3) from dual;