MySQL相关知识（视图、存储过程和函数、触发器）

• 视图

• 概念

1. - 视图是一种虚拟存在的数据表
2. - 这个虚拟的表并不在数据库中实际存在
3. - 作用是将一些比较复杂的查询语句的结果，封装到一个虚拟表中。后期再有相同复杂查询时，直接查询这张虚拟表即可
4. - 说白了，视图就是将一条SELECT查询语句的结果封装到了一个虚拟表中，所以我们在创建视图的时候，工作重心就要放在这条SELECT查询语句上 

•  视图的优点

1. 简单：对于使用视图的用户不需要关心表的结构、关联条件和筛选条件。因为这张虚拟表中保存的就是已经过滤好条件的结果集

2. 安全：视图可以设置权限 , 致使访问视图的用户只能访问他们被允许查询的结果集

3. 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响

• 视图操作 

• 创建视图语法

CREATE VIEW 视图名称 [(列名列表)] AS 查询语句; 

• 查询视图语法

SELECT * FROM 视图名称; 

• 查询视图创建语法

SHOW CREATE VIEW 视图名称; 

• 修改视图表中的数据

UPDATE 视图名称 SET 列名=值 WHERE 条件; 

• 修改视图表结构

ALTER VIEW 视图名称 [(列名列表)] AS 查询语句; 

• 视图的删除

DROP VIEW [IF EXISTS] 视图名称;

•  注意：对视图中的数据进行修改是对源表的数据进行修改

---

 

• 存储过程和函数

• 存储过程和函数的概念

  • 存储过程和函数是 事先经过编译并存储在数据库中的一段 SQL 语句的集合

• 存储过程和函数的好处

1. 存储过程和函数可以重复使用，减轻开发人员的工作量。类似于java中方法可以多次调用

2. 减少网络流量，存储过程和函数位于服务器上，调用的时候只需要传递名称和参数即可

3. 减少数据在数据库和应用服务器之间的传输，可以提高数据处理的效率

4. 将一些业务逻辑在数据库层面来实现，可以减少代码层面的业务处理

• 存储过程和函数的区别

  • 函数必须有返回值，存储过程没有返回值

• 存储过程使用

        注： DELIMITER 分隔符          该关键字用来声明sql语句的分隔符，告诉MySQL该段命令已经结束！ sql语句默认的分隔符是分号，但是有的时候我们需要一条功能sql语句中包含分号，但是并不作为结束标识。这个时候就可以使用DELIMITER来指定分隔符了！

• 创建存储过程语法

-- 修改分隔符为$
DELIMITER $

-- 标准语法
CREATE PROCEDURE 存储过程名称(参数...)
BEGIN
    sql语句;
END$

-- 修改分隔符为分号
DELIMITER ;

• 调用存储过程语法

 -- 标准语法
CALL 存储过程名称(实际参数);

• 查看存储过程语法

 -- 查询数据库中所有的存储过程 标准语法
SELECT * FROM mysql.proc WHERE db='数据库名称';

• 删除存储过程语法

 DROP PROCEDURE [IF EXISTS] 存储过程名称;

• 存储过程语法

• 变量的使用

• 定义变量语法

 DECLARE 变量名 数据类型 [DEFAULT 默认值];

•  变量的赋值

SET 变量名 = 变量值; 

SELECT 列名 INTO 变量名 FROM 表名 [WHERE 条件]; 

• if语句的使用

语法： 

 IF 判断条件1 THEN 执行的sql语句1;
[ELSEIF 判断条件2 THEN 执行的sql语句2;]
...
[ELSE 执行的sql语句n;]
END IF;

• 参数的传递

CREATE PROCEDURE 存储过程名称([IN|OUT|INOUT] 参数名 数据类型)
BEGIN
    执行的sql语句;
END$
/*
    IN:代表输入参数，需要由调用者传递实际数据。默认的
    OUT:代表输出参数，该参数可以作为返回值
    INOUT:代表既可以作为输入参数，也可以作为输出参数
*/ 

• while循环

初始化语句;
WHILE 条件判断语句 DO
    循环体语句;
    条件控制语句;
END WHILE; 

• 游标

• 游标的语法

• 创建游标

DECLARE 游标名称 CURSOR FOR 查询sql语句;

• 打开游标

OPEN 游标名称;

• 使用游标获取数据

FETCH 游标名称 INTO 变量名1,变量名2,...;

• 关闭游标

CLOSE 游标名称;   

 

•  存储函数

• 创建存储函数

DELIMITER $
​
-- 标准语法
CREATE FUNCTION 函数名称([参数 数据类型])
RETURNS 返回值类型
BEGIN
    执行的sql语句;
    RETURN 结果;
END$
​
DELIMITER ;

• 调用存储函数

SELECT 函数名称(实际参数);

• 删除存储函数

DROP FUNCTION 函数名称;  

---

 

• 触发器 

1.触发器的概念

• 触发器是与表有关的数据库对象，可以在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句。触发器的这种特性可以协助应用在数据库端确保数据的完整性 、日志记录 、数据校验等操作 。

• 使用别名 NEW 和 OLD 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

触发器类型	OLD的含义	NEW的含义
INSERT 型触发器	无 (因为插入前状态无数据)	NEW 表示将要或者已经新增的数据
UPDATE 型触发器	OLD 表示修改之前的数据	NEW 表示将要或已经修改后的数据
DELETE 型触发器	OLD 表示将要或者已经删除的数据	无 (因为删除后状态无数据)

2.触发器操作

• 创建触发器

DELIMITER $

CREATE TRIGGER 触发器名称
BEFORE|AFTER INSERT|UPDATE|DELETE
ON 表名
[FOR EACH ROW]  -- 行级触发器
BEGIN
	触发器要执行的功能;
END$

DELIMITER ;

• 查看触发器

SHOW TRIGGERS; 

• 删除触发器

DROP TRIGGER 触发器名称;