MySQL WHERE 子句教程

摘要：在本教程中，您将学习如何在SELECT语句中使用 MySQL WHERE子句来过滤结果集中的行。

MySQL `WHERE`子句简介

WHERE子句允许您为查询返回的行指定搜索条件。下面显示了WHERE子句的语法：

SELECT 
    select_list
FROM
    table_name
WHERE
    search_condition;Code language: SQL (Structured Query Language) (sql)

search_condition是使用逻辑运算符AND 、 OR和NOT的一个或多个表达式的组合。

在 MySQL 中，谓词是一个布尔表达式，其计算结果为TRUE 、 FALSE或UNKNOWN 。

SELECT语句将包含结果集中满足search_condition任何行。

除了SELECT语句之外，您还可以在UPDATE或DELETE语句中使用WHERE子句来指定要更新或删除的行。

当执行带有WHERE子句的SELECT语句时，MySQL 会计算FROM子句之后、 SELECT和ORDER BY子句之前的WHERE子句：

MySQL WHERE 子句示例

我们将使用示例数据库中的employees表进行演示。

1) 使用带有等号运算符的 MySQL WHERE 子句示例

以下查询使用WHERE子句查找职位为Sales Rep的所有员工：

SELECT 
    lastname, 
    firstname, 
    jobtitle
FROM
    employees
WHERE
    jobtitle = 'Sales Rep';Code language: SQL (Structured Query Language) (sql)

试试看

+-----------+-----------+-----------+
| lastname  | firstname | jobtitle  |
+-----------+-----------+-----------+
| Jennings  | Leslie    | Sales Rep |
| Thompson  | Leslie    | Sales Rep |
| Firrelli  | Julie     | Sales Rep |
| Patterson | Steve     | Sales Rep |
| Tseng     | Foon Yue  | Sales Rep |
| Vanauf    | George    | Sales Rep |
| Bondur    | Loui      | Sales Rep |
| Hernandez | Gerard    | Sales Rep |
| Castillo  | Pamela    | Sales Rep |
| Bott      | Larry     | Sales Rep |
| Jones     | Barry     | Sales Rep |
| Fixter    | Andy      | Sales Rep |
| Marsh     | Peter     | Sales Rep |
| King      | Tom       | Sales Rep |
| Nishi     | Mami      | Sales Rep |
| Kato      | Yoshimi   | Sales Rep |
| Gerard    | Martin    | Sales Rep |
+-----------+-----------+-----------+
17 rows in set (0.00 sec)Code language: plaintext (plaintext)

在此示例中， SELECT语句检查employees表的所有行，并仅选择jobTitle列中的值为Sales Rep的行。

2) 使用带有 AND 运算符的 MySQL WHERE 子句

以下示例使用WHERE子句查找职位为Sales Rep且办公室代码为 1 的员工：

SELECT 
    lastname, 
    firstname, 
    jobtitle,
    officeCode
FROM
    employees
WHERE
    jobtitle = 'Sales Rep' AND 
    officeCode = 1;Code language: SQL (Structured Query Language) (sql)

试试看

+----------+-----------+-----------+------------+
| lastname | firstname | jobtitle  | officeCode |
+----------+-----------+-----------+------------+
| Jennings | Leslie    | Sales Rep | 1          |
| Thompson | Leslie    | Sales Rep | 1          |
+----------+-----------+-----------+------------+
2 rows in set (0.00 sec)Code language: plaintext (plaintext)

在此示例中， WHERE子句中的表达式使用AND运算符来组合两个条件：

jobtitle = 'Sales Rep' AND officeCode = 1;Code language: SQL (Structured Query Language) (sql)

仅当两个表达式的计算结果均为TRUE时， AND运算符的计算结果才为TRUE 。因此，查询返回jobTitle列中的值为Sales Rep且officeCode为 1 的行。

3) 使用带有 OR 运算符的 MySQL WHERE 子句

此查询查找职位为Sales Rep员工或办公室代码为 1 的员工：

SELECT 
    lastName, 
    firstName, 
    jobTitle, 
    officeCode
FROM
    employees
WHERE
    jobtitle = 'Sales Rep' OR 
    officeCode = 1
ORDER BY 
    officeCode , 
    jobTitle;Code language: SQL (Structured Query Language) (sql)

试试看

