sql聚合函数,子查询

概念

聚合函数又称为 : 分组函数 , 多行函数 . 对结果集进行统计的

将结果集中所有的记录按照指定的字段进行统计并最终得到一条结果 .

聚合函数分类 :

MIN: 求最小值

MAX: 求最大值

SUM: 求和

AVG: 求平均数

COUNT: 统计记录数的

MIN,MAX,SUM,AVG 是对值的统计 , 而 COUNT 是对记录数的统计 .

聚合函数忽略 NULL 值 , 尤其在 AVG 和 COUNT 中体现的比较明显 .

要现将参与统计的记录查询出来 , 然后再基于聚合函数对结果集进行统计 .

例

查看老师的平均工资是多少

1.现将参与统计的记录查询出来
查询所有老师的工资分别是多少?
select salary
from teacher
2.在上述SQL的基础上添加聚合函数进行统计
select avg（salary）
from teacher

查看老师的最高工资,最低工资,平均工资和工资总和都是多少?

select max(salary),min(salary),avg(salary),sum(salary)
from teacher

查看负责课程编号 1 的老师的平均工资是多少 ?

1:查询出负责课程编号1的老师工资分别是多少
select salary 
from teacher
where subject_id=1

2:再对salary添加聚合函数求平均值
select avg(salary)
from teacher
where subject_id=1

查看总共多少位老师 ?

1.先将所有老师查询出来
select name from teacher

2:使用COUNT对记录数进行统计
select count(name) from teacher

3.聚合函数忽略NULL值,记录数不全
select count(comm)from teacher

4.DBMS基本都对COUNT(*)进行了优化,因此统计记录数用这个
select conut(*) from teacher

查看所有老师的平均奖金是多少 ?

1.聚合函数忽略NULL值,对于AVG统计结果不精确
select avg(comm),sum(comm)from teacher

2.先将NULL使用IFNULL替换为0,然后再进行统计即可
select avg(ifunll(comm,0))from teacher

查看负责课程编号 2 的老师共多少人 ?

select conut(*)
from teacher
where subject_id=2

查看班级编号 (class_id) 为 1 的学生有多少人 ?

select count(*)
from student 
where class_id=1

查看全校学生生日最大的是哪天 ?

select min(birth)
from student

查看 11 岁的课代表总共多少人 ?

select count(*)
from student
where age=11 and job like '%课代表'

姓张的学生有多少人 ?

select count（*）
from student
where name like ‘张%’

工资高于 5000 的老师中最低工资是多少 ?

select min（salary）
from teacher
where salary>5000

4 层有几个班 ?

select conut(*)
from class
where floor=4

老师中 " 总监 " 的平均工资是多少 ?

select avg(salary)
from techer
where title='总监'

GROUP BY 子句 - 分组

GROUP BY 子句可以将结果集按照指定的字段值相同的记录分组 , 然后在组内根据聚合函数进行统计并得

出结果

GROUP BY 子句是配合聚合函数使用的 , 如果 SELECT 子句中没有聚合函数 , 通常不会使用 GROUP BY

子句

当 SELECT 子句中出现了聚合函数 , 那么不在聚合函数中的其它字段都要出现在 GROUP BY 子句中 .

按照单字段分组

例

每组都统计平均工资,因为按照title分组,所以统计结果可以显示title
select avg（salary），title
from teacher
group by title

将结果集按照 title 字段值相同的记录分组

(每个不同的title中排序salary)每个职称为一组（每组排序工资）

 查看每个班级各多少人?

student 表中 class_id 字段记录了该学生的班级号 . 那么班级号相同的记录就可以看作同一个班的学 生, 统计记录数

select conut(*),class_id
from student 
group by class_id

查看学生每种职位各多少人 , 以及最大生日和最小生日 ?

select count(*),min(birth)'最大生日'，max（birth）‘最小生日’，job
from student
group by job

按照多字段分组

GROUP BY 子句中后面指定多个字段 , 此时会将结果集按照这些字段值组合相同的记录看作一组

查看同班级同性别的学生分别多少人 ?

select count（*），gender，class_id
from student 
group by gender,class_id

结果集中性别相同并且班级号相同的记录会被划分为一组

查看每个班每种职位各多少人 ?

select count(*),job,class_id
from student 
group by job,class_id

按照聚合函数排序

查看每个科目老师的平均工资排名 ?

select avg(salary),subject_id
from teacher
group teacher
group by subject_id
order by avg(salary) desc

