MySQL-使用WHERE子句选择
时间：2020-02-23 14:41:03 来源:igfitidea点击:
在本教程中，我们将学习使用MySQL中的where子句从表中选择数据。
我们在SELECT FROM Table教程中介绍了如何从表中选择行。
随时检查一下。
我们使用WHERE子句根据某些条件过滤结果。
WHERE语法
以下是WHERE子句的语法。
SELECT * FROM table_name WHERE condition;
其中，condition用于过滤结果。
在本教程中，我们将使用在本教程系列中一直在使用的" employee"和" comments"表。
员工表
以下是employee表中的行。
mysql> SELECT * FROM employee;
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+ | employeeid | firstname | lastname | email | score | birthday | lastmodified | created | +------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+ | e01 | | | @example.com | 6 | 1900-01-01 | 2016-01-01 01:01:01 | 2016-01-01 01:01:01 | | e02 | John | Doe | [email protected] | 8 | 1900-02-03 | 2016-01-01 01:01:04 | 2016-01-01 01:01:04 | | e03 | Jane | Doe | [email protected] | 9 | 1900-05-20 | 2016-01-01 01:01:04 | 2016-01-01 01:01:04 | | e04 | Tin | Tin | [email protected] | 7 | 1900-10-20 | 2016-01-01 01:02:03 | 2016-01-01 01:02:03 | | e05 | Bob | Coder | [email protected] | 5 | 1900-08-20 | 2016-01-01 01:02:10 | 2016-01-01 01:02:10 | +------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+ 5 rows in set (0.00 sec)
评论表
以下是评论表中的行。
mysql> SELECT * FROM comments;
+-----------+------------+---------------------+---------------------+---------------------+ | commentid | employeeid | commentbody | lastmodified | created | +-----------+------------+---------------------+---------------------+---------------------+ | 1 | e03 | Awesome | 2016-01-01 02:03:04 | 2016-01-01 02:03:04 | | 2 | e03 | Hello World | 2016-01-02 01:02:03 | 2016-01-02 01:02:03 | | 3 | e01 | Happy | 2016-01-02 01:04:03 | 2016-01-02 01:04:03 | | 4 | e04 | This is my comment. | 2016-01-01 02:03:04 | 2016-01-01 02:03:04 | | 5 | e01 | Keep coding.... | 2016-04-04 03:02:01 | 2016-05-04 03:02:01 | +-----------+------------+---------------------+---------------------+---------------------+ 5 rows in set (0.00 sec)
WHERE子句的运算符
我们使用以下运算符通过WHERE子句检查条件。
| 运算符 | 示例 | 说明 |
|---|---|---|
| = | leftSide = rightSide | 我们使用"等于"运算符来检查左侧是否等于右侧。 |
| ！= | leftSide！= rightSide | 我们使用"不等于"运算符来检查左侧是否不等于右侧。 |
| > | leftSide> rightSide | 我们使用"大于"运算符来检查左侧是否大于右侧。 |
| >= | leftSide >= rightSide | 我们使用大于或者等于运算符来检查左侧是否大于或者等于右侧。 |
| IN | leftSide IN('val1'，'val2'，...) | 我们使用IN来检查左侧值是否存在于可能的值列表中。 |
| LIKE | leftSide LIKE pattern | 我们使用LIKE运算符来搜索匹配的模式。 |
| BETWEEN | leftSide BETWEEN option1 AND option2 | 我们使用" BETWEEN"运算符来检查左侧值是否在option1和option2之间(包括两者)。 |
使用WHERE子句选择
=和！=运算符
在下面的示例中，我们选择雇员ID为'e01'的雇员的详细信息。
mysql> SELECT * FROM employee WHERE employeeid = 'e01';
+------------+-----------+----------+-------------------+-------+------------+---------------------+---------------------+ | employeeid | firstname | lastname | email | score | birthday | lastmodified | created | +------------+-----------+----------+-------------------+-------+------------+---------------------+---------------------+ | e01 | | | @example.com | 6 | 1900-01-01 | 2016-01-01 01:01:01 | 2016-01-01 01:01:01 | +------------+-----------+----------+-------------------+-------+------------+---------------------+---------------------+ 1 row in set (0.00 sec)
类似地，我们可以使用不等于！=的运算符来选择所有与值'e01'不匹配的行。
在下面的示例中，我们选择所有不具有employeeid等于" e01"的雇员。
mysql> SELECT * FROM employee WHERE employeeid != 'e01';
将文本值括在单引号中，如下所示。
员工编号！='e01'
>和<运算符
在下面的示例中，我们从employee表中选择得分大于5的行。
mysql> SELECT * FROM employee WHERE score > 5;
+------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+ | employeeid | firstname | lastname | email | score | birthday | lastmodified | created | +------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+ | e01 | | | @example.com | 6 | 1900-01-01 | 2016-01-01 01:01:01 | 2016-01-01 01:01:01 | | e02 | John | Doe | [email protected] | 8 | 1900-02-03 | 2016-01-01 01:01:04 | 2016-01-01 01:01:04 | | e03 | Jane | Doe | [email protected] | 9 | 1900-05-20 | 2016-01-01 01:01:04 | 2016-01-01 01:01:04 | | e04 | Tin | Tin | [email protected] | 7 | 1900-10-20 | 2016-01-01 01:02:03 | 2016-01-01 01:02:03 | +------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+ 4 rows in set (0.00 sec)
同样，我们可以使用小于`
mysql> SELECT * FROM employee WHERE score < 5;
我们不必将数字值括在单引号中。
=运算符
在下面的示例中，我们使用选择在'1900-05-01'或者之前出生的所有员工
mysql> SELECT * FROM employee WHERE birthday <= '1900-05-01';
+------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+ | employeeid | firstname | lastname | email | score | birthday | lastmodified | created | +------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+ | e01 | | | @example.com | 6 | 1900-01-01 | 2016-01-01 01:01:01 | 2016-01-01 01:01:01 | | e02 | John | Doe | [email protected] | 8 | 1900-02-03 | 2016-01-01 01:01:04 | 2016-01-01 01:01:04 | +------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+ 2 rows in set (0.00 sec)
同样，我们可以使用'> ='运算符选择所有在'1900-05-01'或者之后出生的雇员。
mysql> SELECT * FROM employee WHERE birthday >= '1900-05-01';
IN运算符
在下面的示例中，我们选择员工编号" e01"，" e03"和" e05"的详细信息。
mysql> SELECT * FROM employee
WHERE employeeid IN ('e01', 'e03', 'e05');
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+ | employeeid | firstname | lastname | email | score | birthday | lastmodified | created | +------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+ | e01 | | | @example.com | 6 | 1900-01-01 | 2016-01-01 01:01:01 | 2016-01-01 01:01:01 | | e03 | Jane | Doe | [email protected] | 9 | 1900-05-20 | 2016-01-01 01:01:04 | 2016-01-01 01:01:04 | | e05 | Bob | Coder | [email protected] | 5 | 1900-08-20 | 2016-01-01 01:02:10 | 2016-01-01 01:02:10 | +------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+ 3 rows in set (0.00 sec)
LIKE运算符
我们使用LIKE运算符来匹配模式。
可以使用三种方式使用LIKE运算符，如下所示。
值开头的匹配模式。
值结尾处的匹配模式。
值之间的匹配模式。
Like 'pattern％'-匹配开始
在下面的示例中，我们选择所有以" J"开头的名字的雇员。
mysql> SELECT * FROM employee WHERE firstname LIKE 'J%';
+------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+ | employeeid | firstname | lastname | email | score | birthday | lastmodified | created | +------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+ | e02 | John | Doe | [email protected] | 8 | 1900-02-03 | 2016-01-01 01:01:04 | 2016-01-01 01:01:04 | | e03 | Jane | Doe | [email protected] | 9 | 1900-05-20 | 2016-01-01 01:01:04 | 2016-01-01 01:01:04 | +------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+ 2 rows in set (0.01 sec)
LIKE '％pattern'-匹配结束
在以下示例中，我们选择所有姓氏以" oe"结尾的员工。
mysql> SELECT * FROM employee WHERE lastname LIKE '%oe';
+------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+ | employeeid | firstname | lastname | email | score | birthday | lastmodified | created | +------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+ | e02 | John | Doe | [email protected] | 8 | 1900-02-03 | 2016-01-01 01:01:04 | 2016-01-01 01:01:04 | | e03 | Jane | Doe | [email protected] | 9 | 1900-05-20 | 2016-01-01 01:01:04 | 2016-01-01 01:01:04 | +------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+ 2 rows in set (0.00 sec)
LIKE '％pattern％'-中间匹配
在下面的示例中，我们选择" commentbody"列中所有具有模式" co"的注释。
mysql> SELECT * FROM comments WHERE commentbody LIKE '%co%';
+-----------+------------+---------------------+---------------------+---------------------+ | commentid | employeeid | commentbody | lastmodified | created | +-----------+------------+---------------------+---------------------+---------------------+ | 4 | e04 | This is my comment. | 2016-01-01 02:03:04 | 2016-01-01 02:03:04 | | 5 | e01 | Keep coding.... | 2016-04-04 03:02:01 | 2016-05-04 03:02:01 | +-----------+------------+---------------------+---------------------+---------------------+ 2 rows in set (0.00 sec)
BETWEEN运算符
在下面的示例中，我们选择所有出生于'1900-03-01'到'1900-06-01'的雇员(包括日期)。
mysql> SELECT * FROM employee WHERE birthday BETWEEN '1900-03-01' AND '1900-06-01';
+------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+ | employeeid | firstname | lastname | email | score | birthday | lastmodified | created | +------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+ | e03 | Jane | Doe | [email protected] | 9 | 1900-05-20 | 2016-01-01 01:01:04 | 2016-01-01 01:01:04 | +------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+ 1 row in set (0.00 sec)
