函数的作用

1.方便数据的统计

2.处理查询结果

函数分类

1. 数值函数

2. 字符函数

3. 日期函数

4. 转换函数

1. 日期转字符串：
   

   TO_CHAR（date，fmt，params）

   date 日期数据

   fmt：转换格式： YYYY-MM-DD HH24：MI：SS

   params：转换语言，通常省略

2. 字符转日期

   TO_DATE（char，fmt，params）

3. 数字转字符串

   TO_CHAR（number，fmt）

   fmt格式：有以下取值：

   9： 显示数字并忽略前面的0

   0：显示数字，位数不足用0补齐

   .或D：显示小数点

   ，或G：显示千位符

   $：显示美元符号

   S：加上正负号

4. 字符转数字

   TO_NUMBER（char， fmt）

   fmt 和上面一样，可以省略。
   

1. 系统时间

   sysdate  默认格式  dd-MM-yy
   

2. 日期操作

   2.1 ADD_MONTHS（date，i）增加月份

   2.2 NEXT_DAY（date，char）返回的是下周的哪一天，用char指定。根据中英文环境指定不同字符串。

   2.3 LAST_DAY（date）返回所在月的最后一天

   2.4 MONTHS_BETWEEN（date1， date2） 返回两个日期的月份差，天数不同时，返回的是一个小数。如果要得到两个日期的天数差，直接对日期进行相减计算。

   2.5 EXTRACT（date FROM datetime） 截取日期的部分，date的取值有year month  day hour minutes seconds 

select extract(hour from timestamp '2015-10-1 17:25:13') from dual

1. 大小写转换

   UPPER  大写转换

   LOWER（n） 小写转换

   INITCAP（n） 首字母大写

2. 获取子串

   SUBSTR（char，【m】，【n】）

   m表示起始位置： 默认为0，可以为负数，表示从尾部截取

   n表示截取长度：默认到字符串末端。
   

3. 获取字符串长度

   length（char）

4. 字符串连接函数

   CONCAT （char1， char2） 与操作符 || 效果是一样的。

5. 去除子串函数

   TRIM （char  FROM str） 从str去除一个char字符

   LTRIM （str， char）从str中去除char，从头部开始只去除一次，非全局去除

   RTRIM（str， char）从str中去除char，从右边开始只去除一次，非全局去除

   TRIM（str）去除前后空格

6. 替换函数

   REPLACE（STR， TSTR， RSTR）

   将原字符串STR中的TSTR替换为RSTR。

四舍五入函数：

ROUND（n, [,m]）

n 需要进行四舍五入的字段

默认 m = 0

m > 0表示小数点后m位

m < 0表示小数点前m位

取整函数：

SEIL（n） 向上取整 （向大的方向取整）

FLOOT（n） 向下取整 （向小的方向取整）

绝对值函数  abs（n）

取余函数  mod（n，m）

幂指函数  POWER（n，m）

平方根函数  SQRT（n）

三角函数  SIN（n）  ASIN（n）  COS（n） ACOS（n）  TAN（n）  ATAN（n）  注意n指的是弧度，不是角度

 在员工信息表查询出员工的生日
根据身份证号码得到员工的生日
select substr(cardid,7,8)from users;
将部门号01全部替换成信息技术
select replace(deptno,01,'信息极速') from users;
将员工信息表中年龄字段与10取余数
select mod(age,10)from users;
在查询中使用日期函数
select extract(year from redate) from users;

查询出5月份入职的员工信息
select * from  users where extract(month from redate ) = 5;

转换函数

日期转换成字符的函数：to-char(date[,fmt[,params]])【date--将要转换的日期，fmt--转换成的格式，params--日期的语言(此参数通常不写)】

日期默认格式：dd-mon-rr

fmt格式定义：yy【两位的年】 yyyy【四位的年】 year【表示成英文形式】

mm【两位的月份】 month【表示英文的月份】

dd【两位的天数】 day【表示英文的日】

hh24【二十四小时制】 hh12【十二小时制】

