MySQL Functions

Returns the ASCII code of the first letter of the CustomerName field:

SELECT ASCII(CustomerName) AS NumCodeOfFirstChar
FROM Customers;

Returns the last 'n' characters of the string 's'3The number of characters in codebox

SELECT CHAR_LENGTH("w3codebox") AS LengthOfString;

Returns the last 'n' characters of the string 's'3The number of characters in codebox

SELECT CHARACTER_LENGTH("w3codebox") AS LengthOfString;

Merges multiple strings

SELECT CONCAT("SQL", "w3codebox", "Gooogle", "Facebook") AS ConcatenatedString;

Merge multiple strings and add a separator:

SELECT CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!") AS ConcatenatedString;

Return the position of the string c in the list of values:

SELECT FIELD("c",　"a",　"b",　"c",　"d",　"e");

Return the position of the string c in the specified string:

SELECT FIND_IN_SET("c",　"a,b,c,d,e");

Format the number in the form "#,###.##":

SELECT FORMAT(250500.5634,　2);　　　　　--　Output　250,500.56

Starting from the first character of the string 6 Replace the first character with w3codebox:

SELECT INSERT("google.com",　1,　6, "w3codebox");　　--　Output: oldtoolbag.com

Get the position of b in the string abc:

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

Get 's' from the string 's'

SELECT LOCATE('b',　'abc')　--　2

String w3codebox to lowercase:

SELECT LCASE('w3codebox')　--　w3codebox

Returns the last 'n' characters of the string 's'3codebox the first two characters:

