Mysql基础：视图、变量、存储过程与函数

视图

含义： 虚拟表，和普通表一样使用。mysql15.1版本出现的新特性，是通过表动态生成的数据。只保存sql逻辑，不保存查询结果。

作用：

• 重用sql语句
• 简化复杂sql操作，不必要知道查询细节
• 保护数据，提高安全性

视图创建示例：

# 原始sql
select 表1名称.列名称, 表2名称.列名称 from 表1名称 inner join 表2名称 on 表1名称.列名称 = 表2名称.列名称;

# 创建视图
create view 视图名 as select 表1名称.列名称, 表2名称.列名称 from 表1名称 inner join 表2名称 on 表1名称.列名称 = 表2名称.列名称;

# 查询视图
select * from 视图名 where 条件;

视图修改示例：

create or replace view 视图名 as 新的sql语句;  -- 方式一
alter view 视图名 as 新的sql语句;  -- 方式二

视图删除示例：

drop view 视图名;

查看视图结构：

desc view 视图名;

视图的更新：

insert into 视图名 values(列名, ...);
update 视图名 set 列名=值, ...;
delete from 视图名 where 条件;

注意： 视图的更新也会更新原始表, 但是有以下条件将不能更新原始表

• 如果视图的SELECT目标列包含聚集函数，则不能更新
• 如果视图的SELECT子句使用了unique或distinct，则不能更新
• 如果视图中使用了group by子句，则不能更新
• 如果视图中包括经算术表达式计算出来的列，则不能更新
• 如果视图是由单个表的列构成的，但并没有包括主键，则不能更新

变量

可分为
系统变量：全局变量(global)、会话变量(session)
自定义变量：用户变量、局部变量
接下来分别介绍

(一) 系统变量

系统变量由系统提供，不是用户定义，属于服务器层面
语法：

# 查询系统变量
show global|session variables;  -- 查看所有的系统变量
show global|session variables like 条件;  -- 查看部分的系统变量
select @@global|session.系统变量名;  -- 查看某个系统变量
# 给系统变量赋值
set global|session 系统变量名=值;
set global|session.系统变量名=值;

注意： 全局变量作用域针对于所有的会话，并且重启之后会初始化为默认值（如果不想重启恢复为默认值则需要修改配置文件）

(二) 自定义变量

用户变量

作用域：针对当前会话（连接）有效，同于会话变量的作用域
在 begin end 内外都有效
语法：

# 赋值操作(同样可以用于更新)
set @用户变量名=值;
set @用户变量名:=值;
select @用户变量名:=值;
select @用户变量名 into 值 from 表名 where 条件;

# 查看操作
select @用户变量名;

局部变量

作用域： 仅在定义它的begin end 中有效
应用在 begin end 中的第一句话
语法：

# 声明赋值操作
declare 变量名 值;
declare 变量名 值 default 值;

## 更新操作
set 变量名=值;
set 变量名:=值;
select @变量名:=值;
select 用户变量名 into 值 from 表名 where 条件;

# 查看操作
select @变量名;

存储过程和函数

含义： 一组预先编译好的sql语句的集合，理解成批处理语句
作用： 1. 提高代码重用性
2. 简化操作
3. 减少编译次数并且减少与数据库服务器连接的次数
存储过程：可以有0或多个返回，适合做批量插入、批量更新
函数：仅有一个返回，适合做处理数据后返回一个结果
接下来分别介绍

(一) 存储过程

语法：

# 创建语法
DELIMITER $;  -- 声明语句结束符，可以自定义
create procedure 存储过程名(参数列表)
begin
        存储过程体(一组sql语句)
end $  -- 使用语句结束符

# 调用语法
call 存储过程名(参数)

# 删除语法
drop procedure 存储过程名;

# 查看存储过程的信息
desc 存储过程名;

参数列表包含三部分：参数模式 参数名 参数类型

参数模式：
IN：可作为输入
OUT：可作为输出
INOUT：既可以作为输入也可作为输出

(二) 函数

语法：

# 创建语法
create function 函数名(参数列表) returns 返回类型
begin
        函数体
        return 返回值;
end;

# 调用语法
select 函数名(参数)

# 查看函数
show create function 函数名;

# 删除函数
drop function 函数名;

参数列表包含两部分：参数名 参数类型

(三) 流程控制结构

分为：
顺序结构
分支结构
循环结构

分支结构

if函数

if(表达式1，表达式2，表达式3)

执行顺序：如果表达式1成立，返回表达式2，否则返回表达式3
case结构

case 变量|表达式|字段
when 判断条件1 then 返回值1;
when 判断条件2 then 返回值2;
...
else 返回的值;
end case;

if结构
应用在 begin end 中

if 条件1 then 语句1;
elseif 条件2 then 语句2;
...
else 语句;
end if;

循环结构

while、loop、repeat

[标签] while 循环条件 do
        循环体;
end while [标签];

# 死循环loop
[标签] loop
        循环体;
end loop [标签];

[标签] repeat
        循环体;
until 结束循环的条件
end repeat [标签];