Mysql Common function Summary (share) _mysql

A, math function
ABS (x)    return x Absolute Value
BIN (x)    return x binary (Oct return octal, Hex returns hexadecimal)
CEILING (x)    returns the minimum integer value greater than x
EXP (x)    return value E (the base of natural logarithm) of X-square
FLOOR (x)    Returns the maximum integer value less than x
Greatest (x1,x2,..., xn) returns the largest value in the collection
least (x1,x2,..., xn)       returns the smallest value in the collection
LN (x)                      returns the natural logarithm of x
Log (x,y) returns the logarithm of Y to x
MOD (x,y)                   returns the modulus (remainder) of the x/y
Pi () returns PI (pi)
RAND () returns a random value from 0 to 1 by supplying a parameter ( Seed) causes the rand () random number generator to generate a specified value.
ROUND (x,y) returns the rounding of the parameter x with a Y-decimal value
SIGN (x) Returns the value of the symbol representing the number x
SQRT (x) returns the square root of a number
TRUNCATE (x,y)              returns the result of a number x truncated to a Y-decimal.

Two, aggregate functions (in select queries that are commonly used in GROUP BY clauses)
AVG (COL) returns the average value of the specified column
COUNT (COL) Returns the number of non-null values in the specified column
MIN (COL) returns the minimum value of the specified column
Max (COL) returns the maximum value of the specified column
SUM (col) returns the sum of all values for the specified column
Group_concat (COL) Returns the result of a combination of column-value connections that belong to a group

Three, String functions
ASCII value of ASCII (char) return character
Bit_length (str) returns the bit length of the string
CONCAT (S1,S2...,SN) connects S1,s2...,sn to a string
Concat_ws (SEP,S1,S2...,SN) connects the S1,S2...,SN to a string and uses the Sep character interval
INSERT (STR,X,Y,INSTR) begins string str from position x, and the substring of y characters is replaced with string InStr, which returns the result
Find_in_set (str,list) analyzes a comma-delimited list of lists and, if STR is found, returns the position of STR in the list
LCASE (str) or lower (str) Returns the result of changing all characters in the string str to lowercase
Left (str,x) returns the leftmost X character in the string str
LENGTH (s) returns the number of characters in the string str
LTRIM (str) cuts the opening space from the string str
POSITION (SUBSTR,STR) returns the substring substr the first occurrence in string str
QUOTE (str) escapes single quotes in Str with a backslash
REPEAT (STR,SRCHSTR,RPLCSTR) Returns the result of the string str repeats x times
REVERSE (str) Returns the result of the reversed string str
Right (STR,X) returns the rightmost X character in the string str
RTRIM (str) Returns the space of the string str tail
STRCMP (S1,S2) comparison string S1 and S2
TRIM (str) removes all whitespace from the header and tail of the string
UCASE (str) or upper (str) returns the result of converting all characters in the string str to uppercase

Four, date and time functions
Curdate () or current_date () returns the current date
Curtime () or Current_time () returns the current time
Date_add (date,interval int keyword) returns the result of date plus interval int (int must be formatted according to keywords), such as: Selectdate_add (Current_date,interval 6 MONTH);
Date_format (DATE,FMT) formats date values according to the specified FMT format
date_sub (date,interval int keyword) returns the result of date plus interval int (int must be formatted according to keywords), such as: Selectdate_sub (Current_date,interval 6 MONTH);
DayOfWeek (date) returns the day ordinal of the week represented by date (1~7)
DayOfMonth (date) Returns date is the first day of one months (1~31)
DayOfYear (date) Returns date is the first day of the Year (1~366)
Dayname (date) returns the week name of date, such as: SELECT Dayname (current_date);
From_unixtime (TS,FMT) Formats the UNIX timestamp TS according to the specified FMT format
HOUR (time) returns a time value (0~23)
MINUTE (time) returns the minute value of time (0~59)
MONTH (date) returns the month value of date (1~12)
MonthName (date) returns the month name of date, such as: SELECT MonthName (current_date);
Now () returns the current date and time
Quarter (date) returns the quarter (1~4) of date in one year, such as Select Quarter (current_date);
WEEK (date) Return date is the first week of the year (0~53)
Year (date) returns the date of day (1000~9999)
Some examples:

