高級SQL語句課件

第七節(jié)高級SQL語句

?通過本節(jié)學(xué)習(xí)，你需要掌握：

?組運算

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

GROUPINGSETS

?高級子查詢：關(guān)聯(lián)子查詢、EXISTS運

算、WITH子句

?高級DML語句

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

第七節(jié)高級SQL語句

?組運算

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

GROUPINGSETS

?高級子查詢：關(guān)聯(lián)子查詢、EXISTS運算、

WITH子句

?高級DML語句

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

TheSETOperators

UNION/UNIONALL

INTERSECT

■

■

MINUS

UNION運算

TheUNIONSETOperator

TheUNIONoperatorreturnsresultsfrombothqueries

aftereliminatingduplications.

⑥展現(xiàn)所有員工當(dāng)前和歷史工作的詳細(xì)資料。

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

令展現(xiàn)現(xiàn)任工作與歷史工作相同的員工號和

工作。

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

令展現(xiàn)至今為止沒有變更過工作的員工號

SELECTemployeejd

FROMemployees

MINUS

SELECTemployeejd

FROMjob_history;

EMPLOYEEJD

100

____________________103

104

組運算注意事項

令字段數(shù)量、數(shù)據(jù)類型必須相同，字段的名

稱可以不相同。

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

第一個字段的升序排列。

令不能使用DISTINCT關(guān)鍵字強制要求UNION

ALL消除冗余。

⑥ORDERBY子句只能出現(xiàn)在句子最后金道序、

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

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

4,一.一

子查詢中的組運算

SELECTemployeejd,departmentjd

FROMemployees

WHERE[employeejd,departmentjd]

IN[SELECTemployeejd,departmentjd

FROMemployees

UNION

SELECTemployeejd,depa

FROMjob_history);

控制數(shù)據(jù)順序

COLUMNa_dummyNOPRINT

SELECT'sing*AS"Mydream",3a__dummy

FROMdual

UNION

SELECTTdliketoteach；1

FROMdual

UNION

SELECT'theworldW2

Mydream.

FROMdual

ORDERBY2;rdliketoteach

Itheworldto=

第七節(jié)高級SQL語句

?組運算

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

GROUPINGSETS

?高級子查詢：關(guān)聯(lián)子查詢、EXISTS運算、

WITH子句

?高級DML語句

09:04:37廈門大學(xué)計算機系工程碩士教材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運算才艮據(jù)GROUPBY子句中字段從

右到左的順序）分別進(jìn)行數(shù)值合計。

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

維度數(shù)據(jù)的統(tǒng)計與合計工作，需要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運算實現(xiàn)了對數(shù)據(jù)模型的各個維度進(jìn)

行統(tǒng)計的要求。

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

據(jù)的各個維度進(jìn)行統(tǒng)計和合計工作，需要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子句的進(jìn)一步擴

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

方式。

.ORACLE服務(wù)器計算GROUPINGSETS中定義的

所有分組統(tǒng)計）并對所有分組的返回結(jié)果使用

UNIONALL運算。

使用GROUPINGSET只需要對全表進(jìn)行一次掃

描，比起使用復(fù)雜的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)

第七節(jié)高級SQL語句

?組運算

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

GROUPINGSETS

?高級子查詢：關(guān)聯(lián)子查詢、EXISTS運算、

WITH子句

?高級DML語句

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

洞^量,了■查詢

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

個字段值，這樣的子查詢稱為標(biāo)量子查詢。

。標(biāo)量子函數(shù)能出現(xiàn)在

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

◎INSERT語句中的VALUES子句

◎DECODE和CASE中的條件和表達(dá)式部分

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

左邊與買必

標(biāo)量子查詢：在CASE表達(dá)式中

SELECTemployeejd,last_name,

