关于Mysql中的视图和存储过程

最近博主在使用过程中,遇到Mysql的视图和存储过程,记录一下相关知识

1 视图view

视图: 是Mysql的5.1版本后推送的一个特性,本身是一个虚拟表,它的数据来源于真实存在的表，通过执行SQL语句来获取动态数据,在平时使用时,只需要使用名称直接调用,获取结果集.

特点:

• 提高了SQL的复用性
• 保护基础表格的数据,提高安全性(对接第三方数据时,常常对外暴露视图虚拟表)
• 使用简单,方便,直接当做一张虚拟表

以下表为例:

-- 用户表
CREATE TABLE `user` (
  `id` bigint(20) NOT NULL COMMENT '主键ID',
  `name` varchar(30) DEFAULT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`),
  KEY `index_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 添加数据
INSERT INTO `test`.`user`(`id`, `name`, `age`, `email`) VALUES (1, 'Jone', 1, 'test1@baomidou.com');
INSERT INTO `test`.`user`(`id`, `name`, `age`, `email`) VALUES (2, 'Jack', 20, 'test2@baomidou.com');
INSERT INTO `test`.`user`(`id`, `name`, `age`, `email`) VALUES (3, 'Tom', 28, 'test3@baomidou.com');
INSERT INTO `test`.`user`(`id`, `name`, `age`, `email`) VALUES (4, 'Sandy', 21, 'test4@baomidou.com');

1 创建视图

1 格式为: CREATE VIEW 视图名称 AS SQL语句

CREATE VIEW `0817-USER` AS 
SELECT
	* 
FROM
	`user`;

2 使用navicat查看视图

3 视图数据集

2 修改视图

1 格式为:

• 格式1 ALTER VIEW 视图名称 AS SQL语句

• 格式2 CREATE OR REPLACE VIEW 视图名 AS SQL语句

先使用格式1,修改视图查询所有的id,后使用格式2,修改视图查询所有的列.

2 使用格式1修改视图

-- 方式1
ALTER VIEW `0817-USER`
AS
SELECT
	id 
FROM
	`user`;

3 使用navicat查看视图

4 使用格式2修改视图

-- 方式2
CREATE 
	OR REPLACE VIEW `0817-USER` AS 
SELECT
	* 
FROM
	`user`;

5 使用navicat查看视图

3 删除视图

1 格式为: DROP VIEW 视图1,视图2,...;

2 删除视图

DROP VIEW `0817-USER`;

3 navicat查看视图

没有视图数据,视图已经删除.

4 查看视图

1 格式为:

• 格式1 DESC 视图名; (查看视图的字段以及类型等信息)
• 格式2 SHOW CREATE VIEW 视图名; (查看视图的创建过程)

2 先创建视图,再查看视图

-- 1 创建视图
CREATE VIEW `0817-USER` AS 
SELECT
	* 
FROM
	`user`;
-- 2 格式1查看视图
DESC `0817-USER`;

-- 3 格式2查看视图
SHOW CREATE VIEW `0817-USER`;

5 总结

视图,相当于一张虚拟表,在使用时,直接当做表使用即可.

查询视图表数据

select
	* 
from
	`0817-USER`

对于视图,一般用于查询,不用于更新,删除操作. 因为更新,删除视图,会对原表格产生同样的影响.所以有些情况视图不能更新:

1 包含有group by, distinct, having,union,join等

2 常量视图

否则会报如下错误:

2 存储过程

存储过程: 是一组预先编译好的SQL语句的集合,经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它.

特点:

1 增强SQL语言的功能和灵活性

2 提高SQL的复用性,标准

3 减少编译次数,较快的执行速度,提高效率

1 创建存储过程

# 存储过程格式
DELIMITER $
CREATE PROCEDURE 存储过程名称(参数模式 参数名 参数类型,...)
BEGIN
	存储过程体（一组合法的SQL语句）
END $
DELIMITER ;

格式说明:

参数模式:

• in 参数类型是输入的
• out 参数类型是输出的
• inout 参数类型既可以输入也可以输出

1 in类型存储过程

DELIMITER //
  CREATE PROCEDURE in_param(IN p_in int)
    BEGIN
    SELECT p_in;
    SET p_in=2;
    SELECT p_in;
    END;
    //
DELIMITER ;

2 in类型存储过程查看

-- 查看方式1
CALL in_param(1);

-- 查看方式2
SET @p_in=1;
CALL in_param(@p_in);
SELECT @p_in;

3 out类型存储过程

-- out存储过程
DELIMITER //
  CREATE PROCEDURE out_param(OUT p_out int)
    BEGIN
      SELECT p_out;
      SET p_out=2;
      SELECT p_out;
    END;
    //
DELIMITER ;

4 out类型存储过程查看

SET @p_out=1;
CALL out_param(@p_out);
SELECT @p_out;

5 inout类型存储过程

DELIMITER //
  CREATE PROCEDURE inout_param(INOUT p_inout int)
    BEGIN
      SELECT p_inout;
      SET p_inout=2;
      SELECT p_inout;
    END;
    //
DELIMITER ;

6 inout类型存储过程查看

SET @p_inout=1;
CALL inout_param(@p_inout) ;
SELECT @p_inout;

2 查看存储过程

格式: SHOW CREATE PROCEDURE 存储过程名;

SHOW CREATE PROCEDURE out_param

3 修改存储过程

没有修改语句,只能先删除,再新增.

4 删除存储过程

格式: DROP PROCEDURE 存储过程名;

DROP PROCEDURE out_param

删除后,存储过程就不存在,即navicat中函数列里面就没有相关的函数.