Oracle中分组和多表查询

一、分组查询

　　1.语法：select 列名 from 表名 group by 列名;

　　2.举例：各个部门总工资：select deptno, ename , avg(sal) from emp group by deptno ;

　　3.对行筛选用where， 对组筛选用having

       4.举例：各个部门中各个工作的平均工资：select deptno,job,avg(sal) from emp group by deptno ,job ;

 

二、多表连接查询

　　1.交叉连接（笛卡尔积）：所有情况的组合，不推荐使用，select * from emp,dept;

 

　　2.内连接：多张表通过相同的字段进行匹配，只显示匹配成功的数据

　　   a. 查询员工信息与各个部门的所在的工作地址：select * from emp e, dept d where e.deptno = d.deptno;

　　   b.select * from emp e inner join dept d on e.deptno = d.deptno;

　　   不等值连接（一般不使用）：select * from emp e , dept d where e.deptno <=d.deptno;

 

　　3.外连接

　　   左外连接：以左表为基准（左表数据全部显示），去匹配右表数据，如果匹配成功，则全部显示；匹配不成功，显示部分（无数据部分用NULL填充）

　　　a.（Oracle独有）

　　　　  select * from student s, sub b where s.stuid = b.sid(+);

　　    b. select  * from student s left outer join sub b on s.stuid = b.sid;

 

　　   右外连接：以右表为基准（右表数据全部显示），去匹配左表数据，如果匹配成功，则全部显示；匹配不成功，显示部分（无数据部分用NULL填充）

　　　a.（Oracle独有）

　　　　  select * from student s,sub b where s.stuid(+) = b.sid;

　　    b. select  * from student s right outer join sub b on s.stuid = b.sid;

 

　　    全外连接：左表和右表都不做限制，所有的记录都显示，两表不足的地方均为NULL；

　　    a. select * from student s full join sub b on s.stuid = b.sid;

　　    b. select * from student s full outer join sub b on s.stuid = b.sid;

 

　　　自连接：将一张表通过别名“视为”不同的表

　　    查询员工姓名，以及该员工的领导姓名：select e.ename, b.ename from emp e, emp b where e.mgr = b.empno;

 

三、优化

　　层次连接：查询上级为空的员工信息（即最高的职位）

　　select level, empno, ename, mgr from emp 

　　connect by prior empno = mgr  --下层等于上层

　　start with mgr is null  --当前节点的上层节点的值 = x；

　　order by level;