Oracle-DML-高級查詢課件

Java程序員Oracle就業(yè)課程Lesson6高級查詢主講：楊昊Q：952063095

Java程序員Oracle就業(yè)課程Lesson6高級查詢1掌握集合運算掌握子查詢掌握子查詢所能夠解決的各類問題掌握多表連接查詢使用自連接讓一個表連接它本身掌握子查詢解決程序中的各種業(yè)務(wù)邏輯目標(biāo)掌握集合運算目標(biāo)I、集合運算I、集合運算3集合運算符ABAAABBBUNION/UNIONALLINTERSECTMINUS集合運算符ABAAABBBUNION/UNIONALLINEMPLOYEES:提供關(guān)于當(dāng)前所有雇員的詳細(xì)信息JOB_HISTORY:當(dāng)一個雇員轉(zhuǎn)換工作的時候，記錄前面工作開始和結(jié)束的日期，以及工作的標(biāo)識號和部門本課中使用的表本課中使用的表：EMPLOYEES:提供關(guān)于當(dāng)前所有雇員的詳細(xì)信息本課中使用UNION運算符UNION運算符從兩個查詢中返回消除重復(fù)之后的結(jié)果ABUNION運算符UNION運算符從兩個查詢中返回消除重復(fù)之后UNION運算符的使用顯示當(dāng)前和先前的所有雇員的的工作細(xì)節(jié)。每個雇員只顯示一次UNION運算符的使用顯示當(dāng)前和先前的所有雇員的的工作細(xì)節(jié)。UNIONALL運算符ABUNIONALL返回兩個查詢的結(jié)果，其中包括重復(fù)的UNIONALL運算符ABUNIONALL返回兩個查詢的UNIONALL運算符的使用顯示當(dāng)前和先前的所有雇員的部門UNIONALL運算符的使用顯示當(dāng)前和先前的所有雇員的部門INTERSECT運算符ABINTERSECT運算符ABINTERSECT運算符的使用顯示在開始公司的任期之前，擁有工作資格的雇員的IDs和工作IDsINTERSECT運算符的使用顯示在開始公司的任期之前，擁有MINUS運算符ABMINUS運算符ABMINUS運算符顯示那些一次都沒有轉(zhuǎn)換工作的雇員IDsMINUS運算符顯示那些一次都沒有轉(zhuǎn)換工作的雇員IDsII、子查詢II、子查詢14使用子查詢解決問題誰的工資比Abel高？主查詢哪個雇員的工資比Abel高？Abel的工資是多少？子查詢？使用子查詢解決問題誰的工資比Abel高？主查詢哪個雇員的工資子查詢的語法子查詢（內(nèi)部查詢）在主查詢之前執(zhí)行一次子查詢的結(jié)果被用于主查詢（外部查詢）子查詢的語法子查詢（內(nèi)部查詢）在主查詢之前執(zhí)行一次使用子查詢使用子查詢單行子查詢只返回一行結(jié)果使用單行比較運算符單行子查詢只返回一行結(jié)果執(zhí)行單行子查詢執(zhí)行單行子查詢在子查詢中使用組函數(shù)在子查詢中使用組函數(shù)在子查詢中使用HAVING子句Oracle首先執(zhí)行子查詢Oracle返回結(jié)果到主查詢的HAVING子句在子查詢中使用HAVING子句Oracle首先執(zhí)行子查詢這個語句的錯誤是什么？這個語句的錯誤是什么？這個語句將返回行嗎？這個語句將返回行嗎？運算符含義IN與列表中的任一成員相等ANY與子查詢返回的每一個值比較ALL與子查詢返回的所有值比較多行子查詢返回多行使用多行比較運算符運算符含義IN與列表中的任一成員相等ANY與子查詢返回的每一在多行子查詢中使用ANY運算符在多行子查詢中使用ANY運算符在多行子查詢中使用ALL運算符在多行子查詢中使用ALL運算符子查詢中的空值子查詢中的空值相關(guān)子查詢子查詢參考父查詢表中的一列相關(guān)子查詢子查詢參考父查詢表中的一列使用相關(guān)子查詢找到那些收入超過他們部門平均薪酬的所有雇員每次從外部查詢來的行被處理的時候，內(nèi)部查詢就被求值使用相關(guān)子查詢找到那些收入超過他們部門平均薪酬的所有雇員每次使用相關(guān)子查詢顯示那些至少已經(jīng)更換了兩次工作的雇員使用相關(guān)子查詢顯示那些至少已經(jīng)更換了兩次工作的雇員EXISTS運算符檢測子查詢的結(jié)果集中行的存在如果一個子查詢的行值被找到：—內(nèi)部查詢中的搜索將不會繼續(xù)—條件將會標(biāo)記為TRUE如果一個子查詢的行值沒有被找到：—條件將會標(biāo)記為FALSE

