Shulou(Shulou.com)06/01 Report--

In this issue, Xiaobian will bring you an introduction to the functions commonly used in MySQL. The article is rich in content and analyzed and described from a professional perspective. After reading this article, I hope you can gain something.

MySQL functions are internal functions provided by MySQL databases. These built-in functions help users to manipulate data in tables more easily. In this section, we will briefly introduce which types of functions are included in MySQL, and the scope and role of these types of functions. MySQL functions include mathematical functions, string functions, date and time functions, conditional judgment functions, system information functions, encryption functions, etc. Both SELECT statements and their conditional expressions can use these functions. Insert, UPDATE, DELECT statements and their conditional expressions can also use these functions. For example, if one of the data in a table is negative, you need to display that data as positive. This allows you to use absolute value functions. As you can see from the above, MySQL functions can process the data in the table accordingly in order to get the data that the user wants. These functions make MySQL databases more powerful.

MySQL database provides a wealth of functions. MySQL functions include mathematical functions, string functions, date and time functions, conditional judgment functions, system information functions, encryption functions, formatting functions, etc. These functions simplify user operations. For example, string concatenation functions are convenient for concatenating multiple strings together.

Common MySQL functions are:

Date and Time Functions

（1）CURDATE()、CURRENT_DATE()

Returns the current date in the format "Y-MM-DD" or "YMMDD", depending on whether the function is used in string or numeric context

（2）CURRENT_TIMESTAMP()、LOCALTIME()、NOW()、SYSDATE()

These four functions work identically and return the current date and time value in the format "YYYY_MM-DD HH:MM:SS" or "YMMDDHHMMSS," depending on whether the function is used in string or numeric context

（3）UNIX_TIMESTAMP()、UNIX_TIMESTAMP(date)

The former returns the number of seconds from 1970-01-01 00:00 GMT to the present, while the latter returns the number of seconds from 1970-01-01 00:00 GMT to the specified time

（4）FROM_UNIXTIME(date)

UNIX_TIMESTAMP and UNIX_TIMESTAMP are inverse functions, converting UNIX timestamps to time in plain format.

UTC_DATE() and UTC_TIME()

The former returns the current UTC (Universal Standard Time) date value in the format "Y-MM-DD" or "YMMDD," and the latter returns the current UTC time value in the format "Y-MM-DD" or "YMMDD." Which one is used depends on whether the function is used in a string or numeric context

（6）MONTH(date)、MONTHNAME(date)

The former returns the month of the specified date, and the latter returns the name of the month of the specified date

（7）DAYNAME(d)、DAYOFWEEK(d)、WEEKDAY(d)

DAYNAME(d) returns the English name of the workday corresponding to d, such as Sunday, Monday, etc.;DAYOFWEEK(d) returns the index of the corresponding week, 1 represents Sunday, 2 represents Monday;WEEKDAY(d) represents the index of the workday corresponding to d, 0 represents Monday, 1 represents Tuesday

（8）WEEK(d)、WEEKOFYEAD(d)

The former calculates the day d as the week of the year, while the latter calculates the week of the year in which a day lies

（9）DAYOFYEAR(d)、DAYOFMONTH(d)

The former returns d as the day of the year, while the latter returns d as the day of the month.

（10）YEAR(date)、QUARTER(date)、MINUTE(time)、SECOND(time)

YEAR(date) returns the year corresponding to the specified date, ranging from 1970 to 2069;QUARTER(date) returns the quarter of the year corresponding to date, ranging from 1 to 4;MINUTE(time) returns the number of minutes corresponding to time, ranging from 0 to 59;SECOND(time) returns the second value of the specified time

（11）EXTRACE(type FROM date)

Extract a part from the date, type can be YEAR, YEAR_MONTH, DAY_HOUR, DAY_MICROSECOND, DAY_MINUTE, DAY_SECOND

（12）TIME_TO_SEC(time)

Returns the time parameter converted to seconds with the conversion formula "3600* hours + 60* minutes + seconds"

（13）SEC_TO_TIME()

and TIME_TO_SEC(time) are inverse functions of each other, converting seconds to time format

（14）DATE_ADD(date,INTERVAL expr type)、ADD_DATE(date,INTERVAL expr type)

Returns the time after adding expr type to the start time. For example, DATE_ADD ('2010 -12-31 23:59: 59', INTERVAL 1 SECOND) represents adding 1 second to the first time.

（15）DATE_SUB(date,INTERVAL expr type)、SUBDATE(date,INTERVAL expr type)

Returns the time after expr type is subtracted from the start time

（16）ADDTIME(date,expr)、SUBTIME(date,expr)

The former performs the time addition operation of date, while the latter performs the time subtraction operation of date

II. Mathematical functions

（1）ABS(x)

Returns the absolute value of x

（2）PI()

Return pi, default 6 decimal places

（3）SQRT(x)

Returns the square root of nonnegative x

（4）MOD(x,y)

Returns the remainder of x divided by y

（5）CEIL(x)、CEILING(x)

Returns the smallest integer not less than x

（6）FLOOR(x)

Returns the largest integer not greater than x

（7）ROUND(x)、ROUND(x,y)

The former returns the nearest integer to x, i.e. rounding x; the latter returns the nearest number to x, its value is retained to y places after the decimal point, if y is negative, it will be retained to x to y places to the left of the decimal point

（8）SIGN(x)

Returns the sign of parameter x,-1 for negative, 0 for 0, 1 for positive

POW(x,y) and POWER(x,y)

Returns the value of x raised to the power y

