六、用通配符进行过滤
这一章节据介绍什么时通配符、如何使用通配符，以及怎样使用LIKE操作符进行通配搜索，以便对数据进行复杂过滤。
6.1 LIKE操作符
之前介绍的所有操作符都是针对已知值进行过滤的。不管是匹配一个值还是多个值，检验大于还是小于已知值，或者检查某个范围的值，其共同点是过滤中使用的值都是已知的。但是过滤方法并不是任何时候都好用。例如怎么样搜索产品名中包含文本bean bag 的所有产品？用简单的比较操作符肯定不行，必须使用通配符。利用通配符，可以创建比较特定数据的搜索模式。在这个例子中，如果你想找出名称包含bean bag的所有产品，可以构造一个通配符搜索模式，找出在产品名的任何位置出现bean bag的产品。
通配符（wildcard）用来匹配值的一部分的特殊字符。搜索模式（search pattern）由字面值、通配符或者两者组合构成的搜索条件。
通配符本身实际上时SQL的WHERE子句中由特殊含义的字符，SQL支持几种通配符。为在搜索子句中使用通配符，必须使用LIKE操作符。LIKE指示DBMS后跟的搜索模式利用通配符匹配而不是简单的相等匹配进行比较。
谓词（predicate），操作符何时不是操作符？答案是，它作为谓词时。从技术上说，LIKE是谓词而不是操作符。虽然最终的结果是相同的，但应该对此属于
有所了解，以免在SQL文献或手册中遇到此术语时不知所云。
通配符搜索只能用于文本字段（字符串），非文本数据类型字段不能使用通配符搜索。
6.1.1 百分号（%）通配符
最常使用的通配符是百分号（%）在搜索串中，%表示任何字符出现任意次数。例如，为了找出所有以词Fish起头的产品，可写以下的SELECT语句：
区分大小写：
根据DBMS的不同及其配置，搜索可以是区分大小写的。如果区分大小写则不同大小写就不匹配。
通配符可在搜索模式中的任意位置使用，并且可以使用多个通配符。下面的例子使用两个通配符，他们位于模式的两端：
下面的例子使用一个通配符百分号，位于中间：
根据部分信息搜索电子邮箱地址时可以用到将通配符百分号至于中间位置，如 WHERE email LIKE 'b%forta.com'
通配符百分号除了能匹配一个或多个字符外，还能匹配0个字符。%代表搜索模式中给定位置的0个、1个或多个字符。
注意：1、有些DBMS用空格来填补字段的内容。例如，如果某列有50个字符，而存储的文本为Fish bean bag toy(17个字符)，则为填满该列需要在文本后
附加33个空格。这样做一般对数据及使用没有影响，但是可能对上述SQL语句有负面影响。子句WHERE prod_name LIKE 'F%y'只匹配以F开头、以y结尾的
prod_name。如果值后面跟空格则不是以y结尾，所以Fish bean bag toy 就不会检索出来。简单的解决办法是给搜索模式再增加一个%号：'F%y%'还匹配
y之后的字符（或空格）。更好的解决办法是用函数去掉空格。（操作参阅第八课）
2、注意NULL。 通配符%看起来像是可以匹配任何东西，但有个例外，这就是NULL。子句WHERE prod_name LIKE'%'不会匹配产品名称为NULL的行。
6.1.2 下划线（_）通配符
另一个有用的通配符是下划线（_）。下划线的用途与“%”一样，但是它只匹配单个字符，而不是多个字符。
注意：DB2不支持通配符
下面是一个例子，因数据有空格所以注意空格的输入，前面：
上图例子为两个下划线_通配符，所以返回值为12和18的产品名称，下图例子是使用百分号%通配符匹配的结果反馈：
上例可以看出百分比是匹配了多个字符儿下划线是一个下划线匹配一个字符。
6.1.3 方括号（[]）通配符
方括号（[]）通配符用来指定一个字符集。它必须匹配指定位置（通配符的位置）一个字符。
注意： 与前面描述的通配符不同，方括号并不是总是支持集合。不是所有的DBMS都支持用来创建集合的[]，微软的SQL Server支持集合，
但是MySQL，Oracle，DB2，SQLite都不支持。未确定你使用的DBMS是否支持集合，请参阅相应文档。
下例为找出所有名字以 **J ** 或 ** M ** 起头的联系人：
我们可以看到在MySQL中不支持方括号通配符限定集合。
在MySQL的标准LIKE操作符中，方括号 [ ] 不被视为通配符，它们会被当作普通字符来处理。
MySQL的 LIKE 操作符主要支持以下两个通配符：
% (百分号)：匹配任意数量的字符（包括零个字符）。
· LIKE ‘a%’：匹配以 “a” 开头的任何字符串，如 “apple”, “a”, “abc123”。_ (下划线)：匹配任意单个字符。
· LIKE ‘a_’：匹配以 “a” 开头且长度为两个字符的字符串，如 “ab”, “a1”, “ax”。
如何实现类似方括号通配符的功能？
如果你想实现像其他数据库（如SQL Server）中 [abc] 匹配一个特定字符集的功能，在MySQL中有以下几种替代方案：
使用 REGEXP 或 RLIKE (正则表达式)
这是最灵活和强大的方法。正则表达式可以完全覆盖方括号通配符的功能，甚至更强大。
· [abc] 的等效写法：REGEXP ‘[abc]’
· SELECT * FROM table_name WHERE column_name REGEXP ‘[abc]’;
· 这会匹配包含 ‘a’, ‘b’, 或 ‘c’ 中任意一个字符的记录。
· 更多正则表达式示例：
· [a-z]：匹配任何小写字母。
· [0-9]：匹配任何数字。
· ^ [abc]：匹配以 ‘a’, ‘b’, 或 ‘c’ 开头的字符串。
· [abc]$：匹配以 ‘a’, ‘b’, 或 ‘c’ 结尾的字符串。
注意：正则表达式默认是部分匹配。如果希望完全匹配，需要使用 ^ 和 $ 限定符，例如 REGEXP ‘^ [abc]$’ 只会匹配 exactly ‘a’, ‘b’, 或 ‘c’ 这三个单字符字符串。
上述例子我们可以看到在使用REGEXP时方括号集合内在MySQL中不区分大小写，所以会匹配出“Kim Howard”名字中带小写
字母m的结果，这个时候我们需要用到新的正则表达式语句“REGEXP BINAR”，但是这个案例会出现字符集冲突，BINAR关
键字要求进行原始的二进制比较，这意味着它必须是区分大小写和重音的。而我们的[]字符集（utf8mb4_0900_ai_ci）
中_ai 表示不区分重音，_ci表示不区分大小写。MYSQL无法同时执行不区分大小写和中印的规则
解决方案：
使用^匹配开头字母。
使用多个 LIKE 语句配合 OR
对于非常简单的模式，你也可以用多个 LIKE 来模拟。
· 模拟 [abc]：
SELECT * FROM table_name
WHERE column_name LIKE '%a%'
OR column_name LIKE '%b%'
OR column_name LIKE '%c%';
但这种方法是匹配包含这些字符，要精确模拟一个字符的位置，需要结合 _ 通配符，会非常繁琐，不推荐。
使用 FIND_IN_SET (适用于逗号分隔的特定值)
如果你的匹配目标是几个明确的、完整的字符串，而不是单个字符，可以考虑这个函数，但适用场景比较特殊。
SELECT * FROM table_name
WHERE FIND_IN_SET(column_name, 'apple,banana,cherry') > 0;
总结对比
需求 SQL Server / Access (使用 LIKE) MySQL 解决方案 (使用 REGEXP)
匹配单个字符，是 ‘a’, ‘b’ 或 ‘c’ WHERE col LIKE ‘[abc]’ WHERE col REGEXP ‘^ [abc]$’
匹配包含 ‘a’, ‘b’ 或 ‘c’ 的字符串 WHERE col LIKE ‘%[abc]%’ WHERE col REGEXP ‘[abc]’
匹配以 ‘a’, ‘b’ 或 ‘c’ 开头的字符串 WHERE col LIKE ‘[abc]%’ WHERE col REGEXP ‘^ [abc]’
结论：在MySQL中，如果你需要字符集匹配的功能，应该优先使用 REGEXP 正则表达式来代替其他数据库中的方括号通配符。
注意：在[]集合中可以使用^（脱字号）在集合内表示否定。例如WHERE cust_contact REGEXP '^[^JM]'，下例子为返
回结果
上述方法也可以使用NOT操作符得出类似的结果，^的唯一优点是在使用多个WHERE子句时可以简化语法。此处与书上所写NOT LIKE不同，由于MySQL不能使用集合[]所以依然要用NOT + REGEXP这种表达语句。
6.2 使用通配符的技巧
上述例子可以看出SQL的通配符很有用。但是这种功能是有代价的，即通配符搜索一般比前面讨论的其他搜索要耗费更长的处理时间。下面给出一些使用通配符要记住的技巧。
不要过度使用通配符，如果其他操作符能达到相同的目的，应该使用其他操作符。在确实需要使用通配符时，也尽量不i要把他们用在搜索模式的开始处，把通配符置于开始处，搜索起来是最慢的。仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。
6.3 此章小结
使用LIKE操作符进行模糊查询使用通配符%、_、[]通配符，注意各自通配符的区别使用通配符坚持少用不用于开始的原则避免搜索效率低。
6.4章节练习题
1.编写SQL语句，从Products表中检索产品名称（prod_name)和描述（prod_desc)，仅返回描述中包含toy一词的产品。
此处考察LIKE模糊查询，在不确定的字符的情况下加上百分号
2.编写SQL语句，从Products表中检索产品名称（prod_name)和描述（prod_desc)，仅返回描述中不包含toy一词的产品。按产品名称对结果进行排序。
3.编写SQL语句，从Products表中检索产品名称（prod_name)和描述（prod_desc)，仅返回描述中同时出现toy和carrots一词的产品。有好几种方法可以执行此操作，但对于这个挑战题，请使用AND和两个LIKE比较。
4.编写SQL语句，从Products表中检索产品名称（prod_name）和描述（prod_desc），仅返回在描述中以先后顺序同时出现toy和carrots的产品。提示：只需要用带有三个%符号的LIKE即可。
七、创建计算字段
这一刻价绍什么是计算字段，如何创建计算字段，以及如何从应用程序中使用别名引用他们。
7.1 计算字段
存储在数据库表中的数据一般不是应用程序所需要的格式，下面举几个例子。
需要显示公司名，同时还需要显示公司的地址，但这两个信息存储在不同的表列中。城市、州和邮政编码存储在不同的列中（应该这样），但邮件标签打印程序需要把它们作为一个有恰当格式的字段检索出来。列数据是大小写混合的，但报表程序需要把所有数据按大写表示出来。物品订单表存储物品的价格和数量，不存储每个物品的总价格（用价格乘以数量即可）。但为打印发票，需要物品的总价格。需要根据表数据进行诸如总数、平均数的计算。
在上述每个例子中，存储在表中的数据都不是应用程序所有需要的。我们需要直接从数据库中检索出转换、计算或格式化过的数据，而不是检索出数据，然后再在客户端应用程序中重新格式化。这就是计算字段可以派上用场的地方了。计算字段并不实际存在于数据库表中，计算字段是运行时在SELECT语句内创建的。
字段：基本与列的意思相同，经常互换使用，不过数据库列一般称为列，而字段这个术语通常在计算字段这种场合下使用。
需要特别注意，只有数据库知道SELECT语句中哪些列是实际的表列，哪些列是计算字段。从客户端（如应用程序）来看，
计算字段的数据与其他列的数据返回方式相同。
客户端与服务器的格式：
在SQL语句内可完成的许多转换和格式化工作都可以直接在客户端应用程序内完成。但一般来说，在数据库服务器上
完成这些操作比在客户端中完成要快得多。
7.2 拼接字段
为了说明如何使用计算字段，我们来举一个简单的例子，创建由两列组成的标题。
Vendors表包含供应商名和地址信息。加入要生成一个供应商报表，需要在格式化的名称（位置）中列出供应商的位置。
此报表需要一个值，而表中数据存储在两个列vend_name和vend_country中。此外，需要用括号将vend_country括起来，这些东西都没有存储在数据表中。这个返回供应商名称和地址的SELECT 语句很简单，但我们是如何创建这个组合值的呢？
拼接：
将值联结到一起（将一个值附加到另一个值）构成单个值。
解决办法： 把两个列拼接起来。在SQL中的SELECT语句中，可以使用一个特殊的操作符来拼接两个列。根据你所使用的DBMS。此操作符可用加号（+）或两个竖杠（||）表示。在MySQL中和Maria DB中，必须使用特殊的函数。
是+还是||？
SQL Server使用+号。DB2、Oracle、PostgreSQL和SQLite使用||。详细请参阅具体的DBMS文档
下面是使用+号的例子（多数DBMS使用这种语法，MySQL不使用）
SELECT vend_name + '(' + vend_country + ')'
FROM Vendors
ORDER BY vend_name;
下面是相同的语句，但使用||的语法：
SELECT vend_name || '(' || vend_country || ')'
FROM Vendors
ORDER BY vend_name;
下面是使用MySQL或MariaDB时需要使用的语句：
SELECT
Concat(vend_name, '(',vend_country,')')
RROM Vendors
ORDER BY vend_name;
上面两个SELECT语句拼接一下元素：
存储在vend_name列中的名字；
包含一个空格和一个左圆括号的字符串；
存储在vend_country列中的国家；
包含一个右圆括号的字符串。
从上述输出中可以看出，SELECT语句返回包含上述四个元素的一个列（计算字段）。
再看看上述SELECT语句返回的输出。结合成一个字段的两个列用空格填充。许多数据库（不是所有）保存填充为
列宽的文本值，而实际上你要的结果不需要这些空格。为正确返回格式化的数据，必须去掉这些空格。这可以使用
SQL的RTRTM（）函数来完成，如下例子所示：
SELECT
RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
FROM
Vendors
ORDER BY vend_name;
###使用+联结
SELECT
RTRIM(vend_name) || ' (' || RTRIM(vend_country) || ')'
FROM
Vendors
ORDER BY vend_name;
上述为不同于MySQL的数据库代码，下述为MySQL数据库代码与结果（使用CONCAT函数联结列）：
RTRIM()函数去掉值右边所有空格。通过使用RTRIM（），各个列值都进行了整理。
说明：TRIM函数
大多数DBMS都支持RTRIM（）函数，（正如刚才所见，它去掉字符串右边的空格）、LTRIM（）函数去掉左边的空格以及
TRIM（）函数去掉字符串左右两边的空格。
使用别名
从前面的输出可以看到，SELECT语句可以很好地拼接地址字段。但是，这个新计算列的名字是什么呢？实际上它没有名字，它只是一个值。如果仅在SQL查询工具中查看一下结果，这样没有什么不好。但是一个未命名的列不能用于客户端应用中，因为客户端没有办法引用它。
为了解决这个问题，SQL支持列别名。别名（alisa）是一个字段或者值的替换名。别名用AS关键字赋予。请看下面的SELECT语句：
此处仅展示MySQL中用AS关键字别名新建列的用法与输出结果。由上图可以看出新列计算字段有了新列名vend_title。其余DBMS同理。
说明：AS通常可选
在很多DBMS中，AS关键字是可选的，不过最好使用它，这被视为一条最佳实践。
说明：别名的其他用途
别名还有其他用途。常见的用途包括在实际的表列名包含不合法的字符（如空格）时重新命名它。在原来的名字含混
或容易误解时扩充它。
注意：别名
别名的名字既可以是一个单词，也可以是一个字符串。如果是后者，字符串应该括在引号中。虽然这种做法是合法的，
但不建议这么去做。多单词的名字可读性高，不过会给客户端应用带来各种问题。因此，别名最常见的使用是将多个
单词的列名重命名为一个单词的名字。
说明：导出列
别名有时也称为导出列（derived column），不管怎么叫，它们所代表的是相同的东西。
7.3 执行算术计算
计算字段的另一种常见用途是对检索出的数据进行算术计算。举个例子，Orders表包含收到的所有订单，OrderItems表包含每
20008中的所有物品：
item_price列包含订单中每项物品的单价。如下汇总物品的价格（单价乘以订购数量）
输出中显示的expanded_price列是一个计算字段，此计算为quantity*item_price。客户端应用现在可以使用这个新计算列，和使用其他列一样。
提示：如何测试计算
SELECT语句为测试、检验函数和是计算提供了很好的方法。虽然SELECT通常用于从表中检索数据，但是省略了FROM
子句后就是简单的访问和处理表达式，例如SELECT 3*2 ；将返回6，SELECT Trim（'abc'）；将返回abc，
SELECT Curdate（）；使用Curdate（）函数返回当前日期和时间。这样就可以根据需要使用SELECT语句进行检验。
7.4 此章小结
此章主要讲了如何计算拼接字段并进行字段重命名。
拼接字段 + || 和MySQL使用的CONCAT（）函数通过运算符运算字段作为一个新列，可以用AS关键字对此列命名。
7.5 章节练习题
别名的常见用法实在检索出的结果中重命名表的列字段（为了符合特定的报表要求与客户需求）。编写SQL语句，从Vendors表中检索vend_id、vend_name、vend_adress和vend_city，将vend_name重命名为vname，将vend_city重命名为vcity，将vend_address重命名为vaddress。按供应商名称对结果进行排序（可以使用原始名称或新名称）。
注意列名之间的连接需要用英文逗号，不要用成中文逗号。
我们的示例商店正在进行打折促销，所有产品均降价10%。编写SQL语句，从Products表中返回prod_id、prod_price和sale_price。sale_price是一个包含促销价格的计算字段。提示：可以乘以0.9，得到原价的90%（即10%的折扣）。
八、使用函数处理数据
这一刻介绍什么是函数，DBMS支持何种函数，以及如何使用这些函数，还将讲解为什么SQL函数的使用可能会带来问题。
8.1 函数
与大多数计算机语言一样，SQL也可以用函数来处理数据。函数一般是在数据上执行的，为数据的转换和处理提供了方便。前一课中用来去掉字符串尾的空格的RTRIM（）就是一个函数。
函数带来的问题
首先我们讲的是要了解使用SQL函数会存在哪些问题。
与几乎所有的的DBMS都等同的支持SQL语句（如SELECT）不同，每一个DBMS都有特定的函数。事实上，只有少数几个函数被所有主要DBMS等同的支持。虽然所有类型的函数一般都可以在每个DBMS中使用，但各个函数的名称和语法可能极其不同。为了说明可能存在的问题，下表列出了3个常用的函数及其在各个DBMS中的语法：
| 函数 | 语法 |
|---|---|
| 提取字符串的组成部分 | DB2、Oracle、PostgreSQL和SQLite使用SUBSTR()；MariaDB、MySQL和SQL Server使用SUBSTRING() |
| 数据类型转换 | Oracle使用多个函数，每种类型的转换有一个函数；DB2和PostgreSQL使用CAST()；MariaDB、MySQL和SQL Server使用CONVERT() |
| 取当前日期 | DB2和PostgreSQL使用CURRENT_DATE；MariaDB和MySQL使用CURDATE()；Oracle使用SYSDATE；SQL Server使用GETDATE()；SQLite使用DATE() |
上表中可以看到，与SQL语句不一样，SQL的函数不是可移植的。这意味着为特定SQL实现编写的代码在其他实现中可能不能用。
可移植（portable）
所编写的代码可以在多个系统上运行。
为了代码的可移植，许多SQL程序员不赞成使用特定于实现的功能。虽然这样做很有好处，但有的时候并不利于应用程序的性能。如果不使用这些函数，编写某些应用程序代码会很艰难。必须利用其他方法来实现DBMS可以非常有效完成的工作。
提示：是否应该使用函数？
现在，你面临是否应该使用函数的选择。决定权在你，使用或是不使用也没有对错之分。如果你决定使用函数，应该保证
做好代码注释，以便以后你自己（或其他人）能确切地知道这些SQL代码的含义。
8.2 使用函数
大多数SQL实现支持以下类型的函数。
用于处理文本字符串（如删除或填充值，转换值为大写或小写）的文本函数用于在数值数据上进行算术操作（如返还绝对值，进行代数运算）的数值函数用于处理日期和时间值并从这些值中提取特定成分（如返回两个日期之差，检查日期有效性）的日期和时间函数用于生成美观好懂的输出内容的格式化函数（如用语言形式表达出日期，用货币符号和千分位表示金额）。返回DBMS正是用的特殊信息（如返回用户登录信息）的系统函数。
我们在上一课看到函数用于SELECT后面的列名，但函数的作用不仅于此。它还可以作为SELECT语句的其他成分，如在WHERE子句中使用，在其他SQL语句中使用等，后面会做更多的介绍。
8.2.1 文本处理函数
在上一章节，我们已经看过一个文本处理函数的例子。其中使用RTRIM（）函数来去除列值右边的空格。下面是另一个例子，这次使用的是UPPER（）函数：
可以看出，UPPER（）将文本转换为大写，因此本例子中每个供应商都列出两次，第一次位Vendors表中存储的值，第二次作为列vend_name_upcase转换为大写。
提示：大写，小写，大小写混合
此时你应该已经知道SQL函数不区分大小写，因此upper（），UPPER（），Upper（）都可以，Substrate（），
SUBSTR（），SubStr（）也都行。随你的喜好，不过注意保持风格一致，不要变来变去，否则你写的程序代码
就不好读了。
下面表里列出了一些常用的文本处理函数：
| 函数 | 说明 |
|---|---|
| LEFT()（或使用子字符串函数） | 返回字符串左边的字符 |
| LENGTH()（也使用DATALENGTH()或LEN()） | 返回字符串的长度 |
| LOWER() | 将字符串转换为小写 |
| LTRIM() | 去掉字符串左边的空格 |
| RIGHT()（或使用子字符串函数） | 返回字符串右边的字符 |
| RTRIM() | 去掉字符串右边的空格 |
| SUBSTR()或SUBSTRING() | 提取字符串的组成部分（见表8 – 1） |
| SOUNDEX() | 返回字符串的SOUNDEX值 |
| UPPER() | 将字符串转换为大写 |
上表中的SOUNDEX（）需要做进一步的解释。SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX考虑了类似的发音字符和音节，使得能对字符串进行发音比较而不是字母比较。虽然DOUNDEX（）不是SQL概念，但多数DBMS都提供对SOUNDEX（）函数的支持。
说明：SOUNDEX支持
PostgreSQL不支持SOUNDEX（），因此以下的例子不适用于这个DBMS。
另外，如果在创建SQLite的时候使用了SQLITE_SOUNDEX编译时选项，那么SOUNDEX（）在SQLITE中就可用。
因为SQLITE_SOUNDEX不是默认的编译时选项，所以多数SQLITE实现不支持SOUNDEX（）。
下面给出了一个使用SOUNDEX（）函数的例子。Customers表中有一个顾客Kids Place，其联系名为Michelle Green。但如果这是错误的输入，此联系名实际上应该是Michael Green，该怎么办呢？显然，按正确的联系名搜索不会返回数据，如下所示：
可以看到上图返回结果为空，搜不到这个正确的名字。现在试一下使用SOUNDEX（）函数进行搜索，他匹配所有发音类似于Michael Green 的联系名：
在这个例子中，WHERE子句使用SOUNDEX（）函数把cust_contact列值和搜索字符串转换为他们的SOUNDEX值。因为Michael Green和Michelle Green发音相似，所以他们的SOUNDEX值匹配，因此WHERE子句正确的过滤出了所需的数据。
8.2.2 日期和时间处理函数
日期和时间采用相应的数据类型存储在表中，每种DBMS都有自己的特殊形式。日期和时间值以特殊的格式存储，以便更快速和有效的排序或过滤，并且节省物理存储空间。
应用程序一般不使用日期和时间的存储形格式，因此日期和时间函数总是用来读取、统计和处理这些值。由于这个原因，日期和时间函数在SQL中具有重要的作用。遗憾的是，他们很不一致，可移植性最差。
我们举个简单的例子，来说明日期处理函数的用法。Orders表中包含的订单都带有订单日期。要检索出某年的所有订单，需要按订单日期去找，但不需要完整日期，只要年份即可。
为在SQL Server中检索2020年的所有订单，在SQL Server中使用DATEPART（）函数返回日期的某部分。DATEPART（）函数有两个参数，它们分别是返回的成分和从中返回成分的日期。这个函数为SQL Server的内置函数，但不同的DBMS有不同的日期函数，下表为不同的DBMS日期函数对比。
在 MySQL 中不能直接使用
DATEPART()
DATEPART()
MySQL 中提供了功能完全等效的替代方案，核心是通过专门的日期函数直接提取对应时间单位，具体替代规则如下：
MySQL 替代
DATEPART()
DATEPART()
DATEPART(日期部分, 日期值)
| 需求（提取日期部分） | SQL Server 的 DATEPART 用法 | MySQL 替代函数（等效实现） | 示例（假设日期为 ‘2024-05-18 14:30:45’） | 示例结果 |
|---|---|---|---|---|
| 提取年份 | |
|
|
2024 |
| 提取月份（1-12） | |
|
|
5 |
| 提取日期（1-31） | |
|
|
18 |
| 提取小时（0-23） | |
|
|
14 |
| 提取分钟（0-59） | |
|
|
30 |
| 提取秒（0-59） | |
|
|
45 |
| 提取星期（1-7，1=周日） | |
|
|
7（2024-05-18 是周六，DAYOFWEEK 中周六为 7） |
| 提取一年中的第几周 | |
|
|
20（2024年第20周） |
扩展：用
DATE_FORMAT()
如果需要自定义日期部分的显示格式（例如提取月份时显示为两位数
05
5
DATE_FORMAT()
DATEPART()
DATE_FORMAT(日期值, '格式符')
常见场景示例：
| 需求 | DATE_FORMAT 用法 | 示例结果 |
|---|---|---|
| 提取两位数月份 | |
05 |
| 提取两位数日期 | |
18 |
| 提取四位数年份+月份 | |
202405 |
| 提取12小时制小时（带AM/PM） | |
02 PM |
实际查询示例（对比 SQL Server 和 MySQL）
假设需要查询
orders
SQL Server（用 DATEPART）：
SELECT * FROM orders
WHERE DATEPART(YEAR, order_date) = 2024
AND DATEPART(MONTH, order_date) = 5;
MySQL（用 YEAR + MONTH）：
SELECT * FROM orders
WHERE YEAR(order_date) = 2024
AND MONTH(order_date) = 5;
或用
DATE_FORMAT()
SELECT * FROM orders
WHERE DATE_FORMAT(order_date, '%Y%m') = '202405';
同时提取时、分、秒（自定义格式）
如果需要将时、分、秒按特定格式组合显示（例如 15:30:45），可以使用 DATE_FORMAT() 函数，通过格式符 %H（24 小时制小时）、%i（分钟）、%s（秒）来提取：
SELECT DATE_FORMAT(时间字段或时间字符串, '%H:%i:%s');
示例：
SELECT DATE_FORMAT('2024-09-24 15:30:45', '%H:%i:%s'); -- 结果：15:30:45
实际应用示例
假设表 logs 中有一个 create_time 字段（ datetime 类型），需要查询每条记录的时间中的时、分、秒：
SELECT
create_time,
HOUR(create_time) AS 小时,
MINUTE(create_time) AS 分钟,
SECOND(create_time) AS 秒,
DATE_FORMAT(create_time, '%H:%i:%s') AS 时分秒
FROM logs;
通过以上函数，可以灵活提取 MySQL 中日期时间的时、分、秒部分。
总结：MySQL 虽无
DATEPART()
YEAR()
MONTH()
DAY()
DATE_FORMAT()
下面例子是提取某个年份的订单，这里不演示SQL Server和PostgreSQL的用法了，只演示MySQL的用法：
注意：数据类型查看
在日常查询中我们会碰到不同的时间日期数据使用字符串填充的，这个时候以上查询会报错，我们会用到
描述关键字DESC/DESCRIBE。
在 MySQL 中，查看某一列的数据类型可以通过查询系统信息表或使用 `DESCRIBE` 命令实现，以下是两种常用方法：
使用
DESCRIBE
DESC
语法：
DESCRIBE 表名 列名;
-- 或简写为
DESC 表名 列名;
示例：
查看
users
create_time
DESCRIBE users create_time;
执行后会显示该列的详细信息，其中
Type
datetime
varchar(50)
查询
information_schema
如果需要在 SQL 脚本中获取列的数据类型，可查询
information_schema.columns
SELECT DATA_TYPE
FROM information_schema.columns
WHERE table_schema = '数据库名'
AND table_name = '表名'
AND column_name = '列名';
示例：
查看
mydb
orders
order_date
SELECT DATA_TYPE
FROM information_schema.columns
WHERE table_schema = 'mydb'
AND table_name = 'orders'
AND column_name = 'order_date';
该方法会直接返回数据类型（如
timestamp
注意事项：
两种方法都需要替换
表名
列名
数据库名
table_schema
USE 数据库名;
通过以上方法，即可快速获取 MySQL 中某一列的数据类型。
BETWEEN操作符的使用：
在不是整年、月、日等时间日期查询时，需要用操作符BETWEEN对首位时间进行范围规定。与上述取整年例子做一个对比如下例：
以上多个例子可以看出，DBMS提供的功能远不止简单的日期成分提取。大多数DBMS具有比较日期、执行日期的运算、选择日期格式等函数。但是。可以看出，不同DBMS的日期-时间处理函数可能不同。如需了解不同DBMS日期-时间处理函数，请参阅相应的文档。
8.2.3 数值处理函数
数值处理函数进处理数据数值。这些函数一般主要用于代数、三角或几何运算，因此不像字符串或日期-时间处理函数使用那么频繁。
具有讽刺意味的是，在主要的DBMS的函数中，数值函数是最一致、最统一的函数。下表列出一些常用的数值处理函数。
| 函数 | 说明 |
|---|---|
| ABS() | 返回一个数的绝对值 |
| COS() | 返回一个角度的余弦 |
| EXP() | 返回一个数的指数值 |
| PI() | 返回圆周率π的值 |
| SIN() | 返回一个角度的正弦 |
| SQRT() | 返回一个数的平方根 |
| TAN() | 返回一个角度的正切 |
关于具体DBMS所支持的算术处理函数，请参阅相应的文档。
8.3 此章小结
文本函数时间与日期函数数值函数函数几大类还有系统函数，格式化函数等。此章只要讲前三种函数。
8.4 章节练习题
我们的商店已经上线了，正在创建顾客账户。所有用户都需要登录名，默认登陆名是其名称和所在城市的组合。编写SQL语句，返回顾客ID（cust_id）、顾客名称（cust_name）和登录名（user_login），其中登录名全部为大写字母，并由顾客联系人的前两个字符（cust_contact）和其所在城市的前三个字符（cust_city）组成。例如，我的登录名是BEOAK（Bend Forta，居住在Oak Park）。提示：需要使用函数、拼接和别名。
思路分析：
首先根据以上题目信息，可以推测出我们需要用到的数据库表是Customers，我们先看到这个表中所有的列名和其中所需要的几个信息的情况，其中登录名需要拼接字段（上个章节中MySQL用到的文本拼接函数
CONCAT
AS
LEFT
UPPER
解题：
下图是不同DBMS的不同答案：
编写SQL语句，返回2020年1月所有订单的订单号（order_num）和订单日期（order_date）,并按订单日期（order_date）排序。你应该能够根据已学的指示来解决此问题，但也可以开卷查阅DBMS文档。
解题思路：
锁定到具体的年月，在MySQL中用方便的
DATE_FORMAT（）
ORDER BY
解题：
上图为此题MySQL中使用
DATE_FORMATE()
拓展：
在 MySQL 的日期格式化函数（如
DATE_FORMAT()
%y
%Y
%y
示例：
SELECT DATE_FORMAT('2024-09-25', '%y'); -- 结果：'24'
SELECT DATE_FORMAT('1998-05-10', '%y'); -- 结果：'98'
%Y
示例：
SELECT DATE_FORMAT('2024-09-25', '%Y'); -- 结果：'2024'
SELECT DATE_FORMAT('1998-05-10', '%Y'); -- 结果：'1998'
实际应用场景：
假设需要按“年份-月份”格式显示日期，两种格式符的区别如下：
-- 四位数年份 + 两位数月份（推荐，避免歧义）
SELECT DATE_FORMAT('2024-09-25', '%Y-%m'); -- 结果：'2024-09'
-- 两位数年份 + 两位数月份（适合简短显示，可能有歧义）
SELECT DATE_FORMAT('2024-09-25', '%y-%m'); -- 结果：'24-09'
使用时需根据需求选择：若需明确区分世纪（如 2024 与 1924），必须用
%Y
%y
在 MySQL 的日期格式化函数（如
DATE_FORMAT()
%M
%m
%m
示例：
SELECT DATE_FORMAT('2024-01-15', '%m'); -- 结果：'01'（1月）
SELECT DATE_FORMAT('2024-10-05', '%m'); -- 结果：'10'（10月）
%M
示例：
SELECT DATE_FORMAT('2024-01-15', '%M'); -- 结果：'January'（1月）
SELECT DATE_FORMAT('2024-10-05', '%M'); -- 结果：'October'（10月）
实际应用示例：
-- 显示 "月份数字-年份"（如 09-2024）
SELECT DATE_FORMAT('2024-09-25', '%m-%Y'); -- 结果：'09-2024'
-- 显示 "完整月份名 年份"（如 September 2024）
SELECT DATE_FORMAT('2024-09-25', '%M %Y'); -- 结果：'September 2024'
总结：
%m
%M
九、 汇总数据
这一刻介绍什么是SQL的聚集函数，如何利用它们汇总表的数据
9.1 聚集函数
我们经常需要汇总数据而不用把他们实际检索出来，谓词SQL提供了专门的函数。使用这些函数，SQL查询可用于检索数据，以便分析和报表生成。这种类型的检索例子有：
确定表中行数（或者满足某个条件或包含某个特定值的行数）；获得表中某些行的和；找出表列（或所有行或某些特定的行）的最大值、最小值、平均值。
上述例子都需要汇总表中的数据，而不需要查出数据本身。因此返回实际表数据纯属浪费时间和处理资源（更不用说带宽了）。再说一遍，我们实际想要的是汇总信息。
为了方便你这种类型的检索，SQL给出了5个聚集函数，见下表。
| 函数 | 说明 |
|---|---|
| AVG() | 返回某列的平均值 |
| COUNT() | 返回某列的行数 |
| MAX() | 返回某列的最大值 |
| MIN() | 返回某列的最小值 |
| SUM() | 返回某列值之和 |
这些函数能进行上述检索。与前一张介绍的数据处理函数不同，SQL的聚集函数在各个主要SQL视线中得到了相当一致的支持。
聚集函数（aggregate function）
对某些行运行的函数，计算并返回一个值。
下面说明各函数的使用。
9.1.1 AVG（）函数
AVG()
下面例子使用AVG（）返回Products表中所有产品的平均价格：
AVG（）
这条SELECT语句与之前一条的不同指出在于，它包含了WHERE子句。此WHERE子句仅过滤出vend_id为DLL01的产品。因此avg_price中返回的值指示该供应商产品的平均值。
注意：只用于单个列
AVG（）只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出。为了获得多个列的
平均值，必须使用多个AVG（）函数。只有一个例外是要从多个列计算出一个值时，本章后面讲。
说明：NULL值
AVG（）函数忽略列值为NULL的行
9.1.2 COUNT（）函数
COUNT（）
COUNT（）
COUN（）
使用
COUNT（*）
空值（NULL）
COUNT（column）
下面例子返回Customers表中顾客的总数：
在此例子中，利用
COUNT(*)
下面的例子只对具有电子邮件地址的客户计数：
这条SELECT语句使用COUNT（cust_email）列中有值的行进行计数。在此例子中，cust_email的计数为3（表示5个顾客中有3个顾客有电子邮件地址）
说明：NULL值
如果指定列名，则COUNT（）函数会忽略指定列的值为NULL的行，但如果COUNT（）函数中用的
是星号（*），则不忽略。
9.1.3 MAX（）函数
MAX（）返回指定列中的最大值。MAX（）要求指定列名，如下所示：
这里，MAX（）返回Products表中最贵的物品的价格。
提示：对非数值数据使用MAX（）
虽然MAX（）一般用来找出最大的数值或日期值，但许多（并非所有）DBMS允许将它用来返回
任意列中的最大值。在用于文本数据时，MAX（）返回按该列排序后的最后一行。
说明：NULL值
MAX（）函数忽略列值为NULL的行。
9.1.4 MIN（）函数
MIN（）的功能正好与MAX（）功能相反，它返回指定列的最小值。与MAX（）一样，MIN（）要求指定列名，如下所示：
其中
MIN（）
提示：对非数值数据使用MIN（）
虽然MIN（）一般用来找出最小的数值或日期值，但许多（并非所有）DBMS允许将他用来返回任
意列中的最小值，包括返回文本列中的最小值。在用于文本数据时，MIN（）返回该列排序后最
前面的行。
说明：NULL值
MIN（）函数忽略列值为NULL的行。
9.1.5 SUM（）函数
SUM（）
下面举一个例子，OrderItems包含订单中实际的物品，每个物品有相应的数量。可如下检索所订购物品的总数（所有quantity值之和）。
函数
SUM(quantity)
SUM()
函数
SUM(item_price*quantity)
提示：在多个列上进行计算
如本例所示，利用标准的算数操作符，所有聚集函数都可用来执行多个列上的计算。
说明：NULL值
SUM（）函数忽略列值为NULL的行。
9.2 聚集不同值
以上5个聚集函数都可以如下使用
对所有行执行计算，指定ALL参数或不指定参数（因为ALL时默认行为）。
只包含不同的值，指定DISTINCT参数。
提示：ALL为默认
ALL参数不需要指定，因为它时默认行为。如果不指定DISTINCT，则假定为ALL。
下面例子使用AVG（）函数返回特定供应商提供的产品的平均价格。它与上面的SELECT语句相同，但是用了DISTINCT参数，因此平均值只考虑各个不同的价格：
可以看到，在使用了
DISTINCT
注意：DISTINCT不能用于COUNT（*）
如果指定列名，则DISTINCT 只能用于COUNT（）。DISTINCT（）不能用与COUNT（*）。类似
地，DISTINCT必须使用列名，不能用于计算或者表达式。
提示：将DISTINCT（）用于MIN（）和MAX（）
虽然DISTINCT（）从技术上可用于MIN（）和MAX（），但这样做实际上没有价值。一个列中的
最小值和最大值不管是否只考虑不同值，结果都是相同的。
说明：其他聚集参数
除了这里介绍的DISTINCT和ALL参数，有的DBMS还支持其他参数，如支持对查询结果的自己进行
计算的TOP和TOP PERCENT。为了解具体的DBMS支持哪些参数，请查阅相应的文档。
9.3 组合聚集函数
目前为止的所有句句函数例子都只涉及单个函数。但实际上，SELECT子句可根据需要包含多个聚集函数。请看下面的例子：
这里用单挑SELECT语句执行了4个聚集计算，返回4个值（Products表中物品的数目，产品价格的最高值、最低值以及平均值）。
注意：取别名
在指定列名已包含某个聚集函数的结果时，不应该使用表中实际的列名。虽然这样做也算合法，
但许多SQL实现不支持，可能会产生模糊的错误消息。
9.4 此章小结
聚集函数都有哪些（AVG、COUNT、MAX、MIN、SUM），他们作用与列名跟随SELECT子句。对NULL值和DISTINCT参数的注意。如何使用多个聚集函数在一个SELECT子句中。
9.5 章节练习题
编写SQL语句，确定已售出产品的总数（使用OrderItems中的quantity列）。
思路解析：
首先我们需要看产品表OrderItems中都有哪些列，其中的quantity为售出产品数。要求已售出产品的总数用到
SUM（）
修改刚刚创建的语句，确定已经售出的产品（prod_id）BR01的总数。
解题思路：
确定已经售出产品（prod_id）BR01的总数，此时需要加入WHERE子句限制筛选条件。解题如下：
编写SQL语句，确定Products表中价格不超过10美元的最贵产品的价格（prod_price）。将计算所得的字段命名为max_price。
解题思路：
此题首先是确定新的表Products中最贵产品的价格，这个时候用到聚集函数MAX（）来筛选出最贵产品，再加入条件子句WHERE限定筛选条件。解题如下：
十、 分组函数
这一章介绍如何分组数据，以便汇总表内容的子集。这涉及两个新SELECT子句：
GROUP BY
HAVING
10.1 数据分组
从上一章得知，使用SQL聚集函数可以汇总数据。这样，我们就能够对行进行计数，计算和与平均数，不检索所有数据就获得最大值和最小值。
目前为止的所有计算都是在表的所有数据或匹配特定的WHERE子句的数据上进行的。比如下面的例子返回供应商DLL01提供的产品数目：
如果要返回每个供应商提供的产品数目，该怎么办？或者返回只提供一项产品的供应商的产品，或者返回提供10个以上产品的供应商的产品，该怎么办？
这就是分组大显身手的时候了。使用分组可以将数据分为多个逻辑组，对每个组进行聚集计算。
10.2 创建分组
分组是使用SELECT语句的
GROUP BY
上面的SELECT语句制定了两个列：vend_id包含产品供应商的ID；num_prods为计算字段（用COUNT（*）函数建立）。GROUP BY子句指示DBMS按vend_id排序并分组数据。这就会对每个vend_id而不是整个表计算num_prods一次。从输出中可以看到，供应商BRS01有3个产品，供应商DLL01有4个产品，而供应商FNG01有2个产品。
因为使用了
GROUP BY
GROUP BY
在使用GROUP BY子句前，需要知道一些重要的规定。
GROUP BY子句可以包含任意数目的列，因而可以对分组进行嵌套，更细致地进行数据分组。
说明：通过多列分组，可从粗到细逐级划分数据，最后按最细粒度汇总。
示例：
假设有员工表
employees
department
job
salary
-- 先按部门分组，再按职位分组（嵌套分组）
SELECT
department, -- 第一级分组
job, -- 第二级分组（更细致）
COUNT(*) AS emp_count, -- 每个部门+职位组合的人数
AVG(salary) AS avg_salary -- 每个组合的平均工资
FROM employees
GROUP BY department, job; -- 按顺序嵌套分组
结果：会先按部门分成大组，每个部门内再按职位分成小组，最终显示每个（部门+职位）组合的统计数据。
如果在GROUP BY子句中嵌套了分组，数据将在最后指定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）
说明：分组顺序决定汇总粒度，最后一个分组列是最细的汇总单位。
示例：
基于上述员工表，按
department
job
SELECT department, job, COUNT(*)
FROM employees
GROUP BY department, job; -- 最后分组列是 job
结果：汇总结果以「部门+职位」为单位，而非单独的部门或职位。若调换顺序
GROUP BY job, department
GROUP BY子句中列出的每一列都
必须是检索列或有效的表达式（但不能是聚集函数）
*指定相同的表达式*
说明：
GROUP BY 中不能使用 SELECT 里定义的别名若 SELECT 用表达式，GROUP BY 必须重复该表达式
错误示例：
-- 错误1：GROUP BY 使用别名（alias）
SELECT
CONCAT(department, '_', job) AS dept_job, -- 表达式定义别名
COUNT(*)
FROM employees
GROUP BY dept_job; -- 错误：不能用别名
-- 错误2：GROUP BY 表达式与 SELECT 不一致
SELECT
department,
YEAR(hire_date) AS hire_year, -- SELECT 用 YEAR(hire_date)
COUNT(*)
FROM employees
GROUP BY department, hire_date; -- 错误：应使用 YEAR(hire_date)
正确示例：
-- 正确：GROUP BY 重复 SELECT 中的表达式，不使用别名
SELECT
CONCAT(department, '_', job) AS dept_job,
COUNT(*)
FROM employees
GROUP BY CONCAT(department, '_', job); -- 重复表达式
-- 正确：GROUP BY 与 SELECT 表达式一致
SELECT
department,
YEAR(hire_date) AS hire_year,
COUNT(*)
FROM employees
GROUP BY department, YEAR(hire_date); -- 与 SELECT 表达式一致
大多数SQL实现
不允许GROUP BY列带有长度可变的数据类型（如文本或备注型字段）
说明：MySQL 对
TEXT
LONGTEXT
错误示例：
-- 假设表中有 text 类型的 `description` 字段
SELECT description, COUNT(*)
FROM products
GROUP BY description; -- 错误：text 类型不能用于 GROUP BY
解决方法：
通过函数转换为固定长度（如取前 N 个字符）：
SELECT
LEFT(description, 100) AS short_desc, -- 截取前100字符
COUNT(*)
FROM products
GROUP BY LEFT(description, 100); -- 按转换后的值分组
除聚集计算语句外，SELECT语句中的每一列都必须在GROUP BY 子句中给出。
说明：非聚合字段（如普通列）必须出现在 GROUP BY 中，否则违反
ONLY_FULL_GROUP_BY
错误示例：
-- 错误：SELECT 中的 job 未在 GROUP BY 中（非聚合字段）
SELECT
department,
job, -- 非聚合字段，未在 GROUP BY 中
AVG(salary)
FROM employees
GROUP BY department;
正确示例：
-- 正确：所有非聚合字段都在 GROUP BY 中
SELECT
department,
job,
AVG(salary)
FROM employees
GROUP BY department, job; -- 包含 department 和 job
如果分组列中包含具有NULL值的行，则NULL将作为一个分组返回。如果列中有多行NULL值，他们将分为一组。
说明：所有 NULL 值会被归为一个分组，而非单独处理。
示例：
假设
department
SELECT
department, -- 包含 NULL 值
COUNT(*) AS emp_count
FROM employees
GROUP BY department;
结果：会出现一行
department
NULL
GROUP BY 子句必须出现在WHERE子句之后，ORDER BY子句之前。
说明：SQL 语句执行顺序要求：
WHERE
GROUP BY
HAVING
ORDER BY
错误示例：
-- 错误1：GROUP BY 在 WHERE 之前
SELECT department, COUNT(*)
FROM employees
GROUP BY department -- 错误：应在 WHERE 之后
WHERE salary > 5000;
-- 错误2：GROUP BY 在 ORDER BY 之后
SELECT department, COUNT(*)
FROM employees
WHERE salary > 5000
ORDER BY department
GROUP BY department; -- 错误：应在 ORDER BY 之前
正确示例：
-- 正确顺序：WHERE → GROUP BY → ORDER BY
SELECT department, COUNT(*) AS emp_count
FROM employees
WHERE salary > 5000 -- 先过滤高工资员工
GROUP BY department -- 再按部门分组
ORDER BY emp_count DESC; -- 最后按人数排序
提示：ALL子句
SQL Server等有些SQL实现在 GROUP BY中支持可选的ALL子句。这个子句可用来返回
所有分组，即使是没有匹配昂的分组也返回（在此情况下，聚集将返回NULL）。具体的
DBMS是否支持ALL，请参阅相应文档。
MySQL不支持GROUP BY ALL 子句。
在 MySQL 中，不支持
GROUP BY ALL
具体说明：
MySQL 不支持
GROUP BY ALL
若在 MySQL 中尝试使用
GROUP BY ALL
-- 以下语句在 MySQL 中会报错
SELECT prod_id, COUNT(*)
FROM products
GROUP BY ALL prod_id;
错误信息类似：
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ALL prod_id'
MySQL 中如何实现类似效果？
虽然没有
GROUP BY ALL
利用
LEFT JOIN
WHERE
JOIN
ON
示例：
沿用之前的
products
orderitems
-- MySQL 中正确实现“保留所有分组”的方式
SELECT
p.prod_id,
p.prod_name,
SUM(o.quantity) AS total_sales -- 未销售的产品会显示 NULL
FROM products p
LEFT JOIN orderitems o
ON p.prod_id = o.prod_id
AND o.order_id > 100 -- 过滤条件放在 ON 中，不影响主表记录
GROUP BY p.prod_id, p.prod_name; -- 按产品分组
结果：
即使某产品（如 prod_id=3 的平板）没有符合条件的订单，也会被保留在结果中，聚合函数结果为
NULL
| prod_id | prod_name | total_sales |
|---|---|---|
| 1 | 手机 | 3 |
| 2 | 电脑 | 1 |
| 3 | 平板 | NULL |
总结：
MySQL 不支持
GROUP BY ALL
LEFT JOIN
ON
GROUP BY ALL
GROUP BY
注意：通过相对位置指定列
有的SQL实现允许根据SELECT列表中的位置指定GROUP BY的列。例如，GROUP BY 2，1克表示按选择的第二个列分组，然后
再按第一个列分组。虽然这种速记语法很方便，但并非所有的SQL实现都支持，并且使用它容易在编辑SQL语句时出错。
10.3 过滤分组
除了能用GROUP BY 分组数据外，SQL还允许过滤分组，规定包括哪些分组，排除那些分组。例如，你可能想要列出至少有两个订单的所有顾客。为此，必须基于完整的分组而不是个别的行进行顾虑。
我们已经看到了WHERE子句的作用（第4章提及）。但是，在这个例子中WHERE不能完成任务，因为WHERE过滤指定的是行而不是分组。事实上，WHERE没有分组的概念。
那么，不使用WHERE使用什么呢？SQL谓词提供了另一个子句，就是
HAVING
HAVING
WHERE
提示：HAVING支持所有WHERE操作符
再第4章和第5章中，我们学习了WHERE子句的条件（包括通配符条件和带多个操作符的子句）。学过的这些有关WHERE的所欲计
数和选项都适用于HAVING。他们的句法都是相同的，只是关键字有差别。
那么，怎么过滤分组呢？请看以下例子：
这条SELECT语句的前三行类似于上面的语句。最后一行增加了HAVING子句，他过滤了COUNT（*）>=2（两个以上订单）的那些分组。
可以看到，WHERE子句在这里不起作用，因为过滤是基于分组聚集值，而不是特定行的值。
说明：HAVING和WHERE的区别
这里有另一种理解方法，WHERE在数据分组前进行过滤，HAVING在数据分组后尽心顾虑。这是一个重要的区别，WHERE排除的
行不包括在分组中。这可能会改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组。
那么，有没有在一条语句中同时使用WHERE和HAVING子句的需要呢？事实上，确实有。加入想进一步过滤上面的语句，使它返回过去12个月内具有两个以上订单的顾客。为此，可增加一条WHERE子句，过滤出过去12个月内下过的订单，然后再增加HAVING子句过滤出具有两个以上订单的分组。
为了更好理解，来看下面的例子。它列出具有两个以上产品且价格大于等于4的供应商：
这条语句中没第一行是使用了聚集函数的基本SELECT语句，很像前面的例子。WHERE子句过滤所有prod_price至少为4的行，然后按vend_id分组数据，HAVING子句过滤计数为2或2以上的分组。如果没有WHERE子句，就会多检索一行（供应商DLL01，销售4个产品，价格都在4以下）：
说明：使用HAVING和WHERE
HAVING与WHERE非常类似，如果不指定GROUP BY，则大多数DBMS会同等对待他们。不过，你自己要能区分这一点。使用
HAVING时应该结合GROUP BY子句，而WHERE子句用于标准的行级过滤。
10.4 分组和排序
GROUP BY
ORDER BY
| ORDER BY | GROUP BY |
|---|---|
| 对产生的输出排序 | 对行分组，但输出可能不是分组的顺序 |
| 任意列都可以使用（甚至非选择的列也可以使用） | 只可能使用选择列或表达式列，而且必须使用每个选择列表达式 |
| 不一定需要 | 如果与聚集函数一起使用列（或表达式），则必须使用 |
上表中列出的第一项差别极为重要。我们经常发现，用GROUP BY分组的数据确实时以分组顺序输出的。但并不是总是这样，这不是SQL规范所要求的。此外，即使特定的DBMS总是按给出的GROUP BY子句排序数据，用户也可能会要求以不同的顺序排序。就因为你以某种方式分组数据（获得特定的分组聚集值），并不表示你需要以相同的方式排序输出。应该提供明确的ORDER BY子句，即使其效果等同于GROUP BY子句。
提示：不要忘记ORDER BY
一般在使用GROUP BY子句时，应该也给出ORDER BY子句。这是保证数据正确排序的唯一方法。千万不要仅以来GROUP BY 排
序数据。
为说明GROUP BY 和ORDER BY 的使用方法，来看一个例子。下面的SELECT语句类似于前面那些例子。它检索包含三个或更多物品的订单和订购物品数目：
要按订购物品的数目排序输出，需要添加ORDER BY 子句，如下所示：
在这个例子中，使用GROUP BY 子句按订单号（order_num列）分组数据，以便COUNT（*）函数能够返回每个订单中的物品数目。HAVING子句过滤数据，使得只返回包含三个或更多物品的订单。最后用ORDER BY子句排序输出。
10.5 SELECT子句顺序
下面回顾一下SELECT语句中子句的顺序。下表以在SELECT语句中使用时必须遵循的次序，列出迄今为止所学过的子句。
| 子句 | 说明 | 是否必须使用 |
|---|---|---|
| SELECT | 要返回的列或表达式 | 是 |
| FROM | 从中检索数据的表 | 仅在从表选择数据时使用 |
| WHERE | 行级过滤 | 否 |
| GROUP BY | 分组说明 | 仅在按组计算聚集时使用 |
| HAVING | 组级过滤 | 否 |
| ORDER BY | 输出排序顺序 | 否 |
10.6 此章小结
分组函数GROUP BY对数据进行分组，需要注意的六点可以看以上小结，与聚集函数一起使用。HAVING过滤分组，和WHERE的区别。分组和排序的区别，SELECT子句顺序。
10.7 章节练习题
OrderItems表包含每个订单的每个产品。编写SQL语句，返回每个订单号（order_num）各有多少行数（order_lines），并按order_lines对结果进行排序。
编写SQL语句，返回名为cheapest_item的字段，该字段包含每个供应商成本最低的产品（使用Products表中的prod_price），然后从最低成本到最高成本对结果进行排序。
确定最佳顾客非常重要，请编写SQL语句,返回至少含100项的所有订单的订单号（OrderItems表中的order_num）。
确定最佳顾客的另一种方式是看他们花了多少钱。编写SQL语句，返回总价至少为1000的所有订单的订单号（OrderItems表中的order_num）。提示：需要计算总和（item_price乘以quantity）。按订单号对结果进行排序。
下面的SQL语句有问题吗？（尝试在不运行的情况下指出）
SELECT order_num,COUNT(*) AS items
FROM OrderItems
GROUP BY items
HAVING COUNT(*)>=3
ORDER BY items,order_num;
解：
© 版权声明
文章版权归作者所有，未经允许请勿转载。
相关文章
暂无评论...
