数据库技术与应用 课件 项目9、10 学生成绩管理系统数据库中的触发器、学生成绩管理系统数据安全管理

项目9学生成绩管理系统数据库中的触发器数据库技术与应用掌握使用SQL语句创建触发器、修改触发器、删除触发器的方法掌握使用SQL语句创建INSERT型触发器的方法掌握使用SQL语句创建UPDATE型触发器的方法掌握使用SQL语句创建DELETE型触发器的方法本章目标一、使用SQL语句创建INSERT型触发器1.触发器概述触发器可以看作是一种特殊类型的存储过程，它与存储过程的区别在于存储过程使用时需要调用，而触发器是在预先定义好的事件(如INSERT，DELETE等操作)发生时，才会被MySQL自动调用。创建触发器时需要与数据表相关联，当表发生特定事件(如INSERT、DELETE等操作)时，就会自动执行触发器中提前预订好的SQL代码，实现插入数据前，强制检验或转换数据等操作，或是在触发器中代码执行错误后，撤销已执行成功的操作，保证数据的安全。因此，不难看出触发器在使用时的优点和缺点，具体内容如下(1)优点

触发器可以通过数据库中的相关表实现级联无痕更改操作

保证数据安全，进行安全校验。(2)缺点

触发器的使用会影响数据库的结构，同时增加了维护的复杂程度

触发器的无痕操作会造成数据在程序(如PHP、Java等)层面不可控。一、创建触发器1.语法结构触发器必须创建在指定的数据表上，在MySQL中，创建触发器语法如下：createtrigger触发器名触发时间触发事件on表名foreachrowbegintrigger_stmtend;图9-1Navicat工具中使用SQL语句创建触发器子任务1：创建insert型触发器TR1_T1创建insert类型触发器TR1_T1，当向成绩表中插入一条成绩信息时，成绩采用百分制在0～100之间，当输入成绩小于0时，则按0输入，当输入成绩大于100，则按100输入。并执行实现查询的操作。一、使用SQL语句创建无参的存储过程并调用图9-2Navicat工具中使用SQL语句触发触发器子任务1：创建insert型触发器TR1_T1根据要求，触发器必须在数据插入表之前完成成绩的控制，所以使用beforeinsert类型触发器。当向成绩表中插入数据时，触发器自动触发，完成成绩的控制。执行以下语句将触发该触发器。运行效果如图9-2所示。一、使用SQL语句创建无参的存储过程并调用图9-3Navicat工具中使用SQL语句查询成绩表子任务1：创建insert型触发器TR1_T1打开如图9-3所示界面，通过查询语句发现存入数据库的成绩是100，而不是120。运行效果如图9-3所示。一、使用SQL语句创建无参的存储过程并调用图9-4Navicat工具中使用SQL语句创建触发器子任务2：创建insert型触发器TR1_T2创建insert类型触发器TR1_T2，增加一条学生记录时，需要检查性别是否符合范围要求。并执行插入错误验证触发器。一、使用SQL语句创建无参的存储过程并调用图9-5Navicat工具中使用SQL语句触发器生效子任务2：创建insert型触发器TR1_T2根据要求，触发器必须在数据插入表之前完成数据的控制，所以使用beforeinsert类型触发器。当向学生表中插入数据时，触发器自动触发，验证性别的输入。执行以下语句将触发该触发器。运行效果如图9-5所示。一、使用SQL语句创建无参的存储过程并调用1.MySQL创建多个触发器如何在MySQL中为相同的事件和操作时间创建多个触发器。他与MySQL5.7.2+版本相关。如果你有一个较旧版本的MySQL，部分的语句将不起作用。在MySQL5.7.2版之前，您只能为表中的事件创建一个触发器，例如，您只能为BEFOREUPDATE或AFTERUPDATE事件创建一个触发器。MySQL5.7.2+解除了这一限制，允许您为表中的相同事件和操作时间创建多个触发器。事件发生时，触发器将按顺序激活。创建第一个触发器的语法保持不变。如果您在表中有相同事件的多个触发器，MySQL将按创建顺序调用触发器。要更改触发器的顺序，您需要指定FOLLOWS或PRECEDES在FOREACHROW子句之后。FOLLOWS：选项允许在现有触发器之后激活新触发器。PRECEDES：选项允许在现有触发器之前激活新触发器。二、

