group by 和 having子句

group by用于对查询的结果分组统计， having子句用于限制分组显示结果。

问题：如何显示每个部门的平均工资和最高工资？

SELECT AVG(sal), MAX(sal), deptno FROM emp GROUP by deptno;

（注意：这里暗藏了一点，如果你要分组查询的话，分组的字段deptno一定要出现在查询的列表里面，否则会报错。因为分组的字段都不出现的话，就没办法分组了）

问题：显示每个部门的每种岗位的平均工资和最低工资？

SELECT min(sal), AVG(sal), deptno, job FROM emp GROUP by deptno, job;

问题：显示平均工资低于2000的部门号和它的平均工资？

SELECT AVG(sal), MAX(sal), deptno FROM emp GROUP by deptno having AVG(sal) < 2000; 这里deptno就一定要出现在group by 中 对数据分组的总结

1 分组函数只能出现在选择列表(select)、having、order by子句中(不能出现在where中) 2 如果在select语句中同时包含有group by, having, order by 那么它们的顺序是group by, having, order by

显示部门平均工资大于4000的部门的平均工资，部门号，根据部门号排序。

select avg(Sal),deptno from emp group by deptno having avg(sal)>4000 order by deptno;

3 在选择列中如果有列、表达式和分组函数，那么这些列和表达式必须有一个出现在group by 子句中，否则就会出错。

多表查询 说明

多表查询是指基于两个和两个以上的表或是视图的查询。

规定：多表查询的条件是 至少不能少于 表的个数-1 才能排除笛卡尔集

SELECT e.ename, e.sal, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno;

第10讲 查询3 多表查询，子查询

问题：显示部门号为10的部门名、员工名和工资？

SELECT a2.dname, a1.ename, a1.sal FROM emp a1, dept a2 WHERE a1.deptno = a2.deptno and a1.deptno = 10;

表emp取别名a1，表depth取别名a2

问题：显示各个员工的姓名，工资及工资的级别？

SELECT e.ename, e.sal, s.grade FROM emp e, salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal;

问题：显示雇员名，雇员工资及所在部门的名字，并按部门排序？

SELECT e.ename, e.sal, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno ORDER by e.deptno; 部门编号不能漏掉是哪个表的。

自连接

自连接是指在同一张表的连接查询

问题：显示某个员工的上级领导的姓名？ 比如显示员工?FORD?的上级

SELECT worker.ename, boss.ename FROM emp worker,emp boss WHERE worker.mgr = boss.empno AND worker.ename = 'FORD';

select a1.ename||'的上司是'||a2.ename \员工关系表\使用了连接字符串|| 后，（a1.ename||'的上司是'||a2.ename）变成一个整体，给它取别名员工关系表。 员工关系表

---------------------------- SMITH的上司是FORD ALLEN的上司是BLAKE

子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询。 单行子查询

单行子查询是指只返回一行数据的子查询语句 请思考：显示与SMITH同部门的所有员工？

SELECT * FROM emp WHERE deptno = (select deptno from emp WHERE ename = 'SMITH'); 数据库在执行sql 是从左到右扫描的， 如果有括号的话，括号里面的先被优先执行。

多行子查询

多行子查询指返回多行数据的子查询

请思考：如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号 SELECT DISTINCT job FROM emp WHERE deptno = 10;

SELECT * FROM emp WHERE job IN (SELECT DISTINCT job FROM emp WHERE deptno = 10);

（注意：不能用job=..，因为等号=是一对一的）

在多行子查询中使用all操作符

问题：如何显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号？

SELECT ename, sal, deptno FROM emp WHERE sal > all (SELECT sal FROM emp WHERE deptno = 30);

大家想想还有没有别的查询方法。

SELECT ename, sal, deptno FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30); 执行效率上， 函数高得多

在多行子查询中使用any操作符

问题：如何显示工资比部门30的任意一个员工的工资高的员工姓名、工资和部门号？ SELECT ename, sal, deptno FROM emp WHERE sal > ANY (SELECT sal FROM emp WHERE

deptno = 30);

