持续更新中……

《MySQL必知必会》

一、了解SQL

1、数据库基础

数据库：一个以某种有组织的方式存储的数据集合。

表：一种结构化的文件，可用来存储某种特定类型的数据。数据库中的每个表都有一个自己的名字，用来标识自己，此名字是唯一的。

表名：相同数据库不能两次使用相同的表名，不同数据库可以使用相同的表名。

列：表中的一个字段。所有表都说由一个或者多个列组成的。可以把数据库想象成一个网格，每一列存储着一条特定的信息，如一个列存储所有顾客的编号，另一个列存储所有顾客的地址。

数据类型：每个表列都有相应的数据类型，它限制该列中存储的数据。

行：表中的数据是按行存储的，所保存的每个记录存储在自己的行内，如果将表想象成网格，网格中垂直的列为表列，水平行为表行。例如顾客表可以每行存储一个顾客，表中的行数为记录的总数。

主键：一列或者一组列。其值能够唯一区分表中的每个行。表中每一行都应该有可以唯一标识自己的一列。

​ 唯一标识表中每行的这个列或者这组列称为主键。主键用来表示要给特定的行。

应该总是定义主键：虽然并不是总需要主键，但为每个表创建一个主键，以便于以后的数据操作和管理。

​ 表中的任何列都可以作为主键，只有满足以下条件：

• 任意两行不具有相同的 主键值
• 每个行都必须有一个主键值（主键列不允许NULL值）

​ 在使用多列作为主键时，上述条件必须应用到构造主键的所有列，所有列值的组合必须是唯一的。

2、什么是SQL

SQL是结构化查询语言的缩写。SQL是一种专门用来与数据库通信的语言。

SQL的优点：

• 几乎所有重要的DBMS都支持SQL，所以学习此语言使你几乎能与所有数据库打交道。
• 简单易学。
• 虽然看上去很简单，但实际上是一种强有力的语言，灵活使用其语言元素，可以进行非常复杂和高级的数据库操作。

二、MySQL简介

1、什么是MySQL

​ MySQL：数据的所有存储、检索、管理和吹了实际上是由数据库软件——DBMS（数据库管理系统）完成的。MySQL是一种DBMS，即它是一种数据库软件。

​ MySQL数据库是基于客户机-服务器的数据库。

2、MySQL工具

​ mysql命令行实用程序：每个MySQL安装都会有一个mysql的简单命令行使用程序。在使用这个实用程序的时候，需要注意：

• 命令行输入在mysql>之后
• 命令用;或者\g结束，仅按Enter不执行命令
• 输入help或者\h获取帮助。
• 输入quit或者exit退出命令行实用程序。

MySQL Administrator：是一个图形交互客户机，用于简化MySQL服务器的管理。

MySQL Query Browser：一个图形交互客户机，用来编写和执行MySQL命令。

三、使用MySQL

1、连接

信息：连接MySQl，需要以下信息：

• 主机名——连接本地MySQL服务器，为localhost；
• 端口（如果使用默认端口3306之外的端口）；
• 一个合法的用户名；
• 用户口令。

2、选择数据库

use：一个关键字，用于执行任意数据库操作前选择一个数据库。

3、了解数据库和表

查看数据库：在你不知道可以使用哪些数据库名时，可以使用SHOW DATABASES显示这些信息

​ SHOW DATABASES；返回可用数据库的一个列表。

查看要给数据库内的表的列表：可以使用SHOW TABLES

​ SHOW TABLES；返回当前选择的数据库内的可用表的列表。

查看表的信息：SHOW COLUMNS FROM TABLE要求给出一个表名，它对于每个字段返回一行，行中包括字段名、数据类型、是否允许NULL、键信息、默认值、以及其他信息。

所支持的其他SHOW语句：

• SHOW STATUS，用于显示广泛的服务器状态信息；
• SHOW CREATE DATABASE和SHOW CREATE TABLE，分别用来显示创建特定数据库或表的MySQL语句；
• SHOW GRANTS用来显示授权用户的安全权限
• SHO ERRORS和SHOW WARNINGS用来显示服务器错误或者警告信息。

四、检索数据

1、SELECT语句

​ SQL语句是用简单的英语单词构成的，这些单词称为关键词，每个SQL语句都是由一个或者多个关键字构成的。

​ 最经常使用的SQL语句就是SELECT语句，它的用途是从一个或者多个表中检索信息。使用SELECT检索信息，至少给出两条信息——想选择什么，以及从什么地方开始选择。

2、检索单个列

SELECT prod_name 
FROM products;

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

3、检索多个列

SELECT prod_id, prod_name, prod_price 
FROM products;

4、检索所有列

SELECT *
FROM products;

5、检索不同的行

执行下面语句，会返回所有匹配的行，但其中有很多重复的值。

SELECT vend_id 
From products;

如果你不想要每个值每次都出现，怎么办？例如，你想得出products表中产品的所有供应商的ID：

SELECT DISTINCT vend_id
FROM products;

解决的办法是使用DISTINCT，顾名思义，此关键字指示MySQL只返回不同的值。

6、限制结果

​ SELECT语句返回所有匹配的行，它们可能是指定表中的每个行。为了返回第一行或者前几行，可以使用LIMIT子句。

​ 此语句使用SELECT语句检索单个列。LIMIT 5指示MySQL返回不多于5行。

SELECT prod_name 
FROM products
LIMIT 5;

​ 为得出下一个5行，可以指定要检索的开始和行数。

SELECT prod_name 
FROM products
LIMIT 5, 5;

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

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

⭐在行数不够时：LIMIT中指定要检索的行数为检索的最大行数，如果没用足够的行，MySQL将只返回它能返回的那么多行。

7、使用完全限定的表名

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

select products.prod_name
FROM products;

​ 这条SQL语句在功能上等于本章最开始使用的那一条语句，但这里指定了一个完全限定的列名。

​ 表名也可以是完全限定的，如下：

select products.prod_name
FROM book.products;

​ 这条SQL语句在功能上等于刚刚使用的那条语句（假设products表确实存在book数据库中）

​ 虽然这种表示很麻烦，但是有些情形需要这么用，后面会介绍。

五、排序检索数据

1、排序数据

​ 正如前面所述，下面的SQL语句返回某个数据库表的单个列，但却没用特定的顺序。

SELECT prod_name
FROM products;

​ 其次，检索出来的数据并不是以纯粹的随机顺序显示的。如果不排序，数据一般将以它在底层表中出现的顺序显示。这可以是数据最初添加到表中的顺序。但是，如果数据后来进行过更新或删除，则此顺序会收到MySQL重用收回存储空间的影响。因此，如果补明确控制的话，不能（也不应该）依赖该排序顺序。关系数据库设计理论认为，如果不明确规定排序顺序，则不应该假定检索出的数据的顺序有意义。

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

SELECT prod_name
FROM products
ORDER BY prod_name;

​ 这条语句除了指示MySQL对prod_name列以字母顺序排序数据的ORDER BY子句外，与前面的语句相同。

2、按多个列排序

​ 下面的语句指示MySQL按照——先按价格，然后再按名称排序。

SELECT prod_id, prod_name, prod_price
FROM products
ORDER BY prod_price, prod_name;

3、指定排序方向

​ 数据排序分为——升序排序（默认）、降序排序。

​ 当我们想使用降序排序的时候，必须指定DESC关键字。

SELECT prod_id, prod_name, prod_price
FROM products
ORDER BY prod_price DESC;

​ 但是，如果打算用多个列排序怎么办？下面的例子以降序排序产品（最贵的在前面），然后再对产品名排序：

SELECT prod_id, prod_name, prod_price
FROM products
ORDER BY prod_price DESC, prod_name;

DESC关键字只应用到直接位于其前面的列名。如果想在多个列上进行降序排序，必须对每个列指定DECS关键字。

与DESC对应的是ASC关键字，在升序排序时可以指定它，但实际上没用多大用处，因为升序是默认的。

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

SELECT prod_price
FROM products
ORDER BY prod_price DESC
LIMIT 1

​ prod_price DESC保证行是按照最昂贵到最便宜检索的，而LIMIT 1告诉MySQL仅返回一行。

六、过滤数据

1、使用WHERE子句

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

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

SELECT prod_name, prod_price
FROM products
WHERE prod_price = 2.50;

​ 这条语句从products表中检索两个列，但不返回所有行，只返回prod_price值为2.50的行。

2、WHERE子句操作符

​

（1）检查单个值

​ 上面举的例子就是，我们再举一个例子，如下：

select prod_name, prod_price
FROM products
where prod_name = 'fuses';

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

（2）不匹配检查

select vend_id, prod_name
FROM products
where vend_id <> 1003

​ 上述例子是列出不是由供应商1003制造的所有产品。

下面是相同的例子，只是用的是**!=而不是<>**

select vend_id, prod_name
FROM products
where vend_id != 1003

（3）范围值检查

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

​ 例如，BETWEEN操作符可用来检索价格在5美元和10美元之间或日期在指定的开始日期和结束日期之间的所有产品，如下：

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

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

（4）空值检查

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

SELECT cust_id
FROM customers
WHERE cust_email IS NULL

