oracle多表查詢課堂PPT

1、4多表查詢多表查詢目標(biāo)目標(biāo)通過本章學(xué)習(xí)，您將可以通過本章學(xué)習(xí)，您將可以:使用等值和不等值連接在使用等值和不等值連接在select 語句中查詢多個(gè)表中語句中查詢多個(gè)表中的數(shù)據(jù)。的數(shù)據(jù)。使用外連接查詢不滿足連接條件的數(shù)據(jù)。使用外連接查詢不滿足連接條件的數(shù)據(jù)。使用自連接。使用自連接。從多個(gè)表中獲取數(shù)據(jù)從多個(gè)表中獲取數(shù)據(jù)employees departments 笛卡爾集笛卡爾集笛卡爾集會在下面條件下產(chǎn)生笛卡爾集會在下面條件下產(chǎn)生:省略連接條件省略連接條件連接條件無效連接條件無效所有表中的所有行互相連接所有表中的所有行互相連接為了避免笛卡爾集，為了避免笛卡爾集， 可以在可以在 where 加入有效的連

2、接條加入有效的連接條件。件。笛卡爾集笛卡爾集笛卡爾集笛卡爾集: 20 x8=160行行employees (20行行)departments (8行行)equijoinnon-equijoinouter joinself join連接的類型連接的類型cross joinsnatural joinsusing clausefull or two sided outer joinsarbitrary join conditions for outer joins適用于適用于sql: 1999的連接的連接:oracle 提供的連接提供的連接 (8i 或或更早更早): oracle 連接連接使用連接在

3、多個(gè)表中查詢數(shù)據(jù)。使用連接在多個(gè)表中查詢數(shù)據(jù)。在在 where 字句中寫入連接條件。字句中寫入連接條件。在表中有相同列時(shí)，在列名之前加上表名前綴。在表中有相同列時(shí)，在列名之前加上表名前綴。selecttable1.column, table2.columnfromtable1, table2wheretable1.column1 = table2.column2;等值連接等值連接employees departments 外鍵外鍵主鍵主鍵 select employees.employee_id, employees.last_name, employees.department_id, de

4、partments.department_id, departments.location_idfrom employees, departmentswhere employees.department_id = departments.department_id;等值連接等值連接多個(gè)連接條件與多個(gè)連接條件與 and 操作符操作符 employees departments 區(qū)分重復(fù)的列名區(qū)分重復(fù)的列名使用表名前綴在多個(gè)表中區(qū)分相同的列。使用表名前綴在多個(gè)表中區(qū)分相同的列。使用表名可以提高效率。使用表名可以提高效率。在不同表中具有相同列名的列可以用別名加以區(qū)分。在不同表中具有相同列名的列可以用

5、別名加以區(qū)分。select e.employee_id, e.last_name, e.department_id, d.department_id, d.location_idfrom employees e , departments dwhere e.department_id = d.department_id;表的別名表的別名使用別名可以簡化查詢。使用別名可以簡化查詢。使用表名前綴可以提高執(zhí)行效率。使用表名前綴可以提高執(zhí)行效率。連接多個(gè)表連接多個(gè)表employees locations departments 連接連接 n個(gè)表個(gè)表,至少需要至少需要 n-1個(gè)連接條件。個(gè)連接條件。 例

6、如：連接三個(gè)例如：連接三個(gè)表，至少需要兩個(gè)連接條件。表，至少需要兩個(gè)連接條件。非等值連接非等值連接employeesjob_gradesemployees表中的列工資表中的列工資應(yīng)在應(yīng)在job_grades表中的最高表中的最高工資與最低工資之間工資與最低工資之間非等值連接非等值連接select e.last_name, e.salary, j.grade_levelfrom employees e, job_grades jwhere e.salary between j.lowest_sal and j.highest_sal;外連接外連接employeesdepartments190號部門

7、沒有員工號部門沒有員工 外連接語法外連接語法使用外連接可以查詢不滿足連接條件的數(shù)據(jù)。使用外連接可以查詢不滿足連接條件的數(shù)據(jù)。外連接的符號是外連接的符號是 (+)。select table1.column, table2.columnfromtable1, table2wheretable1.column(+) = table2.column;select table1.column, table2.columnfromtable1, table2wheretable1.column = table2.column(+);select e.last_name, e.department_id,

