MySQL 搜索语句--过滤数据

MySQL使用SELECT语句的WHERE子句指定搜索条件。

使用WHERE子句

数据库表一般包含大量的数据，有时我们可能只需要检索表中的一部分数据，只检索所需数据需要指定搜索条件（search criteria），搜索条件也称为过滤条件（filter condition）。

WHERE 子句在表名（FROM子句)之后给出。

SELECT name, priceFROM productsWHERE price = 2.50;

这条语句从products表中检索两个列，但是是不返回所有行，只返回price值为2.50的行。如下所示:

+---------+-----------+|  name   |    price  |+---------+-----------+| Carrots |    2.50   || TNT     |    2.50   |+---------+-----------+

在同时使用ORDERBY和WHERE子句时，应该让ORDERBY位于WHERE之后，否则会产生错误

WHERE子句操作符

--------------------------------------------    操作符                说明--------------------------------------------      =                   等于      <>                  不等于      !=                  不等于      <                   小于      <=                  小于等于      >                   大于      >=                  大于等于      BETWEEN             在指定的两个值之间----------------------------------------------

检查单个值

SELECT name, price FROM productsWHERE name='fuses';

输出如下：

+---------+-----------+|  name   |    price  |+---------+-----------+| Fuses   |    3.42   |+---------+-----------+

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

不匹配检查

以下例子列出不是由供应商1003制造的所有产品

SELECT id, name FROM productsWHERE id <> 1003;

输出如下：

+---------+-----------+|  id     |    name   |+---------+-----------+| 1001    |    Fuses1 || 1002    |    Fuses2 || 1004    |    Fuses4 || 1005    |    Fuses5 |+---------+-----------+

PS：在使用WHERE子句时，单引号用来限定字符串。如果将值与串类型的列进行比较，则需要限定引号；如果与数值列进行比较的值不用引号
以下语句和上面的例子相同

SELECT id, name FROM productsWHERE id != 1003;

范围值检查

为了检查某个范围的值，可以使用BETWEEN操作符。
下面的例子说明如何使用BETWEEN操作符，它检索价格在5美元和10美元之间的所有产品。

SELECT name, price FROM productsWHERE price BETWEEN 5 AND 10;

结果如下所示：

+---------+-----------+|  name   |    price  |+---------+-----------+| Fuses1  |    5.42   || Fuses4  |    6.12   || Fuses3  |    7.92   || Fuses2  |    9.42   |+---------+-----------+

PS：在使用BETWEEN时，必须指定两个值，这两个值必须用AND关键字分隔。

空值检查

NULL 无值（no value），他与字段包含0，空字符串或仅仅包含空格不同。
SELECT语句有一个特殊的WHERE子句 IS NULL，它用来检查具有NULL值得列

SELECT nameFROM productsWHERE price IS NULL;

这条语句返回没有价格（空price字段，不是价格为0）的所有产品，由于表中没有这样的行所以没有返回行

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

数据过滤

组合WHERE子句

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

SELECT id, price, nameFROM products WHERE id=1003 AND price<=10;

此SQL语句检索由供应商1003制造且价格小于等于10美元的所有产品的名称和价格
ADN 用在WHERE子句中的关键字，用来指示检索满足所有给定条件的行。

OR

SELECT id, price, nameFROM products WHERE id=1003 OR id=1002;

次SQL语句检索由任一指定供应商创造的所有产品的产品的名字和价格。
OR WHERE 子句中使用的关键字，用来表示检索匹配任一给定条件的行。

计算次序

WHERE 可以包含任意数目的AND和OR操作符。允许两者结合以进行复杂和高级的过滤。
但是由于AND和OR的优先级不同，AND的优先级要高于OR的优先级，所以在同时使用AND和OR时，要给OR加括号
例如：

SELECT id, price, nameFROM products WHERE (id=1003 OR id=1002) AND price >= 10;

IN 操作符

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

SELECT id, price, nameFROM products WHERE id IN (1002, 1003)ORDER BY name;

次语句检索供应商1002和1003创造的所有产品。
IN操作符后跟由逗号分隔的合法值清单，整个清单必须括在圆括号中。
IN 操作符的优点:

• 在使用长的合法选项清单时，IN操作符的语法更清楚且直观。
• 在使用IN时，计算的次序更容易管理（因为使用的操作符更少）
• IN操作符一般比OR操作符清单执行更快
• IN的最大优点是可以包含其他SELECT语句，使得能够更动态的建立WHERE子句。
  IN WHERE子句中用来指定要匹配的清单的关键字，功能与OR相当。

NOT 操作符

NOT WHERE 子句中用来否定后跟条件的关键字。
下面的例子，列出除了1002和1003之外的所有供应商制造的产品。

SELECT id, price, nameFROM products WHERE id NOT IN (1002, 1003)ORDER BY name;

LIKE 操作符

百分号（%）通配符

最常使用的通配符是百分号（%），在搜索串中，%表示任何字符出现任意次数。例如，为了找出所有以词jet起头的产品，可以使用SELECT语句：

SELECT id, nameFROM productsWHERE name LIKE 'jet%';

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

SELECT id, nameFROM productsWHERE name LIKE '%anvil%';

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

重要的是要注意到，除了一个或多个字符外，%还能匹配0个字符。%代表搜索模式中给定位置的0个，1个，或者多个字符。
注意尾空格 尾空格可能会干扰通配符。例如，在保存词anvil时，如果它后面有一个或多个空格，则子句WHERE name LIKE ‘%anvil’将不会匹配他们，因为在最后的l后面有多余的字符。解决这个问题的一个简单办法是在搜索模式最后附加一个%或者使用函数
%不能匹配NULL

下划线（_）通配符

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

SELECT id, nameFROM productsWHERE name LIKE '_ ton anvil';

显示效果如下：

+---------+-----------------+|  id     |    name         |+---------+-----------------+| 1001    |    .5 ton anvil || 1002    |     1 ton anvil || 1004    |     2 ton anvil |+---------+-----------------+

与%能匹配0个字符不一样，_总是匹配一个字符，不能多也不能少