Mysql视图、变量、存储过程、函数

视图

概念

视图其实就是一个虚拟表，具体的使用和普通的数据表一样，是Mysql5.0.1版本提出的新特性，它其实就是将一组复杂的sql语句进行封装。
视图的好处是：

• 可重用sql
• 简化复杂的sql
• 保护数据，提高安全性

视图的创建

语句：

create view 视图名
as
查询语句

一个案例如下：查询姓名中包含a字母的员工姓名、部门名、工种信息

-- 创建一个视图 将三个表的连接查询放入其中
create view myv1
as
selete 员工姓名字段, 部门名字段， 工种名字段
from 员工表
join 部门表 on 连接条件
join 工种表 on 连接条件;

-- 使用视图
selete * from myv1 where 员工姓名字段 like '%a%'

所以，视图就是一个虚拟表

视图的修改

-- 方式一 如果视图不存在就创建  如果存在就替换
create or replace view 视图名
as
查询语句

-- 方式二 仅仅对视图进行修改
alter view 视图名
as
查询语句

视图的删除与查询

-- 删除 支持一次性删除多个
drop view 视图名1,视图名2,......

-- 查看某个视图的信息
show create view 视图名;

视图的更新

视图也可以对数据进行增删改的操作。具体的语句也就是insert into 表名 values(值...) update 表名 set 字段=值.... delete from 表名 where 筛选条件 这些语句。只不过在对视图进行这些操作时，原始的数据表也会出现相应的变化

为了安全，一般会为视图添加一个只读的权限。

还有一些情况视图也是不能进行更新操作，如下所示：

• 包含一些关键字的sql语句： 分组函数 distinct group by having union
• 常量视图
• selece后面包含子查询
• join
• form一个不能更新的视图
• where字句的子查询引用了from字句中的表

变量

Mysql变量的分类

• 系统变量
  • 全局变量
  • 会话变量
• 自定义变量
  • 用户变量
  • 局部变脸

系统变量

系统变量属于服务器，由系统提供，不能用户自定义，根据作用于的不同又分为了全局变量和会话变量

• 查看所有的系统变量 show [global或session] variables 如果是查看全局变量就加global 如果是查看会话变量就是session如果不写默认是会话变量
• 模糊查询变量的值show variables like '%变量名关键字%'
• 查看某个具体的系统变量的值select @@变量名 这默认的查询会话变量 如果是要查询全局变量则是select @@global.变量名
• 为变量设置值set 系统变量名=值 默认是会话变量 如果想要设置全局变量的值需要在set后面加上global

自定义变量

用户变量

创建用户变量是要求必须初始化，有三种方式

set @用户变量名=值;
set @用户变量名:=值;
select @用户变量名:=值;

为用户变量设置新的值，

-- 和创建的三条语句一样
set @用户变量名=值;
set @用户变量名:=值;
select @用户变量名:=值;
-- 还有一种赋值的方式，将查询数据表的结果赋值给变量 但要求查询结果是一个 一行一列的结果 比如count(*)
select 字段名 into @用户变量名 from 数据表名 where 查询条件;

使用用户变量

select @用户变量名;

set @count=1; -- 创建

select count(*) into @count form user; -- 重新赋值

select @count; -- 查看


set @n=1; 
set @m=2; 
set @sum=@n+@m;
select @sum;

局部变量

局部变量最主要的一个特征就是作用域仅仅在begin end中。它只能写在begin end 中的第一句话

• 创建局部变量

  • declare 变量名 类型
  • declare 变量名 类型 default 值 还可以在创建时设置值

• 设置值

  • set 局部变量名=值
  • set 局部变量名：=值
  • select @局部变量名：=值
  • select 字段 into 局部变量名 from 表名 要求这个字段查询的结果是一行一列的值

• 使用，查看用户变量的值

  • select 局部变量名;

存储过程

一种预先编译好的sql语句的集合，好处如下：

• 提高代码的重用性
• 简化操作
• 减少了编译次数
• 多条sql减少了连接数据库的连接次数

