MySQL聚合函数基础

MySQL

一、聚合函数的使用

COUNT()函数
SUM()函数
AVG()函数
MAX()函数
MIN()函数

1、我想知道我们公司有多少员工

select count(*) as 员工人数 from 员工
select concat('共有员工:',count(*),'人') as 员工人数 from 员工
第二种在MySQL中会出现乱码。

2，我想知道我们公司每个月员工的工资开销是多少？

select sum(目前薪资) as 工资总额 from 员工

3，我想知道我们公司员工的平均薪水如何？

select avg(目前薪资) as 平均薪水 from 员工

4，我想知道我们公司员工的最高工资是多少？

select max(目前薪资) as 最高工资 from 员工

5，我想知道我们公司员工的最低工资是多少？

select min(目前薪资) as 最低工资 from 员工

6，我想知道公司最富有的员工是谁？

select * from 员工 where 目前薪资=(select max(目前薪资) from 员工)

7，我想知道公司最穷的员工是谁？

select * from 员工 where 目前薪资=(select min(目前薪资) from 员工)

8，我想知道公司哪些员工的工资低于公司工资平均水平？

select 姓名,目前薪资 from 员工 where 目前薪资<(select avg(目前薪资)  from 员工)

9,查询出比生产制造部最高工资还要高的其他部门员工信息。

select 姓名,目前薪资,部门 from 员工 where 目前薪资>=(select max(目前薪资) as 生产制造部的最高工资 from 员工 where 部门='生产制造部') and 部门!='生产制造部'
第二种方法
select 姓名,目前薪资,部门 from 员工  where 目前薪资>=(
select cast(max(目前薪资) as UNSIGNED) from 员工 where  部门='生产制造部') and 部门!='生产制造部'

分组的练习

Select select_list
From table_name
Where condition_expression
Group by group_columns
having condition_expression
Order by sort_columns

10、统计出我们公司男女员工的人数

select 部门,
case 性别
when -1 then'女'
when 0 then '男'
end as 性别,count(*) as 人数 from 员工 group by 部门,性别

11，统计公司各部门员工的人数

 select 部门,count(部门) as 人数 from 员工 group by 部门

12，统计公司各部门员工的人数,最高工资，最低工资，平均工资，工资总额

12，统计公司各部门员工的人数,最高工资，最低工资，平均工资，工资总额
 select 部门,count(部门) as 人数,max(目前薪资) as 部门最高工资,
 min(目前薪资) as 部门最低工资,avg(目前薪资) as 平均工资, sum(目前薪资) as 工资总额 from 员工 group by 部门

13，统计公司各部门员工的男女人数

select 姓名,部门,case 性别
      when -1 then '女'
      when 0 then '男'
end as 性别,count(*) as 人数 from 员工 group by 部门,性别

14，查询部门人数超过６６０人的大部门

select 部门,count(*) as 人数 from 员工 group by 部门 having count(*) >660
第二种
select 部门,count(*) as 人数 from 员工 group by 部门 having 人数>660

15，统计出生产制造部员工的男女人数

select 部门,性别,count(*) as 人数  from 员工 where 部门='生产制造部' group by 性别

16，统计出生产制造部员工的女员工人数

select 部门,性别,count(*) as 人数  from 员工 where 部门='生产制造部' group by 性别
having 性别=-1
第二种
select 部门,case 性别
      when -1 then '女'
      when 0 then '男'
end as sex,count(*) as 人数  from 员工 where 部门='生产制造部' group by 性别
having sex='女' 

Order by的用法

17，将生产制造部员工的工资按升序排列

select 姓名,部门,目前薪资 from  员工 order by 目前薪资 asc
因为MySQL中的默认排序就是升序所以可以不用写
select 姓名,部门,目前薪资 from  员工 order by 目前薪资

18，将生产制造部员工的工资按降序排列

select 姓名,部门,目前薪资 from  员工 where 部门='生产制造部' order by 目前薪资 desc

19，将员工按目前薪资升序同时按雇佣日期降序来进行排列

