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);//全外连接
网友评论