1.IN

Test data pack

2.BETWEEN AND

3. The string with “LIKE” matches the query

% : matches one or more characters. The value can be 0. _: matches one character.

4. Use the DISTINCT keyword to extract duplicate rows in the result

5.ORDER by + field name

6. UseGROUP BY + field nameIt means grouping by what field, like this

Only one entry will be displayed randomly in each group. Alternatively, we can use **GROUP_CONCAT(fields)** to display all values of the fields specified after grouping

.

7. Run the LIMIT command to LIMIT the number of query results.

Select xx from table_name limit 2; // Select the first two items from all query records. select xx from table_name limit n,m; // Query three records from n.

8. Use aggregate function to query — count function

For functions other than *, returns the number of non-null values in the selected collection. Returns the number of NULL values in the selected collection for *.

9. Join query.

Join refers to joining together records from different tables. Inner join query: An inner join represents an equal join, that is, a field in the joined table is the same as in each table.

OUTER JOIN query: The OUTER JOIN is to JOIN two tables using OUTER,JOIN keywords. The result set generated by the OUTER JOIN not only contains the row data that meets the JOIN conditions, but also includes all the rows in the left table, right table or two JOIN tables.

SELECT the field name from table_name table 1 LEFT | RIGHT ON the JOIN table 2 in table 1. Field name = table 2. mysql> select owner,information.passward,information.create_time from pet left join information on information.user=pet.name; // The left join feature is how many rows are in the left table, Then the final matching entries from the article how much + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | owner | passward | create_time | +-------+----------+---------------------+ | NULL | NULL | NULL | | root | NULL | NULL | | NULL | NULL | NULL | | lzh | 666666 | 2017-03-30 17:36:50 | | NULL | NULL | NULL | | cyb | 999999 | 2017-03-27 17:47:47 | | NULL | NULL | NULL | | NULL | NULL | NULL | | NULL | NULL | NULL | + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 9 rows in the set (0.02 SEC) / / the following things with the right connections in a way that the query. The mysql > selectowner, information. The passward, information, create_time from pet right join information on information.user=pet.name; +-------+----------+---------------------+ | owner | passward | create_time | +-------+----------+---------------------+  | cyb | 999999 | 2017-03-27 17:47:47 | | lzh | 666666 | 2017-03-30 17:36:50 | + -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 2 rows in the set (0.00 SEC) composite connection query / / / / composite join queries is also known as composite query conditions, when join queries, we can add other constraints, The length of a composite query depends on the shortest table. mysql>selectowner,information.passward,information.create_time from pet,information where information.user=pet.name; +-------+----------+---------------------+ | owner | passward | create_time | +-------+----------+---------------------+  | lzh | 666666 | 2017-03-30 17:36:50 | | cyb | 999999 | 2017-03-27 17:47:47 | + -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 2 rows in the set (0.00 SEC)Copy the code

10. The subquerySince MySQL4.1, multiple queries can be nested, with the outer layer of the query using the result set from the inner layer of the query.

When encountering such multi-level queries,

MySQL starts with the innermost query and moves to the outer, in which the result set generated by each query is assigned to the surrounding parent query. Test data pack

Subqueries with the IN keyword: The IN keyword can detect the presence of a specific value IN the results, and execute external queries if the detection is successful.

Subquery with EXISTS keyword; When the EXISTS keyword is used, the inner query statement does not return the queried record, but instead returns a true or false value (true if the inner query finds a record that meets the condition). When true is returned, the outer statement queries.

Use regular expressions to query

Field name REGEXP ‘Matching method’