MySQL必知必会：使用WHERE、正则表达式、通配符过滤数据

在SELECT语句中，数据根据

WHERE

子句中指定的搜索条件进行过滤。

mysql> select prod_name,prod_price from products where prod_price = 2.50;   # 价格等于2.50的产品名、产品价格+---------------+------------+| prod_name     | prod_price |+---------------+------------+| Carrots       | 2.50       || TNT (1 stick) | 2.50       |+---------------+------------+2 rows in set (0.04 sec)

建议使用SQL语句进行过滤：数据在应用层过滤，SQL的SELECT语句为客户机应用检索出超过实际所需的数据，然后客户机代码对返回数据进行循环，以提取出需要的行。通常，这种实现并不令人满意。因此，对数据库进行了优化，以便快速有效地对数据进行过滤。让客户机应用（或开发语言）处理数据库的工作将会极大地影响应用的性能，并且使所创建的应用完全不具备可伸缩性。此外，如果在客户机上过滤数据，服务器不得不通过网络发送多余的数据，这将导致网络带宽的浪费。
在同时使用ORDER BY和WHERE子句时，应该让ORDER BY位于WHERE之后。

一，WHERE子句操作符

= 等于
<> 不等于
!= 不等于
< 小于
<= 小于等于
> 大于
>= 大于等于
BETWEEN 在指定的两个值之间

1，检查单个值：

mysql> select prod_name,prod_price from products where prod_name = \"fuses\";  # 默认不区分大小写+-----------+------------+| prod_name | prod_price |+-----------+------------+| Fuses     | 3.42       |+-----------+------------+1 row in set (0.03 sec)mysql> select prod_name,prod_price from products where prod_price < 10; # 价格小于10的产品名、产品价格+---------------+------------+| prod_name     | prod_price |+---------------+------------+| .5 ton anvil  | 5.99       || 1 ton anvil   | 9.99       || Carrots       | 2.50       || Fuses         | 3.42       || Oil can       | 8.99       || Sling         | 4.49       || TNT (1 stick) | 2.50       |+---------------+------------+7 rows in set (0.04 sec)mysql> select prod_name,prod_price from products where prod_price <=10; # 价格小于等于10的产品名、产品价格+----------------+------------+| prod_name      | prod_price |+----------------+------------+| .5 ton anvil   | 5.99       || 1 ton anvil    | 9.99       || Bird seed      | 10.00      || Carrots        | 2.50       || Fuses          | 3.42       || Oil can        | 8.99       || Sling          | 4.49       || TNT (1 stick)  | 2.50       || TNT (5 sticks) | 10.00      |+----------------+------------+9 rows in set (0.03 sec)

2，不匹配检查：

mysql> select vend_id,prod_name from products where vend_id <> 1003; # 检索不是由1003供应商制造的所有产品+---------+--------------+| vend_id | prod_name    |+---------+--------------+|    1001 | .5 ton anvil ||    1001 | 1 ton anvil  ||    1001 | 2 ton anvil  ||    1002 | Fuses        ||    1002 | Oil can      ||    1005 | JetPack 1000 ||    1005 | JetPack 2000 |+---------+--------------+7 rows in set (0.05 sec)

3，范围值检查：

mysql> select prod_name,prod_price from products where prod_price between 5 and 10; # 价格 大于等于5，小于等于10 的产品名、产品价格+----------------+------------+| prod_name      | prod_price |+----------------+------------+| .5 ton anvil   | 5.99       || 1 ton anvil    | 9.99       || Bird seed      | 10.00      || Oil can        | 8.99       || TNT (5 sticks) | 10.00      |+----------------+------------+5 rows in set (0.03 sec)

```
BETWEEN AND
```
匹配范围中所有的值，包括指定的开始值和结束值。

4，空值检查：

在一个列不包含值时，称其为包含空值NULL，使用

IS NULL

子句进行检查。

