Shulou(Shulou.com)05/31 Report--

This article mainly introduces what the common functions of MySQL are. It is very detailed and has certain reference value. If you are interested, you must finish reading it.

MySQL's functional methods, covering all the common methods of MySQL. The following is the directory structure of this article. You can click the directory on the right to find it according to your needs:

1. Digital function

Second, string function

III. Date function

4. MySQL advanced function

(1) numerical function

1. ABS (x) returns the absolute value of x

SELECT ABS (- 1);-returns 1

2. AVG (expression) returns the average of an expression, and expression is a field.

SELECT AVG (age) FROM student

3. CEIL (x) / CEILING (x) returns the smallest integer greater than or equal to x

SELECT CEIL (1.5); SELECT CEILING (1.5);-return 2

4. FLOOR (x) returns the largest integer less than or equal to x

SELECT FLOOR (1.5);-return 1

5. EXP (x) returns to the x power of e

SELECT EXP (3);-calculates the third power of e and returns 20.085536923188

6. GREATEST (expr1, expr2, expr3, …) Returns the maximum value in the list

SELECT GREATEST (3, 12, 34, 8, 25);-returns the maximum value 34 in the following list of numbers

7. LEAST (expr1, expr2, expr3, …) Returns the minimum value in the list

SELECT LEAST (3, 12, 34, 8, 25);-returns the minimum value of 3 in the following list of numbers

8. LN returns the natural logarithm of a number

SELECT LN (2);-returns the natural logarithm of 2: 0.6931471805599453

9. LOG (x) returns the natural logarithm (the logarithm based on e)

SELECT LOG (20.085536923188);-returns 3

10. MAX (expression) returns the maximum value in the field expression

SELECT MAX (age) AS maxAge FROM Student;---- age maximum

11. MIN (expression) returns the maximum value in the field expression

SELECT MIN (age) AS minAge FROM Student;---- age minimum

12. POW (x ~ () y) / POWER (x ~ ()) returns x to the y power.

SELECT POW (2, 3); SELECT POWER (2, 3);-returns 2 to the third: 8

13. RAND () returns a random number from 0 to 1

SELECT RAND ();-returns a random number from 0 to 1. If there is a number in (), if RAND (x), x is the same, the return value is the same.

14. ROUND (x) returns the integer closest to x

SELECT ROUND (1.23456);-returns 1

15. SIGN (x) returns the symbol of x, where x is negative, 0, and positive returns-1, 0, and 1, respectively.

SELECT SIGN (- 10);-return-1

16. SQRT (x) returns the square root of x

SELECT SQRT (25);-returns 5

17. SUM (expression) returns the sum of the specified fields

SELECT SUM (age) AS totalAage FROM Student;---- returns the sum of age

18. TRUNCATE (xQuery y) returns the value x reserved to the decimal point (the biggest difference from ROUND is that it is not rounded)

SELECT TRUNCATE (1.23456);-return 1.234

(2) string function

1. Returns the ASCII code of the first character of the string s

SELECT ASCII ('AB');-returns the ASCII code value of A: 65

2. LENGTH/CHAR_LENGTH (s) / CHARACTER_LENGTH (s) returns the number of characters of the string s

SELECT LENGTH ('1234');-return 4

3. CONCAT (S1 ~ S2... Sn) string s1and other strings are merged into a single string

SELECT CONCAT ('hel','llo');-return hello

4. FIND_IN_SET (S1 ~ S2) returns the position of the string that matches S1 in the string S2

SELECT FIND_IN_SET ("c", "arecrum bregory credpene");-return 3

5. The FORMAT (xQuery n) function can format the number x into "#, #. # #", keeping x to n places after the decimal point, and rounding the last digit.

SELECT FORMAT (250500.5634, 2);-returns 250500.56

6. INSERT (S1, x, rep, Len2) string S2 replaces the x position of S1, starting with a string of length len

SELECT INSERT ("google.com", 1, 6, "runnob");-returns runoob.com

7. LOCATE (S1 ~ s) gets the starting position of S1 from the string s

SELECT LOCATE ('st','myteststring');-return 5

8. LCASE (s) / LOWER (s) changes all letters of the string s to lowercase letters

SELECT LOWER ('RUNOOB');-return runoob

