MySQL基础《必知必会》笔记

MySQL基础：《必知必会》

（一）、正则表达式

一、 正则表达式

1. 基本匹配：

   • select name from user where name REGEXP ‘1000’ ;

     记录为： admin 1000 , hello 1000，10000 user ;

     匹配 含有‘1000’的数据

   • ’ . ’ 正则特殊字符，表示匹配任意一个字符：from user where name REGEXP ‘.000’ ;

     记录为：admin 1000 ，admin 2000

2. OR 匹配：’ | ’ 符为正则OR操作符，表示匹配其中之一

   • select name from user where name REGEXP ‘1000|2000’ ;

     记录为： admin 1000 ，admin 2000

3. 匹配多个字符：

   REGEXP ’ [123] admin ’ 表示：匹配 ‘ 1 admin’ 或‘2 admin’ 或者‘ 3 admin’ ；

   与OR的区别是假如 ‘1|2|3 admin’表示匹配：‘1’或‘2’或‘3 admin’ 。

4. 匹配范围：

   REGEXP ‘[1-5] admin’ ;与上述相同，表示匹配1到5 admin；

5. 匹配特殊字符： 如 . 及 | 和 [ ]等。需要使用 ‘\’ 作为转译：

   如: 匹配 ‘admin .’ 。语句为 REGEXP ’ admin \\.’ 。

6. 匹配字符类：
   

7. 匹配多个实例：如匹配 连在一起的4位数字的数据：REGEXP ‘[[:digit:]]{4}’ ;
   

8. 定位符：如 搜索以 数字或‘ . ’开始的数据：REGEXP ‘^[0-9\\.]’ ;
   

二、 与 like的区别

• REGEXP与LIKE的不同点在于：LIKE匹配整个串而REGEXP匹配子串。

（二）、函数

一、 Concat（）拼接函数; 以及RTrim() 和LTrim（）去除右/左 空格

如 select Concat( username,’(’, contry , ‘)’ ) from user ;

则记录为 admin(USA) ; hello (CHINA)

（三）、分组 Group By … having…

• where 关键字过滤指定的行而不是分组。事实上，where没有分组的概念
• having： 所有类型的where子句都可以用having来替代。唯一的差别是where过滤行，而having过滤分组。
• having支持所有where操作符。句法相同，关键字不同
• having与where的区别：where在数据分组前进行过滤，having在数据分组后进行过滤。这是一个重要区别，where排除的行不包括在分组中。这可能会改变计算值，从而影响having子句中基于这些值过滤掉的分组。

（四）、select 子句顺序

• select
• from
• where
• group by
• having
• order by
• limit

（五）、联结表

笛卡尔积

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

联结多个表性能

• MySQL在运行时关联指定的每个表以处理联结。这种处理可能是非常耗费资源的，因此应该仔细，不要联结不必要的表。联结的表越多，性能下降越厉害。
• 为执行任一给定的SQL操作，一般存在不止一种方法。很少有绝对正确或绝对错误的方法。性能可能会受到操作类型、表中数据量、是否存在索引或键以及其他一些条件的影响。因此，有必要对不同的选择机制进行试验，以找出最合适具体情况的方法。

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

（六）、组合查询（union）

• 组合查询和多个where条件 ：多数情况下，组合相同表的两个查询完成的工作与具有多个where子句条件的单条查询完成的工作相同。换句话说，任何具有多个where子句的select语句都可以作为一个组合查询给出。这两种技术在不同的查询中性能也不同。因此，应该试一下 这两种技术，以确定对特定的查询哪一种性能更好。

1. union使用

组合数条SQL查询。将多条select语句，将它们的结果组合成单个结果集。

例如：select * from user where id >5 union select * from user where age > 20 ;（union会从结果集中自动去除重复的行;若想要返回所有匹配行，可使用union all 而不是union）

等同于： select * from user where id >5 or age >20 ;

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

union与where 的联系与区别

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

**注：用 order by子句排序。在用union组合查询时，只能使用一条order by子句，且必须出现在最后一条select 语句之后。**对于结果集，不存在用一种方式排序一部分，而又用另一种方式排序另一部分的情况，因此不允许使用多条order by子句。

