在本教程中，您将学习如何使用sql not运算符来否定select语句where子句中的布尔表达式。
在前面已经学习了如何使用各种逻辑运算符，如：and，or，like，between，in和exists。 这些运算符可帮助您在where子句中形成灵活的条件。
要反转任何布尔表达式的结果，请使用not运算符。 以下演示如何使用not运算符。
not [boolean_expression]
下表显示了not运算符的结果。
| 原值 | 应用not运算符后 |
|---|---|
| true | false |
| false | true |
| null | null |
我们将使用employees表来演示not运算符。employees表的结构如下所示 -
mysql> desc employees;
+---------------+--------------+------+-----+---------+----------------+
| field | type | null | key | default | extra |
+---------------+--------------+------+-----+---------+----------------+
| employee_id | int(11) | no | pri | null | auto_increment |
| first_name | varchar(20) | yes | | null | |
| last_name | varchar(25) | no | | null | |
| email | varchar(100) | no | | null | |
| phone_number | varchar(20) | yes | | null | |
| hire_date | date | no | | null | |
| job_id | int(11) | no | mul | null | |
| salary | decimal(8,2) | no | | null | |
| manager_id | int(11) | yes | mul | null | |
| department_id | int(11) | yes | mul | null | |
+---------------+--------------+------+-----+---------+----------------+
10 rows in set
以下语句检索在部门id为5中工作的所有员工。如下查询语句 -
select
employee_id,
first_name,
last_name,
salary
from
employees
where
department_id = 5
order by
salary;
执行上面示例代码，得到以下结果 -
查询工作在部门id为5且薪水不超过5000的员工。
select
employee_id,
first_name,
last_name,
salary
from
employees
where
department_id = 5
and not salary > 5000
order by
salary;
执行上面示例代码，得到以下结果 -
+-------------+------------+-----------+--------+
| employee_id | first_name | last_name | salary |
+-------------+------------+-----------+--------+
| 126 | irene | liu | 2700 |
| 193 | britney | zhao | 3900 |
| 192 | sarah | yang | 4000 |
+-------------+------------+-----------+--------+
3 rows in set
sql not in运算符示例
要否定in运算符，请使用not运算符。 例如，以下语句将获取不在部门id为1,2或3中工作的所有员工。
select
employee_id,
first_name,
last_name,
department_id
from
employees
where
department_id not in (1, 2, 3)
order by
first_name;
执行上面查询语句，得到以下结果 -
+-------------+------------+-----------+---------------+
| employee_id | first_name | last_name | department_id |
+-------------+------------+-----------+---------------+
| 103 | alexander | lee | 6 |
| 193 | britney | zhao | 5 |
| 104 | bruce | wong | 6 |
| 179 | charles | yang | 8 |
| 109 | daniel | chen | 10 |
| 105 | david | liang | 6 |
| 107 | diana | chen | 6 |
| 204 | hermann | wu | 7 |
| 126 | irene | liu | 5 |
......
| 100 | steven | lee | 9 |
| 203 | susan | zhou | 4 |
| 106 | valli | chen | 6 |
| 206 | william | wu | 11 |
+-------------+------------+-----------+---------------+
31 rows in set
sql not like运算符示例
可以使用not like来否定like运算符。 例如，以下语句检索名字不以字母m开头的所有员工。
select
first_name,
last_name
from
employees
where
first_name not like 'm%'
order by
first_name;
执行上面查询语句，得到以下结果 -
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| alexander | lee |
| alexander | su |
| avg | su |
| britney | zhao |
| bruce | wong |
| charles | yang |
... ...
| shelley | wu |
| shelli | zhang |
| sigal | zhang |
| steven | lee |
| susan | zhou |
| valli | chen |
| william | wu |
+------------+-----------+
34 rows in set
sql not between示例
以下示例说明如何使用not来否定between运算符，以使员工的薪水不在1000到1000之间。
select
employee_id,
first_name,
last_name,
salary
from
employees
where
salary not between 3000
and 5000
order by
salary;
执行上面查询语句，得到以下结果 -
+-------------+------------+-----------+--------+
| employee_id | first_name | last_name | salary |
+-------------+------------+-----------+--------+
| 119 | karen | zhang | 2500 |
| 118 | guy | zhang | 2600 |
| 126 | irene | liu | 2700 |
| 117 | sigal | zhang | 2800 |
| 116 | shelli | zhang | 2900 |
| 104 | bruce | wong | 6000 |
| 202 | pat | zhou | 6000 |
| 179 | charles | yang | 6200 |
... ...
| 205 | shelley | wu | 12000 |
| 201 | michael | zhou | 13000 |
| 146 | karen | liu | 13500 |
| 145 | john | liu | 14000 |
| 102 | lex | liang | 17000 |
| 101 | neena | wong | 17000 |
| 100 | steven | lee | 24000 |
+-------------+------------+-----------+--------+
33 rows in set
sql not exists示例
请参考以下员工和家属表：
以下查询使用not exists运算符来获取没有任何家属的员工。
select
employee_id, first_name, last_name
from
employees e
where
not exists (
select
employee_id
from
dependents d
where
d.employee_id = e.employee_id
);
执行上面查询语句，得到以下结果 -
通过上面的学习，现在您应该知道如何使用not运算符来否定布尔表达式了。
