MySQL数据库的排序与过滤_第三篇（一万字非常详细）_mysql带过滤条件后排序不一致-CSDN博客

本文链接：https://blog.csdn.net/weixin_45395059/article/details/125030824

一、排序检索数据

1. 排序数据

正如前一篇所述，下面的SQL语句返回某个数据库表的单个列。但请
看其输出，并没有特定的顺序。
输入：SELECT prod_name FROM products;
输出：
其实，检索出的数据并不是以纯粹的随机顺序显示的。如果不排序，数据一般将以它在底层表中出现的顺序显示。如果不明确控制的话，不能（也不应该）依赖该排序顺序。关系数据库设计理论认为，如果不明确规定排序顺序，则不应该假定检索出的数据的顺序有意义。
子句（clause）： SQL语句由子句构成，有些子句是必需的，而有的是可选的。一个子句通常由一个关键字和所提供的数据组成。子句的例子有SELECT语句的FROM子句，我们在前一章看到过这个子句。
为了明确地排序用SELECT语句检索出的数据，可使用ORDER BY子句。ORDER BY子句取一个或多个列的名字，据此对输出进行排序。请看下面的例子：
输入：SELECT prod_name FROM products ORDER BY prod_name;
输出：
分析：这条语句除了指示MySQL对 prod_name列 以字母顺序排序数据 的ORDER BY子句外，其余与前面的语句相同。
注意! 通过非选择列进行排序：通常，ORDER BY子句中使用的列为检索显示所选择的列。但是，实际上并不一定要这样，用非检索的列排序数据是完全合法的。如下：
输入：SELECT prod_name FROM products ORDER BY prod_price;
输出：
分析：上图中我们想要检索显示的列为 prod_name列，但我们是以未检索的列prod_price列为依据排序的，显而易见，输出的顺序与刚才的不同。当然，作为排序依据的列必须要存在于检索的表中，不然就会出错了。

2. 按多个列排序

经常需要按不止一个列进行数据排序。例如，如果要显示雇员清单，可能希望按姓和名排序（首先按姓排序，然后在每个姓中再按名排序）。如果多个雇员具有相同的姓，这样做很有用。
为了按多个列排序，只要指定列名，列名之间用逗号分开即可（就像选择多个列时所做的那样）。
下面的代码检索3个列，并按其中两个列对结果进行排序——首先按价格，然后再按名称排序。
输入：SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price,prod_name;
输出：
分析：重要的是理解在按多个列排序时，排序完全按所规定的顺序进行。 换句话说，对于上述例子中的输出，仅在多个行具有相同的prod_price值时才对产品按prod_name进行排序。如果prod_price列中所有的值都是唯一的，则不会按prod_name排序。

3. 指定排序方向

数据排序不限于升序排序（从A到Z），这只是默认的排序顺序。 还可以使用ORDER BY子句以降序（从Z到A）顺序排序。为了进行降序排序，必须指定DESC关键字。

1. 下面的例子按价格以降序排序产品（最贵的排在最前面）：

输入：
SELECT prod_id,prod_price,prod_name
FROM products
ORDER BY prod_price DESC;
输出：

2. 如果打算用多个列排序怎么办？下面的例子以降序排序产品（最贵的在最前面），然后再对产品名排序：
输入：
SELECT prod_id,prod_price,prod_name
FROM products
ORDER BY prod_price DESC, prod_name;
输出：
DESC关键字只应用到直接位于其前面的列名。 在上例中，只对prod_price列指定DESC，对prod_name列不指定。因此，prod_price列以降序排序，而prod_name列（在每个相同价格内）仍然按标准的升序排序。
因此，如果想在多个列上进行降序排序，必须对每个列指定DESC关键字。
与DESC相反的关键字是ASC（ASCENDING），在升序排序时可以指定它。但实际上，ASC没有多大用处，因为升序是默认的（如果既不指定ASC也不指定DESC，则假定为ASC）。

3. 使用ORDER BY和LIMIT的组合，能够找出一个列中最高或最低的值。下面的例子演示如何找出最昂贵物品的值：

输入：
SELECT prod_price,prod_name
FROM products
ORDER BY prod_price DESC
LIMIT 1;
输出：
分析：prod_price DESC保证行是按照由最昂贵到最便宜检索的，而 LIMIT 1告诉MySQL仅返回一行。
注意！！ORDER BY子句的位置：在给出ORDER BY子句时，应该保证它位于FROM子句之后。如果使用LIMIT子句，它必须位于ORDER BY之后。使用子句的次序不对将产生错误消息。

二、过滤数据

