第6章 OracleDML高級(jí)查詢(xún)

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

掌握集合運(yùn)算掌握子查詢(xún)掌握子查詢(xún)所可以處理的各類(lèi)問(wèn)題掌握多表銜接查詢(xún)運(yùn)用自銜接讓一個(gè)表銜接它本身掌握子查詢(xún)處理程序中的各種業(yè)務(wù)邏輯目標(biāo)I、集合運(yùn)算集合運(yùn)算符ABAAABBBUNION/UNIONALLINTERSECTMINUSEMPLOYEES:提供關(guān)于當(dāng)前一切雇員的詳細(xì)信息JOB_HISTORY:當(dāng)一個(gè)雇員轉(zhuǎn)換任務(wù)的時(shí)候，記錄前面任務(wù)開(kāi)場(chǎng)和終了的日期，以及任務(wù)的標(biāo)識(shí)號(hào)和部門(mén)本課中運(yùn)用的表本課中運(yùn)用的表：UNION運(yùn)算符UNION運(yùn)算符從兩個(gè)查詢(xún)中前往消除反復(fù)之后的結(jié)果ABUNION運(yùn)算符的運(yùn)用顯示當(dāng)前和先前的一切雇員的的任務(wù)細(xì)節(jié)。每個(gè)雇員只顯示一次UNIONALL運(yùn)算符ABUNIONALL前往兩個(gè)查詢(xún)的結(jié)果，其中包括反復(fù)的UNIONALL運(yùn)算符的運(yùn)用顯示當(dāng)前和先前的一切雇員的部門(mén)INTERSECT運(yùn)算符ABINTERSECT運(yùn)算符的運(yùn)用顯示在開(kāi)場(chǎng)公司的任期之前，擁有任務(wù)資歷的雇員的IDs和任務(wù)IDsMINUS運(yùn)算符ABMINUS運(yùn)算符顯示那些一次都沒(méi)有轉(zhuǎn)換任務(wù)的雇員IDsII、子查詢(xún)運(yùn)用子查詢(xún)處理問(wèn)題誰(shuí)的工資比Abel高？主查詢(xún)哪個(gè)雇員的工資比Abel高？Abel的工資是多少？子查詢(xún)？子查詢(xún)的語(yǔ)法子查詢(xún)〔內(nèi)部查詢(xún)〕在主查詢(xún)之前執(zhí)行一次子查詢(xún)的結(jié)果被用于主查詢(xún)〔外部查詢(xún)〕運(yùn)用子查詢(xún)單行子查詢(xún)只前往一行結(jié)果運(yùn)用單行比較運(yùn)算符執(zhí)行單行子查詢(xún)?cè)谧硬樵?xún)中運(yùn)用組函數(shù)在子查詢(xún)中運(yùn)用HAVING子句Oracle首先執(zhí)行子查詢(xún)Oracle前往結(jié)果到主查詢(xún)的HAVING子句這個(gè)語(yǔ)句的錯(cuò)誤是什么？這個(gè)語(yǔ)句將前往行嗎？運(yùn)算符含義IN與列表中的任一成員相等ANY與子查詢(xún)返回的每一個(gè)值比較ALL與子查詢(xún)返回的所有值比較多行子查詢(xún)前往多行運(yùn)用多行比較運(yùn)算符在多行子查詢(xún)中運(yùn)用ANY運(yùn)算符在多行子查詢(xún)中運(yùn)用ALL運(yùn)算符子查詢(xún)中的空值相關(guān)子查詢(xún)子查詢(xún)參考父查詢(xún)表中的一列運(yùn)用相關(guān)子查詢(xún)找到那些收入超越他們部門(mén)平均薪酬的一切雇員每次從外部查詢(xún)來(lái)的行被處置的時(shí)候，內(nèi)部查詢(xún)就被求值運(yùn)用相關(guān)子查詢(xún)顯示那些至少曾經(jīng)改換了兩次任務(wù)的雇員EXISTS運(yùn)算符檢測(cè)子查詢(xún)的結(jié)果集中行的存在假設(shè)一個(gè)子查詢(xún)的行值被找到：—內(nèi)部查詢(xún)中的搜索將不會(huì)繼續(xù)—條件將會(huì)標(biāo)志為T(mén)RUE假設(shè)一個(gè)子查詢(xún)的行值沒(méi)有被找到：—條件將會(huì)標(biāo)志為FALSE—內(nèi)部查詢(xún)中的搜索將會(huì)繼續(xù)運(yùn)用EXISTS運(yùn)算符運(yùn)用EXISTS運(yùn)算符找到那些至少有一個(gè)人向他們報(bào)告的雇員運(yùn)用NOTEXISTS運(yùn)算符找到?jīng)]有任何職員的部門(mén)III、多表銜接從多張表中獲取數(shù)據(jù)EMPLOYEESDEPARTMENTS笛卡爾銜接等值銜接非等值銜接外部銜接自銜接銜接類(lèi)型產(chǎn)生一個(gè)笛卡銜接EMPLOYEES(20行)DEPARTMENTS(8行)笛卡爾積：20x8=160行笛卡爾積在以下情況下產(chǎn)生：—忽略了一個(gè)銜接條件—一個(gè)銜接條件失效—第一張表的一切行和第二張表的一切行銜接為了防止笛卡爾積的產(chǎn)生，通常包含一個(gè)有效銜接條件的WHERE子句。笛卡爾積運(yùn)用Oracle語(yǔ)法銜接表運(yùn)用一個(gè)銜接去查詢(xún)多張表的數(shù)據(jù)在WHERE子句中設(shè)定銜接條件當(dāng)多張表的列重名時(shí)，以表名作為列名的前綴。等值銜接外鍵主鍵EMPLOYEESDEPARTMENTS運(yùn)用等值銜接檢索記錄用表前綴在多張表中限定列名用表前綴提高系統(tǒng)性能用列別名區(qū)分不同表中同名的列限定運(yùn)用二義性列名運(yùn)用表別名用表別名簡(jiǎn)化查詢(xún)用表名前綴提高性能兩張表以上的銜接EMPLOYEESDEPARTMENTSLOCATIONS銜接n張表至少需求n-1個(gè)銜接條件。例如：銜接3張表至少要2個(gè)銜接條件非等值銜接EMPLOYEESJOB_GRADESEMPLOYEES表中的工資應(yīng)介于JOB_GRADES表的最低和最高工資之間用非等值銜接檢索記錄外部銜接在ID為190的部門(mén)中沒(méi)有任何雇員外部銜接語(yǔ)法利用一個(gè)外部銜接查詢(xún)那些無(wú)法滿(mǎn)足銜接條件的行外部銜接運(yùn)算符是一個(gè)加號(hào)〔+〕wheree.dept_id(+)=d.id;+號(hào)在左邊,已右邊為準(zhǔn),左邊補(bǔ)齊+號(hào)在右邊,已左邊為準(zhǔn),右邊補(bǔ)齊外部銜接語(yǔ)法+號(hào)問(wèn)題運(yùn)用外部銜接自銜接EMPLOYEES(WORKER)EMPLOYEES(MANAGER)WORKER表中的MANAGER_ID等于MANAGER表中的EMPLOYEE_ID表的自銜接運(yùn)用SQL99語(yǔ)法引見(jiàn)運(yùn)用一個(gè)銜接查詢(xún)多張表的數(shù)據(jù)運(yùn)用SQL99語(yǔ)法引見(jiàn)CrossjoinJoinOn()Leftouterjoinon()Rightouterjoin()Fullouterjoinon() CROSSJOIN產(chǎn)生了一個(gè)笛卡爾積〔Cartesianproduct〕，就象是在銜接兩個(gè)表格時(shí)忘記參與一個(gè)WHERE子句一樣

例： SELECTlast_name,dept_idFROMemp,dept;

例： SELECTename,locFROMempCROSSJOINdept;CROSSJOIN

ON子句被用于當(dāng)在兩個(gè)表中的字段名不一致時(shí)來(lái)銜接表

例：

SELECTdepartment_name,city

FROMdepartment,location

WHEREdepartment.location_id=location.loc_id;

SELECTdepartment_name,city

FROMdepartmentdJOINlocationl

ON(d.location_id=l.id);ON子句 在LEFTOUTERJOIN中，會(huì)前往一切左邊表中的行，甚至在被銜接的表中沒(méi)有可對(duì)應(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 在RIGHTOUTERJOIN中前往的是表中一切右邊的行，甚至在被銜接的表中沒(méi)有對(duì)應(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 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.depar