经典表关联与多表查询

经典表关联与多表查询目的：掌握从多个表查询数据的基本知识了解和学习外连接(outjoin）掌握内连接授课内容：对多于一个表的数据查询1.1现实情况中，在数据库应用中，数据存在于多个相关联的表中.基本上没有数据只存谢谢阅读在于一个表中的情况.小的应用系统一般也有十几个表,大型系统一般有上千个表。1.2你经常要作的就是在多个表中进行数据查询。谢谢阅读1.3Oracle对多表查询使用表连接的技术（tablejoin)谢谢阅读1.4表连接的基本条件：2个表必须有公共字段(同名字段或不同名字段）在一个表中，这个公共字段必须是主键（PK)1.5二个表中的公共字段,在一个表中是主键,在另外一个表中就是外键（FK）。感谢阅读1.6二表关联中，公共字段是主键的表称为父表（主表)。是外键的表称为子表（详细表)。谢谢阅读1.7研究一下scott下的emp和dept表的关系。感谢阅读1.8研究一下oe下的表:CATEGORIES_TABCUSTOMERSINVENTORIESORDERSORDER_ITEMSPRODUCT_DESCRIPTIONSPRODUCT_INFORMATION1.9多表查询的语法select子句from 表1［别名］,表2［别名］，视图［别名]，（select子句）别名感谢阅读where连接语句and其他条件语句［oupyby分类项目］[having子句]［orderby子句]1.10任务：查询每个员工的编号，姓名，部门名称，部门位置selectempno,ename，dname,loc感谢阅读fromempa,deptbwherea。DEPTNO=b.DEPTNO1.11多表查询的原则：对N个表连接,至少要有N-1个相等的条件.而且每个表的公共字谢谢阅读段必须出现一次。1.12多表关联中，如果没有指定关联等式，将产生无效的结果，它将每个关联的表的记录跟其他表的所有记录组合，产生笛卡尔积的数据.感谢阅读测试：selectempno,ename，dname，loc精品文档放心下载fromempa,deptb1.13对OE用户的测试查询公司库存信息,显示仓库名称，产品名称,库存数量,库存金额谢谢阅读select c。WAREHOUSE_NAME,b。PRODUCT_NAME，a.QUANTITY_ON_HAND,a。精品文档放心下载QUANTITY_ON_HAND*b.LIST_PRICE谢谢阅读fromINVENTORIESa,PRODUCT_INFORMATIONb,WAREHOUSESc感谢阅读wherea.WAREHOUSE_ID=c.WAREHOUSE_IDanda。PRODUCT_ID=b。PRODUCT_ID精品文档放心下载内连接（selfjoin）当多表关联使用一个表进行数据进行数据查询，这种连接叫自连接.精品文档放心下载自连接的主要功能是查询表中除了主键外,是否有重复的记录.精品文档放心下载任务：查询员工表中,有同名,职位相同的员工信息(编号,项目，职位，工资)selecta.empno，a。ename，a。job谢谢阅读fromempa，empbwherea。empno<〉b.empnoanda。deptno=b。deptnoanda。job=b。job感谢阅读日常生活中在数据录入时产生的错误由于工作失误,一个数据录入到系统2次或多次。一般在进行自动的数据导入时,产生大量的重复记录。子连接的要求：自连接至少要2个或2个以上的等式条件,一个用于关联,其他用于表示重复的数据。精品文档放心下载外连接（outjoin）：1.1内连接是关联的表的公共字段值必须相同,所有不同的值的记录都没有了。感谢阅读1.2外连接是值一个表的中的公共字段的值可以不与另一个表的公共字段值相同.一般精品文档放心下载时它是null。1.3任务：查询员工表，显示员工的项目,部门名称,部门位置，要求显示所有的员工，即使员工没有部门。谢谢阅读selecta.ename，b。dname，b。loc感谢阅读fromempaleftouterjoindeptb感谢阅读ona。deptno=b。deptno注：此任务无法使用正常的内连接。因为有一个员工没有部门，它的部门编号为空。精品文档放心下载常见的任务如:信息系统中的文档，申请审批,当刚创建时，所有审批信息为null。.但有的审批已经完成。如果与审批人表关联的话，要显示所有的申请,就必须使用外连接。感谢阅读1.4外连接语法：(1）左连接：取出左边的表的所有记录select子句from表1 leftouterjoin表2谢谢阅读on表1。公共字段＝表2。公共字段(2）右连接：取出右边表的所有记录select子句from表1 rightouterjoin表2谢谢阅读on表1。公共字段＝表2.公共字段（3)全连接（左右连接）：左右两边的表的记录都取。感谢阅读select子句from表1 full outerjoin表2精品文档放心下载on表1。公共字段＝表2.公共字段selecta.empno，a.deptno,b。deptno,b.dname感谢阅读fromempaleftouterjoindeptb精品文档放心下载ona.deptno=b.deptnoselecta.empno，a.deptno，b.deptno,b.dname谢谢阅读fromemparightouterjoindeptb感谢阅读ona。deptno=b.deptnoselecta。ename,b。dname,b。loc谢谢阅读fromempafullouterjoindeptb精品文档放心下载ona.deptno=b。deptnoselecta。dname，b.enamefromdeptafullouterjoinempb感谢阅读ona。deptno=b.deptno一般情况下，不使用上述的语法，而使用如下的语法：selecta.dname,b.enamefromdepta,empbwherea。deptno（+）=b。deptno —-精品文档放心下载一般情况情况下，(+）放在关联表的主键的一侧，才有实际的意义。感谢阅读没有(+）的表的取所有的记录，关联的表如果有记录对应就显示关联的值,没有关联的值显示null.精品文档放心下载但使用（+）的情况下,无法实现全连接。因为无法在where的左右同时使用（+)。谢谢阅读selecta.ename,b。dnamefromempa，deptbwherea.deptno=b。deptno(+)谢谢阅读下列的语句是无法通过的：selecta.ename,b.dnamefromempa，deptbwherea。deptno（+）=b。deptno（+)精品文档放心下载1.5任务：自关联（self-join)4.1有些情况下,需要关联一个表，这种关联叫自关联.感谢阅读4.2自关联经常使用的一般是查看表中的记录是否重复。在信息管理系统中，有时出现数据录入的错误.同一个数据，被输入了2次以上，除了主键不一样，其他字段基本上一样.即查询重复的记录。谢谢阅读4.3数据录入错误的发生可能的情况：数据的自动导入，新建系统从老系统中批量导入数据，导致大量的重复记录.感谢阅读用户输入错误的数据,将一个数据输入的2次。4.4如SCOTT的员工表EMP，MGR字段是员工的经理的员工号。要查询每个员工的经理的姓名。就需要使用自关联。谢谢阅读selecta。ename,b。enamefromempa,empbwherea。mgr=b.empno4.5查询emp表中可能同名的员工的记录。selecta.empno,a。enamefromempa，empbwherea.empno<>b。empnoanda.ename=b。ename感谢阅读4.6查询员工表emp的重复记录：selecta.empno,a。enamefromempa，empbwherea。empno〈〉b。empnoanda。ename=b.enameanda。job=b。jobanda.sal=b。sal谢谢阅读4.7子连接会导致对表的大量的操作，需要很大的内存。其他用户对自连接的表的操作谢谢阅读会等待很长的时间.一般情况下最好不要使用自关联。4.8表的自关联的与内关联不同，自关联至少要2个或2个以上的等式条件.谢谢阅读查询结果的联合（UNION）(UNIONALL)将多个查询结果联合在一起：UNION将多个结果集联合在一起，去除重复的记录UNIONALL将多个结果联合在一起,不去除重复的记录感谢阅读Table7—1:SetOperatorsOperator

