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!
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
