MySql common functions _ MySQL

Original works of Lin Bingwen Evankaka. For more information, see http: blogcsdnnetevankaka Lin Bingwen Evankaka. Reprinted please indicate the source http://blog.csdn.net/evankaka

MySQL database provides a wide range of functions. MySQL functions include mathematical functions, string functions, date and time functions, conditional judgment functions, system information functions, encryption functions, and formatting functions. These functions simplify your operations. For example, the string concatenation function can easily connect multiple strings together. In this lecture, we will explain the following content:
Mathematical functions
String functions
Date and time functions
Conditional judgment function
System Information functions
Encryption functions
Formatting functions

MySQL functions are internal functions provided by the MySQL database. These internal functions help you process table data more conveniently. This section briefly introduces the types of functions contained in MySQL, as well as the usage scope and functions of these functions. MySQL functions include mathematical functions, string functions, date and time functions, conditional judgment functions, system information functions, and encryption functions. SELECT statements and their conditional expressions can all use these functions. The INSERT, UPDATE, and DELECT statements and their conditional expressions can also use these functions. For example, if a data in a table is negative, you need to display the data as a positive number. You can use the absolute value function. As you can see above, the MySQL function can process the data in the table to get the data you want. These functions can make MySQL database more powerful.

I. mathematical functions

Mathematical functions are commonly used in MySQL. It is mainly used to process numbers, including integer and floating point numbers. Mathematical functions include absolute value functions, sine functions, cosine functions, and functions for obtaining random numbers.

ABS (X): returns the absolute value of X.
Select ABS (-32 );

MOD (N, M) or %: returns the remainder of N divided by M.
Select MOD (15, 7 );
Select 15% 7;

FLOOR (X): returns the maximum integer not greater than X.
Select FLOOR (1.23 );
Select FLOOR (-1.23 );

CEILING (X): returns the smallest integer not less than X.
Select CEILING (1.23 );
Select CEILING (-1, 1.23 );

ROUND (X): returns an integer rounded down to X.
Select ROUND (1.58 );
Select ROUND (-1.58 );

II. string functions

ASCII (str): returns the ASCII code value of the leftmost character of the str string. If str is a null string, 0 is returned. If 'str' is NULL, return NULL.
Select ASCII ('2 ');
Select ASCII (2 );
Select ASCII ('dx ')

