@[toc]

前言

MySQL 已经成为世界上最受欢迎的数据库管理系统之一。无论是用在小型开发项目上，还是用来构建那些声明显赫的网站，MySQL 都证明了自己是个稳定、可靠、快速、可信的系统。

--> 数据库软件应称为 数据库管理系统（DBMS )

书中图含义

闹钟：向读者提示可能出现的问题

羽毛：表示跟上下文的内容相关的一些有意思的信息

灯泡：提示建议，教读者用容易的办法完成某项任务

书:新术语，提供新的基本词汇的清晰定义。

输入：表示读者自己键入的代码，通常出现在程序清单之前

输出：表示运行 MySQL 代码后得到的结果，通常出现在程序清单之后。

分析：告诉作者这是对输入输出的逐行分析

数据库基础

什么是数据库

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

术语：保存有组织的数据的容器（通常是一个文件或者一组文件）。

数据库是通过 DBMS 创建和操作的容器。

一般情况来说，你不直接访问数据库，而是通过你使用的 DBMS 替你访问数据库。

表

当你需要将数据放入数据库中时，并不是随便将它们放进某个区域而是需要在数据库中创建一个文件，这个文件称为表。是一种结构化的文件，可用来存储某种特定类型的数据。

注意：设计表结构时，存储在表中的数据是一种类型的数据或一个清单。决不应该将顾客的清单与订单的清单存储在同一个数据库表中。这样子做将使以后的检索和访问很困难。应该创建 2 个表，每个清单一个表。

术语：某种特定类型数据的结构化清单

表名

用来标识自己。此名字是唯一的，这表示数据库中没有其他表具有相同的名字。

表名的唯一性取决于多个因素，如数据库名和表名等的结合。这表示，虽然在相同的数据库中不能俩次使用相同的表名，但在不同的数据库中却可以使用相同的表。

模式

表具有一些特定，这些特定定义了数据在表中如何存储，如可以存储什么样的数据，数据如何分解，各部分信息如何命名，等等。描述表的这组信息就是所谓的模式，模式可以用来描述数据库中特定的表以及整个数据库（和其他表的关系）。

术语：关于数据库和表的布局及特性的信息。

列和数据类型

表由列组成。列中存储着表中某部分的信息。

术语：表中的一个字段。所有表都是由一个或多个列组成。

分解数据：正确地将数据分解为多个列极为重要。例如，城市、州、邮政编码应该总是独立的列。通过把它分解开，才有可能利用特定的列对数据进行排序和过滤（例如找出特定州或特定城市的所有顾客），如果它们组合在一个列中，则按州进行排序或过滤会很难。

数据类型：定义列可以存储的数据种类。

术语：所容许的数据的类型。每个表的列都有相应的数据类型，它限制（或容许）该列中存储的数据。

行

表中的数据是按行存储的，所保存的每个记录存储在自己的行内。

行（row）：表中的一个记录

主键

表中每一行都应该有可以唯一标识自己的一列（或一组列）。

术语：一列（或一组列），其值能够唯一区分表中的每个行

列作为主键需要满足的条件

• 任意两行都不具备相同的主键值；

• 每个行都必须具有一个主键值（主键列不允许 NULL 值）

主键通常定义在表的一列上，但这并不是必需的，也可以一起使用多个列作为主键。在使用多列作为主键时，上述条件必须应用到构成主键的所有列，所有列值的组合必须是唯一的（单个列可以不唯一）

设置成主键的好习惯：

• 不更新主键列中的值；

• 不重用主键列中的值

• 不在主键列中使用可能会更改的值。（例如，如果使用一个 名字作为主键以标识某个供应商，当该供应商合并和更改其 名字时，必须更改这个主键。）

疑惑：更改名字为何更改主键

什么是 SQL

SQL(sequel)是结构化查询语言（Structured Query Language）的缩写。SQL 是一种专门用来与数据库通信的语言。

设计 SQL 的目的：提供一种从数据库中读写数据的简单有效的方法。

SQL 的优点

• SQL 不是针对某个特定数据库供应商专有的语言。几乎所有的 DBMS 都支持 SQL

• SQL 简单易学

• 可以进行非常复杂和高级的数据库操作

DBMS专用的SQL
    SQL不是一种专利语言，存在着一个标准委员会，他们试图定义可供所有DBMS使用的SQL语言，但是事实上任意两个DBMS实现的SQL都不完全相同。SQL语言不一定完全可移植。

什么是 MySQL

是一种 DBMS，，即它是一种数据库软件。

优点：

• 成本——MySQL 是开放源代码的，一般可以免费使用（甚至可以免费修改）。

• 性能——MySQL 执行很快

• 可信赖——某些非常重要和声望很高的公司、站点使用 MySQL,这些公司和站点都用 MySQL 来处理自己的重要数据。

• 简单——MySQL 很容易安装和使用

客户机-服务机软件

DBMS 可以分为两类：

一类为基于共享文件系统的 DBMS（用于桌面用途，不用于高端或更关键的应用），

另一类为基于客户机-服务机的 DBMS（MySQL、Oracle 以及 Microsoft SQL Server 等数据库）。

• 服务器部分是负责所有数据访问和处理的一个软件。这个软件运行在称为 数据库服务器 的计算机上。与数据文件打交道的只有服务器软件。关于数据、数据添加、删除和数据更新的所有请求都由服务器软件完成。这些请求或更改来自运行客户机软件的计算机。

• 客户机是与用户打交道的软件，例如，客户机软件通过网络提交该请求给服务器软件。服务器软件处理这个请求，根据需要过滤、丢弃和排序数据；然后把结果送回到你的客户机软件。

进行数据库交互需要客户机软件与服务器软件进行通信。软件可以安装在俩台计算机上或一台计算机上。

MySQL 工具

MySQL 是一个客户机-服务器 DBMS，因此，为了使用 MySQL,需要有一个客户机，即你需要用来与 MySQL 打交道（MySQL 提供要执行的命令）的一个应用。

编写和测试 MySQL 脚本时的三个实用程序：

• mysql 命令行实用程序 ——win+r mysql -uroot -proot

  • 完整的命令行选项和参数列表可用 mysql --help 获得。

  • 命令输入在 mysql>之后；命令用； 或\g 结束，换句话说，仅按 Enter 不执行命令；

  • 输入 help 或\h 获得帮助，也可以输入更多的文本获得特定命令的帮助（如，输入 help select 获得使用 SELECT 语句的帮助）

  • 输入 quit 或 exit 退出命令行实用程序

• MySQL Administrator ——是一个图形交互客户机，用来简化 MySQL 服务器的管理

• Server Infomation ( 服务器信息 )显示客户机和连接的服务器的状态和版本信息；

• Service Control( 服务控制 ) 允许停止和启动 MySQL 以及指定服务器特性；

• User Administration( 用户管理 )用来定义 MySQL 用户、登录和权限；

• Catalogs( 目录 ) 列出可用的数据库并允许创建数据库和表

• MySQL Query Browser

使用 MySQL

本章目的

如何连接和登录到 MySQL

如何执行 MySQL 语句

以及如何获得数据库和表的信息

连接

MySQL 与所有客户机-服务器 DBMS 一样，要求在能执行命令之前登录到 DBMS。登录名可以与网络登录名不相同（假定你使用网络）。MySQL 在内部保存自己的用户列表，并且把每个用户与各种权限关联起来。

连接 MySQL 需要以下信息

1. 主机名（计算机名）——如果连接到本地 MySQL 服务器，如 localhost；

2. 端口（如果使用默认端口 3306 之外的端口）；

3. 一个合法的用户名；

4. 用户口令（如果需要）。

选择数据库

在执行任意数据库操作之前，需要选择一个数据库，可使用 USE 关键字。

关键字（key word) 作为 MySQL 语言组成部分的一个保留字。决不要用关键字命名一个表或列。

USE 数据库名

了解数据库和表

数据库、表、列、用户、权限等的信息存储在数据库和表中（MySQL 使用 MySQL 来存储这些信息）。内部的表一般不直接访问。可用 MySQL 的 SHOW 命令来显示这些信息（MySQL 从内部表中提取这些信息）。

SHOW DATABASES;

返回可用数据库的一个列表。包含在这个列表中的可能是 MySQL 内部使用的数据库（如 information_schema）

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

显示表信息时需先选择数据库。

SHOW 也可以用来显示列

SHOW COLUMNS FROM 列；

它对每个字段返回一行，行内包含字段名、数据类型、是否允许 NULL、键信息、默认值以及其他信息

什么是自动增量？

某些表列需要唯一值。例如，订单编号、雇员ID或（如上面例子中所示的）顾客ID。在每个行添加到表中时，MySQL可以自动地为每个行分配下一个可用编号，不用在添加一行时手动分配唯一值（这样做必须记住最后一次使用的值）。这个功能就是所谓的自动增量。如果需要它，则必须在用CREATE语句创建表时把它作为表定义的组成部分。我们
将在第21章中介绍CREATE语句。

DESCRIBE 语句 MySQL 支持用 DESCRIBE 作为 SHOW COLUMNS FROM 的一种快捷方式。。即 DESCRIBE 列 = SHOW CULUMNS FROM 列；

还支持其他语句：

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

SHOW CREATE DATABASE 和 SHOW CREATE TABLE,分别用来显示创建特定数据库或表的 MySQL 语句；

SHOW GRANTS,用来显示授予用户（所有用户或特定用户）的安全权限；

SHOW ERRORS 和 SHOW WARNING,用来显示服务器错误或警告信息。

命令行 HELP SHOW 查看更多

检索数据

目的：使用 SELECT 语句从表中检索一个或多个数据列。

SELECT 语句

用途：从一个或多个表中检索信息

使用条件：必须至少给出两条信息——想选择什么以及从什么地方选择。

检索单个列

SELECT 列名 FROM 表名；

结束 SQL 语句

多条SQL语句必须以分号（；）分隔。MySQ如同多数DBMS一样，不需要在单条SQL语句后加分号。但特定的DBMS可能必须在单条SQL语句后加上分号。当然，如果愿意可以总是加上分号。事实上，即使不一定需要，但加上分号肯定没有坏处。如果你使用的是mysql命令行，必须加上分号来结束SQL语句。

SQL 语句和大小写

SQL语句不区分大小写，因此SELECT与select是相同的。同样，写成Select也没有关系。许多SQL开发人员喜欢对所有SQL关键字使用大写，而对所有列和表名使用小写，这样做使代码更易于阅读和调试。不过，一定要认识到虽然SQL是不区分大小写的，但有些标识符（如数据库名、表名、列名）可能不同：在MySQL 4.1及之前的版本中，这些标识符默认是区分大小写的；在MySQL 4.1.1版本中，这些标识符默认是不区分大小写的。最佳方式是按照大小写的惯例，且使用时保持一致。

检索多个列

SELECT 关键字后给出多个列名，列名之间必须以逗号分隔。

当心逗号 在选择多个列时，一定要在列名之间加上逗号，但
最后一个列名后不加。如果在最后一个列名后加了逗号，将出
现错误。

​ SELECT 列名 1，列名 2，列名 3 FROM 表名；

检索所有列

通过在实际列名的位置使 用星号（*）通配符来达到

SELECT * FROM 表名；

使用通配符

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

检索未知列

使用通配符有一个大优点。由于不明确指定列名（因为星号检索每个列），所以能检索出名字未知的列。

检索不同的行(DISTINCT)

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

DISTINCT 关键字必须直接放在列名的前面。

SELECT DISTINCT 列名 FROM 表名；

不能部分使用 DISTINCT

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

限制结果(LIMIT)

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

