337 lines
16 KiB
Markdown
337 lines
16 KiB
Markdown
|
## 视图、函数和过程
|
|||
|
|
|
|||
|
|
为了讲解视图、函数和过程，我们首先用下面的 DDL 和 DML 创建名为 hrs 的数据库并为其二维表添加如下所示的数据。
|
|||
|
|
|
|||
|
|
```SQL
|
|||
|
|
-- 创建名为hrs的数据库并指定默认的字符集
|
|||
|
|
create database `hrs` default charset utf8mb4;
|
|||
|
|
|
|||
|
|
-- 切换到hrs数据库
|
|||
|
|
use `hrs`;
|
|||
|
|
|
|||
|
|
-- 创建部门表
|
|||
|
|
create table `tb_dept`
|
|||
|
|
(
|
|||
|
|
`dno` int not null comment '编号',
|
|||
|
|
`dname` varchar(10) not null comment '名称',
|
|||
|
|
`dloc` varchar(20) not null comment '所在地',
|
|||
|
|
primary key (`dno`)
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- 插入4个部门
|
|||
|
|
insert into `tb_dept` values
|
|||
|
|
(10, '会计部', '北京'),
|
|||
|
|
(20, '研发部', '成都'),
|
|||
|
|
(30, '销售部', '重庆'),
|
|||
|
|
(40, '运维部', '深圳');
|
|||
|
|
|
|||
|
|
-- 创建员工表
|
|||
|
|
create table `tb_emp`
|
|||
|
|
(
|
|||
|
|
`eno` int not null comment '员工编号',
|
|||
|
|
`ename` varchar(20) not null comment '员工姓名',
|
|||
|
|
`job` varchar(20) not null comment '员工职位',
|
|||
|
|
`mgr` int comment '主管编号',
|
|||
|
|
`sal` int not null comment '员工月薪',
|
|||
|
|
`comm` int comment '每月补贴',
|
|||
|
|
`dno` int not null comment '所在部门编号',
|
|||
|
|
primary key (`eno`),
|
|||
|
|
constraint `fk_emp_mgr` foreign key (`mgr`) references tb_emp (`eno`),
|
|||
|
|
constraint `fk_emp_dno` foreign key (`dno`) references tb_dept (`dno`)
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- 插入14个员工
|
|||
|
|
insert into `tb_emp` values
|
|||
|
|
(7800, '张三丰', '总裁', null, 9000, 1200, 20),
|
|||
|
|
(2056, '乔峰', '分析师', 7800, 5000, 1500, 20),
|
|||
|
|
(3088, '李莫愁', '设计师', 2056, 3500, 800, 20),
|
|||
|
|
(3211, '张无忌', '程序员', 2056, 3200, null, 20),
|
|||
|
|
(3233, '丘处机', '程序员', 2056, 3400, null, 20),
|
|||
|
|
(3251, '张翠山', '程序员', 2056, 4000, null, 20),
|
|||
|
|
(5566, '宋远桥', '会计师', 7800, 4000, 1000, 10),
|
|||
|
|
(5234, '郭靖', '出纳', 5566, 2000, null, 10),
|
|||
|
|
(3344, '黄蓉', '销售主管', 7800, 3000, 800, 30),
|
|||
|
|
(1359, '胡一刀', '销售员', 3344, 1800, 200, 30),
|
|||
|
|
(4466, '苗人凤', '销售员', 3344, 2500, null, 30),
|
|||
|
|
(3244, '欧阳锋', '程序员', 3088, 3200, null, 20),
|
|||
|
|
(3577, '杨过', '会计', 5566, 2200, null, 10),
|
|||
|
|
(3588, '朱九真', '会计', 5566, 2500, null, 10);
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
### 视图
|
|||
|
|
|
|||
|
|
视图是关系型数据库中将一组查询指令构成的结果集组合成可查询的数据表的对象。简单的说，视图就是虚拟的表，但与数据表不同的是，数据表是一种实体结构，而视图是一种虚拟结构，你也可以将视图理解为保存在数据库中被赋予名字的 SQL 语句。
|
|||
|
|
|
|||
|
|
使用视图可以获得以下好处：
|
|||
|
|
|
|||
|
|
1. 可以将实体数据表隐藏起来，让外部程序无法得知实际的数据结构，让访问者可以使用表的组成部分而不是整个表，降低数据库被攻击的风险。
|
|||
|
|
2. 在大多数的情况下视图是只读的（更新视图的操作通常都有诸多的限制），外部程序无法直接透过视图修改数据。
|
|||
|
|
3. 重用 SQL 语句，将高度复杂的查询包装在视图表中，直接访问该视图即可取出需要的数据；也可以将视图视为数据表进行连接查询。
|
|||
|
|
4. 视图可以返回与实体数据表不同格式的数据，在创建视图的时候可以对数据进行格式化处理。
|
|||
|
|
|
|||
|
|
创建视图。
|
|||
|
|
|
|||
|
|
```SQL
|
|||
|
|
create view `vw_emp_simple`
|
|||
|
|
as
|
|||
|
|
select `eno`,
|
|||
|
|
`ename`,
|
|||
|
|
`job`,
|
|||
|
|
`dno`
|
|||
|
|
from `tb_emp`;
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
> **提示**：因为视图不包含数据，所以每次使用视图时，都必须执行查询以获得数据，如果你使用了连接查询、嵌套查询创建了较为复杂的视图，你可能会发现查询性能下降得很厉害。因此，在使用复杂的视图前，应该进行测试以确保其性能能够满足应用的需求。
|
|||
|
|
|
|||
|
|
有了上面的视图，我们就可以使用之前讲过的 DCL， 限制某些用户只能从视图中获取员工信息，这样员工表中的工资（`sal`）、补贴（`comm`）等敏感字段便不会暴露给用户。下面的代码演示了如何从视图中获取数据。
|
|||
|
|
|
|||
|
|
```SQL
|
|||
|
|
select * from `vw_emp_simple`;
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
查询结果：
|
|||
|
|
|
|||
|
|
```
|
|||
|
|
+------+-----------+--------------+-----+
|
|||
|
|
| eno | ename | job | dno |
|
|||
|
|
+------+-----------+--------------+-----+
|
|||
|
|
| 1359 | 胡二刀 | 销售员 | 30 |
|
|||
|
|
| 2056 | 乔峰 | 分析师 | 20 |
|
|||
|
|
| 3088 | 李莫愁 | 设计师 | 20 |
|
|||
|
|
| 3211 | 张无忌 | 程序员 | 20 |
|
|||
|
|
| 3233 | 丘处机 | 程序员 | 20 |
|
|||
|
|
| 3244 | 欧阳锋 | 程序员 | 20 |
|
|||
|
|
| 3251 | 张翠山 | 程序员 | 20 |
|
|||
|
|
| 3344 | 黄蓉 | 销售主管 | 30 |
|
|||
|
|
| 3577 | 杨过 | 会计 | 10 |
|
|||
|
|
| 3588 | 朱九真 | 会计 | 10 |
|
|||
|
|
| 4466 | 苗人凤 | 销售员 | 30 |
|
|||
|
|
| 5234 | 郭靖 | 出纳 | 10 |
|
|||
|
|
| 5566 | 宋远桥 | 会计师 | 10 |
|
|||
|
|
| 7800 | 张三丰 | 总裁 | 20 |
|
|||
|
|
+------+-----------+--------------+-----+
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
既然视图是一张虚拟的表，那么视图的中的数据可以更新吗？视图的可更新性要视具体情况而定，以下类型的视图是不能更新的：
|
|||
|
|
|
|||
|
|
1. 使用了聚合函数（`SUM`、`MIN`、`MAX`、`AVG`、`COUNT`等）、`DISTINCT`、`GROUP BY`、`HAVING`、`UNION`或者`UNION ALL`的视图。
|
|||
|
|
2. `SELECT`中包含了子查询的视图。
|
|||
|
|
3. `FROM`子句中包含了一个不能更新的视图的视图。
|
|||
|
|
4. `WHERE`子句的子查询引用了`FROM`子句中的表的视图。
|
|||
|
|
|
|||
|
|
删除视图。
|
|||
|
|
|
|||
|
|
```SQL
|
|||
|
|
drop view if exists `vw_emp_simple`;
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
> **说明**：如果希望更新视图，可以先用上面的命令删除视图，也可以通过`create or replace view`来更新视图。
|
|||
|
|
|
|||
|
|
视图的规则和限制。
|
|||
|
|
|
|||
|
|
1. 视图可以嵌套，可以利用从其他视图中检索的数据来构造一个新的视图。视图也可以和表一起使用。
|
|||
|
|
2. 创建视图时可以使用`order by`子句，但如果从视图中检索数据时也使用了`order by`，那么该视图中原先的`order by`会被覆盖。
|
|||
|
|
3. 视图无法使用索引，也不会激发触发器（实际开发中因为性能等各方面的考虑，通常不建议使用触发器，所以我们也不对这个概念进行介绍）的执行。
|
|||
|
|
|
|||
|
|
### 函数
|
|||
|
|
|
|||
|
|
MySQL 中的函数跟 Python 中的函数大同小异，因为函数都是用来封装功能上相对独立且会被重复使用的代码的。如果非要找出一些差别来，那么 MySQL 中的函数是可以执行 SQL 语句的。下面的例子，我们通过自定义函数实现了截断超长字符串的功能。
|
|||
|
|
|
|||
|
|
```SQL
|
|||
|
|
delimiter $$
|
|||
|
|
|
|||
|
|
create function fn_truncate_string(
|
|||
|
|
content varchar(10000),
|
|||
|
|
max_length int unsigned
|
|||
|
|
) returns varchar(10000) no sql
|
|||
|
|
begin
|
|||
|
|
declare result varchar(10000) default content;
|
|||
|
|
if char_length(content) > max_length then
|
|||
|
|
set result = left(content, max_length);
|
|||
|
|
set result = concat(result, '……');
|
|||
|
|
end if;
|
|||
|
|
return result;
|
|||
|
|
end $$
|
|||
|
|
|
|||
|
|
delimiter ;
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
> **说明1**：函数声明后面的`no sql`是声明函数体并没有使用 SQL 语句；如果函数体中需要通过 SQL 读取数据，需要声明为`reads sql data`。
|
|||
|
|
>
|
|||
|
|
> **说明2**：定义函数前后的`delimiter`命令是为了修改终止符（定界符），因为函数体中的语句都是用`;`表示结束，如果不重新定义定界符，那么遇到的`;`的时候代码就会被截断执行，显然这不是我们想要的效果。
|
|||
|
|
|
|||
|
|
在查询中调用自定义函数。
|
|||
|
|
|
|||
|
|
```SQL
|
|||
|
|
select fn_truncate_string('和我在成都的街头走一走，直到所有的灯都熄灭了也不停留', 10) as short_string;
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
```
|
|||
|
|
+--------------------------------------+
|
|||
|
|
| short_string |
|
|||
|
|
+--------------------------------------+
|
|||
|
|
| 和我在成都的街头走一…… |
|
|||
|
|
+--------------------------------------+
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
### 过程
|
|||
|
|
|
|||
|
|
过程（又称存储过程）是事先编译好存储在数据库中的一组 SQL 的集合，调用过程可以简化应用程序开发人员的工作，减少与数据库服务器之间的通信，对于提升数据操作的性能也是有帮助的。其实迄今为止，我们使用的 SQL 语句都是针对一个或多个表的单条语句，但在实际开发中经常会遇到某个操作需要多条 SQL 语句才能完成的情况。例如，电商网站在受理用户订单时，需要做以下一系列的处理。
|
|||
|
|
|
|||
|
|
1. 通过查询来核对库存中是否有对应的物品以及库存是否充足。
|
|||
|
|
2. 如果库存有物品，需要锁定库存以确保这些物品不再卖给别人， 并且要减少可用的物品数量以反映正确的库存量。
|
|||
|
|
3. 如果库存不足，可能需要进一步与供应商进行交互或者至少产生一条系统提示消息。
|
|||
|
|
4. 不管受理订单是否成功，都需要产生流水记录，而且需要给对应的用户产生一条通知信息。
|
|||
|
|
|
|||
|
|
我们可以通过过程将复杂的操作封装起来，这样不仅有助于保证数据的一致性，而且将来如果业务发生了变动，只需要调整和修改过程即可。对于调用过程的用户来说，过程并没有暴露数据表的细节，而且执行过程比一条条的执行一组 SQL 要快得多。
|
|||
|
|
|
|||
|
|
下面的过程实现 hrs 数据库中员工工资的普调，具体的规则是：`10`部门的员工薪资上浮`300`， `20`部门的员工薪资上浮`800`，`30`部门的员工薪资上浮`500`。
|
|||
|
|
|
|||
|
|
```SQL
|
|||
|
|
delimiter $$
|
|||
|
|
|
|||
|
|
create procedure sp_upgrade_salary()
|
|||
|
|
begin
|
|||
|
|
declare flag boolean default 1;
|
|||
|
|
-- 定义一个异常处理器
|
|||
|
|
declare continue handler for sqlexception set flag=0;
|
|||
|
|
|
|||
|
|
-- 开启事务环境
|
|||
|
|
start transaction;
|
|||
|
|
|
|||
|
|
update tb_emp set sal=sal+300 where dno=10;
|
|||
|
|
update tb_emp set sal=sal+800 where dno=20;
|
|||
|
|
update tb_emp set sal=sal+500 where dno=30;
|
|||
|
|
|
|||
|
|
-- 提交或回滚事务
|
|||
|
|
if flag then
|
|||
|
|
commit;
|
|||
|
|
else
|
|||
|
|
rollback;
|
|||
|
|
end if;
|
|||
|
|
end $$
|
|||
|
|
|
|||
|
|
delimiter ;
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
> **说明**：上面的过程代码中使用了`start transaction`来开启事务环境，关于事务，在本课的最后有一个简单的介绍。为了确定代码中是否发生异常，从而提交或回滚事务，上面的过程中定义了一个名为`flag`的变量和一个异常处理器，如果发生了异常，`flag`将会被赋值为`0`，后面的分支结构会根据`flag`的值来决定是执行`commit`，还是执行`rollback`。
|
|||
|
|
|
|||
|
|
调用过程。
|
|||
|
|
|
|||
|
|
```SQL
|
|||
|
|
call sp_upgrade_salary();
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
删除过程。
|
|||
|
|
|
|||
|
|
```SQL
|
|||
|
|
drop procedure if exists sp_upgrade_salary;
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
在过程中，我们可以定义变量、条件，可以使用分支和循环语句，可以通过游标操作查询结果，还可以使用事件调度器，这些内容我们暂时不在此处进行介绍。虽然我们说了很多过程的好处，但是在实际开发中，如果频繁的使用过程并将大量复杂的运算放到过程中，会给据库服务器造成巨大的压力，而数据库往往都是性能瓶颈所在，使用过程无疑是雪上加霜的操作。所以，对于互联网产品开发，我们一般建议让数据库只做好存储，复杂的运算和处理交给应用服务器上的程序去完成，如果应用服务器变得不堪重负了，我们可以比较容易的部署多台应用服务器来分摊这些压力。
|
|||
|
|
|
|||
|
|
如果大家对上面讲到的视图、函数、过程包括我们没有讲到的触发器这些知识有兴趣，建议大家阅读 MySQL 的入门读物[《MySQL必知必会》](https://item.jd.com/12818982.html)进行一般性了解即可，因为这些知识点在大家将来的工作中未必用得上，学了也可能仅仅是为了应付面试而已。
|
|||
|
|
|
|||
|
|
### 其他内容
|
|||
|
|
|
|||
|
|
#### 范式理论
|
|||
|
|
|
|||
|
|
范式理论是设计关系型数据库中二维表的指导思想。
|
|||
|
|
|
|||
|
|
1. 第一范式：数据表的每个列的值域都是由原子值组成的，不能够再分割。
|
|||
|
|
2. 第二范式：数据表里的所有数据都要和该数据表的键（主键与候选键）有完全依赖关系。
|
|||
|
|
3. 第三范式：所有非键属性都只和候选键有相关性，也就是说非键属性之间应该是独立无关的。
|
|||
|
|
|
|||
|
|
> **说明**：实际工作中，出于效率的考虑，我们在设计表时很有可能做出反范式设计，即故意降低方式级别，增加冗余数据来获得更好的操作性能。
|
|||
|
|
|
|||
|
|
#### 数据完整性
|
|||
|
|
|
|||
|
|
1. 实体完整性 - 每个实体都是独一无二的
|
|||
|
|
|
|||
|
|
- 主键（`primary key`） / 唯一约束（`unique`）
|
|||
|
|
2. 引用完整性（参照完整性）- 关系中不允许引用不存在的实体
|
|||
|
|
|
|||
|
|
- 外键（`foreign key`）
|
|||
|
|
3. 域（domain）完整性 - 数据是有效的
|
|||
|
|
- 数据类型及长度
|
|||
|
|
|
|||
|
|
- 非空约束（`not null`）
|
|||
|
|
|
|||
|
|
- 默认值约束（`default`）
|
|||
|
|
|
|||
|
|
- 检查约束（`check`）
|
|||
|
|
|
|||
|
|
> **说明**：在 MySQL 8.x 以前，检查约束并不起作用。
|
|||
|
|
|
|||
|
|
#### 数据一致性
|
|||
|
|
|
|||
|
|
1. 事务：一系列对数据库进行读/写的操作，这些操作要么全都成功，要么全都失败。
|
|||
|
|
|
|||
|
|
2. 事务的 ACID 特性
|
|||
|
|
- 原子性：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行
|
|||
|
|
- 一致性：事务应确保数据库的状态从一个一致状态转变为另一个一致状态
|
|||
|
|
- 隔离性：多个事务并发执行时，一个事务的执行不应影响其他事务的执行
|
|||
|
|
- 持久性：已被提交的事务对数据库的修改应该永久保存在数据库中
|
|||
|
|
|
|||
|
|
3. MySQL 中的事务操作
|
|||
|
|
|
|||
|
|
- 开启事务环境
|
|||
|
|
|
|||
|
|
```SQL
|
|||
|
|
start transaction
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
- 提交事务
|
|||
|
|
|
|||
|
|
```SQL
|
|||
|
|
commit
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
- 回滚事务
|
|||
|
|
|
|||
|
|
```SQL
|
|||
|
|
rollback
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
4. 查看事务隔离级别
|
|||
|
|
|
|||
|
|
```SQL
|
|||
|
|
show variables like 'transaction_isolation';
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
```
|
|||
|
|
+-----------------------+-----------------+
|
|||
|
|
| Variable_name | Value |
|
|||
|
|
+-----------------------+-----------------+
|
|||
|
|
| transaction_isolation | REPEATABLE-READ |
|
|||
|
|
+-----------------------+-----------------+
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
可以看出，MySQL 默认的事务隔离级别是`REPEATABLE-READ`。
|
|||
|
|
|
|||
|
|
5. 修改（当前会话）事务隔离级别
|
|||
|
|
|
|||
|
|
```SQL
|
|||
|
|
set session transaction isolation level read committed;
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
重新查看事务隔离级别，结果如下所示。
|
|||
|
|
|
|||
|
|
```
|
|||
|
|
+-----------------------+----------------+
|
|||
|
|
| Variable_name | Value |
|
|||
|
|
+-----------------------+----------------+
|
|||
|
|
| transaction_isolation | READ-COMMITTED |
|
|||
|
|
+-----------------------+----------------+
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
关系型数据库的事务是一个很大的话题，因为当存在多个并发事务访问数据时，就有可能出现三类读数据的问题（脏读、不可重复读、幻读）和两类更新数据的问题（第一类丢失更新、第二类丢失更新）。想了解这五类问题的，可以阅读我发布在 CSDN 网站上的[《Java面试题全集（上）》](https://blog.csdn.net/jackfrued/article/details/44921941)一文的第80题。为了避免这些问题，关系型数据库底层是有对应的锁机制的，按锁定对象不同可以分为表级锁和行级锁，按并发事务锁定关系可以分为共享锁和独占锁。然而直接使用锁是非常麻烦的，为此数据库为用户提供了自动锁机制，只要用户指定适当的事务隔离级别，数据库就会通过分析 SQL 语句，然后为事务访问的资源加上合适的锁。此外，数据库还会维护这些锁通过各种手段提高系统的性能，这些对用户来说都是透明的。想了解 MySQL 事务和锁的细节知识，推荐大家阅读进阶读物[《高性能MySQL》](https://item.jd.com/11220393.html)，这也是数据库方面的经典书籍。
|
|||
|
|
|
|||
|
|
ANSI/ISO SQL 92标准定义了4个等级的事务隔离级别，如下表所示。需要说明的是，事务隔离级别和数据访问的并发性是对立的，事务隔离级别越高并发性就越差。所以要根据具体的应用来确定到底使用哪种事务隔离级别，这个地方没有万能的原则。
|
|||
|
|
|
|||
|
|
<img src="http://localhost/mypic/20211121225327.png" style="zoom:50%;">
|
|||
|
|
|
|||
|
|
### 总结
|
|||
|
|
|
|||
|
|
关于 MySQL 的知识肯定远远不止上面列出的这些，比如 MySQL 性能调优、MySQL 运维相关工具、MySQL 数据的备份和恢复、监控 MySQL 服务、部署高可用架构等，这一系列的问题在这里都没有办法逐一展开来讨论，那就留到有需要的时候再进行讲解吧，各位读者也可以自行探索。
|