《MySQL必知必会》笔记

第1章 了解SQL

在使用多列作为主键时，上述条件必须应用到构成主键的所有列，所有列值的组合必须是唯一的（但单个列的值可以不唯一）

SQL（发音为字母S-Q-L或sequel）是结构化查询语言（Structured Query Language）

第2章 MySQL简介

数据的所有存储、检索、管理和处理实际上是由数据库软件——DBMS（数据库管理系统-DataBase Management System）完成的

第3章 使用MySQL

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

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

SHOW COLUMNS FROM customers;，查看表结构：字段名、数据类型、是否允许NULL、键信息、默认值以及其他信息（如字段cust_id的auto_increment）

DESCRIBE customers；，是SHOW COLUMNS FROM customers；的一种快捷方式

第4章 检索数据

SQL是不区分大小写的，许多SQL开发人员喜欢对所有SQL关键字使用大写，而对所有列和表名使用小写，这样做使代码更易于阅读和调试。
SQL语句可以在一行上给出，也可以分成许多行。多数SQL开发人员认为将SQL语句分成多行更容易阅读和调试。

DISTINCT关键字，检索出有不同值的列表

SELECT DISTINCT vend_id FROM products;,只返回不同值的vend_id行

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

SELECT prod_name FROM products LIMIT 5;，LIMIT 5指示MySQL返回不多于5行。

SELECT prod_name FROM products LIMIT 5,5;，LIMIT 5, 5指示MySQL返回从行5开始的5行。第一个数为开始位置，第二个数为要检索的行数。行0检索出来的第一行为行0，而不是行1。因此，LIMIT 1, 1将检索出第二行而不是第一行。

LIMIT 4 OFFSET 3意为从行3开始取4行，就像LIMIT 3, 4一样。

第5章 排序检索数据

关系数据库设计理论认为，如果不明确规定排序顺序，则不应该假定检索出的数据的顺序有意义。
ORDER BY子句取一个或多个列的名字，据此对输出进行排序:

SELECT prod_name FROM products ORDER BY prod_name;，对prod_name以字母顺序排序。

为了按多个列排序，只要指定列名，列名之间用逗号分开即可（就像选择多个列时所做的那样）。

# 首先按价格，然后再按名称排序
# 以下是一条命令，分号算作结束，分行便于阅读
SELECT prod_id, prod_price, prod_name FROM products
ORDER BY prod_price, prod_name;

默认升序，为了进行降序排序，必须指定DESC关键字

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

DESC关键字只应用到直接位于其前面的列名，下面的例子以降序排序产品（最贵的在最前面），然后再对产品名排序（默认升序）：

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

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

在字典（dictionary）排序顺序中，A被视为与a相同，这是MySQL（和大多数数据库管理系统）的默认行为。

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

SELECT prod_price
FROM products
ORDER BY prod_price DESC
LIMIT 1;

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

第6章 过滤数据

WHERE子句在表名（FROM子句）之后给出

SELECT prod_name, prod_price
FROM products
WHERE prod_price = 2.5;

条件操作符（WHERE子句操作符）

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

SELECT prod_name, prod_price
FROM products
WHERE prod_name = 'fuses';

MySQL在执行匹配时默认不区分大小写，所以fuses与Fuses匹配。
如果将值与串类型的列进行比较，则需要限定引号。用来与数值列进行比较的值不用引号。

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

BETWEEN匹配范围中所有的值，包括指定的开始值和结束值。

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

SELECT prod_name
FROM products
WHERE prod_price IS NULL;

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

第7章 数据过滤

操作符（operator）：用来联结或改变WHERE子句中的子句的关键字。也称为逻辑操作符（logical operator）。AND操作符，OR操作符，IN操作符，NOT操作符。

SELECT prod_id, prod_price, prod_name
FROM products
WHERE vend_id = 1003 AND prod_price <=10;

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

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

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

SQL（像多数语言一样）在处理OR操作符前，优先处理AND操作符，AND在计算次序中优先级更高。任何时候使用具有AND和OR操作符的WHERE子句，都应该使用圆括号明确地分组操作符。

IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配。IN操作符完成与OR相同的功能。

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

此SELECT语句检索供应商1002和1003制造的所有产品，与下面的SQL语句效果相同。

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

IN操作符优点具体如下：

1. 在使用长的合法选项清单时，IN操作符的语法更清楚且更直观。
2. 在使用IN时，计算的次序更容易管理（因为使用的操作符更少）。
3. IN操作符一般比OR操作符清单执行更快。
4. IN的最大优点是可以包含其他SELECT语句，使得能够更动态地建立WHERE子句。

SELECT prod_name, prod_price
FROM products
WHERE vend_id NOT IN (1002,1003)

此SELECT语句列出除1002和1003之外的所有供应商制造的产品

MySQL支持使用NOT对IN、BETWEEN和EXISTS子句取反

第8章 用通配符进行过滤

操作符何时不是操作符？答案是在它作为谓词（predi-cate）时。

%表示任何字符出现任意次数。

# 找出所有以词jet起头的产品
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE 'jet%';

根据MySQL的配置方式，搜索可以是区分大小写的。

# 可以使用多个通配符
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE `%anvil%`

%代表搜索模式中给定位置的0个、1个或多个字符。

尾空格可能会干扰通配符匹配,解决这个问题的一个简单的办法是在搜索模式最后附加一个%。一个更好的办法是使用函数（第11章将会介绍）去掉首尾空格。

即使是WHERE prod_name LIKE '%'也不能匹配用值NULL作为产品名的行。

_下划线只匹配单个字符而不是多个字符

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

通配符技巧：

1. 不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
2. 在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的。
3. 仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。

第9章 用正则表达式进行搜索

检索列prod_name包含文本1000的所有行：

SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000'
ORDER BY prod_name

SELECT prod_name
FROM products
WHERE prod_name REGEXP '.000'
ORDER BY prod_name;

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

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

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

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

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

[123]定义一组字符，它的意思是匹配1或2或3。

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

下面的集合将匹配数字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

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

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

转义。这包括．、|、[]以及迄今为止使用过的其他特殊字符。\\也用来引用元字符（具有特殊含义的字符）

空白元字符：

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

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

为更方便工作，可以使用预定义的字符集，称为字符类。

类	说明
[:alnum:]	任意字母和数字（同[a-zA-Z0-9]）
[:alpha:]	任意字符（同[a-zA-Z]）
[:blank:]	空格和制表（同[\t]）
[:cntrl:]	ASCII 0到31和127
[:digit:]	任意数字（同[0-9]）
[:graph:]	与[:print:]相同，但不包括空格
[:lower:]	任意小写字母（同[a-z]）
[:print:]	任意可打印字符
[:punct:]	既不在[:alnum:]又不在[:cntrl:]中的任意字符
[:space:]	包括空格在内的任意空白字符（同[\f\n\r\t\v]）
[:upper:]	任意大写字母（同[a-Z]）
[:xdigit:]	任意十六进制数字（同[a-fA-F0-9]）

