IT评测·应用市场-qidao123.com技术社区

标题: 【MySQL进阶篇】存储对象：视图、存储过程及触发器 [打印本页]
作者: 种地    时间: 2024-7-25 01:17
标题: 【MySQL进阶篇】存储对象：视图、存储过程及触发器
一、视图

1、先容

视图（view）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来界说视图的查询中利用的表（基表），并且是在利用视图时动态天生的。
通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。以是我们在创建视图的时间，主要的工作就落在创建这条SQL查询语句上。
2、操作语法

2.1、创建

   CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT [WITH[CASCADED | LOCAL] CHECK OPTION]
  2.2、查询 

   查看创建视图语句：SHOW CREATE VIEW  视图名称;
  查看视图数据：SELECT * FROM 视图名称.....; 
   2.3、修改

   方式一：CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT [WITH[CASCADED | LOCAL] CHECK OPTION]
  方式二：ALTER VIEW 视图名称[(列名列表)] AS SELECT [WITH[CASCADED | LOCAL] CHECK OPTION]
  2.4、删除

   DROP VIEW [IF EXISTS] 视图名称; 
  1. #创建视图
  2. create or replace view stu_v as select id,name from student where id<=10;
  3. #查询视图
  4. show create view stu_v;
  5. select * from stu_v;
  6. select * from stu_v where id<3;
  7. #修改视图
  8. create or replace view stu_v as select id,name,no from student where id<=10;
  9. alter view stu_v as select id,name from student where id<=10;
  10. #删除视图
  11. drop view if exists stu_v;
复制代码
 3、检查选项

  1. create or replace view stu_v as select id,name from student where id<=10;
  2. select * from stu_v;
  3. insert into stu_v values(5,'tom');
  4. #视图并不存储数据，数据在基表当中存储，也就意味着我们插入的数据实际上是插入到student表当中了
  5. insert into stu_v values(15,'tom');
  6. #基表当中会包含这条数据，但是在查询视图时并不包含这条数据，这是因为在创建视图时指定了id<=10，为了避免这种情况的发生可以加上检查选项
复制代码
1.1、试图当中的检查选项

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

  1. #CASCADE :
  2. create or replace view stu_v1 as select id,name from student where id<=20;
  3. create or replace view stu_v2 as select id,name from stu_v1 where id>=10 with cascaded check option ;
  4. insert into stu_v2 values(5,'tom');
  5. insert into stu_v2 values(25,'tom');
  6. #都不能插入成功，会检查stu_v2所依赖的底层所有的视图
  7. create or replace view stu_v3 as select id,name from stu_v2 where id<=15;
  8. insert into stu_v3 values(11,'tom');
  9. insert into stu_v3 values(17,'tom');#能执行成功，会在基表中出现该数据，stu_v3视图的条件不需要检查，只需要检查另外两个视图
  10. insert into stu_v3 values(28,'tom');
复制代码
LOCAL: 

  1. #LOCAL :
  2. create or replace view stu_v_1 as select id,name from student where id<=20;
  3. create or replace view stu_v_2 as select id,name from stu_v_1 where id>=10 with local check option ;
  4. insert into stu_v_2 values(5,'tom');
  5. #需要检查上一个视图的条件但上一个视图没有with check option选项，并且也不满足当前视图的条件
  6. insert into stu_v_2 values(25,'tom');
  7. #满足当前视图的条件，虽然不满足上一个视图的条件，但上一个视图没有with check option选项，因此可以插入
  8. create or replace view stu_v_3 as select id,name from stu_v_2 where id<=15;
  9. insert into stu_v_3 values(11,'tom');
  10. insert into stu_v_3 values(17,'tom');
  11. insert into stu_v_3 values(28,'tom');
  12. #都可以插入进来
复制代码
 4、更新及作用

4.1、视图的更新

要使视图更新，视图中的举动与底子表中的行之间必须存在一对一的关系。假如试图中包含一下任何一项，则该视图不可更新：
1、聚合函数或窗口函数（SUM()、MAX()、MIN()、COUNT()等）
2、DISTINCT
3、GROUP BY
4、HAVING
5、UNION 与 UNION ALL
4.2、视图的作用

1、简朴：试图不光可以简化用户对数据的理解，也可以简化他们的操作。那些被经常利用的查询可以被界说为视图，从而使得用户不必为以后的操作每次指定全部的条件。
2、安全：数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据。
3、数据独立：视图可以帮助用户屏蔽真实表布局带来的影响。
二、存储过程

