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)

truncate()

截取小数点后的位数,

注意

truncate()函数和round()函数不同,round()函数是四舍五入,而truncate()函数是直接保留小数点后指定的位数,当指定的位数为负数时,从小数点左边开始计算

mysql> select truncate(1134.34566,3);
+------------------------+
| truncate(1134.34566,3) |
+------------------------+
|               1134.345 |
+------------------------+
1 row in set (0.00 sec)

mysql> select truncate(1134.34566);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

mysql> select truncate(1134.34566,-1);
+-------------------------+
| truncate(1134.34566,-1) |
+-------------------------+
|                    1130 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select truncate(1134.34566,-3);
+-------------------------+
| truncate(1134.34566,-3) |
+-------------------------+
|                    1000 |
+-------------------------+
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)

注意*

在没有主键的情况下,count(1)的执行效率必count(*)快