MySQL has many built-in functions, and the descriptions of these functions are listed below. Function Description Example ASCII (s) Returns the ASCII code of the first character of the string s. Returns the ASCII code of the first letter of the CustomerName field: CHAR_LENGTH (s) Returns the number of characters of the string s Returns the number of characters of the string RUNOOB CHARACTER_LENGTH (s) Returns the number of characters in the string s, equivalent to CHAR_LENGTH (s) Returns the number of characters of the string RUNOOB CONCAT (s _ 1 ~ 2. S _ n) A number of strings, such as the string S1, and so on, are merged into a single string. Merge multiple strings CONCAT_WS (x, s _ 1 ~ s _ 2… s _ n) Same as CONCAT (S1 and S2…) Function, but each string should be separated by a delimiter x Merge multiple strings and add delimiters: FIELD (sjournal s 1 ~ 2…) Returns the first string s in the string list (S1 ~ S2…) Position in Returns the position of the string c in the list value FIND_IN_SET (S1 and S2) Returns the position of the string that matches S1 in the string S2 Returns the position of the string c in the specified string FORMAT (XMagne n) The function can format the number x to n places after the decimal point, and the last digit is rounded. Format the numeral “#, # #. # #” form: INSERT (s1 ~ (1) ~ (x) ~ () ~ (2)) The string S2 replaces the x position of S1, starting with a string of length len Replace the six characters starting at the first position of the string with runoob: LOCATE (s 1m s) Get the start position of S1 from the string s Get the position of b in the string abc: Returns the position of b in the string abc: LCASE (s) Change all letters of the string s to lowercase letters Convert the string RUNOOB to lowercase: LEFT (sforme n) Returns the first n characters of the string s Returns the first two characters in the string runoob: LOWER (s) Change all letters of the string s to lowercase letters Convert the string RUNOOB to lowercase: LPAD (S1 Lenpender S2) Fill the string S2 at the beginning of string S1 to make the string length reach len Populate the string xx to the beginning of the abc string: LTRIM (s) Remove the space at the beginning of the string s Remove the space at the beginning of the string RUNOOB: MID (spaper n. Len) Truncate a substring of length len from the n position of the string s, which is the same as SUBSTRING. Intercept 3 characters from the second position in the string RUNOOB: POSITION (S1 IN s) Get the start position of S1 from the string s Returns the position of b in the string abc: REPEAT (sforme n) Repeat the string s n times Repeat the string runoob three times: REPLACE (SMagazine S1, Persons2) Replace string S1 in string s with string S2 Replace the character an in the string abc with the character x: REVERSE (s) Reverse the order of the string s Reverse the order of the string abc: RIGHT (sforme n) Returns the last n characters of the string s Returns the last two characters of the string runoob: RPAD (S1 Lenpender S2) Add the string S2 at the end of string S1 to make the length of the string reach len Populate the string xx to the end of the abc string: RTRIM (s) Remove the space at the end of the string s Remove the space at the end of the string RUNOOB: SPACE (n) Return n spaces Return 10 spaces: STRCMP (S1 and S2) Compare the strings S1 and S2, return 0 if S1 is equal to S2, return 1 if S1 > S2, and return-1 if S1 < S2 Compare strings: SUBSTR (s, start, length) Truncate a substring of length length from the start position of the string s Intercept 3 characters from the second position in the string RUNOOB: SUBSTRING (s, start, length) Truncate a length substring from the start position of the string s, which is equivalent to SUBSTR (s, start, length) Intercept 3 characters from the second position in the string RUNOOB: SUBSTRING_INDEX (s, delimiter, number) Returns the substring after the delimiter delimiter that appears after the number of the string s. If number is positive, returns the string to the left of the number character. If number is negative, returns the string to the right of the first (absolute value of number (from the right). TRIM (s) Remove the spaces at the beginning and end of the string s Remove the leading and trailing spaces from the string RUNOOB: UCASE (s) Convert a string to uppercase Convert the string runoob to uppercase: UPPER (s) Convert a string to uppercase Convert the string runoob to uppercase: Function name Description Example ABS (x) Returns the absolute value of x Returns the absolute value of-1: ACOS (x) Find the inverse cosine of x (in radians), x is a numerical value ASIN (x) Find the arc sine (in radians), x is a numeric value ATAN (x) Find the arc tangent (in radians), x is a numerical value ATAN2 (n, m) Find the inverse tangent (in radians) AVG (expression) Returns the average of an expression, where expression is a field Returns the average of the Price fields in the Products table: CEIL (x) Returns the smallest integer greater than or equal to x CEILING (x) Returns the smallest integer greater than or equal to x COS (x) Find the cosine (the parameter is radians) COT (x) Find the cotangent (the parameter is radians) COUNT (expression) Returns the total number of records for the query. The expression parameter is a field or * number. Returns the total number of records in the products field in the Products table: DEGREES (x) Convert radians to angl N DIV m Divisible, n is the divisor, m is the divisor Calculate 10 divided by 5: EXP (x) Return to the x power of e Calculate the third power of e: FLOOR (x) Returns the largest integer less than or equal to x An integer less than or equal to 1.5: GREATEST (expr1, expr2, expr3,…) Returns the maximum value in the list Returns the maximum value in the following list of numbers: LEAST (expr1, expr2, expr3,…) Returns the minimum value in the list Returns the minimum value in the following list of numbers: LN Returns the natural logarithm of a number, based on e. Returns the natural logarithm of 2: LOG (x) or LOG (base, x) Returns the natural logarithm (the base logarithm), and if you have the base parameter, base is the specified base. LOG10 (x) Returns the logarithm with a base of 10 LOG2 (x) Returns the logarithm with a base of 2 Returns the logarithm of base 6 with 2: MAX (expression) Returns the maximum value in the field expression Returns the maximum value of the field Price in the data table Products: MIN (expression) Returns the minimum value in the field expression Returns the minimum value of the field Price in the data table Products: MOD (XBI y) Returns the remainder of x divided by y The remainder of 5 divided by 2: PI () Returns pi (3.141593) POW (XBI y) Returns x to the y power 2 to the third power: POWER (XBI y) Returns x to the y power 2 to the third power: RADIANS (x) Convert angles to radians Convert 180 degrees to radians: RAND () Returns a random number from 0 to 1 ROUND (x [,y] ) Returns the integer closest to x. The optional parameter y represents the number of decimal places to be rounded. If omitted, the integer is returned. SIGN (x) Returns the symbol of x, where x is negative, 0, and positive returns-1, 0, and 1, respectively SIN (x) Find the sine (the parameter is radians) SQRT (x) Returns the square root of x The square root of 25: SUM (expression) Returns the sum of the specified fields Calculate the sum of the fields Quantity in the OrderDetails table: TAN (x) Find the tangent (the parameter is radians) TRUNCATE (XBI y) Returns the value x to the y place after the decimal point (the biggest difference from ROUND is that it is not rounded) Function name Description Example ADDDATE (dQuery n) Calculate the start date d plus the date of n days ADDTIME (tjinn) N is a time expression, time t plus time expression n Add 5 seconds: Add 2 hours, 10 minutes, 5 seconds: CURRENT_DATE () Returns the current date CURRENT_TIME Return to the current time CURRENT_TIMESTAMP () Returns the current date and time CURTIME () Return to the current time DATE () Extract a date value from a date or date-time expression DATEDIFF (d1 and d2) Calculate the number of days between date D1-> D2 DATE_ADD (dCentInterval expr type) After calculating the start date d plus a date after a time period, the type value can be: 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 DATE_FORMAT (dline f) Display the date d as required by the expression f DATE_SUB (date,INTERVAL expr type) Function subtracts the specified interval from the date. DAY (d) Returns the date portion of the date value d The OrderDate field in the Orders table minus 2 days: DAYNAME (d) Return date d is the day of the week, such as Monday,Tuesday DAYOFMONTH (d) The calculated date d is the day of this month. DAYOFWEEK (d) Date d what day is today, 1 Sunday, 2 Monday, and so on DAYOFYEAR (d) The calculated date d is the day of this year. EXTRACT (type FROM d) Gets the specified value from date d, and type specifies the returned value. 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 FROM_DAYS (n) Calculate the date n days after January 1, 2000 HOUR (t) Returns the hourly value in t LAST_DAY (d) Returns the last day of January to a given date LOCALTIME () Returns the current date and time LOCALTIMESTAMP () Returns the current date and time MAKEDATE (year, day-of-year) Returns a date based on the given parameter year year and the number of days in the year day-of-year MAKETIME (hour, minute, second) Combination time. Parameters are hours, minutes and seconds, respectively. MICROSECOND (date) Returns the number of microseconds corresponding to the date parameter MINUTE (t) Returns the minute value in t MONTHNAME (d) Returns the month name among the dates, such as November MONTH (d) Returns the month value in date d, 1 to 12 NOW () Returns the current date and time PERIOD_ADD (period, number) Add a period for the year-month combination date PERIOD_DIFF (period1, period2) Returns the month difference between two periods QUARTER (d) Return date d is the season, return 1 to 4 SECOND (t) Returns the second value in t SEC_TO_TIME (s) Convert time s in seconds to hours and seconds STR_TO_DATE (string, format_mask) Convert a string to a date SUBDATE (dQuery n) Date d minus n days later SUBTIME (tjinn) Time t minus n seconds SYSDATE () Returns the current date and time TIME (expression) Extract the time portion of the incoming expression TIME_FORMAT (tmenf) Display the time t as required by the expression f TIME_TO_SEC (t) Convert time t to seconds TIMEDIFF (time1, time2) Calculate the time difference TIMESTAMP (expression, interval) When there is a single parameter, the function returns a date or date-time expression; when there are two parameters, the parameters are added. TIMESTAMPDIFF (unit,datetime_expr1,datetime_expr2) Calculates the time difference and returns the time difference of datetime_expr2 − datetime_expr1 TO_DAYS (d) Calculate the number of days from date d to January 1, 2000 WEEK (d) The calculated date d is the week ordinal of this year, ranging from 0 to 53 WEEKDAY (d) The date d is what day of the week, 0 means Monday, 1 means Tuesday WEEKOFYEAR (d) The calculated date d is the week ordinal of this year, ranging from 0 to 53 YEAR (d) Returns the year YEARWEEK (date, mode) Returns the year and week (0 to 53). In mode, 0 represents Sunday, 1 represents Monday, and so on.MySQL string function ¶
SELECT
ASCII(CustomerName)
AS
NumCodeOfFirstChar
FROM Customers;
SELECT CHAR_LENGTH("RUNOOB") AS LengthOfString;
SELECT CHARACTER_LENGTH("RUNOOB") AS LengthOfString;
SELECT CONCAT("SQL ", "Runoob ", "Gooogle ", "Facebook") AS ConcatenatedString;
SELECT CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!")AS ConcatenatedString;
SELECT FIELD("c", "a", "b", "c", "d", "e");
SELECT FIND_IN_SET("c", "a,b,c,d,e");
SELECT FORMAT(250500.5634, 2); -- 输出 250,500.56
SELECT INSERT("google.com", 1, 6, "runoob"); -- 输出：runoob.com
SELECT LOCATE('st','myteststring'); -- 5
SELECT LOCATE('b', 'abc') -- 2
SELECT LCASE('RUNOOB') -- runoob
SELECT LEFT('runoob',2) -- ru
SELECT LOWER('RUNOOB') -- runoob
SELECT LPAD('abc',5,'xx') -- xxabc
SELECT LTRIM(" RUNOOB") AS LeftTrimmedString;-- RUNOOB
SELECT MID("RUNOOB", 2, 3) AS ExtractString; -- UNO
SELECT POSITION('b' in 'abc') -- 2
SELECT REPEAT('runoob',3) -- runoobrunoobrunoob
SELECT REPLACE('abc','a','x') --xbc
SELECT REVERSE('abc') -- cba
SELECT RIGHT('runoob',2) -- ob
SELECT RPAD('abc',5,'xx') -- abcxx
SELECT RTRIM("RUNOOB ") AS RightTrimmedString; -- RUNOOB
SELECT SPACE(10);
SELECT STRCMP("runoob", "runoob"); -- 0
SELECT SUBSTR("RUNOOB", 2, 3) AS ExtractString; -- UNO
SELECT SUBSTRING("RUNOOB", 2, 3) AS ExtractString; -- UNO
SELECT SUBSTRING_INDEX('a*b','*',1) -- a
SELECT SUBSTRING_INDEX('a*b','*',-1) -- b
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d*e','*',3),'*',-1) -- c
SELECT TRIM(' RUNOOB ') AS TrimmedString;
SELECT UCASE("runoob"); -- RUNOOB
SELECT UPPER("runoob"); -- RUNOOB
MySQL digital function ¶
SELECT ABS(-1) -- 返回1
SELECT ACOS(0.25);
SELECT ASIN(0.25);
SELECT ATAN(2.5);
SELECT ATAN2(-0.8, 2);
SELECT AVG(Price) AS AveragePrice FROM Products;
SELECT CEIL(1.5) -- 返回2
SELECT CEILING(1.5); -- 返回2
SELECT COS(2);
SELECT COT(6);
SELECT DEGREES(3.1415926535898) -- 180
SELECT LOG(20.085536923188) -- 3
SELECT LOG10(100) -- 2
SELECT LOG2(6); -- 2.584962500721156
SELECT MAX(Price) AS LargestPrice FROM Products;
SELECT MIN(Price) AS MinPrice FROM Products;
SELECT MOD(5,2) -- 1
SELECT PI() --3.141593
SELECT POW(2,3) -- 8
SELECT POWER(2,3) -- 8
SELECT RADIANS(180) -- 3.1415926535898
SELECT RAND() --0.93099315644334
SELECT ROUND(1.23456) --1
SELECT ROUND(345.156, 2) -- 345.16
SELECT SIGN(-10) -- (-1)
SELECT SIN(RADIANS(30)) -- 0.5
SELECT SQRT(25) -- 5
SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;
SELECT TAN(1.75); -- -5.52037992250933
SELECT TRUNCATE(1.23456,3) -- 1.234
MySQL date function ¶
SELECT ADDDATE("2017-06-15", INTERVAL 10 DAY);
->2017-06-25
SELECT ADDTIME('2011-11-11 11:11:11', 5);
->2011-11-11 11:11:16 (秒)
SELECT ADDTIME("2020-06-15 09:34:21", "2:10:5");
-> 2020-06-15 11:44:26
SELECT CURDATE();
-> 2018-09-19
SELECT CURRENT_DATE();
-> 2018-09-19
SELECT CURRENT_TIME();
-> 19:59:02
SELECT CURRENT_TIMESTAMP()
-> 2018-09-19 20:57:43
SELECT CURTIME();
-> 19:59:02
SELECT DATE("2017-06-15");
-> 2017-06-15
SELECT DATEDIFF('2001-01-01','2001-02-02')
-> -32
SELECT DATE_ADD("2017-06-15", INTERVAL 10 DAY);
-> 2017-06-25
SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL 15 MINUTE);
-> 2017-06-15 09:49:21
SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 HOUR);
->2017-06-15 06:34:21
SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 MONTH);
->2017-04-15
SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r')
-> 2011-11-11 11:11:11 AM
SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDate
FROM Orders
SELECT DAY("2017-06-15");
-> 15
SELECT DAYNAME('2011-11-11 11:11:11')
->Friday
SELECT DAYOFMONTH('2011-11-11 11:11:11')
->11
SELECT DAYOFWEEK('2011-11-11 11:11:11')
->6
SELECT DAYOFYEAR('2011-11-11 11:11:11')
->315
SELECT EXTRACT(MINUTE FROM '2011-11-11 11:11:11')
-> 11
SELECT FROM_DAYS(1111)
-> 0003-01-16
SELECT HOUR('1:2:3')
-> 1
SELECT LAST_DAY("2017-06-20");
-> 2017-06-30
SELECT LOCALTIME()
-> 2018-09-19 20:57:43
SELECT LOCALTIMESTAMP()
-> 2018-09-19 20:57:43
SELECT MAKEDATE(2017, 3);
-> 2017-01-03
SELECT MAKETIME(11, 35, 4);
-> 11:35:04
SELECT MICROSECOND("2017-06-20 09:34:00.000023");
-> 23
SELECT MINUTE('1:2:3')
-> 2
SELECT MONTHNAME('2011-11-11 11:11:11')
-> November
SELECT MONTH('2011-11-11 11:11:11')
->11
SELECT NOW()
-> 2018-09-19 20:57:43
SELECT PERIOD_ADD(201703, 5);
-> 201708
SELECT PERIOD_DIFF(201710, 201703);
-> 7
SELECT QUARTER('2011-11-11 11:11:11')
-> 4
SELECT SECOND('1:2:3')
-> 3
SELECT SEC_TO_TIME(4320)
-> 01:12:00
SELECT STR_TO_DATE("August 10 2017", "%M %d %Y");
-> 2017-08-10
SELECT SUBDATE('2011-11-11 11:11:11', 1)
->2011-11-10 11:11:11 (默认是天)
SELECT SUBTIME('2011-11-11 11:11:11', 5)
->2011-11-11 11:11:06 (秒)
SELECT SYSDATE()
-> 2018-09-19 20:57:43
SELECT TIME("19:30:10");
-> 19:30:10
SELECT TIME_FORMAT('11:11:11','%r')
11:11:11 AM
SELECT TIME_TO_SEC('1:12:00')
-> 4320
mysql> SELECT TIMEDIFF("13:10:11", "13:10:10");
-> 00:00:01
mysql> SELECT TIMEDIFF('2000:01:01 00:00:00',
-> '2000:01:01 00:00:00.000001');
-> '-00:00:00.000001'
mysql> SELECT TIMEDIFF('2008-12-31 23:59:59.000001',
-> '2008-12-30 01:01:01.000002');
-> '46:58:57.999999'
mysql> SELECT TIMESTAMP("2017-07-23", "13:10:11");
-> 2017-07-23 13:10:11
mysql> SELECT TIMESTAMP('2003-12-31');
-> '2003-12-31 00:00:00'
mysql> SELECT TIMESTAMP('2003-12-31 12:00:00','12:00:00');
-> '2004-01-01 00:00:00'
mysql> SELECT TIMESTAMPDIFF(DAY,'2003-02-01','2003-05-01'); // 计算两个时间相隔多少天
-> 89
mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01'); // 计算两个时间相隔多少月
-> 3
mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01'); // 计算两个时间相隔多少年
-> -1
mysql> SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55'); // 计算两个时间相隔多少分钟
-> 128885
SELECT TO_DAYS('0001-01-01 01:01:01')
-> 366
SELECT WEEK('2011-11-11 11:11:11')
-> 45
SELECT WEEKDAY("2017-06-15");
-> 3
SELECT WEEKOFYEAR('2011-11-11 11:11:11')
-> 45
SELECT YEAR("2017-06-15");
-> 2017
SELECT YEARWEEK("2017-06-15");
-> 201724