—內(nèi)部查詢中的搜索將會繼續(xù)使用EXISTS運算符使用EXISTS運算符使用EXISTS運算符找到那些至少有一個人向他們報告的雇員使用EXISTS運算符找到那些至少有一個人向他們報告的雇員使用NOTEXISTS運算符找到?jīng)]有任何職員的部門使用NOTEXISTS運算符找到?jīng)]有任何職員的部門III、多表連接III、多表連接34從多張表中獲取數(shù)據(jù)EMPLOYEESDEPARTMENTS從多張表中獲取數(shù)據(jù)EMPLOYEESDEPARTMENTS笛卡爾連接等值連接非等值連接外部連接自連接連接類型連接類型產(chǎn)生一個笛卡連接EMPLOYEES(20行)DEPARTMENTS(8行)笛卡爾積：20x8=160行產(chǎn)生一個笛卡連接EMPLOYEES(20行)DEPARTM笛卡爾積在下列情況下產(chǎn)生：—忽略了一個連接條件—一個連接條件失效—第一張表的所有行和第二張表的所有行連接為了避免笛卡爾積的產(chǎn)生，通常包含一個有效連接條件的WHERE子句。笛卡爾積笛卡爾積使用Oracle語法連接表使用一個連接去查詢多張表的數(shù)據(jù)在WHERE子句中設(shè)定連接條件當(dāng)多張表的列重名時，以表名作為列名的前綴。使用Oracle語法連接表使用一個連接去查詢多張表的數(shù)據(jù)在W等值連接外鍵主鍵EMPLOYEESDEPARTMENTS等值連接外鍵主鍵EMPLOYEESDEPARTMENTS使用等值連接檢索記錄使用等值連接檢索記錄用表前綴在多張表中限定列名用表前綴提高系統(tǒng)性能用列別名區(qū)分不同表中同名的列限定使用二義性列名限定使用二義性列名使用表別名用表別名簡化查詢用表名前綴提高性能使用表別名用表別名簡化查詢兩張表以上的連接EMPLOYEESDEPARTMENTSLOCATIONS連接n張表至少需要n-1個連接條件。例如：連接3張表至少要2個連接條件兩張表以上的連接EMPLOYEESDEPARTMENTSLO非等值連接EMPLOYEESJOB_GRADESEMPLOYEES表中的工資應(yīng)介于JOB_GRADES表的最低和最高工資之間非等值連接EMPLOYEESJOB_GRADESEMPLOY用非等值連接檢索記錄用非等值連接檢索記錄外部連接在ID為190的部門中沒有任何雇員外部連接在ID為190的部門中沒有任何雇員外部連接語法利用一個外部連接查詢那些無法滿足連接條件的行外部連接運算符是一個加號（+）外部連接語法利用一個外部連接查詢那些無法滿足連接條件的行wheree.dept_id(+)=d.id;+號在左邊,已右邊為準(zhǔn),左邊補(bǔ)齊+號在右邊,已左邊為準(zhǔn),右邊補(bǔ)齊外部連接語法+號問題外部連接語法+號問題使用外部連接使用外部連接自連接EMPLOYEES(WORKER)EMPLOYEES(MANAGER)WORKER表中的MANAGER_ID等于MANAGER表中的EMPLOYEE_ID自連接EMPLOYEES(WORKER)EMPLOYEES表的自連接表的自連接使用SQL99語法介紹使用一個連接查詢多張表的數(shù)據(jù)使用SQL99語法介紹使用一個連接查詢多張表的數(shù)據(jù)使用SQL99語法介紹CrossjoinJoinOn()Leftouterjoinon()Rightouterjoin()Fullouterjoinon()使用SQL99語法介紹Crossjoin

