mysql数据库(视图/存储过程/触发器)

1. 视图

视图(view)是一个虚拟存在的表,视图的素具并不在数据库总实际存在,行和列数据来子自定义视图的查询中使用的表,并且是在使用视图是动态生成的
作用: 简化操作
可以让用户只能查询和修改他们所能看到的数据
视图可以帮助用户屏蔽真实表结构变化带来的影响

(1) 视图创建

-- 创建视图
create [or replace] view 视图名称[(列表名称)] as select 语句 [with[cascaded | local] check option]

-- 例如
create or replace view v1 as select name,price from tb_hotel

-- with cascaded check option 像视图操作的时候会检查数据, 会检查依赖视图(默认cascaded)
--with local check option 	递归依赖视图
create or replace view v1 as select name,price from tb_hotel with cascaded check option ;

(2) 查询视图

-- 查询视图创建语句 
show create view 视图名;

-- 查看视图数据
select 语句

-- 如:
show create view v1;
select * from v1;

(3) 修改视图

create or replace view 视图名称[(列名列表)] as select 语句 [with[cascaded | local] check option]
alter view 视图名称[(列名列表)] as as select 语句 [with[cascaded | local] check option]

(3) 删除视图

drop view if exists 视图名称

2 存储过程

存储过程: 事先经过编译并存储在数据库中的一段sql语句的集合,减少数据在数据库和服务器之间的传输,提高数据处理的效率
对sql进行封装,复用,可以接受参数,也可以返回数据,减少网络交互,效率提升

(1) 定义存储过程

create procedure 存储过程名称([参数列表])
begin
	--sql语句
end;

delimiter  $$ -- 设置结束符号

-- 例如
create procedure p1()
begin
    select count(*) from tb_hotel;
end;
-- 调用存储过程语法
call p1();

变量

系统变量: (全局变量 会话变量)

-- 查看系统变量
show [session | global] varibles [like '...']; 
select @@[session | global] 变量名称;

-- 设置系统变量
set [session | global] 变量名 = 值
set @@[session | global] 变量名 = 值

自定义变量:

-- 定义并复制变量
set @变量名 := 值
select @变量名 := 值

-- 查看变量
select @变量名

-- 查询复制
select 字段列表 into @变量名 from 表名

局部变量:

declare 变量名 变量类型 [default ..]
-- 赋值 
set 变量名 = 值
set 变量名 := 值;
select 字段 into 变量名 from 表名...;

-- 例如:
create procedure p1()
begin
    declare sum int default 10;
    select sum;
end;
call p1();

条件语句

if 条件1 then  -- 条件1满足执行
		sql语句
else if 条件2 then -- 条件二满足执行
	sql语句
else	-- 条件都不满足执行
	sql 语句
end if;

参数

in 输入参数
out 输出参数
inout 输入输出参数


-- 例如

create procedure coreP(in num int,out core varchar(10))
begin
    if  num >= 85 then
        set core := "优秀";
    elseif num >= 60 then
        set core := "及格";
    else
        set core := "不及格";
    end if;
end;
call coreP(59,@result);
select @result;

case

case 表达式
	when 条件匹配值1 then 操作
	when 提哦啊见匹配值2 then 操作
	else 操作
end case;
-- 语法二
case
	when 表达式1 then 操作
	when 表达式2 then 操作
	else 操作
end case;

循环语句

while循环:

while 条件 DO 
	sql 逻辑
end while

repea循环(相当于do…while()循环):

repeat
	sql 逻辑
	until 条件
end repeat

loop循环:(相当于for循环)

[label:] loop
	sql 逻辑
end loop [label]

leave label; -- 退出执行标记的循环体
iterate label 	-- 直接进入下一次循环

-- 例如计算单数的值
create procedure p5(in num int , out sum int) -- 计算单数的值
begin
    set sum = 0;
    sum : loop
        if num <=0 then
            leave sum;
        end if;
        if num % 2 != 0 then
            set num := num -1;
            iterate sum;
        end if;
        set sum := sum + num;
        set num := num -1;
    end loop sum;
end;
drop procedure p5;
call p5(100,@sum);
select @sum;

游标

声明游标

declare 游标名称 cursor for 查询语句;

打开游标:

open 游标名称

获取游标记录

fetch 游标名称 into 变量[,变量];

关闭游标:

close 游标名称;

条件处理程序(handler)

declare [continue | exit] handler for 
[sqlstate sqlstate_value | sqlwarning | not found | sqlexception]... statement;
continue: 继续执行当前程序
exit: 终止执行当前程序

sqlstate sqlstate_value: 状态吗
sqlwarning : 所有 01开头的状态码简写
not found : 所有02开头的状态码简写
sqlexception: 所有没哟被上面两个配捕获的简写

-- 例如,声明一个条件处理 程序,当sql状态码为02000的时候,退出程序,关闭游标 u -> 条件处理器可以写到任何条件表达式地方
declare exit handler for sqlstate  '02000' close u;

存储函数

存储函数就是有返回值的存储过程,存储函数的参数只能是in类型,

create function 函数名称([参数列表])
returns 返回值类型 [deterministic | no sql | reads sql data]
begin
	sql 语句
	return ...;
end


deterministic : 相同的输入参数总是产生相同的结果
no sql:不宝行sql语句
 reads sql data: 包含读取数据的语句,单不包含写入数据的语句

(2) 查看存储过程

-- 查询数据库中所有的存储过程
select * from information_schema.ROUTINES where ROUTINE_SCHEMA='数据库名称' ;

-- 查看创建存储过程的语句
show create procedure p1;

(3)删除存储过程

drop procedure if exists p1;

3. 触发器

在insert/update/delete之前或之后,触发执行触发器中的sql语句

(1) 创建触发器

create trigger 触发器名称
before/after insert/update/delete
on 表名 for each row
begin
	-- old 更新之前的数据,new 更新后的数据
	-- 比如获取更新前的id和更新后的id new.id old.id
	语句
end;

-- 例如创建
create trigger t1
after insert 
on tb_hotel for each row
begin
    insert into tb_hotel values ();
end;

(2) 查看触发器

show triggers;

(3) 删除触发器

drop trigger 触发器名称;