MySQL - 07条件查询（单条件、多条件）

原创

szlsay 2022-09-13 11:55:58 博主文章分类：DBA-MySQL ©著作权

©著作权归作者所有：来自51CTO博客作者szlsay的原创作品，请联系作者获取转载授权，否则将追究法律责任

where子句(单条件查询)

在SQL中，insert、update、delete和select后面都能带where子句，用于插入、修改、删除或查询指定条件的记录。

#SQL语句中使用where子句语法
SELECT column_name FROM table_name WHERE column_name 运算符 value

运算符	描述
=	等于
<> 或 !=	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于
between and	选取介于两个值之间的数据范围;在MySQL中，相当于>=并且<=

where子句(多条件查询)

在where子句中，使用and、or可以把两个或多个过滤条件结合起来。

# and、or运算符语法
SELECT column_name FROM table_name WHERE condition1 AND condition2 OR

运算符	描述
and	表示左右两边的条件同时成立
or	表示左右两边只要有一个条件成立

实战案例

将下面的内容在mysql中执行

create table employee(
  id int not null auto_increment primary key,
  name varchar(30),
  sex varchar(1),
  salary int
);

insert into employee(name, sex, salary) values('lily0', '1', 5500);
insert into employee(name, sex, salary) values('lily1', '0', 4500);
insert into employee(name, sex, salary) values('lily2', '0', 4200);
insert into employee(name, sex, salary) values('lily3', '1', 7500);
insert into employee(name, sex, salary) values('lily4', '0', 8500);
insert into employee(name, sex, salary) values('lily5', '1', 6800);
insert into employee(name, sex, salary) values('lily6', '1', 12000);
insert into employee(name, sex, salary) values('lily7', '1', 3500);
insert into employee(name, sex, salary) values('lily8', '1', 6000);
insert into employee(name, sex, salary) values('lily9', '1', 8000);
insert into employee(name, sex, salary) values('lily10', '0', 10000);
insert into employee(name, sex, salary) values('lily11', '0', 4000);

> select * from employee;
+----+--------+------+--------+
| id | name   | sex  | salary |
+----+--------+------+--------+
|  1 | lily0  | 1    |   5500 |
|  2 | lily1  | 0    |   4500 |
|  3 | lily2  | 0    |   4200 |
|  4 | lily3  | 1    |   7500 |
|  5 | lily4  | 0    |   8500 |
|  6 | lily5  | 1    |   6800 |
|  7 | lily6  | 1    |  12000 |
|  8 | lily7  | 1    |   3500 |
|  9 | lily8  | 1    |   6000 |
| 10 | lily9  | 1    |   8000 |
| 11 | lily10 | 0    |  10000 |
| 12 | lily11 | 0    |   4000 |
+----+--------+------+--------+
12 rows in set (0.00 sec)

mysql> select * from employee where sex = '1';
+----+-------+------+--------+
| id | name  | sex  | salary |
+----+-------+------+--------+
|  1 | lily0 | 1    |   5500 |
|  4 | lily3 | 1    |   7500 |
|  6 | lily5 | 1    |   6800 |
|  7 | lily6 | 1    |  12000 |
|  8 | lily7 | 1    |   3500 |
|  9 | lily8 | 1    |   6000 |
| 10 | lily9 | 1    |   8000 |
+----+-------+------+--------+
7 rows in set (0.01 sec)

mysql> select * from employee where sex != '0';
+----+-------+------+--------+
| id | name  | sex  | salary |
+----+-------+------+--------+
|  1 | lily0 | 1    |   5500 |
|  4 | lily3 | 1    |   7500 |
|  6 | lily5 | 1    |   6800 |
|  7 | lily6 | 1    |  12000 |
|  8 | lily7 | 1    |   3500 |
|  9 | lily8 | 1    |   6000 |
| 10 | lily9 | 1    |   8000 |
+----+-------+------+--------+
7 rows in set (0.01 sec)

mysql> select * from employee where sex <> '0';
+----+-------+------+--------+
| id | name  | sex  | salary |
+----+-------+------+--------+
|  1 | lily0 | 1    |   5500 |
|  4 | lily3 | 1    |   7500 |
|  6 | lily5 | 1    |   6800 |
|  7 | lily6 | 1    |  12000 |
|  8 | lily7 | 1    |   3500 |
|  9 | lily8 | 1    |   6000 |
| 10 | lily9 | 1    |   8000 |
+----+-------+------+--------+
7 rows in set (0.00 sec)

mysql> select * from employee where salary > 8000;
+----+--------+------+--------+
| id | name   | sex  | salary |
+----+--------+------+--------+
|  5 | lily4  | 0    |   8500 |
|  7 | lily6  | 1    |  12000 |
| 11 | lily10 | 0    |  10000 |
+----+--------+------+--------+
3 rows in set (0.00 sec)

mysql> select * from employee where salary between 10000 and 12000;
+----+--------+------+--------+
| id | name   | sex  | salary |
+----+--------+------+--------+
|  7 | lily6  | 1    |  12000 |
| 11 | lily10 | 0    |  10000 |
+----+--------+------+--------+
2 rows in set (0.00 sec)

mysql> select * from employee where sex = '1' and salary > 10000;
+----+-------+------+--------+
| id | name  | sex  | salary |
+----+-------+------+--------+
|  7 | lily6 | 1    |  12000 |
+----+-------+------+--------+
1 row in set (0.00 sec)

mysql> select * from employee where sex = '1' or salary > 10000;
+----+-------+------+--------+
| id | name  | sex  | salary |
+----+-------+------+--------+
|  1 | lily0 | 1    |   5500 |
|  4 | lily3 | 1    |   7500 |
|  6 | lily5 | 1    |   6800 |
|  7 | lily6 | 1    |  12000 |
|  8 | lily7 | 1    |   3500 |
|  9 | lily8 | 1    |   6000 |
| 10 | lily9 | 1    |   8000 |
+----+-------+------+--------+
7 rows in set (0.00 sec)

mysql> select * from employee where sex = '1' or salary >= 10000;
+----+--------+------+--------+
| id | name   | sex  | salary |
+----+--------+------+--------+
|  1 | lily0  | 1    |   5500 |
|  4 | lily3  | 1    |   7500 |
|  6 | lily5  | 1    |   6800 |
|  7 | lily6  | 1    |  12000 |
|  8 | lily7  | 1    |   3500 |
|  9 | lily8  | 1    |   6000 |
| 10 | lily9  | 1    |   8000 |
| 11 | lily10 | 0    |  10000 |
+----+--------+------+--------+
8 rows in set (0.00 sec)

mysql> select * from employee where sex = '1' and salary <= 4000 or salary >= 10000;
+----+--------+------+--------+
| id | name   | sex  | salary |
+----+--------+------+--------+
|  7 | lily6  | 1    |  12000 |
|  8 | lily7  | 1    |   3500 |
| 11 | lily10 | 0    |  10000 |
+----+--------+------+--------+
3 rows in set (0.00 sec)

mysql> select * from employee where sex = '1' and (salary <= 4000 or salary >= 10000);
+----+-------+------+--------+
| id | name  | sex  | salary |
+----+-------+------+--------+
|  7 | lily6 | 1    |  12000 |
|  8 | lily7 | 1    |   3500 |
+----+-------+------+--------+
2 rows in set (0.00 sec)

mysql>