正则表达式重复元字符:

元字符	说明
*	0个或多个匹配
+	1个或多个匹配（等于{1，}）
?	0个或1个匹配（等于{0,1}）
{n}	指定数目的匹配
{n,}	不少于指定数目的匹配
{n,m}	匹配数目的范围(m不超过255)

SELECT prod_name
FROM products
WHERE prod_name REGEXP '\\([0-9] sticks?\\)'
ORDER BY prod_name

匹配到TNT (1 stick)，TNT (5 sticks)。
正则表达式\\([0-9] sticks? \\)需要解说一下。\\(匹配(, [0-9]匹配任意数字（这个例子中为1和5）, sticks？匹配stick和sticks（s后的?使s可选，因为?匹配它前面的任何字符的0次或1次出现）, \\)匹配)。

SELECT prod_name
FROM products
WHERE prod_name REGEXP `[[:DIGIT:]]{4}`
ORDER BY prod_name;

[:digit:]匹配任意数字，因而它为数字的一个集合。{4}确切地要求它前面的字符（任意数字）出现4次，所以[[:digit:]]{4}匹配连在一起的任意4位数字。

为了匹配特定位置的文本，需要使用定位符：

元字符	说明
^	文本的开始
$	文本的结尾
[[:<:]]	词的开始
[[:>:]]	词的结尾

查询以一个数（包括以小数点开始的数）开始的所有产品，需要使用^定位符：

SELECT prod_name
FROM products
WHERE prod_name REGEXP '^[0-9\\.]'
ORDER BY prod_name;

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

LIKE和REGEXP的不同在于，LIKE匹配整个串而REGEXP匹配子串。利用定位符，通过用^开始每个表达式，用$结束每个表达式，可以使REGEXP的作用与LIKE一样。

第10章 创建计算字段

计算字段并不实际存在于数据库表中。计算字段是运行时在SELECT语句内创建的。从客户机（如应用程序）的角度来看，计算字段的数据是以与其他列的数据相同的方式返回的。

Concat()拼接串，即把多个串连接起来形成一个较长的串。多数DBMS使用+或||来实现拼接，MySQL则使用Concat()函数来实现。

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

上面的SELECT语句连接以下4个元素：

• 存储在vend_name列中的名字；
• 包含一个空格和一个左圆括号的串；
• 存储在vend_country列中的国家；
• 包含一个右圆括号的串。

输出为ACME (USA)。

MySQL的RTrim()函数可以删除数据右侧多余的空格。
MySQL除了支持RTrim()，还支持LTrim()（去掉串左边的空格）以及Trim()（去掉串左右两边的空格）。

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

别名用AS关键字赋予

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

现在的列名为vend_title，任何客户机应用都可以按名引用这个列，就像它是一个实际的表列一样。

如下汇总物品的价格:

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

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

基本算术操作符:

操作符	说明
+	加
-	减
*	乘
/	除

第11章 使用数据处理函数

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

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

Upper()函数：将文本转换为大写

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

常用的文本处理函数：

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

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

Soundex()函数进行搜索，它匹配所有发音类似于Y.Lie的联系名。
WHERE子句使用Soundex()函数来转换cust_contact列值和搜索串为它们的SOUNDEX值。因为Y.Lee和Y.Lie发音相似，所以它们的SOUNDEX值匹配，因此WHERE子句正确地过滤出了所需的数据。

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

函数	说明
AddDate()	增加一个日期（天，周等）
AddTime()	增加一个时间（时，分等）
CurDate()	返回当前日期
CurTime()	返回当前时间
Date()	返回日期时间的日期部分
DateDiff()	计算两个日期之差
Date_Add()	高度灵活的日期运算函数
Date_Format()	返回一个格式化的日期或时间串
Day()	返回一个日期的天数部分
DayOfWeek()	对于一个日期，返回对于的星期几
Hour()	返回一个时间的小时部分
Minute()	返回一个时间的分钟部分
Month()	返回一个日期的月份部分
Now()	返回当前日期和时间
Second()	返回一个时间的秒部分
Time()	返回一个日期时间的时间部分
Year()	返回一个日期的年份部分

MySQL使用的日期格式必须为yyyy-mm-dd。

Date(order_date)指示MySQL仅提取列的日期部分。

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

检索出2005年9月下的所有订单：

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

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

Month()从日期中返回月份。因此，WHERE Year(order_date)= 2005 AND Month(order_date) = 9检索出order_date为2005年9月的所有行

常用的数值处理函数：

函数	说明
Abs()	返回一个数的绝对值
Cos()	返回一个角度的余弦
Exp()	返回一个数的指数值
Mod()	返回除操作的余数
Pi()	返回圆周率
Rand()	返回一个随机数
Sin()	返回一个角度的正弦
Sqrt()	返回一个数的平方根
Tan()	返回一个角度的正切

第12章 汇总数据

用于汇总检索数据，例子有：

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

聚集函数：

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

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

使用AVG()返回products表中所有产品的平均价格：

SELECT AVG(prod_price) AS avg_price
FROM products

AVG()也可以用来确定特定列或行的平均值。

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

此WHERE子句仅过滤出vend_id为1003的产品，因此avg_price中返回的值只是该供应商的产品的平均值。

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

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

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

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

SELECT COUNT(*) AS num_cust
FROM customers;

上面返回customers表中客户的总数，在此例子中，利用COUNT(*)对所有行计数，不管行中各列有什么值。计数值在num_cust中返回。

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

SELECT COUNT(cust_email) AS num_cust
FROM customers;

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

MAX()返回指定列中的最大值。MAX()要求指定列名。在用于文本数据时，如果数据按相应的列排序，则MAX()返回最后一行。MAX()函数忽略列值为NULL的行。

SELECT MAX(prod_price) AS max_price
FROM products;

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

MIN(),它返回指定列的最小值。MIN()要求指定列名。

SELECT MIN(pro_price) AS min_price
FROM products;

MIN()返回products表中最便宜物品的价格。

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

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

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

下面的例子中，合计每项物品的item_price*quantity，得出总的订单金额。
函数SUM(item_price*quantity)返回订单中所有物品价钱之和，WHERE子句同样保证只统计某个物品订单中的物品。

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

下面使用了DISTINCT参数，因此平均值只考虑各个不同的价格。

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

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;

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

在指定别名以包含某个聚集函数的结果时，不应该使用表中实际的列名。

第13章 分组数据

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一次。

GROUP BY子句指示MySQL分组数据，然后对每个组而不是整个结果集进行聚集。

重要的规定：

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

使用WITH ROLLUP关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组）的值，如下所示：

# 不太明白，需要再具体查查
SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id WITH ROLLUP

