网络数据库 课件 项目7-9 存储过程和触发器、数据安全与保护、JAVA与数据库

项目七

存储过程和触发器任务1存储过程任务2触发器任务1存储过程任务引入小林正在创建一个大型数据库系统，其中创建完成某一特定功能的存储过程。那么，怎么创建存储过程？怎么使用存储过程？存储过程有哪些参数呢？知识准备存储过程存储在数据库内，可由应用程序通过一个调用执行，而且允许用户声明变量、有条件执行以及其他强大的编程功能。存储过程可以使得数据库的管理、显示关于数据库及其用户信息的工作容易得多。存储过程包含程序流、逻辑以及对数据库的查询。它们可以接受参数、输出参数、返回单个或多个结果集以及返回值。可以出于任何使用SQL语句的目的来使用存储过程，它具有以下优点：可以在单个存储过程中执行一系列SQL语句。可以从自己的存储过程内引用其他存储过程，这可以简化一系列复杂语句。存储过程在创建时即在服务器上进行编译，所以执行起来比单个SQL语句快，且能减少网络通信的负担。一、创建存储过程要使用存储过程，首先要创建一个存储过程。使用CREATEPROCEDURE语句创建存储过程。其中，各参数含义如下：procedure_name：新存储过程的名称。number：是可选的整数，用来对同名的过程分组，以便用一条DROPPROCEDURE语句将同组的过程一起除去。@parameter：过程中的参数。data_type：参数的数据类型。VARYING：指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化）。仅适用于游标参数。Default：参数的默认值。OUTPUT：表明参数是返回参数。{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}：RECOMPILE表明SQLServer不会缓存该过程的计划，该过程将在运行时重新编译。ENCRYPTION表示SQLServer加密syscomments表中包含CREATEPROCEDURE语句文本的条目。FORREPLICATION：指定不能在订阅服务器上执行为复制创建的存储过程。sql_statement：过程中要包含的任意数目和类型的SQL语句。二、执行存储过程执行存储过程使用EXECUTE语句其中，各参数含义如下：@return_status：是一个可选的整型变量，保存存储过程的返回状态。procedure_name：是调用的存储过程的名称。number：是可选的整数，用于将相同名称的过程进行组合，使得它们可以用一句DROPPROCEDURE语句除去。该参数不能用于扩展存储过程。@procedure_name_var：是局部定义变量名，代表存储过程名称。@parameter：是过程参数，在CREATEPROCEDURE语句中定义。value：是过程中参数的值。OUTPUT：指定存储过程必须返回一个参数。DEFAULT：根据过程的定义，提供参数的默认值。案例——创建存储过程stud_grade，用于检索所有学生的成绩记录案例——使用系统存储过程查看EMIS数据库和学生表的相关信息检索所有学生的成绩记录

查看EMIS数据库和学生表的相关信息三、存储过程的参数在创建和使用存储过程时，其参数是非常重要的。下面详细讨论存储过程的参数传递和返回。1．使用参数在调用存储过程时，有两种传递参数的方法。2．使用默认参数创建存储过程时，可以为参数提供一个默认值，默认值必须为常量或者NULL。3．使用返回参数在创建存储过程时，可以定义返回参数。案例——创建存储过程average，返回两个参数@st_name和＠st_avg，分别代表了姓名和平均分。查询学号为“17043120”的学生姓名和平均分4．存储过程的返回值存储过程在执行后都会返回一个整型值。5．存储过程的删除不再需要存储过程时可将其删除。通过DROPPROCEDURE语句来完成。任务2触发器任务引入小林想要保证数据库系统中数据完整性，还想在多个表中添加、更新或删除行时，保留表之间所定义的关系，他咨询师兄，师兄告诉他可以在程序中设置一个触发器。那么，怎么创建触发器？怎么使用触发器呢？知识准备触发器在INSERT、UPDATE或DELETE语句对表或视图进行修改时会被自动执行。触发器可以查询其他表，并可以包含复杂的SQL语句。一个表可以有多个触发器。触发器具有如下优点：触发器可通过数据库中的相关表实现级联更改。但是，通过级联引用完整性约束可以更有效地执行这些更改。触发器可以强制比用CHECK约束定义的约束更为复杂的约束。触发器也可以评估数据修改前后的表状态，并根据其差异采取对策。一个表中的多个同类触发器（INSERT、UPDATE或DELETE）允许采取多个不同的对策，以响应同一个修改语句。确保数据规范化。一、创建触发器1．使用SSM创建触发器（1）在资源管理器相应的数据库中的表节点下选择“触发器”，右击鼠标，在弹出的快捷菜单中选择“新建触发器”选项，如图所示。（2）打开对应的窗口，在窗口中修改创建触发器的模板代码，如图所示。2．使用T-SQL创建触发器

