前言：

该文章根据《MySQL必知必会》整理，如需原书资料，关注微信公众号：CodeFish 回复 “电子书” 即可获取！

✨一个值得尝试的GPT变现小项目✨

一、基础概念

名词	描述
数据库（database）	保存有组织的数据的容器（通常是一个文件或一组文件）。
表（table）	某种特定类型数据的结构化清单。
列（column）	表中的一个字段。
行（row）	表中的一个记录。
数据类型（datatype）	所容许的数据的类型。
主键（primary key）	一列（或一组列），其值能够唯一区分表中每个行。

二、子句顺序

子句	说明	是否必须使用
SELECT	要返回的列或表达式	是
FROM	从中检索数据的表	仅在从表选择数据时使用
WHERE	行级过滤	否
GROUP BY	分组说明	仅在按组计算聚集时使用
HAVING	组级过滤	否
ORDER BY	输出排序顺序	否
LIMIT	要检索的行数	否

三、使用MySQL（USE、SHOW）

操作	命令行
连接数据库	mysql -u <用户名> -p -h <主机名> -P <端口>
获取完整命令行	mysql --help
选择数据库	USE <数据库名>;
显示可用数据库	SHOW DATABASES；
显示当前数据库中的表	SHOW TABLES;
显示当前表中的列	SHOW COLUMNS FROM <表名>;
显示服务器状态信息	SHOW STATUS;
显示创建数据库的语句	SHOW CREATE DATABASE <数据库名>;
显示创建表的语句	SHOW CREATE TABLE <数据库名>;
显示用户权限	SHOW GRANTS;
显示服务器错误信息	SHOW ERRORS;
显示服务器警告信息	SHOW WARNINGS;
获取特定命令帮助	help <特定命令>;
退出程序	exit;

四、检索数据（SELECT）

操作	命令行
检索单个列	SELECT <列名> FROM <表名>;
检索多个列	SELECT <列名1>, <列名2>, ... FROM <表名>;
检索所有列	SELECT * FROM <表名>;
检索不同的行	SELECT DISTINCT <列名> FROM <表名>;
限制结果	SELECT <列名> FROM <表名> LIMIT <开始位置>, <行数>;
使用完全限定的表名	SELECT <表名>.<列名> FROM <数据库名>.<表名>;

注意：

• 一般，除非你确实需要表中的每个列，否则最好别使用 * 通配符。
• DISTINCT 关键字应用于所有列而不仅是前置它的列。

五、排序检索数据（ORDER BY）

操作	命令行
排序数据	SELECT <列名> FROM <表名> ORDER BY <排序列名>;
按多个列排序	SELECT <列名1>, <列名2>, ... FROM <表名> ORDER BY <主排序列名>, <次排序列名>, ...;
指定排序方向（降序）	SELECT <列名> FROM <表名> ORDER BY <排序列名> DESC;
指定排序方向（升序）	SELECT <列名> FROM <表名> ORDER BY <排序列名> ASC;
找出一个列中的最高值	SELECT <列名> FROM <表名> ORDER BY <排序列名> DESC LIMIT 1;
找出一个列中的最低值	SELECT <列名> FROM <表名> ORDER BY <排序列名> ASC LIMIT 1;
注意：

• ORDER BY 必须位于 FROM 之后。
• LIMIT 必须位于 ORDER BY 之后。

六、过滤数据（WHERE）

操作	命令行
检查单个值	SELECT <列名> FROM <表名> WHERE <列名> = <检索值>;
不匹配检查	SELECT <列名> FROM <表名> WHERE <列名> != <检索值>;
范围值检查	SELECT <列名> FROM <表名> WHERE <列名> BETWEEN <检索值1> AND <检索值2>;
空值检查	SELECT <列名> FROM <表名> WHERE <列名> IS NULL;

注意：

• 在同时使用 ORDER BY 和 WHERE 子句时，应该让 ORDER BY 位于 WHERE 之后。
• 在通过过滤选择出不具有特定值的行时，数据库不会返回具有 NULL 值的行。

七、数据过滤（AND、OR、IN、NOT）

操作	命令行
AND 操作符（满足所有）	SELECT <列名> FROM <表名> WHERE <过滤条件1> AND <过滤条件2>;
OR 操作符（满足其一）	SELECT <列名> FROM <表名> WHERE <过滤规则1> OR <过滤规则2>;
IN 操作符（指定范围）	SELECT <列名> FROM <表名> WHERE <列名> IN (<检索值1>, <检索值2>, ...);
NOT 操作符（排除指定范围）	SELECT <列名> FROM <表名> WHERE <列名> NOT IN (<检索值1>, <检索值2>, ...);

注意：

• 优先级 AND > OR。

八、用通配符进行过滤（LIKE、%、_）

操作	命令行
百分号通配符（开头或结尾）	SELECT <列名> FROM <表名> WHERE <列名> LIKE '<开头字符>%<结尾字符>';
百分号通配符（包含）	SELECT <列名> FROM <表名> WHERE <列名> LIKE '%<包含字符>%';
下划线通配符（开头或结尾）	SELECT <列名> FROM <表名> WHERE <列名> LIKE '<开头单字符>_<结尾单字符>';
下划线通配符（包含）	SELECT <列名> FROM <表名> WHERE <列名> LIKE '_<包含单字符>_';

注意：

• 百分号通配符不能匹配用值 NULL 作为产品名的行。

九、用正则表达式进行搜索（REGEXP）

操作	命令行
不区分大小写	SELECT <列名> FROM <表名> WHERE <列名> REGEXP '<正则表达式>';
区分大小写	SELECT <列名> FROM <表名> WHERE <列名> REGEXP BINARY '<正则表达式>';

9.1 常用正则表达式

描述	表达式
基本字符匹配	<匹配文本>
进行OR匹配	[<匹配文本>]
匹配范围	[<匹配文本>-<匹配文本>]
匹配特殊字符	\\<特殊字符>

9.2 空白元字符

元字符	说明
\\f	换页
\\n	换行
\\r	回车
\\t	制表
\\v	纵向制表

9.3 匹配字符类

字符类	说明
[:alnum:]	任意字母和数字（同[a-zA-Z0-9]）
[:alpha:]	任意字符（同[a-zA-Z]）
[:blank:]	空格和制表（同[\t]）
[:cntrl:]	ASCII控制字符（ASCII 0到31和127）
[:digit:]	任意数字（同[0-9]）
[:graph:]	与[:print:]相同，但不包括空格
[:lower:]	任意小写字母（同[a-z]）
[:print:]	任意可打印字符
[:punct:]	既不在[:alnum:]又不在[:cntrl:]中的任意字符
[:space:]	包括空格在内的任意空白字符（同[\f\n\r\t\v]）
[:upper:]	任意大写字母（同[A-Z]）
[:xdigit:]	任意十六进制数字（同[a-fA-F0-9]）

9.4 匹配多个实例

元字符	说明

• | 0个或多个匹配

• | 1个或多个匹配（等于{1,}）
  ? | 0个或1个匹配（等于{0,1}）
  {n} | 指定数目的匹配
  {n,} | 不少于指定数目的匹配
  {n,m} | 匹配数目的范围（m不超过255）

9.5 定位符

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

注意：

• ^ 有两种用法。在集合中（用[和]定义），用它来否定该集合，否则，用来指串的开始处。
• 正则表达式测试方法：SELECT ‘<字符串>’ REGEXP ‘<正则表达式>’;

十、创建计算字段（CONCAT）

