第7章高级查询(6课时)

目标

聚合函数

分组函数

子查询

多行子查询

引出

•请思考如下问题？

–查询所有员工的每个月工资总和，平均工资？

–查询工资最高和最低的工资是多少？

–查询公司的总人数？

-查询有奖金的总人数？

聚合函数

•分组函数是对数据行的集合进行操作并按组给出一个结果，这个结果可直接输出，或者用来做判断条件。

针对多行数据进行操作多行函数

1) 最大值 max(参数)

2）最小值min(参数)

3）平均值avg(参数)

4）求和sum(参数)

5）总条数count(参数)

分组函数可以写到select having orderby子句中

例：#求10号部门工资最高和最低的员工的工资

SELECT max(sal),min(sal)

from emp

where deptno=10;

#查询员工最早的入职时间和最晚入职时间

SELECT min(hiredate),max(hiredate)

from emp;

#查询20号部门的平均工资

select avg(sal)

from emp

where deptno=20;

#查询20号部门所有员工每个月的工资总和

select sum(sal)

from emp

where deptno=20;

#查询总人数

select count(empno)

from emp;

#查询有奖金的总人数

select count(empno)

from emp

where comm is not null;

空值问题

#count(*)不忽略空值，其他情况忽略空值

select count(comm)

from emp;

select count(*)

from emp;

求和或求平均值时有null

ifnull(字段名，0）

#上面的查询只汇总了COMM不为null的情况，错误

#要解决上述问题，要利用ifnull()函数

select avg(ifnull(comm,0))

from emp;

练习1：

•1.查询部门20的员工，每个月的工资总和及平均工资。

SELECT SUM(sal),avg(sal)

from emp

where deptno=20;

•2.查询工作在CHICAGO的员工人数，最高工资及最低工资。

SELECT count(empno),max(e.sal),min(e.sal)

from emp e,dept d

where e.deptno=d.deptno and loc="CHICAGO"

SELECT max(sal),min(sal),count(*) from emp NATURAL join dept

where loc='CHICAGO'

•3.查询员工表中一共有几种岗位类型。

select COUNT(DISTINCT job)

from emp;

分组函数

•引出：求各部门平均工资，按照部门进行分组

group by（根据什么进行分组一般与聚合函数配合使用）写在where子句后，没有where就写在from子句后

•*****在SELECT列表中除了分组函数那些项，所有列都必须包含在GROUP BY 子句中。

这句话在oracle中是正确的，在mysql中并没有这个限制，

但这个写法不标准, 因为假如没group的字段如果有多个值, 可能导致查询结果错误

select deptno,job,sum(sal)

from emp

group by deptno;

虽然不报错，但结果并不是按部门和岗位分组的

正确的写法：

select deptno,job,sum(sal)

from emp

group by deptno,job;

下例可以省略dname,因为deptno和DNAME是一一对应的

select d.deptno,d.DNAME,

count(empno),max(sal),min(sal),sum(sal),avg(sal)

from emp e,dept d

where e.DEPTNO=d.DEPTNO

group by d.deptno,d.dname;

select d.deptno,d.DNAME,

count(empno),max(sal),min(sal),sum(sal),avg(sal)

from emp e,dept d

where e.DEPTNO=d.DEPTNO

group by d.deptno;

•GROUP BY 所指定的列并不是必须出现在SELECT 列表中。

select deptno,avg(sal)

from emp

group by deptno;

#查询部门名称，部门编号，各部门平均工资，按照部门进行分组

select deptno,dname,avg(sal)

from emp

natural join dept

group by deptno;

select emp.deptno,dname,avg(sal)

from emp,dept

where emp.deptno=dept.deptno

group by emp.deptno;

having子句

#查询部门名称，部门编号，各部门平均工资，按照部门进行分组,并且平均工资大于2000

select emp.deptno,dname,avg(sal)

from emp,dept

where emp.deptno=dept.deptno and avg(sal)>2000

group by emp.deptno;

上面的写法是错误的

错在聚合函数不能用在where子句中。

如果条件里面带有聚合函数，要用having子句，having子句写在group by后面

总结一下：普通条件写在where子句中，带sum,max,min,count,avg函数的条件写到having子句中。

把上面的语句改造下

select emp.deptno,dname,avg(sal)

from emp,dept

where emp.deptno=dept.deptno

group by emp.deptno

having avg(sal)>2000;

执行顺序*****

#书写顺序 select--from --where--group by--having-- order BY

执行顺序： from--where-group by -having-select-order by

按多列进行分组

根据多列分组时，group by 子句中各列之间用逗号分隔

#查询每个部门每个岗位的工资总和

select deptno 部门编号,job 岗位,sum(sal)

from emp

group by deptno,job;

练习2

1.查询每个部门的部门编号，部门名称，部门人数，最高工资，最低工资，工资总和，平均工资。

select d.deptno,dname,COUNT(e.deptno) 部门人数,max(sal) 最高工资,min(sal) 最低工资,sum(sal) 工资总和,avg(sal) 平均工资

FROM emp e,dept d

where e.deptno=d.deptno

group by e.deptno

2.查询每个部门，每个岗位的部门编号，部门名称，岗位名称，部门人数，最高工资，最低工资，工资总和，平均工资。

SELECT d.deptno,d.dname,e.job,COUNT(e.deptno),MAX(e.sal),MIN(e.sal),SUM(e.sal),avg(e.sal)

from dept d,emp e

where d.deptno=e.deptno

GROUP BY e.deptno,e.job;

3.查询每个经理所管理的人数，经理编号，经理姓名。

select e.mgr,m.ename,count(e.empno) 管理的人数

from emp e,emp m

where e.mgr= m.empno

group by e.mgr;

改造成：

3.查询每个经理所管理的人数，经理编号，经理姓名,要求包括没有经理的人员信息。

select e.mgr,m.ename,count(e.empno) 管理的人数

from emp e

left outer join emp m

on e.mgr=m.empno

group by e.mgr,m.ename;

作业：

练习3

•1.查询部门人数大于2的部门编号，部门名称，部门人数。(having count )

•2.查询部门平均工资大于2000，且人数大于2的部门编号，部门名称，部门人数，部门平均工资，并按照部门人数升序排序。(having count ,avg order by,asc)

课后作业前5题。

预习第2章

子查询概述

子查询简单的理解就是在where子句，having子句或者from子句中写查询语句（select 子句）

如果from子句中相当于利用查询语句产生了一个临时表

如果放在where 子句当中的，用于条件判断的，并且无聚合函数的

如果having子句当中的，也是用于条件判断的，但是条件里包含聚合函数的。

–思考如下问题？

•1）查询工资比Jones工资高的员工信息？

#第一步Jones的工资多高？----->得到他的工资

select sal

from emp

where ename='JONES'

#第二步，查询比他高的。(where 子句中用)

SELECT *

FROM EMP

WHERE SAL>(select sal

from emp

where ename='JONES');

2)查询工资最低的员工姓名

