算术表达式就是加减乘除的运算过程,主要是对一条数据中出现的数字进行统计和运算。
首先,有一张数据表,如下:
mysql> select * from test_score; +----+-------+--------+-------+--------+--------+ | id | class | name | yuwen | shuxue | yingyu | +----+-------+--------+-------+--------+--------+ | 1 | 1 | 张三 | 95 | 90 | 97 | | 2 | 1 | 李四 | 59 | 88 | 90 | | 3 | 1 | 王五 | 99 | 99 | 99 | | 4 | 1 | 马六 | 51 | 56 | 59 | | 5 | 2 | 侯七 | 92 | 94 | 93 | | 6 | 2 | 黑八 | 92 | 97 | 89 | | 7 | 2 | 牛二 | 59 | 60 | 61 | | 8 | 2 | 熊大 | 55 | 50 | 54 | | 9 | 3 | 铁牛 | 53 | 54 | 51 | | 10 | 3 | 铜鼓 | 49 | 47 | 54 | | 11 | 3 | 钢巴 | 47 | 51 | 50 | +----+-------+--------+-------+--------+--------+ 11 rows in set (0.00 sec)然后统计总分,如下:
#学生张三的成绩总分 mysql> SELECT (yuwen+shuxue+yingyu) AS total FROM test_score where name='张三'; +-------+ | total | +-------+ | 282 | +-------+ 1 row in set (0.00 sec)定义:SUM()函数用于计算一组值或表达式的总和。 以前上学时老师会统计每科的平均分,然后看看谁拉了后腿。先计算语文,就是先把班里语文得分相加得到总分,然后除以学生人数,得到的就是平均分,低于平均分的学生就要倒霉了。
#所有学生语文成绩的总分 mysql> SELECT sum(yuwen) AS yuwen_total FROM test_score; +-------------+ | yuwen_total | +-------------+ | 751 | +-------------+ 1 row in set (0.00 sec) #学生人数 mysql> SELECT count(*) AS total FROM test_score; +-------+ | total | +-------+ | 11 | +-------+ 1 row in set (0.00 sec) #求语文成绩的平均值 mysql> select sum(yuwen)/count(*) from test_score; +---------------------+ | sum(yuwen)/count(*) | +---------------------+ | 68.2727 | +---------------------+ 1 row in set (0.04 sec)MySQL提供了许多聚合函数,包括AVG,COUNT,SUM,MIN,MAX等。除COUNT函数外,其它聚合函数在执行计算时会忽略NULL值。
#语文最高分 mysql> SELECT MAX(yuwen) FROM test_score; +------------+ | MAX(yuwen) | +------------+ | 99 | +------------+ 1 row in set (0.00 sec) #语文最低分及所在班级 mysql> SELECT class,MIN(yuwen) FROM test_score; +-------+------------+ | class | MIN(yuwen) | +-------+------------+ | 1 | 47 | +-------+------------+ 1 row in set (0.00 sec) #通过聚合函数求语文成绩的平均值 mysql> SELECT avg(yuwen) AS total FROM test_score; +---------+ | total | +---------+ | 68.2727 | +---------+ 1 row in set (0.00 sec) #格式化后语文成绩的平均值 mysql> SELECT format(avg(yuwen),2) AS total FROM test_score; +-------+ | total | +-------+ | 68.27 | +-------+ 1 row in set (0.00 sec) #出去重复项后的语文成绩平均分 mysql> SELECT avg(DISTINCT yuwen) AS total FROM test_score; +---------+ | total | +---------+ | 66.6667 | +---------+ 1 row in set (0.00 sec)