004-mysql 之视图 - 触发器 - 存储过程 - 函数

mysql 之 视图 - 触发器 - 存储过程 - 函数

1、View 视图

视图：视图是一个虚拟表，它存储一个引用的查询集。

1.1 View 语法

create or replace view：创建一个新的视图。
alter view：修改一个视图。
drop view：删除一个视图。
 
官方文档：
create：https://dev.mysql.com/doc/refman/5.7/en/create-view.html
alter：https://dev.mysql.com/doc/refman/5.7/en/alter-view.html
drop：https://dev.mysql.com/doc/refman/5.7/en/drop-view.html

---

【示例】
create view t_view as select id,name,age from user;
create view t_view2(vid,vname,vage) as select id,name,age from user;
alter view t_view2(v_id,v_name) as select id,name from user;
drop view if exists t_view2;
【注意】
如果没有控制视图的操作权限，则用户还可以对视图进行除了查询以外的其他操作，如：insert into t_view(name,age) values(‘libai’,30);

---

具备以下特点的视图不允许更新：
① 创建视图时的 sql 语句，包含以下关键字的：
  distinct、group by、having、union、union all。
  示例：
  create or repalce view t_view as
  select max(salary) s,department_id from employees group by department_id;
② 常量视图
  示例：
  create or replace view t_view as select ‘tom’ name;
③ select 中包含子查询
  create or replace view t_view as
  select department_id,(select max(salary) from employess) max_salary from departments;
④ select 中使用了 join
  create or replace view t_view as select as
  select last_name,department_name from employees e join departments d on e.department_id = d.department_id;
⑤ from 一个不能更新的视图
  create or replace view t_view as select as
  select * from t_view2; # 这里 t_view2 也是视图，且不能进行更新操作
⑥ where 子句的子查询引用了 from 子句中的表
  create or replace view t_view as select as
  select last_name,email,salary from employees where employee_id in (
    select manager_id from employeess where employees where manager_id is not null
  );

2、Trigger 触发器

触发器：触发器是与表相关联的数据库命名对象，当表发生特定事件时，它会被激活，比如插入或更新。

2.1 Trigger 语法

create trigger：创建触发器。
drop triggr：删除触发器。
 
官方文档
create：https://dev.mysql.com/doc/refman/5.7/en/create-trigger.html
drop：https://dev.mysql.com/doc/refman/5.7/en/drop-trigger.html

---

【示例】
create trigger t_trigger after insert on user for each row
begin
insert into student(name,age) values(‘zhangsan’,50);
end;
 
drop TRIGGER if exists t_trigger;

3、Procedure 存储过程

存储过程是使用 CALL 语句调用的。一个过程没有返回值，但是可以修改它的参数以供调用者稍后检查。它还可以生成返回给客户端程序的结果集。

3.1 Procedure 语法

create procedure：创建存储过程。
alter procedure：修改存储过程。
drop procedure：删除存储过程。
 
官方文档
create：https://dev.mysql.com/doc/refman/5.7/en/create-procedure.html
alter：https://dev.mysql.com/doc/refman/5.7/en/alter-procedure.html
drop：https://dev.mysql.com/doc/refman/5.7/en/drop-procedure.html
 
存储过程中声明变量：declare 变量名 类型;
给变量赋值：
1）声明时就赋值：declare age tinyint default 0;
2）声明完再赋值：

declare age tinyint;
set age=0;

# 【示例】
# 创建
create procedure p_pro(u_name varchar(30),u_age tinyint,out msg varchar(30))
BEGIN
	insert into user(name,age) values(u_name,u_age);
	set msg='插入成功';
end;

# -----------------------------------------------------------------------------
# 调用
call p_pro('lisi',27,@msg);
select @msg;	# 获取存储过程输出

# 或者
set @u_name = 'lisi';
set @u_age = 30;
call p_pro(@u_name,@u_age,@msg);
select @u_name;	# 获取存储过程输入

# -----------------------------------------------------------------------------
# 删除
drop PROCEDURE if exists p_pro;

3.2 if 语句

# if then ... end if
create procedure p_test(name varchar(20),age tinyint,out msg varchar(30))
begin
	if age > 0 then
		insert into student values(0,name,age);
		set msg = '插入成功';
	end if;
end;

# if then ... else ... end if
create procedure p_test(name varchar(20),age tinyint,out msg varchar(30))
begin
	if (age > 0 and CHAR_LENGTH(name) > 5) or CHAR_LENGTH(name) < 3 then
		insert into student values(0,name,age);
		set msg = '插入成功';
	ELSE
		set msg = '插入失败';
	end if;
end;

# if then ... elseif then ... end if
create procedure p_test(name varchar(20),age tinyint,out msg varchar(30))
begin
	if age > 0 and CHAR_LENGTH(name) > 5 then
		insert into student values(0,name,age);
		set msg = '插入成功，长度大于5';
	ELSEIF CHAR_LENGTH(name) < 3 then
		set msg = '插入成功，长度小于3';
	ELSE
		set msg = '插入失败';
	end if;
end;

# 调用
call p_test('tomcat',10,@msg);
select @msg as info;

3.3 case 语句

create procedure p_temp(type varchar(30))
begin
	case type 
		when '中国' then 
			select concat('==我是来自',type) as info;
		when '米国' then 
			select concat('--我是来自',type) as info;
		else 
			select concat('我是来自外太空') as info;
	end case;
end;

# 调用
call p_temp('中国');

3.4 while 语句

create procedure p_wh(i int)
begin
	declare num int;
	set num=3;
	while i>3 do
		insert into student values(0,concat('==',i),10);
		set i=i-1;
	end while;
end;

# 调用
call p_wh(6);

3.5 loop 语句

create procedure p_loop(i int)
begin
	test_loop:loop			# 别名:loop
		if i > 3 then
			insert into student values(0,concat('=-=-=',i),25);
			set i=i-1;
		else
			leave test_loop;	# leave 表示结束循环
		end if;
	end loop;
end;

# 调用
call p_loop(6);

create procedure p_loop2(i int,out msg varchar(30))
begin
	test_loop:loop			# 别名:loop
		if i > 3 then
			insert into student values(0,concat('=-AA=-=',i),25);
			set i=i-1;
		else
			select name into msg from student where id = 19;	# 将 select 查询的结果放到 into 后的 msg 中
			leave test_loop;	# leave 表示结束循环
		end if;
	end loop;
end;

# 调用
call p_loop2(6,@msg);
select @msg;

4、Function 函数

4.1 Function 语法

create function：创建函数。
alter function：修改函数。
drop procedure：删除函数。
 
官方文档
create：https://dev.mysql.com/doc/refman/5.7/en/create-procedure.html
alter：https://dev.mysql.com/doc/refman/5.7/en/alter-procedure.html
drop：https://dev.mysql.com/doc/refman/5.7/en/drop-procedure.html

# 【示例】
# 创建
create function f_func(u_name varchar(30),u_age tinyint)
returns varchar(30)	# 返回类型
BEGIN
	insert into user(name,age) values(u_name,u_age);
	return '插入成功';
	# return (select name from user where id = 9);
end;

# 调用
select f_func('wangwuu',43)
# 或
select f_func('wangwuu',43) as msg