分两步：第一步，查询最低的工资是多少

select min(sal)

from emp;

800

第二步，查询工资等于800的员工的信息

select *

from emp

where sal=(select min(sal)

from emp);

子查询书写注意事项

•子查询可以嵌于以下SQL子句中：

–WHERE子句

–HAVING子句

–FROM子句

–子查询要用括号括起来

–将子查询放在比较运算符的右边

–对于单行子查询要使用单行运算符

–对于多行子查询要使用多行运算符

#查询工资比Jones工资高的员工信息

子查询分类

根据子查询返回的行和列数量,分类单行，多行，多列

单行（子查询只返回一行一列 <.> = <= >= <>）

#显示和雇员7369从事相同工作并且工资大于雇员7876的雇员的姓名和工作。(where 子句中用子查询)

#查询工资最低的员工姓名,岗位及工资(where 子句中用子查询)

having子句中使用子查询：

#查询部门最低工资比20部门最低工资高的部门编号及最低工资

练习4

•1.查询入职日期最早的员工姓名，入职日期

先查询最早的入职日期

select min(hiredate)

from emp;

再查这个日期入职的人

select *

from emp

where hiredate=(select min(hiredate)

from emp);

•2.查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名，工资，部门名称

第一步先查询smith的工资

select sal

from emp

where ename='SMITH'

第二步查询比该工资（800）高的并且工作地点在CHICAGO的

select ename,sal,dname

from emp,dept

where emp.deptno=dept.deptno

and sal>(select sal from emp where ename='SMITH')

