1. 数据库操作

1.1显示数据库

mysql> SHOW DATABASES;

1.2 创建数据库

CREATE DATABASE 数据库名 CHARSET='编码格式'

mysql> CREATE DATABASE create_test CHARSET = 'utf8';

1.3 使用数据库

mysql> USE create_test;

1.4 查看当前数据库

使用 SELECT DATABASE() 查看当前使用的数据库。

mysql> SELECT DATABASE();
+-------------+
| DATABASE()  |
+-------------+
| create_test |
+-------------+

1.5 删除数据库

mysql> DROP DATABASE create_test;

2. 表操作

2.1 创建表

格式：

CREATE TABLE  [IF NOT EXISTS] `表名` (
	`字段名` 列类型 [属性] [索引] [注释]，
	`字段名` 列类型 [属性] [索引] [注释]，
	.......
	`字段名` 列类型 [属性] [索引] [注释]
) [表类型] [字符集设置] [注释]

使用下面的语句创建示例中的 one_piece 表。

mysql> CREATE TABLE one_piece
    -> (
    ->     id CHAR(10) NOT NULL COMMENT '海贼团id',
    ->     pirates CHAR(10) NOT NULL COMMENT '海贼团名称',
    ->     name CHAR(10) NOT NULL COMMENT '海贼名',
    ->     age INT(11) NOT NULL COMMENT '海贼年龄',
    ->     post VARCHAR(10) NULL COMMENT '海贼团职位'
    -> );

注意：创建表时，指定的表名必须不存在，否则会出错。

2.2 更新表

2.2.1 添加列

在刚才创建的 one_piece 表中添加一列 bounty (赏金)。

mysql> ALTER TABLE one_piece
    -> ADD bounty INT(15);

2.2.2 删除列

删除 bounty 列。

mysql> ALTER TABLE one_piece
    -> DROP COLUMN bounty;

2.3 查看表结构

mysql> DESC one_piece;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | char(10)    | NO   |     | NULL    |       |
| pirates | char(10)    | NO   |     | NULL    |       |
| name    | char(10)    | NO   |     | NULL    |       |
| age     | int(11)     | YES  |     | NULL    |       |
| post    | varchar(10) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

2.4 查看表详细信息

\G 后面不能加“ ; ”。

mysql> SHOW TABLE STATUS LIKE 'one_piece' \G
*************************** 1. row ***************************
           Name: one_piece
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2021-09-03 17:53:58
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.39 sec)

2.5 重命名表

两种方法：

ALTER TABLE 表名 RENAME [TO | AS] 新表名;
RENAME TABLE 表名 TO 新表名;

用方法一将 Products 表更名为 new_Products ，再用方法二改回来。

-- 方法一
mysql> ALTER TABLE one_piece RENAME TO new_one_piece;
-- 方法二
mysql> RENAME TABLE new_one_piece TO one_piece;

2.6 删除表

DROP TABLE 表名

mysql> DROP TABLE one_piece;

注意：在该表与其他表有关联时，Mysql 会阻止该表的删除。

3. 查询

3.1 查询多列

同时输出 name, age 列。

mysql> SELECT name, age
    -> FROM one_piece;

3.2 检索唯一值

使用 DISTINCT 关键字，查询字段 age 的唯一值。

mysql> SELECT DISTINCT age
    -> FROM one_piece;

3.3 限制输出

在 Mysql 中使用 LIMIT 关键字限制输出的数据。LIMIT 有两种常见用法：

SELECT * FROM table  LIMIT [offset], rows    -- LIMIT 单独使用
SELECT * FROM table  LIMIT rows OFFSET [offset]    -- 配合 OFFSET 使用

offset：行开始的行的索引。0表示从第1行开始显示（包括第1行），以此类推。

rows：数据显示的条数。

示例：

SELECT * FROM one_piece LIMIT 5;    -- 检索前5条数据
--相当于
SELECT * from one_piece LIMIT 0,5;    -- 从第0行开始检索5条数据
--相当于
SELECT * FROM one_piece LIMIT 5 OFFSET 0;    -- 从第0行开始检索5条数据，注意这里的LIMIT的5指代的是数量

注：如果表中数据不足，即LIMIT设定的数过大，则只会检索到最后一行。

3.4 注释

三种注释方式

-- 单行注释
# 单行注释
/* 多行注释 */

4. ORDER BY 排序

4.1 单列排序

使用 ORDER BY 子句。 ORDER BY 子句取一个或多个列的名字，据此对输出进行排序（默认升序）。

mysql> SELECT name, age
    -> FROM one_piece
    -> ORDER BY age;

**注意：**在指定一条 ORDER BY 子句时，应该保证它是 SELECT 语句中最后一条子句。

4.2 多列排序

mysql> SELECT A, B
    -> FROM test
    -> ORDER BY A, B;

在按多列排序时，仅在多个行具有相同的 A 值时才按 B 进行排序。如果 A 列中所有的值都是 唯一的，则不会按 B 排序。

4.3 指定排序方向

ORDER 默认升序（从A到Z）排序。指定 DESC 关键字进行降序（从Z到 A）排序。

mysql> SELECT age
    -> FROM one_piece
    -> ORDER BY age DESC;

