ORACLE函数

单行函数

字符函数

函数	作用	效果
concat(‘Hello’, ‘World’) ||	连接	HelloWorld
substr(‘HelloWorld’,1,5) (str,index,length)	截取	Hello
length(‘HelloWorld’)	长度	10
instr(‘HelloWorld’,’H’) 数据库从1开始	indexOf()	1
trim(‘H’ from ‘HelloWorld’)	首位去除	elloWorld
replace(‘abcd’,’b’,’m’)	替换	amcd
lpad(‘abc’,6,’*’)	左填充	***abc
rpad(‘abc’,6,’*’)	右填充	abc***
upper/lower	大小写	ABC/abc
initcap	首字母大写	Abc

数字函数

函数	作用	效果
round(45.456, 2 / -1) 可以传负数	四舍五入	45.46 / 50
trunc(45.456, 2)	截断/去除	45.45
mod(17, 5)	求余	2​

日期函数

日期函数

yyyy 年 mm 月 dd 日 day 星期 hh 小时 mi 分钟 ss 秒

sysdate 系统当前时间 (函数) select sysdate from dual

select sysdate-1 from dual //日期减一个数子仍然是日期

select sysdate - to_date(‘1-8-2017’,’dd-mm-yyyy’) from dual //日期-日期 返回相差天数

//日期-日期 返回相差天数

函数 当前时间2017/9/11	作用	效果
months_between(sysdate,’1-8月-2017’)	两日期相差月数	1.336…
add_months(sysdate,1)	加若干月数	2017/10/11
next_day(sysdate,5) 1表示星期日	下一个星期几的日期	2017/9/14 星期四
last_day(sysdate)	本月最后一天	2017/9/30
round(sysdate,’year’)	保留到’year’四舍五入	2018/1/1
trunc(sysdate,’month’)	保留到’month’ 去除	2017/9/1​

转化函数

select to_char(sysdate,'yyyy-mm-dd') from dual
//使用双引号向格式添加字符
select to_char(sysdate,'yyyy"年"mm"月"dd"日"') from dual

//9表示数字，0表示0   格式整数的位数一定要大于数值的位数
select to_char(123123.456456,'$000,999.90') from dual
select to_char(123.456456,'$000,999.90') from dual

select to_date('2017/9/11','yyyy/mm/dd')-sysdate from dual

select to_number('123.123','9999.9999') from dual

通用函数

• nvl(列名,值) 列为null，返回值 值类型要与列的一致

  ​select nvl(manager_id,’无’) from employees //错，manager_id为number型

  ​select nvl(manager_id,999) from employees

• nvl2(列名，值1，值2) 不为null，返回值1 为null，返回值2

  ​select nvl2(manager_id,’有’,’无’) from employees

• nullif(expr1,expr2) 相等返回null， 不相等返回expr1

  ​select nullif(length(‘abc’),length(‘bcdd’)) from dual

• coalesce(expr1,expr2,…) 依次判断是否为null，不为null返回值，为null接着往下判断

  select coalesce(commission_pct,manager_id) from employees

• case expr when 条件1 then 结果1

  ​ when 条件2 then 结果2

  ​ else 结果3

  end

  select salary, case trunc(salary/10000,0) when 1 then '666'
                   else '000'
                     end from employees
  
  select salary,case when salary between 10000 and 20000 then '可以滴'
                   when salary>20000 then '牛x'
                   else '穷'
                     end  from employees

• decode( expr, 条件1,结果1,

  ​ 条件2,结果2,
  ​ 结果3)

  select department_id,decode(department_id,90,'研发',
                                     60,'测试',
                                     '不知道') from employees

聚合函数

count() avg() sum() max() min()

count(列名) 统计该列不为null的个数 count(*) 统计所有的个数

count(distinct 列名) 统计列不重复且非空的记录个数

avg(列名) 统计列不为空的平均值

avg(列名) = sum(列名) / count(列名) 与sum(列名) / count(*) 不同

avg(nvl(列名,0) = sum(列名) / count(*)

不可以在 where 字句中使用聚合函数

可以在 having 字句中使用聚合函数

group by

group by 列名 按列名分组

有 group by 的 sql 中 select 后只允许是节聚合函数或者 group by 的列

例如：查询按部门分组的最高平均工作和部门id

select department_id,max(avg(salary)) from employees group by department_id   --错误的
--group by department_id,但嵌套了两个聚合函数，不能加department_id

--运用子查询：
select department_id,avg(salary) from employees group by department_id having avg(salary) = (select max(avg(salary)) from employees group by department_id )