Mysql的视图、存储过程与函数
- 视图
- 视图的理解
- 创建更新视图
- 修改删除视图
- 视图好处与不足
- 存储过程
- 存储过程的理解
- 创建存储过程
- 存储函数
- 对比存储过程
- 创建存储函数
- 存储过程、函数的查看和删除
视图
视图的理解
视 图其实就是一个虚拟的表,本身不具有数据。
为什么使用视图:比如一张表中,一共十几个字段,但是只想让员工使用其中的几个(需要给不同的人不同的权限),就可以创建该视图让其进行操作,视图中的修改相当于直接对表进行修改。
视图建立在已有表的基础上,这些表称为基表。视图可以理解为存储起来的ELECT语句
创建更新视图
CREATE VIEW 视图名 AS SELECT语句
可以将经常调用的复杂的查询,创建为视图,后期只需要调用该视图即可。
视图的查看、更新操作和表的相同。更新视图的同时会更新基表。更新基表时也会更新视图。
注意:操作的视图中的字段一定要在基表中有一对一的对应,否则无法对该字段进行更新。
修改删除视图
//修改
CREATE OR REPLACE VIEW 视图名 AS SELECT语句
//或
ALTER VIEW 视图名 AS SELECT语句
//删除
DROP VIEW 视图名
若有一个视图的基表被删除,则该视图不能再使用
若一个视图A的创建是基于另外一个或多个视图,则有一个视图出问题,该视图A则不能再使用。
视图好处与不足
好处:
- 操作简单(不需要再去关注表,只对视图操作即可)
- 减少了数据的冗余(存储了SELECT的查询)
- 数据更加安全(可以分配不同的权限来操作表)
- 适应灵活多变的需求(可以不用再对表进行大的操作,改变视图即可)
- 能分解复杂的查询逻辑(可以将几个表一起查询后存到一个视图中)
不足:
需要经常维护,小型项目不推荐使用。
存储过程
存储过程的理解
就是对一组预先编译后的SQL语句的封装。
执行过程为:存储过程先存储在MySQL的服务器上,当需要执行的时候,之间调用存储过程,服务器就会将里面的SQL语句全部执行。
和视图相比,视图就是一个虚拟的表,主要做的是查询操作,存储过程可以更复杂,可以对表随意进行操作,主要存储一些复杂的逻辑。
跟代码中的方法(函数)类似,之间调用方法,实里面的功能。
创建存储过程
无参的存储过程创建与调用
//创建存储过程
CREATE PROCEDURE 存储过程名()
BEGIN
SQL语句
END;
//如果SQL语句有多条,(有多个分号)则需要改变结束标识符
DELIMITER // // 将标识符改为//
CREATE PROCEDURE 存储过程名()
BEGIN
SQL语句
END//
DELIMITER ; // 再将标识符改为;
//调用存储过程
CALL 存储过程名();
OUT参数的创建与调用
DELIMITER //
CREATE PROCEDURE 存储过程名(OUT 参数名 参数类型)
BEGIN
SQL语句 INTO 参数名 //将查询的值赋值给参数
END //
DELIMITER ;
//调用存储过程
CALL 存储过程名(@传入的参数)
//查询该参数的值
SELECT @参数名
例:
DELIMITER //
CREATE PROCEDURE get_num(OUT ms DOUBLE)
BEGIN
SELECT MIN(salary) INTO ms
FROM employees;
END //
DELIMITER ;
CALL get_num(@ms);
SELECT @ms;
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);
OUT 就是 将一个SQL语句执行的值传出来,IN是将一个值传进去后执行SQL语句,IN和OUT可以结合使用或多次使用同一个,中间用逗号隔开
INOUT 参数的创建与调用
相当于IN和OUT结合,就是将一个变量带进去后赋值后再传出来
存储函数
对比存储过程
关键字 | 调用语法 | 返回值 | 应用场景 | |
存储过程 | PROCEDURE | CALL | 零个或多个 | 一般用于更新 |
存储函数 | FUNCTION | SELECT | 只能是一个 | 一般用于查询结果为一个值并返回 |
创建存储函数
DELIMITER //
CREATE FUNCTION 函数名
RETURNS 返回值类型
#约束
DETERMINISTIC
CONTAINS SQL
READS SQL DATA
BEGIN
RETURN (SQL语句)
END //
# 在创建函数前进行以 下操作,可以不在添加约束
SET GLOBAL log_bin_trust_function_creators = 1;
#调用存储函数
SELECT 函数名();
例:
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();
存储过程、函数的查看和删除
#查看指定的存储过程或函数
SHOW CREATE FUNCTION/PROCEDURE 函数名/过程名;
#查看所有的存储过程或函数, 后可以跟LIKE进行模糊查询
SHOW FUNCTION/PROCEDURE STATUS
#查看information_schema数据库表中ROUTINES表,所有的存储过程和函数都在此
SELECT * FROM ROUTINES;
#删除指定的存储过程或函数
DROP FUNCTION/PROCEDURE IF EXISTS 函数名或过程名