06. MySQL高级操作

01. 数据库的备份与恢复

1.1 备份与恢复概述

• 在对数据库做大型改变之前，建议先备份数据库。

• 数据库的备份和恢复都是CMD指定，因此不需要登录MySQL。

  1.2 数据库的备份

• 数据库备份的语法格式：mysqldump -u 用户名 -h IP地址 -p 数据库名 > 存储路径

  • -h：数据库系统所在的IP地址。如果是在本机上，则-h可以省略，默认为-h localhost。
  • -u：登录数据库系统的用户名。
  • -p：回车结束操作，录入用户对应的密码。

• 示例：备份school数据库到D:\sql\school.sql文件中。

  C:\Users\Administrator>mysqldump -u root -p school > D:\sql\school.sql
  Enter password: ******

• 查看备份内容得知：

  • 存储的是对数据库的表的建立、数据的插入的指令。（有这些SQL指令就可以把数据恢复）
  • 值得注意的是，存储的指令中没有创建数据库的指令。
  • 即如果把数据库删除了，恢复数据的时候需要提前创建好一个数据库。

    1.3 数据库的恢复

• 数据库恢复的语法格式：mysql -u 用户名 -h IP地址 -p 数据库名 < SQL文件的路径

• 示例：删除school数据库，然后从D:\sql\school.sql文件中恢复。

  • 删除数据库。

    DROP DATABASE school;

  • 恢复数据库。 ```sql — 创建数据库（SQL命令）。 CREATE DATABASE school CHARSET = ‘utf8mb4’;

— 恢复数据（CMD命令）。 C:\Users\85034>mysql -u root -p school < D:\sql\school.sql Enter password: **

<a name="Bteto"></a>
## 02. 数据库事务TCL
<a name="rUlKc"></a>
### 2.1 数据库事务介绍
<a name="H5amA"></a>
#### 2.1.1 TCL、事务与回滚
- TCL（Transaction Control Language），即事务控制语言。
- 事务是指一个或一组SQL语句组成的一个执行单元，这个执行单元作为一个不可分割的整体，其中的SQL语句要么全部执行，要么全部不执行。
   - 比如Dosbo和Adam都有1000元，此时Dosbo给Adam转账500元，那必然会涉及到如下两条SQL语句。
```sql
UPDATE account SET balance=500 WHERE `name`='Dosbo';
UPDATE account SET balance=1500 WHERE `name`='Adam';

• 此时如果第一条语句执行完成后，因为种种原因导致第二条语句无法执行，那么就会导致Dosbo白白损失500元，Adam也没有拿到钱。那么银行就需要承担相应的法律责任了。
• 为了避免这种情况的发生，当第二条语句无法正常执行完成时，事务就需要回滚撤销第一条语句的执行。因此事务可以保证执行单元中的SQL语句要么全部执行，要么全部不执行。
  • 回滚是指撤销整个执行单元之前已经完成的所有的操作，回到最初的状态。

    2.1.2 存储引擎

• MySQL中不同的存储数据的技术称为存储引擎。存储引擎决定数据在数据库中的存储方式。通过SHOW ENGINES;语句可以查看MySQL支持的存储引擎。
• 在MySQL中使用最多的存储引擎是InnoDB（MySQL 5.5之后默认的存储引擎）、MyISAM（MySQL 5.5之前默认的存储引擎）、MEMORY。

• 其中InnoDB支持事务，而MyISAM、MEMORY等不支持事务。

  2.1.3 事务的ACID属性

• 原子性（Atomicity）：原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

• 一致性（Consistency）：事务必须使数据库从一个一致性状态变换到另一个一致性状态。比如在转账之前，Adam和Dosbo的余额之和是2000，转账后的余额之和依旧是2000，前后是一致的。
• 隔离性（Isolation）：一个事务在执行时，理论上不能受到其他事务的干扰（如同Java多线程中使用锁实现线程安全）。在日常开发中，经常需要并发的执行多个事务，此时每个事务之间就应该隔离开来，互不干扰（通过隔离级别控制）。

• 持久性（Durability）：持久性真正的改变了数据库中的数据。一旦一个事务被提交，那么它会数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。比如一个事务删除了一条数据，事务提交后就没有办法撤回了，只能使用INSERT语句再重新插入那条数据。

  2.2 数据库事务的实现

  2.2.1 事务的分类

• 隐式事务：没有明显开始与结束标记的事务。比如一条INSERT、UPDATE、DELETE语句就是一个隐式事务。

• 显示事务：具有明显开始与结束标记的事务。

  2.2.2 显示事务的创建

• 显示事务的创建步骤：

  • 开启事务。这个操作是可选的，只要关闭自动提交系统就会默认的开启事务。
  • 编写事务中的SQL语句。这里应该只包含SELECT、INSERT、UPDATE、DELETE四种语句，其他SQL语句没有事务之说。
  • 结束事务。事务的结束包括成功执行后的COMMIT提交事务，也包括事务执行失败之后的ROLLBACK回滚事务。
    • 事务SQL语句执行完成后，其修改的结果保留在内存中，如果执行COMMIT则会将内存中的修改结果写入到本地磁盘中；如果执行ROLLBACK则会直接删除内存中的处理结果。

