oracle数据库技术与应用初级core

第五章第四章五Oracle与高级的SQL语句第章企业级卓越人才培养目标掌握SQL语句在Oracle数据库的高级应用分组查询连接查询子查询合并查询分组查询当执行数据统计时，需要将表中的数据划分成几个组，最终统计每个组的数据结果数据分组统计是通过GROUP

BY子句、分组函数以及HAVING子句共同实现分组查询分组函数：作用于多行，并返回单一的值分组函数一般要与GROUP

BY结合使用。如果没有GROUP

BY子句会汇总所有的行，并产生一个结果select avg

(sal

)

as

平均工资

from emp

;雇员表中所有员工的平均工资select count

(

*)

from

emp

where sal

>=

2000

;工资大于等于2000的员工人数分组查询GROUP

BY：对查询结果进行分组统计使用GROUP

BY进行单列分组显示每个部门平均工资和最高工资select deptno,

avg(sal),

max(sal)

from

empgroup

by

deptno

;使用GROUPBY进行多列分组显示每个部门每种岗位的平均工资和最高工资select deptno,

job,

avg(sal),

max(sal)

from

empgroup

by

deptno,

job

;注意：SELECT选择列表中的列必须出现在group

by子句中分组查询HAVING：限制分组统计结果HAVING子句必须跟在GROUP

BY之后select deptno,

avg(sal),

max(sal)

from

empgroup

by

deptnohaving

avg(sal)

<

2500

;显示平均工资低于2500的部门号、平均工资及最高工资连接查询相等连接：检索主从表之间的相关数据显示所有雇员的姓名、和工资及其所在的部门名称deptno……salename……dnamedeptnoselect

e.ename,

e.sal,

d.dname

from

emp

e,

dept

dwhere

e.deptno

=

d.deptnoand

d.deptno

=

10

;select

e.ename,

e.sal,

d.dnamefrom

emp

e,

dept

dwhere

e.deptno

=

d.deptno

;使用AND指定其它条件连接查询不等连接：在连接条件中使用除相等比较符外的其它比较操作符的连接查询select

e.ename,

e.sal,

d.dnamefrom

emp

e,

dept

dwhere

e.deptno

between

10

and

20

;查询部门号在10和20之间的部门名称、雇员名称、雇员薪水连接查询自连接：在同一张表之间的连接查询。用在自参照表上显示上下级关系或者层次关系select a.ename

from

emp

a,

emp

b

where

a.empno

=b.mgr

and

b.ename

=

‘BLAKE’

;显示雇员“BLAKE”的上级经理的名字EMPNOENAMEMGR……7839KING7566JONES78397698BLAKE78397782CLARK7839……连接查询内连接和外连接内连接用于返回满足条件的记录外连接不仅返回满连接足条件的所有记录，而且还返回不满足条件的记录SELECT

table1.column,

table2.columnFROM

table1

[

INNER

|

LEFT

|

RIGHT

|

FULL

]

JOIN

table2ON

table1.column

=

table2.column;INNER

JOIN

表示内连接LEFT

JOIN

表示左联接RIGHTJOIN

表示右连接FULL

JOIN

表示完全连接ON

后跟连接条件连接的语法：连接查询内连接(

INNERJOIN

)

只连接匹配的行select

e.ename,

d.dnamefrom

emp

e

inner

join

dept

don

e.deptno

=

d.deptnoand

e.deptno

=

20

;查询部门编号为“20”的所有雇员姓名及雇员所在部门的名称select

e.ename,

d.dnamefrom

emp

e,

dept

dwhere

e.deptno

=

d.deptnoand

e.deptno

=

20

;另一种写法(相等连接)连接查询左外连接(LEFT

[OUTER]

JOIN)包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行select d.dname,

e.enamefrom

dept

d

left

join

emp

eon

d.deptno

=

e.deptnoand

d.deptno

=

20

;显示所有部门的名称，及部门编号为20的所有雇员名字连接查询右外连接(RIGHT

[OUTER]

JOIN)包含右边表的全部行（不管左边的表中是否存在与它们匹配的行），以及左边表中全部匹配的行select d.dname,