操作	命令行
拼接字段	SELECT CONCAT(<字段1>, <字段2>， ...) FROM <表名>;
使用别名	SELECT CONCAT(<字段1>, <字段2>， ...) AS <列别名> FROM <表名>;
算术计算	SELECT <列名1> <算数操作符> <列名2> AS <列别名> FROM <表名>;

十一、使用数据处理函数（Lower、Upper）

操作	命令行
数据处理函数	SELECT 数据处理函数(<列名>) AS <列别名> FROM <表名>;

11.1 文本处理函数

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

注意：

• SOUNDEX 是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX 考虑了类似的发音字符和音节，使得能对串进行发音比较而不是字母比较。

11.2 日期和时间处理函数

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

注意：

• 无论什么时候指定一个日期，日期格式必须为 yyyy-mm-dd。

11.3 数值处理函数

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

十二、汇总数据（AVG、MAX、MIN、COUNT、SUM）

12.1 聚集函数

操作	命令行
返回某列的平均值	SELECT AVG(<列名>) AS <列别名> FROM <表名>;
返回某列的最大值	SELECT MAX(<列名>) AS <列别名> FROM <表名>;
返回某列的最小值	SELECT MIN(<列名>) AS <列别名> FROM <表名>;
返回某列的行数	SELECT COUNT(<列名>) AS <列别名> FROM <表名>;
返回某列值之和	SELECT SUM(<列名>) AS <列别名> FROM <表名>;

12.2 聚集不同值

操作	命令行
返回某列的平均值（只考虑不同值）	SELECT AVG(DISTINCT <列名>) AS <列别名> FROM <表名>;

注意：

• DISTINCT 必须使用列名，不能用于计算或表达式。

十三、分组数据（GROUP BY）

操作	命令行
创建分组	SELECT <列名>, <聚集计算语句> FROM <表名> GROUP BY <列名>;
创建分组（增加汇总级别）	SELECT <列名>, <聚集计算语句> FROM <表名> GROUP BY <列名> WITH ROLLUP;
过滤分组	SELECT <列名>, <聚集计算语句> FROM <表名> GROUP BY <列名> HAVING <聚集计算语句> <过滤条件>;

注意：

• GROUP BY 子句可以包含任意数目的列。
• 如果在 GROUP BY 子句中嵌套了分组，数据将在最后规定的分组上进行汇总。
• GROUP BY 子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。
• 除聚集计算语句外，SELECT 语句中的每个列都必须在 GROUP BY 子句中给出。
• 如果分组列中具有 NULL 值，则 NULL 将作为一个分组返回。如果列中有多行 NULL 值，它们将分为一组。
• GROUP BY 子句必须出现在 WHERE 子句之后，ORDER BY 子句之前。
• 一般在使用 GROUP BY 子句时，应该也给出 ORDER BY 子句。

十四、使用子查询（IN）

14.1 利用子查询进行过滤

查询语句 A：

SELECT <列名c>
FROM <表名a>
WHERE <过滤条件a>;

查询语句 B：

SELECT <列名b>
FROM <表名b>
WHERE <列名c> IN (<查询语句 A 返回的数据>);

查询语句 A、B 组合：

SELECT <列名b>
FROM <表名b>
WHERE <列名c> IN (
	SELECT <列名c>
	FROM <表名a>
	WHERE <过滤条件a>
);

注意：

• 在 WHERE 子句中使用子查询，应该保证 SELECT 语句具有与 WHERE 子句中相同数目的列。

14.1.1 应用实例

问题：查询订购物品 TNT2 的所有客户信息。

customers 顾客表（部分）：

cust_id（唯一顾客id）	cust_name（顾客名）	cust_contact（顾客联系名）
1001	Coyote Inc.	Y Lee
1002	Mouse House	Jerry Mouse
1003	Wascals	Jim Jones
1004	Yosemite Place	Y Sam
1005	E Fudd	E Fudd

orders 顾客订单表（部分）：

order_num（唯一订单号）	cust_id（顾客id）
20005	10001
20006	10003
20007	10004
20008	10005
20009	10001

orderitems 订单产品表（部分）：

order_num（订单号）	prod_id（产品id）
20005	ANV01
20005	ANV02
20005	TNT2
20006	JP2000
20007	TNT2
20008	FC
20009	ANV03

输入：

SELECT cust_name, cust_contact -- 检索前一步骤返回的所有客户ID的客户信息
FROM customers
WHERE cust_id IN (
	SELECT cust_id -- 检索具有前一步骤列出的订单编号的所有客户的ID
	FROM orders
	WHERE order_num IN (
		SELECT order_num -- 检索包含物品 TNT2 的所有订单的编号
		FROM orderitems
		WHERE prod_id = 'TNT2'
	)
);

输出：

cust_name（顾客名）	cust_contact（顾客联系名）
Coyote Inc.	Y Lee
Yosemite Place	Y Sam

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

SELECT <列名a>, (
	SELECT <计算字段>
	FROM <表名b>
	WHERE <表名b>.<列名c> <计算条件> <表名a>.<列名c>
) AS <列别名>
FROM <表名a>;

注意：

• 任何时候只要列名可能有多义性，就必须使用相关子查询语法（表名和列名由一个句点分隔）。

14.2.1 应用实例

问题：查询 customers 表中每个客户的订单总数。

customers 顾客表（部分）：

cust_id（唯一顾客id）	cust_name（顾客名）	cust_state（顾客国籍）
1001	Coyote Inc.	MI
1002	Mouse House	OH
1003	Wascals	IN
1004	Yosemite Place	AZ
1005	E Fudd	IL

orders 顾客订单表（部分）：

order_num（唯一订单号）	cust_id（顾客id）
20005	10001
20006	10003
20007	10004
20008	10005
20009	10001

输入：

SELECT cust_name, cust_state, (
	SELECT COUNT(*)
	FROM orders
	WHERE orders.cust_id = customers.cust_id
) AS orders -- orders 是一个由圆括号中的子查询建立的计算字段，该子查询对检索出的每个客户执行一次
FROM customers
ORDER BY cust_name;

输出：

cust_name（顾客名）	cust_state（顾客国籍）	orders（顾客订单总数）
Coyote Inc.	MI	2
E Fudd	IL	1
Mouse House	OH	0
Wascals	IN	1
Yosemite Place	AZ	1

十五、联结表（INNER JOIN）

15.1 等值联结

SELECT <表名a>.<列名a>, <表名b>.<列名b>
FROM <表名a>, <表名b>
WHERE <表名a>.<列名c> = <表名b>.<列名c>;

注意：

• 在引用的列可能出现二义性时，必须使用完全限定列名（用一个点分隔的表名和列名）。
• 应该保证所有联结都有 WHERE 子句，否则 MySQL 将返回比想要的数据多得多的数据。

15.2 笛卡儿积

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

SELECT <表名a>.<列名a>, <表名b>.<列名b>
FROM <表名a>, <表名b>;

15.3 内部联结（等值联结的不同语法实现）

SELECT <表名a>.<列名a>, <表名b>.<列名b>
FROM <表名a> INNER JOIN <表名b>
	ON <表名a>.<列名c> = <表名b>.<列名c>;

15.4 联结多个表

SELECT <表名a>.<列名a>, <表名b>.<列名b>, <表名c>.<列名c>
FROM <表名a>, <表名b>, <表名c>
WHERE <表名a>.<列名d> = <表名b>.<列名d>
	AND <表名b>.<列名e> = <表名c>.<列名e>;

注意：

