【2020083103】mysql中范围查询、排序

一、范围查询

（1）查询年纪在18-20岁的学生名字；

select name from students where age=18 or age=19 or age=20;

select name from students where age in (18,19,20);

select name from students where age between 18 and 20;

--不在18-20岁之间的；

select name from students where age not  in (18,19,29);

select name from students where age not between 18 and 20; ---注意不能写成 not (between 18 and 20);

（2）查询年纪为空的学生名字；

select name from students where age is null；----不为空：is not null

（3）查询名字有两个字的；

select name from students where name like '__'（两个下划线）

（4）查询名字以“周”开始的；

select name from students where name like‘周%’；

select name from students where name rlike '^周.*';

（5）查询名字以“周”开始，以“伦”结尾的；

select name from students where name like ‘周%伦’；

select name from students where name rlike '^周.*伦$'；

（6）查询名字至少有两个字的；

select name from students where name like‘__%’;

select name from students where name rlike '\d\d*';

 

二、排序

（1）按年纪从小到大排序；

select * from students order by age；（默认是按照从小到大）

select * from students order by age asc；

 （2）按照年纪从大到小排序

select * from students order by age desc;