ORACLE笔记03++Word格式文档下载.docx

1、显示每个部门的每种岗位的平均工资和最低工资select avg（sal） 平均工资,min（sal） 最低工资, job from emp group by job;显示部门平均工资低于2000的部门号和它的平均工资（1）、查询出各个部门的平均工资 select avg（sal）,deptno from emp group by deptno;（2）、挑选出低于2000的select avg（sal）,deptno from emp group by deptno having avg（sal）（select max（sal） from emp where deptno=30）;在子查询中使用

2、 any 如何显示工资比30号部门的任意一个员工的工资高的员工的姓名，工资和部门号any（select sal from emp where deptno=30）;（select min（sal） from emp where deptno=30）;多列子查询：如何查询与smith 的部门和岗位完全相同的所有雇员先查出 smith 所在的部门和岗位 select deptno,job from emp where ename=select * from emp where （deptno,job）=（ select deptno,job from emp where ename=（deptno

3、,job）中的顺序和子查询中的顺序要一致，不然会出错。 这种办法在oracle 中支持，在别的数据库中不一定支持。在from 子句总使用子查询：如何显示高于自己部门平均工资的员工信息（这里要用到数据查询的一个小技巧，把一个子查询当做一个临时表使用）思考：各个部门平均工资是多少？ 把上面查询的结果当做一个临时表对待： select e.ename,e.sal,t.myavg,e.deptno from emp e,（select avg（sal） myavg,deptno from emp group by deptno）t where e.deptno=t.deptno and e.salt.

4、myavg;！这个方法用得非常多，必须要掌握。查找每个部门工资最高的人的详细信息：查询出各个部门的最高工资 select max（sal）,deptno from emp group by deptno;上面的查询结果当做一个临时表对待： select t1.* from emp t1,（ select max（sal） mymax,deptno from emp group by deptno） t2 where t1.deptno=t2.deptno and t1.sal=t2.mymax;显示每个部门的信息（编号，名称）和人员数量各个部门有多少人？ select deptno ,coun

5、t（*） from emp group by deptno;把上面的查询结果当做临时表： select t1.*,t2.num from dept t1,（ select deptno ,count（*） num from emp group by deptno） t2 where t1.deptno=t2.deptno（+）;（+）：表示外连接符号。小结：需要说明的是：当在from 子句中使用子子查询时，该子查询会被当做一个临时表对待，当在from 子句中使用子查询时，必须给子查询指定别名。分页查询：分页查询是我们学习任何一个数据库必须掌握的一个要点。MYSQL：select * from

6、表名 where limit 从 第几条取，取几条；SQL SERVER:select top 5* from 表名 where id not in（select top 4 id from 表名 where 条件）；排除前5条，再取4条，这个案例实际上是取出了第6条到第9条。ORACLE: select t2.* from（select t1.* ,rownum rn from （select * from emp） t1 where rownum=4;说明：上面的这个sql语句是oracle 数据库效率比较高的查询方法，在百万级别都可以及时响应。oracle 使用三层过滤机制，第一层：初步

7、过滤：select * from emp；第二层：把第一层当做临时表：select t1.* ,rownum rn from （select * from emp） t1 where rownum=6；第三层：把前两层当做临时表：select t2.* from（select t1.* ,rownum rn from （select * from emp） t1 where rownum实际上我们可以把上面的SQL 语句当做一个分页模板来对待：其中把上面的数字进行修改即可。其中：6：表示取到第几条 4：从哪条开始取。如果我们需要对不同的情况进行分页，请在最内层进行修改即可，包括多表查询都可以。

8、请思考：请按照入职时间的先后顺序查询出第7-10个人是谁？select t2.* from（select t1.* ,rownum rn from （select * from emp order by hiredate） t1 where rownum=7;看看分页查询的效率如何？模拟 10w数据的一个表：-测试下create table mytest as select empno,ename,sal,comm,deptno from emp;自我复制：insert into mytest （empno,ename,sal,comm,deptno） select empno,ename,s

