Running Noob

Do Something Meaningful

0%

mysql过滤数据

发表于 更新于 分类于
本文字数： 10k 阅读时长 ≈ 9 分钟
  • 过滤数据

第6章 过滤数据

6.1 ★使用 WHERE 子句

  • 数据库表一般包含大量的数据，很少需要检索表中所有行。通常只会根据特定操作或报告的需要提取表数据的子集。只检索所需数据需要指定搜索条件（search criteria），搜索条件也称为过滤条件（filter condition）。

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

    SELECT prod_id, prod_price, prod_name FROM products WHERE prod_price = 2.50;

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

  • WHERE子句的位置：在同时使用ORDER BY和WHERE子句时，应该让ORDER BY位于WHERE之后，否则将会产生错误。

    SELECT prod_id, prod_price, prod_name FROM products WHERE prod_price = 2.50 ORDER BY prod_name DESC;

6.2 WHERE子句操作符

  • 在 WHERE 子句中经常使用的运算符：
操作符说明
=等于
<>不等于
!=不等于
<小于
<=小于等于
>大于
>=大于等于
BETWEEN …AND…在指定的两个值之间
IS NULL是否为空
AND多个条件同时成立
OR多个条件任一成立
NOT不成立
IN(set)显示在 set 列表中的值，如 in(100, 200)
LIKE ‘pattern’模糊查询

6.2.1 检索单个值

  • SELECT prod_id, prod_price, prod_name FROM products WHERE prod_name = 'fuses';

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

  • 列出价格小于10美元的所有产品：

    SELECT prod_id, prod_price, prod_name FROM products WHERE prod_price < 10;

6.2.2 不匹配检索

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

    SELECT prod_id, prod_price, prod_name FROM products WHERE vend_id <> 1003; 或者

    SELECT prod_id, prod_price, prod_name FROM products WHERE vend_id != 1003;

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

6.2.3 范围值检索BETWEEN

  • 为了检索某个范围的值，可使用BETWEEN操作符。其语法与其他WHERE子句的操作符稍有不同，因为它需要两个值，即范围的开始值和结束值。

    下面的例子说明如何使用BETWEEN操作符，它检索价格在5美元和10美元之间的所有产品：

    SELECT prod_id, prod_name, prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;

  • 从这个例子中可以看到，在使用BETWEEN时，必须指定两个值——所需范围的低端值和高端值。这两个值必须用AND关键字分隔。BETWEEN匹配范围中所有的值，包括指定的开始值和结束值。

6.2.4 空值检索IS NULL

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

    NULL 无值（no value）：它与字段包含0、空字符串或仅仅包含空格不同。

  • SELECT语句有一个特殊的WHERE子句，可用来检索具有NULL值的列，即若指定的列中的某几行的值为NULL，则这几行被检索出来。这个WHERE子句就是IS NULL子句。其语法如下：

    SELECT prod_name FROM products WHERE prod_price IS NULL;

    这条语句返回没有价格（空prod_price字段，不是价格为0）的所有产品，由于表中没有这样的行，所以没有返回数据。但是，customers表确实包含有具有空值的列，如果在文件中没有某位顾客的电子邮件地址，则cust_email列将包含NULL值：

    SELECT cust_id FROM customers WHERE cust_email IS NULL;

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

    例如：SELECT cust_id FROM customers WHERE cust_email != '';，在 customers 表中有五行数据，其中两行数据的 cust_email 为 null，但是检索时不返回，只返回了三行数据。

第7章 数据过滤

7.1 组合 WHERE 子句

  • 第6章中介绍的所有WHERE子句在过滤数据时使用的都是单一的条件。为了进行更强的过滤控制，MySQL允许给出多个WHERE子句。这些子句可以两种方式使用：以AND子句的方式或OR子句的方式使用。

  • 操作符（operator）：用来联结或改变WHERE子句中的子句的关键字。也称为逻辑操作符（logical operator）。

