经典表关联与多表查询

经典表关联与多表查询

目的：

1.掌握从多个表查询数据的基本知识

2.了解和学习外连接(out join)

3.掌握内连接

授课内容：

1.对多于一个表的数据查询

1.1现实情况中，在数据库应用中，数据存在于多个相关联的表中。基本上没有数据只

存在于一个表中的情况。小的应用系统一般也有十几个表，大型系统一般有上千个表。

1.2你经常要作的就是在多个表中进行数据查询。

1.3Oracle对多表查询使用表连接的技术（table join）

1.4表连接的基本条件：

(1)2个表必须有公共字段（同名字段或不同名字段）

(2)在一个表中，这个公共字段必须是主键（PK）

1.5二个表中的公共字段，在一个表中是主键，在另外一个表中就是外键(FK)。

1.6二表关联中，公共字段是主键的表称为父表（主表）。是外键的表称为子表（详细

表）。

1.7研究一下scott下的emp和dept表的关系。

1.8研究一下oe下的表：

CATEGORIES_TAB

CUSTOMERS

INVENTORIES

ORDERS

ORDER_ITEMS

PRODUCT_DESCRIPTIONS

PRODUCT_INFORMATION

1.9多表查询的语法

select 子句

from 表1[ 别名]，表2[ 别名]，视图[ 别名]，（select 子句）别名

where 连接语句and 其他条件语句

[oupy by 分类项目]

[having 子句]

[order by 子句]

1.10任务：查询每个员工的编号，，部门名称，部门位置

select empno,ename, dname,loc

from emp a,dept b

where a.DEPTNO=b.DEPTNO

1.11多表查询的原则：对N个表连接，至少要有N-1个相等的条件。而且每个表的公

共字段必须出现一次。

1.12多表关联中，如果没有指定关联等式，将产生无效的结果，它将每个关联的表的

记录跟其他表的所有记录组合，产生笛卡尔积的数据。

测试：

select empno,ename, dname,loc

from emp a,dept b

1.13对OE用户的测试

查询公司库存信息，显示仓库名称，产品名称，库存数量，库存金额

select c.WAREHOUSE_NAME, b.PRODUCT_NAME,

a.QUANTITY_ON_HAND,a.QUANTITY_ON_HAND*

b.LIST_PRICE

from INVENTORIES a,PRODUCT_INFORMATION b,WAREHOUSES c

where a.WAREHOUSE_ID=c.WAREHOUSE_ID and a.PRODUCT_ID =b.PRODUCT_ID

2.内连接(self join)

1.当多表关联使用一个表进行数据进行数据查询，这种连接叫自连接。

2.自连接的主要功能是查询表中除了主键外，是否有重复的记录。

3.任务：查询员工表中，有同名，职位相同的员工信息（编号，项目，职位，工

资）

select a.empno, a.ename, a.job

from emp a, emp b

where a.empno<>b.empno and a.deptno=b.deptno and a.job=b.job

4.日常生活中在数据录入时产生的错误

(1)由于工作失误，一个数据录入到系统2次或多次。

(2)一般在进行自动的数据导入时，产生大量的重复记录。

5.子连接的要求：

自连接至少要2个或2个以上的等式条件，一个用于关联，其他用于表示重复的

数据。

3.外连接(out join):

1.1内连接是关联的表的公共字段值必须相同，所有不同的值的记录都没有了。

1.2外连接是值一个表的中的公共字段的值可以不与另一个表的公共字段值相同。一般

时它是null.

1.3任务：查询员工表，显示员工的项目，部门名称，部门位置，要求显示所有的员工，

即使员工没有部门。

select a.ename,b.dname,b.loc

from emp a left outer join dept b

on a.deptno=b.deptno

注：此任务无法使用正常的内连接。因为有一个员工没有部门，它的部门编号为空。

常见的任务如：信息系统中的文档，申请审批，当刚创建时，所有审批信息为null.. 但有的审批已经完成。如果与审批人表关联的话，要显示所有的申请，就必须使用外连接。

1.4外连接语法：

（1）左连接：取出左边的表的所有记录

select 子句

from 表1 left outer join 表2

on 表1.公共字段＝表2.公共字段

（2）右连接: 取出右边表的所有记录

select 子句

from 表1 right outer join 表2

on 表1.公共字段＝表2.公共字段

（3）全连接（左右连接）：左右两边的表的记录都取。

select 子句

from 表1 full outer join 表2

on 表1.公共字段＝表2.公共字段

select a.empno,a.deptno,b.deptno,b.dname

from emp a left outer join dept b

on a.deptno=b.deptno

select a.empno,a.deptno,b.deptno,b.dname

from emp a right outer join dept b

on a.deptno=b.deptno

select a.ename,b.dname,b.loc

from emp a full outer join dept b

on a.deptno=b.deptno

select a.dname, b.ename

from dept a full outer join emp b

on a.deptno=b.deptno

一般情况下，不使用上述的语法，而使用如下的语法：

select a.dname, b.ename

from dept a,emp b

where a.deptno(+)=b.deptno --

一般情况情况下，(+)放在关联表的主键的一侧，才有实际的意义。

没有(+)的表的取所有的记录，关联的表如果有记录对应就显示关联的值，没有关联的值显示null.