使用SQL语句创建UPDATE型触发器2.创建多个触发器语法DELIMITER$$CREATETRIGGERtrigger_name[BEFORE|AFTER][INSERT|UPDATE|DELETE]ONtable_nameFOREACHROW[FOLLOWS|PRECEDES]existing_trigger_nameBEGIN…END$$DELIMITER;二、

使用SQL语句创建UPDATE型触发器图9-5Navicat工具中使用SQL语句创建触发器子任务1：创建update型触发器TR2_T1创建update型触发器TR2_T1，当修改一个记录时，确保此记录的成绩(score)在0～100分之间。并执行修改操作错误验证触发器。二、

使用SQL语句创建UPDATE型触发器图9-6Navicat工具中使用SQL语句触发器生效子任务1：创建update型触发器TR2_T1根据要求，触发器必须在数据更新表之后完成成绩的控制，所以使用AFTERUPDATE类型触发器。当向成绩表中修改数据时，触发器自动触发，完成成绩的控制。执行以下语句将触发该触发器。运行效果如图9-6所示。二、

使用SQL语句创建UPDATE型触发器图9-7Navicat工具中使用SQL语句创建触发器子任务2：创建update型触发器TR2_T2创建update型触发器TR2_T2，在学生表Student中定义一个触发器，保证修改学生的出生日期要大于学生的注册日期。并执行修改操作验证触发器。二、

使用SQL语句创建UPDATE型触发器图9-8Navicat工具中使用SQL语句触发器生效子任务2：创建update型触发器TR2_T2根据要求，触发器必须在数据更新表之后完成成绩的控制，所以使用AFTERUPDATE类型触发器。当向学生表中修改数据时，触发器自动触发，完成出生日期的控制。执行以下语句将触发该触发器。运行效果如图9-8所示。二、

