一，视图
什么是视图
视图作为一张虚拟表，它相当于是一张表或多张表的数据结果集。
视图的优点
可以帮我们简化复杂的 SQL 查询，比如在编写视图后，我们就可以直接把它当作一张表来重用它。
创建视图：CREATE VIEW
CREATE VIEW player_above_avg_height AS
SELECT player_id, height
FROM player
WHERE height > (SELECT AVG(height) from player)
嵌套视图
CREATE VIEW player_above_above_avg_height AS
SELECT player_id, height
FROM player
WHERE height > (SELECT AVG(height) from player_above_avg_height)
修改视图：ALTER VIEW
ALTER VIEW player_above_avg_height AS
SELECT player_id, player_name, height
FROM player
WHERE height > (SELECT AVG(height) from player)
删除视图：DROP VIEW
DROP VIEW player_above_avg_height
二，存储过程
什么是存储过程
存储过程和视图一样，都是对 SQL 代码进行封装，可以反复利用。它和视图有着同样的优点，清晰、安全，还可以减少网络传输量。
与视图的不同点
不过它和视图不同，视图是虚拟表，通常不对底层数据表直接操作，而存储过程是程序化的 SQL，可以直接操作底层数据表，能够实现一些更复杂的数据处理。存储过程可以说是由 SQL 语句和流控制语句构成的语句集合，它和函数一样，可以接收输入参数，也可以返回输出参数给调用者，返回计算结果。
如何定义一个存储过程
CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
需要执行的语句
END
例如下面这个定义了一个简单的存储过程来做一个累加运算，计算 1+2+…+n 等于多少
CREATE PROCEDURE `add_num`(IN n INT)
BEGIN
DECLARE i INT;
DECLARE sum INT;
SET i = 1;
SET sum = 0;
WHILE i <= n DO
SET sum = sum + i;
SET i = i +1;
END WHILE;
SELECT sum;
END
当我们需要再次使用这个存储过程的时候，直接使用 CALL add_num(50);
如果使用 Navicat 这个工具来管理 MySQL 执行存储过程，那么直接执行上面这段代码就可以了。如果用的是 MySQL，还需要用 DELIMITER 来临时定义新的结束符。因为默认情况下 SQL 采用（；）作为结束符，这样当存储过程中的每一句 SQL 结束之后，采用（；）作为结束符，就相当于告诉 SQL 可以执行这一句了。但是存储过程是一个整体， SQL 得是采用存储过程整段执行的方式，因此就需要临时定义新的 DELIMITER，新的结束符可以用（//）或者（$$）。所以在 MySQL中，那么上面这段代码，应该写成下面这样：
DELIMITER //
CREATE PROCEDURE `add_num`(IN n INT)
BEGIN
DECLARE i INT;
DECLARE sum INT;
SET i = 1;
SET sum = 0;
WHILE i <= n DO
SET sum = sum + i;
SET i = i +1;
END WHILE;
SELECT sum;
END //
DELIMITER ;
DELIMITER 是定义分隔符的意思，首先用（//）作为结束符，又在整个存储过程结束后采用了（//）作为结束符号，告诉 SQL 可以执行了，然后再将结束符还原成默认的（;）。需要注意的是，如果用的是 Navicat 工具，那么在编写存储过程的时候，Navicat 会自动设置 DELIMITER 为其他符号，不需要再进行 DELIMITER 的操作。
存储过程的 3 种参数类型
在刚才的存储过程中，使用了 IN 类型的参数，另外还有 OUT 类型和 INOUT 类型，作用如下：
举个例子：
CREATE PROCEDURE `get_hero_scores`(
OUT max_max_hp FLOAT,
OUT min_max_mp FLOAT,
OUT avg_max_attack FLOAT,
s VARCHAR(255)
)
BEGIN
SELECT MAX(hp_max), MIN(mp_max), AVG(attack_max) FROM heros WHERE role_main = s INTO max_max_hp, min_max_mp, avg_max_attack;
END
定义了 4 个参数类型，其中 3 个为 OUT 类型，分别为 max_max_hp、min_max_mp 和 avg_max_attack，另一个参数 s 为 IN 类型。
然后就可以调用存储过程，使用下面这段代码即可：
CALL get_hero_scores(@max_max_hp, @min_max_mp, @avg_max_attack, 'XXX');
SELECT @max_max_hp, @min_max_mp, @avg_max_attack;
流控制语句
流控制语句是用来做流程控制的，上面的例子用到了下面的流控制语句：
1，BEGIN…END：BEGIN…END 中间包含了多个语句，每个语句都以（;）号为结束符。
2，DECLARE：DECLARE 用来声明变量，使用的位置在于 BEGIN…END 语句中间，而且需要在其他语句使用之前进行变量的声明。
3，SET：赋值语句，用于对变量进行赋值。
4，SELECT…INTO：把从数据表中查询的结果存放到变量中，也就是为变量赋值。
除了上面这些用到的流控制语句以外，还有一些常用的流控制语句：
1.IF…THEN…ENDIF：条件判断语句，我们还可以在 IF…THEN…ENDIF 中使用 ELSE 和 ELSEIF 来进行条件判断。
2.CASE：CASE 语句用于多条件的分支判断，使用的语法是下面这样的
CASE
WHEN expression1 THEN ...
WHEN expression2 THEN ...
...
ELSE
--ELSE语句可以加，也可以不加。加的话代表的所有条件都不满足时采用的方式。
END
3.LOOP、LEAVE 和 ITERATE：LOOP 是循环语句，使用 LEAVE 可以跳出循环，使用 ITERATE 则可以进入下一次循环。如果你有面向过程的编程语言的使用经验，你可以把 LEAVE 理解为 BREAK，把 ITERATE 理解为 CONTINUE。
4.REPEAT…UNTIL…END REPEAT：这是一个循环语句，首先会执行一次循环，然后在 UNTIL 中进行表达式的判断，如果满足条件就退出，即 END REPEAT；如果条件不满足，则会就继续执行循环，直到满足退出条件为止。
5.WHILE…DO…END WHILE：这也是循环语句，和 REPEAT 循环不同的是，这个语句需要先进行条件判断，如果满足条件就进行循环，如果不满足条件就退出循环。
SQL 是声明型语言，使用 SQL 就像在使用英语，简单直接。而存储过程，尤其是在存储过程中使用到的流控制语句，属于过程性语言，类似于 C++ 语言中函数，这些语句可以帮我们解决复杂的业务逻辑。
存储过程优缺点
优点：
1，可以一次编译多次使用，存储过程只在创造时进行编译，之后的使用都不需要重新编译，这就提升了 SQL 的执行效率。
2，复用减少开发工作量
3，可设置对用户的使用权限，这样就和视图一样具有较强的安全性。
4，可以减少网络传输量，因为代码封装到存储过程中，每次使用只需要调用存储过程即可。
缺点：
1，可移植性差，存储过程不能跨数据库移植，比如在 MySQL、Oracle 和 SQL Server 里编写的存储过程，在换成其他数据库时都需要重新编写。
2，调试困难，只有少数 DBMS 支持存储过程的调试。对于复杂的存储过程来说，开发和维护都不容易。
3，存储过程的版本管理也很困难，比如数据表索引发生变化了，可能会导致存储过程失效。存储过程本身没有版本控制，版本迭代更新的时候很麻烦。
4，不适合高并发的场景，高并发的场景需要减少数据库的压力，有时数据库会采用分库分表的方式，而且对可扩展性要求很高，在这种情况下，存储过程会变得难以维护，增加数据库的压力，显然就不适用了。
网友评论