1、先容

存储过程是事先颠末编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用职员的许多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处置惩罚的效率是有好处的。
存储过程思想上很简朴，就是数据库SQL语言层面的代码的封装与重用。
2、特点

封装、复用；可以吸收参数，也可以返回数据；减少网络交互，效率提升。
3、操作语法

3.1、创建

   CREATE PROCEDURE 存储过程名称[(参数列表)]
  BEGIN
                  --SQL语句
  END;
  3.2、调用

   CALL 名称 [(参数)];
  3.3、查看 

   SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUINE_SCHEMA='xxx';#查询指定数据库的存储过程及状态信息
  SHOW CREATE PROCEDURE 存储过程名称;#查询某个存储过程的界说
  3.4、删除

   DROP PROCEDURE [IF EXISTS] 存储过程名称; 
  1. #创建
  2. create procedure p1()
  3. begin
  4.     select count(*) from student;
  5. end;
  6. #调用
  7. call p1();
  8. #查看
  9. select * from information_schema.ROUTINES where ROUTINE_SCHEMA='test';
  10. show create procedure p1;
  11. #删除
  12. drop procedure if exists p1;
复制代码
 注意：在命令行中，实行创建存储过程的SQL时，需要通过关键字delimiter 指定SQL的竣事符
  1. delimiter $$
  2. mysql> create procedure p1()
  3.     -> begin
  4.     ->     select count(*) from student;
  5.     -> end$$
  6. Query OK, 0 rows affected (0.01 sec)
复制代码
4、变量

 4.1、体系变量

体系变量是MySQL服务器提供，不是用户界说的，属于服务器层面，分为全局变量（GLOBAL）、会话变量（SESSION）。
4.1.1、查看体系变量

   SHOW [SESSION | GLOBAL] VARIABLES;#查看所有体系变量
  SHOW [SESSION | GLOBAL] VARIABLES LIKE'....';#可以通过LIKE含糊匹配的方式查找变量
  SELECT @@[SESSION | GLOBAL] 体系变量名;#查看指定变量的值
  4.1.2、设置体系变量

   SET [SESSION | GLOBAL] 体系变量名=值;
  SET @@[SESSION |GLOBAL] 体系变量名=值; 
  1. #查询系统变量
  2. show session variables;
  3. show session variables like 'auto%';#查看与事务自动提交相关的变量
  4. select @@session.autocommit;
  5. #设置系统变量
  6. set session autocommit=0;
  7. set @@session.autocommit=1;
复制代码
注意：
1、假如没有指定SESSION/GLOBAL默认是SESSION会话变量；
2、MySQL服务器重启之后，所设置的全局参数会失效。
4.2、自界说变量

用户自界说变量是用户根据需要本身界说的变量，用户变量不用提前声明，在用的时间直接用“@变量名”利用就可以。其作用域为当前毗连。
4.2.1、用户变量赋值

   SET @var_name=expr [,@var_name=expr]...;
  SET @var_name:=expr [,@var_name:=expr]...;
  保举利用:=由于在MySQL中判断相等与赋值都是用的=，为了制止混淆，用:=。 
   SELECT @var_name:=expr [,@var_name:=expr]...;
  SELECT 字段名 INTO @var_name FROM 表名; 
  4.2.2、用户变量利用

   SELECT @var_name; 
  1. #用户变量赋值
  2. set @myname='test';
  3. set @myage:='10',@mygender:='男';
  4. select @mycolor:='black';
  5. select count(*) into @mycount from tb_user
  6. #使用自定义变量
  7. select @myname,@myage,@mygender;
复制代码
 注意：用户界说的变量无需对其举行声明或优化，只不外获取到的值为NULL。
4.3、局部变量

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

   DECLARE 变量名 变量类型 [FEFAULT ...];
  变量类型就是数据库字段类型：INT、BIGINT、VARCHAR、DATE、TIME等。 
4.3.2、局部变量赋值

   SET 变量名=值;
  SET 变量名：=值;
  SELECT 字段名 INTO 变量名 FROM 表名...; 
  1. #局部变量的声明和赋值
  2. create procedure p2()
  3. begin
  4.     declare stu_count int default 0;
  5.     set stu_count:=100;
  6.     #select count(*) into stu_count from student;
  7.     select stu_count;
  8. end;
  9. call p2();
