MySQL04-视图、存储过程和函数

MySQL04-视图、存储过程和函数

1.视图

1. 视图的作用。简化复杂的SQL；保护数据的安全性，使用视图显示非核心的数据。

2. 创建视图。

create view v_student_teacher as
select s.id sid,s.name sname,s.class_id scid,t.name tname 
from tb_student s left join tb_teacher t
on  s.teacher_id = t.id;

3. 查看视图。

select * from v_student_teacher;

desc v_student_teacher; -- 查看视图的字段信息
		
show create view v_student_teacher; -- 查看创建视图的SQL

4. 修改视图的两种方式。

-- 修改视图的第一种方式，需要写全部的SQL，不能直接修改部分的SQL。
-- 如果视图存在就replace，不存在就create。
create or replace view v_student_teacher as select s.* 
from tb_student s left join tb_teacher t
on  s.teacher_id = t.id;

-- 修改视图的第二种方式，需要写全部的SQL，不能直接修改部分的SQL。
alter view v_student_teacher as select s.* 
from tb_student s left join tb_teacher t
on  s.teacher_id = t.id;

5. 不能通过修改视图中的数据来更新真实表数据的情况。
   1. 创建视图的SQL中包含聚合函数、group by、distinct、having、union、union all。
   2. select中包含查询。select (select * from tb_student)。
   3. 创建视图的SQL通过join或者,逗号连接。
   4. 常量视图。create or replact view v2 as select 'tom' as name。
   5. from后where后面引用了视图。where s.id in (select * from v_student)。

2. 存储过程

1. 存储过程。一组预先编译好的SQL语句的集合。
2. 存储过程的三种参数模式。
   1. in，该参数可以作为输入。
   2. out，该参数可以作为输出，返回值。
   3. inout，该参数既可以作为输入，也可以作为输出，返回值。

3.MySQL常用命令-存储过程

1. 创建带输入参数和输出参数的存储过程。

create procedure myp3(in name varchar(20),out id int)
begin
	select s.id into id from tb_student s where s.name = name;
end;
	
call myp3('alice',@id); -- 存储过程的调用
select @id; -- 查看用户变量

2. 创建带inout参数的存储过程。

create procedure myp4(inout a int,inout b int)
begin
	set a=a*2;
	set b=b*2;
end;
	
set @m=10; -- 初始化并赋值用户变量
set @n=20; -- 初始化并赋值用户变量
call myp4(@m,@n);
select @m,@n;

3. 存储过程的删除

drop procedure myp1; -- 删除myp1存储过程

4. 存储过程的查看。

show create procedure myp2;

4.函数

1. 函数和存储过程一样，都是预先编译好的SQL。
2. 不同点。
   1. 存储过程可以有0个返回值，或者多个返回值；函数有且仅有1个返回值。
   2. 存储过程适合做批量的插入、批量的更新操作；函数常用来做查询操作。

5.MySQL常用命令-函数

1. 函数的创建和调用。

create function myf2(name varchar(20)) returns int 
begin 
	declare c int default 0;
	select count(*) into c from tb_student s where s.name like concat('%',name, '%');
	return c;
end;

select myf2('t'); -- 函数的调用

2. 函数的删除

drop function myf1;

3. 查看函数

show create function myf1;

6.函数语法-case

1. case等值判断。

case 变量|表达式|字段
    when 要判断的值 then 返回的值或者语句;
    when 要判断的值 then 返回的值或者语句;
    else 返回的值或者语句;
end case;

2. case条件判断。

case
    when 要判断的条件 then 返回的值或者语句;
    when 要判断的条件 then 返回的值或者语句;
    ...
    else 返回的值或语句;
end case;

3. case函数。

create procedure myp3(in num int) 
begin
	case
		when num>100 then select 'a';
		when num>50 then select 'b';
		else select 'c'; -- 默认情况
	end case;
end;
			
call myp3(100);

7.函数语法-if

create function myf4(num int) returns varchar(10)
begin
	if num>100 then return 'a';
	elseif num>50 then return 'b';
	elseif num>30 then return 'c';
	else return 'd';
	end if;
end;
		
select myf4(1);

8.函数语法-三种循环

1. 三种循环。while、loop和repeat。
2. 循环控制。iterate类似于continue；leave类似于break。
3. 三种循环的语法。三种循环都可以配合标签类进行循环控制。

-- while
标签: while 循环条件 do
	循环体
end while 标签;

-- loop
标签: loop
	循环体
end loop 标签; -- loop没有判断语句，相当与死循环。

-- repeat
标签: repeat
	循环体
	until 结束循环的条件
end repeat 标签; -- 相当于java中的do...while；至少执行一次

4. while+leave。

-- 直插入20条数据
create procedure myp6(in num int)
begin
	declare i int default 1;
	a:while i <= num do
		insert into db_test.tb_student(name,class_id)values(concat('a01', i),10);
		
		if i > 20 then  leave a; -- i > 20跳出循环
		end if;
		
		set i = i + 1;
	end while a;
end;

5. while+iterate。

create procedure myp6(in num int)
begin
	declare i int default 1;
	a:while i <= num do
        set i = i + 1;

        -- 奇数跳过本次循环，进入下一次循环。
        if mod(i,2) != 0 then iterate a;
        end if;

        insert into db_test.tb_student(name,class_id)values(concat('a01', i),10);
	end while a;
end;