​ ustomers包含有具有空值的列，如果在文件中没有某位顾客的电子邮件地址，则cust_email列将包含NULL值:

七、数据过滤

1、组合WHERE子句

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

（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美元，但不是由指定供应商制造的也不被检索。

（2）OR操作符

​ OR操作符与AND操作符不同，它指示MySQL检索匹配任一条件的行，如下：

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

​ 此SQL语句检索由任一个指定供应商制造的所有产品的产品名和价格。OR操作符告诉DBMS匹配任一条件而不是同时匹配两个条件。

（3）计算次序

​ WHERE可包含任意数目的AND和OR操作符。允许两者结合以进行复杂和高级的过滤。
​ 但是，组合AND和OR带来了一个有趣的问题。为了说明这个问题，来看一个例子。假如需要列出价格为10美元(含）以上且由1002或1003制造的所有产品。下面的SELECT语句使用AND和OR操作符的组合建立了一个WHERE子句:

SELECT prod_name, prod_price, vend_id
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, vend_id
FROM products
WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;

​ 这条SELECT语句与前一条的唯一差别是，这条语句中，前两个条件用圆括号括了起来。因为圆括号具有较AND或OR操作符高的计算次序，DBMS首先过滤圆括号内的OR条件。这时，SQL语句变成了选择由供应商1002或1003制造的且价格都在10美元(含）以上的任何产品，这正是我们所希望的。

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

2、IN操作符

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

SELECT prod_name, prod_price
FROM products
WHERE vend_id in(1002, 1003)
ORDER BY prod_name

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

​ 如果你认为IN操作符完成与OR相同的功能，那么你的这种猜测是对的。下面的SQL语句完成与上面的例子相同的工作:

SELECT prod_name, prod_price
FROM products
WHERE vend_id = 1002 OR vend_id = 1003
ORDER BY prod_name

​ 为什么要使用IN操作符?其优点具体如下?

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

3、NOT操作符

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

​ 下面的例子说明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操作符

（1）百分号（%）通配符

​ 最常使用的通配符是百分号(%)。在搜索串中，**%表示任何字符出现任意次数。例如，为了找出所有以词jet起头的产品，可使用以下SELECT**语句;

SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE "jet%";

​ 此例子使用了搜索模式’jet%‘。在执行这条子句时，将检索任意以jet起头的词。**%**告诉MySQL接受jet之后的任意字符，不管它有多少字符。

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

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

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

​ 通配符也可以出现在搜索模式的中间，虽然这样做不太有用。下面的例子找出以s起头以e结尾的所有产品:

SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE "s%e";

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

（2）下划线（_)通配符

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

​ 举一个例子:

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

ANV02 1 ton anvil

ANV03 2 ton anvil

​ 此WHERE子句中的搜索模式给出了后面跟有文本的两个通配符。结果只显示匹配搜索模式的行:第一行中下划线匹配1， 第二行中匹配2。.5 ton anvil产品没有匹配，因为搜索模式要求匹配两个通配符而不是一个。对照一下，下面的SELECT语句使用%通配符，返回三行产品:

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

ANV01 .5 ton anvil
ANV02 1 ton anvil
ANV03 2 ton anvil

2、通配符使用技巧

​ 正如所见，MySQL的通配符很有用。但这种功能是有代价的:通配符搜索的处理一般要比前面讨论的其他搜索所花时间更长。这里给出一些使用通配符要记住的技巧。

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

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

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

  总之，通配符是一种极重要和有用的搜索工具，以后我们经常会用到它。

九、用正则表达式进行搜索

1、正则表达式介绍

​ 前两章中的过滤例子允许用匹配、.比较和通配操作符寻找数据。对于基本的过滤（或者甚至是某些不那么基本的过滤)，这样就足够了。但随着过滤条件的复杂性的增加，WHERE子句本身的复杂性也有必要增加。
​ 这也就是正则表达式变得有用的地方。正则表达式是用来匹配文本的特殊的串(字符集合)。如果你想从一个文本文件中提取电话号码，可以使用正则表达式。如果你需要查找名字中间有数字的所有文件，可以使用一个正则表达式。如果你想在一个文本块中找到所有重复的单词，可以使用一个正则表达式。如果你想替换一个页面中的所有URL为这些URL的实际HTML链接,也可以使用一个正则表达式(对于最后这个例子，或者是两个正则表达式)。
​ 所有种类的程序设计语言、文本编辑器、操作系统等都支持正则表达式。有见识的程序员和网络管理员已经关注作为他们技术工具重要内容的正则表达式很长时间了。
​ 正则表达式用正则表达式语言来建立，正则表达式语言是用来完成刚讨论的所有工作以及更多工作的一种特殊语言。与任意语言一样，正则表达式具有你必须学习的特殊的语法和指令。

2、使用MySQL正则表达式

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

（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;

JetPack 1000
JetPack 2000

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

​ 匹配不区分大小写 MySQL中的正则表达式匹配(自版本3.23.4后)不区分大小写(即,大写和小写都匹配)。为区分大小写,可使用BINARY关键字,如WHERE prod_name REGEXPBINARY 'JetPack .000'.

（2）进行OR匹配

​ 为搜索两个串之一(或者为这个串，或者为另一个串)，使用|，如下所示:

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

JetPack 1000
JetPack 2000

​ 语句中使用了正则表达式1000 | 2000。**|为正则表达式的OR操作符。它表示匹配其中之一，因此1000和2000都匹配并返回。使用|从功能上类似于在SELECT语句中使用OR语句，多个OR**条件可并入单个正则表达式。

（3）匹配几个字符之一

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

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

1 ton anvil
2 ton anvil

​ 这里，使用了正则表达式[ 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;

prod_name
1 ton anvil
2 ton anvil
JetPack 1000
JetPack 2000
TNT (1 stick)

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

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

（4）匹配范围

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

[0123456789]

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

[0-9]

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

SELECT prod_name
FROM products
WHERE prod_name REGEXP '[1-5] Ton'
ORDER BY prod_name;

prod_name
.5 ton anvil
1 ton anvil
2 ton anvil

​ 这里使用正则表达式[ 1-5] Ton。**[ 1-5]定义了一个范围，这个表达式意思是匹配1到5，因此返回3个匹配行。由于5 ton**匹配，所以返回.5 ton。

（5）匹配特殊字符

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

​ 例如,如果要找出包含**.**字符的值,怎样搜索?请看下面的例子:

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

vend_name
ACME
Anvils R Us
Furball Inc.
Jet Set
Jouets Et Ours
LT Supplies

​ 这并不是期望的输出，**.匹配任意字符，因此每个行都被检索出来。
​ 为了匹配特殊字符，必须用\\为前导。ll-表示查找-，ll.表示查找.**。

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

vend_name
Furball Inc.

​ 有字符都必须以这种方式转义。这包括**.、|、[]**以及迄今为止使用过的其他特殊字符。

（6）匹配字符类

​ 存在找出你自己经常使用的数字、所有字母字符或所有数字字母字符等的匹配。为更方便工作，可以使用预定义的字符集，称为字符类。如下：

十、创建计算字段

十一、使用数据处理函数

1、使用函数

​ 大多数SQL实现支持以下类型的函数。

• 用于处理文本串（如删除或填充值，转换值为大写或小写)的文本函数。
• 用于在数值数据上进行算术操作（如返回绝对值，进行代数运算)的数值函数。
• 用于处理日期和时间值并从这些值中提取特定成分（例如，返回两个日期之差，检查日期有效性等）的日期和时间函数。
• 返回DBMS正使用的特殊信息（如返回用户登录信息，检查版本细节）的系统函数。

（1）文本处理函数

​ 下面是一个使用upper()函数的例子：

SELECT vend_name, UPPER(vend_name) AS vend_name_upcase
FROM vendors
ORDER BY vend_name;

vend_name vend_name_upcase
ACME ACME
Anvils R Us ANVILS R US
Furball Inc. FURBALL INC.
Jet Set JET SET
Jouets Et Ours JOUETS ET OURS
LT Supplies LT SUPPLIES

​ 正如所见，upper()将文本转换为大写，因此本例子中每个供应商都列出两次，第一次为vendors表中存储的值，第二次作为列vend_name_upcase转换为大写。

​ 下面列出了常用的文本处理函数：

​ 上面SOUNDEX需要做进一步的解释。SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX考虑了类似的发音字符和音节，使得能对串进行发音比较而不是字母比较。虽然SOUNDEX不是SQL概念，但MySQL（就像多数DBMS一样）都提供对sOUNDEX的支持。
​ 下面给出一个使用Soundex()函数的例子。customers表中有一个顾客coyote Inc.，其联系名为Y.Lee。但如果这是输入错误，此联系名实际应该是Y.Lie,怎么办?显然,按正确的联系名搜索不会返回数据，如下所示:

SELECT cust_name, cust_contact
FROM customers
WHERE cust_contact = 'Y. Lie';

cust_name cust_contact

​ 现在试一下使用Soundex()函数进行搜索，它匹配所有发音类似于Y.Lie的联系名:

SELECT cust_name, cust_contact
FROM customers
WHERE Soundex(cust_contact) = Soundex('Y. Lie');