复制代码
 5、if判断

5.1、语法布局

   IF 条件1 THEN
                  ...
  ELSE 条件2 THEN
                  ...
  ELSE
                  ...
  END IF;
  1. #需求：更具定义的分数score变量，判断对应等级
  2. #1、score>=85，等级为优秀
  3. #2、score>=60并且score<85，等级为及格
  4. #3、score<60，等级不及格
  5. create procedure p1()
  6. begin
  7.     declare score int default 58;
  8.     declare result varchar(10);
  9.     if score>=85 then
  10.        set result:='优秀';
  11.     elseif score>=60 and score<85 then
  12.        set result:='及格';
  13.     else
  14.         set result:='不及格';
  15.     end if;
  16.     select result;
  17. end;
  18. call p1();
复制代码
 6、存储过程的参数

类型寄义备注
IN该类参数作为输入，也就是调用时需要传入值默认
OUT该类参数作为输出，也就是该参数可以作为返回值
INOUT既可以作为输入参数，也可以作为输出参数
6.1、语法 

   CREATE PROCEDURE 存储过程名称([IN/OUT/INOUT 参数值 参数类型])
  BEGIN
          --SQL语句
  END;
  1. #存储过程中的参数
  2. #1、需求：根据传入参数的分数score，判断对应等级，并返回
  3. #score>=85，等级为优秀
  4. #score>=60并且score<85，等级为及格
  5. #score<60，等级不及格
  6. create procedure p2(in score int,out result varchar(10))
  7. begin
  8.     if score>=85 then
  9.        set result:='优秀';
  10.     elseif score>=60 and score<85 then
  11.        set result:='及格';
  12.     else
  13.         set result:='不及格';
  14.     end if;
  15. end;
  16. call p2(68,@result);
  17. select @result;
  18. #2、将传入的200分制的分数进行换算，换算成百分制，然后返回
  19. create procedure p3(inout score double)
  20. begin
  21.     set score :=score*0.5;
  22. end;
  23. set @score:=78;
  24. call p3(@score);
  25. select @score;
复制代码
 7、case

7.1、语法

语法一
   CASE case_value
                  WHEN then_value1 THEN statement_list1
                  [WHEN then_value2 THEN statement_list2]
                  [ELSE statement_list]
  END CASE;
  语法二
   CASE
                  WHEN search_condition1 THEN statement_list1
                  [WHEN search_condition2 THEN statement_list2]
  1. #需求：根据传入的月份，判断月份所属的季度（采用case结构）
  2. #1、1-3月份，第一季度
  3. #2、4-6月份，第二季度
  4. #3、7-9月份，第三季度
  5. #4、10-12月份，第四季度
  6. create procedure p4(in month int)
  7. begin
  8.     declare result varchar(10);
  9.     case
  10.         when month>=1 and month<=3 then
  11.          set result:='第一季度';
  12.         when month>=4 and month<=6 then
  13.          set result:='第二季度';
  14.         when month>=7 and month<=9 then
  15.          set result:='第三季度';
  16.         when month>=10 and month<=12 then
  17.          set result:='第四季度';
  18.         else
  19.          set result:='非法参数';
  20.     end case;
  21.     select concat('您输入的月份为：',month,'所属季度：',result);
  22. end;
  23. call p4(16);
复制代码
 8、while循环

while循环是有条件的循环控制语句。满足条件后，再实行循环体中的SQL语句。
语法布局：
   #先判断条件，假如条件为true，则实行逻辑，否则不实行
  WHILE 条件 DO
          SQL语句...
  END WHILE;
  1. #需求：计算从1累加到n的值，n为传入的参数值
  2. #A.定义局部变量，用来记录累加之后的值
  3. #B.每循环一次n-1，知道满足n<0,则不再循环
  4. create procedure p5(in n int)
  5. begin
  6.     declare sum int default 0;
  7.     while n>0 do
  8.         set sum:=sum+n;
  9.         set n:=n-1;
  10.         end while;
  11.     select sum;
  12. end;
  13. call p5(10);
复制代码
 9、repeat循环

