马士兵oracle视频ppt,马士兵oracle视频教程学习笔记个人整理

该楼层疑似违规已被系统折叠 隐藏此楼查看此楼

select avg(sal) from emp where sal>1200 group by deptno having avg(sal)>1500 order by avg(sal) desc;查询sal大于1200的平均值按照deptno排序，并且sal的平均值大于1500，然后用sal平均值的降序排列

子查询

select ename,sal,deptno from emp where sal in(select max(sal) from emp group by deptno);查询所有部门中sal最高的人名和sal以及部门编号。(这是错误的，最后查询出来的值会变多，因为首先查询出来的是各部门最高的薪水值，前面查询语句查询所有的人名，薪水值和部门编号in这些薪水值，所以薪水值只要在各部门最高薪水中的任何一个，都会被查询出来)

select ename,sal from emp join (select max(sal) max_sal,deptno from emp group by deptno) t on (emp.sal=t.max_sal and emp.deptno=t.deptno);

第十四课

select e.ename,t.ename from (select * from emp) t,emp e where t.empno=e.mgr;

select e1.ename,e2.ename from emp e1,emp e2 where e2.empno=e1.mgr;--查询雇员的上级经理姓名

sql1992与sql1999对比

sql1999where后面直接写数据过滤条件

select ename,dname from emp,dept;--1992sql标准

select ename,dname from emp cross join dept;--1999sql新标准 cross join交叉连接

select ename,dname from emp,dept where emp.deptno=dept.deptno;

select ename,dname from emp join dept on (emp.deptno=dept.deptno);

select ename,dname from emp join dept using(deptno);--等值连接可以使用这种方式

select ename,grade from emp e join salgrade s on (e.sal between s.losal and s.hisal);

select ename,dname,grade from emp e join dept d on (d.deptno=e.deptno) join salgrade s on (e.sal between s.losal and s.hisal) where ename not like '_A%';

select e1.ename,e2.ename from emp e1 join emp e2 on (e1.mgr=e2.empno);

--外连接

select e1.ename,e2.ename from emp e1 left join emp e2 on (e1.mgr=e2.empno);--左外连接(可写为left outer join)

select ename,dname from emp e right join dept d on (e.deptno=d.deptno);--右外连接

select ename,dname from emp e full join dept d on (e.deptno=d.deptno);--全外连接

十六课

--求部门中哪些人的薪水最高

select ename,sal from emp join(select max(sal) max_sal,deptno from emp group by deptno) t on (emp.sal=t.max_sal and emp.deptno=t.deptno);

--求部门平均薪水的等级

select deptno avg_sal,grade from (select deptno,avg(sal) avg_sal from emp group by deptno) t join salgrade s on (t.avg_sal between s.losal and s.hisal);

select deptno,avg(grade) from (select deptno,ename,grade from emp join salgrade on (sal between losal and hisal)) t group by deptno;--查询每个部门的平均薪水等级

select ename from emp where empno in (select mgr from emp); --雇员中哪些是经理人

select ename from emp where empno in (select distant mgr from emp); --雇员中哪些是经理人

select sal from emp where sal not in(select e.sal from emp e join (select sal from emp) t on e.sal

select sal from emp t where t.sal >= all (select sal from emp);--貌似all是聚组函数

--求平均薪水最高的部门编号

20

--查询平均薪水最高的部门编号

select deptno,avg_sal from

(select avg(sal) avg_sal,deptno from emp group by deptno)

where avg_sal=

(select max(avg_sal) from