oracle第五講復(fù)雜查詢

Oracle數(shù)據(jù)庫管理系統(tǒng)

第五講復(fù)雜查詢第五講復(fù)雜查詢課程目標(biāo)多表連接查詢分組查詢子查詢123分級查詢4第五講復(fù)雜查詢一、分組查詢分組函數(shù)分組函數(shù)作用于一組數(shù)據(jù)，并對一組數(shù)據(jù)返回一個(gè)值。AVGCOUNTMAXMINSUM第五講復(fù)雜查詢一、分組查詢組函數(shù)語法SELECT [column,]group_function(column),...FROM table[WHERE condition][GROUPBY column][ORDERBY column];第五講復(fù)雜查詢一、分組查詢？查詢最高工資和最低工資？查詢員工的平均工資和工資總和？共有多少員工？工資最高的員工的名字、工作崗位？工資高于平均工資的員工信息？工資低于平均工資且入職時(shí)間在80年前的漲10%第五講復(fù)雜查詢一、分組查詢可以對數(shù)值型數(shù)據(jù)使用AVG和SUM函數(shù)可以對任意數(shù)據(jù)類型的數(shù)據(jù)使用MIN和MAX函數(shù)注意：COUNT(*)返回表中記錄總數(shù)COUNT(expr)返回expr不為空的記錄總數(shù)

COUNT(DISTINCTexpr)返回expr非空且不重復(fù)的記錄總數(shù)組函數(shù)忽略空值NVL函數(shù)使分組函數(shù)無法忽略空值第五講復(fù)雜查詢一、分組查詢GROUPBY、having子句可以使用GROUPBY子句將表中的數(shù)據(jù)分成若干組？每個(gè)部門的平均工資和最高工資？每個(gè)部門各崗位的平均工資和最高工資？平均工資低于2000的部門號和平均工資注意：在SELECT列表中所有未包含在組函數(shù)中的列都應(yīng)該包含在GROUPBY子句中，反之未必然。不能在WHERE子句中使用組函數(shù)可以在HAVING子句中使用組函數(shù)第五講復(fù)雜查詢一、分組查詢having子句：過濾分組使用HAVING過濾分組:1. 行已經(jīng)被分組。2. 使用了組函數(shù)。3. 滿足HAVING子句中條件的分組將被顯示。第五講復(fù)雜查詢二、多表查詢什么是連接查詢

將來自不同表的數(shù)據(jù)在關(guān)系的基礎(chǔ)上連接到一起

為什么要使用連接查詢？想知道某員工所在的部門名，該怎么辦

第五講復(fù)雜查詢二、多表查詢怎么使用連接查詢？交叉連接：廣義笛卡爾積內(nèi)連接：等值連接、非等值連接外連接：左外連接和右外連接自連接復(fù)合條件連接第五講復(fù)雜查詢二、多表查詢交叉連接不帶連接謂詞的連接，即兩個(gè)表的笛卡爾積是兩表中元組的交叉乘積。結(jié)果會(huì)產(chǎn)生一些沒有任何意義的元組，因此實(shí)際應(yīng)用中很少使用。舉例：

select*fromemp,dept;

（返回14*4=56行記錄的笛卡兒積）第五講復(fù)雜查詢二、多表查詢交叉連接笛卡爾集會(huì)在下面條件下產(chǎn)生:

省略連接條件連接條件無效所有表中的所有行互相連接為了避免笛卡爾集，可以在WHERE加入有效的連接條件。第五講復(fù)雜查詢二、多表查詢內(nèi)連接：等值連接：以等號運(yùn)算符（=）為基礎(chǔ)的連接

[<表名1>.]<列名1>=[<表名2>.]<列名2>

第五講復(fù)雜查詢二、多表查詢？顯示SALES部門位置及其員工姓名？查找雇員名、工資及所在部門的名稱？顯示員工姓名、部門名，按部門號排序第五講復(fù)雜查詢二、多表查詢區(qū)分重復(fù)的列名使用表名前綴在多個(gè)表中區(qū)分相同的列。使用表名可以提高效率。在不同表中具有相同列名的列可以用別名加以區(qū)分。另外：連接n個(gè)表,至少需要n-1個(gè)連接條件。