1. 使用WHERE子句

数据库表一般包含大量的数据，很少需要检索表中所有行。通常只会根据特定操作或报告的需要提取表数据的子集。
只检索所需数据需要指定搜索条件（search criteria），搜索条件也称为过滤条件（filter condition）。 SELECT语句中，数据根据WHERE子句中指定的搜索条件进行过滤。 WHERE子句在表名（FROM子句）之后给出 ，如下所示：
输入：
SELECT prod_name, prod_price
FROM products
WHERE prod_price = 2.50;
输出：
分析：这条语句从products表中检索两个列，但不返回所有行，只返回 prod_price值为2.50的行。
这个例子采用了简单的相等测试：它检查一个列是否具有指定的值，据此进行过滤。但是SQL允许做的事情不仅仅是相等测试。
注意！WHERE子句的位置：在同时使用ORDER BY和WHERE子句时，应该让ORDER BY位于WHERE之后，否则将会产生错误。

2. WHERE子句操作符

在上例中，我们在关于相等的测试时看到了第一个WHERE子句，它确定一个列是否包含特定的值。MySQL支持下列条件操作符。

2.1 检查单个值

1. 我们已经看到了测试相等的例子。再来看一个类似的例子：

输入：
SELECT prod_name, prod_price
FROM products
WHERE prod_name = ‘fuses’;
输出：
检查WHERE prod_name=‘fuses’ 语句，它返回prod_name 的值为Fuses的一行。MySQL在执行匹配时默认不区分大小写，所以’fuses’与’Fuses’匹配。

2. 现在来看几个使用其他操作符的例子。

第一个例子是列出价格小于10美元的所有产品：
输入：
SELECT prod_name, prod_price
FROM products
WHERE prod_price < 10;
输出：
第二个例子检索价格小于等于10美元的所有产品（输出的结果比第一个例子输出的结果多两种产品）：
输入：
SELECT prod_name, prod_price
FROM products
WHERE prod_price <= 10;
输出：

2.2 不匹配检查

以下例子列出不是由供应商1003 制造的所有产品：
输入：
SELECT vend_id, prod_name
FROM products
WHERE vend_id <> 1003;
*输出：
下面是相同的例子，其中使用 != 而不是<>操作符：
输入：
SELECT vend_id, prod_name
FROM products
WHERE vend_id != 1003;
输出：
由上面可知，!= 操作符和 <>操作符作用是相同的。
注意！何时使用引号： 如果仔细观察上述WHERE子句中使用的条件，会看到有的值括在单引号内（如前面使用的’fuses’），而有的值未括起来。单引号是用来限定字符串的。如果将值与字符串类型的列进行比较，则需要使用限定引号（‘’）。而用来与数值类型的列进行比较的值不用引号。

2.3 范围值检查

为了检查某个范围的值，可使用BETWEEN操作符。其语法与其他WHERE子句的操作符稍有不同，因为它需要两个值，即范围的开始值和结束值。
例如，BETWEEN操作符可用来检索价格在5美元和10美元之间或日期在指定的开始日期和结束日期之间的所有产品。下面的例子说明如何使用BETWEEN操作符，它检索价格在5美元和10美元之间的所有产品：
输入：
SELECT prod_name, prod_price
FROM products
WHERE prod_price BETWEEN 5 AND 10;
输出：
分析：从这个例子中可以看到，在使用BETWEEN时，必须指定两个值，即所需范围的低端值和高端值。这两个值必须用AND关键字分隔。BETWEEN匹配范围中所有的值，包括指定的开始值和结束值。

2.4 空值检查

在创建表时，表设计人员可以指定其中的列是否可以不包含任何值。在一个列不包含任何值时，则称其为包含空值 NULL。
NULL ：无值（no value），它与列包含 0、空字符串或仅仅包含空格不同。
SELECT语句有一个特殊的WHERE子句，可用来检查具有NULL值的列。这个WHERE子句就是IS NULL子句。其语法如下：
输入：
SELECT prod_name
FROM products
WHERE prod_price IS NULL;
这条语句返回没有价格（空prod_price字段，不是价格为0）的所有产品，由于表中没有这样的行，所以没有返回数据。
但是，在customers 表中包含有具有空值的列，先看看customers 表中的两列数据：
如果在文件中没有某位顾客的电子邮件地址，则cust_email 列将包含NULL值，如下：
输入：
SELECT cust_id ,cust_email
FROM customers
WHERE cust_email IS NULL;
输出：
注意！NULL与不匹配： 在通过过滤选择出不具有特定值的行时，你可能希望返回具有NULL值的行。但是，不行。因为NULL 具有特殊的含义，数据库不知道它们是否匹配，所以在匹配过滤或不匹配过滤时都不返回它们。因此，在过滤数据时，一定要验证返回数据中确实给出了被过滤列具有NULL的行。
还是以customers 表为例，若想要过滤 'cust_email ’ <> ‘ylee@coyote.com’ 的值：
输入：
SELECT cust_id ,cust_email
FROM customers
WHERE cust_email <> ‘ylee@coyote.com’;
输出：
可见，NULL值的行未返回。

