oracle学习笔记四（子查询）

472119j09p

子查询：
查询最高工资的员工姓名
select ename,sal from emp where sal=(select max(sal) from emp);
查询工资高于平均工资的员工信息
select ename,sal from emp where sal>(select avg(sal) from emp);

显示每个部门的平均工资和最高工资
select avg(sal),max(sal) from emp group by deptno;
select avg(sal),max(sal)，deptno from emp group by deptno;
均可以

显示每个部门的每种岗位的平均工资和最高工资
select avg(sal),max(sal),job from emp group by deptno,job;

显示平均工资低于2000的部门号和它的平均工资；
select deptno,avg(sal) from emp  group by deptno having avg(sal)<2000;
先分组，再加条件用having,有分组函数，加条件就得用having ***************

1，分组函数只能出现在选择列表，或者是having,order by 子句中。
2，如果在select语句中同时包含有group by ,having,order by ，那么他们的顺序是：group by ,having ,order by;
3.在选择列表中，如果有表，表达式，和分组函数，那么这些列和表达式必须出现在group by子句中。

select avg(sal),max(sal),deptno from emp group by deptno having avg(sal)>2000 order by avg(sal);

显示雇员名，雇员工资及所在部门的名字
select ename,sal,dname from emp,dept;

规定：多表查询的条件是：至少不能少于表的个数减一；******************

两张表，则至少有一个条件才能排除笛卡尔积，三张表则需要用两个条件，四张表则需要有三个条件；

显示各个员工的姓名，工资及其工资的级别
select a1.ename,a1.sal,a2.grade from emp a1,salgrade a2 where a1.sal between a2.losal and hisal;

显示雇员名，雇员工资及所以部门的名字，并按部门排序；
select a1.ename,a1.sal,a2.dname from dept a2,emp a1 where a1.deptno=a2.deptno order by a2.deptno;


自连接：在同一个表的连接查询

显示某个员工的上级领导的姓名：
比如显示“FORD”的上级
//select ename from emp where empno in(select mgr from emp where ename='FORD');
//select ename from emp where empno =(select mgr from emp where ename='FORD');
推荐：
select worker.ename,boss.ename from emp worker,emp boss where worker.mgr=boss.empno and worker.ename='FORD';

单行子查询：只返回一行数据的子查询;
如果显示与SMITH同一部门的所有员工
select * from emp where deptno = (select deptno from emp where ename='SMITH');
select * from emp where deptno in (select deptno from emp where ename='SMITH');

SQL执行是从左到右执行， 尽可能把最精确定位的条件写在最左边。**********


多行子查询：返回多行数据的子查询
如何查询和部门10的工作相同的雇员的名字，岗位，工资及部门号。

select * from emp where job in (select distinct job from emp where deptno=10);
单行用=，多行用 in；

如果显示工资比部门30的所有员工的工资高的员工的姓名，工资和部门名；
方法1：select ename,sal,deptno from emp where sal> all(select sal from emp where deptno=30);

方法2:select ename,sal,deptno from emp where sal >(select max(sal) from emp where deptno=30);
方法2效率更高。

如果显示工资比部门30的任意一个员工的工资高的员工的姓名，工资和部门名；

方法1：select ename,sal,deptno from emp where sal> any(select sal from emp where deptno=30);

方法2: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');

如何显示高于自己部门平均工资的员工信息？
select * from emp d where d.sal>(select avg(sal) from emp where deptno=d.deptno)
select * from emp d,(select deptno,avg(sal) mysal from emp group by deptno) e
where d.deptno=e.deptno and d.sal>e.mysal;

总结：当在from子句中使用子查询时，该子查询会被作为一个视图来对待，因此叫作内嵌视图，（内嵌视图）。当在from子句中使用查询时，必须给子查询指定别名。给表取别名不需要加as


用查询结果创建新表
create table mytable(id,name,sal,job,deptno) as
select empno,ename,sal,job,deptno from emp;

合并查询：
集合操作符合
union,union all,intersect,minus  大表操作执行速度很快。比and,or快
1)union 该操作用一取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行；
select ename,sal,job from emp where sal>2500 union
select ename,sal,job from emp where job='manager';

2)union all 不会去掉重复行；
3 intersect 取交集；
4）minus 取差集（使用该操作用于取得两个结果集的差集，它只会显示存在第一个集合中，而不存在第二个集合中的数据）；

select ename,sal,job from emp where job='manager' minus
select ename,sal,job from emp where sal>2500 ;

使用子查询更新数据
希望员工scott的岗位，工资，补助与SMITH员工一样

update emp set(job,sal,comm)=(select job,sal,comm from emp where ename='SMITH') where ename='SCOTT';

事务：DML:增，删，改；