mysql> select prod_name from products where prod_price is null;  # 返回prod_price为空值null的prod_name,无对应数据Empty setmysql> select cust_id from customers where cust_email is null; # 检索cust_email为空值时的cust_id+---------+| cust_id |+---------+|   10002 ||   10005 |+---------+2 rows in set (0.04 sec)

在通过过滤选择出不具有特定值的行时，你可能希望返回具有NULL值的行。但是，不行。因为未知具有特殊的含义，数据库不知道它们是否匹配，所以在匹配过滤或不匹配过滤时不返回它们。因此，在过滤数据时，一定要验证返回数据中确实给出了被过滤列具有NULL的行。

二，组合WHERE子句

操作符（operator）用来联结或改变WHERE子句中的子句的关键字。也称为逻辑操作符（ logical operator）。为了进行更强的过滤控制， MySQL允许给出多个WHERE子句。

1，AND操作符：

AND

指示DBMS只返回满足所有给定条件的行。

mysql> select vend_id,prod_price,prod_name from productswhere vend_id = 1003 and prod_price <= 10; #检索由供应商1003制造且价格小于等于10美元的产品信息+---------+------------+----------------+| vend_id | prod_price | prod_name      |+---------+------------+----------------+|    1003 | 10.00      | Bird seed      ||    1003 | 2.50       | Carrots        ||    1003 | 4.49       | Sling          ||    1003 | 2.50       | TNT (1 stick)  ||    1003 | 10.00      | TNT (5 sticks) |+---------+------------+----------------+5 rows in set (0.13 sec)

把两个过滤条件组合在一起。还可以添加多个过滤条件，每添加一条就要使用一个AND。

2，OR操作符：

OR

指示MySQL检索匹配任一条件的行。

mysql> select prod_name,prod_price from productswhere vend_id = 1002 or vend_id = 1003; # 检索由任一个指定供应商制造的所有产品的产品信息+----------------+------------+| prod_name      | prod_price |+----------------+------------+| Fuses          | 3.42       || Oil can        | 8.99       || Detonator      | 13.00      || Bird seed      | 10.00      || Carrots        | 2.50       || Safe           | 50.00      || Sling          | 4.49       || TNT (1 stick)  | 2.50       || TNT (5 sticks) | 10.00      |+----------------+------------+9 rows in set (0.06 sec)

3，AND与OR的使用顺序的问题：

WHERE可包含任意数目的AND和OR操作符。允许两者结合以进行复杂和高级的过滤。
但是，组合AND和OR带来了一个有趣的问题：

mysql> # 优先计算and，查找vend_id为1003且价格>=10的产品，或者vend_id为1002的产品，不管价格如何select prod_name,prod_price from productswhere vend_id = 1002 or vend_id = 1003 and prod_price >= 10;+----------------+------------+| prod_name      | prod_price |+----------------+------------+| Fuses          | 3.42       || Oil can        | 8.99       || Detonator      | 13.00      || Bird seed      | 10.00      || Safe           | 50.00      || TNT (5 sticks) | 10.00      |+----------------+------------+6 rows in set (0.03 sec)

返回的行未按预期的进行过滤。为什么会这样呢？由于AND在计算次序中优先级更高，操作符被错误地组合了。

此问题的解决方法是使用圆括号明确地分组相应的操作符：

mysql> # 使用圆括号明确运算顺序：查找vend_id为1002或1003，且价格>=10的产品select prod_name,prod_price from productswhere (vend_id = 1002 or vend_id = 1003) and prod_price >= 10;+----------------+------------+| prod_name      | prod_price |+----------------+------------+| Detonator      | 13.00      || Bird seed      | 10.00      || Safe           | 50.00      || TNT (5 sticks) | 10.00      |+----------------+------------+4 rows in set (0.03 sec)