• 不要联结不必要的表。联结的表越多，性能下降越厉害。

15.4.1 应用实例

问题：查询订购物品 TNT2 的所有客户信息。

customers 顾客表（部分）：

cust_id（唯一顾客id）	cust_name（顾客名）	cust_contact（顾客联系名）
1001	Coyote Inc.	Y Lee
1002	Mouse House	Jerry Mouse
1003	Wascals	Jim Jones
1004	Yosemite Place	Y Sam
1005	E Fudd	E Fudd

orders 顾客订单表（部分）：

order_num（唯一订单号）	cust_id（顾客id）
20005	10001
20006	10003
20007	10004
20008	10005
20009	10001

orderitems 订单产品表（部分）：

order_num（订单号）	prod_id（产品id）
20005	ANV01
20005	ANV02
20005	TNT2
20006	JP2000
20007	TNT2
20008	FC
20009	ANV03

输入：

SELECT cust_name, cust_contact
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id -- 根据顾客id联结顾客表和订单表
	AND orderitems.order_num = orders.order_num -- 根据订单编号联结订单产品表和订单表
	AND prod_id = 'TNT2'; -- 过滤产品 TNT2 的数据

输出：

cust_name（顾客名）	cust_contact（顾客联系名）
Coyote Inc.	Y Lee
Yosemite Place	Y Sam

十六、创建高级联结（LEFT/RIGHT OUTER JOIN）

16.1 使用表别名

SELECT <表名a>.<列名a>, <表名b>.<列名b>
FROM <表名a> AS <表别名a>, <表名b> AS <表别名b>
WHERE <表别名a>.<列名c> = <表别名b>.<列名c>;

16.2 自联结

SELECT <表名a>.<列名a>, <表名a>.<列名b>
FROM <表名a> AS <表别名b>, <表名a> AS <表别名c>
WHERE <表别名b>.<列名c> = <表别名c>.<列名c>;

16.2.1 应用实例

问题：假如某产品（其 ID 为 DTNTR）存在问题，因此想知道生产该产品的供应商生产的其他产品是否也存在这些问题。

products 产品表（部分）：

prod_id（唯一产品id）	vend_id（供应商id）	prod_name（产品名）
ANV01	1001	.5 ton anvil
ANV02	1001	1 ton anvil
ANV03	1001	2 ton anvil
DTNTR	1003	Detonator
FB	1003	Bird seed
FC	1003	Carrots
FU1	1002	Fuses
JP1000	1005	JetPack 1000
JP2000	1005	JetPack 2000
OL1	1002	Oil can
SAFE	1003	Safe
SLING	1003	Sling
TNT1	1003	TNT (1 stick)
TNT2	1003	TNT (5 sticks)

输入：

SELECT p1.prod_id, p1.prod_name
FROM products AS p1, products AS p2
WHERE p1.vend_id = p2.vend_id -- 根据供应商id进行自联结
	AND p2.prod_id = 'DTNTR'; -- 过滤产品 DTNTR 的数据

输出：

prod_id（唯一产品id）	prod_name（产品名）
DTNTR	Detonator
FB	Bird seed
FC	Carrots
SAFE	Safe
SLING	Sling
TNT1	TNT (1 stick)
TNT2	TNT (5 sticks)

16.3 自然联结

• 自然联结排除多次出现，使每个列只返回一次。
• 迄今为止我们建立的每个内部联结都是自然联结。

16.4 外部联结

16.4.1 左外联结

SELECT <表名a>.<列名a>, <表名b>.<列名b>
FROM <表名a> LEFT OUTER JOIN <表名b>
	ON <表名a>.<列名c> = <表名b>.<列名c>;

16.4.2 右外联结

SELECT <表名a>.<列名a>, <表名b>.<列名b>
FROM <表名a> RIGHT OUTER JOIN <表名b>
	ON <表名a>.<列名c> = <表名b>.<列名c>;

注意：

• 在使用 OUTER JOIN 语法时，必须使用 RIGHT 或 LEFT 关键字指定包括其所有行的表（RIGHT 指出的是 OUTER JOIN 右边的表，而 LEFT 指出的是 OUTER JOIN 左边的表）。
• 与内部联结关联两个表中的行不同的是，外部联结还包括没有关联行的行。

十七、组合查询（UNION）

17.1 不包含重复行

SELECT <列名a>, <列名a>
FROM <表名a>
WHERE <过滤条件a>
UNION 
SELECT <列名a>, <列名a>
FROM <表名b>
WHERE <过滤条件b>

17.2 包含重复行

SELECT <列名a>, <列名a>
FROM <表名a>
WHERE <过滤条件a>
UNION ALL
SELECT <列名a>, <列名a>
FROM <表名b>
WHERE <过滤条件b>

UNION 规则：

• UNION 必须由两条或两条以上的 SELECT 语句组成，语句之间用关键字 UNION 分隔。
• UNION 中的每个查询必须包含相同的列、表达式或聚集函数。
• 列数据类型必须兼容：类型不必完全相同，但必须是 DBMS 可以隐含地转换的类型。
• 在用 UNION 组合查询时，只能使用一条 ORDER BY 子句，它必须出现在最后一条 SELECT 语句之后。

十八、全文本搜索（Match、Against）

并非所有的引擎都支持全文本搜索。两个最常使用的引擎为 MyISAM 和 InnoDB，前者支持全文本搜索，而后者不支持。

全文本搜索的一个重要部分就是对结果排序。具有较高等级的行先返回（因为这些行很可能是你真正想要的行）。

不包含搜索文本的行等级为 0。包含搜索文本的行都有一个等级值，文本中词靠前的行的等级值比词靠后的行的等级值高。

如果指定多个搜索项，则包含多数匹配词的那些行将具有比包含较少词（或仅有一个匹配）的那些行高的等级值。

18.1 启用全文本搜索支持

为了进行全文本搜索，必须索引被搜索的列，而且要随着数据的改变不断地重新索引。

一般在创建表时启用全文本搜索。CREATE TABLE 语句接受 FULLTEXT 子句，它给出被索引列的一个逗号分隔的列表。

更新索引要花时间，如果正在导入数据到一个新表，此时不应该启用 FULLTEXT 索引。应该首先导入所有数据，然后再修改表，定义 FULLTEXT。

18.2 进行全文本搜索

SELECT <列名>
FROM <表名>
WHERE Match(<列名>) Against('<搜索文本>');

注意：

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

18.3 使用查询扩展

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

• 首先，进行一个基本的全文本搜索，找出与搜索条件匹配的所有
  行。
• 其次，MySQL 检查这些匹配行并选择所有有用的词。
• 再其次，MySQL 再次进行全文本搜索，这次不仅使用原来的条件，
  而且还使用所有有用的词。

SELECT <列名>
FROM <表名>
WHERE Match(<列名>) Against('<搜索文本>' WITH QUERY EXPANSION);

18.4 布尔文本搜索

布尔方式不同于迄今为止使用的全文本搜索语法的地方在于， 即使没有定义 FULLTEXT 索引，也可以使用它。

SELECT <列名>
FROM <表名>
WHERE Match(<列名>) Against('<布尔搜索文本>' IN BOOLEAN MODE);

18.4.1 全文本布尔操作符

布尔操作符	说明

• | 包含，词必须存在

• | 排除，词必须不出现
  |> | 包含，而且增加等级值
  < | 包含，且减少等级值
  () | 把词组成子表达式（允许这些子表达式作为一个组被包含、排除、排列等）
  ~ | 取消一个词的排序值

