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

Mysql stored procedure basic function [@ more@] one. String class

CHARSET (str) / / returns the string character set

CONCAT (string2 [,...]) / / connection string

INSTR (string, substring) / / returns the location where substring first appeared in string, and 0 is not returned.

LCASE (string2) / / convert to lowercase

LEFT (string2, length) / / fetches length characters from the left in string2

LENGTH (string) / / string length

LOAD_FILE (file_name) / / read from a file

LOCATE (substring, string [, start_position]) is the same as INSTR, but the start position can be specified

LPAD (string2, length, pad) / / add pad to the beginning of string repeatedly until the string length is length

LTRIM (string2) / / remove front-end spaces

REPEAT (string2, count) / / repeat count times

REPLACE (str, search_str, replace_str) / / replace search_str with replace_str in str

RPAD (string2, length, pad) / / add pad after str until the length is length

RTRIM (string2) / / remove backend whitespace

STRCMP (string1, string2) / / compare the size of two strings character by character

SUBSTRING (str, position [, length]) / / start with the position of str and take length characters

Note: when dealing with strings in mysql, the default first character subscript is 1, that is, the parameter position must be greater than or equal to 1.

Mysql > select substring ('abcd',0,2)

+-- +

| | substring ('abcd',0,2) | |

+-- +

| | |

+-- +

1 row in set (0.00 sec) mysql > select substring ('abcd',1,2)

+-- +

| | substring ('abcd',1,2) | |

+-- +

| | ab |

+-- +

1 row in set (0.02 sec)

TRIM ([[BOTH | LEADING | TRAILING] [padding] FROM] string2) / / removes the specified characters at the specified location

UCASE (string2) / / convert to uppercase

RIGHT (string2,length) / / fetch the last length character of string2

SPACE (count) / / generate count two spaces. Mathematics class

ABS (number2) / / absolute value

BIN (decimal_number) / / Decimal to binary

CEILING (number2) / / rounding up

CONV (number2,from_base,to_base) / / binary conversion

FLOOR (number2) / / rounding down

FORMAT (number,decimal_places) / / keep decimal places

HEX (DecimalNumber) / / convert to hexadecimal

Note: if a string can be passed in HEX (), its ASC- 11 code is returned, for example, HEX ('DEF') returns 4142143

You can also pass in a decimal integer and return its hexadecimal code, for example, HEX (25) returns 19

LEAST (number, number2 [,..]) / / find the minimum

MOD (numerator, denominator) / / residual

POWER (number, power) / / find the index

RAND ([seed]) / / Random number

ROUND (number [, decimals]) / / rounded, decimals is decimal]

Note: not all return types are integers, such as:

(1) the default is changed to the shaping value.

Mysql > select round (1.23)

+-+

| | round (1.23) |

+-+

| | 1 |

+-+

1 row in set (0.00 sec)

Mysql > select round (1.56)

+-+

| | round (1.56) |

+-+

| | 2 |

+-+

1 row in set (0.00 sec)

(2) you can set the number of decimal places to return floating-point data

Mysql > select round (1.567)

+-+

| | round (1.567) | |

+-+

| | 1.57 |

+-+

1 row in set (0.00 sec)

SIGN (number2) / / return symbol, plus or minus or 0

SQRT (number2) / / squared

three。 Date and time class

ADDTIME (date2, time_interval) / / add time_interval to date2

CONVERT_TZ (datetime2, fromTZ, toTZ) / / convert time zone

CURRENT_DATE () / / current date

CURRENT_TIME () / / current time

CURRENT_TIMESTAMP () / / current timestamp

DATE (datetime) / / returns the date part of the datetime

DATE_ADD (date2, INTERVAL d_value d_type) / / add a date or time to the date2

DATE_FORMAT (datetime, FormatCodes) / / display datetime in formatcodes format

DATE_SUB (date2, INTERVAL d_value d_type) / / subtract a time from date2

DATEDIFF (date1, date2) / / two date differences

DAY (date) / / the day of the return date

DAYNAME (date) / / English week

DAYOFWEEK (date) / / week (1-7), 1 is Sunday

DAYOFYEAR (date) / / Day of the year

EXTRACT (interval_name FROM date) / / extract the specified part of the date from date

MAKEDATE (year, day) / / give the year and the day of the year, and generate a date string

MAKETIME (hour, minute, second) / / generate time string

MONTHNAME (date) / / English month name

NOW () / / current time

SEC_TO_TIME (seconds) / / number of seconds converted to time

STR_TO_DATE (string, format) / / string conversion time, displayed in format format

TIMEDIFF (datetime1, datetime2) / / two time differences

TIME_TO_SEC (time) / / number of seconds in time]

WEEK (date_time [, start_of_week]) / / weeks old

YEAR (datetime) / / year

Day ordinal of DAYOFMONTH (datetime) / / month

HOUR (datetime) / / hour

The last date of the month of LAST_DAY (date) / / date

MICROSECOND (datetime) / / microsecond

MONTH (datetime) / month

MINUTE (datetime) / / min

Attached: types that can be used in INTERVAL

DAY, DAY_HOUR, DAY_MINUTE, DAY_SECOND, HOUR, HOUR_MINUTE, HOUR_SECOND, MINUTE, MINUTE_SECOND,MONTH, SECOND, YEAR

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.