MySQL【过滤数据（where）】

使用WHERE子句

在SELECT语句中，数据根据WHERE子句中指定的搜索条件进行过滤。 WHERE子句在表名（FROM子句）之后给出

select prod_id,prod_price,prod_name
from products
where prod_price = 2.50

         

 这条语句从products表中检索两个列，但不返回所有行，只返 回prod_price值为2.50的行

WHERE子句操作符

检查单个值 

select prod_id,prod_price,prod_name
from products
where prod_name = 'fuses'

检查WHERE prod_name=‘fuses’语句，它返回prod_name的值 为Fuses的一行。MySQL在执行匹配时默认不区分大小写，所 以fuses与Fuses匹配。 

不匹配检查 

select vend_id,prod_name
from products
where vend_id <> 1003

何时使用引号 如果仔细观察上述WHERE子句中使用的条件， 会看到有的值括在单引号内（如前面使用的'fuses'），而有 的值未括起来。单引号用来限定字符串。如果将值与串类型的 列进行比较，则需要限定引号。用来与数值列进行比较的值不用引号。 

范围值检查 

为了检查某个范围的值，可使用BETWEEN操作符。

语法：where （需要检索的列） between 起始值 and 结束值

select prod_name,prod_price
from products
where prod_price between 5 and 10

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

空值检查

空值        在创建表时，表设计人员可以指定其中的列是否可以不包含值。在 一个列不包含值时，称其为包含空值NULL。

SELECT语句有一个特殊的WHERE子句，可用来检查具有NULL值的列。 这个WHERE子句就是IS NULL子句。

空值和0        两者是不同的，null和任何值进行运算或者比较结果都是null，从而可以得出null，不可以直接用等号运算符（=）直接与数值进行比较，但是MySQL专门用一个安全等于（<=>）使得null可以和数值进行直接比较。

select prod_name,prod_price
from products
where prod_price is null

没有返回任何数据，说明该表中没有为prod_price为null的数据 

组合WHERE子句

为了进行更强的过滤控制，MySQL允许给出多个WHERE子句。这些子 句可以两种方式使用：以AND子句的方式或OR子句的方式使用。

AND操作符

为了通过不止一个列进行过滤，可使用AND操作符给WHERE子句附加条件。

select prod_name,prod_price,prod_id
from products
where vend_id = 1003 and prod_price <= 10

此SQL语句检索由供应商1003制造且价格小于等于10美元的所 有产品的名称和价格。这条SELECT语句中的WHERE子句包含两 个条件，并且用AND关键字联结它们。AND指示DBMS只返回满足所有给 定条件的行。如果某个产品由供应商1003制造，但它的价格高于10美元， 则不检索它。类似，如果产品价格小于10美元，但不是由指定供应商制 造的也不被检索。

 上述例子中使用了只包含一个关键字AND的语句，把两个过滤条件组 合在一起。还可以添加多个过滤条件，每添加一条就要使用一个AND。

OR操作符

OR操作符与AND操作符不同，它指示MySQL检索匹配任一条件的行。

select prod_name,prod_price,vend_id
from products
where vend_id = 1002 or vend_id =1003

计算次序

WHERE可包含任意数目的AND和OR操作符。允许两者结合以进行复杂 和高级的过滤。

但是，组合AND和OR带来了一个有趣的问题。为了说明这个问题，来 看一个例子。假如需要列出价格为10美元（含）以上且由1002或1003制 造的所有产品。

错误❌示例

select prod_name,prod_price,vend_id
from products
where vend_id = 1002 or vend_id =1003 and prod_price >= 10

原因在于计算的次序。SQL（像多数语言一样）在处理OR操作符前，优先处理AND操 作符。当SQL看到上述WHERE子句时，它理解为由供应商1003制造的任何价格为10美元（含）以上的产品，或者由供应商1002制造的任何产品， 而不管其价格如何。换句话说，由于AND在计算次序中优先级更高，操作符被错误地组合了。

正确✔示例 

select prod_name,prod_price,vend_id
from products
where (vend_id = 1002 or vend_id =1003) and prod_price >= 10

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

IN操作符 

圆括号在WHERE子句中还有另外一种用法。IN操作符用来指定条件范 围，范围中的每个条件都可以进行匹配。IN取合法值的由逗号分隔的清 单，全都括在圆括号中。

select prod_name,prod_price,vend_id
from products
where vend_id in (1002,1003)
order by prod_name

