MySql视图和函数

视图

视图简介

为什么要使用视图？

a) 从三大范式当中，我们知道一张表只存一种实体的数据，但现实业务往往是，需要多个表的数据关联呈现的，并且某些固定的列会被频繁的访问，视图可以避免频繁的编写这些关联查询语句；
b) 某些人可能只允许表中的部分列，不能将整个表的列或数据行暴露出来，我们可以为这些特定的人创建一个视图，把权限给到这些人，起到对基表中的其他列和数据行进行安全保护；
视图中存储了可以返回结果集的 sql 查询语句，当用在 from 子句时，内部的 sql 查询语句就会被执行。

什么是视图？

MySQL 视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。

视图的优点：

1. 简单化，数据所见即所得；
2. 安全性，用户只能查询或修改他们所能见到得到的数据；
3. 逻辑独立性，可以屏蔽真实表结构变化带来的影响。

视图的缺点：

1. 性能相对较差，从视图查询数据可能会很慢，特别是如果视图是基于其他视图创建的；
2. 修改不方便，特别是复杂的聚合视图基本无法修改。

MySQL 中视图的使用

基于单表创建视图：

-- create view 视图名 as 查询语句;
create view view1
as
select 
studentName,gender,age,classid 
from studentinfo;
-- 通过视图查询
select * from view1;

使用别名的视图：

-- 创建视图
create view view2(姓名,性别,年龄,班级编号)
as 
select 
studentName,gender,age,classid 
from studentinfo;
-- 通过视图查询
select * from view2;

基于多表创建视图：

-- 创建视图
create view view3
as select studentName,gender,age,classname
from studentinfo s inner join classinfo c
on s.ClassID = c.ClassID;
-- 通过视图查询
select * from view3;

使用视图更新数据：

在 MySQL 中，视图不仅是可查询的，而且是可更新的。这意味着您可以使用 INSERT 或 UPDATE 语句通过可
更新视图插入或更新基表的行。另外，您可以使用 DELETE 语句通过视图删除底层表的行。

定义视图的 SELECT 语句不能包含以下任何元素：

1. 聚合函数；
2. distinct 子句；
3. group by 子句；
4. having 子句；
5. union 和 union all 子句；
6. 外连接

注意：

• 不建议使用基于多表创建的视图进行更新操作。
• 修改和添加修改的是底层基表，并不是视图

基于视图 view1 修改学生年龄，让学生年龄都+1，代码如下所示：

-- 通过视图查询
select * from view1;
-- 通过 view1 修改学生年龄
update view1 set age = age+1;
-- 查询学生表
select studentname,age from studentinfo;

更新前和更新后的结果如下图所示：

with check option 子句

为了确保视图的一致性，在创建或修改视图时使用 WITH CHECK OPTION 子句。

alter view 视图名
as sql语句 with check option

在创建视图时添加 WITH CHECK OPTION 子句，代码如下所示：

-- 修改视图
alter view view4
as
select * from subject where CreditHour>24 with check option;
-- 通过视图添加新课程
insert into view4 values(null,'layui',20,1,1);

视图管理

查看视图定义

show create view 视图名

查看视图

视图本质上也是表，因此查看视图可以使用 show tables 命令查看。

修改或替换视图

使用 alter view 替换 create view 就可以实现视图的修改作，还可以使用 CREATE OR REPLACE VIEW 语句来创建或替换现有视图。如果一个视图已经存在，MySQL 只会修改视图。如果视图不存在，MySQL 将创建一个新的视图。

函数

函数简介

MySQL 中的函数和 JavaScript 中的函数作用类似，就是执行特定任务的代码块。
其实，大家已经有过在 MySQL 中使用函数的经验了，比如我们获得系统时间，可以用 now()函数，求平均值可以用 avg()函数等等。这些系统定义好的函数我们称为系统函数，可以直接拿来使用，但有些时候我们需要完成特定功能，就需要自己定义函数。用户自己定义的函数，称为自定义函数。

MySQL 中函数的使用

创建函数

创建函数的语法如下所示：

create function 函数名([参数列表]) returns 数据类型
begin
sql 语句;
return 值;
end;

创建一个函数，返回学号为“2011001002”的学生姓名。代码如下所示：

create function myfun1() returns varchar(20) -- 指定数据的返回类型
begin 
	declare namess varchar(20); -- 定义一个变量，接收名字
	-- into namess 把查询到的结果赋值给namess
	select studentname into namess from studentinfo where StudentID='2011001002';
	return namess;-- 返回namess
end;
-- 调用函数
select myfun1();

语法中的几个要点：

1. 函数名后面有一对小括号，括号内可以填写或不填参数，但括号不能省略；
2. 小括号后必须跟 returns，returns后跟返回值类型，类型必须是 MySQL 中的类型；
3. 函数主体放在 begin…end 内,end 前要 return 与前returns 后跟的类型相同的值；
4. select 查询结果也可以用来给变量赋值，但是需要用 into 关键词。

创建一个带参数的函数，输入学号，返回学生姓名。代码如下所示：

create function myfun2(sid varchar(20)) returns varchar(20)
begin
	declare namess varchar(20);
	select studentname into namess from studentinfo where StudentID=sid;
	return namess;
end;
-- 调用函数
select myfun2('2011001002');

函数与过程的区别

• 返回值不同：函数必须有返回值,且仅返回一个结果值；过程可以没有返回值,但是能返回结果集；
• 调用时的不同：函数使用 select进行调用；过程使用 call 进行调用；
• 参数的不同：函数的参数都是 in 参数；过程可以传递 in\out\inout 参数。