SELECT 列名 FROM 表名 LIMIT 5； 检索单个列。指示 fMySQL 返回值不多于 5 行。

SELECT 列名 FROM 表名 LIMIT 5,5；第一个数为开始位置，第二个数为要检索的行数。

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

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

MySQL 5的LIMIT语法 LIMIT 3, 4的含义是从行4开始的3
行还是从行3开始的4行？如前所述，它的意思是从行3开始的4
行，这容易把人搞糊涂。由于这个原因，MySQL 5支持LIMIT的另一种替代语法。LIMIT4 OFFSET 3意为从行3开始取4行，就像LIMIT 3, 4一样。

使用完全限定的表名

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

SELECT 表名.列名 FROM 数据库名.表名；

排序检索数据

排序数据

关系型数据库设计理论认为，如果不明确规定排序顺序，则不应该假定检索出的数据的顺序有意义。

MySQL 检索出的数据并不是以纯粹的随机顺序显示的。如果不排 序，数据一般将以它在底层表中出现的顺序显示。这可以是数据最初添加到表中的顺序。但是，如果数据后来进行过更新或删除，则此顺序将会受到 MySQL 重用回收存储空间的影响。因此，如果不明确控制的话，不能（也不应该）依赖该排序顺序。

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

通过非选择列进行排序
ORDER BY子句中使用的列将是为显示所选择的列。但是，实际上并不一定要这样，用非检索的列排序数据是完全合法的。

为了明确地排序用 SELECT 语句检索出的数据，可使用 ORDER BY 子句。

SELECT 列名 FROM 表名 ORDER BY 列名；

按多个列排序

SELECT 列名 1，列名 2，列名 3 FROM 表名 ORDER BY 列名 1，列名 2；

注意：在按多个列排序时，排序完全按所规定的顺序进行。 在多个行具有相同的 列名 1 值时才对产品按 列名 2 进行排序。如果列名 1中所有的值都是唯一的，则不会按列名 2排序。

指定排序方向

数据不限于默认排序（升序 A-->Z），还可以使用 ORDER BY 子句以降序（ Z-->A）顺序排序。DESC 关键字为降序排序

SELECT 列名 1，列名 2，列名 3 FROM 表名 ORDER BY 列名 1 DESC

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

问题：DESC关键字如果写在多个列名的后面，那么谁降序？

区分大小写和排序顺序

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

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

降序：

SELECT 列名 1 FROM 表名 ORDER BY 列名 DESC LIMIT 1；

升序：

SELECT 列名 1 FROM 表名 ORDER BY 列名 ASC LIMIT 1；

过滤数据

使用 WHERE 子句

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

​ 在 SELECT 语句中，数据根据 WHERE 子句中指定的搜索条件进行过滤。 WHERE 子句在表名（FROM 子句）之后给出。

SELECT 列名1 ，列名2  FROM 表名 WHERE  列名 = 值;

SQL过滤与应用过滤
数据也可以在应用层过滤。为此目的，SQL的SELECT语句为客户机应用检索出超过实际所需的数据，然后客户机代码对返回数据进行循环，以提取出需要的行。
通常，这种实现并不令人满意。因此，对数据库进行了优化，
以便快速有效地对数据进行过滤。让客户机应用（或开发语言）
处理数据库的工作将会极大地影响应用的性能，并且使所创建
的应用完全不具备可伸缩性。此外，如果在客户机上过滤数据，
服务器不得不通过网络发送多余的数据，这将导致网络带宽的
浪费。

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

WHERE 子句操作符

| 操作符 | 说明 | | :-----: | :-------: | | = | 等于 | | <> | 不等于 | | != | 不等于 | | < | 小于 | | <= | 小于等于 | | > | 大于 | | >= | 大于等于 | | BETWEEN | 在指定的两个值之间 |

检查单个值

MySQL 在执行匹配时默认不区分大小写。

不匹配检查

SELECT 列名 1，列名 2 FROM 表名 WHERE 列名 <> 值；

何时使用引号

如果仔细观察上述WHERE子句中使用的条件，会看到有的值括在单引号内，而有的值未括起来。单引号用来限定字符串。如果将值与串类型的
列进行比较，则需要限定引号。用来与数值列进行比较的值不用引号。

范围值检查

SELECT 列名 1，列名 2 FROM 表名 WHERE 列名 2 BETWEEN 值 1 AND 值 2；

空值检查

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

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

SELECT 语句有一个特殊的 WHERE 子句，可用来检查具有 NULL 值的列。 这个 WHERE 子句就是 IS NULL 子句。

SELECT 列名 1 FROM 表名 WHERE 列名 2 IS NULL；

NULL 与不匹配 在通过过滤选择出不具有特定值的行时，你 可能希望返回具有 NULL 值的行。但是，不行。因为未知具有 特殊的含义，数据库不知道它们是否匹配，所以在匹配过滤 或不匹配过滤时不返回它们。 因此，在过滤数据时，一定要验证返回数据中确实给出了被 过滤列具有 NULL 的行。

数据过滤

操作符（operator) 用来联结或改变 WHERE 子句中的子句的关键字。也称为逻辑操作符。

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

AND 操作符

为了通过不止一个列进行过滤，可使用 AND 操作符给 WHERE 子句附加 条件。

SELECT 列名 1，列名 2，列名 3 FROM 表名 WHERE 列名 4 = 值 AND 列名 2 <= 值 ；

AND 用在 WHERE 子句中的关键字，用来指示检索满足所有给定 条件的行。

还可以多添加多个过滤条件，每添加一条就要使用一个 AND。

OR 操作符

它指示 MySQL 检索匹配任一条件的行。

SELECT 列名 1，列名 2 FROM 表名 WHERE 列名 3 = 值 1 OR 列名 3 = 值 2；

OR 操作符告诉 DBMS 匹配任一条件而不是同时匹配 两个条件。如果这里使用的是 AND 操作符，则没有数据返回（此时创建 的 WHERE 子句不会检索到匹配的产品）。

计算次序

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

SELECT 列名 1，列名 2 FROM 表名 WHERE 列名 3 = 值 1 OR 列名 3 =值 2 AND 列名 2 >=值 3；

由于 AND 在计算次序中优先级更高，操作符被错误地组合了。上面的过滤条件变成了列名 3 的值制造的任何产品或者列名 2 制造的大于等于值 3 的任何产品。

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

SELECT 列名 1，列名 2 FROM 表名 WHERE （列名 3 = 值 1 OR 列名 3 =值 2） AND 列名 2 >=值 3；

IN 操作符

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

SELECT 列名 1，列名 2 FROM 列名 3 IN（值 1，值 2）ORDER BY 列名 1；

--检索值 1 和值 2 的所有列 1，列 2。IN 操作符后跟由逗号分隔的合法值清单，整个清单必须括在圆括号中。

IN 操作符与 OR 操作符可以相同的功能。IN 操作符的优点具体如下:

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

• 在使用 IN 时，计算的次序更容易管理（因为使用的操作符更少）。

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

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

NOT 操作符

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

SELECT 列名 1，列名 2 FROM 表名 WHERE 列名 3 NOT IN（值 1，值 2）ORDER BY 列名 1；

MySQL 不是匹配值 1 和值 2 的列 3，而是匹配之外的列 3。

为什么使用 NOT？

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

在与 IN 操作符联合使用时，NOT 使找出与条件列表不匹配的行非常简单。

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

通配符进行过滤

LIKE 操作符

利用通配符可创建比较特定数据的搜索模式。

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

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

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

百分号（%）通配符

最常使用的通配符是百分号（%）。在搜索串中，%表示任何字符出现 任意次数。

SELECT 列名 1，列名 2 FROM 表名 WHERE 列名 2 LIKE '%值%';

%告诉 MySQL 接受值前后的任意字符，不管它有多少字符。

使用通配符的化，匹配的内容是严格区分大小写的。（按照 MySQL 的配置来说，搜索可以是区分大小写的）

通配符可在搜索模式中任意位置使用，并且可以使用多个通配符。

除了一个或多个字符外，%还能匹配 0 个字符。% 代表搜索模式中给定位置的 0 个、1 个或多个字符。

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

注意NULL
虽然似乎%通配符可以匹配任何东西，但有一个例外，即NULL。即使是WHERE prod_name LIKE '%'也不能匹配用值NULL作为产品名的行。

下划线（_）通配符

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

SELECT 列名 1，列名 2 FROM 表名 WHERE 列名 2 LIKE '_值'；

与%能匹配 0 个字符不一样，_总是匹配一个字符，不能多也不能少。

使用通配符的技巧

MySQL 的通配符很有用。但这种功能是有代价的：通配 符搜索的处理一般要比前面讨论的其他搜索所花时间更长。一些技巧：

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

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

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

正则表达式进行搜索

正则表达式是用来匹配文本 的特殊的串（字符集合）。

所有种类的程序设计语言、文本编辑器、操作系统等都支持正则表 达式。

正则表达式用正则表达式语言来建立，正则表达式语言是用来完成 刚讨论的所有工作以及更多工作的一种特殊语言。与任意语言一样，正则表达式具有你必须学习的特殊的语法和指令。

MySQL 正则表达式

仅为正则表达式语言的一个子集 如果你熟悉正则表达式，需
要注意：MySQL仅支持多数正则表达式实现的一个很小的子
集。

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

基本字符匹配

SELECT 列名 1 FROM 表名 WHERE 列名 1 REGEXP '值' ORDER BY 列名 1；

它告诉 MySQL：REGEXP 后所跟的东西作 为正则表达式（与文字正文的值匹配的一个正则表达式）处理。

. 是正则表达式语言中一个特殊的字符。它表示匹配任意一个字符

LIKE与REGEXP:
SELECT 列名 FROM 表名 WHERE 列名 LIKE '值' ORDER BY 表名;
SELECT 列名 FROM 表名 WHERE 列名 REGEXP '值' ORDER BY 列名;

如果执行上述两条语句，会发现第一条语句不返回数据，而第
二条语句返回一行。为什么？
LIKE匹配整个列。如果被匹配的文本在列值中出现，LIKE将不会找到它，相应的行也不被返回（除非使用通配符）。而REGEXP在列值内进行匹配，如果被匹配的文本在列值中出现，REGEXP将会找到它，相应的行将被返回。这是一个非常重要的差别。

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

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

进行 OR 匹配

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

SELECT 列名 FROM 表名 WHERE 列名 REGEXP '1000|2000' ORDER BY 列名

|为正则表达式的 OR 操作符。它表示匹配其中之一，因此值 1 和值 2 都匹配并返回。

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

两个以上的OR条件

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

匹配几个字符之一

匹配任何单一字符：可通过指定一组用[和]括起来的字符来完成想匹配特定的字符

SELECT 列名 FROM 表名 WHERE 列名 REGEXP '[123] TON'

正则表达式[123] Ton,正则表达式[123]Ton 为[1|2|3]Ton 的缩写。[123]定义一组字符，它的意思是匹配 1 或 2 或 3。[ ^123]却匹配除这些字符外的任何东西。

字符集合也可以被否定，即，它们将匹配除指定字符外的任何东西。 为否定一个字符集，在集合的开始处放置一个^即可。

匹配范围

集合可用来定义要匹配的一个或多个字符。

匹配数字 0 到 9：[0123456789] 或[0-9]

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

SELECT 列名 FROM 表名 列名 REGEXP '[1-5] Ton' ORDER BY 列名；

匹配特殊字符

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

SELECT 列名 FROM 表名 WHERE 列名 REGEXP '\.' ORDER BY 列名；

\.匹配. ，所以只检索出一行。这种处理就是所谓的转义（escaping），正则表达式内具有特殊意义的所 有字符都必须以这种方式转义。

