《MySQL必知必会》--第21-25章

第21章--创建和操纵表

21.1 创建表

21.1.1 表创建基础

处理现有的表 在创建新表时，指定的表名必须不存在，否则将出错。如果要防止意外覆盖已有的表，SQL要求首先手工删除该表（请参阅后面的小节），然后再重建它，而不是简单地用创建表语句覆盖它。
如果你仅想在一个表不存在时创建它，应该在表名后给出IFNOT EXISTS。这样做不检查已有表的模式是否与你打算创建的表模式相匹配。它只是查看表名是否存在，并且仅在表名不存在时创建它。

21.1.2 使用NULL值

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

21.1.3 主键再介绍

如果主键使用单个列，则它的值必须唯一。如果使用多个列，则这些列的组合值必须唯一。
为创建由多个列组成的主键，应该以逗号分隔的列表给出各列名，如下所示：
CREATE TABLE orderitems(
order_num int NOT NULL,
order_item int NOT NULL,
prod_id int NOT NULL,
quantity int NOT NULL,
item_price decimal(8, 2) NOT NULL,
PRIMARY KEY (order_num, order_item)
)ENGINE=InnoDB;

21.1.4 使用AUTO_INCREMENT

每个表只允许一个AUTO_INCREMENT列，而且它必须被索引（如，通过使它成为主键）。
覆盖AUTO_INCREMENT 如果一个列被指定为AUTO_INCREMENT，则它需要使用特殊的值吗？你可以简单地在INSERT语句中指定一个值，只要它是唯一的（至今尚未使用过）即可，该值将被用来替代自动生成的值。后续的增量将开始使用该手工插入的值。
确定AUTO_INCREMENT值 让MySQL生成（通过自动增量）主键的一个缺点是你不知道这些值都是谁。
考虑这个场景：你正在增加一个新订单。这要求在orders表中创建一行，然后在orderitms表中对订购的每项物品创建一行。order_num在orderitems表中与订单细节一起存储。这就是为什么orders表和orderitems表为相互关联的表的原因。这显然要求你在插入orders行之后，插入orderitems行之前知道生成的order_num。
那么，如何在使用AUTO_INCREMENT列时获得这个值呢？可使用last_insert_id()函数获得这个值，如下所示：
SELECT last_insert_id()
此语句返回最后一个AUTO_INCREMENT值，然后可以将它用于后续的MySQL语句。

21.1.5 指定默认值

如果在插入行时没有给出值，MySQL允许指定此时使用的默认值。默认值用CREATE TABLE语句的列定义中的DEFAULT关键字指定。
不允许函数 与大多数DBMS不一样，MySQL不允许使用函数作为默认值，它只支持常量。
使用默认值而不是NULL值 许多数据库开发人员使用默认值而不是NULL列，特别是对用于计算或数据分组的列更是如此。

21.1.6 引擎类型

与其他DBMS一样，MySQL有一个具体管理和处理数据的内部引擎。在你使用CREATE TABLE语句时，该引擎具体创建表，而在你使用SELECT语句或进行其他数据库处理时，该引擎在内部处理你的请求。多数时候，此引擎都隐藏在DBMS内，不需要过多关注它。
但MySQL与其他DBMS不一样，它具有多种引擎。它打包多个引擎，这些引擎都隐藏在MySQL服务器内，全都能执行CREATE TABLE和SELECT等命令。
为什么要发行多种引擎呢？因为它们具有各自不同的功能和特性，为不同的任务选择正确的引擎能获得良好的功能和灵活性。
当然，你完全可以忽略这些数据库引擎。如果省略ENGINE=语句，则使用默认引擎（很可能是MyISAM），多数SQL语句都会默认使用它。但并不是所有语句都默认使用它，这就是为什么ENGINE=语句很重要的原因（也就是为什么本书的样列表中使用两种引擎的原因）。
以下是几个需要知道的引擎：

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

引擎类型可以混用。除productnotes表使用MyISAM外，本书中的样例表都使用InnoDB。原因是作者希望支持事务处理（因此，使用InnoDB），但也需要在productnotes中支持全文本搜索（因此，使用MyISAM）。

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

那么，你应该使用哪个引擎？这有赖于你需要什么样的特性。MyISAM由于其性能和特性可能是最受欢迎的引擎。但如果你不需要可靠的事务处理，可以使用其他引擎。

21.2 更新表

为更新表定义，可使用ALTER TABLE语句。但是，理想状态下，当表中存储数据以后，该表就不应该再被更新。在表的设计过程中需要花费大量时间来考虑，以便后期不对该表进行大的改动。
添加列
ALTER TABLE vendors ADD vend_phone CHAR(20);
删除列
ALTER TABLE DROP COLUMN vend_phone;
ALTER TABLE的一种常见用途是定义外键。
ALTER TABLE orderitems 
ADD CONSTRAINT fk_orderitems_orders 
FOREIGN KEY (order_num) REFERENCES orders (order_num);
小心使用ALTER TABLE 使用ALTER TABLE要极为小心，应该在进行改动前做一个完整的备份（模式和数据的备份）。数据库表的更改不能撤销，如果增加了不需要的列，可能不能删除它们。类似地，如果删除了不应该删除的列，可能会丢失该列中的所有数据。