WHERE过滤行，而HAVING过滤分组，HAVING支持所有WHERE操作符。
WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。WHERE排除的行不包括在分组中。这可能会改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组。

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

HAVING过滤COUNT(*) >=2（两个以上的订单）的那些分组。

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

上面列出具有2个（含）以上、价格为10（含）以上的产品的供应商。
WHERE子句过滤所有prod_price至少为10的行。然后按vend_id分组数据，HAVING子句过滤计数为2或2以上的分组

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	要检索的行数	否

第14章 使用子查询

把第一个查询（返回订单号的那一个）变为子查询组合两个查询

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

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

为了执行上述SELECT语句，MySQL实际上必须执行3条SELECT语句。最里边的子查询返回订单号列表，此列表用于其外面的子查询的WHERE子句。外面的子查询返回客户ID列表，此客户ID列表用于最外层查询的WHERE子句。最外层查询确实返回所需的数据。

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

orders是一个计算字段，它是由圆括号中的子查询建立的。该子查询对检索出的每个客户执行一次。
子查询中使用了完全限定列名。

下面的语句告诉SQL比较orders表中的cust_id与当前正从customers表中检索的cust_id：

WHERE orders.cust_id = customers.cust_id

第15章 联结表

外键为某个表中的一列，它包含另一个表的主键值

联结是一种机制，用来在一条SELECT语句中关联表，因此称之为联结。

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

这两个表用WHERE子句正确联结，WHERE子句指示MySQL匹配vendors表中的vend_id和products表中的vend_id。
在联结两个表时，你实际上做的是将第一个表中的每一行与第二个表中的每一行配对。

应该保证所有联结都有WHERE子句，否则MySQL将返回比想要的数据多得多的数据。

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

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

此语句中的SELECT与前面的SELECT语句相同，但FROM子句不同。这里，两个表之间的关系是FROM子句的组成部分，以INNER JOIN指定。在使用这种语法时，联结条件用特定的ON子句而不是WHERE子句给出。传递给ON的实际条件与传递给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;

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

SELECT cust_name, cust_contact
FROM customer
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 = order.order_num
      AND prod_id = 'TNT2';

第16章 创建高级联结

SELECT 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';

FROM子句中3个表全都具有别名。customers AS c建立c作为customers的别名，等等。这使得能使用省写的c而不是全名customers。在此例子中，表别名只用于WHERE子句。但是，表别名不仅能用于WHERE子句，它还可以用于SELECT的列表、ORDER BY子句以及语句的其他部分。

表别名只在查询执行中使用。与列别名不一样，表别名不返回到客户机。

下面首先找到生产ID为DTNTR的物品的供应商，然后找出这个供应商生产的其他物品。它使用了子查询，内部的SELECT语句做了一个简单的检索，返回生产ID为DTNTR的物品供应商的vend_id。该ID用于外部查询的WHERE子句中，以便检索出这个供应商生产的所有物品：

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

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

WHERE（通过匹配p1中的vend_id和p2中的vend_id）首先联结两个表，然后按第二个表中的prod_id过滤数据，返回所需的数据。

自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。虽然最终的结果是相同的，但有时候处理联结远比处理子查询快得多。

无论何时对表进行联结，应该至少有一个列出现在不止一个表中（被联结的列）。
标准的联结（前一章中介绍的内部联结）返回所有数据，甚至相同的列多次出现。自然联结排除多次出现，使每个列只返回一次。

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

许多联结将一个表中的行与另一个表中的行相关联。但有时候会需要包含没有关联行的那些行。使用外部联结。

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

与内部联结关联两个表中的行不同的是，外部联结还包括没有关联行的行。在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表（RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表）。上面的例子使用LEFT OUTER JOIN从FROM子句的左边表（customers表）中选择所有行。为了从右边的表中选择所有行，应该使用RIGHT OUTER JOIN，如下例所示：

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

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;

此SELECT语句使用INNER JOIN将customers和orders表互相关联。GROUP BY子句按客户分组数据，因此，函数调用COUNT(orders.order_num)对每个客户的订单计数，将它作为num_ord返回。

SELECT customer.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

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

第17章 组合查询

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

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

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

第一条SELECT检索价格不高于5的所有物品。第二条SELECT使用IN找出供应商1001和1002生产的所有物品，组合了一下。与下面的查询等价。

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

UNION规则

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

在使用UNION时，重复的行被自动取消。如果想返回所有匹配行，可使用UNION ALL而不是UNION。

在用UNION组合查询时，只能使用一条ORDER BY子句，它必须出现在最后一条SELECT语句之后。将用它来排序所有SELECT语句返回的所有结果，只能有一种排序方式。

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

第18章 全文本搜索

两个最常使用的引擎为MyISAM和InnoDB，前者支持全文本搜索，而后者不支持。

一般在创建表时启用全文本搜索。CREATE TABLE语句（第21章中介绍）接受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;

这里的FULLTEXT索引单个列，如果需要也可以指定多个列。
在定义之后，MySQL自动维护该索引。在增加、更新或删除行时，索引随之自动更新

Match()指定被搜索的列，Against()指定要使用的搜索表达式。

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabit');

Match(note_text)指示MySQL针对指定的列进行搜索，Against('rabbit')指定词rabbit作为搜索文本。由于有两行包含词rabbit，这两个行被返回。
除非使用BINARY方式（本章中没有介绍），否则全文本搜索不区分大小写。

全文本搜索的一个重要部分就是对结果排序。具有较高等级的行先返回（因为这些行很可能是你真正想要的行）。
如果指定多个搜索项，则包含多数匹配词的那些行将具有比包含较少词（或仅有一个匹配）的那些行高的等级值。

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

查询扩展，可能与你的搜索有关的所有其他行，即使它们不包含词anvils。

布尔方式，可以提供关于如下内容的细节：

• 要匹配的词；
• 要排斥的词（如果某行包含这个词，则不返回该行，即使它包含其他指定的词也是如此）；
• 排列提示（指定某些词比其他词更重要，更重要的词等级更高）;
• 表达式分组；
• 另外一些内容。

为了匹配包含heavy但不包含任意以rope开始的词的行，可使用以下查询：

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);

-排除一个词，而*是截断操作符（可想象为用于词尾的一个通配符）。

支持的所有布尔操作符如下：

函数	说明
+	包含，词必须存在
-	排除，词必须不出现
>	包含，而且增加等级
()	把词组成子表达式（允许这些子表达式作为一个组被包含、排除、排列等）
~	取消一个词的排序值
*	词尾的通配符
“”	定义一个短语（与单个词的列表不一样，它匹配整个短语以便包含或排除这个短语）

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('+rabbit +bait' IN BOOLEAN MODE);

这个搜索匹配包含词rabbit和bait的行。

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit bait' IN BOOLEAN MODE);

没有指定操作符，这个搜索匹配包含rabbit和bait中的至少一个词的行。

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('"rabbit bait"' IN BOOLEAN MODE);

