MySQL必知必会

《MySQL 必知必会》读书笔记

@posper 2021/8/3

第 1 章 了解 SQL

数据库术语

• 数据库（database） 保存有组织的数据的容器（通常是一个文件或一组文件）。类似，文件柜
• 数据库软件应称为DBMS（数据库管理系统），用来创建和操纵数据库
• 表（table） 某种特定类型数据的结构化清单。类似，文件柜中的文件
  • 同一个数据库中的表名具有唯一性
• 模式（schema） 用于描述数据库和表的布局及特性的信息
• 列（column） 表中的一个字段
• 数据类型（datatype） 所容许的数据的类型。每个表列都有相应的数据类型
• 行（row） 表中的一个记录

主键

• 主键（primary key）一列（或一组列），其值能够唯一区分表中每个行。

虽然并不总是都需要主键，但最好还是使用主键，，以便于以后的数据操纵和管理

主键值规则

1. 任意两行都不具有相同的主键值；唯一性
2. 每个行都必须具有一个主键值（主键列不允许NULL值）。非空性

主键通常定义在表的一列上，但这并不是必需的，也可以一起使用多个列作为主键。

多个列作为主键时，规则为：，所有列值的组合必须是唯一的（但单个列的值可以不唯一）。

主键的最好习惯

1. 不更新主键列中的值；
2. 不重用主键列的值；
3. 不在主键列中使用可能会更改的值。

SQL

SQL 是结构化查询语言（Structured Query Language）的缩写

SQL 作用：是一种专门用来与数据库通信的语言。

SQL 的优点：

1. 通用，可移植性强；
2. 简单易学；
3. 强有力（可以灵活使用其他语言）

第 2 章 MySQL简介

什么是MySQL

• 名称由来：“My”，不是“我的”，而是因为 MySQL 的设计者 Monty Widenius 可爱的女儿叫做 My

• MySQL 是一种 DBMS，即它是一种数据库软件

• MySQL 的优点：

  • 开源、免费（这个香）
  • 性能高（MySQL执行很快，很快啊~）
  • 简单（易安装、易使用）

客户机—服务器软件

• DBMS可分为两类：一类为基于共享文件系统的DBMS，另一类为基于客户机—服务器的DBMS。
  • 前者代表软件，诸如Microsoft Access和FileMaker。用于桌面用途，通常不用于高端或更关键的应用
  • MySQL、Oracle以及Microsoft SQL Server等数据库是基于客户机—服务器的数据库
• 服务器软件：与数据文件打交道，用于完成数据添加、删除和数据更新
• 客户机：与用户打交道的软件，用于发出数据增/删/改的请求（比如，请求一个按字母顺序列出的产品表）

这些活动具有透明性。即，数据存储哪里、服务器如何完成这一请求是隐藏的。

• 服务器软件：为 MySQL DBMS

>

>

• MySQL 工具：是基于客户机—服务器DBMS 的 MySQL 的客户机，常见工具有以下 3 种：
  • mysql 命令行实用程序 【重点掌握】
    • MySQL 后自带的，没有图形界面，只能通过命令行操纵；
    • win + R 进入 dos，输入 mysql -u root -p，即可进入mysql命令行实用程序
    • 命令用 ;或 \g 结束（不能直接 Enter）
    • 输入 help 或 \h 获得帮助
    • 输入 quit 或 exit 退出命令行实用程序
  • MySQL Administrator
    • 图形交互客户机，用来简化MySQL服务器的管理。
    • 不会跟随 MySQL 一起自动安装，须单独从 http://dev.mysql.com/downloads/ 下载
  • MySQL Query Browser
    • 图形交互客户机，用来编写和执行 MySQL命令。
    • 不会跟随 MySQL 一起自动安装，须单独从 http://dev.mysql.com/downloads/ 下载

第 3 章 使用MySQL

• 连接所需信息：

  • 主机名（本地，为localhost）
  • 端口（默认 3306）
  • 一个合法的用户名
  • 用户口令（如果需要）。

• Windows 本地连接 MySqL 操作

  • win + R 进入 dos，输入 mysql -u root -p，即可进入mysql命令行实用程序

• 常用命令：

  • 查看所有可用数据库：show databases;

  • 选择数据库：use 数据库名;

  • 查看选择的数据库中所有可用的表：show tables;

  • 查看指定表中的列定义：

    • show columns from 表名;

    • 等价于 describe 表名;

• 以上命令的注意点（2 点）：

  1. 命令不区分大小写；
  2. 结尾一定加英文 ;

• 其他 SHOW 语句

>

>

第 4 章 检索数据

使用SELECT语句从表中检索一个或多个数据列

• SELECT 语句用途：是从一个或多个表中检索信息

• 检索单列：SELECT 列名 FROM 表名

• 检索单列：SELECT 列名1, 列名2, ... FROM 表名

• 检索所有列：SELECT * FROM 表名

  • note：只有当的确需要检索所有列，才使用 * 通配符（即，慎用 *）；否则，检索所有列，会降低检索的效率

• 检索不同的行：SELECT DISTINCT 列名1, 列名2, ... FROM 表名

  • 使用 DISTINCT 关键字，只返回不同的值（即，根据 DISTINCT 后面的列名（可以有多列）去重行）
  • 不能部分使用DISTINCT：如果 DISTINCT 后面有多列，则DISTINCT关键字应用于所有列而不仅是前置它的列

• 限制结果：使用 LIMIT 子句，限定返回第一行或前几行

  • 用法：

    1. 一个参数，从第一行开始：SELECT 列名 FROM 表名 LIMIT 待返回行数；

    2. 两个参数，从指定行开始：SELECT 列名 FROM 表名 LIMIT startRow, 待返回行数；

  • Note:

    1. 检索出来的第一行为行0而不是行1。即，行索引从 0 开始；
    2. 在行数不够时，LIMIT中指定要检索的行数为检索的最大行数。即，LIMIT 行数大于 maxRow 时不会报错，而是最多检索到最后一行

  • LIMIT 4 OFFSET 3 与 LIMIT 3, 4 等价，意为从行3开始取4行

• 使用完全限定的表名：数据库名.表名

• 使用完全限定的列名：表名.列名

注意：

1. 单条SQL语句后需要加分号，多条SQL语句必须以分号（；）分隔。但是，mysql命令行，必须加上分号来结束SQL语句；
2. SQL语句不区分大小写。但习惯上，SQL关键字大写，而对列和表名使用小写；
3. SQL语句所有空格都被忽略。可以将 SQL 语句分为多行，提高可读性

