MySQL视图/存储器/触发器

我是蒟蒻你别怕

已于 2022-08-31 19:36:37 修改

阅读量468

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 sql

于 2022-08-31 17:46:40 首次发布

本文链接：https://blog.csdn.net/qq_51591771/article/details/126620466

版权

MySQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

菜鸡自用

orz

视图

视图的定义

视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。

引入视图的原因

视图是存储在数据库中的查询的SQL 语句，它主要出于两种原因：

安全原因，视图可以隐藏一些数据，如：社会保险基金表，可以用视图只显示姓名，地址，而不显示社会保险号和工资数等，

另一原因是可使复杂的查询易于理解和使用。

或者可以数据独立，帮助用户屏蔽真实表结构变化带来的影响

视图的基础语法

#创建视图的语法
create [or replace] view 视图名称[列名列表] as select 语句 [with [cascaded/ local] check option];

#查询
#查看创建视图的语句
show create view 视图名称;

#查看视图数据 
select * from 视图名称;

#需改
1. create [or replace] view 视图名称[列名列表] as select 语句 [with [cascaded/ local] check option];

2.alter view 视图名称[列名列表] as select 语句 [with [cascaded/ local] check option];


#删除
drop view if exists 视图名称;

#创建视图
create or replace view stu_v_1 as select id, name from student where id <= 20 with cascaded check option;

select * from stu_v_1;

insert into stu_v_1 values(6, "tom"); //可插入
insert into stu_v_1 values(30, "tom"); //不可插入 30 > 20

检查选项(cascaded, local)

当使用WITH CHECK OPTION子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如插入，更新，删除，以使其符合视图的定义。 MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysql提供了两个选项：CASCADED 和 LOCAL ，默认值为 CASCADED 。

两个如果依赖上一个视图，就要递归上去。

1.cascade

#表1
create or replace view stu_v_1 as select id, name from student where id <= 20;

insert into stu_v_1 values (10, "tom"); //可插入，没有检查选项

insert into stu_v_1 values (20, "tom"); //可插入，没有检查选项


#表2
create or replace view stu_v_2 as select id, name from stu_v_1 where id >= 10;

insert into stu_v_2 values (7, "tom"); //不可插入，检查选项

insert into stu_v_2 values (26, "tom"); //不可插入，检查选项，同时给上面的表赋予with cascaded check option;

insert into stu_v_2 values (15, "tom"); //可插入，检查选项


#表3
create or replace view stu_v_3 as select id, name from stu_v_2 where id >= 15;

insert into stu_v_3 values (17, "tom"); //可插入，检查选项表2和表1

insert into stu_v_1 values (7, "tom"); //可插入，检查选项表2不满足

2.local

向上递归需要依赖的视图，检查选项只作用于本条语句。

#表1
create or replace view v1 as select id,name from students where id <= 20;

insert into v1 values(21,'john');//插入成功，没有检查选项

#表2
#创建一个基于表1的视图，并添加local检查选项
create or replace view v2 as select id,name from v1 where id > 10 with local check option;

#添加检查选项后，再插入数据，MySQL就会判断插入数据是否满足条件，
insert into v2 values（22，'lucy');//检查选项，满足，向上递归

#表2
#创建一个基于表2的视图
create or replace v3 as select id,name from v2 where id <= 15;

#由于v3没有添加检查选项，但v3是基于v2的，所以现在可以插入的id值依然为 id > 10。
insert into v3 values(18,'Tom');#插入成功

视图的更新

存储过程

阿里开发规范禁用存储过程，看自己来吧....

简介

型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来调用存储过程。. 简单的说就是专门干一件事一段sql语句可以由数据库自己去调用，也可以由java程序去调用。在oracle数据库中存储过程是procedure

基本语法

#创建存储过程
create procedure p1()
begin
		select count(*) from `user`;
end;

#调用存储过程
call p1();

#查看
select * from information_schema.ROUTINES WHERE ROUTINE_SCHEMA= "reggie" 

show create procedure p1;

#删除
drop PROCEDURE if EXISTS p1;

查看的展示

小tips

在命令行中，执行创建存储过程的SQL时，需要通过delimiter指定SQL语句结束符.

变量

1.系统变量

系统变量是MySQL提供的，不被用户定义属于服务器层面，分为全局变量BLOBAL，会话变量Session

//查看系统变量
show session/global variables //查看所有系统变量

show session/global variables like “%” //模糊匹配查询

select @@session/global 系统变量名//精确查找

//设置系统变量
set session/global 系统变量名 = 值
set @@session/global 系统变量名 = 值

Tips:没有指定session/global，默认是session

mysql服务器重启后，所设置的全局参数会失效，如果不想失效，去/etc/my.cnf配置

2.用户变量

用户定义变量是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用“@变量名”使用就可以。其作用域为当前连接。用户定义变量是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用“@变量名”使用就可以.其作用域为当前连接.

@ABC,ABC未声明，可以使用，默认是NULL

#赋值
set @yname = "scy";

set @yage := 10;

set @ygender := '男', @yhobby := 'java';

select @mycolor := 'red';
select count(*) into @mycount from tb_user;


@使用
select @mycolor, mycount;

3.局部变量

局部变量是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的BEGIN....END块。

create PROCEDURE p2()
BEGIN
 DECLARE stu_count int default 0;
 select count(*) into stu_count from user;
 select stu_count;
end;

call p2();

参数

1.if