cust_name cust_contact
Coyote Inc. Y Lee

（2）日期和时间处理函数

​ 日期和时间采用相应的数据类型和特殊的格式存储，以便能快速和有效地排序或过滤，并且节省物理存储空间。
​ 一般，应用程序不使用用来存储日期和时间的格式，因此日期和时间函数总是被用来读取、统计和处理这些值。由于这个原因，日期和时间函数在MySQL语言中具有重要的作用。
​ 下面列出了某些常用的日期和时间处理函数。

​ 需要注意的是MySQL使用的日期格式。无论你什么时候指定一
个日期，不管是插入或更新表值还是用WHERE子句进行过滤，日期必须为格式yyyy-mm-dd。因此，2005年9月1日，给出为2005-09-01。虽然其他的日期格式可能也行，但这是首选的日期格式，因为它排除了多义性（如，04/05/06是2006年5月4日或2006年4月5日或2004年5月6日或……)。

​ 因此基本的日期比较应该很简单，如下：

SELECT cust_id, order_num,order_date
FROM orders
WHERE order_date = '2005-09-01';

cust_id order_num order_date
10001 20005 2005-09-01 00:00:00

​ 但是，使用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语句为:

SELECT cust_id, order_num,order_date
FROM orders
WHERE Date(order_date) = '2005-09-01';

​ 不过，还有一种日期比较需要说明。如果你想检索出2005年9月下的所有订单，怎么办?简单的相等测试不行，因为它也要匹配月份中的天数。有几种解决办法，其中之一如下所示:

SELECT cust_id, order_num,order_date
FROM orders
WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';

cust_id order_num order_date
10001 20005 2005-09-01 00:00:00
10003 20006 2005-09-12 00:00:00
10004 20007 2005-09-30 00:00:00

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

SELECT cust_id, order_num,order_date
FROM orders
WHERE YEAR(order_date) = 2005 AND Month(order_date) = 9;

cust_id order_num order_date
10001 20005 2005-09-01 00:00:00
10003 20006 2005-09-12 00:00:00
10004 20007 2005-09-30 00:00:00

​ Year()是一个从日期(或日期时间)中返回年份的函数。类似，Month( )从日期中返回月份。因此, WHERE Year(order_date)= 2005 AND Month (order_date) =9检索出order_date为2005年9月的所有行。

（3）数值处理函数

​ 数值处理函数仅处理数值数据。这些函数一般主要用于代数、三角或几何运算，因此没有串或日期-时间处理函数的使用那么频繁。

​ 具有讽刺意味的是，在主要DBMS的函数中，数值函数是最一致最统一的函数。下面列出一些常用的数值处理函数。

十二、汇总数据

1、聚集函数

​ 我们经常需要汇总数据而不用把它们实际检索出来，为此MySQL提供了专门的函数。使用这些函数，MySQL查询可用于检索数据，以便分析和报表生成。这种类型的检索例子有以下几种。

• 确定表中行数（或者满足某个条件或包含某个特定值的行数)。
• 获得表中行组的和。
• 找出表列（或所有行或某些特定的行）的最大值、最小值和平均值。

​ 上述例子都需要对表中数据（而不是实际数据本身）汇总。因此，返回实际表数据是对时间和处理资源的一种浪费（更不用说带宽了)。重复一遍，实际想要的是汇总信息。
​ 为方便这种类型的检索，MySQL给出了5个聚集函数，见下图1。这些函数能进行上述罗列的检索。

（1）AVG()函数

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

下面的例子使用AVG()返回products表中所有产品的平均价格:

SELECT AVG(prod_price) AS avg_price
FROM	products;

​ 此SELECT语句返回值avg_Price，它包含products表中所有产品的平均价格。avg_price是一个别名。

​ **AVG()**也可以用来确定特定列或行的平均值。下面的例子返回特定供应商所提供产品的平均价格:

SELECT AVG(prod_price) AS avg_price
FROM	products
WHERE vend_id = 1003;

avg_price
13.212857

​ 这条SELECT语句与前一条的不同之处在于它包含了WHERE子句。此WHERE子句仅过滤出vend_id为1003的产品，因此
​ avg_price中返回的值只是该供应商的产品的平均值。

（2）COUNT()函数

​ *COUNT()函数进行计数。可利用COUNT()确定表中行的数目或符合特定条件的行的数目。
​ COUNT( )函数有两种使用方式。
​ 使用COUNT(* )对表中行的数目进行计数,不管表列中包含的是空
值（NULL)还是非空值。
​ 使用COUNT (column)对特定列中具有值的行进行计数，忽略NULL*值。
​ 下面的例子返回customers表中客户的总数:

SELECT COUNT(*) AS num_cust 
FROM customers;

num_cust
5

​ 在此例子中，利用COUNT (*)对所有行计数，不管行中各列有什么值。计数值在num_cust中返回。

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

SELECT COUNT(cust_email) AS num_cust 
FROM customers;

num_cust
3

​ 这条SELECT语句使用COUNT (cust_email)对cust_email列中有值的行进行计数。在此例子中,cust_email的计数为3(表示5个客户中只有3个客户有电子邮件地址)。

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

（3）MAX()函数

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

SELECT MAX(prod_price) AS max_price 
FROM products;

max_price
55.00

​ 这里，MAX()返回products表中最贵的物品的价格。

（4）MIN()函数

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

SELECT min(prod_price) AS max_price 
FROM products;

max_price
2.50

（5）SUM()函数

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

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

SELECT SUM(quantity) AS items_ordered 
FROM orderitems
WHERE order_num = 20005;

items_ordered
19

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

SELECT SUM(quantity * item_price) AS items_ordered 
FROM orderitems
WHERE order_num = 20005;

items_ordered
149.87

2、聚集不同值 DISTINCT

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

SELECT AVG(DISTINCT prod_price) AS avg_price 
FROM products
WHERE vend_id = 1003;

avg_price
15.998000

​ 可以看到，在使用了DISTINCT后，此例子中的avg_price比较高，因为有多个物品具有相同的较低价格。排除它们提升了平均价格。

3、组合聚集函数

​ 目前为止的所有聚集函数例子都只涉及单个函数。但实际上SELECT语句可根据需要包含多个聚集函数。请看下面的例子:

SELECT COUNT(*) AS num_items,
	MIN(prod_price) AS price_min,
	MAX(prod_price) AS price_max,
	AVG(prod_price) AS price_avg
FROM products;

num_items price_min price_max price_avg
14 2.50 55.00 16.133571

​ 这里用单条SELECT语句执行了4个聚集计算，返回4个值( products表中物品的数目，产品价格的最高、最低以及平均值）。

十三、分组数据

1、数据分组

​ 从上一章知道，SQL聚集函数可用来汇总数据。这使我们能够对行进行计数，计算和与平均数，获得最大和最小值而不用检索所有数据。
​ 目前为止的所有计算都是在表的所有数据或匹配特定的WHERE子句的数据上进行的。提示一下，下面的例子返回供应商1003提供的产品数目:

SELECT COUNT(*) AS num_prods 
FROM products
WHERE vend_id = 1003;

num_prods
7

​ 但如果要返回每个供应商提供的产品数目怎么办?或者返回只提供单项产品的供应商所提供的产品，或返回提供10个以上产品的供应商怎么办?
​ 这就是分组显身手的时候了。分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算。

2、创建分组

​ 分组是在SELECT语句的GROUP BY子句中建立的。理解分组的最好办法是看一个例子:

SELECT vend_id, COUNT(*) AS num_prods 
FROM products
GROUP BY vend_id;

vend_id num_prods
1001 3
1002 2
1003 7
1005 2

​ 上面的SELECT语句指定了两个列，vend_id包含产品供应商的ID，num_prods为计算字段（用COUNT(*)函数建立)。GROUP BY
子句指示MySQL按vend_id排序并分组数据。这导致对每个vend_id而不是整个表计算num_prods一次。从输出中可以看到,供应商1001有3个产品，供应商1002有2个产品，供应商1003有7个产品，而供应商1005有2个产品。
因为使用了GROUP BY，就不必指定要计算和估值的每个组了。系统会自动完成。GROUP BY子句指示MySQL分组数据，然后对每个组而不是整个结果集进行聚集。
​ 在具体使用GROUP BY子句前，需要知道一些重要的规定。

• GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。
• 如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算(所以不能从个别的列取回数据)。
• GROUP BY子句中列出的每个列都必须是检索列或有效的表达式(但不能是聚集函数)。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
• 除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出。
• 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
• GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

3、过滤分组

​ 除了能用GROUP BY分组数据外，MySQL还允许过滤分组，规定包括哪些分组，排除哪些分组。例如，可能想要列出至少有两个订单的所有顾客。为得出这种数据，必须基于完整的分组而不是个别的行进行过滤。
​ 我们已经看到了WHERE子句的作用(第6章中引入)。但是，在这个例子中WHERE不能完成任务，因为WHERE过滤指定的是行而不是分组。事实上，WHERE没有分组的概念。
​ 那么，不使用WHERE使用什么呢? MySQL为此目的提供了另外的子句，那就是HAVING子句。HAVING非常类似于WHERE。事实上，目前为止所学过的所有类型的WHERE子句都可以用HAVING来替代。唯一的差别是WHERE过滤行，而HAVING过滤分组。

