MySQL SQL syntax summary

MySQL PRIMARY key operation

To delete a table primary key:

ALTER TABLE student drop PRIMARY key;

Add Table primary key:

ALTER TABLE student Add primary key (ID);

Reset the primary key increment (if you need to perform a delete table primary KEY statement first):

ALTER TABLE student Modify ID bigint (a) Auto_increment primary key;

Verify that the name is duplicate

Plus limit 1 is telling MySQL to find an instant return without wasting the time behind

Select COUNT (*) from student where name = ' Tom ' limit 1;

GROUP BY statement: divided by criteria into different groups

New Customer table

Id	Name	Cost
1	Tom	1000
2	Jojnny	2000
3	Tom	350
4	Bruce	730
5	Mark	3000
6	Ethan	2300
7	Bruce	1740

Query the total cost per customer:

Select name, sum (cost) from the customer group by customer;

The result of the query is:

Name	SUM (COST)
Bruce	2470
Ethan	2300
Johnny	2000
Mark	3000
Tom	1350

ORDER BY statement: in conditional order

Search by cost from small to large

SELECT * from the customer order by cost;

Query results are

Id	Customer	Cost
3	Tom	350
4	Bruce	730
1	Tom	1000
7	Bruce	1740
2	Johnny	2000
6	Ethan	2300
5	Mark	3000

Group BY ORDER by combination: grouped by grouping criteria and sorted by sorting criteria

According to the total cost of customers from small to large arrangement

Select name, sum (cost) from the customer group by customer order by sum (cost);

The query results are:

Name	SUM (COST)
Tom	1350
Johnny	2000
Ethan	2300
Bruce	2470
Mark	3000

Distinct statement: Removing duplicates

Find out all the customer names

Select distinct name from customer;

The query results are:

Name

Tom

Johnny

Bruce

Mark

Ethan

MySQL SQL syntax summary