第五講復(fù)雜查詢二、多表查詢非等值連接？顯示員工姓名、工資及工資級別

第五講復(fù)雜查詢二、多表查詢外連接：使用外連接可以查詢不滿足連接條件的數(shù)據(jù)Oracle中外連接的符號是(+)

SELECT table1.column,table2.columnFROM table1,table2WHERE table1.column(+)

=table2.column;SELECT table1.column,table2.columnFROM table1,table2WHERE table1.column=table2.column(+);第五講復(fù)雜查詢二、多表查詢外連接：與普通連接的區(qū)別普通連接操作只輸出滿足連接條件的記錄外連接操作以指定表為連接主體，將主體表中不滿足連接條件的記錄一并輸出

第五講復(fù)雜查詢二、多表查詢有關(guān)外連接的幾點(diǎn)說明：：在表名后面加外連接操作符(+)指定非主體表。非主體表有一“萬能”的虛行，該行全部由空值組成。虛行可以和主體表中所有不滿足連接條件的元素進(jìn)行連接，并且由于虛行各列全部是空值，因此與虛行連接的結(jié)果中，來自非主體表的屬性值全部是空值。

第五講復(fù)雜查詢二、多表查詢有關(guān)外連接的幾點(diǎn)說明：：左外連接：wheret1.column=t2.column(+)；

左表不變，右表+null右外連接：wheret1.column(+)=t2.column；

右表不變，左表+null

第五講復(fù)雜查詢二、多表查詢自連接一個(gè)自連接是一個(gè)表連接表本身的連接，通過在邏輯上建立該表的兩份副本來執(zhí)行該查詢。要完成查詢，該表必須給出兩個(gè)別名以用來相互比較。？顯示某員工上級的名字？顯示FORD的上級

第五講復(fù)雜查詢二、多表查詢復(fù)合條件連接：：WHERE子句中含多個(gè)連接條件時(shí)，稱為復(fù)合條件連接？查詢部門ACCOUNTING中薪水大于2000的員工信息。

第五講復(fù)雜查詢二、多表查詢使用SQL:1999語法連接SELECT table1.column,table2.columnFROM table1[CROSSJOIN

table2]|[NATURALJOIN

table2]|[JOIN

table2

USING(column_name)]|[JOIN

table2

ON(table1.column_name=table2.column_name)]|[LEFT|RIGHT|FULLOUTERJOIN

table2

ON(table1.column_name=table2.column_name)];第五講復(fù)雜查詢二、多表查詢叉集使用CROSSJOIN子句使連接的表產(chǎn)生叉集。叉集和笛卡爾集是相同的。例：SELECTename,dnameFROMempCROSSJOINdept;第五講復(fù)雜查詢二、多表查詢自然連接NATURALJOIN子句，會(huì)以兩個(gè)表中具有相同名字的列為條件創(chuàng)建等值連接。第五講復(fù)雜查詢二、多表查詢JOIN連接在JOIN子句創(chuàng)建等值連接時(shí)，可以使用USING子句指定等值連接中需要用到的列。使用USING可以在有多個(gè)列滿足條件時(shí)進(jìn)行選擇。不要給選中的列中加上表名前綴或別名。JOIN和USING子句經(jīng)常同時(shí)使用。例：SELECTe.empno,e.ename,d.locFROMempeJOINdeptdUSING(deptno)第五講復(fù)雜查詢二、多表查詢使用ON子句創(chuàng)建連接SELECTe.employee_id,l.city,d.department_nameFROMemployeeseJOINdepartmentsdONd.department_id=e.department_idJOINlocationslONd.location_id=l.location_id;第五講復(fù)雜查詢二、多表查詢外連接左外連接：LEFTOUTERJOIN右外連接：RIGHTOUTERJOIN全外連接：FULLOUTERJOINSELECTe.last_name,e.department_id,d.department_nameFROMemployeeseLEFTOUTERJOINdepartmentsdON(e.department_id=d.department_id);第五講復(fù)雜查詢?nèi)⒆硬樵冋l的工資比SCOTT高?誰的工資比SCOTT高?MainQuery:?SCOTT的工資是多少??Subquery第五講復(fù)雜查詢?nèi)?、子查詢什么是子查詢（嵌套查詢?一個(gè)SELECT-FROM-WHERE語句稱為一個(gè)查詢塊；將一個(gè)查詢塊嵌套在另一個(gè)查詢塊的WHERE子句或HAVING短語的條件中的查詢稱為嵌套查詢SELECT select_listFROM tableWHERE exproperator

