Basic functions of MySQL Stored Procedure

Basic functions of MySQL Stored Procedure

(1 ). string CHARSET (str) // returns the string Character Set CONCAT (string2 [,...]) // connection string INSTR (string, substring) // returns the position of the first occurrence of the substring in the string. If no position exists, 0 LCASE (string2) is returned. // converts the value to lower-case LEFT (string2, length) // take the length (string) character from the left of string2 // string LENGTH LOAD_FILE (file_name) // read the content from the file LOCATE (substring, string [, start_position]) same as INSTR, but you can specify the start position LPAD (string2, length, pad) // repeat pad at the start of string until the string length is lengthLTRIM (string2) // remove front-end space REPEAT (string2, count) // REPEAT count REPLACE (str, search_str, replace_str) // REPLACE search_strRPAD (string2, length, pad) with replace_str in str) // use pad after str until the length is lengthRTRIM (string2) // remove backend spaces STRCMP (string1, string2) // compare the size of two strings by character, SUBSTRING (str, position [, length]) // starts from the position of str and takes length characters. Note: when processing strings in mysql, the first character subscript is 1 by default, that is, the position parameter must be greater than or equal to 1 mysql> select substring ('abcd',); + ------------------- + | substring ('abcd) | + ----------------------- + | + --------------------- + 1 row in set (0.00 sec) mysql> select substring ('abcd',); + --------------------- + | substring ('abcd ', 1, 2) | + --------------------- + | AB | + --------------------- + 1 row in set (0.02 sec) TRIM ([[BOTH | LEADING | TRAILING] [padding] FROM] string2) // remove the specified character UCASE (string2) at the specified position // convert it to the upper RIGHT (string2, length) // take the last length character SPACE (count) of string2) // generate count spaces (2 ). mathematical ABS (number2) // absolute value BIN (decimal_number) // decimal to binary CEILING (number2) // rounded up CONV (number2, from_base, to_base) // convert FLOOR (number2) in hexadecimal notation // round-down the entire FORMAT (number, decimal_places) // retain the number of decimal places HEX (DecimalNumber) // convert to hexadecimal notation Note: HEX () if a string can be passed in, its ASC-11 code is returned. For example, HEX ('def ') returns 4142143 or a decimal integer, returning its hexadecimal encoding, such as HEX (25) returns 19 LEAST (number, number2 [,...]). // evaluate the minimum MOD (numerator, denominator) // evaluate the remainder POWER (number, power) // evaluate the exponent RAND ([seed]) // random number ROUND (number [, decimals]) // rounding, decimals is the number of decimal places] Note: The return type is not an integer. For example: (1) the default value is "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) the number of decimal places can be set, return floating point data mysql> select round (1.567, 2); + ---------------- + | round (1.567, 2) | + ---------------- + | 1.57 | + ---------------- + 1 row in set (0.00 sec) SIGN (number2) // (3 ). date and Time class ADDTIME (date2, time_interval) // Add time_interval to date2CONVERT_TZ (datetime2, fromTZ, toTZ) // convert the time zone CURRENT_DATE () // current date CURRENT_TIME () // current time CURRENT_TIMESTAMP () // current timestamp DATE (datetime) // returns the datetime part DATE_ADD (date2, INTERVAL d_value d_type) // Add date or time DATE_FORMAT (datetime, FormatCodes) to date2 // display datetimeDATE_SUB (date2, INTERVAL d_value d_type) in formatcodes format) // subtract a time DATEDIFF (date1, date2) from date2 // two date difference days (date) // return the day dayname (date) of the date) // english week DAYOFWEEK (date) // Week (1-7), 1 is Sunday DAYOFYEAR (date) // The day of the year EXTRACT (interval_name FROM date) // extract the specified MAKEDATE (year, day) of the date from date // give the day of the year and year, and generate the date string MAKETIME (hour, minute, second) // generate the time string MONTHNAME (date) // english month name NOW () // current time SEC_TO_TIME (seconds) // converts the second to the time STR_TO_DATE (string, format) // string converted to time, display TIMEDIFF (datetime1, datetime2) in format // two time difference TIME_TO_SEC (time) // time to seconds] WEEK (date_time [, start_of_week]) // week of the YEAR (datetime) // year dayofmonth (datetime) // day of the month HOUR (datetime) // HOUR LAST_DAY (date) // The last date of the MONTH of date MICROSECOND (datetime) // microsecond month (datetime) // month minute (datetime) // returns the symbol, plus or minus or 0 SQRT (number2) // Square