Mysql scalar functions

Several common scalar functions, the simplest of which is to use scalar functions through the class selectabs (-123. Abs: This function returns an absolute number expression. For example, abs (-123); adddate: This function adds the timestamp or timestamp expression (parameter 1) at a time interval (parameter 2). date_add is also used in this function. For example, adddate (2009-01-

The simplest of several common scalar functions is to use scalar functions through the select abs (-123) class. Abs: This function returns an absolute number expression. For example, abs (-123); adddate: This function adds the timestamp or timestamp expression (parameter 1) at a time interval (parameter 2). date_add is also used in this function. For example, adddate ('2017-01-

Several common scalar functions, the simplest of which is to use a scalar function by using a method similar to select abs (-123.

Abs: This function returns the absolute value of a numeric expression. Such as abs (-123 );

Adddate: This function adds the timestamp or timestamp expression (parameter 1) to a time interval (parameter 2). date_add is also used in the same function as this function. For example, adddate ('1970-01-01 ', 4); the result is 2009-01-05. Adddate (, interval 5 month); the result is 2009-06-01. Adddate (timestamp ('1970-01-01 '), interval 5 month); Result: 2009 00:00:00. Adddate ('2014-01-01 12:00:00 ', interval 5 day); the result is 2009 12:00:00.

Addtime: adds up two time expressions. For example, addtime ('2014: 00: 00', '2014: 02: 04 '); the result is 100: 02: 04.

Ascii: returns the ASCII value of the first character of a string expression.

Bin: This function converts the value of a parameter to a binary value.

Bit_count: the number of digits that indicate the value of the parameter. For example, bit_count (3) returns 2.

Bit_length: The bit length of the string value returned by this function. For example, bit_length (bin (2); the result is 16.

Ceiling: returns the maximum integer greater than or equal to the value of the parameter. For example, ceiling (11.11);-> 12. Ceicling (-11.11);->-11

Char: This function returns the string character of the numeric parameter. This function also features the chr function. For example, char (82) + char (105) + char (99) + char (107);-> 'rich'

Character_length: This function returns the length of a string expression, which has the same function as char_length.

Charset: This function returns the Character Set Name of the string parameter.

Coalesce: This function accepts multiple parameters and returns the value of the first non-null value. For example, coalesce (null, null, 'ljp ');-> 'ljp ';

Concat: This function combines the values of two strings.

Conv: This function converts a value (parameter 1) with a base value of parameter 2 to another value with a base value of parameter 3. For example, conv (1110,10, 16) means to convert the number 1110 in decimal format to the hexadecimal value 456. The following two parameters must be in the range of 2 ~ Otherwise, the result is null. In addition, the value of parameter 1 must match the base number of parameter 2. Otherwise, the result is 0.

Convert: the data type of this function conversion parameter 1 is the type specified by parameter 2. Parameter 2 must be a data type, including binary, char, date, datetime, time, signed, signed integer, unsigned, unsigned integer, and varchar. For example, convert ('12. 56', unsigned integer);-> 13.

Database: the name of the current database.

Date: This function converts a parameter to a date value. For example, date ('2014-01-01 12:00:00 ');-> '2014-01-01 '.

Datediff: This function calculates the number of days between two date or timestamp expressions. The first parameter minus the second parameter.

Date_sub: This function deducts a time interval (parameter 2) from a date or timestamp expression (parameter 1). The subdate function is also used in the same function as the function.

Day: returns the number of days of a month from a date or timestamp expression. The result is always between 1 and 1 ~ The integer between 31 and dayofmonth.

Dayname: This function returns the name of a day in a week from a date or timestamp expression.

Dayofweek: This function returns the number of days of a week in a date or timestamp expression. The result is always between 1 and 1 ~ An integer between 7.

Dayofyear: This function returns the serial number of a day of the year in a date or timestamp expression.

Default: This function returns the default value of a column specified by the parameter.

Floor: return the smallest integer less than or equal to the value of the parameter, which is opposite to ceiling.

Exp: This function returns the result of x screens of e.

Format: This function configures the format of a value as nn, nnn, nnn. nnn. The second parameter indicates the number of digits after the decimal point.

Greatest: This function returns the maximum value among a series of parameters, which is opposite to least.

Hex: If the parameter is a number, the hexadecimal representation of the number is returned. If the parameter is a string, the ASCII code corresponding to each character is returned.

If: if the value of the first parameter is true, the function returns the value of the second parameter; otherwise, the value of the third parameter is returned. If (1> 2), "ljp", "st"); returns st.

Ifnull: if the value of parameter 1 is null, the value of parameter 2 is returned. Otherwise, the value of parameter 1 is returned. For example, ifnull (null, "ljp"); returns "ljp ".

Insert: the value of parameter 4 is placed in parameter 1 at the position specified by parameter 2, parameter 3 indicates that the next multiple characters starting from the position specified by parameter 2 in parameter 1 will be replaced by parameter 4. For example, insert ('abcdefgh', 4,3, 'zzz');-> 'abczzzgh '. Insert ('abcdefgh', 4, 2, 'zzz');-> 'abczzzfgh '. Insert ('abcdefgh', 4,0, 'zzz');-> 'abczzzdefgh '.

Insert ('abcdefgh', 4,-1, 'zzz');-> 'abczzz '. Insert ('abcdefgh', 1, 5, 'zzz');-> 'zzzfgh '.

Instr: This function returns the starting position of parameter 2 in parameter 1. If it is found, 0 is returned. For example, instr ('20140901', 4);-> 4.

Interval: the first parameter of the function specifies the value to be inserted. The following parameters form an ascending sequence to see where the first parameter is inserted. This function returns this position. For example, interval (5, 0, 1, 2, 3, 6, 7);-> 4 indicates that 5 is placed after the fourth position (where the value is 3.

Isnull: If the parameter value is null, 1 is returned; otherwise, 0 is returned.

Last_day: return the last day of the month in the expression of the date or timestamp specified by the parameter, for example, last_day ('2017-01-09 ');-> '2017-01-31 '.

Lcase: This function converts all uppercase letters of the parameter value to lowercase letters, which are synonymous with lower and opposite to ucase.

Left: This function returns the left part of string value parameter 1. The length of this part is specified by the second parameter. For example, left ("hello world", 3);-> 'hel '.

Length: the length of a string. For example, length (null);-> null.

Ln: This function returns the natural logarithm of the parameter, which is synonymous with log.

Localtime: This function returns the system date and time. localtimestamp is synonymous with only.

Locate: This function returns the starting position of parameter 1 within parameter 2. If parameter 1 does not appear in parameter 2, return 0. Parameter 3 indicates the start position of the search. Pay attention to the differences between instr functions. The instr function is the starting position of parameter 2 in parameter 1.

Log2: returns the base 2 logarithm of the parameter. For example, log2 (64);-> 6

Log10: returns the base-10 logarithm of the parameter.

Lpad: Fill the value of parameter 3 on the left side of parameter 1 until the total length of the value is equal to the length of parameter 2. If the maximum length is smaller than the length of parameter 1, parameter 1 is truncated on the left. For example, lpad ('data', 6, 'base');-> 'badata '. Lpad ('data', 2, 'base');-> 'da '.

Ltrim: This function deletes all white spaces that appear before the parameter. rtrim indicates deleting the white space at the end of the parameter.

Makedate: parameter 2 indicates the number of days. They are added to parameter 1. For example, makedate (2009,10);-> '2017-01-10 '.

Maketime: the three parameters are hour, minute, and second, respectively. The minute and second must be between 0 and ~ Between 59; otherwise, null is returned.

Mid: This function extracts some string values from parameter 1. Parameter 2 indicates the start position and parameter 3 indicates the number of characters. For example, mid ('database', 5);-> 'base '. Mid ('database', 5, 2);-> 'ba '. Mid ('database',-6);-> 'tabase '. Mid ('database',-6, 3);-> 'tab '.

Minute: This function returns the number of minutes from a time or timestamp expression.

Mod: This function returns the remainder of the two parameters. Such as mod (15.4, 2.2);->

Month: returns the number of months in the timestamp. The value ranges from 1 ~ Between 12.

Monthname: returns the name of a month from a date or timestamp expression. For example, monthname ('2017-01-01 ');-> 'fill l '.

Now: returns the system date and time.

Nullif: if the value of parameter 1 is equal to the value of parameter 2, the function returns null. Otherwise, the value of parameter 1 is returned.

Oct: This function returns the decimal number of octal parameter 1. Such as oct (8);-> 10

Ord: This function returns the character set location of the first character of the string expression specified by the parameter. For example, ord ('da');-> 68.

Period_add: This function adds the number of months to a specified date. The date format must be YYYYMM or YYMM. The result format is YYYYMM. For example, period_add ('20140901', 3);-> 200901.

Period_diff: This function returns the number of months in two days. For example, period_diff ('20140901', '20160901');-> 7

PI: returns the circumference rate.

Power: returns the power of parameter 1 twice.

Quarter: This function returns a quarter value from a date or timestamp expression. The result value is always between 1 and 1 ~ Between 4. However, it is different from the quarter in our daily life ~ To ~ ~ To ~ December is 4.

Rand: This function returns a random number between 0.0 and 1.0. The start point of the next random value. The function is repeatedly called using the same parameter values, and the results are always the same. For example, cast (rand () * 1000 as unsigned integer );

Repeat: repeat the number of times specified by parameter 2 with the value of parameter 1.

Replace: replace the value specified by parameter 2 in the string specified by parameter 1 with the value specified by parameter 3. Such as replace ('data', 'A', 'E');-> 'dete '.

Reverse: This function reverses the character order in a string value.

Right: This function returns the right part of parameter 1. The length of this part is specified by the second parameter.

Round: This function rounds the precision of a number to the specified number of digits. For example, round (123.4,-1);-> 100; round (183.4,-2);-> 200.

Rpad: Fill the value of parameter 3 to the right of parameter 1. The total length of the value is equal to the length specified by parameter 2, which is opposite to that of lpad. Rpad ('data', 2, 'base')-> 'da '.

Second: This function returns the number of seconds in a time or timestamp expression.

Sec_to_time: This function converts seconds to time. For example, sec_to_time (24*60*60)-1);-> 23:59:59

Sign: This function returns a numeric character. Sign (50);-> 1; sign (0)-> 0; sign (-50)->-1;

Space: This function generates a space row. The number of spaces is the value specified by the parameter.

Sqrt: returns the square root of a parameter.

Strcmp: This function compares the values of two string expressions. If the parameter values are equal, the result is 0. If the value of parameter 1 is small,-1 is returned; otherwise, the result is 1.

Substring: This function deducts some string values from parameter 1. Parameter 2 indicates the starting point and parameter 3 indicates the subpayment. For example, substring ('database', 5, 2);-> 'ba '.

Substring_index: This function finds parameter 3 in parameter 1, which is represented by parameter 2. If the parameter 3 is positive, it indicates searching from the left side, and returns the appearance from the left side. If parameter 3 is negative, search from the right. For example, substring_index ('database', 'A', 3);-> 'datab'; substring_index ('database', 'A',-3);-> 'tabase '; substring_index ('database', 'data', 1);-> ''; substring_index ('database', 'data',-1);-> 'base'

Subtime: This function subtract two time expressions and returns a new time. timediff is similar to this function.

Time: This function returns the time part of a time or timestamp expression. For example, time ('12: 13');-> 12:13:00

Time_to_sec: This function converts time to seconds. For example, time_to_sec ('00: 16: 40');-> 1000

Timestampdiff: This function is used to calculate the time between two date or timestamp expressions. Parameter 1 indicates the time interval units, such as day, month, year, quarter, week, hour, minute, second, frac_second. Parameters 2 and 3 form two expressions. For example, timestampdiff (day, '2017-01-01 ', '2017-01-04');-> 4.

Timestamp: This function converts parameter 1 into a timestamp. If parameter 2 is specified, it should be a time expression and will be added to the value of parameter 1.

Timestampadd: This function adds the time interval to a date or timestamp expression. Parameter 1 indicates the unit of time interval, parameter 2 indicates the number of days or months, and parameter 3 indicates the expression added to the time interval. For example, timestampadd (DAY, 2, '2017-01-01 ');-> '2017-01-03'; timestampadd (MONTH, 2, '2017-01-01 '); -> ".

Trim: This function deletes all spaces at the beginning and end of the string value represented by parameter 1.

Truncate: This function truncates the number to the specified decimal place. Pay attention to the difference between the number and the round. The round is rounded down and the truncate is truncated. For example, truncate (123.45,-1);-> 120. Truncate (123.375, 1);-> 123.3.

Unhex: the parameter in hexadecimal format is converted to the corresponding character in contrast to hex. For example, unhex (hex ('hello') '-> hello

Ucase: similar to upper, all lowercase letters in the parameter are converted to uppercase letters.

Week: This function returns the number of weeks from a date or timestamp expression. Similar functions include weekofyear. Result range: 1 ~ An integer between 53. For example, week ('2017-06-07 ');-> 23

Weekday: This function returns the number of days in a week. The result is between 0 and ~ The number between six. 0 indicates Monday. Year: This function returns the number of years from a date or timestamp expression.

Yearweek: If parameter 1 is specified, this function returns the year and week number in the format of YYYYWW from a timestamp or date expression. The number of weeks ranges from 01 to 01 ~ Between 52. For example, yearweek ('2017-07-06 ');-> 2009