SELECT cust_id, COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;

cust_id orders
10001 2

​ 这条SELECT语句的前3行类似于上面的语句。最后一行增加了HAVING子句，它过滤COUNT(* ) >=2(两个以上的订单）的那些分组。

​ 那么，有没有在一条语句中同时使用WHERE和HAVING子句的需要呢﹖事实上，确实有。假如想进一步过滤上面的语句，使它返回过去12个月内具有两个以上订单的顾客。为达到这一点,可增加一条WHERE子句，过滤出过去12个月内下过的订单。然后再增加HAVING子句过滤出具有两个以上订单的分组。

​ 为更好地理解，请看下面的例子，它列出具有2个（含)以上、价格10(含）以上的产品的供应商:

SELECT vend_id, COUNT(*) AS num_prods 
FROM products
WHERE prod_price >= 10
GROUP BY vend_id
HAVING COUNT(*) >= 2

vend_id num_prods
1003 4
1005 2

​ 这条语句中，第一行是使用了聚集函数的基本SELECT，它与前面的例子很相像。WHERE子句过滤所有prod_price至少为10的行。然后按vend_id分组数据，HAVING子句过滤计数为2或2以上的分组。

​ 如果没有WHERE子句，将会多检索出两行（供应商1002，销售的所有产品价格都在10以下;供应商1001，销售3个产品，但只有一个产品的价格大于等于10):

SELECT vend_id, COUNT(*) AS num_prods 
FROM products
GROUP BY vend_id
HAVING COUNT(*) >= 2

vend_id num_prods
1001 3
1002 2
1003 7
1005 2

4、分组和排序

​ 虽然GROUPBY和ORDER BY经常完成相同的工作，但它们是非常不同的。下面汇总了它们之间的差别。

ORDER BY

• 排序产生的输出
• 任意列都可以使用(甚至非选择的列也可以使用)
• 不一定需要

GROUP BY

• 分组行。但输出可能不是分组的顺序
• 只可能使用选择列或表达式列，而且必须使用每个选择列表达式
• 如果与聚集函数一起使用列（或表达式)，则必须使用

​ 上面列出的第一项差别极为重要。我们经常发现用GROUP BY分组的数据确实是以分组顺序输出的。但情况并不总是这样，它并不是SQL规范所要求的。此外，用户也可能会要求以不同于分组的顺序排序。仅因为你以某种方式分组数据（获得特定的分组聚集值)，并不表示你需要以相同的方式排序输出。应该提供明确的ORDER BY子句，即使其效果等同于GROUP BY子句也是如此。

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

​ 为说明GROUP BY和ORDER BY的使用方法，请看一个例子。下面的SELECT语句类似于前面那些例子。它检索总计订单价格大于等于50的订单的订单号和总计订单价格:

SELECT order_num, SUM(quantity * item_price) AS ordertotal 
FROM orderitems
GROUP BY order_num 
HAVING SUM(quantity * item_price) >= 50

order_num ordertotal
20005 149.87
20006 55.00
20007 1000.00
20008 125.00

​ 为按总计订单价格排序输出，需要添加ORDER BY子句，如下所示;

SELECT order_num, SUM(quantity * item_price) AS ordertotal 
FROM orderitems
GROUP BY order_num 
HAVING SUM(quantity * item_price) >= 50
ORDER BY ordertotal;

order_num ordertotal
20006 55.00
20008 125.00
20005 149.87
20007 1000.00

​ 在这个例子中，GROUP BY子句用来按订单号(order_num列)分组数据，以便SUM( )函数能够返回总计订单价格。HAVING*子句过滤数据，使得只返回总计订单价格大于等于50的订单。最后，用ORDER BY子句排序输出。

十四、使用子查询

1、利用子查询进行过滤

​ 订单存储在两个表中。对于包含订单号、客户ID、订单日期的每个订单，orders表存储一行。各订单的物品存储在相关的orderitems表中。orders表不存储客户信息。它只存储客户的ID。实际的客户信息存储在customers表中。
现在，假如需要列出订购物品TNT2的所有客户，应该怎样检索?下面列出具体的步骤。

​ (1)检索包含物品TNT2的所有订单的编号。

​ (2)检索具有前一步骤列出的订单编号的所有客户的ID。

​ (3)检索前一步骤返回的所有客户ID的客户信息。

​ 上述每个步骤都可以单独作为一个查询来执行。可以把一条SELECT语句返回的结果用于另一条SELECT语句的WHERE子句。
​ 也可以使用子查询来把3个查询组合成一条语句。
​ 第一条SELECT语句的含义很明确,对于prod_id为TNT2的所有订单物品，它检索其order_num列。输出列出两个包含此物品的订单:

SELECT order_num
FROM orderitems
WHERE prod_id = 'TNT2';

order_num
20005
20007

​ 下一步，检索具有前一步骤列出的订单编号的所有客户的ID：

SELECT cust_id
FROM orders
WHERE order_num IN (20005, 20007);

cust_id
10001
10004

​ 现在，把第一个查询变为子查询，组合成两个查询：

SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
										FROM orderitems
										WHERE prod_id = 'TNT2')

cust_id
10001
10004

​ 在SELECT语句中，子查询总是从内向外处理。在处理上面的SELECT语句时，MySQL实际上执行了两个操作。
首先，它执行下面的查询:
SELECT order_num FROM orderitems wHERE prod_id='TNT2'
此查询返回两个订单号:20005和20007。然后，这两个值以IN操作符要求的逗号分隔的格式传递给外部查询的WHERE子句。外部查询变成:
​ SELECT cust_id FROM orders WHERE order_num IN (20005,20007)
可以看到，输出是正确的并且与前面硬编码WHERE子句所返回的值相同。

​ 现在得到了订购物品TNT2的所有客户的ID。下一步是检索这些客户ID的客户信息。检索两列的SQL语句为:

SELECT cust_name, cust_contact
FROM customers
WHERE cust_id  IN (10001, 10004)

cust_name cust_contact
Coyote Inc. Y Lee
Yosemite Place Y Sam

​ 可以把其中的WHERE子句转换为子查询而不是硬编码这些客户ID:

SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN (SELECT cust_id
									FROM orders
									WHERE order_num IN (SELECT order_num
																			FROM orderitems
																			WHERE prod_id = 'TNT2'))

cust_name cust_contact
Coyote Inc. Y Lee
Yosemite Place Y Sam

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

​ 使用子查询的另一方法是创建计算字段。假如需要显示customers表中每个客户的订单总数。订单与相应的客户ID存储在orders表中。
为了执行这个操作，遵循下面的步骤。
​ (1)从customers表中检索客户列表。
​ (2)对于检索出的每个客户，统计其在orders表中的订单数目。

​ 为了对每个客户执行*COUNT()计算，应该将COUNT (*)**作为一个子查询。请看下面的代码:

SELECT
	cust_name,
	cust_state,
	( SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id ) AS orders 
FROM
	customers 
ORDER BY
	cust_name

cust_name cust_state orders
Coyote Inc. MI 2
E Fudd IL 1
Mouse House OH 0
Wascals IN 1
Yosemite Place AZ 1

​ 这条SELECT语句对customers表中每个客户返回3列:cust_name、cust_state和orders。orders是一个计算字段，它是由圆括号中的子查询建立的。该子查询对检索出的每个客户执行一次。在此例子中，该子查询执行了5次，因为检索出了5个客户。
​ 子查询中的WHERE子句与前面使用的WHERE子句稍有不同,因为它使用了完全限定列名（在第4章中首次提到)。下面的语句告诉SQL比较orders表中的cust_id与当前正从customers表中检索的cust_id:
wHERE orders.cust_id = customers.cust_id

十五、联结表

1、联结

​ SQL最强大的功能之一就是能在数据检索查询的执行中联结( join)表。联结是利用SQL的SELECT能执行的最重要的操作，很好地理解联结及其语法是学习SQL的一个极为重要的组成部分。

（1）关系表

​ 理解关系表的最好方法是来看一个现实世界中的例子。
​ 假如有一个包含产品目录的数据库表，其中每种类别的物品占一行。对于每种物品要存储的信息包括产品描述和价格，以及生产该产品的供应商信息。
​ 现在，假如有由同一供应商生产的多种物品，那么在何处存储供应商信息（如，供应商名、地址、联系方法等）呢?将这些数据与产品信息分开存储的理由如下。

• 因为同一供应商生产的每个产品的供应商信息都是相同的，对每个产品重复此信息既浪费时间又浪费存储空间。
• 如果供应商信息改变（例如，供应商搬家或电话号码变动)，只需改动一次即可。
• 如果有重复数据（即每种产品都存储供应商信息)，很难保证每次输入该数据的方式都相同。不一致的数据在报表中很难利用。

