Oracle-常用函数

发表于 更新于 分类于 本文字数： 3.1k 阅读时长 ≈ 3 分钟
主要包含了Oracle SQL的一些常用函数，例如CEIL向上取整、向下取整floor、取余MOD等。

CEIL 向上取整

1
2
3
SELECT CEIL(9.5) FROM DUAL;
-- 结果：10

FLOOR 向下取整

1
2
select floor(9.5) from dual;
-- 结果：9

MOD 取余

1
2
select mod(10,3) from dual;
-- 结果：1

round 按精度四舍五入

1
2
select round(8.5555,2) from dual;
-- 结果：8.56

|| 拼接符

1
2
 select j.emplid ||':'|| j.effdt  from ps_job j
-- 结果：0000700002:01-11月-22

CONCAT 拼接

1
2
3
select concat('a','b') from dual ;
-- 结果：ab

BETWEEN AND 取两者之间

1
select id from ps_job j where j.deptid between 30000 and 40000;

IN 、ANY、ALL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- IN
select id from ps_job jwhere id in ('00001','00002','00003')
-- ANY
SELECT  empno, ename, job
   FROM    emp
   WHERE   sal < ANY 
 			(SELECT	sal
          		FROM	emp
        		WHERE	job = 'CLERK')
   AND	    job <> 'CLERK';
/*
分析：后半句sql中，取出job = 'CLERK'的记录对应的薪酬值(多个)，前半句sql根据后面得到的多个值，只要小于其中一个薪酬值，就取出该条记录。
*/
-- ALL
SELECT  empno, ename, job
    FROM    emp
    WHERE   sal > ALL 
  		 (SELECT	avg(sal)
   			FROM		emp
  			GROUP BY	deptno);
/*
分析：后半句sql中，按部门分组取出每个组的平均薪酬值（多个），前半句sql根据后面得到的多个值，只要大于得到的所有薪酬值，就取出该条记录。
*/

LIKE 模糊

1
2
select * from ps_job j where j.id like '_0%'
-- ‘_’站位1个字符，‘%’站位多个字符。

NVL 空判断

1
2
3
select NVL(expr1,expr2) FROM DUAL;
-- 结果：如果expr1为空，则取到值为：expr2。

DECODE

1
2
select J.EMPLID,decode(J.DEPTID,0204000302,'选中'，'未选中') from PS_JOB J
-- 结果：仅DEPTID=‘0204000302’的记录查询出的结果结果为：“选中”，其余为：“未选中”。

CASE

1
2
3
4
5
6
7
8
9
10
SELECT CASE
         WHEN &A = 1 THEN
          'A'
         WHEN &A = 2 THEN
          'B'
         ELSE
          'C'
       END CASE
  FROM DUAL
-- 对&A进行判断，如果&A等于1，则返回A,如果&A等于2，返回B，其他情况返回C；

union 去重合集

1
2
3
4
SELECT 'a' from dual
UNION 
SELECT 'a' from dual
-- 结果：a

union all 不去重合集

1
2
3
4
5
6
7
8
SELECT 'a' from dual
UNION ALL
SELECT 'a' from dual
/*
结果：
a
a
*/

LOWER 、UPPER、INITCAP 大小写转换

1
2
3
4
5
6
SELECT LOWER(J.NAME) FROM PS_JOB J;
-- 结果：查询结果中的name全是小写。
SELECT UPPER(J.NAME) FROM PS_JOB J;
-- 结果：查询结果中的name全是大写。
SELECT INITCAP(J.NAME) FROM PS_JOB J;
-- 结果：查询结果中的name首字母是大写。

SUBSTR 截取

1
2
select substr('abcdef',1,3) from dual ;
-- 结果：abc

LENGTH 字符长度

1
2
select length('abcdef') from dual;
-- 结果：6

INSTR 字符所在位置

1
2
select instr('abcdefg','a') from dual;
-- 结果：1

LPAD 左起填充满位数

1
2
select lpad('abcd',5,'*') from dual;
-- 结果：*abcd

RPAD 右起填充满位数

1
2
select rpad('abcd',5,'*') from dual;
-- 结果：abcd*

TRIM 删除对应的字符

1
2
select trim('a' from 'abcdef') from dual;
-- 结果：bcdef。注意：只能删除一个字符，删除多个字符会报错。

AVG 求平均函数

1
select avg(column) from table1;

COUNT 求数量

1
select count(column) from table1;

MAX 求最大值

1
select max(column) from table1;

MIN 求最小值

1
select min(column) from table1;

SUM 求和

1
select sum(column) from table1;

STDDEV 标准差函数

1
select stddev(column) from table1;

VARIANCE 方差函数

1
select variance(column) from table1;

group by 分组函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
select deptno, AVG(sal) FROM emp GROUP BY deptno;
/* 
先按部门分组，再计算平均值。
结果：DEPTNO  AVG(SAL)
		10      2889
		20	    3330
		30      1000
*/		

select deptno,JOB,AVG(sal) FROM emp GROUP BY deptno,JOB ;
/* 
先按部门和工作分组，再计算平均值。
结果：DEPTNO JOB     AVG(SAL)
		10  CLIK    2889
		20	LUCY    3330
		30  GOTOS   1000
*/

HAVING 函数

1
2
3
4
5
6
SELECT	column, group_function
FROM		table
[WHERE	condition]
[GROUP BY group_by_expression]
[HAVING	group_condition]
[ORDER BY	column];
1
2
3
4
5
6
7
SELECT  deptno, max(sal)
  FROM     emp
  GROUP BY deptno
  HAVING   max(sal)>2900;
/*
分析：先按deptno字段分组，再取出sal大于2900的记录。
*/
1
2
3
4
5
6
7
8
9
10
11
SELECT	deptno, MIN(sal)
 FROM	emp
 GROUP BY	deptno
 HAVING	MIN(sal) >
 	(SELECT	MIN(sal)
 	FROM	emp
 	WHERE	deptno = 20);
 /*
 分析：先取emp表中部门编号为20的记录中最小额薪酬值sal1，然后将emp表按部门标号分组，并且取每组中薪酬记录最小值sal，这个值大于前面的sal1的那些记录会被取出。
 */