• 示例：创建account表，完成转账。

  • 创建account表：

    DROP TABLE IF EXISTS account ;
    CREATE TABLE account (
    id INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(20),
    balance DOUBLE
    );
    INSERT INTO account (`name`, balance) VALUES ('Adam', 1000), ('Dosbo', 1000) ;

  • 实现转账事务： ```sql — 开启事务 START TRANSACTION;

— 编写转账事务的语句 UPDATE account SET balance=500 WHERE name=’Dosbo’; UPDATE account SET balance=1500 WHERE name=’Adam’;

— SQL语句执行完成，提交事务 COMMIT;

— 如果事务执行的结果不想要，则可以回滚 — ROLLBACK;

— 查看事务执行结果 SELECT * FROM account;

<a name="zg8fa"></a>
## 03. 视图VIEW
<a name="iACrq"></a>
### 3.1 视图介绍
- 有些SQL语句需要被重复使用多次，但是这些SQL写起来却非常麻烦。
- 为了解决这种繁琐的重复编码的场景，SQL中诞生了视图的概念。
- 所谓视图，就是将一个SQL的查询结果做个快照，以表的形式保存起来，之后直接查看这张表即可。
<a name="hWf81"></a>
### 3.2 视图的常用操作
<a name="pV0WQ"></a>
#### 3.2.1 创建视图
- 创建视图的SQL语句：`CREATE VIEW v_视图名 AS SELECT查询语句;`
   - `v_视图名`这个以`v`为前缀的命名方式主要为了区分视图名与表名。
- 示例：用视图的方式查询工资等级高于smith的员工信息。
```sql
-- 查询所有员工的信息以及工资等级。
SELECT * FROM emp AS e
    INNER JOIN salgrade AS s ON e.sal BETWEEN s.lowsal AND hisal;
-- 将查询结果封装成视图。
CREATE VIEW v_emp_salgrade AS (
    SELECT * FROM emp AS e
        INNER JOIN salgrade AS s ON e.sal BETWEEN s.lowsal AND hisal
);
-- 借助视图，查询工资等级高于smith的员工信息。
SELECT * FROM v_emp_salgrade
WHERE grade > (
    SELECT grade
    FROM v_emp_salgrade
    WHERE ename = 'smith'
);

3.2.2 查看视图

• 查看视图结果的SQL语句：SELECT * FROM v_视图名;

• 示例：查询视图v_emp_salgrade的结果。

  SELECT * FROM v_emp_salgrade;

• 查看数据库中所有的视图的SQL语句：SHOW TABLE STATUS WHERE comment='view';

  3.2.3 修改视图

• 修改视图名对应的SELECT结果的SQL语句：ALTER VIEW v_视图名 AS SELECT查询语句;

  3.2.4 删除视图

• 删除视图的SQL语句：DROP VIEW v_视图名;

  3.2.5 修改视图的数据

• 当视图中的数据完全来源于原表时，修改视图中的数据时原表中的数据会一起发送改变。

• 示例：将v_emp_salgrade视图中员工编号为7654的工资修改成1300。 ```sql — 修改前查看原表，发现7654员工的工资为1250。 SELECT * FROM emp WHERE empno = 7654;

— 修改视图中的数据。 UPDATE v_emp_salgrade SET sal = 1300 WHERE empno = 7654;

— 验证视图修改。 SELECT * FROM v_emp_salgrade WHERE empno = 7654;

— 查看原数据表中的数据。（原表中的工资也变成1300了） SELECT * FROM emp WHERE empno = 7654;

- 当视图中的数据是经过计算得到的时，则视图中的数据不可被修改。
- 示例：设计一个视图，该视图中存放着每个部门的平均薪资、最高薪资、最低薪资、每个月要发的工资总金额；然后将20号部门的最低工资改成900。
```sql
-- 查询每个部门的平均薪资、最高薪资、最低薪资、每个月要发的工资总金额。
SELECT
    deptno,
    ROUND(AVG(sal + IFNULL(comm, 0)), 2) AS avg_sal,
    MAX(sal + IFNULL(comm, 0)) AS max_sal,
    MIN(sal + IFNULL(comm, 0)) AS min_sal,
    SUM(sal) AS total
FROM emp
GROUP BY deptno;
-- 将查询结果创建成视图。
CREATE VIEW v_sal AS (
    SELECT
        deptno,
        ROUND(AVG(sal + IFNULL(comm, 0)), 2) AS avg_sal,
        MAX(sal + IFNULL(comm, 0)) AS max_sal,
        MIN(sal + IFNULL(comm, 0)) AS min_sal,
        SUM(sal) AS total
    FROM emp
    GROUP BY deptno
);
SELECT * FROM v_sal;
-- 将20号部门的最低工资改成900。
-- SQL语句运行时会报错：[HY000][1288] The target table v_sal of the UPDATE is not updatable
UPDATE v_sal SET min_sal = 900 WHERE deptno = 20;

