- 聚合
- count(*)表示计算总行数
-
select count(*) from students;
- max(列)表示求此列的最大值
-
select max(id) from students where gender=0;
- min(列)表示求此列的最大值
-
select min(id) from students where isdelete=0;
- sum(列)表示此列的和
- avg(列)表示求此列的平均值
- 分组
- 语法:select 列1,列2,聚合...from 表名 group by 列1,列2,列3...
-
select gender,count(*) from students group by gender;
- 分组后进行帅选:select 列1,列2,聚合...from 表名 group by 列1,列2,列3...having 列1,...聚合...
-
select gender,count(*) from students group by gender having gender=0;
- having后面的条件运算符与where的相同
- where是对from后面指定的表进行数据筛选,属于对原始数据的筛选
- having是对group by的结果进行筛选
- 排序
- select * from 表名 order by 列1 asc|desc...
- 分页
- select * from 表名 limit start,count, start索引从0 开始
-
select * from students limit 1,3;
- 已知:每页显示m条数据,当前显示第n页
- 求第n页的数据
- select * from stuents
- where isdelete = 0
- limit (n-1)*m,m
小结:
select distinct *
from 表名
where ...
groupy by ... having ...
order by ...
limit star,count