Mysql common functions, difficulties, notes, and difficulties of mysql Functions

Mysql common functions, difficulties, notes, and difficulties of mysql Functions

I. mathematical functions
ABS (x) returns the absolute value of x.

BIN (x) returns the binary value of x (OCT returns octal, HEX returns hexadecimal)
CEILING (x) returns the smallest integer greater than x.
EXP (x) returns the x power of e (the base of the natural logarithm ).
FLOOR (x) returns the largest integer less than x.
GREATEST (x1, x2,..., xn) returns the maximum value in the set.
LEAST (x1, x2,..., xn) returns the smallest value in the set.
LN (x) returns the natural logarithm of x.
LOG (x, y) returns the base y logarithm of x.
MOD (x, y) returns the modulus (remainder) of x/y)
PI () returns the pi value (circumference rate)
RAND () returns a random value between 0 and 1. You can provide a parameter (SEED) to generate a specified value for RAND () random number generator.
ROUND (x, y) returns the rounded y decimal value of parameter x.
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 truncating x to y decimal places.

Ii. Aggregate functions (usually used in SELECT queries of 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 in the specified column.
GROUP_CONCAT (col) returns the result of a combination of column values.

Iii. String Functions
ASCII (char) returns the ASCII value of a character
BIT_LENGTH (str) returns the bit length of the string.
CONCAT (s1, s2. .., sn) concatenates s1, s2. .., and sn into a string.
CONCAT_WS (sep, s1, s2. .., sn) concatenates s1, s2. .., sn into a string, and uses the sep character interval.
INSERT (str, x, y, instr): Start from position x of string str. Replace substring with string instr with length y. The result is returned.
FIND_IN_SET (str, list) analyzes the list separated by commas (,). If str is found, the position of str in the list is returned.
LCASE (str) or LOWER (str) returns the result of changing all characters in the str string to lowercase.
LEFT (str, x) returns the leftmost x characters in the str string.
LENGTH (s) returns the number of characters in the str string.
LTRIM (str) removes the leading space from the str string.
POSITION (substr, str) returns the POSITION of the substring substr that appears for the first time in the str string.
QUOTE (str) escape single quotes in str with a backslash
REPEAT (str, srchstr, rplcstr) returns the string 'str' repeated x times.
REVERSE (str) returns the result of REVERSE string str
RIGHT (str, x) returns the rightmost x characters in the str string.
RTRIM (str) returns the space at the end of the str string.
STRCMP (s1, s2) compares strings s1 and s2
TRIM (str) removes all spaces at the beginning and end of the string.
UCASE (str) or UPPER (str) returns the result of converting all characters in the str string to uppercase.

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 result of date plus the INTERVAL int (int must be formatted according to the keyword), such as: SELECTDATE_ADD (CURRENT_DATE, INTERVAL 6 MONTH );
DATE_FORMAT (date, fmt) format the date value according to the specified fmt Format
DATE_SUB (date, INTERVAL int keyword) returns the result of date plus the INTERVAL int (int must be formatted according to the keyword), such as: SELECTDATE_SUB (CURRENT_DATE, INTERVAL 6 MONTH );
DAYOFWEEK (date) returns the Day (1 ~ 7)
DAYOFMONTH (date) returns the Day (1 ~ 31)
DAYOFYEAR (date) returns the Day (1 ~ 366)
DAYNAME (date) returns the week name of date, for example, select dayname (CURRENT_DATE );
FROM_UNIXTIME (ts, fmt) format the UNIX timestamp ts Based on the specified fmt Format
HOUR (time) returns the HOUR value of time (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, for example, select monthname (CURRENT_DATE );
NOW () returns the current date and time
QUARTER (date) returns the QUARTER (1 ~ 4), such as select quarter (CURRENT_DATE );
WEEK (date) returns the WEEK (0 ~ 53)
YEAR (date) returns the YEAR (1000 ~ 9999)
Some examples:
Obtain 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 between two date values (number of months): SELECT PERIOD_DIFF (200302,199802 );
Age calculation in Mysql:
SELECT DATE_FORMAT (FROM_DAYS (TO_DAYS (NOW ()-TO_DAYS (birthday), '% y') + 0 AS age FROM employee;
In this way, if Brithday is the year, month, and day of the future, the calculation 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

V. encryption functions
AES_ENCRYPT (str, key) returns the encrypted result of using the key pair string str with the Advanced Encryption Standard algorithm. The result of calling AES_ENCRYPT is a binary string stored as BLOB.
AES_DECRYPT (str, key) returns the decryption result of the string str encrypted with the key using the Advanced Encryption Standard algorithm.
DECODE (str, key) uses the key as the key to decrypt the encrypted string str
ENCRYPT (str, salt) uses the UNIXcrypt () function and uses the keyword salt (a string that uniquely identifies a password, just like a key) to ENCRYPT the string str
ENCODE (str, key) uses key as the key to encrypt the string str. The result of calling ENCODE () is a binary string, which is stored as BLOB.
Calculate the MD5 checksum of the str string using MD5 ().
PASSWORD (str) returns the encrypted version of the string str, which is irreversible and uses different algorithms than the encryption process of UNIX passwords.
SHA () calculates the SHA checksum of the str string
Example:
Select encrypt ('root', 'salt ');
Select encode ('xufeng', 'key ');
Select decode (ENCODE ('xufeng', 'key'), 'key'); # Put encryption and decryption together
SELECT AES_ENCRYPT ('root', 'key ');
SELECT AES_DECRYPT (AES_ENCRYPT ('root', 'key'), 'key ');
SELECT MD5 ('200 ');
Select sha ('123 ');

Vi. Control Flow Functions
MySQL has four functions used for conditional operations. These functions can implement the SQL conditional logic and allow developers to convert some application business logic to the database background.
　　 MySQL control flow Functions:
Case when [test1] THEN [result1]... ELSE [default] END if testN is true, resultN is returned; otherwise, default is returned.
CASE [test] WHEN [val1] THEN [result]... ELSE [default] END if test and valN are equal, resultN is returned; otherwise, default is returned.
IF (test, t, f) IF test is true, t is returned; otherwise, f is returned.
IFNULL (arg1, arg2) If arg1 is not empty, arg1 is returned; otherwise, arg2 is returned.
NULLIF (arg1, arg2) returns NULL if arg1 = arg2; otherwise, arg1 is returned.
The first of these functions is IFNULL (), which has two parameters and judges the first parameter. If the first parameter is not NULL, the function returns the first parameter to the caller. If the parameter is NULL, the second parameter is returned.
　　 For example: Select ifnull (1, 2), IFNULL (NULL, 10), IFNULL (4 * NULL, 'false ');
The NULLIF () function checks whether the two provided parameters are equal. If they are equal, NULL is returned. If they are not equal, the first parameter is returned.
For example: Select nullif (1, 1), NULLIF ('A', 'B'), NULLIF (2 + 3, 4 + 1 );
Like the IF () function provided by many scripting languages, MySQL's IF () function can also establish a simple conditional test, which has three parameters, the first is the expression to be judged. IF the expression is true, IF () returns the second parameter. IF the expression is false, IF () returns the third parameter.
For example: SELECTIF (1 <100, 3), IF (56>, 'true', 'false ');
The IF () function is applicable only when there are two possible results. However, in the real world, we may find that multiple branches are required in conditional testing. In this CASE, MySQL provides the case function, which is the same as the switch-CASE condition routine in PHP and Perl.
The format of the CASE function is a bit complex, usually 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 be compared. If it is true, returns the result. All WHEN-THEN blocks END with the ELSE block. WHEN the END ends with all external CASE blocks, if each of the preceding blocks does not match, the default result specified by the ELSE block is returned. If no ELSE block is specified and all WHEN-THEN comparisons are not true, MySQL returns NULL.
There is another syntax for the CASE function, 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 returned result depends on whether the test is true.
Example:
Mysql> select case 'green'
WHEN 'red' THEN 'stop'
WHEN 'green' THEN 'Go' END;
Select case 9 WHEN 1 THEN 'a 'when 2 THEN 'B' else' N/A' END;
Select case when (2 + 2) = 4 THEN 'OK' WHEN (2 + 2) <> 4 THEN 'not OK 'END ASSTATUS;
SELECT Name, IF (IsActive = 1), 'activated', 'inactivated') as result FROMUserLoginInfo;
SELECT fname, lname, (math + sci + benchmark) AS total,
Case when (math + sci + timeout) <50 THEN 'D'
WHEN (math + sci + benchmark) BETWEEN 50 AND 150 THEN 'C'
WHEN (math + sci + benchmark) BETWEEN 151 AND 250 THEN 'B'
ELSE 'A' END
AS grade FROM marks;
Select if (ENCRYPT ('sue ', 'ts') = upass, 'allow', 'deny') AS LoginResultFROM users WHERE uname = 'sue'; # One login verification

VII. Formatting Functions
DATE_FORMAT (date, fmt) format the date value according to the string fmt
FORMAT (x, y) FORMAT x as a sequence of numbers separated by commas, and y is the number of decimal places in the result (rounded to reserved)
INET_ATON (ip) returns the number of ip addresses
INET_NTOA (num) returns the IP address represented by the number
TIME_FORMAT (time, fmt) format the time value according to the string fmt
The simplest is the FORMAT () function, which can FORMAT a large value into a comma-separated readable sequence.
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 functions
To convert data types, MySQL provides the CAST () function, which can convert a value to a specified data type. Types: 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 );

9. system information functions
DATABASE () returns the current DATABASE name
BENCHMARK (count, expr) repeatedly runs count times for the expression expr
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 USER Name
VERSION () returns the MySQL server VERSION.
Example:
Select database (), VERSION (), USER ();
SELECTBENCHMARK (9999999, LOG (RAND () * PI (); # in this example, MySQL calculates the LOG (RAND () * PI () expression 9999999 times. 10. Where condition 　　The following table lists common comparison operators:

Operator	Name	Example
=	Equal	Id = 10
>	Greater	Id> 10
<	Less	Id <10
> =	Greater than or equal	Id> = 10
<=	Less than or equal	Id <= 10
! = Or <>	Not equal	Id! = 10
Is null	N/	Id is null
Is not null	N/	Id is not null
Between	N/	Id between 1 and 15
In	N/	Id in (3, 4, 5)
Not in	N/	Xuesheng not in (shi, li)
Like	Pattern Matching	Xuesheng like ('shi % ')
Not like	Pattern Matching	Xuesheng not like ('shi % ')
Regexp	Regular Expression	Xuesheng Regular Expression

Tip: The where clause is used to remove rows that do not meet the where condition before grouping query results. Therefore, when a where clause is used, the condition cannot contain aggregate functions to display specific rows. 11. group by grouping results

When used together with Aggregate functions such as AVG () or SUM (), the group by clause can play the largest role. The group by clause can GROUP query results and return the summary of rows. In a query statement with a group by clause, the columns specified in the SELECT list are either specified in the group by clause or included in the aggregate function. The SELECT, group by, and HAVING clauses of query statements are the only places where aggregate functions appear. Therefore, Aggregate functions cannot be used in where clauses. You can use the group by clause to group by xuesheng and obtain the average score of each student. The Code is as follows:

Select xuesheng, avg (fenshu) from tb_chengji02 group by xuesheng;

Group by xuesheng is grouped by students. In the xuesheng column, students with the same name are divided into groups and their average scores are calculated.

 

12, HAVING Clause

The HAVING clause is used to filter groups that meet the conditions, that is, to filter data after the group. HAVING clauses often contain aggregate functions. You can use the HAVING condition to display a specific group, or use multiple grouping standards to group A group. When the HAVING clause is used in the group by clause, only groups meeting the having condition are returned in the query results.

For example, you can query records whose total score is greater than or equal to 280 in the tb_chengji02 table. The Code is as follows:

Select xuesheng, sum (fenshu) from tb_chengji02 group by xuesheng having sum (fenshu)> = 280;

You can also query the existence of records with the total score of Zhang dongxue or Li greater than or equal to 270 in the tb_chengji02 table. If yes, it is displayed. The Code is as follows:

Select xuesheng, sum (fenshu) from tb_chengji02 where xuesheng = 'zhang dongxue 'or xuesheng = 'lily' group by xuesheng having sum (fenshu)> = 270;

 

XIII, DISTINCT Remove duplicate rows in results

Use the DISTINCT keyword to remove duplicate rows in the result.

In the DISTINCT statement, the SELECT field can only be a field specified by DISTINCT, and other fields cannot appear.

For example, query the tb_chengji02 table and remove duplicate data from the xuesheng field in the result. The Code is as follows:

Select distinct xuesheng from tb_chengji02;

14th, ORDER Sort the results

You can use order by to sort query results in ascending or descending ORDER (DESC). BY default, order by will output results in ascending ORDER. DESC can be used to sort data in descending order.

When sorting columns with null values, if they are sorted in ascending order, the null value will appear at the beginning; if it is sorted in descending order, the null value will appear at the end.

For example, to query all the information in the tb_chengji table, sort the information in descending order by "id. The Code is as follows:

Select * from tb_chengji order by id desc;

15th, LIKE Fuzzy search

LIKE is a commonly used comparison operator that can be used to perform fuzzy queries. It has two wildcard characters: "%" and underscore "_". '%' Can match one or more characters, while '_' can only match one character.

For example, in the tb_chengji table, find all students whose second character is "Winter. The Code is as follows:

Select * from tb_chengji where xuesheng like ('_ % ');

16th, CONCAT Join multiple columns

The CONCAT function can combine multiple fields to form a string.

For example, combine the yuwen, shuxue, and yingyu fields in the tb_chengji table to form a new string. The Code is as follows:

Select id, xuesheng, concat (yuwen, ":", shuxue, ":", yingyu) as fenshu from tb_chengji;

17, LIMIT Limit the number of result lines

The LIMIT clause can LIMIT the number of records in the query result to control the number of rows It outputs.

For example, to query the tb_chengji table, sort the IDs in descending order and display three records. The Code is as follows:

Select * from tb_chengji order by id desc limit 3;

You can also use LIMIT to obtain values from the intermediate part of the query result. First, define two parameters. Parameter 1 is the number of the first record to be read (in the query result, the record number of the first result is 0 rather than 1 ), parameter 2 indicates the number of records to be queried.

Select * from tb_chengji order by id desc limit 1, 2;

18, Use functions and expressions

In MySQL, expressions can also be used to calculate the values of each column as output results. Expressions can also contain some functions.

For example, calculate the total score of Middle School Students in the tb_chengji table. The Code is as follows:

Select id, xuesheng, yuwen, shuxue, yingyu, sum (yuwen + shuxue + yingyu) as zongfen from tb_chengji group by id;

Or:

Select *, sum (yuwen + shuxue + yingyu) as zongfen from tb_chengji group by id;

Calculate the three-way average score of Middle School Students in the tb_chengji table. The Code is as follows:

Select *, format (avg (yuwen + shuxue + yingyu)/3), 1) as pingjun feng from tb_chengji group by id;

When you operate a MySQL database, you sometimes need to collect statistics on records in the database, such as average value, minimum value, or maximum value. In this case, you can use the statistical function in MySQL.

Common statistical functions are shown in the following table:

Name	Description
Avg (field name)	Obtains the average value of a specified column.
Count (field name)	If a field is specified, the non-empty records in the field are counted. If DISTINCT is added, records with different values are recorded. The same value is regarded as a record. If count (*) is used, the number of all records containing null values is counted.
Min (field name)	Obtains the minimum value of a specified field.
Max (field name)	Obtains the maximum value of a specified field.
Std (field name)	Specifies the standard deviation value of the field.
Stdtev (field name)	Same as std.
Sum (field name)	Specifies the sum of all records of a field.