3.3 视图控制用户访问列的权限

• 视图除了可以应用在保存查询结果外，还可以用于控制用户访问列的权限。
• 语法格式：CREATE VIEW v_视图名 AS SELECT 用户可查看的列 FROM 表;
• 示例：一般公司中是不允许员工之间相互打听工资的，但是其他信息是可以查看的，因此emp表中sal和comm字段的访问权限应该被限制。 ```sql — sal和comm字段不可被访问，那么视图中只需要列出除sal和comm字段之外的其他所有字段即可。 CREATE VIEW v_emp AS ( SELECT empno, ename, job, mgr, hiredata, deptno FROM emp );

— 此时查看视图，就看不到sal和comm字段了。 SELECT * FROM v_emp;

- 视图创建完成之后，就应该将视图的查看权限分配给用户了。（用户只具有查看视图的权限，不具有查看表的权限）
```sql
-- 创建普通员工用户
CREATE USER 'ordinary_emp'@'%' IDENTIFIED BY '123456';
-- 只分配查看视图的权限。
GRANT SELECT ON myemp2.v_emp TO 'ordinary_emp';

• 最后，切换到普通员工用户上，查看数据。 ```sql mysql> show tables; +—————————+ | Tables_in_myemp2 | +—————————+ | v_emp | +—————————+ 1 row in set (0.00 sec)

mysql> — 可以查看视图 mysql> SELECT * FROM v_emp; +———-+————+—————-+———+——————+————+ | empno | ename | job | mgr | hiredata | deptno | +———-+————+—————-+———+——————+————+ | 7369 | smith | clerk | 7902 | 1980-12-17 | 20 | | 7499 | allen | salesman | 7698 | 1981-02-20 | 30 | | 7521 | ward | salesman | 7698 | 1981-02-22 | 30 | | 7566 | jones | manager | 7839 | 1981-04-02 | 20 | | 7654 | martin | salesman | 7698 | 1981-09-28 | 30 | | 7698 | blake | manager | 7839 | 1981-05-01 | 30 | | 7782 | clark | manager | 7839 | 1981-06-09 | 10 | | 7788 | scott | analyst | 7566 | 1987-07-03 | 20 | | 7839 | king | president | NULL | 1981-11-17 | 10 | | 7844 | turner | salesman | 7698 | 1981-09-08 | 30 | | 7876 | adams | clerk | 7788 | 1987-07-13 | 20 | | 7900 | james | clerk | 7698 | 1981-12-03 | 30 | | 7902 | ford | analyst | 7566 | 1981-12-03 | 20 | | 7934 | miller | clerk | 7782 | 1981-01-23 | 10 | +———-+————+—————-+———+——————+————+ 14 rows in set (0.00 sec)

mysql> — 不可以查看原表 mysql> SELECT * FROM emp; ERROR 1142 (42000): SELECT command denied to user ‘ordinary_emp’@’localhost’ for table ‘emp’

<a name="Yz56v"></a>
## 04. 索引
<a name="oLAcD"></a>
### 4.1 索引的概念
- 在此之前，已经出现过了主键索引和唯一索引。
- 索引可以理解成书籍的目录。当表的数据量特别大时，通过索引来查找数据要比通过其他的键查找要快得多。
<a name="ZrE1Y"></a>
### 4.2 索引的基本操作
<a name="hKeQK"></a>
#### 4.2.1 查看表中的索引
- 查看表中的索引的语法格式：`SHOW INDEX FROM 表名;`
- 示例：查看emp表的索引。
```sql
SHOW INDEX FROM emp;

• 返回结果解读：
  • Table：索引所在的表。
  • Key_name：索引名称。
  • Column_name：索引作用的字段。
  • Cardinality：索引中的数据量。
  • Sub_part：基于作用的字段的哪一部分进行索引。

4.2.2 EXPLAIN语句执行计划

• 通过EXPLAIN SQL语句;可以查看一条SQL语句的执行计划。
• EXPLAIN的返回结果中包含：
  • select_type：查询类型，常见的值有：SIMPLE简单查询。
  • table：查询的表名。
  • type：查询的类型，常见的值有：
    • const：常量级别的查询，即就算查询数据量大，也可以在常量的时间内定位到目标数据，由此可以看出const的查询效率特别高。
    • ref：引用查找，表示通过索引定位到数据。查询效率虽然没有const那么高，但却比ALL快得多。
    • ALL：表示全局查找，查询的效率是最差的。
  • possible_keys：可能使用到的键。
  • key：真实使用到的键。
  • key_len：键的长度。
  • rows：过滤了多少行才定位到数据。

• 示例：查看SELECT * FROM emp WHERE empno = 7654;语句的执行计划。

  EXPLAIN SELECT * FROM emp WHERE empno = 7654;

  4.2.3 用EXPLAIN验证索引效率高

