Advanced and summary of commonly used sql statements in MySQL database

select * from table name;

select field name, field name... from table name;

select table name.field name, table name.field name… from table name;

select table alias.field name from table name as table alias;

select distinct field name from table name;

select * from table name where age >18; 

select * from table name where age>18 and age<28; (18

select * from table name order by asc; (the default is asc, which can be omitted)

select * from table name order by desc;

select count(*) from table name;

select max(age) from table name;

select min(age) from table name;

select sum(age) from table name;

select avg(age) from table name;

select round(avg(age),2) from table name; (query the average age, round to two decimal places)

select gender count(*) from table name group by gender; (group by gender, query gender and number of people)

select gender avg(age) from table name group by gender; (query the average age of each gender)

select gender group_concat(name) from table name group by gender; (group_concat(name) to view the group name)

select gender count() from table name group by gender having count()>2 (having is similar to where, filtering condition, having can only be used for group by, where is used for table data)

select gender count(*) from table name group by gender with rollup; (add a new row at the end to show the summary result)

select * from table name limit n;

select * from table name limit 0,3; (display 3 per page, the first page) 
select * from table name limit 3,3; (display 3 per page, the second page) 
select * from table name limit 6,3; (display 3 per page, the 3rd page)

select * from table name 1 inner join table name 2 on table name 1.cls_id=table name 2.id; (join table 1 cls.id and table 2 with the same id) 
select table name 1. field name 1, table name 2. field name 2 from table name 1 inner join indicates 2 on condition;

select * from tablename1 left/right join tablename2 on tablename1.cls_id=tablename2.id; (the query result is the data matched by the two tables and the data unique to the left table. For data that does not exist in the left/right table, use null to fill it)

select * from table name where age > (select avg(age) from table name);

select name from tablename1 where id in (select cls_id from tablename2);

select * from table name where (height,age) = (select max(height),max(age) from table name);