MySQL data query
| Query all data in the table | select * from table name; |
| Query specified field data | < span>select field 1, field 2, from table name; |
| as alias the field | |
| as alias table | select field,,, from table name as alias; |
| eliminate duplicate rows | |
| condition query | select * From table name where condition; |
Several common types of conditional query
| comparison operator | <,>,<=, >=,=,!= |
| logical transport symbol | and, or, not |
| fuzzy query | where field like Value (% stands for replacing any character, _ stands for replacing one character) |
| range query | < span>in (collection), not in (collection), between… and …, not between… and… |
| Null judgment | is null (represents empty), is not null (represents non-empty) |
Sorting data: easy to view data
| Sorting in ascending order (small to large) | order by field asc (sort in ascending order according to a field) |
| Sorting in descending order (large to small) | order by field desc (sort in descending order according to a field) |
| Multi-field sorting | Order by field 1asc, field 2 desc (After sorting in ascending order according to field 1, if the value 1 is still the same, then sorting in descending order of field 2) |
Aggregate functions
| Get the total number of rows in the table | select count(*) from table name; |
| get the maximum value in the column | select max (field) from table name; |
| get the minimum value in the column | select min (field) from table name; |
| get the sum of the columns< /td> | select sum (field) from table name; |
| get the average value of the column | select avg (field) from table name; |
grouping
| Group by field to get the value of the field (not repeated) | select field from table name group by field; |
| group to get the value of the field and the number of each group | select field, count(*)from table name group by field; |
| group to get the field Value and other information of each group | select field, group_concat (field 2) from table name group by field; |
| Condition query after grouping | elect field from table name group by field having conditions; |
| Summarize after grouping (add the sum of all records in a row of records) | select field, count(*)from table name group by field with rollup; |
< span>Paging: When the amount of data is too large, use the paging display to make data query more convenient
| Query a fixed number of data | Select * from table name limit num; (num represents the first few data) |
| Paginated display of a fixed number of data | Select * from table name limit m, n; (m stands for the starting position, 0 stands for the first one, and so on; n stands for displaying several data) |
< /p>