数据库的建立与维护ppt课件.pptx

第2章数据库的建立与维护 1 在SQLServer2008中 用户要访问并使用数据库 需要正确了解数据库中所有对象及其设置 数据库就像一个容器 它里面除了存放着数据的表之外 还有视图 存储过程 触发器 约束等数据库对象 本章主要学习SQLServer2008数据库的创建 配置和管理数据库 包括设置数据库的大小 规划数据库文件的存储位置 设置和修改数据库的属性及状态 以及如何对数据库中的物理空间进行科学的设置 2 本章的学习目标 了解系统数据库的作用了解数据库对象及构成掌握用SSMS和T SQL创建数据库的方法掌握用SSMS和T SQL修改 删除数据库的操作掌握如何查看数据库状态熟悉如何对数据库中的物理空间进行科学的设置掌握数据库的分离和附加 3 数据库的存储结构分为逻辑存储结构和物理存储结构逻辑存储结构是指数据库的性质信息 数据库由各种不同的数据对象 如表 视图 索引等 组成 它们分别存储数据库的特定信息 构成了数据库的逻辑存储结构 物理存储结构是指磁盘上存储的数据库文件 数据库文件有数据文件和事务日志组成 保存在物理介质上 2 1数据库的组成 4 2 1 1系统数据库 系统数据库有 master model msdb tempdb和resource数据库 用户不要随意修改系统数据库 1 master数据库Master数据库用于记录SQLServer实例的所有系统级信息 是SQLServer的核心 不能对其进行直接修改 应当对其定期进行备份 如果Master数据库变得不可用 那么SQLServer数据库引擎将无法启动 master数据库始终有一个可用的最新的master数据库备份 5 2 model数据库model数据库用作SQLServer实例上创建的所有数据库的模板 创建数据库时 新数据库通过复制model数据库中的内容创建 剩余部分由空白页填充 如果修改model数据库 之后创建的所有数据库都将继承这些修改 即model数据库中的所有用户定义的对象都将复制到所有新创建的数据库中 可以向model数据库中添加任何对象 如 表 视图 存储过程等 以便将这些对象包含到所有新创建的数据库中 6 3 msdb数据库msdb数据库供SQLServer代理程序调度警报和作业以及记录操作员时使用 例如 SQLServer在msdb中的表中自动保留一份完整的联机备份与还原历史记录 这些信息包括执行备份一方的名称 备份时间和用来存储备份的设备或文件 SQLServerManagementStudio利用这些信息来提出计划 以还原数据库和应用任何事务日志备份 该库还将会记录有关所有数据库的备份事件 例如 如果使用调用SQLServer管理对象的VB应用程序执行备份操作 则事件将记录在msdb系统表 MicrosoftWindows应用程序日志和SQLServer错误日志中 默认情况下 msdb使用简单恢复模式 如果使用备份和还原历史记录表 建议对msdb使用完整恢复模式 7 4 tempdb数据库tempdb数据库是一个临时数据库 用于保存临时对象或中间结果集 满足临时存储要求 tempdb数据库在SQLServer每次启动时都重新创建 临时表和临时存储过程在连接断开时自动除去 而且当系统关闭后将没有任何连接处于活动状态 每次启动数据库引擎时 tempdb数据库会重置为其初始大小 在SQLServer运行时 该库会根据需要自动增长 8 5 resource数据库resource数据库是一个只读和隐藏的数据库 它包含了SQLServer2008中的所有系统对象 这些系统对象 例如sys objects 在物理上保留在Resource数据库中 但在逻辑上显示在每个数据库的sys架构中 Resource数据库的物理文件名为mssqlsystemresource mdf和mssqlsystemresource ldf 这些文件位于 ProgramFiles MicrosoftSQLServer MSSQL10 MSSQL Binn 中 每个SQLServer实例都具有一个 也是唯一的一个 关联的mssqlsystemresource mdf文件 并且实例间不共享此文件 9 2 1 2用户数据库 用户数据库是用户根据自己的管理需求创建的数据库 便于自己管理相应的数据 例如 图书馆可以针对图书的管理创建图书管理数据库 大型超市可以针对货品创建超市管理数据库等 10 2 1 3示例数据库 示例数据库是系统为了让用户学习和理解SQLServer而设计的 Northwind和pubs是SQLServer2000中的示例数据库 pubs是以一个图书出版公司为模型的示例库 Northwind是一个有关销售的示例库 AdventureWorks是SQLServer2005中的示例数据库 该库基于的虚拟公司是一家大型跨国生产公司 公司生产金属和复合材料的自行车 产品远销美欧亚市场 公司总部设在华盛顿州的伯瑟尔市 拥有活跃在世界各地的销售团队 约有290名雇员 SQLServer2008不再安装示例数据库 但可以根据自己的需要附加以上数据库 11 2 1 4数据库文件及其文件组 文件数据库有3种类型的文件 主数据文件 默认的扩展名为 mdf次要数据文件 默认的扩展名为 ndf日志文件 默认的扩展名为 ldf数据库中存放数据文件和日志文件的默认位置为 C ProgramFiles MicrosoftSQLServer MSSQL10 MSSQLSERVER MSSQL DATA文件夹下 12 主数据文件主数据文件用来存放数据 它还包含其它数据库文件的指针 它是数据库的起点 主数据文件包含一些系统表 这些表记录了数据库对象及其他文件的位置信息 一个数据库必须有且只有一个主数据文件 主数据文件的扩展名为 MDF 13 次要数据文件次要数据文件也用来存放数据 如果主数据文件可包括数据库中的所有数据就不需要次数据文件 如果主数据文件太大或要扩展到多个磁盘 则需次要数据文件 一个数据库可有多个或没有次要数据文件 次要数据文件的扩展名为 NDF 14 事务日志文件事务是用户定义的一个数据库操作序列 这些操作要么全做要么全不做 事务日志文件用来存放事务日志 即存储所有事务和由这些事务引起的数据库的变化 用于恢复数据库 一个数据库有一个或多个事务日志文件 日志文件的扩展名为 LDF建立好数据库之后 在进行具体的操作之前应当首先配置事务日志 事务日志文件记录SQLServer对这个数据库的操作 以便于在系统出现故障时恢复数据库 15 事务日志支持的操作恢复个别的事务SQLServer启动时恢复所有未完成的事务将还原的数据库 文件 文件组或页回滚到故障点支持事务复制支持备用服务器解决方案 16 事务日志的特征 事务日志是作为数据库中的单独的文件或一组文件实现的 日志缓存与数据页缓存分开管理 日志记录和页的格式不必遵守数据页的格式 事务日志可以在几个文件上实现 17 事务日志提供容错的机制在SQLServer中 事务是指一次完成的操作的集合 虽然一个事务中可能包含了很多的SQL语句 但是在处理上 它们就像是一个操作一样 为了维护数据库的完整性 它们必须彻底完成或者根本不执行 如果一个事务只是部分执行 则数据库将受到损坏 18 事务日志防止没有完成的事务破坏数据的步骤 1 用户执行修改数据库对象的任务 2 当这个事务开始时 在事务日志中会记录一个事务的开始标记 并将与此操作相关的数据写入缓冲区 3 在日志中记录每一个操作 然后在日志中记录一个提交事务的标志 并将日志记录立即写到硬盘上 4 在缓冲区中修改相应的数据 直到检查点进程发生 才将数据写到硬盘上 同时在日志中写入一个检查点记录 5 如果发生系统故障 在服务器恢复正常后 SQLServer会自动利用事务日志进行恢复 保证数据库的完整性 19 事务日志和数据分开存储的优点 事务日志可以单独备份 在服务器失效的事件中有可能将服务器恢复到最近的状态 事务日志不会抢占数据库的空间 可以很容易地检测事务日志的空间 在向数据库和事务日志中写入时会较少产生冲突 20 文件组文件组就是文件的逻辑集合 文件组分为主要文件组 用户定义文件组和默认文件组 文件组用于文件的分配和管理 主文件组primary主文件组包含主数据文件和任何没有明确分配给其他文件组的文件 系统表的所有页均分配在主文件组中 用户定义文件组用户自己定义的文件组 可通过在CREATEDATABASE或ALTERDATABASE语句中使用FILEGROUP关键字指定的任何文件组 也可在 数据库属性 窗口中创建 21 创建数据库应遵循的原则一个数据库均有一个文件组被指定为默认文件组 未指定则主文件组为默认文件组 如果创建表或索引时未指定文件组 则将假定所有页都从默认文件组分配 一次只能有一个文件组为默认文件组 一个文件或文件组只能被一个数据库使用 一个文件只能属于一个文件组 日志文件不能属于文件组 22 2 1 5查看数据库查看数据库对象1 表表是包含数据库中所有数据的数据库对象 由行和列组成 用于组织和存储数据 表中每一行称为一条记录 表中每列称为一个字段 字段具有自己的属性 字段的常用类型 字符型 文本型 数值型 逻辑型和日期型 查看表结构与表内容 23 2 数据库关系图数据库关系图是指数据库中表之间的联系图 24 3 索引索引是一个单独的数据结构 它是依赖于表建立的 是数据的逻辑排序 可加快查找速度 查看索引 4 视图视图是从一个或多个表中导出的表 也称虚拟表 数据库中并不存放视图的数据 只存放其定义 视图中看到的记录数据由某些表 一般是多个 的某些字段组成 查看视图的定义 查看视图数据 25 5 存储过程存储过程是一组为了完成特定功能的SQL语句集合 经编译后存储在服务器端的数据库中 由用户通过指定存储过程的名称来执行 查看存储过程 6 触发器触发器是一种特殊类型的存储过程 它能够在某个规定的事件发生时触发执行 通常用来保持数据完整性和检查数据的有效性 查看触发器 26 7 用户和角色用户是对数据库有存取权限的使用者 角色是指一组数据库用户的集合 数据库中的角色可以根据需要添加 用户如果被加入到某一角色 则将具有该角色拥有的权限 用户dbo databaseowner 是具有在数据库中执行所有活动的暗示性权限的用户 无法删除dbo用户 且此用户始终出现在每个数据库中 查看用户和角色 27 8 架构架构是单个用户所拥有的数据库对象的集合 这些对象形成单个命名空间 命名空间是一组名称不重复的对象 例如 为了避免名称冲突 同一架构中不能有两个同名的表 两个表只有在位于不同的架构中时才可以同名 数据库对象由架构所拥有 而架构由数据库用户或角色所拥有 当架构的所有者离开单位时 可在删除离开的用户之前将该架构的所有权移交给新的用户或角色 查看数据库架构 28 查看数据库属性如打开 数据库属性 对话框查看AdventureWorks数据库内容 常规 选项卡 可查看数据库的所有者 创建时间 大小 可用空间和用户数等 文件 选项卡 可查看数据库文件的逻辑名称 文件类型 所属文件组 初始大小 自动增长 路径和物理名称等 文件组 选项卡 可查看各文件组及文件组中的文件数 29 数据库存储结构页和区页和区是SQLServer在创建数据库对象时为其分配的空间单位 30 页SQLServer中数据存储的基本单位是页 磁盘I O操作在页级执行 SQLServer中的所有信息都存储在页上 页是数据库中使用的最小数据单元 每一个页存储8KB的信息 31 SQLServer使用的页类型 分配页面数据库中给表和索引分配的页面 数据和日志页面用于存储数据库数据和事务日志数据 SQLServer不允许数据跨页面存储 索引页面用于存储数据库中的索引数据 分发页面用于存储数据库中有关索引的信息 文本 图像页面用于存储大量的文本或者二进制的对象 例如图像 32 区区是由8个连续的页面组成的数据结构 大小为8 8KB 64KB 当创建数据库对象时 SQLServer会自动以区为单位给它分配空间 每个区只能包含一个数据库对象 在SQLServer中 数据库是由文件组成 文件是由区组成 区由页面组成 33 存储数据的方法 日志文件 ldf 数据文件 mdfor ndf 区 8个连续的8KB页 页 8KB 34 2 2创建数据库使用数据库存储数据 首相需要创建数据库 定义数据库就是创建数据库和设置数据库选项 创建数据库的方法有两种 一 使用图形界面创建数据库二 使用Transact SQL语句创建数据库 35 1 使用图形界面创建数据库在SQLServerManagementStudio窗口中 打开 对象资源管理器 右击 数据库 在弹出的快捷菜单上选择 新建数据库 命令 如下图所示 36 在 常规 选项页中可设置 数据库名称所有者数据库的逻辑名称 逻辑名称默认与数据库名称相同文件类型 初建时默认有两个文件 一个是主数据文件 另一个是日志文件所属文件组初始大小自动增长 如果按百分比增长 增长的大小是发生增长时文件大小的百分比 存放路径 注意不要放在根目录下物理名称单击 添加 按钮可添加次要数据文件和日志文件单击 删除 按钮可删除次要数据文件和日志文件 37 在 选项 选项页中可设置 排序规则恢复模式 可以选择数据库发生损坏时的恢复模式兼容级别在 其他选项 栏中 可以设置其他数据库选项在 文件组 选项页中可进行 添加文件组删除文件组 38 2 使用图形界面修改数据库右击目标数据库 快捷菜单中选择 属性 在打开的 数据库属性 窗口中进行修改添加和删除数据文件 日志文件添加和删除文件组3 使用图形界面删除数据库 39 4 使用Transact SQL语句创建数据库 命令语法 CREATEDATABASEdatabase name ON filespec LOGON filespec 其中filespec定义为 NAME logical file name FILENAME os file name SIZE size MAXSIZE max size UNLIMITED FILEGROWTH growth increment 40 5 使用T SQL语句修改数据库 使用ALTERDATABASE语句修改数据库语法格式 ALTERDATABASEdatabase ADDFILE n TOFILEGROUPfilegroup name ADDLOGFILE n REMOVEFILElogical file name ADDFILEGROUPfilegroup name REMOVEFILEGROUPfilegroup name MODIFYFILE 41 6 打开数据库命令格式 USEdatabase name7 使用Transact SQL语句删除数据库DROPDATABASEdatabase name 42 2 2 5数据库操作 分离数据库分离数据库将从SQLServer删除数据库 但是保证在组成该数据库的数据和事务日志中的数据完好无损 通过分离数据库可将其附加到同一台或另一台服务器上 当附加到其他服务器时 其状态与它分离时的状态完全一样 在SSMS的对象资源管理器中分离数据库 右击数据库 任务 分离 附加数据库通过附加数据库可将分离后的数据库添加到服务器中使用 在SSMS的对象资源管理器中分离数据库 右击数据库 附加 43 2