select * from student;
-- 查询年龄大于20的
select * from student where age>=20;
-- 查询年龄不等于20的
select * from student where age!=20;-- 或者是select * from student where age <> 20;
-- 查询年龄大于20 小于30 推荐 and并且的意思 &&不推荐
select * from student where age>=20 and age<=30;
-- 等效于
select * from student where between 20 and 30;
-- 查询22岁 19岁 25岁的人的信息 or 或者
select * from student where age=22 or age=19 or age=25;
-- 等效于 in
select * from student where age in (19,22,25);
-- 查询age为空的 null 不能用=
select * from student where age is null;
-- 查询age不为空的
select * from student where age is not null;
模糊查询 like
1._占1个字符
2.%占多个字符
-- 基本语法 select * from student name like "";
select * from student name like "_忠%";
-- 查询包含字符
select * from student name like "%忠%";
排序查询 order by 不写默认是升序 asc 降序 desc
-- 基本语法 select * from student order by 字段1,字段2;
select * from student order by score desc;
聚合函数 sum 求和 max 最大值 min 最小值 count统计数量 avg 平均值
注意:聚合函数会排除null值不会计算
-- 基本语法 select 函数名(字段) from student ;
select count(*) from student ;
select sum (age) from student ;
select max (age) from student ;
select min (age) from student ;
select avg (age) from student ;
-- 基本语法 select 分组的字段 或者是聚合函数 from student group by 分组的字段;
select sex,sum(score) from student group by sex;
-- 分数低于70分不参与分组
select sex,sum(score) from student where score >=70 group by sex;
-- 分组之后人数大于2个
select sex,sum(score),cound(*) from student where score >=70 group by sex having cound(*)>2;
分页查询 limit 数据开始的索引 (n-1)*每页显示的条数;
-- 基本语法 select * from student limit 数据开始的索引,每页显示的条数;
select * from student limit 0,3; -- 第一页
select * from student limit 3,3; -- 第二页
select * from student limit (n-1)*3,3; -- 第n页