復雜查詢-實現(xiàn)多表數(shù)據(jù)的訪問課件

1、4Copyright Oracle Corporation, 2001. All rights reserved.從多表中顯示數(shù)據(jù)從多表中顯示數(shù)據(jù)4-2Copyright Oracle Corporation, 2001. All rights reserved.目標目標完成本課后, 您應當能夠執(zhí)行下列操作： 寫SELECT 語句使用等值和非等值連接從多個表中訪問數(shù)據(jù) 使用外連接查看不滿足連接條件的數(shù)據(jù) 使用一個自連接，連接一個表到它自己4-3Copyright Oracle Corporation, 2001. All rights reserved.大綱大綱 笛卡爾積 連接的類型 ORAC

2、LE的所有連接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.從多表中獲得數(shù)據(jù)從多表中獲得數(shù)據(jù)EMPLOYEES DEPARTMENTS 4-5Copyright Oracle Corpor

3、ation, 2001. All rights reserved.笛卡爾乘積笛卡爾乘積 笛卡爾乘積的形成，當： 一個連接條件被遺漏時 一個連接條件不正確時 在第一個表中的所有行被連接到第二個表的所有行時 為了避免笛卡爾乘積的形成，在WHERE 子句中應當總是包含正確的連接條件4-6Copyright Oracle Corporation, 2001. All rights reserved.笛卡爾乘積的產(chǎn)生笛卡爾乘積的產(chǎn)生笛卡爾乘積20 x8=160 rowsEMPLOYEES (20 rows)DEPARTMENTS (8 rows)4-7Copyright Oracle Corporati

4、on, 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-

5、equijoin 非等值Outer join 外連接 Self join 自連接連接的類型連接的類型Cross joins 交叉連接Natural joins 自然連接Using clause 使用子句Full or two sided outerjoins 全連接或雙向外連接Arbitrary join conditions forouter joins 對于外連接的任意連接條件SQL: 1999 適應連接適應連接:Oracle所有的連接所有的連接(8i 以前以前):4-9Copyright Oracle Corporation, 2001. All rights reserved.用用Or

6、acle 語法連接表語法連接表使用一個連接從多個表中查詢數(shù)據(jù)在WHERE 子句中寫連接條件.當多個表中有相同的列名時，將表名作為列名的前綴SELECTtable1.column, table2.columnFROMtable1, table2WHEREtable1.column1 = table2.column2;4-10Copyright Oracle Corporation, 2001. All rights reserved.大綱大綱笛卡爾積 連接的類型 ORACLE的所有連接Equijoin 等值Non-equijoin 非等值Outer join 外連接 Self join 自連接S

7、QL：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 key4-12Copyright Oracle Corporation, 2001. All rights reserved. SELECT

8、 employees.employee_id, employees.last_name, employees.department_id, departments.department_id, departments.location_idFROM employees, departmentsWHERE employees.department_id = departments.department_id;用等值連接返回記錄用等值連接返回記錄4-13Copyright Oracle Corporation, 2001. All rights reserved.使用使用AND 操作符附加搜索條件

9、操作符附加搜索條件EMPLOYEES DEPARTMENTS 4-14Copyright Oracle Corporation, 2001. All rights reserved.限制不明確的列名限制不明確的列名 在多表中使用表前綴限制修飾列名 用表前綴改善性能 用列別名區(qū)別有相同名稱，但在不同表中的列4-15Copyright Oracle Corporation, 2001. All rights reserved.SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_idFROM

10、employees e , departments dWHERE 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

11、 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在EMPL

12、OYEES 表中的工資必須在JOB_GRADES 表中的最低工資和最高工資之間4-19Copyright Oracle Corporation, 2001. All rights reserved.用非等值連接返回記錄用非等值連接返回記錄SELECT e.last_name, e.salary, j.grade_levelFROM employees e, job_grades jWHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;4-20Copyright Oracle Corporation, 2001. All rights res

13、erved.大綱大綱笛卡爾積 連接的類型 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-

14、22Copyright Oracle Corporation, 2001. All rights reserved.外連接語法外連接語法 你可以用一個外連接查看那些不滿足連接條件的行 外連接運算符是加號(+)SELECT table1.column, table2.columnFROMtable1, table2WHEREtable1.column(+) = table2.column;SELECT table1.column, table2.columnFROMtable1, table2WHEREtable1.column = table2.column(+);4-23Copyright

15、Oracle Corporation, 2001. All rights reserved.SELECT e.last_name, e.department_id, d.department_nameFROM employees e, departments dWHERE e.department_id(+) = d.department_id ; 使用外連接使用外連接4-24Copyright Oracle Corporation, 2001. All rights reserved.大綱大綱笛卡爾積 連接的類型 ORACLE的所有連接Equijoin 等值Non-equijoin 非等值O

