【笔记整理 - MySQL】

资料来源：《MySQL必知必会》

错误记录

登录时报错ERROR 1045 (28000)

打开my.ini文件，添加一行：

...
[mysqld]
# skip-grant-tables 这一行
# 设置3306端口
port = 3306
...

可以跳过登录密码。然后再修改root用户的密码。

修改密码是用了下方代码

ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

未及时断开连接

错误：将数据库断开函数mysql_close放在MyDB类的析构函数中。而MyDB对象作为全局变量使用。最后导致大量连接未能及时关闭。

查看当前的连接：show processlist;

mysql> show processlist;
+----+-----------------+-----------+---------+---------+-------+------------------------+------------------+
| Id | User            | Host      | db      | Command | Time  | State                  | Info             |
+----+-----------------+-----------+---------+---------+-------+------------------------+------------------+
|  5 | event_scheduler | localhost | NULL    | Daemon  | 20181 | Waiting on empty queue | NULL             |
| 14 | root            | localhost | db_test | Sleep   | 19718 |                        | NULL             |
| 23 | root            | localhost | db_test | Sleep   | 13215 |                        | NULL             |
| 24 | root            | localhost | db_test | Sleep   | 13166 |                        | NULL             |
| 25 | root            | localhost | db_test | Sleep   | 13032 |                        | NULL             |
| 26 | root            | localhost | db_test | Sleep   | 12954 |                        | NULL             |
| 27 | root            | localhost | db_test | Sleep   | 12947 |                        | NULL             |
| 28 | root            | localhost | db_test | Sleep   | 12826 |                        | NULL             |
| 29 | root            | localhost | db_test | Sleep   | 12491 |                        | NULL             |
| 30 | root            | localhost | db_test | Sleep   | 12445 |                        | NULL             |
| 31 | root            | localhost | db_test | Sleep   | 12370 |                        | NULL             |
| 32 | root            | localhost | db_test | Sleep   | 12252 |                        | NULL             |
| 33 | root            | localhost | db_test | Sleep   | 12233 |                        | NULL             |
| 34 | root            | localhost | db_test | Sleep   | 12112 |                        | NULL             |
| 35 | root            | localhost | db_test | Sleep   | 12021 |                        | NULL             |
| 36 | root            | localhost | db_test | Sleep   |  1117 |                        | NULL             |
| 37 | root            | localhost | db_test | Sleep   |   997 |                        | NULL             |
| 38 | root            | localhost | db_test | Sleep   |   915 |                        | NULL             |
| 39 | root            | localhost | db_test | Query   |     0 | init                   | show processlist |
| 40 | root            | localhost | db_test | Sleep   |   560 |                        | NULL             |
| 41 | root            | localhost | db_test | Sleep   |   408 |                        | NULL             |
| 42 | root            | localhost | db_test | Sleep   |   256 |                        | NULL             |
+----+-----------------+-----------+---------+---------+-------+------------------------+------------------+
22 rows in set (0.07 sec)

当前系统允许的最大连接数：show variables like "max_connections";

mysql> show variables like "max_connections";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 20    |
+-----------------+-------+
1 row in set, 1 warning (0.13 sec)

方法：kill (连接id)

kill 36;kill 35;kill 34;kill 33;kill 32;kill 31;kill 30;kill 29;kill 28;kill 27;kill 26;kill 25;kill 24;kill 23;kill 14;

MySQL的目录结构

bin目录：存储可执行文件；（.exe）

include目录：存储包含的头文件；（.h）

lib目录：存储库文件；

share目录：存储错误信息、字符集文件……

data目录：放置日志文件及数据库；

my.ini文件：配置文件。

MySQL服务启动和关闭

方法一：Windows服务管理；

方法二：命令行

// 管理员权限打开命令行。 同样适用于其他服务
net start mysql
net stop mysql

MySQL登录

参数：
-u：用户名
-p：密码
-V：输出版本信息并退出
-h：主机地址

// 选项和参数之间不用空格分割也可以
mysql -uroot -p123456

MySQL退出

exit
quit
\q

修改密码

shell> mysqladmin -uroot -p123456 password
// 然后输入新密码

常用命令

显示所有数据库：show databeses

mysql> show databases;

使用/切换数据库：use

USE 数据库名;

select（待补充完善）

本质上应该就是表的选取操作。

用于显示当前连接（选择）的信息

// 当前使用的数据库
select database();

// 当前服务器版本
select version();

// 当前时间
select now();

// 当前用户
select user();

select 选择项 字符串;

select后还能再加上一串字符串，该字符串就是检索出的表的列名。

创建数据库：create