| 元字符 | 说明 | | :-: | :--: | | \f | 换页 | | \n | 换行 | | \r | 回车 | | \t | 制表 | | \v | 纵向制表 |

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

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

匹配字符类

匹配多个实例

定位符

创建计算字段

计算字段

存储在数据库表中的数据一般不是应用程序所需要的格式。

 如果想在一个字段中既显示公司名，又显示公司的地址，但这两 个信息一般包含在不同的表列中。

 城市、州和邮政编码存储在不同的列中（应该这样），但邮件标签 打印程序却需要把它们作为一个恰当格式的字段检索出来。

 列数据是大小写混合的，但报表程序需要把所有数据按大写表示 出来。

 物品订单表存储物品的价格和数量，但不需要存储每个物品的总 价格（用价格乘以数量即可）。为打印发票，需要物品的总价格。  需要根据表数据进行总数、平均数计算或其他计算。

在上述每个例子中，存储在表中的数据都不是应用程序所需要的。 我们需要直接从数据库中检索出转换、计算或格式化过的数据；而不是检索出数据，然后再在客户机应用程序或报告程序中重新格式化。

计算字段并不实际存在于数据库表中。计算字段是运行时在 SELECT 语句内创建的。

字段：基本上与列的意思相同，经常互换使用，不过数据库列一般称为列，而术语字段通常用在计算字段的连接上。

重点：只有数据库知道 SELECT 语句中哪些列是实际的表列，哪些列是计算字段。从客户机（如应用程序）的角度来看，计算字段的数据是以其他列的数据相同的方式返回的。

客户机与服务器的格式：可在 SQL 语句内完成的许多转换和格式化工作都可以直接在客户机应用程序内完成。但一 般来说，在数据库服务器上完成这些操作比在客户机中完 成要快得多，因为 DBMS 是设计来快速有效地完成这种处 理的。

拼接字段

拼接：将值联结到一起构成单个值。

MySQL的不同之处
多数DBMS使用 + 或 ||来实现拼接,MySQL则使用Concat()函数来实现。
SQL转MySQL语句时的区别

vendors表包含供应商名和位置信息。假如要生成一个供应商报表，
需要在供应商的名字中按照name(location)这样的格式列出供应商的位置。
此报表需要单个值，而表中数据存储在两个列vend_name和vend_
country中。此外，需要用括号将vend_country括起来，这些东西都没有明确存储在数据库表中。


SELECT
  CONCAT(vend_name,'(',vend_country,')')
FROM vendors
ORDER BY vend_name;

Concat()拼接串，即把多个串连接起来形成一个较长的串。Concat()需要一个或多个指定的串，各个串之间用逗号分隔。
上面的SELECT语句连接以下4个元素：
1.存储在vend_name列中的名字;
2.包含一个空格和一个左园括号的串;
3.存储在vend_country列中的国家;
4.包含一个左圆括号的串。

RTrim()函数去掉值右边的所有空格。MySQL除了支持RTrim()（正如刚才所见，它去掉串右边的空格），还支持LTrim()（去掉串左边的空格）以及Trim()（去掉串左右两边的空格）。
SELECT
 CONCAT(RTRIM(vend_name),'(',RTRIM(vend_country),')')
FROM vendors
ORDER BY vend_name;

使用别名

SELECT 语句拼接地址字段会得到一个值，一个未命名的列不能用于客户机应用中，因此客户机没有办法引用它。为了解决这个问题，SQL 支持列别名。别名（alias)是一个字段或值的替换名。用 AS 关键字赋予。

SELECT
CONCAT(RTRIM(vend_name),'(',RTRIM(vend_country),')') AS vend_title
FROM vendors
ORDER BY vend_name;
指示SQL创建一个包含指定计算的名为vend_title的计算字段。结果与以前相同，但现在的列名为vend_title,任何客户机应用都可以按名引用这个列,就像它是一个实际的表列一样。

别名的其他用途：常见的用途包括在实际的表列名包含不符合规定的字符（如空格）时重新命名它，在原来的名字含混或容易误解时扩充它。

导出列：别名有时也被称为导出列。

执行算术计算

计算字段的另一常见用途是对检索出的数据进行算术计算。

SELECT prod_id,quantity,item_price
FROM orderitems
WHERE order_num = 20005;
检索order_num为20005的所有物品。


SELECT prod_id,quantity,item_price,quantity*item_price AS expanded_price
FROM orderitems
WHERE order_num = 20005;

expanded_price列为一个计算字段，此计算为quantity*item_price。客户机应用现在可以使用这个新计算
列，就像使用其他列一样。

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

| 操作符 | 说明 | | :-: | :-: | | ＋ | 加 | | - | 减 | | * | 乘 | | / | 除 |

如何测试计算

SELECT 提供了测试和试验函数与计算的一个很好的办法。省略 FROM 子句以便简单地访问和处理表达式。例如：

SELECT 3*2； --将返回 6 SELECT TRIM(' abc')；--将返回 abc SELECT NOW(); --利用 Now()函数返回当前日期和时间

使用数据处理函数

函数

SQL 支持利用函数来处理数据。函数一般是在数据上执行的，它给数据的转换和处理提供了方便。

函数没有SQL的可移植性强
能运行在多个系统上的代码称为可移植的（portable）。相对来说，多数SQL语句是可移植的，在SQL实现之间有差异时，这些差异通常不那么难处理。而函数的可移植性却不强。几乎每种主要的DBMS的实现都支持其他实现不支持的函数，而且有时差异还很大。
为了代码的可移植，许多SQL程序员不赞成使用特殊实现的功能。虽然这样做很有好处，但不总是利于应用程序的性能。如果不使用这些函数，编写某些应用程序代码会很艰难。必须利用其他方法来实现DBMS非常有效地完成的工作。
使用函数时应该做好代码注释，以便以后能够知道SQL的含义。

使用函数

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

• 用于处理文本串（如删除或填充值，转换值为大写或小写）的文本函数。

• 用于在数值数据上进行算术操作（如返回绝对值，进行代数运算）的数值函数。

• 用于处理日期和时间值并从这些值中提取特定成分（例如，返回两个日期之差，检查日期有效性等）的日期和时间函数。

• 返回 DBMS 正使用的特殊信息（如返回用户登录信息，检查版本细节）的系统函数。

文本处理函数

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

Upper()将文本转换为大写。

| 函数 | 说明 | | :---------: | :------------: | | Left() | 返回串左边的字符 | | Length() | 返回串的长度 | | Locate() | 找出串的子串 | | Lower() | 将串转换为小写 | | LTrim() | 去掉串左边的空格 | | RTrim() | 去掉串右边的空格 | | Right() | 返回串右边的字符 | | Soundex() | 返回串的 SOUNDEX 值 | | SubString() | 返回子串的字符 | | Upper() | 将串转换为大写 |

SOUNDEX：是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX 考虑了类似 的发音字符和音节，使得能对串进行发音比较而不是字母比较。虽然 SOUNDEX 不是 SQL 概念，但 MySQL（就像多数 DBMS 一样）都提供对 SOUNDEX 的支持。

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

WHERE子句使用Soundex()函数来转换cust_contact列值和搜索串为它们的SOUNDEX值。

日期和时间处理函数

日期和时间采用相应的数据类型和特殊的格式存储，以便能快速和 有效地排序或过滤，并且节省物理存储空间。

应用程序不使用用来存储日期和时间的格式，因此日期和时 间函数总是被用来读取、统计和处理这些值。由于这个原因，日期和时 间函数在 MySQL 语言中具有重要的作用。

首先需要注意的是 MySQL 使用的日期格式。无论你什么时候指定一个日期，不管是插入或更新值表还是用 WHERE 子句进行过滤，日期必须为 yyyy-mm-dd。排除了多义性。

常用的日期和时间处理函数：

应该总是使用4位数字的年份
MySQL支持2位数字的年份，但使用完整的4位数字年份更可靠，因为MySQL不必做出任何假定。

如果要的是日期，请使用Date()
    使用Date()是一个良好的习惯，即使你知道相应的列只包含日期也是如此。这样，如果由于某种原因表中以后有日期和时间值，你的SQL代码也不用改变。
    Date()和Time()都是MySQL4.1.1中第一次引入的。

数值处理函数

仅处理数值数据。

常用的数值处理函数：

汇总函数

聚集函数

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

• 确定表中行数（或者满足某个条件或包含某个特定值的行数）。

• 获得表中行组的和。

• 找出表列（或所有行或某些特定的行）的最大值、最小值和平均值。

聚集函数：运行在行组上，计算和返回单个值的函数。

| 函数 | 说明 | | :-----: | :-------: | | AVG() | 返回某个列的平均值 | | COUNT() | 返回某列的行数 | | MAX() | 返回某列的最大值 | | MIN() | 返回某列的最小值 | | SUM() | 返回某列值之和 |

标准偏差：MySQL 还支持一系列的偏差聚集函数。

AVG()函数

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

--返回products表中所有产品的平均价格
SELECT AVG(prod_price) AS avg_price
FROM products;

