Mysql database function learning Notes

Mathematical functions

Because MySQL contains a series of arithmetic operations, the relational database management system supports many mathematical functions.

ABS (x); // returns the absolute value of x.
ACOS (x); // returns the arc cosine of x (radians).
ASIN (x); // returns the arc sine of x (radians).
ATAN (x); // returns the arc tangent of x (radians).
CEILING (x); // returns the smallest integer greater than x.
COS (x); // returns the cosine of x (radians).
COT (x); // returns the cotangent of x (radians).
DEGREES (x); // returns the result of converting Radian value x to an angle.
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, x3. ..); // returns the maximum value in the set.
LEAST (x1, x2, x3,); // 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)
POW (x, y) or POWER (x, y); // returns the y POWER of x.
RAND (); // returns a random number from 0 to 1.
RADIANS (x); // returns the result of converting angle x to RADIANS.
ROUND (x, y); // returns the rounded y decimal value of parameter x.
SIGN (x); // return the value of the symbol representing the number x.
SQRT (x); // returns the square of x
SIN (x); // returns the sine of x (radians).
TAN (x); returns the tangent of x (radians).
TRUNCATE (x, y); // returns the result of truncating x to y decimal places.

See the following examples.

Select ABS (-123); // Obtain the absolute value. The result is 123.
Select GREATEST (33,156, 88, 156); // obtain the maximum value of a group, and the result is.

Exercise

Calculate the absolute value of-34, calculate the rounding value of 3.45234, and retain 2 decimal places

Aggregate Functions

MySQL has a set of functions designed specifically to sum or summarize the data in tables in a centralized manner. These functions are often used in select queries that contain group by clauses. Of course, they can also be used for queries without group

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 a specified column
SUM (col); // returns the SUM of all values in the specified column.
STD (col) or STDDEV (col); // return the standard deviation of all values in the specified column
VARIANCE (col); // returns the standard VARIANCE of all values in the specified column
GROUP_CONCAT (col); // returns the result of a combination of column values.
The COUNT () function is the most commonly used function in this group of functions. The number of rows in the result set that contain at least one non-null value is calculated.

Select count (*) from members;

The MIN () and MAX () functions return the minimum or maximum values of the number set.

Select min (quantity) from inventory; // returns the minimum value.
Select max (return) from investments; returns the maximum value.

Exercise

Delete the data table and create a new data table. The field is score and two pieces of data are added, which are 29 and 34 respectively. The average and minimum values are calculated.

String functions

Because the MySQL database not only reports numeric data, but also contains strings, MySQL has a set of functions designed for string operations.

The length () function can be used to obtain the length of a string.

Select length ('asdfgh'); // The result is 7.

By using the trim () function, we can specify the format to be removed when the value is cut. It can also be decided to cut from the header, tail, and sides of the string.

Select trim ('red alter '); // remove spaces on both sides
Select trim (leading '! 'From '!!! Error !!! '); // Remove the header "!" Symbol

The concat () function concatenates the provided parameters into a string.

Select concat ('red', 'Hot '); // The result is redhot.

Here is a simple list of several commonly used string functions. For more functions, see the MySQL documentation.

Date and time functions

MySQL provides many date and time processing functions. Here we just choose some common descriptions.

Use the now () function to get the current date and time, the current date and time will be returned in YYYY-MM-DD HH: MM: SS format

Select now (); // returns the current time

Obtain the date and time separately. You can use the curdate () and curtime () functions.

Select curtime (); // Current Time, in the format of HH: MM: SS
Select curdate (); // The current date in the YYYY-MM-DD format

The week () function returns the week number of the year for the specified date, and the yearweek () function returns the week of the year for the specified date.

Select week ('2017-03-01 '); // The result is 9.
Select yearweek (20040301); // The result is 200409.

For more date and time functions, see the MySQL official documentation.

Encryption functions

MySQL specially designed some functions to encrypt data.

