mysql通配符使用 《MySQL必知必会》读书笔记1

学习《MySQL必知必会》，记录知识。

常用命令

返回当前选择的数据库内可用表的列表：SHOW TABLES;

DESCRIBE customers;是

SHOW COLUMNS FROM customers;的一种快捷方式。

? SHOW STATUS，用于显示广泛的服务器状态信息；

? SHOW CREATE DATABASE和SHOW CREATE TABLE，分别用来显示创

建特定数据库或表的MySQL语句；

? SHOW GRANTS，用来显示授予用户（所有用户或特定用户）的安

全权限；

? SHOW ERRORS和SHOW WARNINGS，用来显示服务器错误或警告消息。关键字

不能部分使用DISTINCT。DISTINCT关键字应用于所有列而不仅是前置它的列。如果给出SELECT DISTINCT vend_id, prod_price，除非指定的两个列都不同，否则所有行都将被检索出来

LIMIT 5，指示MySQL返回.不多于5行。

LIMIT 5， 5指示MySQL返回从行5开始的5行。第一个数为开始 位置，第二个数为要检索的行数。（下标从0开始）

ORDER BY 列名：根据某列进行排序

ORDER BY 列名1，列名2：先根据列名1进行排序，列名1相同再根据列名2进行排序

ORDER BY 列名 DESC：根据某列进行降序排序

ORDER BY 列名1 DESC，列名2：先根据列名1进行降序排序，列名1相同再根据列名2进行（升序）排序

DESC只应用到直接位于其前面的列名。

在多个列上降序排序如果想在多个列上进行降序排序，必须对每个列指定DESC关键字。

与DESC相反的关键字是ASC（ASCENDING），在升序排序时可以指定它。 但实际上，ASC没有多大用处，因为升序是默认的（如果既不指定ASC也 不指定DESC，则假定为ASC）。

使用ORDER BY和LIMIT的组合，能够找出一个列中最高或最低的值。

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

WHERE可包含任意数目的AND和OR操作符。允许两者结合以进行复杂和高级的过滤。此处存在计算次序问题。SQL（像多数语言一样）在处理OR操作符前，优先处理AND操作符。

此问题的解决方法是使用圆括号明确地分组相应的操作符。

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

%：匹配0个、1个或多个字符

_：匹配单个字符

尾空格可能会干扰通配符匹配。

虽然似乎%通配符可以匹配任何东西，但有一个例外，即NULLMySQL正则表达式仅为正则表达式语言的一个子集 如果你熟悉正则表达式，需要注意：MySQL仅支持多数正则表达式实现的一个很小的子集。1 基本字符匹配

REGEXP

LIKE与REGEXP

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

如果执行上述两条语句，会发现第一条语句不返回数据，而第二条语句返回一行。为什么？

LIKE匹配整个列。如果被匹配的文本在列值中出现，LIKE将不会找到它，相应的行也不被返回（除非使用通配符）。而REGEXP在列值内进行匹配，如果被匹配的文本在列值中出现，REGEXP将会找到它，相应的行将被返回。这是一个非常重要的差别。

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

匹配不区分大小

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

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

使用|从功能上类似于在SELECT语句中使用OR语句，多个OR条件可并入单个正则表达式。

两个以上的OR条件

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

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

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

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

集合可用来定义要匹配的一个或多个字符。例如，下面的集合将匹配数字0到9：[0123456789] .

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

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

5 匹配特殊字符

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

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

可以使用预定义的字符集，称为字符类 （character class）。

7 匹配多个实例

目前为止使用的所有正则表达式都试图匹配单次出现。如果存在一个匹配，该行被检索出来，如果不存在，检索不出任何行。但有时需要对匹配的数目进行更强的控制。例如，你可能需要寻找所有的数，不管数中包含多少数字，或者你可能想寻找一个单词并且还能够适应一个尾随的s（如果存在），等等。

举例：

匹配连载一起的4位数字：

需要注意的是，在使用正则表达式时，编写某个特殊的表达式几乎总是有不止一种方法。上面的例子也可以如下编写：

8 定位符

目前为止的所有例子都是匹配一个串中任意位置的文本。为了匹配特定位置的文本，需要使用表9-4列出的定位符。

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

^的双重用途

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

使REGEXP起类似LIKE的作用

前面说过，LIKE和REGEXP的不同在于，LIKE匹配整个串而REGEXP匹配子串。利用定位符，通过用^开始每个表达式，用$结束每个表达式，可以使REGEXP的作用与LIKE一样创建计算字段1 拼接字段多数DBMS使用+或||来实现拼接，MySQL则使用Concat()函数来实现。当把SQL语句转换成MySQL语句时一定要把这个区别铭记在心。

