I. string type
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 is returned.
Lcase (string2) // converts it to lowercase
Left (string2, length) // take the length from the left of string2
Length (string) // String Length
Load_file (file_name) // read content from the file
Locate (substring, string [, start_position]) is the same as instr, but the start position can be specified.
Lpad (string2, length, pad) // repeat pad to start with string until the string length is length
Ltrim (string2) // remove leading 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) // use pad after STR until the length is length.
Rtrim (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 default subscript of the first character is 1, that is, the parameter position must be greater than or equal to 1.
Mysql> select substring ('abcd );
+ -------- +
| Substring ('abcd',) |
+ -------- +
|
+ -------- +
1 row in SET (0.00 Sec) mysql> select substring ('abc );
+ -------- +
| Substring ('abcd', 1, 2) |
+ -------- +
| AB |
+ -------- +
1 row in SET (0.02 Sec)
Trim ([[Both | leading | trailing] [Padding] From] string2) // remove the specified character from the specified position
Ucase (string2) // converts to uppercase
Right (string2, length) // gets the last length character of string2
Space (count) // generate count Spaces
Ii. Mathematics
ABS (number2) // absolute value
Bin (decimal_number) // convert decimal to binary
Ceiling (number2) // rounded up
Conv (number2, from_base, to_base) // hexadecimal conversion
Floor (number2) // round down
Format (number, decimal_places) // number of reserved decimal places
Hex (decimalnumber) // convert to hexadecimal
Note: Hex () can input a string, returns its ASC-11 code, such as hex ('def ') returns 4142143
You can also input a decimal integer to return its hexadecimal encoding. For example, Hex (25) returns 19.
Least (number, number2 [,...]) // calculates the minimum value.
MoD (numerator, denominator) // evaluate the remainder
Power (number, power) // 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 integer.
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 to return floating point data.
Mysql> select round (1.567, 2 );
+ ------ +
| Round (1.567, 2) |
+ ------ +
| 1, 1.57 |
+ ------ +
1 row in SET (0.00 Sec)
Sign (number2) // return sign, positive and negative or 0
SQRT (number2) // Square
Iii. Date and Time
Addtime (date2, time_interval) // Add time_interval to date2
Convert_tz (datetime2, fromtz, totz) // convert the time zone
Current_date () // current date
Current_time () // current time
Current_timestamp () // current Timestamp
Date (datetime) // return the date part of datetime
Date_add (date2, interval d_value d_type) // Add a date or time in 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) // returns the day of the date
Dayname (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 part of the date from date
Makedate (year, day) // specifies the day of the year and year to generate a date string.
Maketime (hour, minute, second) // generate a time string
Monthname (date) // name of the English month
Now () // current time
Sec_to_time (seconds) // converts seconds to time
Str_to_date (string, format) // convert string to time, which is displayed in format
Timediff (datetime1, datetime2) // two time difference
Time_to_sec (time) // time to seconds]
Week (date_time [, start_of_week]) // week
Year (datetime) // year
Dayofmonth (datetime) // The 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) // minute
Appendix: available types in interval
Day, day_hour, day_minute, day_second, hour, hour_minute, hour_second, minute, minute_second, month, second, year