（七）、理解全文本搜索（MyISAM）

两个常用引擎MyISAM和InnoDB，仅MyISAM支持全文搜索。

• 常规如 like关键字利用通配符匹配文本或 REGEXP 正则表达式匹配；虽然这些搜索机制非常有用，但存在几个重要的限制：
  1. 性能— 通配符和正则表达式匹配通常要求MySQL尝试匹配表中所有行（而且这些搜索极少使用表索引）。因此，由于被搜索行数不断增加，这些搜索可能非常耗时。
  2. 明确控制— 使用通配符和正则表达式匹配，很难（而且并不总能）明确地控制匹配什么和不匹配什么。例如，指定一个词必须匹配，一个词必须不匹配，而一个词仅在第一词确定匹配的情况下才可以匹配或者才可以不匹配。
  3. 智能化的结果— 虽然基于通配符和正则表达式的搜索提供了非常灵活地搜索，但他们都不能提供一种智能化的选择结果的方法。例如，一个特殊词的搜索将会返回包含该词的所有行，而不区分包含单个匹配的行和包含多个匹配的行（按照可能是更好的匹配来排列他们）。类似，一个特殊词的搜索将不会找出不包含该词但包含其他相关词的行。

所以这些限制以及更多的限制都可以用全文搜索来解决。

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

1. 使用全文本搜索 （fulltext）

create table user(
	id int not null auto_increment ,
    name char(10) not null ,
    date datetime not null ,
    text text null ,
    primary key(id),
    fulltext(text)
)engine=MyISAM ;

**注：不要在导入数据时使用fulltext； 更新索引要花时间。

2. 进行全文本搜索

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

如：select text from user WHERE Match(text) Against(‘hello’) ;

• 传递给Match（）的值必须与fulltext定义中的相同。如果指定多个列，则必须列出它们（而且次序正确）。
• 除非使用 BINARY方式，否则全文本搜索不区分大小写。

3. 使用查询扩展

在使用查询扩展时，MySQL对数据和索引进行两边扫描来完成搜索：

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

使用查询扩展：select text from test where Match(text) Against(‘hello’ with query expansion) ;

查询扩展极大地增加了返回的行数，但这样做也增加了实际上并不想要的行的数目。

行越多越好： 表中的行越多（这些行中的文本就越多），使用查询扩展返回的结果越好。

4. 布尔文本搜索

布尔方式：

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

注：即使没有 fulltext 索引也可以使用 ；布尔方式不用与迄今为止使用的全文本搜索语法的地方在于，即使没有定义 fulltext 索引，也可以使用它。但这是一种非常缓慢的操作（其性能将随着数据量的增加而降低）。

使用： select text from texts where Match(text) Against(‘hello -admin’ in boolen mode); （匹配hello ，排除admin）

5. 全文本搜索的使用说明

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

（八）、插入，更新，删除

一、 插入

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

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

** INSERT LOW_PRIORITY INTO

二、删除

delete 语句从表中删除行，删除表中所有行。但是，delete不删除表本身 。

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

三、 更新

在update语句中使用子查询： update语句中可以使用子查询，使得能用select语句检索出的数据更新列数据。

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

（九）、创建和操纵表

1. 创建表

• **理解NULL值：**不要把NULL值与空串相混淆。NULL值是没有值，它不是空串。如果指定 ‘’ （两个单引号，期间没有字符），这在 NOT NULL 列中是允许的。空串是一个有效的值，它不是无值。NULL值用关键字NULL而不是空串指定。
• 获取最后插入主键的id： select **last_insert_id() **;
• 不允许函数： 与大多数DBMS不一样，MySQL不允许使用函数作为默认值，它只支持常量 。
• 引擎类型：（引擎可以混用，但外键不能跨引擎）
  1. InnoDB：事务处理引擎。不支持全文搜索；
  2. MyISAM：性能极高的引擎，支持全文本搜索，但不支持事务处理 ；
  3. Memory：功能等同于MyISAM，但由于数据存储在内存（不是磁盘）中，速度快（特别适合于临时表） ；

（十）、视图

一、 什么是视图

视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。它包含的不是数据而是根据需要检索数据的查询。视图提供了一种MySQL的 select 语句层次的封装，可用来简化数据处理以及重新格式化基础数据或保护基础数据 。

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

