2025.4.10 课堂笔记

2.分组查询

2.1语法格式

select 列名1 别名1,列名2 别名2,...

from 表名1 别名1 join 表名2 别名2 on 多表之间连接条件 join 表名3 别名3 on 连接条件...

where 查询条件

group by 分组字段

having 分组条件

order by 排序字段 asc|desc,排序字段2 asc|desc,.....

提示：使用分组查询的条件：

查询中出现了每个，各个，按年度，按季度等字眼，或有此类意思，每个“谁”就group by“谁”

查询结果中有最大，最小，平均，计数，求和这五种聚合函数的字段

例：

查询每个部门的平均工资

select deptno 部门编号,round(avg(sal),2) 平均工资

from emp

group by deptno;

查询部门名称和每个部门的平均工资

select d.dname 部门名称,round(avg(e.sal),2) 平均工资

from dept d,emp e

where d.deptno=e.deptno

group by e.deptno;

注：在Mysql中分组查询时可以查询出分组字段以外的其他字段，而oracle不行

建议在查询中指定分组字段

查询部门的名称以及每个部门员工的数量

select d.dname 部门名称,count(e.empno)

from dept d left join emp e on d.deptno=e.deptno

group by d.dname;

例：查询平均工资大于2000的部门编号和平均工资

select round(avg(sal),2) as 平均工资,deptno 部门编号

from emp

group by deptno

having 平均工资 >2000;

语句执行条件顺序：1.from 2.where 3,group by 4,select ...

例：查询出非销售人员的职位名单，以及从事同一工作的雇员的月工资总和，并且满足工资总和大于5000，查询的结果按月工资综合的升序进行排序

select job as 职位名单,sum(sal) as 月工资总和

from emp

where job !='salesman'

group by job

having 月工资总和>5000

order by 月工资总和;

查询部门平均工资最大值

select max(avg(sal))

from emp

group by deptno; 报错MySQL中不支持聚合函数的嵌套，在MySQL中聚合函数的嵌套使用，而Oracle中可以

select max(temp.平均工资)

from (select avg(sal) 平均工资 from emp group by deptno)

八，子查询

1.简介

一个查询中嵌套着另一个查询，称为子查询

子查询必须放在小括号中

子查询可以出现在任意位置，如select，from，where，having等

2.基本用法

2.1 语法

select （子查询）

from（子查询）别名

where （子查询）

group by 分组字段

having（子查询）

2.2 示例

例：查询工资比编号7566多的雇员信息

(1)

select e1.*,e2.empno,e2.sal

from emp e1,emp e2

where e2.empno=7566 and e1.sal>e2.sal;

(2)

select *

from emp

where sal>(select sal 工资标准 from emp where empno = 7566);

例：查询工资比部门编号30员工工资高的雇员员工

select *

from emp

where sal>(select sal from emp where deptno=30);

报错：将子查询与比较运算法一起使用时，比必须保证子查询返回的结果不能多于一个。

例：查询雇员编号，姓名，部门名称

(1)select e.empno,e.ename,d.dname

from emp e,dept d

where e.deptno=d.deptno;

(2)select empno,ename,(select dname from dept where deptno=e.deptno)

from emp e;

• 一般来说，多表连接查询可以使用子查询替换，但是有的子查询不能使用多表连接查询来替换

• 子查询特点：灵活，方便，一般作为增，删，改，查查询操作的条件，适合于操作一个表的数据

• 多表连接查询更适合于查看表中的内容

3.子查询分类

可以分为三类

• 单列子查询

返回单行单列，使用频率较高

• 多行子查询

返回多行单列

• 多列子查询

返回单行多列或多列多行

3.1单列子查询

例：查询工资比员工编号7564高，同时与员工编号7900从事相同的工作的雇员信息

select *

from emp

where sal>(select sal from emp where empno = 7564) and job=(select job from emp where empno = 7900);