Copy Code code as follows:

Gets the current system time: SELECT From_unixtime (Unix_timestamp ());
SELECT EXTRACT (Year_month from current_date);
SELECT EXTRACT (Day_second from current_date);
SELECT EXTRACT (Hour_minute from current_date);

Returns the difference (number of months) between two date values: SELECT Period_diff (200302,199802);

calculates the age in MySQL:
SELECT date_format (from_days to_days (now ())-to_days (birthday)), '%Y ') +0 as from employee;
Thus, if the brithday is the future month and year, the result is 0.
The following SQL statement calculates the absolute age of an employee, that is, when birthday is a future date, a negative value is obtained.
SELECT Date_format (now (), '%Y ')-date_format (Birthday, '%Y ')-(The Date_format (now (), ' 00-%m-%d ') <date_format ( Birthday, ' 00-%m-%d ')) as age from employee

Five, encryption function
Aes_encrypt (Str,key) Returns the result of encrypting the string str using the Advanced Encryption Standard algorithm with key keys, the result of the invocation aes_encrypt is a binary string, stored as a BLOB type
Aes_decrypt (Str,key) Returns the result of using the Advanced Encryption Standard algorithm to decrypt string str with key keys
DECODE (Str,key) decrypts the cryptographic string using key as the key. Str
ENCRYPT (Str,salt) uses the Unixcrypt () function to encrypt string str using the keyword salt (a string that can uniquely determine the password, like a key)
ENCODE (Str,key) uses key to encrypt string Str as a key, and the result of calling ENCODE () is a binary string that is stored as a BLOB type
MD5 () computes the MD5 checksum of the string str
PASSWORD (str) returns the encrypted version of the string str, which is irreversible and uses a different algorithm for the UNIX password encryption process.
SHA () Compute the Secure Hash Algorithm (SHA) checksum for string str
Example:

Copy Code code as follows:

SELECT ENCRYPT (' root ', ' salt ');
SELECT ENCODE (' Xufeng ', ' key ');
SELECT DECODE (ENCODE (' Xufeng ', ' key '), ' key '); #加解密放在一起
SELECT aes_encrypt (' root ', ' key ');
SELECT Aes_decrypt (aes_encrypt (' root ', ' key '), ' key ');
SELECT MD5 (' 123456 ');
SELECT SHA (' 123456 ');

Six, control flow function
MySQL has 4 functions for conditional operations that implement the conditional logic of SQL, allowing developers to convert some application business logic to the database backend.

MySQL Control flow function:
Case When[test1] THEN [RESULT1] ... else [default] End If TESTN is true, return RESULTN, otherwise return default
case [Test] when[val1] THEN [result] ... ELSE [default]end if test and valn are equal, return RESULTN, otherwise return default
If test is true, return t; otherwise, F is returned to test,t,f.
Ifnull (ARG1,ARG2) If arg1 is not empty, return to Arg1, otherwise return arg2
Nullif (ARG1,ARG2) if arg1=arg2 returns null;
The first of these functions is ifnull (), which has two parameters and is judged by the first argument. If the first argument is not NULL, the function returns the first argument to the caller, and if it is null, the second argument is returned.

such as: SELECT ifnull (1,2), ifnull (null,10), ifnull (4*null, ' false ');
The Nullif () function will verify that the two arguments provided are equal and, if they are equal, return null and, if not equal, return the first argument.

such as: SELECT Nullif (1,1), Nullif (' A ', ' B '), Nullif (2+3,4+1);
Like the IF () function provided by many scripting languages, the if () function of MySQL can also create a simple conditional test, this function has three parameters, the first is the expression to be judged, if the expression is true, if () will return the second argument, if False, if () will return the third argument.

