搜尋

首頁 >資料庫 >mysql教程 >mysql 常用的三類函數

mysql 常用的三類函數

墨辰丷
墨辰丷原創
2018-05-16 15:53:131391瀏覽

本篇主要介紹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為小數位數

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

mysql> select round(1.2345, 3)

mysql> select round(1.2345, 3)

#mysql>;
------------------
| 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常用函數福利

PHP中的MYSQL常用函數php下操作資料庫必備

PHP中的MYSQL常用函數(php下操作資料庫必備)_PHP教學

以上是mysql 常用的三類函數的詳細內容。更多資訊請關注PHP中文網其他相關文章！

陳述：
本文內容由網友自願投稿，版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容，請聯絡admin@php.cn
上一篇：mysql常用語句及用法下一篇：mysql常用語句及用法

相關文章

看更多