排序
默认是按照升序排的
升序:ASC
降序:DESC
SELECT * FROM t_shuihu;
#默认是升序 不加也是
SELECT * FROM t_shuihu;
SELECT * FROM t_shuihu WHERE 1=1 ORDER by id;
SELECT * FROM t_shuihu WHERE 1=1 ORDER by id ASC;
#降序排序
SELECT * FROM t_shuihu WHERE 1=1 ORDER BY id DESC;
#age降序
select * from t_shuihu where 1=1 ORDER by age DESC;
select * from t_shuihu where 1=1 by age DESC;
#多个字段参与排序
#年龄按照升序排,如果年龄相同用id的降序排
SELECT * FROM t_shuihu ORDER BY age,id desc;
#年龄按照降序排,如果年龄相同用id的降序排
SELECT * FROM t_shuihu ORDER BY age desc ,id DESC;
分组
#分组
select * FROM t_honglou GROUP by gender;
#分组一般要和聚合函数一起使用
SELECT COUNT(*) FROM t_honglou GROUP BY gender;
#对应各有多少人
select gender, COUNT(*) FROM t_honglou GROUP BY gender;
#只查男生人数
SELECT gender,COUNT(*) FROM t_honglou WHERE gender='男' GROUP BY gender;
#查找根据性别分组后,人数大于两人的组
#HAVING 分组后的筛选,依赖于group BY
SELECT gender,COUNT(*) FROM t_honglou GROUP BY gender HAVING COUNT(gender)>15;
分页
#分页 软件分页将查询的数据不要在页面全部展示
SELECT COUNT(*) FROM t_honglou;
#分页展示数据,将数据分成多份
#每页两条数据
SELECT * FROM t_shuihu LIMIT 0,2; #第一页
SELECT * FROM t_shuihu LIMIT 2,2; #第二页
SELECT * FROM t_shuihu LIMIT 4,2; #第三页
#SELECT * FROM t_shuihu LIMIT (页码数-1)*每页展示数量数,每页展示数量数
#关注四个变量
#pageNow当前页数(用户决定)
#pageSize每页展示的数(程序员决定)
#pageCount总页数(pageCount=allCount%pageSize==0?allCount/pageSize:allCount/pageSize+1)
#allCount总数量(查询SELECT COUNT(*) FROM 表;)
#后端分页
SELECT * FROM t_honglou LIMIT (pageNow-1)*pageSize,pageSize;