03.mysql的CRUD2

带in子查询

select * from user where id in(10, 20, 30)
select * from user where id not in(10, 20, 30)
select * from user where id in(select id from grade where average>=60.0)

分页查询

select *from user limit M,N;
select *from user limit N offset M;
select id,nickname,name,age,sex from user limit 10;
select id,nickname,name,age,sex from user limit 2000,10

• explain 可以查看SQL语句的效率

explain select *from user;

• 分页

select *from user limit (pageno -1)*pagenum,pagenum;

效率比较的低,主要表现在M,偏移花费时间较多

• 优化

select *from user where id>上一页最后一条数据的id值 limit 20;

排序order by

select id,name,age,sex from user where sex='M' and age>=20 and age<=25 order by age asc;
select id,name,age from user  order by age desc;

分组group by

select sex from user group by sex;
select count(id),sex from user where age >=20 group by sex;
select count(id),age from user group by age having age>20

可以用explain查看:
group by效率与索引密切相关