Three types of commonly used functions in mysql

This article mainly introduces the three types of functions commonly used in MySQL. Interested friends can refer to it. I hope it will be helpful to everyone.

1. String class.

Note: When mysql processes strings, character subscripts start from 1.

1. concat(string1, string2, ...); //Connect string

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

2. instr(string, substring); //Return the position where substring first appears in string , does not exist and returns 0

mysql> select instr('lengxuegang', 'xue');
----------------------- -------
| instr('lengxuegang', 'xue') |
----------------------- ----
|                                                                        ​0.00 sec)

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

3、lcase(string); //Convert to lowercase

mysql> select lcase('LengxueGang');

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

| lcase('LengxueGang') |
----------------------
| lengxuegang |
----------------------
1 row in set (0.00 sec)

4、left (string, length); //Take length characters from the left side of string

mysql> select left('lengxuegang', 4);

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

| left('lengxuegang', 4) |
----------------------- -
| length


#5、length(string); //Return the length of string

mysql> select length('lengxuegang');

-------- ---------------
| length('lengxuegang') |

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

|           11 |

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

1 row in set (0.25 sec)

6. locate(substring, string, [start_position]); //Start the search from start_position and return the position where substring first appears in string. Its function is similar to instr, but note that the positions of string and substring are different.

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

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

| ---------------------------------------

1 row in set (0.00 sec)

7. ltrim(string); //Remove the spaces on the left

mysql> select ltrim(' leng');
------------- ------
| ltrim(' leng') |
------------------

| leng |

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

1 row in set (0.00 sec)

8、repeat(string, count); //Repeat string count times

mysql> select repeat('leng', 4);
-------------------
| repeat('leng', 4) |
-------------------

| langlenglengleng |

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

1 row in set (0.00 sec)

9、replace(string, search_str, replace_str); //Replace search_str with replace_str in string

mysql> select replace(' lengxueganglengxuegang', 'leng', 'cheng');
---------------------------------- ------------------
| replace('lengxueganglengxuegang', 'leng', 'cheng') |
----------- ----------------------------------------

| chengxuegangchengxuegang                                        -------------------------------------------------- --

1 row in set (0.05 sec)

10, rtrim(string); //Remove right-end spaces

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

11, strcmp(string1, string2); //Compare the sizes of two strings and return 1, 0, -1 respectively according to the size relationship

mysql> select strcmp('leng', 'cheng');

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

| strcmp(' leng', 'cheng') |
----------------------
| --------------------
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); //Start from start_pos of string, take length characters

mysql> select substring('lengxuegang', 5, 3);

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

| substring('lengxuegang', 5, 3) |

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

| --------------------------
1 row in set (0.00 sec)

13、trim(); / /Remove spaces at both ends of the string

mysql> select trim(' leng ');
-------------------

| trim (' leng ') |

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

| leng |

------------- ------
1 row in set (0.00 sec)

14、ucase(string); //Convert to uppercase

mysql> select ucase('lengxuegang') ;
-----------------------

| ucase('lengxuegang') |

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

| LENGXUEGANG |

----------------------
1 row in set (0.00 sec)

15.right(string, length); //Get length characters from the right side of string

mysql> select right('lengxuegang', 4);
-- -----------------------

| right('lengxuegang', 4) |

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

| gang                                                              #1 row in set (0.00 sec)

16, space(count); //Generate count spaces

mysql> select space(5);
----- -----
| space(5) |
----------

| |

----------

1 row in set (0.00 sec)

17, lpad(string, length, pad); //Padding pad at the left end of string until its length reaches length

mysql> select lpad('leng ', 10, 'dacb');
--------------------------
| lpad('leng', 10, 'dacb') |
--------------------------

| dacbdaleng |

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

1 row in set (0.00 sec)

18, rpad(); //Fill pad at the right end of string , until its length reaches length

mysql> select rpad('leng', 10, 'dacb');
------------------- -------
| rpad('leng', 10, 'dacb') |
----------------------- ---

| lengdacbda |

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

1 row in set (0.00 sec)

19. coalesce(value1, value2, ...) returns the first non-null value. If all are null, return null

mysql> select coalesce(null, 1, 2) ;
----------------------
| coalesce(null, 1, 2) |
-------- -------------

|           1 | in set (0.03 sec)

2. Mathematics

1. abs(num); //Return absolute value

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

2, bin(decimal_num); //Convert decimal to binary

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

3、ceiling(num); //Round up

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); // Base conversion

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

5、floor(num); //Round down

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, ...); //Take the minimum value

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

7、mod(); //Take remainder

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

8、power(num, power); //Power operation

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

9 , rand([seed]); //Random number

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]); //Rounding, decimals is the number of decimal places

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); //Return sign, positive or negative or 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); //square root

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

13、greatest(value1, value2, ...); //Take the maximum value

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

3. Date and time class

1. current_date(); //Return the current date

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

2、current_time(); //Return the current time

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

3. current_timestamp(); //Return the current timestamp

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

4、now(); //Return the current time

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

Related recommendations:

Recommended MySQL common function benefits

MySQL common functions in PHP are necessary to operate the database under PHP

Common MYSQL functions in PHP (necessary for operating databases under PHP)_PHP tutorial

The above is the detailed content of Three types of commonly used functions in mysql. For more information, please follow other related articles on the PHP Chinese website!