MySQL Common knowledge

First, common keywords:
1.DISTINCT: Filter Repeat
Select distinct create_user_name from Bms_project; In this case, it is necessary to use the distinct filter out duplicates.

2.count: Statistics
Select COUNT (*) from Bms_project; Query the Bms_project table for a total of how many records.
Select COUNT (*) from Bms_project where project_type=1; Query Bms_project table project_type=1 The total number of records.

3.top: Take the front of the N bar
Select Top 3 * from Bms_project; Query the top 3 records in the Bms_project.

4.like: Fuzzy Query
SELECT * from Bms_project where project_name like '% item% ' and project_type=2;
Queries the Bms_project table project_type=2, and Project_Name contains a record of the word "project".

5.between...and ...: From ... to come to ...; In... Between
SELECT * from Bms_project where project_budget between 500000 and 1000000;
Query Bms_project table Project_budget records from 500000 to 1000000.

6.in: Sub-query
SELECT * from Bms_project where ID in (100,110,120,130,140,150);
The query Bms_project table ID is 100,110,120,130,140,150 records.

7.order by: Sort (ASC order, DESC reverse)
SELECT * from Bms_project order by Project_budget DESC;

8.group by: ... Group. Typically used with aggregate functions (count (), SUM (), AVG ())
Select Project_type,sum (project_budget) from Bms_project Group by Project_type;
Note: The fields following group by are consistent with the fields of the non-aggregate function after select.

9.limit x, y (x: Start position, Y: Offset value)
Select Project_budget from Bms_project ORDER BY project_budget desc limit 0,5;
Remove the first five bars from the Bms_project table and display them in the order of the project_budget from large to small.

second, multi-table connection query:
1. Table1 INNER join table2: Inner connection. Only the intersection rows of the two tables that are connected are displayed.
2. Table1 LEFT JOIN table2: outer connection. Displays all rows of the Table1, even if there are no matching rows in table2.
3. Table1 Right join table2: Left outer connection. Displays all rows of the table2, even if there are no matching rows in table1z.
4. Table1 Full JOIN table2: Even if the rows in Table1 do not match in table2, or the rows in Table2 do not match in Table1. It still returns all matching rows in two tables of Table1 and table2.

Take the person's length, make up your own short

MySQL Common knowledge