SQL项目3-2数据库的创建与管理.ppt

项目3 2数据库的创建和管理 3 2 1数据库简介3 2 2估算数据库的空间需求3 2 3数据库的创建3 2 4数据库的管理3 2 5设置数据库选项 2 目标 1 掌握创建数据库的两种方法 理解数据库的逻辑文件名和物理文件名的含义 2 掌握数据库管理的常用操作 查看数据库属性 修改数据库大小 收缩数据库 数据库的更名和删除 3 掌握设置数据库选项的常用操作 项目3 2数据库的创建和管理 3 3 2 1数据库简介 数据库创建的过程就是数据库逻辑设计的物理实现过程 如果能根据ER模型并结合单位的实际情况创建出一个性能优异 结构合理并具有良好扩充性能的数据库 将会为后在数据库基础上开发应用程序奠定坚实的基础 这是数据库管理最重要 最核心的问题 数据库的创建主要包括数据库及其表 索引 视图 存储过程等对象的创建 创建数据库时 系统会建立数据库的存储结构 数据库的存储结构分为逻辑存储结构和物理存储结构两种 数据库的逻辑存储结构指的是数据库是由哪些性质的信息所组成 实际上 SQLServer的数据库是由诸如表 视图 索引等各种不同的数据库对象所组成 4 数据库的物理存储结构是讨论数据库文件是如何在磁盘上存储的 数据库在磁盘上是以文件为单位存储的 由数据库文件和事务日志文件组成 一个数据库至少应该包含一个数据文件和一个事务日志文件 1 主数据文件 PrimaryDatabaseFile 一个数据库可以有一个或多个数据文件 一个数据文件只能属于一个数据库 当有多个数据文件时 有一个文件被定义为主数据文件 简称为主文件 其扩展名为mdf 主数据文件用来存储数据库的启动信息以及部分或者全部数据 是所有数据库文件的起点 包含指向其它数据库文件的指针 一个数据库只能有一个主数据文件 2 次数据文件 SecondaryDatabaseFile 用于存储主数据文件中未存储的剩余数据和数据库对象 一个数据库可以没有次数据文件 但也可以同时拥有多个次数据文件 次数据文件的扩展名为ndf 简称为次文件 5 3 2 1 1数据库的存储结构1 盘区和页面在创建数据库对象时 SQLServer会使用盘区和页面这种特殊的数据结构给数据库对象分配空间 在SQLServer中 数据存储的基本单位是页 页的大小是8KB 这样数据库上每MB是128页 每页的开始部分是96B的页首 用于存放系统信息 如页的类型 页的可用空间 占据该页的数据库对象的对象标识等 页有数据页 索引页 文本 图象页等8种 盘区 区域 是连续8个页 可防止数据产生碎片 使SQLServer易于寻找 每个盘区只能包含一个数据库对象 盘区是表和索引分配空间的基本单位 盘区是表和索引分配空间的基本单位 一个数据库是由文件组成的 文件是由盘区组成的 盘区是由页面组成的 6 2 数据库的存储组织 图5 1数据库的存储组织 7 注意 所有的数据库都有唯一的主数据文件和一个或多个事务日志文件 可能还有次数据文件 数据行存放在数据页中 但数据页只能存放除text ntext和image类型数据外的所有数据 text ntext和image类型数据存储在单独的页中 行不能跨页存储 页中每一行最多包含的数据量是8060B 数据存放在连续的页中 日志文件不包含页 而是包含一系列日志记录 所有新建的数据库都是model数据库的副本 8 3 文件组文件组是文件的集合 对文件进行分组 便于进行数据的管理和分配磁盘空间 例如有三个数据文件data1 ndf data2 ndf data3 ndf 分别位于不同的磁盘上 将这三个文件指派到文件组filegroup1中 假设在文件组filegroup1上创建一个表 对表中数据的查询将会分散到三个磁盘上 从而提高系统的查询性能 文件和文件组的设计规则 文件和文件组不能由一个以上的数据库使用 文件只能是一个文件组的成员 事务日志文件不属于任何一个文件组 数据和事务日志不能属于同一文件或文件组 数据文件和日志文件总是分开存放的 9 主文件组中包含了所有的系统表 当建立数据库时 主文件组包括主数据库文件和未指定组的其他文件 用户定义文件组中可以指定一个缺省文件组 那么在创建数据库对象时如果没有指定将其放在哪一个文件组中 就会将它放在缺省文件组中 如果没有指定缺省文件组 则主文件组为缺省文件组 3 2 1 2事务日志文件SQL创建数据库的时候 会同时创建事务日志文件 事务日志文件存储数据库的更新情况等事务日志信息 当数据库损坏时 管理员使用事务日志恢复数据库 每一个数据库至少必须拥有一个事务日志文件 而且允许拥有多个日志文件 事务日志文件的扩展名为ldf 10 1 事务日志文件和数据文件必须分开存放 优点如下 事务日志文件可以单独备份 有可能从服务器失效的事件中将服务器恢复到最近的状态 事务日志不会抢占数据库的空间 可以很容易地监测到事务日志的空间 在向数据文件和事务日志文件写入数据时会产生较少的冲突 这有利于提高SQLServer的性能 2 事务日志的工作过程在SQLServer中 事务是一次完成的操作的集合 虽然一个事务中可能包含了很多SQL语句 但在处理上就象他们是同一个操作似的 为了维护数据的完整性 事务必须彻底完成或者根本不执行 如果一个事务只是部分地被执行 并作用于数据库 那么数据库将可能被损坏或数据的一致性遭到破坏 11 SQLServer使用数据库的事务日志来防止没有完成的事务破坏数据 事务日志的工作过程 应用程序发出一个修改数据库中的对象的事务 当这个事务开始时 事务日志会记录一个事务开始标志 并将被影响的数据页从磁盘读入缓冲区 事务中每个数据更改语句都被记录在日志文件中 日志文件将记录一个提交事务的标记 每一个事务都会以这种方式记录在事务日志中并被立即写到硬盘上 在缓冲区中修改相应的数据 这些数据一直在缓冲区中 在检查点进程发生时 检查点进程把所有修改过的数据页写到数据库中 并在事务日志中写入一个检查点标志 这个标志用于在数据库恢复过程中确定事务的起点和终点 以及哪些事务已经作用于数据库了 12 随着数据库数据的不断变化 事务日志文件不断增大 因此 必须把它们备份出来 为更多的事务提供空间 备份时 事务日志文件会被截断 事务日志文件包含有在系统故障下恢复数据库需要的所有信息 一般来说 事务日志文件的初始大小是以数据文件大小的10 到25 为起点的 根据数据增长的情况和修改的频率进行调整 关于文件名 SQLServer2005的文件拥有两个名称 即逻辑文件名和物理文件名 当使用Transact SQL命令语句访问某一个文件时 必须使用该文件的逻辑名 物理文件名是文件实际存储在磁盘上的文件名 而且可包含完整的磁盘目录路径 回到首页 13 3 2 2估算数据库的空间需求 作为数据库管理员 主要任务之一就是创建数据库 并且需要为每个文件指定容量 必须尽可能准确地估算数据库容量 以免浪费磁盘空间资源或者因估计不足造成数据库的空间不够 许多因素会影响数据库最终的大小 在估算数据库容量时要考虑如下因素 每行记录的大小 记录数量 表的数量 索引的数量及索引大小 数据库对象的数量和大小 事务日志的大小 数据库的计划增加量 回到首页 14 3 2 3创建数据库 创建数据库的两种方法 1 使用SQLServerManagementStudio创建数据库2 使用Transact SQL语言创建数据库创建数据库的过程实际上就是为数据库设计名称 设计所占用的存储空间和存放文件位置以及相关特性的过程 新建数据库的信息放在系统数据库master中 属于系统级信息 建议 创建新数据库时 数据文件逻辑名加入 data 后缀 形如 jwgl data 事务日志文件逻辑名加入 log 后缀 形如 jwgl log 15 3 2 3 1创建数据库的注意事项 创建数据库需要一定许可 在默认情况下 只有系统管理员和被赋予权限的用户可以创建数据库 数据库被创建后 创建数据库的用户自动成为该数据库的所有者 给数据库指定的名字遵循SQLServer命名规范 所有的新数据库都是系统样本数据库model的副本 单个数据库可以存储在单个文件上 也可以跨越多个文件存储 5 数据库的大小可以被增大或者收缩 6 当新的数据库创建时 SQLServer自动更新master系统数据库中的 sysdatabases 系统表 7 一台服务器上最多可能创建32767个数据库 16 3 2 3 2使用T SQL语言创建数据库说明 在Transact SQL语言的命令格式中 用 括起来的内容表示是可选的 n 表示重复前面的内容 用括起来表示在实际编写语句时 用相应的内容替代 用 括起来表示是必选的 类似A B的格式 表示A和B只能选择一个 不能同时都选 1 创建数据库的SQL语句的语法格式 CREATEDATABASEdatabase name ON PRIMARY n n LOGON n 17 其中 NAME logical file name FILENAME os file name SIZE size MAXSIZE max size UNLIMITED FILEGROWTH growth increment n FILEGROUPfilegroup name n 各参数的含义 如下 database name 新数据库的名称 数据库名称在服务器中必须唯一 最长为128个字符 并且要符合标识符的命名规则 ON 指定存放数据库的数据文件信息 列表用于定义主文件组的数据文件 列表用于定义用户文件组及其中的文件 18 PRIMARY 用于指定主文件组中的文件 主文件组的第一个由指定的文件是主文件 如果不指定PRIMARY关键字 则在命令中列出的第一个文件将被默认为主文件 LOGON 指明事务日志文件的明确定义 如果没有本选项 则系统会自动产生一个文件名前缀与数据库名相同 容量为所有数据库文件大小1 4的事务日志文件 NAME 指定数据库文件的逻辑名称 FILENAME 指定数据库文件的物理名称 即数据库所在文件的操作系统文件名称和路径 该操作系统文件名和NAME的逻辑名称一一对应 19 SIZE 指定数据库的初始容量大小 如果没有指定主文件的大小 则SQLServer默认其与模板数据库中的主文件大小一致 其它数据库文件和事务日志文件则默认为1MB 指定大小的数字size可以使用KB MB GB和TB后缀 默认的后缀为MB Size最小值为1MB 默认值为1MB MAXSIZE 指定操作系统文件可以增长到的最大尺寸 如果没有指定 则文件可以不断增长直到充满磁盘 UNLIMITED 指定文件可以增长到磁盘变满为止 FILEGROWTH 指定文件每次增加容量的大小 当指定数据为0时 表示文件不增长 文件的增长有两种方式 以指定的MB数量增长和按百分比增长 默认后缀为MB 如果没有指定FILEGROWTH 则默认值为10 20 2 几点说明1 创建用户数据库后 要备份master数据库 2 所有数据库都至少包含一个主文件组 所有系统表都分配在主文件组中 数据库还可以包含用户定义的文件组 3 每个数据库都有一个所有者 可在数据库中执行某些特殊的活动 数据库所有者是创建数据库的用户 也可以使用sp changedbowner更改数据库所有者 4 创建数据库的权限默认地授予sysadmin和dbcreator固定服务器角色的成员 Sysadmin和securityadmin固定服务器角色的成员可以将创建数据库的权限授予其他登录 且必须显示授予 不能通过GRANTALL语句授予 21 例5 1 创建一个只含一个数据文件和一个事务日志文件的数据库 数据库名为JWGL1 主数据库文件逻辑名称为jwgl1 data 数据文件的操作系统名称jwgl1 mdf 数据文件大小为5MB 最大值500MB 数据文件大小以10 的增量增加 日志逻辑文件名称jwgl1 log ldf 事务日志的操作系统名称jwgl1 ldf 日志文件初始大小为5MB 最大值100MB 日志文件以2MB增量增加 程序清单为 CREATEDATABASEjwgl1ONPRIMARY NAME jwgl1 data FILENAME c ProgramFiles MicrosoftSQLServer MSSQL DATA jwgl1 mdf SIZE 5MB MAXSIZE 500MB FILEGROWTH 10 LOGON name jwgl1 log FILENAME c ProgramFiles MicrosoftSQLServer MSSQL DATA jwgl1 ldf SIZE 5MB MAXSIZE 100MB FILEGROWTH 2MB GO 22 例5 2 创建一个指定多个数据文件和日志文件的数据库 该数据库名称为jwgl2 有2个10MB的数据文件和2个10MB的事务日志文件 主文件是列表中的第一个文件 并使用PRIMARY关键字显式指定 事务日志文件在LOGON关键字后指定 注意FILE NAME选项中所用的文件扩展名 主数据文件使用 mdf 次数据文件使用 ndf 事务文件使用 ldf 程序清单 CREATEDATABASEjwgl2ONPRIMARY NAME jwgl20 data FILENAME c programfiles microsoftsqlserver mssql data jwgl20 mdf SIZE 10MB MAXSIZE 200 FILEGROWTH 20 23 NAME jwgl21 data FILENAME c programfiles microsoftsqlserver mssql data jwgl21 ndf SIZE 10MB MAXSIZE 200 FILEGROWTH 20 LOGON NAME jwgl20 log FILENAME c programfiles microsoftsqlserver mssql data jwgl20 ldf SIZE 10MB MAXSIZE 200 FILEGROWTH 20 NAME jwgl21 log FILENAME c programfiles microsoftsqlserver mssql data jwgl21 ldf SIZE 10MB MAXSIZE 200 FILEGROWTH 20 GO 24 例5 3 创建一个包含2个文件组的数据库 该数据库名为jwgl3 主文件组包含文件jwgl30 data和jwgl31 data 文件组jwgl3 group包含文件jwgl32 data和jwgl33 data 两个文件组数据文件的FILEGROWTH增量为15 数据文件的初始大小为10MB 事务日志文件的文件名为jwgl3 log FILEGROWTH增量为15 日志文件的初始大小为5MB 程序清单 CREATEDATABASEjwgl3ONPRIMARY NAME jwgl30 data FILENAME c programfiles microsoftsqlserver mssql data jwgl30 mdf SIZE 10MB 25 MAXSIZE 50MB FILEGROWTH 15 NAME jwgl31 data FILENAME c programfiles microsoftsqlserver mssql data jwgl31 ndf SIZE 10MB MAXSIZE 50MB FILEGROWTH 15 FILEGROUPjwgl3 Group NAME jwgl32 data FILENAME c programfiles microsoftsqlserver mssql data jwgl32 ndf SIZE 10MB MAXSIZE 50MB FILEGROWTH 15 26 NAME jwgl33 data FILENAME c programfiles microsoftsqlserver mssql data jwgl33 ndf SIZE 10MB MAXSIZE 50MB FILEGROWTH 15 LOGON NAME jwgl3 log FILENAME c programfiles microsoftsqlserver mssql data jwgl3 ldf SIZE 5MB MAXSIZE 25MB FILEGROWTH 15 GO 27 3 2 3 3使用SQLServerManagementStudio创建数据库使用SQLServerManagementStudio创建数据库比使用T SQL语句更容易 具体步骤如下 1 进入SQLServerManagementStudio 2 右击对象资源管理器的 数据库 项 在快捷菜单中选择 新建数据库 将打开如图5 2所示的数据库属性窗口 3 该窗口有三个选择页 常规 选项 文件组 4 分别进行相关设置 5 点击 确定 28 3 2 3 4由已有库生成创建数据库的脚本由已建数据库JWGL生成该库的SQL脚本的步骤 1 选择数据库JWGL 按右键 选择 编写数据库教本为 S CREATE C 2 选择数据库脚本文件生成的目的地 新查询编辑窗口 N 文件 F 或 剪贴板 C 3 单击 文件 F 便会产生一个 选择文件 的对话窗口 在其中输入文件名 单击 保存 按钮 如要验证脚本的正确性 可进入查询编辑器 调入该脚本程序执行即可 回到首页 29 3 2 4数据库的管理 3 2 4 1数据库属性的查看1 用T SQL语言查看数据库的属性使用系统存储过程可以查看数据库的属性 常用的存储过程有sp helpdb sp spaceused sp filehelp sp helpfilegroup 查看某个数据库或所有数据库的信息 EXECsp helpdbjwgl1查看有关数据库中所占用空间的信息 EXECsp spaceused显示有关数据库中文件组的信息 EXECsp helpfilegroup显示数据库中文件的信息 EXECsp helpfilejwgl1 data2 用SQLServerManagementStudio查看数据库的属性进入SQLServerManagementStudio 右击数据库 选择 属性 选项即可查看数据库相关信息 30 3 2 4 2修改数据库大小用T SQL语言进行数据库大小的设定 例5 4 修改数据库JWGL1日志文件的最大值 由100MB改为80MB 程序清单 USEjwgl1GOALTERDATABASEjwgl1MODIFYFILE NAME jwgl1 log MAXSIZE 80MB 31 例5 5 修改jwgl1 data数据文件的初始值 由5MB改为10MB 程序清单 USEjwgl1GOALTERDATABASEjwgl1MODIFYFILE NAME jwgl1 data SIZE 10MB 应该注意 对于数据库数据文件和事务日志文件初始空间大小的修改 新指定的空间大小值不能小于当前文件空间大小值 2 用SQLServerManagementStudio修改数据库属性进入SQLServerManagementStudio 展开树形结构的 数据库 节点 选中要修改的数据库JWGL1 按右键 在系统弹出快捷菜单上点击 属性 进入JWGL1数据库的属性窗口 可以根据需要进入不同的选项卡修改数据库相应的信息 32 3 2 4 3数据库的收缩SQL允许收缩数据库中的每个文件以删除未使用的页 数据和事务日志文件都可以收缩 数据库文件可以单独地进行手工收缩 也可以设置为按给定的时间间隔进行自动收缩 注意 不能将整个数据库收缩到比原始大小还要小 缩小数据库有两种方法 1 使用SQLServerManagementStudio可以缩小数据库 教材P91 2 使用Transact SQL语言缩小数据库 1 使用DBCCSHRINKDATABASE命令收缩指定数据库中的数据文件 DBCCSHRINKDATABASE database name target percent NOTRUNCATE TRUNCATEONLY 33 database name 要收缩的数据库名称 Target percent 当数据库收缩后 数据库文件中剩余可用空间的百分比 NOTRUNCATE 被释放的文件空间依然保持在数据库文件中 如果未指定 将所释放的文件空间被操作系统回收 TRUNCATEONLY 将数据文件中未使用的空间释放给操作系统 并将文件收缩到上一次所分配的大小 使用TRUNCATEONLY时 将忽略target percent的限制 将数据库设为自动收缩使用ALTERDATABASE语句可以将数据库设为自动收缩 设定数据库是否自动收缩的SQL语句的语法形式 ALTERDATABASEdatabase nameSETAUTO SHRINKon offon 数据库设为自动收缩 off 数据库设为不自动收缩 34 例5 7 将数据库JWGL1的收缩设为自动收缩 SQL脚本程序如下 ALTERDATABASEjwgl1SETAUTO SHRINKon3 2 4 4数据库的更名一般情况下不要做数据库的改名 如要更名 需将数据库属性 选项 中的 限制访问 项设为 single user 更名后再将 限制访问 项改回来 两种给数据库更名的方法 1 进入SQLServerManagementStudio 展开对象资源管理器 右击要更名的数据库 选择 重命名 2 数据库的更名的SQL语句的语法形式 sp renamedb old name new name 其中 old name 是数据库的当前名称 new name 是数据库的新名称 例5 8 将数据库JWGL1更名为JWXT SQL脚本程序如下 sp renamedbjwgl1 jwxt 35 3 2 4 5删除数据库1 利用SQLServerManagementStudio删除数据库 教材P92 2 利用Drop语句删除数据库Dropdatabasedatabase name n 说明 当数据库处于以下状态时不能被删除 数据库正在使用 数据库正在恢复 系统数据库master tempdb model msdb 例5 9 使用DROPDATABASE命令将数据库JWXT删除 DROPdatabasejwxt 回到首页 36 3 2 5设置数据库的选项 数据库选项就是数据库的属性 这些选项将决定数据库的不同部分如何工作 可以使用SQLSQLServerManagementStudio或者 sp dboption 存储过程来修改数据库选项 3 2 5 1使用Transact SQL语句设置数据库的选项使用存储过程sp dboption可以显示或更改数据库选项 但不能对master或tempdb数据库使用sp dboption 设置数据库的选项的SQL语句的语法形式如下 sp dboption database option name value 其中 database 在其中设置指定选项的数据库的名称 option name 要设置的选项的名称 该选项常用的4个参数及含义如下 autoshrink 当value为true时 数据库文件将成为自动周期性收缩的候选文件 dbouseonly 当value为true时 只有数据库所有者可以使用数据库 37 readonly 当value为true时 用户仅能读取数据库中的数据而无法对其修改 singleuse 当value为true时 每次只能有一个用户访问数据库 value option name的新设置 如果省略此参数 sp dboption将返回当前设置 value可以是true false 或on off 例5 10 将数据库JWGL设置为只有数据库所有者可以使用 USEjwglEXECsp dboption jwgl dbouseonly true 38 3 2 5 2利用SQLServerManagementStudio设置数据库的选项 1 进入SQLServerManagementStudio 展开对象资源管理器 右击要查看的数据库 单击 属性 弹出如图5 3所示的窗口 2 单击该数据库属性窗口 选项 选项卡 出现数据库的各个选项 用户可以进行如下常用选项的设置 排序规则 通过从列表中进行选择来指定数据库的排序规则 兼容级别 制定数据库所支持的MicrosoftSQLServer的最新版本 恢复模式 制定数据库的恢复模型 数据库为只读 使这个数据库处于只读状态 如数据库设为只读状态 用户就不能修改数据库中的任何记录 限制访问 制定哪些用户可以访问该数据库 可能的值有 MULTI USER 允许多个用户同时访问该数据库 SINGLE USER 表示该数据库在同一时间只能供一个用户使用 RESTRICTED USER 指定只有db owner dbcreator或sysadmin的成员才有权使用该数据库 自动关闭 当数据库中无用户时 自动关闭该数据库 并将所占用的资源交还给操作系统 自动收缩 指定数据库是否自动缩减 3 单击 确定 按钮 完成选项设置 回到首页 39