使用SQL语句创建UPDATE型触发器查看触发器如果想通过语句查看数据库中已经存在的触发器的信息，可以采用两种方法：一种是利用SHOWTRIGGERS语句查看触发器，另一种是利用SELECT语句查看数据库information_schema下数据表triggers中的触发器数据。利用SHOWTRIGGERS语句查看触发器信息的语法格式如下。SHOWTRIGGERS;在MSQL中，触发器信息都保存在数据库infomationschena下的数据表triggers中，可以通过SELECT语句查看该数据表获取触发器信息。通过triggers数据表查动发器的语法格式如下。SELECT*FROMinformation_schema.triggers[MHEREtrigger_name=’触发器名称’]在上述语法格式中，可以通过WHERE子句指定触发器的名称，如果不指定触发器名称，则会查询出information_schema数据库中所有已经存在的触发器信息。三、使用SQL语句创建DELETE型触发器图9-9Navicat工具中使用SQL语句创建触发器子任务1：创建delete型触发器TR3_T1在学生表Student中创建delete型触发器TR3_T1，当删除学生表Student中学生时，自动删除成绩表studing中相应学生的选课记录。三、使用SQL语句创建DELETE型触发器图9-10Navicat工具中使用SQL语句触发器生效子任务1：创建delete型触发器TR3_T1根据要求，当删除学生表student中学生时，触发器自动触发，自动删除成绩表studing中相应学生的选课记录，完成成绩的控制。执行以下语句将触发该触发器。运行效果如图9-10所示。三、使用SQL语句创建DELETE型触发器图9-11Navicat工具中显示student表子任务1：创建delete型触发器TR3_T1删除之后student表和studing表都没有20211004。查询结果如图9-11和9-12所示。三、使用SQL语句创建DELETE型触发器图9-12Navicat工具中显示studing表图9-13Navicat工具中使用SQL语句创建新表子任务2：创建delete型触发器TR3_T2创建DELETE型触发器TR3_T2，技术人员想要在删除学生信息后，自动将删除的学生信息添加在其他数据表以防后续需要查询被删除的学生信息，具体操作如下。三、使用SQL语句创建DELETE型触发器图9-14Navicat工具中使用SQL语句创建触发器子任务2：创建delete型触发器TR3_T2接着在学生表student中创建触发器。当删除学生表的数据后，触发该触发器，并且在触发器的触发程序中将被删除的学生添加到数据表new_student中，具体SQL语句及运行效果如图9-14所示。三、使用SQL语句创建DELETE型触发器图9-15Navicat工具中使用SQL语句删除记录子任务2：创建delete型触发器TR3_T2触发器创建成功后，会根据触发时机触发事件，技术人员需要删除员工表学号’20211004’中的一条学生记录，具体SQL语句及运行效果如图9-15所示。并且想要在删除操作后查看新表new_studeng中的记录，以验证触发器是否生效，具体SQL语句及运行效果如图9-16所示。三、使用SQL语句创建DELETE型触发器图9-16Navicat工具中使用SQL语句查询记录删除触发器当创建的触发器不再符合当前需求时，可以将它删除。删除触发器的操作很简单，只需要使用MySQL提供的DROPTRIGGER语句即可。DROPTRIGGER语句的基本语法格式如下。DROPTRIGGER[IFEXISTS][数据库名.]触发器名;在上述语法格式中，利用“数据库名，触发器名”方式可以删除指定数据库下的触发器，当省略“数据库名.”时，则删除当前选择的数据库下的触发器。四、使用SQL语句删除触发器子任务1：使用SQL语句查看触发器通过语句查看数据库中已经存在的触发器的信息，可以采用两种方法。方法1：打开如图9-17所示界面，打开“hn”节点，点击“新建查询”按钮，新建查询窗口，在该窗口中输入以下语句查看触发器。四、使用SQL语句删除触发器图9-17Navicat工具中使用SQL语句查看触发器方法2：打开如图9-18所示界面，打开“hn”节点，点击“新建查询”按钮，新建查询窗口，在该窗口中输入以下语句通过WHERE子句指定触发器的名称查看触发器。四、使用SQL语句删除触发器图9-18Navicat工具中使用SQL语句查看触发器子任务2：使用SQL语句删除触发器在一次学生成绩管理系统升级之后，技术人员觉得触发器TR1_T1的使用意义不大，想要删除学生成绩管理系统中的触发器TR1_T1。具体SQL语句及执行结果如下：步骤：打开如图9-19所示界面，打开“hn”节点，点击“新建查询”按钮，新建查询窗口，在该窗口中输入以下语句删除触发器。四、使用SQL语句删除触发器图9-19Navicat工具中使用SQL语句删除触发器上述语句执行结果的信息可以得出，删除语句成功执行，此时通过如下语句再次查询触发器。SELECT*FROMinformation_schema.triggersWHEREtrigger_name='TR1_T1'从上述查询结果可以验证，触发器TR1_T1已经从数据库中成功删除。除使用DROP

TRIGGER语句删除触发器外，当删除触发器关联的数据表时，触发器也会同时被删除。四、使用SQL语句删除触发器总结1.能使用SQL语句创建INSERT型触发器

2.能使用SQL语句创建UPDATE型触发器

