视图，存储过程和函数

视图

一、含义

视图(VIEW)也被称作虚表，即虚拟的表，是一组数据的逻辑表示,其本质是对应于一条SELECT语句，结果集被赋予一个名字，即视图名字。 
视图本身并不包含任何数据，它只包含映射到基表的一个查询语句，当基表数据发生变化，视图数据也随之变化。
好处：
1、简化sql语句
2、提高了sql的重用性
3、保护基表的数据，提高了安全性

二、创建

create view 视图名
as
查询语句;

三、修改

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

四、删除、查看

查看 

1.查看当前数据库中的所有视图

show table status where comment='view';

2.查看指定视图所包含的列名和字段类型

desc 视图名;

3..查看指定视图的创建语句
show create view 视图名;

删除(可以一次删除多个视图)

删除视图不会影响原表的数据

drop view IF EXISTS view1,view2,view3;

五、更新

insert，update，delete等语句可以更新视图中的数据（可以影响到原表）

注意：具备以下特点的视图都不允许更新（即创建视图的时候用到下面语句时）
①包含分组函数、group by、distinct、having、union、或 union all
②join（update可以使用，insert不行）
③常量视图
④where后的子查询用到了from中的表
⑤用到了不可更新的视图

⑥select中包含子查询

例如：

表的创建

CREATE TABLE `admin` (
  `id` int(11) NOT NULL auto_increment,
  `username` varchar(10) NOT NULL,
  `password` varchar(10) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

视图的创建：

CREATE OR REPLACE VIEW my_v1
AS
SELECT * FROM admin;

执行以下语句时，基本表admin的数据也会发生变化。 

INSERT INTO my_v1 VALUES(6,'张三',250);
UPDATE my_v1 SET boyName='李四' WHERE id=6;
delete  from my_v1 where id=5;

六、视图和表的对比

视图是从一个或几个基本表导出的表，它与基本表不同，是一个虚表，数据库中只存放视图的定义，而不存放视图对应的数据，这些数据存放在原来的基本表中，当基本表中的数据发生变化，从视图中查询出的数据也就随之改变 。视图一经定义就可以像基本表一样被查询、删除，也可以在一个视图之上再定义新的视图，但是对视图的更新操作有限制
   

	关键字	是否占用物理空间	使用
视图	view	占用较小，只保存sql逻辑	一般用于查询
表	table	保存实际的数据	增删改查

 

存储过程

 

 

优点

• 存储过程可封装，并隐藏复杂的商业逻辑。
• 存储过程可以回传值，并可以接受参数。
• 存储过程无法使用 SELECT 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。
• 存储过程可以用在数据检验，强制实行商业逻辑等。

缺点

• 存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。
• 存储过程的性能调校与撰写，受限于各种数据库系统。

 

一、创建

create procedure 存储过程名(参数模式 参数名 参数类型)[characteristic]
begin
        存储过程体
end
注意：characteristic
1.参数模式：in、out、inout，其中in可以省略（也可以创建没有参数的存储过程）
2.存储过程体的每一条sql语句都需要用分号结尾

in类型：内部运算变化不影响外部；

out类型：内部运算变化影响外部变化并且传参到储存过程时默认初始化参数为null；

inout类型：与out类型相比不同是默认初始化参数不为null，传的是什么就是什么。

out类型参数不能赋值多个结果

MySQL中的IN、OUT、INOUT类型

out是返回参数，select是查询的结果。类比java ，out是return parameter; ，select 是System.out.println();的输出结果

二、调用

call 存储过程名(实参列表)
举例：
调用in模式的参数：call sp1（‘值’）;
调用out模式的参数：set @name; call sp1(@name);select @name;
调用inout模式的参数：set @name=值; call sp1(@name); select @name;

三、查看 修改 和删除

查看

1.查看指定数据库中所有的存储过程名字，更详细的信息（创建语句，时间等）选择select * 查看proc表中的所有字段。

select `name` from mysql.proc where db = 'your_db_name' and `type` = 'PROCEDURE'

2.查看所有数据库中，创建时间和所在数据库存储过程或函数（查询的是mysql表中的数据）

show procedure status;

3.查看创建存储过程的语句

show create procedure 存储过程名;

修改

语法格式 
ALTER {PROCEDURE | FUNCTION} sp_name [characteristic……]

参数说明

Sp_name，表示存储过程或函数的名称 
characteristic，表示要修改存储过程的哪个部分 
Characteristic的取值如下 
CONTAINS SQL，表示子程序包含SQL语句，但是，不包含读或写数据的语句 
NO SQL，表示子程序中，不包含SQL语句 
READS SQL DATA，表示子程序中，包含读数据的语句 
MODIFIES DATA，表示子程序中，包含写数据的语句 
SQL SECURITY {DEFINER | INVOKER}，指明谁有权限来执行 
DEFINER，表示只有定义者，自己才能够执行 
INVOKER，表示调用者可以执行 
COMMENT’string’，表示注释信息

例：修改注释

修改存储过程的名字

update mysql.`proc` 
set `name`='修改后的名字' ,specific_name='修改后的名字' 
where db='存储过程所在的数据库库名' and `name`='旧的存储过程名字';

其实修改的就是mysql.proc表中的值， name和specific_name的值不一样也可以修改，修改后的名字为name的值，具体影响不清楚

删除
drop procedure if exists 存储过程名;

函数

函数和存储过程类似，都是持久性存储模块。函数的定义和存储过程也类似，不同之处是函数必须指定返回类型。

 

调用

SELECT 函数名(参数列表)

查看

1.查看指定数据库下的所有函数名字。

select name from mysql.proc where db = 'xx' and type = 'FUNCTION' //函数

2.查看函数创建时间，函数语句等。

show function status; //函数

3.查看函数创建语句

show create function func_name;

删除
DROP FUNCTION if exists  fun_name;

函数和存储过程区别

参数类型

存储过程的参数可以有IN,OUT,INOUT三种类型

函数只能有IN类~~存储过程声明时不需要返回类型，而函数声明时需要描述返回类型，且函数体中必须包含一个有效的RETURN语句。

返回值

存储过程的返回值，可以有多个值或没有

函数的返回值，只有一个值且必须有。

调用

存储过程 ： call procedure_name(参数列表)

函数：select function_name(参数列表)

 

一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。存储过程，功能强大，可以执行包括修改表等一系列数据库操作；用户定义函数不能用于执行一组修改全局数据库状态的操作。

存储过程一般是作为一个独立的部分来执行（ EXECUTE 语句执行），而函数可以作为查询语句的一个部分来调用（SELECT调用），由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。 SQL语句中不可用存储过程，而可以使用函数。