Summary of MySQL commonly used functions

Free learning recommendation: mysql video tutorial

##this This article explains the function methods of MySQL, covering all common MySQL methods. The following is the directory structure of this article. You can click on the directory on the right to search according to your needs:

1. Numeric functions
• 2. String functions
• 3 , Date function
• 4. MySQL advanced functions

(1) Numeric function

1. ABS(x) Return The absolute value of x

SELECT ABS(-1);
---- 返回1

2. AVG(expression) returns the average of an expression, 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);   
---- 返回2

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

SELECT FLOOR(1.5); 
---- 返回1

5, EXP(x) Returns e raised to the power of x

SELECT EXP(3);
---- 计算 e 的三次方,返回20.085536923188

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

SELECT GREATEST(3, 12, 34, 8, 25);
---- 返回以下数字列表中的最大值34

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

SELECT LEAST(3, 12, 34, 8, 25);
---- 返回以下数字列表中的最小值3

8. LN returns the natural logarithm of the number

SELECT LN(2);
---- 返回 2 的自然对数：0.6931471805599453

9. LOG(x) returns the natural logarithm (logarithm with base e)

SELECT LOG(20.085536923188);
---- 返回 3

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

SELECT MAX(age) AS maxAge FROM Student;
---- age最大值

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

SELECT MIN(age) AS minAge FROM Student;
---- age最小值

12. POW(x,y)/POWER(x,y) returns x raised to the power of y

SELECT POW(2,3);
SELECT POWER(2,3);
---- 返回2 的 3 次方：8

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

SELECT RAND();
---- 返回 0 到 1 的随机数，若()里面有数字,RAND(x),x相同时，返回值相同

14, ROUND(x) returns the nearest integer to x

SELECT ROUND(1.23456);
---- 返回 1

15 , SIGN(x) returns the sign of x, x is a negative number, 0, and positive number returns -1, 0 and 1 respectively

SELECT SIGN(-10);
---- 返回 -1

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

SELECT SQRT(25);
---- 返回5

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

SELECT SUM(age) AS totalAage FROM Student;
---- 返回age的总和

18. TRUNCATE(x,y) returns the value x to the decimal point The value of the last y digit (the biggest difference from ROUND is that it will not be rounded)

SELECT TRUNCATE(1.23456,3);
---- 返回1.234

(2) String function

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

SELECT ASCII('AB');
---- 返回A的ASCII码值：65

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

SELECT LENGTH('1234');
---- 返回4

3. CONCAT(s1,s2…sn) strings s1, s2 and other strings are combined into one string

SELECT CONCAT('hel','llo');
---- 返回hello

4.FIND_IN_SET( s1, s2) returns the position of the string matching s1 in string s2

SELECT FIND_IN_SET("c", "a,b,c,d,e");
---- 返回3

5. The FORMAT(x,n) function can format the number x "#, .##", keep x to n digits after the decimal point, and round the last digit

SELECT FORMAT(250500.5634, 2); 
---- 返回250,500.56

6, INSERT(s1,x,len,s2) string s2 replacement The x position of s1 starts a string of length len

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

7. LOCATE(s1,s) gets the starting position of s1 from the string s

SELECT LOCATE('st','myteststring');
---- 返回5

8, LCASE(s)/LOWER(s) turns all letters of string s into lowercase letters

SELECT LOWER('RUNOOB');
---- 返回runoob

9, UCASE(s)/UPPER(s )Convert all letters of string s into uppercase letters

SELECT UCASE('runoob');
---- 返回RUNOOB

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

SELECT TRIM('    RUNOOB    ');
---- 返回RUNOOB

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

SELECT LTRIM('    RUNOOB    ');
---- 返回 ’RUNOOB   ‘

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

SELECT RTRIM('    RUNOOB    ');
---- 返回 ’    RUNOOB‘

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

SELECT SUBSTR("RUNOOB", 2, 3) AS ExtractString;
---- 从字符串 RUNOOB 中的第 2 个位置截取 3个 字符，返回UNO

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

SELECT SUBSTR/SUBSTRING("RUNOOB", 2, 3);
---- 从字符串 RUNOOB 中的第 2 个位置截取 3个 字符，返回UNO

