SQL数据库技术.ppt

SQLserver数据库技术 第3讲管理数据库 学习要点 SQLServer数据库的存储结构事务日志的概念数据库的创建 属性设置 收缩 删除SQLserver的标识符规则 从逻辑结构上讲 SQLServer2000数据库用于存放数据库对象 如表 视图 存储过程 触发器 索引和约束等 在企业管理器中 可以直观地看到他们 从物理结构的角度讲这些数据库对象是要以一定的数据文件形式存储在硬盘中的 这就是所谓数据库的存储结构 如下介绍 1基本概念及存储结构 主数据文件用来存储数据库的启动信息 部分或全部数据 扩展名为 mdf每个数据库只能有一个主数据文件次数据文件用于保存所有主要数据文件中容纳不下的数据 扩展名为 ndf 用于存储数据库中的所有对象 保存用于恢复数据库的日志信息 1 文件的概念 2 文件组的概念出于分配和管理上的需要 可以将数据库文件分成不同的文件组 实际应用中可以通过控制在特定磁盘上放置的数据和索引来提高数据库系统的性能 文件组 将多个数据文件集合起来形成的一个整体目的 便于进行管理和数据的分配类型 Primary组 包含了主数据文件和部分次数据文件用户命名文件组 包含多个次数据文件默认文件组 包含所有在创建时没有指定文件组的表 索引等数据库对象 注意 一个数据文件不能存在于两个或两个以上的文件组里 日志文件不属于任何文件组 3 数据库的物理存储结构 数据页页是数据库存储的基本单元 是一块大小为8KB的连续磁盘空间 即1MB空间可以存储128个页 每页的开始部分是96字节的页首 用于存储系统信息 如页的类型 页的可用空间 该页的所有者ID等 页根据功能划分为数据页 索引页 文本和图像页等8个类型 事务日志文件不包含页 而包含一系列日志记录 范围 扩展 extent 范围是SQLServer数据库分配给表或索引的存储空间的最基本单位 每个范围包括八个连续页 即每兆字节最多可容纳16个范围 为了提高空间存储效率 为分两种统一范围 统一扩展 和混合范围 混合扩展 范围 扩展盘区 统一扩展盘区 该扩展盘区的所有八页只能由拥有该盘区的单个对象 表或索引 独自使用 混合扩展盘区 混合类型的范围可供8个数据库对象共享 事务日志文件的存储结构事务日志文件 用以记录所有事务和每个事务对数据库所做的修改 当数据库破坏时可以用事务日志恢复数据库内容 从物理上讲 日志文件是由一系列连续的日志记录组成的 日志记录序列必须存储在一个或多个物理文件内 查看 系统数据库和示例数据库的数据文件及日志文件安装SQLServer2000时 系统自动创建4个系统数据库和2个示例数据库 补充说明 表中数据是以行为单元进行存储 任何一行数据都会被存储在数据页中 但注意 行不能跨页 即行宽 8KByte 一页可存放多行 例 1000行的表 每行宽度5 1024Byte 那么它所占的空间为多少 1000 5 1024Byte 1000 8 1024Byte如果将行宽压缩到4 1024Byte 那么他所占的空间为多少字节 多少页 还有什么其他好处 1000 8 1024Byte 500页 I O减少 性能提高 如果除了主数据文件 mdf 外 还有多个次数据文件 那么 数据 如表中记录 不是只存储在一个数据文件中 而是分散存储在不同的数据文件上 表数据跨文件存储 见下页示意图 行不跨页 一页存多行 一个表中数据跨文件存储示意图 表a的第1 2行记录 表a的第3 4行记录 表a的第n行记录 2创建数据库 图数据库及其对象的结构关系 创建数据库应具备的条件创建数据库的登录账户必须具有systemadministrators或dbcreator的服务器角色 在SQLServer中 可以使用1 企业管理器2 数据库向导3 执行CREATEDATABASE语句3种方法来创建数据库 创建数据库是创建表及其它数据库对象的第一步 数据库的创建包括为确定数据库名称 决定数据库大小 及在数据库中用来存储数据的文件 1 使用企业管理器创建数据库 1 进入SQLServer企业管理器 在左边的目录树结构中选择 数据库 文件夹 2 单击 操作 菜单中的 新建数据库 命令或右键单击 数据库 文件夹 在弹出的快捷菜单上选择 新建数据库 命令 系统打开 数据库属性 对话框 3 在 常规 选项卡中输入数据库的名称 假设为Sales 输入数据库名称 4 选择 数据文件 选项卡 在该窗口中设置数据文件的属性 可修改数据文件的逻辑名和物理名 完成后不能再修改 5 选择 事务日志 选项卡 在该窗口中可设置日志文件的属性 可修改日志文件的逻辑名和物理名 创建完成后不能再修改 6 单击 确定 按钮 数据库创建完成 使用向导创建数据库首先选中相应的服务器实例 点击工具栏上的 运行向导 选中创建数据库即可 3 使用T SQL语句创建数据库创建数据库的T SQL语句的语法形式 CREATEDATABASEdatabase name ON n n LOGON n PRIMARY NAME logical file name FILENAME os file name SIZE size MAXSIZE max size UNLIMITED FILEGROWTH growth increment n FILEGROUPfilegroup name n database name 新创建的数据库的名称 ON 指出用来存储数据库中数据部分的磁盘文件 数据文件 PRIMARY 指定主文件组中的主文件 LOGON 指定用来存储数据库日志的磁盘文件 NAME logical file name 指定数据文件或日志文件的逻辑名 FILENAME os file name 指定数据文件或日志文件的操作系统文件名 包括文件名和路径 SIZE size 指定数据文件或日志文件的初始大小 默认单位为MB MAXSIZE max size UNLIMITED 指定数据文件或日志文件可以增长到的最大容量 默认单位为MB FILEGROWTH growth increment 指定数据文件或日志文件的增长幅度 默认单位为MB 例2 1创建一个数据库sample 主文件为sample dat CREATEDATABASEsampleONPRIMARY NAME sample dat FILENAME d programfiles microsoftsqlserver mssql data sample data mdf SIZE 5 MAXSIZE 50 FILEGROWTH 10 例2 2创建一个只包含一个数据文件和一个日志文件的数据库 该数据库名为Sales 数据文件的逻辑名为Sales data 数据文件的操作系统名为sales data mdf 初始大小为10MB 最大可增加至500MB 增幅为10 日志文件的逻辑名为Sales log 操作系统名为sales log ldf 初始大小为5MB 最大值为100MB 日志文件大小以2MB增幅增加 CREATEDATABASESalesON NAME Sales data FILENAME d programfiles microsoftsqlserver mssql data sales data mdf SIZE 10MB MAXSIZE 500MB FILEGROWTH 10 LOGON NAME Sales log FILENAME d programfiles microsoftsqlserver mssql data sales log ldf SIZE 5MB MAXSIZE 100MB FILEGROWTH 2MB 例2 3创建一个包含多个数据文件和日志文件的数据库 该数据库名为student 含有三个初始大小为10MB的数据文件和两个8MB的日志文件 CREATEDATABASEstudentONPRIMARY NAME std dat1 FILENAME d programfiles microsoftsqlserver mssql data student1 mdf SIZE 10MB MAXSIZE 200MB FILEGROWTH 20 NAME std dat2 FILENAME d programfiles microsoftsqlserver mssql data student2 ndf SIZE 10MB MAXSIZE 200MB FILEGROWTH 20 NAME std dat3 FILENAME d programfiles microsoftsqlserver mssql data student3 ndf SIZE 10MB MAXSIZE 200MB FILEGROWTH 20 LOGON NAME std log1 FILENAME d programfiles microsoftsqlserver mssql data stdlog1 ldf SIZE 8MB MAXSIZE 100MB FILEGROWTH 10MB NAME std log2 FILENAME d programfiles microsoftsqlserver mssql data stdlog2 ldf SIZE 8MB MAXSIZE 100MB FILEGROWTH 10MB 例2 4创建一个包含两个文件组的数据库 该数据库名为business 主文件组包含business dat1和business dat2两个数据文件 文件组business group包含文件数据文件business dat3 该数据库还包含一个日志文件business log CREATEDATABASEbusinessONPRIMARY NAME business dat1 FILENAME d programfiles microsoftsqlserver mssql data businessdat1 mdf SIZE 10MB MAXSIZE 50MB FILEGROWTH 10 NAME business dat2 FILENAME d programfiles microsoftsqlserver mssql data businessdat2 ndf SIZE 10MB MAXSIZE 500MB FILEGROWTH 10 FILEGROUPbusiness group NAME business dat3 FILENAME d programfiles microsoftsqlserver mssql data businessdat3 ndf SIZE 10MB MAXSIZE 50MB FILEGROWTH 10 LOGON NAME business log FILENAME d programfiles microsoftsqlserver mssql data businesslog ldf SIZE 8MB MAXSIZE 100MB FILEGROWTH 10MB 3数据库的管理 3 1查看数据库3 2修改数据库3 3收缩数据库3 4删除数据库 3 1查看数据库 1查看数据库两种方法 企业管理器和存储过程 1 用企业管理器查看数据库的属性进入SQLServer企业管理器 在左边的目录树结构中选中要查看的数据库 单击右键 在弹出的快捷菜单上单击 属性 命令 即可进入数据库的属性窗口 该窗口共有6个选项卡 包括 常规 数据文件 事务日志 文件组 选项 及 权限 我们可以根据需要进入选择不同的选项卡查看数据库相应的信息 演示创建用户命名文件组 添加次数据文件 修改日志文件或次数据文件逻辑名物理名 2 用T SQL语句查看数据库的属性使用系统存储过程sp helpdb可以查看某个数据库或所有数据库的属性 格式 sp helpdbdatabase name该存储过程显示了数据库的名称 大小 所有者 创建日期以及数据文件和日志文件等属性 例2 5查看数据库Sales的属性 sp helpdbSales例2 6查看所有数据库的属性sp helpdb 3 2修改数据库 1 修改数据库名称一般情况下 不建议用户修改创建好的数据库名称 因为许多应用程序可能已经使用了该数据库的名称 引起不必要的麻烦 1 ALTERDATABASE语句Alterdatabasedatabasenamemodifyname newname 举例 2 Sp renamedb存储过程例 Execsp renamedb schoolst std 将数据库schoolst改名为std SQLserver2000不提供重命名属性 补充 重命名数据库语法形式为 sp renamedb old name new name old name 数据库的当前名称 new name是数据库的新名称 练习题 将数据库business更名为company sp renamedb business company 2修改数据库大小实质上就是修改数据文件和日志文件的长度 或者增加和删除文件 如果数据库中的数据量不断膨胀 需要扩大数据库的尺寸 3种方式 1 设置数据库为自动增长方式 这个在创建数据库时设计 2 直接修改数据库中的数据文件和日志文件的大小 3 在数据库中增加新的次要数据文件或日志文件 演示 使用t sql语句后面系统讲解 ALTERDATABASEdatabase ADDFILE n TOFILEGROUPfilegroup name ADDLOGFILE n REMOVEFILElogical file name ADDFILEGROUPfilegroup name REMOVEFILEGROUPfilegroup name MODIFYFILE MODIFYNAME new dbname MODIFYFILEGROUPfilegroup name filegroup property NAME new filegroup name 用T SQL语句修改数据库属性其语法形式为 ADDFILE n TOFILEGROUPfilegroup name 表示向指定的文件组添加新的数据文件 ADDLOGFILE n 添加新的事务日志文件 REMOVEFILElogical file name 删除某一文件 ADDFILEGROUPfilegroup name 添加一个文件组 REMOVEFILEGROUPfilegroup name 删除某一文件组 MODIFYFILE 修改某个文件的属性 MODIFYNAME new dbname 修改数据库的名字 MODIFYFILEGROUPfilegroup name filegroup property NAME new filegroup name 修改某一文件组的属性 例2 7为数据库sample添加一个数据文件sample dat2和一个日志文件sample log2 ALTERDATABASEsampleADDFILE NAME sample dat2 FILENAME d programfiles microsoftsqlserver mssql data sample dat2 ndf SIZE 4 MAXSIZE 10 FILEGROWTH 1 ALTERDATABASEsampleADDLOGFILE NAME sample log2 FILENAME d programfiles microsoftsqlserver mssql data sample log2 ldf SIZE 4 MAXSIZE 10 FILEGROWTH 1 例2 8为数据库sample添加一个文件组USER1 并向该文件组添加一个二个数据文件sampuser dat1和sampuser dat2 ALTERDATABASEsampleADDFILEGROUPUSER1ALTERDATABASEsampleADDFILE NAME sampuser dat1 FILENAME d programfiles microsoftsqlserver mssql data sampuser dat1 ndf SIZE 4 MAXSIZE 10 FILEGROWTH 1 NAME sampuser dat2 FILENAME d programfiles microsoftsqlserver mssql data sampuser dat2 ndf SIZE 4 MAXSIZE 10 FILEGROWTH 1 TOFILEGROUPUSER1 例2 9从数据库sample中删除文件sampuser dat2 ALTERDATABASEsampleREMOVEFILEsampuser dat2例2 10删除数据库sample中的文件组USER1 ALTERDATABASEsampleREMOVEFILEsampuser dat1ALTERDATABASEsampleREMOVEFILEGROUPUSER1 注意 在删除文件组时必须先删除文件组中所包含的文件 例2 11修改数据库sample中数据文件sample dat2的属性 将其初始大小改为10MB 最大容量改为80MB 增长幅度改为5MB ALTERDATABASEsampleMODIFFILE NAME sample dat2 SIZE 10 MAXSIZE 80 FILEGROWTH 5 例2 12修改数据库business中文件组business group的属性 将其改名为group1 并设置为DEFAULT属性 即该文件组为默认文件组 ALTERDATABASEbusinessMODIFYFILEGROUPbusiness groupNAME group1ALTERDATABASEbusinessMODIFYFILEGROUPgroup1DEFAULT例2 13将数据库student改名为stud teacher ALTERDATABASEstudentMODIFYNAME stud teacher 3 3收缩数据库 SQLServer2000允许收缩数据库中的每个文件以删除未使用的页 数据文件和事务日志文件都可以收缩 1 自动收缩 1 使用企业管理器设置自动收缩数据库在数据库的 属性对话框 中 点击 选项 选项卡 选中 自动收缩 复选框 即可完成数据库自动收缩的设置 设置自动收缩 2 T SQL语句自动收缩数据库使用ALTERDATABASE语句可以将数据库设为自动收缩 当数据库中有足够的可用空间时 就会发生收缩 其语法形式为 ALTERDATABASEdatabase nameSETAUTO SHRINKon off还可使用存储过程 SP dboption databasename autoshrink true 注意 1 自动收缩功能将在数据库的自由空间超过25 时开始 2 不能将整个数据库收缩到比其初始化的空间还要小 即初始空间占满之后才有可能自动收缩 3 自动收缩功能将在后台执行 对数据库运行有一定影响 2 手工收缩 收缩数据库 收缩数据和日志文件 1 使用企业管理器收缩数据库及数据 日志文件 2 使用Transact sql命令实现 了解 收缩数据库命令DBCCSHRINKDATABASE database name target percent NOTRUNCATE TRUNCATEONLY database name 是要收缩的数据库名称 target percent 是数据库收缩后的数据库文件中所要的剩余可用空间百分比 NOTRUNCATE 被释放的文件空间依然保持在数据库文件的范围内 TRUNCATEONLY 尽量收缩数据库到最小 指定的自由空间百分比将失效 同时将数据文件中的任何未使用的空间释放给操作系统 如果未指定 NOTRUNCATE和TRUNCATEONLY 则按百分比被释放的文件空间将被操作系统回收利用 例2 16缩小sales数据库的大小 使得该数据库中的文件有20 的可用空间 DBCCSHRINKDATABASE sales 20 收缩数据文件或日志文件命令DBCCSHRINKFILE file name target size EMPTYFILE NOTRUNCATE TRUNCATEONLY file name 是要收缩的数据或日志文件的逻辑名 target size 将文件缩小到指定的长度 以MB为单位 如果缺省该项 文件将尽最大可能进行缩小 EMPTYFILE 将指定文件上的数据全部迁移到本文件组的其它文件上 以后的操作将不会再再该文件上增加数据 此时即可删除该数据库文件 注意 1 手工收缩数据库不能小于初始数据文件大小 即使有多余的空间 但可采用继续收缩数据或日志文件的方式继续缩小数据库总大小 注意 数据文件收缩不能小于其实际数据所占空间 日志文件借助backuplog可收缩到比初始值要小 2 执行完清空某个数据文件的数据时 其所占空间显示大小并不是0MB 但此时数据已被转移 可以删除此数据文件 否则不允许删除 3 4删除数据库 1 用企业管理器删除数据库 1 打开SQLServer企业管理器窗口 在左边的目录树结构中选择要删除的数据库文件夹 如company 2 选择 操作 菜单中的 删除 命令或右键单击要删除的数据库 在弹出的快捷菜单中选择 删除 命令 系统会弹出 删除数据库 的确认对话框 3 单击 是 按钮就会删除该数据库 注 删除之前要停止服务 此操作会将数据库彻底删除 2 T SQL语句删除数据库语法形式 DROPDATABASEdatabase name 例2 18删除数据库studentDROPDATABASEstudent例2 19删除sample和student数据库 DROPDATABASEsample student 注意 4个系统数据库master tempdb model msdb不能删除 正在使用的数据库不能删除 数据库被删除之后 文件及其数据都从服务器上的磁盘中删除 一旦删除数据库 它即被永久删除 不能恢复 所以删除数据库时一定要谨慎 4SQLServer命名规则 为了提供完善的数据库管理机制 SQLServer设计了严格的命名规则 在创建或引用数据库实体 如表 索引 约束等时 必须遵守SQLServer的命名规则 否则有可能发生一些难以预料和检查的错误 本章将介绍SQLServer有关对象命名的内容 SQLServer有关标识符的分类和格式规定 SQLServerd的标识符 SQLServer的所有对象 包括服务器 数据库以及数据库对象 如表 视图 列 索引 触发器 存储过程 规则 默认值和约束等都可以有一个标识符 例如下面的SQL语句 CREATETABLEEXA