oracle数据库的多表查询

多表查询

如果要在一个查询语句中显示多个表中的数据，就必须用到多表查询

查询经验

在日后的开发中，很多人都肯定要接触到许多新的数据库和数据表，那么在这种时候有两种做法

做法1：新人做法，上来直接输入以下命令

select * from emp

如果此时数据量较大的话，一无法浏览数据，二有可能造成系统死机

做法2：老人做法，先看一下有多少条记录

select count（*）from emp

--不同表如果没有关联关系，那么查询结果会出现很多冗余数据，笛卡尔积的结果

select * from emp, dept;

消除笛卡尔积的方式

--找两个表之间关联的关系，消除笛卡尔积

select * from emp, dept where emp.deptno=dept.deptno;

当表的名称过长的时候可以给表起别名

--给表加别名

select * from emp e, dept d where e.deptno=d.deptno;

多表查询的步骤

第一步确定要查询的数据

SELECT e1.ename, e1.job , e1.sal,e2.ename 领导, d.dname, d.loc from emp e1,emp e2, dept d

第二步 确定关联条件

where e2.empno=e1.mgr and e1.deptno= d.deptno;

多表查询

--查询出每一个雇员的编号、姓名、职位、部门名称、位置

SELECT e.empno,e.ename,e.job, d.deptno, d.loc from emp e, dept d WHERE e.deptno= d.deptno;

自身关联（两表关联）

--查询每一位员工的姓名、职位、领导的姓名

--emp表的自身关联

SELECT e1.ename, e1.job ,e2.ename from emp e1,emp e2 where e2.empno=e1.mgr;

三表关联（自身关联）

--查询每个雇员的编号、姓名、基本工资、职位，领导的姓名、部门及位置

SELECT e1.ename, e1.job , e1.sal,e2.ename 领导, d.dname, d.loc from emp e1,emp e2, dept d

where e2.empno=e1.mgr and e1.deptno= d.deptno;

三表关联

--查询雇员的编号、姓名、工资、部门名称、工资所在公司的工资等级

select e.empno, e.ename, e.sal, d.dname,

s.grade,decode(s.grade,1,'第五等工资',2,'第四等工资',3,'第三等工资',4,'第二等工资',5,'第一等工资') 工资等级

from emp e, dept d, salgrade s

WHERE e.deptno= d.deptno and (e.sal between s.losal and s.hisal)

左右连接

左表数据会完全显示出来，就算右表没有匹配的

（+）是oracle的独有语法，

--加号可以理解为把右边的表附加到主表上（这句话是核心）

不用左右连接的比较（当empno有空值的时候只有13条数据）

SELECT e1.ename, e1.job ,e2.ename from emp e1,emp e2 where e2.empno(+)=e1.mgr;

SELECT e1.ename, e1.job ,e2.ename from emp e1,emp e2 where e2.empno(+)=e1.mgr;

除了oracle独有的语法外，在sql语法中，也提供了另外一套用于表连接的操作sql，格式如下

这些连接都可以用where子句来实现,其实不写where语句就是交叉连接

最重要的还是左右连接和全连接

--交叉连接(会产生笛卡尔积)

select * from emp cross join dept

--自然连接;自动找寻表与表之间的外键关联关系(消除笛卡尔积，且两表必须有键的关联关系)

select * from emp NATURAL join dept

--join using子句，相同字段名称可以使用

select * from emp join dept using(deptno);

--join on子句,不相同字段名称也可以使用

select * from emp join dept on (emp.deptno=dept.deptno)

--左外连接（左边为主表） left outer join...on

select * from emp LEFT OUTER JOIN dept on emp.deptno=dept.deptno

--右外连接right outer join...on

select * from emp right OUTER JOIN dept on emp.deptno=dept.deptno

--全连接 full right outer join...on

select * from emp FULL OUTER join dept on emp.deptno=dept.deptno

统计函数和分组查询

统计函数

select sum(sal), trunc(AVG(sal)),max(sal), min(sal),count(sal) FROM emp

分组查询

把所有查询的数据按照一定条件合在一组中

意义，把重复的数据合并在一起，

如按照部门号分组

--分组查询

--按照部门编号分组，求每个部门的人数和平均工资

--GROUP by语句要查询的字段必须包含在分组中（如查询ename,就没有数据），除了统计函数

select deptno,count(*), trunc(AVG(sal)) from emp GROUP by deptno

--按照职位分组，求出每个职位的最高工资和最低工资

select job,MAX（sal）, min(sal) from emp GROUP by job