SQL语言基础--续

继续SQL语言知识积累，同上篇，示例都是以Oracle系统自带的数据为背景。

 

子查询：
 把中间过程查询出来的结果当成一张表,示例如下（表连接）：
 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 e1.ename,e2.ename from emp e1,emp e2 where e1.mgr = e2.empno;

 

sql1999---连接条件和数据过滤条件分开:
 select ename,dname from emp cross join dept;---交叉连接
 条件不写在where里面，改在join on()里面
 select ename,dname from emp join dept on(emp.deptno = dept.deptno);
 select ename,dname from emp join dept using (deptno);--不推荐。
 连三张表：
 select ename,dname,grade from emp e join dept d on (e.deptno = d.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
 left outer join emp e2 on (e1.mgr = e2.empno); 
  ---outer可省略。如下：
 select e1.ename,e2.ename from emp e1 left join emp e2 on (e1.mgr = e2.empno);
  --左外连接，会把左边的表的多余数据显示出来。
 类似，也有右外连接，会把右边多余的数据显示出来。
 全外连接（sql1992不支持）：
 select ename,dname from emp e full join dept d on (e.deptno = d.deptno);
  --左右多余的数据都取出来--显示出来。
 1.部门平均薪水的等级：
 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);
 2.部门平均的薪水等级：
 select deptno, avg(grade) from
 (select deptno,ename,grade from emp
 join salgrade s on(emp.sal between s.losal and s.hisal)) t
 group by deptno;
 3.求雇员中有哪些人是经理人
 select ename from emp where empno in(select distinct mgr from emp);
 4.不准用组函数，求薪水的最高值
 select distinct sal from emp where sal not in
 (select distinct e1.sal from emp e1 join emp e2 on(e1.sal<e2.sal));
 5.平均薪水最高的部门编号
 select deptno ,avg_sal from
   (select avg(sal) avg_sal, deptno from emp group by deptno)
 where avg_sal =
   (select max(avg_sal) from
 (select avg(sal) avg_sal,deptno from emp group by deptno));
 组函数嵌套：（最多两层）
 select deptno ,avg_sal from
   (select avg(sal) avg_sal, deptno from emp group by deptno)
 where avg_sal =
 (select max(avg(sal)) avg_sal,deptno from emp group by deptno);
 6.平均薪水最高的部门名称
 select dname from dept where deptno =
 (select deptno from
   (select avg(sal) avg_sal, deptno from emp group by deptno)
 where avg_sal =
   (select max(avg_sal) from
 (select avg(sal) avg_sal,deptno from emp group by deptno)));
 7.平均薪水的等级最低的部门名称
 select dname from dept
 where deptno =
   (select deptno from
     (selcet deptno, avg_sal,grade from
       (select avg(sal) avg_sal,deptno from emp group by dept) t
     join salgrade s on(t.avg_sal between s.losal and s.hisal)
   ) t1
 where t1.grade =
 (select min(grade) from
   (selcet deptno, avg_sal,grade from
     (select avg(sal) avg_sal,deptno from emp group by dept) t
   join salgrade s on(t.avg_sal between s.losal and s.hisal))))