MySQL the next day

Query fuzzy query (like) scope query

Non-contiguousselect * from students where id in(1,3,8);

Continuous select * from students where id between 3 and 8 and gender=1;

Polymerization

get statistical data quickly

1. COUNT (*)
2. SUM ()
3. Max ()
4. Min ()
5. AVG ()

Example: select count(*) from students
select sum(id) from students where isDelete=0 ;
Sub-query: select * from students where id=(select min(id) from students where idDelete=0);

Grouping (group BY)

select gender as xb,count(*) as rs from students group by gender;
Filter After grouping
select gender,count(*) from students group by gender having gender=1;
Having&where:

Where is the filter for the original data, for the From

Having is to filter the result after grouping, for group by

Sort (order By)

Default Ascending

select * from 表名 order by 列1 asc|desc,列2 asc|desc,……    

Pagination

select * from 表名 limit start,count   

• From Start (0), get count bar data

• Page n, showing m data
  select * from students<br/>where isdelete=0<br/>limit (n-1)*m,m;

  Summarize

  select (distinct) * from 表名 where .... group by ... having ... order by ... limit start,count;  

  Connection Query

  Establishing relationships between tables and tables

  Select Students.name,subjects.title,scores.score
  From scores
  Inner JOIN students on Scores.stuid=students.id
  Inner join subjects on scores.subid=subjects.id;

Generic Templates

select distinct 列 *from 表名1 inner|left|right join 表名2 on 表之间的关系group by ……having……order by ……asc|desclimit start,count

5/11/2018 6:35:33 PM

MySQL the next day