第 5 章 排序检索数据

使用SELECT语句的ORDER BY子句，根据需要排序检索出的数据

• 直接 SELECT 语句的检索顺序：

  • 如果不排序，数据一般将以它在底层表中出现的顺序显示：
    • 如果未进行增/删行，即为数据最初添加到表中的顺序；
    • 如果数据后来进行过更新或删除，则此顺序将会受到MySQL重用回收存储空间的影响。此时，SELECT的检索顺序，没有意义

• 根据一列排序：SELECT 列名 FROM 表名 ORDER BY 待排序的列;

• 根据多列排序：SELECT 列名 FROM 表名 ORDER BY 待排序的列1, 待排序的列2, ...;

  • 根据多列排序时，只有当第一列的属性值相同时，才会比较第二列，以此类推…

• 排序方向：

  • 升序：ASC，默认升序，所以 ASC 通常不写
  • 降序：DESC，必须明确指明

• ORDER BY子句的位置

  • ORDER BY子句，必须位于FROM子句之后；
  • LIMIT 子句，必须位于ORDER BY之后

  子句的次序不对将产生错误消息

  /** 检索价格最贵的物品 */
  SELECT prod_price
  FROM products
  ORDER BY prod_price DESC
  LIMIT 1;
  

第 6 章 过滤数据

使用SELECT语句的WHERE子句指定搜索条件（本章仅为单一条件）

• 搜索条件：也称过滤条件
• WHERE子句的位置：让ORDER BY位于WHERE之后，否则将会产生错误
• WHERE 操作符

注意：

• 等于是 ‘=’，不是 ‘==’；
• 串类型的值需要加引号 ‘’，数值型不用引号 ‘’

• 检索单个值：SELECT 列名 FROM 表名 WHERE 搜索条件;

• 范围检查：SELECT 列名 FROM 表名 WHERE 列名 BETWEEN min AND max;

  /** 检索价格在5美元和10美元之间的所有产品 */
  SELECT prod_name, prod_price
  FROM products
  WHERE prod_price BETWEEN 5 AND 10;
  

• 空值检查：

  /** 检索具有 NULL 值的列 */
  SELECT cust_id
  FROM customers
  WHERE cust_email IS NULL;
  

在过滤数据时，一定要验证返回数据中确实给出了被过滤列具有NULL的行。 （有点困惑？？？）

第 7 章 数据过滤

使用组合WHERE子句以建立功能更强的更高级的搜索条件（多个），以及如何使用NOT和IN操作符。

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

• AND 操作符：满足全部条件

• OR 操作符：满足一个条件即可

• 计算次序：AND 的计算优先级大于 OR

  • 在同时具有 AND 和 OR 子句的搜索条件中，应该使用圆括号 () 明确地分组操作符；否则，可能造成检索错误

  /**
  * 检索由供应商1003制造的任何价格为10美元（含）以上的产品，或者由供应商1002制造的任何产品；
  * 使用默认计算次序，会返回错误的检索信息
  */
  SELECT prod_name, prod_price
  FROM products
  WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10;
  
  /**
  * 检索价格为10美元（含）以上且由1002或1003制造的所有产品；
  * 使用圆括号 () 改变优先级
  */
  SELECT prod_name, prod_price
  FROM products
  WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;
  

• IN操作符

  • 作用：用来指定条件范围，范围中的每个条件都可以进行匹配；
  • 语法：WHERE 列名 IN (condition1, condition2, ...)
  • 等价形式：WHERE 列名 = condition1 OR 列名 = condition2 OR ...)
  • IN 操作符优点（和 OR 操作符相比）：
    1. IN 操作符更清楚、直观；
    2. IN 操作符更易管理；
    3. IN 操作符一般比OR操作符执行更快；
    4. IN的最大优点是可以包含其他SELECT语句，使得能够更动态地建立WHERE子句（见 ch14）

• NOT操作符

  • 作用：否定它之后所跟的任何条件；
  • MySQL支持使用NOT 对IN 、BETWEEN 和EXISTS子句取反

  /** 检索除1002和1003之外的所有供应商制造的产品 */
  SELECT prod_name, prod_price
  FROM products
  WHERE vend_id NOT IN (1002, 1003);
  

第 8 章 用通配符进行过滤

使用LIKE操作符进行通配搜索，以便对数据进行复杂过滤。

• 通配符（wildcard） 用来匹配值的一部分的特殊字符；

• 搜索模式（search pattern）由字面值、通配符或两者组合构成的搜索条件；

• LIKE操作符，后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较

  • LIKE是谓词而不是操作符

• 百分号（%）通配符：

  • % 表示任何字符出现任意次数（即，>= 0）；
  • % 可以置于模式串的任意位置（前、中、后），一个模式中可以有多个 %
  • 注意：% 不能用来匹配列值为 NULL 值的行

• 下划线（_）通配符：与 % 类似，但是 _ 只能匹配一个字符

  /** 匹配单个（一个）字符开头，且以" ton avil"结尾的产品 */
  SELECT prod_name, prod_price
  FROM products
  WHERE prod_name LIKE '_ ton avil';
  
  /** 匹配任意（0,1,2,...都可）字符开头，且以" ton avil"结尾的产品 */
  SELECT prod_name, prod_price
  FROM products
  WHERE prod_name LIKE '% ton avil';
  

• 使用通配符的技巧

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

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

使用正则表达式来更好地控制数据过滤

• 正则表达式作用：用来匹配文本的特殊的串（字符集合）

• MySQL 中的正则表达式仅为正则表达式语言的一个子集

• MySQL 中 REGEXP 后所跟的东西为正则表达式

• 基本字符匹配

  • SELECT 待查列名... FROM 表名 WHERE 限定列名 REGEXP '待匹配串'
  • 正则表达式中的 .，表示匹配任意一个字符

• | 操作符

  • | 是正则表达式的 OR 操作符，只匹配其中一个
  • 用法：WHERE 列名 REGEXP '模式串1|模式串2|...'

• [和]

  • 作用：匹配特定字符（只匹配一个字符）
  • 用法：[123]

• 匹配范围

  • 集合：用来匹配一个或多个字符
  • 匹配数字：[0-9]
  • 匹配字母：[a-z]

