MySQL04-视图、存储过程和函数
MySQL04-视图、存储过程和函数
1.视图
-
视图的作用。简化复杂的SQL;保护数据的安全性,使用视图显示非核心的数据。
-
创建视图。
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;
- 查看视图。
select * from v_student_teacher;
desc v_student_teacher; -- 查看视图的字段信息
show create view v_student_teacher; -- 查看创建视图的SQL
- 修改视图的两种方式。
-- 修改视图的第一种方式,需要写全部的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;
- 不能通过修改视图中的数据来更新真实表数据的情况。
- 创建视图的SQL中包含聚合函数、group by、distinct、having、union、union all。
- select中包含查询。select (select * from tb_student)。
- 创建视图的SQL通过join或者,逗号连接。
- 常量视图。create or replact view v2 as select 'tom' as name。
- from后where后面引用了视图。where s.id in (select * from v_student)。
2. 存储过程
- 存储过程。一组预先编译好的SQL语句的集合。
- 存储过程的三种参数模式。
- in,该参数可以作为输入。
- out,该参数可以作为输出,返回值。
- inout,该参数既可以作为输入,也可以作为输出,返回值。
3.MySQL常用命令-存储过程
- 创建带输入参数和输出参数的存储过程。
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; -- 查看用户变量
- 创建带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;
- 存储过程的删除
drop procedure myp1; -- 删除myp1存储过程
- 存储过程的查看。
show create procedure myp2;
4.函数
- 函数和存储过程一样,都是预先编译好的SQL。
- 不同点。
- 存储过程可以有0个返回值,或者多个返回值;函数有且仅有1个返回值。
- 存储过程适合做批量的插入、批量的更新操作;函数常用来做查询操作。
5.MySQL常用命令-函数
- 函数的创建和调用。
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'); -- 函数的调用
- 函数的删除
drop function myf1;
- 查看函数
show create function myf1;
6.函数语法-case
- case等值判断。
case 变量|表达式|字段
when 要判断的值 then 返回的值或者语句;
when 要判断的值 then 返回的值或者语句;
else 返回的值或者语句;
end case;
- case条件判断。
case
when 要判断的条件 then 返回的值或者语句;
when 要判断的条件 then 返回的值或者语句;
...
else 返回的值或语句;
end case;
- 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.函数语法-三种循环
- 三种循环。while、loop和repeat。
- 循环控制。iterate类似于continue;leave类似于break。
- 三种循环的语法。三种循环都可以配合标签类进行循环控制。
-- while
标签: while 循环条件 do
循环体
end while 标签;
-- loop
标签: loop
循环体
end loop 标签; -- loop没有判断语句,相当与死循环。
-- repeat
标签: repeat
循环体
until 结束循环的条件
end repeat 标签; -- 相当于java中的do...while;至少执行一次
- 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;
- 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;