sql聚合函数,子查询

例

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

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='总监'

例

每组都统计平均工资,因为按照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

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

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_idHAVING子句

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

例

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

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;)

例

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

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

未知条件:这两个人的班级号是多少?
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))

  给与范传奇赋值同一科目的所有老师涨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