• 匹配特殊字符

  • MySQL 特殊字符：-, |, [, ], \
  • MySQL 中 \\ 转义（两个反斜杠）特殊字符

• 匹配字符类

  

• 匹配多个实例

  

• 定位符

  

• MySQL 中的正则表达式不区分大小写。如果要区分大小写，需要使用 BINARY 关键字

  /** 匹配产品名中包含 'JetPack .00' 的产品（使用BINARY，区分大小写） */
  SELECT prod_name
  FROM products
  WHERE prod_name REGEXP BINARY 'JetPack .00';
  

• 在LIKE和REGEXP之间有一个重要的差别

  • LIKE匹配整个列。如果被匹配的文本在列值中出现，LIKE将不会找到它，相应的行也不被返回（除非使用
    通配符）；
  • 而REGEXP在列值内进行匹配，如果被匹配的文本在列值中出现，REGEXP将会找到它，相应的行将被返回

• ^ 的双重用途

  1. 在集合中（用[和]定义），用它来否定该集合；
  2. 否则，用来指串的开始处

第 10 章 计算字段

如何创建计算字段以及怎样从应用程序中使用别名引用它们

• 何时需要使用计算字段？

  • 存储在表中的数据都不是应用程序所需要的。我们需要直接从数据库中检索出转换、计算或格式化过的数据时，需要使用计算字段

• 计算字段并不实际存在于数据库表中。计算字段是运行时在SELECT语句内创建的

• 拼接字段：SELECT Concat(列名1, ',', 列名2, ...)，但此时的计算字段没有字段名

• 使用别名

  • 使用 AS 关键字
  • 用法：SELECT 列名 AS 列别名
  • 别名用处：
    1. 为计算字段添加列名；
    2. 原表中的实际字段名有歧义时，可利用别名为其重命名（仅仅在 SELECT，而不影响表明字段名的实际存储）

• 执行算术计算

  • 将表中指定列，进行算术计算，构成新的计算字段

  /** 对表中两列进行三数计算 */
  SELECT prod_id,
  	   quantity,
  	   item_price,
  	   quantity * item_price AS expanded_price
  FROM orderitems
  WHERE order_num = 20005;
  

第 11 章 使用数据处理函数

MySQL支持何种函数，以及如何使用这些函数

• SQL支持利用函数来处理数据
• 多数SQL语句是可移植的，而函数的可移植性却不强（不同 DBMS 的函数可能有差异）
• 使用函数的习惯：使用函数，应该保证做好代码注释，以便于以后确切地知道SQL代码的含义
• 常见SQL函数类型：
  • 文本处理文本函数：处理文本串
  • 数值函数：在数值数据上进行算术操作
  • 日期和时间函数
  • 系统函数：返回DBMS正使用的特殊信息（用户登录信息，检查版本细节）

文本处理文本函数

• 常用文本处理函数

• 用法：函数(列名)

  • note：可以与WHERE等子句组合使用，达到过滤的目的

• SOUNDEX函数：匹配发音类似的行

  -- vend_name 转换为大写SELECT vend_name, UPPER(vend_name) AS vend_name_upcaseFROM vendorsORDER BY vend_name;/*** SOUNDEX函数：匹配发音类似的行*/SELECT cust_name, cust_contactFROM customersWHERE cust_contact = 'Y Lie'; -- 表中没有 'Y Lie'-- 使用 Soundex 函数SELECT cust_name, cust_contactFROM customersWHERE SOUNDEX(cust_contact) = SOUNDEX('Y Lie'); -- 可以匹配到表中的发音和'Y Lie' 类似的，'Y Lee'
  

日期和时间处理函数

• 常用日期和时间处理函数

• MySQL 的日期格式：

  • 首选的日期格式：yyyy-mm-dd
  • 虽然其他的日期格式（yyyy/mm/dd等）可能也行，但是yyyy-mm-dd排除了多义性
  • datetime 类型分为日期（date）和时间（time），匹配时应该分别比较二者：
    • 使用 Date() 函数匹配日期；
    • 使用 Time() 函数匹配时间；

  SELECT cust_id, order_numFROM ordersWHERE order_date = '2005-09-01'; -- 首选日期格式								 -- 但是这样不是最好的方案，应该分别匹配日期（date）和时间（time）									 -- 见下面的方法，使用Date()函数-- WHERE order_date = '2005/09/01';-- WHERE order_date = '05/09/01';SELECT cust_id, order_numFROM ordersWHERE Date(order_date) = '2005-09-01'; -- 进与列中的 date 匹配，不管时间/*** 匹配 2005 年 9 月全部订单*/-- 解法1SELECT cust_id, order_numFROM ordersWHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';-- 解法2SELECT cust_id, order_numFROM ordersWHERE YEAR(order_date) = 2005 AND MONTH(order_date) = 9;
  

数值处理函数

主要DBMS的函数中，数值函数是最一致最统一的函数

第 12 章 汇总数据

SQL的聚集函数以及如何利用它们汇总表的数据

• 汇总数据：返回对表中数据的汇总，而不是实际数据本身

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

• 常用SQL聚集函数（5个）
  

• 常用SQL聚集函数（5个）细节

  • AVG() 只用于单个列。为了获得多个列的平均值，必须使用多个AVG()函数
  • COUNT()函数有两种使用方式：
    • COUNT(*)：对表中所有行计数，列值为NULL也会计数;
    • COUNT(column)：对特定列中具有值的行进行计数，忽略列值为NULL的行
  • MAX()/ MIN()
    • 一般用于找出数值、日期值的最大/ 最小值；
    • 如果用于文本数据时，如果数据按相应的列排序，则 MAX()/ MIN()返回 最后一行/最前面的行
  • SUM()
    • 返回指定列的总和；
    • 或者返回多个列运算的算术结果
  • 关于 NULL 值
    • AVG()、COUNT(column)、MAX()、MIN()、SUM() 函数忽略列值为NULL的行；
    • COUNT(*) 函数，尽管列值为 NULL 的行也会考虑