CREATE DATABASE [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name
// [IF NOT EXISTS]：如果该数据库不存在，就创建
// [DEFAULT] CHARACTER SET [=] charset_name：指定字符集，没细说

如果用默认设置：

CREATE DATABASE db_name

修改数据库：alter

好像就只是修改使用的字符集而已。

ALTER DATABASE db_name [DEFAULT] CHARACTER SET [=] charset_name

查找相关属性/设置

// 大小写，单双引号不敏感
show variables like "character%";

// 结果
+--------------------------+----------------------------------------------+
| Variable_name            | Value                                        |
+--------------------------+----------------------------------------------+
| character_set_client     | utf8                                         |
| character_set_connection | utf8                                         |
| character_set_database   | utf8                                         |
| character_set_filesystem | binary                                       |
| character_set_results    | utf8                                         |
| character_set_server     | utf8                                         |
| character_set_system     | utf8                                         |
| character_sets_dir       | D:\MySQL\mysql-8.0.23-winx64\share\charsets\ |
+--------------------------+----------------------------------------------+

删除数据库：drop

DROP DATABASE [IF EXITSTS] db_name;

查看某一张表的属性：describe

SHOW COLUMNS FROM 表名;

快捷方式：DESCRIBE 表名

检索数据

SQL语句大小写不敏感，大小写混用也无所谓。但规范将SQL关键字使用大写，方便阅读。

SELECT

SELECT 列名1,列名2,…… FROM 表名;
// 搜索处的结果未排序

// 检索所有列
SELECT * FROM 表名;

删除结果中的重复值：DISTINCT

select distinct

SELECT DISTINCT 列名 FROM 表名;

限制结果：LIMIT

限制返回结果不多于几行。

行的下标也是从0开始。

// 形式一，返回前n行
SELECT 列名 FROM 表名 LIMIT 数字n;

// 形式二：返回从第n1行之后的n2行。范围是[n1,n1+n2)
SELECT 列名 FROM 表名 LIMIT 数字n1,数字n2;

完全限定的表名

就是列加上所属的表名，表加上所属的数据库名。

SELECT 表名.列名 FROM 数据库名.表名;

将选择结果排序：ORDER BY

SELECT 列名1
FROM 表名 
ORDER BY 列名2;
// 列名1和列名2不一定是同一列也可以。

先按列1排序，遇到相同的值再按列2排序：

SELECT 列名,……
FROM 表名 
ORDER BY 列名1,列名2;

// 如果列1中所有的值都是唯一的，则不会按列2排序，即等价于“ORDER BY 列名1;”

升序降序

升序排列：从A到Z；默认排序方式，参数ASC没多大用。

降序排列：从Z到A。加上参数DESC

SELECT 列名,……
FROM 表名 
ORDER BY 列名 DESC;
// DESC只作用于直接位于其前面的列名，如果要求在多个列上降序排序，则要对每个列指定DESC

大小写区分

在字典排序顺序中，A视为与a相同，这是MySQL的默认行为。但者可通过数据库管理员设置改变。

注意子句间的次序

ORDER BY必须在FROM之后，LIMIT必须在ORDER BY之后。

过滤数据

WHERE

数据库表通常会根据特定条件提取数据的自己，需要指定搜索条件，有时也称为过滤条件。

WHERE子句位于FROM子句之后；ORDER BY位于WHERE之后。

SELECT 列名
FROM 表名
WHERE 列名=列值;
// 选择该列中值满足条件的行

WHERE子句操作符

=	
<>	// 不等于
!=  // 不等于
<
<=
>
>=
BETWEEN …… AND ……	// 在指定的2个值之间
IS NULL

例子：
SELECT prod_name, prod_price
FROM products
WHERE prod_name = 'fuses';	// MySQL默认不区分大小写

// 范围值检查	[a,b]
……
WHERE prod_price BETWEEN 5 AND 10;

// 控制检查
……
WHERE prod_price = IS NULL;

组合WHERE子句

MySQL允许给出多个WHERE子句（以AND子句或OR子句的形式）。

AND

返回的结果必须满足所有AND子句连接的条件。

SELECT prod_id, prod_name, prod_price
FROM products
WHERE vend_id = 1003 AND prod_price <= 10;
// 还能再这基础上添加更多的条件，每加一个条件就要使用一个AND

OR

返回结果匹配任一条件即可。

SELECT prod_name, prod_price
FROM products
WHERE vend_id = 1002 OR vend_id = 1003;

运算符优先级

AND大于OR。可以用()划分操作。

SELECT prod_name, prod_price
FROM products
WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10;
// 筛选出“由供应商1003制造且价格为10美元以上的产品，或由供应商1002制造的产品”

SELECT prod_name, prod_price
FROM products
WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;
// 筛选出“由供应商1002或供应商1003制造，且价格为10美元以上的产品”

在使用具有AND和OR的WHERE子句时，都应该尽量用圆括号。

IN

用来指定条件范围，范围中每个条件都可以进行匹配。

SELECT prod_name, prod_price
FROM products
WHERE vend_id IN (1002,1003)
ORDER BY prod_name;

// 其实IN起的作用与OR相同，等价于下方语句
SELECT prod_name, prod_price
FROM products
WHERE vend_id = 1002 OR vend_id = 1003
ORDER BY prod_name;

使用IN操作符的意义：

• 使用长的条件范围时，IN的语法更清楚直观；
• IN执行得比OR更快；
• IN还能包含其它SELECT子句。

NOT

WHERE子句中的NOT只有一个功能：否定它之后所跟的任何条件。

SELECT prod_name, prod_price
FROM products
WHERE vend_id NOT IN (1002,1003)
ORDER BY prod_name;

普通的WHERE子句中，NOT没有太多优势，但在复杂的子句中，NOT与IN联合使用会非常有效。

MySQL中允许NOT对IN、BETWEEN、EXISTS子句取反，这与其它DBMS有很大差别。

通配符：配合LIKE子句

以上的操作都是针对已知值进行过滤。如果要对包含特定字符串的文本进行匹配，就必须使用通配符。

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

搜索模式（search pattern）：有字面值、通配符或二者组成的搜索条件。

通配符本身实际是SQL的WHERE子句中有特殊含义的字符。

为了使用通配符，必须使用LIKE操作符，LIKE指示MySQL，后面跟的搜索模式利用通配符匹配。

书中提到了谓词和操作符是有区别的，但理解不了。

通配符可以在搜索模式的任意位置使用。

%：百分号通配符

%表示任何字符出现任意次数（包括0次）。

无所谓单双引号。

// 找出所有以词“jet”开头的产品
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE 'jet%';
// 能匹配到“jet1”、"JetPack"、“jet12fat2t2ts”、...

// 匹配任何位置包含文本“anvil”
...
... LIKE "%anvil%";

// 匹配以“s”开头，以“e”结尾
...
... LIKE "s%e";

// 空格可能会干扰匹配结果。例如“anvil   ”后面跟有一或多个空格，使用 LIKE "%anvil" 将不会匹配这些字符。

除了NULL以外，%能匹配任何东西。

_：下划线通配符

用途与%一样，但只能匹配一个字符，不多不少，不包括0。

SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE '_ ton anvil';
// 能匹配到“1 ton anvil”、“2 ton anvil”

---

通配符搜索的处理花费时间较长。

• 不要过度使用通配符，如果有其他操作符能达到同等效果，应优先使用；
• 确实要使用通配符时，除非绝对必要，否则不要把它们用在搜索模式的开始处，那样搜索起来是最慢的。

正则表达式

可以看做是一种专门用来匹配文本的语言，被广泛支持。

MySQL的WHERE子句对正则表达式提供了初步的支持。

REGEXP

书中提到：与REGEXP相比，LIKE不会返回数据。

.：匹配任意一个字符

SELECT prod_name
FROM products
WHERE prod_name REGEXP '.000';
// 等价于 ... LIKE '_000';

REGEXP告诉MySQL：之后跟的东西作为正则表达式处理。

.：正则表达式语言中的特殊字符，表示匹配任意一个字符。

---

|：OR匹配

SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000|2000|3000';

---

[...]：匹配几个字符之一

SELECT prod_name
FROM products
WHERE prod_name REGEXP '[123] ton';
// 意思是匹配包含“1或2或3 ton”的字符

---

^：否定一个字符集

SELECT prod_name
FROM products
WHERE prod_name REGEXP '[^123] ton';
// 意思是匹配除了 1/2/3 以外的任何东西

---

[...-...]：匹配范围

SELECT prod_name
FROM products
WHERE prod_name REGEXP '[1-5] ton';
// 匹配1到5。字母范围也有效：[a-z]

---

\\前导：匹配特殊字符

如果要匹配的文本中含有之前提到的特殊字符.、|、^等。

匹配特殊字符必须以\\为前导：\\-表示查找-；\\.表示查找. 。

\\也能用来引用元字符

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

多数正则表达式使用单个反斜杠转义特殊字符。但MySQL要求2个反斜杠（MySQL自己解释一个，正则表达式解释一个）。

预定义字符集

类	说明
[:alnum:]	任意字母和数字
[:alpha:]	任意字母
[:blank:]	空格和制表
[:cntrl:]	ASCII控制字符（ASCII 0~31和127）
[:digit:]	任意数字
[:graph:]	与[:print:]相同，但不包括空格
[:lower:]	任意小写字母
[:print:]	任意可打印字符
[:punct:]	既不在[:alnum:]中又不在[:cntrl:]中的任意字符
[:space:]	包括空格在内的任意空白字符（上文提到的元字符）
[:upper:]	任意大写字母
[:xdigit:]	任意十六进制数字

---

匹配多个实例

元字符	说明
*	0或多个匹配
+	1或多个匹配
?	0或1个匹配
{n}	指定数目的匹配
{n, }	>=指定数目的匹配
{n,m}	[n,m]范围的匹配

例子：
SELECT prod_name
FROM products
WHERE prod_name REGEXP '\\([0-9] sticks?\\)';

// 筛选的结果有：“TNT (1 stick)”、“TNT (5 sticks)”

例子：匹配连在一起的4位数字
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[[:digit:]]{4}';
// '{4}'要求它前面的字符（任意数字）出现4次。
// 等价写法：
... REGEXP '[0-9][0-9][0-9][0-9]';

---

定位符

之前的例子都是匹配一个串中任意位置的文本。为了匹配特定位置的文本，需要下表中的定位符。

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

例子：找出以一个数或小数点开始的所有产品
SELECT prod_name
FROM products
WHERE prod_name REGEXP '^[0-9\\.]';
// 只有当“.”或“任意数字”为串中第一个字符时才匹配它们。

^有双重用途：在集合中用来否定该集合；否则用来指串的开始处。

可以在不使用数据库表的情况下用SELECT来测试正则表达式。

REGEXP检查总是返回0（未匹配）或1（匹配）。

例如：

SELECT ‘hello' REGEXP '[0-9]';

计算字段

存储在数据库表中的数据格式不一定是应用程序需要的格式。例如：

• 将分布在不同列中的数据集合在一个字段中，并以适当的格式显示。

这就需要用到计算字段了。

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

只有数据库知道那些列是实际列表，那些列是字段。接收数据的客户机看来二者相同。

例子：

vendors表中有name和location列表，现在要求以name(location)的形式生成一个表。

拼接（concatenate）：Concat()

其它DBMS使用+或||来实现拼接。

SELECT Concat(name, ' (', species, ')')
FROM pet
ORDER BY name;
// Concat大小写不敏感，单双引号都行
// 结果
+----------------------------------+
| concat(name, " (", species, ")") |
+----------------------------------+
| Fluffy (cat)                     |
| Claws (cat)                      |
| Buffy (dog)                      |
| Fang (dog)                       |
| Bowser (dog)                     |
| Chirpy (bird)                    |
| Whistler (bird )                 |
| Slim (snake)                     |
| Tom (cat)                        |
+----------------------------------+

Concat()需要指定一或多个串，各个串之间用逗号分隔。

上面的Concat()连接了4个元素：

• 存储在name列中的元素；
• 一个空格和一个左圆括号
• 存储在species列中的元素；
• 一个右圆括号。

去掉空格：Trim() / LTrim() / RTrim()

// 如果name元素中含有空格，RTrim()能去掉其右边的所有空格，使得格式对齐
SELECT Concat(RTrim(name), ' (', species, ')')
FROM pet
ORDER BY name;

// LTrim() 去掉左边的空格
// Trim() 去掉左右两边的空格

---

使用别名：AS

别名（alias）有时候也称为导出列。

SELECT Concat(name, ' (', species, ')') AS pet_title
FROM pet
ORDER BY name;

+------------------+
| pet_title        |
+------------------+
| Fluffy (cat)     |
| Claws (cat)      |
| Buffy (dog)      |
| Fang (dog)       |
| Bowser (dog)     |
| Chirpy (bird)    |
| Whistler (bird ) |
| Slim (snake)     |
| Tom (cat)        |
+------------------+

---

公式计算

SELECT prod_id, quantity, item_price, 
	   quantity*item_price AS expanded_price
FROM orderitems
WHERE order_num = 20005;

// 第4列的元素为第2列元素与第3列元素的乘积，且名为expanded_price

显示的expanded_price就是一个计算字段。

MySQL支持+、-、*、/四种运算，且能用圆括号区分优先顺序。

测试计算

SELECT可以省略FROM子句以便简单地访问和处理表达式。

mysql> select 2*2;
+-----+
| 2*2 |
+-----+
|   4 |
+-----+

mysql> select trim("   asd  ");
+------------------+
| trim("   asd  ") |
+------------------+
| asd              |
+------------------+

函数

大多数SQL支持以下类型的函数：

• 用于处理文本的文本函数；
• 在数值上进行计算的数值函数；
• 处理日期和时间值，并从中提取特定成分的时间函数；
• 返回DBMS正在使用的信息（用户信息，检查版本……）的系统函数。

文本处理函数

例子：

SELECT vend_name, Upper(vend_name) AS vend_name_up
FROM vendors

常用的文本处理函数：

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

日期和时间处理函数

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

日期的格式必须是yyyy-mm-dd

虽然支持2位数的年份，但不推荐使用。

---

**注意：**如果要是用的是日期，要使用Date()

SELECT cust_id, order_num
FROM orders
WHERE order_date = '2005-09-01';

如果order_date存储的日期格式是2005-09-01 11:30:05，则无法选出任何结果。

所以得使用Date()提取日期部分，才能与检索条件 = '2005-09-01'匹配。

SELECT cust_id, order_num
FROM orders
WHERE Date(order_date) = '2005-09-01';

即使知道库中的数据只有日期，使用Date()也是一个好习惯，因为当库中数据格式改变后，也不需要修改SQL代码。

---

如果要检索2005年9月中的所有订单：

SELECT cust_id, order_num
FROM orders
WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';

或者

SELECT cust_id, order_num
FROM orders
WHERE Year(order_date) = 2005 AND Mouth(order_date) = 9;

数值处理函数

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

汇总数据

有时会需要汇总数据而不用把它们实际检索出来。为此MySQL提供了专门的函数。

这种类型的检索例子有：

• 确定表中行数（或满足某个条件的行数）；
• 获得表中行组的和；
• 找出列表的最大值、最小值、平均值。

上述例子对表中数据汇总，但并不需要实际数据本身。实际需要的是汇总信息。

聚集函数

运行在行组上，计算和返回单个值的函数。

SQ：

聚集函数：

函数	说明
AVG()	返回某列的平均值
COUNT()	返回某列行数
MAX()	返回某列最大值
MIN()	返回某列最小值
SUM()	返回某列之和

各个函数用法格式基本相同。

AVG()

例子：

SELECT AVG(prod_price) AS avg_price
FROM products
WERE ven_id = 1003;

AVG()只作用于一列，忽略值为NULL的行。

---

COUNT()

2种使用方法：

1、COUNT(*)：确定表中所有行的行数。（理所应当包含NULL）

2、COUNT(column)：检索特定列行数，忽略NULL。

---

MAX()

MAX()通常用于返回日期或数值的最大值，但也能用于文本。

忽略NULL。

根据实际操作，应该是按字符串比较规则进行比较。

---

MIN()

与MAX()相反。

忽略NULL。

---

SUN()

功能如其名，一个自己不会意识到的例子：

SELECT SUM(item_price*quantity) AS total_price
FROM ordeitems
WERE order_id = 1003;

忽略NULL。

聚集不同值

以上聚集函数可以与DISTINCE和ALL配合使用。

ALL是默认参数。

SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WERE ven_id = 1003;

使用了DISTINCE后，AVG()只会考虑不同的值，相同的值不纳入计算。

**注意：**不能使用COUNT(DISTINCT *)

DISTINCT可用于MIN()和MAX()，但实际上没多大意义。

组合聚集函数

就是同时使用多个聚集函数：

SELECT COUNT(*) AS num_items,
	   MIN(prod_price) AS price_min,
	   MAX(prod_price) AS price_max,
	   AVG(prod_price) AS price_avg
FROM products;

分组：GROUP BY

以某列属性为标识进行分组。

例子：返回供应商1003提供的产品数量：

SELECT COUNT(*) AS num_prods
FROM products
WHERE vend_id = 1003;

如果要返回所有供应商提供的产品数该怎么办？这就用到分组了。

SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;

使用GROUP BY的一些规定：

• 可以包含多列，以实现更细致的分组；
• 除了聚集计算语句外，SELECT语句中出现的所有列，都必须出现在GROUP BY子句中。
• NULL也被视为一个值，被分为一组。
• GROUP BY 子句必须出现在 WHERE 子句之后，ORDER BY 子句之前。

过滤分组：HAVING

WHERE 用于过滤行；HAVING 用于过滤分组。

以上所有类型的 WHERE 子句都能被 HAVING 子句替代。

例子：

SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;
HAVING COUNT(*) >= 2;

过滤出了提供的产品数大于等于2的分组。

WHERE 在数据分组前进行过滤，HAVING 在数据分组后进行过滤。

也存在 WHERE 和 HAVING 子句同时使用的需求：

列出具有2个以上价格>=10的产品的供应商。

SELECT vend_id, COUNT(*) AS num_prods
FROM products
WHERE prod_price >= 10
GROUP BY vend_id;
HAVING COUNT(*) >= 2;

分组和排序

GROUP BY 有时会输出排好序的结果，但实际上并没有分组排序功能。通常要明确提供 ORDER BY 子句，确定排序规则。

子查询

SELECT order_num
FROM orderitems
WHERE prod_id = 'TNT2';

# 得到结果“20005”、“20007”，再执行查询

SELECT cust_id
FROM orders
WHERE order_num IN (20005,20007);

使用子查询：

SELECT cust_id
FROM orders
WHERE order_num IN (
					SELECT order_num
					FROM orderitems
					WHERE prod_id = 'TNT2');

子查询一般与 IN 配合使用，但也可以用于测试 =、<> 等。

作为计算字段使用子查询

需求：显示customers表中每个客户的订单总数。订单与相应的客户ID存储在orders表中。

查找客户10001的订单总数：

SELECT COUNT(*) AS orders
FROM orders
WHERE cust_id = 10001;

实现需求：（对每个客户都执行了一次统计操作）

SELECT cust_name,
		(SELECT COUNT(*) 
		 FROM orders
		 WHERE orders.cust_id = customers.cust_id)
		 AS orders
FROM customers
ORDER BY cust_name;

使用了完全限定列名的子查询是相关子查询。

连接表

关系表：为了避免数据冗余，将一个表分解为多个表，这些表通过外键相互联系起来形成关系。

连接

FROM…WHERE

例子：

SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id;

如果没有 WHERE 子句，得到的结果就是一个笛卡尔积，笛卡尔积会包含大量无意义的数据。

INNER JOIN…ON

SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;

内部连接（等值连接）

目前用到的都是等值连接，基于两个表之间的相等测试。

连接多个表

SELECT vend_name, prod_name, prod_price, quantity
FROM vendors, products, orderitems
WHERE vendors.vend_id = products.vend_id
	AND orderitems.prod_id = products.prod_id
	AND order_num = 20005;

连接的表的数量没有限制，连接的表越多，性能越差。

高级连接

表别名

SQL允许给表名起别名。理由：

一、缩短SQL语句；

二、允许在单条SELECT语句中多次使用相同的表。

使用别名的例子：

SELECT vend_name, prod_name, prod_price, quantity
FROM vendors AS v, products AS p, orderitems AS o
WHERE v.vend_id = p.vend_id
	AND o.prod_id = p.prod_id
	AND order_num = 20005;

除了 WHERE 子句外，表别名还能用于 SELECT 的列表、 ORDER BY 子句以及语句的其他部分。

自连接

某物品（DTNTR）存在问题，查找出生产该物品的厂商生产的其它物品是否也存在问题：

SELECT prod_id, prod_name
FROM products 
WHERE vend_id = (SELECT vend_id
				 FROM products
				 WHERE prod_id = 'DTNTR');

相同效果的查询：~~（思路理解不了）~~没问题了，可以看做是：先在p2中进行 p2.prod_id = 'DTNTR'，然后将筛选出的元组（相当于上方子查询的结果）用p1.vend_id = p2.vend_id条件进行连接。

SELECT p1.prod_id, p1.prod_name
FROM products AS p1, products AS p2
WHERE p1.vend_id = p2.vend_id
	AND p2.prod_id = 'DTNTR';

注意：

筛选条件是p2.name = 'Tom';而不是p1.name = 'Tom';。

自然连接

既然表能进行连接，所以至少会有一个列出现在不止一个表中。自然连接排除这些多余出现的列。

从书中介绍看，自然连接只是一个概念，并不是什么和关键字、操作密切相关的东西。就是在使用 SELECT 时不要选出那些重复出现的列就行了。

外连接

分左外连接（LEFT OUTER JOIN）和右外连接（RIGHT OUTER JOIN）。

在等值连接的基础上完全保留左/右侧的表的元组。

例子

直接看例子：有2个表，t1、t2。

mysql> select * from t1;
+------+
| col1 |
+------+
| A    |
| B    |
| C    |
+------+

mysql> select * from t2;
+------+------+
| col1 | col2 |
+------+------+
| A    |    1 |
| A    |    2 |
| B    |    3 |
| D    |    4 |
+------+------+

内连接（等值连接）：

mysql>  select * from t1 inner join t2 on t1.col1=t2.col1;
+------+------+------+
| col1 | col1 | col2 |
+------+------+------+
| A    | A    |    1 |
| A    | A    |    2 |
| B    | B    |    3 |
+------+------+------+

左外连接：

在内连接的基础上显示左侧表（t1）的所有元组

mysql>  select * from t1 left outer join t2 on t1.col1=t2.col1;
+------+------+------+
| col1 | col1 | col2 |
+------+------+------+
| A    | A    |    2 |
| A    | A    |    1 |
| B    | B    |    3 |
| C    | NULL | NULL |
+------+------+------+

右外连接：

在内连接的基础上显示右侧表（t2）的所有元组

mysql>  select * from t1 right outer join t2 on t1.col1=t2.col1;
+------+------+------+
| col1 | col1 | col2 |
+------+------+------+
| A    | A    |    1 |
| A    | A    |    2 |
| B    | B    |    3 |
| NULL | D    |    4 |
+------+------+------+

笛卡尔积：

mysql>  select * from t1,t2;
+------+------+------+
| col1 | col1 | col2 |
+------+------+------+
| C    | A    |    1 |
| B    | A    |    1 |
| A    | A    |    1 |
| C    | A    |    2 |
| B    | A    |    2 |
| A    | A    |    2 |
| C    | B    |    3 |
| B    | B    |    3 |
| A    | B    |    3 |
| C    | D    |    4 |
| B    | D    |    4 |
| A    | D    |    4 |
+------+------+------+

全外连接：

MySQL没有FULL JOIN，只能用UNION将左右连接的结果组合。

mysql>  select * from t1 left join t2 on t1.col1=t2.col1
    -> union
    ->  select * from t1 right join t2 on t1.col1=t2.col1;
+------+------+------+
| col1 | col1 | col2 |
+------+------+------+
| A    | A    |    2 |
| A    | A    |    1 |
| B    | B    |    3 |
| C    | NULL | NULL |
| NULL | D    |    4 |
+------+------+------+

总结

左/右外连接都是建立在等值连接的基础上，把左侧/右侧的表补完，如果遇到没有匹配等值条件就用null填充。

外连接与没有任何规则约束的笛卡尔积完全不同。

带聚集函数的连接

就是放在一起用，就这样。

组合查询：UNION

UNION 操作符将多个 SELECT 操作的结果取并集。

例子：想让结果包括价格少于等于5的物品，还包括供应商1001和1002生产的物品。（结果为2张表）

SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5;

SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001,1002);