• 在4.1中有提到，通过索引来查找数据要比通过其他的键查找要快得多。

• 示例：以下两条SQL语句查询的是同一条数据。

  SELECT * FROM emp WHERE empno = 7654;
  SELECT * FROM emp WHERE ename = 'martin';

• 但是分别查看这两条SQL语句的执行计划，会发现明显的区别：

  • 第一条WHERE empno = 7654使用主键索引字段empno查找的SQL语句是const类型的查询，并且只过滤了1行数据就定位到了结果。
  • 第二条WHERE ename = 'martin'使用的是普通的字段查找，因此是ALL类型的查询，并且过滤了14行数据才定位到结果。 ```sql mysql> EXPLAIN SELECT * FROM emp WHERE empno = 7654; +——+——————-+———-+——————+———-+———————-+————-+————-+———-+———+—————+———-+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +——+——————-+———-+——————+———-+———————-+————-+————-+———-+———+—————+———-+ | 1 | SIMPLE | emp | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | +——+——————-+———-+——————+———-+———————-+————-+————-+———-+———+—————+———-+ 1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM emp WHERE ename = ‘martin’; +——+——————-+———-+——————+———+———————-+———+————-+———+———+—————+——————-+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +——+——————-+———-+——————+———+———————-+———+————-+———+———+—————+——————-+ | 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 14 | 10.00 | Using where | +——+——————-+———-+——————+———+———————-+———+————-+———+———+—————+——————-+ 1 row in set, 1 warning (0.00 sec)

- 由此可以看出，使用索引查询的效率要比不使用索引高的多。
- 主键索引查询效率高的原因：
   - 表中主键的字段实际上是有序的，在这个基础上通过主键查找时，其底层的查找算法是二分查找算法。
   - 二分查找算法要比顺序查找快很多。
<a name="Br1lN"></a>
#### 4.2.4 添加索引
- 向表中字段添加索引的语法格式：`CREATE INDEX idx_索引名 ON 表名(字段名);`
- 示例：给emp表的ename字段添加索引。
```sql
CREATE INDEX idx_name ON emp(ename);

• 此时再查看SELECT * FROM emp WHERE ename = 'martin';的执行计划，发现其查询效率已经大大提高了。（type变成了ref，rows=1）

  mysql> EXPLAIN SELECT * FROM emp WHERE ename = 'martin';
  +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
  | id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
  +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
  |  1 | SIMPLE      | emp   | NULL       | ref  | idx_name      | idx_name | 43      | const |    1 |   100.00 | NULL  |
  +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
  1 row in set, 1 warning (0.00 sec)

• 通过建立索引提高查询效率的原因：

  • 4.1中有提到，索引就类似于一本书籍的目录。
  • 那么CREATE INDEX idx_name ON emp(ename)给名字创建索引，实际上就是将表中所有的名字添加到了目录中。
  • 利用目录查询数据的效率肯定也比顺序查找来的高。
  • 但是表中有1000w个名字，那么就表示要将1000w个名字全部添加到目录中，此时这个目录就会非常厚。
  • 因此自定义索引实际上是以一种用空间换时间的思想来提高查询效率的。

• 自定义索引的注意点：

  • 自定义索引提升效率的本质是用空间换时间。
  • 但是对于计算机而言，内存空间也是一种比较宝贵的资源。
  • 因此在创建索引时，需要追求的是利用少量的空间来提升效率。

    4.2.5 删除索引

• 删除表中指定索引的语法格式：DROP INDEX idx_索引名 ON 表名;

• 示例：删除emp表的idx_name索引。

  DROP INDEX idx_name ON emp;

  4.2.6 前缀索引

• 前缀索引的语法格式：CREATE INDEX idx_索引名 ON 表名(字段名(元素索引));，其中元素索引是从1开始的。

• 在以利用少量的空间来提升效率的前提下，4.2.4中将表中所有的名字都添加到索引目录中显然是不合适的。

• 那么有一种优化思路就是给姓名中所有的姓添加索引，这样即节约了大部分空间，还提升了一部分效率。

  -- 给姓名中所有的姓添加索引实际上就是给ename字段的第一个元素添加索引。
  CREATE INDEX idx_pre_name ON emp(ename(1));

• 此时再查看SELECT * FROM emp WHERE ename = 'martin';的执行计划，发现其type还是ref不变，但rows变成了2。

  mysql> EXPLAIN SELECT * FROM emp WHERE ename = 'martin';
  +----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
  | id | select_type | table | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra       |
  +----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
  |  1 | SIMPLE      | emp   | NULL       | ref  | idx_pre_name  | idx_pre_name | 7       | const |    2 |   100.00 | Using where |
  +----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
  1 row in set, 1 warning (0.00 sec)

• 这说明前缀索引虽然效率没有直接索引整个字段来得高，但是还是比type=ALL没有索引来的快得多，并且还可以大大节省空间。