CONCAT (str1, str2,...): returns a string from the parameter link. If any parameter is NULL, return NULL. There can be more than two parameters. A numeric parameter is converted to an equivalent string.
Select CONCAT ('My, s', 'ql ');
Select CONCAT ('My, NULL, 'ql ');
Select CONCAT (14.3 );

LENGTH (str): returns the LENGTH of the str string.
Select LENGTH ('text ');

LOCATE (substr, str): returns the position where the substring substr appears first. if the substring is not in str, return 0.
Select LOCATE ('bar', 'foobarbar ');
Select LOCATE ('xbar', 'foobar ');

INSTR (str, substr): returns the first position of the substring substr in the str string.
Select INSTR ('foobar', 'bar ');
Select INSTR ('xbar', 'foobar ');

LEFT (str, len): returns the leftmost len character of the str string.
Select LEFT ('foobarbar', 5 );

RIGHT (str, len): returns the rightmost len character of the str string.
Select RIGHT ('foobarbar', 4 );

SUBSTRING (str, pos): returns a SUBSTRING from the start position of str.
Select SUBSTRING ('quadratically ', 5 );

TRIM (str): returns the str string. all prefixes or suffixes are deleted.
Select TRIM ('bar ');

LTRIM (str): returns the str string that deletes the leading space character.
Select LTRIM ('barbar ');

RTRIM (str): return the str string that deletes the trailing space characters.
Select RTRIM ('barbar ');

REPLACE (str, from_str, to_str): returns the str string, which is replaced by the to_str string.

Select REPLACE ('www .mysql.com ', 'W', 'WW ');

REPEAT (str, count): returns a string consisting of repeated countTimes of str. If count <= 0, an empty string is returned. If 'str' or 'count' is NULL, return NULL.
Select REPEAT ('mysql', 3 );

REVERSE (str): returns the string str in the reversed character order.
Select REVERSE ('ABC ');

INSERT (str, pos, len, newstr): returns the str string, which is the substring starting from the position pos and is replaced by newstr.
Select INSERT ('whatareyou', 5, 3, 'Is ');

III. date and time functions

DAYOFWEEK (date): returns the index of the week of the date (1 = Sunday, 2 = Monday ,... 7 = Saturday ).
Select DAYOFWEEK ('2017-02-03 ');
Select DAYOFWEEK (now ());

WEEKDAY (date): returns the week index of date (0 = Monday, 1 = Tuesday ,...... 6 = Sunday ).
Select WEEKDAY ('2017-11-05 ');

DAYOFMONTH (date): returns the date in the month of date, within the range of 1 to 31.
Select DAYOFMONTH ('2017-02-03 ');

DAYOFYEAR (date): returns the number of days in a year from 1 to 366.
Select DAYOFYEAR ('2014-02-03 ');

MONTH (date): returns the MONTH of date, ranging from 1 to 12.
Select MONTH ('2014-02-03 ');

DAYNAME (date): returns the name of the week of date.
Select DAYNAME ("1998-02-05 ");

MONTHNAME (date): returns the name of the month of the date.
Select MONTHNAME ("1998-02-05 ");

QUARTER (date): returns the QUARTER of a year from date, ranging from 1 to 4.
Select QUARTER ('98-04-01 ');

WEEK (date, first): for the place where Sunday is the first day of a WEEK, there is a single parameter that returns the number of weeks of date, ranging from 0 to 52. Two parameter formats: WEEK () allows you to specify whether the WEEK starts on Sunday or Monday. If the second parameter is 0, the week starts from Sunday, and if the second parameter is 1, it starts from Monday.
Select WEEK ('2017-02-20 ');
Select WEEK ('2014-02-20 ', 0 );
Select WEEK ('2014-02-20 ', 1 );

YEAR (date): returns the YEAR of date, ranging from 1000 to 9999.
Select YEAR ('98-02-03 ');

HOUR (time): returns the HOUR of time, ranging from 0 to 23.
Select HOUR ('10: 05: 03 ');

MINUTE (time): returns the MINUTE of time, ranging from 0 to 59.
Select MINUTE ('98-02-03 10:05:03 ');

SECOND (time): The number of seconds for returning time, ranging from 0 to 59.
Select SECOND ('10: 05: 03 ');

DATE_ADD (date, INTERVAL expr type), to increase the date, accurate to seconds
DATE_SUB (date, INTERVAL expr type) for date reduction, accurate to seconds

SELECT "1997-12-31 23:59:59" + INTERVAL 1 SECOND;

Select interval 1 DAY + "1997-12-31 ";
SELECT "1998-01-01"-INTERVAL 1 SECOND;
SELECT DATE_ADD ("23:59:59", INTERVAL 1 SECOND );
SELECT DATE_ADD ("23:59:59", INTERVAL "" MINUTE_SECOND );

SELECT DATE_SUB ("00:00:00", INTERVAL "1" DAY_SECOND );
SELECT DATE_SUB ("1998-01-02", INTERVAL 31 DAY );

CURRENT_DATE: returns today's date value in 'yyyy-MM-DD 'or YYYYMMDD format, depending on whether the function is used in a string or numeric context.
Select CURDATE ();

CURRENT_TIME: returns the current time value in 'hh: MM: SS' or HHMMSS format.
Select CURTIME ();

NOW (): returns the current date and time in 'yyyy-MM-DD HH: MM: SS' or YYYYMMDDHHMMSS format
Select NOW ();

IV. control flow function CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result...] [ELSE result] end case when [condition] THEN result [WHEN [condition] THEN result...] [ELSE result] END
In the returned results of the first scheme, value = compare-value. The returned results of the second solution are the real results of the first case. If no matching result value exists, the result after ELSE is returned. If no ELSE part exists, the return value is NULL.
Select case 11 WHEN 1 THEN 'one'
WHEN 2 THEN 'two' ELSE 'more' END;
Select case when 1> 0 THEN 'true' ELSE 'false' END;

Select case binary 'B'
WHEN 'a 'Then 1 WHEN 'B' THEN 2 END;

IF (expr1, expr2, expr3)
IF expr1 is TRUE (expr1 <> 0 and expr1 <> NULL), the return value of IF () is expr2; otherwise, the return value is expr3. The return value of IF () is a numeric or string value, depending on the context.
Select if (1> 2, 2, 3 );
Select if (1 <2, 'yes', 'no ');
Select if (STRCMP ('test', 'test1'), 'no', 'yes ');

Strcmp (str1, str2): If str1> str2 returns 1, str1 = str2 returns 0, str1

STRCMP (expr1, expr2)