+-----------+-----------+--------------------+------------+
| lastName  | firstName | jobTitle           | officeCode |
+-----------+-----------+--------------------+------------+
| Murphy    | Diane     | President          | 1          |
| Bow       | Anthony   | Sales Manager (NA) | 1          |
| Jennings  | Leslie    | Sales Rep          | 1          |
| Thompson  | Leslie    | Sales Rep          | 1          |
| Firrelli  | Jeff      | VP Marketing       | 1          |
| Patterson | Mary      | VP Sales           | 1          |
| Firrelli  | Julie     | Sales Rep          | 2          |
| Patterson | Steve     | Sales Rep          | 2          |
| Tseng     | Foon Yue  | Sales Rep          | 3          |
| Vanauf    | George    | Sales Rep          | 3          |
| Bondur    | Loui      | Sales Rep          | 4          |
| Hernandez | Gerard    | Sales Rep          | 4          |
| Castillo  | Pamela    | Sales Rep          | 4          |
| Gerard    | Martin    | Sales Rep          | 4          |
| Nishi     | Mami      | Sales Rep          | 5          |
| Kato      | Yoshimi   | Sales Rep          | 5          |
| Fixter    | Andy      | Sales Rep          | 6          |
| Marsh     | Peter     | Sales Rep          | 6          |
| King      | Tom       | Sales Rep          | 6          |
| Bott      | Larry     | Sales Rep          | 7          |
| Jones     | Barry     | Sales Rep          | 7          |
+-----------+-----------+--------------------+------------+
21 rows in set (0.00 sec)Code language: plaintext (plaintext)

仅当表达式之一计算结果为TRUE时， OR运算符计算结果为TRUE ：

jobtitle = 'Sales Rep' OR officeCode = 1Code language: SQL (Structured Query Language) (sql)

因此，查询将返回职位为“销售代表”或办公室代码为 1 的所有员工。

4) 使用 MySQL WHERE 子句和 BETWEEN 运算符示例

如果值在某个值范围内，则BETWEEN运算符返回TRUE ：

expression BETWEEN low AND highCode language: SQL (Structured Query Language) (sql)

以下查询查找办公室代码为 1 到 3 的办公室的员工：

SELECT 
    firstName, 
    lastName, 
    officeCode
FROM
    employees
WHERE
    officeCode BETWEEN 1 AND 3
ORDER BY officeCode;Code language: SQL (Structured Query Language) (sql)

试试看

+-----------+-----------+------------+
| firstName | lastName  | officeCode |
+-----------+-----------+------------+
| Diane     | Murphy    | 1          |
| Mary      | Patterson | 1          |
| Jeff      | Firrelli  | 1          |
| Anthony   | Bow       | 1          |
| Leslie    | Jennings  | 1          |
| Leslie    | Thompson  | 1          |
| Julie     | Firrelli  | 2          |
| Steve     | Patterson | 2          |
| Foon Yue  | Tseng     | 3          |
| George    | Vanauf    | 3          |
+-----------+-----------+------------+
10 rows in set (0.00 sec)Code language: plaintext (plaintext)

5) 使用 MySQL WHERE 子句和 LIKE 运算符示例

如果值与指定模式匹配，则LIKE运算符的计算结果为TRUE 。

要形成模式，请使用%和_通配符。 %通配符匹配零个或多个字符的任何字符串，而_通配符匹配任何单个字符。

以下查询查找姓氏以字符串'son'结尾的员工：

SELECT 
    firstName, 
    lastName
FROM
    employees
WHERE
    lastName LIKE '%son'
ORDER BY firstName;Code language: SQL (Structured Query Language) (sql)

试试看

+-----------+-----------+
| firstName | lastName  |
+-----------+-----------+
| Leslie    | Thompson  |
| Mary      | Patterson |
| Steve     | Patterson |
| William   | Patterson |
+-----------+-----------+
4 rows in set (0.00 sec)Code language: plaintext (plaintext)

6) 使用 MySQL WHERE 子句和 IN 运算符示例

如果某个值与列表中的任何值匹配，则IN运算符返回TRUE 。

value IN (value1, value2,...)Code language: SQL (Structured Query Language) (sql)

以下示例使用WHERE子句和IN运算符来查找位于办公室代码为 1 的办公室的员工。

SELECT 
    firstName, 
    lastName, 
    officeCode
FROM
    employees
WHERE
    officeCode IN (1 , 2, 3)
ORDER BY 
    officeCode;Code language: SQL (Structured Query Language) (sql)

试试看

+-----------+-----------+------------+
| firstName | lastName  | officeCode |
+-----------+-----------+------------+
| Diane     | Murphy    | 1          |
| Mary      | Patterson | 1          |
| Jeff      | Firrelli  | 1          |
| Anthony   | Bow       | 1          |
| Leslie    | Jennings  | 1          |
| Leslie    | Thompson  | 1          |
| Julie     | Firrelli  | 2          |
| Steve     | Patterson | 2          |
| Foon Yue  | Tseng     | 3          |
| George    | Vanauf    | 3          |
+-----------+-----------+------------+
10 rows in set (0.00 sec)Code language: plaintext (plaintext)

