5 - 视图｜存储过程

视图

视图是虚拟存在的表 表头下的数据在真表里 表头下的数据存储在创建视图时 在select命令访问的真表里
优点：

• 安全
• 数据独立
• 简单 用户无需关心数据内部

视图基本使用

create view v1 as select name from tarena.user

除了视图，每个表都会存在两个文件

查看所有表的状态

show table status ; 

查看创建视图具体select命令

show create view 视图名;

使用视图

• insert

insert into tarena.v11 vlaues ("xxx",2002);

• delete

delete from tarena.v11 where name in ("xxx","yyy")

• update

# 物理表 会跟着变
update tarena.v11 set uid=1001 where name='root'

视图进阶

可以通过视图修改数据限制 限制方式如下：

• with check option
  • local 首先满足自身的限制，同时满足基表的限制
    local

mysql> create view v31 as select name , uid from tarena.user where uid <= 100;
mysql> create view v45 as select name,uid from v31 where uid>=10 with local check option;
mysql> update v45 set uid=800 where name="ftp"
# 虽然超出基表v31限制，但是还是改成了 因为基表没有加限制

# 修改视图v31 加修改限制
mysql> create or REPLACE view v31 as select name,uid from tarena.user where uid <= 100 with check option;
mysql> update v45 set uid=6 where name="sshd"; 没有满足自身限制
mysql> update v45 set uid=600 where name="sshd"; 没有满足基表v31的限制

• cascaded 满足视图自身限制即可（默认值）

mysql> create view  v21 as > select name,uid from tarena.user where uid>10> with check option# 此时的限制条件是uid>10  如果改小于10 会失败
mysql> update v21 set uid=9 where name='root'; # 会失败

存储过程

存储过程就是mysql服务里的脚本。mysql存储过程 是登陆mysql服务之后编写的脚本 是由一条或多条sql命令组成，存放在mysql库下的表里。避免重复的sql操作。

写好的存储过程 会存放在mysql库下的proc表里

创建存储过程

命令行结束符 修改为//
delimiter //
---
create procedure 库名.存储过程名(列表参数)
begin一组合法的sql命令
end 
//
--
delimiter ;

查看已创建的存储过程

select db,name,type,body from mysql.proc where type="procedure"

执行存储过程

call 库名.存储过程名  创建的存储过程没有参数
call 库名.存储过程名(参数) 

删除存储过程

drop procedure 库.存储过程名; 不加括号 无论有无参数

存储过程进阶

用户自定义变量

• 用户变量
  

• 局部变量：begin/end语句块中
  

存储过程参数

调用参数时，名称前也不需要@
create procedure 名称(类型 变量名 数据类型 , 类型 变量名 数据类型,…)

delimiter //
create procedure tarena.p3()
begin
select name from tarena.user where name="ftp";
end //
delimiter ;

delimiter //
create procedure tarena.p4(in dept_no int)
begin
select dept_id,count(name) from employees where dept_id=dept_no
group by dept_id
end //
delimiter;## 调用
call tarena.p4(3)

• out参数的作用

delimiter //
create procedure tarena.p5(in emp_name varchar(10),out mail varchar)
begin
select email into mail from employees where name=emp_name;
end //
delimiter ;# 调用
call tarena.p5("bob",@x)

• inout参数
  既有in参数的功能 又有out参数的功能

delimiter //
create procedure tarena.myadd(inout i int)
begin
set i=i+100;
end //
delimiter ;
set @x = 8
set @y = 101
call tarena.myadd(@x);
call tarena.myadd(@y);
select @x,@y

循环结构

• if
  格式一：

IF 条件 THEN语句;
END IF;

格式二：

IF 条件 THEN语句1;
ELSEIF 条件2 THEN语句2;
ELSE语句3;
END IF;

解决乱码问题：

alter database tarena default character set utf8;
# 查看库使用的字符集
show create detabase tarena \G
show create table tarena.departments \G
# 修改完重新创建存储过程 就好使了

• case
  语法格式

CASE 变量｜表达式｜字段
WHEN 判断的值1 THEN 返回值1;
WHEN 判断的值2 THEN 返回值2；
... ...
ELSE 返回值n;
END CASE;

案例

delimiter //
create procedure tarena.deptype_pro2(IN no int ,OUT dept_type varchar(5))
begin
declare type varchar(5);
select dept_name into type from departments where dept_id=no;
case type
when '运维部' then set dept_type='技术部';
else set dept_type='非技术部';
end case;
end //
delimiter ;
# @使用自定义变量接受out参数的值
call tarena.deptype_pro(1,@t);
# 查看自定义变量@t的值
select @t;

• 循环语法

标签名：WHILE 循环条件 DO循环体;
END WHILE 标签名

# loop循环 只要不人为结束 就一直执行下去
LOOP循环体;
END LOOP
# repeat 循环：至少循环一次
repeat循环体;
UNTILE 循环结束条件
end repeat

循环结构控制语句：在循环条件满足的情况下，控制循环的执行

• break 结束循环
• ITERATE 相当于continue 结束当前循环并开始下次循环
  注意：要给循环加标签 （就是给循环起一个名字）才能使用
  案例：

delimiter //
create procedure tarena.while_pro(IN i int)
begin
declare j int default 1;
while j < i doinsert into tarena.departments(dept_name) values("hr");set j=j+1;
end while;
end //
delimiter ;
# 调用
mysql > call tarena.while_pro(3);

show processlist 相当于 ps -aux

案例

delimiter //
create procedure tarena.while_pro3(IN i int)
begindeclare j int default 0;a:while j<i doset j=j+1;if mod(j,2)=0 then iterate a;end if ;insert into tarena.departments(dept_name) value(concat('hr',i))end while a;
end //
delimiter ;
# 调用
call tarena.while_pro3(10);
select * from tarena.departments;