• rows=2是因为表中有两条m开头的名字的数据，需要再从这两条中查询出martin的数据。

  4.3 复合索引

  4.3.1 复合索引的概念

• 所谓的复合索引，就是同时给多个字段添加同一个索引。

• 注意：复合索引需要满足最左侧原则，即对数据进行过滤筛选时，必须带上最左侧的字段才具有索引价值，否则没有意义。

  4.3.2 创建符合索引

• 复合索引的语法格式：CREATE INDEX idx_索引名 ON 表名(字段1, 字段2, …, 字段N);

• 示例：给员工的姓与工作职位添加复合索引。 ```sql — 先删除前缀索引idx_pre_name。 DROP INDEX idx_pre_name ON emp;

— 创建复合索引。 CREATE INDEX idx_name_job ON emp(ename(1), job);

- 创建完成后，用`SHOW`语句可以查看到索引的信息。
   - idx_name_job索引作用域ename(1)和job两个字段。
   - 并且Seq_in_index优先级中，ename(1)的优先级为1，job的优先级为2。
```sql
mysql> SHOW INDEX FROM emp;
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| emp   |          0 | PRIMARY      |            1 | empno       | A         |          14 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| emp   |          1 | idx_name_job |            1 | ename       | A         |          10 |        1 |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| emp   |          1 | idx_name_job |            2 | job         | A         |          14 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.01 sec)

4.3.3 用复合索引查询数据

• 复合索引需要满足最左侧原则，即对数据进行过滤筛选时，必须带上最左侧的字段才具有索引价值，否则没有意义。

• 示例：查看只有job字段查找数据的执行计划，发现type的值是ALL全局查找，并且possible_key和key的值都为NULL，因此实际上并没有用到索引。

  mysql> EXPLAIN SELECT * FROM emp WHERE job = 'clerk';
  +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
  | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
  +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
  |  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |    10.00 | Using where |
  +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
  1 row in set, 1 warning (0.00 sec)

• 但是，复合索引查询除了最左侧的字段，右侧的任何字段都可以省略。

• 示例：查看只有ename字段查找数据的执行计划，发现type的值是ref引用查找，并且possible_key和key的值为idx_name_job，这就表示此次查询使用了idx_name_job索引。

  mysql> EXPLAIN SELECT * FROM emp WHERE ename = 'adams';
  +----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
  | id | select_type | table | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra       |
  +----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
  |  1 | SIMPLE      | emp   | NULL       | ref  | idx_name_job  | idx_name_job | 7       | const |    2 |   100.00 | Using where |
  +----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
  1 row in set, 1 warning (0.00 sec)

• 当然，效果最好的是复合索引中所有字段都使用的场景。

• 示例：查看ename字段和job字段都使用的查找数据的执行计划，发现possible_key和key的值为idx_name_job，并且rows=1，由此可以看出查询效率比只用ename字段来得高。

  mysql> EXPLAIN SELECT * FROM emp WHERE ename = 'adams' AND job = 'clerk';
  +----+-------------+-------+------------+------+---------------+--------------+---------+-------------+------+----------+-------------+
  | id | select_type | table | partitions | type | possible_keys | key          | key_len | ref         | rows | filtered | Extra       |
  +----+-------------+-------+------------+------+---------------+--------------+---------+-------------+------+----------+-------------+
  |  1 | SIMPLE      | emp   | NULL       | ref  | idx_name_job  | idx_name_job | 46      | const,const |    1 |   100.00 | Using where |
  +----+-------------+-------+------------+------+---------------+--------------+---------+-------------+------+----------+-------------+
  1 row in set, 1 warning (0.00 sec)

• 注意点1：

  • ename字段和job字段都使用指的是WHERE筛选中用AND连接两个条件，而非用OR连接。
  • 因为OR实际上是一个连接操作，它会单独用job字段和单独用ename字段都筛选一次数据。
  • 而前面验证过了，单独用job字段过滤数据并不会触发索引，因此用OR连接两个条件的查询也不会处罚索引。

• 示例：用OR连接两个字段，发现type=ALL全局查找。并且possible_keys虽然是idx_name_job，但key这个表示真实使用到的键的值为NULL，说明这条查询语句并没有用到索引。

  mysql> EXPLAIN SELECT * FROM emp WHERE ename = 'adams' OR job = 'clerk';
  +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
  | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
  +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
  |  1 | SIMPLE      | emp   | NULL       | ALL  | idx_name_job  | NULL | NULL    | NULL |   14 |    19.00 | Using where |
  +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
  1 row in set, 1 warning (0.00 sec)

• 注意点2：复合索引的最左侧原则并不是说最左侧的字段在查询时也要出现在最左侧，而是只需要查询条件结合到最左侧的字段即可。

