MySQL进阶
视图
视图是关系型数据库中将一组查询指令构成的结果集组合成可查询的数据表的对象。简单的说，视图就是虚拟的表，但与数据表不同的是，数据表是一种实体结构，而视图是一种虚拟结构，你也可以将视图理解为保存在数据库中被赋予名字的 SQL 语句。
使用视图可以获得以下好处：
- 可以将实体数据表隐藏起来，让外部程序无法得知实际的数据结构，让访问者可以使用表的组成部分而不是整个表，降低数据库被攻击的风险。
- 在大多数的情况下视图是只读的（更新视图的操作通常都有诸多的限制），外部程序无法直接透过视图修改数据。
- 重用 SQL 语句，将高度复杂的查询包装在视图表中，直接访问该视图即可取出需要的数据；也可以将视图视为数据表进行连接查询。
- 视图可以返回与实体数据表不同格式的数据，在创建视图的时候可以对数据进行格式化处理。
创建视图
-- 创建视图
create view `vw_avg_score`
as
select `stu_id`, round(avg(`score`), 1) as `avg_score`
from `tb_record` group by `stu_id`;
-- 基于已有的视图创建视图
create view `vw_student_score`
as
select `stu_name`, `avg_score`
from `tb_student` natural join `vw_avg_score`;
因为视图不包含数据，所以每次使用视图时，都必须执行查询以获得数据，如果你使用了连接查询、嵌套查询创建了较为复杂的视图，你可能会发现查询性能下降得很厉害。因此，在使用复杂的视图前，应该进行测试以确保其性能能够满足应用的需求。
使用视图
select * from `vw_student_score` order by `avg_score` desc;
+————–+———-+ | stuname | avgscore | +————–+———-+ | 杨过 | 95.6 | | 任我行 | 53.5 | | 王语嫣 | 84.3 | | 纪嫣然 | 73.8 | | 岳不群 | 78.0 | | 东方不败 | 88.0 | | 项少龙 | 92.0 | +————–+———-+
既然视图是一张虚拟的表，那么视图的中的数据可以更新吗？视图的可更新性要视具体情况而定，以下类型的视图是不能更新的：
- 使用了聚合函数（
SUM、MIN、MAX、AVG、COUNT等）、DISTINCT、GROUP BY、HAVING、UNION或者UNION ALL的视图。 SELECT中包含了子查询的视图。FROM子句中包含了一个不能更新的视图的视图。WHERE子句的子查询引用了FROM子句中的表的视图。
删除视图
drop view vw_student_score;
如果希望更新视图，可以先用上面的命令删除视图，也可以通过 create or replace view 来更新视图。
视图的规则和限制：
- 视图可以嵌套，可以利用从其他视图中检索的数据来构造一个新的视图。视图也可以和表一起使用。
- 创建视图时可以使用
order by子句，但如果从视图中检索数据时也使用了order by，那么该视图中原先的order by会被覆盖。 - 视图无法使用索引，也不会激发触发器（实际开发中因为性能等各方面的考虑，通常不建议使用触发器）的执行。
存储过程
过程（又称存储过程）是事先编译好存储在数据库中的一组 SQL 的集合，调用过程可以简化应用程序开发人员的工作，减少与数据库服务器之间的通信，对于提升数据操作的性能也是有帮助的。其实迄今为止，我们使用的 SQL 语句都是针对一个或多个表的单条语句，但在实际开发中经常会遇到某个操作需要多条 SQL 语句才能完成的情况。
例如，电商网站在受理用户订单时，需要做以下一系列的处理。
- 通过查询来核对库存中是否有对应的物品以及库存是否充足。
- 如果库存有物品，需要锁定库存以确保这些物品不再卖给别人， 并且要减少可用的物品数量以反映正确的库存量。
- 如果库存不足，可能需要进一步与供应商进行交互或者至少产生一条系统提示消息。
- 不管受理订单是否成功，都需要产生流水记录，而且需要给对应的用户产生一条通知信息。
我们可以通过过程将复杂的操作封装起来，这样不仅有助于保证数据的一致性，而且将来如果业务发生了变动，只需要调整和修改过程即可。对于调用过程的用户来说，过程并没有暴露数据表的细节，而且执行过程比一条条的执行一组 SQL 要快得多。
示例：用存储过程实现了查询某门课程的最高分、最低分和平均分。
drop procedure if exists sp_score_stat;
delimiter $$
create procedure sp_score_stat(
courseId int,
out maxScore decimal(4,1),
out minScore decimal(4,1),
out avgScore decimal(4,1)
)
begin
select max(score) into maxScore from tb_record where cou_id=courseId;
select min(score) into minScore from tb_record where cou_id=courseId;
select avg(score) into avgScore from tb_record where cou_id=courseId;
end $$
delimiter ;
上面定义的过程有四个参数，其中第一个参数是输入参数，代表课程的编号，后面的参数都是输出参数，因为过程不能定义返回值，只能通过输出参数将执行结果带出，定义输出参数的关键字是 out，默认情况下参数都是输入参数。
调用过程。
1
call sp_score_stat(1111, @a, @b, @c);
获取输出参数的值。
1
select @a as 最高分, @b as 最低分, @c as 平均分;
mysql> call sp_score_stat(1111, @a, @b, @c);
Query OK, 1 row affected (0.00 sec)
mysql> select @a as 最高分, @b as 最低分, @c as 平均分;
+——–+——–+——–+
| 最高分 | 最低分 | 平均分 |
+——–+——–+——–+
| 95.0 | 65.0 | 82.5 |
+——–+——–+——–+
1 row in set (0.01 sec)
删除过程。
1
drop procedure sp_score_stat;
在过程中，我们可以定义变量、条件，可以使用分支和循环语句，可以通过游标操作查询结果，还可以使用事件调度器。
在实际开发中，如果频繁的使用过程并将大量复杂的运算放到过程中，会给据库服务器造成巨大的压力，而数据库往往都是性能瓶颈所在，使用过程无疑是雪上加霜的操作。所以，对于互联网产品开发，我们一般建议让数据库只做好存储，复杂的运算和处理交给应用服务器上的程序去完成，如果应用服务器变得不堪重负了，我们可以比较容易的部署多台应用服务器来分摊这些压力。
其他
关于 SQL 和 MySQL 的知识肯定远远不止上面列出的这些，比如 SQL 本身的优化、MySQL 性能调优、MySQL 运维相关工具、MySQL 数据的备份和恢复、监控 MySQL 服务、部署高可用架构等。