多列指定排序方向时，要使用逗号分隔。

mysql> SELECT name, age
    -> FROM one_piece
    -> ORDER BY name DESC, age;

5. WHERE 过滤数据

5.1 WHERE 子句操作符

操作符	说明	操作符	说明
=	等于	>	大于
<>、!=	不等于	>=	大于等于
<	小于	!>	不大于
<=	小于等于	BETWEEN	在两值之间(包含边界)
!<	不小于	IS NULL	是NULL值

5.2 范围值检查

使用 WHERE 关键字和 BETWEEN AND 进行范围值检查（前闭后闭）。

mysql> SELECT age
    -> FROM one_piece
    -> WHERE A BETWEEN 5 AND 10;

查询字段 age 中 >=5 并且 <= 10 的数据。

5.3 空值检查

使用 WHERE 关键字和 IS NULL 进行范围值检查。如果没有 NULL 值就不返回数据。

mysql> SELECT name
    -> FROM one_piece
    -> WHERE name IS NULL;

6. 高级数据过滤

6.1 WHERE 组合过滤

使用 AND 、OR 操作符给 WHERE 子句添加附加条件。 AND 的优先级比 OR 要高，优先级高低 () 、 AND 、 OR。在使用的过程中要注意各个优先级的影响。

mysql> SELECT name, age
    -> FROM one_piece
    -> WHERE(name = '索隆' OR name = '路飞')
    -> AND age >= 18;

6.2 IN 操作符

IN 操作符用来指定条件范围，范围中的每个条件都可以进行匹配。(与 OR 的功能相同，但速度比 IN 慢)

mysql> SELECT name, age
    -> FROM one_piece
    -> WHERE name IN ('索隆', '路飞')

6.3 NOT 操作符

WHERE 子句中的 NOT 操作符有且只有一个功能，那就是否定其后所跟的任何条件。

mysql> SELECT name
    -> FROM one_piece
    -> WHERE name NOT IN ('索隆', '路飞')

7. 通配符过滤

通配符搜索只能用于文本字段（字符串），非文本数据类型字段不能使用通配符搜索。

在使用通配符过滤之前要先了解 LIKE ， LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式或取值。

7.1 % 通配符

% 表示任何字符出现任意次数。例如，为了找出所有以 路 开始的 name。

mysql> SELECT name, age
    -> FROM one_piece
    -> WHERE name LIKE '路%';

7.2 _ 通配符

通配符 _ 的用途与 % 一样也是匹配任意字符，但它只匹配单个字符，而不是多个字符。

mysql> SELECT name, age
    -> FROM one_piece
    -> WHERE name LIKE '乌_普';

8. 创建计算字段

8.1 拼接字段

下面将 name ， sex 两列进行合并。并通过 AS 关键字进行给新列赋予别名。

mysql> SELECT Concat(name, '(', sex, ')') AS new_column
    -> FROM one_piece;

8.2 执行算数计算

通过 quantity （数量）、 price （价格）来计算 total_price （总价）

mysql> SELECT quantity, price,
    -> quantity * price AS total_price
    -> FROM test

9. 函数

9.1 常用文本处理函数

函数	说明
LEFT(str, length)	返回指定长度的字符串的左边部分
RIGHT(str, length)	返回指定长度的字符串右边部分
LTRIM(str)	去掉字符串左边的空格
RTRIM(str)	去掉字符串右边的空格
LOWER(str)	将字符串转换为小写
UPPER(str)	将字符串转换为大写
LENGTH(str)	返回字符串的长度

使用 LENGTH(str) 获取字符串的长度。

mysql> SELECT name, LENGTH(name) AS length
    -> FROM one_piece;

9.2 日期和时间处理函数

查询在 2000年出生的人员信息。

mysql> SELECT *
    -> FROM test
    -> WHERE YEAR(brithday)=2000;

9.3 数值处理函数

函数	说明
ABS()	返回一个数的绝对值
COS()	返回一个角度的余弦
SIN()	返回一个角度的正弦
TAN()	返回一个角度的正切
PI()	返回圆周率
EXP()	返回一个数的指数值
SQRT()	返回一个数的平方根

以 ABS() 函数为例

sql> SELECT ABS(-1);
+---------+
| ABS(-1) |
+---------+
|       1 |
+---------+

10. 数据聚集

10.1 聚集函数

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

10.1.1 AVG() 函数

查询平均 age 。

mysql> SELECT AVG(age) AS avg_age
    -> FROM one_piece

10.1.2 COUNT() 函数

两种使用方式：

COUNT(*) 对表中行的数目进行计数，包括空值。

mysql> SELECT COUNT(*) AS num_person
    -> FROM one_piece;

COUNT(column) 对特定列中非 NULL 行进行计数。

mysql> SELECT COUNT(name) AS num_name
    -> FROM one_piece;

10.1.3 MAX() & MIN() 函数

当 column 列为数值列， MAX(column) / MIN(column) 返回 column 列中的最大值 / 最小值。

当 column 列为文本数据， MAX(column) / MIN(column) 返回 column 列数据排序后的最后一行 / 最前面的行。

10.1.4 SUM() 函数

SUM() 用来返回指定列值的和（总计）（忽略列值为 NULL 的行）。