--返回特定供应商所提供产品的平均价格
SELECT AVG(prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;

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

COUNT 函数

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

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

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

--返沪customers表中客户的数量
--COUNT(*)对所有行计数,不管行中各列有什么值
SELECT COUNT(*) AS num_cust
FROM customers;
--返回具有电子邮件地址的客户计数
SELECT COUNT(cust_email) AS num_cust
FROM customers;

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

MAX 函数

MAX()返回指定列中的最大值。MAX()要求指定列名。

SELECT MAX(prod_price) AS max_price
FROM products;

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

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

MIN 函数

返回指定列的最小值。要求指定列名。

SELECT MIN(prod_price) AS max_price
FROM products;

和函数MAX()函数类似

SUM 函数

返回指定列值的和（总计）。

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

函数SUM(quantity)返回订单中所有物品数量之和，WHERE子
句保证只统计某个物品订单中的物品。

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

函数SUM(item_price*quantity)返回订单中所有物品价钱
之和，WHERE子句同样保证只统计某个物品订单中的物品。

--在多个列上进行计算:利用标准的算术操作符,所有聚集函数都可用来执行多个列上的计算
--NULL值 SUM()函数忽略列值为NULL的行

聚集不同值

DISTINCT 在 MySQL 5.0.3 中被添加。

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

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

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

ALL 为默认：ALL 参数不需要指定，因为它是默认行为。

SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;
--使用了DISTINCT参数，因此平均值只考虑各个不同的价格

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

组合聚集函数

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;

取别名：在指定别名以包含某个聚集函数的结果时，不应该使 用表中实际的列名。虽然这样做并非不合法，但使用唯一的名 字会使你的 SQL 更易于理解和使用（以及将来容易排除故障）。

分组数据

数据分组

SQL 聚集函数可以用来汇总数据。这使我们能够对行进 行计数，计算和与平均数，获得最大和最小值而不用检索所有数据。目前为止的所有计算都是在表的所有数据或匹配特定的 WHERE 子句的 数据上进行的。

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

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

创建分组

分组是在 SELECT 语句的 GROUP BY 子句中建立的。

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

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 子句之前。

过滤分组

WHERE 过滤指定的是行而不是分组。事实 上，WHERE 没有分组的概念。

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

有关 WHERE 的所有这些技术和选项都适用于 HAVING。它们的句法是相同的，只是关键字有差别。

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

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

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


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

分组和排序

GROUP BY 和 ORDER BY 经常完成相同的工作，但它们是非常不同 的。

| ORDER BY | GROUP BY | | :--------------------: | :--------------------------: | | 排序产生的输出 | 分组行。但输出可能不是分组的顺序 | | 任意列都可以使用（甚至非选择的列也可以使用） | 只可能使用选择列或表达式列，而且必须使用每个选择列表达式 | | 不一定需要 | 如果与聚集函数一起使用列（或表达式），则必须使用 |

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

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

SELECT 子句顺序

| 子句 | 说明 | 是否必须使用 | | :------: | :-------: | :---------: | | SELECT | 要返回的列或表达式 | 是 | | FROM | 从中检索数据的表 | 仅在从表选择数据时使用 | | WHERE | 行级过滤 | 否 | | GROUP BY | 分组说明 | 仅在按组计算聚集时使用 | | HAVING | 组级过滤 | 否 | | ORDER BY | 输出排序字段 | 否 | | LIMIT | 要检索的行数 | 否 |

使用子查询

子查询

SELECT 是 SQL 的查询（任何 SQL 语句都是查询）。此术语是指 SELECT 语句。

子查询：嵌套在其他查询中的查询。

利用子查询进行过滤

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

--列出订购物品TNT2的所有客户
SELECT
    order_num
FROM
    orderitems
WHERE
    prod_id = 'TNT2';
--查询订单20005和20007的客户ID
SELECT
    cust_id
FROM
    orders
WHERE
    order_num IN ( 20005, 20007 );

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

--合并上述sql，子查询
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' ) );

格式化 SQL：包含子查询的 SELECT 语句难以阅读和调试，特 别是它们较为复杂时更是如此。如上所示把子查询分解为多行 并且适当地进行缩进，能极大地简化子查询的使用。

对于能嵌套的子查询的数目没有限制，不过在实际使用时由于 性能的限制，不能嵌套太多的子查询。

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

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

作为计算字段使用子查询

使用子查询的另一方法是创建计算字段。

假如需要显示 customers 表中每个客户的订单总数。

为了执行这个操作，遵循下面的步骤。

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

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

连结表

联结

SQL 最强大的功能之一就是能在数据检索查询的执行中联结（join）表。

关系表

理解关系表的一个例子：

假如有一个包含产品目录的是数据库表，其中每种类别的物品占一行。对于每种物品要存储的信息包括产品描述和价格，以及生产该产品的供应商信息。

现在，假如有由同一供应商生产的多种物品，那么在何处存储供应 商信息（如，供应商名、地址、联系方法等）呢？将这些数据与产品信 息分开存储的理由如下。

• 因为同一供应商生产的每个产品的供应商信息都是相同的，对每 个产品重复此信息既浪费时间又浪费存储空间。

• 如果供应商信息改变（例如，供应商搬家或电话号码变动），只需 改动一次即可。

• 如果有重复数据（即每种产品都存储供应商信息），很难保证每次 输入该数据的方式都相同。不一致的数据在报表中很难利用。

关键是，相同数据出现多次决不是一件好事，此因素是关系数据库 设计的基础。关系表的设计就是要保证把信息分解成多个表，一类数据 一个表。各表通过某些常用的值（即关系设计中的关系（relational））互 相关联。

在这个例子中，可建立两个表，一个存储供应商信息，另一个存储 产品信息。vendors 表包含所有供应商信息，每个供应商占一行，每个供 应商具有唯一的标识。此标识称为主键（primary key）（在第 1 章中首次 提到），可以是供应商 ID 或任何其他唯一值。

products 表只存储产品信息，它除了存储供应商 ID（vendors 表的主 键）外不存储其他供应商信息。vendors 表的主键又叫作 products 的外键， 它将 vendors 表与 products 表关联，利用供应商 ID 能从 vendors 表中找出 相应供应商的详细信息。

外键：外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。

• 供应商信息不重复，从而不浪费时间和空间；

• 如果供应商信息变动，可以只更新 vendors 表中的单个记录，相 关表中的数据不用改动；

• 由于数据无重复，显然数据是一致的，这使得处理数据更简单。

关系数据可以有效地存储和方便地处理。因此，关系数据库的可伸缩性远比非关系数据库要好。

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

为什么要使用联结

分解数据为多个表能更有效地存储，更方便地处理，并 且具有更大的可伸缩性。

数据存储在多个表中，怎样用单条 SELECT 语句检索？——联结，联结是一种机制，用在一条 SELECT 语句中关联表。使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行。

维护引用完整性：联结不是物理实体，就是说在数据库表中不存在。联结由 MySQL 根据需要建立，它存在于查询的执行当中。

创建联结

SELECT vend_name,prod_name,prod_price
FROM vendors,products
--这里需要完全限定列名
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name,prod_name;

它们就是这条SELECT语句联结的两个表的名字。这两个表用WHERE子句正确联结，WHERE子句指示MySQL匹配vendors表中的vend_id和products表中的vend_id。

完全限定列名：在引用的列可能出现二义性时，必须使用完全限定列名（用一个点分隔的表名和列名）。如果引用一个没有有用表名限制的具有二义性的列名，MySQL 将返回错误。

WHERE 子句的重要性

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

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

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

不要忘了WHERE子句:应该保证所有联结都有WHERE子句，否
则MySQL将返回比想要的数据多得多的数据。同理，应该保
证WHERE子句的正确性。不正确的过滤条件将导致MySQL返回
不正确的数据。
叉联结:有时我们会听到返回称为叉联结（cross join）的笛卡
儿积的联结类型。

内部联结

目前为止所用的联结称为等值联结，基于两个表之间的相等测试。这种联结也称为内部联结。

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

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

联结多个表

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;

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

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'
    )
);
--子查询并不总是执行复杂SELECT操作的最有效的方法
SELECT
    cust_name,
    cust_contact
FROM
    customers,
    orders,
    orderitems
WHERE
    customers.cust_id = orders.cust_id
    AND orderitems.order_num = orders.order_num
    AND prod_id = 'TNT2';

创建高级联结

使用表别名

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

别名除了用于列名和计算字段外，SQL还允许给表名起别名。这样做有两个主要理由，第一缩短SQL语句，第二允许在单条SELECT语句中多次使用相同的表。


别名不仅能用于WHERE子句，它还可以用于SELECT的列表、ORDER BY子句以及语句的其他部分。
表别名只在查询执行中使用。与列别名不一样，表别名不返回到客户机。

使用不同类型的联结

自联结

--子查询
SELECT prod_id,prod_name
FROM products
WHERE vend_id = (SELECT vend_id FROM products WHERE prod_id = 'DTNTR');

它使用了子查询。内部的SELECT语句做了一个简单的检索，返回生产ID为DTNTR的物品供应商的vend_id。该ID用于外部查询的WHERE子句中，以便检索出这个供应商生产的所有物品
--自联结
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';

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

自然联结

外部联结

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

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

使用带聚集函数的联结

使用联结和联结条件

注意所使用的联结类型。一般我们使用内部联结，但使用外部联 结也是有效的。

 保证使用正确的联结条件，否则将返回不正确的数据。

 应该总是提供联结条件，否则会得出笛卡儿积。

 在一个联结中可以包含多个表，甚至对于每个联结可以采用不同 的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前，分别测试每个联结。这将使故障排除更为简单。

组合查询

多数 SQL 查询都只包含从一个或多个表中返回数据的单条 SELECT 语 句。MySQL 也允许执行多个查询（多条 SELECT 语句），并将结果作为单个 查询结果集返回。这些组合查询通常称为并（union）或复合查询 （compound query）。

有两种基本情况，其中需要使用组合查询：

 在单个查询中从不同的表返回类似结构的数据；

 对单个表执行多个查询，按单个查询返回数据。

组合查询和多个WHERE条件:多数情况下，组合相同表的两个
查询完成的工作与具有多个WHERE子句条件的单条查询完成的
工作相同。换句话说，任何具有多个WHERE子句的SELECT语句
都可以作为一个组合查询给出。

创建组合查询

可用 UNION 操作符来组合数条 SQ 查询，利用 UNION，可给出多条 SELECT 语句，将它们的结果组合成单个结果集。

使用 UNION

给出每条 SELECT 语句，在各条语 句之间放上关键字 UNION。

--检索价格不高于5的所有物品
SELECT vend_id,prod_id,prod_price
FROM products
WHERE prod_price <=5
UNION
--找出供应商1001和1002生产的所有物品
SELECT vend_id,prod_id,prod_price
FROM products
WHERE vend_id IN(1001,1002);

俩条结合起来的意思:需要价格小于等于5的所有物品的一个列表，而且还想包括供应商1001和1002生产的所有物品（不考虑价格）

--多条where子句
SELECT vend_id,prod_id,prod_price
FROM products
WHERE prod_price <=5
OR vend_id IN(1001,1002);

在这个简单的例子中，使用UNION可能比使用WHERE子句更为复杂。
但对于更复杂的过滤条件，或者从多个表（而不是单个表）中检索数据
的情形，使用UNION可能会使处理更简单。

UNION 规则

UNION 必须由两条或两条以上的 SELECT 语句组成，语句之间用关 键字 UNION 分隔（因此，如果组合 4 条 SELECT 语句，将要使用 3 个 UNION 关键字）。

 UNION 中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）。

 列数据类型必须兼容：类型不必完全相同，但必须是 DBMS 可以 隐含地转换的类型（例如，不同的数值类型或不同的日期类型）。

包含或取消重复的行

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 (1000,1002);

UNION与WHERE:UNION几乎总是完成与多个WHERE条件相同的工作。UNION ALL为UNION的一种形式，它完成WHERE子句完成不了的工作。如果确实需要每个条件的匹配行全部出现（包括重复行），则必须使用UNION ALL而不是WHERE。

对组合查询结果排序

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;

组合不同的表：UNION 的组合查询可以应用不同的表

全文本搜索

并非所有引擎都支持全文本搜索：MySQL 支持几种基本的数据库引擎，并非所有的引擎都支持本书所描述的全文本搜索。MyISAM 支持全文本搜索和 InnoDB 不支持。

LIKE 关键字和正则表达式这些搜索机制非常有用但是存在几个重要的限制：

• 性能——通配符和正则表达式匹配通常要求 MySQL 尝试匹配表中所有行（而且这些搜索极少使用索引）。因此，由于被搜索行数不断增加，这些搜索可能非常耗时。

• 明确控制——使用通配符和正则表达式匹配，很难（而且并不总是能）明确地控制匹配什么和不匹配什么。例如，指定一个词必 须匹配，一个词必须不匹配，而一个词仅在第一个词确实匹配的 情况下才可以匹配或者才可以不匹配。

• 智能化的结果——虽然基于通配符和正则表达式的搜索提供了非 常灵活的搜索，但它们都不能提供一种智能化的选择结果的方法。 例如，一个特殊词的搜索将会返回包含该词的所有行，而不区分 包含单个匹配的行和包含多个匹配的行（按照可能是更好的匹配 来排列它们）。类似，一个特殊词的搜索将不会找出不包含该词但 包含其他相关词的行。

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

使用全文本搜索

为了进行全文本搜索，必须索引被搜索的列，而且要随着数据的改 变不断地重新索引。在对表列进行适当设计后，MySQL 会自动进行所有 的索引和重新索引。

启用全文吧搜索支持

插入数据

数据插入

INSERT 是用来插入（或添加）行到数据库表的。插入可以用几种方式使用：

• 插入完整的行

• 插入行的一部分

• 插入多行

• 插入某些查询的结果

插入及系统安全：可针对每个表或每个用户，利用 MySQL 的安全机制禁止使用 INSERT 语句

插入完整的行

要求指定表名和被插入到新行中的值。

INSERT INTO customers
VALUES
    ( NULL, 'Pep E.LaPew', '100 Main Stree', 'Los Angeles', 'CA', '90046', 'USA', NULL, NULL );
