Common functions of MySQL

MySQL has the following common functions to master:

1. Mathematical Functions

Function name	Role
ABS (x)	Returns the absolute value of X
SQRT (x)	Returns the non-negative two-square root of X
MOD (x, y)	Returns the remainder after x is removed by y
CEILING (x)	Returns the smallest integer not less than X
Floor (x)	Returns the largest integer less than x
ROUND (x, y)	Rounding x, leaving the Y position after the decimal point
TRUNCATE (x, y)	Number of y digits after rounding x decimal points
Sign (x)	Returns the symbol for x, -1,0 or 1

2. String class functions

Function name	Role
LENGTH (x)	Returns the length of the string str
CONCAT (S1,s2,...)	Returns a new string that is generated by one or more string connections
TRIM (str)	Remove spaces on both sides of a string
REPLACE (STR,S1,S2)	Use string s2 instead of all strings in string str s1
SUBSTRING (Str,n,len)	Returns the substring of the string str with the starting position N and len length
REVERSE (str)	Returns the result of a string str reversal
LOCATE (S1,STR)	Returns the position of the substring S1 in the string str

3. Date and Time functions

Function name	Role
Curdate ()	Get System Current Date
Curtime ()	Get System Current Time
Sysdate ()	Gets the date and time of the current system
Time_to_sec ()	Returns the result of converting a date to a second
Adddate ()	Add operations on the execution date
Sbudate ()	Minus operations on the execution date
Date_format ()	Format output date and time values

4. Conditional Judgment function

Function name	Role
IF (EXPR,V1,V2)	Returns v1 if the expr expression is true, otherwise v2
Ifnull (V1,V2)	Returns v1 If V1 is not NULL, otherwise v2
Case Expr-V1 then R1[when v2 then R2 ...] [ELSE RN] END	If the value of expr equals V1, V2, and so on, returns the result after the corresponding position then, otherwise returns the result of the else after the RN

5. Cryptographic functions

Function name	Role
MD5 (str)	MD5 Encryption of String str
ENCODE (STR,PWD_STR)	Encrypt strings using PWD as a password str
DECODE (STR,PWD_STR)	Decrypting a string using PWD as a password str

Common functions of MySQL