Shulou(Shulou.com)06/01 Report--

The following mainly brings you what three types of functions commonly used in mysql, I hope these contents can bring you practical use, which is also the main purpose of this article that I edit mysql which commonly used three types of functions. All right, don't talk too much nonsense, let's just read the following.

First, string class.

Note: when dealing with strings in mysql, the character subscript starts at 1.

1. Concat (string1, string2,.); / / connection string

Mysql > select concat ('leng',' xue', 'gang') as name

+-+

| | name |

+-+

| | lengxuegang |

+-+

1 row in set (0.00 sec)

2. Instr (string, substring); / / returns the location where substring first appeared in string. 0 is not returned.

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 |

+-+

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) | |

+-- +

| | leng |

+-- +

1 row in set (0.01 sec)

5. Length (string); / / returns 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 searching from start_position, and return the location where substring first appeared in string. Its function is similar to that of instr, but note that the location of string is different from that of substring.

Mysql > select locate ('leng',' lengxueganglengxuegang', 4)

+-+

| | locate ('leng',' lengxueganglengxuegang', 4) | |

+-+

| | 12 |

+-+

1 row in set (0.00 sec)

7. Ltrim (string); / / remove the space 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) | |

+-+

| | lenglenglengleng |

+-+

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.05sec)

10. Rtrim (string); / / remove the space at the right end

Mysql > select rtrim ('leng')

+-+

| | rtrim ('leng') |

+-+

| | leng |

+-+

1 row in set (0.00 sec)

11. Strcmp (string1, string2); / / compare the size of two strings and return 1, 0 and-1 respectively according to the size relationship

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); / / start with the start_pos of string and take length characters

Mysql > select substring ('lengxuegang', 5,3)

+-+

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

+-+

| | xue |

+-+

1 row in set (0.00 sec)

13. Trim (); / / remove the 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); / / take length characters to the right 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); / / fill the left end of the string with pad 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 the right end of the string with pad 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, or null if it is all null

Mysql > select coalesce (null, 1,2)

+-+

| | coalesce (null, 1,2) | |

+-+

| | 1 |

+-+

1 row in set (0.03 sec)

2. Mathematics

1. Abs (num); / / returns absolute value

Mysql > select abs (- 3.5)

+-+

| | abs (- 3.5) |

+-+

| | 3.5 | |

+-+

1 row in set (0.03 sec)

2. Bin (decimal_num); / / Decimal to binary

Mysql > select bin (12)

+-+

| | bin (12) | |

+-+

| | 1100 |

+-+

1 row in set (0.05sec)

3. Ceiling (num); / / rounding 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); / / binary conversion

Mysql > select conv (10,10,2)

+-+

| | conv (10,10,2) | |

+-+

| | 1010 |

+-+

1 row in set (0.00 sec)

5. Floor (num); / / rounding 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

Mysql > select least (10,4,-4,0)

+-+

| | least (10,4,-4,0) | |

+-+

| |-4 |

+-+

1 row in set (0.10 sec)

7. Mod (); / / take the 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]); / / rounded. Decimals is a decimal place.

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 symbol, plus or minus 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); / / squared

Mysql > select sqrt (3)

+-+

| | sqrt (3) | |

+-+

| | 1.7320508075689 | |

+-+

1 row in set (0.00 sec)

13. Greatest (value1, value2,...); / / take the maximum

Mysql > select greatest (2,3,10)

+-+

| | greatest (2, 3, 10) |

+-+

| | 10 |

+-+

1 row in set (0.00 sec)

III. Date and time category

1. Current_date (); / / returns the current date

Mysql > select current_date ()

+-+

| | current_date () |

+-+

| | 2012-07-01 | |

+-+

1 row in set (0.04 sec)

2. Current_time (); / / returns the current time

Mysql > select current_time ()

+-+

| | current_time () |

+-+

| | 02:05:41 |

+-+

1 row in set (0.00 sec)

3. Current_timestamp (); / / returns the current timestamp

Mysql > select current_timestamp ()

+-+

| | current_timestamp () |

+-+

| | 2012-07-01 02:06:12 |

+-+

1 row in set (0.04 sec)

4. Now (); / / returns the current time

Mysql > select now ()

+-+

| | now () |

+-+

| | 2012-07-01 02:06:57 |

+-+

1 row in set (0.00 sec)

For the above about mysql which commonly used three types of functions, we do not think it is very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.