Home >Database >Mysql Tutorial >Three types of commonly used functions in mysql
Three types of commonly used functions in mysql
- 墨辰丷Original
- 2018-05-16 15:53:131406browse
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
-------- ---------------
| 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') |
------------------
--- ---------------
1 row in set (0.00 sec)
8、repeat(string, count); //Repeat string count times
mysql> select repeat('leng', 4);
-------------------
| repeat('leng', 4) |
-------------------
-------------------
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') |
----------- ----------------------------------------
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 ');
-------------------
-------------------
| leng |------------- ------
1 row in set (0.00 sec)
14、ucase(string); //Convert to uppercase
mysql> select 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);
-- -----------------------
---------- ----------------
| 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') |
--------------------------
------- ------------------
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') |
----------------------- ---
--------------------------
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. 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)
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!