第七節高級SQL語句

?通過本節學習，你需要掌握：

?組運算

?高級分組子句：ROLLUP、CUBE、

GROUPINGSETS

?高級子查詢：關聯子查詢、EXISTS運

算、WITH子句

?高級DML語句

09:04:36廈門大學計算機系工程碩士教材1

第七節高級SQL語句

?組運算

?高級分組子句：ROLLUP、CUBE、

GROUPINGSETS

?高級子查詢：關聯子查詢、EXISTS運算、

WITH子句

?高級DML語句

09:04:36廈門大學計算機系工程碩士教材2

TheSETOperators

UNION/UNIONALL

INTERSECT

■

■

MINUS

UNION運算

TheUNIONSETOperator

TheUNIONoperatorreturnsresultsfrombothqueries

aftereliminatingduplications.

⑥展現所有員工當前和歷史工作的詳細資料。

SELECTemployeejd,jobjd

FROMemployees

UNION

SELECTemployeejd,jobjd

FROMjob_history;

SELECTemployeejd,jobjd,departmentjd

FROMemployees

UNION

SELECTemployeejd,jobjd,departmentjd

FROMjob_history;

EMPLOYEEIDJOB」DDEIMRTMEN幾眼

■■■

200(AC_ACCOUNT90

200AD.ASST

200ADA?

UNIONALL運算

TheUNIONALLOperator

AB

TheUNIONALLoperatorreturnsresultsfromboth

queriesincludingallduplications.

SELECTemployeejd,jobjd,departmentjd

FROMemployees

UNIONALL

SELECTemployeeJd,jobjd；departmentjd

FROMjob_historyORDERBY子句

id;必須放在最后寫

EMPLOYEEJDJOBJDDEPARTMENTJD

■■■

176SA_REP80

176SA_MAN80

176SA_REPJggg

INTERSECT運算

TheINTERSECTOp|erator

B

TheINTERSECToperatorreturnsresultsthatare

commontobothqueries.aS

令展現現任工作與歷史工作相同的員工號和

工作。

SELECTemployeejd,jobjd

FROMemployees

INTERSECT

SELECTemployeejd,jobjd

FROMjob_history;

EMPLOYEEJDJOBJD

176SA_REP

200AD_ASST

SELECTemployeejd,jobjd,departmentjd

FROMemployees

INTERSECT

SELECTemployeejd,jobjd,departmentjd

FROMjob_history;

—EMPLOYEE.!_JOB_JDrDEPARTMENT口I；

176SAREPonl

MINUS運算

TheMINUSOperator

AB

m

TheMINUSoperatorreturnsrowsfromthefirstquery

thatarenotpresentinthesecondquery.s

令展現至今為止沒有變更過工作的員工號

SELECTemployeejd

FROMemployees

MINUS

SELECTemployeejd

FROMjob_history;

EMPLOYEEJD

100

____________________103

104

組運算注意事項

令字段數量、數據類型必須相同，字段的名

稱可以不相同。

令除UNIONALL運算，其他運算消除冗余，按

第一個字段的升序排列。

令不能使用DISTINCT關鍵字強制要求UNION

ALL消除冗余。

⑥ORDERBY子句只能出現在句子最后金道序、

的字段可以是第一個SELECT子句十艙字段"

名、表達式、同義詞或者位置符耳。再

4,一.一

子查詢中的組運算

SELECTemployeejd,departmentjd

FROMemployees

WHERE[employeejd,departmentjd]

IN[SELECTemployeejd,departmentjd

FROMemployees

UNION

SELECTemployeejd,depa

FROMjob_history);

控制數據順序

COLUMNa_dummyNOPRINT

SELECT'sing*AS"Mydream",3a__dummy

FROMdual

UNION

SELECTTdliketoteach；1

FROMdual

UNION

SELECT'theworldW2

Mydream.

FROMdual

ORDERBY2;rdliketoteach

Itheworldto=

第七節高級SQL語句

?組運算

?高級分組子句：ROLLUP、CUBE、

GROUPINGSETS

?高級子查詢：關聯子查詢、EXISTS運算、

WITH子句

?高級DML語句

09:04:37廈門大學計算機系工程碩士教材17

ROLLUP運算語法

SELECT[column^groupJunction(column)...

FROMtable

[WHEREcondition]

[GROUPBY[ROLLUP]group_by_expression]

[HAVINGhaving_expression];

[ORDERBYcolumn];

⑥ROLLUPAGROUPBY子句的擴展,

計累計值。

SELECTdepartmentJd,jobjd,SUM[salary]

FROMemployees

WHEREdepartmentjd<60

GROUPBYROLLUP(department_id,job_id);

DEPARTMENTJDJOBJDSUM(SALARY)

10AD_ASST4400

4400

20MK_MAN13000

20MK_REP6000

19000

50ST_CLERK

50ST_MAN5800

彳30

40900

令ROLLUP運算才艮據GROUPBY子句中字段從

右到左的順序）分別進行數值合計。

令如果不使用ROLLUP運算，同樣完成對n個

維度數據的統計與合計工作，需要n+1個

SELECT語句用UNIONALL連接。這樣的做

法是低效的，因為每個SELECT語句都要對

全表掃描。而ROLLUP語句只要掃描一次。

CUBE運算語法

SELECT[column}group_function^column)...

FROMtable

[WHEREcondition]

[GROUPBY[CUBE]group_by_expression]

[HAVINGhaving_expression];

[ORDERBYcolumn];

?CUBE是GROUPBY子句的擴展，唾線計各垂

個維度的累計值。

CUBE運算

SELECTdepartmentjd,job_id,SUM(salary)

FROMemployees

WHEREdepartmentjd<60

GROUPBYCUBEfdepartmentJd,jobjd);

