oracle數(shù)據(jù)庫(kù)-p高級(jí)子查詢

目標(biāo)通過本章學(xué)習(xí)，您將可以:書寫多列子查詢子查詢對(duì)空值的處理在FROM

子句中使用子查詢?cè)赟QL中使用單列子查詢相關(guān)子查詢書寫相關(guān)子查詢使用子查詢更新和刪除數(shù)據(jù)使用EXISTS

和NOT

EXISTS

操作符使用WITH

子句子查詢子查詢是嵌套在SQL

語句中的另一個(gè)SELECT

語句SELECT

...FROM

...WHERE

...(SELECT

...FROM

...WHERE

...)主查詢子查詢子查詢子查詢(內(nèi)查詢)在主查詢執(zhí)行之前執(zhí)行主查詢使用子查詢的結(jié)果

(外查詢)SELECT

select_listFROM

tableWHERE

expr

operator

(SELECT

select_listFROM

table);SELECT

last_nameFROMWHEREemployeessalary

>(SELECT

salaryFROM

employeesWHERE employee_id

=

149);子查詢應(yīng)用舉例10500多列子查詢Main

queryWHERE

(MANAGER_ID,

DEPARTMENT_ID)

INSubquery100901026012450主查詢與子查詢返回的多個(gè)列進(jìn)行比較列比較多列子查詢中的比較分為兩種:成對(duì)比較不成對(duì)比較成對(duì)比較舉例SELECT

employee_id,

manager_id,

department_idFROM

employeesWHERE (manager_id,

department_id)

IN(SELECT

manager_id,

department_idFROM

employeesWHERE employee_id

IN

(178,174))AND employee_id

NOT

IN

(178,174);不成對(duì)比較舉例SELECTFROMWHEREemployee_id,

manager_id,

department_idemployeesmanager_id

IN(SELECTFROMWHEREmanager_idemployeesemployee

id

IN

(174,141))ANDdepartment_id

IN(SELECTFROMWHEREdepartment_idemployeesemployee_id

IN

(174,141))ANDemployee_id

NOT

IN(174,141);SELECTFROMa.last_name,

a.salary,a.department_id,

b.salavgemployees

a,

(SELECT

department_id,AVG(salary)

salavgFROM

employeesGROUP

BY

department_id)

bWHEREANDa.department_id

=

b.department_ida.salary

>

b.salavg;在FROM

子句中使用子查詢單列子查詢表達(dá)式單列子查詢表達(dá)式是在一行中只返回一列的子查詢Oracle8i

只在下列情況下可以使用,例如:SELECT

語句(FROM

和WHERE

子句)INSERT

語句中的VALUES列表中Oracle9i中單列子查詢表達(dá)式可在下列情況下使用:DECODE

和CASESELECT

中除GROUP

BY

子句以外的所有子句中單列子查詢應(yīng)用舉例在CASE

表達(dá)式中使用單列子查詢SELECT

employee_id,

last_name,(CASEWHEN

department_id

=FROMTHEN

'Canada'

ELSE

'USA'

END)

locationemployees;(SELECT

department_id

FROM

departmentsWHERE

location

id

=

1800)在ORDER

BY

子句中使用單列子查詢SELECTFROMemployee_id,

last_nameemployees

e20ORDER

BY

(SELECT

department_nameFROM

departments

dWHERE

e.department_id

=

d.department_id);相關(guān)子查詢相關(guān)子查詢按照一行接一行的順序執(zhí)行，主查詢的每一行都執(zhí)行一次子查詢GET從主查詢中獲取候選列EXECUTE子查詢使用主查詢的數(shù)據(jù)USE如果滿足內(nèi)查詢的條件則返回該行相關(guān)子查詢SELECT

column1,

column2,

...FROM

table1WHERE

column1

operator(SELECTFROMWHERE子查詢中使用主查詢中的列outercolum1,

column2table2expr1

=outer.expr2);相關(guān)子查詢舉例(SELECT

AVG(salary)FROM

employees

WHERE department_id

=outer.department_id)SELECT

last_name,

salary,

department_idFROM employees

outerWHERE salary

>;相關(guān)子查詢舉例SELECT

e.employee_id,

last_name,e.job_idFROM employees

eWHERE 2

<=

(SELECT

COUNT(*)FROM

job_historyWHERE employee_id

=

e.employee_id);EXISTS

操作符EXISTS

操作符檢查在子查詢中是否存在滿足條件的行如果在子查詢中存在滿足條件的行:不在子查詢中繼續(xù)查找條件返回TRUE如果在子查詢中不存在滿足條件的行:條件返回FALSE繼續(xù)在子查詢中查找EXISTSSELECT

employee_id,

last_name,

job_id,

department_idFROM employees

outerWHERE (

SELECT

'X'FROM

employeesWHERE manager_id=outer.employee_id);EXISTS

操作符應(yīng)用舉例NOT

EXISTSSELECT

department_id,

department_nameFROM

departments

dWHERE (SELECT

'X'FROM

employeesWHERE

department_id=

d.department_id);NOT

EXISTS

操作符應(yīng)用舉例相關(guān)更新使用相關(guān)子查詢依據(jù)一個(gè)表中的數(shù)據(jù)更新另一個(gè)表的數(shù)據(jù)UPDATE

table1

alias1SET column

=

(SELECT

expressionFROMWHEREtable2

alias2alias1.column

=alias2.column);相關(guān)更新應(yīng)用舉例ALTER

TABLE

employeesADD(department_name

VARCHAR2(14));UPDATEemployees

eSETdepartment_name

=(SELECT

department_nameFROM departments

dWHERE e.department_id

=

d.department_id);DELETE

FROM

table1

alias1WHEREcolumn

operator(SELECT

expressionFROMWHEREtable2

alias2alias1.column

=

alias2.column);相關(guān)刪除使用相關(guān)子查詢依據(jù)一個(gè)表中的數(shù)據(jù)刪除另一個(gè)表的數(shù)據(jù)DELETE

FROM

employees

EWHERE

employee_id

=(SELECT

employee_idFROMWHEREemp_historyemployee_id

=

E.employee_id);相關(guān)刪除應(yīng)用舉例WITH

子句使用WITH

子句,可以避免在SELECT

語句中重復(fù)書寫相同的語句塊WITH

子句將該子句中的語句塊執(zhí)行一次

并

到用戶的臨時(shí)表空間中使用WITH

子句可以提高查詢效率WITH

子句應(yīng)用舉例WITHdept_costsSELECTFROMWHEREAS(d.department_name,

SUM(e.salary)

AS

dept_totalemployees

e,

departments

de.department_id

=

d.department_idGROUP

BY

d.department_name),avg_cost

AS(SELECT

SUM(dept_total)/COUNT(*)

AS

dept_avgFROM

dept_costs)SELECT

*FROMWHEREdept_costsdept_total

>(SELECT