repeat是有条件的控制语句，当满足条件的时间退出循环。
语法布局：
   #先实行一次逻辑，然后判断逻辑是否满足，假如满足则退出，反之则举行下一次循环
  REPEAT
          SQL逻辑...
          UNTIL 条件
  END REPEAT;
  1. create procedure p6(in n int)
  2. begin
  3.     declare sum int default 0;
  4.     repeat
  5.         set sum :=sum+n;
  6.         set n :=n-1;
  7.     until n<=0
  8.         end repeat;
  9.     select sum;
  10. end;
  11. call p6(10);
复制代码
 10、loop循环

loop实现简朴的循环，假如不在SQL逻辑中增加退出循环的条件，可以用实在现简朴的死循环。loop可以配合以下两个语句利用：
· LEAVE:退出循环。
· ITERATE:必循用在循环中，作用是跳过当前语句剩下的语句，直接进入下一次循环。
   [begin_label:] LOOP
          SQL 逻辑...
  END LOOP [end_lable];
    LEAVE lable;#跳出循环
  ITERATE lable;#进入下一次循环 
  1. #需求：计算从1累加到n的值，n为传入的参数值
  2. #A.定义局部变量，用来记录累加之后的值
  3. #B.每循环一次n-1，知道满足n<0,则不再循环
  4. create procedure p7(in n int)
  5. begin
  6.     declare sum int default 0;
  7.     total:loop
  8.         if n<=0 then
  9.             leave total;
  10.         end if;
  11.         set sum :=sum+n;
  12.         set n :=n-1;
  13.     end loop total;
  14.     select sum;
  15. end;
  16. call p7(10);
  17. #需求：计算从1到n之间偶数的累加值，n为传入的参数值
  18. create procedure p8(in n int)
  19. begin
  20.     declare sum int default 0;
  21.     total:loop
  22.         if n<=0 then
  23.             leave total;
  24.         elseif n%2=1 then
  25.             set n:=n-1;
  26.             iterate total;
  27.         end if;
  28.         set sum :=sum+n;
  29.         set n :=n-2;
  30.     end loop total;
  31.     select sum;
  32. end;
  33. call p8(10);
复制代码
11、游标(cursor) 

  1. create procedure p9()
  2. begin
  3.     declare stu_count int default 0;
  4.     select * into stu_count from student;
  5.     select stu_count;
  6. end;
  7. call p9();
  8. #会报这样一条错误：The used SELECT statements have a different number of columns
复制代码
这个时间就需要游标了。
游标是用来存储查询结果集的数据类型，在存储过程和函数中可以利用游标对结果集举行循环的处置惩罚，游标的利用包罗游标的声明、OPEN、FETCH和CLOSE。
11.1操作语法

声明游标：
   DECLARE 游标名称 CURSOR FOR 查询语句;  
  打开游标：
   OPEN 游标名称; 
   获取游标记录：
   FETCH 游标名称 INTO 变量[,变量];
  关闭游标：
   CLOSE 游标名称; 
  11.2、条件处置惩罚程序

条件处置惩罚程序可以用来界说在流程控制布局实行过程中遇到题目时相应的处置惩罚步骤。具体语法：
   DECLARE handler_action HANDLER condition_value [,condition_vakue]...statement;
  
  handler_action
          CONTINUE:继续实行当出息序
          EXIT:终止实行当出息序
  condition_value
          SQLSTATE sqlstate_value:状态码，如02000
          SQLWARNING:所有以01开头的SQLSTATE代码的简写
          NOT FOUND: 所有以01开头的SQLSTATE代码的简写
          SQLEXCEPTION:所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的简写
  1. #需求：根据传入的参数uage，用来查询用户表tb_user中，所有用户年龄小于等于uage的用户姓名（name）和专业（profession）
  2. #并将用户的姓名和专业插入到所创建的一张新表（id，name，profession）中
  3. #A.声明游标，存储查询结果集
  4. #B.创建表结构
  5. #C.开启游标
  6. #D.获取游标中的记录
  7. #E.插入数据到新表中
  8. #F.关闭游标
  9. create procedure p9(in uage int)
  10. begin
  11.     declare u_name varchar(10);
  12.     declare u_profession varchar(20);
  13.     declare u_cursor cursor for select name,profession from tb_user where age<=uage;
  14.     declare exit handler for SQLSTATE '02000' close u_cursor;
  15.     create table if not exists tb_user_pro(
  16.         id int primary key auto_increment,
  17.         name varchar(10) ,
  18.         profession varchar(20)
  19.     );
  20.     open u_cursor;
  21.     while true do
  22.         fetch u_cursor into u_name,u_profession;
  23.         insert into tb_user_pro values(null,u_name,u_profession);
  24.         end while;
  25.     close u_cursor;
  26. end;