使用UNION：（结果为一张表）

SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5;
UNION
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001,1002);

规则：

• 必须由2条及以上的 SELECT 子句组成，SELECT 之间用 UNION 分隔；
• 每个 SELECT 查询必须包含相同的列、表达值、聚集函数，顺序无所谓；
• 列的类型必须兼容：不必完全相同，但至少得可以隐式转换。

UNION ALL

使用 UNION 会自动移除重复行，可以使用 UNION ALL 查看所有行。

配合使用 ORDER BY

只能在最后一个 SELECT 子句中使用 ORDER BY 。

全文本搜索

主要用于从以文本作为属性的列中，全出具有关键词的元组。

MySQL支持多种基本的数据库引擎，最常用的是 MyISAM 和 InnoDB ，前者支持全文本搜索，后者不支持。

之前介绍了 LIKE 和 正则表达式REGEXP 。它们的缺陷：

• 尝试匹配所有行，会非常耗时；
• 控制很难，无法明确指明该匹配什么、不匹配什么；
• 结果不够智能化，无法区分匹配一次或匹配多次的行之类的。

这些限制都能用全文本搜索来解决。

设置

为了进行全文本搜索，必须索引被搜索的列，且要随着数据的修改不断更新索引。索引更新 MySQL 会自动进行。