• 示例：调转ename字段和job字段的位置，发现也还是触发了索引。

  mysql> EXPLAIN SELECT * FROM emp WHERE job = 'clerk' AND ename = 'adams';
  +----+-------------+-------+------------+------+---------------+--------------+---------+-------------+------+----------+-------------+
  | id | select_type | table | partitions | type | possible_keys | key          | key_len | ref         | rows | filtered | Extra       |
  +----+-------------+-------+------------+------+---------------+--------------+---------+-------------+------+----------+-------------+
  |  1 | SIMPLE      | emp   | NULL       | ref  | idx_name_job  | idx_name_job | 46      | const,const |    1 |   100.00 | Using where |
  +----+-------------+-------+------------+------+---------------+--------------+---------+-------------+------+----------+-------------+
  1 row in set, 1 warning (0.00 sec)

  4.4 索引的底层结构（B+树）

• 索引的查找算法是二分查找法，这是因为索引的底层结构使用的是B+树。

• 可以通过SHOW INDEX语句查询表索引信息，即可发现Index_type索引类型的值是BTREE，即B+树。

  mysql> SHOW INDEX FROM emp;
  +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
  | Table | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
  +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
  | emp   |          0 | PRIMARY      |            1 | empno       | A         |          14 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
  | emp   |          1 | idx_name_job |            1 | ename       | A         |          10 |        1 |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
  | emp   |          1 | idx_name_job |            2 | job         | A         |          14 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
  +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
  3 rows in set (0.00 sec)

• B+树简介：

  • B+树是一种树数据结构，通常用于数据库和操作系统的文件系统中。
  • B+树的特点是能够保持数据稳定有序，其插入与修改拥有较稳定的对数时间复杂度。
  • B+树元素自底向上插入，这与二叉树恰好相反。
• 图解B+树：
  • B+树的顶层上支存放的是中间的两个索引，图中即为3和5。
  • 对于小于3的就走左侧子分支；对于大于5的，就走右侧子分支；对于在3~5之间的，就走中间的子分支。
  • 通过这样的B+树结构，可以用二分查找算法快速的定位到一行数据。

05. 自定义函数

5.1 函数的概念与定义

5.1.1 函数的概念与定义

• MySQL中的函数其实与Python中函数的逻辑是一样的，就是将系统没有提供的逻辑自己实现封装一下。

• MySQL中自定义函数的语法格式：

  CREATE FUNCTION 函数名(形参1 数据类型, 形参2 数据类型, …) RETURNS 返回的数据类型
  BEGIN
      函数体
      RETURN 返回值
  END

  5.1.2 DELIMITER定义结束符

• SQL语句的结束符是分号;，但是函数体中又会有多条语句。

• 为了避免函数定义时函数体中出现分号导致SQL认为语句结束而出现语法错误，因此一般在函数定义之前都会修改结束符（一般修改成两个美元符号$$），当函数定义完成后，再修改回分号。

• 故，一个自定义函数推荐的语法格式为：

  DELIMITER $$
  CREATE FUNCTION 函数名(形参1 数据类型, 形参2 数据类型, …) RETURNS 返回的数据类型
  BEGIN
      函数体
      RETURN 返回值
  END $$
  DELIMITER ;

• 这里可能出现一个BUG：（概率性出现，并非100%会出现）

  • 第一次修改结束符的时候，只能修改成%%，即DELIMITER %%。
  • 并且在下一次修改之前，不能出现注释语句，否则会报错。
  • 第二次及以后则可以将结束符修改成任意符号，并且中间也可以有注释信息。

    5.1.3 在函数中定义变量

• 变量的定义：DECLARE 变量名 [DEFAULT 默认值]

• 变量的赋值：SET 变量名 = 值

  5.1.4 允许自定义函数设置

• MySQL 8.0开始默认不允许自定义函数，若直接自定义函数则会报错：[HY000][1418] This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

• 使用SET GLOBAL log_bin_trust_function_creators = 1;即可允许自定义函数。

  5.1.5 自定义函数基本实例

• 编写一个函数，用于计算一个日期与当前日期所差的年份。

  DELIMITER $$
  CREATE FUNCTION get_diff_year(other_date DATE) RETURNS INT
  BEGIN
    DECLARE `days` INT DEFAULT 0;  -- 记录两个时间相差的天数
    DECLARE `year` INT DEFAULT 0;  -- 将天数转换成年份
    -- 给变量赋值
    SET `days` = DATEDIFF(CURRENT_DATE(), other_date);
    SET `year` = FLOOR(`days` / 365);
    -- 返回变量
    RETURN `year`;
  END $$
  DELIMITER ;

• 定义好的函数在使用上与之前接触过的所有系统函数没有差别。

• 示例：用自定义函数查询一下员工的入职年限。（入职年限=YEAR(当前时间-入职时间)）

  SELECT
    empno,
    ename,
    hiredata,
    get_diff_year(hiredata) AS 入职年限
  FROM emp;

  5.2 函数中的流程控制

  5.2.1 分支结构

• 函数中的分支逻辑语法格式为：

  IF 条件表达式1 THEN
      执行的语句1;
  ELSEIF 条件表达式2 THEN
      执行的语句2;
  ELSE
      执行的语句3;
  END IF;

