聚合函数

- 创建数据库
CREATE DATABASE my4;
-- 引用数据库
USE my3;
DROP DATABASE m4;
-- 删除数据库
DROP DATABASE my4;


-- 删除表
DROP TABLE t_student;


CREATE TABLE t_student(
id INT PRIMARY KEY AUTO_INCREMENT,
studentName VARCHAR(20),
grade INT
)CHARSET=utf8;


INSERT INTO t_student(studentName,grade) VALUES('张三',90),('李四',83),('王五',40),('赵六',50);
INSERT INTO t_student(studentName) VALUES ('田七');
SELECT*FROM t_student;


-- 统计学生总数 count(*)表示统计所有记录，count(列名)只统计非空列
SELECT COUNT(*) FROM t_student;
SELECT COUNT(grade) FROM t_student;




-- 统计总分
SELECT SUM(grade) FROM t_student;




-- 统计平均分
SELECT AVG(grade) FROM t_student;


-- 显示该班最高分、最低分
SELECT MAX(grade),MIN(grade) FROM t_student;


-- 显示最高分是谁
-- 在where 条件中不能使用聚合函数
SELECT*FROM t_student ORDER BY grade DESC LIMIT 0,1;






--  -----------------------------------------------------

CREATE TABLE t_employee(
id INT PRIMARY KEY AUTO_INCREMENT,
employeeName VARCHAR(20),
money INT,
deptName VARCHAR(20)
)CHARSET=utf8;


INSERT INTO t_employee(employeeName,money,deptName) VALUES('张三',3000,'销售部');
INSERT INTO t_employee(employeeName,money,deptName) VALUES('李四',8000,'行政部');
INSERT INTO t_employee(employeeName,money,deptName) VALUES('张翠山',6000,'销售部');
INSERT INTO t_employee(employeeName,money,deptName) VALUES('李源',4000,'市场部');
INSERT INTO t_employee(employeeName,money,deptName) VALUES('李世民',5000,'财务部');
INSERT INTO t_employee(employeeName,money,deptName) VALUES('张合',3500,'企划部');
INSERT INTO t_employee(employeeName,money,deptName) VALUES('王语嫣',3500,'销售部');
INSERT INTO t_employee(employeeName,money,deptName) VALUES('刘文彩',2300,'行政部');
INSERT INTO t_employee(employeeName,money,deptName) VALUES('万华',4000,'财务部');
INSERT INTO t_employee(employeeName,money,deptName) VALUES('周董',3000,'企划部');


SELECT*FROM t_employee;
DROP TABLE t_employee;


-- 统计每个部门有多少人
SELECT deptName,COUNT(*) num FROM t_employee GROUP BY deptName;
-- 在分组查询中，查询字段只能是聚合函数和分组函数
-- 统计各部门最高工资
SELECT deptName, MAX(money) FROM t_employee  GROUP BY deptName;
-- 统计各部门工资在5000以下的人数
 SELECT deptName,COUNT(money) FROM t_employee WHERE money<5000 GROUP BY deptName;
 -- 统计各部门的平均工资
 SELECT e.deptName,AVG(money) FROM t_employee e GROUP BY e.deptName;
 
 
 -- 统计平均工资在3000以上的部门
 -- having 是对分组的结果再进行过滤，所有having条件中可以加入聚合函数
 SELECT deptName,AVG(money) FROM t_employee e GROUP BY deptName HAVING AVG(money)>3000;
 
 -- 统计每个部门中工资在3000以上的部门人数
 SELECT  deptName,COUNT(money) FROM t_employee WHERE money>3000 GROUP BY deptName;
 SELECT deptName,COUNT(money) FROM t_employee e WHERE e.money>3000 GROUP BY deptName;
 
 -- 查询部门人数至少是2个的部门
 SELECT deptName,COUNT(*) FROM t_employee e GROUP BY deptName HAVING COUNT(*)>=2;
 
 -- 按部门平均工资降序显示部门
 SELECT deptName,AVG(money) FROM t_employee GROUP BY deptName ORDER BY AVG(money) DESC;