• 聚集不同值

  • 以上5个聚集函数，如果值返回包含不同的值时，需要指定DISTINCT参数
  • DISTINCT 从MySQL 5.0.3版本才开始支持
  • DISTINCT 注意点：
    • 如果指定列名，则DISTINCT只能用于COUNT(列名)，不允许使用COUNT（DISTINCT *）
    • DISTINCT必须使用列名，不能用于计算或表达式

  -- 计算所有产品的平均价格SELECT AVG(prod_price) AS avg_priceFROM products;/*** COUNT() 函数*/-- 用法1：利用COUNT(*)对表中所有列计数（无论列值是否为NULL）SELECT COUNT(*)FROM customers;-- 用法2：利用COUNT(column)对表中特定列计数（忽略列值为NULL的行）SELECT COUNT(cust_email) AS num_custFROM customers;/*** MAX()/MIN() 函数*/SELECT MAX(prod_price) AS max_priceFROM products;-- minSELECT MIN(prod_price) AS min_priceFROM products;/*** SUM() 函数*/SELECT SUM(item_price * quantity) AS total_priceFROM orderitemsWHERE order_num = 20005;/*** DISTINCT参数*/SELECT AVG(DISTINCT prod_price) AS avg_priceFROM productsWHERE vend_id = 1003;
  

聚集函数的结果，通常应取不同于表中列名的别名（更易于理解、可读）

第 13 章 分组数据

分组数据：主要设计SELECT的两个子句，GROUP BY子句和HAVING子句

• 数据分组：分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算

GROUP BY：创建分组

• GROUP BY子句作用：指示MySQL分组数据，然后对每个组而不是整个结果集进行聚集
• GROUP BY子句规定：
  • 关于列：
    • GROUP BY子句可以包含任意数目的列，即可以使用嵌套分组（不知道是个啥？？？）
    • GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。
      • 如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
    • 除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出
  • 关于NULL值：
    • 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组
  • GROUP BY子句的位置：
    • GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前

使用WITH ROLLUP关键字，可以得到每个分组以及每个分组汇总级别

HAVING：过滤分组

• HAVING子句作用：用来过滤分组，规定包括哪些分组，排除哪些分组
• HAVING和WHERE的差别：二者句法是相同的，只是关键字有差别
  1. WHERE过滤行，而HAVING过滤分组；
  2. WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤
     • 即，WHERE排除的行将不包括在分组中

/*** 查找至少有两个订单的所有顾客：分组 + 分组过滤*/SELECT vend_id, COUNT(*) AS num_prodsFROM productsGROUP BY vend_id -- 未限定，该分组中的 prod_price 任意HAVING COUNT(*) > 2;/** 查找至少有两个订单、且每笔订单金额均不低于10的所有顾客 */-- WHERE 和 HAVING 一起使用时SELECT vend_id, COUNT(*) AS num_prodsFROM productsWHERE prod_price >= 10 -- 分组前，先使用 WHERE 过滤。即，分组中的 prod_price 均不低于 10GROUP BY vend_idHAVING COUNT(*) > 2;

分组和排序

• ORDER BY与GROUP BY的区别：

  

• 即，GROUP BY提供的分组顺序不靠谱；应该使用ORDER BY保证分组顺序的唯一性

SELECT order_num, SUM(quantity*item_price) AS ordertotalFROM orderitemsGROUP BY order_numHAVING SUM(quantity*item_price) >= 50 -- HAVING 中可以使用聚集函数，但是GROUP BY中不行ORDER BY ordertotal; -- 保证分组顺序的唯一性。ORDER BY中可以使用别名，但是GROUP BY中不行

SELECT子句顺序

第 14 章 使用子查询

• 版本要求：MySQL 4.1引入了对子查询的支持
• 简单查询：即从单个数据库表中检索数据的单条语句
• 子查询（subquery），即嵌套在其他查询中的查询

利用子查询进行过滤

/*** 要列出订购物品TNT2的所有客户*/-- 解法1：使用子查询SELECT customers.*FROM customersWHERE cust_id IN (SELECT cust_id				  FROM orders				  WHERE order_num IN (SELECT order_num									  FROM orderitems									  WHERE prod_id = 'TNT2'));-- 解法2：表联结（ch15）SELECT customers.*FROM orderitems, customers, ordersWHERE orderitems.order_num = orders.order_num	  AND customers.cust_id = orders.cust_id	  AND orderitems.prod_id = 'TNT2';

• 使用子查询时，通常分解为多行并且适当地进行缩进（如上栗子），以提高可读性
• 子查询的嵌套数目没有限制，不过在实际使用时由于性能的限制，不能嵌套太多的子查询
• 列必须匹配：应该保证子查询的SELECT语句 和 WHERE子句中具有相同数目的列

子查询一般与IN操作符结合使用，但也可以用于测试等于（=）、不等于（<>）等

使用子查询作为计算字段

• 子查询的另一种用法：创建计算字段

• 栗子：需要显示customers表中每个客户的订单总数

  SELECT cust_id, 	   cust_name, 		(SELECT COUNT(*)         FROM orders         WHERE orders.cust_id = customers.cust_id) AS orders_sum -- 子查询作为计算字段，这里必须加小括号()FROM customersGROUP BY cust_id;
  

  注意：使用子查询作为计算字段时，必须加小括号()

• 相关子查询：涉及外部查询的子查询

• 列名可能有多义性时，就必须使用完全限定列名（即，表明.列名）

第 15 章 联结表

• 主键（primary key）：表中的一列（或一组列），其值能够唯一区分表中每个行。

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

• 将数据分解成多个表的优点：

  • 更有效地存储，省时省空间；
  • 更便于信息变更处理。一般只用更新单个表中的单个记录，不用全盘更改；
  • 无重复数据；
  • 可伸缩性强

• 将数据分解成多个表的缺点：

  • 数据存储在多个表中时，无法直接从一个表中取出数据；
  • 此时，需要使用表联结

• 笛卡儿积（cartesian product） ：由没有联结条件（WHERE子句）的表关系返回的结果为笛卡儿积

  • 笛卡尔积：m * n
  • 笛卡尔积中可能包含不正确的列（即，一个表的列有值，另一个表所在列全为NULL）
  • 注意：应该保证所有的表联结都有WHERE子句，避免返回笛卡尔积中不正确的数据

内部联结

• 内（部）联结：基于两个表之间的相等测试，又叫等值联结（equijoin）

  /** 内联结（等值联结） */-- 写法1：INNER JOIN ... ONSELECT vend_name, prod_name, prod_priceFROM vendors INNER JOIN productsON vendors.vend_id = products.vend_id;-- 写法2：WHERE子句SELECT vend_name, prod_name, prod_priceFROM vendors, productsWHERE vendors.vend_id = products.vend_id;
  

• 注意：不要联结不必要的表

  • 理论上可以联结无数个表；
  • 但是，联结的表越多，性能下降越厉害