3.能使用SQL语句创建DELETE型触发器谢谢观看！项目10学生成绩管理系统数据安全管理数据库技术与应用了解用户与权限的作用；掌握CREATEUSE创建用户掌握ALTERUSE设置密码掌握GRANT授予用户权限本章目标学生成绩管理系统数据库提供数据共享服务，数据共享必然带来数据库的安全性问题。为了保护数据库以防止不合法的使用所造成的数据泄露、更改或破坏，可以创建视图限制用户数据的范围，创建存储过程和触发器增强系统的安全性和可靠性，对数据库进行备份。当数据库被破坏时通过备份文件进行数据库的还原，除此之外数据的安全性可以通过设置用户权限等实现。一、用户管理1、创建用户基本语法格式如下：CREATEUSER'username'@'hostname'[IDENTIFIEDBY[PASSWORD]'password'][,'username'@'hostname'[IDENTIFIEDBY[PASSWORD]'password']]....在上述语法格式中，username表示新创建用户的名称；hostname表示主机名；IDENTIFIEDBY用于设置用户的密码；PASSWORD关键字表示使用哈希值设置密码，是可选项，如果密码是一个普通字符串，就不需要使用PASSWORD关键字；password表示用户登录时使用的密码，需要用单引号括起来。CREATEUSER语句可以同时创建多个用户，多个用户之间用逗号分隔。一、用户管理2、删除用户1.使用DROPUSER语句删除用户DROPUSER语句与DROPDATABASE语句类似，如果要删除某个用户，只需要在DROPUSER后面指定要删除的用户信息即可。语法格式如下。DROPUSER‘username'@'hostname'[,'username'@'hostname'];在上述语法格式中，username表示要删除的用户，hostname表示主机名。DROPUSER语句可以同时删除一个或多个用户，多个用户之间用逗号进行分隔。值得注意的是，使用DROPUSER语句删除用户时，执行删除操作的用户必须拥有DROPUSER权限。2.使用DELETE语句删除用户DELETE语句不仅可以删除普通表中的数据，还可以删除mysql.user表中的数据。使用该语句删除mysql.user表中的数据时，需要指定表名为mysql.user和要删除的用户信息。同样地，在使用DELETE语句时，执行删除操作的用户必须拥有对mysql.user表的DELETE权限，语法格式如下。DELETEFROMmysql.userWHEREhost=‘hostname’ANDuser=‘usernane’;在上述语法格式中，mysql.user参数指定要操作的表，WHERE指定条件语句。host和user都是mysql.user表的字段，这两个字段可以确定唯一的一条记录。一、用户管理3、修改用户密码方法1：使用mysqladmin命令修改用户密码。在MySQL的安装目录bin文件夹下有一个mysqladmin.exe可执行程序，它对应的命令mysqladmin通常用于执行一些管理性的任务(如修改用户密码)，以及显示服务器状态等。使用mysqladmin命令修改密码的基本语法格式如下。mysqladmin-uusername[-hhostname]-ppasswordnew_password在上述语法格式中，username表示要修改密码的用户名；参数-h用于指定对应的主机名，可以省略不写，默认为localhost；-p后面的password为关键字，用于指定要修改的内容为密码，而不是修改后的密码；new_password为新设置的密码。一、用户管理3、修改用户密码方法2：使用ALTERUSER语句修改用户密码，基本语法格式如下。ALTERUSER账户名IDENTIFIEDBynew_password;在上述语法格式中，账户名包括用户名和主机名，new_pasword表示新设置的密码。需要注意的是，使用这种方法修改用户密码时，要求执行修改密码提作的用户有修改mysql.user数据表的权限。方式3：使用SET语句修改用户密码，基本语法各式如下。SETPASSWORD=new_password;在上述语法格式中，new_password为新设置的密码。一、用户管理3、修改用户密码

