MySQL课堂笔记2

Wwhere 条件筛选

select * from student_user where age>20;

select * from student_user where age>20 and class=172;

select * from student_user where brithday>'2019-03-28';

select * from student_user where age between 20 and 22;

select * from student_user where age in (20,22);

like 模糊查询

% 通配全字符，0或多个

_  通配一个字符

order by 排序

select * from student_user order by age asc;正序

select * from student_user order by age desc;倒序

聚合函数，组函数

-- count 统计

-- max 最大值

-- min 最小值

-- sum 总和

-- avg 平均

group by 分组

--查询每个班级有多少学生？

select class,count(1) as user_number from student_user group by class;

-- 查询每个班级的平均分？

select class,avg(price) as price_avg from student_user group by class;

select的查询字段不能多于group by的字段，分组函数除外

having 对分组后的数据，再次做筛选

-- 查询班级的平均分等于13.11

select class,avg(price) as avg_price from student_user group by class having avg_price=13.11;

条件筛选顺序

select--from--where--group by--having--order by 

limit 分页查询

select * from student_user limit m,n;

m表示：大于n，从n+1开始

n表示：偏移量，查几条