java文件sql-oracle官方課件

1、多表查詢目標(biāo)通過本章學(xué)習(xí)，您將可以:使用等值和不等值連接在SELECT 語句中查詢多個表中的數(shù)據(jù)。使用外連接查詢不滿足連接條件的數(shù)據(jù)。使用自連接。從多個表中獲取數(shù)據(jù)EMPLOYEES DEPARTMENTS 笛卡爾集笛卡爾集會在下面條件下產(chǎn)生:省略連接條件連接條件無效所有表中的所有行互相連接為了避免笛卡爾集， 可以在 WHERE 加入有效的連接條件。笛卡爾集Cartesianproduct: 20 x8=160 rowsEMPLOYEES (20 rows)DEPARTMENTS (8 rows)EquijoinNon-equijoinOuter joinSelf join連接的類型Cross

2、 joinsNatural joinsUsing clauseFull or two sided outer joinsArbitrary join conditions for outer joins適用于SQL: 1999的連接:Oracle 提供的連接 (8i 或更早): Oracle 連接使用連接在多個表中查詢數(shù)據(jù)。在 WHERE 字句中寫入連接條件。在表中有相同列時，在列名之前加上表名前綴。SELECTtable1.column, table2.columnFROMtable1, table2WHEREtable1.column1 = table2.column2;等值連接EMPLO

3、YEES DEPARTMENTS Foreign keyPrimary key SELECT employees.employee_id, employees.last_name, employees.department_id, departments.department_id, departments.location_idFROM employees, departmentsWHERE employees.department_id = departments.department_id;等值連接多個連接條件與 AND 操作符 EMPLOYEES DEPARTMENTS 區(qū)分重復(fù)的列名

4、使用表名前綴在多個表中區(qū)分相同的列。使用表名可以提高效率。在不同表中具有相同列名的列可以用別名加以區(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í)行效率。連接多個表EMPLOYEES LOCATIONS DEPARTMENTS 連接 n個表,至少需要 n-1個連接條件。 例如：連接

5、三個表，至少需要兩個連接條件。非等值連接EMPLOYEESJOB_GRADESSalary in the EMPLOYEES table must be between lowest salary and highest salary in the JOB_GRADEStable.非等值連接SELECT e.last_name, e.salary, j.grade_levelFROM employees e, job_grades jWHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;外連接EMPLOYEESDEPARTMENTSTher

6、e are no employees in department 190. 外連接語法使用外連接可以查詢不滿足連接條件的數(shù)據(jù)。外連接的符號是 (+)。SELECTtable1.column, table2.columnFROMtable1, table2WHEREtable1.column(+) = table2.column;SELECTtable1.column, table2.columnFROMtable1, table2WHEREtable1.column = table2.column(+);SELECT e.last_name, e.department_id, d.depart

7、ment_nameFROM employees e, departments dWHERE e.department_id(+) = d.department_id ; 外連接自連接EMPLOYEES (WORKER)EMPLOYEES (MANAGER)MANAGER_ID in the WORKER table is equal to EMPLOYEE_ID in the MANAGER table.自連接SELECT worker.last_name | works for | manager.last_nameFROM employees worker, employees manag

8、erWHERE worker.manager_id = manager.employee_id ;Practice 4, Part One: OverviewThis practice covers writing queries to join tables together using Oracle syntax.使用SQL: 1999 語法連接使用連接從多個表中查詢數(shù)據(jù)：SELECTtable1.column, table2.columnFROMtable1CROSS JOIN table2 |NATURAL JOIN table2 |JOIN table2 USING (column_

9、name) |JOIN table2 ON(table1.column_name = table2.column_name) |LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.column_name = table2.column_name);叉集使用CROSS JOIN 子句使連接的表產(chǎn)生叉集。叉集和笛卡爾集是相同的。 SELECT last_name, department_nameFROM employeesCROSS JOIN departments ;自然連接NATURAL JOIN 子句，會以兩個表中具有相同名字的列為條件創(chuàng)建等值連接。在表

10、中查詢滿足等值條件的數(shù)據(jù)。如果只是列名相同而數(shù)據(jù)類型不同，則會產(chǎn)生錯誤。 SELECT department_id, department_name, location_id, cityFROM departmentsNATURAL JOIN locations ;自然連接使用 USING 子句創(chuàng)建連接在NATURAL JOIN 子句創(chuàng)建等值連接時，可以使用 USING 子句指定等值連接中需要用到的列。使用 USING 可以在有多個列滿足條件時進(jìn)行選擇。不要給選中的列中加上表名前綴或別名。NATURAL JOIN 和 USING 子句經(jīng)常同時使用。 SELECT e.employee_id,

11、e.last_name, d.location_idFROM employees e JOIN departments dUSING (department_id) ;USING 子句使用ON 子句創(chuàng)建連接自然連接中是以具有相同名字的列為連接條件的?？梢允褂?ON 子句指定額外的連接條件。這個連接條件是與其它條件分開的。ON 子句使語句具有更高的易讀性。 SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_idFROM employees e JOIN departments dON (

12、e.department_id = d.department_id);ON 子句 使用ON 子句創(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;內(nèi)連接 與 外連接在SQL: 1999中，內(nèi)連接只返回滿足連接條件的數(shù)據(jù)。A join between two tables that returns the resu

13、lts of the inner join as well as unmatched rows left (or right) tables is a left (or right) outer join.A join between two tables that returns the results of an inner join as well as the results of a left and right join is a full outer join. SELECT e.last_name, e.department_id, d.department_nameFROM

14、employees eLEFT OUTER JOIN departments dON (e.department_id = d.department_id) ;LEFT OUTER JOIN SELECT e.last_name, e.department_id, d.department_nameFROM employees eRIGHT OUTER JOIN departments dON (e.department_id = d.department_id) ;RIGHT OUTER JOIN SELECT e.last_name, e.department_id, d.departme

15、nt_nameFROM employees eFULL OUTER JOIN departments dON (e.department_id = d.department_id) ;FULL OUTER JOIN 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)AND e.manager_id = 149 ;Additional ConditionsSummaryIn this lesson, you should have learned how to usejoins to display data from multiple tables in:Orac