快捷菜单

创建触发器模板二、使用触发器在SQLServer中，除了INSERT、UPDATE和DELETE3种触发器外，还提供了INSTEADOFINSERT、INSTEADOFUPDATE和INSTEADOFDELETE触发器。1．INSERT触发器当向表中插入记录时，INSERT触发器被执行。一般情况下，这种触发器常用来检查插入数据是否满足要求。案例——创建一个触发器，当执行INSERT操作时，该触发器被触发（1）创建触发器。（2）执行INSERT操作。2．UPDATE触发器当向表中更新记录时，UPDATE触发器被执行。案例——创建一个触发器，当执行UPDATE操作时，该触发器被触发（1）创建触发器。（2）执行UPDATE操作。3．DELETE触发器创建触发器测试触发器trigger1的执行创建触发器测试触发器trigger2的执行三、删除触发器除了在对象资源管理器中删除触发器外，也可以使用DROPTRIGGER语句来删除触发器。案例——删除trigger1触发器项目总结项目实战实战一创建名为PRO的存储过程来查询部门号为30的员工信息实战二创建一个触发器，更新7521号员工的工资为6000（1）创建触发器。（2）触发触发器并更新数据。查询部门号为30的员工信息更改工资项目八

数据安全与保护任务1账号和角色任务2备份和还原数据库任务3数据的导入和导出任务1账号和角色任务引入小林经过这一段时间的学习，终于建成一个达到自己要求的数据库系统，为了防止别人访问或修改数据库，他创建了一个登陆账号和用户名。那么，怎么创建登陆账号？怎么创建用户名名，并将用户名关联到登陆账号？知识准备在SQLServer中，账号有两种，一种是登录服务器的登录账号（loginname），另外一种是使用数据库的用户账号（username）。登录账号只是让用户登录到SQLServer中，登录名本身并不能让用户访问服务器中的数据库。要访问特定的数据库，还必须具有用户名。用户名在特定的数据库内创建，并关联一个登录名（当一个用户创建时，必须关联一个登录名）。用户定义的信息存放在服务器上的每个数据库的sysusers表中，用户没有密码同它相关联。通过授权给用户来指定用户可以访问的数据库对象的权限。一、服务器的登录账号在安装SQLServer后，系统默认创建登录账号。在对象资源管理器中。展开“安全性”→“登录”节点，即可看到系统创建的默认登录账号，如图所示。其中，默认登录账号sa是超级管理员账号，允许SQLServer的系统管理员登录。系统默认创建的登录账号案例——新建一个登录账号，使其能够通过混合验证的方式访问教育管理系统快捷菜单

“登录名-新建”对话框“服务器角色”选项卡“用户映射”选择页“状态”选择页快捷菜单“服务器属性”对话框“连接到服务器”对话框服务器二、数据库的用户如果用户想访问SQLServer，首先必须要有一个适当的登录账号和密码来登录到SQLServer，用户登录后，并不意味着就能自动访问由SQLServer管理的数据库中的数据了，他们还必须有一个适当的数据库用户账号，才能访问SQLServer的数据。登录账号存储在主数据库的系统登录表中，用户账号存储在各个数据库的系统用户表中。如图所示，有4个不同的登录账号有权登录到SQLServer数据库

SQLServer登录账号和数据库用户账号

系统自动创建的用户账号案例——在数据库中创建一个用户账号，并将其关联到liuly登录账号中

