ROUND()

ROUND() 函数用于把数值字段舍入为指定的小数位数。

ROUND 简单案例

ROUND(X)： 返回参数X的四舍五入的一个整数。

mysql> SELECT ROUND(-1.49);
+--------------+
| ROUND(-1.49) |
+--------------+
|           -1 |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT ROUND(-1.99);
+--------------+
| ROUND(-1.99) |
+--------------+
|           -2 |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT ROUND(1.51);
+-------------+
| ROUND(1.51) |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

ROUND(X, D)： 返回参数X的四舍五入的有 D 位小数的一个数字。如果D为0，结果将没有小数点或小数部分。

mysql> SELECT ROUND(1.388, 1);
+-----------------+
| ROUND(1.388, 1) |
+-----------------+
|             1.4 |
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT ROUND(1.498, 0);
+-----------------+
| ROUND(1.498, 0) |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.00 sec)

注意：ROUND 返回值数据类型会被变换为一个BIGINT!

ROUND 案例

查询所有课程的平均报名人数，平均结果保留两位小数

SELECT ROUND(AVG(student_count),2) AS avg_student_count
FROM courses;

执行输出结果

mysql> SELECT ROUND(AVG(student_count),2) AS avg_student_count
    -> FROM courses;
+-------------------+
| avg_student_count |
+-------------------+
|            929.00 |
+-------------------+
1 row in set (0.00 sec)

练习题：ROUND

题目描述：查询教师年龄在20岁以上的平均教师年龄，输出结果为四舍五入的整数

SELECT ROUND(AVG(age)) as avg_teacher_age
FROM teachers
WHERE age > 20;

目标输出结果

mysql> SELECT ROUND(AVG(age)) as avg_teacher_age
    -> FROM teachers
    -> WHERE age > 20;
+-----------------+
| avg_teacher_age |
+-----------------+
|              23 |
+-----------------+
1 row in set (0.01 sec)