MySQL nine categories of commonly used functions

First, mathematical functions

ABS (x) returns the absolute value of X
BIN (x) returns the binary of X (Oct returns octal, HEX returns hex)
CEILING (x) returns the smallest integer value greater than X
EXP (x) return value E (base of natural logarithm) x Times 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 X-base logarithm
MOD (x, y) returns the modulo (remainder) of x/y
Pi () returns the value of Pi (pi)
RAND () returns a random value from 0 to 1, which enables the rand () random number generator to generate a specified value by providing a parameter (seed).
ROUND (x, y) returns the rounding of parameter x with a value of y decimal place
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 Y decimal places

Second, aggregation function (commonly used in the GROUP BY clause in the SELECT query)

Avg (COL) returns the average 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 of the specified column
Group_concat (COL) Returns the result of a combination of column value connections belonging to a group

"List the results of the highest salary per department", the SQL statement is as follows:

SELECT DEPT, MAX (SALARY) as MAXIMUM from the staff GROUP by DEPT

"Query the total number of salaries per department"
SELECT DEPT, SUM (SALARY) as total from the staff GROUP by DEPT

Specific usage reference: MySQLGROUP by where has usage details

Three, String function

ASCII (char) returns the ASCII code value of a character
Bit_length (str) returns the bit length of a string
CONCAT (S1,S2...,SN) to concatenate s1,s2...,sn into a string
Concat_ws (SEP,S1,S2...,SN) joins the S1,S2...,SN into a string and uses the Sep character interval
Insert (STR,X,Y,INSTR) starts the string str from the x position, and the Y-character substring is replaced with a string InStr, which returns

Back to Results
Find_in_set (str,list) parses 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 a 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 position of the substring substr the first occurrence in the string str
QUOTE (str) escapes single quotation marks in Str with a backslash
REPEAT (STR,SRCHSTR,RPLCSTR) Returns the result of a string str repeating x times
REVERSE (str) Returns the result of reversing the string str
Right (STR,X) returns the rightmost X character in a string str
RTRIM (str) returns a space at the trailing end of a string str
STRCMP (S1,S2) comparing strings S1 and S2
TRIM (str) removes all whitespace from string header and trailing
UCASE (str) or upper (str) returns the result of converting all characters in the string str to uppercase

LOCATE (SUBSTR,STR)
POSITION (substr in str)
Returns the position of the substring substr the first occurrence in the string str. If the substring substr does not exist in STR, the return value is 0:
mysql> SELECT LOCATE (' Bar ', ' Foobarbar ');
-4 (description is counted starting from 1)
mysql> SELECT LOCATE (' Xbar ', ' foobar ');
0

This function is multi-byte safe. In MySQL 3.23, this function is case-sensitive, and when in MySQL 4.0, if either parameter is a binary string, it is case-sensitive.

LOCATE (Substr,str,pos)
Returns the position of the substring substr the first occurrence after the POS position in the string str. If SUBSTR does not return 0 in str:
mysql> SELECT LOCATE (' Bar ', ' Foobarbar ', 5);
7

This function is multi-byte safe. In MySQL 3.23, this function is case-sensitive, and when in MySQL 4.0, if either parameter is a binary string, it is case-sensitive.

A typical example is:
Slect * FROM TableName where LOCATE (colum1,colum1,1) >0

Iv. 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 date plus the result of the time interval int (int must follow the key

format), such as: SELECT

Date_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 date plus the result of the time interval int (int must follow the key

format), such as: SELECT

Date_sub (current_date,interval 6 MONTH);
DAYOFWEEK (date) returns the day ordinal of the week represented by date (1~7)
DayOfMonth (date) Return date is the day of the one month (1~31)
DayOfYear (date) returns the day ordinal of a year (1~366)
Dayname (date) returns the weekday name of date, such as SELECT dayname (current_date);
From_unixtime (TS,FMT) formats UNIX timestamp TS According to the specified FMT format
HOUR (time) returns the hour value (0~23)
MINUTE (time) returns the minute value of time (0~59)
Month (date) returns the months 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 a date in the quarter of the year (1~4), such as Select QUARTER (current_date);
WEEK (date) Returns a date of the week ordinal of a year (0~53)
Year (date) returns the date of day (1000~9999)
Some examples:
Get 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);
Calculate 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 a future month or so, 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 ')-(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 with the key key using the Advanced Encryption Standard algorithm, calling

The result of the aes_encrypt is a binary