7.1.1 AND 操作符

  • 为了通过不止一个列进行过滤，可使用AND操作符给WHERE子句附加条件。下面的代码给出了一个例子：

    SELECT prod_id, prod_price, prod_name FROM products WHERE vend_id = 1003 AND prod_price <=10;

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

  • AND：用在WHERE子句中的关键字，用来指示检索满足所有给定条件的行。

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

7.1.2 OR 操作符

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

    SELECT prod_id, prod_price, prod_name FROM products WHERE vend_id = 1002 OR vend_id = 1003;

    此SQL语句检索由任一个指定供应商制造的所有产品的产品名和价格。OR操作符告诉DBMS匹配任一条件而不是同时匹配两个条件。如果这里使用的是AND操作符，则没有数据返回（此时创建的WHERE子句不会检索到匹配的产品）。

  • OR：WHERE子句中使用的关键字，用来表示检索匹配任一给定条件的行。

7.1.3 计算次序

  • WHERE可包含任意数目的AND和OR操作符。允许两者结合以进行复杂和高级的过滤。但是，组合AND和OR带来了一个有趣的问题——优先级的问题。SQL（像多数语言一样）在处理OR操作符前，优先处理AND操作符。此问题的解决方法是使用圆括号明确地分组相应的操作符。

    SELECT prod_id, prod_price, prod_name, vend_id FROM products WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >=10; -> SQL语句的含义是选择由供应商1002或1003制造的且价格都在10美元（含）以上的任何产品。

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

7.2 IN 操作符

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

    SELECT prod_id, prod_price, prod_name FROM products WHERE vend_id IN (1002,1003) AND prod_price >= 10; 这一句和前面一句的含义与作用相同。此SELECT语句检索由供应商1002或1003制造的且价格都在10美元（含）以上的任何产品。IN操作符后跟由逗号分隔的合法值清单，整个清单必须括在圆括号中。

  • 注意：IN操作符完成与OR相同的功能，但是更简短。

  • IN：WHERE子句中用来指定要匹配值的清单的关键字，功能与OR相当。

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

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

7.3 NOT 操作符

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

  • NOT：WHERE子句中用来否定后跟条件的关键字。

  • 下面的例子说明NOT的使用。为了列出除1002和1003之外的所有供应商制造的产品，可编写如下的代码：

    SELECT prod_id, prod_name, prod_price FROM products WHERE vend_id NOT IN (1002,1003);

    这里的NOT否定跟在它之后的条件，因此，MySQL不是匹配1002和 1003 的 vend_id ，而是匹配 1002 和 1003 之外供应商的vend_id。

  • 为什么使用NOT？对于简单的WHERE子句，使用NOT确实没有什么优势。但在更复杂的子句中，NOT是非常有用的。例如，在与IN操作符联合使用时，NOT使得找出与条件列表不匹配的行非常简单。

第8章 用通配符进行过滤

8.1 LIKE 操作符

  • 通配符（wildcard）：用来匹配 “值的一部分” 的特殊字符。

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

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

    使用LIKE，能够查找包含特殊值或部分值的行（不管这些值位于列内什么位置）。

8.1.1 百分号（%）通配符

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

    例如，为了找出所有以词jet起头的产品，可使用以下SELECT语句：

    SELECT prod_id, prod_name FROM products WHERE prod_name LIKE 'jet%'; 在执行这条子句时，将检索任意以jet起头的词。%告诉MySQL接受jet之后的任意字符，不管它有多少字符。

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

  • 通配符可在搜索模式中任意位置使用，并且可以使用多个通配符。下面的例子使用两个通配符，它们位于模式的两端：

    SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '%anvil%';

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

  • 注意尾空格：尾空格可能会干扰通配符匹配。例如，在保存词anvil 时，如果它后面有一个或多个空格，则子句WHERE prod_name LIKE ‘%anvil’ 将不会匹配它们，因为在最后的 l 后有多余的字符。解决这个问题的一个简单的办法是在搜索模式最后附加一个%。一个更好的办法是使用函数（第11章将会介绍）去掉首尾空格。例如：

    SELECT prod_name FROM products WHERE Trim(prod_name) LIKE '_ ton%' ORDER BY prod_name;

  • 注意：除了一个或多个字符外，**%还能匹配0个字符。%代表搜索模式中给定位置的0个、1个或多个字符。**

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