好的书写习惯:在SELECT子句中凡是出现了聚合函数,函数或表达式时都应当取别名来增加可读性
并且当我们使用了别名时,在ORDER BY子句中还可以按照该别名排序

select avg(salary) avg_sal,subject_id
from teacher
group by  subject_id
order by avg_sal desc

分组中的过滤条件

问题

查看每个科目老师的平均工资 ? 但是仅查看平局工资高于 6000 的那些

错误

聚合函数不能出现在 WHERE 子句中

select avg(salary),subject_id
from teacher
where avg(salary)>6000
group by subject_id

原因

WHERE 子句的过滤时机 : 在检索表中每条记录时用于过滤使用的 , 在生成结果集时 WHERE 会发挥作用 .

我们希望将统计结果进行过滤 , 这应当实在检索表 (WHERE 发挥作用 ) 之后生成结果集 , 并将结果集分组统计

后才能得到统计结果从而进行过滤 ( 这个过滤时机已经是在 WHERE 之后进行了 ).

SELECT AVG ( salary ) ,subject_id

FROM teacher

GROUP BY subject_id

ORDER BY AVG ( salary ) DESC

好的书写习惯 : 在 SELECT 子句中凡是出现了聚合函数 , 函数或表达式时都应当取别名来增加可读性

并且当我们使用了别名时 , 在 ORDER BY 子句中还可以按照该别名排序

SELECT AVG ( salary ) avg_sal,subject_id

FROM teacher

GROUP BY subject_id

ORDER BY avg_sal DESC

SELECT AVG ( salary ) ,subject_id

FROM teacher

WHERE AVG ( salary ) > 6000

GROUP BY subject_id HAVING 子句

HAVING 与 WHERE 一样都是用来添加过滤条件进行过滤的

他们的区别 :

WHERE 是对记录进行过滤 的 , 而 HAVING 是对分组过滤 的

只有满足 HAVING 要求的分组才会被保留

过滤时机不同

WHERE 是在检索表时进行过滤的 , 产生结果集之前进行过滤的

HAVING 是在产生结果集并根据 GROUP BY 对结果集分组后进行过滤的

HAVING 可以使用聚合函数作为过滤条件 ,WHERE 不可以

数据库对各子句的执行顺序
SELECT AVG(salary),subject_id 4     根据筛选出的分组再进行统计并得到实际的结果集
FROM teacher 1                      确定数据来自哪张表
GROUP BY subject_id 2               确定结果集按照那个字段分组,执行后结果集已经分号组
HAVING AVG(salary)>6000 3           根据HAVING的过滤条件筛选出符合要求的分组

查看每个科目老师的平均工资，前提是该科目老师最高工资要超过 9000

SELECT AVG(salary) avg_sal,subject_id 4    将确定的两个分组按照AVG进行统计并生成结果集
FROM teacher 1                             确定数据来自哪张表
GROUP BY subject_id 2                      确定分组,一共6个分组
HAVING MAX(salary)>9000 3                  仅有2个分组满足过滤要求
ORDER BY avg_sal 5                         对结果集按照指定的字段排序

查看科目老师的工资总和是多少 ? 前提是该科老师的平均奖金要高于 4000.

select sum(salary),subject_id
from teacher
group by subject_id
having avg(ifnull(comm,0))>4000

查看各科目男老师的平均工资是多少 ? 前提是该科目老师最低工资高于 4000.

select avg(salary),subject_id
from teacher
where gender='男'
group by subject_id
having min(salary)>4000

子查询

概念

嵌套在其他 SQL 语句中的一条 DQL 语句被称为子查询

应用场景

DQL 中使用 : 基于一个查询结果集进行二次查询

DML 中使用 : 基于一个查询结果集对表数据进行增 , 删 , 改操作

DDL 中使用 : 基于结果集创建数据库对象 ( 表 , 视图 )

子查询分类

单行单列子查询 : 查询结果集为一个值 . 仅一行一列

多行单列子查询 : 查询结果集为一组数据

多列子查询 : 结果集检索出来是一张表 . 多用于 DDL 语句 .

在 DQL 中使用子查询

单行单列子查询

例

查看比范传奇工资高的老师都有谁 ?

1:未知条件:范传奇的工资是多少?
SELECT salary FROM teacher WHERE name='范传奇'         通过这条DQL可以得知范传奇的工资:3000
2:查看谁的工资高于范传奇的工资?
SELECT name,salary
FROM teacher
WHERE salary>(SELECT salary FROM teacher WHERE name='范传奇') 子查询要用()括起来,语法要求