16、uter 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_ID4-26Copyri

17、ght Oracle Corporation, 2001. All rights reserved.連接一個表到它本身連接一個表到它本身SELECT worker.last_name | works for | manager.last_nameFROM employees worker, employees managerWHERE worker.manager_id = manager.employee_id ;4-27Copyright Oracle Corporation, 2001. All rights reserved.練習練習4, 第一部分第一部分: 概覽概覽這部分練習包括用O

18、racle 語法寫將表連接在一起的查詢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 Corp

19、oration, 2001. All rights reserved.用用SQL 連接表連接表: 1999 語法語法用一個連接從多個表中查詢數(shù)據(jù)SELECTtable1.column, table2.columnFROMtable1CROSS 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_na

20、me = 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 Oracl

21、e Corporation, 2001. All rights reserved.創(chuàng)建交叉連接創(chuàng)建交叉連接 CROSS JOIN 子句導致兩個表的交叉乘積 該連接和兩個表之間的笛卡爾乘積是一樣的 SELECT last_name, department_nameFROM employeesCROSS JOIN departments ;4-32Copyright Oracle Corporation, 2001. All rights reserved.大綱大綱笛卡爾積 連接的類型 ORACLE的所有連接Equijoin 等值Non-equijoin 非等值Outer join 外連接 Sel

22、f join 自連接SQL：1999適應性連接Cross joins 交叉連接Natural joins 自然連接Using clause 使用子句Full or two sided outer joins 全連接或雙向外連接對于外連接的任意連接條件4-33Copyright Oracle Corporation, 2001. All rights reserved.創(chuàng)建自然連接創(chuàng)建自然連接 NATURAL JOIN 子句基于兩個表之間有相同名字的所有列 它從兩個表中選擇在所有的匹配列中有相等值的行 如果有相同名字的列的數(shù)據(jù)類型不同，返回一個錯誤4-34Copyright Oracle Cor

23、poration, 2001. All rights reserved. SELECT department_id, department_name, location_id, cityFROM departmentsNATURAL JOIN locations ;用自然連接返回記錄用自然連接返回記錄4-35Copyright Oracle Corporation, 2001. All rights reserved.大綱大綱笛卡爾積 連接的類型 ORACLE的所有連接Equijoin 等值Non-equijoin 非等值Outer join 外連接 Self join 自連接SQL：1999

24、適應性連接Cross joins 交叉連接Natural joins 自然連接Using clause 使用子句Full or two sided outer joins 全連接或雙向外連接對于外連接的任意連接條件4-36Copyright Oracle Corporation, 2001. All rights reserved.用用USING 子句創(chuàng)建連接子句創(chuàng)建連接 如果一些列有相同的名字，但數(shù)據(jù)類型不匹配，NATURAL JOIN 子句能夠用USING 子句修改以指定將被用于一個等值連接的列 當有多個列匹配時，用USING 子句匹配唯一的列 在引用列不要使用表名或者別名 NATURAL

25、 JOIN 和USING 子句是相互排斥的4-37Copyright Oracle Corporation, 2001. All rights reserved. SELECT e.employee_id, e.last_name, d.location_idFROM employees e JOIN departments dUSING (department_id) ;用用USING 子句返回記錄子句返回記錄4-38Copyright Oracle Corporation, 2001. All rights reserved.用用ON 子句創(chuàng)建連接子句創(chuàng)建連接 對于自然連接的連接條件，基本

26、上是帶有相同名字的所有列的等值連接 為了指定任意條件，或者指定要連接的列，可以使用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_idFROM employees e JOIN departments dON (e.department_id = d.department_id);用用ON 子句返

27、回記錄子句返回記錄4-40Copyright Oracle Corporation, 2001. All rights reserved. 用用ON 子句創(chuàng)建三向連接子句創(chuàng)建三向連接SELECT employee_id, city, department_nameFROM employees e JOIN departments dON d.department_id = e.department_id JOIN locations lON d.location_id = l.location_id;4-41Copyright Oracle Corporation, 2001. All rig

28、hts 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.內(nèi)與外連接內(nèi)與外連接 在SQL: 1999 中，連接兩個表，僅

29、返回匹配的行的連接，稱為內(nèi)連接 在兩個表之間的連接，返回內(nèi)連接的結(jié)果，同時還返回不匹配行的左（或右）表的連接，稱為左（或右）連接 在兩個表之間的連接，返回內(nèi)連接的結(jié)果， 同時還返回左和右連接，稱為全連接4-43Copyright Oracle Corporation, 2001. All rights reserved. SELECT e.last_name, e.department_id, d.department_nameFROM employees eLEFT OUTER JOIN departments dON (e.department_id = d.department_id) ;左外連接左外連接4-44Copyright Oracle Corporation, 2001. All rights reserved. SELECT e.last_name,