经典表关联与多表查询.doc

经典表关联与多表查询
目的:
1. 掌握从多个表查询数据的基本知识
2. 了解和学习外连接（out join)
3. 掌握内连接
授课内容：
1. 对多于一个表的数据查询
1.1 现实情况中，在数据库应用中，数据存在于多个相关联的表中。基本上没有数据只存在于一个表中的情况.小的应用系统一般也有十几个表，大型系统一般有上千个表。
1.2 你经常要作的就是在多个表中进行数据查询。
1.3 Oracle对多表查询使用表连接的技术（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。
但使用（+)的情况下,无法实现全连接。 因为无法在where 的左右同时使用(+).
select a。ename,b。dname
from emp a， dept b
where a.deptno=b。deptno（+）
下列的语句是无法通过的：
select a。ename，b.dname
from emp a, dept b
where a.deptno(+）=b。deptno（+)
1.5 任务：
4. 自关联(self—join）
4.1 有些情况下，需要关联一个表，这种关联叫自关联。
4.2 自关联经常使用的一般是查看表中的记录是否重复。在信息管理系统中，有时出现数据录入的错误.同一个数据，被输入了2次以上，除了主键不一样,其他字段基本上一样。即查询重复的记录。
4.3 数据录入错误的发生可能的情况：
(1) 数据的自动导入,新建系统从老系统中批量导入数据，导致大量的重复记录。
(2) 用户输入错误的数据,将一个数据输入的2次.
4.4 如SCOTT的员工表EMP， MGR字段是员工的经理的员工号。要查询每个员工的经理的姓名。就需要使用自关联。
select a。ename, b。ename
from emp a， emp b
where a.mgr=b。empno
4.5 查询emp表中可能同名的员工的记录.
select a。empno,a.ename
from emp a, emp b
where a。empno〈〉b。empno and a。ename=b.ename
4.6 查询员工表emp的重复记录：
select a。empno,a.ename
from emp a, emp b
where a.empno〈〉b。empno and a.ename=b.ename and a.job=b。job and a。sal=b。sal
4.7 子连接会导致对表的大量的操作，需要很大的内存。其他用户对自连接的表的操作会等待很长的时间。一般情况下最好不要使用自关联。
4.8 表的自关联的与内关联不同，自关联至少要2个或2个以上的等式条件。
5. 查询结果的联合(UNION)（UNION ALL）
- 将多个查询结果联合在一起：
- UNION将多个结果集联合在一起,去除重复的记录
- UNION ALL将多个结果联合在一起，不去除重复的记录
Table 7-1： Set Operators
Operator
Description
UNION ALL
Returns all the rows retrieved by the queries, including duplicate rows.
UNION
Returns all non—duplicate rows retrieved by the queries.
INTERSECT
Returns rows that are retrieved by both queries.
MINUS
Returns the remaining rows when the rows retrieved by the second query are subtracted from the rows retrieved by the first query.
- union语法：
select
union
select
union
select
例子1：
select 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. 查询结果的交集(INTERSECT）：
- 将多个查询结果集联合在一起，只保留相同的记录。摘除不同的记录
- 语法:
select 语句
intersect
select
intersect
select
例子:
select ＊ from emp where deptno=10
intersect
select * from emp where job=’CLERK’
7. 查询结果的差集（MINUS）：
- 将多个结果集联合在一起，保留它们差异的记录，将包含第2个结果集的记录减去。
- 语法：
select
minus
select
minus
select
例子:
select ＊ from emp where deptno=10
minus
select * from emp where job='CLERK'
注：
1. Oracle在合并2个结果集时，Oracle并不关心合并运算符的任何一边的列名,合并的结果集以第一个结果集的列名为新的列名。
2. select语句必须有相同的列，如果被查询的结果集有不同的列，可使用Oracle的内置表达式合成为相同的列数。
3. select的相对应的列必须为相同的类型。长度可以不同.
4. 在对输出进行排序时,Oracle使用第1个select语句的列名给出查询结果,因为，只有第1个select的列作为查询结果，因此只有第一个select的列名出现在order by 子句中。
8. Oracle9i实现SQL Server 2000 中的select top n 的SQL语句：
在Oracle9i中没有类似的select top n 的语句。但是它提供了ROWNUM内置函数。
可以实现top n的查询语句。
Select
From
Where rownum〈=n
ROWNUM是Oracle在做查询时自动计算的。它会随着记录集的变化而动态变化.
ROWNUM返回第一次从表中选择时返回行的序列号。第1行的ROWNUM为1.
如果想返回一个复杂查询的结果集的top n, 要把此结果集作为中间结果集放在from中,再使用rownum函数。如下例子：
select * from
（ select deptno， sum(sal）
from emp
group by deptno)
where rownum<=2
课前提问：
1. 查询采购金额多于5000元的客户清单,以及每个客户的采购金额，并按总采购金额排序 （客户名称， 采购金额）
select a.CUST_FIRST_NAME||' ’｜| CUST_LAST_NAME, sum(c。UNIT_PRICE＊C.QUANTITY) as totalRMB
from CUSTOMERS a, ORDERS b, ORDER_ITEMS c
where a。customer_ID=b。Customer_ID and b。order_id=c.Order_ID
group by a.CUST_FIRST_NAME|｜' '｜| CUST_LAST_NAME
having sum（c。UNIT_PRICE*C.QUANTITY)>5000
order by totalRMB desc
2. 查询总销售额大于5000元的产品清单，以及每种产品的累计销售额，按总销售额降序排序 （产品名称,销售金额）
select a。PRODUCT_NAME，sum(c.UNIT_PRICE*C.QUANTITY） as totalRMB
from PRODUCT_INFORMATION a, ORDER_ITEMS c
where a。PRODUCT_ID=c.PRODUCT_ID
group by a.PRODUCT_NAME
having sum(c.UNIT_PRICE*C。QUANTITY）〉5000
order by totalRMB desc
[] 课后作业：
1. 查询工作地点在‘NEWYORK’的员工的编号，姓名，职位，部门名称。
2. 查询每个部门的名称，地点，汇总工资，平均公司。
3. 查询订单号为2354的定单的明细信息（产品名称，销售数量，销售金额）
4. 查询订单金额大于1000的订单信息(显示订单号，客户名称，订单日期，销售金额)
5. 查询公司1999年每个月的订单数量，订单金额，最大的订单金额，最小的订单金额
二次作业