任何时候使用具有AND和OR操作符的WHERE子句，都应该使用圆括号明确地分组操作符。不要过分依赖默认计算次序，即使它确实是你想要的东西也是如此。使用圆括号没有什么坏处，它能消除歧义。

4，IN操作符：

圆括号在WHERE子句中还有另外一种用法。

IN

操作符用来指定条件范围，范围中的每个条件都可以进行匹配。

mysql> # IN操作符后跟由逗号分隔的合法值清单，整个清单必须括在圆括号select prod_name,prod_price from productswhere vend_id in (1002,1003) order by prod_name;+----------------+------------+| prod_name      | prod_price |+----------------+------------+| Bird seed      | 10.00      || Carrots        | 2.50       || Detonator      | 13.00      || Fuses          | 3.42       || Oil can        | 8.99       || Safe           | 50.00      || Sling          | 4.49       || TNT (1 stick)  | 2.50       || TNT (5 sticks) | 10.00      |+----------------+------------+9 rows in set (0.17 sec)

IN操作符完成与OR相同的功能，但是：
在使用长的合法选项清单时， IN操作符的语法更清楚且更直观；
在使用IN时，计算的次序更容易管理（因为使用的操作符更少）；
IN操作符一般比OR操作符清单执行更快；
IN的最大优点是可以包含其他SELECT语句，使得能够更动态地建立WHERE子句；

5，NOT操作符：

NOT

否定它之后所跟的任何条件。

mysql> # 列出1002和1003之外的供应商生产的产品select prod_name,prod_price from productswhere vend_id not in (1002,1003) order by prod_name;+--------------+------------+| prod_name    | prod_price |+--------------+------------+| .5 ton anvil | 5.99       || 1 ton anvil  | 9.99       || 2 ton anvil  | 14.99      || JetPack 1000 | 35.00      || JetPack 2000 | 55.00      |+--------------+------------+5 rows in set (0.09 sec)

在与IN操作符联合使用时， NOT使找出与条件列表不匹配的行非常简单。
MySQL 支持使用 NOT 对 IN 、 BETWEEN 和EXISTS子句取反，这与多数其他DBMS允许使用NOT对各种条件取反有很大的差别。

三，用通配符进行过滤

前面介绍的所有操作符都是针对已知值进行过滤的，共同点是过滤中使用的值都是已知的。但是，这种过滤方法并不是任何时候都好用。利用通配符就可创建比较特定数据的搜索模式。

1，要使用LIKE操作符：

为在搜索子句中使用通配符，必须使用

LIKE

操作符。 LIKE指示MySQL，后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较。

2，百分号（ %）通配符：

表示任何字符出现任意次数。

mysql> # 找到所有以词jet起头的产品select prod_id,prod_name from products where prod_name like \"jet%\";+---------+--------------+| prod_id | prod_name    |+---------+--------------+| JP1000  | JetPack 1000 || JP2000  | JetPack 2000 |+---------+--------------+2 rows in set (0.07 sec)

根据MySQL的配置方式，搜索可以是区分大小写的。

通配符可在搜索模式中任意位置使用，并且可以使用多个通配符：

mysql> # 比如下方出现在头尾两处 ，匹配任意位置包含文本anvil的值select prod_id,prod_name from products where prod_name like \"%anvil%\";+---------+--------------+| prod_id | prod_name    |+---------+--------------+| ANV01   | .5 ton anvil || ANV02   | 1 ton anvil  || ANV03   | 2 ton anvil  |+---------+--------------+3 rows in set (0.03 sec)

通配符也可以出现在搜索模式的中间，虽然这样做不太有用：

mysql> # 比如下方出现在搜索模式的中间，匹配所有以s开头e结尾的值select prod_name from products where prod_name like \"s%e\";+-----------+| prod_name |+-----------+| Safe      |+-----------+1 row in set (0.03 sec)