• 示例：定义一个函数，给学生成绩分等级。 ```sql — 函数定义 DELIMITER $$ CREATE FUNCTION get_level(score DECIMAL(5, 2)) RETURNS CHAR(1) BEGIN IF score >= 90 THEN

    RETURN 'A';

  ELSEIF score >= 80 THEN

    RETURN 'B';

  ELSEIF score >= 70 THEN

    RETURN 'C';

  ELSEIF score >= 60 THEN

    RETURN 'D';

  ELSE

    RETURN 'E';

  END IF; END $$ DELIMITER ;

— 函数使用 SELECT stu.sno, stu.sname, sco.cid, sco.score, get_level(IFNULL(sco.score, 0)) AS grade FROM student AS stu INNER JOIN score_tb AS sco on stu.sno = sco.sno;

<a name="aAGHb"></a>
#### 5.2.2 循环结构
- MySQL中有三种循环结构：LOOP循环、WHILE循环、REPEAT-UNTIL循环。
- LOOP循环：
   - LOOP循环的本质是建立一个死循环机制，然后在要退出循环逻辑的位置，使用`LEAVE`退出循环。
   - LOOP循环的语法格式：
```sql
标记: LOOP
        循环体
        LEAVE 标记
END LOOP;

• 示例：定义函数，传入一个参数n，求1~n的累加和。 ``sql -- 定义函数求1~n的累加和 DELIMITER $$ CREATE FUNCTION get_sum(num INT) RETURNS INT BEGIN DECLAREtotalINT DEFAULT 0; -- 接收求和 DECLAREcount` INT DEFAULT 1; — 计数器

  — 循环求和 A: LOOP SET total = total + count; SET count = count + 1;

  IF count = num + 1 THEN

     LEAVE A;

  END IF; END LOOP;

  RETURN total; END $$ DELIMITER ;

SELECT get_sum(10); — 55

- WHILE循环：
   - WHILE循环就类似于Java等语言中的while循环，当循环条件为真时，循环体会一直执行。
   - WHILE循环的语法格式：
```sql
WHILE 循环条件 DO
        循环体
END WHILE;

• 示例：定义函数，传入一个参数n，求1~n的累加和。 ``sql DELIMITER $$ CREATE FUNCTION get_sum1(num INT) RETURNS INT BEGIN DECLAREtotalINT DEFAULT 0; DECLAREcount` INT DEFAULT 1;

  WHILE count <= num DO SET total = total + count; SET count = count + 1; END WHILE;

  RETURN total; END $$ DELIMITER ;

SELECT get_sum1(10);

- REPEAT-UNTIL循环：
   - REPEAT-UNTIL循环类似于Java等语言中的do-while循环，但是区别在于REPEAT-UNTIL循环会一直循环到满足某个条件为止。
   - REPEAT-UNTIL循环的语法格式：
```sql
REPEAT
        循环体
UNTIL 退出循环的条件 
END REPEAT;

• 示例：定义函数，传入一个参数n，求1~n的累加和。 ``sql DELIMITER $$ CREATE FUNCTION get_sum2(num INT) RETURNS INT BEGIN DECLAREtotalINT DEFAULT 0; DECLAREcount` INT DEFAULT 1;

  REPEAT SET total = total + count; SET count = count + 1; UNTIL count = num + 1 END REPEAT;

  RETURN total; END $$ DELIMITER ;

SELECT get_sum2(10);

<a name="Ym7v0"></a>
## 06. 存储过程
<a name="EB7c5"></a>
### 6.1 存储过程的概念与SQL执行流程
- 存储过程可以理解成保存在MySQL服务中的一组已经封装好的常用的SQL代码。
- 把常用的SQL语句封装成存储过程，比单独执行一条一条的SQL语句要来的高效。
   - 单条SQL语句在执行时需要经过以下一些步骤：
      - 查询缓存：在一条SELECT语句执行之前，SQL会先去查询这条SELECT之前有没有执行过。若之前执行过，则会直接将之前执行的缓存记录返回。
      - 解析：若这条SELECT语句之前没有执行过（即查不到缓存），那么SQL会先解析这条SELECT语句，比如看看这条语句需要查询哪些字段、用了什么筛选条件、逻辑要求是什么等等。
      - 预处理与查询优化：语句解析完成后，SQL会对SELECT进行一个预处理，看看有没有可优化的机制，比如看看能不能用主键等索引提高查询效率等。
      - 查询：当预处理完成后，SQL就将SELECT语句正式的交给查询引擎进行查询操作。查询操作会从存储引擎中获取符合条件的数据，然后经过需要的运算，最后将数据返回给客户端。
![sql执行流程.png](https://cdn.nlark.com/yuque/0/2022/png/2692415/1669257384210-e66466ad-a34c-45fe-aaf3-41e5b1329af1.png#averageHue=%23f8f8f5&clientId=u24290e46-f430-4&from=paste&height=381&id=u748c4949&originHeight=880&originWidth=1848&originalType=binary&ratio=1&rotation=0&showTitle=false&size=714988&status=done&style=none&taskId=ue70e74a3-95b9-4e46-a6a0-5e1c91d917d&title=&width=800)
   - 当存储过程定义完成时，存储过程中的SQL语句会完成编译并存储在服务器上。
   - 此时执行存储过程，相当于直接执行最后一步查询操作，查询缓存、解析、预处理与查询优化这三步都无需再做一次了。
<a name="WZRxf"></a>
### 6.2 三种存储过程结构的创建与调用
<a name="i1xNf"></a>
#### 6.2.1 无参存储过程的创建与调用
- 构造无参存储过程的语法格式：
```sql
DELIMITER $$
CREATE PROCEDURE 存储过程名()
BEGIN
    存储过程中的SQL语句;
