Query the specified field Select field 1, Field 2 from the table name;
Eliminate duplicate rows (repeating refers to all fully repeating rows in the result set) SELECT DISTINCT field 1, Field 2 ... from table name
Comparison operator (< > =) SELECT * FROM table name where id>4
The logical operator (and or not) of the SELECT * from table name where id>4 (condition 1) and gender=1 (condition 2);
Fuzzy query (like% any number of characters _ any one character) select * from table name where name like ' horse% ';
Range query (in Between...and ...) SELECT * FROM table name where ID in (all-in-one); Query ID is a line of three lines (discontinuous)
SELECT * FROM table name where ID between 1 and 5; (Query ID 1 to 5 row, contiguous)
Empty (is null) (non-NULL) SELECT * FROM table name where condition is null;
Precedence (parentheses, not, comparison operators, logical operators (and above or first))
Aggregation functions
(Count: Total row count) Select COUNT (*) from table name where ...
(max,min: Maximum minimum value) select MAX (ID) from table name where ...
(Sum,avg: Summation averaging) select sum (ID) from table name where ...
GROUP BY: Select Gender Count (*) from table name Group by gender; (grouped by sex and total statistics)
Filter the grouped results (having) Select Gender count (*) from table name GROUP by gender have gender=0;
Where is the data filter for the original table after from, and having the result of group by IS filtered.
Sort (Order By default ascending ASC Descending desc) SELECT * FROM table name where condition order by id DESC;
Paging (limit) select * from table name where condition limit starts with the first few data, gets how many pieces of data
Full SELECT statement keyword order:
SELECT DISTINCT *
From table name Inner (left,right) join table name on relation
where ...
GROUP BY ...
ORDER BY ...
Limit Start,count
FOREIGN KEY constraint when creating a table last Add foreign key (constraint field) References table name (field name)
New FOREIGN key ALTER TABLE name ADD constraint foreign key name foreign key (field) References foreign key table name (field)
INNER JOIN query (inner JOIN connection table name on table name. field = Connection table name. field) PS: Two tables must have a relationship
Left join (the left join is left table, the data in the table is all displayed) right join the same
MySQL Database: Query statements, connection queries and foreign KEY constraints