and loc='CHICAGO';

•3.查询入职日期比20部门入职日期最早的员工还要早的员工姓名，入职日期

第一步查询20部门入职日期最早的员工的入职日期

select min(hiredate)

from emp

where deptno=20

第二步查询入职日期比刚才的日期小的员工姓名，入职日期

select ename,hiredate

from emp

where hiredate<(select min(hiredate)

from emp

where deptno=20);

•4.查询部门人数大于所有部门平均人数的的部门编号，部门名称，部门人数

第1步：所有部门平均人数

所有部门的平均人数=总人数/部门个数=14/3

select count(empno)/COUNT(DISTINCT deptno)

from emp;

第2步：查询部门人数大于刚才计算结果的的部门编号，部门名称，部门人数

查询各部门人数

select emp.deptno,count(*) ct,dname

from emp,dept

where emp.deptno=dept.deptno

group by emp.deptno,dname

having ct>(select count(empno)/COUNT(DISTINCT deptno)

from emp)

在 FROM 子句中使用子查询

#查询比自己部门平均工资高的员工姓名，工资，部门编号，部门平均工资

emp表里:empno deptno

需要别外一个表：deptno avgsal

第一步:分组查询各部门及其平均工资

select deptno,avg(sal) avgsal

from emp

group by deptno

第二步：与员工表关联，查询结果

SELECT emp.ename,emp.sal,emp.deptno,b.avgsal

from emp,(select deptno,avg(sal) avgsal

from emp

group by deptno) b

where emp.deptno=b.deptno

and emp.sal>b.avgsal;

如果没有员工的部门也参与运算：

select e.deptno,d.dname,count(e.empno)

from emp e,dept d

where e.deptno=d.deptno

group by e.deptno,d.dname

having count(e.empno)>(

(SELECT COUNT(EMPNO) FROM emp)/(select count(DISTINCT deptno) from dept)

)

多行子查询

–多行操作符包括：

•IN

•ANY

ALL

引出：

–查询是经理的员工姓名，工资

select empno,ename,sal

from emp

where empno in (select mgr from emp);

any

•= ANY：表示等于子查询结果中的任意一个，即等于谁都可以，相当于IN。

查询是经理的员工姓名，工资用另外一种方式=any 相当于in

select empno,ename,sal,mgr

from emp

where empno=any(select mgr from emp);

查询比10号部门任意一个人的工资高的员工的信息

select *

from emp

where sal>any(select sal from emp where deptno=10)

查询比10号部门任意一个人的工资低的员工的信息

select *

from emp

where sal<any(select sal from emp where deptno=10)

–查询是经理的员工姓名，工资。

–查询部门编号不为10，且工资比10部门任意一名员工工资高的员工编号，姓名，职位，工资。

all

–查询部门编号不为20，且工资比20部门所有员工工资高的员工编号，姓名，职位，工资。

select empno,ename,job,sal

from emp

where deptno<>20

and sal>all(select sal from emp where deptno=20);

–查询部门编号不为10，且工资和10部门所有员工工资相等的员工编号，姓名，职位，工资。

SELECT empno,ename,job,sal

from emp

where deptno<>10

and sal=all(select sal from emp where deptno=10);

练习5

•1.查询入职日期比10部门任意一个员工晚的员工姓名、入职日期，不包括10部门员工

SELECT ENAME,HIREDATE

FROM emp

WHERE HIREDATE> ANY(SELECT HIREDATE FROM emp WHERE DEPTNO=10)

AND DEPTNO !=10

•2.查询入职日期比10部门所有员工晚的员工姓名、入职日期，不包括10部门员工

SELECT ename,HIREDATE

FROM emp

WHERE HIREDATE > ALL(SELECT HIREDATE FROM emp WHERE DEPTNO=10)

AND DEPTNO<>10

•3.查询职位和10部门任意一个员工职位相同的员工姓名，职位，不包括10部门员工

SELECT ename,JOB

from emp

where job=any(select job from emp where deptno=10)

and deptno!=10

子查询中的空值

查询不是经理的员工姓名。

SELECT ename

FROM emp

WHERE empno NOT IN

(SELECT mgr

FROM emp);

