MySQL数据库技术与项目应用教程电子教案 项目六-3 数据库编程(触发器与事件)

备课纸适用班级：_______P.课题项目六编程操作网上商城系统数据（三）课时2+4课时教学目的会使用SQL创建和管理触发器会使用SQL创建和管理事件重点数据库自动化任务难点触发器和事件的应用教具多媒体+机房课型新授作业课后习题课后项目实战教学过程课时分配教学方法教学内容课程引入提出问题1.对于繁杂的数据处理任务如何自动完成？2.相关数据的一致性如何保障？任务4使用触发器实现自动任务知识描述：触发器是数据库中的独立对象，为了确保数据完整性，设计人员可以用触发器实现复杂的业务逻辑。例如，当用户选购好商品之后，并完成了订单，那么用户所选购的商品的库存量应该根据用户订单中商品的数量进行减少。备课纸适用班级：_P.带着问题开本任务讨论如下内容：展本章学习（1）触发器与外键约束的区别？激发学生兴（2）怎样定义和管理触发器?趣（3）怎样定义和管理事件?触发器概述触发器是一种特殊的存储过程，可以用来对表实施复杂的完整性约束，保持数据的一致性。当触发器所保护的数据发生改变时，触发器会自动被激活，并执行触发器中所定义的相关操作，以保证关联数据的完整性。MySQL中，激活触发器的事件：INSERT事件UPDATE事件DELETE事件详细阐述触发器中的两个逻辑表：NEW表：用来存放更新后的记录。OLD表：表用来存放更新前的记录。学习提示NEW和OLD的表结构与触发器所在数据表的结构完全一致，当触发器的执行完成之后，这两个表也会被自动删除。创建触发器语法格式CREATETRIGGER触发器名称触发时间触发事件ON表名FOREACHROW程序体触发器名称：指要创建的触发器的名称；触发时间：指触发器执行的时间，它可以是BEFORE或AFTER，以指明触发器是在激活它的语句之前或之后触发；触发事件：指激活触发程序的语句类型，包括INSERT、UPDATE和DELETE表名：是指触发事件操作的表名称；备课纸适用班级：_P.FOREACHROW：表示任何一条记录上的操作满足触发事件都会触发该触发器；程序体：指触发器被触发后执行的语句集。【例6.45】创建触发器trig_ins_addnum，当uid为1的用户向购物车中添加商实例精讲CREATETRIGGERtrig_ins_addnum#建议命名以trig_开头，其中ins表示是insertAFTERINSERT#数据插入完成后执行ONcart#触发器建立的表为cartFOREACHROWSET@total=@total+NEW.cnum;#累加新增记录的购买数量【例6.46】向users表中添加新用户，判断输入的密码长度，若长度小于6，则拒绝插入新用户，并提示“密码长度小于6，请重新输入“。若长度大于等于6，则按md5算法对密码加密处理。实例演示DELIMITER//CREATETRIGGERtrig_ins_checkpwdBEFOREINSERT#向users表插入数据前执行ONusersFOREACHROWBEGIN IFlength(new.upwd)>=6THEN#执行判断密码长度 SETnew.upwd=md5(upwd);#符合条件时的处理逻辑 ELSE #自定义错误提示，数据插入失败 SIGNALSQLSTATE'45000'SETmessage_text='密码长度小于6，请重新输入'; ENDIF;END//学习提示触发器中对表本身执行INSERT和UPDATE操作时，触发器的动作时间只能用BEFORE不能用AFTER。当触发程序的语句类型是INSERT或者UPDATE时，在触发器里不能再用UPDATESET，应直接使用SET，避免出现UPDATESET重复错误。备课纸适用班级：_P.管理触发器1.查看触发器的定义SHOWTRIGGERS语句来查看触发器的基本信息SHOWTRIGGERS[{FROM|IN}数据库名][LIKE匹配模式|WHERE条件表达式]【例6.47】查看cart表上定义的触发器。mysql>SHOWTRIGGERSlike'cart%'\G;***************************1.row***************************Trigger:trig_ins_addnumEvent:INSERTTable:cartStatement:SET@total=@total+NEW.cnumTiming:AFTERCreated:2021-09-0710:35:34.06sql_mode:STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTIONDefiner:root@localhostcharacter_set_client:utf8mb4collation_connection:utf8mb4_0900_ai_ciDatabaseCollation:utf8_bin1rowinset(0.00sec)2.删除触发器DROPTRIGGER[IFEXISTS]trigger_name;【例6.48】删除名称为trig_ins_addnum的触发器。mysql>DROPTRIGGERtrig_ins_addnum;QueryOK,0rowsaffected(0.02sec)任务5使用事件实现自动任务任务描述数据库管理是一项重要且烦琐的工作，许多日常管理任务往往会频繁地、周期性地执行，例如定期维护索引、定时刷新数据、定时关闭帐户、定义打开或关数据库等操作，实际应用中，数据库管理员会定义事件对象以自动化完成这些任务。本任务将详细介绍MySQL中事件的创建、维护和管理等。备课纸适用班级：_P.事件概述事件是在特定时刻调用的\o"MySQL知识库"数据库对象。一个事件可调用一次，也可周期性的被调用，它由一个特定的线程来管理，也就是“事件调度器”。类比事件和触发器类似，都是在某些事情发生的时候启动。当数据库上启动一条语句的时候，触发器就启动了，而事件是根据调度事件来启动。事件取代了原先只能由\o"操作系统知识库"操作系统的计划任务来执行的工作，而且\o"MySQL知识库"MySQL的事件调度器可以精确到每秒钟执行一个任务。1.开启事件调度器事件调度器是MySQL数据库服务器的一部分，负责事件的调度，它不断监视某个事件是否需要被调用。在创建事件前，必须先打开事件调度器。MySQL中的全局变量@@GLOBAL.EVENT_SCHEDULER用于监控事件调度器是否开启。【例6.46】查看MySQL服务器事件调度器的状态。mysql>SHOWVARIABLESLIKE'event_scheduler';+-----------------+-------+|Variable_name|Value|+-----------------+-------+|event_scheduler|OFF|+-----------------+-------+1rowinset,1warning(0.01sec)【例6.47】打开MySQL服务器事件调度器。mysql>SET@@GLOBAL.event_scheduler=ON;QueryOK,0rowsaffected(0.00sec)mysql>SHOWVARIABLESLIKE'event_scheduler';+-----------------+-------+|Variable_name|Value|+-----------------+-------+|event_scheduler|ON|+-----------------+-------+备课纸适用班级：_P.学习提示事件调试器的状态值也可以用1和0来表示，其中1表示ON，0表示OFF。当服务器重启时，事件调度器的状态会恢复到默认值。若要想永久改变事件调度器的状态，可以修改my.ini文件，并在[mysqld]部分添加如下内容，然后重启MySQL。创建事件每个事件由事件调度（eventschedule）和事件动作（eventaction）两个主要部分组成。语法格式精讲CREATEEVENT[IFNOTEXISTS]事件名称ONSCHEDULE时间与频率[ONCOMPLETION[NOT]PRESERVE][ENABLE|DISABLE][COMMENT事件注释]DO程序体;

