SQL开发知识：MySQL学习条件查询数据

一、条件查询

利用where语句可以对数据进行筛选

select * from 表名 where 条件;

二、比较运算符

​ 运算符 ​	​ 描述 ​	​ 例子 ​
=	等于	where id = 1
\>	大于	where age > 10
<	小于	where age < 10
>=	大于等于	where age >= 10
<=	小于等于	where age <= 10
!=	不等于	where name != '老王'

select * from users where id = 1;

三、逻辑运算符

​ 运算符 ​	​ 描述 ​	​ 例子 ​
and	并且	where id = 1 and age > 10
or	或者	where id = 1 or age > 10
not	取反	where not id = 1

select * from users where id = 1 and age = 24;

select * from users where not id = 1;

四、范围查询

​ 运算符 ​	​ 描述 ​	​ 例子 ​
in	在指定的非连续范围内	where id in(1,3,5);
between … and …	在指定的连续范围内	where id between 1 and 5;

select * from users where id in (1,3,4);

select * from users where id between 1 and 5;

五、空判断

​ 运算符 ​	​ 描述 ​	​ 例子 ​
is null	判断是否为空	where name is null
is not null	判断是否不为空	where name is not null

 注：null与''是不一样的

INSERT INTO users (name, birth_date, phone,age)
VALUES (”, ‘1990-01-01’, ‘13813145213’,30);

INSERT INTO users (name, birth_date, phone,age)
VALUES (null, ‘1990-01-01’, ‘13813145213’,30);

INSERT INTO users (name, birth_date, phone,age)
VALUES (‘老张’, null, ‘17813145213’,30);

select * from users where birth_date is null;

六、模糊查询

select * from users where name like ‘王%’;

select * from users where name like ‘%王’;

七、优先级

• 小括号，not，比较运算符，逻辑运算符
• and比or先运算，如果同时出现并希望先算or，需要结合()使用