愚公移山日记·31

愚公移山日记·31

group分组查询
分组是将数据集划分为若干个小区域，然后针对若干个小区域进行数据处理
select gender from students group by gender;
计算每个类别有点多少人
select gender,count() from students group by gender
按照年龄统计有多少人
selecr age ,count() from students group by age;
查询男生女生性别的中的最大值
select from students where gender = ‘男’ or gender = ‘女’ group by gender;
group by + group_concat()
group_concat(字段名)根据结果使用group_concat()放置每一个分组中某字段的集合
select gender,group_concat(name) form students group by gender;
查询每组性别的平均年龄
select gender,avg(age) from students group by gender;
查询平均年龄超过30 的性别以及姓名
select gender,avg(age) from students group by gender heaving avg（age)>30;
select gender,avg(age) av from students group by gender heaving av>30;
查询每种性别的平均年龄，和名字
select gender,avg(age),group_concat(name) form students group by gender;
查询每种性别中的人数多于2个的性别和姓名
select gender,count() c,group_concat(name) from students group by gender heaving c >2;
汇总
with rollup
select gender,coun() from students group by gender with rollup;
limit限制记录
limit 必须写在sql语句之后
limit 起始记录,记录数
表中的数据默认从0开始,
取出前五个数据
select * from students limit 0,5;
select * from students limit 5;
select * from students limit 4,2;
标准的sql书写格式：
select 字段1，字段2，…
from 表名
[ where 条件]
[group by 字段名]
[order by 字段名 排序规则]
[heacing 条件]
[limit 起始位置,数量]
分页查询
每页显示两个第一个页面
select * from students limit 2;
select * from students limit 2,2;
select * from students limit 4,2;
select * from students limit 6,2;
limit 数据分页原理： limit(第x页-1)每页数量
展示最新的数据
sclect * from students order by desc limit 0,2;
连接查询
当查询的结果来源于多张表时，需要将多张表连成一个大的数据进行汇总显示。
内连接查询 查询的结果为两个表匹配的数据
select * from students inner classes;
select * from students inner classes where students.cla_id = classes.id;
按照要求只显示姓名，班级
select students.name,calsses.name from students inner join classes where students.cls_id = classes.di;
内连接的另外的写法
select s.name,c.name from students s,calsses c where s.cls_id = c.id;
where 和on 的使用方法相似
查询有能够对应班级的学生以及班级信息，显示学生的所有信息 students , 只能显示班级名称 classse name。
select students.*,classes.name from students inner join classes on students.cls_id = classes.id;
在以上的查询中，将班级名显示在第一列
select classes.name,students.from students inner join classes on students.cls_id = classes.id;
查询所有能够对应班级的学生以及班级信息，按照班级进行排序
select * from students inner join classes omn students.cls_id = classes.if order by classes.id;
当同一个班级的时候，按照学生的id进行从小到大的排序
select * from students inner join classes omn students.cls_id = classes.if order by classes.id,students.if;
查询每一位同学对应的班级信息
左（外）连接 查询的结果是两个表匹配的数据于右表特有的数据连接
查询每一位同学的班级信息
select * from students left join classes on students.cls_id = classes.id;
查询没有班级的学生
select studnets. from students left join classes on students.cls_id = students.id where calsses.id is null;