除了一个或多个字符外， %还能匹配0个字符。 %代表搜索模式中给定位置的0个、 1个或多个字符。
尾空格可能会干扰通配符匹配，解决这个问题的一个简单的办法是在搜索模式最后附加一个%。一个更好的办法是使用函数去掉首尾空格。
虽然似乎%通配符可以匹配任何东西，但有一个例外，即NULL。即使是WHERE prod_name LIKE \’%\’也不能匹配用值NULL作为产品名的行。

3，下划线（_）通配符：

下划线

只匹配单个字符。

mysql> # 下划线 _ 通配符 ：匹配一个字符，不能多不能少select prod_id,prod_name from productswhere prod_name like \"_ ton anvil\";+---------+-------------+| prod_id | prod_name   |+---------+-------------+| ANV02   | 1 ton anvil || ANV03   | 2 ton anvil |+---------+-------------+2 rows in set (0.04 sec)

4，使用通配符的技巧：

通配符搜索的处理一般要比前面讨论的其他搜索所花时间更长。这里给出一
些使用通配符要记住的技巧：

不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的。
仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。

四，WHERE子句内使用正则表达式

对于基本的过滤（或者甚至是某些不那么基本的过滤），用匹配、比较和通配操作符寻找数据这样就足够了。但随着过滤条件的复杂性的增加， HERE子句本身的复杂性也有必要增加。
这也就是

正则表达式

变得有用的地方。正则表达式是用来匹配文本的特殊的串（字符集合）。

1，基本字符匹配：

mysql> select prod_name from products where prod_name regexp \"1000\";+--------------+| prod_name    |+--------------+| JetPack 1000 |+--------------+1 row in set (0.15 sec)mysql> select prod_name from products where prod_name regexp \".000\";+--------------+| prod_name    |+--------------+| JetPack 1000 || JetPack 2000 |+--------------+2 rows in set (0.07 sec)

REGEXP后所跟的东西作为正则表达式。
MySQL中的正则表达式匹配（自版本3.23.4后）不区分大小写。为区分大小写，可使用BINARY关键字，如WHERE prod_name REGEXPBINARY ‘JetPack .000’。

2，进行OR匹配：

使用 |

mysql> -- 正则表达式的OR操作符： |select prod_name from products where prod_name regexp \"1000|2000\" order by prod_name;+--------------+| prod_name    |+--------------+| JetPack 1000 || JetPack 2000 |+--------------+2 rows in set (0.04 sec)

可以给出两个以上的OR条件。例如，\’1000 | 2000 | 3000’将匹配1000或2000或3000。

3，匹配几个字符之一：

使用[ ]

mysql> select prod_name from products where prod_name regexp \'[123] Ton\' order by prod_name;  # [123]匹配单一字符：1或2或3+-------------+| prod_name   |+-------------+| 1 ton anvil || 2 ton anvil |+-------------+2 rows in set (0.03 sec)mysql> select prod_name from products where prod_name regexp \'[1|2|3] Ton\' order by prod_name;  # [1|2|3]同[123]，匹配单一字符：1或2或3+-------------+| prod_name   |+-------------+| 1 ton anvil || 2 ton anvil |+-------------+2 rows in set (0.04 sec)mysql> select prod_name from products where prod_name regexp \'[^123]\' order by prod_name;  # 取反+----------------+| prod_name      |+----------------+| .5 ton anvil   || 1 ton anvil    || 2 ton anvil    || Bird seed      || Carrots        || Detonator      || Fuses          || JetPack 1000   || JetPack 2000   || Oil can        || Safe           || Sling          || TNT (1 stick)  || TNT (5 sticks) |+----------------+14 rows in set (0.03 sec)

4，匹配范围：

集合可用来定义要匹配的一个或多个字符。

mysql> select prod_name from products where prod_name regexp \'[1-5] Ton\' order by prod_name;  # [1-5]匹配1,2,3,4,5+--------------+| prod_name    |+--------------+| .5 ton anvil || 1 ton anvil  || 2 ton anvil  |+--------------+3 rows in set (0.03 sec)

