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;
  1. 查看视图。
select * from v_student_teacher;

desc v_student_teacher; -- 查看视图的字段信息
		
show create view v_student_teacher; -- 查看创建视图的SQL
  1. 修改视图的两种方式。
-- 修改视图的第一种方式,需要写全部的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;
  1. 不能通过修改视图中的数据来更新真实表数据的情况。
    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; -- 查看用户变量
  1. 创建带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;
  1. 存储过程的删除
drop procedure myp1; -- 删除myp1存储过程
  1. 存储过程的查看。
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'); -- 函数的调用
  1. 函数的删除
drop function myf1;
  1. 查看函数
show create function myf1;

6.函数语法-case

  1. case等值判断。
case 变量|表达式|字段
    when 要判断的值 then 返回的值或者语句;
    when 要判断的值 then 返回的值或者语句;
    else 返回的值或者语句;
end case;
  1. case条件判断。
case
    when 要判断的条件 then 返回的值或者语句;
    when 要判断的条件 then 返回的值或者语句;
    ...
    else 返回的值或语句;
end case;
  1. 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;至少执行一次
  1. 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;
  1. 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;
posted @ 2021-10-31 14:31  行稳致远方  阅读(25)  评论(0)    收藏  举报