之后可使用 Match() + Against() 的组合来使用。

一般在创建表的时候启动：

...
FULLTEXT(note_id)	// 为“note_id”列建立索引，可以有多行，用逗号隔开
...

如果正在导入数据到新表，应该先导入所有数据后，再启动FULLTEXT（书中没给出操作）。因为这样建立索引的总时间小于每导入一行数据就建立索引所花费的时间。

使用

将返回文本中带有 rabbit 一词的结果。

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit');

等价的 LIKE 子句

SELECT note_text
FROM productnotes
WHERE note_text LIKE '%rabbit%';

LIKE 子句没有配上排序子句，所以返回的结果顺序没有意义，但全文本搜索会通过文本内容计算出一个优先级，然后将优先级较高的行放在上方。

在书中给出的例子中，返回了2行，排在上方的行中，rabbit 出现在第3个词；第二行中，rabbit 出现在第20个词。

查看全文本搜索的优先级：

SELECT note_text, Match(note_text) Against('rabbit') AS ranks
FROM productnotes;

因为没有 WHERE 子句，会返回所有行，且没有优先级排序。第2列的值就是全文本搜索的优先级。

不含rabbit 的行优先级为0，含有rabbit 的行的优先级受 匹配词的数目、匹配词的位置、匹配词的总数... 计算出来。

