结构化查询语言SQL语句的使用ppt课件

5数据库管理与保护,数据库运行的最小逻辑工作单位是事务，所有对数据库的操作，都以事务作为一个整体来执行或撤销。数据库管理系统以事务为基础对数据库提供了一些监控和管理功能，这些管理和保护技术主要包括：并发控制数据库恢复完整性控制安全性控制,事务的概念事务：一个逻辑工作单元的数据库操作集合，称为事务。事务由一条或多条SQL语句构成。一个应用程序可以包含多个事务。,【例】在School数据库中完成一个学生退学的操作可以定义为一个事务，该操作包括：将成绩表中该生相关课程和成绩记录写入历史成绩表；删除成绩表中该生相关记录；将该生信息写入历史学生信息表；删除学生表中该生的记录；,如果多条SQL语句其中某些没有正确执行，那么数据库中的信息就可能会不一致。若把这些操作定义为一个事务，那么如果有语句执行错误，所有已经执行的语句都会回滚，恢复该事务执行前的状态，因此，这些语句要么所有都执行成功，要么所有执行失败，就可以避免数据的不一致。,5.1事务管理,2事务的特性事务具有以下四个特性，简称ACID：,事务的原子性(Atomic)：指组成一个事务的多个数据库操作是一个不可分隔的原子单元，只有所有的操作执行成功，整个事务才被提交。事务的一致性(Consistency)：指事务在完成时，必须使所有的数据都保持一致状态，仍然满足相关约束规则，以保持所有数据的完整性。事务的隔离性(Isolation)：指当有多个事务并发执行时，彼此互不干扰，与它们先后单独执行时的结果一样。事务的持久性(Durabiliy)：指一个事务完成之后，它对于数据库的所有修改永久性有效，即使出现系统故障造成数据错误或丢失也能恢复。,5.1事务管理,3.SQLServer的事务管理机制SQLServer通过强制事务管理和事务处理，保证每个事务符合ACID特性。主要管理机制有以下几个方面：锁定机制：通过加锁使事务相互隔离，保持事务的隔离性，支持多个事务并发执行。记录机制：将事务的执行记录在事务日志文件中，保证事务的持久性。即使服务器硬件、操作系统或SQLServer自身出现故障，SQLServer也可以在重新启动时使用事务日志，将所有未完成的事务自动地回滚到系统出现故障的位置。强制管理：强制保持事务的原子性和一致性。事务启动之后，就必须成功完成，否则SQLServer将撤消该事务启动之后对数据所作的所有修改。,5.1事务管理,4事务的定义和应用数据库管理系统可以按照系统默认的规定自动划分事务并强制管理，一般一条语句就是一个事务。,例如：修改School的Student表，将所有女生是否住校LiveInDom设置为1。UPDATEStudentSETLiveInDom=1WHERESex=女这条语句影响表中多行数据，系统通过事务保证该语句正确完成对所有相关记录的修改。,用户在开发数据库应用程序时，可根据数据处理需要自己定义事务。BeginTransaction事务的定义开始；CommitTransaction提交事务；RollBackTransaction取消事务。,5.1事务管理,【例5-1】将退学操作的SQL操作定义为一个事务，假定退学学生学号为”2015141442015”，历史学生信息表为HistoryStudent，历史学生成绩表为HistoryGrade。BeginTransactionINSERTINTOHistoryGradeSELECT*FROMGradeWHEREStudentCode=2015141442015DELETEFROMGradeWHEREStudentCode=2015141442015INSERTINTOHistoryStudentSELECT*FROMStudentWHEREStudentCode=2015141442015DELETEFROMStudentWHEREStudentCode=2015141442015CommitTransaction,该事务如果有其中任何一条语句执行错误，事务会回滚来撤消已执行的语句对数据库的更新操作结果。,5.1事务管理,5.2并发控制,2)数据获取错误：当一个事务恰好读取了另一个事务在回滚之前修改的数据，那么这个数据与数据库的实际数据不符合，该数据被称为“脏数据”。,3)不可重读：一个事务读取某数据后，该数据被另一事务更新，当该事务再读该数据进行校验时，数据不一致。,1.并发访问问题1)丢失修改：当两个事务同时对同一数据修改时，最后只有一个生效，另一个修改丢失。,允许数据库中的相同数据同时被多个事务访问。,例:两人同时定机票,修改剩余机票数。,例如，一个定票事务修改了剩余机票数后回滚，另一事务恰在其回滚前读取剩余机票，事务就获取了“脏数据”。,2.锁定管理数据库通过锁定机制解决并发访问的问题。锁定可以防止用户读取正在由其他用户更改的数据，并可以防止多个用户同时更改相同数据，解决数据库并发访问所带来的数据不一致问题。,锁定管理包括：加锁、锁定和解锁。事务在访问某数据对象之前，向系统申请加锁，加锁的数据对象在被其它事务访问时受到限制，待事务完成后锁被释放。,5.2并发控制,3.SQLServer的锁粒度和锁模式SQLServer具有多粒度锁定，允许一个事务锁定不同类型的数据对象。锁定在较小的粒度（例如行）可以增加系统并发访问能力但需要较大的系统开销；锁定在较大的粒度就并发而言是相当昂贵的，但要求的系统开销较低，因为需要维护的锁较少。,系统根据事务自动确定锁定粒度并实施锁定：,5.2并发控制,SQLServer使用不同的锁模式锁定资源，这些锁模式用于确定并发事务访问资源的方式。,表4-19SQLServer使用的锁模式,5.2并发控制,3.SQLServer的锁粒度和锁模式,4.SQLServer的并发控制方式（1）乐观并发控制：乐观并发控制假定不太可能在多个用户间发生资源冲突，允许不锁定任何资源而执行事务。（2）悲观并发控制：系统使用锁阻止事务执行可能对其他事务产生负面影响的操作。默认使用悲观并发控制。,SQLServer不必由用户指定和配置就可以自动动态确定每个SQL语句的锁的级别和执行锁定。但用户仍可以通过以下方法自定义应用程序中的锁定：处理死锁和设置死锁优先级。处理超时和设置锁超时持续时间。设置事务隔离级别。对SELECT、INSERT、UPDATE和DELETE语句使用表级锁定提示。配置索引的锁定粒度。游标设置,5.2并发控制,磁盘的物理损坏、系统瘫痪、恶意破坏、数据操作失误等都有可能造成数据库损毁、数据不正确或部分数据丢失等。,1备份的对象：(1)数据库本身。一个数据库的每次备份都产生一个独立的数据库备份文件，它是数据库恢复的基础。(2)事务日志。对每一个数据库的事务日志的每次备份都会产生一个独立的日志备份文件。,备份：用来还原和恢复数据库的资料副本称为备份。,2备份的介质备份一般需要较大容量的存储设备，常用的备份介质：(1)磁盘设备(2)磁带设备,5.3数据库备份和恢复,3备份策略完整数据库备份备份是数据库的完整复本，可用它重建或恢复到备份时刻的数据库状态。占用存储空间大，备份所需时间较长。无法恢复到故障点。,(4)文件和文件组备份当一次进行完整数据库备份占据过大资源或耗时太长时，可以对部分数据库文件或文件组进行备份。同时必须进行单独的事务日志备份。在恢复一个文件备份后，使用事务日志将文件内容前滚，使其与数据库其余部分一致。,(3)事务日志备份事务日志备份转储上一次数据库备份之后的事务日志。在某一刻的数据库备份恢复之后，进一步依据事务日志前滚事务可恢复到最后一次备份事务日志中的某一时刻。如果在故障发生后，立即备份事务日志，可使数据库恢复到故障发生的那一刻。事务日志备份占用的资源比较小。,(2)数据库差异备份仅复制自上一次完整数据库备份之后修改过的数据库页。所占空间小，备份速度快，可频繁进行。在完整数据库备份恢复的基础上，差异备份能恢复数据库到最后一次差异备份时刻，但无法恢复到故障点。,可综合使用各种备份策略。一般至少包括数据库备份和日志备份两部分。,【例5-3】完整数据库备份与事务日志备份相结合的备份策略应用。1）对School数据库每天清晨0:00做完整备份，每隔4小时做事务日志备份；2）如果某天上午11:00数据库发生故障，使用完整数据库备份可恢复到当天0:00的数据库状态，再使用日志可以恢复到上午8:00的数据库状态；3）如果在数据库发生故障时，数据库管理系统可以工作，那么可以立即备份数据库日志，使用这个日志就可以将数据库恢复到故障点。,【例5-2】完整数据库备份及可恢复性：对School数据库的完整备份。School数据库每天清晨0:00做完整备份，如果某天上午11:00数据库发生故障，使用完整数据库备份只能恢复到当天0:00的数据库状态，当天0:00到11:00对数据库的操作全部丢失。,5.3数据库备份和恢复,4数据库恢复数据库恢复是与备份相对应的操作，根据备份策略的不同可以选择不同的恢复策略，常用的策略有：,(2)完全恢复依据数据库备份和事务日志备份将数据库恢复到任意的时刻点。操作复杂，并且需要大量磁盘空间，对操作频繁且数据恢复非常重要的数据库才使用该策略。,(1)简单恢复依据完全数据库备份和增量数据库备份恢复数据库到最后一次备份的执行时刻。操作简单，备份操作点之后的数据丢失需要重建，适用于规模较小或数据不经常改变的数据库。,5.3数据库备份和恢复,5SQLServer的数据库备份和恢复(1)人工进行数据库备份在可视化环境下完成当前时刻数据库的备份，其主要步骤包括选择备份数据库、选择备份策略、添加备份目的文件或设备、设置重写方式等。,【例5-4】将School数据库备份到磁盘文件D:SchoolBackup。,1)选中数据库，在右键菜单中选择“任务”-“备份”,5.3数据库备份和恢复,2)选择备份策略为完全备份，点击添加按钮设置备份的目的设备；在选择备份目的窗口添加备份目的设备或文件，设置目的文件为：D:SchoolBackup。,3)单击【确定】按钮开始备份，完成后出现提示对话框。,(2)系统定时自动进行数据库备份使用数据库维护计划工具可以定义数据库维护计划，让系统定时自动进行数据库备份。维护计划每次执行备份会根据当时时间生成一个新的备份文件名存放备份。,【例5-5】建立一个数据库维护计划SchoolBackup，将School数据库每天0:00备份一次，事务日志每隔4小时备份一次，备份保存到磁盘目录D:SchoolBak下。,“管理”-“维护计划”，右键菜单中点击“新建维护计划”；在出现的对话框中输入维护计划名“SchoolBackup”后按“确定”，进入“维护计划设计窗口”,5.3数据库备份和恢复,2)从工具箱中将“备份数据库”任务拖动至设计窗口。,3)右击“备份数据库”任务，在右键菜单中点击“编辑”，进入“备份数据库”对话框，在其中选择要备份类型、备份的数据库,4)设置备份文件存放的目录位置后，按“确定”。,5)点击“计划”后的“”按钮，进入作业计划调试方式设置窗口。,6)设置频率为每天0:00:00执行一次，开始日期为“2016/10/1”，单击“确定”完成设置。这样就建立好了对数据库文件的备份任务。,5.3数据库备份和恢复,7)在计划窗口中添加一个子任务，重复2)-6)建立对事务日志进行备件的任务,使用数据库维护计划时要注意：,SQLServerAgent服务必须启动正常运行。,2）系统为数据库、事务日志备份文件及作业调度报表文件自动命名，保证每次生成独立的文件。,5.3数据库备份和恢复,3)右击维护计划，在右键菜单中点击“查看历史记录”，可查看维护计划的执行情况,(3)数据库的恢复SQLServer的数据库恢复可以由数据库管理员通过SSMS提供的数据库还原工具人工完成，还可使用SQL语句完成。,【例5-6】使用School备份文件D:SchoolBackup.bak恢复数据库。,1)在控制台根目录窗格选中数据库，点右键找到“任务/还原/数据库”菜单；,2)在“还原数据库窗口”的“常规”窗口中，指定“目标数据库”为“School”,“源设备”为D:SchoolBackup.bak,5.3数据库备份和恢复,3)在“还原数据库窗口”的“选项”窗口中，指定其它还原选项后，按“确定”按钮，开始数据库还原。,6系统群集备份技术群集备份技术采用网络上两个以上运行着相同数据库系统的服务器，当一个服务器出现故障后，其它服务器接管该服务器继续工作。,双机热备份技术：操作系统、数据库管理系统和应用程序安装在两台服务器的本地硬盘上，通过一个虚拟名称对外服务，某一时刻只有一台处于活动状态。系统信息以及数据库数据同时写入两台服务器，保证数据即时同步。双机侦测软件检测到当前活动的服务器故障发生时会自动激活备份服务器接管服务，保证系统服务不间断。,5.3数据库备份和恢复,SQLServer通过数据转换服务(DataTransformationServices，DTS)提供数据导入/导出功能，可实现在同类或异类OLEDB和ODBC数据源之间的数据导入、导出、验证以及转换。,【例5-7】将School中班级表ClassInfo数据导出到Excel文件D:Class.xls。,1)选中数据库，点右键找到“任务/导出数据”菜单，进入DTS导入/导出/向导首页。,5.3数据库备份和恢复,7数据导入和导出,2)按“下一步”，进入“选择数据源”窗口，在其中选择School数据库。,3)按“下一步”，进入“选择目标”窗口，在其中将目标选择为“MicrosoftExcel”,文件选为“D:Class.xls”,4)点“下一步”，在“指定表复制和查询”窗口中选择“复制一个表或视图的数据”,5.3数据库备份和恢复,5)点“下一步”，在“选择源表和源视图”窗口中选择“dbo.ClassInfo”,6)点“下一步”，在“查看数据类型映射”窗口中确认数据类型映射是否正确，如果不正确，可以进行修改,7)点“下一步”，在“保存和运行包”窗口选择“立即运行”,8)点“完成”后，执行此次数据导出，生成D:Class.xls,8附加和分离数据库在复制数据库前数据库必须脱离SQLServer，只能实现脱机复制。,【例5-8】将School数据库分离，然后将数据库文件SchoolDB.MDF复制为SchoolDB_Bak.MDF，再附加数据库SchoolDB_Bak.MDF。,1）分离数据库：在“数据库”文件夹找到School数据库，右击选择“任务/分离”菜单。在“分离数据库”对话框中，当数据库状态显示“就绪”时，单击【确定】按钮，即可实现数据库的分离。,5.3数据库备份和恢复,2）附加数据库：右击“数据库”，在右键菜单中选择“附加”。,3）在“附加数据库”窗口中选择要附加的文件后，点击“确定”即可附加数据库。,DBMS的安全性控制，主要是指保护数据库，防止因用户非法使用数据库造成数据泄露、更改或破坏。,1数据安全控制技术（1）用户标识和认证：用户名及口令验证。（2）访问控制：DBMS通过授权可以定义合法用户访问数据库内各个对象的操作权限。（3）视图机制：在DBMS中，为不同的用户定义不同的视图，通过视图机制把要保密的数据对无权操作的用户隐藏起来。（4）审计方法：审计可以跟踪用户的全部操作记录下来放入审计日志文件中，可以利用审计跟踪的信息，重现导致数据库现有状况的一系列事件，找出非法存取数据的人、时间和内容等。（5）数据加密：根据一定的算法将原始数据（称为明文）变换为不可直接识别的数据格式（称为密文），从而使得不知道解密算法的人即使进入数据库也无法识别数据的内容。,5.4数据库安全性控制,5.4.1SQLServer登录管理,2SQLServer的安全管理和控制SQLServer提供了对数据库中的数据对象的授权用户访问控制，可以使用的安全技术包括身份验证模式、登录、用户、角色、授权、废除和拒绝对象上的权限以及数据加密等。SQLServer在三个层次上进行安全管理:,数据库安全管理的三个层次,5.4数据库安全性控制,（1）SQLServer的身份认证模式Windows认证模式(集成身份认证模式)允许一个已登录Windows的网络用户不必再提供一个单独的登录账号就能登录到该系统中的一个SQLServer服务器，从而实现SQLServer服务器与Windows登录的安全集成SQLServer认证模式。用户必须输入有效的SQLServer登录账号及口令。这个登录账号是独立于操作系统的登录账号的，从而可以在一定程度上避免操作系统层上对数据库的非法访问。,（2）设置身份认证模式系统在安装时会询问使用何种认证模式，也可使用SSMS重设认证模式：1）在SSMS中右击需要设置的SQL服务器，在弹出菜单中选择“注册”命令。2）在“新建服务器注册”窗口中选择认证模式。,5.4数据库安全性控制,（3）登录账号管理登录账号是服务器级用户访问数据库系统的标识，登录账号的信息是系统信息，存储在master数据库中。SQLServer默认的登录账号：sa被称为系统管理员(SystemAdministrator)，在SQLServer系统中它拥有全部权限，可以执行所有的操作。,5.4数据库安全性控制,（4）创建登录账号,【例5-9】创建一个名称为“Wanghong”的SQLServer认证帐号，并为其设置密码“123456”，设置其默认访问的数据库为School。,展开“安全性”-“登录名”，右键菜单中选择“新建登录名”，在窗口中输入信息后，点击“确定”。,（5）编辑登录账号展开“安全性”-“登录名”-需修改的登录帐号，双击登录帐号，出现“登录属性”窗口，在其中可修改登录帐号的属性。此窗口与创建容器相同,5.4数据库安全性控制,（6）删除登录账号展开“安全性”-“登录名”-需删除的登录帐号，右击登录帐号，在右键菜单中选“删除”，在“确认删除”窗口中点击“确定”。,1.数据库用户及其权限管理1）数据库用户SQLServer登录帐户建立后，必须映射为数据库用户才能访问数据库。登录帐户已经映射为其默认访问数据库的用户,5.4数据库安全性控制,5.4.2数据库级管理,【例5.10】将帐户“Wanghong”映射为数据库School的用户,展开“数据库”-“School”-“安全性”-“用户”，在右键菜单中选择“新建用户”,在“新建用户”窗口中，用户名填写“Wanghong”，登录名选择为“Wanghong”，默认架构选择为“dbo”，数据库角色身份选择为“db.dbowner”，点击“确定”,角色“db.dbowner”具有对该数据库的所有权限,SQLServer可访问的数据库对象与操作权限如下表所示：,5.4.2数据库级管理,2)数据库用户权限管理,权限操作授予权限：有权限，可被用户其它角色的拒绝权限覆盖具有授予权限：可修改权限拒绝权限：无权限，就算别的角色授予也不行未设置：可从用户所属的其它角色继承权限,【例5.11】为School数据库的用户Wanghong设置权限：对Student表具有“Select”、“Insert”权限，禁止“Delete”权限，仅对“Telephone”列具有“Update”权限。,5.4.2数据库级管理,2)数据库用户权限管理,使用管理员(sa)帐号登录数据库右击“数据库”-“School”-“安全性”-“用户”-Wanghong，选择“属性”，出现“数据库用户”窗口，选择“安全对象”选项页单击“搜索”按钮，弹出“添加对象”对话框，选择“特定类型的所有对象”，在“选择对象类型”对话框中选择对象类型为“表”，对象名称为“dbo.Student”在Student权限中设置权限选中“更新”后点列权限，设置对列的更新控制,2.数据库系统角色管理和权限管理角色可以简化安全管理。将一组用户归类为一个角色，通过对角色的数据访问权限和操作权限管理，自动实现对属于该角色的所有用户成员的权限管理。一个用户可以是任意多个角色的成员，同时持有每个角色的权限，也可直接给用户授权，这些权限的累加就是该用户的访问权限集合。,5.4.2数据库级管理,用户自定义数据库角色：根据需要为用户定义新的数据库角色。,SQLServer中有两类预定义角色：即固定服务器角色和固定数据库角色。这两个角色具有系统级别或数据库级的操作权限，如果有用户需要这些权限，则必须将其帐户添加到这些预定义角色。另外，数据库管理员也可根据需要为用户定义新的数据库角色。,常用固定数据库角色：,5.4.2数据库级管理,2.数据库系统角色管理和权限管理,(1)在SSMS中，右击“数据库”-“School”-“安全性”-“角色”-“数据库角色”，在右键菜单中点击“新建数据库角色”。“角色名称”输入“Teacher”，“此角色拥有的架构”选择“dbo”。单击【确定】按钮即可完成角色创建。,5.4.2数据库级管理,2.数据库系统角色管理和权限管理,【例5-12】为School数据库创建一个名称为“Teacher”的数据库角色，设置其对所有表具有Select权限，此外，对Course表还具有Insert、Update、Delete权限，禁止在Student和ClassInfo执行Delete操作，对Grade表仅对Grade列具有Update权限，其他列具有Select权限。,(2)双击角色名“Teacher”,进入“安全对象”标签页,(3)点击“搜索”，选择“特定对象”，进入“选择对