MySQL 过滤查询 where语句应用

文章中用到的表

create table student(
	id int ,
    name varchar(20),
    sex varchar(20),
    phone varchar(20),
    address varchar(20),
    primary key(id)
)default charset=utf8;
insert into student(id,name,sex,phone,address) values(1,'小A','男','10004','earth');
insert into student(id,name,sex,phone,address) values(2,'小B','男','10001','earth');
insert into student(id,name,sex,phone,address) values(3,'小C','男','10002','earth');
insert into student(id,name,sex,phone,address) values(4,'小D','男','10003','earth');
insert into student(id,name,sex,phone,address) values(5,'小F','男','10006','earth');
insert into student(id,name,sex,phone,address) values(6,'小E','男','10005','earth');

where子句操作符

where子句操作符有=,<>,!=,<,<=,>,>=.
1.查询学生表中名字为小A的学生信息

select * from student where name='小A';

2.输出学生表中所有名字不为小A的学生的信息

select * from student where name<>'小A';

select * from student where name!='小A';

!=和<>操作符效果是一样的

3.查询phone小于10002的学生的信息

select * from student where phone<'10002';

4.查询phone小于等于10002的学生信息

select * from student where phone<='10002';

范围查询

1.查询学生id>=2且<=5的学生信息

select * from student where id between 2 and 5;

select * from student where id>=2 and id<=5;

between可以用>=和<=的组合来取代,效果是一样的.

空值查询

1.输出id不为空的所有学生的信息

select * from student where id is not null;

2.输出phone为空的所有学生的信息

select * from student where phone is  null;

组合where语句

1.查询学生id为3或者为4的学生信息

select * from student where id = 3 or id = 4;

2.查询学生id为1且address为earth的学生信息

select * from student where id = 1 and address='earth';

3.查询学生id为2或者3,并且phone为10001的学生信息

select * from student where (id=2 or id=3) and phone='10001';

and操作符合or操作符是可以任意组合的,其中and优先级比or高,但是我们可以使用圆括号()来优先进行or.

4.查询学生id为2或者3的学生信息

select * from student where id in (2,3);

not操作符

not操作符的作用就是取反,对not后面的任何条件都会取反

1.查询学生id不为2且不为3的所有学生的信息

select * from student where id not in(2,3);