查询扩展

放宽查询范围，会列出没有查询关键词，但由与其相关的行。

使用查询扩展时，会对索引扫描2次，第2次会匹配“有用的词”。（并没有讲什么是有用的词）

例子：

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit' WITH QUERY EXPANSION);

布尔文本搜索

全文本搜索的另一种形式。

优点：（部分能理解的）

• 可以设置要排斥的词，如果排斥词出现在文本内，就算该文本含有匹配词也会被排除；
• 设置匹配词的等级值

// 使用了布尔模式，但没有指定条件，与全文本模式结果一样
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit' IN BOOLEAN MODE);

布尔操作符	说明
+	包含，词必须存在
-	排除，词必须不存在
>	包含，提高词等级
<	包含，减少词等级
()	把词组成子表达式
~	取消一个词的排序值
*	词尾的通配符
“”	定义一个短语（用于匹配字符串）

例子：

匹配含 heavy 但不包含任意以 rope 开头的词的行：

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);

同时包含 rabbit 和 bait：

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

包含 rabbit 或 bait：

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

匹配 “rabbit bait” 而不是其中任意一个词

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

匹配 rabbit 或 bait，增加前者等级，降低后者等级：

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

匹配 rabbit 和 bait，增加前者等级，降低后者等级：（书中语义不够清晰，不知道和上一个例子的区别。也许是用于同时使用多个操作符？）

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