对每个列必须提供一个值，如果每个列没有值，应该使用NULL值。（假设表允许对该列指定空值），各个列必须以它们在表定义中出现的次序填充。
自动增量（MySQL）的工作,但又不能省略此列。所以指定一个NULL值。

上述语法很简单，但并不安全，应该尽量避免使用。上面的SQL语句高度依赖于表中列的定义次序,并且还依赖于其次序容易获得的信息，即使得到这种次序信息，也不能保证下一次表结构变动后各个列保持完全相同的次序。
省略列需要满足的条件:
1.该列定义为允许null值
2.在表定义中给出默认值。这表示如果不给出值，将使用默
认值。
安全方法(可以省略列)：
INSERT INTO customers ( cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email )
VALUES
    ( 'Pep F.LaPew', '100 Main Stree', 'Los Angeles', 'CA', '90046', 'USA', NULL, NULL );
采用列值对应的方法,必须以其指定的次序匹配指定的列名，不一定按各个列出现的实际表中的次序。其优点是，即使表的结构改变，该INSERT语句仍然可以正确工作。

总是使用列的列表:一般不要使用没有明确给出列的列表的
INSERT语句。使用列的列表能使SQL代码继续发挥作用，即使
表结构发生了变化。

仔细地给出值:不管使用哪种INSERT语法，都必须给出VALUES的正确数目。如果不提供列名，则必须给每个表列提供一个值。如果提供列名，则必须对每个列出的列给出一个值。如果不这样，将产生一条错误消息，相应的行插入不成功。

提高整体性能:数据库经常被多个客户访问，对处理什么请求以及用什么次序处理进行管理是MySQL的任务。INSERT操作可能很耗时（特别是有很多索引需要更新时），而且它可能降低等待处理的SELECT语句的性能。

如果数据检索是最重要的（通常是这样），则你可以通过在
INSERT和INTO之间添加关键字LOW_PRIORITY，指示MySQL
降低INSERT语句的优先级

没有输出：INSERT 语句一般不会产生输出。

插入多个行

可以使用多条 INSERT 语句，每条语句用一个分号结束。列名（和次序）相同，可以用逗号隔开每组，没组的值用一对圆括号括起来。

单条 INSERT 语句处理多个插入比使用多条 INSERT 语句快

插入检索的数据

INSERT 一般用来给表插入一个指定列值的行。但是，INSERT 还存在另一种形式，可以利用它将一条 SELECT 语句的结果插入表中。

如果你想从另一张表中合并客户列表到你的 customers 表，不需要每次读取一行，然后再将它用 INSERT 插入，可以如下进行：

首先创建一个 custnew 表，在填充 custnew 时，不应该使用已经在 customers 中使用过的 cust_id 值，主键重复后续的 INSERT 操作将会失败。

INSERT SELECT中的列名:为简单起见，这个例子在INSERT和
SELECT语句中使用了相同的列名。但是，不一定要求列名匹配。
事实上，MySQL甚至不关心SELECT返回的列名。它使用的是
列的位置，因此SELECT中的第一列（不管其列名）将用来填充
表列中指定的第一个列，第二列将用来填充表列中指定的第二
个列，如此等等。这对于从使用不同列名的表中导入数据是非
常有用的。

INSERT SELECT中SELECT语句可包含WHERE子句以过滤插入的数据。

更新和删除数据

更新数据

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

• 更新表中特定行；

• 更新表中所有行

不要省略 WHERE 子句，在使用 UPDATE 时一定要注意细心，因为稍不注意就会更新表中所有行。

UPDATE 语句由 3 部分组成：

• 要更新的表

• 列名和它们的新值

• 确定要更新行的过滤条件

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

UPDATE语句以WHERE子句结束，它告诉MySQL更新哪一行。没有WHERE子句，MySQL将会用这个电子邮件地址更新所有邮箱。

更新多个列在set后面多加一个，每个“列 = 值”对之间用逗号分隔（最后一列之后不用逗号）

UPDATE语句中使用子查询:UPDATE语句中可以使用子查询，使得能用SELECT语句检索出的数据更新列数据。

IGNORE:如果用UPDATE语句更新多行，并且在更新这些行中的一行或多行时出现一个错误，则整个UPDATE操作被取消（错误发生前更新的所有行被恢复到它们原来的值）。IGNORE关键字可以使得发生错误也继续更新。

删除数据

从一个表中删除（去掉）数据，使用 DELETE 语句。

• 从表中删除特定的行；

• 从表中删除所有的行。

不要省略 WHERE 子句：在使用 DELETE 时一定要注意细心，因为稍不注意，就会错误地删除表中所有行。

DELETE FROM customers WHERE cust_id = 10006;
DELETE不需要使用列名或通配符。DELETE删除整行而不是删除列。
删除指定的列请使用UPDATE语句。

DELETE删除的是表中的行，可以删除表中所有行。但是，DELETE不删除表本身。
TRUNCATE TABLE速度比DELETE速度更快（实际上它是删除原来的表并重新创建一个表，而不是逐行删除表中的数据。）

更新和删除的指导原则

如果省略 WHERE 子句，执行 UPDATE 则表中每个行都将用新值更新。执行 DELETE 语句则表中的数据都将被删除。

原则/习惯：

• 除非确实打算更新和删除每一行，否则绝对不要使用不带 WHERE 子句的 UPDATE 或 DELETE 语句。

• 保证每个表都有主键，尽可能 像 WHERE 子句那样使用它（可以指定各主键、多个值或值的范围）。

• 在对 UPDATE 或 DELETE 语句使用 WHERE 子句前，应该先用 SELECT 进 行测试，保证它过滤的是正确的记录，以防编写的 WHERE 子句不 正确。

• 使用强制实施引用完整性的数据库，这样 MySQL 将不允许删除具有与其他表相关联的数据的行。

MySQL 没有撤销（undo）按钮！！！！

创建和操纵表

创建表

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

• 使用具有交互式创建和管理表的工具

• 表也可以直接用 MySQL 语句操纵。

表创建基础

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

• 新表的名字，在关键字 CREATE TABLE 之后给出；

• 表列的名字和定义，用逗号分隔。

处理现有的表：在创建新表时，指定的表名必须不存在，否则将出错。如果要防止意外覆盖已有的表，SQL 要求首先手工删除该表。然后再重建它，而不是简单地用创建表语句覆盖它。如果你仅想在一个表不存在时创建它，应该在表名后给出 IF NOT EXISTS。

使用 NULL 值

NULL 值就是没有值或缺值。允许 NULL 值的列也允许在 插入行时不给出该列的值。

每个表列或者是 NULL 列，或者是 NOT NULL 列，这种状态在创建时由 表的定义规定。

理解NULL:不要把NULL值与空串相混淆。NULL值是没有值，它不是空串。如果指定''（两个单引号，其间没有字符），这在NOT NULL列中是允许的。空串是一个有效的值，它不是无值。NULL值用关键字NULL而不是空串指定。

主键再介绍

主键值必须唯一。即，表中的每个行必须具有唯一的主 键值。如果主键使用单个列，则它的值必须唯一。如果使用多个列，则 这些列的组合值必须唯一。

主键可以在创建表时定义，或者在创建表之后定义。

主键和NULL值:主键为其值唯一标识表中每个行的列。主键中只能使用不允许NULL值的列。允许NULL值的列不能作为唯一标识。

使用 AUTO_INCREMENT

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

每个表只允许一个 AUTO_INCREMENT 列，而且它必须被索引（如，通 过使它成为主键）。

覆盖AUTO_INCREMENT:如果一个列被指定为AUTO_INCREMENT，则它需要使用特殊的值吗？你可以简单地在INSERT语句中指定一个值，只要它是唯一的（至今尚未使用过）即可，该值将被用来替代自动生成的值。后续的增量将开始使用该手工插入的值。

last_insert_id()函数可以确定AUTO_INCREMNENT列的值
SELECT last_insert_id()此语句返回最后一个AUTO_INCREMENT值

指定默认值

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

CREATE TABLE orderitems(
    order_num int NOT NULL,
    order_item char(10) NOT NULL,
    quantity int NOT NULL DEFAULT 1, --在未
给出数量的情况下使用数量1。
)ENGINE = InnoDB;

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

使用默认值而不是 NULL 值：许多数据库开发人员使用默认值而不是 NULL 列，特别是对用于计算或数据分组的列更是如此。

引擎类型

InnoDB 是一个可靠的事务处理引擎，它不支持全文本搜索；

MEMORY 在功能等同于 MyISAM,但由于数据存储在内存（不是磁盘）中，速度很快（特别适合于临时表）

MyISAM 是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。

引擎类型可以混用。外键不能跨引擎，混用引擎类型有一个大缺陷。即使用一 个引擎的表不能引用具有使用不同引擎的表的外键。

更新表

为更新表定义，可使用 ALTER TABLE 语句。但是，理想状态下，当表 中存储数据以后，该表就不应该再被更新。在表的设计过程中需要花费 大量时间来考虑，以便后期不对该表进行大的改动。

改变表结构必须给出以下信息：

• 在 ALTER TABLE 之后给出要更改的表名（该表必须存在，否则将出错）；

• 所做要更改的列表

ALTER TABLE vendors ADD vend_phone CHAR(20);--必须明确数据类型
ALTER TABLE vendors DROP COLUMN vend_phone;

ALTER TABLE的一种常见用途是定义外键。

ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY(order num) REFERENCES orders(order_num)

复杂的表结构更改一般需要手动删除过程，它涉及以下步骤：

• 用新的列布局创建一个新表；

• 使用 INSERT SELECT 语句从旧表复制数据到新表。如果有必要，可使用转换函数和计算字段；

• 检验包含所需数据的新表；

• 重命名旧表（如果确定，可以删除它）；

• 根据需要，重新创建触发器、存储过程、索引和外键。

小心使用ALTER:TABLE 使用ALTER TABLE要极为小心，应该在进行改动前做一个完整的备份（模式和数据的备份）。数据库表的更改不能撤销，如果增加了不需要的列，可能不能删除它们。类似地，如果删除了不应该删除的列，可能会丢失该列中的所有数据。

删除表

删除表（删除整个表而不是其内容）非常简单，使用 DROP TABLE 语句即可：DROP TABLE customers2;执行这条语句将永久删除该表。

重命名表

使用 RENAME TABLE 语句可以重命名一个表

RENAME TABLE customers2 TO customers;

可以同时对多个表重命名中间用逗号进行连接。

使用视图

视图

视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态 检索数据的查询。

SELECT
    cust_name,
    cust_contact
FROM
    customers,
    orders,
    orderitems
WHERE
    customers.cust_id = orders.cust_id
    AND orderitems.order_num = orders.order_num
    AND prod_id = 'TNT2';

然后把这个查询包装成一个虚拟表，则可以轻松地检索出相同的数据。

为什么使用视图

• 重用 SQL 语句。

• 简化复杂的 SQL 操作。在编写查询后，可以方便地重用它而不必知道它的基本查询细节。

• 使用表的组成部分而不是整个表。

• 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。

• 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

视图创建之后，可以用与表基本相同的方式利用它们。可以对视图执行 SELECT 操作，过滤和排序数据，将视图联结到其他视图或表，甚至能添加和更新数据。

视图仅仅是用来查看存储在别处的数据的一种设施。视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的。在添加或更改这些表中的数据时，视图将返回改变过的数据。

性能问题：因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时所需的任一个检索。如果你用多个联结和过滤创建了复杂的视图或者嵌套了视图，可能会发现性能 下降得很厉害。

视图的规则和限制

• 与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的名字）。

• 对于可以创建的视图数目没有限制。

