《MYSQL必知必会》读书笔记（三）

第十九章 插入数据

使用INSERT语句插入数据，大家都很熟悉。 例如向 Customers 表插入一条name为tom，age为29的数据 一般有以下两种方式：

INSERT INTO Customers VALUES("tom", "29")；
INSERT INTO Customers(name, age) VALUES("tom", "29")；点击复制代码复制出错复制成功

一般推荐第二张方式，因为第一种方式的数据顺序必须与列在表中的数据保持一致，容易写错，其次是当表结构发生改变时，第一种方式需要变更数据顺序，第二种方式不需要。

插入检索出的数据

INSERT一般用来给表插入一个指定列值的行。但是，INSERT还存在 另一种形式，可以利用它将一条SELECT语句的结果插入表中。如下图所示，这个例子使用INSERT SELECT从custnew表中将所有数据导入customers表

第二十章 更新和删除数据

更新数据

使用UPDATE语句更新数据，大家都很熟练了，一般UPDATE语句组成部分如下：

UPDATE 表名
SET 列名 = 新值
WHERE 过滤条件;点击复制代码复制出错复制成功

如下图所示： 需要注意的是：

1.在使用UPDATE语句时，不要省略WHERE子句 ，否则就会更新表中所有行。

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

删除数据

使用DELETE语句更新数据，大家也都很熟练了，一般DELETE语句组成部分如下：

DELETE FROM 表名 WHERE 过滤条件;

如下图所示： 需要注意的是：

1.在使用DELETE语句时，不要省略DELETE子句 ，否则会删除表中所有行。

2.DELETE语句从表中删除行，甚至是删除表中所有行。但是，DELETE不删除表本身。

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

更新和删除的指导原则

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

2.保证每个表都有主键(如果忘记这个内容，请参阅第15章)，尽可能 像WHERE子句那样使用它(可以指定各主键、多个值或值的范围)。

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

4.使用强制实施引用完整性的数据库(关于这个内容，请参阅第15 章)，这样MySQL将不允许删除具有与其他表相关联的数据的行。

5.MySQL没有撤销(undo)按钮。应该非常小心地使用UPDATE和DELETE，否则你会发现自己更新或删除了错误的数据。

第二十一章 创建和操纵表

创建表

使用CREATE语句来创建一个表，大家都很熟悉了，如下图所示

需要注意的有以下几点：

1.在建表时，每一列要么是可为NULL列，要么是NOT NULL列，如果不指定，默认为可为NULL列。

2.主键必须保证唯一，不能为NULL。如果使用一个列作为主键，值必须唯一，如果使用多个列作为主键，那么多个列组合的值必须唯一。

3.MySQL有一个具体管理和处理数据的内部引擎，在执行SQL语句时，可以使用ENGINE语句指定引擎，如果省略ENGINE=语句，则使用默认引擎(很可能是MyISAM)，以下为MySQL常见的几个引擎： InnoDB

是一个可 靠的事 务 处 理 引 擎 ( 参 见 第 26 章 )， 它 不 支 持 全 文 本搜索;

MEMORY

在功能等同于MyISAM，但由于数据存储在内存(不是磁盘) 中，速度很快，所以特别适合于临时表;

MyISAM

是一个性能极高的引擎，它支持全文本搜索(参见第18章)， 但不支持事务处理。

更新表

在表建立以后，如果需要对表结构进行修改，我们可以使用ALTER TABLE语句对表进行修改。例如：

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

1.用新的列布局创建一个新表。

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

3.检验包含所需数据的新表。

4.重命名旧表(如果确定，可以删除它)。

5.用旧表原来的名字重命名新表。

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

删除表

删除表(删除整个表而不是其内容)非常简单，使用DROP TABLE语，例如: 删除customers2表

DROP TABLE customers2;点击复制代码复制出错复制成功

重命名表

使用RENAME TABLE语句可以重命名一个表。 例如： 将表customers2名字改为customers

RENAME TABLE customers2 to customers;点击复制代码复制出错复制成功

第二十二章 使用视图

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

后面就可以把productcustomers视图看成一个虚拟表进行查询，如下图所示：

视图主要的用途：

