Oracle数据库查询练习（二）过滤、排序、函数

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了Oracle数据库查询练习（二）过滤、排序、函数，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含6093字，纯文字阅读大概需要9分钟。

内容图文

本人菜鸡，如有错误，恳请指出。

---

使用到的函数与关键字

Notes
聚合函数（组函数）：用来输入多个数据，输出一个数据的，如sum,min,max,avg,count
 
为什么where不能跟聚合函数？
聚集函数也叫列函数，它们都是基于整列数据进行计算的，而where子句则是对数据行进行过滤的(这里过滤是在一个记录里边过滤的,基于"行")，在筛选过程中依赖“基于已经筛选完毕的数据得出的计算结果”是一种悖论，这是行不通的。更简单地说，因为聚集函数要对全列数据时行计算，因而使用它的前提是：结果集已经确定！
 
所有包含于SELECT 列表中，而未包含于组函数中的列都必须包含于 GROUP BY 子句中。
 
where子句在查询过程中执行优先级别优先于聚合语句
 
1.WHERE 子句用来筛选 FROM 子句中指定的操作所产生的行。
2.GROUP BY 子句用来分组 WHERE 子句的输出。
3.HAVING 子句用来从分组的结果中筛选行。

• case
  

• order by

• group by 传送门（点我）
  

• having

---

员工表（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)	所属部门编号

部门表（dept）
字段	类型	描述
deptno	number	部门编号
dname	number	部门名称
loc	number	地址

工资等级表（salgrade）
字段	类型	描述
grade	number	等级名称
losal	number	此等级的最低工资
hisal	number	此等级的最高工资

奖金表（bonus）
字段	类型	描述
ename	varchar2(10)	员工姓名
job	varchar2(9)	员工岗位
sal	number	员工工资
comm	number	员工奖金（commission）

--员工表
create table EMP
(
empno NUMBER(4) primary key,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
);

insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (1, 'dog', 'code', 7369, to_date('05-06-2018', 'dd-mm-yyyy'), 5000, 5000, 10);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980', 'dd-mm-yyyy'), 800, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-02-1981', 'dd-mm-yyyy'), 1600, 300, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7521, 'WARD', 'SALESMAN', 7698, to_date('22-02-1981', 'dd-mm-yyyy'), 1250, 500, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, 'JONES', 'MANAGER', 7839, to_date('02-04-1981', 'dd-mm-yyyy'), 2975, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-09-1981', 'dd-mm-yyyy'), 1250, 1400, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7698, 'BLAKE', 'MANAGER', 7839, to_date('01-05-1981', 'dd-mm-yyyy'), 2850, null, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7788, 'SCOTT', 'ANALYST', 7566, to_date('19-04-1987', 'dd-mm-yyyy'), 3000, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981', 'dd-mm-yyyy'), 5000, null, 10);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7844, 'TURNER', 'SALESMAN', 7698, to_date('08-09-1981', 'dd-mm-yyyy'), 1500, 0, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7876, 'ADAMS', 'CLERK', 7788, to_date('23-05-1987', 'dd-mm-yyyy'), 1100, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7900, 'JAMES', 'CLERK', 7698, to_date('03-12-1981', 'dd-mm-yyyy'), 950, null, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7902, 'FORD', 'ANALYST', 7566, to_date('03-12-1981', 'dd-mm-yyyy'), 3000, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7934, 'MILLER', 'CLERK', 7782, to_date('23-01-1982', 'dd-mm-yyyy'), 1300, null, 10);

--部门表
create table DEPT
(
deptno NUMBER(2) primary key,
dname VARCHAR2(14),
loc VARCHAR2(13)
);


insert into DEPT (deptno, dname, loc)
values (10, '财务', 'NEW YORK');
insert into DEPT (deptno, dname, loc)
values (20, '研发', 'DALLAS');
insert into DEPT (deptno, dname, loc)
values (30, '销售', 'CHICAGO');
insert into DEPT (deptno, dname, loc)
values (40, '运营', 'BOSTON');

--工资等级表
create table SALGRADE
(
GRADE NUMBER,
LOSAL NUMBER,
HISAL NUMBER
);
INSERT INTO SALGRADE VALUES (1,  700, 1200);
INSERT INTO SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SALGRADE VALUES (5, 3001, 9999);

--奖金表
create table BOUNS
(
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER,
COMM NUMBER
);

---

（1） 查询每名员工的empno，ename，sal和dept,要求使用CASE 表达式将各部门由部门号转换为对应的中文名称：10—财务部，20—研发部，30—销售部

SELECT empno,ename,sal,
CASE deptno 
WHEN 10 THEN '财务部'
WHEN 20 THEN '研发部'
WHEN 30 THEN '销售部'
END  "部门"
FROM "EMP";

（2）使用子查询创建表，并将新表中的记录查询出来

CREATE TABLE emp10 AS SELECT * FROM emp WHERE deptno=10;
SELECT * FROM emp10;

（3）统计平均工资大于 500 的部门，显示部门号和平均工资

SELECT deptno "部门号",AVG(nvl(sal,0)+nvl(comm,0)) "平均工资" FROM emp
GROUP BY deptno
HAVING AVG(nvl(sal,0)+nvl(comm,0))>500;

（4）查找部门 30 中得到最多奖金的员工姓名

SELECT ename "员工姓名" FROM "EMP" 
WHERE comm=(
SELECT MAX(nvl(comm,0)) FROM "EMP"
GROUP BY deptno
HAVING deptno = 30
);

（5）查找工资大于自己部门平均工资的员工信息

• 写法一

SELECT * FROM emp  e
WHERE nvl(sal,0)>
(
  SELECT AVG(nvl(sal,0)) FROM "EMP"
  GROUP BY deptno
  HAVING deptno=e.deptno
);

• 写法二

SELECT * FROM emp e WHERE sal > (SELECT AVG(nvl(sal,0)) FROM emp WHERE e.deptno=deptno);

（6）查找平均工资大于 2000 的工作岗位

SELECT job "工作岗位" FROM "EMP"
GROUP BY job
HAVING AVG(nvl(sal,0))>2000;

（7）分组统计每个部门下，每种职位的平均补贴（没奖金的设为0）和总工资(包括奖金)
提示：因为comm属性有空值，需要用nvl()函数处理comm属性

SELECT deptno "部门号",job "职位",AVG(nvl(comm,0)) "平均补贴",SUM(nvl(sal,0)) "总工资" FROM "EMP"
GROUP BY deptno,job;

（8）以工作岗位分组，找出平均工资最高的两种岗位。
提示：排序后在where中指定 rownum < 3;

SELECT * FROM (
SELECT job "工作岗位",AVG(nvl(sal,0)) "平均工资" FROM "EMP"
GROUP BY job
ORDER BY "平均工资" DESC
)
WHERE rownum<3;

内容总结

以上是互联网集市为您收集整理的Oracle数据库查询练习（二）过滤、排序、函数全部内容，希望文章能够帮你解决Oracle数据库查询练习（二）过滤、排序、函数所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---