mysql> SELECT SUM(price * quantity) AS total_price
    -> FROM test

10.2 组合聚集函数

计算 one_piece 表中数据的条数，年龄的最小值、最大值和平均值。

mysql> SELECT COUNT(*) AS num_person,
    -> MIN(age) AS age_min,
    -> MAX(age) AS age_max,
    -> AVG(age) AS age_avg
    -> FROM one_piece;

11. 数据分组

11.1 数据分组

使用分组将数据分为多个逻辑组，对每个组进行聚集计算。
例：统计各个海贼团( pirates )的人数。

mysql> SELECT pirates, COUNT(*) AS num_person
    -> FROM one_piece
    -> GROUP BY pirates;

group by注意事项:

GROUP BY 可以嵌套使用。
GROUP BY 子句中列出的每一列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在 SELECT 中使用表达式，则必须在 GROUP BY 子句中指定相同的表达式。不能使用别名。
除聚集计算语句外，SELECT 语句中的每一列都必须在 GROUP BY 子句中给出。
如果分组列中包含具有 NULL 值的行，则 NULL 将作为一个分组返回。如果列中有多行 NULL 值，它们将分为一组。
GROUP BY 子句必须出现在 WHERE 子句之后，ORDER BY 子句之前。

11.2 过滤分组

使用 HAVING 子句在数据分组后进行过滤。

查询海贼团人数在500人以上的海贼团名称及人数。

mysql> SELECT pirates, COUNT(*) AS num_person
    -> FROM one_piece
    -> GROUP BY pirates
    -> HAVING COUNT(*) >= 500;

WHERE 与 HAVING 的主要区别：

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

SELECT子句顺序：

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

12. 子查询

12.1 利用子查询进行过滤

现在查询草帽海贼团的排名信息。

mysql> SELECT rank
    -> FROM rank_info
    -> WHERE id IN (SELECT id
    ->                     FROM one_piece
    ->                     WHERE pirates = '草帽海贼团');

注意：

在 SELECT 语句中，子查询总是从内向外处理。
作为子查询的 SELECT 语句只能查询单个列。检索多个列会报错。

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

查询海贼团排名和任务信息，首先从 one_piece 表中根据 id 检索出排名信息，再统计每个冒险团的人数。

mysql> SELECT rank,
    -> (SELECT COUNT(*)
    ->  FROM one_piece AS oe
    ->  WHERE oe.id = ro.id) AS num_person
    -> FROM rank_info AS ro
    -> ORDER BY rank;

注意：上面的例子中使用的是 oe.id 和 ro.id ，而不是直接使用 id ，因为在两个表中都有 id 列，在有可能混淆列名时必须使用这种语法。

13. 表联结

13.1 自联结

假如现在有人不知道乔巴所属的海贼团, 想要知道乔巴所属海贼团的所有成员名称与赏金。
先看一下子查询的方式：

mysql> SELECT name, bounty
    -> FROM one_piece
    -> WHERE pirates = (SELECT pirates
    ->                    FROM one_piece
    ->                    WHERE name = '乔巴');

接下来使用自联结的方式：

mysql> SELECT c1.name, c1.bounty
    -> FROM Customers AS c1, Customers AS c2
    -> WHERE c1.pirates = c2.pirates
    ->  AND c2.name = '乔巴';

通常情况下，自联结的方式比子查询的方式要快很多。

13.2 等值联结

联结是一种机制，用来在一条 SELECT 语句中关联表，因此称为联结。使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行。联结不是物理实体。换句话说，它在实际的数据库表中并不存在。它只在查询执行期间存在。

两表 table1， table2 中数据如下：

table1                      table2
+------+------+------+ +------+------+------+
| A    | B    | C    |      | C    | D    | E    |
+------+------+------+ +------+------+------+
|    1 |    2 |    3 |      |    2 |    3 |    4 |
|    4 |    5 |    6 |      |    6 |    7 |    8 |
+------+------+------+ +------+------+------+

现在通过表联结，获取两个表中的数据。

mysql> SELECT *
    -> FROM table1 AS t1, table2 AS t2
    -> WHERE t1.C = t2.C;
+------+------+------+------+------+------+
| A    | B    | C    | C    | D    | E    |
+------+------+------+------+------+------+
|    4 |    5 |    6 |    6 |    7 |    8 |
+------+------+------+------+------+------+

注意：上例中WHERE 中限制了联结条件，如果没有条件的话，返回的结果就是两表的笛卡尔积，返回 6 × 9 共 54条数据

13.3 内联结

上面的联结准确来说是等值联结，也可以称为内联结，它还有另一种语法。返回的结果以上面相同。

mysql> SELECT *
    -> FROM table1 AS t1 INNER JOIN table2 AS t2
    ->   ON t1.C = t2.C;
+------+------+------+------+------+------+
| A    | B    | C    | C    | D    | E    |
+------+------+------+------+------+------+
|    4 |    5 |    6 |    6 |    7 |    8 |
+------+------+------+------+------+------+

一般内联结可以用如下图进行表示，取两个表关联字段相同的部分。

13.4 自然联结

自然连接是一种特殊的等值连接，它在两个关系表中自动比较相同的属性列，无须添加连接条件，并且在结果中消除重复的属性列。