• 为了创建视图，必须具有足够的访问权限。这些限制通常由数据库管理人员授予。

• 视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图。

• ORDER BY 可以用在视图中，但如果从该视图检索数据 SELECT 中也含有 ORDER BY，那么该视图中的 ORDER BY 将被覆盖。

• 视图不能索引，也不能有关联的触发器或默认值。

• 视图可以和表一起使用。

使用视图

• 用 CREATE VIEW 语句来创建。

• 使用 SHOW CREATE VIEW viewname；来查看创建视图的语句。

• 用 DROP 删除视图，其语法为 DROP VIEW viewname;

• 更新视图时，可以先用 DROP 再用 CREATE，也可也直接用 CREATE OR REPLACE VIEW。如果要更新的视图不存在，则第 2 条更新语句会创建一个视图；如果要更新的视图存在，则第 2 条更新语句会替换原有视图。

利用视图简化负责的联结

视图最常见的应用之一是隐藏复杂的 SQL，这通常都会涉及联结。

CREATE VIEW productcustomers AS SELECT
cust_name,
cust_contact,
prod_id
FROM
    customers,
    orders,
    orderitems
WHERE
    customers.cust_id = orders.cust_id
    AND orderitems.order_num = orders.order_num;

SELECT
    cust_name,
    cust_contact
FROM
    productcustomers
WHERE
    prod_id = 'TNT2';

思考：视图不是一个临时表么，为啥还可以用它的表名分开可以查到

创建可重用的视图：创建不受特定数据限制的视图是一种 好办法。

用视图重新格式化检索出的数据

视图的另一常见用途是重新格式化检索出的数据。下面的 SELECT 语句在单个组合计算列中返回供应商名和位置：

--单个组合计算列中返回供应商名和位置
SELECT
    Concat( RTrim( vend_name ), '(', RTrim( vend_country ), ')' ) AS vend_title
FROM
    vendors
ORDER BY
    vend_name;

--假如经常需要这个格式的结果。不必在每次需要时执行联结，创建一个视图，每次需要时使用它即可。

CREATE VIEW vendorlocation AS
SELECT
    Concat( RTrim( vend_name ), '(', RTrim( vend_country ), ')' ) AS vend_title
FROM
    vendors
ORDER BY
    vend_name;

--检索出以创建所有邮件标签的数据，可如下进行:
SELECT * FROM vendorlocations;

用视图过滤不想要的数据

视图对于应用普通的 WHERE 子句也很有用。例如，可以定义 customeremaillist 视图，它过滤没有电子邮件地址的客户。

CREATE VIEW customeremaillist AS SELECT
cust_id,
cust_name,
cust_email
FROM
    customers
WHERE
    cust_email IS NOT NULL;

SELECT * FROM customeremaillist;


SELECT * FROM customeremaillist WHERE cust_id = 10001;

WHERE子句与WHERE子句 如果从视图检索数据时使用了一条
WHERE子句，则两组子句（一组在视图中，另一组是传递给视
图的）将自动组合。

使用视图与计算字段

--检索某个特定订单中的物品，计算每种物品的总价格
SELECT
    prod_id,
    quantity,
    item_price,
    quantity * item_price AS expanded_price
FROM
    orderitems
WHERE
    order_num = 20005;

CREATE VIEW orderitemsexpanded AS
SELECT
    order_num,
    prod_id,
    quantity,
    item_price,
    quantity * item_price AS expanded_price
FROM
    orderitems

SELECT * FROM orderitemsexpanded WHERE order_num = 20005;

更新视图

视图的数据能否更新，答案视情况而定。
通常，视图是可更新的（即，可以进行INSERT、UPDATE和DELETE）。更新一个视图将更新其基表。如果你对视图增加或删除行，实际上是对其基表增加或删除行。不能更新的情况是指如果MySQL不能正确地确定被更新地基数据，则不允许更新（包括插入和删除）。有以下操作，则不能进行视图的更新:
1.分组（GROUP BY 和HAVING）
2.联结
3.子查询
4.并
5.聚集函数（Min()、Count()、Sum()等）;
6.DISTINCT
7.导出（计算）列。

视图的主要作用是进行数据检索。

使用存储过程

存储过程

使用的大多数 SQL 语句都是针对一个或多个表的单条语 句。并非所有操作都这么简单，经常会有一个完整的操作需要多条语句 才能完成。例如，考虑以下的情形。

 为了处理订单，需要核对以保证库存中有相应的物品。

 如果库存有物品，这些物品需要预定以便不将它们再卖给别的人， 并且要减少可用的物品数量以反映正确的库存量。

 库存中没有的物品需要订购，这需要与供应商进行某种交互。

 关于哪些物品入库（并且可以立即发货）和哪些物品退订，需要 通知相应的客户。

执行这个处理需要针对许多表的多条 MySQL 语句。此外，需要执行的具体语句及其次序也不是固定的，它 们可能会（和将）根据哪些物品在库存中哪些不在而变化。

可以创建存储过程。存储过程简单来说，就是为以后的使用而保存 的一条或多条 MySQL 语句的集合。可将其视为批文件，虽然它们的作用 不仅限于批处理。

为什么要使用存储过程

 通过把处理封装在容易使用的单元中，简化复杂的操作（正如前 面例子所述）。

 由于不要求反复建立一系列处理步骤，这保证了数据的完整性。 如果所有开发人员和应用程序都使用同一（试验和测试）存储过 程，则所使用的代码都是相同的。 这一点的延伸就是防止错误。需要执行的步骤越多，出错的可能 性就越大。防止错误保证了数据的一致性。

 简化对变动的管理。如果表名、列名或业务逻辑（或别的内容） 有变化，只需要更改存储过程的代码。使用它的人员甚至不需要 知道这些变化。 这一点的延伸就是安全性。通过存储过程限制对基础数据的访问减 少了数据讹误（无意识的或别的原因所导致的数据讹误）的机会。

 提高性能。因为使用存储过程比使用单独的 SQL 语句要快。

 存在一些只能用在单个请求中的 MySQL 元素和特性，存储过程可 以使用它们来编写功能更强更灵活的代码（在下一章的例子中可 以看到。） 换句话说，使用存储过程有 3 个主要的好处，即简单、安全、高性能。 显然，它们都很重要。不过，在将 SQL 代码转换为存储过程前，也必须知 道它的一些缺陷。

 一般来说，存储过程的编写比基本 SQL 语句复杂，编写存储过程 需要更高的技能，更丰富的经验。

 你可能没有创建存储过程的安全访问权限。许多数据库管理员限 制存储过程的创建权限，允许用户使用存储过程，但不允许他们 创建存储过程。

MySQL 将编写存储过程的安全和访问与执行存储过程的安全和访问区分开来。

使用存储过程

执行存储过程

MySQL 称存储过程的执行为调用，因此 MySQL 执行存储过程的语句 为 CALL。CALL 接受存储过程的名字以及需要传递给它的任意参数。

CALL productpricing(@pricelow,@pricehigh,@priceaverage);
productpricing的存储过程，它计算并返回产品的最低、最高和平均价格。
存储过程可以显示结果，也可以不显示结果。

创建存储过程

CREATE PROCEDURE productpricing()
BEGIN
    SELECT Avg(prod_price) AS priceaverage
    FROM products;
END;
使用上面的存储过程
CALL productpricing();
存储过程名为productpricing，如果存储过程接受参数，它们将在()中列举出来。此存储过程没有参数，但后跟的()仍然需要。BEGIN和END语句用来限定存储过程体，过程体本身仅是一个简单的SELECT语句。
在MySQL处理这段代码时，它创建一个新的存储过程productpricing。没有返回数据，因为这段代码并未调用存储过程，这里只是为以后使用而创建它。

删除存储过程

存储过程在创建之后，被保存在服务器上以供使用，直至被删除。

DROP PROCEDURE productpricing;这条语句删除刚创建的存储过程。请注意没有使用后面的()， 只给出存储过程名。

仅当存在时删除 如果指定的过程不存在，则 DROP PROCEDURE 将产生一个错误。当过程存在想删除它时（如果过程不存在也 不产生错误）可使用 DROP PROCEDURE IF EXISTS。

使用参数

存储过程并不显示结果，而是把结果返回给你指定的变量。

变量：内存中一个特定的位置，用来临时存储数据。

变量名：所有 MySQL 变量都必须以@开始。

建立智能存储过程

迄今为止使用的所有存储过程基本上都是封装 MySQL 简单的 SELECT 语句。

COMMENT 关键字:本例子中的存储过程在 CREATE PROCEDURE 语 句中包含了一个 COMMENT 值。它不是必需的，但如果给出，将 在 SHOW PROCEDURE STATUS 的结果中显示。

检查存储过程

SHOW CREATE PROCEDURE 语句

限制过程状态结果 SHOW PROCEDURE STATUS 列出所有存储过 程。为限制其输出，可使用 LIKE 指定一个过滤模式，例如：

SHOW PROCEDURE STATUS LIKE 'ordertotal';

使用游标

游标

MySQL 检索操作返回一组称为结果集的行。这组返 回的行都是与 SQL 语句相匹配的行（零行或多行）。

游标（cursor）是一个存储在 MySQL 服务器上的数据库查询， 它不是一条 SELECT 语句，而是被该语句检索出来的结果集。

存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

只能用于存储过程,不像多数 DBMS，MySQL 游标只能用于 存储过程（和函数）。

使用游标

 在能够使用游标前，必须声明（定义）它。这个过程实际上没有 检索数据，它只是定义要使用的 SELECT 语句。

 一旦声明后，必须打开游标以供使用。这个过程用前面定义的 SELECT 语句把数据实际检索出来。

 对于填有数据的游标，根据需要取出（检索）各行。

 在结束游标使用时，必须关闭游标。 在声明游标后，可根据需要频繁地打开和关闭游标。在游标打开后， 可根据需要频繁地执行取操作。

创建游标

DECLARE 命名游标，并定义 相应的 SELECT 语句，根据需要带 WHERE 和其他子句。可以参考存储过程。

CREATE PROCEDURE processorders()
BEGIN
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
END;

打开和关闭游标

OPEN ordernumbers;在处理 OPEN 语句时执行查询，存储检索出的数据以供浏览和滚 动。

CLOSE ordernumbers;CLOSE 释放游标使用的所有内部内存和资源，因此在每个游标 不再需要时都应该关闭。

在一个游标关闭后，如果没有重新打开，则不能使用它。但是，使 用声明过的游标不需要再次声明，用 OPEN 语句打开它就可以了。

隐含关闭:如果你不明确关闭游标，MySQL 将会在到达 END 语 句时自动关闭它。

使用游标数据

DECLARE 语句的次序 DECLARE 语句的发布存在特定的次序。 用 DECLARE 语句定义的局部变量必须在定义任意游标或句柄 之前定义，而句柄必须在游标之后定义。不遵守此顺序将产生错误消息。

使用触发器

触发器

MySQL 语句在需要时被执行，存储过程也是如此。但是，如果你 想要某条语句（或某些语句）在事件发生时自动执行，怎么办呢？例 如：

 每当增加一个顾客到某个数据库表时，都检查其电话号码格式是 否正确，州的缩写是否为大写；

 每当订购一个产品时，都从库存数量中减去订购的数量；

 无论何时删除一行，都在某个存档表中保留一个副本。

触发器是 MySQL 响应以下任意语句而 自动执行的一条 MySQL 语句（或位于 BEGIN 和 END 语句之间的一组语 句）：

 DELETE；

 INSERT；

 UPDATE。

创建触发器