快捷菜单

“选择登录名”对话框“查找对象”对话框新建用户账号三、角色角色是一个强大的工具，可以将用户集中到一个单元中，然后对该单元应用权限。对一个角色授予、拒绝或废除的权限也适用于该角色的任何成员。可以建立一个角色来代表单位中一类工作人员所执行的工作，然后给这个角色授予适当的权限。当工作人员开始工作时，只需将他们添加为该角色成员，当他们离开工作时，将他们从该角色中删除。而不必在每个人接受或离开工作时，反复授予、拒绝和废除其权限。权限在用户成为角色成员时自动生效。和登录账号类似，用户账号也可以分成组，称为数据库角色（DatabaseRoles）。在SQLServer中，角色可分为两种：数据库角色：由数据库成员所组成的组，此成员可以是用户或者其他的数据库角色。应用程序角色：用来控制应用程序存取数据库的，本身并不包含任何成员。1．数据库角色在数据库创建时，系统默认创建10个固定的标准角色。展开school数据库“安全性”节点中“角色”→“数据库角色”，即可看到其中默认的10个固定角色，如图所示。

默认的10个标准角色案例——为liuly分配数据库读写权限

快捷菜单“数据库角色属性—db_datareader”对话框“选择数据库用户或角色”对话框“查找对象”对话框案例——创建新角色2．应用程序角色SQLServer中的安全系统在最低级别，即数据库本身上实现。无论使用什么应用程序与SQLServer通信，这都是控制用户活动的最佳方法。快捷菜单

“数据库角色-新建”对话框任务2备份和还原数据库任务引入小林发现了创建的数据库有的数据丢失了，急的小林直上火。这时他同学告诉他，最好经常备份数据库，这样能减少丢失数据的危险。那么，怎么备份数据库呢？怎么将备份的数据库进行还原呢？知识准备对于计算机用户来说，对一些重要文件、资料定期进行备份是一种良好的习惯。同样，对于数据库管理员和用户，对数据库备份与还原（或恢复）依然是一项重要且不可缺少的工作。因为在一个复杂的大型数据库中，造成数据丢失的原因有很多。用户可能对数据库进行误操作或者恶意操作、物理磁盘的数据冲突、外界突发事件的影响等等，这些都有可能造成数据损失甚至是系统崩溃。在这时候，就需要根据以前的数据库备份采取符合需求的还原和重建工作。一、备份数据库数据库备份就是创建备份完成时数据库内存在的数据的副本。数据库备份是一件相当麻烦的工作，需要认真进行规划。因为不同的备份策略直接影响到数据库的还原工作。例如，如果在备份数据库的时候采取的策略是只备份数据库而不备份事务日志，那么在出现意外情况后对数据库进行还原，就只能还原到上一次备份之前的程度，备份之后的信息全部丢失。所以要针对不同的情况全面考虑以采取最符合实际的备份策略。（一）数据库备份1．使用SSMS创建数据库备份

快捷菜单“SQLServer备份”对话框的“常规”选项卡“选择备份目的”对话框“介质选项”选项卡“备份选项”选项卡

提示对话框2．使用T-SQL命令备份数据库（二）数据库事务日志备份事务日志是自上次备份事务日志后对数据库执行的所有事务的一系列记录。可以使用事务日志备份将数据库还原到特定的即时点（如输入多余数据前的那一点）或还原到故障点。一般情况下，事务日志备份比数据库备份使用的资源少。因此可以比数据库备份更经常地创建事务日志备份。经常备份将减少丢失数据的危险。事务日志备份有时比数据库备份大。例如，数据库的事务率很高，从而导致事务日志迅速增大。在这种情况下，应更经常地创建事务日志备份。数据库事务日志备份的创建步骤和数据库备份的创建方法是一样的，只需要在“备份数据库”对话框的“备份类型”下拉列表中选择“事务日志”即可，这里就不在详细讲述了。二、数据库还原数据库还原也称为数据库恢复。（一）还原数据库备份1．使用SSMS还原数据库备份“删除对象”对话框快捷菜单“选择备份设备”对话框“定位备份文件”对话框

