继续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))))