下文主要給大家?guī)鞰ySQL有哪些常用三類函數(shù)，希望這些內容能夠帶給大家實際用處，這也是我編輯mysql有哪些常用三類函數(shù)這篇文章的主要目的。好了，廢話不多說，大家直接看下文吧。                                                          

創(chuàng)新互聯(lián)建站主要為客戶提供服務項目涵蓋了網頁視覺設計、VI標志設計、全網營銷推廣、網站程序開發(fā)、HTML5響應式網站建設、成都做手機網站、微商城、網站托管及網站維護公司、WEB系統(tǒng)開發(fā)、域名注冊、國內外服務器租用、視頻、平面設計、SEO優(yōu)化排名。設計、前端、后端三個建站步驟的完善服務體系。一人跟蹤測試的建站服務標準。已經為成都公路鉆孔機行業(yè)客戶提供了網站維護服務。

一、字符串類。

注：mysql在處理字符串時，字符下標從1開始。

1、concat(string1, string2, ......); //連接字符串

mysql> select concat('leng', 'xue', 'gang') as name;
+-------------+
| name        |
+-------------+
| lengxuegang |
+-------------+
1 row in set (0.00 sec)

2、instr(string, substring); //返回substring首次在string中出現(xiàn)的位置，不存在返回0

mysql> select instr('lengxuegang', 'xue');
+-----------------------------+
| instr('lengxuegang', 'xue') |
+-----------------------------+
|                           5 |
+-----------------------------+
1 row in set (0.00 sec)

mysql> select instr('lengxuegang', 'none');
+------------------------------+
| instr('lengxuegang', 'none') |
+------------------------------+
|                            0 |
+------------------------------+
1 row in set (0.00 sec)

3、lcase(string); //轉換為小寫

mysql> select lcase('LengxueGang');
+----------------------+
| lcase('LengxueGang') |
+----------------------+
| lengxuegang          |
+----------------------+
1 row in set (0.00 sec)

4、left(string, length); //從string左邊起取length個字符

mysql> select left('lengxuegang', 4);
+------------------------+
| left('lengxuegang', 4) |
+------------------------+
| leng                   |
+------------------------+
1 row in set (0.01 sec)

5、length(string); //返回string的長度

mysql> select length('lengxuegang');
+-----------------------+
| length('lengxuegang') |
+-----------------------+
|                    11 |
+-----------------------+
1 row in set (0.25 sec)

6、locate(substring, string, [start_position]); //從start_position出開始查找，返回substring在string中首次出現(xiàn)的位置。其功能與instr類似，不過注意string與substring的位置是不一樣的。

mysql> select locate('leng', 'lengxueganglengxuegang', 4);
+---------------------------------------------+
| locate('leng', 'lengxueganglengxuegang', 4) |
+---------------------------------------------+
|                                          12 |
+---------------------------------------------+
1 row in set (0.00 sec)

7、ltrim(string); //去除左邊的空格

mysql> select ltrim('   leng');
+------------------+
| ltrim('   leng') |
+------------------+
| leng             |
+------------------+
1 row in set (0.00 sec)

8、repeat(string, count); //重復string count次

mysql> select repeat('leng', 4);
+-------------------+
| repeat('leng', 4) |
+-------------------+
| lenglenglengleng  |
+-------------------+
1 row in set (0.00 sec)

9、replace(string, search_str, replace_str); //在string中將search_str替換為replace_str

mysql> select replace('lengxueganglengxuegang', 'leng', 'cheng');
+----------------------------------------------------+
| replace('lengxueganglengxuegang', 'leng', 'cheng') |
+----------------------------------------------------+
| chengxuegangchengxuegang                           |
+----------------------------------------------------+
1 row in set (0.05 sec)

10、rtrim(string); //去除右端空格

mysql> select rtrim('leng     ');
+--------------------+
| rtrim('leng     ') |
+--------------------+
| leng               |
+--------------------+
1 row in set (0.00 sec)

11、strcmp(string1， string2); //比較兩個字符串大小，按大小關系分別返回1、0、-1

mysql> select strcmp('leng', 'cheng');
+-------------------------+
| strcmp('leng', 'cheng') |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.04 sec)

mysql> select strcmp('cheng', 'leng');
+-------------------------+
| strcmp('cheng', 'leng') |
+-------------------------+
|                      -1 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select strcmp('leng', 'leng');
+------------------------+
| strcmp('leng', 'leng') |
+------------------------+
|                      0 |
+------------------------+
1 row in set (0.00 sec)

12、substring(string, start_pos, length); //從string的start_pos開始，取length個字符

mysql> select substring('lengxuegang', 5, 3);
+--------------------------------+
| substring('lengxuegang', 5, 3) |
+--------------------------------+
| xue                            |
+--------------------------------+
1 row in set (0.00 sec)

13、trim(); //去除字符串兩端空格

mysql> select trim('  leng   ');
+-------------------+
| trim('  leng   ') |
+-------------------+
| leng              |
+-------------------+
1 row in set (0.00 sec)

14、ucase(string); //轉換為大寫

mysql> select ucase('lengxuegang');
+----------------------+
| ucase('lengxuegang') |
+----------------------+
| LENGXUEGANG          |
+----------------------+
1 row in set (0.00 sec)