在创建触发器时，需要给出 4 条信息：

 唯一的触发器名；

 触发器关联的表；

 触发器应该响应的活动（DELETE、INSERT 或 UPDATE）；

 触发器何时执行（处理之前或之后）。

保持每个数据库的触发器名唯一 在 MySQL 5 中，触发器名必 须在每个表中唯一，但不是在每个数据库中唯一。这表示同一 数据库中的两个表可具有相同名字的触发器。这在其他每个数 据库触发器名必须唯一的 DBMS 中是不允许的。

触发器创建是用 CREATE TRIGGER 语句创建的。

CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added';

CREATE TRIGGER用来创建名为newproduct的新触发器。触发器
可在一个操作发生之前或之后执行，这里给出了AFTER INSERT，
所以此触发器将在INSERT语句成功执行后执行。这个触发器还指定FOREACH ROW，因此代码对每个插入行执行。

仅支持表只有表才支持触发器，视图不支持（临时表也不 支持）。

触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器。因此，每个表最多支持 6 个触发器（每条 INSERT、UPDATE 和 DELETE 的之前和之后）。

单一触发器不能与多个事件或多个表关联，所 以，如果你需要一个对 INSERT 和 UPDATE 操作执行的触发器，则应该定义 两个触发器。

触发器失败:如果 BEFORE 触发器失败，则 MySQL 将不执行请 求的操作。此外，如果 BEFORE 触发器或语句本身失败，MySQL 将不执行 AFTER 触发器（如果有的话）。

删除触发器

DROP TRIGGER newproduct;

触发器不能更新或覆盖。为了修改一个触发器，必须先删除它，然后重新创建。

使用触发器

INSERT 触发器

INSERT 触发器在 INSERT 语句执行之前或之后执行。需要知道以下几 点：

 在 INSERT 触发器代码内，可引用一个名为 NEW 的虚拟表，访问被 插入的行；

 在 BEFORE INSERT 触发器中，NEW 中的值也可以被更新（允许更改 被插入的值）；

 对于 AUTO_INCREMENT 列，NEW 在 INSERT 执行之前包含 0，在 INSERT 执行之后包含新的自动生成值。

AUTO_INCREMENT 列具有 MySQL 自动赋予的值。

CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num;

此代码创建一个名为neworder的触发器，它按照AFTER INSERT
ON orders执行。在插入一个新订单到orders表时，MySQL生
成一个新订单号并保存到order_num中。触发器从NEW. order_num取得这个值并返回它。此触发器必须按照AFTER INSERT执行，因为在BEFORE INSERT语句执行之前，新order_num还没有生成。对于orders的每次插入使用这个触发器将总是返回新的订单号。

BEFORE或AFTER：通常，将BEFORE用于数据验证和净化（目
的是保证插入表中的数据确实是需要的数据）。

DELETE 触发器

DELETE 触发器在 DELETE 语句执行之前或之后执行。需要知道以下两 点：

 在 DELETE 触发器代码内，你可以引用一个名为 OLD 的虚拟表，访 问被删除的行；

 OLD 中的值全都是只读的，不能更新。

CREATE TRIGGER deleteorder BEFORE DELETE ON orders FOR EACH ROW
BEGIN
    INSERT INTO archive_orders(order_num,order_date,cust_id)VALUES(OLD.order_num,OLD.order_date,OLD.cust_id);
END;

在任意订单被删除前将执行此触发器。它使用一条INSERT语句
将OLD中的值（要被删除的订单）保存到一个名为archive_
orders的存档表中（为实际使用这个例子，你需要用与orders相同的列创建一个名为archive_orders的表）。

使用BEFORE DELETE触发器的优点（相对于AFTER DELETE触发器
来说）为，如果由于某种原因，订单不能存档，DELETE本身将被放弃。

多语句触发器:正如所见，触发器deleteorder使用BEGIN和
END语句标记触发器体。这在此例子中并不是必需的，不过也
没有害处。使用BEGIN END块的好处是触发器能容纳多条SQL
语句（在BEGIN END块中一条挨着一条）。

UPDATE 触发器

UPDATE 触发器在 UPDATE 语句执行之前或之后执行。需要知道以下几 点：

 在 UPDATE 触发器代码中，你可以引用一个名为 OLD 的虚拟表访问 以前（UPDATE 语句前）的值，引用一个名为 NEW 的虚拟表访问新 更新的值；

 在 BEFORE UPDATE 触发器中，NEW 中的值可能也被更新（允许更改 将要用于 UPDATE 语句中的值）；

 OLD 中的值全都是只读的，不能更新。

CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);

何数据净化都需要在UPDATE语句之前进行，就像这个例子中一样。每次更新一个行时，NEW.vend_state中的值（将用来更新表行的值）都用Upper(NEW.vend_state)替换

关于触发器的进一步介绍

 与其他 DBMS 相比，MySQL 5 中支持的触发器相当初级。未来的 MySQL 版本中有一些改进和增强触发器支持的计划。

 创建触发器可能需要特殊的安全访问权限，但是，触发器的执行 是自动的。如果 INSERT、UPDATE 或 DELETE 语句能够执行，则相关 的触发器也能执行。

 应该用触发器来保证数据的一致性（大小写、格式等）。在触发器 中执行这种类型的处理的优点是它总是进行这种处理，而且是透 明地进行，与客户机应用无关。

 触发器的一种非常有意义的使用是创建审计跟踪。使用触发器， 把更改（如果需要，甚至还有之前和之后的状态）记录到另一个 表非常容易。

 遗憾的是，MySQL 触发器中不支持 CALL 语句。这表示不能从触发 器内调用存储过程。所需的存储过程代码需要复制到触发器内。

管理事务处理

事务处理

事务处理（transaction processing）可以用来维护数据库的完整性，它保证成批的 MySQL 操作要么完全执行，要么完全不执行。

给系统添加订单的过程如下。

(1) 检查数据库中是否存在相应的客户（从 customers 表查询），如果 不存在，添加他/她。

(2) 检索客户的 ID。

(3) 添加一行到 orders 表，把它与客户 ID 关联。

(4) 检索 orders 表中赋予的新订单 ID。

(5) 对于订购的每个物品在 orderitems 表中添加一行，通过检索 出来的 ID 把它与 orders 表关联（以及通过产品 ID 与 products 表关联）。

假如由于某种数据库故障（如超出磁盘空间、安全限制、表 锁等）阻止了这个过程的完成。数据库中的数据会出现什么情况？

1.如果故障发生在添加了客户之后，orders 表添加之前，不会有什么 问题。某些客户没有订单是完全合法的。在重新执行此过程时，所插入 的客户记录将被检索和使用。可以有效地从出故障的地方开始执行此过 程。

2.但是，如果故障发生在 orders 行添加之后，orderitems 行添加之前， 怎么办呢？现在，数据库中有一个空订单。

3.更糟的是，如果系统在添加 orderitems 行之中出现故障。结果是数 据库中存在不完整的订单，而且你还不知道。

事务处理是一种机制，用来管理必须成批执行的MySQL操作，以保证数据库不包含不完整的操作结果。利用事务处理，可以保证一组操作不会中途停止，它们或者作为整体执行，或者完全不执行（除非明确指示）。如果没有错误发生，整组语句提交给（写到）数据库表。如果发生错误，则进行回退（撤销）以恢复数据库到某个已知且安全的状态。

请看相同的例子，这次我们说明过程如何工作。

(1) 检查数据库中是否存在相应的客户，如果不存在，添加他/她。 (2) 提交客户信息。

(3) 检索客户的 ID。

(4) 添加一行到 orders 表。

(5) 如果在添加行到 orders 表时出现故障，回退。

(6) 检索 orders 表中赋予的新订单 ID。

(7) 对于订购的每项物品，添加新行到 orderitems 表。

(8) 如果在添加新行到 orderitems 时出现故障，回退所有添加的 orderitems 行和 orders 行。

(9) 提交订单信息。

在使用事务和事务处理时，有几个关键词汇反复出现。下面是关于 事务处理需要知道的几个术语：

 事务（transaction）指一组 SQL 语句；

 回退（rollback）指撤销指定 SQL 语句的过程；

 提交（commit）指将未存储的 SQL 语句结果写入数据库表；

 保留点（savepoint）指事务处理中设置的临时占位符（placeholder），你可以对它发布回退（与回退整个事务处理不同）。

控制事务处理

使用 ROLLBACK

MySQL 的 ROLLBACK 命令用来回退（撤销）MySQL 语句

SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;

这个例子从显示ordertotals表（此表在第24章中填充）的内
容开始。首先执行一条SELECT以显示该表不为空。然后开始一
个事务处理，用一条DELETE语句删除ordertotals中的所有行。另一条SELECT语句验证ordertotals确实为空。这时用一条ROLLBACK语句回退START TRANSACTION之后的所有语句，最后一条SELECT语句显示该表不为空。

ROLLBACK 只能在一个事务处理内使用（在执行一条 START TRANSACTION 命令之后）。

哪些语句可以回退：事务处理用来管理 INSERT、UPDATE 和 DELETE 语句。你不能回退 SELECT 语句。（这样做也没有什么意 义。）你不能回退 CREATE 或 DROP 操作。事务处理块中可以使用 这两条语句，但如果你执行回退，它们不会被撤销。

使用 COMMIT

一般的 MySQL 语句都是直接针对数据库表执行和编写的。这就是 所谓的隐含提交（implicit commit），即提交（写或保存）操作是自动 进行的。在事务处理块中，提交不会隐含地进行。为了进行明确的提交，使用 COMMIT 语句：

START TRANSACTION;
DELETE FROM orderitems WHERE oder_num = 2000;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;


隐含事务关闭 当COMMIT或ROLLBACK语句执行后，事务会自
动关闭（将来的更改会隐含提交）。

使用保留点

简单的 ROLLBACK 和 COMMIT 语句就可以写入或撤销整个事务处理。只是对简单的事务处理才能这样做，更复杂的事务处理可能需要部分提交或回退。

为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符。这样需要回退，可以回退到某个占位符。这些占位符称为保留点。

SAVEPOINT deletel；创建占位符，每个保留点都取标识它的名字，以便在回退时，MySQL 知道要回退到何处。ROLLBACK TO deletel;

保留点越多越好：可以在 MySQL 代码中设置任意多的保留点，越多越好。因为保留点越多，你就越能按自己的意愿灵活进行回退。

释放保留点：保留点在事务处理完成（执行一条 ROLLBACK 或 COMMIT 后自动释放）。mysql5 以来，可以用 RELEASE SAVEPOINT 明确释放保留点。

更改默认的提交行为

正如所述，默认的 MySQL 行为是自动提交所有更改。为指示 MySQL 不自动提交更改，需要使用以下语句：SET autocommit = 0;

autocommit 标志决定是否自动提交更改，不管有没有 COMMIT 语句。设置 autocommit 为 0（假）指示 MySQL 不自动提交更改（直到 autocommit 被设置为真为止）。

set autocommit = 0;

标志为连接专用：autocommit 标志是针对每个连接而不是服务器的。

全球化和本地化

字符集和校对顺序

数据库表被用来存储和检索数据。不同的语言和字符集需要以不同 的方式存储和检索。因此，MySQL 需要适应不同的字符集（不同的字母 和字符），适应不同的排序和检索数据的方法。

 字符集为字母和符号的集合；

 编码为某个字符集成员的内部表示；

 校对为规定字符如何比较的指令。

使用字符集和校对顺序

MySQL 支持众多的字符集。为查看所支持的字符集完整列表。

SHOW CHARACTER SET；这条语句显示所有可用的字符集以及每个字符集的描述和默认 校对。

SHOW COLLATION；显示所有可用的校对，以及它们适用的字符集。