select 姓名,目前薪资,雇佣日期 from 员工 order by 目前薪资 asc,雇佣日期 desc

课堂练习相对复杂一点的语句：

 select 部门,count(部门) as 人数,max(目前薪资) as 部门最高工资,
 min(目前薪资) as 部门最低工资,avg(目前薪资) as 平均工资, sum(目前薪资) as 工资总额 from 员工 group by 部门
order by 人数 desc

20，将生产制造部的员按年龄进行降序排列

select  姓名,出生日期,year(now())-year(出生日期) as 年龄 from 员工 where 部门='生产制造部' order by 年龄 desc
第二种
select  姓名,出生日期 from 员工 where 部门='生产制造部' order by 出生日期 asc

21，查询出生产制造部年龄最大的员工信息

select  姓名,出生日期,year(now())-year(出生日期) as 年龄 from 员工 where 部门='生产制造部' order by 年龄 desc limit 1

22,查询出生产制造部中工资最高的员工信息（两种写法）

select 姓名,部门,目前薪资 from 员工 where 部门='生产制造部' order by 目前薪资 desc limit 1
第二种
select * from 员工 where 部门='生产制造部'  and 目前薪资='生产制造部部门最高工资'

–生产制造部部门最高工资

select max(目前薪资) from 员工 where  部门='生产制造部' 
select * from 员工 where 部门='生产制造部'  and 目前薪资=(select max(目前薪资) from 员工 where  部门='生产制造部' )

select max(目前薪资) from 员工 where  部门='生产制造部' 

表联结查询

SELECT 字段列表
FROM 表1 INNER|LEFT|RIGHT JOIN 表2
ON 表1.字段 = 表2.字段;

select a.id,a.username,a.mobile,b.name from easybuy_user a inner join easybuy_usertype b
on a.type= b.id

以下写法也可以，旦不建议：

select b.name,a.id,a.username,a.mobile from easybuy_usertype b inner join easybuy_user a
on  b.id=a.type

第二种写法

select a.id,a.username,a.mobile,b.name from easybuy_user a,easybuy_usertype b where a.type=b.id

三张表的联合查询

select a.id,a.username,a.mobile,b.name,c.address from easybuy_user a inner join easybuy_usertype b 
on a.type= b.id inner join easybuy_user_address c on a.id=c.userid

第二种写法

select a.id,a.username,a.mobile,b.name,c.address from easybuy_user a,easybuy_usertype b,easybuy_user_address c 
where a.type=b.id and a.id=c.userid

外连结——左连结：以左表为主进行匹配查询

select a.id,a.username,a.mobile,b.address from easybuy_user a left join easybuy_user_address b on a.id=b.userid

外连结——右连结：因为如同字面意识它是以右面为主导进而查询表

select a.id,a.username,a.mobile,b.address from  easybuy_user_address b right join easybuy_user a  on a.id=b.userid

第二种

ppt作业

1. 单分数最高的人和单分数最低的人。

select max(score) as 最高分,min(score) as 最低分 from t1

2. 两门分数加起来的第2至5名

select name,sum(score)两门课程分数 from t1 group by name order by 两门课程分数 desc limit 1,4

3. 两门总分数在150分以下的人。

   select name,sum(score)两门课程分数 from t1 group by name having 两门课程分数<150

4. 两门平均分数介于60和80的人。

   select name,avg(score)两门课程分数 from t1 group by name having 两门课程分数>60 and 两门课程分数<80

5.总分大于150分，平均分小于90分的人数。

select count(distinct(name)) as 人数 from t1 group by name having sum(score)>150 and avg(score)<90

6. 总分大于150分，平均分小于90分的人数有几个。

   select name , sum(score) as 总分, avg(score) as 平均分 from t1 group by name having 总分>150 and 平均分<90

5.总分大于150分，平均分小于90分的人数。

select count(distinct(name)) as 人数 from t1 group by name having sum(score)>150 and avg(score)<90

6. 总分大于150分，平均分小于90分的人数有几个。

   select name , sum(score) as 总分, avg(score) as 平均分 from t1 group by name having 总分>150 and 平均分<90