8.1.2 下划线（_）通配符

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

    SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '_ ton anvil';

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

8.2 使用通配符的技巧

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

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

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

★第9章 用正则表达式进行搜索

9.1 正则表达式介绍

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

9.2 使用MySQL正则表达式

  • 正则表达式与MySQL有何关系？已经说过，正则表达式的作用是匹配文本，将一个模式（正则表达式）与一个文本串进行比较。MySQL用WHERE子句对正则表达式提供了初步的支持，允许你指定正则表达式，过滤SELECT检索出的数据。
  • 注意：MySQL仅支持多数正则表达式实现的一个很小的子集。

9.2.1 基本字符匹配

  • 我们从一个非常简单的例子开始。下面的语句检索列prod_name包含文本1000的所有行：

    SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name;

    除关键字LIKE被REGEXP替代外，这条语句看上去非常像使用LIKE的语句（第8章）。它告诉MySQL：REGEXP后所跟的东西作为正则表达式（与文字正文1000匹配的一个正则表达式）处理。

  • 为什么要费力地使用正则表达式？在刚才的例子中，正则表达式确实没有带来太多好处（可能还会降低性能），不过，请考虑下面的例子：SELECT prod_name FROM products WHERE prod_name REGEXP '.000' ORDER BY prod_name;

    这里使用了正则表达式 .000 。**. 是正则表达式语言中一个特殊的字符。它表示匹配任意一个字符**，因此，1000和2000都匹配且返回。

  • LIKE与REGEXP的区别：在LIKE和REGEXP之间有一个重要的差别。请看以下两条语句：

    SELECT prod_name FROM products WHERE prod_name LIKE '1000' ORDER BY prod_name;

    SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name;

    如果执行上述两条语句，会发现第一条语句不返回数据，而第二条语句返回一行。为什么？正如第8章所述，LIKE匹配整个列。如果被匹配的文本在列值中出现，LIKE将不会找到它，相应的行也不被返回（除非使用通配符）。而REGEXP在列值内进行匹配，如果被匹配的文本在列值中出现，REGEXP将会找到它，相应的行将被返回。这是一个非常重要的差别。

    那么，REGEXP能不能用来匹配整个列值（从而起与LIKE相同的作用）？答案是肯定的，使用^和$定位符（anchor）即可。

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

9.2.2 进行OR匹配

  • 为搜索两个串之一（或者为这个串，或者为另一个串），使用 | ，如下所示：

    SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000' ORDER BY prod_name;

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

9.2.3 匹配几个字符之一

  • . 匹配任何单一字符。但是，如果你只想匹配特定的字符，怎么办？可通过指定一组用 [ 和 ] 括起来的字符来完成，如下所示：

    SELECT prod_name FROM products WHERE prod_name REGEXP '[123] ton' ORDER BY prod_name;

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

  • 正如所见，**[] 是另一种形式的OR语句**。事实上，正则表达式[123]Ton为[1|2|3]Ton的缩写，也可以使用后者。但是，需要用[]来定义OR语句查找什么。为更好地理解这一点，请看下面的例子：

    SELECT prod_name FROM products WHERE prod_name REGEXP '1|2|3 ton' ORDER BY prod_name;

    两个要求的行被检索出来，但还检索出了另外3行。之所以这样是由于MySQL假定你的意思是’1’或’2’或’3 ton’。除非把字符|括在一个集合中，否则它将应用于整个串。

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