DEPARTMENT」DJOBJDSUM(SALARY)

10AD_ASST4400

104400

20MK_MAN13000

20MK_REP6000

2019000(B

50ST-CLERK11700

50ST.MAN5800

5017500?

AD_ASST4400

MK_MAN13000

MK_REP6000

ST_CLERK@17版

ST_MAN5800

令CUBE運算實現了對數據模型的各個維度進

行統計的要求。

令如果不采用CUBE運算，同樣完成對n維數

據的各個維度進行統計和合計工作，需要2n

個SELECT語句用UNIONALL連接，而且這

樣的做法是低效的。

GROUPINGSETS

SELECTdepartmentJd,job_id,

manager_id,avg(salary)

FROMemployees

GROUPBYGROUPINGSETS

[[departmentjdjobjd],(jobjd.managerjd]];

DEPARTMENTJDJOBJDMANAGERJDAVG(SALARY)

10AD.ASST4400

____________________3MK_MAN13000

20MK_REP6000

50ST_CLERK2925

■■■

MK_REP2016000

SA_MAN10010500

SA_REP1498866.66667

ST_CLERK1242925

ST_MAN100注7800.

GROUPINGSETS

⑥GROUPINGSETS是GROUPBY子句的進一步擴

展，可以在一個查詢語句中定義多種分組統計

方式。

.ORACLE服務器計算GROUPINGSETS中定義的

所有分組統計）并對所有分組的返回結果使用

UNIONALL運算。

使用GROUPINGSET只需要對全表進行一次掃

描，比起使用復雜的UNION語句，是簡捷而高

效的寫法。

ORACLE9i及更高版本使用。

CUBE、ROLLUP、GROUPINGSETS

的比較

CUBE?b,c)等價于GROUPINGSETS

((a,b,c),(a,b),(a,c),(b,c),

(a),(b),(c),())

ROLLUPfa,b,c)等價于GROUPINGSETS((a,b,c),(a,

b),(a),0)

第七節高級SQL語句

?組運算

?高級分組子句：ROLLUP、CUBE、

GROUPINGSETS

?高級子查詢：關聯子查詢、EXISTS運算、

WITH子句

?高級DML語句

09:04:37廈門大學計算機系工程碩士教材29

洞^量,了■查詢

⑥如果一^個子查詢對每行記錄都恰好返回一

個字段值，這樣的子查詢稱為標量子查詢。

。標量子函數能出現在

◎SELECT語句中除GROUPBY以夕卜的任意子句

◎INSERT語句中的VALUES子句

◎DECODE和CASE中的條件和表達式部分

◎UPDATE語句中SET和WHERE子句中您集?符的

左邊與買必

標量子查詢：在CASE表達式中

SELECTemployeejd,last_name,

