数据库优化第4讲 - 聚合函数、分组、排序、分页、连接查询、子查询
一、聚合函数
-- count
-- 查询学生总数
select count(*) from students;
-- 查询男性有多少人,女性有多少人
select count(*) as 男性人数 from students where gender = 1;
select count(*) as 女性人数 from students where gender = 2;
-- 最大值
-- max
-- 查询最大的年龄
select max(age) from students;
-- 查询女性的最大编号
select max(id) from students where gender = 2;
-- 查询最大年龄的女性id
-- select max(age) from students where gender=2;
-- 最小值
-- min
-- 查询未删除的学生最小编号
select min(id) from students where is_delete=0;
-- 求和
-- sum
-- 查询男生的年龄和
select sum(age) from students where gender = 1;
-- 求和字段 是varchar 0 如果字段内容是数字的话会进行相加
-- 平均值
-- avg
-- 查询未删除女生的年龄的平均值
select avg(age) from students where gender=2 and is_delete = 0; -- 默认保留4位小数
-- 计算平均年龄 sum(age)/count(*)
-- 四舍五入 round(123.23 , 1) 保留1位小数
-- 计算所有人的平均年龄,保留2位小数
select round(avg(age),2) from students;
select round(sum(age)/count(*),2) from students;
-- 计算男性的平均身高 保留2位小数
二、分组(group by)
group by的含义:将查询结果按照1个或多个字段进行分组,字段值相同的为一组
group by可用于单个字段分组,也可用于多个字段分组
group by + group_concat()
group_concat(字段名)可以作为一个输出字段来使用,
表示分组之后,根据分组结果,使用group_concat()来放置每一组的某字段的值的集合
group by + 集合函数
通过group_concat()的启发,我们既然可以统计出每个分组的某字段的值的集合,那么我们也可以通过集合函数来对这个值的集合做一些操作
group by + having
having 条件表达式:用来分组查询后指定一些条件来输出查询结果
having作用和where一样,但having只能用于group by
2.1 分组
-- group by
-- select ... from students group by gender;
select name from students group by gender; -- 聚合函数
-- 真正能区分这个组的字段
-- 按照性别分组,查询所有的性别
select gender from students group by gender;
-- 计算男生和女生中的人数
select count(*) from students group by gender;
select gender as 性别,count(*) from students group by gender;
select gender as 性别,count(*) from students where gender = 1;
select count(*) from students where gender = 2;
-- 男女同学最大年龄
select gender as 性别,max(age) from students group by gender;
-- group_concat(...) 查看组内的信息
-- 查询同种性别中的姓名
select gender as 性别,group_concat(name) from students group by gender;
-- 查询组内年龄,姓名 'a' + b + 'c' b 变量
select gender as 性别,group_concat('姓名:',name,'-','年龄:',age) from students group by gender;
2.2 分组之后的筛选
having
-- 查询男生女生总数大于2
select gender,count(*) from students group by gender having count(*)>2;
-- 查询男生女生总数大于2的姓名
select gender,count(*),group_concat(name) from students group by gender having count(*)>2;
-- 查询平均年龄超过18岁的性别,以及姓名 having avg(age) > 18
select gender,group_concat(name),avg(age) from students group by gender having avg(age) > 18;
三、排序
order by 字段
asc从小到大排列,即升序 默认
desc从大到小排序,即降序
-- 查询年龄在18到26岁之间的男同学,按照年龄从小到到排序
select * from students where (age between 18 and 26) and gender=1;
select * from students where (age between 18 and 26) and gender=1 order by age;
select * from students where (age between 18 and 26) and gender=1 order by age asc;
-- 查询年龄在18到26岁之间的女同学,身高从高到矮排序
select * from students where (age between 18 and 26) and gender=2 order by id desc;
-- order by 多个字段
-- 查询年龄在18到28岁之间的男性,年龄从高到矮排序, 如果身高相同的情况下按照年龄从小到大排序
-- 排序的字段 相同了,先按照 年龄排序 , 如果年龄相同 再按照身高进行排序
select * from students where (age between 18 and 28) and gender=1 order by age desc,hiht asc;
-- 按照年龄从小到大、身高从高到矮的排序
四、 分页
limit start, count 起始的位置(从0开始), 个数
-- 限制查询出来的数据个数
select * from students limit 2;
-- 查询前5个数据
select * from students limit 5;
-- 查询id 6-10(包含)的数据
select * from students limit 5,5;
-- 制作分页
-- 每页显示2个,第1个页面
select * from students limit 0,2;
-- 每页显示2个,第2个页面
select * from students limit 2,2;
-- 每页显示2个,第3个页面
select * from students limit 4,2;
-- 每页显示2个,第4个页面
select * from students limit 6,2;
limit 第三页的话 limit (第N页-1)*每页显示的个数,每页显示的个数
select * from students limit 2*(6-1),2; 错误的
select gender from students limit 0,2 group by gender ; 只能写到最后
限制显示的条数
五、连接查询
5.1 内连接 inner join … on
select ... from 表A inner join 表B;
select * from students inner join classes;
-- 查询 有能够对应班级的学生以及班级信息,显示学生的所有信息,只显示班级名称
select s.*,c.name from students as s inner join classes as c on s.cls_id = c.id;
5.2 左连接 left join … on
select ... from 表A inner join 表B;
select * from students left join classes on students.cls_id = classes.id;
select * from classes left join students on students.cls_id = classes.id;
六、子查询:一个select语句中嵌套另外的select语句
select * from students where higt = (select max(higt) from students where gender = '男');
七、自关联
两张表
# 查询黑龙江省
SELECT * FROM provinces WHERE province = '黑龙江省';
# 查询黑龙江省下面的市
SELECT * FROM cities WHERE provinceid=230000;
# 子查询 inner join
SELECT * FROM provinces AS p INNER JOIN cities AS c ON
p.provinceid=c.provinceid HAVING p.`province`='湖南省';
一张表
# 查询黑龙江省
SELECT * FROM areas WHERE NAME = '黑龙江';
# 查询黑龙江省下面的市
SELECT * FROM areas WHERE pid=12;
# 查询哈尔滨市下面的区
SELECT * FROM areas WHERE pid=167;
SELECT * FROM areas AS p INNER JOIN areas AS c ON p.`id`=c.`pid` HAVING p.`name`='黑龙江';
SELECT * FROM areas AS p INNER JOIN areas AS c ON p.`id`=c.`pid` HAVING p.`name`='哈尔滨';