mysql内置函数之数学函数实验
程序员文章站
2023-01-28 22:38:23
6.7 mysql 内置函数-数学函数
表准备
mysql> select * from student;
+------+------+--------+...
6.7 mysql 内置函数-数学函数
表准备
mysql> select * from student;
+------+------+--------+-------+
| id | name | sex | score |
+------+------+--------+-------+
| 1 | aa | female | NULL |
| 2 | bb | male | NULL |
| 3 | cc | male | 3.1 |
| 4 | dd | male | 3.5 |
| 5 | ee | male | 3.6 |
| 6 | ff | male | 3.9 |
+------+------+--------+-------+
6 rows in set (0.00 sec)
6.7.1 十进制转二进制 – bin
bin(number2)
mysql> select bin(10);
+---------+
| bin(10) |
+---------+
| 1010 |
+---------+
1 row in set (0.00 sec)
mysql> select bin(3);
+--------+
| bin(3) |
+--------+
| 11 |
+--------+
1 row in set (0.00 sec)
6.7.2 向上取整 – ceiling
ceiling(number2)
实验一：
mysql> select ceiling(3.1);
+--------------+
| ceiling(3.1) |
+--------------+
| 4 |
+--------------+
1 row in set (0.00 sec)
mysql> select ceiling(3.5);
+--------------+
| ceiling(3.5) |
+--------------+
| 4 |
+--------------+
1 row in set (0.00 sec)
mysql> select ceiling(3.6);
+--------------+
| ceiling(3.6) |
+--------------+
| 4 |
+--------------+
1 row in set (0.00 sec)
实验二：
mysql> select name,ceiling(score) from student;
+------+----------------+
| name | ceiling(score) |
+------+----------------+
| aa | NULL |
| bb | NULL |
| cc | 4 |
| dd | 4 |
| ee | 4 |
| ff | 4 |
+------+----------------+
6 rows in set (0.00 sec)
6.7.3 向下取整 – floor
floor(number2)
实验一：
mysql> select floor(3.1);
+------------+
| floor(3.1) |
+------------+
| 3 |
+------------+
1 row in set (0.00 sec)
mysql> select floor(3.5);
+------------+
| floor(3.5) |
+------------+
| 3 |
+------------+
1 row in set (0.00 sec)
mysql> select floor(3.9);
+------------+
| floor(3.9) |
+------------+
| 3 |
+------------+
1 row in set (0.00 sec)
实验二：
mysql> select name,floor(score) from student;
+------+--------------+
| name | floor(score) |
+------+--------------+
| aa | NULL |
| bb | NULL |
| cc | 3 |
| dd | 3 |
| ee | 3 |
| ff | 3 |
+------+--------------+
6 rows in set (0.00 sec)
6.7.4 取最大值 – max
max(num1,num2)
实验一：
mysql> select score from student;
+-------+
| score |
+-------+
| NULL |
| NULL |
| 3.1 |
| 3.5 |
| 3.6 |
| 3.9 |
+-------+
6 rows in set (0.00 sec)
mysql> select max(score) from student;
+------------------+
| max(score) |
+------------------+
| 3.90000009536743 |
+------------------+
1 row in set (0.00 sec)
mysql> select max(id) from student;
+---------+
| max(id) |
+---------+
| 6 |
+---------+
1 row in set (0.00 sec)
结论：注意浮点型在取最大值时的取值。
6.7.5 取最小值 – min
min(num1,num2)
实验一：
mysql> select score from student;
+-------+
| score |
+-------+
| NULL |
| NULL |
| 3.1 |
| 3.5 |
| 3.6 |
| 3.9 |
+-------+
6 rows in set (0.00 sec)
mysql> select min(score) from student;
+------------------+
| min(score) |
+------------------+
| 3.09999990463257 |
+------------------+
1 row in set (0.00 sec)
mysql> select min(id) from student;
+---------+
| min(id) |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)
结论：注意浮点型在取最小值时的取值。
6.7.6 开平方 – sqrt
sqrt(num1)
实验一：
mysql> select sqrt(4);
+---------+
| sqrt(4) |
+---------+
| 2 |
+---------+
1 row in set (0.00 sec)
6.7.6 返回0-1内的随机数 – rand
rand();
实验一：
mysql> select rand();
+-------------------+
| rand() |
+-------------------+
| 0.608572646610972 |
+-------------------+
1 row in set (0.00 sec)
mysql> select rand();
+-------------------+
| rand() |
+-------------------+
| 0.154164170058597 |
+-------------------+
1 row in set (0.00 sec)
mysql> select rand();
+-------------------+
| rand() |
+-------------------+
| 0.945102941193714 |
+-------------------+
1 row in set (0.00 sec)
mysql> select rand();
+-------------------+
| rand() |
+-------------------+
| 0.263022226526423 |
+-------------------+
1 row in set (0.00 sec)
6.7.7 四舍五入 – round
返回参数X的四舍五入的一个整数。
实验一：
mysql> select round(1.1)
-> ;
+------------+
| round(1.1) |
+------------+
| 1 |
+------------+
1 row in set (0.00 sec)
mysql> select round(1.3);
+------------+
| round(1.3) |
+------------+
| 1 |
+------------+
1 row in set (0.00 sec)
mysql> select round(1.5);
+------------+
| round(1.5) |
+------------+
| 2 |
+------------+
1 row in set (0.00 sec)
mysql> select round(1.6);
+------------+
| round(1.6) |
+------------+
| 2 |
+------------+
1 row in set (0.00 sec)
mysql> select round(1.9);
+------------+
| round(1.9) |
+------------+
| 2 |
+------------+
1 row in set (0.00 sec)
6.7.8 小数四舍五入 –ROUND(X,D)
返回参数X的四舍五入的有D为小数的一个数字。如果D为0，结果将没有小数点或小数部分。
实验一：
mysql> select round(1.99999,3);
+------------------+
| round(1.99999,3) |
+------------------+
| 2.000 |
+------------------+
1 row in set (0.00 sec)
mysql> select round(1.89999,3);
+------------------+
| round(1.89999,3) |
+------------------+
| 1.900 |
+------------------+
1 row in set (0.00 sec)
mysql> select round(1.89990,3);
+------------------+
| round(1.89990,3) |
+------------------+
| 1.900 |
+------------------+
1 row in set (0.00 sec)
mysql> select round(1.89390,3);
+------------------+
| round(1.89390,3) |
+------------------+
| 1.894 |
+------------------+
1 row in set (0.00 sec)
mysql> select round(1.89930,3);
+------------------+
| round(1.89930,3) |
+------------------+
| 1.899 |
+------------------+
1 row in set (0.00 sec)
结论：该方法对小数点后的第count位后的数进行四舍五入