SQL操作，一般存在不止一种方法。

因此，应该进行实验，以找出最适合具体情况的方法

第 16 章 创建高级联结

主要介绍表别名的使用，以及自联结、自然联结、外联结

• 使用表别名的理由：

  • 缩短SQL语句；简化SQL代码（大部分都是这个目的）
  • 允许在单条SELECT语句中多次使用相同的表。 多用于自联结

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

自联结

• 自联结：通俗地说，就是表自己和自己联结
• 注意：由于自联结容易造成列名的二义性，所以通常需要使用表别名（即，表别名的第2个理由）
• 例子：查找与 prod_id = 'DTNTR' 同一供应商提供的所有产品

/*** 查找与 prod_id = 'DTNTR' 同一供应商提供的所有产品*/-- 方法1：使用子查询SELECT prod_id, prod_nameFROM productsWHERE vend_id in (SELECT vend_id				  FROM products				  WHERE prod_id = 'DTNTR');-- 方法2：使用自联结SELECT p1.prod_id, p1.prod_nameFROM products as p1, products as p2 -- 使用表别名WHERE p1.vend_id = p2.vend_id	  AND p2.prod_id = 'DTNTR';

Note：在同一表中查询数据，用自联结而不用子查询。

（虽然二者检索结果相同，但是有时候处理联结远比处理子查询快得多。应通过试验，挑选合适的方法）

自然联结

• 自然联结：排除列多次出现，使每个列只返回一次

• 做法：一般是通过对表使用通配符（SELECT *），对所有其他表的列使用明确的子集来完成的

  /*** 自然联结：保证检索结果中每个列至多只出现一次*/SELECT c.*, o.order_num, o.order_date -- 选择 customers 所有 * 列，以及 orders 某些明确的列FROM customers as c, orders as o, orderitems as oiWHERE c.cust_id = o.cust_id	  AND oi.order_num = o.order_num	  AND prod_id = 'FB';
  

外部联结

• 外部联结：联结包含了那些在相关表中没有关联行的行，称为外部联结
• 分类：
  • 左外联结：FROM 表名1 LEFT OUTER JOIN 表名2 ON 联结条件，取左边（表1）的所有行
  • 右外联结：FROM 表名1 RIGHT OUTER JOIN 表名2 ON 联结条件，取右边（表2）的所有行

使用联结和联结条件

第 17 章 组合查询

利用UNION操作符将多条SELECT语句组合成一个结果集

• 组合查询：执行多个查询（多条SELECT语句），并将结果 作为单个查询结果集返回，又称之为“并”或“复合查询”
• 使用组合查询的情况：
  1. 在单个查询中从不同的表返回类似结构的数据；
  2. 对单个表执行多个查询，按单个查询返回数据

UNION 关键字

• 用法：在多个SELECT查询之间，加上UNION关键字，即可
• UNION规则
  • UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔；
  • UNION中的每个查询必须包含相同的列、表达式或聚集函数（但列的次序不必相同）；
  • 列数据类型必须兼容（类型不必完全相同，DBMS能隐式转换即可）
• 注意：使用多个WHERE条件，可以达到和UNION相同的检索结果。但是，检索性能不同；具体通过试验判断挑选何者

/*** 查找“价格小于5”且供应商为1001或1002的产品*/-- 解法1：UNIONSELECT vend_id, prod_id, prod_priceFROM productsWHERE prod_price <= 5UNION -- 两个 SELECT 查询之间添加SELECT vend_id, prod_id, prod_priceFROM productsWHERE vend_id in (1001, 1002);-- 解法2：使用多条where子句，用OR连接SELECT vend_id, prod_id, prod_priceFROM productsWHERE prod_price <= 5	  OR vend_id in (1001, 1002);

• **UNION 和 UNION ALL 的区别：**UNION 会自动取消重复的行，但是 UNION ALL 不会取消重复的行

对组合查询结果排序

在用UNION组合查询时，只能使用一条ORDER BY子句，它必须出现在最后一条SELECT语句之后（但，是对整个结果集排序，而不是对最后一个SELECT部分排序）

/*** UNION 默认去除重复行；* UNION ALL 不去除重复行；*/SELECT vend_id, prod_id, prod_priceFROM productsWHERE prod_price <= 5UNION ALLSELECT vend_id, prod_id, prod_priceFROM productsWHERE vend_id in (1001, 1002)ORDER BY vend_id; -- 使用 UNION 只允许有一条 ORDER BY，是对所有结果排序；且 ORDER BY 放在最后

第 19 章 插入数据

• 使用 INSERT 向表中插入数据，主要有以下 4 种形式：

  1. 插入完整的行；（即，不指定列名。不推荐）
  2. 插入行的一部分；（即，指定列名。推荐）
  3. 插入多行；
  4. 插入某些查询的结果

插入一行

• 方法一：不指定列名 【不推荐】

  • 语法：INSERT INTO 表名 VALUES(val1, val2, ...)
    • VALUES 中必须给出表中每个列的值，如果某列没值，则使用 NULL
    • val1，val2, … 分别对应表中 第一列，第二列，…
  • 缺点：
    • 不安全（过分依赖表中列的次序，表结构改变时会出现错误）；
    • 不灵活（VALUES 中必须给出所有所有列值）

• 方法二：指定列名 【推荐使用】

  • 语法：INSERT INTO 表名(col1, col2, ...) VALUES(val1, val2, ...)
  • 特点：
    • 优点：更安全（表中列次序改变时，SQL 语句仍然能正常使用）；更灵活（VALUES 中不必给出所有所有列值）
    • 缺点：SQL 更繁琐（但更安全）;

• 几点注意：

  1. 总是使用明确列的 INSERT 语句；
  2. 省略的列必须满足以下某个条件（或的关系，不是且）
     • 该列定义为允许NULL值（无值或空值
     • 在表定义中给出默认值
  3. 提高整体性能
     • INSERT 操作可能很耗时，而且它可能降低等待处理的SELECT语句的性能
     • 解决方案：使用关键字LOW_PRIORITY，指示MySQL降低INSERT语句的优先级

  插入多行

• 方法一：

  • 使用多条INSERT语句，甚至一次提交它们，每条语句用一个分号结束
  • 语法：INSERT INTO 表名(...) VALUES(...); INSERT INTO 表明(...) VALUES(...); ...

