数学函数
| abs(number) |
绝对值函数 |
| bin(decimal_number) |
十进制转换二进制 |
| hex(decimal_number) |
转换十六进制 |
| conv(number,from_base,to_base) |
进制转换 |
| ceiling(number) |
向上取整 |
| floor(number) |
向下取整 |
| format(number,decimal_places) |
格式化，保留小数 |
| rand() |
返回随机浮点数，范围[0.0,1.0) |
| mod(number,denominator) |
取模，求余 |
Abs
mysql> select abs(12);
+---------+
| abs(12) |
+---------+
| 12 |
+---------+
1 row in set (0.00 sec)
mysql> select abs(-12);
+----------+
| abs(-12) |
+----------+
| 12 |
+----------+
1 row in set (0.00 sec)
mysql> select abs(-12.3);
+------------+
| abs(-12.3) |
+------------+
| 12.3 |
+------------+
1 row in set (0.00 sec)
mysql>
Bin
mysql> select bin(10);
+---------+
| bin(10) |
+---------+
| 1010 |
+---------+
1 row in set (0.00 sec)
mysql> select bin(17);
+---------+
| bin(17) |
+---------+
| 10001 |
+---------+
1 row in set (0.00 sec)
mysql> select bin(20);
+---------+
| bin(20) |
+---------+
| 10100 |
+---------+
1 row in set (0.00 sec)
mysql> select bin(3.14);
+-----------+
| bin(3.14) |
+-----------+
| 11 |
+-----------+
1 row in set (0.00 sec)
mysql> select bin(7.14);
+-----------+
| bin(7.14) |
+-----------+
| 111 |
+-----------+
1 row in set (0.00 sec)
mysql>
Hex
mysql> select hex(15);
+---------+
| hex(15) |
+---------+
| F |
+---------+
1 row in set (0.00 sec)
mysql> select hex(11);
+---------+
| hex(11) |
+---------+
| B |
+---------+
1 row in set (0.00 sec)
mysql> select hex(16);
+---------+
| hex(16) |
+---------+
| 10 |
+---------+
1 row in set (0.00 sec)
mysql> select hex(255);
+----------+
| hex(255) |
+----------+
| FF |
+----------+
1 row in set (0.00 sec)
mysql>
Conv
mysql> select conv(10,10,2);
+---------------+
| conv(10,10,2) |
+---------------+
| 1010 |
+---------------+
1 row in set (0.00 sec)
mysql> select conv(10,10,5);
+---------------+
| conv(10,10,5) |
+---------------+
| 20 |
+---------------+
1 row in set (0.00 sec)
mysql> select conv(10,10,4);
+---------------+
| conv(10,10,4) |
+---------------+
| 22 |
+---------------+
1 row in set (0.00 sec)
mysql> select conv(10,10,16);
+----------------+
| conv(10,10,16) |
+----------------+
| A |
+----------------+
1 row in set (0.00 sec)
mysql>
Format
mysql> select format(3.1415926535,2);
+------------------------+
| format(3.1415926535,2) |
+------------------------+
| 3.14 |
+------------------------+
1 row in set (0.00 sec)
mysql> select format(3.1415926535,3);
+------------------------+
| format(3.1415926535,3) |
+------------------------+
| 3.142 |
+------------------------+
1 row in set (0.00 sec)
mysql> select format(3.1415926535,4);
+------------------------+
| format(3.1415926535,4) |
+------------------------+
| 3.1416 |
+------------------------+
1 row in set (0.00 sec)
mysql>
Mod
mysql> select mod(10,3);
+-----------+
| mod(10,3) |
+-----------+
| 1 |
+-----------+
1 row in set (0.00 sec)
mysql> select mod(24,3);
+-----------+
| mod(24,3) |
+-----------+
| 0 |
+-----------+
1 row in set (0.00 sec)
mysql> select mod(24,4);
+-----------+
| mod(24,4) |
+-----------+
| 0 |
+-----------+
1 row in set (0.00 sec)
mysql> select mod(24,5);
+-----------+
| mod(24,5) |
+-----------+
| 4 |
+-----------+
1 row in set (0.00 sec)
mysql>
Rand
mysql> select rand();
+--------------------+
| rand() |
+--------------------+
| 0.6462398373021184 |
+--------------------+
1 row in set (0.00 sec)
mysql> select rand();
+--------------------+
| rand() |
+--------------------+
| 0.7120559184924289 |
+--------------------+
1 row in set (0.00 sec)
mysql> select rand();
+--------------------+
| rand() |
+--------------------+
| 0.6215601112894342 |
+--------------------+
1 row in set (0.00 sec)
mysql> select rand();
+--------------------+
| rand() |
+--------------------+
| 0.9716328317035295 |
+--------------------+
1 row in set (0.00 sec)
mysql> select rand()*100;
+-----------------+
| rand()*100 |
+-----------------+
| 99.348385538299 |
+-----------------+
1 row in set (0.00 sec)
mysql> select rand()*100;
+--------------------+
| rand()*100 |
+--------------------+
| 5.2520812538006165 |
+--------------------+
1 row in set (0.00 sec)
mysql> select rand()*100;
+--------------------+
| rand()*100 |
+--------------------+
| 28.215252727470595 |
+--------------------+
1 row in set (0.00 sec)
mysql> select format(rand()*100,1);
+----------------------+
| format(rand()*100,1) |
+----------------------+
| 25.3 |
+----------------------+
1 row in set (0.00 sec)
mysql> select format(rand()*100,1);
+----------------------+
| format(rand()*100,1) |
+----------------------+
| 42.0 |
+----------------------+
1 row in set (0.00 sec)
mysql> select format(rand()*100,0);
+----------------------+
| format(rand()*100,0) |
+----------------------+
| 34 |
+----------------------+
1 row in set (0.00 sec)
mysql> select format(rand()*100,0);
+----------------------+
| format(rand()*100,0) |
+----------------------+
| 43 |
+----------------------+
1 row in set (0.00 sec)
mysql>
mysql> select format(rand()*1000,0);
+-----------------------+
| format(rand()*1000,0) |
+-----------------------+
| 145 |
+-----------------------+
1 row in set (0.00 sec)
mysql> select format(rand()*1000,0);
+-----------------------+
| format(rand()*1000,0) |
+-----------------------+
| 431 |
+-----------------------+
1 row in set (0.00 sec)
mysql> select format(rand()*1000,0);
+-----------------------+
| format(rand()*1000,0) |
+-----------------------+
| 720 |
+-----------------------+
1 row in set (0.00 sec)
mysql>
Ceiling
mysql> select ceiling(3.1);
+--------------+
| ceiling(3.1) |
+--------------+
| 4 |
+--------------+
1 row in set (0.00 sec)
mysql> select ceiling(3.9);
+--------------+
| ceiling(3.9) |
+--------------+
| 4 |
+--------------+
1 row in set (0.00 sec)
mysql> select ceiling(3.009);
+----------------+
| ceiling(3.009) |
+----------------+
| 4 |
+----------------+
1 row in set (0.00 sec)
mysql> select ceiling(3.0);
+--------------+
| ceiling(3.0) |
+--------------+
| 3 |
+--------------+
1 row in set (0.00 sec)
mysql> select ceiling(3.99);
+---------------+
| ceiling(3.99) |
+---------------+
| 4 |
+---------------+
1 row in set (0.00 sec)
mysql> select ceiling(-3.99);
+----------------+
| ceiling(-3.99) |
+----------------+
| -3 |
+----------------+
1 row in set (0.00 sec)
mysql> select ceiling(-3.1);
+---------------+
| ceiling(-3.1) |
+---------------+
| -3 |
+---------------+
1 row in set (0.00 sec)
mysql> select ceiling(-3.1001);
+------------------+
| ceiling(-3.1001) |
+------------------+
| -3 |
+------------------+
1 row in set (0.00 sec)
mysql>
Floor
ysql> select floor(4.5);
+------------+
| floor(4.5) |
+------------+
| 4 |
+------------+
1 row in set (0.00 sec)
mysql> select floor(4.1);
+------------+
| floor(4.1) |
+------------+
| 4 |
+------------+
1 row in set (0.00 sec)
mysql> select floor(4.001);
+--------------+
| floor(4.001) |
+--------------+
| 4 |
+--------------+
1 row in set (0.00 sec)
mysql> select floor(4.9);
+------------+
| floor(4.9) |
+------------+
| 4 |
+------------+
1 row in set (0.00 sec)
mysql> select floor(4.999);
+--------------+
| floor(4.999) |
+--------------+
| 4 |
+--------------+
1 row in set (0.00 sec)
mysql> select floor(-4.999);
+---------------+
| floor(-4.999) |
+---------------+
| -5 |
+---------------+
1 row in set (0.00 sec)
mysql> select floor(-4.9);
+-------------+
| floor(-4.9) |
+-------------+
| -5 |
+-------------+
1 row in set (0.00 sec)
mysql> select floor(-4.001);
+---------------+
| floor(-4.001) |
+---------------+
| -5 |
+---------------+
1 row in set (0.00 sec)
mysql> select floor(-4.1);
+-------------+
| floor(-4.1) |
+-------------+
| -5 |
+-------------+
1 row in set (0.00 sec)
mysql>
其他函数
mysql> select user();
+--------+
| user() |
+--------+
| root@ |
+--------+
1 row in set (0.00 sec)
mysql> select database();
+------------+
| database() |
+------------+
| scott |
+------------+
1 row in set (0.00 sec)
mysql> create table user(
-> id bigint primary key auto_increment,
-> name varchar(20) not null,
-> password char(32) not null
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> desc user;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| password | char(32) | NO | | NULL | |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> insert into user (name,password) values ('张三','abcd1234');
Query OK, 1 row affected (0.00 sec)
mysql> select *from user;
+----+--------+----------+
| id | name | password |
+----+--------+----------+
| 1 | 张三 | abcd1234 |
+----+--------+----------+
1 row in set (0.00 sec)
mysql> insert into user(name,password) values ('李四',md5('hello'));
Query OK, 1 row affected (0.00 sec)
mysql> select *from user;
+----+--------+----------------------------------+
| id | name | password |
+----+--------+----------------------------------+
| 1 | 张三 | abcd1234 |
| 2 | 李四 | 5d41402abc4b2a76b9719d911017c592 |
+----+--------+----------------------------------+
2 rows in set (0.00 sec)
mysql> select md5('a');
+----------------------------------+
| md5('a') |
+----------------------------------+
| 0cc175b9c0f1b6a831c399e269772661 |
+----------------------------------+
1 row in set (0.00 sec)
mysql> select md5('123456');
+----------------------------------+
| md5('123456') |
+----------------------------------+
| e10adc3949ba59abbe56e057f20f883e |
+----------------------------------+
1 row in set (0.00 sec)
mysql> select name from user where name='李四' and password='hello';
Empty set (0.00 sec)
mysql> select name from user where name='张三' and password='abcd1234';
+--------+
| name |
+--------+
| 张三 |
+--------+
1 row in set (0.00 sec)
mysql> select name from user where name='李四' and password=md5('hello');
+--------+
| name |
+--------+
| 李四 |
+--------+
1 row in set (0.01 sec)
mysql> select password('1223');
+-------------------------------------------+
| password('1223') |
+-------------------------------------------+
| *0D7F7F31D85EF4BCE2C4BB738B9B3319F6D68B0F |
+-------------------------------------------+
1 row in set, 1 warning (0.02 sec)
mysql> select md5('hello');
+----------------------------------+
| md5('hello') |
+----------------------------------+
| 5d41402abc4b2a76b9719d911017c592 |
+----------------------------------+
1 row in set (0.00 sec)
mysql> select ifnull(null,10);
+-----------------+
| ifnull(null,10) |
+-----------------+
| 10 |
+-----------------+
1 row in set (0.00 sec)
mysql> select ifnull(null,10) result;
+--------+
| result |
+--------+
| 10 |
+--------+
1 row in set (0.00 sec)
mysql> select ifnull(20,10) result;
+--------+
| result |
+--------+
| 20 |
+--------+
1 row in set (0.00 sec)
mysql> select ifnull(20,null) result;
+--------+
| result |
+--------+
| 20 |
+--------+
1 row in set (0.00 sec)
mysql>
结尾
最后，感谢您阅读我的文章，希望这些内容能够对您有所启发和帮助。如果您有任何问题或想要分享您的观点，请随时在评论区留言。
同时，不要忘记订阅我的博客以获取更多有趣的内容。在未来的文章中，我将继续探讨这个话题的不同方面，为您呈现更多深度和见解。
谢谢您的支持，期待与您在下一篇文章中再次相遇！