视图常见应用：

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

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

二、 视图的规则和限制

• 与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的名字） 。
• 对于可以创建的视图数目没有限制 。
• 为了创建视图，必须具有足够的访问权限。这些限制通常有数据库管理人员授予。
• 视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图 。
• 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条更新语句会替换原有视图 。

# 创建视图
create view productcustomers AS
select cust_name ,cust_contact,prod_id
from customers , orders , orderitems
where customers.cust_id = order.cust_id
and orderitems.order_num = orders.order_num ;

# 使用视图
select * from productcustomers ;

• 创建可重用的视图： 创建不受特定数据限制的视图是一个种好办法。例如，上面创建的视图返回生产所有产品的客户而不仅仅是生产 TNT2的客户。扩展视图的范围不仅使得它能被重用，而且甚至更有用。这样做不需要创建和维护多个类似视图 。
• 将视图用于检索： 一般，应该将视图用于检索（select 语句）而不用于更新（insert、update 和 delete）。

通常，视图是可更新的（即，可以对它们使用 insert、update和 delete）。但并非所有视图都是可更新的。基本上可以说，如果MySQL不能正确地确定被更新的基数据，则不允许更新（包括插入和删除）。如果视图定义中有以下操作，则不能进行视图的更新：

1. 分组（使用 group by 和 having ）；
2. 联结 ；
3. 子查询 ；
4. 并 ；
5. 聚集函数（Min() 、Count() 、Sum() 等）;
6. distinct ;
7. 导出（计算）列 。

（十一）、存储过程

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

1. 为了处理订单，需要核对以保证库存中有相应的物品 。
2. 如果库存有物品，这些物品需要预定以便不将它们再卖给别人，并且要减少可用的物品数量以反映正确地库存量 。
3. 库存中没有的物品需要订购，这需要与供应商进行某种交互 。
4. 关于哪些物品入库（并且可以立即发货）和哪些物品退订，需要通知相应的客户 。

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

可以单独编写每条语句，并根据结果有条件地执行另外的语句。在每次需要这个处理时（以及每个需要它的应用中）都必须做这些工作。

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

一、 为什么要使用储存过程

1. 通过把处理封装在容易使用的单元中，简化复杂的操作 ；

2. 由于不要求反复建立一系列处理步骤，这保证了数据的完整性。

   如果所有开发人员和应用程序都使用同一（试验和测试）存储过程，则所使用的代码都是相同的。

   这一点的延伸就是防止错误。需要执行的步骤越多，出错的可能性就越大。防止错误保证了数据的一致性。

3. 简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化，只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化 。

   这一点的延伸就是安全性。通过存储过程限制对基础数据的访问减少数据讹误（无意识的或别的原因所导致的数据讹误）的机会。

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

5. 存在一些只能用单个请求中的MySQL元素和特性，储存过程可以使用它们来编写功能更强更灵活地代码 。

存储过程有3个重要的好处，即简单、安全、高性能。但在将SQL代码转换为存储过程前，也必须知道它的一些缺陷。

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

• 不能编写存储过程？你依然可以使用： MySQL将编写存储过程的安全和访问与执行存储过程的安全和访问区分开来。这是好事情。即使你不能（或不想）编写自己的存储过程，也仍然可以在适当的时候执行别的存储过程 。

二、 储存过程的使用

1. 存储过程的执行

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

call productpricing（@pricelow , @pricelhigh , @priceaverage ）;

2. 存储过程的创建

create procedure productpricing()
begin
	select avg(prod_price) as priceaverage
	from products ;
end ;

此存储过程名为 priductpricing ，用create procedure productpricing（） 语句定义。begin 和 end 语句用来限定存储过程体，过程本身仅是一个简单的 select 语句。

在MySQL处理这段代码时，它创建了一个新的存储过程 product-pricing 。没有返回数据，因为这段代码并未调用存储过程，这里只是为以后而创建它。