三、数据过滤

1. 组合WHERE子句

上面介绍的所有WHERE子句在过滤数据时使用的都是单一的条件。为了进行更强的过滤控制，MySQL允许给出多个WHERE子句。这些子句可以两种方式使用：以 AND子句的方式或 OR子句的方式。
操作符（operator）：用来联结或改变 WHERE子句中的子句的关键字。也称为逻辑操作符（logical operator）。

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 是用在WHERE子句中的关键字，用来指示检索满足所有给定条件的行。 上述例子中使用了只包含一个关键字AND的语句，把两个过滤条件组
合在一起。还可以添加多个过滤条件，每添加一条就要使用一个AND。

1.2 OR操作符

OR： WHERE子句中使用的关键字，与AND操作符不同，它指示MySQL检索匹配任一过滤条件的行。
请看如下的SELECT语句：
输入：
SELECT prod_name, prod_price
FROM products
WHERE vend_id = 1002 OR vend_id = 1003;
输出：
分析：此SQL语句检索由任一个指定供应商制造的所有产品的产品名和价格。OR操作符告诉DBMS匹配任意一个过滤条件而不是同时匹配两个过滤条件。
如果这里使用的是AND操作符，则没有数据返回（此时创建的WHERE子句不会检索到匹配的产品）。

1.3 计算次序

WHERE可包含任意数目的AND和OR操作符。允许两者结合以进行复杂和高级的过滤。
但是，组合AND和OR带来了一个有趣的问题。为了说明这个问题，来看一个例子。假如需要列出价格为10美元（含）以上且由供应商1002或1003制造的所有产品。下面的SELECT语句使用AND和OR操作符的组合建立了一个WHERE子句：
输入：
SELECT prod_name, prod_price
FROM products
WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10;
输出：
分析：请看上面的结果。返回的行中有两行价格小于10美元，显然，返回的行未按预期的进行过滤。为什么会这样呢？原因在于计算的次序。SQL（像多数语言一样）在处理OR操作符前，优先处理AND操作符。 当SQL看到上述WHERE子句时，它理解为由供应商1003制造的任何价格为10美元（含）以上的产品，或者由供应商1002制造的任何产品，而不管其价格如何。换句话说，由于AND在计算次序中优先级更高，操作符被错误地组合了。
此问题的解决方法是使用圆括号明确地分组相应的操作符。 请看下面的SELECT语句及输出：
输入：
SELECT prod_name, prod_price
FROM products
WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;
输出：
分析：因为圆括号() 具有较AND或OR操作符高的计算次序，DBMS首先过滤圆括号内的OR条件。这时，SQL语句变成了选择由供应商1002或1003制造的且价格都在10美元（含）以上的任何产品，这正是我们所希望的。
注意！在WHERE子句中使用圆括号任何时候使用具有AND和OR操作符的WHERE子句，都应该使用圆括号明确地分组操作符。 不要过分依赖默认计算次序，即使它确实是你想要的东西也是如此。使用圆括号没有什么坏处，它能消除歧义。

2. IN操作符

圆括号在WHERE子句中还有另外一种用法。IN操作符用来指定过滤条件的范围，范围中的每个条件都可以进行匹配。IN取合法值的由逗号分隔的清单，全都括在圆括号中。注意：IN操作符完成了与OR相同的功能。下面的例子说明了这个操作符：
输入：
SELECT prod_name, prod_price
FROM products
WHERE vend_id IN (1002, 1003)
ORDER BY prod_name;
输出：
分析：此SELECT语句检索供应商1002和1003制造的所有产品。IN操作符后跟由逗号分隔的合法值清单，整个清单必须括在圆括号中。
为什么要使用IN操作符？其优点具体如下。
- 在使用长的合法选项清单时，IN操作符的语法更清楚且更直观。
- 在使用IN时，计算的次序更容易管理（因为使用的操作符更少）。
- IN操作符一般比OR操作符清单执行更快。
- IN的最大优点是可以包含其他SELECT语句，使得能够更动态地建立WHERE子句。

3. NOT操作符

