一. 數(shù)字函數(shù)

函數(shù)名稱	描述
abs()	絕對(duì)值函數(shù)
bin()	十進(jìn)制轉(zhuǎn)換二進(jìn)制
hex()	轉(zhuǎn)換成十六進(jìn)制
conv(number,from_base,to_base)	將number從from_base轉(zhuǎn)換成to_base進(jìn)制
ceiling()	向上取整
floor()	向下取整
format(number,decimal_places)	格式化，保留decimal_placese位小數(shù)
rand()	返回隨機(jī)浮點(diǎn)數(shù)，范圍[0.0 , 1.0)
mod(number,denominator)	取模，求余

---

• 絕對(duì)值——abs()
• 向上取整——ceiling()
• 向下取整——floor()

mysql> select abs(-100.2);
+-------------+
| abs(-100.2) |
+-------------+
|       100.2 |
+-------------+
//變得更大
mysql> select ceiling(23.04);
+----------------+
| ceiling(23.04) |
+----------------+
|             24 |
+----------------+
//變得更小
mysql> select floor(22.99);
+--------------+
| floor(22.99) |
+--------------+
|           22 |
+--------------+

---

• 十進(jìn)制轉(zhuǎn)換二進(jìn)制——bin()
• 轉(zhuǎn)換成十六進(jìn)制——hex()
• 進(jìn)制轉(zhuǎn)換——conv()

//十進(jìn)制轉(zhuǎn)二進(jìn)制
mysql> select bin(7);
+--------+
| bin(7) |
+--------+
| 111    |
+--------+
//轉(zhuǎn)換成十六進(jìn)制
mysql> select hex(23);
+---------+
| hex(23) |
+---------+
| 17      |
+---------+
1 row in set (0.00 sec)
//將16進(jìn)制的23轉(zhuǎn)換成十進(jìn)制數(shù)字
mysql> select conv(23,16,10);
+----------------+
| conv(23,16,10) |
+----------------+
| 35             |
+----------------+

---

精度控制——format()
約分按照四舍五入的規(guī)則

mysql> select format(12.3456,2);
+-------------------+
| format(12.3456,2) |
+-------------------+
| 12.35             |
+-------------------+
1 row in set (0.00 sec)mysql> select format(12.3446,2);
+-------------------+
| format(12.3446,2) |
+-------------------+
| 12.34             |
+-------------------+

---

產(chǎn)生隨機(jī)數(shù)——rand()

mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.2663527114934779 |
+--------------------+
1 row in set (0.00 sec)mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.6162809968146319 |
+--------------------+

rand()配合format()使用

mysql> select format(rand()*100,0);
+----------------------+
| format(rand()*100,0) |
+----------------------+
| 28                   |
+----------------------+mysql> select format(rand()*100,0);
+----------------------+
| format(rand()*100,0) |
+----------------------+
| 56                   |
+----------------------+

二. 其他函數(shù)

函數(shù)名稱	描述
user()	查詢當(dāng)前用戶
md5()	對(duì)字符串形成md5摘要
database()	顯示當(dāng)前使用的數(shù)據(jù)庫(kù)
password()	MySQL使用該函數(shù)對(duì)用戶加密
ifnull(val1,val2)	如果val1是NULL，則返回val2，否則返回val1

---

• ifnull()

mysql> select ifnull('abc',123);
+-------------------+
| ifnull('abc',123) |
+-------------------+
| abc               |
+-------------------+
1 row in set (0.00 sec)mysql> select ifnull(null,123);
+------------------+
| ifnull(null,123) |
+------------------+
|              123 |
+------------------+

---

• 形成md5摘要——md5()

md5摘要是HTTPS協(xié)議的一部分

mysql> select md5('abc');
+----------------------------------+
| md5('abc')                       |
+----------------------------------+
| 900150983cd24fb0d6963f7d28e17f72 |
+----------------------------------+

• 給用戶加密——password()

mysql> select password('root');
+-------------------------------------------+
| password('root')                          |
+-------------------------------------------+
| *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
+-------------------------------------------+