AES_ENCRYPT (str, key); // returns the result of using the key to encrypt the string str using the Advanced Encryption Standard algorithm.
AES_DECRYPT (str, key); // return the decryption result of the string str using the Advanced Encryption Standard algorithm using the key pair.
DECODE (str, key); // use the key as the key to decrypt the encrypted string str
ENCRYPT (str, salt); // use the UNIX crypt () function and use the keyword salt to ENCRYPT the String str
ENCODE (str, key); // use key as the key to encrypt the String str
MD5 (); // calculate the MD5 checksum of String str
PASSWORD (str); // return the encrypted version of the string str
SHA (); // calculate the SHA checksum of the str string
The password () function is used to create an encrypted password string, which is suitable for inserting it into the MySQL security system. This encryption process cannot be reversed. It uses different algorithms than UNIX encryption.

If you want to, you can use the ENCRYPT () function to ENCRYPT the string using the UNIX crypt () system. The ENCRYPT () function receives the string to be encrypted and (optional) the salt used for the encryption process (a string that uniquely identifies the password, just like a key ).

You can also use the ENCODE () and DECODE () functions to encrypt and decrypt strings. ENCODE () has two parameters: the encrypted string and the key used as the basis for encryption:

Insert into users values ('', 'John', ENCODE ('asdfasdf ', 'Secret _ key '));
Select * from users;
Select id, uname, DECODE (upass, 'Secret _ key') from users;

Control flow functions

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

Case where [test1] then [result1]... else [defalut] end; // If testN is true, resultN is returned; otherwise, default is returned.
Case [test] where [val1] then [result1]... else [default] end; // if test and valN are true, 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); // If arg1 = arg2, null is returned; otherwise, arg1 is returned.

The first of these functions is the ifnull () function, which has two parameters and judges the first parameter. If the second parameter is not null, the function returns the first parameter to the caller. If it is null, the second parameter is returned.

Select ifnull (), ifnull (null, 10), ifnull (4 * null, 'false'); // The result is, 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.

The if () function has three parameters. The first parameter is the expression to be judged. if the expression is true, the if () function returns the second parameter, which is false. The third parameter is returned. The if () function is applicable only when there are two results.

Formatting functions

MySQL also has some functions designed specifically for formatting data.

Date_format (date, fmt); // format the value of date based on the string fmt
Format (x, y); // format x as a string separated by commas (,). y indicates the number of decimal places in the result.
Inet_aton (ip); // return the number of ip addresses.
Inet_ntoa (num); // return 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. The first parameter of format () is the formatted data, and the second parameter is the number of decimal places in the result.

Select format (999999999868595049,2), format (-4512,6); // The result is 999,999,999, 82;-4.512.000000

For more information, see related documents.

Exercise

Format '200' in the format of 'yyyy-mm-DD'. The format is '% Y-% m-% d'

Data conversion functions

To convert data types, MySQL provides the cast () function, which converts a value to a specified data type.

Generally, when a numeric value is used, the string is automatically converted to 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] to return values as a number rather than a string, you only need to use these functions in the numeric environment or convert them into numbers.

Select cast (now () as signed integer), curdate () + 0;

System Information functions

MySQL also has some special functions used to obtain information about the system itself.

Database (); // returns the name of the current database.
Benchmark (count, expr); // repeat the expression expr for count times
Connection_id (); // return the connection id of the current customer
Found_rows (); // returns the number of records returned by the last select query (results are not restricted by limit).
Get_lock (str, dur); // get a lock named by the string str with a dur S latency
Is_free_lock (str); // check whether the lock named str is released.
Last_insert_id (); // return the value of the last auto increment id automatically generated by the system.
Master_pos_wait (log, pos, dur); // lock the master server's dur seconds until the pos is synchronized from the location specified by the log of the slave server and the master server's log
Release_lock (str); // release the lock named by the string str
User ()/system_user (); // return the current logon user name
Version (); // returns the MySQL server version.
The database (), user (), and version () functions can return the information of the currently selected database, current user, and MySQL version respectively:

Select database (), version (), user ();