单行函数
字符函数
函数 | 作用 | 效果 |
---|---|---|
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 )