查看和 ' 李费水 ' 在同一个班的学生都有谁 ?

未知条件:李费水的班级号是多少?
select class_id from student where name='李费水'；



select name，gender,class_id
from student
where class_id=(select class_id from student where name='李费水'；);

查看工资最高的老师的工资和奖金是多少 ?

未知条件:最高工资是多少钱
select max(salsry)from teacher;




select name,salary,comm
from teacher
where salary=(select max(salsry)from teacher;)

多行单列子查询

多行单列子查询会检索出一组数组 , 此时如果作为过滤条件使用时 :

判断相等 , 不能再使用 "=". 任何一个值都不可能同时等于多个值 , 只能等于其中之一 .

因此判断相等时要伴随 IN 使用 :IN( 列表 )

IN 和 NOT IN 都可以使用

判断 >,>=,<,<= 时 , 要伴随 ANY 和 ALL 使用

>ANY( 列表 ): 大于列表中的其中之一即可 . 判断标准 : 大于最小的即可

>ALL( 列表 ): 大于列表中所有 . 判断标准 : 大于最大的

<ANY( 列表 ): 小于列表其中之一 , 小于最大的即可

<ALL( 列表 ): 小于列表所有 , 小于最小的

例

查看与 " 祝雷 " 和 " 李费水 " 在同一个班的学生都有谁 ?

报错 : 子查询返回了多于一行的记录 . 因为等于判断不能同时等于多个值 .

未知条件:这两个人的班级号是多少?
select class_id from student where name in('祝雷'，'李费水')

select name，geder，class_id
from student
where class_id=(select class_id from student where name in('祝雷'，'李费水'))

实际写法 :

select name,gender,class_id
from student
where class_id in(
select class_id
from student
where name in('祝雷'，‘李费水’)
)

查看比教科目 2 和科目 4 老师工资都高的老师都有谁 ?

未知条件:科目2和科目4老师的工资都是多少?
select salary 
from teacher
where subject_id in(2,4)

select name,salary,subject_id
from teacher
where salary>all(
select salary 
from teacher
where subject_id in(2,4))

另一种思路

未知条件

科目2和科目4老师的工资是多少？

#选择  最大      从..起        在哪里           在..内 
select max(salry)from teacher where subject_id in(2,4)

select name,salary,subject_id
from teacher
where salary>(select max(salary)from teacher where subjct_id in (2,4))

另一种思路

未知条件：科目2和科目4老师的最高工资是多少？

select max（salary）from teacher where subjct_id in (2,4)

select name,salary,subject_id
from teacher where salary>(
select max(salary)from teacher where subkect_id in (2,4))

在dml语句中使用子查询

列

  给与范传奇赋值同一科目的所有老师涨500

未知条件：‘范传奇’赋值的科目id？
select subkect_id from teacher where name='范传奇'

基于上述DQL进行DML操作
update teacher 
set salary=salary+500
where subject_id=(
select subject_id from teacher where name='范传奇')

 在ddl语句中使用子查询

以创建表为列，可以将一个查询结果集当做表创建出来

列

。创建一张表，该表中记录了每个科目老师的工资情况，要求展示，最高，最低，总和和平均工资以及该科目的id

select max(salary),min(salary),sum(salary),subject_id
from teacher
group by subject_id

创建表

CREATE TABLE teacher_salary_info(
max_sal DOUBLE(7,2),
min_sal DOUBLE(7,2),
sum_sal DOUBLE(7,2),
avg_sal DOUBLE(7,2),
subject_id INT
)

表创建后还要进行 DML 操作

执行若干次的 INSERT ....

上述操作太麻烦

将子查询的结果集当做表创建出来

select max(salary),min(salary),sum(salary),sum(salary),avg(salary),subject_id
from teacher
group by subject_id

该子查询的结果集就是我们期望的表的样子

那么此时我们可以执行如下 DDL

create table teacher_salary_info
as
select max(salary),min(salary),sum(salary),avg(salary),subject_id
from teacher
group by subject_id

上述 SQL 中生成的表字段名是使用 SELECT 子句中字段名 , 如果 SELECT 中含有函数或表达式时要指定别

名 , 此时生成的

表的字段名会采用该别名 :

create table teacher_salary_info
as
select
    max(salary) max_sal,min(salary) min_sal,
sum(salary) sum_sal,avg(salary) avg_sal,subject_id
from teacher 
group by subject_id