mi【分钟】 ss【秒】

例子：

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY

-------------------

2019-03-26 15:04:33

SQL> select to_char(sysdate,'yy-mm-dd hh12:mi:ss') from dual;

TO_CHAR(SYSDATE,'

-----------------

19-03-26 03:05:31

SQL> select to_char(sysdate,'year-month-day hh12:mi:ss') from dual;

TO_CHAR(SYSDATE,'YEAR-MONTH-DAYHH12:MI:SS')

--------------------------------------------------------------------

twenty nineteen-3月 -星期二 03:07:23

TO_CHAR(SYSDATE,'YEAR-MM-DAYHH12:MI:SS')

----------------------------------------------------------------

twenty nineteen-03-星期二 03:09:00

SQL> select to_char(sysdate,'year-mm-dd hh12:mi:ss') from dual;

TO_CHAR(SYSDATE,'YEAR-MM-DDHH12:MI:SS')

---------------------------------------------------------

twenty nineteen-03-26 03:09:29

字符转换为日期的函数：to_date(date[,fmt[,params]])【to_date()按照系统默认格式显示日期】 下例：

SQL> select to_date('2019-3-26','yyyy-mm-dd') from dual;

TO_DATE('2019-

--------------

26-3月 -19

数字转换为字符的函数：to_char(number[,fmt])

9：显示数字并忽视前面的0

0：显示数字，位数不足用0补齐

.或D：显示小数点

，或G：显示千位符

$：美元符号

S：加正负号（前后都可以,但不能同时加在前后）

SQL> select to_char(12345.678,'$99999.999') from dual;

TO_CHAR(123

-----------

 $12345.678

SQL> select to_char(12345.678,'$99,999.999') from dual;

TO_CHAR(1234

------------

 $12,345.678

SQL> select to_char(12345.678,'$99,999,999') from dual;

TO_CHAR(1234

------------

     $12,346

SQL> select to_char(12345.678,'$s99,999,999') from dual;

select to_char(12345.678,'$s99,999,999') from dual

                         *

第 1 行出现错误:

ORA-01481: 无效的数字格式模型

SQL> select to_char(12345.678,'s99,999,999') from dual;

TO_CHAR(123

-----------

    +12,346

字符转换成数字的函数：to_number(char[,fmt])

SQL> select to_number('$1000','9999') from dual;

select to_number('$1000','9999') from dual

                 *

第 1 行出现错误:

ORA-01722: 无效数字

SQL> select to_number('$1000','$9999') from dual;

TO_NUMBER('$1000','$9999')

--------------------------

                      1000

获取系统时间函数：sysdate,默认格式DD-MON-RR（日-月-年），下例：

SQL> select sysdate from dual;

SYSDATE

--------------

26-3月 -19

日期操作：add_months(date.i)，函数作用是返回在指定日期上添加的月份【i可以是任何数，若是小数则截取整数，若是负数，则相当于在原来的日期上剪去相应月份】

SQL> select add_months(sysdate,3),add_months(sysdate,-3),add_months(sysdate,0.5) from dual;

ADD_MONTHS(SYS ADD_MONTHS(SYS ADD_MONTHS(SYS

-------------- -------------- --------------

26-6月 -19     26-12月-18     26-3月 -19

SQL> select add_months(sysdate,3),add_months(sysdate,-3),add_months(sysdate,1.5) from dual;

ADD_MONTHS(SYS ADD_MONTHS(SYS ADD_MONTHS(SYS

-------------- -------------- --------------

26-6月 -19     26-12月-18     26-4月 -19

函数next_day(date,char)：如果char的值是“星期一”，则返回date指定日期的下一个星期一是哪天。

函数last_day(date)：返回月最后一天是几号。

函数month_between(date1,date2)：返回两个日期之间相隔的月份。下例：

SQL> select months_between('26-3月-19','10-1月-19')from dual;

MONTHS_BETWEEN('26-3月-19','10-1月-19')

---------------------------------------

                             2.51612903

函数extract(date from datetime)：获取当前时间的年或月或日，下例：

SQL> select extract(year from sysdate)from dual;

EXTRACT(YEARFROMSYSDATE)

------------------------

                    2019

sysdate可以表示时间类型，再看一个例子：

SQL> select extract(hour from timestamp '2019-3-26 13:35:26') from dual;

EXTRACT(HOURFROMTIMESTAMP'2019-3-2613:35:26')

---------------------------------------------

                                           13

字符函数：

1、大小写转换函数（变为大写upper('abc')、变为小写lower('SDD')、首写字母变为大写initcap('abs')）

2、获取子字符串函数（substr(char,[m[,n]]）m若是整数即从头开始算，m若是负数即从尾开始算 n可以省略 表示  从m到末尾

m=0 从字符串的首字母开始截取

m=负数  表示 从字符串的尾部开始截取

3、获取字符串长度函数（length(" abc"),空格也算一个字符数）

4、字符串连接函数（concat('ab','cd')   'ab'||'cd'）

5、去除字串函数（trim('a' from 'abcd')从字符串中去除一个字符，ltrim('abc','a')从字符串左边去除指定字符，rtrim('abcaa','a')从字符串右边去除指定字符，trim(' abc')去除字符串中首位空格）

6、替换函数（replace('abcde','ab','A')若最后'A'字符省略，就会被删除）

数值函数：四舍五入round（n,[m]）

【n表示要进行四舍五入的值，m表示保留小数点后几位或前几位】

省略m：0（即取整）

m>0：小数点后面m位

m<0：小数点前面m位

dual 目标表

1. 取整：

   CEIL(n)：取整时取最大值（上限）

   FLOOR(n)：取整时取最小值（下限）
   

2. 常用计算：

   ABS(n)：取绝对值

   MOD(m,n)：取余【m代表除数，n代表被除数，m、n中任意一个为null值，结果返回null】

   POWER(m,n)：返回m的n次幂

   sqrt(n)：开平方
   

3. 三角函数：

   正弦 SIN(n)、反正弦 ASIN(n)

   COS(n)、ACOS(n)

   TAN(n)、ATAN(n)

   例：SQL> select ceil(23.45),floor(23.45) from dual;

、取整函数（ceil(n)取最大整数、floor(n)取最小整数）、常用计算（绝对值abs(n)、取余数mod(m,n)、求平方power(m,n)）、求平方根sqrt(n)、三角函数正弦sin(n)、反正弦asin(n)、余弦cos(n)、反余弦acos(n)、正切tan(n)、反正切atan(n)）

字符函数：

1、大小写转换函数（变为大写upper('abc')、变为小写lower('SDD')、首写字母变为大写initcap('abs')）

2、获取子字符串函数（substr('abced',m,n)）m若是整数即从头开始算，m若是负数即从尾开始算

3、获取字符串长度函数（length(" abc"),空格也算一个字符数）

4、字符串连接函数（concat('ab','cd')   'ab'||'cd'）

5、去除字串函数（trim('a' from 'abcd')从字符串中去除一个字符，ltrim('abc','a')从字符串左边去除指定字符，rtrim('abcaa','a')从字符串右边去除指定字符，trim(' abc')去除字符串中首位空格）

6、替换函数（replace('abcde','ab','A')若最后'A'字符省略，就会被删除）

数值函数：四舍五入（round(n,m)）、取整函数（ceil(n)取最大整数、floor(n)取最小整数）、常用计算（绝对值abs(n)、取余数mod(m,n)、求平方power(m,n)）、求平方根sqrt(n)、三角函数正弦sin(n)、反正弦asin(n)、余弦cos(n)、反余弦acos(n)、正切tan(n)、反正切atan(n)）

m>0小数点前m位，四舍五入

m<0小数点后m位，四舍五入

函数胡作用：方便数据胡统计、处理查询结果（身份证年月日）

数值函数：四舍五入、求和

字符函数：大小写转换

日期函数：获取当前时间、查询时间间隔

转换函数：字符、数值、日期之间转换

课程总结

数值函数

四舍五入：round（n[,m])

取整：floor(n)

绝对值：abs(n)

取余数：mod(m,n)

平方根：sqrt(n)

三角函数：sin(n)、asin(n)等

字符函数

大小写转换：upper(char)、lower(char)

字符串连接：concat(char1,char2)

获取子字符串：substr(char,[m[,n]])

获取字符串长度：length(char)

日期函数

获取当前时间：

sysdate 系统时间

ADD_MONTHS(date，i）加或减掉i个月后日期

LAST_DAY(date）得到月最后一天

month——between（date1，date2) 两个日期间月份差，结果含有小数

extract(date from datetime) 提取指定日期的某部分，如年、月、日。

转换函数

to_char(date[,fmt[,params]]) 日期类型转字符类型

to_char(numbe[,fmt])  数值转字符

to_number(char[,fmt]) 字符转数值

转换函数

日期转换成字符的函数：to-char(date[,fmt[,params]])【date--将要转换的日期，fmt--转换成的格式，params--日期的语言(此参数通常不写)】

日期默认格式：dd-mon-rr

fmt格式定义：yy【两位的年】 yyyy【四位的年】 year【表示成英文形式】

mm【两位的月份】 month【表示英文的月份】

dd【两位的天数】 day【表示英文的日】

hh24【二十四小时制】 hh12【十二小时制】

mi【分钟】 ss【秒】

例子：

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY

-------------------

2019-03-26 15:04:33

SQL> select to_char(sysdate,'yy-mm-dd hh12:mi:ss') from dual;

TO_CHAR(SYSDATE,'

-----------------

19-03-26 03:05:31

SQL> select to_char(sysdate,'year-month-day hh12:mi:ss') from dual;

TO_CHAR(SYSDATE,'YEAR-MONTH-DAYHH12:MI:SS')

--------------------------------------------------------------------

twenty nineteen-3月 -星期二 03:07:23

TO_CHAR(SYSDATE,'YEAR-MM-DAYHH12:MI:SS')

----------------------------------------------------------------

twenty nineteen-03-星期二 03:09:00

SQL> select to_char(sysdate,'year-mm-dd hh12:mi:ss') from dual;

TO_CHAR(SYSDATE,'YEAR-MM-DDHH12:MI:SS')

---------------------------------------------------------

twenty nineteen-03-26 03:09:29

字符转换为日期的函数：to_date(date[,fmt[,params]])【to_date()按照系统默认格式显示日期】 下例：

SQL> select to_date('2019-3-26','yyyy-mm-dd') from dual;

TO_DATE('2019-

--------------

26-3月 -19

数字转换为字符的函数：to_char(number[,fmt])

9：显示数字并忽视前面的0

0：显示数字，位数不足用0补齐

.或D：显示小数点

，或G：显示千位符

$：美元符号

S：加正负号（前后都可以,但不能同时加在前后）

SQL> select to_char(12345.678,'$99999.999') from dual;

TO_CHAR(123

-----------

 $12345.678

SQL> select to_char(12345.678,'$99,999.999') from dual;

TO_CHAR(1234

------------

 $12,345.678

SQL> select to_char(12345.678,'$99,999,999') from dual;

TO_CHAR(1234

------------

     $12,346

SQL> select to_char(12345.678,'$s99,999,999') from dual;

select to_char(12345.678,'$s99,999,999') from dual

                         *

第 1 行出现错误:

ORA-01481: 无效的数字格式模型

SQL> select to_char(12345.678,'s99,999,999') from dual;

TO_CHAR(123

-----------

    +12,346

字符转换成数字的函数：to_number(char[,fmt])

SQL> select to_number('$1000','9999') from dual;

select to_number('$1000','9999') from dual

                 *

第 1 行出现错误:

ORA-01722: 无效数字

SQL> select to_number('$1000','$9999') from dual;

TO_NUMBER('$1000','$9999')

--------------------------

                      1000