1. In the use of MySQL, flexible use of functions can improve development efficiency, convenient to solve some difficult problems. The common MySQL functions in this article include string functions, numeric functions, date and time functions, and aggregate functions.

CHARSET(str) // Returns a string character setCONCAT (string2 [,...]. )// Connection string
INSTR (string ,substring ) // Returns the position at which subString first appears in the string
LCASE (string ) // The string is converted to lowercase
LEFT (string ,length ) // Take the length character from the left of the string
LENGTH (string ) // String length
LOAD_FILE (file_name ) // Read from a file
LOCATE (substring , string[,start_position]) same as INSTR, but can specify the start position LPAD (string ,length ,pad ) // Repeat with pad at the beginning of the string until the string length is length
LTRIM (string2) // Remove the space at the beginning of the string
REPEAT (string ,count ) // Repeat count times
REPLACE (str ,search_str ,replace_str ) // Replace search_str with replace_str in STR
RPAD (string ,length ,pad) // Follow the string with pad until the length is length
RTRIM (string) // Remove whitespace at the end of the string
STRCMP (string1 ,string2 ) // Compare two string sizes character by character,
SUBSTRING (str , position [,length ]) // Start with the string position and take the length character. SUBSTR is the same as SUBSTRIN.Note: When processing strings in MySQL, the default first character subscript is1That is, the parameter position must be greater than or equal to1, position supports negative numbers. If it is negative, it is truncated from behind. Mysql > select substring (' abcd ',0.2); + -- -- -- -- + | substring (' abcd '.0.2) | + -- -- -- -- + | | + -- -- -- -- +1 row in set (0.00SEC) 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 character at the specified position
UCASE (string2 ) // Convert to uppercase
RIGHT(string2,length) // take the last character of string2
SPACE(count) // Generate count Spaces
Copy the code

2. MySQL math class

ABS (number2 ) / / the absolute value
BIN (decimal_number ) // Convert from decimal to binary
CEILING (number2 ) // round up
CONV(number2,from_base,to_base) // Base conversion
FLOOR (number2 ) // round down
FORMAT (number,decimal_places ) // Keep decimal places
HEX (DecimalNumber ) // Convert to hexadecimalNote: if a string can be passed in HEX(), return its ASC-11The code, such as HEX(' DEF ') returns4142143You can also pass in a decimal integer and return its hexadecimal code, such as HEX(25) returns19LEAST (number , number2 [,..] )// Find the minimum value
MOD (numerator ,denominator ) / / for more
POWER (number ,power ) / / index
RAND([seed]) / / random number
ROUND (number [,decimals ]) // Decimals indicates the number of decimal places. Negative numbers are supported.Note: Return types are not all integers, for example:1Mysql > 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)
(2Mysql > select round();1.567.2); + - - - - - - + | round (1.567.2) + | | + -- -- -- -1.57| + - - - - - - +1 row in set (0.00 sec)
SIGN (number2 ) // The function returns the symbol of the argument
Copy the code

MySQL date_time class

ADDTIME (date2 ,time_interval ) // add time_interval to date2
CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) // Change the time zone
CURRENT_DATE ( ) // The current date
CURRENT_TIME ( ) // The current time
CURRENT_TIMESTAMP ( ) // The current timestamp
DATE (datetime ) // Returns the date portion of datetime
DATE_ADD (date2 , INTERVAL d_value d_type ) // Add the date or time to date2
DATE_FORMAT (datetime ,FormatCodes ) // Display datetime using formatCodes
DATE_SUB (date2 , INTERVAL d_value d_type ) // Subtract a time from date2
DATEDIFF (date1 ,date2 ) // Two dates are different
DAY (date ) // Return the day of the date
DAYNAME (date ) // English week
DAYOFWEEK (date ) // Sunday (1-7)
DAYOFYEAR (date ) // The day of the year
EXTRACT (interval_name FROM date ) // Extracts the specified part of the date from date
MAKEDATE (year ,day ) // Give the year and the day of the year to generate the date string
MAKETIME (hour ,minute ,second ) // Generate a time string
MONTHNAME (date ) // English month name
NOW ( ) // The current time
SEC_TO_TIME (seconds ) // Seconds convert to time
STR_TO_DATE (string ,format ) // String conversion time, displayed in format
TIMEDIFF (datetime1 ,datetime2 ) // Two time difference
TIME_TO_SEC (time ) // Time in seconds]
WEEK (date_time [,start_of_week ]) / / how many weeks
YEAR (datetime ) / / year
DAYOFMONTH(datetime) // The day of the month
HOUR(datetime) / / hour
LAST_DAY(date) //date Specifies the last date of the month
MICROSECOND(datetime) / / ms
MONTH(datetime) / / month
MINUTE(datetime) // returns a symbol, positive or negative, or 0
SQRT(number2) / / open square
Copy the code

MySQL aggregate function

AVG([DISTINCT] EXPR) Returns the average value. The DISTINCT option can be used to return an average of the different values of EXPR. COUNT(expR) Indicates statistics. If no matching row is found, COUNT() returns0. MIN([DISTINCT] expR), MAX([DISTINCT] expR) Returns the minimum and maximum value of EXPR. MIN() and MAX() can be a string argument; In these cases, they return a minimum or maximum string value. SUM([DISTINCT] expr) Returns the total number of EXPR. If there are no rows in the return set, SUM() returnsNULL 。
Copy the code

The above content hopes to help you, more free PHP factory PDF, PHP advanced architecture video materials, PHP wonderful good article can be wechat search concerns: PHP open source community

2021 Jinsanyin four big factory interview real questions collection, must see!

Four years of PHP technical articles collation collection – PHP framework

A collection of four years’ worth of PHP technical articles – Microservices Architecture

Distributed Architecture is a four-year collection of PHP technical articles

Four years of PHP technical essays – High Concurrency scenarios

Four years of elite PHP technical article collation collection – database