Oracle笔记3

11、group by语句:select avg(sal) from emp group  by deptno;

                 select ename from emp where sal=(select max(sal) from emp);

                 select deptno,max(sal) from emp;

12、having语句:select avg(sal),deptno from emp group by deptno having avg(sal) > 2000;//对分组进行限制。

    总结：语句顺序：先是select from，where过滤，group by分组，分组完了进行having限制，最后结果来order by排序

    select avg(sal) from emp where sal>1200 group by deptno having avg(sal)>1500 order by avg(sal) desc;//单条语句必须记住，不能查书！

13、子查询：select ename from emp where sal=(select max(sal) from emp);//select里面套了一个select语句叫子查询，可以在where或者from里添加。

             select ename,sal from emp where sal>(select avg(sal) from emp);

             select max(sal),deptno from emp group by 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);

14、自连接(self_table_connection)：select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr = e2.empno;

15、1999标准的一些语法：select ename,dname from emp cross join dept;//交叉连接

                        select ename,dname from emp,dept where emp.deptno = dept.deptno;//（旧）等值连接

                        select ename,dname from emp join 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 (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 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);//左外连接

            select ename,dname from emp e join dept d on (e.deptno = d.deptno);//不能拿出右边多余的记录

            select ename,dname from emp e right outer join dept d on (e.deptno = d.deptno);//右外连接（去掉outer也可以）

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