这个搜索匹配短语rabbit bait而不是匹配两个词rabbit和bait。

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('>rabbit <carrot' IN BOOLEAN MODE);

匹配rabbit和carrot，增加前者的等级，降低后者的等级。

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('+safe +(<combination)' IN BOOLEAN MODE);

这个搜索匹配词safe和combination，降低后者的等级。

在布尔方式中，不按等级值降序排序返回的行。

全文本搜索的某些重要的说明:

• 在索引全文本数据时，短词被忽略且从索引中排除。短词定义为那些具有3个或3个以下字符的词（如果需要，这个数目可以更改）。
• MySQL带有一个内建的非用词（stopword）列表，这些词在索引全文本数据时总是被忽略。如果需要，可以覆盖这个列表（请参阅MySQL文档以了解如何完成此工作）。
• 许多词出现的频率很高，搜索它们没有用处（返回太多的结果）。因此，MySQL规定了一条50%规则，如果一个词出现在50%以上的行中，则将它作为一个非用词忽略。50%规则不用于IN BOOLEAN MODE。
• 如果表中的行数少于3行，则全文本搜索不返回结果（因为每个词或者不出现，或者至少出现在50%的行中）。
• 忽略词中的单引号。例如，don’t索引为dont。
• 不具有词分隔符（包括日语和汉语）的语言不能恰当地返回全文本搜索结果。
• 如前所述，仅在MyISAM数据库引擎中支持全文本搜索。

第19章 插入数据

插入可以用几种方式使用：

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

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

INSERT INTO customers(
        cust_name,
        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');

如果表的定义允许，则可以在INSERT操作中省略某些列。省略的列必须满足以下某个条件:

• 该列定义为允许NULL值（无值或空值）。
• 在表定义中给出默认值。这表示如果不给出值，将使用默认值。

只要每条INSERT语句中的列名（和次序）相同，可以如下组合各语句:

INSERT INTO customers(
        cust_name,
        cust_address,
        cust_city,
        cust_state,
        cust_zip,
        cust_country)
VALUES(
        'Pep E. LaPew',
        '100 Main Street',
        'Los Angeles',
        'CA',
        '90046',
        'USA'
      ),
      (
        'M. Martian',
        '42 Galaxy Way',
        'New York',
        'NY',
        '11213',
        'USA'
      );

下面的例子使用INSERT SELECT从custnew中将所有数据导入customers，也可以省略cust_id，让MySQL生成新值:

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

第20章 更新和删除数据

可采用两种方式使用UPDATE：

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

基本的UPDATE语句由3部分组成，分别是：

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

WHERE子句不要省略，否则会更新表中所有行。

客户10005现在有了电子邮件地址，因此他的记录需要更新：

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

UPDATE语句总是以要更新的表的名字开始。在此例子中，要更新的表的名字为customers。SET命令用来将新值赋给被更新的列。如这里所示，SET子句设置cust_email列为指定的值。UPDATE语句以WHERE子句结束，它告诉MySQL更新哪一行。没有WHERE子句，MySQL将会用这个电子邮件地址更新customers表中所有行，这不是我们所希望的。

更新客户10005的cust_name和cust_email列：

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

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

为了删除某个列的值，可设置它为NULL（假如表定义允许NULL值）。如下进行：

UPDATE customers
SET cust_email = NULL
WHERE cust_id = 1005;

DELETE可以从表中删除数据，有两种方式使用：

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

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

DELETE FROM customers
WHERE cust_id = 10006

在这个例子中，只删除客户10006。如果省略WHERE子句，它将删除表中每个客户。

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

更快的删除 如果想从表中删除所有行，不要使用DELETE。可使用TRUNCATE TABLE语句，它完成相同的工作，但速度更快（TRUNCATE实际是删除原来的表并重新创建一个表，而不是逐行删除表中的数据）

使用UPDATE或DELETE时所遵循的习惯：

• 除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句。
• 保证每个表都有主键（如果忘记这个内容，请参阅第15章），尽可能像WHERE子句那样使用它（可以指定各主键、多个值或值的范围）。
• 在对UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确。
• 使用强制实施引用完整性的数据库（关于这个内容，请参阅第15章），这样MySQL将不允许删除具有与其他表相关联的数据的行。

第21章 创建和操纵表

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

• 新表的名字，在关键字CREATE TABLE之后给出；
• 表列的名字和定义，用逗号分隔。

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),
)ENGINE=InnoDB;

如果你仅想在一个表不存在时创建它，应该在表名后给出IF NOT EXISTS。

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

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)
)ENGINE=InnoDB;

如果主键使用单个列，则它的值必须唯一。如果使用多个列，则这些列的组合值必须唯一，主键中只能使用不允许NULL值的列。

每个表只允许一个AUTO_INCREMENT列，而且它必须被索引（如，通过使它成为主键）。
SELECT_last_insert_id()此语句返回最后一个AUTO_INCREMENT值，然后可以将它用于后续的MySQL语句。

默认值用CREATE TABLE语句的列定义中的DEFAULT关键字指定，下面给该列的描述添加文本DEFAULT 1指示MySQL，在未给出数量的情况下使用数量1：

CREATE TABLE orderitems
(
        order_num    int    NOT NULL,
        order_item    int    NOT NULL,
        prod_id    char(10)    NOT NULL,
        quantity    int    NOT NULL DEFAULT 1,
        item_price    decimal(8,2)    NOT NULL,
        PRIMARY KEY (order_num, order_item)
)ENGINE=InnoDB;

引擎：

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

外键（用于强制实施引用完整性，如第1章所述）不能跨引擎，即使用一个引擎的表不能引用具有使用不同引擎的表的外键。

当表中存储数据以后，该表就不应该再被更新。
为了使用ALTER TABLE更改表结构，必须给出下面的信息：

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

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

ALTER TABLE vendors
ADD vend_phone CHAR(20);

这条语句给vendors表增加一个名为vend_phone的列，必须明确其数据类型。
删除刚刚添加的列，可以这样做：

ALTER TABLE vendors
DROP COLUMN vend_phone

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

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

1. 用新的列布局创建一个新表；
2. 使用INSERT SELECT语句（关于这条语句的详细介绍，请参阅第19章）从旧表复制数据到新表。如果有必要，可使用转换函数和计算字段；
3. 检验包含所需数据的新表；
4. 重命名旧表（如果确定，可以删除它）；
5. 用旧表原来的名字重命名新表；
6. 根据需要，重新创建触发器、存储过程、索引和外键。

删除表（删除整个表而不是其内容）非常简单，使用DROP TABLE语句即可：

DROP TABLE customers2;

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

RENAME TABLE customers2 TO customers;

可以使用下面的语句对多个表重命名：

RENAME TABLE backup_customers TO customers,
             backup_vendors TO vendors,
             backup_products TO products;

第22章 使用视图

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

