Mysql (9) common functions _ MySQL

Mysql (9) common functions bitsCN.com

Mysql common functions

Related links:

Mysql (1) installation of mysql

Http: // database/201210/162314 .html;

Mysql-related operations (2)

Http: // database/201210/162315 .html;

Mysql operations on data tables (3)

Http: // database/201210/162316 .html;

Mysql (4) Data Table query operations

Http: // database/201210/162317 .html;

Mysql operations (5)

Http: // database/201210/162318 .html;

Mysql (6) string pattern matching

Http: // database/201210/163969 .html;

Mysql (7) in-depth select query

Http: // database/201210/163970 .html;

Mysql Index (8)

Http: // database/201210/163971 .html

I. string functions

CANCAT (S1, S2,... Sn) select S1, S2,... Sn as a string

INSERT (str, x, y, instr) replace string 'str' from position x with string instr

LOWER (str) converts all characters in string str to lowercase

UPPER (str) converts all characters in string str to uppercase

LEFT (str, x) returns the leftmost x characters of the str string.

RIGHT (str, x) returns x characters at the rightmost of the str string.

LPAD (str, n, pad) uses the string pad to fill the leftmost str until the length is n characters long

RPAD (str, n, pad) uses the string pad to fill the rightmost side of str until the length is n characters long

LTRIM (str) removes spaces on the left side of the str string

RTRIM (str) removes spaces on the right of the str string

REPEAT (str, x) returns the result of repeating str x times.

STRCMP (s1, s2) compares strings s1 and s2

TRIM (str) removes the spaces at the end of the string line and the line header.

SUBSTRING (str, x, y) returns the string of y string lengths starting from the string str x position

II. numeric functions

ABS (x) returns the absolute value of x.

CEIL (x) returns the maximum integer greater than x.

FLOOR (x) returns the largest integer less than x.

MOD (x, y) returns the modulo of x/y.

RAND () returns 0 ~ Random value in 1

ROUND (x, y) returns the rounded y decimal value of parameter x.

TRUNCATE (x, y) returns the result of truncating x to y decimal places.

III. date and time functions

CURDATE () returns the current date

CURTIME () returns the current time

NOW () returns the current date and time

UNIX_TIMESTAMP (date) returns the Unix timestamp of the date.

FROM_UNIXTIME returns the date value of the Unix timestamp

WEEK (date) returns the WEEK number of the year in date.

YEAR (date) returns the YEAR of date.

HOUR (time) returns the HOUR value of time

MINUTE (time) returns the MINUTE value of time

MONTHNAME (date) returns the month name of the date.

DATE_FORMAT (date, fmt) returns the date value formatted by the string fmt

DATE_ADD (date, INTERVAL expr type) returns a date or time value plus a time INTERVAL value

DATEDIFF (expr, expr2) returns the number of days between the start time and the end time.

IV. process functions

IF (value, t f) IF the value is true, t is returned; otherwise, f is returned.

IFNULL (value1, value2): If value1 is not null, value1 IS returned; otherwise, value2 is returned.

Case when [value1] THEN [result1]... ELSE

[Default] END if value1 is true, result1 is returned; otherwise, default is returned.

CASE [expr] WHEN [value1] THEN [result1]... ELSE

[Default] END if expr is equal to value1, result1 is returned; otherwise, default is returned.

V. Other functions

DATABASE () returns the current DATABASE name

VERSION () returns the current database VERSION.

USER () returns the current login USER name

INET_ATON (IP) returns the number of IP addresses

INET_NTOA () returns the IP address represented by a number.

PASSWORD () returns the encrypted version of the string str

MD5 () returns the MD5 value of the str string.

BitsCN.com