2 去除空格

通过删除数据右侧多余的空格来整理数据，这可以使用MySQL的RTrim()函数来完成，如下所示：

Trim函数

MySQL除了支持RTrim()（正如刚才所见，它去掉串右边的空格），还支持LTrim()（去掉串左边的空格）以及Trim()（去掉串左右两边的空格）。3 执行算术计算

MySQL支持表10-1中列出的基本算术操作符。此外，圆括号可用来区分优先顺序。

如何测试计算

SELECT提供了测试和试验函数与计算的一个很好的办法。虽然SELECT通常用来从表中检索数据，但可以省略FROM子句以便简单地访问和处理表达式。例如，SELECT 3*2;将返回6，SELECT Trim('abc');将返回abc，而SELECT Now()利用Now()函数返回当前日期和时间。通过这些例子，可以明白如何根据需要使用SELECT进行试验。函数

函数没有SQL的可移植性强

能运行在多个系统上的代码称为可移植的（portable）。相对来说，多数SQL语句是可移植的，在SQL实现之间有差异时，这些差异通常不那么难处理。而函数的可移植性却不强。几乎每种主要的DBMS的实现都支持其他实现不支持的函数，而且有时差异还很大。

为了代码的可移植，许多SQL程序员不赞成使用特殊实现的功能。虽然这样做很有好处，但不总是利于应用程序的性能。如果不使用这些函数，编写某些应用程序代码会很艰难。必须利用其他方法来实现DBMS非常有效地完成的工作。如果你决定使用函数，应该保证做好代码注释，以便以后你（或其他人）能确切地知道所编写SQL代码的含义。1 文本处理函数

上面已经看过一个文本处理函数的例子，其中使用RTrim() 函数来去除列值右边的空格。下面是另一个例子，这次使用Upper()函数：

以下是某些常用的文本处理函数：

2 日期和时间处理函数

应该总是使用4位数字的年份

支持2位数字的年份，MySQL处理00-69为2000-2069，处理70-99为1970-1999。虽然它们可能是打算要的年份，但使用完整的4位数字年份更可靠，因为MySQL不必做出任何假定。

基本的日期，举例说明：

但是，使用WHERE order_date = '2005-09-01'可靠吗？order_ date的数据类型为datetime。这种类型存储日期及时间值。样例表中的值全都具有时间值00:00:00，但实际中很可能并不总是这样。如果用当前日期和时间存储订单日期（因此你不仅知道订单日期，还知道下 订 单 当 天 的 时 间 ）， 怎 么 办 ？ 比 如 ， 存 储 的 order_date 值为2005-09-01 11:30:05，则WHERE order_date = '2005-09-01'失败。 即使给出具有该日期的一行，也不会把它检索出来，因为WHERE匹配失败。

解决办法是指示MySQL仅将给出的日期与列中的日期部分进行比较，而不是将给出的日期与整个列值进行比较。为此，必须使用Date()函数。Date(order_date)指示MySQL仅提取列的日期部分，更可靠的SELECT语句为：

如果要的是日期，请使用Date()

如果你想要的仅是日期，则使用Date()是一个良好的习惯，即使你知道相应的列只包含日期也是如此。这样，如果由于某种原因表中以后有日期和时间值，你的SQL代码也不用改变。当然，也存在一个Time()函数，在你只想要时间时应该使用它。Date()和Time()都是在MySQL 4.1.1中第一次引入的。

如果你想检索出2005年9月下的所有订单，怎么办？简单的相等测试不行，因为它也要匹配月份中的天数。有几种解决办法，其中之一如下所示：

还有另外一种办法（一种不需要记住每个月中有多少天或不需要操心闰年2月的办法）：

3 数值处理函数

聚集函数

1 AVG()函数

AVG()通过对表中行数计数并计算特定列值之和，求得该列的平均值。AVG()可用来返回所有列的平均值，也可以用来返回特定列或行的平均值。

只用于单个列

AVG()只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出。为了获得多个列的平均值，必须使用多个AVG()函数。

NULL值

AVG()函数忽略列值为NULL的行。2 COUNT()函数

COUNT()函数进行计数。可利用COUNT()确定表中行的数目或符合特定条件的行的数目。

COUNT()函数有两种使用方式。

? 使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。

? 使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。

下面的例子返回customers表中客户的总数：

下面的例子只对具有电子邮件地址的客户计数：

NULL值

如果指定列名，则指定列的值为空的行被COUNT()函数忽略，但如果COUNT()函数中用的是星号（*），则不忽略3 MAX()函数

MAX()返回指定列中的最大值。MAX()要求指定列名，如下所示：

