视图的增删改操作和自定义函数的创建与应用

什么是视图？
视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。
视图的优点：
简单化，数据所见即所得；
安全性，用户只能查询或修改他们所能见到得到的数据；
逻辑独立性，可以屏蔽真实表结构变化带来的影响。
视图的缺点：
性能相对较差，从视图查询数据可能会很慢，特别是如果视图是基于其他视图创建的；
修改不方便，特别是复杂的聚合视图基本无法修改。
基于单表创建视图

-- 创建视图
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 studentName,gender,age,classname
	from studentinfo s , classinfo c
	where s.ClassID = c.ClassID;
-- 通过视图查询
select * from view3;

使用视图更新数据

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

WITH CHECK OPTION 子句

-- 创建视图
create view view4
as
select * from subject where CreditHour>24;
-- 通过视图查询
select * from view4;
-- 通过视图添加新课程
insert into view4 values(null,'java 基础',20,1,1);
-- 通过视图查询
select * from view4;
-- 查询课程表
select * from subject;

这个时候可以得出，虽然数据添加进去了，但是查询视图根本看不到添加的数据，在基表却可以看到，这就是一个问题，由此有了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 tables 命令查看。
show create view view1;

修改或替换视图

-- 使用 alter view 替换 create view 就可以实现视图的修改操作，
--还可以使用 CREATE OR REPLACE VIEW 语句来创建或替换现有视图
-- 修改视图
alter view view4
as
select * from subject;

删除视图

-- 使用“drop view 视图名”命令就可以删除视图了。
drop view view1;

函数的使用

创建函数

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

-- 创建函数
create function myfun1() returns varchar(20)
begin
declare name varchar(20);
select studentname into name from studentinfo where studentid='2011001004';
return name;
end;
-- 调用函数
select myfun1();

注意事项
函数名后面有一对小括号，括号内可以填写或不填参数，但括号不能省略；
小括号后必须跟 returns，returns 后跟返回值类型，类型必须是 MySQL 中的类型；
函数主体放在 begin…end 内,end 前要 return 与前面 returns 后跟的类型相同的值；
select 查询结果也可以用来给变量赋值，但是需要用 into 关键词。
创建一个带参数的函数，输入学号，返回学生姓名。

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

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

查看存储过程