​ 关键是，相同数据出现多次决不是一件好事，此因素是关系数据库设计的基础。关系表的设计就是要保证把信息分解成多个表，一类数据一个表。各表通过某些常用的值（即关系设计中的关系( relational ) )互相关联。
​ 在这个例子中，可建立两个表，一个存储供应商信息，另一个存储产品信息。vendors表包含所有供应商信息，每个供应商占一行，每个供应商具有唯一的标识。此标识称为主键( primary key)(，可以是供应商ID或任何其他唯一值。
​ products表只存储产品信息，它除了存储供应商ID(vendors表的主键）外不存储其他供应商信息。vendors表的主键又叫作products的外键，它将vendors表与products表关联，利用供应商ID能从vendors表中找出相应供应商的详细信息。

外键(foreign key) 外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。
这样做的好处如下:

• 供应商信息不重复，从而不浪费时间和空间;
• 如果供应商信息变动，可以只更新vendors表中的单个记录，相关表中的数据不用改动;
• 由于数据无重复，显然数据是一致的，这使得处理数据更简单。

​ 总之，关系数据可以有效地存储和方便地处理。因此，关系数据库的可伸缩性远比非关系数据库要好。(foreign key)外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。

可伸缩性(scale) 能够适应不断增加的工作量而不失败。设计良好的数据库或应用程序称之为可伸缩性好( scale well )。

（2）为什么要使用联结

​ 正如所述，分解数据为多个表能更有效地存储，更方便地处理，并且具有更大的可伸缩性。但这些好处是有代价的。
​ 如果数据存储在多个表中，怎样用单条SELECT语句检索出数据?答案是使用联结。简单地说，联结是一种机制，用来在一条SELECT语句中关联表，因此称之为联结。使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行。

2、创建联结

联结的创建非常简单，规定要联结的所有表以及它们如何关联即可，请看下面的例子：

SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name

vend_name prod_name prod_price
ACME Bird seed 10.00
ACME Carrots 2.50
ACME Detonator 13.00
ACME Safe 50.00
ACME Sling 4.49
ACME TNT (1 stick) 2.50
ACME TNT (5 sticks) 10.00
Anvils R Us .5 ton anvil 5.99
Anvils R Us 1 ton anvil 9.99
Anvils R Us 2 ton anvil 14.99
Jet Set JetPack 1000 35.00
Jet Set JetPack 2000 55.00
LT Supplies Fuses 3.42
LT Supplies Oil can 8.99

​ 我们来考察一下此代码。SELECT语句与前面所有语句一样指定要检索的列。这里,最大的差别是所指定的两个列(prod_name
和prod_price)在一个表中，而另一个列(vend_name)在另一个表中。
​ 现在来看FROM子句。与以前的SELECT语句不一样，这条语句的FROM子句列出了两个表,分别是vendors和products。它们就是这条SELECT语句联结的两个表的名字。这两个表用WHERE子句正确联结，WHERE子句指示MySQL匹配vendors表中的vend_id和products表中的vend_id。
​ 可以看到要匹配的两个列以vendors.vend_id和products.vend_id指定。这里需要这种完全限定列名，因为如果只给出vend_id，则MySQL不知道指的是哪一个（它们有两个，每个表中一个)。

（1）WHERE子句的重要性

​ 在联结两个表时，你实际上做的是将第一个表中的每一行与第二个表中的每一行配对。WHERE子句作为过滤条件，它只包含那些匹配给定条件（这里是联结条件）的行。没有WHERE子句，第一个表中的每个行将与第二个表中的每个行配对，而不管它们逻辑上是否可以配在一起。

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

​ 对于笛卡尔积的理解可以对比下述代码与上一个例子进行理解：

SELECT vend_name, prod_name, prod_price
FROM vendors, products
ORDER BY vend_name, prod_name

vend_name prod_name prod_price
ACME .5 ton anvil 5.99
ACME 1 ton anvil 9.99
ACME 2 ton anvil 14.99
ACME Bird seed 10.00
ACME Carrots 2.50
ACME Detonator 13.00
ACME Fuses 3.42
ACME JetPack 1000 35.00
ACME JetPack 2000 55.00
ACME Oil can 8.99
ACME Safe 50.00
ACME Sling 4.49
ACME TNT (1 stick) 2.50
ACME TNT (5 sticks) 10.00
Anvils R Us .5 ton anvil 5.99
Anvils R Us 1 ton anvil 9.99
Anvils R Us 2 ton anvil 14.99
Anvils R Us Bird seed 10.00
Anvils R Us Carrots 2.50
Anvils R Us Detonator 13.00
Anvils R Us Fuses 3.42
Anvils R Us JetPack 1000 35.00
Anvils R Us JetPack 2000 55.00
Anvils R Us Oil can 8.99
Anvils R Us Safe 50.00
Anvils R Us Sling 4.49
Anvils R Us TNT (1 stick) 2.50
Anvils R Us TNT (5 sticks) 10.00
Furball Inc. .5 ton anvil 5.99
Furball Inc. 1 ton anvil 9.99
Furball Inc. 2 ton anvil 14.99
Furball Inc. Bird seed 10.00
Furball Inc. Carrots 2.50
Furball Inc. Detonator 13.00
Furball Inc. Fuses 3.42
Furball Inc. JetPack 1000 35.00
Furball Inc. JetPack 2000 55.00
Furball Inc. Oil can 8.99
Furball Inc. Safe 50.00
Furball Inc. Sling 4.49
Furball Inc. TNT (1 stick) 2.50
Furball Inc. TNT (5 sticks) 10.00
Jet Set .5 ton anvil 5.99
Jet Set 1 ton anvil 9.99
Jet Set 2 ton anvil 14.99
Jet Set Bird seed 10.00
Jet Set Carrots 2.50
Jet Set Detonator 13.00
Jet Set Fuses 3.42
Jet Set JetPack 1000 35.00
Jet Set JetPack 2000 55.00
Jet Set Oil can 8.99
Jet Set Safe 50.00
Jet Set Sling 4.49
Jet Set TNT (1 stick) 2.50
Jet Set TNT (5 sticks) 10.00
Jouets Et Ours .5 ton anvil 5.99
Jouets Et Ours 1 ton anvil 9.99
Jouets Et Ours 2 ton anvil 14.99
Jouets Et Ours Bird seed 10.00
Jouets Et Ours Carrots 2.50
Jouets Et Ours Detonator 13.00
Jouets Et Ours Fuses 3.42
Jouets Et Ours JetPack 1000 35.00
Jouets Et Ours JetPack 2000 55.00
Jouets Et Ours Oil can 8.99
Jouets Et Ours Safe 50.00
Jouets Et Ours Sling 4.49
Jouets Et Ours TNT (1 stick) 2.50
Jouets Et Ours TNT (5 sticks) 10.00
LT Supplies .5 ton anvil 5.99
LT Supplies 1 ton anvil 9.99
LT Supplies 2 ton anvil 14.99
LT Supplies Bird seed 10.00
LT Supplies Carrots 2.50
LT Supplies Detonator 13.00
LT Supplies Fuses 3.42
LT Supplies JetPack 1000 35.00
LT Supplies JetPack 2000 55.00
LT Supplies Oil can 8.99
LT Supplies Safe 50.00
LT Supplies Sling 4.49
LT Supplies TNT (1 stick) 2.50
LT Supplies TNT (5 sticks) 10.00

​ 从上面的输出中可以看到，相应的笛卡儿积不是我们所想要的。这里返回的数据用每个供应商匹配了每个产品，它包括了
供应商不正确的产品。实际上有的供应商根本就没有产品。

（2）内部联结

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

SELECT vend_name, prod_name, prod_price
FROM vendors
INNER JOIN products 
ON vendors.vend_id = products.vend_id

vend_name prod_name prod_price
Anvils R Us .5 ton anvil 5.99
Anvils R Us 1 ton anvil 9.99
Anvils R Us 2 ton anvil 14.99
LT Supplies Fuses 3.42
LT Supplies Oil can 8.99
ACME Detonator 13.00
ACME Bird seed 10.00
ACME Carrots 2.50
ACME Safe 50.00
ACME Sling 4.49
ACME TNT (1 stick) 2.50
ACME TNT (5 sticks) 10.00
Jet Set JetPack 1000 35.00
Jet Set JetPack 2000 55.00

（3）联结多个表

​ SQL对一条SELECT语句中可以联结的表的数目没有限制。创建联结的基本规则也相同。首先列出所有表，然后定义表之间的关系。例如:

SELECT prod_name, vend_name, prod_price, quantity
FROM orderitems, products, vendors
WHERE products.vend_id = vendors.vend_id 
	AND orderitems.prod_id = products.prod_id
	AND order_num = 20005

prod_name vend_name prod_price quantity
.5 ton anvil Anvils R Us 5.99 10
1 ton anvil Anvils R Us 9.99 3
TNT (5 sticks) ACME 10.00 5
Bird seed ACME 10.00 1

​ 此例子显示编号为20005的订单中的物品。订单物品存储在orderitems表中。每个产品按其产品ID存储，它引用products表中的产品。这些产品通过供应商ID联结到vendors表中相应的供应商，供应商ID存储在每个产品的记录中。这里的FROM子句列出了3个表，而WHERE子句定义了这两个联结条件，而第三个联结条件用来过滤出订单20005中的物品。

十六、创建高级联结

1、使用表别名

​ 给列起别名的语法如下：

SELECT CONCAT(RTrim(vend_name),'(',RTRIM(vend_country), ')') AS vend_title 
FROM vendors
ORDER BY vend_name;

​ CONCAT(RTrim(vend_name), '(', RTRIM(vend_country), ')') AS vend_title: 这个部分使用CONCAT函数将两个列的值组合在一起，创建了一个新的名为vend_title的列。RTRIM函数用于从列的末尾去掉空格，因此RTRIM(vend_name)和RTRIM(vend_country)将去除这些列中可能存在的多余空格。最终结果会将每个供应商的名称和国家组合成一个标题，并将其放在括号中

​ 别名除了用于列名和计算字段外，SQL还允许给表名起别名。这样做有两个主要理由:

• 缩短SQL语句;
• 允许在单条SELECT语句中多次使用相同的表。

下面的例子，就是运用了别名的方式，其功能与上一章的例子基本相同：

SELECT cust_name, cust_contact
FROM customers AS c, orders AS o, orderitems AS oi  
WHERE c.cust_id = o.cust_id
	AND oi.order_num = o.order_num
	AND prod_id = 'TNT2'

2、使用不同类型的联结

（1）自联结

​ 如前所述，使用表别名的主要原因之一是能在单条SELECT语句中不止一次引用相同的表。下面举一个例子。
​ 假如你发现某物品(其ID为DTNTR）存在问题，因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。此查询要求首先找到生产ID为DTNTR的物品的供应商，然后找出这个供应商生产的其他物品。下面是解决此问题的一种方法:

SELECT prod_id, prod_name
FROM products
WHERE vend_id = (SELECT vend_id
								 FROM products
								 WHERE prod_id = 'DTNTR');

prod_id prod_name
DTNTR Detonator
FB Bird seed
FC Carrots
SAFE Safe
SLING Sling
TNT1 TNT (1 stick)
TNT2 TNT (5 sticks)

​ 这是第一种解决方案，它使用了子查询。内部的SELECT语句做了一个简单的检索，返回生产ID为DTNTR的物品供应商的vend_id。该ID用于外部查询的WHERE子句中，以便检索出这个供应商生产的所有物品（第14章中讲授了子查询的所有内容。更多信息请参阅该章)。

​ 下面是使用联结的相同查询：

SELECT p1.prod_id, p1.prod_name
FROM products AS p1, products AS p2
WHERE p1.vend_id = p2.vend_id
	AND p2.prod_id = 'DTNTR';

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

（2）自然联结

​ 自然联结是这样一种联结，其中你只能选择那些唯一的列。这一般是通过对表使用通配符(SELECT *)，对所有其他表的列使用明确的子集来完成的。下面举一个例子:

SELECT c.*, o.order_num, o.order_date, oi.prod_id, oi.quantity, oi.item_price
FROM customers AS c, orders AS o, orderitems AS oi 
WHERE c.cust_id = o.cust_id
	AND oi.order_num = o.order_num
	AND prod_id = 'FB'

​ 在这个例子中，通配符只对第一个表使用。所有其他列明确列出，所以没有重复的列被检索出来。

（3）外部联结

​ 许多联结将一个表中的行与另一个表中的行相关联。但有时候会需要包含没有关联行的那些行。例如，可能需要使用联结来完成以下工作:

• 对每个客户下了多少订单进行计数，包括那些至今尚未下订单的客户;
• 列出所有产品以及订购数量，包括没有人订购的产品;
• 计算平均销售规模，包括那些至今尚未下订单的客户。

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

SELECT customers.cust_id, orders.order_num
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id

cust_id order_num
10001 20005
10001 20009
10003 20006
10004 20007
10005 20008

​ 可以看到只输出了有订单的客户，而那些没有订单的客户被忽略了，为了检索所有客户，可如下进行：

SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id

cust_id order_num
10001 20005
10001 20009
10002 NULL
10003 20006
10004 20007
10005 20008

​ 类似于上一章中所看到的内部联结,这条SELECT语句使用了关键字OUTER JOIN来指定联结的类型（而不是在WHERE子句中指定)。但是，与内部联结关联两个表中的行不同的是，外部联结还包括没有关联行的行。在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表（RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表)。上面的例子使用LEFT OUTERJOIN从FROM子句的左边表( customers表）中选择所有行。为了从右边的表中选择所有行，应该使用RIGHT OUTER JOIN，如下例所示:

SELECT customers.cust_id, orders.order_num
FROM customers RIGHT OUTER JOIN orders
ON customers.cust_id = orders.cust_id

cust_id order_num
10001 20005
10001 20009
10003 20006
10004 20007
10005 20008

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

3、使用带聚集函数的联结

​ 虽然至今为止聚集函数的所有例子只是从单个表汇总数据，但这些函数也可以与联结一起使用。
​ 请看下面的例子。如果要检索所有客户及每个客户所下的订单数，下面使用了COUNT ()函数的代码可完成此工作:

SELECT customers.cust_name, customers.cust_id, COUNT(orders.order_num) AS num_ord 
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id

cust_name cust_id num_ord
Coyote Inc. 10001 2
Wascals 10003 1
Yosemite Place 10004 1
E Fudd 10005 1

​ 此SELECT语句使用INNER JOIN将customers和orders表互相关联。GROUP BY子句按客户分组数据，因此，函数调用COUNT (orders.order_num)对每个客户的订单计数，将它作为num_ord返回。
​ 聚集函数也可以方便地与其他联结一起使用。请看下面的例子:

SELECT customers.cust_name, customers.cust_id, COUNT(orders.order_num) AS num_ord 
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id

cust_name cust_id num_ord
Coyote Inc. 10001 2
Mouse House 10002 0
Wascals 10003 1
Yosemite Place 10004 1
E Fudd 10005 1

​ 这个例子使用左外部联结来包含所有客户，甚至包含那些没有任何下订单的客户。结果显示也包含了客户Mouse House，它
有0个订单。

4、使用联结和联结条件

​ 汇总一下关于联结及其使用的某些要点。

• 注意所使用的联结类型。一般我们使用内部联结，但使用外部联结也是有效的。
• 保证使用正确的联结条件，否则将返回不正确的数据。
• 应该总是提供联结条件，否则会得出笛卡儿积。
• 在一个联结中可以包含多个表，甚至对于每个联结可以采用不同的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前，分别测试每个联结。这将使故障排除更为简单。

十七、组合查询

1、组合查询

 MySQL允许执行多个查询(多条SELECT语句)，并将结果作为单个查询结果集返回。这些组合查询通常称为并（ union)或复合查询。
