MySQL—统计函数和数学函数以及GROUP BY配合HAVING

 合计/统计函数

count

-- 演示 mysql 的统计函数的使用
-- 统计一个班级共有多少学生？
SELECT COUNT(*) FROM student
-- 统计数学成绩大于 90 的学生有多少个？
SELECT COUNT(*) FROM student
WHERE math > 90
-- 统计总分大于 250 的人数有多少？
SELECT COUNT(*) FROM student
WHERE (math + english + chinese) > 250
-- count(*) 和 count(列) 的区别
解释 :count(*) 返回满足条件的记录的行数
count(列): 统计满足条件的某列有多少个，但是会排除 为 null 的情况
CREATE TABLE t15 (
`name` VARCHAR(20));
INSERT INTO t15 VALUES('tom');
INSERT INTO t15 VALUES('jack');
INSERT INTO t15 VALUES('mary');
INSERT INTO t15 VALUES(NULL);
SELECT * FROM t15;
SELECT COUNT(*) FROM t15; -- 4
SELECT COUNT(`name`) FROM t15;-- 3

 sum

 

-- 演示 sum 函数的使用
-- 统计一个班级数学总成绩？
SELECT SUM(math) FROM student; 
-- 统计一个班级语文、英语、数学各科的总成绩
SELECT SUM(math) AS math_total_score,SUM(english),SUM(chinese) FROM student;
-- 统计一个班级语文、英语、数学的成绩总和
SELECT SUM(math + english + chinese) FROM student; 
-- 统计一个班级语文成绩平均分
SELECT SUM(chinese)/ COUNT(*) FROM student;
SELECT SUM(`name`) FROM student; 

-- 演示 max 和 min 的使用
-- 求班级最高分和最低分（数值范围在统计中特别有用）
SELECT MAX(math + english + chinese), MIN(math + english + chinese)
FROM student; -- 求出班级数学最高分和最低分
SELECT MAX(math) AS math_high_socre, MIN(math) AS math_low_socre
FROM student;

avg

 

-- 演示 avg 的使用
-- 练习：
-- 求一个班级数学平均分？
SELECT AVG(math) FROM student; 
-- 求一个班级总分平均分
SELECT AVG(math + english + chinese) FROM student;

max/min

-- 求班级最高分和最低分（数值范围在统计中特别有用）
SELECT MAX(math + english + chinese), MIN(math + english + chinese
     FROM student;
-- 求出班级数学最高分和最低分
SELECT MAX(math) AS math_high_socre, MIN(math) AS math_low_socre
    FROM student; 

group by配合 having

使用 group by 子句对列进行分组

使用 having 子句对分组后的结果进行过滤（筛选条件）

 

# 演示 group by + having
GROUP by 用于对查询的结果分组统计, (示意图)
-- having 子句用于限制分组显示结果. 
-- ?如何显示每个部门的平均工资和最高工资
-- 老韩分析: avg(sal) max(sal)
-- 按照部门来分组查询
SELECT AVG(sal), MAX(sal) , deptno
    FROM emp GROUP BY deptno; 
-- 使用数学方法，对小数点进行处理
SELECT FORMAT(AVG(sal),2), MAX(sal) , deptno
	FROM emp GROUP BY deptno; 
-- ?显示每个部门的每种岗位的平均工资和最低工资
-- 老师分析 1. 显示每个部门的平均工资和最低工资
		-- 2. 显示每个部门的每种岗位的平均工资和最低工资
SELECT AVG(sal), MIN(sal) , deptno, job
	FROM emp GROUP BY deptno, job;
	--先根据部门再根据部门中的岗位，依次递进的感觉
--GROUP BY deptno, job;根据部门，根据岗位，综合起来就是根据部门中的岗位

-- ?显示平均工资低于 2000 的部门号和它的平均工资 // 别名
-- 老师分析 [写 sql 语句的思路是化繁为简,各个击破]
-- 1. 显示各个部门的平均工资和部门号
-- 2. 在 1 的结果基础上，进行过滤，保留 AVG(sal) < 2000
-- 3. 使用别名进行过滤
SELECT AVG(sal), deptno
	FROM emp GROUP BY deptno
		HAVING AVG(sal) < 2000;
-- 使用别名,查询速度更快，因为只用到一次avg函数
SELECT AVG(sal) AS avg_sal, deptno
	FROM emp GROUP BY deptno
		HAVING avg_sal<2000

 

数学函数（ABS等）

-- 演示数学相关函数
-- ABS(num) 绝对值
SELECT ABS(-10) FROM DUAL; 
-- BIN (decimal_number )十进制转二进制
SELECT BIN(10) FROM DUAL; 
-- CONV(number2,from_base,to_base) 进制转换
-- 下面的含义是 8 是十进制的 8, 转成 2 进制输出
SELECT CONV(8, 10, 2) FROM DUAL; 
-- 下面的含义是 8 是 16 进制的 8, 转成 10 进制输出
SELECT CONV(8, 16, 10) FROM DUAL; 
-- CEILING (number2 ) 向上取整, 得到比 num2 大的最小整数
SELECT CEILING(-1.1) FROM DUAL; 
-- FLOOR (number2 ) 向下取整,得到比 num2 小的最大整数
SELECT FLOOR(-1.1) FROM DUAL; 
-- FORMAT (number,decimal_places ) 保留小数位数(四舍五入)
SELECT FORMAT(78.125458,2) FROM DUAL; 
-- HEX (DecimalNumber ) 转十六进制
-- LEAST (number , number2 [,..]) 求最小值
SELECT LEAST(0,1, -10, 4) FROM DUAL;
-- MOD (numerator ,denominator ) 求余
SELECT MOD(10, 3) FROM DUAL; 
-- RAND([seed]) RAND([seed]) 返回随机数 其范围为 0 ≤ v ≤ 1.0
-- 老韩说明
-- 1. 如果使用 rand() 每次返回不同的随机数 ，在 0 ≤ v ≤ 1.0
-- 2. 如果使用 rand(seed) 返回随机数, 范围 0 ≤ v ≤ 1.0, 如果 seed 不变，
-- 该随机数也不变了
SELECT RAND() FROM DUAL;
SELECT CURRENT_TIMESTAMP() FROM DUAL;