such as: Selectif (1<10,2,3), if (56>100, ' true ', ' false ');
The IF () function is only suitable for use when there are only two possible results. However, in the real world, we may find that multiple branches are required in a conditional test. In this case, MySQL provides case functions, as well as PHP and Perl language switch-case conditional routines.
The format of the case function is somewhat complex, usually as follows:

Copy Code code as follows:

case [expression to be evaluated]
When [Val 1] THEN [result 1]
When [Val 2] THEN [result 2]
When [Val 3] THEN [result 3]
......
When [Val n] THEN [result N]
ELSE [Default result]
End

Here, the first parameter is the value or expression to be judged, followed by a series of when-then blocks, the first parameter of each block specifies the value to compare, and if true, the result is returned. All When-then blocks will end with an else block, and when end ends all external case blocks, if each of the preceding blocks does not match, the default result specified by the else block is returned. If you do not specify an else block, and all when-then comparisons are not true, MySQL returns null.
The case function also has another syntax, which is sometimes convenient to use, as follows:
Case

Copy Code code as follows:

When [conditional test 1] THEN [result 1]
When [conditional test 2] THEN [result 2]
ELSE [Default result]
End

In this condition, the result returned depends on whether the corresponding condition test is true.
Example:

Copy Code code as follows:

Mysql>select case ' green '
When ' Red ' THEN ' stop '
When the ' green ' THEN ' go ' end;
SELECT Case 9 If 1 THEN ' a ' when 2 THEN ' B ' ELSE ' n/a ' end;
SELECT case When (2+2) =4 the THEN ' OK ' when (2+2) <>4 THEN ' Don't OK ' end asstatus;
SELECT name,if ((isactive = 1), ' activated ', ' inactive ') as result fromuserlogininfo;
SELECT Fname,lname, (math+sci+lit) as Total,
Case when (Math+sci+lit) < THEN ' D '
When (math+sci+lit) BETWEEN THEN ' C '
When (Math+sci+lit) BETWEEN 151 and THEN ' B '
ELSE ' A ' end
As grade from Marks;
SELECT IF (ENCRYPT (' Sue ', ' ts ') =upass, ' Allow ', ' deny ') as Loginresultfrom users WHERE uname = ' Sue '; #一个登陆验证

Vii. formatting functions
Date_format (DATE,FMT) Format Date value according to string FMT
Format (x,y) formats the x as a comma-separated number sequence, and y is the number of decimal places for the result
Inet_aton (IP) Returns the number representation of an IP address
Inet_ntoa (num) returns the IP address represented by the number
Time_format (TIME,FMT) format time value according to string FMT
The simplest of these is the format () function, which formats a large number as a comma-spaced, readable sequence.
Example:

Copy Code code as follows:

SELECT FORMAT (34234.34323432,3);
SELECT Date_format (now (), '%w,%d%m%Y%r ');
SELECT Date_format (now (), '%y-%m-%d ');
SELECT date_format (19990330, '%y-%m-%d ');
SELECT Date_format (now (), '%h:%i%p ');
SELECT Inet_aton (' 10.122.89.47 ');
SELECT Inet_ntoa (175790383);

Viii. type conversion function
For data type conversion, MySQL provides the cast () function, which converts a value to the specified data type. Types are: binary,char,date,time,datetime,signed,unsigned
Example:

Copy Code code as follows:

SELECT CAST (now () as signed INTEGER), Curdate () +0;
SELECT ' F ' =binary ' f ', ' F ' =cast (' F ' as BINARY);

Ix. System Information function
DB () returns the current database name
BENCHMARK (count,expr) repeatedly runs the expression expr count times
CONNECTION_ID () Returns the connection ID of the current customer
Found_rows () returns the total number of rows retrieved by the last select query
User () or system_user () returns the current login username
Version () return to MySQL server versions
Example:

Copy Code code as follows:

SELECT DATABASE (), VERSION (), USER ();
Selectbenchmark (9999999,log (rand () *pi ()) #该例中, MySQL calculates LOG (rand () *pi ()) expression 9,999,999 times.