创建语法：

create procedure 存储过程名（参数列表）
begin
	存储过程体，也就是一组合法的sql语句
end

需要注意的就是存储过程名中的参数列表，它由三部分组成：参数模式+参数名+参数类型，比如in stuname varchar(20)

参数模式为：in、out、inout

存储过程体中每条sql语句的结尾必须加; 分号

存储过程的结尾可以使用delimiter 重新设置 语法：delimiter $这里使用$符号作为存储过程的结束标识，所以创建语法应该如下：

delimiter $
create procedure 存储过程名（参数列表）
begin
	存储过程体，也就是一组合法的sql语句
end $

调用语法：

call 存储过程名（新参列表）$
-- 上面设定的存储过程结尾符号

存储过程参数列表中的变量是局部变量

带in模式

案例：根据女生名字，查询对应男朋友的信息

-- 创建
delimiter $
create procedure myp1(in woman_name varchar(10))
begin

	select bo.*
	from boys bo
	right join beauty be
	on bo.id = be.boyfriend_id;
	where be.name=woman_name
end $

-- 调用
call myp1('女生姓名')$

上面是一个入参，如果有多个入参的情况如下。案例是验证用户是否登录成功

delimiter $
create procedure mypro2(in user_name varcher(10),in pass_word varchar(10))
begin
	select count(*) from user where username=user_name and password=pass_word;
end $

带out模式

在存储过程的参数列表中，某个参数的参数类型为out，在存储过程体中，只要为这个参数赋值了就会自动返回。

案例，根据女生名返回对应的男朋友姓名

-- 创建
delimiter $
create procedure mypro3(in woman_name varchar(10),out boy_name varchar(10))
begin
	select bo.name into boy_name
	from boys bo
	join beauty be
	on be.boyfriend_id = bo.id
	where be.name = woman_name;
end $

-- 调用 
-- 需要先定义一个变量来接收值
set @boyName=''$
call mypro3('女生姓名'，@boyName)$
select @boyName$

多个out模式的参数，案例是根据女生名返回对应的男朋友姓名和年龄

-- 创建
delimiter $
create procedure mypro3(in woman_name varchar(10),out boy_name varchar(10),out boy_age varchar(10))
begin
	select bo.name,bo.age into boy_name,boy_age
	from boys bo
	join beauty be
	on be.boyfriend_id = bo.id
	where be.name = woman_name;
end $

带inout模式

案例：输入两个数字，要求翻倍并返回

delimiter $
create procedure mypro4(inout a int,inout b int)
begin
	set a=a*2;
	set b=b*2;
end

删除和查看信息

删除语法：

drop procedure 存储过程名;

查看存储过程信息的语法：

show create procedure 存储过程名;

函数

函数和存储过程的概念基本上一样，都是把一组sql进行封装，最大的区别是存储过程的返回值有0个或多个，而函数的返回值有且仅有一个。

函数的创建语法如下：

create function 函数名(参数列表) returns 返回类型
begin
	函数体
end

注意事项：

• 参数列表包含两部分：参数名 参数类型
• 函数体中必须要有return语句
• 如果函数体中只有一句话，begin end 可以省略
• 需要使用delimiter语句设置结束标记

调用语法：

select 函数名(参数列表)

案例一，无参有返回

-- 创建一个函数，返回所有员工的个数
delimiter $
create function fun1() returns int
begin
	-- 创建一个变量
	declare c int default 0;
	-- 查询个数并为变量赋值
	select count(*) into c
	from empl;
	-- 返回
	return c;
end $

-- 调用
select fun1()$

案例二，有参有返回

-- 根据员工编号，返回员工工资
create function myfun2(emplNo int) returns double
begin
	-- 创建一个变量
	declare curSal double default 0.0; 
	-- 查询 并为变量赋值	
	select sal into curSal
	from empl
	where empl_id = emplNo;
	-- 返回
	return curSal;
end $

-- 调用
select myfun2(12342)$

查看函数的信息：

show create function 函数名;

删除函数：

drop function 函数名;