e.enamefrom

dept

d

right

join

emp

eon

d.deptno

=

e.deptno

and

e.deptno

=

20

;显示所有雇员名字，及部门编号为20的雇员所在的部门的名称连接查询完全外连接(FULL[OUTER]

JOIN)包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行。select d.dname,

e.enamefrom

dept

d

full

join

emp

eon

d.deptno

=

e.deptnoand

e.deptno

=

20

;显示部门编号为20的所有雇员名字及对应的部门名称，同时列出其他的雇员名字和部门名称子查询子查询：嵌入在其它SQL语句中的SELECT语句，也叫做嵌套查询子查询又被分为单行子查询多行子查询多列子查询子查询单行子查询：内层子查询只返回单行数据外层查询的WHERE子句中可以使用单行比较运算符(<>、=、>=、<=、>、<)select ename,

deptno

fromempwhere

deptno

=(

select

deptno

from

empwhere

ename=‘BLAKE’

)

;显示“BLAKE”所在部门的所有雇员名字及部门编号子查询多行子查询：内层子查询返回多行数据外层查询的WHERE子句中必须使用多行比较符(IN、ALL、ANY)select ename,

deptno

fromempwhere

deptno

in(

select

deptno

from

empwhere

ename=‘BLAKE’

)

;使用IN显示“BLAKE”所在部门的所有雇员名字及部门编号子查询使用ALL显示比部门编号为20的所有雇员的工资都高的雇员信息select ename,

deptno

fromempwhere

sal

>

all(select

sal

from

emp

where

deptno=20)

;使用ANY显示比部门编号为10的任一雇员的工资高的雇员信息select ename,

deptno

fromempwhere

sal

>

any(select

sal

from

emp

where

deptno=10)

;子查询多列子查询：内层子查询返回多列数据select ename,

job,

sal,

deptno

fromempwhere

(

deptno,

job

)

=(

select

deptno,

job

from

emp

where

ename

=

‘SMITH’

)

;显示与“SMITH”同一部门，同一职务的雇员信息子查询insert

into employee

(

id,

name

)select

empno,

ename

from emp

;INSERT语句中使用子查询update

emp

set

sal

=(select

sal

from

emp

where

ename

=

‘SMITH’)

;UPDATE语句中使用子查询delete

from

emp

where

sal

=(select

sal

from

emp

where

ename

=

‘SMITH’)

;DELETE语句中使用子查询CREATE

TABLE

EMPLOYEE(ID

NUMBER(10)PRIMARY

KEY,NAME

VARCHAR2(10));合并查询合并查询：将两个或多个分结果集进行集合操作得到新的结果集可采用的集合操作符有：UNION(并集，结果总集删除重复记录)UNIION

ALL(并集，结果不删除重复记录)INTERSECT(交集)MINUS(差集)合并查询UNION：获取两个结果集的并集，自动过滤掉重复数据行，按输出结果的第一列排序select

ename,

job,

sal

from

emp

where

sal

>=

2000unionselect

ename,

job,

sal

from

emp

where

job

=‘MANAGER’;查询工资在2000以上，或者职务是“MANAGER”的员工的名字，职务和工资UNION

ALL：与UNIN的区别是它不会过滤掉重复数据行合并查询INTERSECT：获取两个结果集的交集，即显示同时存在于两个结果集中的数据。select

ename,

job,

sal

from

emp

where

sal

>=

2000intersectselect

ename,

job,

sal

from

emp

where

job

=‘MANAGER’;查询工资在2000以上，并且职务是“MANAGER”的员工的名字，职务和工资合并查询MINUS：获取两个结果集的差集。即显示在第一个结果集中存在，第二个结果集中不存在的数据集。select

ename,

job,

sal

from

emp

where

sal

>=

2000minusselect

ename,

job,

sal

from

emp

where

job

=‘MANAGER’;查询工资在2000以上，但职务不是“MANAGER”的员工的名字，职务和工资其它复杂的查询使用CASE表达式select

ename,

sal,case

when

sal

>

3000

then

3when

sal

>

200then

2else

1end

gradefrom

emp

;对雇员表雇员工资进行查询分级对于工资在