MySQL数值运算符和函数

朴灿烈づ我的快乐病毒、 2022-07-18 11:57 165阅读 0赞

![Center][]

**对于普通加减法运算，此处不予详细讲解，如下例：**

mysql> SELECT 3+4;
    +-----+
    | 3+4 |
    +-----+
    |   7 |
    +-----+
    1 row in set (0.03 sec)

**CEIL和FLOOR**

**CEIL为向上取整，只要有小数位，不管是几，直接入一位。例如：**

mysql> SELECT CEIL(3.01);
    +------------+
    | CEIL(3.01) |
    +------------+
    |          4 |
    +------------+
    1 row in set (0.02 sec)

FLOOR与此

相反

mysql> SELECT FLOOR(3.99);
    +-------------+
    | FLOOR(3.99) |
    +-------------+
    |           3 |
    +-------------+
    1 row in set (0.00 sec）

**DIV和MOD**

**DIV为取整，MOD为取余**

mysql> SELECT 3/4;
    +--------+
    | 3/4    |
    +--------+
    | 0.7500 |
    +--------+
    1 row in set (0.00 sec)
    
    mysql> SELECT 3 DIV 4;
    +---------+
    | 3 DIV 4 |
    +---------+
    |       0 |
    +---------+
    1 row in set (0.00 sec)
    
    mysql> SELECT 3 MOD 4;
    +---------+
    | 3 MOD 4 |
    +---------+
    |       3 |
    +---------+
    1 row in set (0.00 sec)

注：MOD可以换成％

**POWER幂运算**

例如3的2次方

mysql> SELECT POWER(3,2);
    +------------+
    | POWER(3,2) |
    +------------+
    |          9 |
    +------------+
    1 row in set (0.14 sec)

**ROUND四舍五入**

例如3.652保留小数点后两位。

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

保留小数点后一位：

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

**TRUNCATE**

直接去掉某几位

mysql> SELECT TRUNCATE(125.89,2);
    +--------------------+
    | TRUNCATE(125.89,2) |
    +--------------------+
    |             125.89 |
    +--------------------+
    1 row in set (0.00 sec)
    
    mysql> SELECT TRUNCATE(125.89,1);
    +--------------------+
    | TRUNCATE(125.89,1) |
    +--------------------+
    |              125.8 |
    +--------------------+
    1 row in set (0.00 sec)
    
    mysql> SELECT TRUNCATE(125.89,0);
    +--------------------+
    | TRUNCATE(125.89,0) |
    +--------------------+
    |                125 |
    +--------------------+
    1 row in set (0.00 sec)
    
    mysql> SELECT TRUNCATE(125.89,-1);
    +---------------------+
    | TRUNCATE(125.89,-1) |
    +---------------------+
    |                 120 |
    +---------------------+
    1 row in set (0.00 sec)

[Center]: /images/20220717/b0353ad59a934edc9b1f715ee61fa772.png