有两种基本情况，其中需要使用组合查询:

• 在单个查询中从不同的表返回类似结构的数据;
• 对单个表执行多个查询，按单个查询返回数据。

2、创建组合查询

（1）使用UNION

​ UNION的使用很简单。所需做的只是给出每条SELECT语句，在各条语句之间放上关键字UNION。
​ 举一个例子，假如需要价格小于等于5的所有物品的一个列表，而且还想包括供应商1001和1002生产的所有物品（不考虑价格)。当然，可以利用WHERE子句来完成此工作，不过这次我们将使用UNION。

SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001, 1002)

vend_id prod_id prod_price
1003 FC 2.50
1002 FU1 3.42
1003 SLING 4.49
1003 TNT1 2.50
1001 ANV01 5.99
1001 ANV02 9.99
1001 ANV03 14.99
1002 OL1 8.99

​ 第一条SELECT检索价格不高于5的所有物品。第二条SELECT使用IN找出供应商1001和1002生产的所有物品。UNION指示MySQL执行两条SELECT语句，并把输出组合成单个查询结果集。

（2）UNION规则

 **UNION**的使用规则如下：

• UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔（因此，如果组合4条SELECT语句，将要使用3个UNION关键字)。
• UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出)。
• 列数据类型必须兼容:类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型)。

​ 如果遵守了这些基本规则或限制,则可以将并用于任何数据检索任务。

（3）包含或取消重复的行

​ 上面的例子的两个SELECT语句，如果分别执行的话，是有9行输出结果的，但使用UNION关键字的话，只输出8行，这是因为有一行重复元素。

​ 这是因为在使用UNION时，重复的行被自动取消，这是默认行为，但如果需要，可以改变它。只需要使用UNION ALL而不是UNION，如下：

SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
UNION ALL
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001, 1002)

vend_id prod_price
1003 2.50
1002 3.42
1003 4.49
1003 2.50
1001 5.99
1001 9.99
1001 14.99
1002 3.42
1002 8.99

（4）对组成查询结果排序

​ SELECT语句的输出用ORDER BY子句排序。在用UNION组合查询时，只能使用一条ORDER BY子句，它必须出现在最后一条SELECT语句之后。对于结果集，不存在用一种方式排序一部分，而又用另一种方式排序另一部分的情况，因此不允许使用多条ORDER BY子句，上一个例子排序后的结果如下：

SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
UNION 
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001, 1002)
ORDER BY vend_id, prod_price

vend_id prod_id prod_price
1001 ANV01 5.99
1001 ANV02 9.99
1001 ANV03 14.99
1002 FU1 3.42
1002 OL1 8.99
1003 FC 2.50
1003 TNT1 2.50
1003 SLING 4.49