mysql> SELECT *
    -> FROM table1 AS t1 NATURAL JOIN table2 t2;
+------+------+------+------+------+
| C    | A    | B    | D    | E    |
+------+------+------+------+------+
|    6 |    4 |    5 |    7 |    8 |
+------+------+------+------+------+

13.5 外联结

13.5.1 左外联结

左外联结，左表( table1 )的记录将会全部表示出来，而右表( table2 )只会显示符合搜索条件的记录。右表记录不足的地方均为 NULL 。

mysql> SELECT *
    -> FROM table1 AS t1 LEFT JOIN table2 AS t2
    ->   ON t1.C = t2.C;
+------+------+------+------+------+------+
| A    | B    | C    | C    | D    | E    |
+------+------+------+------+------+------+
|    4 |    5 |    6 |    6 |    7 |    8 |
|    1 |    2 |    3 | NULL | NULL | NULL |
+------+------+------+------+------+------+

13.5.2 右外联结

右外联结，右表( table2 )的记录将会全部表示出来，而右左表( table1 )只会显示符合搜索条件的记录。左表记录不足的地方均为 NULL 。

mysql> SELECT *
    -> FROM table1 AS t1 RIGHT JOIN table2 AS t2
    ->  ON t1.C = t2.C;
+------+------+------+------+------+------+
| A    | B    | C    | C    | D    | E    |
+------+------+------+------+------+------+
|    4 |    5 |    6 |    6 |    7 |    8 |
| NULL | NULL | NULL |    2 |    3 |    4 |
+------+------+------+------+------+------+

13.6 四种联结对比图

14. 插入数据

14.1 插入完整行

使用 INSERT 插入完整行它要求指定表名和插入到新行中的值。

mysql> INSERT INTO one_piece
    -> VALUES('1',
    ->        '草帽海贼团',
    ->        '路飞',
    ->        'age',
    ->        '团长',
    ->        '1500000000');

注意：

必须每列提供一个值，空值使用NULL
各列必须以它们在表定义中出现的次序填充

14.2 插入部分行

INSERT 推荐的插入方法是明确给出表的列名。这样还可以省略列，即只给某些列提供值，给其他列不提供值。

省略的列必须满足以下某个条件：

该列定义为允许 NULL 值（无值或空值）。
在表定义中给出默认值（如果不给出值，将使用默认值）。

如果表中不允许有 NULL 值或者默认值，这时却省略了表中的值， DBMS 就会产生错误消息，相应的行不能成功插入。

现在同样在 one_piece 表中插入一行。

mysql> INSERT INTO one_piece(id,
    ->                       pirates,
    ->                       name)
    -> VALUES('1',
    ->        '草帽海贼团',
    ->        '路飞');

不管使用哪种INSERT 语法，VALUES 的数目都必须正确。如果不提供列名，则必须给每个表列提供一个值；如果提供列名，则必须给列出的每个列一个值。否则，就会产生一条错误消息，相应的行不能成功插入。

14.3 从一个表复制到另一个表

有一种数据插入不使用 INSERT 语句。要将一个表的内容复制到一个全新的表（运行中创建的表）。

mysql> CREATE TABLE one_pieceCopy AS
    -> SELECT * FROM one_piece;

任何 SELECT 选项和子句都可以使用，包括 WHERE 和 GROUP BY。
可利用联结从多个表插入数据。
不管从多少个表中检索数据，数据都只能插入到一个表中。

主要用途：它是试验新 SQL 语句前进行表复制的很好工具。先进行复制，可在复制的数据上测试 SQL 代码，而不会影响实际的数据。

15.更新数据

使用 UPDATE 语句，更新（修改）表中的数据。

有两种使用 UPDATE 的方式：

更新表中的特定行
更新表中的所有行

使用时要明确是 更新特定行 还是 更新所有行。

UPDATE 语句中可以使用子查询，使得能用 SELECT 语句检索出的数据更新列数据。

15.1 更新单行单列

将 路飞 的赏金更新为 10000000000

mysql> UPDATE one_piece
    -> SET bounty = 10000000000
    -> WHERE name = '路飞';

15.2 更新单行多列

在更新多个列时，只需要使用一条 SET 命令，每个“列=值”对之间用逗号分隔（最后一列之后不用逗号）。

mysql> UPDATE one_piece
    -> SET bounty = 10000000000,
    ->     age = '19'
    -> WHERE name = '路飞';

15.3 更新所有行

不使用 WHERE 限制条件，即更新表中所有行。

mysql> UPDATE one_piece
    -> SET bounty = 10000000000,
    ->     age = '19'

15.4 删除列中的值

假如表定义允许 NULL 值，要删除某个列的值，可设置它为 NULL。（要注意删除列值（保留列结构）和删除列（完全删除）的区别）

mysql> UPDATE one_piece
    -> SET bounty = NULL
    -> WHERE name = '路飞';

16. 删除数据

使用 DELETE 语句，删除表中的数据。

有两种使用 DELETE 的方式：

删除表中的特定行
删除表中的所有行

使用时要明确是 删除特定行 还是 删除所有行。

16.1 删除单行

删除 one_piece 表中 name 为 路飞 的行。

