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;