Mysql的视图、存储过程与函数

视图

视图的理解

视 图其实就是一个虚拟的表，本身不具有数据。
为什么使用视图：比如一张表中，一共十几个字段，但是只想让员工使用其中的几个（需要给不同的人不同的权限），就可以创建该视图让其进行操作，视图中的修改相当于直接对表进行修改。
视图建立在已有表的基础上，这些表称为基表。视图可以理解为存储起来的ELECT语句

创建更新视图

CREATE VIEW 视图名 AS  SELECT语句
1

可以将经常调用的复杂的查询，创建为视图，后期只需要调用该视图即可。
视图的查看、更新操作和表的相同。更新视图的同时会更新基表。更新基表时也会更新视图。
注意：操作的视图中的字段一定要在基表中有一对一的对应，否则无法对该字段进行更新。

修改删除视图

//修改
CREATE OR REPLACE VIEW 视图名 AS SELECT语句
//或
ALTER VIEW 视图名 AS SELECT语句
//删除
DROP VIEW 视图名
1
2
3
4
5
6

若有一个视图的基表被删除，则该视图不能再使用
若一个视图A的创建是基于另外一个或多个视图，则有一个视图出问题，该视图A则不能再使用。

视图好处与不足

好处：

1. 操作简单（不需要再去关注表，只对视图操作即可）
2. 减少了数据的冗余（存储了SELECT的查询）
3. 数据更加安全（可以分配不同的权限来操作表）
4. 适应灵活多变的需求（可以不用再对表进行大的操作，改变视图即可）
5. 能分解复杂的查询逻辑（可以将几个表一起查询后存到一个视图中）

不足：
需要经常维护，小型项目不推荐使用。

存储过程

存储过程的理解

就是对一组预先编译后的SQL语句的封装。
执行过程为：存储过程先存储在MySQL的服务器上，当需要执行的时候，之间调用存储过程，服务器就会将里面的SQL语句全部执行。
和视图相比，视图就是一个虚拟的表，主要做的是查询操作，存储过程可以更复杂，可以对表随意进行操作，主要存储一些复杂的逻辑。
跟代码中的方法(函数)类似，之间调用方法，实里面的功能。

创建存储过程

无参的存储过程创建与调用

//创建存储过程
CREATE PROCEDURE 存储过程名()
BEGIN
SQL语句
END;
//如果SQL语句有多条，（有多个分号）则需要改变结束标识符
DELIMITER //                  //   将标识符改为//
CREATE  PROCEDURE 存储过程名()
BEGIN
SQL语句
END//
DELIMITER ;                   // 再将标识符改为;
//调用存储过程
CALL 存储过程名();
1
2
3
4
5
6
7
8
9
10
11
12
13
14

OUT参数的创建与调用

DELIMITER //

CREATE PROCEDURE 存储过程名(OUT 参数名 参数类型)
BEGIN
SQL语句 INTO 参数名    //将查询的值赋值给参数
END //
DELIMITER ;

//调用存储过程
CALL 存储过程名(@传入的参数)

//查询该参数的值
SELECT @参数名
1
2
3
4
5
6
7
8
9
10
11
12
13

例：

DELIMITER //
CREATE PROCEDURE get_num(OUT ms DOUBLE)
BEGIN 
SELECT MIN(salary) INTO ms
FROM employees;
END //
DELIMITER ;

CALL get_num(@ms);

SELECT @ms;
1
2
3
4
5
6
7
8
9
10
11

IN 参数的创建与调用（直接举例）

//查找last_name 为 name 的人 ，， 类似于函数的调用
DELIMITER //

CREATE PROCEDURE show_num(IN Aname VARCHAR(20))
BEGIN
SELECT  * FROM employees
WHERE last_name = Aname;
END //

DELIMITER ;

SET @Aname := 'name';

CALL show_num(@Aname);
1
2
3
4
5
6
7
8
9
10
11
12
13
14

OUT 就是 将一个SQL语句执行的值传出来，IN是将一个值传进去后执行SQL语句，IN和OUT可以结合使用或多次使用同一个，中间用逗号隔开

INOUT 参数的创建与调用
相当于IN和OUT结合，就是将一个变量带进去后赋值后再传出来

存储函数

对比存储过程

创建存储函数

DELIMITER //
CREATE FUNCTION 函数名
RETURNS 返回值类型
		#约束
	DETERMINISTIC
	CONTAINS SQL 
	READS SQL DATA
BEGIN
	RETURN (SQL语句)
END //

# 在创建函数前进行以 下操作，可以不在添加约束
SET GLOBAL log_bin_trust_function_creators = 1;

#调用存储函数
SELECT 函数名();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

例：

SET GLOBAL log_bin_trust_function_creators = 1; 
DELIMITER //
CREATE FUNCTION get_email()
RETURNS VARCHAR(25)
BEGIN 
		RETURN (SELECT email FROM employees WHERE last_name='Abel');
		
END //

#调用存储函数
SELECT get_email();
1
2
3
4
5
6
7
8
9
10
11

存储过程、函数的查看和删除

#查看指定的存储过程或函数
SHOW CREATE FUNCTION/PROCEDURE 函数名/过程名;
#查看所有的存储过程或函数， 后可以跟LIKE进行模糊查询
SHOW FUNCTION/PROCEDURE STATUS 
#查看information_schema数据库表中ROUTINES表，所有的存储过程和函数都在此
SELECT * FROM ROUTINES;

#删除指定的存储过程或函数
DROP  FUNCTION/PROCEDURE IF EXISTS  函数名或过程名
1
2
3
4
5
6
7
8
9