8、d.department_namefrom employees e, departments dwhere e.department_id(+) = d.department_id ; 外連接外連接自連接自連接employees (worker)employees (manager)worker 表中的表中的manager_id 和和 manager 表中的表中的manager_id相等相等自連接自連接select worker.last_name | works for | manager.last_namefrom employees worker, employees managerwh

9、ere worker.manager_id = manager.employee_id ;使用使用sql: 1999 語法連接語法連接使用連接從多個(gè)表中查詢數(shù)據(jù)：使用連接從多個(gè)表中查詢數(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 tabl

10、e2 on (table1.column_name = table2.column_name);叉集叉集使用使用cross join 子句使連接的表產(chǎn)生叉集。子句使連接的表產(chǎn)生叉集。叉集和笛卡爾集是相同的。叉集和笛卡爾集是相同的。 select last_name, department_namefrom employeescross join departments ;自然連接自然連接natural join 子句，會以兩個(gè)表中具有相同名字的子句，會以兩個(gè)表中具有相同名字的列為條件創(chuàng)建等值連接。列為條件創(chuàng)建等值連接。在表中查詢滿足等值條件的數(shù)據(jù)。在表中查詢滿足等值條件的數(shù)據(jù)。如果只是列名相同

11、而數(shù)據(jù)類型不同，則會產(chǎn)生錯(cuò)誤。如果只是列名相同而數(shù)據(jù)類型不同，則會產(chǎn)生錯(cuò)誤。 select department_id, department_name, location_id, cityfrom departmentsnatural join locations ;自然連接自然連接使用使用 using 子句創(chuàng)建連接子句創(chuàng)建連接在在natural join 子句創(chuàng)建等值連接時(shí)，可以使用子句創(chuàng)建等值連接時(shí)，可以使用 using 子句指定等值連接中需要用到的列。子句指定等值連接中需要用到的列。使用使用 using 可以在有多個(gè)列滿足條件時(shí)進(jìn)行選擇?？梢栽谟卸鄠€(gè)列滿足條件時(shí)進(jìn)行選擇。不要給選中的列

12、中加上表名前綴或別名。不要給選中的列中加上表名前綴或別名。natural join 和和 using 子句經(jīng)常同時(shí)使用。子句經(jīng)常同時(shí)使用。 select e.employee_id, e.last_name, d.location_idfrom employees e join departments dusing (department_id) ;using 子句子句使用使用on 子句創(chuàng)建連接子句創(chuàng)建連接自然連接中是以具有相同名字的列為連接條件的。自然連接中是以具有相同名字的列為連接條件的。可以使用可以使用 on 子句指定額外的連接條件。子句指定額外的連接條件。這個(gè)連接條件是與其它條件分開的

13、。這個(gè)連接條件是與其它條件分開的。on 子句使語句具有更高的易讀性。子句使語句具有更高的易讀性。 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 子句子句 使用使用on 子句創(chuàng)建多表連接子句創(chuàng)建多表連接select employee_id, city, department_namefrom employees e join d

14、epartments don d.department_id = e.department_id join locations lon d.location_id = l.location_id;內(nèi)連接內(nèi)連接 與與 外外連接連接在在sql: 1999中，內(nèi)連接只返回滿足連接條件的數(shù)據(jù)。中，內(nèi)連接只返回滿足連接條件的數(shù)據(jù)。兩個(gè)表在連接過程中除了返回滿足連接條件的行以外還兩個(gè)表在連接過程中除了返回滿足連接條件的行以外還返回左（或右）表中不滿足條件的行返回左（或右）表中不滿足條件的行 ，這種連接稱為，這種連接稱為左（或右）左（或右） 外聯(lián)接。外聯(lián)接。兩個(gè)表在連接過程中除了返回滿足連接條件的行以外還兩

15、個(gè)表在連接過程中除了返回滿足連接條件的行以外還返回兩個(gè)表中不滿足條件的行返回兩個(gè)表中不滿足條件的行 ，這種連接稱為滿，這種連接稱為滿 外聯(lián)外聯(lián)接。接。 select e.last_name, e.department_id, d.department_namefrom employees eleft outer join departments don (e.department_id = d.department_id) ;左外聯(lián)接左外聯(lián)接 select e.last_name, e.department_id, d.department_namefrom employees eright outer join departments don (e.department_id = d.department_id) ;右外聯(lián)接右外聯(lián)接 select e.last_name, e.department_id, d.department_namefrom employees efull outer join departme