因为子查询的结果中有一条空值，这条空值导致主查询没有记录返回。这是因为所有的条件和空值比较结果都是空值。因此无论什么时候只要空值有可能成为子查询结果集合中的一部分，就不能使用NOT IN 运算符

select enamefrom empwhere empno not in(select mgr from emp where mgr is not null);

SELECT ename

FROM emp

WHERE empno NOT IN

(SELECT mgr

FROM emp where mgr is not null);

在 FROM 子句中使用子查询

相当于生成一个虚拟表

•查询比自己部门平均工资高的员工姓名，工资，部门编号，部门平均工资

SELECT a.ename, a.sal, a.deptno, b.salavg

FROM empa, (SELECT deptno, avg(sal) salavg

FROM emp

GROUP BYdeptno) b

WHERE a.deptno = b.deptno

AND a.sal >b.salavg;

作业

课后练习18道

-- 1.查询部门平均工资在2500元以上的部门名称及平均工资。

select dname,avg(sal)

from emp

join dept

on emp.deptno = dept.DEPTNO

group by dname

having avg(sal)>2500

-- 2.查询员工岗位中不是以“SA”开头并且平均工资在2500元以上的岗位及平均工资，并按平均工资降序排序。

-- select job,avg(sal)

-- from emp

-- where job not like 'SA%'

-- group by JOB

-- having avg(sal) >2500

-- order by avg(sal) desc

-- 3.查询部门人数在2人以上的部门名称、最低工资、最高工资,并对求得的工资进行四舍五入到整数位。

-- select dname,min(round(sal,0)),max(round(sal,0))

-- from emp

-- join dept

-- on emp.deptno = dept.deptno

-- group by dname

-- having count(*)>2

-- 4.查询岗位不为SALESMAN，工资和大于等于2500的岗位及每种岗位的工资和。

-- select job,sum(sal)

-- from emp

-- where job <> 'SALESMAN'

-- group by JOB

-- having sum(sal) >= 2500

-- 5.显示经理号码和经理姓名，这个经理所管理员工的最低工资，没有经理的KING也要显示，不包括最低工资小于3000的，按最低工资由高到低排序。

select m.empno,m.ename,min(e.sal)

from emp e

left join emp m

on e.mgr = m.empno

group by m.empno,m.ENAME

having min(e.sal)>=3000

order by min(e.sal) desc

-- 6.查询工资高于编号为7782的员工工资，并且和7369号员工从事相同工作的员工的编号、姓名及工资。

-- select empno,ename,sal

-- from emp

-- where sal>(select sal

-- from emp

-- where empno = 7782)

-- and job = (select JOB

-- from emp

-- where empno = 7369)

-- 7.查询工资最高的员工姓名和工资。

-- select ename,sal

-- from emp

-- where sal = (select max(sal)

-- from emp)

-- 8.查询部门最低工资高于10号部门最低工资的部门的编号、名称及部门最低工资。

-- select emp.deptno,dname,min(sal)

-- from emp

-- join dept

-- on emp.deptno = dept.deptno

-- group by emp.deptno,dname

-- having min(sal) > (select min(sal)

-- from emp

-- where deptno = 10)

-- 9.查询员工工资为其部门最低工资的员工的编号和姓名及工资。

-- select empno,ename,sal

-- from emp,(select deptno,min(sal) minsal

-- from emp

-- group by deptno) t1

-- where emp.deptno = t1.deptno

-- and emp.sal = t1.minsal

-- select empno,ename,sal

-- from emp

-- where (deptno,sal) in(select deptno,min(sal)

-- from emp

-- group by deptno)

-- select empno,ename,sal

-- from emp t1

-- where sal = (select min(sal)

-- from emp

-- where deptno = t1.deptno)

-- 10.显示经理是KING的员工姓名，工资。

-- select e.ename,e.sal

-- from emp e,emp m

-- where e.mgr = m.empno

-- and m.ename = 'KING'

-- select ename,sal

-- from emp

-- where mgr = (select EMPNO

-- from emp

-- where ename = 'KING')

-- 11.显示比员工SMITH参加工作时间晚的员工姓名，工资，参加工作时间。

-- select ename,sal,hiredate

-- from emp

-- where hiredate > (select HIREDATE

-- from emp

-- where ename = 'SMITH')

-- 12.使用子查询的方式查询哪些职员在NEW YORK工作。

-- select *

