复杂查询-实现多表数据的访问.ppt

4 Copyright Oracle Corporation, 2001. All rights reserved. 从多表中显示数据 4-2Copyright Oracle Corporation, 2001. All rights reserved. 目标 完成本课后, 您应当能够执行下列操作： 写SELECT 语句使用等值和非等值连接从多个表中访 问数据 使用外连接查看不满足连接条件的数据 使用一个自连接，连接一个表到它自己 4-3Copyright Oracle Corporation, 2001. All rights reserved. 大纲 笛卡尔积 连接的类型 ORACLE的所有连接 Equijoin 等值 Non-equijoin 非等值 Outer join 外连接 Self join 自连接 SQL：1999适应性连接 Cross joins 交叉连接 Natural joins 自然连接 Using clause 使用子句 Full or two sided outer joins 全连接或双向外连接 对于外连接的任意连接条件 4-4Copyright Oracle Corporation, 2001. All rights reserved. 从多表中获得数据 EMPLOYEES DEPARTMENTS 4-5Copyright Oracle Corporation, 2001. All rights reserved. 笛卡尔乘积 笛卡尔乘积的形成，当： 一个连接条件被遗漏时 一个连接条件不正确时 在第一个表中的所有行被连接到第二个表的所有行时 为了避免笛卡尔乘积的形成，在WHERE 子句中应当总 是包含正确的连接条件 4-6Copyright Oracle Corporation, 2001. All rights reserved. 笛卡尔乘积的产生 笛卡尔乘积 20x8=160 rows EMPLOYEES (20 rows)DEPARTMENTS (8 rows) 4-7Copyright Oracle Corporation, 2001. All rights reserved. 大纲 笛卡尔积 连接的类型 ORACLE的所有连接 Equijoin 等值 Non-equijoin 非等值 Outer join 外连接 Self join 自连接 SQL：1999适应性连接 Cross joins 交叉连接 Natural joins 自然连接 Using clause 使用子句 Full or two sided outer joins 全连接或双向外连接 对于外连接的任意连接条件 4-8Copyright Oracle Corporation, 2001. All rights reserved. Equijoin 等值 Non-equijoin 非等值 Outer join 外连接 Self join 自连接 连接的类型 Cross joins 交叉连接 Natural joins 自然连接 Using clause 使用子句 Full or two sided outer joins 全连接或双向外连接 Arbitrary join conditions for outer joins 对于外连接的 任意连接条件 SQL: 1999 适应连接: Oracle所有的连接 (8i 以前): 4-9Copyright Oracle Corporation, 2001. All rights reserved. 用Oracle 语法连接表 使用一个连接从多个表中查询数据 在WHERE 子句中写连接条件. 当多个表中有相同的列名时，将表名作为列名的前缀 SELECTtable1.column, table2.column FROMtable1, table2 WHEREtable1.column1 = table2.column2; 4-10Copyright Oracle Corporation, 2001. All rights reserved. 大纲 笛卡尔积 连接的类型 ORACLE的所有连接 Equijoin 等值 Non-equijoin 非等值 Outer join 外连接 Self join 自连接 SQL：1999适应性连接 Cross joins 交叉连接 Natural joins 自然连接 Using clause 使用子句 Full or two sided outer joins 全连接或双向外连接 对于外连接的任意连接条件 4-11Copyright Oracle Corporation, 2001. All rights reserved. 什么是等值连接? EMPLOYEES DEPARTMENTS Foreign keyPrimary key 4-12Copyright Oracle Corporation, 2001. All rights reserved. SELECT employees.employee_id, employees.last_name, employees.department_id, departments.department_id, departments.location_id FROM employees, departments WHERE employees.department_id = departments.department_id; 用等值连接返回记录 4-13Copyright Oracle Corporation, 2001. All rights reserved. 使用AND 操作符附加搜索条件 EMPLOYEES DEPARTMENTS 4-14Copyright Oracle Corporation, 2001. All rights reserved. 限制不明确的列名 在多表中使用表前缀限制修饰列名 用表前缀改善性能 用列别名区别有相同名称，但在不同表中的列 4-15Copyright Oracle Corporation, 2001. All rights reserved. SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e , departments d WHERE e.department_id = d.department_id; 使用表别名 使用表别名简化查询 使用表别名改善性能. 4-16Copyright Oracle Corporation, 2001. All rights reserved. 多于两个表的连接 EMPLOYEES LOCATIONS DEPARTMENTS 为了连接n 个表，你最少需要n-1 个连接条件。例如， 为了连接3 个表，最少需要两个连接 4-17Copyright Oracle Corporation, 2001. All rights reserved. 大纲 笛卡尔积 连接的类型 ORACLE的所有连接 Equijoin 等值 Non-equijoin 非等值 Outer join 外连接 Self join 自连接 SQL：1999适应性连接 Cross joins 交叉连接 Natural joins 自然连接 Using clause 使用子句 Full or two sided outer joins 全连接或双向外连接 对于外连接的任意连接条件 4-18Copyright Oracle Corporation, 2001. All rights reserved. 非等值连接 EMPLOYEESJOB_GRADES 在EMPLOYEES 表中的工资 必须在JOB_GRADES 表中 的最低工资和最高工资之间 4-19Copyright Oracle Corporation, 2001. All rights reserved. 用非等值连接返回记录 SELECT e.last_name, e.salary, j.grade_level FROM employees e, job_grades j WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal; 4-20Copyright Oracle Corporation, 2001. All rights reserved. 大纲 笛卡尔积 连接的类型 ORACLE的所有连接 Equijoin 等值 Non-equijoin 非等值 Outer join 外连接 Self join 自连接 SQL：1999适应性连接 Cross joins 交叉连接 Natural joins 自然连接 Using clause 使用子句 Full or two sided outer joins 全连接或双向外连接 对于外连接的任意连接条件 4-21Copyright Oracle Corporation, 2001. All rights reserved. 外连接 EMPLOYEESDEPARTMENTS 在部门190中无雇员 4-22Copyright Oracle Corporation, 2001. All rights reserved. 外连接语法 你可以用一个外连接查看那些不满足连接条件的行 外连接运算符是加号(+) SELECT table1.column, table2.column FROMtable1, table2 WHEREtable1.column(+) = table2.column; SELECT table1.column, table2.column FROMtable1, table2 WHEREtable1.column = table2.column(+); 4-23Copyright Oracle Corporation, 2001. All rights reserved. SELECT e.last_name, e.department_id, d.department_name FROM employees e, departments d WHERE e.department_id(+) = d.department_id ; 使用外连接 4-24Copyright Oracle Corporation, 2001. All rights reserved. 大纲 笛卡尔积 连接的类型 ORACLE的所有连接 Equijoin 等值 Non-equijoin 非等值 Outer join 外连接 Self join 自连接 SQL：1999适应性连接 Cross joins 交叉连接 Natural joins 自然连接 Using clause 使用子句 Full or two sided outer joins 全连接或双向外连接 对于外连接的任意连接条件 4-25Copyright Oracle Corporation, 2001. All rights reserved. 自连接 EMPLOYEES (WORKER)EMPLOYEES (MANAGER) 在WORKER 表中的MANAGER_ID 等于MANAGER 表中的EMPLOYEE_ID 4-26Copyright Oracle Corporation, 2001. All rights reserved. 连接一个表到它本身 SELECT worker.last_name | works for | manager.last_name FROM employees worker, employees manager WHERE worker.manager_id = manager.employee_id ; 4-27Copyright Oracle Corporation, 2001. All rights reserved. 练习4, 第一部分: 概览 这部分练习包括用Oracle 语法写将表连接在一起的查询 4-28Copyright Oracle Corporation, 2001. All rights reserved. 大纲 笛卡尔积 连接的类型 ORACLE的所有连接 Equijoin 等值 Non-equijoin 非等值 Outer join 外连接 Self join 自连接 SQL：1999适应性连接 Cross joins 交叉连接 Natural joins 自然连接 Using clause 使用子句 Full or two sided outer joins 全连接或双向外连接 对于外连接的任意连接条件 4-29Copyright Oracle Corporation, 2001. All rights reserved. 用SQL 连接表: 1999 语法 用一个连接从多个表中查询数据 SELECTtable1.column, table2.column FROMtable1 CROSS JOIN table2 | NATURAL JOIN table2 | JOIN table2 USING (column_name) | JOIN table2 ON(table1.column_name = table2.column_name) | LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.column_name = table2.column_name); 4-30Copyright Oracle Corporation, 2001. All rights reserved. 大纲 笛卡尔积 连接的类型 ORACLE的所有连接 Equijoin 等值 Non-equijoin 非等值 Outer join 外连接 Self join 自连接 SQL：1999适应性连接 Cross joins 交叉连接 Natural joins 自然连接 Using clause 使用子句 Full or two sided outer joins 全连接或双向外连接 对于外连接的任意连接条件 4-31Copyright Oracle Corporation, 2001. All rights reserved. 创建交叉连接 CROSS JOIN 子句导致两个表的交叉乘积 该连接和两个表之间的笛卡尔乘积是一样的 SELECT last_name, department_name FROM employees CROSS JOIN departments ; 4-32Copyright Oracle Corporation, 2001. All rights reserved. 大纲 笛卡尔积 连接的类型 ORACLE的所有连接 Equijoin 等值 Non-equijoin 非等值 Outer join 外连接 Self join 自连接 SQL：1999适应性连接 Cross joins 交叉连接 Natural joins 自然连接 Using clause 使用子句 Full or two sided outer joins 全连接或双向外连接 对于外连接的任意连接条件 4-33Copyright Oracle Corporation, 2001. All rights reserved. 创建自然连接 NATURAL JOIN 子句基于两个表之间有相同名字的所 有列 它从两个表中选择在所有的匹配列中有相等值的行 如果有相同名字的列的数据类型不同，返回一个错误 4-34Copyright Oracle Corporation, 2001. All rights reserved. SELECT department_id, department_name, location_id, city FROM departments NATURAL JOIN locations ; 用自然连接返回记录 4-35Copyright Oracle Corporation, 2001. All rights reserved. 大纲 笛卡尔积 连接的类型 ORACLE的所有连接 Equijoin 等值 Non-equijoin 非等值 Outer join 外连接 Self join 自连接 SQL：1999适应性连接 Cross joins 交叉连接 Natural joins 自然连接 Using clause 使用子句 Full or two sided outer joins 全连接或双向外连接 对于外连接的任意连接条件 4-36Copyright Oracle Corporation, 2001. All rights reserved. 用USING 子句创建连接 如果一些列有相同的名字，但数据类型不匹配， NATURAL JOIN 子句能够用USING 子句修改以指定将被 用于一个等值连接的列 当有多个列匹配时，用USING 子句匹配唯一的列 在引用列不要使用表名或者别名 NATURAL JOIN 和USING 子句是相互排斥的 4-37Copyright Oracle Corporation, 2001. All rights reserved. SELECT e.employee_id, e.last_name, d.location_id FROM employees e JOIN departments d USING (department_id) ; 用USING 子句返回记录 4-38Copyright Oracle Corporation, 2001. All rights reserved. 用ON 子句创建连接 对于自然连接的连接条件，基本上是带有相同名字的所 有列的等值连接 为了指定任意条件，或者指定要连接的列，可以使用 ON 子句 连接条件从另一个搜索条件中被分开 ON 子句使得代码易懂 4-39Copyright Oracle Corporation, 2001. All rights reserved. SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e JOIN departments d ON (e.department_id = d.department_id); 用ON 子句返回记录 4-40Copyright Oracle Corporation, 2001. All rights reserved. 用ON 子句创建三向连接 SELECT employee_id, city, department_name FROM employees e JOIN departments d ON d.department_id = e.department_id JOIN locations l ON d.location_id = l.location_id; 4-41Copyright Oracle Corporation, 2001. All rights reserved. 大纲 笛卡尔积 连接的类型 ORACLE的所有连接 Equijoin 等值 Non-equijoin 非等值 Outer join 外连接 Self join 自连接 SQL：1999适应性连接 Cross joins 交叉连接 Natural joins 自然连接 Using clause 使用子句 Full or two sided outer joins 全连接或双向外连接 对于外连接的任意连接条件 4-42Copyright Oracle Corporation, 2001. All rights reserved. 内与外连接 在SQL: 1999 中，连接两个表，仅返回匹配的行的连 接，称为内连接 在两个表之间的连接，返回内连接的结果，同时还返回 不匹配行的左（或右）表的连接，称为左（或右）连接 在两个表之间的连接，返回内连接的结果， 同时还返 回左和右连接，称为全连接 4-43Copyright Oracle Corporation, 2001. All rights reserved. SELECT e.last_name, e.department_id, d.department_name FROM employees e LEFT OUTER JOIN departments d ON (e.department_id = d.department_id) ; 左外连接 4-44Copyright Oracle Corporation, 2001. All rights reserved. SELECT e.last_name, e.depart