END $$
DELIMITER ;

• 调用无参存储过程的语法格式：CALL 存储过程名();
• 示例：创建一个存储过程，用于给10号部门的员工涨薪10%，20号部门涨薪20%，30号部门涨薪30%。 ```sql — 定义存储过程 DELIMITER $$ CREATE PROCEDURE update_sal() BEGIN UPDATE emp SET sal = sal + sal 0.1 WHERE deptno = 10; UPDATE emp SET sal = sal + sal 0.2 WHERE deptno = 20; UPDATE emp SET sal = sal + sal * 0.3 WHERE deptno = 30; END $$ DELIMITER ;

— 调用存储过程 CALL update_sal();

<a name="pKcpZ"></a>
#### 6.2.2 含参存储过程的创建与调用
- 构造含参存储过程的语法格式：
```sql
DELIMITER $$
CREATE PROCEDURE 存储过程名(参数名 数据类型)
BEGIN
    存储过程中的SQL语句;
END $$
DELIMITER ;

• 调用含参存储过程的语法格式：CALL 存储过程名(参数);
• 示例：创建一个存储过程，用于获取指定部门的平均薪资、最高薪资、最低薪资。 ```sql — 定义存储过程 DELIMITER $$ CREATE PROCEDURE get_sal(deptno INT) BEGIN SELECT

    ROUND(AVG(sal), 2) AS avg_sal,
    MAX(sal) AS max_sal,
    MIN(sal) AS min_sal

  FROM emp WHERE deptno = deptno; END $$ DELIMITER ;

— 调用存储过程，查询20号部门的工资信息 CALL get_sal(20);

<a name="HqsAY"></a>
#### 6.2.3 含返回值的存储过程的创建与调用
- 存储过程在结构上是没有`RETURN`这个返回值结构的，因此就需要用参数修饰符来表示数据的走向：
   - 对参数的修饰符为`IN`：表示这个数据是要输入的，就与传统意义上的参数类似。
   - 对参数的修饰符为`OUT`：表示这个数据是要输出的，就与传统意义上的返回值类似。
- 示例：创建一个存储过程，采用输入输出的方式，获取指定部门的平均薪资、最高薪资、最低薪资。
```sql
DELIMITER $$
-- deptno是要输入的，用IN修饰。
-- avg_sal、max_sal、min_sal是要输出的，用OUT修饰。
CREATE PROCEDURE get_sal1(
    IN deptno INT,
    OUT avg_sal DOUBLE,
    OUT max_sal DOUBLE,
    OUT min_sal DOUBLE
)
BEGIN
    SELECT
        ROUND(AVG(sal), 2) AS avg_sal, MAX(sal) AS max_sal, MIN(sal) AS min_sal
        INTO avg_sal, max_sal, min_sal
        -- INTO表示将获取的数据，传递给被OUT修饰的参数
        -- 通过被OUT修饰的参数把数据传递到存储过程外部调用的位置。
    FROM emp
    WHERE deptno = deptno;
END $$
DELIMITER ;

• 调用含返回值的存储过程，需要先定义变量来接收返回值数据，定义的语法格式为：SET @变量名 = 缺省值; ```sql — 调用存储过程get_sal1，查询20号部门的工资信息。 — 定义接收返回值数据的变量 SET @avg_sal = 0; SET @max_sal = 0; SET @min_sal = 0;

— 调用存储过程函数 CALL get_sal1(20, @avg_sal, @max_sal, @min_sal);

— 查询返回值的值，获取结果 SELECT @avg_sal, @max_sal, @min_sal;

<a name="KIPje"></a>
### 6.3 MySQL 5.x中的排名函数
- MySQL中对于排名的最好处理方式是使用窗口函数，但是窗口函数是MySQL 8.0开始新增的特性，在MySQL 5.x版本中无法使用。
- MySQL 5.x中按照薪资对员工信息进行排名的SQL语句如下：
   - `@ranking:=0`中的`:=`表示每次查询都会把变量`ranking`的值重置为0。
```sql
SELECT
    emp.*,
    @ranking := @ranking + 1 AS ranking
FROM emp
    JOIN (SELECT @ranking := 0) AS t
ORDER BY sal DESC;