Oracle的常用基础 escape(转义关键字符)、round函数、over()函数

最新推荐文章于 2023-04-10 21:07:24 发布

一个阳光努力的程序盐

最新推荐文章于 2023-04-10 21:07:24 发布

阅读量2.2k

点赞数

分类专栏： Oracle之基础sql和基础知识

本文链接：https://blog.csdn.net/qq_33879355/article/details/70918266

版权

Oracle之基础sql和基础知识专栏收录该内容

12 篇文章 0 订阅

订阅专栏

escape关键字：

select * from dept where DEPARTNAME like '\_%' escape '\';

select * from dept where DEPARTNAME like 'a_%' escape 'a';

后面escape是转义字符，代表a后面的是字符了，无特殊意义。

round函数、 over函数

准备数据：

CREATE
TABLE EMP
(
EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
)

insert into SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7369.0, 'SMITH', 'CLERK', 7902.0, TO_TIMESTAMP('1980-12-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 2758.3, null, 20.0);
insert into SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7499.0, 'ALLEN', 'SALESMAN', 7698.0, TO_TIMESTAMP('1981-02-20 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 2758.3, 300.0, 30.0);
insert into SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (8001.0, '小王', 'CLERK', null, null, 1001.0, null, null);
insert into SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (1100.0, '321', null, null, null, 241.0, null, null);
insert into SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7521.0, 'WARD', 'SALESMAN', 7698.0, TO_TIMESTAMP('1981-02-22 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 2758.3, 500.0, 30.0);
insert into SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7566.0, 'JONES', 'MANAGER', 7839.0, TO_TIMESTAMP('1981-04-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 3570.0, null, 20.0);
insert into SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7654.0, 'MARTIN', 'SALESMAN', 7698.0, TO_TIMESTAMP('1981-09-28 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 2758.3, 1400.0, 30.0);
insert into SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7698.0, 'BLAKE', 'MANAGER', 7839.0, TO_TIMESTAMP('1981-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 3420.0, null, 30.0);
insert into SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7788.0, 'SCOTT', 'ANALYST', 7566.0, TO_TIMESTAMP('1987-04-19 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 3000.0, null, 20.0);
insert into SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7839.0, 'KING', 'PRESIDENT', null, TO_TIMESTAMP('1981-11-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 5000.0, null, 10.0);
insert into SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7844.0, 'TURNER', 'SALESMAN', 7698.0, TO_TIMESTAMP('1981-09-08 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 2758.3, .0, 30.0);
insert into SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7876.0, 'ADAMS', 'CLERK', 7788.0, TO_TIMESTAMP('1987-05-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 2758.3, null, 20.0);
insert into SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7900.0, 'JAMES', 'CLERK', 7698.0, TO_TIMESTAMP('1981-12-03 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 2758.3, null, 30.0);
insert into SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7902.0, 'FORD', 'ANALYST', 7566.0, TO_TIMESTAMP('1981-12-03 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 3000.0, null, 20.0);
insert into SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7934.0, 'MILLER', 'CLERK', 7782.0, TO_TIMESTAMP('1982-01-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 2758.3, null, 10.0);

round函数：

ROUND(2.149， 0) 将 2.149 四舍五入到一个整数结果为2。

ROUND(2.15， 1) 将 2.15 四舍五入到一个小数位，结果为2.2。

ROUND(2.149， 1) 将 2.149 四舍五入到一个小数位结果为2.1。

ROUND(-1.475， 2) 将 -1.475 四舍五入到两小数位结果为-1.48)。

ROUND(21.5， -1) 将 21.5 四舍五入到小数点左侧一位结果为20。

如果：eg: select ROUND(21.5， 9) from dual; 结果是：21.5

over函数：

select deptno,ename,sal,
sum(sal) over (order by ename) 连续求和,
sum(sal) over () 总和,
100*round(sal/sum(sal) over (),4) "份额(%)"
from emp

select deptno,ename,sal,
sum(sal) over (partition by deptno order by ename) 部门连续求和,--各部门的薪水"连续"求和
sum(sal) over (partition by deptno) 部门总和, -- 部门统计的总和，同一部门总和不变
100*round(sal/sum(sal) over (partition by deptno),4) "部门份额(%)",
sum(sal) over (order by deptno,ename) 连续求和, --所有部门的薪水"连续"求和
sum(sal) over () 总和, -- 此处sum(sal) over () 等同于sum(sal)，所有员工的薪水总和
100*round(sal/sum(sal) over (),4) "总份额(%)"
from emp

总结：over（）会连续起作用，如果和partition by 用会个个数据组的和，如果partition by 和order by用则会在数据组内部进行累加