mysql> DELETE FROM one_piece
    -> WHERE name = '路飞';

16.2 删除所有行

删除 Customers 中的所有行。不删除表本身。

mysql> DELETE FROM one_piece;

如果想从表中删除所有行，推荐使用 TRUNCATE TABLE 语句，它完成相同的工作，而速度更快（因为不记录数据的变动）。

但要注意： TRUNCATE 属于数据定义语言（ DDL ），且 TRUNCATE 命令执行后无法回滚，使用 TRUNCATE 命令之前最好对当前表中的数据做备份。

mysql> TRUNCATE TABLE one_piece;

17.存储过程

17.1 为什么使用存储过程

优点：

通过把 “某个处理” 封装在一个易用的单元中，可以简化复杂的操作
由于不要求反复建立一系列处理步骤，因而保证了数据的一致性。
简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化，那么只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。
因为存储过程通常以编译过的形式存储，所以DBMS处理命令所需的工作量少，提高了性能。

简单来说，使用存储过程有三个主要的好处：简单、安全、高性能。

17.2 创建存储过程

我们来看一个简单的存储过程例子，对草帽海贼团人数进行计数。

mysql> DELIMITER $$
mysql> CREATE PROCEDURE personCount()
    -> BEGIN
    ->     SELECT COUNT(*) AS num_person
    ->     FROM one_piece
    ->     WHERE pirates = '草帽海贼团';
    -> END$$

在定义过程时，使用 DELIMITER $$ 命令将语句的结束符号从分号 ; 临时改为 $$，使得过程体中使用的分号被直接传递到服务器，而不会被 Mysql 解释。

使用 CALL 存储过程名(参数) 来调用存储过程。

mysql> DELIMITER ;
mysql> CALL personCount();

17.3 存储过程体

存储过程体包含了在过程调用时必须执行的语句，例如：dml、ddl语句，if-then-else和while-do语句、声明变量的declare语句等。

过程体格式：以begin开始，以end结束(可嵌套)

BEGIN
　　BEGIN
　　　　BEGIN
　　　　　　statements; 
　　　　END
　　END
END

注意：每个嵌套块及其中的每条语句，必须以分号结束，表示过程体结束的 begin-end 块(又叫做复合语句 compound statement )，则不需要分号。

17.4 存储过程参数

存储过程可以有0个或多个参数，用于存储过程的定义。

3种参数类型：

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

17.4.1 IN输入参数

mysql> DELIMITER $$
mysql> CREATE PROCEDURE in_test(IN p_in INT)
    -> BEGIN
    ->     SELECT p_in;    -- 第一次查询
    ->     SET p_in = 2;   -- 修改p_in的值
    ->     SELECT p_in;    -- 第二次查询
    -> END$$
    
mysql> DELIMITER ;
mysql> SET @p_in = 1;
mysql> CALL in_test(@p_in);
+------+
| p_in |
+------+
|    1 |
+------+
+------+
| p_in |
+------+
|    2 |
+------+
mysql> SELECT @p_in;
+-------+
| @p_in |
+-------+
|     1 |
+-------+

虽然 p_in 在存储过程中被修改，但并不影响 @p_in 的值，因为 in_test 只接受输入参数，并不输出参数，所以相当于在函数内改变，但并未将这个值输出给 @p_in 。

17.4.2 OUT输入参数

mysql> DELIMITER $$
mysql> CREATE PROCEDURE out_test(OUT p_out INT)
    -> BEGIN
    ->     SELECT p_out;    -- 第一次查询
    ->     SET p_out = 2;   -- 修改p_out的值
    ->     SELECT p_out;    -- 第二次查询
    -> END$$
mysql> DELIMITER ;
mysql> SET @p_out = 1;
mysql> CALL out_test(@p_out);
+-------+
| p_out |
+-------+
|  NULL |
+-------+
+-------+
| p_out |
+-------+
|     2 |
+-------+
mysql> SELECT @p_out;
+--------+
| @p_out |
+--------+
|      2 |
+--------+

第一个返回结果为 NULL 是因为 OUT 是向调用者输出参数，不接收输入的参数，所以第一次查询时 p_out 还未赋值，所以是 NULL 。最后 @p_out 变量的值变为2是因为调用了 out_test 存储过程，输出参数，改变了 p_out 变量的值。

17.4.3 INOUT输入输出参数

mysql> DELIMITER $$
mysql> CREATE PROCEDURE inout_test(INOUT p_inout INT)
    -> BEGIN
    ->     SELECT p_inout;    -- 第一次查询
    ->     SET p_inout = 2;   -- 修改p_inout的值
    ->     SELECT p_inout;    -- 第一次查询
    -> END$$
    
mysql> DELIMITER ;
mysql> SET @p_inout = 1;
mysql> CALL inout_test(@p_inout);
+---------+
| p_inout |
+---------+
|       1 |
+---------+
+---------+
| p_inout |
+---------+
|       2 |
+---------+
mysql> SELECT @p_inout;
+----------+
| @p_inout |
+----------+
|        2 |
+----------+

调用 inout_test 存储过程，既接受了输入的参数，也输出参数， @p_inout 的值被改变。

17.5 删除存储过程

使用 DROP PROCEDURE 来删除存储过程。