If the strings are the same, STRCMP () returns 0. if the first parameter is smaller than the second in the current sorting order,-1 is returned. otherwise, 1 is returned.
Select STRCMP ('text', 'text2 ');
Select STRCMP ('text2', 'text ');
Select STRCMP ('text', 'text ');

V. system information functions are used to query the system information of the MySQL database. For example, query the database version and the current user of the database. This section describes in detail the functions and usage of system information functions.

Obtain the MySQL version number, number of connections, and database name.

The VERSION () function returns the database VERSION number;

The CONNECTION_ID () function returns the number of connections to the server, that is, the number of connections to the MySQL service until now;

DATABASE () and SCHEMA () return the current DATABASE name.

Function used to obtain the user name

USER (), SYSTEM_USER (), SESSION_USER (), CURRENT_USER (), and CURRENT_USER functions can return the name of the current USER.

Function for retrieving character sets and sorting methods of strings

The CHARSET (str) function returns the character set of the str string. Generally, this character set is the default character set of the system. The COLLATION (str) function returns the character arrangement of the str string.

Gets the Last automatically generated ID value.
The LAST_INSERT_ID () function returns the final AUTO_INCREMENT value.

VI. encryption functions

The encryption function is used in MySQL to encrypt data. Because some sensitive information in the database is not expected to be viewed by others, it should be encrypted to make the data look garbled. For example, the user's password should be encrypted. This section describes in detail the functions and usage of encryption functions.
The following describes the names, functions, and usage of various encryption functions.

Encryption function PASSWORD (str)
The PASSWORD (str) function can encrypt the string 'str. Generally, the PASSWORD (str) function is used to encrypt the user's PASSWORD. The following uses the PASSWORD (str) function to encrypt the string "abcd.

Encryption function MD5 (str)

The MD5 (str) function can encrypt the string 'str. The MD5 (str) function encrypts common data. The following uses the MD5 (str) function to encrypt the string "abcd.

Encryption function ENCODE (str, pswd_str)
The ENCODE (str, pswd_str) function can use the string pswd_str to encrypt the String str. The encrypted result is a binary number, which must be saved using BLOB fields.
Decryption function
The DECODE (crypt_str, pswd_str) function can use the string pswd_str to decrypt crypt_str. Crypt_str is the binary data encrypted by ENCODE (str, pswd_str. The string pswd_str should be the same as the encrypted string pswd_str. The following uses DECODE (crypt_str, pswd_str) to decrypt data encrypted with ENCODE (str, pswd_str.

VII. Other functions

FORMAT (x, n)

The FORMAT (x, n) function can FORMAT the number x and retain x to n digits after the decimal point. This process requires rounding. For example, FORMAT (2.356, 2) returns 2.36; FORMAT (2.353, 2) returns 2.35. The following uses the FORMAT (x, n) function to FORMAT 235.3456 and 235.3454, which are retained to the third digit after the decimal point.

Conversion functions for numbers in different Hexadecimal formats

ASCII (s) returns the ASCII code of the first character of string s; BIN (x) returns the binary code of x; HEX (x) returns the hexadecimal code of x; OCT (x) returns the octal code of x. CONV (x, f1, f2) converts x from the f1 hexadecimal number to the f2 hexadecimal number.

And the following
Functions for converting IP addresses and numbers

The INET_ATON (IP) function can convert an IP address to a digital representation. the INET_NTOA (n) function can convert the number n to an IP address. The IP value in the INET_ATON (IP) function must be enclosed by quotation marks. These two functions are inverse functions.

Lock function and unlock function

The GET_LOCT (name, time) function defines a lock named nam with a duration of time seconds. If the lock succeeds, 1 is returned. if the attempt times out, 0 is returned. If an error occurs, NULL is returned. The RELEASE_LOCK (name) function removes the lock named name. If the unlock succeeds, 1 is returned. if the attempt times out, 0 is returned. if the unlock fails, NULL is returned. the IS_FREE_LOCK (name) function determines whether to use the lock named name. If yes, 0 is returned. otherwise, 1 is returned.
The function that repeats the specified operation.

The BENCHMARK (count, expr) function repeats the expression expr for count times and returns the execution time. This function can be used to determine the speed at which MySQL processes expressions.

Function for changing character sets

The CONVERT (s USING cs) function converts the character set of string s to cs.

CAST (x AS type) and CONVERT (x, type) CONVERT x to type. These two functions only apply to BINARY, CHAR, DATE, DATETIME, TIME, signed integer, and unsigned integer types. However, the two methods only change the data type of the output value and do not change the field type in the table.