布尔模式中，输出结果不按等级值降序排序

全文搜索的补充说明

• 短词被忽略且从索引中排除。短词默认定义为字符<=3的词；
• MySQL 内建有一个非用词（stopword）列表，表中的词在索引时被忽略；
• 出现频率 >50% 的词被视为非用词忽略。此规则不适用于布尔模式；
• 如果表中的行数少于3行，则全文本搜索不返回结果；
• 忽略词中的单引号；
• 不具有词分隔符（汉语、日语）的语言不能恰当地返回结果；

数据插入：INSERT

插入的方式：

• 插入完整的行；
• 插入行的一部分；
• 插入多行；
• 插入某些查询的结果。

插入单行

最简单的例子：插入一个新客户

INSERT INTO customers
VALUES(NULL,
	'Pep E. LaPew',
	'100 Main Street',
	'Los angeles',
	'CA',
	'90046',
	'USA',
	NULL,
	NULL);

第一列为cust_id，为自增的主键，可以使用 NULL。

很简单，但不安全，高度依赖于表中列的定义次序，尽量避免使用。

更繁琐但更安全的方法：

INSERT INTO customers(cust_name,
	cust_address,
	cust_city,
	cust_state,
	cust_zip,
	cust_country,
	cust_contact,
	cust_email)
VALUES('Pep E. LaPew',
	'100 Main Street',
	'Los angeles',
	'CA',
	'90046',
	'USA',
	NULL,
	NULL);

这个例子完成的工作与前一例完全相同，但在表名后的括号中明确给出了列名，列名和值一一对应。

VALUES中的值的次序，与提供的列名的次序相同，但这个次序不一定是实际表中的次序，这也意味着，如果表的结构改变，这个INSERT语句仍能正常工作。

例子中没有给出cust_id，因为那是自增的列，由MySQL赋值。

INSERT操作省略一些列的条件：

• 该列定义允许为NULL值；
• 该列定义中给出默认值。

如果不满足以上条件，就会报错。

提高整体性能

在多个用户访问数据库时，INSERT 的操作通常很耗时（涉及到更新索引），可以降低 INSERT 操作的优先级，让其它用户的 SELECT 操作先执行。

INSERT LOW_PRIORITY INTO

同样适用于 UPDATE 和 DELETE 。

插入多行

1、执行多条 INSERT 语句；

2、单条语句多组值，每组 VALUES 中的次序都要相同。（更快）

INSERT INTO customers(...)
VALUES(...),
	(...),
	(...);

插入检索出的数据

将 SELECT 语句的结果插入表中。INSERT SELECT。

INSERT INTO customers(cust_id,
	cust_name,
	cust_address,
	cust_city,
	cust_state,
	cust_zip,
	cust_country,
	cust_contact,
	cust_email)
SELECT cust_id,
	cust_name,
	cust_address,
	cust_city,
	cust_state,
	cust_zip,
	cust_country,
	cust_contact,
	cust_email
FROM custnew;

SELECT 语句从 custnew 中将所有数据检索出，然后导入 customers 表中。

为了简单说明，INSERT 和 SELECT 语句中使用了相同的列名，但实际上列名并不要求匹配。SELECT 的列与之前的例子中的 VALUES 后的值一样，类型与 INSERT 的列匹配，能将数据放入就行了。

SELECT 语句中可以使用 WHERE 子句过滤导入的数据。

补充：批量导入数据

从一个空表开始的，填充它的一个简易方法是创建一个文本文件，每个动物各一行，然后用一个语句将文件的内容装载到表中。

可以创建一个文本文件“pet.txt”，每行包含一个记录，用定位符(tab)把值分开，并且以CREATE TABLE语句中列出的列次序给出。对于丢失的值(例如未知的性别，或仍然活着的动物的死亡日期)，你可以使用NULL值。为了在你的文本文件中表示这些内容，使用\N（反斜线，字母N）。例如，Whistler鸟的记录应为(这里值之间的空白是一个定位符)：

name	owner	species	sex	birth	death
Whistler	Gwen	bird	\N	1997-12-09	\N

先要进行相关设置

否则：

ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides

网上的方法：

1）先退出；

2）

mysql --local-infile -u root -p

经过实践：这一步是非必要的，不需要退出，直接查询、设置即可。

仅就“设置”而言，是非必要的。但“8.0版本安全性提高”，所以必须用该方式登录

3）

show variables like 'local_infile';

4）

set global local_infile=on;

录入成功

mysql> LOAD DATA LOCAL INFILE 'D:/MySQL/load_data/pet.txt' INTO TABLE pet;

更新和删除数据：UPDATE DELETE

UPDATE

不要省略 WHERE 子句：使用 UPDATE 时一定要非常小心，因为稍不注意，就会更新表中所有行。

例子：更新customers表中客户10005的邮箱地址。

UPDATE customers
SET cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;