视图的一些常见应用:

• 重用SQL语句。
• 简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道它的基本查询细节。
• 使用表的组成部分而不是整个表。
• 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。
• 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

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

视图创建和使用的一些最常见的规则和限制:

• 与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的名字）。
• 对于可以创建的视图数目没有限制。
• 为了创建视图，必须具有足够的访问权限。这些限制通常由数据库管理人员授予。
• 视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图。
• ORDER BY可以用在视图中，但如果从该视图检索数据的SELECT语句中也含有ORDER BY，那么该视图中的ORDER BY将被覆盖。
• 视图不能索引，也不能有关联的触发器或默认值。
• 视图可以和表一起使用。例如，编写一条联结表和视图的SELECT语句

视图的创建:

• 视图用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;

这条语句创建一个名为productcustomers的视图，它联结三个表，以返回已订购了任意产品的所有客户的列表。如果执行SELECT * FROM productcustomers，将列出订购了任意产品的客户。

为检索订购了产品TNT2的客户，可如下进行：

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

这条语句通过WHERE子句从视图中检索特定数据。在MySQL处理此查询时，它将指定的WHERE子句添加到视图查询中的已有WHERE子句中，以便正确过滤数据。

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

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

这条语句使用与以前的SELECT语句相同的查询创建视图

SELECT *
FROM vendorlocatinos

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

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

可以像使用其他表一样使用视图customeremaillist：

SELECT *
FROM customeremaillist;

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

视图对于简化计算字段的使用特别有用。下面是第10章中介绍的一条SELECT语句。它检索某个特定订单中的物品，计算每种物品的总价格：

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

可转换为一个视图：

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

为检索订单20005的详细内容（上面的输出），如下进行：

SELECT *
FROM orderitemsexpanded
WHERE order_num = 20005;

一般，应该将视图用于检索（SELECT语句）而不用于更新（INSERT、UPDATE和DELETE)。

视图提供了一种MySQL的SELECT语句层次的封装，可用来简化数据处理以及重新格式化基础数据或保护基础数据。

第23章 使用存储过程

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

存储过程优点：

• 通过把处理封装在容易使用的单元中，简化复杂的操作（正如前面例子所述）。
• 由于不要求反复建立一系列处理步骤，这保证了数据的完整性。如果所有开发人员和应用程序都使用同一（试验和测试）存储过程，则所使用的代码都是相同的。这一点的延伸就是防止错误。需要执行的步骤越多，出错的可能性就越大。防止错误保证了数据的一致性。
• 简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化，只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。这一点的延伸就是安全性。通过存储过程限制对基础数据的访问减少了数据讹误（无意识的或别的原因所导致的数据讹误）的机会。
• 提高性能。因为使用存储过程比使用单独的SQL语句要快。
• 存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码（在下一章的例子中可以看到。）换句话说，使用存储过程有3个主要的好处，即简单、安全、高性能。显然，它们都很重要。

不过，在将SQL代码转换为存储过程前，也必须知道它的一些缺陷：

• 一般来说，存储过程的编写比基本SQL语句复杂，编写存储过程需要更高的技能，更丰富的经验。
• 你可能没有创建存储过程的安全访问权限。许多数据库管理员限制存储过程的创建权限，允许用户使用存储过程，但不允许他们创建存储过程。

CALL productpricing(
        @pricelow,
        @pricehigh,
        @priceaverage
);

上面执行名为productpricing的存储过程，它计算并返回产品的最低、最高和平均价格。

一个返回产品平均价格的存储过程。以下是其代码：

CREATE PROCEDURE productpricing()
BEGIN
    SELECT Avg(pro_price) AS priceaverage
    FROM products;
END;

此存储过程名为productpricing，用CREATE PROCEDURE productpricing()语句定义。如果存储过程接受参数，它们将在()中列举出来。此存储过程没有参数，但后跟的()仍然需要。BEGIN和END语句用来限定存储过程体，过程体本身仅是一个简单的SELECT语句（使用第12章介绍的Avg()函数）。

如果用的是命令行，需要改分隔符。我感觉不可能在命令行里写这个。

CALL productpricing();

CALL productpricing();执行刚创建的存储过程并显示返回的结果。

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

DROP PROCEDUREC productpricing;

这条语句删除刚创建的存储过程。当过程存在想删除它时（如果过程不存在也不产生错误）可使用DROP PROCEDURE IF EXISTS。

productpricing只是一个简单的存储过程，它简单地显示SELECT语句的结果。一般，存储过程并不显示结果，而是把结果返回给你指定的变量。

CREATE PROCEDURE productpricing(
    OUT pl DECIMAL(8,2),
    OUT ph DECIMAL(8,2),
    OUT pa DECIMAL(8,2)
)
BEGIN
    SELECT Min(prod_price)
    INTO pl
    FROM products;
    SELECT Max(prod_price)
    INTO ph
    FROM products;
    SELECT Avg(prod_price)
    INTO pa
    FROM products;
END;

此存储过程接受3个参数：pl存储产品最低价格，ph存储产品最高价格，pa存储产品平均价格。每个参数必须具有指定的类型，这里使用十进制值。关键字OUT指出相应的参数用来从存储过程传出一个值（返回给调用者）。
MySQL支持IN（传递给存储过程）、OUT（从存储过程传出，如这里所用）和INOUT（对存储过程传入和传出）类型的参数。存储过程的代码位于BEGIN和END语句内，如前所见，它们是一系列SELECT语句，用来检索值，然后保存到相应的变量（通过指定INTO关键字）。

存储过程的参数允许的数据类型与表中使用的数据类型相同。

为调用此修改过的存储过程，必须指定3个变量名，如下所示：

CALL productpricing(
    @pricelow,
    @pricehigh,
    @priceaverage,
)

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

在调用时，这条语句并不显示任何数据，为了显示检索出的产品平均价格，可如下进行：

SELECT @priceaverage

为了获得3个值，可使用以下语句：

SELECT @pricehigh, @pricelow, @priceaverage;

ordertotal接受订单号并返回该订单的合计：

CREATE PROCEDURE ordertotal(
    IN onumber INT,
    OUT ototal DECIMAL(8,2)
)
BEGIN
    SELECT Sum(item_price*quantity)
    FROM orderitems
    WHERE order_num = onumber
    INTO ototal;
END;

onumber定义为IN，因为订单号被传入存储过程。ototal定义为OUT，因为要从存储过程返回合计。
SELECT语句使用这两个参数，WHERE子句使用onumber选择正确的行，INTO使用ototal存储计算出来的合计。为调用这个新存储过程，可使用以下语句：

CALL ordertotal(20005, @total);

必须给ordertotal传递两个参数；第一个参数为订单号，第二个参数为包含计算出来的合计的变量名。

为了显示此合计，可如下进行：

SELECT @total

为了得到另一个订单的合计显示，需要再次调用存储过程，然后重新显示变量：

