Oracle数据库-第四章：多表查询

多表查询，又称表联合查询，即一条语句涉及到的表有多张，数据通过特定的连接进行联合显示.

 

 

笛卡尔积

在数学中，两个集合X和Y的笛卡尓积（Cartesian product），又称直积，表示为X × Y.

假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。

在数据库中,如果直接查询俩张表,那么其查询结果就会产生笛卡尔积

例如:

select *

from s_emp,s_dept;

 

 

连接查询

为了在多表查询中避免笛卡尔积的产生,我们可以使用连接查询来解决这个问题.

连接查询分为:

1.等值连接

2.不等值连接

3.外连接

左外连接

右外连接

全连接

4.自连接

 

等值连接

利用一张表中某列的值和另一张表中某列的值相等的关系,把俩张表连接起来。

例如:查询员工的名字、部门编号、部门名字

select last_name,dept_id,name

from s_emp,s_dept

where s_emp.dept_id=s_dept.id;

 

为了表述的更加清楚,可以给每张表起别名

select se.last_name,se.dept_id,sd.name

from s_emp se,s_dept sd

where se.dept_id=sd.id;

 

 

不等值连接

假设数据库中还有一张工资等级表:salgrade

工资等级表salgrade:

gradeName列表示等级名称

losal         列表示这个级别的最低工资数

hisal    列表示这个级别的最高工资数

 

表中的数据类似于下面内容:

id  salgrade        losal hisal

1  初级程序员   2000  4000

2  中级程序员   4000  6000

 

例如:

查询出员工的名字、职位、工资、工资等级名称

SELECT  e.last_name, e.title, e.salray, s.gradeName

FROM    s_emp e, salgrade s

WHERE   e.salray BETWEEN s.losal AND s.hisal

 

 

外连接

外连接分为:左外连接 右外连接 全连接

先分别在俩s_emp和s_dept表中插入新的数据

特点:新员工tom不在任何部门,新增部门st下面没有任何员工

insert into s_emp(id,last_name) values(26,'tom');

insert into s_dept(id,name) values(60,'st');

commit;

 

下面条sql语句可以把上面插入的数据给删除掉

delete from s_emp where id=26;

delete from s_dept where id=60;

commit;

 

这个时候再使用等值连接的话,查询出来的数据就会少,因为新增的员工tom和部门表中的数据连接不上,当然新增的部门st也和员工表中的数据连接不上.那么这俩条数据都是在等值连接中查询不出来.

 

 

左外连接

例如:

查询所有员工 以及对应的部门的名字,没有部门的员工也要显示出来

select last_name,dept_id,name

from s_emp,s_dept

where s_emp.dept_id=s_dept.id(+);

 

或者 俩者是等价的

 

select last_name,dept_id,name

from s_emp left outer join s_dept

on s_emp.dept_id=s_dept.id;

 

注意:outer可以省去不写

 

 

右外连接

例如:

查询所有员工 以及对应的部门的名字,没有任何员工的部门也要显示出来

select last_name,dept_id,name

from s_emp,s_dept

where s_emp.dept_id(+)=s_dept.id;

 

select last_name,dept_id,name

from s_emp right outer join s_dept

on s_emp.dept_id=s_dept.id;

 

注意:outer可以省去不写

 

 

全连接

例如:

查询所有员工 以及对应的部门的名字,没有任何员工的部门也要显示出来,没有部门的员工也要显示出来

select last_name,dept_id,name

from s_emp full outer join s_dept

on s_emp.dept_id=s_dept.id;

 

注意:outer可以省去不写

 

 

自连接

一张表,自己和自己连接

例如:

查询每个员工的名字以及员工对应的管理者的名字

select s1.last_name,s2.last_name manager_name

from s_emp s1,s_emp s2

where s1.manager_id = s2.id;

 

对查询结果集的操作

如果有俩条sql语句,每一条sql都可以查询出一个结果,这个被称之为结果集。那么我们可以使用下面的关键字对俩个结果集进行操作

union                取俩个结果集的并集

union all        把俩个结果集合在一起显示出来        

minus            第一个结果集除去第二个结果集和它相同的部分

intersect        求俩个结果集的交集

 

注意:前提条件 俩个结果集中查询的列要完全一致

 

union  取俩个结果集的并集

例如:

select last_name,dept_id,name

from s_emp,s_dept

where s_emp.dept_id=s_dept.id(+)

union

select last_name,dept_id,name

from s_emp,s_dept

where s_emp.dept_id(+)=s_dept.id;

 

union all  把俩个结果集合在一起显示出来        

例如:

select last_name,dept_id,name

from s_emp,s_dept

where s_emp.dept_id=s_dept.id(+)

union all

select last_name,dept_id,name

from s_emp,s_dept

where s_emp.dept_id(+)=s_dept.id;

 

minus  第一个结果集除去第二个结果集和它相同的部分

例如:

select last_name,dept_id,name

from s_emp,s_dept

where s_emp.dept_id=s_dept.id(+)

minus

select last_name,dept_id,name

from s_emp,s_dept

where s_emp.dept_id(+)=s_dept.id;

 

对比俩种情况的结果

 

select last_name,dept_id,name

from s_emp,s_dept

where s_emp.dept_id(+)=s_dept.id

minus

select last_name,dept_id,name

from s_emp,s_dept

where s_emp.dept_id=s_dept.id(+);

 

intersect  求俩个结果集的交集

select last_name,dept_id,name

from s_emp,s_dept

where s_emp.dept_id=s_dept.id(+)

intersect

select last_name,dept_id,name

from s_emp,s_dept

where s_emp.dept_id(+)=s_dept.id;

 

oracle中的伪列 rownum

伪列rownum，就像表中的列一样，但是在表中并不存储。伪列只能查询，不能进行增删改操作。它会根据返回的结果为每一条数据生成一个序列化的数字.rownum是oracle才有的伪列

 

rownum 所能作的操作:

 

rownum 只能等于1  如果让其等于其他数 则查不到数据

例如:

select last_name

from s_emp

where rownum=1

 

rownum 大于0      如果让其大于其他数 则查不到数据

例如:

select last_name

from s_emp

where rownum>0

 

rownum 可以小于任何数

例如:

select last_name

from s_emp

where rownum<7