• 方法二：

  • 只要每条INSERT语句中的列名（和次序）相同，可以使用单条INSERT语句有多组值，每组值用一对圆括号括起来，
    用逗号分隔
  • 语法：INSERT INTO 表名(col1, col2, ...) VALUES(val11, val12, ...), (val21, val22, ...);

• 提高INSERT的性能：为MySQL用单条INSERT语句处理多个插入比使用多条INSERT语句快

插入检索出的数据

• INSERT SELECT语句：给一个表插入从另一个表的检索结果
• 语法：INSERT INTO 表名(col1, col2, ...) SELECT col1', col2' FROM table2
• 注意：INSERT SELECT语句中，INSERT和SELECT语句中，不一定要求列名匹配（列名可以不同，但是相对位置一定要一样。即，使用 col1’ 填充 col1，用 col2’ 填充 col2）

第 20 章 更新和删除数据

利用UPDATE和DELETE语句进一步操纵表数据

更新数据

使用 UPDATE 更新（修改）表中的数据，主要有以下两种形式：

1. 更新表中特定行；
2. 更新表中所有行

更新特定行：

• 语法：

  可以选择更新单列，或者更新多列（更新多列时，用逗号 “,” 分隔）

  UPDATE 表名SET col1 = newVal1, col2 = newVal2, ...WHERE 过滤条件
  

更新所有行：

• 以上 UPDATE 语句不加 WHERE 条件时，就是对表中所有行，进行操作

注意：

1. 不要省略WHERE子句；否则，则更新表中所有行了；
2. IGNORE关键字。如果用UPDATE语句更新多行时，有一行或者多行发生错误，则整个 UPDATE 操作就会被取消（就算正确的也会取消）。如果想让发生错误，也继续进行更新，可使用IGNORE关键字。即，UPDATE IGNORE 表名…

删除数据

使用 DELETE 删除表中的数据，主要有以下两种形式：

1. 删除表中特定行；
2. 删除表中所有行

删除表中特定行

• 语法：

  DELETE FROM 表名WHERE 过滤条件
  

删除表中所有行

• 以上 DELETE 不加 WHERE 语句，即删除所有行
• 注意：
  • 删除表中所有行，DELETE 并不是不删除表本身（表结构）
  • 更快的删除。如果想从表中删除所有行，不要使用DELETE。可使用TRUNCATE TABLE语句（相当于删除原有表结构，并重新创建一个表，而不是逐行删除表中的数据，所以更快）

删除某个列的值

• 思路：可使用 UPDATE 语句将它设置为NULL（假如表定义允许NULL值）

• 语法：

  UPDATE 表名SET col1 = NULL, col2 = NULL, ...WHERE 过滤条件
  

更新和删除的指导原则

1. 除非确实打算更新和删除表中所有行，否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句；
2. 保证每个表都有主键，尽可能像 WHERE 子句那样使用它（可以指定各主键、多个值或值的范围）。
3. 在对UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确。
4. 使用强制实施引用完整性的数据库

MySQL没有撤销（undo）操作。应该非常小心地使用UPDATE和DELETE

第 21 章 创建和操纵表

创建表

创建表（表结构本身），主要有 2 中方法：

1. 使用交互式创建和管理表的工具（比如ch2说的，或者 navicat）
2. 使用 MySQL 语句

使用交互式创建和管理表的工具时，实际上底层仍然使用的是 MySQL 语句（只不过不是用户写的，而是工具自动转换了）

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 DEFAULT 'China',  cust_contact char(50)  NULL,  cust_email   char(255) NULL,  PRIMARY KEY (cust_id)) ENGINE=InnoDB;

主键

• 可以使用一列，或者多列（组合值必须唯一）来唯一确定每一行。
• 多列作为主键时，使用逗号 , 隔开，即 PRIMARY KEY(col1, col2, ...)
• 主键中只能使用不允许 NULL 值的列

AUTO_INCREMENT 列

• AUTO_INCREMENT告诉MySQL，本列每当增加一行时 该列值 自动增量
• 每个表只允许一个AUTO_INCREMENT列，而且它必须被索引（如，通过使它成为主键）
• 如何获取最后一个 AUTO_INCREMENT 值？
  • 用 last_insert_id()函数 获取最后一个 AUTO_INCREMENT 值
  • 即，SELECT last_insert_id()

指定默认值

• 在列定义中使用 DEFAULT 关键字指定。比如，上面的 DEFAULT 'China'

引擎类型

• 引擎：用来具体管理和处理数据
  • 比如，使用 CREATE TABLE、SELECT 等语句时，其实是该引擎在内部处理你的请求
  • 多数时候，此引擎都隐藏在 DBMS 内，不需要过多关注它
• MySQL 具有多个引擎，这些引擎都隐藏在MySQL服务器内，全都能执行CREATE TABLE和SELECT等命令
• 为什么 MySQL 要发行多种引擎呢？
  • 因为它们具有各自不同的功能和特性，为不同的任务选择正确的引擎能获得良好的功能和灵活性。
• MySQL 常见引擎：
  1. InnoDB 是一个可靠的事务处理引擎（参见第26章），它不支持全文本搜索；
  2. MEMORY 在功能等同于 MyISAM，但由于数据存储在内存（不是磁盘）中，速度很快（特别适合于临时表）；
  3. MyISAM 是一个性能极高的引擎，它支持全文本搜索（参见第18章），但不支持事务处理。
• 关于引擎混用的两点注意：
  1. 引擎类型可以混用
     • 比如，书中除 productnotes表使用 MyISAM（想要使用全文本搜索） 外，本书中的样例表都使用 InnoDB（想要支持事务）
  2. 外键不能跨引擎
     • 混用引擎类型有一个大缺陷，即外键（用于强制实施引用完整性，见 ch1）不能跨引擎；
     • 即。使用一个引擎的表不能引用具有使用不同引擎的表的外键

可以使用 SHOW ENGINES; 查看 MySQL 的内部引擎

更新表

• 使用 ALTER TABLE 语句可以更改更改表结构（即，表本身），必须给出一下信息：

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

• 给表添加列

  ALTER TABLE 表名ADD 列名 列定义（类型，是否允许 NULL 等）;-- 栗子ALTER TABLE vendorsADD vend_phone CHAR(20);
  

• 删除表中某列

  ALTER TABLE 表名DROP COLUMN 列名;
  

Note：小心使用ALTER TABLE，应该在进行改动前做一个完整的备份（模式和数据的备份）。（因为数据库表的更改不能撤销）