• | 词尾的通配符
  “” | 定义一个短语（与单个词的列表不一样，它匹配整个短语以便包含或排除这个短语）

18.4.2 应用实例

productnotes 产品注释表（部分）：

note_id（唯一注释id）	node_text（注释文本）
101	Customer complaint:\r\nSticks not individually wrapped, too easy to mistakenly detonate all at once.\r\nRecommend individual wrapping.
102	Can shipped full, refills not available.\r\nNeed to order new can if refill needed.
103	Safe is combination locked, combination not provided with safe.\r\nThis is rarely a problem as safes are typically blown up or dropped by customers.
104	Quantity varies, sold by the sack load.\r\nAll guaranteed to be bright and orange, and suitable for use as rabbit bait.
105	Included fuses are short and have been known to detonate too quickly for some customers.\r\nLonger fuses are available (item FU1) and should be recommended.
106	Matches not included, recommend purchase of matches or detonator (item DTNTR).
107	Please note that no returns will be accepted if safe opened using explosives.
108	Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.
109	Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.
110	Customer complaint: rabbit has been able to detect trap, food apparently less effective now.
111	Shipped unassembled, requires common tools (including oversized hammer).
112	Customer complaint:\r\nCircular hole in safe floor can apparently be easily cut with handsaw.
113	Customer complaint:\r\nNot heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.
114	Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added.\r\nComment forwarded to vendor.

18.4.2.1 实例一

输入：

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('+rabbit +bait' IN BOOLEAN MODE);

分析：这个搜索匹配包含词 rabbit 和 bait 的行。

18.4.2.2 实例二

输入：

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit bait' IN BOOLEAN MODE);

分析：没有指定操作符，这个搜索匹配包含 rabbit 和 bait 中的至少一个词的行。

18.4.2.3 实例三

输入：

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('"rabbit bait"' IN BOOLEAN MODE);

分析：这个搜索匹配短语 rabbit bait 而不是匹配两个词 rabbit 和 bait。

18.4.2.4 实例四

输入：

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('>rabbit <carrot' IN BOOLEAN MODE);

分析：匹配 rabbit 和 carrot，增加前者的等级，降低后者的等级。

18.4.2.5 实例五

输入：

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('+safe +(<combination)' IN BOOLEAN MODE);

分析：这个搜索匹配词 safe 和 combination，降低后者的等级。

18.5 全文本搜索的使用说明

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

十九、插入数据（INSERT INTO）

19.1 插入完整的行

INSERT INTO <表名>(
	<列名a>,
	<列名b>,
	...
) VALUES (
	'<值a>',
	'<值b>',
	...
);

注意：

• 如果提供列名，则必须对每个列出的列给出一个值。

• 如果不提供列名，则必须给每个表列提供一个值。

• 如果表的某些列满足以下某个条件，则可以在 INSERT 操作中省略。

  1.该列定义为允许 NULL 值（无值或空值）。

  2.在表定义中给出默认值。这表示如果不给出值，将使用默认值。

• 可以通过在 INSERT 和 INTO 之间添加关键字 LOW_PRIORITY，指示 MySQL 降低 INSERT 语句的优先级。这也适用于 UPDATE 和 DELETE 语句。

19.2 插入多个行

INSERT INTO <表名>(
	<列名a>,
	<列名b>,
	...
) VALUES (
	'<值a>',
	'<值b>',
	...
), (
	'<值d>',
	'<值e>',
	...
);

19.3 插入检索出的数据

INSERT INTO <表名a>(
	<列名a>,
	<列名b>,
	<列名c>,
	...
)
SELECT 
	<列名a>,
	<列名b>,
	<列名c>,
	...
FROM <表名b>;

注意：MySQL 在此使用的是列的位置，SELECT 中的第一列（不管其列名）将用来填充表列中指定的第一个列，第二列将用来填充表列中指定的第二个列，如此等等。

二十、更新和删除数据（UPDATE、DELETE）

20.1 更新数据

UPDATE <表名>
SET <列名a> = '<新值a>',
	<列名b> = '<新值b>',
	...
WHERE <过滤条件>;

注意：

• 不要省略 WHERE 子句，因为稍不注意，就会更新表中所有行。
• UPDATE 语句中可以使用子查询，使得能用 SELECT 语句检索出的数据更新列数据。
• 可使用 IGNORE 关键字避免更新操作被异常取消。
• 为了删除某个列的值，可设置它为 NULL（假如表定义允许 NULL 值）。

20.2 删除数据

DELETE FROM <表名>
WHERE <过滤条件>;

注意：

• 不要省略 WHERE 子句，因为稍不注意，就会错误地删除表中所有行。
• 如果想从表中删除所有行，不要使用 DELETE。可使用 TRUNCATE TABLE 语句，它完成相同的工作，但速度更快（TRUNCATE 实际是删除原来的表并重新创建一个表，而不是逐行删除表中的数据。

20.3 更新和删除的指导原则

• 除非确实打算更新和删除每一行，否则绝对不要使用不带 WHERE 子句的 UPDATE 或 DELETE 语句。
• 保证每个表都有主键，尽可能像 WHERE 子句那样使用它（可以指定各主键、多个值或值的范围）。
• 在对 UPDATE 或 DELETE 语句使用 WHERE 子句前，应该先用 SELECT 进行测试，保证它过滤的是正确的记录，以防编写的 WHERE 子句不正确。
• 使用强制实施引用完整性的数据库，这样 MySQL 将不允许删除具有与其他表相关联的数据的行。

二十一、创建和操纵表（CREATE、ALTER、DROP、RENAME）

21.1 创建表

DROP TABLE IF EXISTS <表名>;
CREATE TABLE <表名> (
	<列名a> <数据类型> <是否空值>,
	<列名b> <数据类型> <是否空值>,
	...
	PRIMARY KEY (<主键列a>, <主键列b>, ...)
) ENGINE = <存储引擎> CHARACTER SET = <字符集>;

21.1.1 使用NULL值

• 允许 NULL 值的列也允许在插入行时不给出该列的值。不允许 NULL 值的列不接受该列没有值的行。
• 不要把 NULL 值与空串相混淆，空串是一个有效的值，NULL 值用关键字 NULL 而不是空串指定。
• 主键中只能使用不允许 NULL 值的列。

21.1.2 主键

• 表中的每个行必须具有唯一的主键值。
• 如果主键使用单个列，则它的值必须唯一。如果使用多个列，则这些列的组合值必须唯一。

21.1.3 使用 AUTO_INCREMENT

CREATE TABLE <表名> (
	<列名> <数据类型>, <是否空值> AUTO_INCREMENT,
	...
) AUTO_INCREMENT = <初始值> ...;

• 列尾追加 AUTO_INCREMENT 表示本列每当增加一行时自动增量。
• 每个表只允许一个 AUTO_INCREMENT 列，而且它必须被索引。
• 可使用 last_insert_id() 函数获得指定表的最后一个 AUTO_INCREMENT 值。

21.1.4 使用默认值

CREATE TABLE <表名> (
	<列名> <数据类型>, <是否空值> DEFAULT <默认值>,
	...
) ...;

注意：

• MySQL 不允许使用函数作为默认值，它只支持常量。

21.1.5 引擎类型

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

注意：

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

21.2 更新表

21.2.1 增加列

ALTER TABLE <表名>
ADD <列名> <数据类型>;

21.2.2 删除列

ALTER TABLE <表名>
DROP COLUMN <列名>;

21.2.3 增加外键约束