5，匹配特殊字符：
为了匹配特殊字符，必须用\\为前导。 \\-表示查找-， \\.表示查找.。
\\也用来引用元字符。为了匹配反斜杠（ \\）字符本身，需要使用\\\\。

mysql> select vend_name from vendors where vend_name regexp \"\\\\.\" order by vend_name; # ‘\\\\.\'匹配字符.+--------------+| vend_name    |+--------------+| Furball Inc. |+--------------+1 row in set (0.12 sec)

6，匹配字符类：

可以使用预定义的字符集，称为字符类（ character class）。
[:alnum:] 任意字母和数字（同[a-zA-Z0-9]）
[:alpha:] 任意字符（同[a-zA-Z]）
[:blank:] 空格和制表（同[\\t]）
[:cntrl:] ASCII控制字符（ ASCII 0到31和127）
[:digit:] 任意数字（同[0-9]）
[:graph:] 与[:print:]相同，但不包括空格
[:lower:] 任意小写字母（同[a-z]）
[:print:] 任意可打印字符
[:punct:] 既不在[:alnum:]又不在[:cntrl:]中的任意字符
[:space:] 包括空格在内的任意空白字符（同[\\f\\n\\r\\t\\v]）
[:upper:] 任意大写字母（同[A-Z]）
[:xdigit:] 任意十六进制数字（同[a-fA-F0-9]）

mysql> select prod_name from products where prod_name regexp \'[:digit:]\' order by prod_name; #[:digit:]匹配任意数字+----------------+| prod_name      |+----------------+| .5 ton anvil   || 1 ton anvil    || 2 ton anvil    || JetPack 1000   || JetPack 2000   || TNT (1 stick)  || TNT (5 sticks) |+----------------+7 rows in set (0.03 sec)

7，匹配多个实例：
有时需要对匹配的数目进行更强的控制，用正则表达式重复元字符来完成。

0个或多个匹配

1个或多个匹配（等于{1,}）
? 0个或1个匹配（等于{0,1}）
{n} 指定数目的匹配
{n,} 不少于指定数目的匹配
{n,m} 匹配数目的范围（ m不超过255）

mysql> select prod_name from products where prod_name regexp \'\\\\([0-9] sticks?\\\\)\'order by prod_name;  # 返回了\'TNT (1 stick)\'和\'TNT (5 sticks)\'+----------------+| prod_name      |+----------------+| TNT (1 stick)  || TNT (5 sticks) |+----------------+2 rows in set (0.04 sec)mysql> select prod_name from products where prod_name regexp \'[[:digit:]]{4}\'order by prod_name;  # [[:digit:]]{4}匹配连在一起的任意4位数字+--------------+| prod_name    |+--------------+| JetPack 1000 || JetPack 2000 |+--------------+2 rows in set (0.03 sec)

7，定位符：

匹配特定位置的文本：
^ 文本的开始
$ 文本的结尾
[[:<:]] 词的开始
[[:>:]] 词的结尾
例如，如果你想找出以一个数（包括以小数点开始的数）开始的所有产品，怎么办？简单搜索[0-9\\.]（或[[:digit:]\\.]）不行，因为它将在文本内任意位置查找匹配。解决办法是使用^定位符，如下所示：

mysql> select prod_name from products where prod_name regexp \'^[0-9\\\\.]\' order by prod_name; #找出以一个数（包括以小数点开始的数）开始的所有产品+--------------+| prod_name    |+--------------+| .5 ton anvil || 1 ton anvil  || 2 ton anvil  |+--------------+3 rows in set (0.03 sec)

^匹配串的开始。因此，^[0-9\\\\.]只在.或任意数字为串中第一个字符时才匹配它们。
^有两种用法。在集合中（用[和]定义），用它来否定该集合，否则，用来指串的开始处。
通过用^开始每个表达式，用$结束每个表达式。