“还原数据库”对话框提示对话框

还原EMIS数据库2．使用T-SQL命令还原数据库备份（二）还原事务日志备份还原事务日志备份除以下参数说明外，其他参数与RESTOREDATABASE语句的相同：STOPAT=date_time|@date_time_var：指定将数据库还原到其在指定的日期和时间时的状态。如果对STOPAT使用变量，则该变量必须是varchar、char、smalldatetime或datetime数据类型。只有在指定的日期和时间前写入的事务日志记录才能应用于数据库。STOPATMARK='mark_name'[AFTERdatetime]：指定还原到指定的标记，包括包含该标记的事务。STOPBEFOREMARK='mark_name'[AFTERdatetime]：指定还原到指定的标记，但不包括包含该标记的事务。任务3数据的导入和导出任务引入小林想在SQLServer数据库服务器与其它数据库服务器间移动数据，怎么才能将数据移动到其他数据服务器中呢？小林就这问题咨询老师，老师告诉他可以将SQLServer数据库服务器中的数据导出，或者将其他数据库服务器中的数据导入到SQLServer数据库服务器来实现。那么，怎么将数据导出呢？怎么将数据导入呢？知识准备导入和导出数据是在不同应用之间按普通格式读写数据从而实现数据交换的过程。一、数据的导出“SQLServer导入和导出向导”对话框“选择数据源”页面

“选择目标”页面“指定表复制或查询”页面

“选择源表和源视图”页面“列映射”对话框“查看数据类型映射”页面“查看数据类型映射”页面“执行已完成”页面导出的数据二、数据的导入“选择数据源”页面“选择目标”页面

“选择源表和源视图”页面“完成向导”页面“执行成功”页面

导入的表项目总结项目实战实战一创建一个登录账号ABC/123456（1）在对象资源管理器中选择“登录名”节点，右击鼠标，在弹出的快捷菜单选择“新建登录”选项，打开“登录名-新建”对话框。（2）输入登录名为“ABC”，选择“SQLServer身份验证”单选按钮，输入密码为123456。然后在“默认数据库”列表框中选择“company”数据库。（3）在“服务器角色”选择页中，选择“securityadmin”服务器角色。（4）在“用户映射”选择页中勾选“company”数据库复选框。（5）设置完成后，单击“确定”按钮，创建一个名称为ABC的登录账号。实战二在company数据库中创建一个用户账号，并将其关联到ABC登录账号中（1）在对象资源管理器中，展开“数据库”→company节点，选择“用户”节点，右击鼠标，在弹出的快捷菜单中选择“新建用户”命令，打开“数据库用户-新建”对话框。（2）在“用户名”和“登录名”中输入ABC。（3）设置完成后，单击“确定”按钮，即可在ABC数据库中创建一个新的用户账号。项目九

JAVA与数据库任务1概述任务2利用JDBC访问数据库任务3利用Hibernate访问数据库任务4利用JAVA创建图形界面任务1概述任务导入小林已经在SQLServer创建好了数据库，并学会在SQLServer查询所需数据，现在他想通过JAVA来访问数据库。那么，可以使用哪些方法来通过JAVA访问数据库呢？在使用JAVA访问数据库之前的先安装Java开发包。那么，怎么安装JAVA的开发包JDK呢？知识准备一、JAVA访问数据库的方法JAVA可以访问的数据库类型有许多种,以上主流的关系型数据库管理系统都支持。JAVA访问数据库的方法有如下几类：1．使用JDBC（JavaDataBaseConnection,Java数据库连接）方式JDBC是Java应用访问关系型数据库的接口规范。2．使用MyBatis框架方式Mybatis是开源的持久层框架，能够对JDBC进行简单的封装，但其并不是完全的ORM（ObjectRelationalMapping，对象关系映射），无法脱离数据库进行适配。3．使用ORM框架方式ORM是内存中的对象和关系型数据库中的记录之间的映射关系，实现了JPA（JavaPersistenceAPI）规范，Sun引入新的JPAORM规范出于两个原因：其一，简化现有JavaEE和JavaSE应用开发工作；其二，Sun希望整合ORM技术，形成统一的标准化的规范。二、JDK的安装JDK的全称为JavaDevelopmentKit，即Java开发包，这是用Java访问数据库的必装软件工具包，对于JDBC和JPA本来就是JDK的一部分，ORM方式的使用也会依赖于JDK，下文以JDK17.0.2（64位）在Windows1064位的安装为例进行讲解。ORM框架流行度排名图（1）登录Oracle公司官网，下载JavaSE的最新稳定版。（2）下载完成后，双击下载的文件启动安装向导，如图所示。（3）单击“下一步(Next)”按钮，选择安装JavaSE的目标文件夹，如图所示。（4）单击“下一步(Next)”按钮，开始安装程序，并显示进度条。（5）如果不需要访问JDK的官方文档，单击安装向导中的“关闭（Close）”按钮，即可完成安装。