ALTER TABLE <表名a>
ADD CONSTRAINT <外键约束名>
FOREIGN KEY (<外键名>)
REFERENCES <表名b> (<外键名>);

21.2.3 删除表

DROP TABLE <表名>;

21.2.4 重命名表

RENAME TABLE <旧表名> TO <新表名>;

二十二、使用视图（VIEW）

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

22.1 为什么使用视图

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

22.2 视图的规则和限制

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

22.3 创建视图

CREATE VIEW <视图名> AS
SELECT <列名a>, <列名b>, <列名c>...
FROM <表名a>, <表名b>, <表名c>, ...
WHERE <表名a>.<列名d> = <表名b>.<列名d>
	AND <表名b>.<列名e> = <表名c>.<列名e>
	...;

22.4 更新视图

更新一个视图将更新其基表，如果视图定义中有以下操作，则不能进行视图的更新（MySQL不能正确地确定被更新的基数据）：

• 分组（使用 GROUP BY 和 HAVING）。
• 联结。
• 子查询。
• 并。
• 聚集函数（Min()、Count()、Sum() 等）。
• DISTINCT。
• 导出（计算）列。

二十三、使用存储过程（PROCEDURE）

23.1 为什么要使用存储过程

• 通过把处理封装在容易使用的单元中，简化复杂的操作。
• 由于不要求反复建立一系列处理步骤，这保证了数据的完整性。
• 简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化，只需要更改存储过程的代码。
• 提高性能。因为使用存储过程比使用单独的SQL语句要快。
• 存在一些只能用在单个请求中的 MySQL 元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码。

23.2 执行存储过程

CALL <存储过程名>();

23.2 创建存储过程

CREATE PROCEDURE <存储过程名>()
BEGIN
	<处理语句a>;
	<处理语句b>;
	...
END;

23.3 删除存储过程

DROP PROCEDURE <存储过程名>;

23.4 使用参数

• IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）。
• OUT 输出参数：表示过程向调用者传出值(可以返回多个值，传出值只能是变量）。
• INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）。

23.4.1 应用实例一

问题：执行名为 productpricing 的存储过程，它计算并返回产品的最低、最高和平均价格。

products 产品表（部分）：

prod_id（唯一产品id）	prod_price（产品价格）
ANV01	5.99
ANV02	9.99
ANV03	14.99
DTNTR	13.00
FB	10.00
FC	2.50
FU1	3.42
JP1000	35.00
JP2000	55.00
OL1	8.99
SAFE	50.00
SLING	4.49
TNT1	2.50
TNT2	10.00

输入：

-- 创建存储过程
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;

-- 执行存储过程
CALL productpricing(
	@pricelow,
	@pricehigh,
	@priceaverage
)

-- 检索产品信息
SELECT 	@pricelow, @pricehigh, @priceaverage;

输出：

@pricelow	@pricehigh	@priceaverage
55.00	2.50	16.13

23.4.2 应用实例二

问题：使用 IN 和 OUT 参数。ordertotal 接受订单号并返回该订单的合计。

orderitems 订单产品表：

order_num（订单号）	prod_id（产品id）	quantity（产品数量）	item_price（产品价格）
20005	ANV01	10	5.99
20005	ANV02	3	9.99
20005	TNT2	5	10.00
20005	FB	10	10.00
20006	JP2000	1	55.00
20007	TNT2	100	10.00
20008	FC	50	2.50
20009	FB	1	10.00
20009	OL1	1	8.99
20009	SLING	1	4.49
20009	ANV03	1	14.99

输入：

-- 创建存储过程
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;

-- 执行存储过程
CALL ordertotal(20005, @total);

-- 检索订单信息
SELECT @total;

输出：

@total
149.87

23.5 建立智能存储过程

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

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

输入：

-- Name: ordertotal
-- Parameters:
-- 			onumber = 订单号
--			taxable = 如果不征税，则为 0；如果应税，则为 1
-- 			ototal = 订单合计
CREATE PROCEDURE ordertotal(
	IN onumber INT,
	IN taxable BOOLEAN,
	OUT ototal DECIMAL(8, 2)
) COMMENT 'Obtain order total, optionally adding tax'
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; -- 最后保存到局部变量 ototal
END;

CALL ordertotal(20005, 0, @total);
SELECT @total;

输出：

@total
149.87

CALL ordertotal(20005, 1, @total);
SELECT @total;

@total
158.86

23.6 检查存储过程

显示用来创建一个存储过程的 CREATE 语句：

SHOW CREATE PROCEDURE <存储过程名>;

获得包括何时、由谁创建等详细信息的存储过程列表：

SHOW PROCEDURE STATUS LIKE '<存储过程名>';

二十四、使用游标（PROCEDURE）

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

orders 顾客订单表（部分）：

order_num（唯一订单号）	cust_id（顾客id）
20005	10001
20006	10003
20007	10004
20008	10005
20009	10001

24.1 创建游标

-- 创建存储过程
CREATE PROCEDURE processorders()
BEGIN
	DECLARE ordernumbers CURSOR -- 声明游标
	FOR
	SELECT order_num FROM orders; -- 从 orders 表中检索数据
END;

分析：这个存储过程并没有做很多事情，DECLARE 语句用来定义和命名游标，这里为 ordernumbers。存储过程处理完成后，游标就消失（因为它局限于存储过程）。

24.2 打开和关闭游标

-- 创建存储过程
CREATE PROCEDURE processorders()
BEGIN
	DECLARE ordernumbers CURSOR -- 声明游标
	FOR
	SELECT order_num FROM orders; -- 从 orders 表中检索数据
	
	OPEN ordernumbers; -- 打开游标
	
	CLOSE ordernumbers; -- 关闭游标
END;

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

24.3 使用游标数据

-- 创建存储过程
CREATE PROCEDURE precessorders()
BEGIN
	DECLARE o INT; -- 声明局部变量 o
	
	DECLARE ordernumbers CURSOR -- 声明游标
	FOR
	SELECT order_num FROM orders; -- 从 orders 表中检索数据
	
	OPEN ordernumbers; -- 打开游标
	
	FETCH ordernumbers INTO o; -- 检索 order_num 列到局部变量 o 中
	
	CLOSE ordernumbers; -- 关闭游标
END;

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

-- 创建存储过程
CREATE PROCEDURE precessorders()
BEGIN
	DECLARE done BOOLEAN DEFAULT (0); -- 声明局部变量 done 默认值为 0
	DECLARE o INT; --  -- 声明局部变量 o
	
	DECLARE ordernumbers CURSOR -- 声明游标
	FOR
	SELECT order_num FROM orders; -- 从 orders 表中检索数据
	
	-- 这条语句定义了一个 CONTINUE HANDLER，它是在条件出现时被执行的代码。
	-- 这里，它指出当 SQLSTATE '02000' 出现时，SET done = 1。
	-- SQLSTATE '02000' 是一个未找到条件，当 REPEAT 由于没有更多的行供循环
	-- 而不能继续时，出现这个条件。
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
	
	OPEN ordernumbers; -- 打开游标

	REPEAT -- 遍历所有行
		FETCH ordernumbers INTO o; -- 检索 order_num 列到局部变量 o 中
	UNTIL done END REPEAT; -- 当局部遍历 done 为 1 时结束遍历
	
	CLOSE ordernumbers; -- 关闭游标
END;

分析：与前一个例子一样，这个例子使用 FETCH 检索当前 order_num 到声明的名为 o 的变量中。但与前一个例子不一样的是，这个例子中的 FETCH 是在 REPEAT 内，因此它反复执行直到 done 为真（由UNTIL done END REPEAT; 规定）。为使它起作用，用一个 DEFAULT 0（假，不结束）定义变量 done。

存储过程 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;

-- 执行存储过程
CALL ordertotal(20005, @total);

-- 检索订单信息
SELECT @total;

-- 创建存储过程
CREATE PROCEDURE precessorders()
BEGIN
	DECLARE done BOOLEAN DEFAULT 0; -- 声明局部变量 done 默认值为 0
	DECLARE o INT;-- 声明局部变量 o
	DECLARE t DECIMAL(8, 2);-- 声明局部变量 t
	
	DECLARE ordernumbers CURSOR -- 声明游标
	FOR
	SELECT order_num FROM orders; -- 从 orders 表中检索数据
	
	-- 设置 done = 1 的触发条件
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
	
	CREATE TABLE IF NOT EXISTS ordertotals ( -- 创建 ordertotals 表存储查询结果
		order_num INT,
		total DECIMAL(8, 2)
	);
	
	OPEN ordernumbers; -- 打开游标
	
	REPEAT -- 遍历所有行
		FETCH ordernumbers INTO o; -- 检索 order_num 列到局部变量 o 中
		CALL ordertotal(o, 1, t); -- 获取该订单的总数
		INSERT INTO ordertotals(order_num, total) -- 将订单和总数插入 ordertotals 表
			VALUES(o, t);
	UNTIL done END REPEAT; -- 结束循环
	
	CLOSE ordernumbers; -- 关闭游标
END;

分析：在这个例子中，我们增加了另一个名为t的变量（存储每个订单的合计）。此存储过程还在运行中创建了一个新表（如果它不存在的话），名为 ordertotals。这个表将保存存储过程生成的结果。

注意：

• 用 DECLARE 语句定义的局部变量必须在定义任意游标或句柄之前定义，而句柄必须在游标之后定义。

二十五、使用触发器（TRIGGER）

products 产品表（部分）：

prod_id（唯一产品id） | prod_price（产品价格）
|:-|:-|:-|
ANV01 | 5.99
ANV02 | 9.99
ANV03 | 14.99
DTNTR | 13.00
FB | 10.00
FC | 2.50
FU1 | 3.42
JP1000 | 35.00
JP2000 | 55.00
OL1 | 8.99
SAFE | 50.00
SLING | 4.49
TNT1 | 2.50
TNT2 | 10.00

25.1 创建触发器

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

• 唯一的触发器名；
• 触发器关联的表；
• 触发器应该响应的活动（DELETE、INSERT、UPDATE）；
• 触发器何时执行，处理之前（BBEFORE）或之后（AFTER）。

CREATE TRIGGER newproduct -- 创建名为 newproduct 的新触发器
AFTER INSERT ON products -- 触发器将在 INSERT 语句成功执行后执行
FOR EACH ROW -- 代码对每个行执行
SELECT 'Product added' INTO @info; -- 将信息赋值到变量 @info

输入：

INSERT INTO products -- 添加一行到 products 表中
VALUES (
	'ANV999',
	1001,
	'.5 ton anvil',
	5.99,
	'aaaaa'
);

SELECT @info -- 查看变量 @info

输出：

@info
Product added

注意：

• 触发器名必须在每个表中唯一，但不是在每个数据库中唯一。
• 只有表才支持触发器，视图不支持（临时表也不支持）。
• 触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器。因此，每个表最多支持 6 个触发器（每条INSERT、UPDATE 和 DELETE的之前和之后）。
• 如果 BEFORE 触发器或语句本身失败，MySQL 将不执行 AFTER 触发器（如果有的话）。

25.2 删除触发器

DROP TRIGGER newproduct; -- 删除名为 newproduct 的触发器

注意：

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

25.3 使用触发器

orders 顾客订单表（部分）：

order_num（唯一订单号）	cust_id（顾客id）
20005	10001
20006	10003
20007	10004
20008	10005
20009	10001

25.3.1 INSERT 触发器

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

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

创建触发器：

CREATE TRIGGER neworder -- 创建名为 neworder 的新触发器
AFTER INSERT ON orders -- 触发器将在 INSERT 语句成功执行后执行
FOR EACH ROW -- 代码对每个行执行
SELECT NEW.order_num INTO @info; -- 将信息赋值到变量 @info

分析：在插入一个新订单到 orders 表时，MySQL 生成一个新订单号并保存到 order_num 中。触发器从 NEW. order_num 取得这个值并返回它。

输入：

INSERT INTO `orders` VALUES (99999, '2005-09-01 00:00:00', 10001);
SELECT @info -- 查看变量 @info

输出：

@info
99999

25.3.2 DELETE 触发器

DELETE触发器在DELETE语句执行之前或之后执行。

• 在 DELETE 触发器代码内，你可以引用一个名为 OLD 的虚拟表，访问被删除的行。
• OLD 中的值全都是只读的，不能更新。

创建 archive_orders 存档表：

DROP TABLE IF EXISTS `archive_orders`;
CREATE TABLE `archive_orders`  (
  `order_num` int(0) NOT NULL AUTO_INCREMENT,
  `order_date` datetime(0) NOT NULL,
  `cust_id` int(0) NOT NULL,
  PRIMARY KEY (`order_num`) USING BTREE,
  INDEX `fk_orders_customers`(`cust_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

创建触发器：

CREATE TRIGGER deleteorder -- 创建名为 deleteorder 的新触发器
BEFORE DELETE ON orders -- 触发器将在 DELETE 语句执行前执行
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的存档表中。

输入：

DELETE FROM orders -- 删除订单
WHERE order_num = '99999';

SELECT * -- 检索存档表
FROM archive_orders;

输出：

order_num（唯一订单号）	cust_id（顾客id）
99999	10001

25.3.3 UPDATE 触发器

UPDATE 触发器在 UPDATE 语句执行之前或之后执行。

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

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

CREATE TRIGGER updatevendor -- 创建名为 updatevendor 的新触发器
BEFORE UPDATE ON vendors -- 触发器将在 UPDATE 语句执行前执行
FOR EACH ROW -- 代码对每个行执行
SET NEW.vend_state = Upper(NEW.vend_state); -- 更新表行的值

分析：任何数据净化都需要在 UPDATE 语句之前进行，就像这个例子中一样。每次更新一个行时，NEW.vend_state 中的值（将用来更新表行的值）都用 Upper(NEW.vend_state) 替换。

二十六、使用索引（INDEX、UNIQUE、FULLTEXT、HASH）

26.1 主键索引

创建表的同时创建索引：

CREATE TABLE <表名> (
	<列名> <数据类型> NOT NULL,
	PRIMARY KEY(<列名>)
);

• 在 InnoDB 存储引擎中，表都是根据主键顺序组织存放的，每张表都有一个主键。

• 如果创建表没有显示地定义主键，引擎会按如下方式选择或创建主键：

  首先判断表中是否有非空的唯一索引，如果有，则该列为主键。

  如果不符合上述条件，引擎会自动创建一个 6 字节大小的指针。

• 当表中有多个非空唯一索引时，引擎将选择建表时第一个定义的非空唯一索引为主键。

26.2 普通索引

直接创建索引：

CREATE INDEX <索引名> ON <表名>(<列名>(<索引长度>))

修改表结构：

ALTER TABLE <表名> ADD INDEX <索引名> ON (<列名>(<索引长度>))

创建表的同时创建索引：

CREATE TABLE <表名> (
	<列名> <数据类型> <是否空值>,
	INDEX <索引名> (<列名>(<索引长度>))
);

26.3 唯一索引

直接创建索引：

CREATE UNIQUE INDEX <索引名> ON <表名>(<列名>(<索引长度>))

修改表结构：

ALTER TABLE <表名> ADD UNIQUE INDEX <索引名> ON (<列名>(<索引长度>))

创建表的同时创建索引：

CREATE TABLE <表名> (
	<列名> <数据类型> <是否空值>,
	UNIQUE <索引名> (<列名>(<索引长度>))
);

26.4 全文索引

直接创建索引：

CREATE FULLTEXT INDEX <索引名> ON <表名>(<列名>(<索引长度>))

修改表结构：

ALTER TABLE <表名> ADD FULLTEXT INDEX <索引名> ON (<列名>(<索引长度>))

创建表的同时创建索引：

CREATE TABLE <表名> (
	<列名> <数据类型> <是否空值>,
	FULLTEXT <索引名> (<列名>(<索引长度>))
);

26.5 组合索引

直接创建索引：

CREATE UNIQUE INDEX <索引名> ON <表名>(<列名a>(<索引长度>), <列名b>(<索引长度>))

修改表结构：

ALTER TABLE <表名> ADD UNIQUE INDEX <索引名> ON (<列名a>(<索引长度>), <列名a>(<索引长度>))

创建表的同时创建索引：

CREATE TABLE <表名> (
	<列名> <数据类型> <是否空值>,
	UNIQUE <索引名> (<列名>(<索引长度>), <列名a>(<索引长度>))
);

26.6 哈希索引（仅 Memory 显式支持）

直接创建索引：

CREATE INDEX <索引名> USING HASH ON <表名>(<列名>(<索引长度>));

创建表时同时创建索引：

CREATE TABLE <表名> (
	<列名> <数据类型> <是否空值>,
	KEY USING HASH <索引名> (<列名>(<索引长度>))
) ENGINE = MEMORY;

注意：

• InnoDB 存储引擎使用自适应哈希索引。
• MyISAM 存储引擎不支持哈希索引。

26.7 优化前缀索引

优化方式：计算完整列的选择性，并使前缀的选择性接近于完整列的选择性。

具体如下：

1. 完整列的选择性：SELECT COUNT(DISTINCT <列名>) / COUNT(*) FROM <表名>;
2. 前缀选择性：SELECT COUNT(DISTINCT Left(<列名>, <前缀长度>)) / COUNT(*) FROM <表名>;
3. 调整前缀长度，使其选择性接近完整列的选择性。

26.8 查看索引

SHOW INDEX FROM <表名>;

26.9 删除索引

DROP INDEX <索引名> ON TABLE <表名>;

26.10 EXPLAIN 命令

EXPLAIN SELECT ... FROM ...;

字段	说明
id	选择标识符
select_type	查询的类型
table	输出结果集的表
partitions	匹配的分区
type	访问的类型
possible_keys	可能使用的索引
key	实际使用的索引
key_len	索引字段的长度
ref	列与索引的比较
rows	扫描出的行数（估算值）
filtered	按表条件过滤的行百分比
Extra	执行情况的描述和说明

二十七、管理事务处理（TRANSACTION）

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

术语：

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

27.1 使用 ROLLBACK

SELECT * FROM <表名a>; -- 检索表a

START TRANSACTION; -- 开始事务
DELETE FROM <表名a>; -- 删除表a
SELECT * FROM <表名a>; -- 检索表a为空
ROLLBACK; -- 回退开始事务后的所有语句

SELECT * FROM <表名a>; -- 检索表a不为空

27.2 使用 COMMIT

START TRANSACTION; -- 开始事务
DELETE FROM <表名a> WHERE <过滤条件a>; -- 删除表a数据
DELETE FROM <表名b> WHERE <过滤条件b>; -- 删除表b数据
COMMIT; -- 提交事务

分析：最后的 COMMIT 语句仅在不出错时写出更改。如果第一条 DELETE 起作用，但第二条失败，则 DELETE 不会提交（实际上，它是被自动撤销的）。

27.3 使用保留点

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

SELECT * FROM <表名a>; -- 检索表a

START TRANSACTION; -- 开始事务
SAVEPOINT delete1; -- 创建占位符 delete1
DELETE FROM <表名a>; -- 删除表a
SELECT * FROM <表名a>; -- 检索表a为空
ROLLBACK TO delete1; -- 回退到占位符 delete1

SELECT * FROM <表名a>; -- 检索表a不为空

27.4 更改默认的提交行为

默认的 MySQL 行为是自动提交所有更改。autocommit 标志决定是否自动提交更改，不管有没有 COMMIT 语句。

设置 autocommit 为 0（假）指示 MySQL 不自动提交更改（直到 autocommit 被设置为真为止）。

SET autocommit = 0;

二十八、使用字符集和校对顺序（CHARACTER、COLLATE）

术语：

• 字符集为字母和符号的集合；
• 编码为某个字符集成员的内部表示；
• 校对为规定字符如何比较的指令。

查看所支持校对的完整列表。

SHOW CHARACTER SET;

创建一个表，并且指定一个字符集和一个校对顺序。

CREATE TABLE <表名> (
	<列名a> <数据类型> <是否空值>,
	<列名b> <数据类型> <是否空值>,
	...
) DEFAULT CHARACTER SET <字符集> COLLATE <校对序列>;

对整个表以及特定列指定 CHARACTER SET 和 COLLATE。

CREATE TABLE <表名> (
	<列名a> <数据类型> <是否空值>,
	<列名b> <数据类型> <是否空值> CHARACTER SET <字符集> COLLATE <校对序列>,
	...
) DEFAULT CHARACTER SET <字符集> COLLATE <校对序列>;

二十九、管理用户（GRANT、REVOKE）

mysql 数据库有一个名为 user 的表，它包含所有用户账号。

USE mysql;
SELECT user FROM user; -- 获得所有用户账号列表

29.1 创建用户账号

CREATE USER <用户名> IDENTIFIED BY '<口令>';

重命名一个用户账号。

RENAME USER <原用户名> TO <新用户名>;

29.2 删除用户账号

DROP USER <用户名>;

29.3 设置访问权限

查看赋予用户账号的权限。

SHOW GRANTS FOR <用户名>;

对用户授予 SELECT 访问权限。

GRANT SELECT ON <数据库名>.<表名> TO <用户名>;

对用户撤销 SELECT 访问权限。

REVOKE SELECT ON <数据库名>.<表名> FROM <用户名>;

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

• 整个服务器，使用 GRANT ALL 和 REVOKE ALL。
• 整个数据库，使用 ON database.*。
• 特定的表，使用 ON database.table。
• 特定的列。
• 特定的存储过程。

权限	说明
ALL	除 GRANT OPTION 外的所有权限
ALTER	使用 ALTER TABLE
ALTER ROUTINE	使用 ALTER PROCEDURE 和 DROP PROCEDURE
CREATE	使用 CREATE TABLE
CREATE ROUTINE	使用 CREATE PROCEDURE
CREATE TEMPORARY TABLES	使用 CREATE TEMPORARY TABLE
CREATE USER	使用 CREATE USER、DROP USER、RENAME USER 和 REVOKE ALL PRIVILEGES
CREATE VIEW	使用 CREATE VIEW
DELETE	使用 DELETE
DROP	使用 DROP TABLE
EXECUTE	使用 CALL 和存储过程
FILE	使用 SELECT INTO OUTFILE 和 LOAD DATA INFILE
GRANT OPTION	使用 GRANT 和 REVOKE
INDEX	使用 CREATE INDEX 和 DROP INDEX
INSERT	使用 INSERT
LOCK TABLES	使用 LOCK TABLES
PROCESS	使用 SHOW FULL PROCESSLIST
RELOAD	使用 FLUSH
REPLICATION CLIENT	服务器位置的访问
REPLICATION SLAVE	由复制从属使用
SELECT	使用 SELECT
SHOW DATABASES	使用 SHOW DATABASES
SHOW VIEW	使用 SHOW CREATE VIEW
SHUTDOWN	使用 mysqladmin shutdown（用来关闭 MySQL）
SUPER	使用 CHANGE MASTER、KILL、LOGS、PURGE、MASTER 和 SET GLOBAL。还允许 mysqladmin 调试登录
UPDATE	使用 UPDATE
USAGE	无访问权限

注意：

• 可通过列出各权限并用逗号分隔，将多条 GRANT 语句串在一起。

29.4 更改口令

更改指定用户口令。

SET PASSWORD FOR <用户名> = Password('<新口令>');

更改当前用户口令。

SET PASSWORD = Password('<新口令>');

三十、数据库维护（ANALYZE、CHECK）

30.1 备份数据

• 使用命令行实用程序 mysqldump 转储所有数据库内容到某个外部文件。在进行常规备份前这个实用程序应该正常运行，以便能正确地备份转储文件。
• 可用命令行实用程序 mysqlhotcopy 从一个数据库复制所有数据（并非所有数据库引擎都支持这个实用程序）。
• 可以使用 MySQL 的 BACKUP TABLE 或 SELECT INTO OUTFILE 转储所有数据到某个外部文件。这两条语句都接受将要创建的系统文件名，此系统文件必须不存在，否则会出错。数据可以用 RESTORE TABLE 来复原。

30.2 进行数据库维护

检查表键是否正确。

ANALYZE TABLE <表名a>, <表名b>, ...;

针对许多问题对表进行检查。

CHECK TABLE <表名a>, <表名b>, ...;

注意：

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

30.3 诊断启动问题

在排除系统启动问题时，首先应该尽量用手动启动服务器。MySQL 服务器自身通过在命令行上执行 mysqld 启动。下面是几个重要的 mysqld 命令行选项：

• –help 显示帮助 —— 一个选项列表。
• –safe-mode 装载减去某些最佳配置的服务器。
• –verbose 显示全文本消息（为获得更详细的帮助消息与 --help 联合使用）。
• –version 显示版本信息然后退出。

30.4 查看日志文件

• 错误日志：它包含启动和关闭问题以及任意关键错误的细节。此日志通常名为 hostname.err，位于 data 目录中。此日志名可用 --log-error 命令行选项更改。
• 查询日志：它记录所有 MySQL 活动，在诊断问题时非常有用。此日志文件可能会很快地变得非常大，因此不应该长期使用它。此日志通常名为 hostname.log，位于 data 目录中。此名字可以用 --log 命令行选项更改。
• 二进制日志：它记录更新过数据（或者可能更新过数据）的所有语句。此日志通常名为 hostname-bin，位于 data 目录内。此名字可以用 --log-bin 命令行选项更改。
• 缓慢查询日志：顾名思义，此日志记录执行缓慢的任何查询。这个日志在确定数据库何处需要优化很有用。此日志通常名为 hostname-slow.log ， 位于data 目录中。此名字可以用 --log-slow-queries 命令行选项更改。

注意：

• 在使用日志时，可用 FLUSH LOGS 语句来刷新和重新开始所有日志文件。

三十一、改善性能

• 首先，MySQL（与所有 DBMS 一样）具有特定的硬件建议。在学习和研究 MySQL 时，使用任何旧的计算机作为服务器都可以。但对用于生产的服务器来说，应该坚持遵循这些硬件建议。
• 一般来说，关键的生产 DBMS 应该运行在自己的专用服务器上。
• MySQL 是用一系列的默认设置预先配置的，从这些设置开始通常是很好的。但过一段时间后你可能需要调整内存分配、缓冲区大小等。（为查看当前设置，可使用 SHOW VARIABLES; 和 SHOWSTATUS;。）
• 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 数据类型

串数据类型

最常用的数据类型是串数据类型。它们存储串，如名字、地址、电话号码、邮政编码等。有两种基本的串类型，分别为定长串和变长串。

数据类型	说明
CHAR	1～255 个字符的定长串。它的长度必须在创建时指定，否则 MySQL 假定为 CHAR(1)
ENUM	接受最多 64K 个串组成的一个预定义集合的某个串
LONGTEXT	与 TEXT 相同，但最大长度为 4GB
MEDIUMTEXT	与 TEXT 相同，但最大长度为16K
SET	接受最多 64 个串组成的一个预定义集合的零个或多个串
TEXT	最大长度为 64K 的变长文本
TINYTEXT	与 TEXT 相同，但最大长度为 255 字节
VARCHAR	长度可变，最多不超过 255 字节。如果在创建时指定为 VARCHAR(n)，则可存储 0 到 n 个字符的变长串（其中 n ≤ 255）

数值数据类型

数值数据类型存储数值。

数据类型	说明
BIT	位字段，1～64 位。（在 MySQL 5 之前，BIT 在功能上等价于 TINYINT
BIGINT	整数值，支持 -9223372036854775808 ～ 9223372036854775807（如果是 UNSIGNED，为 0 ～ 18446744073709551615）的数
BOOLEAN（或 BOOL）	布尔标志，或者为 0 或者为 1，主要用于开/关（on / off）标志
DECIMAL（或 DEC）	精度可变的浮点值
DOUBLE	双精度浮点值
FLOAT	单精度浮点值
INT（或INTEGER）	整数值，支持 -2147483648 ～ 2147483647（如果是 UNSIGNED，为 0 ～ 4294967295）的数
MEDIUMINT	整数值，支持 -8388608～8388607（如果是UNSIGNED，为0～16777215）的数
REAL	4 字节的浮点值
SMALLINT	整数值，支持 -32768 ～ 32767（如果是 UNSIGNED，为 0 ～ 65535）的数
TINYINT	整数值，支持 -128 ～ 127（如果为 UNSIGNED，为 0 ～ 255）的数

日期和时间数据类型

MySQL 使用专门的数据类型来存储日期和时间值。

数据类型	说明
DATE	表示 1000-01-01 ～ 9999-12-31 的日期，格式为 YYYY-MM-DD
DATETIME	DATE 和 TIME 的组合
TIMESTAMP	功能和 DATETIME 相同（但范围较小）
TIME	格式为 HH:MM:SS
YEAR	用 2 位数字表示，范围是 70（1970 年）～ 69（2069 年），用 4 位数字表示，范围是 1901 年 ～ 2155 年

二进制数据类型

二进制数据类型可存储任何数据（甚至包括二进制信息），如图像、多媒体、字处理文档等。

数据类型	说明
BLOB	Blob 最大长度为 64KB
MEDIUMBLOB	Blob 最大长度为 16MB
LONGBLOB	Blob 最大长度为 4GB
TINYBLOB	Blob 最大长度为 255 字节

写在最后：

✨一个值得尝试的GPT变现小项目✨

扫描下方二维码或关注微信公众号：CodeFish 回复关键字 “电子书” 即可获取本文章相关书籍资料！

更多优质电子书资源持续更新中…