(CASE

WHENdepartmentjd=

(SELECTdepartmentjdFROM

departments

WHERElocationjd=1800)

THEN"Canada1ELSE'USA'END)location

FROMemployees;

標(biāo)量子查詢：在ORDERBY子句中

SELECTemployeejd,last_name

FROMemployeese

ORDERBY(SELECTdepartment_name

FROMdepartmentsd

WHEREe.departmentjd=d.departmentjd);

關(guān)聯(lián)子查詢和嵌套子查詢的區(qū)別

⑥嵌套子查詢的執(zhí)行步驟：

◎內(nèi)查詢執(zhí)行一次，并得到一個值

◎外查詢執(zhí)行一次，利用到內(nèi)查詢的值

⑥關(guān)聯(lián)子查詢：

◎外查詢?nèi)∫缓蜻x行

?用候選行的值執(zhí)行內(nèi)查詢

關(guān)聯(lián)子查詢：語法

SELECTcolumnl,column2f...

FROMtablelouter

WHEREcolumnloperator

(SELECTcolumnl,column2

FROMtable2

WHEREexprl=

outer,exp閽);

關(guān)聯(lián)子查詢：例1

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

SELECTlast_name,salarydepartmentjd

FROMemployeesouter

WHEREsalary>(SELECTAVG(salary)

FROMemployees

WHEREdepartmentjd=

outer.departmmtjd￡七X

美"聯(lián)子查詢：例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結(jié)構(gòu)代替EXISTS運算

SELECTemployeejd,last_name;job_id,

departmentjd

FROMemployees

WHEREemployeejdIN

(SELECTmanagerjd

FROMemployees

WHEREmanagerjdISNOTNULL);

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

高。-

NOTEXISTS運算

e提取沒有員工的部門

SELECTdepartmentjd,department_name

FROMdepartmentsd

WHERENOTEXISTS(SELECTX

FROMemployees

WHEREdepartmentjd

=d.depar

DEPARTMENTJD|DEPARTMENT_NAME

;190廊ntracting

令NOTIN結(jié)構(gòu)可以代替NOTEXISTS運算

SELECTdepartmentjd,department_name

FROMdepartments

WHEREdepartmentjdNOTIN(SELECT

departmentjd

FROMemployees);

norowsselected.

關(guān)聯(lián)更新：語法

UPDATEtablelaliasl

SETcolumn=[SELECTexpression

FROMtable2alias2

WHEREaliasl.column=

alias2.column);

。通過關(guān)聯(lián)子查詢用一張表格的值來更新另

長表格

關(guān)聯(lián)更新：例1

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

名稱

ALTERTABLEemployees

ADD[department_nameVARCHAR2[14]];

UPDATEemployeese

SETdepartment_name=

[SELECTdepartment_name

FROMdepartmentsd

WHEREe.departmentjd=

d.departmentjd);

關(guān)聯(lián)更新：例2

⑥根據(jù)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);

關(guān)聯(lián)刪除：語法

DELETEFROMtablelaliasl

WHEREcolumnoperator

(SELECTexpression

FROMtable2alias2

WHEREaliasl.column=alias2,column);

⑥通過關(guān)聯(lián)子查詢基于一張表格來刪除另一

張表格中的記錄、4?

關(guān)聯(lián)刪除：例1

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

EMP_HISTORY表中的記錄。

DELETEFROMemployeesE

WHEREemployeejd=

(SELECTemployeejd

FROMemp_history

WHEREemployeejd=E.employee

關(guān)聯(lián)刪除：例2

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

工，變更記錄。每當(dāng)一個員工進(jìn)行

:弟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語句中。

⑥當(dāng)WITH子句定義的臨時表格的名字與數(shù)據(jù)

庫中已知表名相同，則SELECT語句優(yōu)先使

用WITH定義的表格。

第七節(jié)高級SQL語句

?組運算

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

GROUPINGSETS

?高級子查詢：關(guān)聯(lián)子查詢、EXISTS運算、

WITH子句

?高級DML語句

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

多表INSERT語句

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

句中的一部份。

令多表插入語句可以用在數(shù)據(jù)倉庫系統(tǒng)中，

用于從一個數(shù)據(jù)源向多個目標(biāo)分發(fā)數(shù)據(jù)。

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

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

◎多條INSERT…SELECT語句4金，

◎使用IF…THEN語法構(gòu)造的插入過程國魚殳:

多表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轉(zhuǎn)換）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.

行列轉(zhuǎn)換

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;

[