7) 使用带有 IS NULL 运算符的 MySQL WHERE 子句

要检查值是否为NULL ，请使用IS NULL运算符，而不是等于运算符 ( = )。如果值为NULL ， IS NULL运算符将返回TRUE 。

value IS NULLCode language: SQL (Structured Query Language) (sql)

在数据库世界中， NULL是一个标记，表示某个值丢失或未知。并且 NULL 不等于数字 0 或空字符串。

以下语句使用带有IS NULL运算符的WHERE子句来获取reportsTo列中的值为NULL的行：

SELECT 
    lastName, 
    firstName, 
    reportsTo
FROM
    employees
WHERE
    reportsTo IS NULL;Code language: SQL (Structured Query Language) (sql)

试试看

+----------+-----------+-----------+
| lastName | firstName | reportsTo |
+----------+-----------+-----------+
| Murphy   | Diane     |      NULL |
+----------+-----------+-----------+
1 row in set (0.01 sec)Code language: plaintext (plaintext)

8) 使用带比较运算符的 MySQL WHERE 子句

下表显示了可用于构成WHERE子句中的表达式的比较运算符。

操作员	描述
=	等于。您几乎可以将它用于任何数据类型。
<> 或 !=	不等于
<	少于。通常将其与数字和日期/时间数据类型一起使用。
>	比...更棒。
<=	小于或等于
>=	大于或等于

以下查询使用不等于 (<>) 运算符来查找不是Sales Rep所有员工：

SELECT 
    lastname, 
    firstname, 
    jobtitle
FROM
    employees
WHERE
    jobtitle <> 'Sales Rep';Code language: SQL (Structured Query Language) (sql)

试试看

+-----------+-----------+----------------------+
| lastname  | firstname | jobtitle             |
+-----------+-----------+----------------------+
| Murphy    | Diane     | President            |
| Patterson | Mary      | VP Sales             |
| Firrelli  | Jeff      | VP Marketing         |
| Patterson | William   | Sales Manager (APAC) |
| Bondur    | Gerard    | Sale Manager (EMEA)  |
| Bow       | Anthony   | Sales Manager (NA)   |
+-----------+-----------+----------------------+
6 rows in set (0.00 sec)Code language: plaintext (plaintext)

以下查询查找办公室代码大于 5 的员工：

SELECT 
    lastname, 
    firstname, 
    officeCode
FROM
    employees
WHERE 
    officecode > 5;Code language: SQL (Structured Query Language) (sql)

试试看

+-----------+-----------+------------+
| lastname  | firstname | officeCode |
+-----------+-----------+------------+
| Patterson | William   | 6          |
| Bott      | Larry     | 7          |
| Jones     | Barry     | 7          |
| Fixter    | Andy      | 6          |
| Marsh     | Peter     | 6          |
| King      | Tom       | 6          |
+-----------+-----------+------------+
6 rows in set (0.00 sec)Code language: plaintext (plaintext)

以下查询返回办公室代码小于或等于 4 (<=4) 的员工：

SELECT 
    lastname, 
    firstname, 
    officeCode
FROM
    employees
WHERE 
    officecode <= 4;Code language: SQL (Structured Query Language) (sql)

试试看

+-----------+-----------+------------+
| lastname  | firstname | officeCode |
+-----------+-----------+------------+
| Murphy    | Diane     | 1          |
| Patterson | Mary      | 1          |
| Firrelli  | Jeff      | 1          |
| Bondur    | Gerard    | 4          |
| Bow       | Anthony   | 1          |
| Jennings  | Leslie    | 1          |
| Thompson  | Leslie    | 1          |
| Firrelli  | Julie     | 2          |
| Patterson | Steve     | 2          |
| Tseng     | Foon Yue  | 3          |
| Vanauf    | George    | 3          |
| Bondur    | Loui      | 4          |
| Hernandez | Gerard    | 4          |
| Castillo  | Pamela    | 4          |
| Gerard    | Martin    | 4          |
+-----------+-----------+------------+
15 rows in set (0.00 sec)Code language: plaintext (plaintext)

概括

使用WHERE子句按条件过滤行。
MySQL 在FROM子句之后、 SELECT和ORDER BY子句之前评估WHERE子句。

本教程有帮助吗？

MySQL WHERE子句简介