删除表

• 删除表（删除整个表（表结构），而不仅仅是其内容）

  DROP TABLE 表名;
  

Note：小心使用 DROP TABLE，该操作没有确定，也不能撤销，应谨慎使用。（更稳妥的办法是在进行改动前做一个完整的备份）

重命名表

• 单个表重命名

  RENAME TABLE 旧表表名 TO 新表名;
  

• 多个表重命名

  RENAME TABLE 旧表表名1 TO 新表名1,			 旧表表名2 TO 新表名2,			 ... ;
  

第 22 章 使用视图

MySQL 5添加了对视图的支持

视图

为什么使用视图

视图的常见应用：

1. 重用 SQL 语句；

2. 简化 复杂的 SQL 操作；

   在编写查询后，可以方便地重用它而不必知道它的基本查询细节。

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

4. 保护数据

   可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。

5. 更改数据格式和表示

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

使用视图的规则和限制

1. 视图名必须唯一，但视图数目没有限制；
2. 为了创建视图，必须具有足够的访问权限；
3. 视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图；
4. ORDER BY 可以用在视图中，但如果从该视图检索数据 SELECT 中也含有 ORDER BY，那么该视图中的 ORDER BY 将被覆盖；
5. 视图不能索引，也不能有关联的触发器或默认值；
6. 视图可以和表一起使用

使用视图

视图的常见操作

• 创建视图：CREATE VIEW 视图名 AS ...
• 查看创建视图的语句：SHOW CREATE VIEW 视图名;
• 删除视图：DROP VIEM 视图名;
• 更新视图：可以先用 DROP 再用 CREATE，也可以直接用 CREATE OR REPLACE VIEW 视图名 ...
  • CREATE OR REPLACE VIEW 视图名 ...：如果如果要更新的视图不存在，则创建之；否则，则替换之

利用视图简化复杂的 SQL 语句

可以创建一个具有 一般性 的视图（比如，两个表的联结，不带 WHERE 子句），然后从视图中查询，可以达到简化 SQL 语句的目的

-- 创建视图CREATE VIEW viewname ASSELECT col1, col2, ...FROM table1, table2WHERE 联结条件;-- 利用视图查询（简化表联结 操作）SELECT *FROM viewname;

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

视图的另一常见用途是 重新格式化检索出的数据

-- 创建视图CREATE VIEW viewname ASSELECT Concat(col1, col2, ...) AS 列别名FROM table1, table2;-- 利用视图查询（简化表联结 操作）SELECT *FROM viewname;

用视图过滤不想要的数据

可以使用对视图添加 WHERE 过滤条件，达到过滤数据的目的；然后使用视图，进行查询（又达到了 “简化” SQL 的目的）

使用视图与计算字段

视图对于 简化计算字段的使用特别有用

CREATE VIEW viewname ASSELECT quantity * item_price AS expand_price -- 计算字段FROM orderitems;-- 查询视图SELECT *FROM viewname;

更新视图

• 视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的
• 视图是可更新的（即，可以对它们使用 INSERT、UPDATE 和 DELETE）
  • 更新一个视图将更新其基表（因为视图本身没有数据）；
  • 如果对视图增加或删除行，实际上是对其基表增加或删除行
• 如果视图定义中有以下操作，则不能进行视图的更新（7 种情况）：
  • 分组（使用 GROUP BY 和 HAVING）；
  • 联结；
  • 子查询；
  • 并；
  • 聚集函数（Min()、Count()、Sum()等）；
  • DISTINCT；
  • 导出（计算）列

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

第 23 章 存储过程

存储过程和函数，类似于 Java 中的方法

• 存储过程：一组预先编译好的 SQL 语句的集合，理解成批处理语句

存储过程的特点

存储过程的优点

1. 提高 SQL 代码的重用性；
2. 简化复杂的操作；
3. 保证数据的完整性；
   • 不必要反复建立一系列处理步骤，而是使用同一个 存储过程
4. 提高性能
   • 因为使用存储过程比使用单独的SQL语句要快
   • 较少编译次数并且减少了和数据库服务器的连接次数，提高了效率

总之：存储过程的优点，就是 简单、安全、高性能

存储过程的缺点

1. 存储过程的编写比基本 SQL 语句复杂；
2. 可能没有创建存储过程的安全访问权限
   • 大多数人都是只有存储过程的使用权限，而 没有创建权限

创建存储过程

• 创建语法：

  CREATE PROCEDURE 存储过程名(参数...)BEGIN	存储过程体（一条，或者 多条 SQL 语句）END;
  

• 修改 MySQL 命令行实用程序语句分隔符：

  • 默认的MySQL语句分隔符为 ;，但是如果使用 MySQL 命令行实用程序语句 创建存储过程时，存储过程体仍然使用 ;作为分隔符就会出现 “提前终止”的错误，所以此时需要修改 MySQL 命令行实用程序语句分隔符

  • 临时更改命令行实用程序的语句分隔符

    DELIMITER // -- 修改为使用 "//" 作为分隔符 ....DELIMITER ; -- 使用完后，重新修改为使用 ";" 作为分隔符 
    

mysql 命令行要改，但是 Navicat 不用改

调用存储过程

• 语法：

  CALL 存储过程名(参数...);
  

• 注意：

  • 就算是调用无参的存储过程，那() 也不能省略；

删除存储过程

• 语法：

  DROP PROCEDURE 存储过程名;-- 仅当存在时删除DROP PROCEDURE IF EXISTS 存储过程名;
  

• 注意：

  • 删除存储过程时，只用带“存储过程 名”，而不需要参数和() 【注意，和 调用的区别】

参数模式

• 参数模式类型：

  • IN：作为存储过程的输入参数
  • OUT：作为存储过程的返回值
  • INOUT：既可以作为存储过程的输入参数，还能作为作为存储过程的返回值

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

  • 变量名：所有 MySQL 变量都必须以 @ 开始
  • 声明局部变量：使用 DECLARE 关键字，可以在存储过程中声明 局部变量
  • 变量赋值：使用 INTO 语句，可以将 SELECT 检索出来的结果保存到 变量中

使用存储过程的步骤

1. 创建存储过程；
2. 调用存储过程；
3. 显示结果（如果有 OUT 模式的参数的话）

