mysql 常用的三類函數-mysql教程-PHP中文網

本篇主要介紹mysql 常用的三類函數，有興趣的朋友參考下，希望對大家有幫助。

一、字串類別。

註：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中出現的位置，不存在回傳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 |
#--------------------------------

11 in set

##1 (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)

#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中首次出現的位置。其功能與instr類似，不過注意string與substring的位置是不一樣的。

mysql> select locate('leng', 'lengxueganglengxuegang', 4);

------------------------- --------------------

| locate('leng', 'lengxueganglengxuegang', 4) |

---------- -----------------------------------

| ---------------------------------------
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)

二、數學類別

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]); //隨機數字

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為小數位數

--------------- ---

| 1.235 |

#----------------

----------

| -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)