Oracle数据库之一_多表查询
Oracle的查询是SQL语句的重点,在日常开发中,增删改查4项操作,查询占80%的工作量。
Oracle的单表查询通常较简单,大多数时候,一个select * from 表名即可搞定。
这里我们重点介绍一下Oracle中的多表查询。多表查询意味着数据分散在多个表中,表与表之间通过关联字段进行联接。
示例表使用scott用户的如下几张表:
emp表为员工表,字段分别是员工号、员工姓名、工作、直属上司、入职日期、薪金、佣金(销售提成)、部门号。
其中JOB(工作)种类有:CLERK(职员)、SALESMAN(销售员,只有销售员才有佣金)、MANAGER(经理)、ANALYST(分析师)、PRESIDENT(总裁)。
dept表为部门表,字段分别是部门号、部门名称、所在城市。
salgrade表为薪金等级表,分5档,从700~1200为第1档,1201~1400为第2档,......
下面将以上述表为示例数据,讲解Oracle中的多表查询。
一、通用的多表查询写法
假设要查询员工编号、员工姓名、部门名称这三项数据。
分析:员工编号和员工姓名在emp表中,部门名称在dept表中,两表的关联字段是deptno。
SQL语句:
select e.empno, e.ename, d.deptno
from scott.emp e, scott.dept d
where e.deptno = d.deptno
在编写多表查询时,先写from部分,在from部分中列举要查询的表,以逗号隔开,每个表取一个简短的别名。
where部分写上表之间的联接条件,如果是2表关联,有1个联接条件,3表关联,则有2个联接条件,以此类推。
最后,在select部分把要查询的字段逐个列出,记得使用表的别名来修饰字段,表明该字段来源于哪张表。
如果查询时,有其它附加条件,则在where部分使用“and”添加附加条件。
例如,查询部门号为10的员工,显示其员工编号、员工姓名、部门名称。
SQL语句:
select e.empno, e.ename, d.deptno
from scott.emp e, scott.dept d
where e.deptno = d.deptno
and e.deptno = 10
三个表的关联查询,语法示例如下:
select a.col1, a.col2, b.col3, c.col4
from a, b, c
where a.no1 = b.no1 and b.no2 = c.no2
二、SQL92标准的多表查询写法
除了上述通用的多表查询写法之外,SQL92标准中也规范了多表查询的写法,两者都是可用的。
将查询员工编号、员工姓名、部门名称改写为SQL92标准的写法:
select e.empno, e.ename, d.deptno
from scott.emp e inner join scott.dept d
on e.deptno = d.deptno
where e.deptno = 10
在这种语法中,使用关键字“inner join”来连接两张表,并使用关键字“on”来标注关联字段,其它条件写在where语句中。
inner join表示内联接,除了内联接之外,还有外联接、自联接等用法。
三个表的关联查询,SQL92标准的写法:
select a.col1, a.col2, b.col3, c.col4
from a inner join b
on a.no1 = b.no1
inner join c
on b.no2 = c.no2
三、不等值的两表关联
例如,要显示员工号、员工姓名、薪金、薪金等级。
分析:薪金等级在salgrade表中,而且数据是一个区间段,这时,可以使用不等值连接。当然,使用通用写法或SQL92标准写法都是可以的。
通用写法:
SQL92标准写法:
四、自联接
在emp表中,有一个字段是MGR,是该员工直属上司的员工号。
假如有一个查询,要求显示员工号、员工姓名、直属上司的员工号、上司姓名。
分析:员工号、员工姓名、直属上司的员工号都在emp表中,上司姓名其实也在emp表中,但我们应该把emp表当成两个表来用,通过上司的员工号,查出上司姓名。
象这种带层级关系的表,要查询上级数据都需要使用自联接。
查询语句如下:
五、外联接
前面的内联接,是要求两表数据必须绝对匹配,但有的时候,我们要将不匹配的数据也列出来。
例如上例的自联接查询,你会发现查询结果只有13条,而其实emp表里有14条记录,少了谁呢?
少了KING这个人,他是公司总裁,他没有直属上司,所以使用内联接无法显示他的信息。
外联接又分左外联接、右外联接、全外联接,主要区别是以左表为主还是以右表为主,还是全部都显示。
我们使用外联接改善上题的查询:
“left outer join”就是左外联接,意味着以“left outer join”这个关键词左边的表为主表,该表的数据必须完全显示,即使数据与右表没有匹配项也要显示。
这时,KING这个员工就出现了。
其实,右外联接只要把left改成right,以右表为主。
全外联接只要把left改成full,左、右表都为主。
Oracle为外联接提供了一种特殊语法,即(+)语法:
这种语法使得我们可以用通用写法达到SQL92标准的效果。加号放在非主表的那一方。
再来一个示例,显示所有部门,并显示该部门的所有员工的员工号、姓名。