1 视图

1.1 视图的作用

当我们创建一张表的视图后，可以用和表差不多的使用方式来使用视图，比如可以对视图进行select查询操作、过滤或者排序数据等等。同时，也可以联结其它视图或者表，甚至可以添加和更新数据（但一般不会这么做，而且存在诸多限制）。总结起来，视图有以下优点：

• 重用SQL语句，简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道它的基本查询细节。
• 使用表的组成部分而不是整个表，做到保护数据，给用户授予表的特定部分的访问权限而不是整个表的访问权限。
• 更改数据格式和表示，视图可返回与底层表的表示和格式不同的数据。

但是，视图也存在一些缺点，因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时所需的任一个检索。如果你用多个联结和过滤创建了复杂的视图或者嵌套了视图，可能会发现性能下降得很厉害。因此，在部署使用了大量视图的应用前，应该进行测试。

1.2 视图的简单应用

创建视图

# 查询姓名中包含a字符的员工名、部门名和工种信息
# 创建视图
CREATE VIEW view1
AS
SELECT last_name, department_name, job_title
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN jobs j ON j.job_id  = e.job_id;
# 使用视图
SELECT * FROM view1 WHERE last_name LIKE '%a%';

修改视图

• 方式一：create or replace view 视图名 as 查询语句;
• 方式二：alter view 视图名 as 查询语句;

CREATE OR REPLACE VIEW view2
AS
SELECT AVG(salary),job_id FROM employees GROUP BY job_id;

ALTER VIEW view2 AS SELECT * FROM employees;

删除视图

DROP VIEW view1, view2;

正常的视图更新同表的更新是一样的，但具备以下特点的视图不允许更新：

• 分组（使用GROUP BY和HAVING）；联结
• 子查询
• 并
• 聚集函数（Min()、Count()、Sum()等）
• DISTINCT
• 导出（计算）列。

2 存储过程

2.1 存储过程的作用

在大多数实际场景中，操作并非都是使用一条SQL语句就可以完成的了。一个操作可能会涉及到连续执行三四条SQL语句以上，例如：

1. 为了处理订单，需要核对以保证库存中有相应的物品。
2. 如果库存有物品，这些物品需要预定以便不将它们再卖给别的人，并且要减少可用的物品数量以反映正确的库存量。
3. 库存中没有的物品需要订购，这需要与供应商进行某种交互。
4. 关于哪些物品入库（并且可以立即发货）和哪些物品退订，需要通知相应的客户。

对于这种场景，就可以使用存储过程进行优化了。存储过程的英文是Stored Procedure。它的思想很简单，就是一组经过预先编译的SQL语句的封装。存储过程预先存储在MySQL服务器上，需要执行的时候，客户端只需要向服务器端发出调用存储过程的命令，服务器端就可以把预先存储好的这一系列SQL语句全部执行。

• 提高了sql语句的重用性
• 减少操作过程中的失误，提高效率
• 减少网络传输量（客户端不需要把所有的SQL语句通过网络发给服务器）
• 减少了SQL语句暴露在网上的风险，也提高了数据查询的安全性

值得一提的是，存储过程是不具有事务性的。要保证一个存储过程中的全部SQL语句具有事务性，可以在存储过程中开启事务。

2.2 存储过程的简单使用

• in：该参数可以作为输入，也就是该参数需要调用方传入值
• out：该参数可以作为输出，也就是该参数可以作为返回值
• inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值

# 1.空参列表
# 案例：插入到admin表中五条记录
SELECT * FROM admin;
DELIMITER $	# 存储过程的结尾可以使用 delimiter 重新设置
CREATE PROCEDURE myp1()
BEGIN
	INSERT INTO admin(username,`password`) 
	VALUES('john1','0000'),('lily','0000'),('rose','0000'),('jack','0000'),('tom','0000');
END $
#调用
CALL myp1()$

# 2.创建带in模式参数的存储过程
# 案例1：创建存储过程实现 根据女神名，查询对应的男神信息
CREATE PROCEDURE myp1(IN beautyName VARCHAR(20))
BEGIN
	SELECT bo.*
	FROM boys bo
	RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
	WHERE b.name=beautyName;
END $
#调用
CALL myp2('柳岩')$

# 3.创建out 模式参数的存储过程
# 案例1：根据输入的女神名，返回对应的男神名
CREATE PROCEDURE myp2(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
	SELECT bo.boyname INTO boyname
	FROM boys bo
	RIGHT JOIN
	beauty b ON b.boyfriend_id = bo.id
	WHERE b.name=beautyName ;
END $

# 4.创建带inout模式参数的存储过程
# 案例1：传入a和b两个值，最终a和b都翻倍并返回
CREATE PROCEDURE myp3(INOUT a INT ,INOUT b INT)
BEGIN
	SET a=a*2;
	SET b=b*2;
END $
# 调用
SET @m=10$
SET @n=20$
CALL myp8(@m,@n)$
SELECT @m,@n$

删除和查看存储过程

DROP PROCEDURE myp1;
SHOW CREATE PROCEDURE myp2;

3 函数

3.1 函数的作用

SQL自带许多封装好的函数，使用函数可以实现对数据的各种处理操作。MySQL支持自定义函数，定义好之后，调用方式与调用MySQL预定义的系统函数一样。

3.2 函数的简单使用

定义函数的形式：

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
	函数体
END 

• 参数列表 包含两部分：参数名 参数类型
• 函数体：肯定会有return语句，没有会报错。return语句没有放在函数体的最后也不报错，但不建议
• 函数体中仅有一句话，则可以省略begin end
• 使用delimiter语句设置结束标记

# 1.无参有返回 案例：返回公司的员工个数
DELIMITER $	# 存储过程的结尾可以使用 delimiter 重新设置
CREATE FUNCTION myf1() RETURNS INT
BEGIN
    DECLARE c INT DEFAULT 0;#定义局部变量
    SELECT COUNT(*) INTO c#赋值
    FROM employees;
    RETURN c;
END $
SELECT myf1()$
# 2.有参有返回 案例1：根据员工名，返回它的工资
CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	SET @sal=0;#定义用户变量 
	SELECT salary INTO @sal   #赋值
	FROM employees
	WHERE last_name = empName;
RETURN @sal;
END $
SELECT myf2('k_ing') $

删除和查看函数

SHOW CREATE FUNCTION myf3;
DROP FUNCTION myf3;

4 总结

无论是定义和使用方式，存储过程和函数是比较类似的。

它们和视图有着同样的优点，清晰、安全，还可以减少网络传输量。不过不同的是，视图是虚拟表 ，通常不对底层数据表直接操作，而存储过程是程序化的SQL，可以直接操作底层数据表 ，相比于面向集合的操作方式，能够实现一些更复杂的数据处理。一旦存储过程被创建出来，使用它就像使用函数一样简单，我们直接通过调用存储过程名即可。然而，相较于函数，存储过程是没有返回值的。