mysql> DROP PROCEDURE in_test;

18.管理事务处理

18.1 事务处理

事务处理（transaction processing）是一种机制，用来管理必须成批执行的SQL` 操作，保证数据库不包含不完整的操作结果。利用事务处理，可以保证一组操作不会中途停止，它们要么完全执行，要么完全不执行（除非明确指示）。如果没有错误发生，整组语句提交给（写到）数据库表；如果发生错误，则进行回退（撤销），将数据库恢复到某个已知且安全的状态，以此来维护数据库的完整性。

事务处理术语：

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

事务处理用来管理 INSERT、UPDATE 和 DELETE 语句。不能回退 SELECT 语句（回退 SELECT 语句也没有必要），也不能回退 CREATE 或 DROP 操作。事务处理中可以使用这些语句，但进行回退时，这些操作也不撤销。

一般来说，事务是必须满足4个条件（ ACID ）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

原子性： 一个事务（ transaction ）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（ Rollback ）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性： 在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性： 数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（ Read uncommitted ）、读提交（ read committed ）、可重复读（ repeatable read ）和串行化（ Serializable）。
持久性： 事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

18.2 控制事务处理

BEGIN / START TRANSACTION ：显式地开启一个事务；
COMMIT / COMMIT WORK ：提交事务，使已对数据库进行的所有修改成为永久性的；
ROLLBACK / ROLLBACK WORK ：回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
SAVEPOINT ：SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
RELEASE SAVEPOINT ：删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
ROLLBACK TO ：把事务回滚到标记点；
SET TRANSACTION ：用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有 READ UNCOMMITTED（读未提交）、READ COMMITTED（读已提交，项目中常用）、REPEATABLE READ（可重复读，Mysql 默认隔离级别）和 SERIALIZABLE（串行化）。

简单示例：

mysql> use test;
mysql> CREATE TABLE transaction_test(id int(5)) ENGINE = INNODB;  # 创建数据表
 
mysql> SELECT * FROM transaction_test;
Empty set (0.01 sec)
mysql> BEGIN;  # 开始事务
 
mysql> INSERT INTO runoob_transaction_test VALUE(1);
 
mysql> INSERT INTO runoob_transaction_test VALUE(2);
 
mysql> COMMIT; # 提交事务
 
mysql>  SELECT * FROM transaction_test;
+------+
| id   |
+------+
| 1    |
| 2    |
+------+
mysql> BEGIN;    # 开始事务
mysql> INSERT INTO transaction_test VALUES(3);
mysql> SAVEPOINT first_insert;    # 声明一个保存点
mysql> INSERT INTO transaction_test VALUES(4);
mysql> SAVEPOINT second_insert;    # 声明一个保存点
mysql> INSERT INTO transaction_test VALUES(5);
mysql> ROLLBACK TO second_insert;    # 回滚到 second_insert 保存点
mysql> SELECT * FROM transaction_test;    # 因为回滚所以数据没有插入
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
mysql> ROLLBACK TO first_insert;
mysql> SELECT * FROM transaction_test;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
mysql> COMMIT;    # 执行 COMMIT 或 ROLLBACK 后保存点自动释放

18.3 事务隔离级别

先看一下如何查看事务隔离级别。

# 查看默认事务隔离级别（session）
select @@transaction_isolation;
# 查看当前会话的隔离级别
select @@session.transaction_isolation;
# 查看全局的事务隔离级别
select @@global.transaction_isolation;

在 Mysql 下事务的隔离级别有四种，由低到高依次为 Read uncommitted 、Read committed 、Repeatable read (默认)、Serializable ，这四个级别中的后三个级别可以逐个解决脏读、不可重复读 、幻读的问题。

18.3.1 脏读

对于两个事务T1与T2，T1读取了已经被T2更新但是还没有提交的字段之后，若此时T2回滚，T1读取的内容就是临时并且无效的。

示例：

打开两个Mysql客户端，分别执行下面操作，查询当前会话的隔离级别（默认 REPEATABLE READ ）。修改当前会话隔离级别为（ READ UNCOMMITTED ）。全局事务隔离级别仍然为 REPEATABLE READ 。

mysql> SELECT @@session.transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;    # 修改会话隔离级别
mysql> SELECT @@session.transaction_isolation;    # 当前会话隔离级别已修改
+---------------------------------+
| @@session.transaction_isolation |
+---------------------------------+
| READ-UNCOMMITTED                |
+---------------------------------+
mysql> SELECT @@global.transaction_isolation;    # 全局事务隔离级别未修改
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| REPEATABLE-READ                |
+--------------------------------+

之后黑框用来做更新，白框用来查询。

由于黑框的 ④ 回滚，白色背景的客户端中 ③ 读取的数据就是临时并且无效的。即脏读。

18.3.2 不可重复读

对于两个事务T1和T2，T1读取了一个字段，然后T2更新了该字段并提交之后，当T1再次读取的时候，结果不一致的情况发生。

由于黑框的更新操作，白框出现两次读取的结果不一致。

18.3.3 幻读

对于两个事务T1、T2，T1从表中读取数据，然后T2进行了INSERT操作并提交，当T1再次读取的时候，结果不一致的情况发生。

由于黑框的插入操作，白框出现两次读取的结果不一致。

几种现象在各隔离级别中出现的可能性：

隔离级别	脏读（Dirty Read）	不可重复读（NonRepeatable Read）	幻读（Phantom Read）
未提交读（Read uncommitted）	可能	可能	可能
已提交读（Read committed）	不可能	可能	可能
可重复读（Repeatable read）	不可能	不可能	可能
可串行化（Serializable ）	不可能	不可能	不可能

19. 游标

SQL 检索操作返回结果集，简单地使用 SELECT 语句，没有办法得到第一行、下一行或前 10 行。有时，需要在检索出来的行中前进或后退一行或多行，这就是游标的用途所在。游标（cursor）是一个存储在 DBMS 服务器上的数据库查询，它不是一条 SELECT 语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

19.1 使用游标

游标使用的步骤：

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

声明游标后，可以根据需要频繁地 打开或关闭 游标。在游标打开时，可根据需要频繁地执行取操作。

注意：不像多数DBMS，MySQL游标只能用于存储过程（和函数）。

19.2 创建游标

使用 DECLEAR 来创建游标，DECLARE 命名游标，并定义相应的 SELECT 语句，根据需要带 WHERE 和其他子句。

下面的语句定义了名为 ordernumbers 的游标，使用了可以检索所有订单的 SELECT 语句。

Order表中的信息：

DROP PROCEDURE IF EXISTS processorder;
CREATE PROCEDURE processorder()
BEGIN
	-- 定义游标
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
END;

这个存储过程中，使用 DECLARE 语句用来定义和命名游标。存储过程处理完成后，游标就消失（因为它局限于存储过程）。

19.3 使用游标数据

使用 OPEN 语句来打开游标，CLOSE 语句关闭游标，在一个游标被打开后，可以使用 FETCH 语句分别访问它的每一行。FETCH 指定检索的数据（所需的列），数据存储的位置（定义的变量）。它还向前移动游标中的内部行指针，使下一条 FETCH 语句检索下一行（不重复读取同一行）。

DROP PROCEDURE IF EXISTS processorder;
CREATE PROCEDURE processorder()
BEGIN
    -- 定义局部变量
    DECLARE num INT;
    -- 定义游标
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
    -- 打开游标
    OPEN ordernumbers;
    -- 获取第一行数据
    FETCH ordernumbers INTO num;
    -- 查询结果
    SELECT num;
    -- 关闭游标
    CLOSE ordernumbers;
END;
CALL processorder();

其中 FETCH 用来检索当前行的 order_num 列（将自动从第一行开始）到一个名为 num 的局部变量中，并将查询 num 的结果。由于只检索到第一行，所以 num 的值为 ‘20005’。

下面，循环检索数据，从第一行到最后一行。

DROP PROCEDURE IF EXISTS processorder;
CREATE PROCEDURE processorder()
BEGIN
	-- 定义局部变量
	DECLARE done BOOLEAN DEFAULT false;
	DECLARE num INT;
	-- 定义游标
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
	-- 定义CONTINUE HANDLER
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=true;
	-- 打开游标
	OPEN ordernumbers;
	-- 循环所有行
	REPEAT
		-- 获取第一行数据
		FETCH ordernumbers INTO num;
	-- 结束循环
	UNTIL done END REPEAT;
	-- 查询结果
	SELECT num;
	-- 关闭游标
	CLOSE ordernumbers;
END;
CALL processorder();

循环了结果集的所有行，所以 num 的值是最后一行的数据。

与上一个例子不同之处是，这个例子的 FETCH 是在 REPEAT 内，因此它反复执行直到 done 为真。
DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET done=true;

这条语句定义了一个 CONTINUE HANDLER，它是在条件出现时被执行的代码。这里，它指出当 SQLSTATE '02000' 出现时，SET done=true。SQLSTATE '02000'是一个未找到条件，当 REPEAT 由于没有更多的行供循环而不能继续时，出现这个条件。

19.4 游标的优缺点

优点：游标是面向集合与面向行的设计思想之间的一种桥梁，因为游标是针对行操作的，所以对从数据库中 SELECT 查询得到的每一行可以进行分开的独立的相同或不同的操作，是一种分离的思想。可以满足对某个结果行进行特殊的操作。如基于游标位置的增删改查能力。

缺点：

速度较慢
会产生死锁现象
内存大

20. 约束

20.1 约束

DBMS 通过在数据库表上施加约束来实施引用完整性。大多数约束是在表定义中定义的，用 CREATE TABLE 或是 ALTER TABLE 语句。

20.2 主键

主键是一种特殊的约束，用来保证一列（或一组列）中的值是唯一的，而且永不改动。没有主键，要安全地 UPDATE 或 DELETE 特定行而不影响其他行会非常困难。

主键的条件：

任意两行的主键值都不相同。
每行都具有一个主键值（即列中不允许 NULL 值）。

创建表时定义主键。

CREATE TABLE teacher
(
	id INT(11) PRIMARY KEY,
	teacher_name VARCHAR(10)
);

使用 ALTER TABLE 添加主键。

ALTER TABLE teacher
ADD CONSTRAINT PRIMARY KEY(id);

删除主键约束。

ALTER TABLE teacher DROP PRIMARY KEY;

20.3 外键

外键是表中的一列，其值必须列在另一表的主键中。外键是保证引用完整性的极其重要部分。

下面新建 student 表并添加外键 teacher_id 与 teacher 表中的主键 id 进行关联。

在创建表的时定义外键。

CREATE TABLE student
(
	stu_id INT(11) PRIMARY KEY,
	teacher_id INT(11) REFERENCES teacher(id),
	stu_name VARCHAR(10)
);

使用 ALTER TABLE 添加外键。

ALTER TABLE student
ADD CONSTRAINT teacher_id_id
FOREIGN KEY (teacher_id) REFERENCES teacher(id);

使用外键可以有效地防止意外删除，比如在上面两表中如果删除 teacher 表中的信息，如果该 id 在 student 表中也有出现，那么 Mysql 会防止删除操作。当然也可以启用级联删除的特性，那么在删除时就会删除所有相关信息。

删除外键。

ALTER TABLE student DROP FOREIGN KEY teacher_id_id;

20.4 唯一约束

唯一约束用来保证一列（或一组列）中的数据是唯一的。它们类似于主键，但存在以下重要区别。

表可包含多个唯一约束，但每个表只允许一个主键。
唯一约束列可包含 NULL 值。
与主键不一样，唯一约束不能用来定义外键。

在创建表的时定义唯一约束。

CREATE TABLE student
(
	stu_id INT(11) PRIMARY KEY,
	teacher_id INT(11) REFERENCES teacher(id),
	stu_name VARCHAR(10)
);

使用 ALTER TABLE 添加唯一约束。

ALTER TABLE student
ADD CONSTRAINT unique_id UNIQUE(stu_id);

删除唯一性约束。

ALTER TABLE student DROP INDEX unique_id;

20.5 检查约束

检查约束用来保证一列（或一组列）中的数据满足一组指定的条件。

常见用途：

检查最小或最大值。
指定范围。
只允许特定的值。

下面创建一个检查约束来限制性别列只能输入男、女。

在创建表的时定义检查约束。

CREATE TABLE student
(    
    stu_id INT(11) PRIMARY KEY,
	gender VARCHAR(1) CHECK(gender IN('男', '女'))
);

使用 ALTER TABLE 添加检查约束。

ALTER TABLE student ADD CONSTRAINT check_gender CHECK(gender in ('男', '女'));

删除检查约束。

ALTER TABLE student DROP CHECK check_gender;

21. 索引

索引用来排序数据以加快搜索和排序操作的速度。主键数据总是排序的，因此，按主键检索特定行总是一种快速有效的操作。但是，搜索其他列中的值通常效率不高。这时候我们可以使用索引，在一个或多个列上定义索引，使 DBMS保存其内容的一个排过序的列表。在定义了索引后，DBMS 以使用书的索引类似的方法使用它。DBMS 搜索排过序的索引，找出匹配的位置，然后检索这些行。

索引特点：

索引提高检索的性能，但降低了数据增删改的性能。在执行这些操作时，DBMS 必须动态地更新索引。
索引数据可能要占用大量的存储空间。
并非所有数据都适合做索引。取值不多的数据（如地区）不如具有更多可能值的数据（如姓名），能够更加体现索引的价值。
索引用于数据过滤和数据排序。如果你经常以某种特定的顺序排序数据，则该数据可能适合做索引。
可以在索引中定义多个列（例如，国家 + 城市）。

21.1 普通索引

在创建表的时创建普通索引。

DROP TABLE IF EXISTS student;
CREATE TABLE student
(    
  	id INT(11),
	stu_name VARCHAR(10)
)

直接创建。

CREATE INDEX stu_id ON student(id);

修改表时创建。

ALTER TABLE student ADD INDEX stu_id(id);

删除索引。

DROP INDEX stu_id ON student;

21.2 唯一索引

唯一索引列值必须唯一，但允许有空值（注意和主键不同）。如果是组合索引，则列值的组合必须唯一。事实上，在许多场合，创建唯一索引的目的往往不是提高访问速度，而是为了避免数据出现重复。

CREATE UNIQUE INDEX stu_id ON student(id);

21.3 全局索引

全文索引只能作用在 CHAR、VARCHAR、TEXT、类型的字段上。创建全文索引需要使用 FULLTEXT 参数进行约束。

CREATE FULLTEXT INDEX s_name ON student(stu_name);

21.4 多列索引

多列索引，即在数据表的多个字段上创建索引。

CREATE TABLE student
(    
  id INT(11),
	stu_name VARCHAR(10),
	email VARCHAR(20),
	INDEX info(stu_name, email)
);

在多列索引中，只有查询条件中使用了这些字段中的第一个字段(即上面示例中的 stu_name 字段)，索引才会被使用（最左前缀’原则）。如果没有用到第一字段，则索引不起任何作用。

-- 使用索引
SELECT * FROM student WHERE stu_name = '张三';
SELECT * FROM student WHERE stu_name = '李四' AND email = '11111@qq.com';
-- 未使用索引
SELECT * FROM student WHERE email = '11111@qq.com';

❤️吐血总结《Mysql从入门到入魔》，图文并茂（建议收藏）❤️