SQL> -- sum 函数用来求和
SQL> -- 计算一下每一个月要发多少工资,不含奖金
SQL> select sum(sal) from emp;
SUM(SAL)
----------
29025
SQL> select sal from emp;
SAL
----------
800
1600
1250
2975
1250
2850
2450
3000
5000
1500
1100
SAL
----------
950
3000
1300
已选择14行。
SQL> -- 计算一下每年发的奖金的和
SQL> select sum(comm) from emp;
SUM(COMM)
----------
2200
SQL> select comm from emp;
COMM
----------
300
500
1400
0
COMM
----------
已选择14行。
SQL> -- 多行函数自动虑空
SQL> -- 计算一下公司的平均工资
SQL> select sum(sal)/count(*) 平均工资1,sum(sal)/count(sal) 平均工资2,avg(sal) 平均工资3 from emp;
平均工资1 平均工资2 平均工资3
---------- ---------- ----------
2073.21429 2073.21429 2073.21429
SQL> -- avg 用来计算某一个字段的平均值
SQL> -- 计算一下平均奖金
SQL> select sum(comm)/count(*) 平均奖金1, sum(comm)/count(comm) 平均奖金2,avg(comm) 平均奖金3 from emp;
平均奖金1 平均奖金2 平均奖金3
---------- ---------- ----------
157.142857 550 550
SQL> -- 多行函数的虑空并不是在所有的场合都适用,如果你不希望他的虑空起作用你可以采用函数的嵌套来屏蔽该功能
SQL> ed
SP2-0110: 无法创建保存文件 "afiedt.buf"
SQL> define;
DEFINE _DATE = "16-3月 -16" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
DEFINE _USER = "SCOTT" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1002000100" (CHAR)
DEFINE _EDITOR = "Notepad" (CHAR)
DEFINE _O_VERSION = "Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options" (CHAR)
DEFINE _O_RELEASE = "1002000100" (CHAR)
SQL> DEFINE _EDITOR="notepad++";
SQL> ed
SP2-0110: 无法创建保存文件 "afiedt.buf"
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- --------------
SAL COMM DEPTNO
---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80
800 20
7499 ALLEN SALESMAN 7698 20-2月 -81
1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81
1250 500 30
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- --------------
SAL COMM DEPTNO
---------- ---------- ----------
7566 JONES MANAGER 7839 02-4月 -81
2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81
1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81
2850 30
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- --------------
SAL COMM DEPTNO
---------- ---------- ----------
7782 CLARK MANAGER 7839 09-6月 -81
2450 10
7788 SCOTT ANALYST 7566 19-4月 -87
3000 20
7839 KING PRESIDENT 17-11月-81
5000 10
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- --------------
SAL COMM DEPTNO
---------- ---------- ----------
7844 TURNER SALESMAN 7698 08-9月 -81
1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87
1100 20
7900 JAMES CLERK 7698 03-12月-81
950 30
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- --------------
SAL COMM DEPTNO
---------- ---------- ----------
7902 FORD ANALYST 7566 03-12月-81
3000 20
7934 MILLER CLERK 7782 23-1月 -82
1300 10
已选择14行。
SQL> ed
SP2-0110: 无法创建保存文件 "afiedt.buf"
SQL> define;
DEFINE _DATE = "16-3月 -16" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
DEFINE _USER = "SCOTT" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1002000100" (CHAR)
DEFINE _EDITOR = "notepad++" (CHAR)
DEFINE _O_VERSION = "Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options" (CHAR)
DEFINE _O_RELEASE = "1002000100" (CHAR)
SQL> DEFINE _EDITOR = "Notepad++";
SQL> ed
SP2-0110: 无法创建保存文件 "afiedt.buf"
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- --------------
SAL COMM DEPTNO
---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80
800 20
7499 ALLEN SALESMAN 7698 20-2月 -81
1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81
1250 500 30
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- --------------
SAL COMM DEPTNO
---------- ---------- ----------
7566 JONES MANAGER 7839 02-4月 -81
2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81
1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81
2850 30
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- --------------
SAL COMM DEPTNO
---------- ---------- ----------
7782 CLARK MANAGER 7839 09-6月 -81
2450 10
7788 SCOTT ANALYST 7566 19-4月 -87
3000 20
7839 KING PRESIDENT 17-11月-81
5000 10
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- --------------
SAL COMM DEPTNO
---------- ---------- ----------
7844 TURNER SALESMAN 7698 08-9月 -81
1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87
1100 20
7900 JAMES CLERK 7698 03-12月-81
950 30
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- --------------
SAL COMM DEPTNO
---------- ---------- ----------
7902 FORD ANALYST 7566 03-12月-81
3000 20
7934 MILLER CLERK 7782 23-1月 -82
1300 10
已选择14行。
SQL> ed
SP2-0110: 无法创建保存文件 "afiedt.buf"
SQL> select sum(comm)/count(*) 正确的,avg(nvl(comm,0)) from emp;
正确的 AVG(NVL(COMM,0))
---------- ----------------
157.142857 157.142857
SQL> -- 查询工资最高和最低的员工信息
SQL> select max(sal) 最高工资, min(sal) 最低工资 from emp;
最高工资 最低工资
---------- ----------
5000 800
SQL> select max(comm) , min(comm) from emp;
MAX(COMM) MIN(COMM)
---------- ----------
1400 0
SQL> select max(nvl(comm,null)) from emp;
MAX(NVL(COMM,NULL))
-------------------
1400
SQL> -- 分组
SQL> -- 求每一个部门的工资总和 和平均工资
SQL> select deptno,sum(sal),avg(sal)
2 from emp
3 group by deptno;
DEPTNO SUM(SAL) AVG(SAL)
---------- ---------- ----------
30 9400 1566.66667
20 10875 2175
10 8750 2916.66667
SQL> select * from emp where deptno = 20;
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- --------------
SAL COMM DEPTNO
---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80
800 20
7566 JONES MANAGER 7839 02-4月 -81
2975 20
7788 SCOTT ANALYST 7566 19-4月 -87
3000 20
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- --------------
SAL COMM DEPTNO
---------- ---------- ----------
7876 ADAMS CLERK 7788 23-5月 -87
1100 20
7902 FORD ANALYST 7566 03-12月-81
3000 20
SQL> set linesize 200;
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7902 FORD ANALYST 7566 03-12月-81 3000 20
SQL> -- 统计部门的平均工资，部门号，岗位
SQL> select deptno,avg(sal),job
2 from emp
3 group by deptno;
select deptno,avg(sal),job
*
第 1 行出现错误:
ORA-00979: 不是 GROUP BY 表达式
SQL> -- group by 后面必须要跟select后面没有在多行函数里面的字段
SQL> select deptno,avg(sal),job
2 from emp
3 group by deptno,job;
DEPTNO AVG(SAL) JOB
---------- ---------- ------------------
20 950 CLERK
30 1400 SALESMAN
20 2975 MANAGER
30 950 CLERK
10 5000 PRESIDENT
30 2850 MANAGER
10 1300 CLERK
10 2450 MANAGER
20 3000 ANALYST
已选择9行。
SQL> -- 分组函数的过滤
SQL> -- 统计部门号为20的部门下的所有职位的平均工资
SQL> select deptno,avg(sal),job
2 from emp
3 where deptno=20
4 group by deptno,job;
DEPTNO AVG(SAL) JOB
---------- ---------- ------------------
20 950 CLERK
20 2975 MANAGER
20 3000 ANALYST
SQL> select deptno,avg(sal),job
2 from emp
3 group by deptno,job
4 having detpno=20;
having detpno=20
*
第 4 行出现错误:
ORA-00904: "DETPNO": 标识符无效
SQL> 4
4* having detpno=20
SQL> c /detpno/deptno;
4* having deptno=20
SQL> /
DEPTNO AVG(SAL) JOB
---------- ---------- ------------------
20 950 CLERK
20 2975 MANAGER
20 3000 ANALYST
SQL> -- 统计平均工资大于2000的部门
SQL> select deptno,avg(sal)
2 from emp
3 where avg(sal) > 2000
4 group by deptno;
where avg(sal) > 2000
*
第 3 行出现错误:
ORA-00934: 此处不允许使用分组函数
SQL> select deptno,avg(sal)
2 from emp
3 group by deptno
4 having avg(sal) > 2000;
DEPTNO AVG(SAL)
---------- ----------
20 2175
10 2916.66667
SQL> --1. where和having都可以用来做条件的过滤操作,但是where后面不能跟分组函数,having后面可以跟分组函数
SQL> -- 2 尽量使用where 因为他的效率更高
SQL> select deptno,job,sum(sal) from emp group by rollup(detpno,job);
select deptno,job,sum(sal) from emp group by rollup(detpno,job)
*
第 1 行出现错误:
ORA-00904: "DETPNO": 标识符无效
SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);
DEPTNO JOB SUM(SAL)
---------- ------------------ ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
20 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
DEPTNO JOB SUM(SAL)
---------- ------------------ ----------
30 9400
29025
已选择13行。
SQL> break on deptno skip 2;
SQL> /
DEPTNO JOB SUM(SAL)
---------- ------------------ ----------
10 CLERK 1300
MANAGER 2450
PRESIDENT 5000
8750
20 CLERK 1900
ANALYST 6000
MANAGER 2975
10875
DEPTNO JOB SUM(SAL)
---------- ------------------ ----------
30 CLERK 950
MANAGER 2850
SALESMAN 5600
9400
29025
已选择13行。
SQL> break on null;
SQL> /
DEPTNO JOB SUM(SAL)
---------- ------------------ ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
20 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
DEPTNO JOB SUM(SAL)
---------- ------------------ ----------
30 9400
29025
已选择13行。
SQL> break on deptno skip 2;
SQL> /
DEPTNO JOB SUM(SAL)
---------- ------------------ ----------
10 CLERK 1300
MANAGER 2450
PRESIDENT 5000
8750
20 CLERK 1900
ANALYST 6000
MANAGER 2975
10875
DEPTNO JOB SUM(SAL)
---------- ------------------ ----------
30 CLERK 950
MANAGER 2850
SALESMAN 5600
9400
29025
已选择13行。
SQL> spool off;
707
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