ONSCHEDULE时间和频率：定义事件执行的开始和结束时间、执行的频率与持续时间。

ONCOMPLETION[NOT]PRESERVE：默认情况事件执行完后会自动删除，若想保留事件定义，则要设置ONCOMPLETIONPRESERVE。

ENABLE|DISABLE：用于启用或禁用事件。创建时默认为ENABLE。

DO程序体：用于指定事件执行的SQL语句集。可以是简单的INSERT或者UPDATE语句，还可以调用存储过程或者BEGIN…END的语句块。事件在创建时会根据时间和频率的不同设置，确定事件仅执行一次或是定期重复执行。（1）定义执行一次的事件，时间和频率设置语法如下。【例6.51】从当前时间开始的5分钟后，修改乐器类商品的价格为原价9折。实例精讲CREATEEVENTevent_discountONSCHEDULEATCURRENT_TIMESTAMP+INTERVAL5MINUTEDOUPDATEgoodsgJOINcategorycONg.cid=c.cidSETgprice=gprice*0.9WHEREcname='乐器';备课纸适用班级：_P.学习提示：使用AT设置时间戳时，不能设置为过期时间。（2）定义重复执行的事件，时间和频率设置语法如下。EVERY时间间隔[STARTS开始时间[+INTERVAL时间间隔]][ENDS结束时间[+INTERVAL时间间隔]]【例6.52】创建名为event_huge_sales事件，从2022年1月1日起，每天晚上8点后，蔬菜水果类商品的价格修改为原价的8折。实例精讲CREATEEVENTevent_huge_salesONSCHEDULEEVERY1DAYSTARTS'2022-1-120:00:00'ENDS'2022-12-3123:59:59'DOUPDATEgoodsgJOINcategorycONg.cid=c.cidSETgprice=gprice*0.8WHEREcname='蔬菜水果';【例6.53】创建名为event_reindex_goods事件，每周调用存储过程proc_reindex_goods,用于重建goods表上索引ix_gname。首先，创建存储过程proc_reindex_goods，用于重建索引ix_gname。实例演示DELIMITER//CREATEPROCEDUREproc_reindex_goods()DETERMINISTICBEGINIFEXISTS(SELECT* FROMinformation_schema.statistics WHEREtable_schema='onlinedb'#筛选数据库名 ANDtable_name='goods'#筛选表名 ANDindex_name='ix_gname')#筛选索引名 DROPINDEXix_gnameongoods; ENDIF; CREATEINDEXix_gnameongoods(gname);#建立索引END//备课纸适用班级：_P.创建事件，用于每周一次调用该存储过程CREATEEVENTevent_reindex_goodsONSCHEDULEEVERY1WEEK#执行频率为每周一次STARTS'2022-1-103:00:00'DOCALLproc_reindex_goods();管理事件1.查看事件语法格式SHOWEVENTS[{FROM|IN}数据库名][LIKE匹配模式|WHERE条件表达式]【例6.54】查看onlinedb数据库中的所有事件，并格式化显示。实例演示mysql>SHOWEVENTSFROMonlinedb\G;***************************1.row***************************Db:onlinedbName:event_reindex_goodsDefiner:root@localhostTimezone:SYSTEMType:RECURRINGExecuteat:NULLIntervalvalue:1Intervalfield:WEEKStarts:2022-01-0103:00:00Ends:NULLStatus:ENABLEDOriginator:1character_set_client:utf8mb4collation_connection:utf8mb4_0900_ai_ciDatabaseCollation:utf8_bin1rowinset(0.03sec)要查看事件的创建信息，其语法格式如下：SHOWCREATEEVENTevent_name;2.修改事件备课纸适用班级：_P.语法格式ALTEREVENT事件名称ONSCHEDULE时间与频率[ONCOMPLETION[NOT]PRESERVER][RENAMETO新事件名称][ENABLE|DISABLE][COMMENT事件注释][DO程序体];略讲【例6.55】禁用event_reindex_goods的事件。ALTEREVENTevent_reindex_goodsDISABLE;【例6.56】启用名为event_reindex_goods的事件。ALTEREVENTevent_reindex_goodsENABLE;QueryOK,0rowsaffected(0.01sec)【例6.57】修改事件event_reindex_goods的为即时事件，事件执行完后不删除，并重新命名为event_reindex。实例演示mysql>ALTEREVENTevent_reindex_goods->ONSCHEDULEATCURRENT_TIMESTAMP->ONCOMPLETIONPRESERVE->RENAMETOevent_reindex->DO->CALLproc_reindex_goods();QueryOK,0rowsaffected(0.02sec)3.删除事件略讲DROPEVENT[IFEXISTS]事件名称【例6.58】删除名为event_reindex的事件。mysql>DROPEVENTevent_reindex;QueryOK,0rowsaffected(0.01sec)项目实战