大家想想还有没有别的查询方法。

SELECT ename, sal, deptno FROM emp WHERE sal > (SELECT min(sal) FROM emp WHERE deptno = 30);

多列子查询

单行子查询是指子查询只返回单列、单行数据，多行子查询是指返回单列多行数据，都是针对单列而言的，而多列子查询是指查询返回多个列数据的子查询语句。

请思考如何查询与SMITH的部门和岗位完全相同的所有雇员。

SELECT * FROM emp WHERE (deptno, job) = (SELECT deptno, job FROM emp WHERE ename = 'SMITH');

前面的括号不能丢，否者无法执行。

第11讲 子查询、分页查询、合并查询 在from子句中使用子查询

请思考：如何显示高于自己部门平均工资的员工的信息

select emp.* from emp,(select avg(sal) asal,deptno from emp group by deptno) a1 where emp.sal>a1.asal and emp.deptno=a1.deptno;

其中，平均工资必须取别名，否则后面比较时不能用分组函数，给查询的结果定义名字为a1

如何衡量一个程序员的水平？

网络处理能力， 数据库， 程序代码的优化程序的效率要很高

小总结：

在这里需要说明的当在from子句中使用子查询时，该子查询会被作为一个视图来对待，因此叫做内嵌视图，当在from子句中使用子查询时，必须给子查询指定别名。

注意：别名不能用as，如：SELECT e.ename, e.deptno, e.sal, ds.mysal FROM emp e, (SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno) as ds WHERE e.deptno = ds.deptno AND e.sal > ds.mysal;

在ds前不能加as，否则会报错 （给表取别名的时候，不能加as；但是给列取别名，是可以加as的）

分页查询

按雇员的id号升序取出

select * from (select a1.*,rownum rn from (select * from emp) a1 where rownum<=10) where rn >=6; 必须先用小于，否则rownum没法排序号，导致输出为空。

几个查询变化

a. 指定查询列，只需要修改最里层的子查询 只查询雇员的编号和工资

SELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT ename, sal FROM emp) e WHERE ROWNUM <= 10) WHERE rn >= 6; b. 排序查询，只需要修改最里层的子查询 工资排序后查询6-10条数据

SELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT ename, sal FROM emp ORDER by sal) e WHERE ROWNUM <= 10) WHERE rn >= 6;

用查询结果创建新表

这个命令是一种快捷的建表方式

CREATE TABLE mytable (id, name, sal, job, deptno) as SELECT empno, ename, sal, job, deptno FROM emp;

合并查询

有时在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号union，union all，intersect，minus

多用于数据量比较大的数据局库，运行速度快。 1). union

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行。 SELECT ename, sal, job FROM emp WHERE sal >2500 UNION

SELECT ename, sal, job FROM emp WHERE job = 'MANAGER'; 2).union all

该操作符与union相似，但是它不会取消重复行，而且不会排序。 SELECT ename, sal, job FROM emp WHERE sal >2500 UNION ALL

SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行。 3). intersect

使用该操作符用于取得两个结果集的交集。

SELECT ename, sal, job FROM emp WHERE sal >2500 INTERSECT

SELECT ename, sal, job FROM emp WHERE job = 'MANAGER'; 4). minus

使用改操作符用于取得两个结果集的差集，他只会显示存在第一个集合中，而不存在第二个集合中的数据。

SELECT ename, sal, job FROM emp WHERE sal >2500 MINUS

SELECT ename, sal, job FROM emp WHERE job = 'MANAGER'; （MINUS就是减法的意思）

创建数据库有两种方法：

1). 通过oracle提供的向导工具。√

database Configuration Assistant 【数据库配置助手】 2).我们可以用手工步骤直接创建

第12讲 java操作oracle（未加入）、在oracle中操作数据

在oracle中操作数据 - 使用特定格式插入日期值 使用 to_date函数