CROSSJOIN產(chǎn)生了一個笛卡爾積（Cartesianproduct），就象是在連接兩個表格時忘記加入一個WHERE子句一樣

例：

SELECTlast_name,dept_idFROMemp,dept;

例：

SELECTename,locFROMempCROSSJOINdept;CROSSJOIN CROSSJOIN產(chǎn)生了一個笛卡爾積（Cartesia

ON子句被用于當(dāng)在兩個表中的字段名不一致時來連接表

例：

SELECTdepartment_name,city

FROMdepartment,location

WHEREdepartment.location_id=location.loc_id;

SELECTdepartment_name,city

FROMdepartmentdJOINlocationl

ON(d.location_id=l.id);ON子句

ON子句被用于當(dāng)在兩個表中的字段名不一致時來連接表

O 在LEFTOUTERJOIN中，會返回所有左邊表中的行，甚至在被連接的表中沒有可對應(yīng)的列名的情況下也如此。

例：

SELECTlast_name,dept_id

FROMempe,deptd

WHEREe.department_id=d.department_id(+);

SELECTlast_name,dept_id

FROMemp

LEFTOUTERJOINDeptONe.dept_id=d.dept_id;

LEFTOUTERJOIN 在LEFTOUTERJOIN中，會返回所有左邊表中 在RIGHTOUTERJOIN中返回的是表中所有右邊的行，甚至在被連接的表中沒有對應(yīng)的情況下也如此。例：

SELECTlast_name,d.dept_id

FROMemployeese,departmentsd

WHEREe.department_id(+)=d.department_id; SELECTlast_name,d.dept_id

FROMemployeese

RIGHTOUTERJOINdepartmentsd

ON(e.department_id=d.department_id);

RIGHTOUTERJOIN 在RIGHTOUTERJOIN中返回的是表中所有右邊的

FULLOUTERJOIN=LEFTOUTJOIN+RIGHTOUTJOIN例：

SELECTlast_name,d.dept_id

FROMemployeese，departmentsd

WHEREe.department_id=d.department_id（+）

UNIONALL SELECTlast_name,d.dept_id

FROMemployeese，departmentsd

WHEREe.department_id（+）=d.department_id

SELECTlast_name,d.dept_id

FROMemployeese

FULLOUTERJOINdepartmentsd

ON(e.department_id=d.department_id);

FULLOUTERJOIN FULLOUTERJOIN=LEFTOUTJOIN附加條件附加條件Java程序員Oracle就業(yè)課程Lesson6高級查詢主講：楊昊Q：952063095