对非数值数据使用MAX()

虽然MAX()一般用来找出最大的数值或日期值，但MySQL允许将它用来返回任意列中的最大值，包括返回文本列中的最大值。在用于文本数据时，如果数据按相应的列排序，则MAX()返回最后一行。

NULL值

MAX()函数忽略列值为NULL的行。 4 MIN()函数

MIN()的功能正好与MAX()功能相反，它返回指定列的最小值。与MAX()一样，MIN()要求指定列名，如下所示：

对非数值数据使用MAX()

MIN()函数与MAX()函数类似，MySQL允许将它用来返回任意列中的最小值，包括返回文本列中的最小值。在用于文本数据时，如果数据按相应的列排序，则MIN()返回最前面的行。

NULL值

MIN()函数忽略列值为NULL的行。 5 SUM()函数

SUM()用来返回指定列值的和（总计）。

下面举一个例子，orderitems表包含订单中实际的物品，每个物品有相应的数量（quantity）。可如下检索所订购物品的总数（所有quantity值之和）：

SUM()也可以用来合计计算值。在下面的例子中，合计每项物品的item_price*quantity，得出总的订单金额：

在多个列上进行计算

如本例所示，利用标准的算术操作符， 所有聚集函数都可用来执行多个列上的计算。

NULL值

SUM()函数忽略列值为NULL的行。6 聚集不同值

MySQL 5 及后期版本

下面将要介绍的聚集函数的DISTINCT的使用，已经被添加到MySQL 5.0.3中。下面所述内容在MySQL 4.x中不能正常运行。

以上5个聚集函数都可以如下使用：

? 对所有的行执行计算，指定ALL参数或不给参数（因为ALL是默认行为）；

? 只包含不同的值，指定DISTINCT参数。

ALL为默认

ALL参数不需要指定，因为它是默认行为。如果不指定DISTINCT，则假定为ALL。

下面的例子使用AVG()函数返回特定供应商提供的产品的平均价格。它与上面的SELECT语句相同，但使用了DISTINCT参数，因此平均值只考虑各个不同的价格：

注意

如果指定列名，则DISTINCT只能用于COUNT()。DISTINCT 不能用于COUNT(*)，因此不允许使用COUNT（DISTINCT），否则会产生错误。类似地，DISTINCT必须使用列名，不能用于计算或表达式。

将DISTINCT用于MIN()和MAX()

虽然DISTINCT从技术上可用于MIN()和MAX()，但这样做实际上没有价值。一个列中的最小值和最大值不管是否包含不同值都是相同的。7 组合聚集函数

实际上SELECT语句可根据需要包含多个聚集函数。请看下面的例子：

分组数据

分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算。

1 创建分组

在具体使用GROUP BY子句前，需要知道一些重要的规定。

? GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。

? 如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。

? GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。

? 除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出。

? 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。

? GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

2 过滤分组

除了能用GROUP BY分组数据外，MySQL还允许过滤分组，规定包括哪些分组，排除哪些分组。例如，可能想要列出至少有两个订单的所有顾客。为得出这种数据，必须基于完整的分组而不是个别的行进行过滤。

HAVING非常类似于WHERE。事实上，目前为止所学过的所有类型的WHERE子句都可以用HAVING来替代。唯一的差别是WHERE过滤行，而HAVING过滤分组。HAVING支持所有WHERE操作符。

HAVING和WHERE的差别

这里有另一种理解方法，WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。这是一个重要的区别，WHERE排除的行不包括在分组中。这可能会改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组。

同时使用WHERE和HAVING子句

列出具有2个（含）以上、价格为10（含）以上的产品的供应商：

3 分组和排序

虽然GROUP BY和ORDER BY经常完成相同的工作，但它们是非常不同的。表13-1汇总了它们之间的差别。

不要忘记ORDER BY

一般在使用GROUP BY子句时，应该也给出ORDER BY子句。这是保证数据正确排序的唯一方法。千万不要仅依赖GROUP BY排序数据。

举例：

检索总计订单价格大于等于50的订单的订单号和总计订单价格：

为按总计订单价格排序输出，需要添加ORDER BY子句

使用子查询1 利用子查询进行过滤

订单存储在两个表中。对于包含订单号、客户ID、订单日期的每个订单，orders表存储一行。各订单的物品存储在相关的orderitems表中。orders表不存储客户信息。它只存储客户的ID。实际的客户信息存储在customers表中。

现在，假如需要列出订购物品TNT2的所有客户，应该怎样检索？

列必须匹配

在WHERE子句中使用子查询（如这里所示），应该保证SELECT语句具有与WHERE子句中相同数目的列。通常，子查询将返回单个列并且与单个列匹配，但如果需要也可以使用多个列。

