数据库常用的的函数

多行函数（或者叫聚合函数，组函数）

select count(ename) from emp;      -- count：计算个数

select max(sal) from emp;          -- max：计算最大值

select min(sal) from emp;          -- min：计算最小值

select sum(sal) from emp;          -- sum：计算和

select avg(sal) from emp;          -- avg：计算平均值

多行函数（组函数）不统计null值，解决方案：

对于COUNT()，如果你想要包含NULL值，可以使用COUNT(*)，它会计算所有行，包括NULL。

1. 优先选择不包含null的列进行计算

2. mysql：IFNULL函数

select sum(comm) from emp; -- 2200
select count(comm) from emp; -- 4
select avg(comm) from emp; -- 550
select sum(IFNULL(comm,0)) from emp; -- 157.142857

3. oracle：nvl/nvl2函数

select avg(nvl(comm,0)) from emp; -- 157.142857

单行函数:

① 字符函数

concat 字符串拼接

-- mysql和oracle都支持
select concat(ename,sal) from emp; 

-- mysql 单引号 和双引号 都支持，oracle必须是单引号；mysql和oracle都支持伪表dual
select concat('hello','world') from dual;  
select concat("hello","world") from dual; 

select 'hello' || 'world' from dual;  -- oracle中支持

lower / upper / initcap

-- mysql不支持initcap

select 'Hello World' 原始, lower('Hello World') 小写,upper('Hello World') 大写, initcap('hello world') 首字母大写 from  dual ;

substr 字符串截取

• substr(str,begin):将str从begin开始截取

• substr(str,begin,len) :将str从begin开始截取，截取的长度是len

select substr('Hello world',3) from dual ; -- llo world  MySql:从0开始数

select substr('Hello world',3,3) from dual ; -- llo

Oracle/Sql Server :从1开始数

length / lengthb

获取字符个数、字节数

• length :求取字符个数
• lengthb:获取字节数，一个中文占2/3个字节数。
  • utf-8：一个中文占3个字节数。
  • gbk:一个中文占2个字节数。

-- mysql不支持lengthb

select * from nls_database_parameters ; -- 查看NLS_CHARACTERSET字段值（查看oracle字符集）

select length('Hello world') 字符数, lengthb('Hello world') 字节数 from  dual ;

select length('西安') 字符数, lengthb('西安') 字节数 from  dual ;

instr 查找字符串

instr(a,b) :在a中，查找b的位置(从1开始数)。如果不存在，返回0 ;

select instr('hello world','ll') from dual ;  -- 3
select instr('hello world','llw') from dual ; -- 0

lpad左填充、rpad右填充

select lpad('hello',10,'*') 左填充, rpad('hello',10,'*') 右填充 from dual ;

trim / replace 替换

• trim():去掉左右两端空格
• trim('X' from str)去掉str左右两段的X

select trim('X' from 'XXhello worldXXX') from dual ;

select replace('Hello world','l','*') from dual ; -- 将l替换成*

② 数值函数

a. round(数字,n) :对数字的第n位小数进行四舍五入。
select round(67.183,2) 一, round(67.183,1) 二, round(67.183,0) 三,round(67.183,-1) from dual ;
-- 可以是负数

b. mod()：求余
select mod(124,3) from dual ;

c. trunc（数字,n）：截取（舍尾） 
-- mysql不支持trunc
select trunc(67.183,2) 一,trunc(67.183,1) 二, trunc(67.183,0) 三,	trunc(67.183,-1) from dual ;

③ 通用函数

nullif(a,b) :如果a=b，则返回null;否则返回a

select nullif('hello','hello') from dual ; -- null
select nullif('hello','world') from dual ; -- hello

coalesce :从左往右，寻找第一个不为null的值

select coalesce(null,null,'c') from dual ;

条件判断函数

• (1) decode(字段,条件1,表达式1,条件2,表达式2,...,条件n,表达式n,其他)

select ename, sal 涨前,job,decode(job,'PRESIDENT',sal+1000,'MANAGER',sal+800,sal+400) 涨后 from emp;

• (2) case表达式(case...end)

搜索CASE

select ename, sal 涨前,job,  case job
	when 'PRESIDENT' then sal+1000
	when 'MANAGER' then sal+800
	else   sal+400
	end  涨后
from emp ;

简单CASE表达式（标准CASE表达式）

  SELECT ename, sal, 
         CASE
           WHEN job = 'PRESIDENT' THEN sal + 1000
           WHEN job = 'MANAGER' THEN sal + 800
           ELSE sal + 400
         END AS '涨后'
  FROM emp;

④ 转换函数

• 数字/日期<-->字符串

TO_CHAR和TO_DATE用于在日期和字符串之间转换，TO_NUMBER用于将字符串转换为数字。

⑤ oracle的日期函数

- a.关键字sysdate：获取当前时间

    - select sysdate from dual ;

- b.格式化时间 to_char(时间,格式) 

    - select to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mi:ss') from dual;

- c.日期 可以+/-数字

    - select (sysdate-1) 昨天,sysdate 今天,sysdate+1 明天 from dual ;	

- d.日期只能-日期，不能+日期

    - select sysdate-hiredate 工作天数 from emp ;

- e. months_between(日期1,日期2) :相差的月数。日期1- 日期2 

    - select months_between(sysdate,hiredate) 工作月数 from emp ;

- f: add_months(时间,月数):增加月份

    - select add_months(sysdate,3) from dual ; 

- g. last_day（时间） :当月的最后一天

    - select  last_day(sysdate) from dual ;

- i. next_day(时间,'星期N'):下一个星期N是哪一天 

    - select  next_day(sysdate,'星期五') from dual ;

- j. 四舍五入

    - select round(sysdate,'month') 月,round(sysdate,'year') from dual ;

- h.截取（舍尾）

    - select trunc(sysdate,'month') 月,trunc(sysdate,'year') from dual ;

- 日期格式

    - yy:两位数字的年份 2004--04
    - yyyy:四位数字的年份 2004年
    - mm:两位数字的月份 8月--08
    - dd:两位数字的天数 30号--30
    - hh24:二十四小时制8点--20
    - hh12:十二小时制8点--08
    - mi,ss--显示分钟\秒
    - day显示星期几
    - month显示几月
    - year显示年