Java程序員Oracle就業(yè)課程Lesson6高級查詢61掌握集合運算掌握子查詢掌握子查詢所能夠解決的各類問題掌握多表連接查詢使用自連接讓一個表連接它本身掌握子查詢解決程序中的各種業(yè)務(wù)邏輯目標(biāo)掌握集合運算目標(biāo)I、集合運算I、集合運算63集合運算符ABAAABBBUNION/UNIONALLINTERSECTMINUS集合運算符ABAAABBBUNION/UNIONALLINEMPLOYEES:提供關(guān)于當(dāng)前所有雇員的詳細(xì)信息JOB_HISTORY:當(dāng)一個雇員轉(zhuǎn)換工作的時候，記錄前面工作開始和結(jié)束的日期，以及工作的標(biāo)識號和部門本課中使用的表本課中使用的表：EMPLOYEES:提供關(guān)于當(dāng)前所有雇員的詳細(xì)信息本課中使用UNION運算符UNION運算符從兩個查詢中返回消除重復(fù)之后的結(jié)果ABUNION運算符UNION運算符從兩個查詢中返回消除重復(fù)之后UNION運算符的使用顯示當(dāng)前和先前的所有雇員的的工作細(xì)節(jié)。每個雇員只顯示一次UNION運算符的使用顯示當(dāng)前和先前的所有雇員的的工作細(xì)節(jié)。UNIONALL運算符ABUNIONALL返回兩個查詢的結(jié)果，其中包括重復(fù)的UNIONALL運算符ABUNIONALL返回兩個查詢的UNIONALL運算符的使用顯示當(dāng)前和先前的所有雇員的部門UNIONALL運算符的使用顯示當(dāng)前和先前的所有雇員的部門INTERSECT運算符ABINTERSECT運算符ABINTERSECT運算符的使用顯示在開始公司的任期之前，擁有工作資格的雇員的IDs和工作IDsINTERSECT運算符的使用顯示在開始公司的任期之前，擁有MINUS運算符ABMINUS運算符ABMINUS運算符顯示那些一次都沒有轉(zhuǎn)換工作的雇員IDsMINUS運算符顯示那些一次都沒有轉(zhuǎn)換工作的雇員IDsII、子查詢II、子查詢74使用子查詢解決問題誰的工資比Abel高？主查詢哪個雇員的工資比Abel高？Abel的工資是多少？子查詢？使用子查詢解決問題誰的工資比Abel高？主查詢哪個雇員的工資子查詢的語法子查詢（內(nèi)部查詢）在主查詢之前執(zhí)行一次子查詢的結(jié)果被用于主查詢（外部查詢）子查詢的語法子查詢（內(nèi)部查詢）在主查詢之前執(zhí)行一次使用子查詢使用子查詢單行子查詢只返回一行結(jié)果使用單行比較運算符單行子查詢只返回一行結(jié)果執(zhí)行單行子查詢執(zhí)行單行子查詢在子查詢中使用組函數(shù)在子查詢中使用組函數(shù)在子查詢中使用HAVING子句Oracle首先執(zhí)行子查詢Oracle返回結(jié)果到主查詢的HAVING子句在子查詢中使用HAVING子句Oracle首先執(zhí)行子查詢這個語句的錯誤是什么？這個語句的錯誤是什么？這個語句將返回行嗎？這個語句將返回行嗎？運算符含義IN與列表中的任一成員相等ANY與子查詢返回的每一個值比較ALL與子查詢返回的所有值比較多行子查詢返回多行使用多行比較運算符運算符含義IN與列表中的任一成員相等ANY與子查詢返回的每一在多行子查詢中使用ANY運算符在多行子查詢中使用ANY運算符在多行子查詢中使用ALL運算符在多行子查詢中使用ALL運算符子查詢中的空值子查詢中的空值相關(guān)子查詢子查詢參考父查詢表中的一列相關(guān)子查詢子查詢參考父查詢表中的一列使用相關(guān)子查詢找到那些收入超過他們部門平均薪酬的所有雇員每次從外部查詢來的行被處理的時候，內(nèi)部查詢就被求值使用相關(guān)子查詢找到那些收入超過他們部門平均薪酬的所有雇員每次使用相關(guān)子查詢顯示那些至少已經(jīng)更換了兩次工作的雇員使用相關(guān)子查詢顯示那些至少已經(jīng)更換了兩次工作的雇員EXISTS運算符檢測子查詢的結(jié)果集中行的存在如果一個子查詢的行值被找到：—內(nèi)部查詢中的搜索將不會繼續(xù)—條件將會標(biāo)記為TRUE如果一個子查詢的行值沒有被找到：—條件將會標(biāo)記為FALSE