DescriptionUNIONALL

Returnsalltherowsretrievedbythequeries,includingduplicaterows。谢谢阅读UNION

Returnsallnon-duplicaterowsretrievedbythequeries。感谢阅读INTERSECT

Returnsrowsthatareretrievedbybothqueries.精品文档放心下载MINUS

Returnstheremainingrowswhentherowsretrievedbythesecondqueryaresubtractedfromtherowsretrievedbythefirstquery.谢谢阅读-

union语法：selectunionselectunionselect例子1：selectempno,enamefromempwheredeptno=10unionselectdeptno，dnamefromdept例子2：select＊fromempwheredeptno=10谢谢阅读unionselect＊fromempwherejob='CLERK'感谢阅读unionall语法selectunionallselect精品文档放心下载unionallselect例子1:select*fromempwheredeptno=10感谢阅读unionallselect*fromempwherejob='CLERK’谢谢阅读查询结果的交集(INTERSECT)：将多个查询结果集联合在一起,只保留相同的记录。摘除不同的记录精品文档放心下载语法：select语句intersectselect精品文档放心下载intersectselect例子：select*fromempwheredeptno=10谢谢阅读intersectselect*fromempwherejob='CLERK'精品文档放心下载查询结果的差集（MINUS）:将多个结果集联合在一起，保留它们差异的记录，将包含第2个结果集的记录减去.感谢阅读语法：selectminusselectminusselect例子：select＊fromempwheredeptno=10感谢阅读minusselect*fromempwherejob='CLERK'精品文档放心下载注:Oracle在合并2个结果集时,Oracle并不关心合并运算符的任何一边的列名，合并的结果集以第一个结果集的列名为新的列名.谢谢阅读select语句必须有相同的列，如果被查询的结果集有不同的列，可使用Oracle的内置表达式合成为相同的列数。精品文档放心下载select的相对应的列必须为相同的类型。长度可以不同。精品文档放心下载在对输出进行排序时，Oracle使用第1个select语句的列名给出查询结果，因为，只有第1个select的列作为查询结果，因此只有第一个select的列名出现在orderby子句中.谢谢阅读Oracle9i实现SQLServer2000中的selecttopn的SQL语句：谢谢阅读Oracle9i中没有类似的selecttopn的语句。但是它提供了ROWNUM内置函数.谢谢阅读可以实现topn的查询语句。SelectFromWhererownum<=nROWNUM是Oracle在做查询时自动计算的。它会随着记录集的变化而动态变化。感谢阅读ROWNUM返回第一次从表中选择时返回行的序列号.第1行的ROWNUM为1。精品文档放心下载如果想返回一个复杂查询的结果集的topn,要把此结果集作为中间结果集放在from中，再使用rownum函数。如下例子：精品文档放心下载select*from（selectdeptno，sum（sal）fromempgroupbydeptno)whererownum〈=2课前提问：查询采购金额多于5000元的客户清单，以及每个客户的采购金额,并按总采购金额排序(客户名称,采购金额）精品文档放心下载selecta.CUST_FIRST_NAME｜|’'｜|CUST_LAST_NAME,sum(c.UNIT_PRICE*C.QUANTITY)astotalRMBfromCUSTOMERSa，ORDERSb,ORDER_ITEMSc精品文档放心下载wherea.customer_ID=b.Customer_I