（10）EXP(x)

Returns the value of e raised to the power of x

（11）LOG(x)

Returns the natural logarithm of x, the logarithm of x relative to base e

（12）LOG10(x)

Returns the radix 10 logarithm of x

（13）RADIANS(x)

Returns the value of x converted from angles to radians

（14）DEGREES(x)

Returns the radian to angle value of x

（15）SIN(x)、ASIN(x)

The former returns the sine of x, where x is a given radian value; the latter returns the arcsine of x, where x is sine

（16）COS(x)、ACOS(x)

The former returns the cosine of x, where x is a given radian value; the latter returns the inverse cosine of x, where x is the cosine

（17）TAN(x)、ATAN(x)

The former returns the tangent of x, where x is a given radian value; the latter returns the arctangent of x, where x is tangent

（18）COT(x)

Returns the cotangent of a given radian value x

III. String functions

（1）CHAR_LENGTH(str)

Count the number of characters in a string

（2）CONCAT(s1,s2，...)

Returns the string generated by the connection parameter, one or more contents to be spliced, if any one is NULL, the return value is NULL.

（3）CONCAT_WS(x,s1,s2,...)

Returns a string that is a concatenation of multiple strings, with an x between each string

（4）INSERT(s1,x,len,s2)

Returns string s1, whose substring starts at position x and is replaced by string s2 by len characters

(5) LOWER(str) and LCASE(str), UPPER(str) and UCASE(str)

The first two convert all letters in str to lowercase, the last two convert all letters in string to uppercase

（6）LEFT(s,n)、RIGHT(s,n)

The former returns n characters of string s from the far left, and the latter returns n characters of string s from the far right

（7）LPAD(s1,len,s2)、RPAD(s1,len,s2)

The former returns s1 with the string s2 padded to len character length on the left and shortened to len character if s1 is longer than len; the former returns s1 with the string s2 padded to len character length on the right and shortened to len character if s1 is longer than len

（8）LTRIM(s)、RTRIM(s)

The former returns the string s with all spaces removed from the left; the latter returns the string s with all spaces removed from the right

（9）TRIM(s)

Return string s string with spaces removed

（10）TRIM(s1 FROM s)

Delete all substrings s1 at both ends of string s, delete spaces by default if s1 is not specified

（11）REPEAT(s,n)

Returns a string consisting of repeated string s, the number of string s equals n

（12）SPACE(n)

Returns a string of n spaces

（13）REPLACE(s,s1,s2)

Returns a string, replacing all strings s1 in string s with string s2

（14）STRCMP(s1,s2)

0 if all strings in s1 and s2 are the same;-1 if the first argument is less than the second according to the current classification order; 1 otherwise

（15）SUBSTRING(s,n,len)、MID(s,n,len)

The two functions do the same thing, returning a string of length len starting with the nth character in the string s

（16）LOCATE(str1,str)、POSITION(str1 IN str)、INSTR(str,str1)

The three functions work the same way, returning the starting position of the substring str1 in the string str (starting with the number of characters).

（17）REVERSE(s)

Invert the string s

（18）ELT(N,str1,str2,str3,str4,...)

Returns the Nth string

IV. Conditional judgment function

（1）IF(expr,v1,v2)

Returns v1 if expr is TRUE, otherwise returns v2

（2）IFNULL(v1,v2)

Return v1 if v1 is not NULL, otherwise return v2

（3）CASE expr WHEN v1 THEN r1 [WHEN v2 THEN v2] [ELSE rn] END

If expr is equal to some vn, then return the result after THEN, if not all values, then return rn after ELSE

V. System Information Function

（1）VERSION()

View MySQL version number

（2）CONNECTION_ID()

View the current user's connections

（3）USER()、CURRENT_USER()、SYSTEM_USER()、SESSION_USER()

Check the combination of username and host currently verified by MySQL server. Generally, the return value of these functions is the same.

（4）CHARSET(str)

View the character set used by string str

（5）COLLATION()

View String Arrangement

VI. Encryption function

（1）PASSWORD(str)

Computes and returns the encrypted string password from the original plaintext password str, note that the encryption of this function is one-way (irreversible), so it should not be used in personal applications but only in MySQL server authentication systems

（2）MD5(str)

Computes an MD5 128-bit checksum for the string, and returns the value as a binary string of 32 hexadecimal digits

（3）ENCODE(str, pswd_str)

Use pswd_str as password, encrypt str

（4）DECODE(crypt_str,pswd_str)

decrypts the encrypted string crypt_str using pswd_str as the password, crypt_str is the string returned by the ENCODE function

VII. Other functions

（1）FORMAT(x,n)

Format the number x and round to n decimal places, and return the result as a string

（2）CONV(N,from_base,to_base)

Conversion between different base numbers, the return value is a string representation of the value N, converted from_base to_base

（3）INET_ATON(expr)

Given a dot address representation of a network address as a string, return an integer representing the value of the address, which can be 4 or 8 bits

（4）INET_NTOA(expr)

Given a numeric network address (4 or 8 bits), return the dot address representation of that address as a string

（5）BENCHMARK(count,expr)

Repeating the expression expr count times, which can be used to calculate how fast MySQL processes an expression, usually results in a value of 0 (0 just means fast, not no speed). Another function is to use it internally in MySQL client to report the execution time of statements

（6）CONVERT(str USING charset)

Use charset to represent string str

The above is a small series for everyone to share MySQL commonly used functions introduced, if there is a similar doubt, may wish to refer to the above analysis to understand. If you want to know more about it, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.