9. UCASE (s) / UPPER (s) changes all letters of the string s to uppercase letters

SELECT UCASE ('runoob');-return RUNOOB

10. TRIM (s) removes the spaces at the beginning and end of the string s

SELECT TRIM ('RUNOOB');-return RUNOOB

11. LTRIM (s) removes the space at the beginning of the string s

SELECT LTRIM ('RUNOOB');-return 'RUNOOB'

12. RTRIM (s) removes the space at the end of the string s

SELECT RTRIM ('RUNOOB');-return' RUNOOB'

13. SUBSTR (s, start, length) truncates a substring of length length from the start position of the string s

SELECT SUBSTR ("RUNOOB", 2,3) AS ExtractString;---- intercepts 3 characters from the second position in the string RUNOOB and returns UNO

14. SUBSTR/SUBSTRING (s, start, length) truncates a substring of length length from the start position of the string s

SELECT SUBSTR/SUBSTRING ("RUNOOB", 2,3);-truncate 3 characters from the second position in the string RUNOOB and return UNO

15. POSITION (S1 IN s) gets the starting position of S1 from the string s

SELECT POSITION ('b' in 'abc');-return 2

16. REPEAT (sQuery n) repeat the string s n times

SELECT REPEAT ('runoob',3);-return runoobrunoobrunoob

17. REVERSE (s) reverses the order of the string s

SELECT REVERSE ('abc');-return cba

18. STRCMP (S1 and S2) compare the strings S1 and S2, return 0 if S1 and S2 are equal, if S1 > S2 return 1, if the number of days between s1d2

SELECT DATEDIFF ('2001-01-01-01); return-32

9. DATE_FORMAT displays the date d as required by the expression f

SELECT DATE_FORMAT ('2011.11.11 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11) / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 11:11:11 AM

10. DAYNAME (d) returns the day of the week, such as Monday,Tuesday

SELECT DAYNAME ('2011-11-11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11);-return Friday

11. DAYOFMONTH (d) the date of calculation d is the day of the month

SELECT DAYOFMONTH ('2011-11-11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11)

12. DAYOFWEEK (d) date d what day is today, 1 Sunday, 2 Monday, and so on

SELECT DAYOFWEEK ('2011-11-11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11);-return 6

13. DAYOFYEAR (d) the date of calculation d is the day of this year

SELECT DAYOFYEAR ('2011-11-11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11)

14. EXTRACT (type FROM d) gets the specified value from date d, and type specifies the returned value

Available values for type are:

MICROSECOND

SECOND

MINUTE

HOUR

DAY

WEEK

MONTH

QUARTER

YEAR

SECOND_MICROSECOND

MINUTE_MICROSECOND

MINUTE_SECOND

HOUR_MICROSECOND

HOUR_SECOND

HOUR_MINUTE

DAY_MICROSECOND

DAY_SECOND

DAY_MINUTE

DAY_HOUR

YEAR_MONTH

SELECT EXTRACT (MINUTE FROM '2011-12-13 14-14-15-15-16');-return 15

15. DAYOFWEEK (d) date d what day is today, 1 Sunday, 2 Monday, and so on

SELECT DAYOFWEEK ('2011-11-11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11);-return 6

16. UNIX_TIMESTAMP () gets the timestamp

SELECT UNIX_TIMESTAMP ('2019-2-19'); SELECT UNIX_TIMESTAMP (expression);-return 1550505600

17. FROM_UNIXTIME () timestamp turns the date

SELECT FROM_UNIXTIME (1550505600);-return 2019-02-19 00:00:00SELECT FROM_UNIXTIME (1550505600,'% Ymai% mmi% d');-return 2019-02-19

(4) MySQL advanced function

1. IF (expr,v1,v2) returns the result v1 if the expression expr holds true; otherwise, the result v2 is returned

SELECT IF (1 > 0); return yes

2. CONV returns the f1 binary number to become the f2 decimal number

SELECT CONV (13Jing 10jue 2);-return 1101

3. CURRENT_USER () / SESSION_USER () / SYSTEM_USER () / USER () returns the current user

4. DATABASE () returns the current database name

5. VERSION () returns the version number of the database

These are all the contents of the article "what are the common functions of MySQL?" Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.