Shulou(Shulou.com)05/31 Report--

What are the common functions in mysql? in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Control flow function

IFNULL (expr1,expr2)

If expr1 is not NULL,IFNULL () returns expr1, otherwise it returns expr2. IFNULL () returns a number or string value, depending on the context in which it is used.

Mysql > select IFNULL (1Jue 0);-> 1mysql > select IFNULL (0JEI 10);-> 0mysql > select IFNULL (1pm 0LJ 10);-> 10mysql > select IFNULL (1pm 0mlys');-> 'yes''

IF (expr1,expr2,expr3)

If expr1 is TRUE (expr10 and expr1NULL), then IF () returns expr2, otherwise it returns expr3. IF () returns a number or string value, depending on the context in which it is used.

Mysql > select IF;-> 3mysql > select IF (1 'yes'mysql > select IF (strcmp (' test','test1'), 'yes','no');->' no'

Expr1 is calculated as an integer value, which means that if you are testing floating-point or string values, you should use a comparison operation to do so.

Mysql > select IF (0.1 0mysql > select IF);-> 1

In the first case above, IF (0. 1) returns 0 because 0. 1 is converted to an integer value, causing IF (0) to be tested. This may not be what you expect. In the second case, the comparison tests the original floating-point value to see if it is non-zero, and the result of the comparison is used as an integer.

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

The first version returns result, where value=compare-value. In the second version, result is returned if the first condition is true. If there is no matching result value, the result is returned in the result after ELSE. If there is no ELSE part, then NULL is returned.

Mysql > SELECT CASE 1 WHEN 1 THEN "one" WHEN 2 THEN "two" ELSE "more" END;-> "one" mysql > SELECT CASE WHEN 1 > 0 THEN "true" ELSE "false" END;-> "true" mysql > SELECT CASE BINARY "B" when "a" then 1 when "b" then 2 END;-> NULL

Mathematical function

All mathematical functions return NULL in the event of an error.

-

Monocular subtraction. Change the symbol of the parameter.

Mysql > select-2; note that if this operator is used with a BIGINT, the return value is a BIGINT! This means that you should avoid using-- on integers-- that might have a value of-2 ^ 63!

ABS (X)

Returns the absolute value of X.

Mysql > select ABS (2);-> 2mysql > select ABS (- 32);-> 32

This feature can be safely used for BIGINT values.

SIGN (X)

Returns the symbol of the parameter, which is-1, 0, or 1, depending on whether X is negative, zero, or positive.

Mysql > select SIGN (- 32);->-1mysql > select SIGN (0);-> 0mysql > select SIGN (234);-> 1

MOD (NMagol M)

%

Module (similar to the% operator in C). Returns the remainder of N divided by M.

Mysql > select MOD (234,10);-> 4mysql > select 253% 7;-> 1mysql > select MOD (29jie 9);-> 2 this function is safe for BIGINT values.

FLOOR (X)

Returns the maximum integer value not greater than X.

Mysql > select FLOOR (1.23);-> 1mysql > select FLOOR (- 1.23);->-2 notice that the return value is transformed into a BIGINT!

CEILING (X)

Returns a minimum integer value not less than X.

Mysql > select CEILING;-> 2mysql > select CEILING (- 1.23);->-1

Notice that the return value is transformed into a BIGINT!

ROUND (X)

Returns an integer rounded to the parameter X.

Mysql > select ROUND;->-1mysql > select ROUND;->-2mysql > select ROUND;-> 2

Notice that the return value is transformed into a BIGINT!

ROUND (XBI D)

Returns a number where D is a decimal when the parameter X is rounded. If D is 0, there will be no decimal point or decimal part.

Mysql > select ROUND (1.298, 1);-> 1.3mysql > select ROUND (1.298, 0);-> 1

Notice that the return value is transformed into a BIGINT!

EXP (X)

Returns the X power of the value e (the base of the natural logarithm).

Mysql > select EXP (2);-> 7.389056mysql > select EXP (- 2);-> 0.135335

LOG (X)

Returns the natural logarithm of X.

Mysql > select LOG (2);-> 0.693147mysql > select LOG (- 2);-> NULL

If you want the logarithm of any base B of a number X, use the formula LOG (X) / LOG (B).

LOG10 (X)

Returns the base 10 logarithm of X.

Mysql > select LOG10 (2);-> 0.301030mysql > select LOG10;-> 2.000000mysql > select LOG10 (- 100);-> NULL

POW (XQuery Y)

POWER (XQuery Y)

Returns the Y power of the value X.

Mysql > select POW (2);-> 4.000000mysql > select POW (2);-> 0.250000

SQRT (X)

Returns the square root of a nonnegative number X.

Mysql > select SQRT (4);-> 2.000000mysql > select SQRT (20);-> 4.472136

PI ()

Returns the value of PI (pi).

Mysql > select PI ();-> 3.141593

COS (X)

Returns the cosine of X, where X is given in radians.

Mysql > select COS (PI ());->-1.000000

SIN (X)

Returns the sine of X, where X is given in radians.

Mysql > select SIN (PI ());-> 0.000000

TAN (X)

Returns the tangent of X, where X is given in radians.

Mysql > select TAN (PI () + 1);-> 1.557408

ACOS (X)

Returns the inverse cosine of X, that is, the cosine value is X. If X is not in the range of-1 to 1, return NULL.

Mysql > select ACOS (1);-> 0.000000mysql > select ACOS (1.0001);-> NULLmysql > select ACOS (0);-> 1.570796

ASIN (X)

Returns the X arcsine value, that is, its sine value is X. L returns NULL if X is not in the range of-1 to 1.

Mysql > select ASIN (0.2);-> 0.201358mysql > select ASIN ('foo');-> 0.000000

ATAN (X)

Returns the inverse tangent of X, that is, its tangent is X.

Mysql > select ATAN (2);-> 1.107149mysql > select ATAN (- 2);->-1.107149

ATAN2 (XQuery Y)

Returns the inverse tangent of two variables X and Y. It is similar to calculating the inverse tangent of Ymax X, except that the symbols of two parameters are used to determine the quadrant of the result.

Mysql > select ATAN (- 2);->-0.785398mysql > select ATAN (PI (), 0);-> 1.570796

COT (X)

Returns the cotangent of X.

Mysql > select COT (12);->-1.57267341mysql > select COT (0);-> NULL

RAND ()

RAND (N)

Returns a random floating-point value in the range 0 to 1.0. If an integer parameter N is specified, it is used as the seed value.

Mysql > select RAND ();-> 0.5925mysql > select RAND (20);-> 0.1811mysql > select RAND (20);-> 0.1811mysql > select RAND ();-> 0.2079mysql > select RAND ();-> 0.7888

You cannot use a column with the RAND () value in an ORDER BY clause, because ORDER BY will repeat the column multiple times. In MySQL3.23, however, you can do:

SELECT * FROM table_name ORDER BY RAND (), which is advantageous to get one from SELECT * FROM table1,table2 WHERE aquib AND c select LEAST (2L0);-> 0mysql > select LEAST (34.0pr 3.0, 5.0767.0);-> 3.0mysql > select LEAST ("B", "A", "C");-> "A"

In versions prior to MySQL 3.22.5, you could use MIN () instead of LEAST.

If the return value is used in an INTEGER context, or if all parameters are integer values, they are compared as integers.

If the return values are used in a REAL context, or if all parameters are real values, they are compared as real numbers.

If any parameter is a size-sensitive string, the parameter is compared as a case-sensitive string.

In other cases, parameters are compared as case-insensitive strings.

GREATEST (XMagna Y...)

Returns the parameter with the maximum (maximum value). Parameters are compared using the same rules as LEAST.

Mysql > select GREATEST (2Jing 0);-> 2mysql > select GREATEST (34.0, 3.0, 5.0767.0);-> 767.0mysql > select GREATEST ("B", "A", "C");-> "C" in MySQL versions before 3.22.5, you can use MAX () instead of GREATEST.

DEGREES (X)

Returns the parameter X, which is transformed from radians to angles.

Mysql > select DEGREES (PI ());-> 180.000000

RADIANS (X)

Returns the parameter X, which is transformed from an angle to radians.

Mysql > select RADIANS (90);-> 1.570796

TRUNCATE (XBI D)

Returns the number X, truncated to D decimal places. If D is 0, there will be no decimal point or decimal part.

Mysql > select TRUNCATE (1.223L1);-> 1.2mysql > select TRUNCATE (1.999L1);-> 1.9mysql > select TRUNCATE (1.999L0);-> 1 string function for operations on string positions, the first position is marked as 1.

ASCII (str)

Returns the ASCII code value of the leftmost character of the string str. Returns 0 if str is an empty string. Returns NULL if str is NULL.

Mysql > select ASCII ('2');-> 50mysql > select ASCII (2);-> 50mysql > select ASCII ('dx');-> 100

You can also see the ORD () function.

ORD (str)

If the leftmost character of the string str is a multibyte character, by using the format ((first byte ASCII code) * 256 + (second byte ASCII code)) [* 256+third byte ASCII code...] Returns the ASCII code value of the character to return the multibyte character code. If the leftmost character is not a multibyte character. Returns the same value as the ASCII () function.

Mysql > select ORD ('2');-> 50

CONV (NParth from Basebook Basebook totalbase)

Convert numbers between different digital bases. Returns the string number of the number N, transformed from the from_base base to the to_ base, and NULL if any parameter is NULL. The parameter N is interpreted as an integer, but can be specified as an integer or a string. The minimum base is 2 and the maximum base is 36. If to_base is a negative number, N is considered to be a signed number, otherwise N is regarded as an unsigned number.

CONV works at 64-bit precision.

Mysql > select CONV ("a", 16jue 2);-> '1010'mysql > select CONV ("6e", 18page8);->' 172'mysql > select CONV (- 17pr 10pyrryl 18);->'- H'mysql > select CONV (10 + "10" + '10fuz0xajin10);->' 40'

BIN (N)

Returns a string representation of the binary value N, where N is a long integer (BIGINT) number, which is equivalent to CONV. If N is NULL, return NULL.

Mysql > select BIN (12);-> '1100'

OCT (N)

Returns a string representation of the octal value N, where N is a long integer number, which is equivalent to CONV. If N is NULL, return NULL.

Mysql > select OCT (12);-> '14'

HEX (N)

Returns a string representation of the hexadecimal value N, where N is a BIGINT number, which is equivalent to CONV. If N is NULL, return NULL.

Mysql > select HEX (255);-> 'FF'

CHAR (NJI...)

CHAR () interprets parameters as integers and returns a string consisting of the ASCII code characters of those integers. The NULL value is skipped.

Mysql > select CHAR;-> 'MySQL'mysql > select CHAR;->' MMM''

CONCAT (str1,str2,...)

Returns the string from the parameter link. Returns NULL if any parameter is NULL. There can be more than 2 parameters. A numeric parameter is transformed into an equivalent string.

Mysql > select CONCAT ('My',' slots, 'QL');->' MySQL'mysql > select CONCAT ('My', NULL,' QL');-> NULLmysql > select CONCAT;-> '14.3'

LENGTH (str)

OCTET_LENGTH (str)

CHAR_LENGTH (str)

CHARACTER_LENGTH (str)

Returns the length of the string str.

Mysql > select LENGTH ('text');-> 4mysql > select OCTET_LENGTH (' text');-> 4

Note that for multibyte characters, their CHAR_LENGTH () is evaluated only once.

LOCATE (substr,str)

POSITION (substr IN str)

Returns the position where the substring substr appears first in the string str, and returns 0. 0 if substr is not in str.

Mysql > select LOCATE ('bar',' foobarbar');-> 4mysql > select LOCATE ('xbar',' foobar');-> 0 this function is multibyte reliable.

LOCATE (substr,str,pos)

Returns the position where the substring substr appears first in the string str, starting with the position pos. If substr is not in str, return 0.

Mysql > select LOCATE ('bar',' foobarbar',5);-> 7

This function is multibyte reliable.

INSTR (str,substr)

Returns the first occurrence of the substring substr in the string str. This is the same as LOCATE (), which has the form of two parameters, except that the parameters are reversed.

Mysql > select INSTR ('foobarbar',' bar');-> 4mysql > select INSTR ('xbar',' foobar');-> 0

This function is multibyte reliable.

LPAD (str,len,padstr)

Returns the string str, padded on the left with the string padstr until str is len characters long.

Mysql > select LPAD ('hi',4,'??');->'? hi'

RPAD (str,len,padstr)

Returns the string str, padded on the right with the string padstr until str is len characters long.

Mysql > select RPAD ('hi',5,'?');->' hi???'

LEFT (str,len)

Returns the leftmost len characters of the string str.

Mysql > select LEFT ('foobarbar', 5);->' fooba'

This function is multibyte reliable.

RIGHT (str,len)

Returns the rightmost len characters of the string str.

Mysql > select RIGHT ('foobarbar', 4);->' rbar'

This function is multibyte reliable.

SUBSTRING (str,pos,len)

SUBSTRING (str FROM pos FOR len)

MID (str,pos,len)

Returns a substring of len characters from the string str, starting with the position pos. The variant form of using FROM is the ANSI SQL92 syntax.

Mysql > select SUBSTRING ('Quadratically',5,6);->' ratica'

This function is multibyte reliable.

SUBSTRING (str,pos)

SUBSTRING (str FROM pos)

Returns a substring from the starting position of the string str, pos.

Mysql > select SUBSTRING ('Quadratically',5);->' ratically'mysql > select SUBSTRING ('foobarbar' FROM 4);->' barbar'

This function is multibyte reliable.

SUBSTRING_INDEX (str,delim,count)

Returns the substring after the delimiter delim that appears at the count of the string str. If count is positive, returns all characters from the last delimiter to the left (the number from the left). If count is negative, returns the last delimiter to all characters on the right (from the right).

Mysql > select SUBSTRING_INDEX ('www.mysql.com','.', 2);-> 'www.mysql'mysql > select SUBSTRING_INDEX (' www.mysql.com','.'- 2);-> 'mysql.com'

This function is reliable for multibytes.

LTRIM (str)

Returns the string str whose leading space character is deleted.

Mysql > select LTRIM ('barbar');->' barbar'

RTRIM (str)

Returns the string str whose trailing space character was deleted.

Mysql > select RTRIM ('barbar');->' barbar' this function is reliable for multibytes.

TRIM ([[BOTH | LEADING | TRAILING] [remstr] FROM] str)

Returns the string str whose remstr prefixes or suffixes are deleted. If no modifiers BOTH, LEADING, or TRAILING are given, BOTH is assumed. If remstr is not specified, the space is deleted.

Mysql > select TRIM ('bar');->' bar'mysql > select TRIM (LEADING'x 'FROM' xxxbarxxx');-> 'barxxx'mysql > select TRIM (BOTH' x 'FROM' xxxbarxxx');-> 'bar'mysql > select TRIM (TRAILING' xyz' FROM 'barxxyz');->' barx'

This function is reliable for multibytes.

SOUNDEX (str)

Returns a homonym string for str. Two strings that sound "roughly the same" should have the same homonym string. A "standard" homophone string is four characters long, but the SOUNDEX () function returns a string of any length. You can use SUBSTRING () on the result to get a "standard" homophone string. All non-numeric alphanumeric characters are ignored in the given string. All international letters outside Amurz are treated as vowels.

Mysql > select SOUNDEX ('Hello');->' H400'mysql > select SOUNDEX ('Quadratically');->' Q36324'

SPACE (N)

Returns a string of N space characters.

Mysql > select SPACE (6);->''

REPLACE (str,from_str,to_str)

Returns the string str where all occurrences of the string from_str are replaced by the string to_str.

Mysql > select REPLACE ('www.mysql.com',' walled, 'Ww');->' WwWwWw.mysql.com'

This function is reliable for multibytes.

REPEAT (str,count)

Returns a string consisting of the string str that repeats countTimes times. If count select REPEAT ('MySQL', 3);->' MySQLMySQLMySQL'

REVERSE (str)

Returns the string str that reverses the order of characters.

Mysql > select REVERSE ('abc');->' cba'

This function is reliable for multi-bytes.

INSERT (str,pos,len,newstr)

Returns the string str, the substring at the beginning of the pos and the length of the len character is replaced by the string newstr.

Mysql > select INSERT ('Quadratic', 3,4,' What');-> 'QuWhattic'

This function is reliable for multibytes.

ELT (NMagol str1 ~ str2 ~ str3 ~..)

If N = 1, return str1, if N =

2, return str2, and so on. Returns NULL if N is less than 1 or greater than the number of parameters. ELT () is the inverse operation of FIELD ().

Mysql > select ELT (1, 'ej',' Heja', 'hej',' foo');-> 'ej'mysql > select ELT (4,' ej', 'Heja',' hej', 'foo');->' foo'

FIELD (str,str1,str2,str3,...)

Returns str in str1, str2, str3

... The index of the list. If str is not found, 0 is returned. FIELD () is the inverse operation of ELT ().

Mysql > select FIELD ('ej',' Hej', 'ej',' Heja', 'hej',' foo');-> 2mysql > select FIELD ('fo',' Hej', 'ej',' Heja', 'hej',' foo');-> 0

FIND_IN_SET (str,strlist)

If the string str is in the table strlist consisting of N substrings, a value from 1 to N is returned. A string table is a string of substrings separated by ",". If the first argument is a constant string and the second argument is a column of type SET, the FIND_IN_SET () function is optimized to use bit operations! Returns 0 if str is not in strlist or if strlist is an empty string. Returns NULL if any of the parameters is NULL. If the first parameter contains a ",", the function will not work properly.

Mysql > SELECT FIND_IN_SET ('bachelors, page1, pr, pas, c,');-> 2

MAKE_SET (bits,str1,str2,...)

Returns a collection containing a string of substrings separated by "," characters, consisting of strings corresponding to bits in the bits collection. Str1 corresponds to bit 0, corresponding to bit 1, and so on. In str1

Str2,... The NULL string in is not added to the result.

Mysql > SELECT MAKE_SET;-> 'a'mysql > SELECT MAKE_SET';-> 'hello,world'mysql > SELECT MAKE_SET');-> 'hello,world'mysql > world');-> 'world';-> 'world';-> 'world');-> 'world';-> 'hello,world'mysql > SELECT MAKE_SET);->''

EXPORT_SET (bits,on,off, [separator, [number_of_bits]])

Returns a string, where you get a "on" string for each bit set in "bits" and a "off" string for each reset bit. Each string is separated by "separator" (default ","), and only the "number_of_bits" (default 64) bit of "bits" is used.

Mysql > select EXPORT_SET (5 recordings)-> Yrecincts

LCASE (str)

LOWER (str)

Returns the string str, which changes all characters to lowercase based on the current character set mapping (default is ISO-8859-1 Latin1). This function is reliable for multibytes.

Mysql > select LCASE ('QUADRATICALLY');->' quadratically'

UCASE (str)

UPPER (str)

Returns the string str, changing all characters to uppercase based on the current character set mapping (default is ISO-8859-1 Latin1). This function is reliable for multibytes.

Mysql > select UCASE ('Hej');->' HEJ'

This function is reliable for multibytes.

LOAD_FILE (file_name)

Reads the file and returns the contents of the file as a string. The file must be on the server, you must specify the full pathname to the file, and you must have file permission. Everything in the file must be readable and less than max_allowed_packet. If the file does not exist or cannot be read for one of the above reasons, the function returns NULL.

Mysql > UPDATE table_name SET blob_column=LOAD_FILE ("/ tmp/picture") WHERE id=1;MySQL automatically converts numbers to strings if necessary, and vice versa: mysql > SELECT 1 + "1";-> 2mysql > SELECT CONCAT (2 test');->'2 test' if you want to explicitly convert a number to a string, pass it as an argument to CONCAT (). If the string function provides a binary string as an argument, the resulting string is also a binary string. A number that is converted to a string is treated as a binary string. This only affects the comparison.

Date and time function

For a description of the range of values each type has and a valid format for values between specified dates, see 7.3.6 date and time types. Here is an example of using a date function. The following query selects all records with date_col values within the last 30 days: mysql > SELECT something FROM table WHERE TO_DAYS (NOW ())-TO_DAYS (date_col) select DAYOFWEEK ('1998-02-03');-> 3

WEEKDAY (date)

Returns the weekly index of date (0 = Monday, 1 = Tuesday,... 6 = Sunday).

Mysql > select WEEKDAY ('1997-10-04 22 5mysql > select WEEKDAY (' 1997-11-05');-> 2

DAYOFMONTH (date)

Returns the mid-month date of the date, in the range of 1 to 31.

Mysql > select DAYOFMONTH ('1998-02-03');-> 3

DAYOFYEAR (date)

Returns the number of days in a year for date, in the range of 1 to 366.

Mysql > select DAYOFYEAR ('1998-02-03');-> 34

MONTH (date)

Returns the month of the date, ranging from 1 to 12.

Mysql > select MONTH ('1998-02-03');-> 2

DAYNAME (date)

Returns the name of the week of date.

Mysql > select DAYNAME ("1998-02-05");-> 'Thursday'

MONTHNAME (date)

Returns the month name of the date.

Mysql > select MONTHNAME ("1998-02-05");-> 'February'

QUARTER (date)

Returns the quarter of the year in date, ranging from 1 to 4.

Mysql > select QUARTER (98-04-01');-> 2

WEEK (date)

WEEK (date,first)

For places where Sunday is the first day of the week, there is a single parameter that returns the number of weeks of date, ranging from 0 to 52. The two-parameter form WEEK () allows you to specify whether the week begins on Sunday or Monday. If the second parameter is 0, the week starts on Sunday, and if the second parameter is 1, it starts on Monday.

Mysql > select WEEK ('1998-02-20');-> 7mysql > select WEEK ('1998-02-20);-> 7mysql > select WEEK (' 1998-02-20);-> 8

YEAR (date)

Returns the year of the date, ranging from 1000 to 9999.

Mysql > select YEAR ('98-02-03');-> 1998

HOUR (time)

Returns the hour of time, ranging from 0 to 23.

Mysql > select HOUR ('10VOV 05RU 03');-> 10

MINUTE (time)

Returns the minutes of time, ranging from 0 to 59.

Mysql > select MINUTE ('98-02-03 10 purl 05purl 03');-> 5

SECOND (time)

The number of seconds to return to time, ranging from 0 to 59.

Mysql > select SECOND ('10VR 05R 03');-> 3

PERIOD_ADD (PPJN)

Add N months to stage P (in YYMM or YYYYMM format). Returns the value in the format YYYYMM. Note that the phase parameter P is not a date value.

Mysql > select PERIOD_ADD (9801 Magi 2);-> 199803

PERIOD_DIFF (P1and P2)

Returns the number of months between periods P1 and P2, which should be in the format YYMM or YYYYMM. Note that the period parameters P1 and P2 are not date values.

Mysql > select PERIOD_DIFF (9802 ~ 199703);-> 11

DATE_ADD (date,INTERVAL expr type)

DATE_SUB (date,INTERVAL expr type)

ADDDATE (date,INTERVAL expr type)

SUBDATE (date,INTERVAL expr type)

These functions perform date operations. For MySQL 3.22, they are new. ADDDATE () and SUBDATE () are synonyms for DATE_ADD () and DATE_SUB (). In MySQL 3.23, you can use + and-instead of DATE_ADD () and DATE_SUB (). (see example) date is a DATETIME or date value that specifies a start date, expr is an expression that specifies an interval value that is added to or subtracted from the start date, and expr is a string; it can start with a "-" to indicate a negative interval. Type is a keyword that indicates how an expression should be interpreted. EXTRACT (type

The FROM date) function returns the "type" interval from the date. The following table shows how the type and expr parameters are associated:

Type value means expected expr format SECOND seconds SECONDSMINUTE minute MINUTESHOUR time HouseDay DaySMONTH month MONTHSYEAR year YEARSMINUTE_SECOND minutes and seconds "MINUTES:SECONDS" HOUR_MINUTE hours and minutes "HOURS:MINUTES" DAY_ hour and hour "DAYS HOURS" YEAR_MONTH year and month "YEARS-MONTHS" HOUR_SECOND hours, minutes, "HOURS:MINUTES:SECONDS" DAY_MINUTE days, hours, minutes "DAYS HOURS:MINUTES" DAY_SECOND days, hours, minutes, seconds "DAYS HOURS:MINUTES:SECONDS"

MySQL allows any punctuation separator in expr format. Indicates that the recommended delimiter is displayed. If the date parameter is a date value and your calculation contains only the YEAR, MONTH, and DAY parts (that is, no time part), the result is a date value. Otherwise, the result is a data value.

Mysql > SELECT "1997-12-31 23:59:59" + INTERVAL 1 SECOND;-> 1998-01-01 00:00:00mysql > SELECT INTERVAL 1 DAY + "1997-12-31";-> 1998-01-01mysql > SELECT "1998-01-01"-INTERVAL 1 SECOND;-> 1997-12-31 23:59:59 mysql > SELECT DATE_ADD ("1997-12-31 23:59:59", INTERVAL 1 SECOND) -> 1998-01-01 00:00:00mysql > SELECT DATE_ADD ("1997-12-31 23:59:59", INTERVAL 1 DAY);-> 1998-01-01 23:59:59mysql > SELECT DATE_ADD ("1997-12-31 23:59:59" INTERVAL "1:1" MINUTE_SECOND) -> 1998-01-01 00:01:00mysql > SELECT DATE_SUB ("1998-01-01 00:00:00", INTERVAL "1 1:1:1" DAY_SECOND);-> 1997-12-30 22:58:59mysql > SELECT DATE_ADD ("1998-01-01 00:00:00", INTERVAL "- 1 10" DAY_HOUR) -> 1997-12-30 14:00:00mysql > SELECT DATE_SUB ("1998-01-02", INTERVAL 31 DAY);-> 1997-12-02mysql > SELECT EXTRACT (YEAR FROM "1999-07-02");-> 1999mysql > SELECT EXTRACT (YEAR_MONTH FROM "1999-07-02 01:02:03");-> 199907mysql > SELECT EXTRACT (DAY_MINUTE FROM "1999-07-02 01:02:03");-> 20102

If you specify an interval value that is too short (excluding the interval part expected by the type keyword), MySQL assumes that you omit the leftmost interval value. For example, if you specify that a type is DAY_SECOND, the value expr is expected to have days, hours, minutes, and seconds. If you specify a value like 1:10, MySQL assumes that the day and hour parts are lost and that the value represents minutes and seconds. In other words, "1:10"

DAY_SECOND interprets it as equivalent to "1:10" MINUTE_SECOND, which is ambiguous to the way that MySQL interprets that the time value represents elapsed time rather than time of day. If you use a date that is really incorrect, the result is NULL. If you increase MONTH, YEAR_MONTH, or YEAR and the resulting date is greater than the maximum number of days in the new month, the date is adjusted with the maximum day in the new moon.

Mysql > select DATE_ADD ('1998-01-30, Interval 1 month);-> 1998-02-28 Note: from the previous example, the words INTERVAL and type keywords are not case-sensitive.

TO_DAYS (date)

Give a date date and return a number of days (from the number of days in the year).

Mysql > select TO_DAYS (950501);-> 728779mysql > select TO_DAYS ('1997-10-07');-> 729669

TO_DAYS () is not intended to be used to use the values before the Gregorian calendar (1582).

FROM_DAYS (N)

Give a number of days N and return a date value.

Mysql > select FROM_DAYS (729669);-> '1997-10-07'

TO_DAYS () is not intended to be used to use the values before the Gregorian calendar (1582).

DATE_FORMAT (date,format)

Format the date value according to the format string. The following modifiers can be used in format strings:

% M month name (January... December)% W week name (Sunday... Saturday)% D the date of the month with the English prefix (1st, 2nd, 3rd, etc.). )% Y years, numbers, 4 digits% y years, numbers, 2 digits% an abbreviated name of the week (Sun... Sat) the number of days in d months, number (00... 31)% e days in the month, number (0. 31)% m months, number (01. 12)% c month, number (1 …... 12)% b abbreviated month name (Jan... Dec)% j days of the year (001... 366)% H hours (00. 23)% k hours (0. 23)% h hours (01. 12)% I hours (01. 12)% l hours (1 …... 12)% I minutes, number (00. 59)% r time, 12 hours (hh:mm:ss [AP] M)% T time, 24 hours (hh:mm:ss)% S seconds (00 …... 59)% s seconds (00. 59) days in a week of pAM or PM%w (0=Sunday... 6=Saturday)% U week (0... 52), here Sunday is the first day of the week. 52), here Monday is the first day of the week%% a word "%".

All other characters are copied to the result without explanation.

Mysql > select DATE_FORMAT ('1997-10-04 22 Saturday October 1997'mysql 2300mm,'% W% M% Y');-> 'HRV > select DATE_FORMAT (' 1997-10-04 22lv 2300mm,'% HRV% iSaturday October 1997'mysql% s') -> '22:23:00'mysql > select DATE_FORMAT' ('1997-10-04 22 Oct 277'mysql,'% D% y% a% d% m% b% j');-> 'select DATE_FORMAT 97 Sat 04 10 Oct 277'mysql,' H% k% I% T% S% w') ->'22 22 10 10:23:00 PM 22:23:00 00 6'

In MySQL3.23,% is required before the format modifier character. In earlier versions of MySQL,% was optional.

TIME_FORMAT (time,format)

This works like the DATE_FORMAT () function above, but the format string can only contain format modifiers that handle hours, minutes, and seconds. Other modifiers produce a null value or 0.

CURDATE ()

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.

Mysql > select CURDATE ();-> '1997-12-15'mysql > select CURDATE () + 0;-> 19971215

CURTIME ()

CURRENT_TIME

Returns the current time value in 'HH:MM:SS' or HHMMSS format, depending on whether the function is used in a string or in the context of a number.

Mysql > select CURTIME ();-> '23:50:26'mysql > select CURTIME () + 0;-> 235026

NOW ()

SYSDATE ()

CURRENT_TIMESTAMP

Returns the current date and time in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or in the context of a number.

Mysql > select NOW ();-> '1997-12-1523: 50:26'mysql > select NOW () + 0;-> 19971215235026

UNIX_TIMESTAMP ()

UNIX_TIMESTAMP (date)

If there is no argument call, returns a Unix timestamp (seconds from '1970-01-01 00:00:00'GMT). If UNIX_TIMESTAMP () is called with a date parameter, it returns the second value from' 1970-01-01 00:00:00'GMT. Date can be a DATE string, a DATETIME string, a TIMESTAMP, or a number of local time in YYMMDD or YYYYMMDD format.

Mysql > select UNIX_TIMESTAMP ();-> 882226357mysql > select UNIX_TIMESTAMP ('1997-10-04 22 882226357mysql 2315);-> 875996580

When UNIX_TIMESTAMP is used for a TIMESTAMP column, the function accepts the value directly, with no implicit "string-to-unix-timestamp" transformation.

FROM_UNIXTIME (unix_timestamp)

Returns the value represented by the unix_timestamp parameter in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context.

Mysql > select FROM_UNIXTIME (875996580);-> '1997-10-0422: 23:00'mysql > select FROM_UNIXTIME (875996580) + 0;-> 19971004222300

FROM_UNIXTIME (unix_timestamp,format)

Returns a string representing the Unix time stamp, formatted according to the format string. Format can contain the same modifiers as the entries listed in the DATE_FORMAT () function.

Mysql > select FROM_UNIXTIME (UNIX_TIMESTAMP (),'% Y% D% M% h:%i:%s% x');-> '1997 23rd December 03:43:30 x'

SEC_TO_TIME (seconds)

Returns the seconds parameter, transformed into hours, minutes, and seconds, with values formatted as' HH:MM:SS' or HHMMSS, depending on whether the function is used in a string or in a numeric context.

Mysql > select SEC_TO_TIME (2378);-> '00:39:38'mysql > select SEC_TO_TIME (2378) + 0;-> 3938

TIME_TO_SEC (time)

Returns the time parameter, which is converted to seconds.

Mysql > select TIME_TO_SEC ('22 80580mysql 2300');-> 80580mysql > select TIME_TO_SEC ('00 80580mysql 39 °38');-> 2378

Other functions

DATABASE ()

Returns the current database name.

Mysql > select DATABASE ();-> 'test'

If there is no current database, DATABASE () returns an empty string.

USER ()

SYSTEM_USER ()

SESSION_USER ()

Returns the current MySQL user name.

Mysql > select USER ();-> 'davida@localhost'

In MySQL 3.22.11 or later, this includes the user name and customer hostname. You can extract only the user name part like this (whether the value includes the host name part can work):

Mysql > select substring_index (USER (), "@", 1);-> 'davida'

PASSWORD (str)

Calculates a password string from the plain text password str. This function is used to encrypt MySQL passwords in order to store passwords in the Password column of the user authorization table.

Mysql > select PASSWORD ('badpwd');->' 7f84554057dd964b'

PASSWORD () encryption is non-reversible. PASSWORD () does not perform password encryption in the same way as Unix password encryption. You should not assume that if your Unix password is the same as your MySQL password, PASSWORD () will result in the same encryption value as that stored in the Unix password file. See ENCRYPT ().

ENCRYPT (str [, salt])

Encrypt the str using the Unix crypt () system call. The salt parameter should be a string of 2 characters. (in MySQL 3.22.16, salt can be longer than 2 characters. )

Mysql > select ENCRYPT ("hello");-> 'VxuFAJXVARROc'

If crypt () is not available on your system, ENCRYPT () always returns NULL. ENCRYPT () retains only the first eight characters of str and ignores everything else, at least on some systems. This will be determined by the behavior of the underlying crypt () system call.

ENCODE (str,pass_str)

Use pass_str as the password to encrypt str. To decrypt the result, use DECODE (). The result is a binary string, and if you want to save it in the column, use a BLOB column type.

DECODE (crypt_str,pass_str)

Use pass_str as the password to decrypt the encrypted string crypt_str. Crypt_str should be a string returned by ENCODE ().

MD5 (string)

Calculates the MD5 checksum on the string. The value can be returned as a 32-long hexadecimal number, for example as a hash key.

Mysql > select MD5 ("testing")-> 'ae2b1fca515949e5d54fb22b8ed95575'

This is a "RSA data Security MD5 message digest algorithm".

LAST_INSERT_ID ([expr])

Returns the last automatically generated value that is inserted into an AUTO_INCREMENT column. See 20.4.29 mysql_insert_id ().

Mysql > select LAST_INSERT_ID ();-> 195

The resulting ID is maintained at the server on a per-connection basis and will not be changed by other clients. If you update another AUTO_INCREMENT column with a non-magic value (that is, a value that is not NULL and a value that is not 0), it will not even be changed. If expr is given as a parameter in the LAST_INSERT_ID () of a UPDATE clause, the parameter value is returned as a LAST_INSERT_ID () value. This can be used to mimic the order: first create the table:

Mysql > create table sequence (id int not null); mysql > insert into sequence values (0)

The table can then be used to generate sequence numbers, like this:

Mysql > update sequence set id=LAST_INSERT_ID (id+1)

You can generate the order without calling LAST_INSERT_ID (), but the utility that uses the function automatically maintains the ID value on the server as the last automatically generated value. You can retrieve the new ID value, just as you can read any normal AUTO_INCREMENT value in the normal MySQL. For example, LAST_INSERT_ID () (with no argument) returns the new ID. The C API function mysql_insert_id () can also be used to get a value.

FORMAT (XBI D)

Format the number X to be similar to the format'#, rounded to D as a decimal. If D is 0, there will be no decimal point and decimal part.

Mysql > select FORMAT (12332.123456, 4);-> '12332.1235'mysql > select FORMAT (12332.1);->' 12332.1000'mysql > select FORMAT (12332.2);-> '12332'

VERSION ()

Returns a string indicating the version of the MySQL server.

Mysql > select VERSION ();-> '3.22.19b Murlog'

GET_LOCK (str,timeout)

An attempt was made to get a lock on a name given by the string str, and the second timeout is a timeout. Returns 1 if the lock is successfully obtained, 0 if the attempt times out, or NULL if an error occurs (for example, an overflow from memory or a thread killed with mysqladmin kill). When you execute RELEASE_LOCK (), execute a new GET_LOCK (), or the thread terminates, a lock is released. This function can be used to implement an application lock or an analog record lock, which prevents other customers from requesting locks with the same name; customers who approve of a given lock string name can use the string to perform the lock suggested by the child collaboration.

Mysql > select GET_LOCK ("lock1", 10);-> 1mysql > select GET_LOCK ("lock2", 10);-> 1mysql > select RELEASE_LOCK ("lock2");-> 1mysql > select RELEASE_LOCK ("lock1");-> NULL

Notice that the second RELEASE_LOCK () call returns NULL because the lock "lock1" is automatically released by the second GET_LOCK () call.

RELEASE_LOCK (str)

Releases the lock acquired through GET_LOCK () named by the string str. Returns 1 if the lock is released, 0 if the lock is not locked by this thread (in this case the lock is not released), and NULL if the named lock does not exist. If the lock has never been acquired by calling GET_LOCK () or if it has been released, the lock will not exist.

BENCHMARK (count,expr)

The BENCHMARK () function repeats the countTimes execution of the expression expr, which can be used to time MySQL to process how fast the expression is. The result value is always 0. Intended for mysql customers, it reports the execution time of the query.

Mysql > select BENCHMARK (1000000 hello encode ("hello", "goodbye") +-+ | BENCHMARK (1000000 focus encode ("hello") "goodbye")) | +-+ | 0 | +-+ 1 row in set (4.74sec)

The reported time is the elapsed time on the client side, not the CPU time on the server side. It might be wise to execute BENCHMARK () several times, and note how much the server machine is loaded to interpret the results.

Functions used with the GROUP BY clause

If you use an aggregate function in a statement that does not contain a GROUP BY clause, it is equivalent to aggregating all lines.

COUNT (expr)

Returns the number of non-null values for rows retrieved by a SELECT statement.

Mysql > select student.student_name,COUNT (*) from student,course where student.student_id=course.student_id GROUP BY student_name

COUNT (*) varies somewhat in the number of retrieved rows it returns, regardless of whether they contain null values or not. If SELECT retrieves from a table, or if no other columns are retrieved and there is no WHERE clause, COUNT (*) is optimized to return quickly. For example:

Mysql > select COUNT (*) from student

COUNT (DISTINCT expr, [expr...])

Returns the number of different values.

Mysql > select COUNT (DISTINCT results) from student

In MySQL, you can get the number of different combinations of expressions by giving a list of expressions. In ANSI SQL, you may have to be in CODE (DISTINCT..) Concatenate all expressions within.

AVG (expr)

Returns the average of expr.

Mysql > select student_name, AVG (test_score) from student GROUP BY student_name

MIN (expr)

MAX (expr)

Returns the minimum or maximum value of expr. MIN () and MAX () can have a string argument; in this case, they return the minimum or maximum string value.

Mysql > select student_name, MIN (test_score), MAX (test_score) from student GROUP BY student_name

SUM (expr)

Returns the sum of expr. Note that if the returned collection has no rows, it returns NULL!

STD (expr)

STDDEV (expr)

Returns the expr standard deviation (deviation). This is an extension to ANSI SQL. The form STDDEV () of this function is to provide compatibility with Oracle.

BIT_OR (expr)

Returns the bit OR of all bits in the expr. The calculation is carried out with 64-bit (BIGINT) precision.

BIT_AND (expr)

Returns the sum of all the bits in the expr. The calculation is carried out with 64-bit (BIGINT) precision.

MySQL extends the use of GROUP BY. You can use columns or calculations in the current SELECT expression in the GROUP BY section, which represents any possible values for this group. You can use it for better performance and avoid sorting and grouping unnecessary items. For example, you do not need to aggregate on customer.name in the following query: mysql > select order.custid,customer.name,max (payments) from order,customer where order.custid = customer.custid GROUP BY order.custid

In ANSI SQL, you will have to add customer.name to the GROUP BY clause. In MySQL, names are redundant.

Do not use this feature if the columns you omitted from the GROUP BY section are not unique in the group.

In some cases, you can use MIN () and MAX () to get a specific column value, even if it is not unique. The following example shows the column value from the row containing the lowest value in the sort column: substr (MIN (concat (6-length (6-length (sort)), column), 7 column length))

Note that if you are using MySQL 3.22 (or earlier) or if you are trying to comply with ANSI SQL, you cannot use expressions in GROUP BY or ORDER BY clauses. You can resolve this limitation by using an alias for the expression: mysql > SELECT id,FLOOR (value/100) AS val FROM tbl_name GROUP BY id,val ORDER BY val

In MySQL3.23, you can do this: mysql > SELECT id,FLOOR (value/100) FROM tbl_name ORDER BY RAND ()

This is the answer to the questions about which functions are commonly used in mysql. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

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.