CALL ordertotal(20009, @total)

有如下需求：

• 获得合计（与以前一样）；
• 把营业税有条件地添加到合计；
• 返回合计（带或不带税）。

-- Name: ordertotal
-- Parameters: onumber = order number
--             taxable = 0 if not taxable, 1 if taxable
--             ototal  = order total variable

CREATE PROCEDURE ordertotal(
    IN onumber INT,
    IN taxable BOOLEAN,
    OUT ototal DECIMAL(8,2)
)COMMENT 'Obtain order total, optionally adding tax'
BEGIN

    --Declare variable for total
    DECLARE total DECIMAL(8,2)
    --DECLARE taxrate INT DEFAULT 6;
    DECLARE taxrate INT DEFAULT 6;

    --Get the order total
    SELECT Sum(item_price*quantity)
    FROM orderitems
    WHERE order_num = onumber
    INTO total;

    --Is this taxable?
    IF taxable THEN
        -- Yes, so add taxrate to the total
        SELECT total+(total/100*taxrate) INTO total;
    END IF;
    -- And finally, save to out variable
    SELECT total INTO ototal;

END;

上面增加了注释（前面放置--）。在存储过程复杂性增加时，这样做特别重要。添加了另外一个参数taxable，它是一个布尔值（如果要增加税则为真，否则为假）。在存储过程体中，用DECLARE语句定义了两个局部变量。DECLARE要求指定变量名和数据类型，它也支持可选的默认值（这个例子中的taxrate的默认被设置为6%）。SELECT语句已经改变，因此其结果存储到total（局部变量）而不是ototal。IF语句检查taxable是否为真，如果为真，则用另一SELECT语句增加营业税到局部变量total。最后，用另一SELECT语句将total（它增加或许不增加营业税）保存到ototal。

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

CALL ordertotal(20005, 0, @total);
SELECT @total

BOOLEAN值指定为1表示真，指定为0表示假（实际上，非零值都考虑为真，只有0被视为假）。通过给中间的参数指定0或1，可以有条件地将营业税加到订单合计上。

为显示用来创建一个存储过程的CREATE语句，使用SHOW CREATE PROCEDURE语句：

SHOW CREATE PROCEDURE ordertotal;

为了获得包括何时、由谁创建等详细信息的存储过程列表，使用SHOW PROCEDURE STATUS。

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

SHOW PROCEDURE STATUS LIKE 'ordertotal';

第24章 使用游标

游标（cursor）是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集
只能用于存储过程，不像多数DBMS, MySQL游标只能用于存储过程（和函数）。

使用游标涉及几个明确的步骤：

• 在能够使用游标前，必须声明（定义）它。这个过程实际上没有检索数据，它只是定义要使用的SELECT语句。
• 一旦声明后，必须打开游标以供使用。这个过程用前面定义的SELECT语句把数据实际检索出来。
• 对于填有数据的游标，根据需要取出（检索）各行。
• 在结束游标使用时，必须关闭游标。
  在声明游标后，可根据需要频繁地打开和关闭游标。在游标打开后，可根据需要频繁地执行取操作。

DECLARE命名游标，并定义相应的SELECT语句，根据需要带WHERE和其他子句

下面的语句定义了名为ordernumbers的游标，使用了可以检索所有订单的SELECT语句

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

这个存储过程并没有做很多事情，DECLARE语句用来定义和命名游标，这里为ordernumbers。 存储过程处理完成后，游标就消失（因为它局限于存储过程）。

游标用OPEN CURSOR语句来打开：OPEN ordernumbers
在处理OPEN语句时执行查询，存储检索出的数据以供浏览和滚动。
游标处理完成后，应当使用如下语句关闭游标：CLOSE ordernumbers

CLOSE释放游标使用的所有内部内存和资源，因此在每个游标不再需要时都应该关闭。在一个游标关闭后，如果没有重新打开，则不能使用它
隐含关闭 如果你不明确关闭游标，MySQL将会在到达END语句时自动关闭它。

CREATE PROCEDURE processorders()
BEGIN
    -- Declare the cursor
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;

    -- OPEN the cursor
    OPEN ordernumbers;

    -- Close the cursor
    CLOSE ordernumbers;
END;

这个存储过程声明、打开和关闭一个游标。但对检索出的数据什么也没做。

在一个游标被打开后，可以使用FETCH语句分别访问它的每一行。FETCH指定检索什么数据（所需的列），检索出来的数据存储在什么地方。它还向前移动游标中的内部行指针，使下一条FETCH语句检索下一行（不重复读取同一行）。

CREATE PROCEDURE processorders()
BEGIN
    -- Declare local variables
    DECLARE o INT;

    -- Declare the cursor
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;

    -- OPEN the cursor
    OPEN ordernumbers;

    -- Get order number
    FETCH ordernumbers INTO o;

    -- Close the cursor
    CLOSE ordernumbers;
END;

其中 FETCH 用来检索当前行的 order_num 列（将自动从第一行开始）到一个名为o的局部声明的变量中。对检索出的数据不做任何处理。
在下一个例子中，循环检索数据，从第一行到最后一行。

CREATE PROCEDURE processorders()
BEGIN
    -- Declare local variables
    Declare done BOOLEAN DEFAULT 0;
    DECLARE o INT;

    -- Declare the cursor
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;

    -- Declare continue handler
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

    -- OPEN the cursor
    OPEN ordernumbers;

    -- Loop through all rows
    REPEAT
        -- Get order number
        FETCH ordernumbers INTO o;
    -- End of loop
    UNTIL done END REPEAT

    -- Close the cursor
    CLOSE ordernumbers;
END;

这个例子使用FETCH检索当前order_num到声明的名为o的变量中。但与前一个例子不一样的是，这个例子中的FETCH是在REPEAT内，因此它反复执行直到done为真（由UNTIL done END REPEAT；规定）。
为使它起作用，用一个DEFAULT 0（假，不结束）定义变量done。那么，done怎样才能在结束时被设置为真呢？答案是用以下语句：
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
这条语句定义了一个CONTINUE HANDLER，它是在条件出现时被执行的代码。这里，它指出当SQLSTATE '02000’出现时，SET done=1。SQLSTATE '02000’是一个未找到条件，当REPEAT由于没有更多的行供循环而不能继续时，出现这个条件。

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

如果一切正常，你可以在循环内放入任意需要的处理（在FETCH语句之后，循环结束之前）。
重复或循环？ 除这里使用的REPEAT语句外，MySQL还支持循环语句，它可用来重复执行代码，直到使用LEAVE语句手动退出为止。通常REPEAT语句的语法使它更适合于对游标进行循环。