• mysql命令行客户机分隔符： 默认使用 MySQL 语句分隔符为；号。mysql命令行使用程序也使用；号作为分割符 。如果命令行使用 程序要解释存储过程自身内的；字符，则他们最终不会成为存储过程的成分，这会使存储过程中的SQL出现句法错误。

  #解决方法: 临时更改命名行使用程序的语句分隔符
  delimiter //
  create procedure productpricing()
  begin
  	select avg(prod_price) as priceaverage
  	from products ;
  end //
  delimiter ; 
  #最后恢复原来的语句分隔符， 可使用 delimiter ； 
  

  除 \ 符号外，任何字符都可以用作语句分隔符 。

• 使用：call productpricing () ;
  

3. 存储过程的删除

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

删除命令（if exists : 如果过程不存在也不会报错）： drop procedure productpricing if exists；

三、 使用参数

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

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

#decimal(8,2)表示保留8位有效数字，其中保留小数点后2位 
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 ;

关键字 out 指出相应的参数用来从存储过程传出一个值（返回给调用者）。MySQL支持 IN（传递给储存过程）、out（从储存过程传出）和 inout （对存储过程传入和传出）类型的参数。存储过程的代码位于 begin 和 end 语句内，它们是一系列select 语句 ，用来检索值，然后保存到相应的变量（通过指定 into 关键字）

• 调用：由于存储过程要求3个参数，因此必须正好传递3个参数。

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

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

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

​ select @priceaverage , @pricehigh ;

又如：使用 in 和 out 参数

#接收一个int类型的参数 ，并返回ototal
create pricedure ordertotal(
	in onumber int ,
    out ototal decimal(8,2)
)
begin 
	select Sum(item_price*quantity) 
	from orderitems
	where order_num = onumber 
	into ototal ;
end ;

调动： call ordertotal（20005 ，@total）；

显示： select @total ；

为了得到另一个订单需要再次

调用：call ordertotal （200009 ， @total）；

显示：select @total ；

四、 建立智能存储过程

只有在存储过程内包含业务规则和智能处理时，他们的威力才真正显现出来 。如以下场景：

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

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

#Name：ordertotal
#参数：	onumber = order number
#是否增税：	taxable = 0 if not taxable ，1 if taxable
#合计：	ototal = order total variable 

create procedure ordertotal(
	in onumber int ,
    int taxable boolean ,
    out ototal decimal(8,2)
)comment 'hello'
begin
	declare total decimal(8,2) ;
	declare taxrate int default 6 ;
	
	select Sum(item_price * quantity) from orderitems
	where order_num = onumber 
	into total ;
	
	if taxable then 
		select total+(total/100*taxrate) into total ;
	end if ;
	
	select total into ototal ;
	
end ;

用 declare 语句定义了两个局部变量 。 if 语句检查taxable 是否为真，如果为真，则用另一个 select 语句增加营业税到局部变量 total 。最后用 select 语句 将total（它增加或许不增加营业税） 保存到ototal 。

注：comment关键字： 本例子中的存储过程在 create procedure 语句中包含了一个 comment 值。 它不是必需的， 但如果给出，将在show procedure status 的结果中显示 。

if语句： 这个例子给出了 MySQL的if语句的基本用法。if 语句还支持 elseif 和 else 子句（前者还使用 then 子句 ， 后者不使用）。

五、 检查存储过程

我了显示用来创建一个存储过程的 create 语句，使用 show create procedure 语句 ：

show create procedure ordertotal ；

限制过程状态结果： show procedure status 列出所有存储过程。为限制其输出，可使用 like 指定一个过滤模式 ，例如：show procedure status like ‘ordertotal’ ；

（十二）、游标

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

**游标（cursor）**是一个存储在MySQL 服务器上的数据库查询，它不是一条select 语句 ，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

游标主要用于交互应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或作出更改 。

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

一、 使用游标

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

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

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

二、 创建游标

游标用 **declare **语句创建。并定义相应的select 语句，根据需要带where 和其他子句。例，定义了名为 ordernumbers 的游标 ，使用了可以检索所有订单的 select 语句 。

# declare语句用来定义和命名游标，这里为 ordernumbers 。
# 存储过程处理完成后，游标就消失（因为它局限于存储过程）。
# 在定义游标之后，可以打开它 
create procedure processorders()
begin 
	declare ordernumbers CURSOR 
	for
	select order_num from orders ;
end ;