​ 这条UNION在最后一条SELECT语句后使用了ORDER BY子句。虽然ORDER BY子句似乎只是最后一条SELECT语句的组成部分，但实际上MySQL将用它来排序所有SELECT语句返回的所有结果。

十八、全文本搜索

1、理解全文本搜索

 在使用全文本搜索时，MySQL不需要分别查看每个行，不需要分别分析和处理每个词。MySQL创建指定列中各词的一个索引，搜索可以针对这些词进行。这样，MySQL可以快速有效地决定哪些词匹配（哪些行包含它们)，哪些词不匹配，它们匹配的频率，等等。

2、使用全文本搜索

（1）创建全文本搜索

​ 一般在创建表时启用全文本搜索。CREATE TABLE语句(第21章中介绍）接受FULLTEXT子句，它给出被索引列的一个逗号分隔的列表。
下面的CREATE语句演示了FULLTEXT子句的使用:

CREATE TABLE productnotes
(
note_id INT NOT NULL auto_INCREMENT,
prod_id char(10) NOT NULL,
note_date DATETIME NOT NULL,
note_text TEXT NULL,
PRIMARY KEY(note_id),
FULLTEXT(note_text)
)ENGINE=MyISAM;

​ 第21章将详细考察CREATE TABLE语句。现在，只需知道这条
CREATE TABLE语句定义表productnotes并列出它所包含的
列即可。这些列中有一个名为note_text的列，为了进行全文本搜索，MySQL根据子句FULLTEXT ( note_text)的指示对它进行索引。这里的FULLTEXT索引单个列，如果需要也可以指定多个列。
​ 在定义之后，MySQL自动维护该索引。在增加、更新或删除行时，
索引随之自动更新。

（2）进行全文本搜索

​ 在索引之后，使用两个函数Match ( )和Against()执行全文本搜索，其中Match()指定被搜索的列，Against()指定要使用的搜索表达式。

SELECT note_text
FROM productnotes
WHERE MATCH(note_text) Against('rabbit')

note_text
Customer complaint: rabbit has been able to detect trap, food apparently less effective now.
Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait.

​ 此SELECT语句检索单个列note_text。由于WHERE子句，一个全文本搜索被执行。Match(note_text)指示MySQL针对指定的列进行搜索，Against ( ‘ rabbit ‘ )指定词rabbit作为搜索文本。由于有两行包含词rabbit，这两个行被返回。

（3）使用查询扩展

​ 查询扩展用来设法放宽所返回的全文本搜索结果的范围。考虑下面的情况。你想找出所有提到anvils的注释。只有一个注释包含词anvils,但你还想找出可能与你的搜索有关的所有其他行，即使它们不包含词anvils。
​ 这也是查询扩展的一项任务。在使用查询扩展时，MySQL对数据和索引进行两遍扫描来完成搜索:

• 首先，进行一个基本的全文本搜索，找出与搜索条件匹配的所有行;
• 其次，MySQL检查这些匹配行并选择所有有用的词（我们将会简要地解释MySQL如何断定什么有用，什么无用)。
• 再其次，MySQL再次进行全文本搜索，这次不仅使用原来的条件，而且还使用所有有用的词。

​ 利用查询扩展，能找出可能相关的结果，即使它们并不精确包含所查找的词。

下面是一个简单的全文本搜索，没有查询扩展：

SELECT note_text
FROM productnotes
WHERE MATCH(note_text) Against('anvils')

note_text
Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.

​ 下面是相同的查询，但这次使用查询扩展：

SELECT note_text
FROM productnotes
WHERE MATCH(note_text) Against('anvils' WITH QUERY EXPANSION)

note_text
Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.
Customer complaint:
Sticks not individually wrapped, too easy to mistakenly detonate all at once.
Recommend individual wrapping.
Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.
Please note that no returns will be accepted if safe opened using explosives.
Customer complaint: rabbit has been able to detect trap, food apparently less effective now.
Customer complaint:
Circular hole in safe floor can apparently be easily cut with handsaw.
Matches not included, recommend purchase of matches or detonator (item DTNTR).

​ 这次返回了7行。第一行包含词anvils，因此等级最高。第二行与anvils无关,但因为它包含第一行中的两个词(customer和recommend)，所以也被检索出来。第3行也包含这两个相同的词，但它们在文本中的位置更靠后且分开得更远，因此也包含这一行，但等级为第三。第三行确实也没有涉及anvils（按它们的产品名)。
​ 正如所见，查询扩展极大地增加了返回的行数，但这样做也增加了你实际上并不想要的行的数目。

十九、插入数据

1、数据插入

​ 顾名思义，INSERT是用来插入（或添加）行到数据库表的。插入可以用几种方式使用:

• 插入完整的行;
• 插入行的一部分;
• 插入多行;
• 插入某些查询的结果。

2、插入完整的行

​ 把数据插入表中最简单的方法是使用基本的INSERT语法，它要求指定表名和被插入到新行的值。如下：

INSERT INTO customers
VALUES
	( NULL, 'Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', NULL, NULL );

​ 此例子插入一个新客户到customers表。存储到每个表列中的数据在VALUES子句中给出，对每个列必须提供一个值。如果某个列没有值(如上面的cust_contact和cust_email列),应该使用NULL值（假定表允许对该列指定空值)。各个列必须以它们在表定义中出现的次序填充。第一列cust_id也为NULL。这是因为每次插入一个新行时，该列由MySQL自动增量。你不想给出一个值（这是MySQL的工作)，又不能省略此列（如前所述，必须给出每个列)，所以指定一个NULL值（它被MySQL忽略，MySQL在这里插入下一个可用的cust_id值)。
​ 虽然这种语法很简单，但并不安全，应该尽量避免使用。上面的SQL语句高度依赖于表中列的定义次序，并且还依赖于其次序容易获得的信息。即使可得到这种次序信息，也不能保证下一次表结构变动后各个列保持完全相同的次序。因此，编写依赖于特定列次序的SQL语句是很不安全的。如果这样做，有时难免会出问题。
​ 编写INSERT语句的更安全（不过更烦琐）的方法如下:

INSERT INTO customers ( cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email )
VALUES
	( 'Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', NULL, NULL );

​ 因为提供了列名，VALUES必须以其指定的次序匹配指定的列名，不一定按各个列出现在实际表中的次序。其优点是，即使表的结构改变，此INSERT语句仍然能正确工作。你会发现cust_id的NULL值是不必要的，cust_id列并没有出现在列表中，所以不需要任何值。

3、插入多个行

​ 有两种方法：

• 可以使用多条INSERT语句，每条语句用一个分号结束，如下：

