mysql 数学函数

常用函数

COUNT(expression)	返回查询的字段总数，参数是一个字段或者 * 号
SUM(expression)	返回指定字段的总和
AVG(expression)	返回一个表达式的平均值，expression 是一个字段
MAX(expression)，MIN(expression)	返回字段 expression 中的最大值或最小值
ABS(num)	返回 num 的绝对值
CEILING(num)	向上取整
FLOOR(num)	向下取整
FORMAT(num, decimal_places)	保留小数位数
LEAST(num1, num2, num3, …)	返回列表中的最小值
GREATEST(num1, num2, num3, …)	返回列表中的最大值
MOD(num1, num2)	返回 num1 除以 num2 以后的余数
CONV(num, from_base, to_base)	进制转换
RAND()	返回 0 到 1 的随机数

count 合计函数的使用

count(列名) 会排除掉为null 的情况，即null不统计

快速入门案例

mysql> select * from student;
+----+--------+---------+---------+------+
| id | name   | chinese | english | math |
+----+--------+---------+---------+------+
|  1 | 宋江   |      88 |      85 |   95 |
|  2 | 李逵   |      81 |      55 |   65 |
|  3 | 林冲   |      89 |      85 |   95 |
|  4 | 鲁智深 |      99 |      85 |   95 |
|  5 | 杨志   |      97 |      85 |   96 |
|  6 | 晁盖   |      88 |      75 |   95 |
+----+--------+---------+---------+------+
6 rows in set (0.00 sec)

1.统计一个班共有多少学生

mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.03 sec)

2.统计语文大于90 的学生有多少个

mysql> select count(*) from student where chinese>90;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

3.统计总分大于270的人数

mysql> select count(*) from student where (chinese+english+math)>270;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

// 现在 我想知道成绩大于270的具体是谁？
// count() 是个函数 只会显示一条  想看具体是谁需要用联合查询 后边会讲
mysql> select count(*),name from student where (chinese+english+math)>270;
+----------+--------+
| count(*) | name   |
+----------+--------+
|        2 | 鲁智深 |
+----------+--------+
1 row in set (0.00 sec)

count(*) 和 count(列名) 的区别

// count(*) 会统计一共的记录数，count(列名) 会排除掉为null 的情况
mysql> select * from test;
+------+-------+
| id   | name  |
+------+-------+
|  100 | hello |
|  200 | NULL  |
+------+-------+
2 rows in set (0.00 sec)

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

mysql> select count(id) from test;
+-----------+
| count(id) |
+-----------+
|         2 |
+-----------+
1 row in set (0.00 sec)

mysql> select count(name) from test;
+-------------+
| count(name) |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

sum 列求和函数的使用

快速入门案例

mysql> select * from student;
+----+--------+---------+---------+------+
| id | name   | chinese | english | math |
+----+--------+---------+---------+------+
|  1 | 宋江   |      88 |      85 |   95 |
|  2 | 李逵   |      81 |      55 |   65 |
|  3 | 林冲   |      89 |      85 |   95 |
|  4 | 鲁智深 |      99 |      85 |   95 |
|  5 | 杨志   |      97 |      85 |   96 |
|  6 | 晁盖   |      88 |      75 |   95 |
+----+--------+---------+---------+------+
6 rows in set (0.00 sec)

1.统计一个班数学总成绩

mysql> select sum(math) from student;
+-----------+
| sum(math) |
+-----------+
|       541 |
+-----------+
1 row in set (0.00 sec)

mysql> select sum(math) as '数学总分' from student;
+----------+
| 数学总分 |
+----------+
|      541 |
+----------+
1 row in set (0.00 sec)

2.统计一个班语数英各科总成绩

mysql> select sum(chinese) as '语文总分',sum(math) as '数学总分',sum(english) as '英语总分' from student;
+----------+----------+----------+
| 语文总分 | 数学总分 | 英语总分 |
+----------+----------+----------+
|      542 |      541 |      470 |
+----------+----------+----------+
1 row in set (0.00 sec)

3.统计一个班语数英所有成绩总和

//mysql中加法运算任何数和null相加都为null，如果宋江的数学成绩为null，那么统计结果会少宋江的成绩
mysql> select sum(chinese+english+math) as '成绩总和' from student;
+----------+
| 成绩总和 |
+----------+
|     1553 |
+----------+
1 row in set (0.00 sec)

// 推荐用这种方式  这种方法就算有人成绩为null 也不影响，因为没有加法运算
mysql> select sum(chinese)+sum(math)+sum(english) from student;
+-------------------------------------+
| sum(chinese)+sum(math)+sum(english) |
+-------------------------------------+
|                                1553 |
+-------------------------------------+
1 row in set (0.00 sec)

4.统计一个班数学平均成绩

mysql> select sum(math)/count(*) as '数学平均成绩' from student;
+-------------------+
| 数学平均成绩      |
+-------------------+
| 90.16666666666667 |
+-------------------+
1 row in set (0.00 sec)