String, stored as BLOB type
Aes_decrypt (Str,key) Returns the result of decrypting the string str with the key key using the Advanced Encryption Standard algorithm
DECODE (Str,key) uses key as key to decrypt encrypted string str
ENCRYPT (Str,salt) uses the Unix crypt () function, with the keyword salt (a string that can uniquely determine the password,

Just like a key.) Encrypt string str
ENCODE (Str,key) uses key as the key to encrypt the string str, and the result of calling ENCODE () is a binary string

, which 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, encrypted with a UNIX password

Different algorithms are used for the process.
SHA () computes the Secure Hash Algorithm (SHA) checksum of the string str
Example:
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 ');

VI. Control Flow function

MySQL has 4 functions for conditional operation, which can implement the conditional logic of SQL, allowing the developer to

Converting the program business logic to the database background

。
MySQL Control flow function:
Case When[test1] then [RESULT1] ... ELSE [Default] END if TESTN is true, returns RESULTN, no

Returns the default
case [Test] when[val1) Then [result] ... ELSE [Default] END if test and valn are equal, the return

Back to Resultn, otherwise return to default
if (test,t,f) if test is true, returns T;
Ifnull (ARG1,ARG2) If arg1 is not empty, return arg1, otherwise return arg2
Nullif (ARG1,ARG2) If ARG1=ARG2 returns NULL, otherwise returns ARG1
The first of these functions is ifnull (), which has two parameters and is judged on the first parameter. If the first parameter does not

Is null, the function returns to the caller

A parameter, or NULL to return the second argument.
such as: SELECT Ifnull, Ifnull (null,10), ifnull (4*null, ' false ');
The Nullif () function will verify that the supplied two parameters are equal, return NULL if they are equal, and return if they are not equal.

The first parameter.
such as: SELECT Nullif, Nullif (' A ', ' B '), Nullif (2+3,4+1);
Like the IF () function provided by many scripting languages, the if () function of MySQL can also establish a simple conditional test, which

Number has three parameters, the first one is to be judged

expression, if the expression is true, if () returns the second argument, if False, if () returns a third argument.
such as: SELECT if (1<10,2,3), if (56>100, ' true ', ' false ');
The IF () function is suitable for use only if there are only two possible outcomes. In the real world, however, we may find that in a conditional test

Multiple branches are required. In this case,

MySQL provides the case function, which is the same as the switch-case conditional routine for PHP and the Perl language.
The format of the case function is somewhat complex and usually resembles the following:
case [expression to being 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 of each block

parameter specifies the value to compare, if true,

The result is returned. All When-then blocks will end with the else block, and when end ends all external case blocks, if the previous

Each block does not match will return to else

The default result that the block specifies. If you do not specify an else block and all when-then comparisons are not true, MySQL will return

Null.
The case function has another syntax, which is sometimes very convenient to use, as follows:
Case
When [conditional test 1] Then [result 1]
When [conditional test 2] Then [result 2]
ELSE [Default result]
END
In this condition, the result of the return depends on whether the appropriate condition test is true.
Example:
Mysql>select case ' green '
When the ' red ' then ' stop '
When the ' green ' then ' go ' END;
SELECT Case 9 If 1 then ' a ' is 2 Then ' B ' ELSE ' n/a ' END;
Select Case When (=4) $amp;<amp; $gt, 4 then= "" ' not= "" OK "=" end= "" as= "" status;
SELECT name,if ((IsActive = 1), ' activated ', ' inactive ') as RESULT from Userlogininfo;
SELECT Fname,lname, (math+sci+lit) as Total,
Case when (Math+sci+lit) < ' D '
When (Math+sci+lit) between and "C"
When (Math+sci+lit) between 151 and "B"
ELSE ' A ' END
As grade from Marks;
SELECT IF (ENCRYPT (' Sue ', ' ts ') =upass, ' Allow ', ' deny ') as Loginresult from users WHERE

uname = ' Sue '; #一个登陆验证

Vii. formatting functions

Date_format (DATE,FMT) formatting date dates values according to the string FMT
Format (x, y) formats x as a comma-separated sequence of numbers, and Y is the number of decimal digits of 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 the string FMT
The simplest of these is the format () function, which formats large numeric values into a readable sequence of comma intervals.
Example:
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 conversions, MySQL provides the cast () function, which converts a value to the specified data type. Type

Yes:

binary,char,date,time,datetime,signed,unsigned
Example:
SELECT CAST (now () as signed INTEGER), Curdate () +0;
SELECT ' F ' =binary ' f ', ' F ' =cast (' F ' as BINARY);

Nine, System Information function

Database () returns the current DB 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 () returns the versions of the MySQL server
Example:
SELECT DATABASE (), VERSION (), USER ();
SELECT BENCHMARK (9999999,log (rand () *pi ())), #该例中, MySQL computes the LOG (rand () *pi ()) expression

MySQL nine categories of commonly used functions