SQL-oracle内置函数

发表于 更新于 阅读次数：

表达式

1
2
3
4
5
6
7
8
-- 替换符  
SELECT q'=I'm fine.=' FROM dual;

-- 连接符
SELECT 'Im '||'fine.' FROM dual;

-- 空值处理
select empno, sal, nvl(sal, 0), ename, nvl(ename, '无') from emp;

多行函数

1
2
3
4
5
6
7
8
9
10
-- 求和
select sum(sal) from emp;
-- 求平均值
select avg(sal) from emp;
-- 最大值
select max(sal) from emp;
-- 最小值
select min(sal) from emp;
-- 计数
select count(empno) from emp;

字符处理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- lower 转小写
select ename, lower(ename) from emp;

-- upper 转大写
select ename, upper(ename) from emp;

-- initcap 单词首字母大写
select ename, initcap(ename) from emp;

-- concat 字符连接
select ename, concat(ename, '12') from emp; -- 最多两个参数
-- substr 截取
select ename, substr(ename, 0, 1) from emp; -- 取名称的第一个字符
-- length 字符串长度
select ename, length(ename) from emp;
--  replace 替换
-- trim 去除首尾空格
-- instr 字符搜索
-- rpad 从右边填充字符
-- lpad 从左边填充字符

日期

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 格式化
select sysdate, to_char(sysdate, 'yyyy-MM-dd hh24:mi:ss') from dual;

-- moths_between两个日期间隔的月数
select empno, ename,
       hiredate, -- 入职时间
       months_between(sysdate, hiredate), -- 入职多少个月
       (sysdate - hiredate) months -- 入职所少天了
from emp;
-- next_day 返回下一个星期一的日期
select next_day(sysdate, 1) from dual;
select next_day(sysdate, 'Mon') from dual;

-- last_day 返回该月的最后一天的日期
select last_day(sysdate) from dual;

-- add_months 返回几个月以后的日期
select add_months(sysdate, 3) from dual;

数值

1
2
3
4
5
6
7
8
9
10
11
12
13
-- round 四舍五入
select round(17.561, -1) from dual; -- 20
select round(17.561,2) from dual; -- 17.56

-- trunc 截断
select trunc(17.561, -1) from dual; -- 10
select trunc(17.561,2) from dual; -- 17.56

-- mod 取余
select mod(10,3) from dual; -- 1

-- 加减乘除运算
+ - * / ( )

数值、字符、日期之间的类型转换

1
2
3
4
5
6
7
8
9
10
11
## 日期转字符
select sysdate, to_char(sysdate, 'yyyy-MM-dd hh24:mi:ss') from dual;

## 字符转日期类型 (都是日期类型才能做计算)
select to_date('2018-06-07','yyyy-MM-dd') from dual;

select EMPNO,
       EMPNO+'213', -- 隐式转换(有索引时不会执行，一般不能用)
       EMPNO+ to_number('213') , -- 字符转数值
       EMPNO || '123' -- 字符串拼接
from EMP;

where后的条件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
= >= <= > < <> != 
不能用于null值比较;
使用is (not) null 比较空值

-- between .. and
select * from emp where sal  between 500 and 1000;

-- and
select * from emp where deptno=20 and sal=800;

-- or
select * from emp where deptno=20 or sal=800;

-- in (null值自动忽略)
select ename,job,sal from emp where job in ('CLERK','ANALYST');
-- not in (不能有null值)
select ename,job,sal from emp where job in ('CLERK','ANALYST') and sal not in (1000,3000,5000);

-- like
select * from emp where empname like '%sc%'; -- 'xx%'走索引;'%xx%'不走索引

-- all
all的含义是将一个值与all后的每一个值去比较，满足全部比较条件才为true
SELECT promo_id FROM promotions WHERE promo_cost < ALL (SELECT MAX(promo_cost) FROM promotions GROUP BY (promo_end_date - promo_begin_date) );

-- any(some)
any的含义是将一个值与any后的每一个值去比较，满足任何一个比较条件即为true

集合

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- union求并集，公共部分只有包含一次 
例：求emp表ename中含’A‘或含有‘M’ 
select * from emp where ename like '%A%' 
    union 
select * from emp where ename like '%M%'; 
 
-- union all求集并，公共部分只有包含二次 
例：求emp表ename中含’A‘或含有‘M’ 
select * from emp where ename like '%A%' 
    union all 
select * from emp where ename like '%M%'; 
 
-- intersect求交集，只有包含公共部分 
例：求emp表ename中即含’A‘又含有‘M’ 
select * from emp where ename like '%A%' 
    intersect 
select * from emp where ename like '%M%'; 
 
-- minus求差集，求集合A去除集合A和集合B的交集 
例：求emp表sal从700到1200 
select * from emp where sal between 700 and 1300 
    minus 
select * from emp where sal between 1200 and 1400;