Home > Database > Mysql Tutorial > Three types of commonly used functions in mysql

Three types of commonly used functions in mysql

墨辰丷
Release: 2018-05-16 15:53:13
Original
1384 people have browsed it

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!

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template