mysql 组合所以_MySQL必知必会（二）

摘自《MySQL必知必会》

4.1 SELECT语句

为了使用SELECT检索表数据，必须至少给出两条信息——想选择什么，以及从什么地方选择。

4.2 检索单个列

SELECT prod_name FROM products;

上述语句利用SELECT 语句从products 表中检索一个名为prod_name的列。

如上的一条简单SELECT语句将返回表中所有行。数据没有过滤(过滤将得出结果集的一个子集)，也没有排序。

注意，SQL语句不区分大小写，因此SELECT与select是相同的。许多SQL开发人员喜欢对所有SQL关键字使用大写，而对所有列和表名使用小写，这样做使代码更易于阅读和调试。

使用空格:在处理SQL语句时，其中所有空格都被忽略。SQL语句可以在一行上给出，也可以分成许多行。多数SQL开发人员认为将SQL语句分成多行更容易阅读和调试。

4.3 检索多个列

SELECT prod_id, prod_name, prod_price FROM products;

4.4 检索所有列

SELECT * FROM products;

一般，除非你确实需要表中的每个列，否则最好别使用*通配符。虽然使用通配符可能会使你自己省事，不用明确列出所需列，但检索不需要的列通常会降低检索和应用程序的性能。

4.5 检索不同的行

SELECT DISTINCT vendor_id FROM products;

SELECT DISTINCT vend_id告诉MySQL只返回不同的vend_id行。

如果使用DISTINCT关键字，它必须直接放在列名的前面。

不能部分使用DISTINCT: DISTINCT关键字作用于所有列而不只是它前置的列。如果给出SELECT DISTINCT vend_id, prod_price; 只会消除这2个字段值均相同的记录。

4.6 限制结果

SELECT prod_name FROM products

LIMIT 5;

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

为得出下一个5行，可指定要检索的开始行和行数，如下所示：

SELECT prod_name FROM products

LIMIT 5,5;

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

检索出来的第一行为行0而不是行1。因此，LIMIT 1, 1将检索出第二行而不是第一行。

LIMIT中指定要检索的行数为检索的最大行数。如果没有足够的行(例如，给出LIMIT 10, 5，但只有13行)，MySQL将只返回它能返回的那么多行。

4.7 使用完全限定的表名

迄今为止使用的SQL例子只通过列名引用列。也可能会使用完全限定的名字来引用列(同时使用表名和列字)。

SELECT products.prod_name FROM products;

表名也可以是完全限定的:

SELECT products.prod_name FROM crashcourse.products;

5.1 排序数据

子句(clause): SQL语句由子句构成，有些子句是必需的，而有的是可选的。一个子句通常由一个关键字和所提供的数据组成。子句的例子有SELECT语句的FROM子句。

为了明确地排序用SELECT语句检索出的数据，可使用ORDER BY子句。ORDER BY子句取一个或多个列的名字，据此对输出进行排序。

SELECT prod_name FROM products

ORDER BY prod_name;

5.2 按多个列排序

SELECT prod_id, prod_price, prod_name

FROM products

ORDER BY prod_price, prod_name;

上述例子中的输出，仅在多个行具有相同的prod_price值时才对产品按prod_name进行排序。如果prod_price列中所有的值都是唯一的，则不会按prod_name排序。

5.3 指定排序方向

数据排序不限于升序排序(从A到Z)。这只是默认的排序顺序。为了进行降序排序，必须指定DESC关键字。

SELECT prod_id, prod_price, prod_name

FROM products

ORDER BY prod_price DESC;

如果打算用多个列排序怎么办？

SELECT prod_id, prod_price, prod_name

FROM products

ORDER BY prod_price DESC, prod_name;

DESC关键字只应用到直接位于其前面的列名。因此，prod_price列以降序排序，而prod_name列(在每个价格内)仍然按标准的升序排序。如果想在多个列上进行降序排序，必须对每个列指定DESC关键字。

区分大小写和排序顺序: 在对文本性的数据进行排序时，A与a相同吗？a位于B之前还是位于Z之后？这些问题不是理论问题，其答案取决于数据库如何设置。在字典(dictionary)排序顺序中，A被视为与a相同，这是MySQL(和大多数数据库管理系统)的默认行为。但是，许多数据库管理员能够在需要时改变这种行为。如果确实需要改变这种排序顺序，用简单的ORDER BY子句做不到。你必须请求数据库管理员的帮助。

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

SELECT prod_price FROM products

ORDER BY prod_price DESC

LIMIT 1;

ORDER BY子句的位置: 在给出ORDER BY子句时，应该保证它位于FROM子句之后。如果使用LIMIT，它必须位于ORDER BY之后。使用子句的次序不对将产生错误消息。

6.1 使用WHERE子句

数据库表一般包含大量的数据，很少需要检索表中所有行。通常只会根据需要提取表数据的子集。只检索所需数据需要指定搜索条件或过滤条件。

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

SELECT prod_name, prod_price FROM products

WHERE prod_price = 2.5;

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

6.2 WHERE子句操作符

= 等于

<> 不等于

!= 不等于

< 小于

<= 小于等于

> 大于

>= 大于等于

BETWEEN 在指定的两个值之间

SELECT prod_name, prod_price FROM products

WHERE prod_name = "fuses";

MySQL在执行匹配时默认不区分大小写，所以fuses与Fuses匹配。

何时使用引号: 单引号用来限定字符串。如果将值与串类型的列进行比较，则需要限定引号。用来与数值列进行比较的值不用引号。

SELECT prod_name, prod_price FROM products

WHERE prod_price BETWEEN 5 AND 10;

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

6.2.4 空值检查

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

SELECT prod_name FROM products

