MySQL必知必会（两万字MySQL教程，熟练掌握MySQL）

MySQL必知必会（两万字MySQL教程，熟练掌握MySQL）

前言

本文是作者在阅读《MySQL必知必会》[英]Ben Forta 著 刘晓霞 钟鸣 译 人民邮电出版社 所整理的阅读笔记，包含了MySQL的从0到1的所有知识点。本文适用于：
① 零基础
② 刚学MySQL，想快速掌握
③ 学过完整的MySQL，想复盘
④ 准备面试
因为本文大多内容是从书中截取，当然也有作者自己的部分见解，所以可以搭配书本和本文一起学习。
另外，本文的sql脚本在下面这个链接中，请在阅读本文前，先运行sql脚本，以便实操。
本文数据库脚本文件

若有不对的地方，欢迎大家评论区或私信交流！
最后，希望大家可以自己手敲代码，不要直接复制我的代码，这样才能帮助你真正掌握MySQL!

1.了解SQL

1.1主键

主键通常定义在一个列上，但这并不是必须的，也可以使用多个列作为主键。在使用多列作为主键时，所有列值的组合必须是唯一的，但单个列的值可以不唯一。

2.MySQL简介

DBMS数据库管理系统，MySQL就是一种DBMS。

cmd进入MySQL程序命令，输入exit退出程序

mysql -u root -p

基础命令

//显示所有数据库
show databases;
//显示所选数据库的所有表
show tables;
//显示表结构[使用COLUMNS:列，关键字实现]
show columns from 表名;
//显示表所有数据，用*，如果只需要某一列，则将*替换为列名即可
select * from 表名

3.检索数据

3.1distinct关键字

​ distinct用来查询不重复记录的条数,即用distinct来返回不重复字段的条数（count(distinct id)）,其原因是distinct只能返回他的目标字段，而无法返回其他字段。

select distinct vend_id from products;

注意事项
distinct 【查询字段】，必须放在要查询字段的开头，即放在第一个参数
只能在SELECT 语句中使用，不能在 INSERT, DELETE, UPDATE 中使用
DISTINCT 表示对后面的所有参数的拼接取不重复的记录，即查出的参数拼接每行记录都是唯一的
不能与all同时使用，默认情况下，查询时返回的就是所有的结果

3.2LIMIT关键字

LIMIT 5 限制返回不多于5行

select prod_name from products LIMIT 5;

LIMIT 5,5 限制从行5开始的五行，第一个5为开始位置，第二个5为要检索的行数。

select prod_name from products LIMIT 5,5;

3.3使用完全限定的表名（多表联查的时候会用到）

select products.prod_name from crashcourse.products;

4.排序检索数据

4.1排序数据

ORDER BY子句取一个或多个列的名字，据此对输出进行排序。如下，使用ORDER BY，通过number对返回结果进行排序。

select username from user ORDER BY number;

4.2按多个排序

注意，仅在多个行具有相同的prod_price时，才对产品使用prod_name排序。

select prod_id,prod_price,prod_name 
from products 
ORDER BY prod_price,prod_name;

4.3指定排序方向（DESC、ASC）

ORDER BY默认升序排序（A到Z），但是如果想要进行降序排序，必须指定DESC关键字

select prod_id,prod_price,prod_name 
from products 
ORDER BY prod_price DESC;

如果打算用多个列排序（例：最贵的在前面）

select prod_id,prod_price,prod_name 
from products 
ORDER BY prod_price DESC,prod_name;

DESC关键字只应用到直接位于其前面的列名。在上例中，只对prod_price列指定降序排序，而prod_name列（在每个价格内）仍按标准准时的升序排序。

与DESC相反的关键字时ASC，ASC表示升序排序，但是因为升序是默认的，所以没啥作用。

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

select prod_price 
from products 
ORDER BY prod_price 
DESC LIMIT 1;

注意order by句子的位置 在给出order by句子时，应该保证它位于from子句之后。如果使用了limit，它必须位于order by之后

5.过滤数据

5.1WHERE关键字

select prod_name,prod_price 
from products
where prod_price = 2.50

注意：同时使用order by和where子句时，order 应位于where之后

5.2WHERE子句操作符

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

注意BETWEEN需要搭配and使用

select prod_name,prod_price 
from products
where prod_price BETWEEN 1 and 20;

5.3空值检查

某个列的值可能为空，当NULL作为过滤条件时。

select prod_name
from products
where prod_price IS NULL;

6.数据过滤

6.1AND操作符

并列同时满足

select prod_id,prod_price,prod_name
from products
where vend_id = 1003 and prod_price <= 10;

上述例子只使用了一个AND,当有多个条件时，没添加一条就要使用一个AND

6.2OR操作符

满足其中一个条件即可

select prod_name,prod_price
from products
where vend_id = 1002 or vend_id = 1003;

6.3计算次序

AND 和 OR结合使用

假如需要列出10美元（含）以上且由1002或1003制造的产品