21.3 删除表

21.4 重命名表

RENAME TABLE customers2 TO customers;

第22章--使用视图

22.1 视图

视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。
作为视图，它不包含表中应该有的任何列或数据，它包含的是一个SQL查询（与上面用以正确联结表的相同的查询）。

22.1.1 为什么使用视图

下面是视图的一些常见应用。

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

在视图创建之后，可以用与表基本相同的方式利用它们。可以对视图执行SELECT操作，过滤和排序数据，将视图联结到其他视图或表，甚至能添加和更新数据（添加和更新数据存在某些限制。关于这个内容稍后还要做进一步的介绍）。
重要的是知道视图仅仅是用来查看存储在别处的数据的一种设施。视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的。在添加或更改这些表中的数据时，视图将返回改变过的数据。
性能问题 因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时所需的任一个检索。如果你用多个联结和过滤创建了复杂的视图或者嵌套了视图，可能会发现性能下降得很厉害。因此，在部署使用了大量视图的应用前，应该进行测试。

22.1.2 视图的规则和限制

下面是关于视图创建和使用的一些最常见的规则和限制。

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

22.2 使用视图

在理解什么是视图（以及管理它们的规则及约束）后，我们来看一下视图的创建。

• 视图用CREATE VIEW语句来创建。
• 使用SHOW CREATE VIEW viewname；来查看创建视图的语句。
• 用DROP删除视图，其语法为DROP VIEW viewname;。
• 更新视图时，可以先用DROP再用CREATE，也可以直接用CREATE OR REPLACE VIEW。如果要更新的视图不存在，则第2条更新语句会创建一个视图；如果要更新的视图存在，则第2条更新语句会替换原有视图。

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

视图的最常见的应用之一是隐藏复杂的SQL，这通常都会涉及联结。
可以看出，视图极大地简化了复杂SQL语句的使用。利用视图，可一次性编写基础的SQL，然后根据需要多次使用。

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

如上所述，视图的另一常见用途是重新格式化检索出的数据。

22.2.3 用视图过滤不想要的数据

22.2.4 使用视图与计算字段

22.2.5 更新视图

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

第23章--使用存储过程

23.1 存储过程
23.2 为什么要使用存储过程
23.3 使用存储过程
MySQL称存储过程的执行为调用，因此MySQL执行存储过程的语句为CALL。CALL接受存储过程的名字以及需要传递给它的任意参数。
23.3.2 创建存储过程
23.3.3 删除存储过程
23.3.4 使用参数
23.3.5 建立智能存储过程
23.3.6 检查存储过程

第24章--使用游标

24.1 游标

有时，需要在检索出来的行中前进或后退一行或多行。这就是使用游标的原因。游标（cursor）是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。
游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。
只能用于存储过程 不像多数DBMS，MySQL游标只能用于存储过程（和函数）。

24.2 使用游标

第25章--使用触发器

25.1 触发器

MySQL语句在需要时被执行，存储过程也是如此。但是，如果你想要某条语句（或某些语句）在事件发生时自动执行，怎么办呢？例如：
所有这些例子的共同之处是它们都需要在某个表发生更改时自动处理。这确切地说就是触发器。触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句（或位于BEGIN和END语句之间的一组语句）：

• DELETE；
• INSERT；
• UPDATE。

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

25.2 创建触发器

• 在创建触发器时，需要给出4条信息：
• 唯一的触发器名；
• 触发器关联的表；
• 触发器应该响应的活动（DELETE、INSERT或UPDATE）；
• 触发器何时执行（处理之前或之后）。

保持每个数据库的触发器名唯一 在MySQL 5中，触发器名必须在每个表中唯一，但不是在每个数据库中唯一。这表示同一数据库中的两个表可具有相同名字的触发器。这在其他每个数据库触发器名必须唯一的DBMS中是不允许的，而且以后的MySQL版本很可能会使命名规则更为严格。因此，现在最好是在数据库范围内使用唯一的触发器名。
触发器用CREATE TRIGGER语句创建。下面是一个简单的例子：
CREATE TRIGGER newproduct AFTER INSERT ON products FRO EACH ROW SELECT 'Product added';
在这个例子中，文本Product added将对每个插入的行显示一次。
仅支持表 只有表才支持触发器，视图不支持（临时表也不支持）。
触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器。因此，每个表最多支持6个触发器（每条INSERT、UPDATE和DELETE的之前和之后）。单一触发器不能与多个事件或多个表关联，所以，如果你需要一个对INSERT和UPDATE操作执行的触发器，则应该定义两个触发器。
25.3 删除触发器
25.4 使用触发器