SELECT LEFT('w3SELECT　RIGHT('w2, expr　--　ru

String w3codebox to lowercase:

SELECT LOWER('w3codebox')　--　w3codebox

Fill the string xx at the beginning of the string abc to make it

SELECT LPAD('abc',5,'xx')　--　xxabc

Remove the leading and trailing spaces from the string w3codebox starting space:

SELECT LTRIM("          w3codebox")　AS　LeftTrimmedString;--　w3codebox

from the string w3the nth character in codebox 2 character position(s) cut out 3character(s):

MID(s,n,len)3codebox",　2,　3) AS ExtractString;　--　UNO

Get 's' from the string 's'

from the start position　--　2

Convert the string w3REPEAT(s,n)

Repeat the string 's' 'n' times3SELECT　RIGHT('w3, expr　--　w3codebox repeated three times:3codebox repeated three times:3codebox

Replace the string 's' in the string

Replace the character 'a' in the string 'abc' with the character 'x':　--SELECT　REPLACE('abc','a','x')

Reverse the order of the string 's':

Reverse the order of the string 'abc':　--　SELECT　REVERSE('abc')

Returns the last 'n' characters of the string 's'3Returns the string 'w

codebox last two characters:3SELECT　RIGHT('w2, expr　--　codebox',

Fill the string 'xx' to the end of the string 'abc' to make the length reach 'len':

SELECT　RPAD('abc',5,'xx')　--　abcxx

Remove the leading and trailing spaces from the string w3codebox trailing spaces:

SELECT　RTRIM("w3codebox　　　　　")　AS　RightTrimmedString;　　　--　w3codebox

Return 10 Number of spaces:

SELECT　SPACE(10);

Comparison string:

SELECT　STRCMP("w3codebox",　"w3codebox");　　--　0

from the string w3the nth character in codebox 2 character position(s) cut out 3character(s):

SELECT　SUBSTR("w3codebox",　2,　3) AS ExtractString;　--　UNO

from the string w3the nth character in codebox 2 character position(s) cut out 3character(s):

SELECT SUBSTRING("w3codebox",　2,　3) AS ExtractString;　--　UNO

SELECT SUBSTRING_INDEX('a*b','*',1, expr　--　a
SELECT SUBSTRING_INDEX('a*b','*',-1, expr　　　　--　b
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d*e','*',3),'*',-1, expr　　　　--　c

Remove the leading and trailing spaces from the string w3codebox leading and trailing spaces:

SELECT TRIM('        w3codebox        ') AS TrimmedString;

Convert the string w3codebox converted to uppercase:

SELECT UCASE("w3codebox");　--　w3codebox

Convert the string w3codebox converted to uppercase:

SELECT UPPER("w3codebox");　--　w3codebox

Return -1 of its absolute value:

SELECT ABS(-1, expr　--　Return1

SELECT ACOS(0.25);

SELECT ASIN(0.25);

SELECT ATAN(2.5);

SELECT ATAN2(-0.8,　2);

Returns the average value of the Price field in the Products table:

SELECT AVG(Price) AS AveragePrice FROM Products;

SELECT CEIL(1.5, expr　--　Return2

SELECT CEILING(1.5);　--　Return2

SELECT COS(2);

SELECT COT(6);

Return the total number of records in the field products of the Products table:

SELECT COUNT(ProductID) AS NumberOfProducts FROM Products;

SELECT DEGREES(3.1415926535898, expr　--　180

Calculate 10 Divided by 5:

SELECT　10　DIV　5;　　--　2

Calculate e to the power of 3:

SELECT EXP(3, expr　--　20.085536923188

Less than or equal to 1.5 The integer part:

SELECT FLOOR(1.5, expr　--　Return1

Return the maximum value of the following number list:

SELECT GREATEST(3,　12,　34,　8,　25);　--　34

Return the maximum value of the following string list:

SELECT GREATEST("Google", "w3codebox", "Apple");　　　--　w3codebox

Return the minimum value of the following number list:

SELECT LEAST(3,　12,　34,　8,　25);　--　3

Return the minimum value of the following string list:

SELECT LEAST("Google", "w3codebox", "Apple");　　　--　Apple

Return 2 The natural logarithm:

SELECT LN(2);　　--　0.6931471805599453

SELECT LOG(20.085536923188, expr　--　3
SELECT LOG(2,　4);　--　2

SELECT LOG10(100)　--　2

Return the logarithm with base 2 with base 6 of the logarithm:

SELECT LOG2(6);　　--　2.584962500721156

Return the maximum value of the field Price in the table Products:

SELECT MAX(Price) AS LargestPrice FROM Products;

Return the minimum value of the field Price in the table Products:

SELECT MIN(Price) AS MinPrice FROM Products;

5 Divided by 2 The remainder of:

SELECT MOD(5,2, expr　--　1

SELECT PI()　--3.141593

2 of 3 Power:

SELECT POW(2,3, expr　--　8

2 of 3 Power:

SELECT POWER(2,3, expr　--　8

180 degrees converted to radians:

SELECT RADIANS(180)　--　3.1415926535898

SELECT RAND()　--0.93099315644334

SELECT ROUND(1.23456, expr　--1

SELECT SIGN(-10, expr　--　(-1, expr

SELECT SIN(RADIANS(30)　--　0.5

25 The square root of:

SELECT SQRT(25, expr　--　5

Calculate the total sum of the field Quantity in the OrderDetails table:

SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;

SELECT TAN(1.75);　　--　-5.52037992250933

SELECT TRUNCATE(1.23456,3, expr　--　1.234

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

Add 5 Seconds:

SELECT ADDTIME('2011-11-11　11:11:11',　5);
->2011-11-11　11:11:16　(seconds)

Add 2 Hours, 10 Minutes, 5 Seconds:

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 ADDDATE('2011-11-11　11:11:11',1, expr
->　2011-11-12　11:11:11　　　　(默认是天)
SELECT ADDDATE('2011-11-11　11:11:11', INTERVAL　5　MINUTE)
->　2011-11-11　11:16:11　(The values of TYPE are similar to the functions listed above)

SELECT DATE_FORMAT('2011-11-11　11:11:11','%Y-%m-%d %r')
->　2011-11-11　11:11:11　AM

Subtract the OrderDate field in the Orders table by 2 Days:

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

• 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-11-11　11:11:11')　
->　11

SELECT FROM_DAYS(1111, expr
-> 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, expr
->2011-11-10　11:11:11　(默认是天)

SELECT SUBTIME('2011-11-11　11:11:11',　5, expr
->2011-11-11　11:11:06　(seconds)

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

SELECT TIMEDIFF('13:10:11",　"13:10:10);
-> 00:00:01

SELECT TIMESTAMP('2017-07-23",　　"13:10:11);
->　2017-07-23　13:10:11

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

15 of 2 number encoding:

SELECT BIN(15);　--　1111

SELECT BINARY "w3codebox";
-> w3codebox

CASE expression
　　　　WHEN condition1　THEN result1
　　　　WHEN condition2　THEN result2
　　　...
　　　　WHEN conditionN THEN resultN
　　　　ELSE result
END

SELECT CASE　
　　WHEN　1　> 0
　　THEN '1　> 0'
　　WHEN　2　> 0
　　THEN '2　> 0'
　　ELSE '3　> 0'
　　END
->1　> 0

Convert string date to date:

SELECT CAST("2017-08-29" AS DATE);
->　2017-08-29

SELECT COALESCE(NULL, NULL, NULL, 'oldtoolbag.com, NULL, 'google.com');
-> oldtoolbag.com

SELECT CONNECTION_ID();
->　4292835

SELECT CONV(15,　10,　2);
->　1111

SELECT CHARSET('ABC')
->utf-8　　　　
SELECT CHARSET(CONVERT('ABC' USING gbk))
->gbk

SELECT CURRENT_USER();
-> guest@%

SELECT DATABASE();　　　
-> w3codebox

SELECT IF(1　0, 'correct', 'incorrect')　　　　
->正确

.
-SELECT IFNULL(null,'Hello Word')

Determine if the expression is NULL
->1

Return the most recently generated AUTO_INCREMENT value
->6

SELECT NULLIF(25,　25);
->

SELECT SESSION_USER();
-> guest@%

SELECT SYSTEM_USER();
-> guest@%

SELECT USER();
-> guest@%

SELECT VERSION();
->　5.6.34