安装向导

指定目标文件夹（6）在任务栏单击“显示桌面”菜单项，这种可看到桌面，此时按住“Shift”键不松开，单击鼠标右键，出现如图所示的菜单，然后选择“在此处打开PowerShell窗口”菜单项完成安装菜单查看安装的JDK版本任务2利用JDBC访问数据库任务导入小林已经安装好了JAVA的开发包JDK，他想利用JDBC访问在SQLServer中创建的教学管理系统数据库，并对学院表中的数据进行修改和查询。那么，怎么下载安装JDBC呢？怎么利用JDBC访问数据库呢？知识准备在利用JDBC访问SQLServer数据库之前得先安装JDBC，下面介绍JDBC驱动包的下载和配置。（1）JavaJDBCforSQLServer驱动包的下载（2）以JavaJDBCforSQLServer驱动包配置构建路径（ConfigureBuildPath）以下2种办法根据项目类型选择其中一种即可。方法一：针对普通的JavaProject采取手工配置构建路径的办法方法二：针对MavenProject，采用自动配置构建路径的办法。添加用户库的菜单入口“新建用户库”对话框选择JAR包对话框添加SQLServerJDBC依赖来自动配置

选择Maven项目选择简单的项目配置Maven项目案例——访问EMIS数据库中学院表通过Java控制台程序来查询EMIS数据库中的所有学院，并完成增加、修改和删除学院的功能。

运行结果任务3利用Hibernate访问数据库任务导入小林通过上一个任务的学习已经掌握了利用JDBC访问数据库的方法，他还想利用Hibernate访问在SQLServer中创建的教学管理系统数据库，并对课程表中的数据进行修改和查询。那么，怎么下载安装Hibernate框架呢？怎么利用Hibernate访问数据库呢？知识准备在利用Hibernate访问SQLServer数据库之前得先安装Hibernate框架，下面介绍Hibernate框架的下载和配置。

添加用户库的菜单入口“添加库（AddLibrary）”对话框

新建用户库对话框

添加外部JAR包Hibernate库配置完成后的界面搜索和安装过程确认HibernateTools功能点选中的对话框

安装条款同意对话框非签名软件的的确认框Eclipse重启的确认框Hibernate配置是否成功的验证框案例——访问EMIS数据库中课程表通过Java控制台程序来查询EMIS数据库中的所有课程，并完成增加、修改和删除课程的功能。选择Hibernate配置文件(cfg.xml)向导父文件夹选择对话框Hibernate配置文件的数据库连接配置Hibernate配置文件的Hibernate属性配置添加类新建类Course的基本信息类Course的基本属性和方法类Course的getter/setter方法setter和getter方法自动生成的触发菜单创建setter和getter方法图9-36CourseDemo的基本信息

课程表操作的运行结果

编辑映射项配置浏览选择eduMIS2/Course.hbm.xml文件完成编辑后的效果图

新建HibernateXML映射文件建立类名Course和Course.hbm.xml文件的映射关系CourseDemo.java的运行结果任务4利用JAVA创建图形界面任务导入小林通觉得不管是利用JDBC访问数据库他还是利用Hibernate访问数据库都比较麻烦，他想在JAVA的界