-- 1、创建存储过程DELIMITER // -- 修改为使用 "//" 作为分隔符，mysql 命令行要改，但是 Navicat 不用改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 //-- 2、调用存储过程CALL ordertotal(20005, @ototal);-- 3、显示结果SELECT @ototal;

检查存储过程

• 目的：检查包括何时、由谁创建等详细信息的存储过程列表

• 语法：

  SHOW CREATE PROCEDURE 存储过程名名;
  

第 25 章 使用触发器

MySQL 5 以后的版本，才支持触发器

触发器

• 触发器：某个表发生更改时自动处理
• 触发器，仅仅支持 DELETE、UPDATE、INSERT 这 3 条语句

创建触发器

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

  • 唯一的触发器名；
  • 触发器关联的表；
  • 触发器应该响应的活动（DELETE、INSERT 或 UPDATE）；
  • 触发器何时执行（BEFORE 或 AFTER）

• 语法：

  CREATE TRIGGER 触发器名 BEFORE/ AFTER 表名FOR EACH ROWBEGIN... -- 触发器应代码（如果只有一条 则可以省略 BEGIN, END）END;
  

• 注意：

  1. 保持每个数据库的触发器名唯一；

     在MySQL 5中，触发器名 虽然不是在每个数据库中唯一，而是必须在每个表中唯一。

     但是，最好是在数据库范围内使用唯一的触发器名

  2. 《MySQL 必知必会》书上的例子有问题，不能直接在 SELECT ...，而是要将结果保存到一个变量 @arg中，然后再 查询之；

  3. 触发器仅支持表，视图不支持触发器（临时表也不支持）

删除触发器

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

• 删除触发器，语法：

  DROP TRIGGER 触发器名;
  

使用触发器

INSERT触发器

• INSERT 触发器在 INSERT 语句执行之前或之后执行

• INSERT 触发器特点：

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

• AUTO_INCREMENT 列，确定新生成值的方法：

  -- 1、使用触发器，获取 AUTO_INCREMENT 列 最新的值CREATE TRIGGER newprodcuct AFTER INSERT ON ordersFOR EACH ROW SELECT NEW.order_new INTO @new_order_num;-- 2、插入INSERT INTO orders(cust_name)VALUES("conan");-- 3、查询SELECT @new_order_num;
  

• 使用 BEFORE 还是 AFTER？ 【即，二者区别】

  • 通常，将 BEFORE 用于数据验证和净化（目的是保证插入表中的数据确实是需要的数据）

DELETE 触发器

• DELETE触发器在DELETE语句执行之前或之后执行
• DELETE 触发器特点：
  • 在 DELETE 触发器代码内，你可以引用一个名为 OLD 的虚拟表，访问被删除的行；
  • OLD 中的值全都是只读的，不能更新。
• DELETE 触发器，可以用来备份（存档）被删除的记录；
  • 使用 BEFORE DELETE 触发器更好

UPDATE 触发器

• UPDATE触发器在UPDATE语句执行之前或之后执行
• UPDATE 触发器特点：
  • 在 UPDATE 触发器代码中，OLD 虚拟表访问以前（UPDATE 语句前）的值，NEW 虚拟表访问新更新的值；
  • 在 BEFORE UPDATE 触发器中，NEW 中的值可能也被更新（允许更改将要用于（即，UPDATE之前） UPDATE 语句中的值）；
  • OLD 中的值全都是只读的，不能更新。

使用触发器时的细节

1. 创建触发器可能需要特殊的安全访问权限，但是，触发器的执行是自动的

   即，如果INSERT、UPDATE或DELETE语句能够执行，则相关的触发器也能执行

2. 应该用触发器来保证数据的一致性（大小写、格式等）；

3. 可以使用触发器 创建审计跟踪

   使用触发器，把更改（如果需要，甚至还有之前和之后的状态）记录到另一个表非常容易。

4. MySQL 触发器中不支持 CALL 语句

   这表示不能从触发器内调用存储过程

第 26 章 管理事务处理

MySQL 中所有存储引擎只有 InnoDB 支持事务

事务

• 事务（transaction）：一个事务其实就是一个完整的业务逻辑。是一个最小的工作单元，不可再分。
  • 只有 DML语句（即，INSERT DELETE UPDATE）才会与事务有关，其它语句和事务无关
  • 事务的本质，就是批量的 DML 语句同时成功，或者同时失败！
• 回退（rollback）指撤销指定SQL语句的过程； 事务失败，撤销之前的 DML 语句
• 提交（commit）指将未存储的SQL语句结果写入数据库表；事务成功，并持久化到数据库中
• 保留点（savepoint）指事务处理中设置的临时占位符（placeholder），你可以对它发布回退（与回退整个事务处理不同）。

开始事务

• MySQL 默认情况下是支持自动提交事务的（也叫，隐含提交）

  • 一般的 MySQL 语句都是直接针对数据库表执行和编写的，且 MySQL 语句的提交（写或保存）操作是自动进行的；
  • 自动提交，即 每执行一条DML语句，则提交一次！

• 关闭 MySQL 自动提交机制，语法：

  START TRANSACTION; -- 开始一个事务，到该事务 commit 之前 DML 不会自动提交
  

提交事务

• 关闭 MySQL 自动提交机制后，事务将不会自动提交，此时需要明确的提交事务

• 明确提交事务，使用 COMMIT 语句

  START TRANSACTION; -- 关闭 MySQL 自动提交 DML... -- 一批 DML 语句COMMIT; -- 明确提交事务
  

撤销事务

• 当事务失败时，可以使用 ROLLBACK 撤销之前执行失败的 DML 语句

• ROLLBACK 只能在一个事务处理内使用（在执行一条 START TRANSACTION 命令之后）。

  START TRANSACTION; -- 关闭 MySQL 自动提交 DML... -- 一批 DML 语句ROLLBACK; -- 回滚
  

Note：当 COMMIT 或 ROLLBACK 语句执行后，事务会自动关闭（将来的更改会隐含提交）。

创建保留点

• 为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符（即，保留点）

• 保留点-使用语法：

  /** 1、创建保留点 */SAVEPOINT 保留点名;... -- 一批 DML/** 2、回退到保留点 */ROLLBACK TO 保留点名;
  

• 保留点越多越好

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

• 释放保留点

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

更改默认的提交行为

• 默认的 MySQL 行为是自动提交所有更改

• 修改 MySQL 不自动提交，语法：

  SET autocommit = 0;
  

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

  autocommit标志是针对每个连接而不是服务器的