Mysql数据库——查询数据（模糊查询与order by）

一般查询

1、where和null

select  * from student where address= null #<——错误用法#

select  * from student where address is null 

select  * from student where address is not null

2、关系运算

select  * from student where age>11

3、and和or

select  * from student where age>11 and address is not null

select * from student where age=11 or age=24

4、between and （包括边界值 且小数在前大数在后）

select * from student where age between 11 and 24

模糊查询

% 匹配0次或多次

select * from student where name like 张三%

_只能匹配一次

select * from student where name like 张三_

escape

a. escape后面单引号中只能是单个字符；

b. escape后面可以是字母、#、$、,、等字符；

select * from student where name like %A%% escape A#（自动把A后面的字符当做一般字符处理）

in (升级版的or)

select * from student where age in(11,24)
select * from student where age=11 or age=24

排序order by

select * from student order by age asc
select * from student order by age desc
#单列排序，此时asc可以省略

select * from student order by age，mobile desc
select * from student order by age asc，mobile desc
#多列排序，前面如果不强调的话默认正序，且先满足第一列，第一列值重复的情况下才对第二列进行排序。

伪表 dual

dual是一个虚拟表，用来构成select的语法规则

select mod(1,3) from dual = select mod(1,3)

去除重复值 distinct

select distinct age from student#去除年龄相同的值
select distinct name,age from student#去除年龄和姓名均相同的值