concat 连接符

select concat('hello','123') from dual ;
-- oracle中支持||

多行函数

1. count：计算个数 （一般选择非空的列：主键）（ count(*) ）
   select count(ename) from emp;

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

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

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

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

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

mysql：

1. 选择不包含非空的列进行计算
2. 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

oracle：

• a. nvl/nvl2
  select avg(nvl(comm,0)) from emp; -- 157.142857

单行函数:

① 字符函数

a. lower / upper / initcap

-- initcap mysql不支持

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

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

select substr('Hello world',3) from dual ; -- 从1开始数

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

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

d. length / lengthb

-- lengthb mysql不支持

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

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

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

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

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

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

f. lpad左填充、rpad右填充

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

g. trim()

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

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

h. replace() 替换

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. trunc（数字,n）：截取（舍尾） 
-- mysql不支持trunc
select trunc(67.183,2) 一,trunc(67.183,1) 二, trunc(67.183,0) 三,	trunc(67.183,-1) from dual ;

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

③ 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显示年

④ oracle的通用函数

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

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

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

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

• d. 条件判断函数

• (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)

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

⑤ 转换函数

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