1. SQL execution sequence

Write the order

SELECT DISTINCT
    <select list>
FROM
    <left_table> <join_type>
JOIN
    <right_table> ON <join_condition>
WHERE
    <where_condition>
GROUP BY
    <group_by_list>
HAVING
    <having_condition>
ORDER BY
    <order_by_condition>
LIMIT
    <limit_params>
Copy the code

Execution order

FROM 
    <left_table>
ON 
    <join_condition>
<join_type> JOIN    <right_table>
WHERE 
    <where_condition>
GROUP BY 
    <group_by_list>
HAVING 
    <having_condition>
SELECT 
    DISTINCT <select list>
ORDER BY 
        <order_by_condition>
LIMIT 
    <limit_params>
Copy the code

2. Use regular expressions

A Regular Expression is a single string used to describe or match a series of strings that conform to a syntactic rule.

symbol meaning
^ Matches at the beginning of the string
$ Matches at the end of the string
. Matches any single character, including newline characters
[…]. Matches any character in parentheses
[^…]. Cannot match any character in parentheses
a* Matches zero or more A’s (including empty strings)
a+ Matches one or more A’s (excluding empty strings)
a? Matches zero or one A
a1|a2 Matches A1 or A2
a(m) Match m as
a(m,) Match at least m AS
a(m,n) Match m as to N as
a(,n) Matches 0 to n as
(…). Combine schema elements into a single element 
select * from emp where name regexp '^T';
select * from emp where name regexp '2 $';
select * from emp where name regexp '[uvw]';
Copy the code

3. MySQL common functions

Digital function

The name of the function As with
ABS Beg absolute value
SQRT Take the quadratic root
MOD Strives for the remainder
CEIL The function has the same function as CEILING. It returns the minimum integer that is not less than the CEILING
FLOOR Round down, and the return value is converted to a BIGINT
RAND Generate a random number between 0 and 1, passing in the integer argument is, used to generate a repeating sequence
ROUND Round the parameters passed
SIGN Returns the symbol of the argument
POW POWER and two functions of the same function, are passed parameters to the POWER of the result value
SIN Ask sine value
ASIN Take the arcsine, which is the inverse of sine
COS Strives for the cosine
ACOS I’m going to take the inverse cosine of the function cosine
TAN Ask tangent value
ATAN Take the inverse tangent of the function TAN
COT Ask cotangent value

String function

The name of the function As with
LENGTH Evaluates the string length function that returns the length of the string in bytes
CONCAT The merge string function returns the string produced by the connection argument, which can be one or more
INSERT Substitution string function
LOWER Converts letters in a string to lowercase
UPPER Converts letters in a string to uppercase
LEFT Intercepts a string from the left side of the string, returning several characters to the left of the string
RIGHT Intercepts a string from the right hand character, returning several characters to the right of the string
TRIM Removes Spaces on the left and right sides of the string
REPLACE String replacement function that returns a new string after replacement
SUBSTRING Intercepts a string, returning a character substitution of the specified length starting at the specified position
REVERSE The string inversion function returns a string in reverse order from the original string

Date function

The name of the function As with
CURDATE The CURRENT_DATE function returns the current system date
CURTIME The CURRENT_TIME function returns the current system time
NOW The SYSDATE function returns the date and time of the current system
MONTH Gets the month in the specified date
MONTHNAME Gets the English name of the month in the specified date
DAYNAME Gets the English name of the day of the week corresponding to the specified day period
DAYOFWEEK Gets the value of the week’s index position for the specified date
WEEK Gets the week of the year for the specified date and whether the return value ranges from 0 to 52 or 1 to 53
DAYOFYEAR Gets the day of the year for which the specified date is returned. The value ranges from 1 to 366
DAYOFMONTH Gets the day of a month. The value ranges from 1 to 31
YEAR Gets the year. The returned value ranges from 1970 to 2069
TIME_TO_SEC Convert the time parameter to seconds
SEC_TO_TIME Converts seconds to time, which is the inverse of TIME_TO_SEC
DATE_ADD The ADDDATE function adds a specified interval to a date
DATE_SUB SUBDATE subtracts the specified interval from the date
ADDTIME Time addition adds a specified time to the original time
SUBTIME Time subtraction, subtracting the specified time from the original time
DATEDIFF Gets the interval between two dates, returning the value of argument 1 minus argument 2
DATE_FORMAT Formats the specified date, returning the value of the specified format based on the argument
WEEKDAY Gets the index of the days of the week for the specified date

Aggregation function

The name of the function As with
MAX Query the maximum value of a specified column
MIN Query the minimum value of the specified column
COUNT Count the number of rows in the query result
SUM Summation, returns the sum of the specified columns
AVG Returns the average value of the specified column data