视图、存储过程、触发器

最新推荐文章于 2024-06-29 14:10:39 发布

芝麻干

最新推荐文章于 2024-06-29 14:10:39 发布

阅读量623

点赞数 1

文章标签： sql 数据库 mysql

本文链接：https://blog.csdn.net/qq_59212867/article/details/133894440

版权

视图

视图(View) 是一种虚拟存在的表。视图中的数据并不在数据库中实际存在, 行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。视图值保存了查询的SQL逻辑，不保存查询结果。

1. 视图基本操作

-- 创建视图
create view stu_v_1 as select id,name from student where id <=20;

-- 查看视图创建语句
show CREATE view  stu_v_1

-- 修改视图
CREATE or REPLACE view stu_v_1 as SELECT * from student;
alter view stu_v_1 as SELECT * from student;

-- 删除视图
drop view stu_v_1;

2. 视图检查选项CASCADED

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

CASCADE: 不管关联视图的是否开启视图检查,都需要判断是否满足插入条件

SELECT * from student;

create view v1 as SELECT id,name from student where id <=20;
insert into v1 VALUES (5,'Tom');
insert into v1 VALUES (25,'Tom');

create view v2 as SELECT id,name from v1 where id >=10 with CASCADED check OPTION;
insert into v2 VALUES (6,'Tom');
insert into v2 VALUES (11,'Tom');
-- id为21插入失败的原因是，v2视图是通过v1视图查询出来的，且用到了cascaded（级联）,不仅需要检测v2的条件，还要检测v1的条件
insert into v2 VALUES (21,'Tom');

create view v3 as SELECT id,name from v2 where id >=15;
insert into v3 VALUES (16,'Tom');
insert into v3 VALUES (7,'Tom');
-- id为22插入v3失败的原因是，v3视图是通过v2视图查询出来的,需要检测v2的条件,且v2又和v1有级联，所以v1,v2,v3三个视图的条件都要进行检测
insert into v3 VALUES (22,'Tom');

LOCAL:和CASCADE不同的是，进行视图检查时,会去看关联的表有没有开启视图检查,如果没有开启视图检查，则不需要判断关联表的条件

create view v4 as SELECT id,name from student where id <=20;
insert into v4 VALUES (5,'Tom');
insert into v4 VALUES (25,'Tom');

create view v5 as SELECT id,name from v4 where id >=10 with CASCADED LOCAL OPTION;
insert into v5 VALUES (6,'Tom');
-- 这里的id=11,如果是local的话,就可以进行插入,因为虽然关联了v4,但v4没有开启视图检查,所以只需满足v5视图的条件即可
insert into v5 VALUES (11,'Tom');

create view v6 as SELECT id,name from v2 where id >=15
insert into v6 VALUES (16,'Tom');
-- 这里的id=22同样可以插入,因为虽然关联了v5,v5虽然有视图检查,但是id满足条件,且v5关联的v4没有开启视图检查,所以只需满足v5,v6视图的条件即可
insert into v6 VALUES (22,'Tom');

3. 视图的更新

存储过程

1. 基本语法

-- 创建存储过程
CREATE PROCEDURE p1()
BEGIN
	
	SELECT count(*) from tb_user;
	
END

-- 调用
call p1()

-- 查看
SELECT * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'itcast' 
show create PROCEDURE p1;

-- 删除
DROP PROCEDURE IF EXISTS P1;

2. 变量

变量主要分为三种：系统变量、用户自定义变量和局部变量。

系统变量

-- 查看系统变量
show session variables ;

show session variables like 'auto%';
show global variables like 'auto%';

select @@global.autocommit;
select @@session.autocommit;


-- 设置系统变量
set session autocommit = 1;

insert into course(id, name) VALUES (6, 'ES');

set global autocommit  = 0;

select @@global.autocommit;

用户自定义变量

-- 变量: 用户变量
-- 赋值
set @myname = 'itcast';
set @myage := 10;
set @mygender := '男',@myhobby := 'java';

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

-- 使用
select @myname,@myage,@mygender,@myhobby;

select @mycolor , @mycount;

select @abc;

局部变量

-- 变量: 用户变量
-- 赋值
set @myname = 'itcast';
set @myage := 10;
set @mygender := '男',@myhobby := 'java';

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

-- 使用
select @myname,@myage,@mygender,@myhobby;

select @mycolor , @mycount;

select @abc;

3. if 条件判断

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();

4. 存储过程-参数（IN/OUT）

需求：

-- in/out/inout参数

-- 根据传入(in)参数score，判定当前分数对应的分数等级，并返回(out)。
-- score >= 85分，等级为优秀。
-- score >= 60分 且 score < 85分，等级为及格。
-- score < 60分，等级为不及格。

create procedure p4(in 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(18, @result);
select @result;




-- 将传入的 200分制的分数,进行换算,换算成百分制 , 然后返回分数 ---> inout
create procedure p5(inout score double)
begin
    set score := score * 0.5;
end;

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

5. 存储过程- 循环

while

while循环是满足条件后，再执行循环体中的SQL语句，对应Java中的while语法

-- while   计算从1累加到n的值，n为传入的参数值。

-- A. 定义局部变量, 记录累加之后的值;
-- B. 每循环一次, 就会对n进行减1 , 如果n减到0, 则退出循环
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(100);

repeat

对应Java中的do while语法

-- repeat   计算从1累加到n的值，n为传入的参数值。
-- A. 定义局部变量, 记录累加之后的值;
-- B. 每循环一次, 就会对n进行-1 , 如果n减到0, 则退出循环
create procedure p8(in n int)
begin
    declare total int default 0;

    repeat
        set total := total + n;
        set n := n - 1;
    until  n <= 0
    end repeat;

    select total;
end;

call p8(10);
call p8(100);

loop

对应Java中的死循环，leave对应break，iterate对应continue

-- loop需求1  计算从1累加到n的值，n为传入的参数值。
-- A. 定义局部变量, 记录累加之后的值;
-- B. 每循环一次, 就会对n进行-1 , 如果n减到0, 则退出循环 ----> leave xx
create procedure p9(in n int)
begin
    declare total int default 0;

    sum:loop
        if n<=0 then
            leave sum;
        end if;

        set total := total + n;
        set n := n - 1;
    end loop sum;

    select total;
end;


-- loop需求2  计算从1到n之间的偶数累加的值，n为传入的参数值。
-- A. 定义局部变量, 记录累加之后的值;
-- B. 每循环一次, 就会对n进行-1 , 如果n减到0, 则退出循环 ----> leave xx
-- C. 如果当次累加的数据是奇数, 则直接进入下一次循环. --------> iterate xx

create procedure p10(in n int)
begin
    declare total int default 0;

    sum:loop
        if n<=0 then
            leave sum;
        end if;

        if n%2 = 1 then
            set n := n - 1;
            iterate sum;
        end if;

        set total := total + n;
        set n := n - 1;
    end loop sum;

    select total;
end;

call p10(100);

6. 存储过程-游标

-- 游标
-- 根据传入的参数uage，来查询用户表 tb_user中，所有的用户年龄小于等于uage的用户姓名（name）和专业（profession），
-- 并将用户的姓名和专业插入到所创建的一张新表(id,name,profession)中。

-- 逻辑:
-- A. 声明游标, 存储查询结果集
-- B. 准备: 创建表结构
-- C. 开启游标
-- D. 获取游标中的记录
-- E. 插入数据到新表中
-- F. 关闭游标

create procedure p11(in uage int)
begin
    declare uname varchar(100);
    declare upro varchar(100);
    declare 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;
    while true do
        fetch u_cursor into uname,upro;
        insert into tb_user_pro values (null, uname, upro);
    end while;
    close u_cursor;

end;


call p11(30);

7. 存储函数

存储函数和存储过程的区别在于，只能有IN参数，且必须有返回值。

-- 存储函数
-- 从1到n的累加

create function fun1(n int)
returns int deterministic
begin
    declare total int default 0;

    while n>0 do
        set total := total + n;
        set n := n - 1;
    end while;

    return total;
end;


select fun1(50);

触发器

-- 触发器
-- 需求: 通过触发器记录 user 表的数据变更日志(user_logs) , 包含增加, 修改 , 删除 ;

-- 准备工作 : 日志表 user_logs
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 tb_user for each row
begin
    insert into user_logs(id, operation, operate_time, operate_id, operate_params) VALUES
    (null, 'insert', now(), new.id, concat('插入的数据内容为: id=',new.id,',name=',new.name, ', phone=', NEW.phone, ', email=', NEW.email, ', profession=', NEW.profession));
end;


-- 查看
show triggers ;

-- 删除
drop trigger tb_user_insert_trigger;

-- 插入数据到tb_user
insert into tb_user(id, name, phone, email, profession, age, gender, status, createtime) VALUES (26,'三皇子','18809091212','erhuangzi@163.com','软件工程',23,'1','1',now());



-- 修改数据触发器
create trigger tb_user_update_trigger
    after update on tb_user 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.name, ', phone=', old.phone, ', email=', old.email, ', profession=', old.profession,
            ' | 更新之后的数据: id=',new.id,',name=',new.name, ', phone=', NEW.phone, ', email=', NEW.email, ', profession=', NEW.profession));
end;

show triggers ;

update tb_user set profession = '会计' where id = 23;

update tb_user set profession = '会计' where id <= 5;





-- 删除数据触发器
create trigger tb_user_delete_trigger
    after delete on tb_user for each row
begin
    insert into user_logs(id, operation, operate_time, operate_id, operate_params) VALUES
    (null, 'delete', now(), old.id,
        concat('删除之前的数据: id=',old.id,',name=',old.name, ', phone=', old.phone, ', email=', old.email, ', profession=', old.profession));
end;

show triggers ;


delete from tb_user where id = 26;