高级SQL语句课件

第七节高级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;

[