虽然子查询一般与IN操作符结合使用，但也可以用于测试等于（=）、不等于（）等。

2 作为计算字段使用子查询

假如需要显示customers表中每个客户的订单总数。订单与相应的客户ID存储在orders表中。 为了执行这个操作，遵循下面的步骤。

(1) 从customers表中检索客户列表。

(2) 对于检索出的每个客户，统计其在orders表中的订单数目。

相关子查询（correlated subquery） 涉及外部查询的子查询。

这种类型的子查询称为相关子查询。任何时候只要列名可能有多义性，就必须使用这种语法（表名和列名由一个句点分隔）。

逐渐增加子查询来建立查询

用子查询测试和调试查询很有技巧性，特别是在这些语句的复杂性不断增加的情况下更是如此。用子查询建立（和测试）查询的最可靠的方法是逐渐进行，这与MySQL处理它们的方法非常相同。首先，建立和测试最内层的查询。然后，用硬编码数据建立和测试外层查询，并且仅在确认它正常后才嵌入子查询。这时，再次测试它。对于要增加的每个查询，重复这些步骤。这样做仅给构造查询增加了一点点时间，但节省了以后（找出查询为什么不正常）的大量时间mysql通配符使用，并且极大地提高了查询一开始就正常工作的可能性。联结表

联结是一种机制，用来在一条SELECT语句中关联表，因此称之为联结。使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行。

1 WHERE重要性

可 以 看 到 要 匹 配 的 两 个 列 以vendors.vend_id和products. vend_id指定。这里需要这种完全限定列名，因为如果只给出vend_id，则MySQL不知道指的是哪一个（它们有两个，每个表中一个）。

笛卡儿积（cartesian product）

由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

不要忘了WHERE子句

应该保证所有联结都有WHERE子句，否则MySQL将返回比想要的数据多得多的数据。同理，应该保证WHERE子句的正确性。不正确的过滤条件将导致MySQL返回不正确的数据。2 内部联结

目前为止所用的联结称为等值联结（equijoin），它基于两个表之间的相等测试。这种联结也称为内部联结。其实，对于这种联结可以使用稍微不同的语法来明确指定联结的类型。下面的SELECT语句返回与前面例子完全相同的数据：

使用哪种语法

ANSI SQL规范首选INNER JOIN语法。此外，尽管使用WHERE子句定义联结的确比较简单，但是使用明确的联结语法能够确保不会忘记联结条件，有时候这样做也能影响性能。

性能考虑

MySQL在运行时关联指定的每个表以处理联结。这种处理可能是非常耗费资源的，因此应该仔细，不要联结不必要的表。联结的表越多，性能下降越厉害。3 自联结

假如你发现某物品（其ID为DTNTR）存在问题，因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。此查询要求首先找到生产ID为DTNTR的物品的供应商，然后找出这个供应商生产的其他物品。下面是解决此问题的一种方法：

为解决此问题，使用了表别名。products的第一次出现为别名p1，第二次出现为别名p2。现在可以将这些别名用作表名。例如，SELECT语句使用p1前缀明确地给出所需列的全名。如果不这样，MySQL将返回错误，因为分别存在两个名为prod_id、prod_name的列。MySQL不知道想要的是哪一个列（即使它们事实上是同一个列）。WHERE（通过匹配p1中的vend_id和p2中的vend_id）首先联结两个表，然后按第二个表中的prod_id过滤数据，返回所需的数据。

用自联结而不用子查询

自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。虽然最终的结果是相同的，但有时候处理联结远比处理子查询快得多。应该试一下两种方法，以确定哪一种的性能更好。4 自然联结

自然联结：你只能选择那些唯一的列。这一般是通过对表使用通配符（SELECT *），对所有其他表的列使用明确的子集来完成的。

5 外部联结

联结包含了那些在相关表中没有关联行的行。这种类型的联结称为外部联结。

为了检索所有客户，包括那些没有订单的客户，可如下进行：

没有*=操作符

MySQL不支持简化字符*=和=*的使用，这两种操作符在其他DBMS中是很流行的。

外部联结的类型

存在两种基本的外部联结形式：左外部联结和右外部联结。它们之间的唯一差别是所关联的表的顺序不同。换句话说，左外部联结可通过颠倒FROM或WHERE子句中表的顺序转换为右外部联结。因此，两种类型的外部联结可互换使用，而究竟使用哪一种纯粹是根据方便而定。6 使用带聚集函数的联结

如果要检索所有客户及每个客户所下的订单数，下面使用了COUNT()函数的代码可完成此工作：

（编辑：常州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!