in 和 or 具有相同的作用！！！

 为什么要使用IN操作符？其优点具体如下。

 在使用长的合法选项清单时，IN操作符的语法更清楚且更直观。

 在使用IN时，计算的次序更容易管理（因为使用的操作符更少）。

 IN操作符一般比OR操作符清单执行更快。

 IN的最大优点是可以包含其他SELECT语句，使得能够更动态地建 立WHERE子句。

NOT操作符

WHERE子句中的NOT操作符有且只有一个功能，那就是否定它之后所 跟的任何条件。

select prod_name,prod_price,vend_id
from products
where vend_id not in (1002,1003)
order by prod_name

MySQL中的NOT MySQL支持使用NOT对IN、BETWEEN和 EXISTS子句取反，这与多数其他DBMS允许使用NOT对各种条件 取反有很大的差别。 

用通配符进行过滤 

LIKE操作符

前面介绍的所有操作符都是针对已知值进行过滤的。不管是匹配一 个还是多个值，测试大于还是小于已知值，或者检查某个范围的值，共同点是过滤中使用的值都是已知的。但是，这种过滤方法并不是任何时候都好用。例如，怎样搜索产品名中包含文本anvil的所有产品？用简单的比较操作符肯定不行，必须使用通配符。利用通配符可创建比较特定数据的搜索模式。

通配符（wildcard【万用字符】） 用来匹配值的一部分的特殊字符。

搜索模式（search pattern） 由字面值、通配符或两者组合构 成的搜索条件。 

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

百分号（%）通配符

最常使用的通配符是百分号（%）。在搜索串中，%表示任何字符出现 任意次数。

select prod_name,prod_price
from products
where prod_name like 'jet%'

此例子使用了搜索模式'jet%'。在执行这条子句时，将检索任 意以jet起头的词。%告诉MySQL接受jet之后的任意字符，不 管它有多少字符。 

区分大小写 根据MySQL的配置方式，搜索可以是区分大小 写的。如果区分大小写，'jet%'与JetPack 1000将不匹配。 

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

select prod_name,prod_price
from products
where prod_name like '%anvil%'

 搜索模式'%anvil%'表示匹配任何位置包含文本anvil的值，而 不论它之前或之后出现什么字符。

注意NULL 虽然似乎%通配符可以匹配任何东西，但有一个例 外，即NULL。即使是WHERE prod_name LIKE '%'也不能匹配 用值NULL作为产品名的行。 

下划线（_）通配符 

另一个有用的通配符是下划线（_）。下划线的用途与%一样，但下划 线只匹配单个字符而不是多个字符。

select prod_name,prod_price
from products
where prod_name like '_ ton anvil'

         

正如所见，MySQL的通配符很有用。但这种功能是有代价的：通配 符搜索的处理一般要比前面讨论的其他搜索所花时间更长。

 不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。

 在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的。

 仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。

正则表达式进行搜索

正则表达式是用来匹配文本 的特殊的串（字符集合）。如果你想从一个文本文件中提取电话号码，可 以使用正则表达式。如果你需要查找名字中间有数字的所有文件，可以 使用一个正则表达式。如果你想在一个文本块中找到所有重复的单词， 可以使用一个正则表达式。如果你想替换一个页面中的所有URL为这些 URL的实际HTML链接，也可以使用一个正则表达式（对于最后这个例子， 或者是两个正则表达式）。

使用MySQL正则表达式 

正则表达式的作 用是匹配文本，将一个模式（正则表达式）与一个文本串进行比较。MySQL 用WHERE子句对正则表达式提供了初步的支持，允许你指定正则表达式， 过滤SELECT检索出的数据。

基本字符匹配 

语法：where (需要查找的列) REGEXP ‘匹配字符串’【用法和like类似】

select prod_name,prod_price
from products
where prod_name regexp '1000'

select prod_name
from products
where prod_name regexp '.000'

 .是正则表达式语言中一个特殊的字符。

匹配不区分大小写  MySQL中的正则表达式匹配（自版本 3.23.4后）不区分大小写（即，大写和小写都匹配）。为区分大小写，可使用BINARY关键字，如WHERE prod_name REGEXP BINARY 'JetPack .000'。 

进行OR匹配 

为搜索两个串之一（或者为这个串，或者为另一个串），使用 |（或）

select prod_name,prod_price
from products
where prod_name regexp '1000|2000';

语句中使用了正则表达式1000|2000。|为正则表达式的OR操作 符。它表示匹配其中之一，因此1000和2000都匹配并返回。【用了通配符的or】 

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

匹配几个字符之一 

如果你只想匹配特定的字符，怎么办？ 可通过指定一组用[和]括起来的字符来完成

select prod_name,prod_price
from products
where prod_name regexp '[123] Ton'