15、right(string, length); //取string右邊length個字符

mysql> select right('lengxuegang', 4);
+-------------------------+
| right('lengxuegang', 4) |
+-------------------------+
| gang                    |
+-------------------------+
1 row in set (0.00 sec)

16、space(count); //生成count個空格

mysql> select space(5);
+----------+
| space(5) |
+----------+
|          |
+----------+
1 row in set (0.00 sec)

17、lpad(string, length, pad); //在string的左端填充pad，直到其長度達到length

mysql> select lpad('leng', 10, 'dacb');
+--------------------------+
| lpad('leng', 10, 'dacb') |
+--------------------------+
| dacbdaleng               |
+--------------------------+
1 row in set (0.00 sec)

18、rpad(); //在string的右端填充pad，直到其長度達到length

mysql> select rpad('leng', 10, 'dacb');
+--------------------------+
| rpad('leng', 10, 'dacb') |
+--------------------------+
| lengdacbda               |
+--------------------------+
1 row in set (0.00 sec)

19、coalesce(value1, value2, ...) 返回第一個非null值，如果全為null，則返回null

mysql> select coalesce(null, 1, 2);
+----------------------+
| coalesce(null, 1, 2) |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.03 sec)

二、數(shù)學類

1、abs(num); //返回絕對值

mysql> select abs(-3.5);
+-----------+
| abs(-3.5) |
+-----------+
|       3.5 |
+-----------+
1 row in set (0.03 sec)

2、bin(decimal_num); //十進制轉二進制

mysql> select bin(12);
+---------+
| bin(12) |
+---------+
| 1100    |
+---------+
1 row in set (0.05 sec)

3、ceiling(num); //向上取整

mysql> select ceiling(3.4);
+--------------+
| ceiling(3.4) |
+--------------+
|            4 |
+--------------+
1 row in set (0.00 sec)

mysql> select ceiling(-3.4);
+---------------+
| ceiling(-3.4) |
+---------------+
|            -3 |
+---------------+
1 row in set (0.00 sec)

4、conv(num, from_base, to_base); //進制轉換

mysql> select conv(10, 10, 2);
+-----------------+
| conv(10, 10, 2) |
+-----------------+
| 1010            |
+-----------------+
1 row in set (0.00 sec)

5、floor(num); //向下取整

mysql> select floor(3.6);

+------------+
| floor(3.6) |
+------------+
|          3 |
+------------+
1 row in set (0.00 sec)

mysql> select floor(-3.6);
+-------------+
| floor(-3.6) |
+-------------+
|          -4 |
+-------------+

1 row in set (0.00 sec)

6、least(num1, num2, num3, ......); //取最小值

mysql> select least(10, 4, -4, 0);
+---------------------+
| least(10, 4, -4, 0) |
+---------------------+
|                  -4 |
+---------------------+
1 row in set (0.10 sec)

7、mod(); //取余

mysql> select mod(10, 3);
+------------+
| mod(10, 3) |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

8、power(num, power); //冪運算

mysql> select power(3, 3);
+-------------+
| power(3, 3) |
+-------------+
|          27 |
+-------------+
1 row in set (0.08 sec)

9、rand([seed]); //隨機數(shù)

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

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

10、round(number, [decimals]); //四舍五入，decimals為小數(shù)位數(shù)

mysql> select round(1.2345);
+---------------+
| round(1.2345) |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

mysql> select round(1.2345, 3);
+------------------+
| round(1.2345, 3) |
+------------------+
|            1.235 |
+------------------+
1 row in set (0.00 sec)

11、sign(number); //返回符號，正負或0

mysql> select sign(0);
+---------+
| sign(0) |
+---------+
|       0 |
+---------+
1 row in set (0.00 sec)

mysql> select sign(2);
+---------+
| sign(2) |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

mysql> select sign(-2);
+----------+
| sign(-2) |
+----------+
|       -1 |
+----------+
1 row in set (0.00 sec)

12、sqrt(num); //開平方

mysql> select sqrt(3);
+-----------------+
| sqrt(3)         |
+-----------------+
| 1.7320508075689 |
+-----------------+
1 row in set (0.00 sec)

13、greatest(value1, value2, ...); //取最大值

mysql> select greatest(2, 3, 10);
+--------------------+
| greatest(2, 3, 10) |
+--------------------+
|                 10 |
+--------------------+
1 row in set (0.00 sec)

三、日期時間類

1、current_date(); //返回當前日期

mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2012-07-01     |
+----------------+
1 row in set (0.04 sec)

2、current_time(); //返回當前時間

mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 02:05:41       |
+----------------+
1 row in set (0.00 sec)

3、current_timestamp(); //返回當前時間戳

mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2012-07-01 02:06:12 |
+---------------------+
1 row in set (0.04 sec)

4、now(); //返回當前時間

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2012-07-01 02:06:57 |
+---------------------+
1 row in set (0.00 sec)

對于以上關于mysql有哪些常用三類函數(shù)，大家是不是覺得非常有幫助。如果需要了解更多內容，請繼續(xù)關注我們的行業(yè)資訊，相信你會喜歡上這些內容的。