The built-in functions of MySQL can be roughly divided into the following categories:

1. Processing text functions of text data (such as splicing, case conversion, etc.);

2. A numerical function (such as an algebraic operation) that processes numerical data;

3. Date time class function;

4. System function.

 

Here we mainly introduce the first two kinds of common functions.

 

1, Text processing function

1. Clear the space (note that it is not blank, and indent, carriage return and line feed cannot be cleared)

-- Clear spaces at both ends of string
select TRIM(" abc ");   -- "abc"
-- Clear space at left end of string
select LTRIM(" abc ");  -- "abc "
-- Clear space at right end of string
select RTRIM(" abc ");  -- " abc"

2. Case conversion

-- String capitalization
select UPPER("abc"); -- "ABC"
-- String to lowercase
select LOWER("ABC"); -- "abc"

3. Intercept string

-- LEFT(str,len) Starting from the left end of the string len Character
select LEFT("some strings", 3); -- "som"
-- RIGHT(str,len) Intercept from the right end of the string len Character
select RIGHT("some strings", 2); -- "gs"
-- SUBSTR(str FROM pos FOR len) 
-- Starting from the left end of the string pos Beginning len Character
-- FROM as well as FOR Omission
select SUBSTR("some strings" FROM 2 FOR 3); -- ome
select SUBSTR("some strings", 2, 3); -- ome
-- SUBSTR(str FROM pos)
-- Starting from the left end of the string pos Start character to end of string
-- FROM Omission
select SUBSTR("some strings" FROM 2); -- ome strings
select SUBSTR("some strings", 2); -- ome strings

-- SUBSTRING() yes SUBSTR() Synonyms

4. Calculate string length

-- LENGTH(str) Returns the byte length of a string
-- 3 bytes for a Chinese character, 1 byte for a number or letter
select LENGTH("some strings"); -- 12
select LENGTH("Chinese characters"); -- 12

-- CHAR_LENGTH(str) Returns the length of the string
select CHAR_LENGTH("some strings"); -- 12
select CHAR_LENGTH("Chinese characters"); -- 4

-- CHARACTER_LENGTH(str) yes CHAR_LENGTH(str) Synonyms
-- Same effect CHAR_LENGTH(str)
select CHARACTER_LENGTH("some strings"); -- 12
select CHARACTER_LENGTH("Chinese characters"); -- 4

-- BIT_LENGTH(str) Returns the bit length of a string, 8 bits for a byte
-- Equivalent to length()Results multiplied by 8
select BIT_LENGTH("some strings"); -- 96
select BIT_LENGTH("Chinese characters"); -- 96

5. Find string
 

-- LOCATE(substr,str) Return substr stay str Position in
-- Subscript starts from 1. When 0 is returned, it means it does not exist
select LOCATE("st", "some strings"); -- 6
select LOCATE("so", "some strings"); -- 1
select LOCATE("sa", "some strings"); -- 0

-- LOCATE(substr,str,pos) 
-- from str Of pos Position start(Contain pos place) Return substr stay str Position in
select LOCATE("s", "some strings"); -- 1
select LOCATE("s", "some strings", 1); -- 1
select LOCATE("s", "some strings", 2); -- 6
select LOCATE("s", "some strings", 6); -- 6
select LOCATE("s", "some strings", 7); -- 12

6. SOUNDEX

-- Evaluate the SOUNDEX value
select SOUNDEX("Y Lee"); -- Y400
select SOUNDEX("Y Lie"); -- Y400
select SOUNDEX("Y Lee") = SOUNDEX("Y Lie"); -- 1

-- select SOUNDEX(str1) = SOUNDEX(str2)
-- String query effect with similar pronunciation
-- SOUNDEX(str) Function ignores Division alpha All characters except

2, Numerical function

1. absolute value

select ABS(1);		-- 1
select ABS(-1);		-- 1
-- What's interesting is that numeric functions accept only numeric strings
-- The following examples will not show the verification one by one
select ABS("10"); -- 10
select ABS("-10");-- 10

2. pI value

select pi(); -- 3.141593

3. Trigonometric function

-- The parameter value of trigonometric function is radian value
-- To use angle values, you need to use the pi()Function to convert
-- angle/180 = radian/pi
select pi(); 		-- 3.141593

-- Sine function
select SIN(0);	-- 0
select SIN(pi()/2);	-- 1

-- cosine function
select COS(0);  -- 1
select COS(pi()/2); -- 6.123...*e-17

-- tangent
select TAN(pi()/4); -- 0.99999...

-- Arcsine
select ASIN(1) / pi(); -- 0.5

-- Inverse cosine
select ACOS(0) / pi(); -- 0.5

-- Arc tangent
select ATAN(1) / pi(); -- 0.25

4. exponentiation

-- POW(X,Y) seek x Of y Secondary power
select POW(2,2);	-- 8
select POW(2,3);	-- 8
-- power Alias
select POWER(2,3);-- 8

-- with e Bottom index
select exp(1);	-- 2.718281828459045
select exp(2);	-- 7.38905609893065

5. seek surplus

select MOD(5,3); -- 2

6. random numbers

-- 0~1 Random number between
select RAND();

7. rounding

-- Rounding down
select FLOOR(5.5); 	-- 5
select FLOOR(-5.5); -- -6

-- Rounding up
select CEIL(5.5);	  -- 6
select CEIL(-5.5);	-- -5
-- CEILING Same effect ceil
select CEILING(5.5);	-- 6
select CEILING(-5.5); -- -5

-- round Function is rounded
select ROUND(5.4);	-- 5
select ROUND(5.5);	-- 6
select ROUND(5.6);	-- 6
-- ROUND(X,D) The second parameter specifies the number of decimal places to keep
-- ROUND(X) Amount to ROUND(X,0)
select ROUND(5.5555,2); -- 5.56
select ROUND(5.5555,3);	-- 5.556
-- Be careful D Can be negative equal to positive integer low position 0
select ROUND(5555.55,-2); -- 5600
select ROUND(5555.55,-3); -- 6000

8. square root

select sqrt(4);	-- 2
select sqrt(2); -- 1.4142135623730951