方法4：使用UPDATE语句修改用户密码。这种方法就是通过UPDATE语句直接修改mysql.user的数据，需要利用root用户登录。修改密码的基本语法格式如下。UPDATEmysql.userSETauthentication_string=PASSWORD('new_password’)WHEREUser='username'andHost='hostname';在上述语法格式中，new_pasword为新设置的密码，usemame为要修改的用户名hostname为对应的主机名。使用这种方法修改密码后，还需要使用FLUSHPRIVILEGES重新加载权限表。需要注意的是，在MySQL8.0及后续的版本中已经度弃PASSWORD()函数，因此本书也不推荐使用此种方法修改用户密码。一、用户管理子任务1：创建用户使用CREATEUSER语句创建两个新用户，用户名分别为test1和test2，密码分别为123和456，并查看新的用户。图10-1Navicat工具中使用SQL语句创建用户一、用户管理子任务1：创建用户打开如图10-2所示界面，使用SELECT语句查询mysql.user表中的数据，验证用户是否创建成功。以查询用户test1为例。运行效果如图10-2所示。图10-2Navicat工具中使用SQL语句查看用户一、用户管理子任务2：删除用户使用DROPUSER语句删除用户test1。步骤1：打开如图10-3所示界面，打开“hn”节点，点击“新建查询”按钮，新建查询窗口，在该窗口中输入以下语句。图10-3Navicat工具中使用SQL语句删除用户一、用户管理子任务2：删除用户打开如图10-4所示界面，上述语句执行成功后，可以通过SELECT语句验证用户是否被删除。运行效果如图10-4所示。图10-4Navicat工具中使用SQL语句查看用户从运行结果可以得出，mysqluser表中已经没有用户test1，说明test1用户已被成功删除。一、用户管理子任务3：修改用户密码技术人员为了方便测试系统的一些功能，创建了一个普通用户作为系统的测试账号，该账号需要分享给技术组的其他人员使用。为保证账号的安全，一旦技术组有人员离职，就需要对该用户的密码进行修改。图10-5Navicat工具中使用SQL语句创建用户一、用户管理子任务3：修改用户密码当组内有人员离职时，技术人员需要修改用户admin_test的密码。此次选择使用ALTERUSER语句修改用户密码，修改之前需要先登录root账户。登录后执行的SQL语句及执行结果如下，运行效果如图10-6所示。图10-6Navicat工具中使用SQL语句查看用户二、权限管理1、授予权限用户登录MySQL后，可以对数据进行增删改查的操作，是因为登录的用户拥有这些权限。MySQL提供了用于为用户授予权限的GRANT语句，其基本语法格式如下。GRANT权限类型[(字段列表)][，权限类型[(字段列表)]]ON权限级别TO'username'@'hostname’[,'username'@'hostname']…[WITHwith_option]在上述语法格式中，各参数的含义如下。(1)权限类型：指的是表10-3中的权限名称。(2)字段列表：表示权限设置到哪些字段上，同时给多个字段设置同一个权限时，多个字段名之间使用逗号分隔。如果不指定字段，则设置的权限作用于整个表。(3)权限级别：指表10-3中包含的权限级别，其值可以设置如下几种。二、权限管理1、授予权限*.*：表示全局级别的权限，即授予的权限适用于所有数据库和数据表。*：如果当前未选择数据库，表示全局级别的权限；如果当前选择了数据库，则为当前选择的数据库授予权限。数据库名.*：表示数据库级别的权限，即授子的权限适用于指定数据库中的所有表。数据库名.表名：表示表级别的权限。如果不指定将授予权限的字段，则授予的权限适用于指定数据库的指定表中的所有列。(4)TO子句用于指定一个或多个用户。(5)WITH关键字后面的参数with_option的取值有5个，具体如下。GRANTOPTION:将自己的权限授予其他用户。MAX_QUERIES_PER_HOURcount：设置每小时最多可以执行多少次查询。NAX_UPDATES_PER_HOURcount：设置每小时最多可以执行多少次更新。MAX_CONNECHONS_PER_HOURcount：设置每小时最大的连接数量。NAX_UER_CONNECHONS：设置每个用户最多可以同时建立连接的数量。二、权限管理2、查看权限授权语句执行成功后，可以对用户的授予权限进行查询；其中表权限可以在mysql.tables_priv中查看，列权限可以在mysql.columns_priv中查看，有两种方法，第一种是使用SELECT语句，第二种还可以使用SHOWGRANTS语句查看用户权限，其基本语法格式如下：SHOWGRANTSFOR'username'@'hostname';二、权限管理3.收回权限为保证数据库的安全，对于用户一些不必要的权限应该及时收回。MySQL提供了REVOKE语句用于收回指定用户的指定权限，其基本语法格式如下。REVOKE权限类型[(字段列表)][，权限类型[(字段列表)]]ON权限级别FROM‘username'@'hostname'[,'username'@'hostname']…上述语法格式中的权限类型表示收回的权限类型，字段列表表示权限作用的字段。如果不指定字段，则表示作用于整个数据表。当用户拥有的权限比较多时，使用上述收回方式就比较繁琐，为此MySQL提供了一次性收回所有权限的功能。一次性收回用户所有权限的语法格式如下。REVOKEALLPRIVILEGES,GRANTOPTIONFROM‘username’@‘hostname’[,‘username’@‘hostname']…二、权限管理子任务1：授予权限技术人员在使用admin_test用户测试系统时，需要为admin_test用户授予数据库student_score的学生信息表student的SELECT权限，以及对sno和sname字段的