存储函数备课纸适用班级：_P.（1）创建并调用存储函数func_users_count，查询2021年1月1日以后注册的用户总数。（2）使用SQL语句查看用户自定义函数func_users_count。

存储过程（3）创建并调用存储过程proc_get_integer，输入100以内能够同时被3和5整除的整数。（4）创建并调用存储过程proc_rand_record，为users表添加10000条测试记录。（5）创建并调用存储过程proc_user_order，根据指定的uID查询该用户的订单总数。（6）删除存储过程proc_user_order（7）创建存储过程proc_orders_count，统计查询每个用户的订单数。

触发器（8）创建触发器trig_order_num,当用户下单时，即订单详情表有数据插入时，同步更新goods表中相应商品的库存量以及销售量。（9）创建触发器trig_goods_type，当更改表goodstype中某个商品类别ID时，同时将goods表对应的商品类别ID全部更新。

事件（10）在网上商城数据库中，完成销售月报表以及日报表功能，统计每月以及每日的销售总金额，以及商品销售总数量。（10）在网上商城数据库中，完成销售月报表以及日报表功能，统计每月以及每日的销售总金额，以及商品销售总数量。AI助学：根据提示词高效编写存储函数应用场景：在网上商城系统的开发过程中，开发人员需要编写大量存储函数和存储过程来封装复杂的业务逻辑，例如计算会员折扣价格、处理订单提交流程、触发库存自动扣减等。这类代码对语法规范和逻辑严谨性要求较高，手工编写不仅耗时，还容易出现边界条件疏漏或事务控制缺失等问题。本节将使用AI辅助你快速理解MySQL存储程序的核心原理，并根据业务需求生成高