CREATE PROCEDURE processorders()
BEGIN
    -- Declare local variables
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE o INT;
    DECLARE t DECIMAL(8,2);
    

    -- Declare the cursor
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;

    -- Declare continue handler
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

    -- Create a table to store the results
    CREATE TABLE IF NOT EXISTS ordertotals
    (order_num INT, total DECIMAL(8,2));

    -- OPEN the cursor
    OPEN ordernumbers;

    -- Loop through all rows
    REPEAT
        -- Get order number
        FETCH ordernumbers INTO o;

        -- Get the total for this order
        CALL ordertotal(o, 1, t);

        -- Insert order and total into ordertotals
        INSERT INTO ordertotals(order_num, total)
        VALUES(o,t)
    -- End of loop
    UNTIL done END REPEAT

    -- Close the cursor
    CLOSE ordernumbers;
END;

在这个例子中，我们增加了另一个名为t的变量（存储每个订单的合计）。此存储过程还在运行中创建了一个新表（如果它不存在的话），名为ordertotals。这个表将保存存储过程生成的结果。FETCH像以前一样取每个order_num，然后用CALL执行另一个存储过程（我们在前一章中创建）来计算每个订单的带税的合计（结果存储到t）。最后，用INSERT保存每个订单的订单号和合计。此存储过程不返回数据，但它能够创建和填充另一个表，可以用一条简单的SELECT语句查看该表：

SELECT *
FROM ordertotals;

第25章 使用触发器

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

• DELETE
• INSERT
• UPDATE

其他MySQL语句不支持触发器。

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

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

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

触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器。因此，每个表最多支持6个触发器（每条INSERT、UPDATE和DELETE的之前和之后）。单一触发器不能与多个事件或多个表关联，所以，如果你需要一个对INSERT和UPDATE操作执行的触发器，则应该定义两个触发器。

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

为了删除一个触发器，可使用DROP TRIGGER语句，如下所示：

DROP TRIGGER newproduct;

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

INSERT触发器

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

• 在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行；
• 在BEFORE INSERT触发器中，NEW中的值也可以被更新（允许更改被插入的值）；
• 对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值。

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的每次插入使用这个触发器将总是返回新的订单号。

INSERT INTO orders(order_date, cust_id)
VALUES(Now(),10001);

orders包含3个列。order_date和cust_id必须给出，order_num由MySQL自动生成，而现在order_num还自动被返回。

BEFORE或AFTER？ 通常，将BEFORE用于数据验证和净化（目的是保证插入表中的数据确实是需要的数据）。本提示也适用于UPDATE触发器。

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中的值全都是只读的，不能更新。

下面的例子保证州名缩写总是大写（不管UPDATE语句中给出的是大写还是小写）。
显然，任何数据净化都需要在UPDATE语句之前进行，就像这个例子中一样。每次更新一个行时，NEW.vend_state中的值（将用来更新表行的值）都用Upper(NEW.vend_state)替换。

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

• 与其他DBMS相比，MySQL 5中支持的触发器相当初级。未来的MySQL版本中有一些改进和增强触发器支持的计划。
• 创建触发器可能需要特殊的安全访问权限，但是，触发器的执行是自动的。如果INSERT、UPDATE或DELETE语句能够执行，则相关的触发器也能执行。
• 应该用触发器来保证数据的一致性（大小写、格式等）。在触发器中执行这种类型的处理的优点是它总是进行这种处理，而且是透明地进行，与客户机应用无关。
• 触发器的一种非常有意义的使用是创建审计跟踪。使用触发器，把更改（如果需要，甚至还有之前和之后的状态）记录到另一个表非常容易。
• 遗憾的是，MySQL触发器中不支持CALL语句。这表示不能从触发器内调用存储过程。所需的存储过程代码需要复制到触发器内。

第26章 管理事务处理

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

• 事务（transaction）指一组SQL语句；
• 回退（rollback）指撤销指定SQL语句的过程；
• 提交（commit）指将未存储的SQL语句结果写入数据库表；
• 保留点（savepoint）指事务处理中设置的临时占位符（place-holder），你可以对它发布回退（与回退整个事务处理不同）。

管理事务处理的关键在于将SQL语句组分解为逻辑块，并明确规定数据何时应该回退，何时不应该回退。
MySQL使用下面的语句来标识事务的开始：

START TRANSACTION

使用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只能在一个事务处理内使用（在执行一条STARTTRANSACTION命令之后）。

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

使用COMMIT

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

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

在这个例子中，从系统中完全删除订单20010。因为涉及更新两个数据库表orders和orderItems，所以使用事务处理块来保证订单不被部分删除。最后的COMMIT语句仅在不出错时写出更改。如果第一条DELETE起作用，但第二条失败，则DELETE不会提交（实际上，它是被自动撤销的）。
隐含事务关闭：当COMMIT或ROLLBACK语句执行后，事务会自动关闭（将来的更改会隐含提交）。

使用保留点

简单的ROLLBACK和COMMIT语句就可以写入或撤销整个事务处理。但是，只是对简单的事务处理才能这样做，更复杂的事务处理可能需要部分提交或回退。
例如，前面描述的添加订单的过程为一个事务处理。如果发生错误，只需要返回到添加orders行之前即可，不需要回退到customers表（如果存在的话）。
为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符。这样，如果需要回退，可以回退到某个占位符。
这些占位符称为保留点。为了创建占位符，可如下使用SAVEPOINT语句：

SAVEPOINT deletel;

每个保留点都取标识它的唯一名字，以便在回退时，MySQL知道要回退到何处。为了回退到本例给出的保留点，可如下进行：

ROLLBACK TO deletel;

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

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

更改默认的提交行为

正如所述，默认的MySQL行为是自动提交所有更改。换句话说，任何时候你执行一条MySQL语句，该语句实际上都是针对表执行的，而且所做的更改立即生效。为指示MySQL不自动提交更改，需要使用以下语句：

SET autocommit=0

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

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

第27章 全球化和本地化

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

• 字符集为字母和符号的集合；
• 编码为某个字符集成员的内部表示；
• 校对为规定字符如何比较的指令。

在MySQL的正常数据库活动（SELECT、INSERT等）中，不需要操心太多的东西。使用何种字符集和校对的决定在服务器、数据库和表级进行。

MySQL支持众多的字符集。为查看所支持的字符集完整列表，使用以下语句：
这条语句显示所有可用的字符集以及每个字符集的描述和默认校对

SHOW CHARACTER SET;

为了查看所支持校对的完整列表，使用以下语句：

SHOW COLLATION;

此语句显示所有可用的校对，以及它们适用的字符集。可以看到有的字符集具有不止一种校对。例如，latin1对不同的欧洲语言有几种校对，而且许多校对出现两次，一次区分大小写（由_cs表示），一次不区分大小写（由_ci表示）。

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

SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation%';

实际上，字符集很少是服务器范围（甚至数据库范围）的设置。不同的表，甚至不同的列都可能需要不同的字符集，而且两者都可以在创建表时指定。