1.重用SQL语句。

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

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

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

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

视图的规则和限制：

1.与表一样，视图必须唯一命名(不能给视图取与别的视图或表相 同的名字)。

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

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

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

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

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

7.视图可以和表一起使用。例如，编写一条联结表和视图的SELECT语句。

常见的视图操作语句

1.视图用CREATE VIEW语句来创建。

2.使用SHOW CREATE VIEW viewname;来查看创建视图的语句。

3.用DROP删除视图，其语法为DROP VIEW viewname;。

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

我们上面的例子中视图的作用其实是简化复杂SQL的使用，其实视图还有其他的作用，例如：

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

用视图过滤不想要的数据

更新视图

视图是可更新的(也就是可以对它们使用INSERT、UPDATE和DELETE)。更新一个视图将更新其基表(可以回忆一下，视图本身没有数据)。如果你对视图增加或删除行，实际上是对其基表增加或删除行。但是当视图定义中有以下操作时，则不能进行视图的更新:

1.分组(使用GROUP BY和HAVING); 联结;

2.子查询;

3.并;

4.聚集函数(Min()、Count()、Sum()等);

5.DISTINCT;

6.导出(计算)列。

第二十三章 使用存储过程

存储过程，就是可以一条或多条MySQL语句的组合起来，并且可以加入一些业务逻辑。

创建和执行存储过程

简单的示例： 使用CREATE PROCEDURE语句创建一个存储过程，对一个SELECT语句进行封装，之后可以使用CALL语句来执行这个存储过程。

需要注意的是，因为在存储过程中会包含一些;分隔符，而在命令行实用程序中，使用;字符来作为语句分隔符，为了避免语法错误，可以使用DELIMITER语句来定义一个新的语句结束分隔符。如下图所示：

删除存储过程

可以使用DROP PROCEDURE 语句来删除一个存储过程，例如： 删除名为productpricing的存储过程

DROP PROCEDURE productpricing；点击复制代码复制出错复制成功

使用参数

在创建存储过程时，可以使用IN语句来存储传入参数，OUT语句来存储返回结果。 在下面这个例子中，20005是传入参数，@total是返回结果。传入参数和返回结果也可以定义多个。

建立智能存储过程

在创建存储过程中，也可以使用IF，THEN，END IF语句来设置判断条件，这是存储过程与简单的语句封装最大的区别。

例如：

添加了另外一个 参数taxable，它是一个布尔值(如果要增加税则为真，否则为假)。在 存储过程体中，用DECLARE语句定义了两个局部变量。DECLARE要求指定 变量名和数据类型，它也支持可选的默认值(这个例子中的taxrate的默 认被设置为6%)。SELECT语句已经改变，因此其结果存储到total(局部 变量)而不是ototal。IF语句检查taxable是否为真，如果为真，则用另 一SELECT语句增加营业税到局部变量total。最后，用另一SELECT语句将 total(它增加或许不增加营业税)保存到ototal。 BOOLEAN值指定为1表示真，指定为0表示假(实际上，非零值 都考虑为真，只有0被视为假)。通过给中间的参数指定0或1，可以有条件地将营业税加到订单合计上。

检查存储过程

可以使用 SHOW CREATE PROCEDURE 语句显示用来创建一个存储过程的 CREATE 语句也可以使用 SHOW PROCEDURE STATUS 列出所有存储过程。为限制其输出，可使用LIKE指定一个过滤模式，例如:SHOW PROCEDURE STATUS LIKE 'ordertotal';

第二十四章 使用游标

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

创建游标

定义了一个名为ordernumbers的游标

打开和关闭游标

打开名为ordernumbers的游标

OPEN ordernumbers;点击复制代码复制出错复制成功

关闭名为ordernumbers的游标

CLOSE ordernumbers;点击复制代码复制出错复制成功

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

使用游标数据

这个例子使用FETCH检索当前order_num到声明的名为o的变量中。但与前一个例子不一样的是，这个 例子中的FETCH是在REPEAT内，因此它反复执行直到done为真(由UNTIL done END REPEAT;规定)。为使它起作用，用一个DEFAULT 0(假，不结 束)定义变量done。当在 FETCH 语句中引用的游标位置处于结果表最后一行之后时，SQLSTATE会为02000，这个时候done会为真，停止循环。