使用了正则表达式[123] Ton，[123]定义一组字符，它 的意思是匹配1或2或3，因此，1 ton和2 ton都匹配且返回（没 有3 ton）。 

正如所见，[]是另一种形式的OR语句。事实上，正则表达式[123]Ton 为[1|2|3]Ton的缩写，也可以使用后者。但是，需要用[]来定义OR语句 查找什么。 

select prod_name,prod_price
from products
where prod_name regexp '1|2|3Ton'

结果和意料的不同，因为这里的MySQL将理解作‘1’或者‘2’或者‘3 Ton’,除非把字符|括在一个集合中，否则它将应用于整个串。 

字符集合也可以被否定，即，它们将匹配除指定字符外的任何东西。 为否定一个字符集，在集合的开始处放置一个^即可。因此，尽管[123] 匹配字符1、2或3，但[^123]却匹配除这些字符外的任何东西。

select prod_name,prod_price
from products
where prod_name regexp '[^123] Ton'

匹配范围

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

[123456789]

为简化这种类型的集合，可使用-来定义一个范围。下面的式子功能 上等同于上述数字列表：

[1-9]

范围不限于完整的集合，[1-3]和[6-9]也是合法的范围。此外，范 围不一定只是数值的，[a-z]匹配任意字母字符。

select prod_name,prod_price
from products
where prod_name regexp '[1-5] Ton'

 这里使用正则表达式[1-5] Ton。[1-5]定义了一个范围，这个 表达式意思是匹配1到5，因此返回3个匹配行。由于5 ton匹配， 所以返回.5 ton。

匹配特殊字符 

如果要找出包含.字符的值，怎样搜索？

select prod_name,prod_price
from products
where prod_name regexp '.'

这并不是期望的输出，.匹配任意字符，因此每个行都被检索出来。

为了匹配特殊字符，必须用\\为前导。\\-表示查找-，\\.表示查找.。 

select prod_name,prod_price
from products
where prod_name regexp '\\.'

 这种处理 就是所谓的转义（escaping），正则表达式内具有特殊意义的所 有字符都必须以这种方式转义。

\\也用来引用元字符（具有特殊含义的字符。

 匹配\ 为了匹配反斜杠（\）字符本身，需要使用\\\。

\或\\?  多数正则表达式实现使用单个反斜杠转义特殊字符， 以便能使用这些字符本身。但MySQL要求两个反斜杠（MySQL 自己解释一个，正则表达式库解释另一个）

匹配字符类

匹配多个实例

你可能需要寻找所有的数，不管 数中包含多少数字，或者你可能想寻找一个单词并且还能够适应一个尾 随的s（如果存在），等等。

select prod_name
from products
where prod_name regexp '\\([0-9] sticks?\\)'

 

正则表达式\\([0-9] sticks?\\)需要解说一下。\\(匹配)， [0-9]匹配任意数字（这个例子中为1和5），sticks?匹配stick 和sticks（s后的?使s可选，因为?匹配它前面的任何字符的0次或1次出 现），\\)匹配)。没有?，匹配stick和sticks会非常困难。 ​​​​​​​

select prod_name
from products
where prod_name regexp '[[:digit:]]{4}'

[:digit:]匹配任意数字，因而它为数字的一个集合。{4}确切地要求它前面的字符（任意数字）出现4次，所以 [[:digit:]]{4}匹配连在一起的任意4位数字。 需要注意的是，在使用正则表达式时，编写某个特殊的表达式几乎 总是有不止一种方法。 

select prod_name
from products
where prod_name regexp '[0-9][0-9][0-9][0-9]'

 这样写的代码有同样的效果。

定位符

前面都是匹配一个串中的任意位置的文本，为了匹配特殊位置的文本，此时需要我们使用定位符

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

select prod_name
from products
where prod_name regexp '^[0-9\\.]';

 

^匹配串的开始。因此，^[0-9\\.]只在.或任意数字为串中第 一个字符时才匹配它们。没有^，则还要多检索出4个别的行（那 些中间有数字的行）。

使用[0-9.]的效果也一样，所以这块地方，我也不是很清楚\\是用来干嘛的，有知道的同学可以在和我交流交流

^的双重用途  ^有两种用法。在集合中（用[和]定义），用它 来否定该集合，否则，用来指串的开始处。 

使REGEXP起类似LIKE的作用  前面说过，LIKE和REGEXP 的不同在于，LIKE匹配整个串而REGEXP匹配子串。利用定位符，通过用^开始每个表达式，用$结束每个表达式，可以使 REGEXP的作用与LIKE一样。