oracle数据库ppt+中科院培训专用Les04_cn

第四章从多表中显示数据Copyright©OracleCorporation,2001.Allrightsreserved.从多表中显示数据进度表：时间主题55分钟讲演55分钟练习110分钟总共中国科学院西安网络中心©2005OracleSQL入门4-1第四章从多表中显示数据4-2Copyright©OracleCorporation,2001.Allrightsreserved.目标完成本课后,您应当能够执行下列操作：•写SELECT语句使用等值和非等值连接从多个表中访问数据•使用外连接查看不满足连接条件的数据•使用一个自连接，连接一个表到它自己课程目标本课学习怎样从多个表中获得数据。中国科学院西安网络中心©2005OracleSQL入门4-2第四章从多表中显示数据4-3Copyright©OracleCorporation,2001.Allrightsreserved.从多表中获得数据EMPLOYEESDEPARTMENTS……来自多表的数据有时你需要使用来自多表的数据。在幻灯片中，报告显示了来自单独的两个表的数据。EmployeeID在EMPLOYEES表中。DepartmentID在EMPLOYEES和DEPARTMENTS表中都有。LocationIDs在DEPARTMENTS表中。为了生成报告，你需要连接EMPLOYEES和DEPARTMENTS表，并从两个表中访问数据。中国科学院西安网络中心©2005OracleSQL入门4-3第四章从多表中显示数据4-4Copyright©OracleCorporation,2001.Allrightsreserved.笛卡尔乘积•笛卡尔乘积的形成，当：–一个连接条件被遗漏时–一个连接条件不正确时–在第一个表中的所有行被连接到第二个表的所有行时•为了避免笛卡尔乘积的形成，在WHERE子句中应当总是包含正确的连接条件笛卡尔乘积当一个连接条件无效或被遗漏时，其结果是一个笛卡尔乘积(Cartesianproduct)，其中所有行的组合都被显示。第一个表中的所有行连接到第二个表中的所有行。一个笛卡尔乘积会产生大量的行，其结果没有什么用。你应该在WHERE子句中始终包含一个有效的连接条件，除非你有特殊的需求，需要从所有表中组合所有的行。对于一些测试笛卡尔乘积是有用的，例如你需要产生大量的行来模拟一个相当大的数据量。中国科学院西安网络中心©2005OracleSQL入门4-4第四章从多表中显示数据4-5Copyright©OracleCorporation,2001.Allrightsreserved.笛卡尔乘积的产生笛卡尔乘积:20x8=160行EMPLOYEES(20行)DEPARTMENTS(8行)……笛卡尔乘积(续)如果连接条件被遗漏，就会产生笛卡尔乘积。幻灯片中的例子从EMPLOYEES和DEPARTMENTS表中显示雇员的名字和部门名字。因为无WHERE子句被指定，EMPLOYEES表中所有的行(20行)被与DEPARTMENTS表中的所有行(8行)连接，因此产生160行的输出。SELECTlast_name,department_namedept_nameFROMemployees,departments;中国科学院西安网络中心©2005OracleSQL入门4-5第四章从多表中显示数据4-6Copyright©OracleCorporation,2001.Allrightsreserved.•Equijoin等值•Non-equijoin非等值•Outerjoin外连接•Selfjoin自连接连接的类型•Crossjoins交叉连接•Naturaljoins自然连接•Usingclause使用子句•Fullortwosidedouterjoins全连接或双向外连接•Arbitraryjoinconditionsforouterjoins对于外连接的任意连接条件SQL:1999适应连接:Oracle所有的连接(8i以前):连接的类型Oracle9i数据库提供SQL:1999兼容的连接语法。在9i发布以前，连接语法不同于ANSI标准。新的SQL:1999兼容连接语法不提供任何对Oracle以前发布的版本中私有连接语法性能的改进。中国科学院西安网络中心©2005OracleSQL入门4-6第四章从多表中显示数据4-7Copyright©OracleCorporation,2001.Allrightsreserved.用Oracle语法连接表使用一个连接从多个表中查询数据•在WHERE子句中写连接条件•当多个表中有相同的列名时，将表名作为列名的前缀SELECTtable1.column,table2.columnFROMtable1,table2WHEREtable1.column1=table2.column2;SELECTtable1.column,table2.columnFROMtable1,table2WHEREtable1.column1=table2.column2;定义连接当数据从多表中查询时，要使用连接(join)条件。一个表中的行按照存在于相应列中的公值被连接到另一个表中的行，即，通常所说的主键和外键列。从多个表中显示数据，在WHERE子句中写一个简单的连接条件。在语法中：table1.column指示获取数据的表和列table1.column1=是连接表的条件table2.column2原则在写一个连接表的SELECT语句时，在列名前面用表名可以使语义清楚，并且加快数据库访问。如果相同的列名出现在多个表中，列名必须前缀表名。为了连接n个表在一起，你昀少需要n-1个连接条件。例如，为了连接4个表，昀少需要3个连接条件。如果表中有一个连接主键，该规则可能不适用，其中可能有多行用来唯一地标识每一行。更多信息，见Oracle9iSQLReference,“SELECT”。中国科学院西安网络中心©2005OracleSQL入门4-7第四章从多表中显示数据4-8Copyright©OracleCorporation,2001.Allrightsreserved.什么是等值连接?EMPLOYEESDEPARTMENTS外键FK主键PK……等值连接为了确定一个雇员的部门名，需要比较EMPLOYEES表中的DEPARTMENT_ID列与DEPARTMENTS表中的DEPARTMENT_ID列的值。在EMPLOYEES和DEPARTMENTS表之间的关系是一个相等(equijoin)关系，即，两个表中DEPARTMENT_ID列的值必须相等。通常，这种连接类型包括主键和外键。注：等值连接也被称为简单连接(simplejoins)或内连接(innerjoins)。教师注释解释抉择矩阵(decisionmatrix)用于简化写连接的使用，例如，如果你想显示同一个部门中所有姓Goyal的雇员的名字和部门号，可以写出下面的决策矩阵：显示列源表条件last_nameemployeeslast_name='Goyal'department_namedepartmentsemployees.department_id=departments.department_id现在看着上面的抉择矩阵，SQL语句可以容易地写出。第一列给出SELECT语句的字段列表，第二列给出FROM子句，第三列给出WHERE子句的条件。中国科学院西安网络中心©2005OracleSQL入门4-8第四章从多表中显示数据4-9Copyright©OracleCorporation,2001.Allrightsreserved.SELECTemployees.employee_id,employees.last_name,employees.department_id,departments.department_id,departments.location_idFROMemployees,departmentsWHEREemployees.department_id=departments.department_id;用等值连接返回记录…用等值连接返回记录在幻灯片的例子中：SELECT子句指定要返回的列名：−employeelastname、employeenumber和departmentnumber，这些是EMPLOYEES表中的列−departmentnumber、departmentname和locationID，这些是DEPARTMENTS表中的列FROM子句指定数据库必须访问的两个表：−EMPLOYEES表−DEPARTMENTS表WHERE子句指定表怎样被连接：EMPLOYEES.DEPARTMENT_ID=DEPARTMENTS.DEPARTMENT_ID因为DEPARTMENT_ID列是两个表的公共列，它必须用表名做前缀以避免混淆。中国科学院西安网络中心©2005OracleSQL入门4-9第四章从多表中显示数据4-10Copyright©OracleCorporation,2001.Allrightsreserved.使用AND操作符附加搜索条件EMPLOYEESDEPARTMENTS……添加查询条件除连接之外，你可能还要求用WHERE子句在连接中限制一个或多个表中的行。例如，为了显示雇员Matos的部门号和部门名，你需要添加条件到WHERE子句中。SELECTlast_name,employees.department_id,department_nameFROMemployees,departmentsWHEREemployees.department_id=departments.department_idANDlast_name='Matos';中国科学院西安网络中心©2005OracleSQL入门4-10第四章从多表中显示数据4-11Copyright©OracleCorporation,2001.Allrightsreserved.限制不明确的列名•在多表中使用表前缀限制修饰列名•用表前缀改善性能•用列别名区别有相同名称，但在不同表中的列限制不明确的列名你需要在WHERE子句中用表的名字限制列的名字以避免含糊不清。没有表前缀，DEPARTMENT_ID列可能来自DEPARTMENTS表，也可能来自EMPLOYEES表，这种情况下需要添加表前缀来执行查询。如果列名在两个表之间不相同，就不需要限定列。但是，使用表前缀可以改善性能，因为你确切地告诉Oracle服务器在那里找到列。必须限定不明确的列名也适用于在其它子句中可能引起混淆的那些列，例如SELECT子句或ORDERBY子句。中国科学院西安网络中心©2005OracleSQL入门4-11第四章从多表中显示数据4-12Copyright©OracleCorporation,2001.Allrightsreserved.SELECTe.employee_id,e.last_name,e.department_id,d.department_id,d.location_idFROMemployeese,departmentsdWHEREe.department_id=d.department_id;使用表别名•使用表别名简化查询•使用表别名改善性能表别名用表名限制列名可能是非常耗时的，特别是当表名字很长时，你可以使用表别名代替表名。就象列别名给列另一个名字一样，表别名给表另一个名字。表别名有助于保持SQL代码较小，因此使用的存储器也少。注意在例子中表的FROM子句中怎样定义表别名。表名完全指定，然后跟着别名。EMPLOYEES表被给予别名e，DEPARTMENTS表被给予别名d。原则表别名昀多可以有30个字符，但短一些更好。如果在FROM子句中表别名被用于指定的表，那么在整个SELECT语句中都要使用表别名。表别名应该是有意义的。表别名只对当前的SELECT语句有效。中国科学院西安网络中心©2005OracleSQL入门4-12第