如果忽略了 WHERE 子句，将会更新表中的所有邮箱数据。

更新多个列：

UPDATE customers
SET cust_name = 'The Fudds',
	cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;

UPDATE 语句中可以使用子查询，用 SELECT 语句查询处的数据更新列数据。

如果列的可以被设置为 NULL ，则可以通过修改为 NULL 来删除某个列的值。

DELETE

同样，如果省略了 WHERE 子句，就可能会删除表中所有行。

例子：删除客户10006。

DELETE FROM customers
WHERE cust_id = 10006;

DELETE 删除整行，故不需要列名或通配符。如果要删除指定列的数据，就是用 UPDATE。

使用指导原则：

• UPDATE 和 DELETE 尽量带上 WHERE 子句，除非的确要修改/删除所有行；
• 在对 UPDATE 和 DELETE 使用 WHERE 子句前，先用 SELECT 进行测试，确保 WHERE 过滤的结果正确；
• 使用强制实施引用完整性的数据库，这样MySQL就不允许删除具有与其他表相关联的数据的行。

创建和操纵表

创建表：CREATE

例子：将主键设定为cust_id，并且cust_id自动增长

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_zip		char(50)		NULL,
	cust_contry		char(50)		NULL,
	cust_contact	char(50)		NULL,
	cust_email		char(50)		NULL
	PRIMARY KEY (cust_id)
) ENGINE=InnoDB

创建新的表时，表明必须不存在。如果要覆盖已存在的表，应该先将其删除，再创建同名表。

NULL / NOT NULL

设定为 NOT NULL 的列不允许为空，插入数据时必须给出值。否则会失败。

**！注意：**NULL 与空串（''，2个单引号，中间没有字符）不同，NULL 值是没有值，而空串是一个有效的值。

主键：PRIMARY KEY

主键的值必须唯一，如果同时将多个列设为主键，则这些列的值的组合必须是唯一的。

...
PRIMARY KEY (cust_id, cust_name)
...

AUTO_INCREMENT

通常用于订单号等列，这些列的唯一意义是它们是唯一的。每次当该列增加一行时，自动增量。

每个表只允许有一个 AUTO_INCREMENT 列，且通常作为主键。

AUTO_INCREMENT 列的值也能手动指定，在插入数据时为该列赋值没有出现过的数据就行了。

确定 AUTO_INCREMENT 的值：

有如下场景：表A中的列a为表B中的外键，现在表A中增加了一行，列a自动增加，如何在表B中插入对应数据？

可以使用 last_insert_id() 函数来获取最后一个 AUTO_INCREMENT 值。

SELECT last_insert_id()

指定默认值：DEFAULT

CREATE TABLE customers
(
...
	cust_zip	char(50)	NULL DEFAULT 1,
...
) ENGINE=InnoDB

引擎类型

MySQL 中的 CREATE 创建表，SELECT 筛选数据等操作，都是由引擎在内部处理请求。

MySQL 打包多种引擎，不同引擎有不同的功能和特性，为不同任务选择合适的引擎能获得良好的功能和灵活性。

如果省略 ENGINE=… 就会使用默认引擎。

外键不能跨引擎！

更新表：ALTER

例子：为表添加一个列

ALTER TABLE vendors
ADD vend_phone CHAR(20);

删除刚添加的列：

ALTER TABLE vendors
DROP COLUMN vend_phone;

ALTER TABLE 常见的用途是定义外键：

ALTER TABLE orderitems
DROP CONSTRAINT fk_orderitems_orders
FOREIGN KEY (order_num) REFERENCES orders (order_num);

删除表：DROP

DROP TABLE custmers;

重命名表：RENAME

可以用逗号分隔，同时重命名多个表。

RENAME TABLE customers2 TO custmers; 

视图：VIEW

视图能将 SELECT 的查询条件包装成一个虚拟表，从而简化查询过程。

视图并不包含数据和列，包含的只是SQL查询。

创建视图：CREATE VIEW

删除视图：DROP VIEW

更新视图：1、先 DROP 再 CREATE；2、直接使用 CREATE OR REPLACE VIEW。

例子：

SELECT cust_name, cust_contact
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
	AND orders.order_num = orderitems.order_num
	AND prod_id = 'TNT2';

// 使创建视图（只有连接条件）
CREATE VIEW productcustomers AS
SELECT cust_name, cust_contact
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
	AND orders.order_num = orderitems.order_num;

// 使用视图
SELECT cust_name, cust_contact
FROM productcustomers
WHERE prod_id = 'TNT2';

总之， SELECT 检索的结果都能包装成一个视图。

通过视图过滤表中不想要的数据：过滤没有电子邮件地址的客户

CREATE VIEW productcustomers AS
SELECT cust_idm cust_name, cust_email
FROM customers
WHERE cust_email IS NOT NULL;

更新视图

可以像操作表一样，使用 INSERT 、UPDATE 和 DELETE 对表进行更改。

对视图的修改会反映到实际的表中。

当视图定义中有如下操作，就不能进行视图更新：

• 分组（GROUP BY、HAVING）
• 连接
• 子查询
• 并
• 聚集函数
• DISTINCT
• 导出（计算）列

视图主要用于数据检索。一系列的限制并不是很严重的问题。

存储过程（类似于编写函数）

一些非常复杂的情况下，要使用多个MySQL语句对多个表进行操作，而具体执行的语句和顺序是不固定的，得根据每一步的实际结果决定下一步走么做。

优点：

• 把处理封装在容易使用的单元中，简化复杂操作；
• 封装了操作，不会在编写复杂步骤的过程中出错；

缺点：

• 编写过程复杂；
• 创建存储过程需要权限。

执行

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

创建

创建了一个名为productpricing的存储过程

CREATE PROCEDURE productpricing()
BEGIN
	SELECT Avg(prod_price) AS priceaverage
	FROM products;
END;

调用

CALL productpricing();

P.166 页对适使用令行客户机有重要注释

参数

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;

变量类型：

• IN：传递给存储过程；
• OUT：从存储过程传出；
• INOUT：传入和传出。

调用：

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

使用变量：

SELECT @pricelow, @pricehigh, @priceaverage;

另一个例子：

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;

P.171 有一个复杂的例子，用到了流程控制

检查存储过程

SHOW CREATE PROCEDURE ordertotal;

游标：CURSOR

SELECT 返回的结果是与其匹配的所有行，简单地使用 SELECT 无法单独一行一行或前10行地处理。