通常系统管理在安装时定义一个默认的字符集和校对。此外，也可 以在创建数据库时，指定默认的字符集和校对。为了确定所用的字符集和校对，可以使用以下语句：

SHOW VARIABLES LIKE 'character%'；

SHOW VARIABLES LIKE 'collation%'；

为了给表指定字符集和校对，可以使用带子句的 CREATE TABLE

CREATE TABLE mytable(
    columnn1 INT,
    column2 VARCHAR(10)
)DEFAULT CHARACTER SET hebrew
COLLATE hebrew_general_ci;

这个例子中指定了CHARACTER SET和COLLATE两者。一般，MySQL如
下确定使用什么样的字符集和校对。
 如果指定CHARACTER SET和COLLATE两者，则使用这些值。
 如果只指定CHARACTER SET，则使用此字符集及其默认的校对（如
SHOW CHARACTER SET的结果中所示）。
 如果既不指定CHARACTER SET，也不指定COLLATE，则使用数据库默认。

CREATE TABLE mytable(
    columnn1 INT,
    column2 VARCHAR(10),
    column3 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci
)DEFAULT CHARACTER SET hebrew
COLLATE hebrew_general_ci;
这里对整个表以及一个特定的列指定了CHARACTER SET和
COLLATE。

校对在对用 ORDER BY 子句检索出来的数据排序时起重要 的作用。如果你需要用与创建表时不同的校对顺序排序特定的 SELECT 语 句，可以在 SELECT 语句自身中进行：

SELECT * FROM customers
ORDER BY lastname,firstname COLLATE latin1_general_cs;
SELECT使用COLLATE指定一个备用的校对顺序（在这个例子中，为区分大小写的校对）。这显然将会影响到结果排序的次序。
临时区分大小写:上面的SELECT语句演示了在通常不区分大
小写的表上进行区分大小写搜索的一种技术。当然，反过来
也是可以的。
SELECT的其他COLLATE子句:除了这里看到的在ORDER BY子
句中使用以外，COLLATE还可以用于GROUP BY、HAVING、聚集
函数、别名等。

如果绝对需要，串可以在字符集之间进行转换。为此，使用Cast()或Convert()函数。

安全管理

访问控制

MySQL 服务器的安全基础是：用户应该对他们需要的数据具有适当 的访问权，既不能多也不能少。换句话说，用户不能对过多的数据具有 过多的访问权。

考虑以下内容：

• 多数用户只需要对表进行读和写，但少数用户甚至需要能创建和删除表；

• 某些用户需要读表，但可能不需要更新表；

• 你可能想允许用户添加数据，但不允许他们删除数据；

• 某些用户（管理员）可能需要处理用户账户的权限，但多数用户不需要；

• 你可能想让用户通过存储过程访问数据，但不允许他们直接访问数据；

• 你可能想根据用户登录的地点限制对某些功能的访问

不要使用 root :应该严肃对待 root 登录的使用。仅在绝对需 要时使用它（或许在你不能登录其他管理账号时使用）。不应 该在日常的 MySQL 操作中使用 root。

管理用户

MySQL 用户账号和信息存储在名为 mysql 的 MySQL 数据库中。一般 不需要直接访问 mysql 数据库和表（你稍后会明白这一点），但有时需要 直接访问。

use mysql;
SELECT user from user;

创建用户账号

CREATE USER 语句

CREATE USER ben IDENTIFIED BY 'p@\$\$w0rd';

CREATE USER 创建一个新用户账号。在创建用户账号时不一定需 要口令，不过这个例子用 IDENTIFIED BY 'p@\$\$wOrd'给出了 一个口令。

指定散列口令:IDENTIFIED BY 指定的口令为纯文本，MySQL 将在保存到 user 表之前对其进行加密。为了作为散列值指定口 令，使用 IDENTIFIED BY PASSWORD。

使用 GRANT 或 INSERT GRANT 语句（稍后介绍）也可以创建用 户账号，但一般来说 CREATE USER 是最清楚和最简单的句子。 此外，也可以通过直接插入行到 user 表来增加用户，不过为安 全起见，一般不建议这样做。MySQL 用来存储用户账号信息 的表（以及表模式等）极为重要，对它们的任何毁坏都可能严重地伤害到 MySQL 服务器。因此，相对于直接处理来 说，最好是用标记和函数来处理这些表。

重新命名一个用户账户，使用 RENAME USER 语句：

RENAME USER ben TO bforta;

MySQL 5 之前 仅 MySQL 5 或之后的版本支持 RENAME USER。 为了在以前的 MySQL 中重命名一个用户，可使用 UPDATE 直接 更新 user 表。

删除用户账号

删除一个用户账号（以及相关权限），使用 DROP USER 语句：

DROP USER bforta;(5 以后直接删除用户账户和所有相关的账户权限)

设置访问权限

在创建用户账号后，必须接着分配访问权限。新创建的用户账号没有访 问权限。它们能登录 MySQL，但不能看到数据，不能执行任何数据库操作。 为看到赋予用户账号的权限，使用 SHOW GRANTS FOR。USAGE 表 示根本没有权限

为设置权限，使用 GRANT 语句。GRANT 要求你至少给出以下信息：

 要授予的权限；

 被授予访问权限的数据库或表；

 用户名。

GRANT 和 REVOKE 可在几个层次上控制访问权限：

 整个服务器，使用 GRANT ALL 和 REVOKE ALL；

 整个数据库，使用 ON database.*；

 特定的表，使用 ON database.table；

 特定的列；

 特定的存储过程。

更改口令

SET PASSWORD 语句。新口令必须如下加密：

SET PASSWORD FOR bforta =  Password('n3w p@$$w0rd')
新口令必须传递到Password()函数进行加密。

数据库维护

备份数据

MySQL 数据库是基于磁盘的文件，普通的备份系统和例程就能备份 MySQL 的数据。但是，由于这些文件总是处于打开和使用状态，普通的文件副本备份不一定总是有效。下面有一些解决方案：

• 使用命令行实用程序 mysqldump 转储所有数据库内容到某个外部 文件。在进行常规备份前这个实用程序应该正常运行，以便能正 确地备份转储文件。

• 可用命令行实用程序 mysqlhotcopy 从一个数据库复制所有数据 （并非所有数据库引擎都支持这个实用程序）。

• 可以使用 MySQL 的 BACKUP TABLE 或 SELECT INTO OUTFILE 转储所 有数据到某个外部文件。这两条语句都接受将要创建的系统文件 名，此系统文件必须不存在，否则会出错。数据可以用 RESTORE TABLE 来复原。

首先刷新未写数据：为了保证所有数据被写到磁盘（包括索引 数据），可能需要在进行备份前使用 FLUSH TABLES 语句。

进行数据库维护

MySQL 提供了一系列的语句，可以（应该）用来保证数据库正确和正常运行。

• ANALYZE TABLE，用来检查表键是否正确。

• CHECH TABLE 用来针对许多问题对表进行检查。在 MyISAM 表上还对索引进行检查。

  • CHANGED 检查自最后一次检查以来改动过的表。EXTENDED 执行最彻底的检查。

    FAST 只检查未正常关闭的表

    MEDIUM 检查所有被删除的链接并进行键检验

    QUICK 只进行快速扫描。

• 如果 MyISAM 表访问产生不正确和不一致的结果，可能需要用到 REPAIR TABLE 来修复相应的表。这条语句不应该经常使用，如果需要经常使用，可能会有更大的问题要解决。

• 如果从一个表中删除大量数据，应该使用 OPTIMIZE TABLE 来收回所用空间，从而优化表的性能。

诊断启动问题

mysqld 命令行选项：

• --help 显示帮助——一个选项列表；

• --safe-mode 装载减去某些最佳配置的服务器；

• --verbose 显示全文本消息（为获得更详细的帮助消息与--help 联合使用）；

• --version 显示版本信息然后退出

查看日志文件

• 错误日志。它包含启动和关闭问题以及任意关键错误的细节。日志通常名为 hostname.err,位于 data 目录中。此日志名可用--log-error 命令行选项更改。

• 查询日志。它记录所有 MySQL 活动，在诊断问题时非常有用。此日志文件可能会很快地变得非常大，因此不应该长期使用它。此日志通常名为 hostname.log，位于 data 目录中。此名字可以用--log 命令行选项更改。

• 二进制日志。它记录更新过数据（或者可能更新过数据）的所有语句。此日志通常名为 hostname-bin，位于 data 目录内。此名字可以用--log-bin 命令行选项进行更改。5 以后添加的，以前 MySQL 版本中使用的是更新日志。

• 缓慢查询日志。顾名思义，此日志记录执行缓慢的任何查询。这 个日志在确定数据库何处需要优化很有用。此日志通常名为 hostname-slow.log ，位于 data 目录中。此名字可以用 --log-slow-queries 命令行选项更改。

改善性能

改善性能

• 首先，MySQL（与所有 DBMS 一样）具有特定的硬件建议。在学习和研究 MySQL 时，使用任何旧的计算机作为服务器都可以。但对用于生产的服务器来说，应该坚持遵循这些硬件建议。

• 一般来说，关键的生产 DBMS 应该运行在自己的专用服务器上。

• MySQL 是用一系列的默认设置预先配置的，从这些设置开始通常是很好的。但过一段时间后你可能需要调整内存分配、缓冲区大小等。（为查看当前设置，可使用 SHOW VARIABLES;和 SHOW STATUS;。）

• MySQL 一个多用户多线程的 DBMS，换言之，它经常同时执行多 个任务。如果这些任务中的某一个执行缓慢，则所有请求都会执 行缓慢。如果你遇到显著的性能不良，可使用 SHOW PROCESSLIST 显示所有活动进程（以及它们的线程 ID 和执行时间）。你还可以用 KILL 命令终结某个特定的进程（使用这个命令需要作为管理员登 录）。

• 总是有不止一种方法编写同一条 SELECT 语句。应该试验联结、并、 子查询等，找出最佳的方法。

• 使用 EXPLAIN 语句让 MySQL 解释它将如何执行一条 SELECT 语句。

• 一般来说，存储过程执行得比一条一条地执行其中的各条 MySQL 语句快。

• 应该总是使用正确的数据类型。

• 决不要检索比需求还要多的数据。换言之，不要用 SELECT *（除非你真正需要每个列）。

• 有的操作（包括 INSERT）支持一个可选的 DELAYED 关键字，如果 使用它，将把控制立即返回给调用程序，并且一旦有可能就实际 执行该操作。

• 在导入数据时，应该关闭自动提交。你可能还想删除索引（包括 FULLTEXT 索引），然后在导入完成后再重建它们。

• 必须索引数据库表以改善数据检索的性能。确定索引什么不是一 件微不足道的任务，需要分析使用的 SELECT 语句以找出重复的 WHERE 和 ORDER BY 子句。如果一个简单的 WHERE 子句返回结果所花 的时间太长，则可以断定其中使用的列（或几个列）就是需要索 引的对象。

• 你的 SELECT 语句中有一系列复杂的 OR 条件吗？通过使用多条 SELECT 语句和连接它们的 UNION 语句，你能看到极大的性能改 进。

• 索引改善数据检索的性能，但损害数据插入、删除和更新的性能。 如果你有一些表，它们收集数据且不经常被搜索，则在有必要之 前不要索引它们。（索引可根据需要添加和删除。）

• LIKE 很慢。一般来说，最好是使用 FULLTEXT 而不是 LIKE。

• 数据库是不断变化的实体。一组优化良好的表一会儿后可能就面目全非了。由于表的使用和内容的更改，理想的优化和配置也会改变。

• 最重要的规则就是，每条规则在某些条件下都会被打破。