Mysql stored procedure basic function (transfer)
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