这就需要用到游标了。

创建游标

DECLARE cursor_name CURSOR 
FOR select_statement;

打开和关闭游标

OPEN cursor_name;
CLOSE cursor_name;

使用游标：FETCH

将游标 cursor_name 中 SELECT 语句的执行结果保存到变量参数 var_name 中。变量参数 var_name 必须在游标使用之前定义。

FETCH cursor_name INTO var_name [,var_name]...

使用游标类似高级语言中的数组遍历，当第一次使用游标时，此时游标指向结果集的第一条记录。

每执行一次FETCH，游标都会向下移动一条记录。

例子：将users表中user_name列的所有数据改为MySQL。

mysql> CREATE PROCEDURE pro_users()
    -> BEGIN
    -> DECLARE result VARCHAR(100);
    -> DECLARE no INT;
    -> DECLARE cur_1 CURSOR FOR SELECT user_name FROM users;
    
    --当游标移动到数据的末行之后，没有数据可读了，就会执行
    -> DECLARE CONTINUE HANDLER FOR NOT FOUND SET no=1;
    
    -> SET no=0;
    
    -> OPEN cur_1;
    
    -> WHILE no=0 do
    -> FETCH cur_1 into result;
    -> UPDATE users SET user_name='MySQL'
    -> WHERE user_name=result;
    -> END WHILE;
    
    -> CLOSE cur_1;
    
    -> END //
Query OK, 0 rows affected (0.05 sec)

mysql> call pro_users() //

循环部分可以改为

 -> REPEAT
 
 -> FETCH cur_1 into result;
 -> UPDATE users SET user_name='MySQL'
 -> WHERE user_name=result;
 
 -> UNTIL no END REPEAT;

当no的值为1时，也会结束循环。

CONTINUE HANDLER：在条件出现时被执行的代码。

触发器：TRIGGER

MySQL语句有时需要在一些事件发生时执行。例如：

• 当增加一个顾客信息到表中时，检查格式是否正确并且做出修正；

• 每订购一个产品时，从库存数量中减去对应产品的存量；

• 每删除一行，都在某个存档表中保留一个副本。

  这就用到了触发器。

触发器是能响应以下任意语句，并自动执行的一条MySQL语句（或位于 BEGIN 和 END 语句之间的一组语句）：

• INSERT
• DELETE
• UPDATE

BEFORE | AFTER

触发器被触发的时刻，表示触发器是在激活它的语句之前或之后触发。

创建触发器

CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added';

创建了一个名为newproduct的触发器；

触发类型为AFTER INSERT；

关联的表为products；

FOR EACH ROW好像是固定搭配（书中没给出其他参数）；

执行的动作是显示信息'Product added'

视图、临时表不支持触发器。

每个表 每个事件 每次 只允许一个触发器。

单一触发器也不能触发多个事件或与多个表关联。

如果 BEFORE 触发器失败，MySQL就不会执行请求的操作；

如果 BEFORE 触发器或语句本身失败，MySQL不会执行 AFTER 触发器（如果存在的话）。

删除触发器

DROP TRIGGER newproduct;

使用触发器

INSERT 触发器

• INSERT触发器代码内，可以使用名为 NEW 的虚拟表，访问被插入的行；

• NEW 在 BEFORE INSERT 中使用，可以修改被插入的值。（没给出例子）

例子：orders表中每插入新的数据就将其显示出来。

CREATE TRIGGER newproduct AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num;

DELETE 触发器

• 可以引用名为 OLD 的虚拟表，访问被删除的行。
• OLD 的值全是只读的，无法更新。

没提到 AFTER DELETE 的 OLD。

例子：每从orders表中删除一行，就将被删除的行插入archive_orders表中。

CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW S
BEGIN
	INSERT INTO archive_orders(order_num, order_date, cust_id)
	VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);	
END;

本例中 BEGIN … END 不是必须的。使用了 BEGIN … END 后，能往里面放入多条SQL语句。

如果无法存档archive_orders表，则 DELETE 操作会被放弃。

UPDATE 触发器

• 可同时使用 OLD 和 NEW 。分别能访问更新前的值和更新的值。
• OLD 和 NEW 的其他特性同上面2个触发器。

例子：保证州名缩写总是大写。

CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW Set NEW.vend_state = Upper(NEW.vend_state);

事务处理

事务处理用于维护数据库的完整性，保证一系列SQL操作要么都执行，要么都不执行。

书里的内容非常含糊，事务的提交、回滚都是通过编写好的代码实现的，只有几个简单的例子。

干脆也简单的记就算了。

C语言教程网的补充：MySQL的自动提交设置默认是打开的，每条 SOL 语句都会被当做一个单独的事务自动执行。将其关闭后，执行关闭语句的位置就作为一个事务的起点，直到执行 COMMIT 语句和 ROLLBACK 语句后，该事务才结束。结束之后，这就是下一个事务的起点。

开始事务：START TRANSACTION

或者 BEGIN

撤销：ROLLBACK

例子：

SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;

事务处理用来管理 INSERT 、UPDATE 、DELETE 。不能退回 SELECT 语句。

所以以上的例子意义在哪？

按书中描述，如果发生错误，就执行退回操作。难道在编写该代码之前就知道了会发生错误，所以同时用到了事务的ROLLBACK功能？那为什么不干脆编写正确的代码？？？

从C语言教程网的例子看，SQL语句像是个交互性的语言，用户输入一段指令，它就返回一段结果。也就是说，可以将START TRANSACTION;看做是一个状态的开启，在输入SQL语句时如果感觉不对劲，就是用ROLLBACK退回，否则就COMMIT。

提交：COMMIT

保存点：SAVEPOINT

SAVEPOINT save1
...
ROLLBACK TO save1

管理用户

查看用户列表：

USE mysql;
SELECT user FROM user;

创建新用户

创建用户的同时设定密码（可选）。

CREATE USER ben IDENTIFIED BY 'p@$$w0rd';

重命名用户

RENAME USER ben TO bforta;

删除用户

DROP USER bforta;

更改密码

时候Password()函数对密码进行加密。

SET PASSWORD FOR bforta = Password('n3w p@$$w0rd');

设置访问权限

显示用户权限

SHOW GRANTS FOR bforta;

授予权限

允许用户bforta在crashcourse数据库的所有表上使用 SELECT 。

GRANT SELECT ON crashcourse.* TO bforta;

撤销权限

REVOKE SELECT ON crashcourse.* FROM bforta;