没参数，感觉有点像语法，所以加里面

#根据分数，返回等级

create procedure p3()
begin
    declare score int default 58;
    declare result varchar(10);


    if score >= 85 then
            set result := "优秀";

    elseif score >=60 then
            set result := "及格";
    else   
            set result := "不及格"

    end if
    select result;
end;


call p3();

2.in, out , inout

输入，输出，可输入可输出

#1
create procedure p4(int score int , out result varchar(10))

begin
    if score >= 85 then
            set result := "优秀";

    elseif score >=60 then
            set result := "及格";
    else   
            set result := "不及格"
    end if
end;


call p4(46, @result);


select @result;
    

#2 二百分转成百分制

create procedure p5(inout score double)
begin 
    set score := score * 0.5;
end;

set @score = 120;
call p5(@score);
select @score;

3.case

根据月份判断季度，没什么意思，直接上图了

4.while

create procedure p7(in n int)
begin
	declare total int default 0;
	while n>0 do
				set total := total + n;
				set n := n - 1;
	end while;
	select total;
end;

call p7(10);

5.repeat

满足条件退出循环和上面的区分一下就行。

6.loop

loap实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用来实现死循环，loop可以配和以下两个语句.

leave：配和循环，退出循环

iterate:跳过当前循环，直接进入下一次循环

没什么意义，到时候看笔记就行

7.cursor

改天补上

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

#打开游标
open 游标名称;

#打开游标记录
fetch 游标名称 into 变量，变量;

#关闭游标
close 游标名称;

create procedure p11(int uage int)
begin
    declear uname varchar(100); //先声明普通变量，在声明游标
    declear upro varchar(100);
    declear u_cursor cursor for select name, profession from tb_user where age <= uage;
    
    drop table if exists tb_user_pro;

    create table if not exists tb_user_pro(
                id int primary key auto_increment;
                name varchar(100),
                profession varchar(100)
    );


    open u_cursor;
    
    with true do
        fetch u_cursor into uname, upro;
        
        insert into tb_user_pro values(null, uname, upro);
    end while;
    
    close u_cursor;

end;

Handler

异常来跳出循环，多少优点抽象。。。

create procedure p11(int uage int)
begin
    declear uname varchar(100); //先声明普通变量，在声明游标
    declear upro varchar(100);
    declear u_cursor cursor for select name, profession from tb_user where age <= uage;//声明游标
    declare exit handler for SQLSTATE '02000' close u_cursor;
    
    drop table if exists tb_user_pro; //检查有无表

    create table if not exists tb_user_pro(//创建表
                id int primary key auto_increment;
                name varchar(100),
                profession varchar(100)
    );


    open u_cursor; //开启游标
    
    with true do //遍历赋值给表
        fetch u_cursor into uname, upro;
        
        insert into tb_user_pro values(null, uname, upro);
    end while;
    
    close u_cursor; //关闭游标

end;

存储函数

没什么意思，看看就行

触发器

触发器是与表有关的数据库对象，指在insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。

使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发

栗子：一条update更新了5条语句，那触发器就执行5次，这就是行级触发器，语句级的话就执行一次。

触发器类型

INSERT型触发器：NEW表示将要或者已经新增的数据

UPDATE型触发器：OLD表示修改之前的数据,NEW表示将要或者已经修改后的数据

DELETE型触发器：OLD表示将要或者已经修改后的数据

1.insert

#创建表
create table user_logs(
	id int(11)not null auto_increment,
	operation varchar(20) not null comment'操作类型，insert/update/delete' ,
	operate_time datetime not null comment "操作时间",
	operate_id int(11) not null comment '操作的ID',
	operate_params varchar(500) comment '操作参数',
	primary key(id )
)engine=innodb default charset=utf8;


#创建触发器
create trigger tb_user_insert_trigger
	after insert on users for each ROW
begin
	insert into user_logs (id,operation,operate_time,operate_id,operate_params)
	VALUES
	(1,'insert', now(), new.id,concat('插入的数据内容为: id=' , new.id ,' , name=' , new.username));
end;		 

#查看触发器
show triggers;

#删除触发器
drop trigger tb_user_insert_trigger

#插入结果
insert into users VALUES (3, "张三", '123242');

#查看表的创建语句
show create TABLE users

select * from user_logs

触发器展示

结果展示

2.update

#创建表
create table user_logs(
	id int(11)not null auto_increment,
	operation varchar(20) not null comment'操作类型，insert/update/delete' ,
	operate_time datetime not null comment "操作时间",
	operate_id int(11) not null comment '操作的ID',
	operate_params varchar(500) comment '操作参数',
	primary key(id )
)engine=innodb default charset=utf8;


#创建触发器
create trigger tb_user_update_trigger
	after update on users for each ROW
begin
	insert into user_logs (id,operation,operate_time,operate_id,operate_params)
	VALUES
	(null,'update', now(), new.id, concat('更新之前的数据内容为: id=' , old.id ,' , name=' , old.username, '更新之后的数据内容为: id=' , new.id ,' , name=' , new.username));
end;

update users set username = "张某某" where id = 1;

3.delete

create trigger tb_user_delete_trigger
	after delete on users for each ROW
begin
	insert into user_logs (id,operation,operate_time,operate_id,operate_params)
	VALUES
	(null,'update', now(), old.id, concat('更新之前的数据内容为: id=' , old.id ,' , name=' , old.username));
end;

delete from users where id = 1