select prod_name,prod_price
from products
where (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;

注意，需要用()明确分组相应的操作符，从而消除歧义

6.4IN操作符

圆括号()在where子句中还有另一种用法，用于IN操作符指定条件范围。

select prod_name,prod_price
from products
where vend_id IN (1002,1003)
order by prod_name;

需注意的是IN后面的圆括号表示的不是区间。另外，IN和OR的功能相同，但是IN的语法更清楚且更加直观

select prod_name,prod_price
from products
where vend_id = 1002 or vend_id = 1003
order by prod_name;

6.5NOT操作符

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

例如：为了列出1002和1003之外的所有供应商制造的产品，可编写如下代码：

select prod_name,prod_price
from products
where vend_id NOT IN (1002,1003)
order by prod_name;

7.用通配符进行过滤

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

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

7.1LIKE操作符

7.1.1百分号（%）通配符

（1）例如：找出jet起头的产品

select prod_id,prod_name
from products
where prod_name LIKE 'jet%'

%告诉MySQL接受jet之后的任意字符，不管它有多少个字符。需注意的是，根据MySQL的配置方式，搜索可以是区分大小写的，如果区分大小写，'jet%'与JetPack 1000 将不匹配。

（2）通配符可在搜索模式中任意位置使用，并且可以使用多个通配符。下面的例子，通配符位于模式的两端：

select prod_id,prod_name
from products
where prod_name LIKE '%anvil%';

搜索模式’%anvil%'表示匹配任何位置包含文本anvil的值。

（3）通配符也可以位于搜索模式的中间，例如找出s起头，e结尾的所有产品

select prod_name
from products
where prod_name LIKE 's%e';

注意首尾空格会干扰%，并且'%'不能匹配NULL

7.1.2下划线（_）通配符

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

例子：

select prod_id,prod_name
from products
where prod_name LIKE '_ ton anvil';

与7.1.1的（2）的结果进行对比

7.2通配符的使用技巧

​ MySQL的通配符很有用，但是这种功能是有代价的：通配符搜索的处理一般要比前面讨论的其他搜索花费的时间更长。因此不要过度使用通配符，且不要把他们放在搜索模式的开始处（必要情况除外）。

8.用正则表达式进行搜索

​ 前面两章说过，过滤可以用匹配、比较和通配符寻找数据，但是随着过滤条件复杂性的增加，where子句本身的复杂性也有必要增加。这时，正则表达式就会变得有用。

​ 正则表达式是用来匹配文本的特殊的串（字符集合）。例如你想从一个文本文件中提取电话号码，可以使用正则表达式……

8.1基本字符匹配

select prod_name
from products
where prod_name REGEXP '1000'
order by prod_name;

看起来和前面说的LIKE没说明区别，只是把LIKE替换成REGEXP，但是REGEXP后面跟的已经是一个正则表达式了。请继续看下面例子

select prod_name
from products
where prod_name REGEXP '.000'
order by prod_name;

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

LIKE 和 REGEXP的区别

select prod_name
from products
where prod_name LIKE '1000'
order by prod_name;
——————————————————————————————————————
select prod_name
from products
where prod_name REGEXP '1000'
order by prod_name;

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

LIKE匹配整个列，如果被匹配的文本在列值中出现，LIKE将不会找到它，相应的行也不会被返回（除非使用通配符）。而REGEXP在列值内进行匹配，如果被匹配的文本在列值中出现，REGEXP将会找到它，相应的行将被返回。通俗点说，LIKE如果不使用通配符的话，是跟一整个列值进行比较。

那么REGEXP可以用于匹配整个列值吗？答案是肯定的，需要用到一些符号，后面会讲到。

总体而言，LIKE更常用于简单的模糊匹配，而REGEXP适用于更复杂的模式匹配，因为它支持正则表达式。在使用这两个操作符时，请注意性能差异，因为正则表达式匹配可能会比简单的通配符匹配更消耗资源。

需注意的是，正则表达式不区分大小写，如果需要区分大小写需要使用BINARY关键字实现。

-- 查找用户名以 "john" 开头的用户，不区分大小写
SELECT * FROM users WHERE username REGEXP '^john';

8.2进行OR匹配

select prod_name
from products
where prod_name REGEXP '1000|2000'
order by prod_name;

可以有多个or条件，例如’1000|2000|3000’

8.3匹配几个字符之一

select prod_name
from products
where prod_name REGEXP '[123] Ton'
order by prod_name;

[123]的意思是匹配1或2或3，其实[]是OR的一种缩写，不同的是，如下：

select prod_name
from products
where prod_name REGEXP '1|2|3 Ton'
order by prod_name;

当然，字符集合也可以被否定，加上^即可，例如

[^123]

就可以取反，排除掉。

8.4匹配范围

集合可以用于匹配一个或多个字符，例如[0123456789]，但是也可以用[0-9]表示。此外，例如[a-z]也是可以的。

select prod_name
from products
where prod_name REGEXP '[1-5] Ton'
order by prod_name;

8.5匹配特殊字符

比如我们要匹配一些特殊含义字符，例如匹配 .、[]、|、-等怎么办呢？

这时可以使用\作为前导，例如：

select vend_name
from vendors
where vend_name REGEXP '\\.'
order by vend_name;

\\也用来引用元字符，如下:
\\f		换页
\\n		换行
\\r		回车
\\t		制表
\\v		纵向制表

此外，为了匹配\本身，需要用\\\

8.6匹配类字符

8.7定位符

以上所讲述的例子都是匹配一个串中任意位置的文本，但是当需要匹配指定位置文本时就要使用定位符。

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

例如，你想找一个数（包括以小数点开始的数），怎么办呢？

select prod_name
from products
where prod_name REGEXP '^[0-9\\.]'
order by prod_name;

9.创建计算字段

9.1拼接字段

多数的DBMS使用+或||来实现字段拼接，而MySQL则是使用Concat()函数来实现。

select CONCAT(vend_name,'(',vend_country,')')
from vendors
order by vend_name;

Concat()需要一个或多个指定串时，各串之间用逗号分隔。

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

select Concat(rtrim(vend_name),'(',rtrim(vend_country),')')
from vendors
order by vend_name;

如果要去掉左边的空格就用ltrim()实现。

​ 那么拼接好的字段列的名字是什么呢？实际上他没有名字，它只是一个值。一个未命名的列不能用于客户机应用中，因为客户机没有帮发引用它。为解决这一问题，SQL支持列别名。别名用关键字AS赋予。

select Concat(rtrim(vend_name),'(',rtrim(vend_country),')') AS
vend_title
from vendors
order by vend_name;

9.2执行算术计算

计算机字段的另一种常见用途是对检索出的数据进行算术计算。例如，orders表包含收到的所有订单，orderitems表包含每个订单的各项物品。下面的SQL语句检索订单号20005中的所有物品。

select prod_id quantity,item_price
from orderitems
where order_num = 20005;

item_price列包含订单中每项物品的单价。如下汇总物品的价格（单价乘以订购数量）：

select prod_id,
		quantity,
		item_price,
		quantity*item_price AS expanded_price
from orderitems
where order_num = 20005;

MySQL算术运算符包括：+ - * /，可以使用圆括号来区分优先级。

10.使用数据处理函数

10.1文本处理函数

上一章我们使用rtrim()来去除右边的空格，下面是另一个例子，这次使用的是Upper()函数

select vend_name,Upper(vend_name) AS vend_name_upcase
from vendors
order by vend_name;

正如所见，Upper()将文本转换为大写。

常用的文本处理函数

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

SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法

10.2日期和时间处理函数

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

其中，有一种情况，如果你想检索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;

10.3数值处理函数

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

11.汇总数据

11.1聚集函数

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

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

11.2AVG函数

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

select AVG(prod_price) AS avg_price
from products;

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

11.3COUNT()函数

COUNT()函数的两种使用方式

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

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

select COUNT(*) AS num_cust
from customers;

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

select COUNT(cust_email) AS num_cust
from customers;

11.4MAX()函数

select MAX(prod_price) AS max_price
from products;

一般用来找出最大日期或数值，忽略NULL行

11.5MIN()函数

select MIN(prod_price) AS min_price
from products;

与MAX()相反

11.6SUM()函数

检索订购物品总数：

select SUM(quantity) AS items_ordered
from orderitems
Where order_num = 20005;

可在多个列之间执行算术运算，忽略NULL值

11.7聚集不同值

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

• 对所有执行计算，指定ALL参数或者不给参数（ALL默认）
• 只包含不同值，指定DISTINCT参数

使用AVG()函数返回特定供应商提供产品的平均价格：

select AVG(DISTINCT prod_price) AS avg_price
from products
where vend_id = 1003;

注意：如果指定列名，则DISTINCT只能用于COUNT(),不能用于COUNT(*)。

11.8组合聚集函数

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;

12.分组数据

12.1创建分组（GROUP BY）

分组是在select语句的GROUP BY子句中建立的。理解分组的例子如下：

select vend_id,COUNT(*) AS num_prods
from products
GROUP BY vend_id;

分析：上面的select语句指定了两个列，vend_id包含产品供应商ID，num_prods为计算字段。GROUP BY子句指示MySQL按vend_id排序并分组数据。这导致对每个vend_id而不是整个表计算num_prods一次。

因为使用了GROUP BY，就不必指定要计算和估值的每个组了。系统会自动完成。

关于GROUP BY的一些使用规定：

• GROUP BY子句可以包含任意数目的列。
• GROUP BY子句中列出的每个列都必须是检索列或者是有效表达式，**但是不可以是聚集函数。**如果在select中使用了表达式，那么GROUP BY子句中指定相同的表达式，不能使用别名。
• 除聚集计算语句外，select语句中的每个列都必须在GROUP BY子句中给出。
• 如果分组列中具有NULL值，则NULL将作为一个分组返回。有多个NULL值则将它们分为一组。
• GROUP BY子句必须在WHERE子句之后

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

select vend_id,COUNT(*) AS num_prods
from products
GROUP BY vend_id with rollup;

12.2过滤分组（HAVING）

例如，可能想要列出至少两个订单的所有顾客。为了得出这种数据，那必须基于完整的分组而不是个别的分组进行过滤。这个时候并不能使用where子句，因为where过滤的是指定的行而不是分组。

这时候就要用到HAVING子句，having和where非常相似，语法基本一致，唯一不同的是，where用于过滤行，而having用于过滤分组。

select cust_id,COUNT(*) AS orders
from orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;

那么，有没有在一条语句中同时使用where和having的情况呢？答案是肯定的

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

select vend_id,count(*) AS num_prods
from products
where prod_price >= 10
GROUP BY vend_id
having count(*) >= 2

12.3分组和排序

虽然group by和order by经常完成相同的工作，但是它们非常不同。如下：

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

一般在使用group by子句时，应该也给出order by子句。这是保证数据正确排序的唯一方法。

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

select order_num,SUM(quantity*item_price) AS ordertotal
from orderitems
group by order_num
having SUM(quantity*item_price) >= 50
order by ordertotal;

12.4select子句顺序

顺序如下：

子句（顺序）	说明	是否必须使用
select	要返回的列或表达式	是
from	从中检索数据的表	仅在从表选择数据时使用
where	行级过滤	否
group by	分组说明	仅在按组计算聚集时使用
having	组级过滤	否
order by	输出排序顺序	否
limit	要检索的行数	否

13.使用子查询

子查询：即嵌套查询

13.1利用子查询进行过滤

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

（1）检索包含TNT2的所有订单编号

select order_num
from orderitems
where prod_id = 'TNT2';

（2）检索具有前一步骤列出的订单编号的所有用户ID

select cust_id
from orders
where order_num IN (20005,20007)

那么以上两个步骤其实可以组合起来，将第一个查询嵌套到第二个里面

select cust_id
from orders
where order_num IN(
    select order_num
    from orderitems
    where prod_id = 'TNT2'
);

（3）检索前一步骤返回所有用户ID的客户信息

select cust_name,cust_contact
from customers
where cust_id IN (10001,10004);

那么其实第三步也可以继续嵌套

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

对于嵌套数目没有限制，但是考虑性能，不宜嵌入过多

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

使用子查询的另一种方法是创建计算字段。假设需要显示customers表中每个客户的订单总数。订单与相应的客户ID存储在orders表中。

（1）从customers表中检索客户列表

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

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.cust_id = customers.cust_id，这里用来完全限定列名，相关子查询：涉及外部查询的子查询。

14.联结表（多表联查）

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

14.1创建联结

联结的创建非常简单，规定要联结的所有表以及它们如何关联即可。

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将返回比想要的数据多得多的数据，务必保证where子句的正确性。

14.2内部联结(INNER 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;

通过inner join……on……实现

14.3联结多个表

SQL对于一条select语句中可以联结的表的数目没有限制。创建联结表的规则也基本相同。

select prod_name,vend_name,prod_price,quantity
from orderitems,products,vendors
where products.vend_id = vendors.vend_id
	AND orderitems.prod_id = products.prod_id
	AND order_num = 20005;

性能考虑：联结的表越多，性能下降越厉害

15.创建高级联结

15.1使用表别名

select cust_name,cust_contact
from customers AS c,orders AS o,orderitems AS oi
where c.cust_id = o.cust_id
	AND oi.order_num = o.order_num
	AND prod_id = 'TNT2';

15.2使用不同联结

15.2.1自联结

如前所述，使用表别名的主要原因之一是能在单条select语句中不止一次引用相同的表。下面举一个例子：

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

法一（子查询嵌套）：

select prod_id,prod_name
from products
where vend_id = (select vend_id from products where prod_id = 'DTNTR');

法二（自联结）：

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

15.2.2自然联结

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

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

select c.*,o.order_num,o.order_date,
		oi.prod_id,oi.quantity,oi.item_price
from customers AS c,orders AS o,orderitems AS oi
where c.cust_id = o.cust_id
	AND oi.order_num = o.order_num
	AND prod_id = 'FB';

正常情况下，内部联结都是使用自然联结

15.2.3外部联结（LEFT\RIGHT JOIN 左联结和右联结）

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

例子：检索所有客户及其订单

法一（内部联结）：

select customers.cust_id,orders.order_num
from customers INNER JOIN orders
ON customers.cust_id = orders.cust_id;

但是该结果不包括NULL

法二（外部联结）：

select customers.cust_id,orders.order_num
from customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;

分析：

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

select customers.cust_id,orders.order_num
from customers RIGHT OUTER JOIN orders
ON orders.cust_id = customers.cust_id;

外部联结的类型：左联结和右联结，它们之间唯一差别是所关联的表顺序不同。换句话说，左外部联结可以通过from或where子句中表的顺序转换为右外部联结。因此，两种类型的外部联结可互换使用，而究竟使用哪一种纯粹是根据方便而决定。

15.3使用带聚集函数的联结

检索所有客户及每个客户所下的订单数

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 customers.cust_name,
		customers.cust_id,
		COUNT(orders.order_num) AS num_ord
from customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id
group by customers.cust_id;

15.4使用联结和联结条件

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

16.组合查询

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

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

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

组合查询和多个where实现的效果大多数时候是一致的，但是性能有所差异，下面会说到。

16.1创建组合查询（使用UNION）

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

例子：假如需要价格小于等于5的所有物品的一个列表，而且还想包括供应商1001和1002所生产的物品（不考虑价格）

创建UNION涉及编写多条select语句，所以先看单条语句怎么写：

select vend_id,prod_id,prod_price
from products
where prod_price <= 5;

select vend_id,prod_id,prod_price
from products
where vend_id IN (1001,1002);

为了组合这两条语句，按如下

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

作为对比，现给出多个where的查询方法：

select vend_id,prod_id,prod_price
from products
where prod_price <= 5
OR vend_id IN (1001,1002);

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

16.2UNION使用规则

• 两条或者两条以上select
• 每个查询必须包含相同的列、表达式或聚集函数
• 列数据类型必须兼容

16.3包含或取消重复的行

UNION从查询结果集中自动去除重复的行，这是UNION的默认行文，但如果需要改变，可以改变它。

**如果想匹配所有的行，使用UNION ALL。**在使用多个where子句查询时，也会自动去除重复的行，因此UNION ALL就显得非常重要，可以匹配所有行。

select vend_id,prod_id,prod_price
from products
where prod_price <= 5
UNION ALL
select vend_id,prod_id,prod_price
from products
where vend_id IN (1001,1002);

16.4对组合查询结果排序

使用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;

16.5UNION小结

上述关于union的操作都只是组合同一张表的数据，但实际上union是可以组合不同的表。

17.全文本搜索

首先，并非所有引擎都支持全文本搜索，最常用的引擎为MyISAM和InnoDB，前者支持全文本搜索，而后者不支持。这也就是，本文章的创建的多个样例表都使用InnoDB引擎，只有productnotes表使用MyISAM的原因。

前面介绍LIKE和正则表达式用于匹配，虽然这些搜索机制都非常有效，但是存在几个重要限制。

• 性能——通配符和正则表达式匹配通常要求MySQL尝试匹配表中的所有行（而且这些搜索极少使用表索引）。因此，由于被搜索行数不断增加，这些搜索可能非常耗时。
• 明确控制——对于一个词，你可以非常明确要怎么匹配，但是对于文本就不行了。
• 智能化结果

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

在索引之后，select可与Match()和Against()一起使用以实际执行搜索。

17.1启用全文本搜索支持

**一般在创建表时启用全文本搜索，**create table语句接受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;

这些列中有一个名为note_text的列，为了进行全文本搜索，MySQL根据子句FULLTEXT(note_text)的指示对它进行索引。这里的FULLTEXT索引单个列，如果需要也可以指定多个列。

在定义之后，MySQL自动维护该索引。在增加、更新或删除行时，索引随之自动更新。

可以在创建表时指定FULLTEXT，或者在稍后指定（在这种情况下所有已有数据必须立即索引）。

不要在导入数据时使用FULLTEXT，更新索引要花时间，虽然不是很多，但毕竟要花时间。

17.2进行全文本搜索

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

例子：

select note_text
from productnotes
where Match(note_text) Against('rabbit');

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

传递给Match()的值必须与FULLTEXT()定义的相同。如果指定多个列，则必须列出它们（且次序正确）

事实上面的搜索也可以用LIKE完成：

select note_text
from productnotes
where note_text LIKE '%rabbit%';

但是次序不同（虽然并不是总是出现这种情况）

上述两个句子都不包含order by，但是LIKE的排序不是那么好。两行都包含rabbit，但包含词rabbit作为第3个词的等级比作为第20个词的行高，这很重要，应该排在前面。全文本搜索的一个重要部分就是对结果进行排序。具有较高等级的行先返回。

为演示排序如何工作，请看下面例子：

select note_text
		Match(note_text) Against('rabbit') AS ranks
from productnotes;

由此可以看出两个句子的优先级。

排序多个搜索项：如果指定多个搜索项，则包含多数匹配词的那些行将比具有包含较少词的那些行等级高

正确所见，全文本搜索比LIKE更强大。

17.3使用扩展查询（with query expansion）

你想找出所有提到anvils的注释。只有一个注释包含anvlis，但你还想找出可能与你搜索有关的所有其他行，即使它们不包含词anvils。

这也是查询扩展的一项任务。在使用查询扩展时，MySQL对数据和缩影进行两遍扫描来完成搜索：

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

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

例子：

（1）只进行全文本搜索，不用查询扩展

select note_text
from productnotes
where Match(note_text) Against('anvils');

（2）使用查询扩展

select note_text
from productnotes
where Match(note_text) Against('anvils' with query expansion);

行越多越好，表示查询扩展的效果越好

17.4布尔文本搜索（in boolean mode）

布尔搜索细节：

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

即使没有fulltext索引也可以使用

select note_text
from productnotes
where Match(note_text) Against('heavy' in boolean mode);

以上例子与全文本搜索并无区别，那么请看下面的例子，将使用排斥某些词

select note_text
from productnotes
where Match(note_text) Against('heavy -rope*' in boolean mode);

-rope*明确指示MySQL排除任何以rope开始的词。

全文本布尔操作符

下面举几个例子，说明某些操作符如何使用

（1）搜索匹配包含词rabbit和bait的行

select note_text
from productnotes
where Match(note_text) Against('+rabbit +bait' in boolean mode);

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

select note_text
from productnotes
where Match(note_text) Against('rabbit bait' in boolean mode);

（3）搜索匹配短语rabbit bait而不是匹配两个词rabbit和bait

select note_text
from productnotes
where Match(note_text) Against('"rabbit bait"' in boolean mode);

（4）匹配rabbit和carrot，增加前者等级，降低后者等级

select note_text
from productnotes
where Match(note_text) Against('>rabbit <carrot' in boolean mode);

（5）搜索匹配词safe和combination，降低后者等级

select note_text
from productnotes
where Match(note_text) Against('+safe +(<combination)' in boolean mode);

17.5全文本搜索注意事项

• 在索引全文本时短词（3/3个一下字符的词将被忽略），但是可以修改。
• MySQL带有一个内建的非用词（stopword）列表，这些词在索引全文本时总是被忽略，但也可修改。
• 50%规则，有些词频繁出现，频率在50%以上的将被忽略，但是50%规则不适用于in boolean mode。
• 表行数少于3行，则全本搜索不返回结果
• 忽略词中单引号（例：don’t索引为dont）
• 不具词分隔符（包含日语和汉语）的语言不能恰当的返回全文本搜索结果
• 仅在MyISAM数据库引擎中支持

18.插入数据

18.1插入完整的行(insert into)

法一：直接插入，不太安全

insert into Customers
values(NULL,
      'zhangsan',
      '100 main street',
      'Los Angeles',
      'CA',
      '90046',
      'CN',
      NULL,
      NULL);

法二：给出列名再插入，繁琐，但安全

insert into customers(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email)
values('lisi',
      '100 main street',
      'Los Angeles',
      'CA',
      '90046',
      'CN',
      NULL,
      NULL);

提高整体性能

数据库经常被多个用户访问，对于处理什么请求以及用什么次序处理进行管理是MySQL的任务。insert操作可能很耗时（特别是有很多索引需要跟新时），而且他可能降低等待处理select语句的性能。

如果检索数据是重要的（通常是这样），则你可以通过再insert和into之间添加关键字low_priority，指示MySQL降低insert语句的优先级，如下：

insert LOW_PRIORITY into

关于索引更新的解释：

18.2插入多个行

insert into customers(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email)
values('wangwu',
      '100 main street',
      'Los Angeles',
      'CA',
      '90046',
      'CN',
      NULL,
      NULL),
	  ('zhaoliu',
      '100 main street',
      'Los Angeles',
      'CA',
      '90046',
      'CN',
      NULL,
      NULL);

用单条insert插入多条数据可以提高性能，比起插入多条数据，每条数据用一个insert要快

18.3插入检索出的数据

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

insert into customers(cust_id,cust_contact,cust_email，cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country)
select cust_id,cust_contact,cust_email，cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country
from custnew;

select在中也同样可使用where子句进行过滤操作

19.更新和删除数据

19.1更新数据（update）

为了更新（修改）表中的数据，可使用update语句。可采用两种方法更新：

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

不要省略where句子，使用update的时候要小心，一不小心就可能更新整个表

非常简单，使用步骤如下：

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

例子

update customers
set cust_email = 'elmer@1632.com'
where cust_id = 10005;

更新多个列时，也只需一个set

update customers
set cust_name = 'xixihaha',
	  cust_email = 'elmer@1632.com'
where cust_id = 10005;

update在更新多行时，如果其中一行出错，则全部取消。若想要出错又继续更新，可用ignore关键字

update ignore customers……

19.2删除数据（delete）

为了删除（去掉）表中的数据，可使用delete语句。可采用两种方法删除：

• 从表中特删除定行
• 从表中删除所有行

不要省略where句子，使用delete的时候要小心，一不小心就可能删掉整个表的行

非常简单，例子如下：

delete from customers
where cust_id = 10006;

关于清空表：truncate table 是更快的删除语句，原理是删除原来的表，重新建一个表

20.创建和操纵表

20.1表的基础创建

使用create table关键字进行创建，创建时要注意表名是否已经存在

其中，表的主键可以用primary key指定

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

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

当然，还可以指定每列的默认值，通过default关键字实现。与大多数DBMS一样，MySQL不允许使用函数作为默认值，它只支持常量。

quantity   int   NOT NULL  default 1

20.2使用NULL值

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

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

理解NULL：不要把NULL值与空串混淆。NULL是没有值，空串是一个有效值

20.3主键再介绍

主键可以不仅一列，可以由多列组合而成，只要保证组合结果不一致就行。

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

20.4引擎类型

数据库引擎用ENGINE=来指定，如果不指定，大概率会是MyISAM。

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

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

数据库引擎类型可以混用，但是外键不能跨引擎，即使用一个引擎的表不能引用具有不同引擎表的外键。

20.5更新表

为更新表定义，可使用alter table语句。

alter table vendors
add vend_phone char(20);

这条语句给vendors表增加了vend_phone列

如果想要删掉，可以这样做

alter table vendors
drop column vend_phone;

alert table常用于添加外键使用：

alter table orderitems
add constraint fk_ordertiems_orders
foreign key (order_num) references orders(order_num)

使用alter table要特别小心，最好先备份一个表，因为操作不可撤销！！！

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

• 用新的列布局创建一个新表
• 使用insert select 从旧表将数据复制到新表
• 检验包含所需数据的新表
• 重命名旧表
• 用旧表原来的名字重命名新表
• 根据需要，重新创建触发器、存储过程、索引和外键

20.6删除表

非常简单，但是要谨慎使用，该操作是永久删除，不能撤销：

drop table customers2;

20.7重命名表

使用rename table语句

rename table customers2 to customers;

最后，其实这些操作都比较繁琐，但是现在有很多的数据库可视化操作工具可以帮助我们便捷的完成这些操作，例如navicat……

21.使用视图

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

21.1视图

理解视图的最好方法是看下面这个例子：

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

此查询用来检索订购了某个特定产品的用户，任何需要这个数据的人都必须理解相关表的结构，并且知道如何创建查询和对表进行联结。为了检索其他产品（或多个产品）的相同数据，必须修改最后的where子句。

现在，假如可以把整个查询包装成一个名为productcustomers的虚拟表，则可以轻松的检索出相同的数据：

select cust_name,cust_contact
from productcustomers
where prod_id = 'TNT2';

这就是视图的作用。productcustomers是一个视图，作为视图，它不包含表中应该有的任何列或数据，它包含的是一个SQL查询（与上面用以正确联结表的相同查询）

21.1.2为什么使用视图

• 重用SQL语句
• 简化复杂的SQL操作
• 使用表的组成部分而不是整个表
• 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。
• 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据

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

21.1.2视图的规则和限制

• 命名唯一
• 创建数目没限制
• 可嵌套，即可以利用从其他视图中检索的查询来构造一个视图
• order by可以使用，但如果从该视图检索数据select中也包含order by，那么该视图的order by将被覆盖
• 视图不能索引，也不能有关联的触发器和默认值
• 视图可以和表一起使用，例如，编写一条联结表和视图的select语句

21.2使用视图

• 视图用create view语句创建
• 使用show create view viewname来查看创建视图的语句
• 用drop删除视图：drop view viewname
• 更新视图时，可以先用drop再用create，也可以直接用create or replace view。如果要更新的视图不存在，则第2条更新语句会创建一个视图；如果要更新的视图存在，则第2条语句会替换原有的视图。

21.2.1利用视图简化复杂的联结

视图最常见的应用之一是隐藏复杂的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的视图，那么为检索订购了TNT2的客户，可如下进行：

select cust_name,cust_contact
from productcustomers
where prod_id = 'TNT2';

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

下面代码是我们再第9章使用的

select Concat(rtrim(vend_name),'(',rtrim(vend_country),')') 
AS vend_title
from vendors
order by vend_name;

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

create view vendorlocations AS
select Concat(rtrim(vend_name),'(',rtrim(vend_country),')') 
AS vend_title
from vendors
order by vend_name;

那么，使用视图查询即可

select * from vendorlocations;

21.2.3使用视图过滤不想要的数据

例如，可以定义视图customeremaillist视图，它过滤没有电子邮件地址的客户，如下：

create view customeremaillist AS
select cust_id,cust_name,cust_email
from customers
where cust_email is not null;

那么，使用视图查询

select * from customeremaillist;

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

21.2.4使用视图与计算字段

视图对于简化计算字段的使用非常有效

原操作：

select prod_id,
		quantity,
		item_price,
		quantity*item_price AS expanded_price
from orderitems
where order_num = 20005;

将其转换为一个视图

create view orderitemsexpandeds
select order_num,
		prod_id,
		quantity,
		item_price,
		quantity*item_price AS expanded_price
from orderitems;

为检索订单20005的详细内容，则

select *
from orderitemsexpandeds
where order_num = 20005;

21.2.5更新视图

许多视图都是不可更新的，这听上去像是一个严重的限制，但其实不是的，视图主要是用于数据的检索。

22.使用存储过程

前面我们所使用的大多数SQL语句都是针对一个或多个表的单条语句。并非所有操作都这么简单，经常会有一个完整的操作需要多条语句才能完成。那么，怎么编写此代码？可以单独编写每条语句，并根据结果有条件的执行另外的语句。在每次需要这个处理时（以及每个需要它的应用中）都必须做这些工作。

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

22.1为什么使用存储过程

• 通过把处理封装在容易使用的单元中，简化复杂的操作。
• 由于不要求反复建立一系列处理步骤，这保证了数据的完整性。
• 简化对变动的管理。

换句话说，使用存储过程的主要好处，即简单、安全、高性能。

22.2使用存储过程

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

22.2.1创建存储过程

例子，一个返回产品平均价格的存储过程：

create procedure productpricing()
begin
	select Avg(prod_price) AS priceaverage
	from products;
end;

productpricing后面()是用于传递参数的，虽然这个存储过程没有参数，但是也要把括号打上。

需注意的是，mysql命令行客户机的分隔符：

那如何使用这个存储过程？如下：

call productpricing();

22.2.2删除存储过程

存储过程在创建之后，将被保存在服务器上以供使用，直至被删除。注意：仅当存在时删除，如果指定的过程不存在，则删除会报错！

drop procedure productpricing;

22.2.3使用参数

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

以下是productpricing的修改版本（如果不先删除则无法再次创建）

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指定关键字）。

那么，怎么调用上面这个存储过程呢：

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

在调用时，这条语句并不显示任何数据。它返回以后可以显示（或在其他处理中使用）的变量。

为检索出产品的平均价格，如下：

select @priceaverage;

select @pricelow,@pricehigh,@priceaverage;

下面是另一个例子，这次使用IN和OUT参数。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);

则

select @total;

22.2.4建立智能存储过程

考虑这个场景。你需要获得与以前一样的订单合计，但需要对合计增加营业税，不过只针对某些顾客（或许是你所在州中那些顾客）。那么你需要做下面几件事情：

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

存储过程如下：

-- Name:ordertotal
-- Parameters: onumber = order number
--             taxable = 0 if not taxable,1 if taxable
--             ototaT = order total variable
CREATE PROCEDURE ordertotal(
    IN onumber INT,
    IN taxable BOOLEAN,
    OUT ototal DECIMAL(8,2)
)COMMENT 'Obtain order total，optionaly adding tax'
BEGIN
    -- Declare variable for total
    DECLARE total DECIMAL(8,2);
    -- DecTare tax percentage
    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（她增加或不增加税）保存到ototal。

comment关键字：本例中它不是必须的，但如果给出，将在show procedure status的结果中显示

调用存储过程：

call ordertotal(20005,0,@total);
select @total;

call ordertotal(20005,1,@total);
select @total;

22.2.5检查存储过程

show create procedure ordertotal;

23.使用游标

通过前面的学习可以知道MySQL检索操作返回一组称为结果集的行。这组返回的行都是SQL语句相匹配的行（零行或多行）。使用简单的select语句，例如，没有办法得到第一行、下一行或前十行，也不存在每次一行地处理所有行地简单办法（相对于成批地处理它们）。

有时，需要检索出来地行中前进或后退一行或多行。这就是使用游标的原因。

游标是一个存储在MySQL服务器上的数据库查询，它不是一条select语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。

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

23.1使用游标

步骤：

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

在声明游标后，可根据需要频繁地打开和关闭游标。在游标打开后，可根据需要频繁地执行操作。

23.1.1创建游标

游标用declare语句创建。declare命名游标，并定义相应的select语句，根据需要带where和其他子句。

create procedure processorders()
begin
	declare ordernumbers cursor
	for
	select order_num from orders;
end;

在游标定义后可以打开它。

23.1.2打开和关闭游标

open ordernumbers;

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

游标处理完成后，应当使用如下语句关闭游标：

close ordernumbers;

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

声明过的游标不必再次声明，下次要用直接open即可，如果你不明确关闭游标，MySQL将会在到达end语句时自动关闭它。

注意，MYSQL游标只能用于存储过程（和函数），所以……open cursor语句应当存储过程内，单独会报错。

下面是前面例子的修改版本：

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;

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

23.1.3使用游标数据

在一个游标被打开后，可以使用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的局部声明的变量中。对检索出的数据不做任何处理。

在下一个例子中，循环检索数据，从第一行1到最后一行：

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由于没有更多的行供循环而不能继续时，出现这个条件。

为了把这些内容组织起来，再次变样：

DELIMITER //

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 //

DELIMITER ;

在这个例子中，我们增加了另一个命名为t的变量（存储每个订单的合计）。此存储过程还在运行中创建了一个新表（如果它不存在的话），名为ordertotals。这个表将保存存储过程生成的结果。fetch像以前一样取每个order_num，然后用call执行另一个存储过程来计算每个订单的带税合计（结果存储到t）。最后，用insert保存每个订单的订单号和合计。

此存储过程不返回数据，但它能够创建和填充另一个表。

call processorders();
select *
from ordertotals;

这样，我们就得到了存储过程、游标、逐行处理以及存储过程调用其他存储过程的一个完整工作样例。

24.使用触发器

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

这时就要用到触发器了，触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句（或位于begin和end之间的一组语句）

• delete
• insert
• update

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

24.1创建触发器

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

• 唯一的触发器名
• 触发器关联的表
• 触发器应该响应的活动（delete、insert、update）
• 触发器何时执行（处理之前或之后）

保持触发器在每个表中的名唯一，触发器仅支持表，视图不支持。触发器按每个表事件每次定义，每个表每个事件每次只允许一个触发器。因此，每个表最多支持6个触发器（每条insert、update、delete之前或之后）。单一触发器不能与多个事件或多个表联结。

触发器用create trigger创建

create trigger newproduct after insert on products
for each row select 'Product added' INTO @asd;

创建了名为newproduct的触发器，这里after表示在处理之后触发。这个触发器还指定for each row，因此代码对每个插入行执行。

在这个例子中，文本Product added将对每个插入的行显示一次。

24.2删除触发器

drop trigger newproduct;

触发器不能更新或者覆盖，为了修改一个触发器，只能删除了再建。

24.3使用触发器

24.3.1insert触发器

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

create trigger neworder after insert on orders
for each row select NEW.order_num INTO @order_num;

测试

insert into orders(order_date,cust_id)
values(Now(),10001);
SELECT @order_num;

24.3.2delete触发器

在delete触发器代码内，可引用一个名为OLD的虚拟表，访问被删除的行。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_order的存档表中（为实际使用这个例子，你需要用到与orders相同的列创建一个名为archive——orders的表）

使用before delete触发器的有点：（相对于after delete触发器来说）由于某种原因，订单不能存档，delete本身也将被放弃。

24.3.3update触发器

在update触发器代码内，可引用一个名为OLD的虚拟表访问以前（update语句前）的值，引用一个名为NEW的虚拟表访问新更新的值。OLD中的值都是只读，不能更新。

下面例子，保证州名缩写总是大写（不管update语句中给出的是大写还是小写）

create trigger updatevendor before update on vendors
for each row set NEW.vend_state = Upper(NEW.vend_state);

25.管理事务处理

并非所有引擎都支持事务处理，MyISAM和InnoDB是两种最常见的搜索引擎，前者不支持明确的事务管理，而后者支持。如果你的应用中需要事务处理功能，则一定要使用正确的引擎类型。

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

下面通过两个例子给大家介绍一下：

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

25.1控制事务处理

管理事务处理的关键在于将SQL语句组分解为逻辑块，并明确规定数据何时改回退，何时不该回退。

MySQL使用下面的语句来标识事务的开始：

start transaction

25.2使用rollback

MySQL的rollback命令用来回退（撤销）MySQL语句，请看下面的语句：

-- 注意逐条执行
select * from ordertotals;
start transaction;
delete from ordertotals;
select * from ordertotals;
rollback;
select * from ordertotals;

这个例子从显示ordertotals表的内容开始。首先执行一条select以显示该表不为空。然后开始一个事务处理，用一条delete语句删除ordertotals中所有的行。另一条select语句验证ordertotals确实为空。这时用一条rollback语句回退start transaction之后的所有语句，最后一条select语句显示该表不为空。

显然，rollback只能在一个事务处理内使用（在执行一条start transaction命令之后）

那么那些语句可以会退呢？事务处理用来管理insert、update、delete语句，但是不能回退select语句，因为其实这样做也没有什么意义。需要特别注意的是：create和drop操作无法回退！！！

25.3使用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语句执行后，事务会自动关闭（将来的更改会隐含提交）。

25.4使用保留点

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

例如，前面描述的添加订单的过程为一个事务处理。如果发生错误，只需要返回到添加orders行之前即可，不需要回退到customers表（如果存在的话）。

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

这些占位符称为保留点。为了创建占位符，可可如下使用savepoint语句：

savepoint delete1;

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

rollback to delete1;

保留点越多越好！！！保留点越多，你就越能按自己的意愿灵活地进行回退

释放保留点：保留点在事务处理完成（执行一条rollback或commit）后自动释放。

25.5更改默认的提交行为

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

set autocommit = 0;

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

autocommit是针对每个连接，而不是服务器的。

26.安全管理

用户不能有过多的权限，什么类型的程序员就给他配什么权限。所谓访问控制就是需要创建和管理用户账号。

如果用MySQL语句去操作这些会比较麻烦，所以建议大家使用图形化工具Navicat工具去实现管理。这样会非常便捷高效。

27.数据库维护

27.1备份数据

像所有数据一样，MySQL的数据也必须经常备份。

这里同样也是可以使用Navicat进行备份。

27.2进行数据库维护

• analyze table，用来检查表键是否正确。analyze table返回的状态信息如下：

  analyze table orders;
  

• check table用来针对许多问题对表进行检查。在MyISAM表上还对索引进行检查。check table支持一系列的用于MyISAM表的方式。changed检查最后一次检查依赖改动过的表。extended执行最彻底的检查，fast只检查未关闭的表，medium检查所被删除的链接并进行键检验，quick只进行快速扫描。如下所示，check table发现和修复的问题：

check table orders,orderitems;

• 如果MyISAM表访问产生不正确和不一致的结果，可能需要用repair table来修复相应的表。这条语句不应该经常使用，如果经常需要使用，可能会有更大的问题要解决。
• 如果从一个表中删除大量数据，应该使用optimize table来收回所用的空间，从而优化表的性能。

27.3诊断启动问题

下面是几个重要的mysqld命令行选项：

–help 显示帮助——一个选项列表

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

–verbose 显示全文消息（配合–help使用）

–version显示版本信息然后退出

27.4查看日志文件

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语句刷新和重新开始所有日志文件。

28.改善性能

28.1改善性能

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

相信你学到这里，已经掌握MySQL了，给自己点个赞！