★9.2.4 匹配范围

  • ★集合可用来定义要匹配的一个或多个字符。例如，下面的集合将匹配数字0到9：[0123456789] ，为简化这种类型的集合，可使用 - 来定义一个范围。下面的式子功能上等同于上述数字列表：[0-9] ,范围不限于完整的集合，[1-3]和[6-9]也是合法的范围。此外，范围不一定只是数值的，**[a-z]匹配任意字母字符**。

    ★匹配数字0到9：[0-9]；匹配任意字母字符：[a-z]。

★9.2.5 匹配特殊字符

  • 正则表达式语言由具有特定含义的特殊字符构成。我们已经看到 . 、[]、 | 和 - 等，还有其他一些字符。请问，如果你需要匹配这些字符，应该怎么办呢？例如，如果要找出包含 . 字符的值，怎样搜索？请看下面的例子：

    SELECT vend_name FROM vendors WHERE vend_name REGEXP '\\.' ORDER BY vend_name;

    为了匹配特殊字符，必须用\为前导。\- 表示查找 - ，\. 表示查找 . 。 这种处理就是所谓的转义（escaping），正则表达式内具有特殊意义的所有字符都必须以这种方式转义。这包括 . 、| 、[] 以及迄今为止使用过的其他特殊字符。

  • \ 也用来引用元字符（具有特殊含义的字符），如表9-1所列。

元字符说明
\f换页
\n换行
\r回车
\t制表
\v纵向制表

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

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

★9.2.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]）

★9.2.7 匹配多个实例

  • 目前为止使用的所有正则表达式都试图匹配单次出现。如果存在一个匹配，该行被检索出来，如果不存在，检索不出任何行。但有时需要对匹配的数目进行更强的控制。例如，你可能需要寻找所有的数，不管数中包含多少数字，或者你可能想寻找一个单词并且还能够适应一个尾随的s（如果存在），等等。这可以用下表列出的正则表达式重复元字符来完成。
元字符说明
*0个或多个匹配
+1个或多个匹配（等于{1,}）
?0个或1个匹配（等于{0,1}）
{n}指定数目的匹配
{n,}不少于指定数目的匹配
{n,m}匹配数目的范围（m不超过255）

下面举几个例子。

  • SELECT prod_name FROM products WHERE prod_name REGEXP '\\([0-9] sticks?\\)' ORDER BY prod_name;

    正则表达式 \([0-9] sticks?\) 说明：\( 匹配 (，\) 匹配 )，[0-9]匹配任意数字，sticks? 匹配 stick 和 sticks（s后的?使s可选，因为?匹配它 前面 的任何字符的0次或1次出现），如果没有?，匹配stick和sticks会非常困难。

  • SELECT prod_name FROM products WHERE prod_name REGEXP '[[:digit:]]{4}' ORDER BY prod_name;

    如前所述，[:digit:] 匹配任意数字，因而它为数字的一个集合。{4}确切地要求它前面的字符（任意数字）出现4次，所以[[:digit:]]{4}匹配连在一起的任意4位数字。

    注意：这里匹配字符类指的是形式，使用时一般在[]中使用 ，所以应为 [[:digit:]] ，而不只是 [:digit:]

★9.2.8 定位符

  • 目前为止的所有例子都是匹配一个串中任意位置的文本。为了匹配特定位置的文本，需要使用下表中列出的定位符。
元字符说明
^文本的开始
$文本的结尾
[[:<:]]词的开始
[[:>:]]词的结尾

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

    SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]' ORDER BY prod_name;

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

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

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

  • 简单的正则表达式测试：可以在不使用数据库表的情况下用SELECT来测试正则表达式。REGEXP检查总是返回0（没有匹配）或1（匹配）。可以用带文字串的REGEXP来测试表达式，并试验它们。相应的语法如下：SELECT 'hello' REGEXP '[0-9]'; 这个例子显然将返回0（因为文本hello中没有数字）。

---------------The End---------------