第二十五章 使用触发器

可以使用触发器是在MySQL响应 INSERT UPDATE DELETE 语句前后自动执行一条MySQL语句。

创建触发器

创建触发器语句的格式一般是 CREATE TRIGGER 触发器名称 触发时机 触发操作 FOR EACH ROW 执行操作; 例如：

CREATE TRIGGER newproduct AFTER INSERT FOR EACH ROW SELECT 'Product added'；点击复制代码复制出错复制成功

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

删除触发器

DROP TRIGGER newproduct;删除名为newproduct的触发器点击复制代码复制出错复制成功

INSERT触发器

1. 在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被 插入的行;
   
2. 在BEFORE INSERT触发器中，NEW中的值也可以被更新(允许更改 被插入的值);
   
3. 对于AUTO_INCREMENT列，NEW在INSERT执行之前列的值会是0，在INSERT 执行之后包含新的自动生成值。 上面的例子中创建一个名为neworder的触发器，它按照AFTER INSERT ON orders执行。在插入一个新订单到orders表时，MySQL生 成一个新订单号并保存到order_num中。触发器从NEW. order_num取得这个值并返回它。
   

DELETE触发器

DELETE触发器在DELETE语句执行之前或之后执行，在DELETE触发器代码内，你可以引用一个名为OLD的虚拟表，访问被删除的行。OLD中的值全都是只读的，不能更新。 上面这个例子中，在任意订单被删除前将执行此触发器。它使用一条INSERT语句将OLD中的值(要被删除的订单)保存到一个名为archive_ orders的存档表中(为实际使用这个例子，你需要用与orders相同的列 创建一个名为archive_orders的表)。

UPDATE触发器

UPDATE触发器在UPDATE语句执行之前或之后执行。在UPDATE触发器代码中，你可以引用一个名为OLD的虚拟表访问 以前(UPDATE语句前)的值，引用一个名为NEW的虚拟表访问新 更新的值。在BEFORE UPDATE触发器中，NEW中的值可能也被更新(允许更改 将要用于UPDATE语句中的值)。OLD中的值全都是只读的，不能更新。 上面面的例子保证州名缩写总是大写(不管UPDATE语句中给出的是大 写还是小写)

注意事项：

1.只有表才支持触发器，视图不支持(临时表也不 支持)。

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

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

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

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

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

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

第二十六章 管理事务处理

事务处理可以用来维护数据库的完整性，它保证一组SQL语句要么完全执行，要么完全不执行。利用事务处理，可以保证一组操作不会中途停止，它们 或者作为整体执行，或者完全不执行(除非明确指示)。如果没有错误发 生，整组语句提交给(写到)数据库表。如果发生错误，则进行回退(撤 销)以恢复数据库到某个已知且安全的状态。 事务(transaction)指一组SQL语句; 回退(rollback)指撤销指定SQL语句的过程; 提交(commit)指将未存储的SQL语句结果写入数据库表; 保留点(savepoint)指事务处理中设置的临时占位符(place- holder)，你可以对它发布回退(与回退整个事务处理不同)。

控制事务处理

事务开始 START TRANSACTION点击复制代码复制出错复制成功

使用ROLLBACK

MySQL的ROLLBACK命令用来回退(撤销)MySQL语句。例如： 从显示ordertotals表(此表在第24章中填充)的内 容开始。首先执行一条SELECT以显示该表不为空。然后开始一 个事务处理，用一条DELETE语句删除ordertotals中的所有行。另一条 SELECT语句验证ordertotals确实为空。这时用一条ROLLBACK语句回退 START TRANSACTION之后的所有语句，最后一条SELECT语句显示该表不为空。 ROLLBACK只能在一个事务处理内使用(在执行一条START TRANSACTION命令之后)。可以回退INSERT、UPDATE和 DELETE语句。不能回退SELECT，CREATE，DROP语句。

使用COMMIT

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

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

使用保留点