15. POSITION(s1 IN s) obtains from string s The starting position of s1

SELECT POSITION('b' in 'abc');
---- 返回2

16. REPEAT(s,n) repeats the string s n times

SELECT REPEAT('runoob',3);
---- 返回runoobrunoobrunoob

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

SELECT REVERSE('abc');
---- 返回cba

18. STRCMP(s1,s2) compares strings s1 and s2. If s1 and s2 are equal, return 0. If s1>s2, return 1. If s1

SELECT STRCMP("runoob", "runoob");
---- 返回0

(3) Date function

1, CURDATE()/CURRENT_DATE() returns the current Date

SELECT CURDATE();
SELECT CURRENT_DATE();
---- 返回2019-02-19

2. CURRENT_TIME()/CURTIME() returns the current time

SELECT CURRENT_TIME();
---- 返回11:40:45

3. CURRENT_TIMESTAMP() returns the current date and time

SELECT CURRENT_TIMESTAMP();
---- 返回2019-02-19 11:41:32

4. ADDDATE(d,n) calculates the start date d plus n days’ date

SELECT ADDDATE("2017-06-15", INTERVAL 10 DAY);
---- 返回2017-06-25

5. ADDTIME(t, n) Time t plus n seconds of time

SELECT ADDTIME('2011-11-11 11:11:11', 5);
---- 返回2011-11-11 11:11:16

6. DATE() extracts the date value from the date or datetime expression

SELECT DATE("2017-06-15 11:11:16");
---- 返回2017-06-15

7. DAY(d) returns the date part of date value d

SELECT DAY("2017-06-15"); 
---- 返回15

8、DATEDIFF(d1,d2)计算日期 d1->d2 之间相隔的天数

SELECT DATEDIFF('2001-01-01','2001-02-02'); 
---- 返回-32

9、DATE_FORMAT按表达式 f的要求显示日期 d

SELECT DATE_FORMAT('2011.11.11 11:11:11','%Y-%m-%d %r');
---- 返回2011-11-11 11:11:11 AM

10、DAYNAME(d)返回日期 d 是星期几，如 Monday,Tuesday

SELECT DAYNAME('2011-11-11 11:11:11');
---- 返回Friday

11、DAYOFMONTH(d)计算日期 d 是本月的第几天

SELECT DAYOFMONTH('2011-11-11 11:11:11');
---- 返回11

12、DAYOFWEEK(d)日期 d 今天是星期几，1 星期日，2 星期一，以此类推

SELECT DAYOFWEEK('2011-11-11 11:11:11');
---- 返回6

13、DAYOFYEAR(d)计算日期 d 是本年的第几天

SELECT DAYOFYEAR('2011-11-11 11:11:11');
---- 返回315

14、EXTRACT(type FROM d)从日期 d 中获取指定的值，type 指定返回的值
type可取值为：
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:15:16');
---- 返回15

15、DAYOFWEEK(d)日期 d 今天是星期几，1 星期日，2 星期一，以此类推

SELECT DAYOFWEEK('2011-11-11 11:11:11');
---- 返回6

16、UNIX_TIMESTAMP()得到时间戳

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

17、FROM_UNIXTIME()时间戳转日期

SELECT FROM_UNIXTIME(1550505600);
---- 返回2019-02-19 00:00:00
SELECT FROM_UNIXTIME(1550505600, '%Y-%m-%d');
---- 返回2019-02-19

（四）MySQL高级函数

1、IF(expr,v1,v2)如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2

SELECT IF(1>0,'yes','no');
---- 返回yes

2、CONV(x,f1,f2)返回 f1 进制数变成 f2 进制数

SELECT CONV(13,10,2);
---- 返回1101

3、CURRENT_USER()/SESSION_USER()/SYSTEM_USER()/USER()返回当前用户
4、DATABASE()返回当前数据库名
5、VERSION()返回数据库的版本号

有不正确的地方，欢迎前来指正！

相关免费学习推荐：mysql数据库(视频)

The above is the detailed content of Summary of MySQL commonly used functions. For more information, please follow other related articles on the PHP Chinese website!