CREATE TABLE mytable
(
    columnn1    INT,
    columnn2    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，则使用数据库默认。

除了能指定字符集和校对的表范围外，MySQL还允许对每个列设置它们，如下所示：
这里对整个表以及一个特定的列指定了CHARACTER SET和COLLATE。

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

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

SELECT * FROM customers
ORDER BY lastname, firstname COLLATE latin1_general_cs;

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

第28章 安全管理

MySQL用户账号和信息存储在名为mysql的MySQL数据库中。一般不需要直接访问mysql数据库和表（你稍后会明白这一点），但有时需要直接访问。需要直接访问它的时机之一是在需要获得所有用户账号列表时。为此，可使用以下代码：

USE mysql
SELECT user FROM user;

为了创建一个新用户账号，使用CREATE USER语句，如下所示：

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

为重新命名一个用户账号，使用RENAME USER语句，如下所示：

RENAME USER ben TO bforta;

为了删除一个用户账号（以及相关的权限），使用DROP USER语句，如下所示：

DROP USER bforta;

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

SHOW GRANTS FOR bforta

输出结果显示用户bforta有一个权限USAGE ON *.*。USAGE表示根本没有权限（我知道，这不很直观），所以，此结果表示在任意数据库和任意表上对任何东西没有权限。

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

• 要被授予的权限
• 要被授予访问权限的数据库或表
• 用户名

GRANT SELECT ON crashcourse.* TO bforta;

此GRANT允许用户在crashcourse.*（crashcourse数据库的所有表）上使用SELECT。通过只授予SELECT访问权限，用户bforta对crashcourse数据库中的所有数据具有只读访问权限。
每个GRANT添加（或更新）用户的一个权限。MySQL读取所有授权，并根据它们确定权限。

GRANT的反操作为REVOKE，用它来撤销特定的权限。下面举一个例子：

REVOKE SELECT ON crashcourse.* FROM bforta

这条REVOKE语句取消刚赋予用户bforta的SELECT访问权限。被撤销的访问权限必须存在，否则会出错。

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

• 整个服务器，使用GRANT ALL和REVOKE ALL；
• 整个数据库，使用ON database.*；
• 特定的表，使用ON database.table；
• 特定的列；
• 特定的存储过程。

权限	说明
ALL	除GRANT OPTION外的所有权限
ALTER	使用ALTER TABLE
ALTER ROUTINE	使用ALTER PROCEDURE和DROP ROCEDURE
CREATE	使用CREATE TABLE
CREATE ROUTINE	使用CREATE PROCEDURE
CREATE TEMPORARY TABLES	使用CREATE TEMPORARY TABLE
CREATE USER	使用CREATE USER、DROP USER、RENAME USER和REVOKE ALL PRIVILEGES
CREATE VIEW	使用CREATE VIEW
DELETE	使用DELETE
DROP	使用DROP TABLE
EXECUTE	使用CALL和存储过程
FILE	使用SELECT INTO OUTFILE和LOAD DATA INFILE
GRANT OPTION	使用GRANT和REVOKE
INDEX	使用CREATE INDEX和DROP INDEX
INSERT	使用INSERT
LOCK TABLES	使用LOCK TABLES
PROCESS	使用SHOW FULL PROCESSLIST
RELOAD	使用FLUSH
REPLICATION CLIENT	服务器位置的访问
REPLICATION SLAVE	由复制从属使用
SELECT	使用SELECT
SHOW DATABASES	使用SHOW DATABASES
SHOW VIEW	使用SHOW CREATE VIEW
SHUTDOWN	使用mysqladmin shutdown（用来关闭MySQL）
SUPER	使用CHANGE MASTER、KILL、LOGS、PURGE、MASTER和SET GLOBAL。还允许mysqladmin调试登录
UPDATE	使用UPDATE
USAGE	无访问权限

为了更改用户口令，可使用SET PASSWORD语句。新口令必须如下加密：

SET PASSWORD FOR bforta = Password('n3w p@$$w0rd');

SET PASSWORD还可以用来设置你自己的口令：
在不指定用户名时，SET PASSWORD更新当前登录用户的口令。

SET PASSWORD = Password('n3w p@$$w0rd')

第29章 数据库维护

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

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

ANALYZE TABLE orders;

CHECK TABLE用来针对许多问题对表进行检查。在MyISAM表上还对索引进行检查。CHECK TABLE支持一系列的用于MyISAM表的方式。CHANGED检查自最后一次检查以来改动过的表。EXTENDED执行最彻底的检查， FAST只检查未正常关闭的表， MEDIUM检查所有被删除的链接并进行键检验，QUICK只进行快速扫描。如下所示，CHECKTABLE发现和修复问题：

CHECK TABLE orders, orderitems;

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

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

在排除系统启动问题时，首先应该尽量用手动启动服务器。MySQL服务器自身通过在命令行上执行mysqld启动。下面是几个重要的mysqld命令行选项：

• –help 显示帮助——一个选项列表；
• –safe-mode 装载减去某些最佳配置的服务器；
• –verbose 显示全文本消息（为获得更详细的帮助消息与 –help联合使用）；
• –version 显示版本信息然后退出

MySQL维护管理员依赖的一系列日志文件。主要的日志文件有以下几种。

• 错误日志。它包含启动和关闭问题以及任意关键错误的细节。此日志通常名为hostname.err，位于data目录中。此日志名可用--log-error命令行选项更改。
• 查询日志。它记录所有MySQL活动，在诊断问题时非常有用。此日志文件可能会很快地变得非常大，因此不应该长期使用它。此日志通常名为hostname.log，位于data目录中。此名字可以用--log命令行选项更改。
• 二进制日志。它记录更新过数据（或者可能更新过数据）的所有语句。此日志通常名为hostname-bin，位于data目录内。此名字可以用--log-bin命令行选项更改。注意，这个日志文件是MySQL5中添加的，以前的MySQL版本中使用的是更新日志。
• 缓慢查询日志。顾名思义，此日志记录执行缓慢的任何查询。这个日志在确定数据库何处需要优化很有用。此日志通常名为hostname-slow.log ，位于data目录中。此名字可以用--log-slow-queries命令行选项更改。

在使用日志时，可用FLUSH LOGS语句来刷新和重新开始所有日志文件。

第30章 改善性能

数据库管理员把他们生命中的相当一部分时间花在了调整、试验以改善DBMS性能之上。在诊断应用的滞缓现象和性能问题时，性能不良的数据库（以及数据库查询）通常是最常见的祸因。
可以看出，下面的内容并不能完全决定MySQL的性能。我们只是想回顾一下前面各章的重点，提供进行性能优化探讨和分析的一个出发点。

• 首先，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。
• 数据库是不断变化的实体。一组优化良好的表一会儿后可能就面目全非了。由于表的使用和内容的更改，理想的优化和配置也会改变。
• 最重要的规则就是，每条规则在某些条件下都会被打破。