MySQL笔记---单表查询2

MySQL笔记---单表查询2

/**
  单表查询之 排序
  select * from 表名 order by 字段名 acs|desc        asc 生序      desc 倒序
 */
show databases;
use db_26_homework;
show tables;


select *
from stu
order by sage asc;
# 升序

select *
from stu
order by sage desc;
# 倒叙

select *
from stu
order by sage;
# 默认 是 asc


select *
from stu
order by sage asc, score desc;;
# 按年龄排序，如果年龄相同，按分数排序

select *
from stu
where sex = '女'
order by sage desc;
# 先条件 后 排序


/**
  单表查询之 分组
  group by
  ⚠️：分组后只能获取组信息，不能获取个体信息
 */


select count(*), sex, max(score), avg(score)
from stu
group by sex;
# 分组后能扩获取的信息： 分组列 + 聚合函数

select count(*), sex, max(score), avg(score)
from stu
where sage > 20
group by sex;
# 先条件 再分组

select sage, count(*)
from stu
where sex is not null
group by sage;
# 按年龄分组 , 性别不为 null


select count(*), sage, max(score), avg(score)
from stu
where sex is not null
  and sage
    in (18, 20, 22, 24, 26)
group by sage;
# 只要年龄为18 20 22 24 26 的学生
/**
  单表查询之 having xxxx 添加组条件 (可以添加聚合条件)
  having 给查询后的组添加条件
 */

select count(*), sage, max(score), avg(score)
from stu
where sex is not null
group by sage
having count(*) > 3;
# sex 不为 null，按年龄分组，保留总人数 > 3 的组信息


select count(*), sage, max(score), avg(score)
from stu
where sex is not null
group by sage
having sage in (18, 20, 22, 14);
# sex 不为 null，按年龄分组，保留年龄 为 18，20，22，14 的组信息


select count(*), sage, max(score), avg(score)
from stu
where sex is not null
group by sage
having count(*) >= 3
order by sage desc
limit 0,3;
# sex 不为 null，按年龄分组，保留总人数 > 3 的组信息, 按 sage 倒序, 只要前三条记录
# 先分组，再排序

/**
  单表查询语句的格式:

  select 列｜聚合函数｜列运算
  from 表名
  where 记录条件
  group by 分组列
  having 组条件
  order by 排序列 desc|acx (倒叙｜正序)
  limit startIndex,numbers (起始索引，记录数) e.g. 0,3 前三条
 */

---

#### 练习 ```sql /** 注意：统计信息=分组列+聚合函数 给stu表添加sclass 班级名称 字段值：class_1 /class_2 / class_3 / class_4 /class_5 4.1 获取每个班级的总人数 最高分 最低分 平均分 班级名称 4.2 获取平均分>20的班级的 统计信息 按总人数倒序 4.3 获取名字中含有2的女生的信息 先按年龄倒序 再按分数升序 4.4 获取男生和女生的统计信息 4.5 获取年龄和出生日期不符的学生的信息 4.6 获取所有男生按年龄分组 显示平均分最高的前3条记录

*/

<br>
```sql
select *
from stu;
alter table stu
    add sclass varchar(10);
# 添加字段
update stu
set sclass = 'class_1'
where sage % 5 = 0;

update stu
set sclass = 'class_2'
where sage % 5 = 1;

update stu
set sclass = 'class_3'
where sage % 5 = 2;

update stu
set sclass = 'class_4'
where sage % 5 = 3;

update stu
set sclass = 'class_5'
where sage % 5 = 4;
# 添加 字段值：class_1 /class_2 / class_3 / class_4 /class_5


select count(*) '总人数', max(score) '最高分', min(score) '最低分', avg(score) '平均分', sclass
from stu
group by sclass;
# 4.1  获取每个班级的总人数 最高分 最低分 平均分 班级名称


select count(*) '总人数', max(score) '最高分', min(score) '最低分', avg(score) '平均分', sclass
from stu
group by sclass
having avg(score) > 20
order by count(*);
# 获取平均分>20的班级的 统计信息  按总人数倒序

select *
from stu
where sex = '女'
  and sname like '%2%'
order by sage desc, score asc;
# 4.3  获取名字中含有2的女生的信息  先按年龄倒序 再按分数升序


select count(*) '总人数', max(score) '最高分', min(score) '最低分', avg(score) '平均分', sex
from stu
group by sex
having sex is not null;
# 4.4  获取男生和女生的统计信息


select *
from stu
where sage != (YEAR(now()) - YEAR(sbirthday));
# 4.5  获取年龄和出生日期不符的学生的信息


select count(*) '总人数', max(score) '最高分', min(score) '最低分', avg(score) '平均分', sage
from stu
where sex = '男'
group by sage
order by avg(score)
limit 0,3;
# 4.6  获取所有男生按年龄分组  显示平均分最高的前3条记录