WHERE子句中的NOT操作符有且只有一个功能，那就是否定它之后所跟的任何条件。
NOT ：WHERE子句中用来否定后跟条件的关键字。
下面的例子说明NOT的使用。为了列出除1002和1003之外的所有供应
商制造的产品，可编写如下的代码：
输入：
SELECT prod_name, prod_price
FROM products
WHERE vend_id NOT IN (1002, 1003)
ORDER BY prod_name;
输出：
这里的NOT否定跟在它之后的条件，因此，MySQL不是匹配1002和 1003 的 vend_id ，而是匹配 1002 和 1003 之外供应商的vend_id。
为什么使用NOT？对于简单的WHERE子句，使用NOT确实没有什么优势。但在更复杂的子句中，NOT是非常有用的。例如，在与IN操作符联合使用时，NOT使找出与条件列表不匹配的行非常简单。
注意！MySQL中的NOT MySQL支持使用NOT对IN、BETWEEN和EXISTS子句取反，这与多数其他DBMS允许使用NOT对各种条件取反有很大的差别。

四、使用通配符进行过滤

1.LIKE操作符

通配符（wildcard）：用来匹配值的一部分的特殊字符。
搜索模式（search pattern）：由字面值、通配符或两者组合构成的搜索条件。
通配符本身实际是SQL的WHERE子句中有特殊含义的字符，SQL支持几种通配符。
为在搜索子句中使用通配符，必须使用LIKE操作符。LIKE指示MySQL，后跟的搜索模式利用通配符匹配而不是直接相等匹配（=）进行比较。

1.1 百分号（%）通配符

最常使用的通配符是百分号（%）。在搜索串中，（%）表示任何字符出现任意次数。(%)代表搜索模式中给定位置的0个、1个或多个字符。 例如，为了找出所有以词 ‘jet’ 起头的产品，可使用以下SELECT语句：
输入：
SELECT prod_name, prod_price
FROM products
WHERE prod_name LIKE ‘jet%’;
输出：
分析：此例子使用了搜索模式 ‘jet%’。在执行这条子句时，将检索任意以jet 起头的词。(%)告诉MySQL接受 jet 之后的任意字符，不管它有多少字符。
区分大小写： 根据MySQL的配置方式，搜索可以是区分大小写的。如果区分大小写，‘jet%’ 与JetPack 1000将不匹配。
通配符可在搜索模式中任意位置使用，并且可以使用多个通配符。 下面的例子使用两个通配符，它们位于模式的两端：
输入：
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE ‘%anvil%’;
输出：
分析：搜索模式’%anvil%'表示匹配任何位置包含文本anvil的值，而不论它之前或之后出现什么字符。
注意NULL： 虽然似乎(%)通配符可以匹配任何东西，但有一个例外，即NULL。即使是WHERE prod_name LIKE ‘%’ 也不能匹配用NULL值作为产品名的行。

1.2 下划线（_）通配符

另一个有用的通配符是下划线（_）。下划线的用途与%一样，但下划线只匹配单个字符而不是多个字符。
举一个例子：
输入：
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE ‘_ ton anvil’;
输出：
此WHERE子句中的搜索模式给出了后面跟有文本的两个通配符。结果只显示匹配搜索模式的行：第一行中下划线匹配1，第二行中匹配2。‘.5 ton anvil’ 产品没有匹配，因为搜索模式要求匹配一个通配符而不是两个。
对照一下，下面的SELECT语句使用(%)通配符，返回三行产品：
输入：
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE ‘%anvil%’;
输出：
与(%)能匹配0个字符不一样，(_)总是匹配一个字符，不能多也不能少。

2. 使用通配符的技巧

正如所见，MySQL的通配符很有用。但这种功能是有代价的：通配符搜索的处理一般要比前面讨论的其他搜索所花时间更长。这里给出一些使用通配符要记住的技巧。
- 不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
- 在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的。
- 仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。

五、总结

本篇文章学习了如何用SELECT语句的ORDER BY子句对检索出的数据进行排序。这个子句必须是SELECT语句中的最后一条子句。可根据需要，利用它在一个或多个列上对数据进行排序。
介绍了如何用SELECT语句的WHERE子句过滤返回的数据。我们学习了如何对相等、不相等、大于、小于、值的范围以及NULL值等进行测试。
介绍如何用AND和OR操作符组合成WHERE子句，而且还讲授了如何明确地管理计算的次序，如何使用IN和NOT操作符。
介绍了什么是通配符以及如何在WHERE子句中使用SQL通配符，并且还说明了通配符应该细心使用，不要过度使用。