// round() 方法，自定义小数点后保留几位
mysql> select round(sum(math)/count(*)) as '数学平均成绩' from student;
+--------------+
| 数学平均成绩 |
+--------------+
|           90 |
+--------------+
1 row in set (0.02 sec)

avg 求平均值函数的使用

只讨论数据不为null的情况，建表时数据全部设置为not null

快速入门案例

mysql> select * from student;
+----+--------+---------+---------+------+
| id | name   | chinese | english | math |
+----+--------+---------+---------+------+
|  1 | 宋江   |      88 |      85 |   95 |
|  2 | 李逵   |      81 |      55 |   65 |
|  3 | 林冲   |      89 |      85 |   95 |
|  4 | 鲁智深 |      99 |      85 |   95 |
|  5 | 杨志   |      97 |      85 |   96 |
|  6 | 晁盖   |      88 |      75 |   95 |
+----+--------+---------+---------+------+
6 rows in set (0.00 sec)

1.求一个班级数学平均数

mysql> select avg(math) from student;
+-------------------+
| avg(math)         |
+-------------------+
| 90.16666666666667 |
+-------------------+
1 row in set (0.00 sec)

mysql> select round(avg(math),2) from student;
+--------------------+
| round(avg(math),2) |
+--------------------+
|              90.17 |
+--------------------+
1 row in set (0.00 sec)

2.求一个班级总分平均分

mysql> select round(avg(chinese+math+english),2) from student;
+------------------------------------+
| round(avg(chinese+math+english),2) |
+------------------------------------+
|                             258.83 |
+------------------------------------+
1 row in set (0.00 sec)

max 和 min的使用

快速入门案例

mysql> select * from student;
+----+--------+---------+---------+------+
| id | name   | chinese | english | math |
+----+--------+---------+---------+------+
|  1 | 宋江   |      88 |      85 |   95 |
|  2 | 李逵   |      81 |      55 |   65 |
|  3 | 林冲   |      89 |      85 |   95 |
|  4 | 鲁智深 |      99 |      85 |   95 |
|  5 | 杨志   |      97 |      85 |   96 |
|  6 | 晁盖   |      88 |      75 |   95 |
+----+--------+---------+---------+------+
6 rows in set (0.00 sec)

1.查询总分的最高分和最低分

mysql> select max(chinese+english+math) as '最高分', min(chinese+english+math) as '最低分' from student;
+--------+--------+
| 最高分 | 最低分 |
+--------+--------+
|    279 |    201 |
+--------+--------+
1 row in set (0.00 sec)

其他数学函数的使用

mysql> select * from student;
+----+--------+---------+---------+-------+
| id | name   | chinese | english | math  |
+----+--------+---------+---------+-------+
|  3 | 鲁智深 |   33.00 |   32.00 | 12.00 |
|  2 | 林冲   |   95.00 |   98.00 | 91.00 |
|  1 | 李逵   |   18.00 |   25.00 | 11.00 |
+----+--------+---------+---------+-------+
3 rows in set (0.00 sec)

//ABS(num)	返回 num 的绝对值
mysql> select abs(-11.22);
+-------------+
| abs(-11.22) |
+-------------+
|       11.22 |
+-------------+
1 row in set (0.00 sec)

//CEILING(num)	向上取整    FLOOR(num)	向下取整
mysql> select ceiling(12.22), floor(12.22),ceiling(-12.22), floor(-12.22);
+----------------+--------------+-----------------+---------------+
| ceiling(12.22) | floor(12.22) | ceiling(-12.22) | floor(-12.22) |
+----------------+--------------+-----------------+---------------+
|             13 |           12 |             -12 |           -13 |
+----------------+--------------+-----------------+---------------+
1 row in set (0.00 sec)

//FORMAT(num, decimal_places)	保留小数位数
mysql> select format(12.345,2),format(-12.345,2),format(12.123,2),format(-12.123,2);
+------------------+-------------------+------------------+-------------------+
| format(12.345,2) | format(-12.345,2) | format(12.123,2) | format(-12.123,2) |
+------------------+-------------------+------------------+-------------------+
| 12.35            | -12.35            | 12.12            | -12.12            |
+------------------+-------------------+------------------+-------------------+
1 row in set (0.00 sec)

//LEAST()  返回列表中的最小值   GREATEST()	返回列表中的最大值
mysql> select least(1,2,3), greatest(1,2,3);
+--------------+-----------------+
| least(1,2,3) | greatest(1,2,3) |
+--------------+-----------------+
|            1 |               3 |
+--------------+-----------------+
1 row in set (0.00 sec)

//MOD(num1, num2)	返回 num1 除以 num2 以后的余数
mysql> select mod(10,3),mod(10,5);
+-----------+-----------+
| mod(10,3) | mod(10,5) |
+-----------+-----------+
|         1 |         0 |
+-----------+-----------+
1 row in set (0.00 sec)

//RAND()	返回 0 到 1 的随机数
mysql> select rand(), rand()*100;
+-------------------+-------------------+
| rand()            | rand()*100        |
+-------------------+-------------------+
| 0.397183169980704 | 50.65288259708591 |
+-------------------+-------------------+
1 row in set (0.00 sec)