MySQL數值運算符和函數

數值運算符和函數

CEIL()             進一取整

DIV                  整數除法

FLOOR()        舍一取整

MOD                取餘數(取模)

POWER()        幂運算

POUND()         四舍五入

TRUNCATE()   數字截取

例1；簡單的加減乘除

mysql> SELECT 3+4;

+-----+

| 3+4 |

+-----+

|   7 |

+-----+

1 row in set (0.01 sec)

例2:進一取整

mysql> SELECT CEIL(3.01);

+------------+

| CEIL(3.01) |

+------------+

|          4 |

+------------+

1 row in set (0.01 sec)

例3:向下取整

mysql> SELECT FLOOR(3.01);

+-------------+

| FLOOR(3.01) |

+-------------+

|           3 |

+-------------+

1 row in set (0.00 sec)

例:4 整數除法，忽略掉小數部分

mysql> SELECT 3 DIV 4;

+---------+

| 3 DIV 4 |

+---------+

|       0 |

+---------+

1 row in set (0.01 sec)

如果直接除

mysql> SELECT 3 / 4;

+--------+

| 3 / 4  |

+--------+

| 0.7500 |

+--------+

1 row in set (0.00 sec)

例5:MOD取餘數

mysql> SELECT 5 MOD 3;

+---------+

| 5 MOD 3 |

+---------+

|       2 |

+---------+

1 row in set (0.00 sec)

mysql> SELECT 5.33 MOD 3;

+------------+

| 5.33 MOD 3 |

+------------+

|       2.33 |

+------------+

1 row in set (0.00 sec)

例6:幂運算

mysql> SELECT POWER(2,4);

+------------+

| POWER(2,4) |

+------------+

|         16 |

+------------+

1 row in set (0.00 sec)

例7:四舍五入 ，保留小數點後N位，我們這裡保留了小數點後面三位

mysql> SELECT ROUND(3.6567,3);

+-----------------+

| ROUND(3.6567,3) |

+-----------------+

|           3.657 |

+-----------------+

1 row in set (0.00 sec)

mysql> SELECT ROUND(3.6560,3);

+-----------------+

| ROUND(3.6560,3) |

+-----------------+

|           3.656 |

+-----------------+

1 row in set (0.00 sec)

例8:數字截取，截取小數點後面N位

mysql> SELECT TRUNCATE(125.786,2);

+---------------------+

| TRUNCATE(125.786,2) |

+---------------------+

|              125.78 |

+---------------------+

1 row in set (0.00 sec)

注意數字截取和四舍五入不一樣

例9:截取的第二個參數為負值，負值時幾，就變成幾個0

mysql> SELECT TRUNCATE(125.786,-2);

+----------------------+

| TRUNCATE(125.786,-2) |

+----------------------+

|                  100 |

+----------------------+

1 row in set (0.00 sec)

mysql> SELECT TRUNCATE(125.786,-1);

+----------------------+

| TRUNCATE(125.786,-1) |

+----------------------+

|                  120 |

+----------------------+

1 row in set (0.00 sec)