Write in front:
From the perspective of practice, familiar with the database is the first step in large query chance, summarizes the basic knowledge of database query, today to share with you ~
Basics
The most simple query
SELECT the field name 1, 2 FROM the field name table name
Query all fields
SELECT * FROM table name
Query and to heavy
Filter field name 1, field name 2 have the same data item
SELECT DISTINCT field name 1, 2 from the field name, table name
Query to the fields of the assigned to the new field name
SELECT the field name AS the new fields of 1, the field name AS the new fields of 2 from the table name
By combining logic query data do not exist in the table field
SELECT the field name 1, computational logic 1 AS the field name 2 from the table name
For example:
The SELECT user_id, SUM (VALUE1, VALUE2) AS VALUEFROM use_info_table
Add a filter condition
SELECT the field name 1, 2 from the field name name of the table WHERE conditions 1 AND condition 2 OR 3
Grouping query (common use)
SELECT the field name 1, COUNT (2) the field name, the SUM (field name 3) FROM the table name GROUP BY the field name 1
Explanation: according to the field name 1 group, 2 count within the group to the field, the field 3 sum
Grouping query (not common use)
SELECT the field name 1, 2 from the field name table name GROUP BY field name 1
Explanation: according to the field name 1 group, within the group of field 2 random values
To sort the query results
SELECT the field name 1, 2 from the field name table name ORDER BY the field name 1 ASC/DESC
Explanation: ASC table ascending, descending DESC table, default ascending
The query results are filtered
SELECT the field name 1, 2 from the field name table name HAVING conditions 1 AND condition 2 OR 3
Explanation: the WHERE used to constrain data, before the result set to work, HAVING a filter is a statement, is in the query result set the query results are after filtering operation,
The article limits the output data item number
SELECT the field name 1, 2 FROM the field name table name article LIMIT the number of
The above command execution order
Select - & gt; Where - & gt; Group by - & gt; Having - & gt; The order by
Multi-table query and the subquery, the UNION operator
Connections within the INNER JOIN
The field name 1, SELECT a. b. field name 3 from table 1 aINNER JOIN table 2 bON a. field name=1 b. The field name 2
Explanation: only when the field 1 in table 1 and table 2 fields there are 2 will match the results
LEFT connection LEFT JOIN
The field name 1, SELECT a. b. field name 3 from table 1 aLEFT JOIN table 2 bON a. field name=1 b. The field name 2
Explanation: will be subject to field 1 table 1 and table 2 when there is no corresponding value field 2 complement Null
The RIGHT connection RIGHT JOIN
Field name 1, SELECT a. b. field name 3 from table 1 aRIGHT the JOIN table 2 bON a. field name=1 b. The field name 2
Explanation: the field 2 in table 2, table 1 field 1 when there is no corresponding value Null
More trouble is diverted the original:
https://mp.weixin.qq.com/s/v8qh3b771ed5pkcAxPllJw
CodePudding user response:
Site won't openCodePudding user response: