MySQL基础笔记、八股（视图 /存储过程/触发器）

最新推荐文章于 2024-05-20 14:38:13 发布

转码的小盒

最新推荐文章于 2024-05-20 14:38:13 发布

阅读量218

点赞数

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

本文链接：https://blog.csdn.net/vx_ahhhhyx319/article/details/128663118

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

文章目录

视图 /存储过程/触发器

视图 /存储过程/触发器

1. 视图

1.1 基本语法

视图创建：create [or replace] view 视图名 as select语句 [with [cascaded/local] check option];
视图修改： 1) create or replace view 视图名 as select语句(修改版) [with [cascaded/local] check option];
2) alter view 视图名 as select语句(修改版) [with [cascaded/local] check option];
视图查询： 1) 查看创建视图语句 show create view 视图名; (类似查表)
2) 查看视图数据 select * from 视图名; (类似查表)
视图删除： drop view 视图名1[,视图名2,...];
插入数据： insert into 视图名 values(数据); (类似表操作)

1.2 检查选项(check option)

cascaded级联(默认检查选项)
套娃：下一层依赖上一层(create v2 from v1)，如下图三层，第二层语句后带有with cascaded check option，即使第一层没有检查选项，也会按照cascaded检查选项操作(相当于“复制”了一份check option给上层)。上层不会对下层这样操作(第二层不会“复制”给第三层)。

向视图v3插入insert(更新update同理)数据。
cascaded：id得满足id<=20 and id>=10(即满足第一、二层where条件，因为语句后面带了检查选项)；id不需要满足id<=15(即不需要满足第三层条件，因为语句后没带检查选项)。
local本地
顾名思义，只管本层的检查选项。
套娃：下一层依赖上一层，第二层语句后带有with local check option，与cascaded不同的是，第二层只管自己这层(有选项就按选项操作)，不会给第一层“复制”。

向视图v3插入insert(更新update同理)数据。
local：id满足带检查选项的层就行，id>=10即可。

1.3 视图的更新与作用

视图更新： 视图更新的条件是视图和其依赖的基表每一行的字段一一对应，视图字段可以少于基表。
包含以下任意一项，视图不可更新：(1)聚合函数(sum()、min()、count()等) (2)distinct (3)group by, having (4)union/union all
视图作用
(1)视图是一个虚拟的表(并非表的查询结果，只是查询过程的封装)，可以基于基表的查询创建一个视图(虚拟的新表)，供以后使用(from 该视图，相当于基于虚拟表进行操作) ；
(2)将创建视图时的条件(如where后的条件)封装了，若有check option，以后只要基于该视图(from 该视图)，自带条件；
(3)安全(可以给用户看到指定字段的数据，其他字段数据不可见)
(4)数据独立(基表表结构发生更改，视图的数据不会发生变化)

2. 存储过程

存储过程就是封装一段SQL语句，便于以后直接调用，提高复用性，其可以接收参数，也可以返回数据。

2.1 基础语法

创建：

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

调用： call 存储过程名([参数列表]);
查看： 1)查指定数据库的存储过程及状态 select * from information_schema.ROUTINES where ROUTINE_SCHEMA = '数据库名';
2)查储存过程的定义 show create procedure 存储过程名;
删除： drop procedure [if exists] 存储过程名;

注：在命令终端里执行创建存储过程操作时，需要通过关键字delimiter指定mysql的结束符。如在终端中mysql> delimiter $$，表示以后的SQL语句都得用$$结尾，而不是;结尾。这解决了--SQL语句;和end;后同时带分号的情况。现只要用end$$即可在终端里创建存储过程。(创建完后记得改回分号mysql> delimiter ;)

2.2 变量

1.系统变量
系统变量是MySQL服务器提供，非用户定义。分为全局变量(global)、会话变量(session(默认))。(DataGrip中一个控制台console里的变量就是会话变量，在一个控制台更改一个会话系统变量，另一个控制台的该系统变量不会改变) MySQL重启后所有系统变量置回初值。

查看系统变量： 1)模糊查看 show [global/session] variables [like '...'];(不加like 查看所有系统变量)
2)指定查看 select @@[global./session.]系统变量名;
设置系统变量： set [global/session] 系统变量名=值;
set @@[global./session.]系统变量名=值;

2.自定义变量
自定义变量是用户自己定义的变量，在当前会话生效。

赋值： set @变量名1 := 值1[, @变量名2 := 值2, ...];
select @变量名1 := 值1[, @变量名2 := 值2, ...];
select 字段名 into @变量名 from 表名;
使用： select @变量名;

注：若变量未赋值，使用时为NULL。

3.局部变量
局部变量是根据需要定义的在局部生效的变量，访问前需要声明。可以用作存储过程内的局部变量和输入参数(一般在begin和end内使用，且只在其间有效)。

