In-depth understanding of MySQL advanced drifting (3)

Function

Mathematical function

Requirements:
1)- The absolute value of 123;
2) Get the maximum value of 100,88,33,156;

Aggregation function

MySQL has a set of functions specifically for summation or pairing Designed for centralized summary of the data in the table, these functions are often used in select queries containing group by clauses. Of course, they can also be used for queries without group

1) This group Among the functions, the most commonly used one is the COUNT() function, which calculates the number of rows in the result set that contain at least one non-null value
select count(*) from students;
2)MIN() and MAX() The function returns the minimum or maximum value of the number set
select min(score) from data; //Return the minimum value
select max(age) from data;Return the maximum value
Requirements:
New data table , the field is score, add two pieces of data, 29 and 34 respectively, to calculate the average and minimum value

String function

MySQL database not only contains numerical data, It also contains strings, some commonly used ones are listed below:
The length of a string can be obtained through the length() function
select length('abcdefg');//The result is 7
Through the trim() function It allows us to specify the removal format when cutting values, and we can also decide to cut from the beginning, end, and both sides of the string.
select trim(' red hair');//Remove the spaces on both sides
select trim(leading '!' from '!!!heihei!!!');//Remove the first "!" symbol
The concat() function concatenates the provided parameters into a string
select concat('woyao','yaosini');//The result is woyaoyaosini

Date time function

1) Use the now() function to get the current date and time, which will be returned in the format of YYYY-MM-DD HH:MM:SS
select now();//Return the current time
2) To obtain the date and time separately, you can use the curdate() and curtime() functions
select curtime();//The current time, the format is HH:MM:SS
select curdate();//The current date, the format For YYYY-MM-DD
3) The week() function returns the week of the year for the specified date, and the yearweek() function returns the week of the year for the specified date
select week ('2017-02-24');//The result is 8
select yearweek(20170224);//The result is 200408

Encryption function (learn about it)

The password() function is used to create an encrypted password string, which is suitable for insertion into the MySQL security system. This encryption process is irreversible and uses a different algorithm than UNIX password encryption.
You can also use the UNIX crypt() system to encrypt strings through the ENCRYPT() function. The ENCRYPT() function receives the string to be encrypted and (optional) the salt (a character that can uniquely determine the password) used in the encryption process. string, like a key).
You can also use the ENCODE() function and DECODE() function to encrypt and decrypt strings. ENCODE() has two parameters: the encrypted string and the key as the basis for encryption;

Control Stream function

MySQL provides 4 functions for conditional operations. These functions implement the conditional logic of SQL, allowing developers to convert some application business logic to the database backend.

The first of these functions is the ifnull() function, 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 it is null, the second parameter is returned.

The nullif() function will check whether the two provided parameters are equal. If they are equal, null will be returned. If they are not equal, the first parameter will be returned.
The if() function has three parameters. The first one is the expression to be judged. If the expression is true, the if() function will return the second parameter. If it is false, it will return the third parameter. The if() function is suitable to be used when there are only two results;

Format function

MySQL also has some functions specially designed for formatting data

The more commonly used function is the format() function, which can format large values ​​into an easy-to-read sequence separated by commas. The first parameter of format() is the formatted data, and the second parameter is the number of decimal places in the result

Data conversion function

In order to perform data type conversion, MySQL provides the cast() function, which can convert a value into a specified data type
Normally, when using numerical operations, The string will be automatically converted into a number;
select 1+'99';//The result is 100
select 1+cast('99' as signed);//The result is 100
We can force Many date and time functions [including now(), curtime(), and curdate() functions] output the value they return as a number rather than a string. Simply use these functions in a numeric environment or convert them to Number

System information function

database(), user() and version() functions can respectively return the currently selected database and the current user And MySQL version information:

The above is the detailed content of In-depth understanding of MySQL advanced drifting (3). For more information, please follow other related articles on the PHP Chinese website!