复制代码
三、存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的。具体语法：
   CREATE FUNCTION 存储过程名称[(参数列表)]
  RETURNS type [characteristic ...]
  BEGIN
                  --SQL语句
                  RETURN...;
  END;
  characterstic说明：
  DETERMINISTIC：相同的输入参数总是产生相同的结果
  NO SQL：不包含SQL语句
  READS SQL DATA：包含读取数据的语句，但不包含写入数据的语句
  1. #从1到n的累加
  2. create function fun(n int)
  3. returns int deterministic
  4. begin
  5.     declare sum int default 0;
  6.     while n>0 do
  7.         set sum:=sum+n;
  8.         set n:=n-1;
  9.         end while;
  10.     return sum;
  11. end;
  12. select fun(100);
复制代码
四、触发器

触发器是与表有关的数据库对象，指在insert/update/delete之前或之后，触发并实行触发器中界说的SQL语句集合。触发器这种特性可以协作应用在数据库端确保数据的完整性，日志记录，数据校验等操作。
利用别名OLD和NEW来引用触发器中发生变革的记录内容，者与其他的数据库是相似的。如今触发器还只支持行级触发，不支持语句级触发。
触发器类型NEW和OLD
INSERT型触发器NEW表示将要或已经新增的数据
UPDATE型触发器OLD表示修改之前的数据，NEW表示将要修改或修改后的数据
DELETE型触发器OLD表示将要货已经删除的数据
 创建：
   CREATE TRIGGER trigger_name
  BEDORE/AFTER INSERT/UPDATE/DELETE ON table_name FOR EACH ROW
  BEGIN
          trigger_stmt;
  END;
  查看：
    SHOW TRIGGERS;
  删除：
   DROP TRIGGER [schema_name.] trigger_name;#假如没有指定schema_name.默认当前数据库 
  1. #需求：通过触发器记录user表的数据变更日志（user_logs），包含增加，修改、删除
  2. create table user_log(
  3.     id int(11) not null auto_increment,
  4.     operation varchar(20) not null comment '操作类型，insert/update/delete',
  5.     operate_time datetime not null comment '操作时间',
  6.     operate_id int(11) not null comment '操作的ID',
  7.     operate_params varchar(500) comment '操作参数',
  8.     primary key (`id`)
  9. )engine=innodb default charset =utf8;
  10. #插入数据的触发器
  11. create trigger insert_trigger
  12.     after insert on tb_user for each row
  13. begin
  14.        insert into user_log(id, operation, operate_time, operate_id, operate_params) values
  15.         (null,'insert',now(),new.id,concat('插入的数据内容为:id=',new.id,'name=',new.name,'phone=',new.phone,'profession=',new.profession));
  16. end;
  17. #插入数据到tb_user表，验证
  18. insert into tb_user values(5,'二秃子',66,'1','12345678910','通信工程');
  19. #修改数据的触发器
  20. create trigger update_trigger
  21.     after update on tb_user for each row
  22. begin
  23.        insert into user_log(id, operation, operate_time, operate_id, operate_params) values
  24.         (null,'update',now(),new.id,concat('更新之前的数据:id=',old.id,'name=',old.name,'phone=',old.phone,'profession=',old.profession,
  25.                                     '更新之后的数据:id=',new.id,'name=',new.name,'phone=',new.phone,'profession=',new.profession));
  26. end;
  27. update tb_user set age =56 where age=55;
  28. #删除数据的触发器
  29. create trigger delete_trigger
  30.     after delete on tb_user for each row
  31. begin
  32.        insert into user_log(id, operation, operate_time, operate_id, operate_params) values
  33.         (null,'delete',now(),old.id,concat('删除之前的数据:id=',old.id,'name=',old.name,'phone=',old.phone,'profession=',old.profession));
  34. end;
  35. delete from tb_user where id=5;
  36. show triggers;
复制代码
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。



欢迎光临 IT评测·应用市场-qidao123.com技术社区 (https://dis.qidao123.com/) Powered by Discuz! X3.4