mysql排序 范围_【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;