任务3创建教务数据库.ppt

任务3创建教务数据库 任务目标SQLServer2005成功安装后 通常包含 个主要的系统数据库 master model tempdb和msdb 除了系统数据库 还可以创建用户数据库 数据库是对象的集合 SQLServer2005数据库的数据对象包括所有的数据表 视图 存储过程 触发器等 这些数据库对象我们将在后续章节中陆续介绍 本任务目标是进行教务数据库的创建 以及数据库的查看修改 删除以及其他常见管理操作 使学生能利用SQL语句或SQLServerManagementStudio进行数据库的各种操作 返回目录 学习引领在进行数据管理时 相关的信息要存放到数据库中 数据库就象是一个容器 其中可以容纳表 视图 索引 存储过程和触发器等数据库对象 数据库的创建主要包括数据库及其表 索引 视图 存储过程等对象的创建 使用SQLServer2005进行数据管理之前 首先必须创建好数据库 并指定数据库的数据文件名和日志文件名以及数据库的存放位置等属性 本任务在了解数据库的概念基础上 考虑如何创建教务数据库 通过教务数据库创建操作掌握数据库创建 管理的语法 并完成书中的课堂实践及习题来消化理解数据库的相关知识 返回目录 任务组成描述 返回目录 4 任务3 1教务数据库的创建 子任务1做好创建数据库前的准备子任务内容认识数据库文件和文件组 创建数据库前的注意事项 子任务要求理解数据库的存储结构 文件和文件组的概念 了解创建数据库前的一些注意事项 能够学会思考在创建数据库之前先规划好自己的数据库 5 1 数据库文件和文件组数据库的存储结构分为逻辑存储结构和物理存储结构两种 数据库的逻辑存储结构指的是数据库是由哪些信息所组成 SQLServer的数据库是由诸如表 视图 索引等各种不同的数据库对象所组成 数据库的物理存储结构是讨论数据库文件是如何在磁盘上存储的 数据库在磁盘上是以文件为单位存储的 由数据库文件和事务日志文件组成 一个数据库至少应该包含一个主数据库文件和一个事务日志文件 数据文件包含数据和对象 例如表 索引 存储过程和视图 日志文件包含恢复数据库中的所有事务所需的信息 为了便于分配和管理 可以将数据文件集合起来 放到文件组中 6 SQLServer2005数据库文件有3类 1 主数据文件 Primary 存放数据 每个数据库都必须有一个主数据文件 主数据文件的建议文件扩展名是 mdf 2 次数据文件 Secondary 存放数据 一个数据库可以没有也可以有多个次数据文件 次数据文件可用于将数据分散到多个磁盘上 另外 如果数据库超过了单个Windows文件的最大大小 可以使用次数据文件 这样数据库就能继续增长 次数据文件的建议扩展名为 ndf 3 事务日志文件 TransactionLog 存放事务日志 每个数据库必须有一个或多个日志文件 事务日志的建议文件扩展名是 ldf 7 为了更好地实现数据库文件的组织 引入了文件组 FileGroup 的概念 即可以把各个数据库文件组成一个组 对它们整体进行管理 通过设置文件组 可以有效地提高数据库的读写速度 例如 可以将3个数据文件datafile1 mdf datafile2 mdf datafile3 mdf分别创建在3个盘上 将这3个文件组成一个文件组filegroup 在创建表时 可以指定将表创建在该文件组上 这样该表的数据就可以分布在3个盘上 当对该表执行查询操作时 可以并行操作 从而大大提高查询效率 8 2 创建数据库前的准备工作在创建数据库之前 应注意下列事项 1 创建数据库需要一定权限 必须至少拥有CREATEDATABASE CREATEANYDATABASE或ALTERANYDATABASE权限 在默认情况下 只有系统管理员和数据库拥有者可以创建数据库 2 数据库被创建后 创建数据库的用户将成为该数据库的所有者 3 对于一个SQLServer实例 最多可以创建32767个数据库 4 创建数据库的过程实际上就是为数据库设计名称 设计所占用的存储空间和存放文件位置的过程等 数据库名字必须遵循SQLServer命名规范 5 有三种类型的文件可用于存储数据库 主文件 辅助文件和事务日志 数据库必须具有一个主数据文件和至少一个事务日志文件 或者 也可以选择创建一个或多个辅助数据文件和其他事务日志文件 9 任务3 1教务数据库的创建 子任务2创建教务数据库子任务内容用SQLServerManagementStudio创建教务数据库 利用SQL语句创建教务数据库 子任务要求能够利用SQLServerManagementStudio和SQL语句两种方法创建教务数据库 了解创建数据库的注意点 掌握根据不同的要求创建不同的数据库 能够分析错误并修改 10 1 在SQLServerManagementStudio中创建教务数据库 1 启动SQLServerManagementStudio 在对象资源管理器中 如图3 1所示 连接到SQLServer2005数据库引擎实例 再展开该实例 右击 数据库 在弹出的快捷菜单中选择 新建数据库 命令 打开 新建数据库 窗口 2 在 新建数据库 窗口中的 常规 选择页 如图3 2所示 里 有以下几个可选项 在 数据库名称 文本框中 我们输入数据库名称 教务管理 若要通过接受所有的默认值来创建数据库 则单击 确定 按钮 否则 继续后面的可选项目的选择 若要更改所有者名称 单击后面的 按钮选择其他所有者 若要启用数据库的全文搜索 选中 使用全文索引 复选框 若要更改主数据文件和事务日志文件的默认值 在 数据库文件 列表中单击相应的单元并输入新值 在这里我们输入数据库名为 教务管理 初始数据库文件的大小设为5MB 将数据文件保存路径改为 D 教务管理 11 3 切换到 新建数据库 窗口中的 选项 选择页中 如图3 3所示 其中有以下几个可选项 若要更改数据库的排序规则 从 排序规则 下拉列表框中选择一个排序规则 若要更改恢复模式 从 恢复模式 下拉列表框中选择一个恢复模式 若要更改数据库其他选项 从下面的列表中根据需要修改选项值 4 若要添加新文件组 切换到 文件组 选择页 如图3 4所示 单击 添加 按钮 然后输入文件组的值 5 所有参数设置完毕后 单击 确定 按钮 教务管理数据库就创建成功 在 对象资源管理器 窗格中选择 数据库 并右击 在弹出的快捷菜单中选择 刷新 命令或按F5键 刷新对象资源管理器后 就可以看到教务管理数据库创建成功 如图3 5所示 12 2 利用T SQL语句创建教务管理数据库在SQLServer2005中 还可以利用T SQL提供的CREATEDATABASE语句来创建教务管理数据库 创建步骤为 1 运行MicrosoftSQLServerManagementStudio 2 单击快捷工具栏中的 新建查询 按钮 出现查询窗口 在该查询窗口中编写T SQL语句 创建一个名为教务管理的数据库的语句是 CREATEDATABASE教务管理 这是使用CREATEDATABASE语句的最基本用法 所有设置都使用默认值 在对象资源管理器中即可以看见所创建的数据库 不过要注意的是需要手动刷新对象资源管理器 否则对象资源管理器不会将新创建的数据库名显示出来 13 CREATEDATABASE语句的具体格式如下 CREATEDATABASE数据库名 ON PRIMARY N N LOGON N COLLATE排序规则名 其中 NAME 逻辑文件名 FILENAME 物理文件名 SIZE SIZE KB MB GB TB MAXSIZE MAX SIZE KB MB GB TB UNLIMITED FILEGROWTH GROWTH INCREMENT KB MB GB TB N FILEGROUP文件组名 DEFAULT N 14 下面我们按照不同的要求创建教务管理数据库 1 创建数据库 教务管理 所有设置均采用默认值 SQL语句如下 CREATEDATABASE教务管理 2 创建数据库 教务管理 指定数据库的数据文件所在位置 CREATEDATABASE教务管理ON NAME 教务管理 data FILENAME E 教务管理 mdf 15 3 创建一个只含一个数据文件和一个事务日志文件的数据库 数据库名为教务管理 主数据库文件逻辑名称为教务管理 data 数据文件的操作系统名称为教务管理 mdf 数据文件初始大小为5MB 最大值为500MB 数据文件大小以10 的增量增加 日志逻辑文件名称教务管理 log ldf 事务日志的操作系统名称为教务管理 ldf 日志文件初始大小为5MB 最大值100MB 日志文件以2MB增量增加 16 CREATEDATABASE教务管理ONPRIMARY NAME 教务管理 data FILENAME c ProgramFiles MicrosoftSQLServer MSSQL 1 MSSQL DATA 教务管理 mdf SIZE 5MB MAXSIZE 500MB FILEGROWTH 10 LOGON NAME 教务管理 log FILENAME c ProgramFiles MicrosoftSQLServer MSSQL 1 MSSQL DATA 教务管理 ldf SIZE 5MB MAXSIZE 100MB FILEGROWTH 2MB 17 课堂实践11 启动 SQLServerManagementStudio 创建数据库Student 并进行如下设置 数据库文件和日志文件的逻辑名称分别为 Student data和Student log 物理文件存放在D DATABASE文件夹中 数据文件的增长方式为 按MB 自动增长 初始大小为5MB 文件增长量为1MB 日志文件的增长方式为 按百分比 自动增长 初始大小为2MB 文件增长量为10 18 2 使用T SQL语句创建数据库Teacher 并要求进行如下设置 数据库文件和日志文件的逻辑名称分别为 Teacher data和Teacher log 物理文件存放在D DATABASE文件夹中 数据文件的增长方式为 按MB 自动增长 初始大小为5MB 文件增长量为2MB 日志文件的增长方式为 按百分比 自动增长 初始大小为2MB 文件增长量为15 3 用T SQL语句创建数据库 该数据库名称为company 数据库包含三个文件组 主文件组 company1 group和company2 group 主文件组包含company01 data和company02 data company1 group文件组包含company11 data文件 company2 group文件组包含company21 data文件 数据库的事务日志文件为company log 所以文件的初始大小为2MB 最大值为5MB 文件的增长方式为20 4 认真查看各题操作后的结果情况 本章首页 19 任务3 2教务数据库的管理 子任务1查看修改教务数据库子任务内容用SQLServerManagementStudio查看修改教务数据库 用T SQL语句修改教务数据库 子任务要求掌握使用两种方法进行教务数据库查看修改的操作方法 能够根据具体的要求进行数据库的修改 20 1 使用SQLServerManagementStudio查看修改教务数据库 1 启动SQLServerManagementStudio 在对象资源管理器中 用户可以右击所选择的数据库 教务管理 在弹出的快捷菜单中选择 属性 命令打开 数据库属性 窗口 如图3 7所示 我们就可以进行数据库信息的查看和修改 2 在 数据库属性 窗口的 常规 选择页中 显示的是数据库的基本信息 这些信息不能修改 3 在 文件 选择页中 可以修改数据库的数据文件与日志文件 也可以根据需要添加 也可以更改数据库的所有者 4 在 文件组 选择页中 可以修改现有的文件组 也可以指定数据库的默认文件组 添加新文件组 5 在 选项 选择页中 修改数据库的排序规则 21 2 使用T SQL语句查看修改教务数据库 1 用T SQL查看教务数据库属性可用以下系统存储过程来查看教务数据库 EXECsp helpdb教务管理 查看教务数据库信息USE教务管理EXECsp spaceused 查看教务数据库所占用空间USE教务管理EXECsp helpfilegroup 查看教务数据库中的文件组USE教务管理EXECsp helpfile 查看教务数据库中的文件 22 2 用T SQL修改教务数据库T SQL提供了修改数据库的语句ALTERDATABASE 可以利用该语句在数据库中添加或删除文件和文件组 也可以更改文件和文件组的属性 例如更改文件的名称和大小 ALTERDATABASE提供了更改数据库名称 文件组名称以及数据文件和日志文件的逻辑名称的能力 但不能改变数据库的存储位置 具体格式如下 ALTERDATABASE ADDFILE ADDLOGFILE REMOVEFILE ADDFILEGROUP REMOVEFILEGROUP MODIFYFILE MODIFYNAME MODIFYFILEGROUP SET 23 下面我们对前面建立的教务管理数据库进行一些简单的修改 将数据库教务管理日志文件的最大值改为现在的100MB ALTERDATABASE教务管理MODIFYFILE NAME 教务管理 log MAXSIZE 100MB 将教务管理 data数据文件的初始值改为20MB ALTERDATABASE教务管理MODIFYFILE NAME 教务管理 data SIZE 20MB 24 给教务管理数据库增加一个数据文件 ALTERDATABASE教务管理ADDFILE 添加文件 NAME 教务管理 data2 指定文件的逻辑名称 FILENAME D 教务管理 教务管理 data2 ndf 指定操作系统中的文件名 SIZE 10 指定文件大小 MAXSIZE 25 指定最大的文件大小 FILEGROWTH 5 指定文件增长的增量 25 任务3 2教务数据库的管理 子任务2删除数据库子任务内容用SQLServerManagementStudio删除数据库 用T SQL语句删除数据库 子任务要求掌握使用两种方法进行数据库删除的操作方法 能够清楚认识到删除数据库的后果 26 1 使用SQLServerManagementStudio删除数据库启动SQLServerManagementStudio 连接到本地数据库默认实例 在对象资源管理器中 展开树形目录 定位到要删除的数据库 右击该数据库 再选择 删除 命令 如图3 8所示 确认选择了正确的数据库 再单击 确定 按钮 若删除了数据库 则不能恢复内容 所以一定要慎重 27 2 使用T SQL语句删除数据库T SQL提供了数据库删除语句DROPDATABASE 具体格式如下 DROPDATABASE 数据库名 n 其中 数据库名指定要删除的数据库的名称 若要显示数据库列表 可使用sys databases目录视图 要执行DROPDATABASE操作 用户至少必须对数据库具有CONTROL权限 连接的数据库上下文也不能与要删除的数据库相同 执行删除数据库操作会从SQLServer实例中删除数据库 并删除该数据库使用的物理磁盘文件 执行删除操作时 如果数据库或它的任意一个文件处于脱机状态 则不会删除磁盘文件 可使用Windows资源管理器手动删除这些文件 不能删除当前正在使用的数据库 因为这时数据库正处于打开状态 以供用户读写 删除数据库时 都应备份master数据库 如果我们要删除教务管理数据库 则语句为 DROPDATABASE教务管理 28 任务3 2教务数据库的管理 子任务3数据库的其他常见管理子任务内容了解数据库和文件的状态 分离附加数据库 使数据库脱机联机 收缩数据库和文件 子任务要求理解数据库的几种常见管理方法 能够进行这几种常见管理的操作 29 1 了解数据库的状态数据库总是处于一个特定的状态中 管理员必须了解数据库的状态 才能有效地进行管理 数据库可能的状态包括ONLINE OFFLINE RESTORING RECOVERY RECOVERYPENDING SUSPECT或者EMERGENCY 查看实例上所有数据库的当前状态的具体操作如下 1 打开SQLServerManagementStudio的查询编辑器 并输入如下的SQL代码 SELECT FROMsys databases提示 sys databases是一个系统目录视图 2 单击工具栏上的 执行 按钮 数据库实例上所有数据库的状态信息 都会显示在结果窗格中的state desc列下 30 2 了解文件状态在SQLServer2005中 数据库文件的状态独立于数据库的状态 文件始终处于一个特定状态 例如ONLINE或OFFLINE 因此 对文件状态的了解是管理文件的前提条件 如果想要查看文件当前的状态 具体操作如下 1 打开SQLServerManagementStudio的查询窗口 输入如下的SQL代码 SELECT FROMsys master files提示 sys master files是一个系统目录视图 2 单击工具栏上的 执行 按钮 数据库实例上所有数据库的状态信息都会显示在结果窗格中的state desc列下 31 3 分离数据库在SQLServer中可以分离数据库的数据和事务日志文件 然后将它们重新附加到同一个或其他SQLServer实例上 分离教务数据库的具体步骤如下所示 1 打开SQLServerManagementStudio并连接到数据库实例 2 在 对象资源管理器 窗格中展开数据库实例下的 数据库 项 3 选中需要分离的数据库 教务管理 并右击 4 在弹出的快捷菜单中选择 任务 分离 命令 打开 分离数据库 对话框 5 要分离的数据库 列表框中的 数据库名称 栏中显示了所选数据库的名称 教务管理 6 设置完毕后 单击 确定 按钮 数据库引擎将执行分离数据库任务 如果分离成功 在 对象资源管理器 中将不会出现被分离的数据库 32 4 附加数据库在SQLServer中 用户可以在数据库实例上附加被分离的数据库 附加时 数据库引擎会启动数据库 通常情况下 附加数据库时会将数据库重置为分离或复制时的状态 附加数据库的具体步骤如下 1 打开SQLServerManagementStudio并连接到数据库实例 2 在 对象资源管理器 窗格中选中数据库实例下的 数据库 项 并右击 3 在弹出的快捷菜单中选择 附加数据库 命令 打开 附加数据库 对话框 4 在 附加数据库 对话框中 单击 添加 按钮 打开 定位数据库文件 对话框 5 在 定位数据库文件 对话框中选择数据库所在的磁盘驱动器并展开目录树定位到数据库的 mdf文件 如果需要为附加的数据库指定不同的名称 可以在 附加数据库 对话框的 附加为 栏中输入名称 6 如果需要更改所有者 可以在 所有者 栏中选择其他项 以更改数据库的所有者 7 设置完毕后 单击 确定 按钮 数据库引擎将执行附加数据库任务 如果附加成功 在 对象资源管理器 中将会出现被附加的数据库 33 5 使数据库脱机如果需要暂停数据库 可以执行数据库脱机操作 具体步骤如下 1 打开SQLServerManagementStudio并连接到数据库实例 2 在 对象资源管理器 窗格中展开数据库实例下的 数据库 项 3 选中需要脱机的数据库 教务管理 并右击 4 在弹出的快捷菜单中选择 任务 脱机 命令 执行脱机数据库任务 5 数据库脱机任务执行完成后 会弹出 使数据库脱机 对话框 34 6 使数据库联机如果需要重新启动脱机的数据库 可以执行数据库联机操作 具体步骤如下 1 打开SQLServerManagementStudio并连接到数据库实例 2 在 对象资源管理器 窗格中展开数据库实例下的 数据库 项 3 选中前面已经脱机的数据库 教务管理 并右击 4 在弹出的快捷菜单中选择 任务 联机 命令 执行联机数据库任务 5 数据库联机任务执行完成后 会弹出 使数据库联机 对话框 35 7 收缩数据库如果在创建数据库时 分配的空间过大 可以执行收缩数据库操作来减小数据库占用的空间 具体步骤如下 1 打开SQLServerManagementStudio并连接到数据库实例 2 在 对象资源管理器 窗格中展开数据库实例下的 数据库 项 3 选中需要收缩的数据库 教务管理 并右击 4 在弹出的快捷菜单中选择 任务 收缩 数据库 命令 打开 收缩数据库 对话框 如图3 15所示 5 在对话框的 当前分配的空间 文本框中显示的是数据库当前占用的空