WHERE prod_price IS NULL;

7.1 组合WHERE子句

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

SELECT prod_id, prod_name, prod_price

FROM products

WHERE vend_id = 1003 AND prod_price <= 10;

此SQL语句检索由供应商1003制造且价格小于等于10美元的所有产品的名称和价格。

SELECT prod_name, prod_price

FROM products

WHERE vend_id = 1002 OR vend_id = 1003;

此SQL语句检索由任一个指定供应商制造的所有产品的产品名和价格。

7.1.3 计算次序

WHERE可包含任意数目的AND和OR操作符。允许两者结合以进行复杂和高级的过滤。

SQL(像多数语言一样)在处理OR操作符前，优先处理AND操作符。

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

7.2 IN操作符

IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配。

SELECT prod_name, prod_price

FROM products

WHERE vend_id IN (1002, 1003)

ORDER BY prod_price;

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

IN操作符优点：在使用长的合法选项清单时，IN操作符的语法更清楚且更直观。

IN操作符一般比OR操作符清单执行更快。

IN的最大优点是可以包含其他SELECT语句，使得能够更动态地建立WHERE子句。

7.3 NOT操作符

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

SELECT prod_name, prod_price

FROM products

WHERE vend_id NOT IN (1002, 1003);

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

MySQL支持使用NOT 对IN 、BETWEEN 和EXISTS子句取反，这与多数其他DBMS允许使用NOT对各种条件取反有很大的差别。

8.1 LIKE操作符

通配符(wildcard)： 用来匹配值的一部分的特殊字符。

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

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

8.1.1 百分号(%)通配符

在搜索串中，%表示任何字符出现任意次数。例如，为了找出所有以词jet起头的产品，可使用以下SELECT语句：

SELECT prod_id, prod_name

FROM products

WHERE prod_name LIKE "jet%";

通配符可在搜索模式中任意位置使用，并且可以使用多个通配符。搜索模式'%anvil%'表示匹配任何位置包含文本anvil的值，而不论它之前或之后出现什么字符。

重要的是要注意到，除了一个或多个字符外，%还能匹配0个字符。

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

8.1.2 下划线(_)通配符

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

8.2 使用通配符的技巧

通配符搜索的处理一般要比前面讨论的其他搜索所花时间更长。这里给出一些使用通配符要记住的技巧。不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。

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

9.2 使用MySQL正则表达式

正则表达式的作用是匹配文本，将一个模式(正则表达式)与一个文本串进行比较。MySQL用WHERE子句对正则表达式提供了初步的支持，允许你指定正则表达式，过滤SELECT检索出的数据。

MySQL仅支持多数正则表达式实现的一个很小的子集。

9.2.1 基本字符匹配

SELECT prod_name FROM products

WHERE prod_name REGEXP ".000" ;

输出：

prod_name

JetPack 1000

JetPack 2000

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

MySQL中的正则表达式匹配(自版本3.23.4后)不区分大小写(即，大写和小写都匹配)。为区分大小写，可使用BINARY关键字，如

WHERE prod_name REGEXP BINARY 'JetPack .000'。

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

SELECT prod_name FROM products

WHERE prod_name LIKE "1000" ;

SELECT prod_name FROM products

WHERE prod_name REGEXP "1000" ;

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

LIKE匹配整个列。如果被匹配的文本在列值中出现，LIKE将不会找到它，相应的行也不被返回(除非使用通配符)。而REGEXP在列值内进行匹配，如果被匹配的文本在列值中出现，REGEXP将会找到它，相应的行将被返回。

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

9.2.2 进行OR匹配

为搜索两个串之一(或者为这个串，或者为另一个串)，使用 | 。

1000|2000。| 表示匹配其中之一，因此1000和2000都匹配并返回。

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

9.2.3 匹配几个字符之一

正则表达式[123] Ton：[123]定义一组字符，它的意思是匹配1或2或3，因此，1 ton和2 ton都匹配且返回。

正如所见，[]是另一种形式的OR语句。事实上，正则表达式[123]Ton为[1|2|3]Ton的缩写，也可以使用后者。但是，需要用[]来定义OR语句查找什么。

注意区分：1|2|3 Ton。MySQL假定你的意思是'1'或'2'或'3 ton'。

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

9.2.4 匹配范围

[0123456789] = [0-9]

[a-z]

9.2.5 匹配特殊字符

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

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

\\也用来引用元字符(具有特殊含义的字符):

\\f 换页

\\n 换行

\\r 回车

\\t 制表

\\v 纵向制表

匹配\ 为了匹配反斜杠(\)字符本身，需要使用\\\。

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

9.2.6 匹配字符类

[: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 匹配多个实例

重复元字符：

* 0个或多个匹配

+ 1个或多个匹配(等于{1,})

? 0个或1个匹配(等于{0,1})

{n} 指定数目的匹配

{n,} 不少于指定数目的匹配

{n,m} 匹配数目的范围(m不超过255)

正则表达式\\([0-9] sticks?\\)：\\( 匹配 (，[0-9]匹配任意数字，sticks?匹配stick和sticks(s后的?使s可选，因为?匹配它前面的任何字符的0次或1次出现)，\\)匹配)。

9.2.8 定位符

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

^ 文本的开始

$ 文本的结尾

[[:<:>

[[:>:]] 词的结尾

^有两种用法。在集合中(用[和]定义)，用它来否定该集合，或者，用来指串的开始处。

简单的正则表达式测试： 可以在不使用数据库表的情况下用SELECT来测试正则表达式。REGEXP检查总是返回0(没有匹配)或1(匹配)。

SELECT 'hello' REGEXP '0-9'

这个例子显然将返回0(因为文本hello中没有数字)。