DBA 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);
VALUES (”, ‘1990-01-01’, ‘13813145213’,30);
INSERT INTO users (name, birth_date, phone,age)
VALUES (null, ‘1990-01-01’, ‘13813145213’,30);
VALUES (null, ‘1990-01-01’, ‘13813145213’,30);
INSERT INTO users (name, birth_date, phone,age)
VALUES (‘老张’, null, ‘17813145213’,30);
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，需要结合()使用
