MySQL数值及聚合函数

数值函数

abs()

取绝对值

mysql> select abs(1);+--------+| abs(1) |+--------+|      1 |+--------+1 row in set (0.00 sec)mysql> select abs(-1);+---------+| abs(-1) |+---------+|       1 |+---------+1 row in set (0.01 sec)

aqrt()

求二次方根

注意

当为负数时,返回null

mysql> select sqrt(4);+---------+| sqrt(4) |+---------+|       2 |+---------+1 row in set (0.00 sec)mysql> select sqrt(2);+--------------------+| sqrt(2)            |+--------------------+| 1.4142135623730951 |+--------------------+1 row in set (0.00 sec)mysql> select sqrt(-2);+----------+| sqrt(-2) |+----------+|     NULL |+----------+1 row in set (0.00 sec)

mod()

取余

mysql> select mod(12,3);+-----------+| mod(12,3) |+-----------+|         0 |+-----------+1 row in set (0.00 sec)mysql> select mod(13,4);+-----------+| mod(13,4) |+-----------+|         1 |+-----------+1 row in set (0.00 sec)mysql> select mod(13,-4);+------------+| mod(13,-4) |+------------+|          1 |+------------+1 row in set (0.00 sec)mysql> select mod(-13,-4);+-------------+| mod(-13,-4) |+-------------+|          -1 |+-------------+1 row in set (0.01 sec)mysql> select mod(-13,4);+------------+| mod(-13,4) |+------------+|         -1 |+------------+1 row in set (0.00 sec)

rand()

生成一个0~1之间的随机数，传入整数参数时，用来产生重复序列

mysql> select rand();+--------------------+| rand()             |+--------------------+| 0.6796255388107388 |+--------------------+1 row in set (0.00 sec)mysql> select 10+rand();+--------------------+| 10+rand()          |+--------------------+| 10.386429689253148 |+--------------------+1 row in set (0.00 sec)mysql> select (10+rand())*30;+--------------------+| (10+rand())*30     |+--------------------+| 326.79815581701524 |+--------------------+1 row in set (0.00 sec)

round(num,n)

四舍五入

注意

n表示要保留的小数点位数,不写默认为0

mysql> select round(1234.98765,3);+---------------------+| round(1234.98765,3) |+---------------------+|            1234.988 |+---------------------+1 row in set (0.00 sec)mysql> select round(1234.98765);+-------------------+| round(1234.98765) |+-------------------+|              1235 |+-------------------+1 row in set (0.00 sec)mysql> select round(1234.98765,7);+---------------------+| round(1234.98765,7) |+---------------------+|        1234.9876500 |+---------------------+1 row in set (0.00 sec)

pow()

pow(x,y)

求x的y次方根等价于 power(x,y)

mysql> select pow(10,3);+-----------+| pow(10,3) |+-----------+|      1000 |+-----------+1 row in set (0.02 sec)mysql> select pow(10,-3);+------------+| pow(10,-3) |+------------+|      0.001 |+------------+1 row in set (0.00 sec)mysql> select pow(5,-2);+-----------+| pow(5,-2) |+-----------+|      0.04 |+-----------+1 row in set (0.00 sec)

聚合函数

max()

求最大值

mysql> select max(age) from text;+----------+| max(age) |+----------+|       89 |+----------+1 row in set (0.00 sec)

min()

求最小值

mysql> select min(age) from text;+----------+| min(age) |+----------+|       10 |+----------+1 row in set (0.00 sec)

sum()

求和

mysql> select sum(age) from text;+----------+| sum(age) |+----------+|      221 |+----------+1 row in set (0.00 sec)

avg()

求平均数

mysql> select avg(age) from text;+----------+| avg(age) |+----------+|  36.8333 |+----------+1 row in set (0.00 sec)

count()

求总数

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