《Oracle Les06_显示多个表中的数据》由会员分享，可在线阅读，更多相关《Oracle Les06_显示多个表中的数据（42页珍藏版）》请在金锄头文库上搜索。
1、显示多个表中的数据6 - 2ObjectivesAfter completing this lesson, you should be able to do the following: 编写 SELECT 语句，以便使用等值联结和非等值联结来访问 多个表中的数据 使用自联结将表联结到自身 使用外联结查看不满足联结条件的数据 从两个或者多个表的所有行生成笛卡尔乘积6 - 3获得多个表中的数据EMPLOYEES DEPARTMENTS 6 - 4联结类型符合SQL:1999标准的联结: 自然联结: NATURAL JOIN 子句 USING 子句 ON 子句外联结: LEFT OUTER JOI
2、N RIGHT OUTER JOIN FULL OUTER JOIN交叉联结6 - 5使用Oracle SQL:1999 语法将表联结起来使用联结可以查询多个表中的数据:SELECTtable1.column, table2.column FROMtable1 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_nam
3、e = table2.column_name)| CROSS JOIN table2;6 - 6限定模糊的列名使用表前缀可以限定多个表中的列名. 使用表前缀可以提高性能. 表前缀不是指表的全名，可以给表起别名. 表别名可以是很短的名字: 更少的SQL代码, 更少的内存占用 使用列别名可以区分位于不同表中但名称相同的列.6 - 7创建自然联结 NATURAL JOIN 子句是以两个表中具有相同名称的所有列 为基础的. 它将选择两上表中那些在所有名称相同的列中有相等值的 行. 如果列具有相同的名称，但是数据类型不同，就会返回一个 错误.6 - 8SELECT department_id, depa
4、rtment_name,location_id, city FROM departments NATURAL JOIN locations ;通过自然联结检索记录注：上列中显示，LOCATIONS表是通过LOCATION_ID列与 DEPARTMENT表联结的，该列是两表中唯一一个名称相同的列。如 果还有其它公用的列，联结就会使用所有的这些相同的列。6 - 9SELECT department_id, department_name,location_id, city FROM departments NATURAL JOIN locations ;等效于等值联结SELECT departme
5、nt_id, department_name,location_id, city FROM departments,locations WHERE departments.location_id=locations.location_id;在Oracle9i版本以前，ORACLE使用自己传统的语法来做表的联结，发 展至今，为了兼容ANSI SQL:1999标准，使数据库支持这两种语法， 新的ANSI SQL:1999标准较之传统语法而言，并没有性能的优势，仅 仅是一种国际标准。 区别：传统的语法，是先做一个笛卡尔乘积做为临时的中间结果集，再 利用WHERE条件去筛选结果。ANSI SQL:19
6、99则是先联结表，然后将任意单独的WHERE子句 条件应用到联结的结果集。 趋势：传统的Oracle联结语法，正由ANSI兼容的标准语法代替。6 - 10SELECT department_id, department_name,location_id, city FROM departments NATURAL JOIN locations WHERE department_id IN （20，50）;带WHERE子句的自然联结通过WHERE子句，可以对自然联结进行限制，上例中将输出的行限 制为部门标识为20或50的行。6 - 11通过USING子句创建联结如果几个列具有相同的名称，但是数据
7、类型不匹配，可以使 用USING子句来修改NATURAL JOIN子句来指定用于等值 联结的列. 在多个列匹配时，使用USING子句只能匹配一个列. NATURAL JOIN 和 USING 子句是互不相容的.6 - 12用于联结的列名EMPLOYEES DEPARTMENTS 外键主键6 - 13SELECT employee_id, last_name, location_id, department_id FROM employees JOIN departments USING (department_id) ;使用 USING 子句检索记录6 - 14SELECT l.city, d
8、.department_name FROM locations l JOIN departments d USING (location_id) WHERE d.location_id = 1400;使用USING子句时表别名限制 不能使用WHERE,having对USING子句中的列做限制. 如果USING子句中的列，在其它地方会被用到，不能给表起 别名。SELECT d.department_id,d.location_id,l.city, CONCAT(d.location_id,l.city) FROM departments d JOIN locations l USING (loc
9、ation_id);6 - 15通过ON子句创建联结自然联结是联结条件基本上是具有相同名称的所有列的等值 联结. 要指定任意条件或指定要联结的列，使用ON子句. 联结条件与其它搜索条件分开. ON子句使代码更容易理解.6 - 16SELECT 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子句检索记录6 - 17SELECT empl
10、oyee_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;通过ON子句创建三向联结6 - 18SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e JOIN departments d ON (e.dep
11、artment_id = d.department_id) AND e.manager_id = 149 ;在联结中加入附加条件使用AND子句或WHERE子句增加附加条件: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) WHERE e.manager_id = 149 ;Or6 - 19自然联结总结Natural Join、Usi
12、ng和On关键字创建的联结都叫自然联结。 使用Natural Join联结时，前提条件是源表和目标表有相同名称 且数据类型一致的列，这种联结叫纯自然联结。 当源表和目标表，存在多个相同名称的列时，我们仅希望使用其中 某一个或几个，并非使用全部相同列进行联结时，使用Using进行 限制。Natural和Using是互斥的存在。当源表和目标表，并不存在名称相同的列，但列的实际数据有等值 或者不等值关系时，使用On显式的进行关系陈述。这种形式，也是 最通用，最节省性能的方式。6 - 20自联结MANAGER_ID in the WORKER table is equal to EMPLOYEE_ID
13、 in the MANAGER table.EMPLOYEES (WORKER)EMPLOYEES (MANAGER)6 - 21自联结若要查找EMPLOYEES表中Jones员工的经理的名字应该怎么做？ 使用我们现在已经掌握的知识，可能会分以下几步 1.通过Jones名字，查找该员工的manager_id，得到该员工的经理 的id是123 select manager_id from employees where upper(last_name)=JONES; 2.通过123这个员工代码，查找员工代码为123的员工姓名为 Vollman。 select last_name from emp
14、loyees where employee_id=123;在这一过程中，我们需要对employees表搜索两次。6 - 22将表联结到自身SELECT worker.last_name emp, manager.last_name mgr FROM employees worker JOIN employees manager ON (worker.manager_id = manager.employee_id AND UPPER(worker.last_name) = JONES);EMPLOYEES表联结到自身，类似于两张表相联结的情况，可以通过给 EMPLOYEES起不同的别名，来模拟
15、成两张表 上例中，我们给EMPLOYEES表分别起了两个别名：worker和 manager 在FROM子句中，和前面讲的两张相联结的情况相同，同时，也可以使 用where和and子句增加附加条件。6 - 23自联结等效传统语法的写法SELECT worker.last_name emp, manager.last_name mgr FROM employees worker JOIN employees manager ON (worker.manager_id = manager.employee_id AND UPPER(worker.last_name) = JONES);SELECT
16、 worker.last_name emp, manager.last_name mgr FROM employees worker,employees manager WHERE worker.manager_id = manager.employee_id AND UPPER(worker.last_name) = JONES;6 - 24非等值联结EMPLOYEESJOB_GRADESEMPLOYEES表中的工资必须在 JOB_GRADES表中的最低工资和最高工资 之间。6 - 25SELECT a.last_name,a.salary,b.grade_level FROM employees a JOIN job_grades b ON a.salary BETWEEN b.lowes