—內(nèi)部查詢中的搜索將會繼續(xù)使用EXISTS運算符使用EXISTS運算符使用EXISTS運算符找到那些至少有一個人向他們報告的雇員使用EXISTS運算符找到那些至少有一個人向他們報告的雇員使用NOTEXISTS運算符找到?jīng)]有任何職員的部門使用NOTEXISTS運算符找到?jīng)]有任何職員的部門III、多表連接III、多表連接94從多張表中獲取數(shù)據(jù)EMPLOYEESDEPARTMENTS從多張表中獲取數(shù)據(jù)EMPLOYEESDEPARTMENTS笛卡爾連接等值連接非等值連接外部連接自連接連接類型連接類型產(chǎn)生一個笛卡連接EMPLOYEES(20行)DEPARTMENTS(8行)笛卡爾積：20x8=160行產(chǎn)生一個笛卡連接EMPLOYEES(20行)DEPARTM笛卡爾積在下列情況下產(chǎn)生：—忽略了一個連接條件—一個連接條件失效—第一張表的所有行和第二張表的所有行連接為了避免笛卡爾積的產(chǎn)生，通常包含一個有效連接條件的WHERE子句。笛卡爾積笛卡爾積使用Oracle語法連接表使用一個連接去查詢多張表的數(shù)據(jù)在WHERE子句中設(shè)定連接條件當(dāng)多張表的列重名時，以表名作為列名的前綴。使用Oracle語法連接表使用一個連接去查詢多張表的數(shù)據(jù)在W等值連接外鍵主鍵EMPLOYEESDEPARTMENTS等值連接外鍵主鍵EMPLOYEESDEPARTMENTS使用等值連接檢索記錄使用等值連接檢索記錄用表前綴在多張表中限定列名用表前綴提高系統(tǒng)性能用列別名區(qū)分不同表中同名的列限定使用二義性列名限定使用二義性列名使用表別名用表別名簡化查詢用表名前綴提高性能使用表別名用表別名簡化查詢兩張表以上的連接EMPLOYEESDEPARTMENTSLOCATIONS連接n張表至少需要n-1個連接條件。例如：連接3張表至少要2個連接條件兩張表以上的連接EMPLOYEESDEPARTMENTSLO非等值連接EMPLOYEESJOB_GRADESEMPLOYEES表中的工資應(yīng)介于JOB_GRADES表的最低和最高工資之間非等值連接EMPLOYEESJOB_GRADESEMPLOY用非等值連接檢索記錄用非等值連接檢索記錄外部連接在ID為190的部門中沒有任何雇員外部連接在ID為190的部門中沒有任何雇員外部連接語法利用一個外部連接查詢那些無法滿足連接條件的行外部連接運算符是一個加號（+）外部連接語法利用一個外部連接查詢那些無法滿足連接條件的行wheree.dept_id(+)=d.id;+號在左邊,已右邊為準(zhǔn),左邊補(bǔ)齊+號在右邊,已左邊為準(zhǔn),右邊補(bǔ)齊外部連接語法+號問題外部連接語法+號問題使用外部連接使用外部連接自連接EMPLOYEES(WORKER)EMPLOYEES(MANAGER)WORKER表中的MANAGER_ID等于MANAGER表中的EMPLOYEE_ID自連接EMPLOYEES(WORKER)EMPLOYEES表的自連接表的自連接使用SQL99語法介紹使用一個連接查詢多張表的數(shù)據(jù)使用SQL99語法介紹使用一個連接查詢多張表的數(shù)據(jù)使用SQL99語法介紹CrossjoinJoinOn()Leftouterjoinon()Rightouterjoin()Fullouterjoinon()使用SQL99語法介紹Crossjoin

CROSSJOIN產(chǎn)生了一個笛卡爾積（Cartesianproduct），就象是在連接兩個表格時忘記加入一個WHERE子句一樣

例：

SELECTlast_name,dept_idFROMemp,dept;

例：

SELECTename,locFROMempCROSSJOINdept;CROSSJOIN CROSSJOIN產(chǎn)生了一個笛卡爾積（Cartesia

ON子句被用于當(dāng)在兩個表中的字段名不一致時來連接表

例：

SELECTdepartment_name,city

FROMdepartment,location

WHEREdepartment.location_id=location.loc_id;

SELECTdepartment_name,city

FROMdepartmentdJOINlocationl

ON(d.location_id=l.id);ON子句

ON子句被用于當(dāng)在兩個表中的字段名不一致時來連接表

O 在LEFTOUTERJOIN中，會返回所有左邊表中的行，甚至在被連接的表中沒有可對應(yīng)的列名的情況下也如此。

例：

SELECTlast_name,dept_id

FROMempe,deptd

WHE