(CASE

WHENdepartmentjd=

(SELECTdepartmentjdFROM

departments

WHERElocationjd=1800)

THEN"Canada1ELSE'USA'END)location

FROMemployees;

標量子查詢：在ORDERBY子句中

SELECTemployeejd,last_name

FROMemployeese

ORDERBY(SELECTdepartment_name

FROMdepartmentsd

WHEREe.departmentjd=d.departmentjd);

關聯子查詢和嵌套子查詢的區別

⑥嵌套子查詢的執行步驟：

◎內查詢執行一次，并得到一個值

◎外查詢執行一次，利用到內查詢的值

⑥關聯子查詢：

◎外查詢取一候選行

?用候選行的值執行內查詢

關聯子查詢：語法

SELECTcolumnl,column2f...

FROMtablelouter

WHEREcolumnloperator

(SELECTcolumnl,column2

FROMtable2

WHEREexprl=

outer,exp閽);

關聯子查詢：例1

⑥提取高于本部門平均工資的員工

SELECTlast_name,salarydepartmentjd

FROMemployeesouter

WHEREsalary>(SELECTAVG(salary)

FROMemployees

WHEREdepartmentjd=

outer.departmmtjd￡七X

美"聯子查詢：例2

⑥提取至少變更2次工作的員工

SELECTe.employeejd,.e.jobjd

FROMemployeese

WHERE2<=(SELECTCOUNTf*]

FROMjob_history

WHEREemployeejd=e.employeejd);

EXISTS運算：例子

⑥提取至少有一名下屬的員工信息

SELECTemployeejd,last_name,jobjd,

departmentjd

FROMemployeesouter

WHEREEXISTS(SELECT1

FROMemployees

WHEREmanagerJd

■

outer.e?

令可以使用IN結構代替EXISTS運算

SELECTemployeejd,last_name;job_id,

departmentjd

FROMemployees

WHEREemployeejdIN

(SELECTmanagerjd

FROMemployees

WHEREmanagerjdISNOTNULL);

。但相比而言，使用EXISTS的執行效福會更

高。-

NOTEXISTS運算

e提取沒有員工的部門

SELECTdepartmentjd,department_name

FROMdepartmentsd

WHERENOTEXISTS(SELECTX

FROMemployees

WHEREdepartmentjd

=d.depar

DEPARTMENTJD|DEPARTMENT_NAME

;190廊ntracting

令NOTIN結構可以代替NOTEXISTS運算

SELECTdepartmentjd,department_name

FROMdepartments

WHEREdepartmentjdNOTIN(SELECT

departmentjd

FROMemployees);

norowsselected.

關聯更新：語法

UPDATEtablelaliasl

SETcolumn=[SELECTexpression

FROMtable2alias2

WHEREaliasl.column=

alias2.column);

。通過關聯子查詢用一張表格的值來更新另

長表格

關聯更新：例1

.在EMPLOYEES表中新增一個字段，保存部門

名稱

ALTERTABLEemployees

ADD[department_nameVARCHAR2[14]];

UPDATEemployeese

SETdepartment_name=

[SELECTdepartment_name

FROMdepartmentsd

WHEREe.departmentjd=

d.departmentjd);

關聯更新：例2

⑥根據rewards表里最新的員工工資增長記錄,

更新employees表的工資(salary)字段。

reward表

EMPLOYEEJD|PAY.RAISE|PAYRAISE.DATE

12480001-Jan-08

10150001-Jan-08

UPDATEemployees

SETsalary=(SELECTemployees.salary+rewards.pay_raise

FROMrewards

WHEREemployeejd=

employees.employeejd

ANDpayraise_date=

(SELECTMAX(payraise_date)

FROMrewards

WHEREemployeejd=

employees.employeejd))

WHEREemployees.employeejd

IN(SELECTemployeejdFROMrewards);

關聯刪除：語法

DELETEFROMtablelaliasl

WHEREcolumnoperator

(SELECTexpression

FROMtable2alias2

WHEREaliasl.column=alias2,column);

⑥通過關聯子查詢基于一張表格來刪除另一

張表格中的記錄、4?

關聯刪除：例1

⑥在EMPLOYEES表中刪除同時存在于

EMP_HISTORY表中的記錄。

DELETEFROMemployeesE

WHEREemployeejd=

(SELECTemployeejd

FROMemp_history

WHEREemployeejd=E.employee

關聯刪除：例2

◎弓手在JOBJHSTORY表中只保留每個員工班

工，變更記錄。每當一個員工進行

:弟5次工作變更，需要刪除最早的一次工

作變更圮錄。寫出這樣的判斷刪除語句。

DELETEFROMjob_historyJH

WHEREstart_date=

(SELECTMIN(start_date)

FROMjob_history

WHEREemployeejd=JH.employeeJd)

AND4<(SELECTCOUNTf*)

FROMjob_history

WHEREemployeejd=JH.employeeJd);

WITH子句：例子

DEPARTMENT_NAMEDEPT.TOTAL

Executive58000

Sales3710D

WITH

dept_costsAS(

SELECT,SUM(salary]AS

dept_total

FROMemployees,departments

WHEREemployees.departmentjd=

departments.departmentjd

GROUPBY),

avg_costAS

(SELECTSUM(dept_total)/COUNT(*)ASdept.avg

FROMdept_costs)

SELECT*FROMdept_costs

WHEREdept_total>

(SELECTdept_avgFROMavg_cost)

ORDERBYdepartment_name;

WITH子句注意事項

⑥只能用在SELECT語句中。

⑥當WITH子句定義的臨時表格的名字與數據

庫中已知表名相同，則SELECT語句優先使

用WITH定義的表格。

第七節高級SQL語句

?組運算

?高級分組子句：ROLLUP、CUBE、

GROUPINGSETS

?高級子查詢：關聯子查詢、EXISTS運算、

WITH子句

?高級DML語句

09:04:37廈門大學計算機系工程碩士教材52

多表INSERT語句

⑥INSERT…SELECT語句可以成為多表插入語

句中的一部份。

令多表插入語句可以用在數據倉庫系統中，

用于從一個數據源向多個目標分發數據。

令多表插入語句作為單一^勺DML語句）與以

下兩種方式比較，效率有明顯的提高：

◎多條INSERT…SELECT語句4金，

◎使用IF…THEN語法構造的插入過程國魚殳:

多表INSERT語句：語法

語法

INSERT[ALL][conditional_insert_clause]

[insert_into_clausevalues_clause](subquery]

conditional_insert_clause

[ALL][FIRST]

[WHENconditionTHEN][insert_into_clause

values_clause]

[ELSE][insert_into_clausevalues.clause;^

多表INSERT語句：類型

.UnconditionalINSERT

.ConditionalALLINSERT

.ConditionalFIRSTINSERT

.Pivoting（行歹4轉換）INSERT

Uncond什ionalINSERTALL

INSERTALL

INTOsal_history

VALUES(EMPIDHIREDATE’SAL)

INTOmgr_historyVALUES(EMPID.MGR,SAL)

SELECTemployeejdEMPID,hire_date

HIREDATE,salarySAL,managerjdMGR

FROMemployees

WHEREemployeejd>200;

ConchtionalINSERTALL

INSERTALL

WHENSAL>10000THEN

INTOsal_historyVALUES(EMPID,HIREDATE7SAL]

WHENMGR>200THEN

INTOmgr_historyVALUES(EMPID’MGRSAL)

SELECTemployeejdEMPID,hire_date

HIREDATE,salarySAL,manager_idMGR

FROMemployees

WHEREemployeejd>200;

4rowscreated.Jq/維

Cond讓ionalFIRSTINSERT

INSERTFIRST

WHENSAL>25000THEN

INTOspecial_salVALUESfDEPTID,SAL)

WHENHIREDATElike('%00%')THEN

INTOhiredate_history_00VALUESfDEPTID,HIREDATE)

WHENHIREDATElike('%99%')THEN

INTOhiredate_history_99VALUESfDEPTID,HIREDATE)

ELSE

INTOhiredate.historyVALUES(DEPTID,HIREDATE)

SELECTdepartmentjdDEPTID,SUM(salary)SAL,

MAX(hire_date)HIREDATE

FROMemployees

GROUPBYdepartmentjd;

8rowscreated.

行列轉換

DESCSALES_SOURCE_DATA

NameNull?II_____31?^______

|EMPLOYEE_IDNUMBERS)

|WEEK_IDNUMBER(2)

SALES_MONNUMBER??)

|SALES_TUENUMBER且2)

^SALESWED

—?NUMBERS,2)

|SALES_THURNUMBER(8,2)

)SALES_FRINUMBER(8,2)

SELECT*FROMSALESSOURCE_DA黔良&/

EMPLOYEE」。WEEK」。SALES」##SALESTIIESALES_WEbSALESTHURSALES_FP.I

176-T—00300040005000~6000

DESCSALESINFO

NameNull?Typo

iEMPLOYEEJDNUMBER⑹

|WEEK：NUMBER(2)

：SALESNUMBER")

SELECT*FROMsalesjnfo;

[