(SELECT select_list FROM table);第五講復(fù)雜查詢?nèi)?、子查詢注意事?xiàng)子查詢要包含在括號內(nèi)。將子查詢放在比較條件的右側(cè)。不要在子查詢中使用ORDERBY子句。單行操作符對應(yīng)單行子查詢，多行操作符對應(yīng)多行子查詢。第五講復(fù)雜查詢?nèi)⒆硬樵儐涡凶硬樵冎环祷匾恍薪Y(jié)果。使用單行比較操作符。Operator=>>= <<= <>MeaningEqualtoGreaterthanGreaterthanorequaltoLessthanLessthanorequaltoNotequalto第五講復(fù)雜查詢?nèi)?、子查詢？顯示與SMITH同一部門的所有員工1）查詢出SMITH的部門號2）顯示結(jié)果第五講復(fù)雜查詢?nèi)⒆硬樵兌嘈凶硬樵兎祷囟嘈?。使用多行比較操作符。操作符

INANY

ALL含義等于集合中的任何一個(gè)元素比子查詢返回的任意一個(gè)值怎么樣就為真

比子查詢返回的所有值都怎么樣才為真第五講復(fù)雜查詢?nèi)?、子查詢？查詢與部門10的工種相同的員工姓名、工種1）查詢10部門有哪些工種2）使用in操作符？查找工資比部門30所有員工工資高的員工信息？查找比部門30任意一個(gè)員工的工資高的員工第五講復(fù)雜查詢?nèi)?、子查詢多列子查詢？查詢與SMITH部門和崗位完全相同的員工 1）查出SMITH的部門號和崗位 2）where(deptno,job)=子查詢結(jié)果第五講復(fù)雜查詢?nèi)?、子查詢在from子句中使用子查詢：內(nèi)嵌視圖？查詢高于自己部門平均工資的員工信息 1）查出各部門的平均工資和部門號 2）把上面的查詢結(jié)果看成一張表

wherea1.deptno=a2.deptnoanda1.sal>a2.sal第五講復(fù)雜查詢?nèi)?、子查詢相關(guān)子查詢相關(guān)子查詢按照一行接一行的順序執(zhí)行，主查詢的每一行都執(zhí)行一次子查詢GET從主查詢中獲取候選列EXECUTE子查詢使用主查詢的數(shù)據(jù)USE如果滿足內(nèi)查詢的條件則返回該行第五講復(fù)雜查詢?nèi)?、子查詢相關(guān)子查詢SELECTcolumn1,column2,...FROMtable1outerWHEREcolumn1operator (SELECTcolum1,column2FROMtable2WHEREexpr1=outer.expr2);？查詢高于自己部門平均工資的員工信息第五講復(fù)雜查詢?nèi)?、集合查詢UNION、INTERSECT、MINUSUnion可以完成將兩個(gè)以上的表的相類似的查詢結(jié)果合并在一起，并且相同的只取其一；unionall則表示返回所有行；Intersect返回在兩個(gè)表中都有相同內(nèi)容的信息；Minus則返回只在一個(gè)表中出現(xiàn)的信息。第五講復(fù)雜查詢?nèi)?、集合查詢?：列出有特長的考生名字（在表students,student_skill同時(shí)出現(xiàn)）sql>selectnamefromsutdentsintersectselectnamefromstudent_skill;第五講復(fù)雜查詢?nèi)⒓喜樵兝?：列出沒有特長的考生（僅在表students出現(xiàn)）sql>selectnamefromsutdentsminusselectnamefromstudent_skill;第五講復(fù)雜查詢四、分級查詢（家族樹）對具有層次樹結(jié)構(gòu)的數(shù)據(jù)按照層次關(guān)系輸出顯示，語法格式如下：SELECT…FROMtable_nameWHEREcondition