9、al,comm,deptno from mytest;合并查询：（详细请见幻灯片）（1）、union：（2）、union all（3）、intersect 取交集（4）、minus 取差集ORACLE 的内连接和外连接：内连接：是我们用的最多的一种连接。前面我们所说的都是内连接：比如：我们需要显示员工的信息和部门名称 select emp.ename,dept.dname from emp ,dept where emp.deptno=dept.deptno;等价于： select emp.ename,dept.dname from emp inner join dept where emp.

10、deptno=dept.deptno;外连接：左外连接，右外连接，完全外连接。我们创建两张表做测试：create table stu （id number primary key,name varchar2（32）;insert into stu values（1,Jack）;insert into stu values（2,Tom）;insert into stu values（3,Kity）;insert into stu values（4,nono）;create table exam （id number primary key, grade number）;insert into e

11、xam values（1,56）;insert into exam values（2,76）;insert into exam values（11,8）;实际应用：显示所有人的成绩，如果没有成绩，也要显示该人的姓名和ID号成绩显示为空select stu.name,stu.id,exam.grade from stu,exam where stu.id=exam.id;上面我们使用的是内连接，特点是只有两张表同时匹配上，才被选则。使用左外连接来解决：select stu.name,stu.id,exam.grade from stu left join exam on stu.id=exam.

12、id;如果stu（左表）的记录没有和exam（右表）任意一条记录匹配上，也要被选中。怎么判断一张表示左表还是右边呢？如果在left join 左边，就是左表。另外一种写法：select stu.name,stu.id,exam.grade from stu ,exam where stu.id=exam.id（+）;右外连接：显示所有成绩，如果没有名字匹配，显示空select stu.id,stu.name,exam.grade from stu right join exam on stu.id=exam.id;右外连接指的是右边的表如果没有和左边的任何一条记录匹配，也要被选中。另外还有一种

13、写法：select stu.id,stu.name,exam.grade from stu ,exam where stu.id（+）=exam.id;把（+）写在左边就表示右外连接。实际上，左外连和右外连接是可以相互转换的：例如：（1）、select stu.id,stu.name,exam.grade from stu right join exam on stu.id=exam.id;（2）、select stu.id,stu.name,exam.grade from stu ,exam where stu.id（+）=exam.id;（3）、select stu.name,stu.id

14、,exam.grade from exam left join stu on stu.id=exam.id;（4）、select stu.name,stu.id,exam.grade from stu ,exam where stu.id（+）=exam.id;完全外连接：两个表查询不管有没有匹配上，都要显示出来显示所有成绩和所有人的名字，如果没有相应的匹配值，则显示为空：select stu.id,stu.name,exam.grade from stu full join exam on stu.id=exam.id;小练习：列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门：s

15、elect dept.dname,emp.* from dept , emp where emp.deptno（+）=dept.deptno;select dept.dname,emp.* from dept left join emp on emp.deptno=dept.deptno;练习：6、（1）、select avg（sal）,sum（sal）,max（sal）,min（sal） from emp;（2）、select count（job）,avg（sal） from emp group by job;（3）、select 雇员总数,count（*） from emp union s

16、elect 补助雇员, count（comm） from emp;（4）、select 管理者,count（job） from emp where job=MANAGER（5）、select max（sal）-min（sal） from emp;（6）、select 岗位, avg（sal） from emp group by job union select 部门,avg（sal） from emp group by deptno ;7、（1）、select dept.dname,emp.ename,emp.sal,emp.job from emp,dept where dept.deptno=emp.deptno and dept.deptno=20;（2）、select emp.ename,m,dept.dname from emp,dept where dept.deptno=emp.deptno and m is not null;（3）、select emp.ename,emp.sal,dept.dname from emp,dept where dept.deptno=emp.deptno and dept.loc=DALLAS;（4）、