视图｜存储过程
- 视图
- 视图基本使用
- 使用视图
- 视图进阶
- 存储过程
- 创建存储过程
- 存储过程进阶
- 存储过程参数
- 循环结构
视图
视图是虚拟存在的表 表头下的数据在真表里 表头下的数据存储在创建视图时 在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
- 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 do
insert 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)
begin
declare j int default 0;
a:while j<i do
set 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;