三、 打开和关闭游标

游标使用 open cursor 语句打开 ；

open ordernumbers ;

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

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

close  ordernumbers ;

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

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

四、使用游标数据

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

例 1： 从游标中检索单个行（第一行）

create procedure processorders()
begin
	-- 声明局部变量
	declare o INT ;
	-- 声明 游标
	declare ordernumbers CURSOR ;
	FOR
	select order_num from orders ;
	
	-- 打开游标
	open the cursor ;
	-- 获取 order_num 
	fetch ordernumbers INTO o ;
	-- 关闭 游标
	close ordernumbers ;
end ;

其中 fetch 用来检索当前行的order_num 列（将自动从第一行开始）到一个名为o的局部声明的变量中。对数据不做任何处理。

例 2： 循环检索数据，从第一行到最后一行

create procedure processorders()
begin
	-- 声明局部变量
	declare done boolean default 0 ;
	declare o INT ;
	
	-- 声明游标
	declare ordernumbers CURSOR 
	FOR 
	select order_num from orders ;
	-- 声明 continue handler
	declare continue handler FOR SQLSTATE '02000' set done = 1 ;
	
	-- 打开游标
	open ordernumbers；
	-- 遍历所有行
	repeat 
		-- 获取order_number
		fetch ordernumbers INTO o ;
	-- 结束遍历
	until done end repeat ;
	-- 关闭游标
	close ordernumbers ;
end ;

使用 fetch 检索当前order _num到声明的名为 o 的变量中。但与前一个例子不一样的是，这个例子中的 fetch是在 repeat内，因此它反复执行直到done为真（until done end repeat；规定）。使用 default 0（假，不结束）定义变量done 。

语句： declare continue handler for sqlstate ‘02000’ set done = 1 ; 该语句定义了一个continue handler ，它在条件出现时被执行的代码。这里，指定 sqlstate ‘02000’ 出现时，set done = 1 。 sqlstate ‘02000’ 是一个未找到条件 ，当repeat由于没有更多的行供循环而不能继续时，出现这个条件 。

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

注：重复或循环？： 除了这里使用的 repeat 语句外，MySQL还支持循环语句（while循环、loop循环），它可用来重复执行代码，知道使用 leave 语句手动退出为止。通常repeat 语句的语法使它更适合于对游标进行循环。

例 3： 对取出的数据进行某种实际的处理

create procedure processorders()
begin
	-- 声明局部变量
	declare done boolean default 0 ;
	declare o INT ;
	-- decimal(8,2)：保留精度为8，即保留8位有效数字，其中保留小数点后2位
	declare t DECIMAl(8,2);
	
	-- 声明游标
	declare ordernumbers CURSOR 
	for
	select order_num from orders ;
	-- 声明 continue handler 
	declare continue handler for sqlstate '02000' set done = 1 ;
	
	-- 创建一张表来存储结果
	create table if not exists ordertotals
	(order_num INT , total DECIMAL(8,2));
	
	-- 打开 游标
	open ordernumbers ;
	-- 遍历所有行
	repeat 
		-- 获取 order_number 
		retch ordernumbers INTO o ;
		-- 获取 total ；获取订单总数(上一章：四、 建立智能存储过程)
		call ordertotal(o,1,t);
		-- 插入order 和 total 到 ordertotals
		insert into ordertotals(order_num ,total) 
		values(o,t) ;
	-- 结束循环
	until done end repeat ;
	-- 关闭 游标
	close ordernumbers;
end ;
	

t 变量为存储每个订单的合计。此存储过程还在运行中创建了一个新表（如果不存在）名为 ordertotals 。这个表将保存存储过程生成的结果。fetch 像以前一样取每个order_num,然后用call 执行另一个存储过程（上一章，四、 建立智能存储过程示例）来计算每个订单的带税的合计（结果存储到 t ）。最后，用 insert 保存每个订单的订单号和合计 。（此过程不返回数据，但它创建和填充另一个表，可以使用简单的查询语句：select * FROM ordertotals；查询该表）

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

（十三）、触发器

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

• 每当增加一个顾客到某个数据库表时，都检查其电话号码格式是否正确，州的缩写是否为大写；
• 每当订购一个产品时，都从库存数量中减去订购的数量；
• 无论何时删除一行，都在某个存档表中保留一个副本。