• INSERT INTO TABLE_NAME(...) VALUES(...);
  INSERT INTO TABLE_NAME(...) VALUES(...);
  ...
  - ```mysql
    INSERT INTO TABLE_NAME(...)
    VALUES(...),(...)...;
  

4、插入检索出的数据

​ INSERT一般用来给表插入一个指定列值的行。但是，INSERT还存在另一种形式，可以利用它将一条SELECT语句的结果插入表中。这就是所谓的INSERT SELECT,顾名思义，它是由一条INSERT语句和一条SELECT语句组成的。
​ 假如你想从另一表中合并客户列表到你的customers表。不需要每次读取一行，然后再将它用INSERT插入，可以如下进行:

INSERT INTO customers ( cust_id, cust_contact, cust_email, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country ) SELECT
cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country 
FROM
	custnew;

​ 这个例子使用INSERT SELECT从custnew中将所有数据导入customers。SELECT语句从custnew检索出要插入的值，而不是列出它们。SELECT中列出的每个列对应于customers表名后所跟的列表中的每个列。这条语句将插入多少行有赖于custnew表中有多少行。如果这个表为空，则没有行被插入（也不产生错误，因为操作仍然是合法的)。如果这个表确实含有数据，则所有数据将被插入到customers。
​ 这个例子导入了cust_id（假设你能够确保cust_id的值不重复)。你也可以简单地省略这列（从INSERT和SELECT中)，这样MySQL就会生成新值。
​ INSERT SELECT中的列名为简单起见,这个例子在INSERT和SELECT语句中使用了相同的列名。但是，不一定要求列名匹配。事实上,MySQL甚至不关心SELECT返回的列名,它使用的是列的位置,因此SELECT中的第一列(不管其列名）将用来填充表列中指定的第一个列,第二列将用来填充表列中指定的第二个列，如此等等,这对于从使用不同列名的表中导入数据是非常有用的。
​ INSERT SELECT中SELECT语句可包含WHERE子句以过滤插入的数据。

二十、更新和删除数据

1、更新数据

​ 为了更新（修改）表中的数据，可使用UPDATE语句。可采用两种方式使用UPDATE:

• 更新表中特定行;
• 更新表中所有行。

下面分别对它们进行介绍。

​ UPDATE语句非常容易使用，甚至可以说是太容易使用了。基本的UPDATE语句由3部分组成，分别是:

• 要更新的表;
• 列名和它们的新值;
• 确定要更新行的过滤条件。

​ 举一个简单例子。客户10005现在有了电子邮件地址，因此他的记录需要更新，语句如下:

UPDATE customers
SET cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005

​ 更新多个列的语法如下：

UPDATE customers 
SET cust_email = 'elmer@fudd.com',
cust_name = 'The Fudds' 
WHERE
	cust_id = 10005

​ 在更新多个列时，只需要使用单个SET命令，每个“列=值”对之间用逗号分隔。

​ 为了删除某个列的值，可以设置它为NULL，如下：

UPDATE customers 
SET cust_email = NULL 
WHERE
	cust_id = 10005

2、删除数据

​ 为了从一个表中删除（去掉）数据，使用DELETE语句。可以两种方式使用DELETE:

• 从表中删除特定的行;
• 从表中删除所有行。

​ 下面的语句从customers表中删除一行。

DELETE FROM customers
WHERE cust_id = 10006

​ 上述例子，只删除客户10006，若省略WHERE子句，它将删除表中每个客户。

​ DELETE不需要列名或通配符。DELETE删除整行而不是删除列。为了删除指定的列，请使用UPDATE语句。

3、更新和删除的指导原则

​ 前一节中使用的UPDATE和DELETE语句全都具有WHERE子句，这样做的理由很充分。如果省略了WHERE子句，则UPDATE或DELETE将被应用到表中所有的行。换句话说，如果执行UPDATE而不带WHERE子句，则表中每个行都将用新值更新。类似地，如果执行DELETE语句而不带WHERE子句，表的所有数据都将被删除。
​ 下面是许多SQL程序员使用UPDATE或DELETE时所遵循的习惯。除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句。
​ 保证每个表都有主键(如果忘记这个内容，请参阅第15章)，尽可能像WHERE子句那样使用它（可以指定各主键、多个值或值的范围)。在对UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确。使用强制实施引用完整性的数据库（关于这个内容，请参阅第15章)，这样MySQL将不允许删除具有与其他表相关联的数据的行。

二十一、创建和操作表

1、创建表

​ MySQL不仅用于表数据操纵，而且还可以用来执行数据库和表的所有操作，包括表本身的创建和处理。

​ 创建表的方法有两种：

• 使用具有交互创建和管理表的工具；
• 表也可以直接用MySQL语句操纵。

可以使用CREATE TABLE创建表。在使用交互工具时，实际上使用的是MySQL语句。但是这些语句不上用户编写的，界面工具会自动生成并执行相应的MySQL语句（改现有表等操作也是如此）。

（1）表创建基础

​ 使用CREATE TABLE创建表时，必须给出下列信息：

• 新表的名字，在关键字CREATE TABLE
• 表列的名字和定义，用逗号隔开

CREATE TABLE语句也可能会包括其他关键字或选项，但至少包括表名和列的细节。如下：

CREATE TABLE customers (
  cust_id int NOT NULL AUTO_INCREMENT,
  cust_name char(50) NOT NULL,
  cust_address char(50) DEFAULT NULL,
  cust_city char(50) DEFAULT NULL,
  cust_state char(5) DEFAULT NULL,
  cust_zip char(10) DEFAULT NULL,
  cust_country char(50) DEFAULT NULL,
  cust_contact char(50) DEFAULT NULL,
  cust_email char(255) DEFAULT NULL,
  PRIMARY KEY (cust_id)
) ENGINE=InnoDB

​ 从上面的例子可以看到，表名紧跟在CREATE TABLE关键字后面，实际的表定义(所有列)括在圆括号中。各列之间用逗号隔开。这个表由9列组成，每列的定义以列名（在表中必须是唯一的）开始，后跟列的数据类型。表的的主键可以在创建表时用PRIMARY KEY关键字指定。

（2）使用NULL值

​ NULL值就是没用值或缺值。允许NULL即允许插入列时不给出该列的值，不允许NULL值的列不接受该列没有值的行，换句话说，在插入或更新行时，该列必须有值。

CREATE TABLE orders (
  order_num int NOT NULL AUTO_INCREMENT,
  order_date datetime NOT NULL,
  cust_id int NOT NULL,
  PRIMARY KEY (order_num),
  KEY fk_orders_customers (cust_id),
  CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id)
) ENGINE=InnoDB 

​ 上面这个例子，创建了orders表。该表包括3个列，分别是订单号、订单日期、客户ID，由于3个列都需要，因此每个列的定义都包含NOT NULL。如果试图插入没用值的列，将会返回错误，且插入失败。

​ 下一个例子混合了NULL和NOT NULL

CREATE TABLE vendors (
  vend_id int NOT NULL AUTO_INCREMENT,
  vend_name char(50) NOT NULL,
  vend_address char(50) DEFAULT NULL,
  vend_city char(50) DEFAULT NULL,
  vend_state char(5) DEFAULT NULL,
  vend_zip char(10) DEFAULT NULL,
  vend_country char(50) DEFAULT NULL,
  PRIMARY KEY (vend_id)
) ENGINE=InnoDB

​ 由于供应商和供应商名字子列是必需的，因此使用NOT NULL，其余5个列都允许NULL值。NULL为默认值，如果不指定NOT NULL，则认定为NULL。

（3）主键再介绍

​ 主键值必须唯一。即主键如果使用单个列，则其值必须唯一；如果使用多个列，则这些列的组合之必须唯一。

​ 下面是单个列作为主键的语句定义：

PRIMARY KEY (vend_id)

​ 为创建多个列组成的主键，应该以逗号分隔的列给出各列名：

CREATE TABLE orderitems (
  order_num int NOT NULL,
  order_item int NOT NULL,
  prod_id char(10) NOT NULL,
  quantity int NOT NULL,
  item_price decimal(8,2) NOT NULL,
  PRIMARY KEY (order_num,order_item),
  KEY fk_orderitems_products (prod_id),
  CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num),
  CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id)
) ENGINE=InnoDB 

​ 每个订单有多项物品，但每个订单任何时候都只有1个第一项物品，1个第二项物品，如此等待。因此订单号（order_num列）和订单物品（order_item列）的组合是唯一的，因此可以作为主键。

（4）AUTO_INCREMENT

​ 观察一下customers表和orders表。customers表中的顾客列由cust_id唯一标识，每个顾客有一个唯一的编号。

​ 只要能保证每个顾客的编号不同，其值是多少都可以，为了方便我们引入AUTO_INCREMENT，如下：

cust_id int NOT NULL AUTO_INCREMENT,

​ AUTO_INCREMENT告诉MySQL，本列每当增加一行时自动增量。每次执行INSERT操作时，MySQL自动对该列增量，给该列赋予下一个可用的值，这样给每个行分配一个唯一的cust_id，从而可用用作主键的值。

​ 每个表只允许一个AUTO_INCREMENT列，而且它必须被索引。

（5）指定默认值

​ 如果在插入行时没有给出值，MySQL允许指定此时使用的默认值。默认值用CREATE TABLE语句的列定义中的DEFAULT关键字指定。

​ 不允许函数：与大多数DBMS不一样，MySQL不允许使用函数作为默认值，它只支持常量。

​ 使用默认值而不是NULL值 许多数据库开发人员使用默认值而不是NULL列。

（6）引擎类型

​ 迄今为止使用的CREATE TABLE语句全都以ENGINE=InnoDB语句结束。

​ 与其他DBMS一样，MySQL有一个具体管理和处理数据的内部引擎在你使用CREATE TABLE语句时，该引擎具体创建表，而在你使用SELECT语句或进行其他数据库处理时，该引擎在内部处理你的请求。多数时候此引擎都隐藏在DBMS内，不需要过多关注它。

​ 但MySQL与其他DBMS不一样，它具有多种引擎。它打包多个引擎这些引擎都隐藏在MySQL服务器内，全都能执行CREATE TABLE和SELECT等命令。
为什么要发行多种引擎呢?因为它们具有各自不同的功能和特性为不同的任务选择正确的引擎能获得良好的功能和灵活性。

当然，你完全可以忽略这些数据库引擎。如果省略ENGINE=语句则使用默认引擎(很可能是MyISAM)，多数SQL语句都会默认使用它。但并不是所有语句都默认使用它，这就是为什么ENGINE=语句很重要的原因(也就是为什么本书的样列表中使用两种引的原因)。

以下是几个需要知道的引擎:

• InnoDB是一个可靠的事务处理引擎(参见第26章)，它不支持全文本搜索;
• MEMORY在功能等同于MyISAM，但由于数据存储在内存(不是磁盘)中，速度很快(特别适合于临时表);
• MyISAM是一个性能极高的引擎，它支持全文本搜索(参见第18章)，但不支持事务处理。

2、更新表

​ 更新表，可以使用ALTER TABLE语句。

​ 想要更新表，必须给出以下信息：

• 在ALTER TABLE后给出要更改的表名。
• 所做更改的列表。

​ 下面的例子给vendors表添加一个vend_phone列：

alter table vendors
add vend_phone char(20);

​ 删除刚刚增加的列，可以使用：

alter table vendors
DROP COLUMN vend_phone;

3、删除表

​ 使用DROP TABLE 即可：

DROP TABLE xx

4、重命名表

使用RENAME TABLE即可：

RENAME TABLE xx TO yy