MySQL--条件查询、范围查询、排序

–not
不在18岁以上的女性 的范围信息

select * from student where not(age>18 and gender='女');

年龄不是小于或者等于18 并且是女性

select * from student (not age<=18) and gender='女';

模糊查询(效率低)

1、like
%替换1个或者多个
_替换1个
查询姓名以“小”开始的名字

select * from student where name like"小%";

查询至少两个字以上的名

select * from student where name like "__%";

2、rlike 正则
查询以周开始的名字
查询以周开始、伦结尾的名字

select * from student where name rlike "^周.*";
select * from student where name rlike "^周.*伦$";

范围查询

1、in (1,3,8)表示非连续的范围
查询年龄为18,34的姓名

select name,age from student where age in (18,34);

not in 不非连续的范围
查询年龄不是18,34的姓名

select name,age from student where age not  in (18,34);

between … and …表示连续的范围内

select name,age from student where age between 18 and 34; //年龄18到34范围内的信息
select name,age from student where age not between 18 and 34;//年龄不是18到34范围内的信息

空判断

is null 判断为空
is not null 判断非空

select * from student where height is null;
select * from student where height is not null;

排序

order by 字段
asc从小到大排序，升序
desc从大到小排序，降序

select * from student where (age between 18 and 34) and gender='男' order by age;  //年龄从18到34之间男性，按照年龄从小到大排序
select * from student where (age between 18 and 34) and gender='女' order by heightdesc;//查询年龄18到34岁女性，按身高从高到矮排序

order by 多个字段

select * from student where (age between 18 and 34) and gender='女' order by height desc,id desc;//查询年龄18到34岁女性，按身高从高到矮排序,身高相同情况下，按id来排

按照年龄从小到大、身高从高到矮排序

select * from student order by age asc,height desc;