声明： declare 变量名变量类型[default 值]; (可给默认值)
赋值： set 变量名:=值;
select 字段名 into 变量名 from 表名;
使用： select 变量名;

2.3 条件判断(if)

语法：

if 条件1 then
		语句1；
elseif 条件2 then
		语句2;
else
		语句3;
end if;

2.4 参数(in/out/inout)

类型： (1)in输入参数(默认) (2)out输出参数 (3)inout既可作输入，又可作输出
使用： 在创建存储过程时create procedure p1(in/out/inout 变量名变量类型) ...

2.5 条件选择(case)

语法：

case 判定值
		when 值1 then 语句1;  #判断值=值1，走语句1
		when 值2 then 语句2;
		else 语句3;
end case;

case
		when 条件1 then 语句1;  #条件1成立，走语句1
		when 条件2 then 语句2;
		else 语句3;
end case;

2.6 循环(while/repeat/loop)

while语法：

while 条件 do
		语句;       #条件满足，走语句，不满足，结束循环
end while;

实例：

create procedure p7(in n int, out res int)    -- 输入n，输出从1加到n
begin
    declare i int default 1;
    set res := 0;
    while i<=n do
        set res := res + i;
        set i := i + 1;
    end while;
end;
call p7(4,@result);
select @result;

repeat语法：
和while相反，不满足条件继续循环，知道满足条件才退出循环(类似do…while)

repeat
   	语句;           #条件不满足，继续循环(不管条件，开始就会执行一次循环语句)
   	until 条件      #满足条件，退出循环
end repeat;

loop语法：
loop相当于死循环，需要标记配合leave(类似break)、iterate(类似continue)使用。

标记:loop
   	语句; 
end loop 标记;

实例：

create procedure p8(in n int)    -- 输入n，输出从1加到n
begin
    declare i int default 1;
    declare res int default 0;
    flag:loop
        if(i>n) then
            leave flag;
        end if;
        set res := res + i;
        set i := i + 1;
    end loop flag;
    select res;
end;
call p8(4);

2.7 游标(cursor)

游标是用来存储查询结果的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。游标使用过程如下：

游标声明： declare 游标名 cursor for select语句;
游标打开： open 游标名;
获取游标记录： fetch 游标名 into 变量1[, 变量2, ...];
游标关闭 close 游标名;

实例：

create procedure p9(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;  #1.声明游标
    declare exit handler for SQLSTATE '02000' close u_cursor;  #条件处理程序handler
    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;    #2.开启游标
    while true do
        fetch u_cursor into uname,upro;   #3.获取游标记录
        insert into tb_user_pro values(null,uname,upro); #将游标记录循环插入表中
    end while;
    close u_cursor;  #4.关闭游标
end;

call p9(30);

2.8 存储函数

存储函数是必须有返回值的存储过程，存储函数的参数只能说in类型，一般存储函数都可以用存储过程替代。
实例：

create function fun1(n int)  #输入n，输出从1加到n
returns int deterministic #返回值类型，存储函数的特性是deterministic
begin
    declare res int default 0;
    declare i int default 1;
    while i<=n do
        set res := res + i;
        set i := i + 1;
    end while;
    return res;
end;
select fun1(4);

3. 触发器

触发器是与表相关的数据库对象，当表执行insert/update/delete操作之前或之后，可触发并执行触发器中定义的SQL语句。(MySQL只支持行级触发：一次SQL语句操作影响了几行，就触发几次触发器；不支持语句级触发：一次SQL语句操作不管影响了几行，只触发一次触发器)
在这里插入图片描述

3.1 基础语法

创建：

create trigger 触发器名
before/after insert/update/delete  #何时触发以及触发器类型
on 表名 for each row   #行级触发器
begin
   触发器中的SQL语句(触发时执行);
end;

查看： 查看当前数据库中所有触发器：show triggers;
删除： 删除[指定数据库下的]触发器：drop trigger [数据库名] 触发器名;

3.2 触发器使用

当表执行insert/update/delete操作之前或之后，触发器触发。以表的更新为例，利用触发器完成表的更新日志。

-- 创建更改数据的触发器
create trigger tb_user_update_trigger
    after update on tb_user for each row            #在tb_user表更新数据后触发
begin
    insert into user_logs(id, operation, operate_time, operate_id, operate_params) VALUES   #触发后将数据插入更新日志表user_logs中
    (null, 'update', now(), new.id,
     concat('更新前的数据为：id= ',old.id,', phone= ',old.phone,', email= ',old.email,', profession= ',old.profession,
          '| 更新后的数据为：id= ',new.id,', phone= ',new.phone,', email= ',new.email,', profession= ',new.profession));
end;

现只要tb_user表中更新数据，更新日志表中就会记录更新前后的数据。(更新操作影响了几行，在日志表user_logs中就会有几条数据)