这些例子的共同之处是它们都需要在某个表发生更改时自动处理。确切的说就是触发器。触发器是MySQL响应一下任意语句而自动执行的一条MySQL语句（或位于begin 和 end 语句之间的一组语句）：（其他MySQL语句不支持触发器）

• delete
• insert
• update

一、 创建触发器

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

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

注：保持每个数据库的触发器名唯一： 在MySQL5中，触发器名必须在每个表中唯一，但不是在每个数据库中唯一。这表示同一数据库中的连个表可具有相同名字的触发器。这表示同一数据库中的两个表可具有相同名字的触发器。这在其他每个数据库触发器名必须唯一的DBMS中是不允许的，而且以后的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将对每个插入的行心显示一次。

为了测试这个触发器，使用insert语句添加一行或多行到 products 中，你将看到对每个成功的插入，显示Product added消息。

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

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

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

二、 删除触发器

使用drop trigger 语句；

drop trigger newproduct ;

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

三、 使用触发器

1. insert 触发器

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

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

例 1： auto_increment列具有MySQL 自动赋予的值

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

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

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

2. delete 触发器

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

• 在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_orders 的存档表中（为实际使用这个例子，你需要用于orders 相同的列创建一个名为 archive_orders的表 ）。

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

3. update 触发器

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

• 在update 触发器代码中，你可以引用一个名为 OLD的虚拟表访问以前（update语句前）的值，引用一个名为NEW 的虚拟表访问新更新的值 ；
• 在 before update 触发器中，NEW 中的值可能也被更新（允许更改将要用于update 语句中的值）；
• OLD 中的值全都是只读的，不能更新 。

**例： ** 保证州名缩写总是大写

create trigger updateevendor before update on vendors
for each row set NEW.vend_state = Upper(New.vend_state）；

4. 关于粗发起的进一步介绍

使用触发器时需要记住的重点。

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

（十四）、管理事务处理

一、 事务处理

注： 并非所有引擎都支持事务处理： MySQL支持几种基本的数据库引擎。正如本章所述，并非所有引擎都支持明确的事务处理管理。 MyISAM和InnoDB是两种最常使用的引擎。前者不支持明确的事务处理管理，而后者支持。这就是为什么本书中使用的样例表被创建来使用InnoDB而不是更经常使用的MyISAM的原因。如果你的应用中需要事务处理功能，则一定要使用正确的引擎类型。

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

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

下面面是关于事务处理需要知道的几个术语：

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

二、 控制事务处理

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

语句：start transaction 来标识事务的开始。

1. 使用rollback

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

select * from ordertotals ;
start transaction ;
delete from ordertotals ;
select * from ordertotals ;
rollback ;
select * from ordertotals ;

注：那些语句可以回退？： 事务处理用来管理 insert 、update 和 delete 语句。你不能回退 select 语句（这样做也没有什么意义）。你不能回退 create 和 drop 操作。事务处理块中可以使用者两条语句，但如果你执行回退，他们不会被撤销。

2. 使用 commit

一般的MySQL语句都是直接针对数据库表执行和编写的。这就是所谓的隐含提交（implicit commit），即提交（写或保存）操作是自动进行的。

但，在事务块处理中，提交不会隐含地进行。为进行明确的提交，使用commit语句

start transaction ;
delete from orderitems where order_num = 20010 ;
delete from orders where order_num = 20010 ;
commit ;

如果第一条delete起作用，但第二条失败，则delete 不会提交（实际上，它是被自动撤销的）。

注：隐含事务关闭： 但commit 或 rollback 语句执行后，事务会自动关闭（将来的更改会隐含提交）。

3. 使用保留点

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

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

这些占位符称为保留点。创建占位符语句： savepoint delete1 ；

每个保留点都取标识它的唯一名字，以便在回退时，MySQL知道要退到何处。

如语句： rollback to delete1 ；

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

注：释放保留点： 保留点在事务处理完成（执行一条rollback或commit）后自动释放。自MySQL 5 以来，也可以用 release savepoint 明确地释放保留点。

4. 更改默认的提交行为

set autocommit = 0 ;

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