《经典表关联与多表查询（2020年10月整理）.pptx》由会员分享，可在线阅读，更多相关《经典表关联与多表查询（2020年10月整理）.pptx（7页珍藏版）》请在金锄头文库上搜索。
1、经典表关联与多表查询,目的： 掌握从多个表查询数据的基本知识 了解和学习外连接(out join) 掌握内连接 授课内容： 对多于一个表的数据查询 现实情况中，在数据库应用中，数据存在于多个相关联的表中。基本上没有数据只 存在于一个表中的情况。小的应用系统一般也有十几个表，大型系统一般有上千个 表。 你经常要作的就是在多个表中进行数据查询。 Oracle 对多表查询使用表连接的技术（table join） 表连接的基本条件： 2 个表必须有公共字段（同名字段或不同名字段） 在一个表中，这个公共字段必须是主键（PK） 二个表中的公共字段，在一个表中是主键，在另外一个表中就是外键(FK)。 二表关
2、联中，公共字段是主键的表称为父表（主表）。是外键的表称为子表（详细 表）。 研究一下 scott 下的 emp 和 dept 表的关系。 研究一下 oe 下的表： CATEGORIES_TAB CUSTOMERS INVENTORIES ORDERS ORDER_ITEMS PRODUCT_DESCRIPTIONS PRODUCT_INFORMATION 多表查询的语法 select 子句 from表 1 别名，表 2 别名，视图 别名，（select 子句）别名 where 连接语句 and 其他条件语句 oupy by 分类项目 having 子 句 order by 子句 任务：查询每个
3、员工的编号，姓名，部门名称，部门位置 select empno,ename, dname,loc from emp a, dept b where a.DEPTNO=b.DEPTNO,1,2,多表查询的原则：对 N 个表连接，至少要有 N-1 个相等的条件。而且每个表的公 共字段必须出现一次。 多表关联中，如果没有指定关联等式，将产生无效的结果，它将每个关联的表的 记录跟其他表的所有记录组合，产生笛卡尔积的数据。 测试： select empno,ename, dname,loc from emp a, dept b 对 OE 用户的测试 查询公司库存信息，显示仓库名称，产品名称，库存数量，库
4、存金额 selectc.WAREHOUSE_NAME, b.PRODUCT_NAME, a.QUANTITY_ON_HAND,a.QUANTITY_ON_HAND*b.LIST_PRICE fromINVENTORIES a,PRODUCT_INFORMATION b,WAREHOUSES c where a.WAREHOUSE_ID=c.WAREHOUSE_ID and a.PRODUCT_ID =b.PRODUCT_ID 内连接 (self join) 当多表关联使用一个表进行数据进行数据查询，这种连接叫自连接。 自连接的主要功能是查询表中除了主键外，是否有重复的记录。 任务： 查询员工表
5、中，有同名，职位相同的员工信息（编号，项目，职位，工 资） select a.empno, a.ename, a.job from emp a, emp b where a.empnob.empno and a.deptno=b.deptno and a.job=b.job 日常生活中在数据录入时产生的错误 由于工作失误，一个数据录入到系统 2 次或多次。 一般在进行自动的数据导入时，产生大量的重复记录。 子连接的要求： 自连接至少要 2 个或 2 个以上的等式条件，一个用于关联，其他用于表示重复的 数据。 外连接(out join): 内连接是关联的表的公共字段值必须相同，所有不同的值的记录
6、都没有了。 外连接是值一个表的中的公共字段的值可以不与另一个表的公共字段值相同。一般 时它是 null. 任务：查询员工表，显示员工的项目，部门名称，部门位置，要求显示所有的员工， 即使员工没有部门。 select a.ename,b.dname,b.loc from emp a left outer join dept b,3,on a.deptno=b.deptno 注：此任务无法使用正常的内连接。因为有一个员工没有部门，它的部门编号为空。 常见的任务如：信息系统中的文档，申请审批，当刚创建时，所有审批信息为 null. 但 有的审批已经完成。 如果与审批人表关联的话，要显示所有的申请，就
7、必须使用外连 接。 外连接语法： 左连接：取出左边的表的所有记录 select 子句 from 表 1left outer join 表 2 on 表 1.公共字段表 2.公共字段 右连接: 取出右边表的所有记录 select 子句 from 表 1right outer join 表 2 on 表 1.公共字段表 2.公共字段 全连接（左右连接）：左右两边的表的记录都取。 select 子句 from 表 1fullouter join 表 2 on 表 1.公共字段表 2.公共字段 select a.empno,a.deptno,b.deptno,b.dname from emp a le
8、ft 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.deptn
9、o 一般情况下，不使用上述的语法，而使用如下的语法： select a.dname, b.ename from dept a, emp b where a.deptno(+)=b.deptno- 一般情况情况下，(+)放在关联表的主键的一侧，才有实际的意义。 没有(+)的表的取所有的记录，关联的表如果有记录对应就显示关联的值，没有关联的 值显示 null.,4,但使用(+)的情况下，无法实现全连接。 因为无法在 where 的左右同时使用(+). select a.ename,b.dname from emp a, dept b where a.deptno=b.deptno(+) 下列的语句
10、是无法通过的： select a.ename,b.dname from emp a, dept b where a.deptno(+)=b.deptno(+) 1.5 任务： 自关联(self-join) 有些情况下，需要关联一个表，这种关联叫自关联。 自关联经常使用的一般是查看表中的记录是否重复。在信息管理系统中，有时出现 数据录入的错误。同一个数据，被输入了 2 次以上，除了主键不一样，其他字段基 本上一样。即查询重复的记录。 数据录入错误的发生可能的情况： 数据的自动导入，新建系统从老系统中批量导入数据，导致大量的重复记录。 用户输入错误的数据，将一个数据输入的 2 次。 (3) 如 S
11、COTT 的员工表 EMP, MGR 字段是员工的经理的员工号。要查询每个员工的经 理的姓名。就需要使用自关联。 select a.ename, b.ename from emp a, emp b where a.mgr=b.empno 查询 emp 表中可能同名的员工的记录。 select a.empno,a.ename from emp a, emp b where a.empnob.empno and a.ename=b.ename,查询员工表 emp 的重复记录： select a.empno,a.ename from emp a, emp b wherea.empnob.empnoa
12、nda.ename=b.enameanda.job=b.joband a.sal=b.sal 子连接会导致对表的大量的操作，需要很大的内存。其他用户对自连接的表的操作 会等待很长的时间。一般情况下最好不要使用自关联。 表的自关联的与内关联不同，自关联至少要 2 个或 2 个以上的等式条件。 5.查询结果的联合（UNION）（UNION ALL） -将多个查询结果联合在一起：,5,UNION 将多个结果集联合在一起，去除重复的记录 UNION ALL 将多个结果联合在一起，不去除重复的记录,-union 语法： select union select union select 例子 1： sel
13、ect empno,ename from emp where deptno=10 union select deptno,dname from dept 例子 2： select * from emp where deptno=10 union select * from emp where job=CLERK,- union all 语法 select union all select union all select 例子 1： select * from emp where deptno=10 union all select * from emp where job=CLERK,6,查
14、询结果的交集（INTERSECT）： 将多个查询结果集联合在一起，只保留相同的记录。摘除不同的记录 语法： select 语句 intersect select intersect select 例子： select * from emp where deptno=10 intersect select * from emp where job=CLERK 查询结果的差集（MINUS）： 将多个结果集联合在一起，保留它们差异的记录，将包含第 2 个结果集的记录减去。 语法： select minus select minus select 例子： select * from emp where
15、 deptno=10 minus select * from emp where job=CLERK 注： Oracle 在合并 2 个结果集时，Oracle 并不关心合并运算符的任何一边的列名，合并 的结果集以第一个结果集的列名为新的列名。 select 语句必须有相同的列，如果被查询的结果集有不同的列，可使用 Oracle 的内 置表达式合成为相同的列数。 select 的相对应的列必须为相同的类型。长度可以不同。 在对输出进行排序时，Oracle 使用第 1 个 select 语句的列名给出查询结果，因为， 只有第 1 个 select 的列作为查询结果，因此只有第一个 select 的
16、列名出现在 order by 子句中。 8.Oracle9i 实现 SQL Server 2000 中的 select top n 的 SQL 语句： 在 Oracle9i 中没有类似的 select top n 的语句。但是它提供了 ROWNUM 内置函数。 可以实现 top n 的查询语句。 Select From Where rownum=n,7,ROWNUM 是 Oracle 在做查询时自动计算的。它会随着记录集的变化而动态变化。 ROWNUM 返回第一次从表中选择时返回行的序列号。第 1 行的 ROWNUM 为 1。 如果想返回一个复杂查询的结果集的 top n, 要把此结果集作为中间结果集放在 from 中， 再使用 rownum 函数。如下例子： select * from ( select deptno, sum(sal) from emp group by deptno) where rownum5000 order by totalRMB desc 查询总销售额大于 5000 元的产品清单，以及每种产品的累计销售额，按总销售额降 序排序 （产品名称，销售金额）