-- from emp

-- where deptno in(select DEPTNO

-- from dept

-- where loc = 'NEW YORK')

-- 13.写一个查询显示和员工SMITH工作在同一个部门的员工姓名，雇用日期，查询结果中排除SMITH。

-- select ename,hiredate

-- from emp

-- where deptno = (select deptno

-- from emp

-- where ename = 'SMITH')

-- and ename <> 'SMITH'

-- 14.写一个查询显示其工资比全体职员平均工资高的员工编号、姓名。

-- select empno,ename

-- from emp

-- where sal > (select avg(sal)

-- from emp)

-- 15. 显示部门名称和人数

-- select dname,c

-- from dept,(select deptno,count(*) c

-- from emp

-- group by deptno) t1

-- where dept.deptno = t1.deptno

select dname,count(*)

-- from emp,dept

-- where emp.deptno = dept.DEPTNO

-- group by dname

-- select dname,(select count(*)

-- from emp

-- where deptno = d.deptno)

-- from dept d

-- 16. 显示每个部门的最高工资的员工

-- select *

-- from emp,(select deptno,max(sal) maxsal

-- from emp

-- group by deptno) t

-- where emp.deptno = t.deptno

-- and emp.sal = t.maxsal

-- 17. 显示出和员工号7369部门相同的员工姓名，工资

-- select ename,sal

-- from emp

-- where deptno = (select deptno

-- from emp

-- where empno = 7369)

-- 18. 显示出和姓名中包含“W”的员工相同部门的员工姓名

-- select ename

-- from emp

-- where deptno in (select DEPTNO

-- from emp

-- where ename like '%W%')

-- insert into i values('gej')

-- select length(k)

-- from i

create table dept(

deptno int(4) primary key,

dname varchar(10),

loc varchar(10)

)engine = innodb

create table emp(

empno int(4) primary key,

ename varchar(10),

deptno int(4),

constraint emp_dept_fk foreign key(deptno) references dept(deptno)

)engine = innodb

-- constraint tname1_tname_fk foreign key(cname1) references tname(cname)

create or replace view view_20

select empno,deptno from emp

create index emp_ename_index on emp(ename)

作业：

练习4

1.查询入职日期最早的员工姓名，入职日期

select ename,hiredate

from emp

where hiredate = (select min(hiredate)

from emp

)

2.查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名，工资，部门名称

select ename,sal,dname

from emp

join dept

on emp.deptno = dept.deptno

where loc = 'CHICAGO'

and sal >(

select sal

from emp

where ename = 'SMITH')

3.查询入职日期比20部门入职日期最早的员工还要早的员工姓名，入职日期

select ename,hiredate

from emp

where hiredate<

(select min(hiredate) from emp where deptno=20)

4.查询部门人数大于所有部门平均人数的的部门编号，部门名称，部门人数

select emp.deptno,dname,count(empno)

from emp

join dept

on emp.deptno=dept.deptno

group by emp.deptno,dname

having count(empno)>(select count(empno)/count(distinct emp.deptno)

from emp)

select emp.deptno,dname,count(*)

from emp

join dept

on emp.deptno = dept.deptno

group by emp.deptno,dname

having count(*)>(select avg(c)

from (select count(*) c

from emp

group by deptno) t)

最后编辑于：2020.08.03 14:07:02

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 194,457评论 5赞 459
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 81,837评论 2赞 371
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 141,696评论 0赞 319
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 52,183评论 1赞 263
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 61,057评论 4赞 355
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 46,105评论 1赞 272
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 36,520评论 3赞 381
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 35,211评论 0赞 253
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 39,482评论 1赞 290
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 34,574评论 2赞 309
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 36,353评论 1赞 326
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 32,213评论 3赞 312
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 37,576评论 3赞 298
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 28,897评论 0赞 17
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 30,174评论 1赞 250
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 41,489评论 2赞 341
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 40,683评论 2赞 335

第7章 高级查询(6课时)

目标

引出

聚合函数

求和或求平均值时有null

分组函数

having子句

执行顺序*****

按多列进行分组

子查询概述

子查询书写注意事项

多行子查询

select enamefrom empwhere empno not in(select mgr from emp where mgr is not null);

作业

课后练习18道

推荐阅读更多精彩内容

第7章高级查询(6课时)