简单的ROLLBACK和COMMIT语句就可以写入或撤销整个事务处理。但 是，只是对简单的事务处理才能这样做，更复杂的事务处理可能需要部 分提交或回退。为了支持回退部分事务处理，必须能在事务处理块中合适的位置放 置占位符。这样，如果需要回退，可以回退到某个占位符。

创建占位符

SAVEPOINT delete1;创建一个名称为delete1的占位符
ROLLBACK TO delete1;回退到delete1的占位符点击复制代码复制出错复制成功

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

更改默认的提交行为

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

SET autocommit=0;点击复制代码复制出错复制成功

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

第二十七章 全球化和本地化

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

使用字符集和校对顺序

show CHARACTER SET;点击复制代码复制出错复制成功

可以展示可用的字符集，MySQL 默认字符集是latin1，一般我们常用的就是utf8

show COLLATION;点击复制代码复制出错复制成功

可以展示所支持校对以及它们适用的字符集的完整列表，有的字符集具有不止一种校对。

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

show VARIABLES like 'character%'；查看字符集相关的配置
show VARIABLES like 'collation%'；查看校对相关的配置点击复制代码复制出错复制成功

给表指定字符集和校对

这个例子中指定了CHARACTER SET和COLLATE两者。一般，MySQL如 下确定使用什么样的字符集和校对。

1.如果指定CHARACTER SET和COLLATE两者，则使用这些值。

2.如果只指定CHARACTER SET，则使用此字符集及其默认的校对(如SHOW CHARACTER SET的结果中所示)。

3.如果既不指定CHARACTER SET，也不指定COLLATE，则使用数据库默认。

对列指定字符集和校对

查询时指定校对顺序

此SELECT使用COLLATE指定一个备用的校对顺序(在这个例子 中，为区分大小写的校对)。除了这里看到的在ORDERBY子 句中使用以外，COLLATE还可以用于GROUP BY、HAVING、聚集 函数、别名等。

第二十八章 安全管理

MySQL用户账号和信息存储在名为mysql的MySQL数据库中。 获得所有用户账号列表

创建用户账号

CREATE USER ben IDENTIFIED BY 'passwOrd'; CREATE USER创建一个新用户账号。在创建用户账号时不一定需要密码，不过这个例子用IDENTIFIED BY 'passwOrd'给出了一个密码。

重新命名一个用户账号

RENAME USER ben TO bforta;点击复制代码复制出错复制成功

删除用户账号

DROP USER bforta;点击复制代码复制出错复制成功

设置访问权限

为看到赋予用户账号的权限，使用SHOW GRANTS FOR，如下图所示: 输出结果显示用户bforta有一个权限USAGE ON .。此结果表示在任意数据库和任意表上对任何数据没有权限。 用户定义为user@host MySQL的权限将会把用户名和主机名结合定义。如果不指定主机名，则使用默认的主机名%(授予用户访问权限而不管主机名)。

GRANT语句

GRANT语句的一般格式是 GRANT 权限 ON 范围 TO 用户; 例如：

GRANT SELECT ON crashhouse.* TO bforta;点击复制代码复制出错复制成功

GRANT允许用户在crashcourse.*(crashcourse数据库的所 有表)上使用SELECT语句。

SHOW GRANTS可以用来显示bforta用户的权限

REVOKE

GRANT的反操作为REVOKE，用它来撤销特定的权限。

REVOKE SELECT ON crashhouse.* FROM bforta;点击复制代码复制出错复制成功

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

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

1.整个服务器，使用GRANT ALL和REVOKE ALL;

2.整个数据库，使用ON database.*;

3.特定的表，使用ON database.table;

4.特定的列;

5.特定的存储过程。

下面是可以授予或撤销的每个权限：

注意事项:

提前授权

在使用GRANT和REVOKE时，用户账号必须存在， 但对所涉及的对象没有这个要求。这允许管理员在创建数据库 和表之前设计和实现安全措施。这样做的副作用是，当某个数据库或表被删除时(用DROP语 句)，相关的访问权限仍然存在。而且，如果将来重新创建该 数据库或表，这些权限仍然起作用。

更改密码

更改特定用户的密码

SET PASSWORD FOR bforta = Password('123456');点击复制代码复制出错复制成功

更改当前用户的密码

SET PASSWORD = Password('123456');