数据库的创建与管理.ppt

第二章数据库的创建与管理 教学内容2 1数据库简介2 2估算数据库的空间需求2 3数据库的创建2 4数据库的管理2 5设置数据库的选项 SQLServer2000简介 SQLServer2000有6个系统数据库1 Master数据库2 Model数据库3 Msdb数据库4 Tempdb数据库5 Pubs数据库6 Northwind数据库 SQLServer系统最重要的数据库 它记录了SQLServer系统的所有系统信息 这些系统信息包括所有的登录信息 系统设置信息 SQLServer的初始化信息和其它系统数据库及用户数据库的相关信息 是所有用户数据库和Tempdb数据库的模板数据库 它含有Master数据库所有系统表的子集 这些系统数据库是每个用户定义数据库需要的 代理服务数据库 为其警报 任务调度和记录操作员的操作提供存储空间 一个临时数据库 它为所有的临时表 临时存储过程及其它临时操作提供存储空间 实例数据库 实例数据库 SQLServer工具和实用程序 2 1 1企业管理器2 1 2服务管理器2 1 3查询分析器服务器网络实用工具导入和导出数据客户端网络实用工具联机帮助文档 企业管理器是SQLServer中最重要的一个管理工具 SQLServer服务管理器是在服务器端实际工作时最有用的实用程序 服务管理器用来启动 暂停 继续和停止数据库服务器的实时服务 启动SQLServer服务 在系统中用户有几个启动SQLServer服务的办法 可以配置WindowsNT 2000服务 使每次WindowsNT 2000启动时都自动启动它 也可以用服务管理器启动 通过应用程序连接SQLServer时 也可以启动它 A 自动启动 WindowsNT 2000启动时 可以自动启动SQLServer服务 在安装SQLServer时 就可以选择使用此特性 方法是在设置服务账号的界面中的启动类别时 设置为 自动 安装SQLServer之后 也可以在WindowsNT 2000的控制面版中 将SQLServer服务设置为自动启动 步骤如下 1 打开WindowsNT 2000控制面版 2 双击 服务 3 在服务对话框中 滚动服务列表框找到MSSQLServer 此时MSSQLServer的启动类别为手动 4 右击MSSQLServer 在弹出的快捷菜单中有启动 暂停 停止或重新启动该服务等命令 如图4 9所示 可以使用这些命令启动 暂停 停止或重新启动SQLServer服务 5 在快捷菜单上选择 属性 打开对话框 将启动类型设置为 自动 6 单击 确定 此时Services列表框中MSSQLServer的启动类别已经变为 自动 B 使用SQLServer服务管理器启动 用户可以使用SQLServer服务管理器启动SQLServer服务 步骤如下 1 在 开始 菜单的 程序 项中 单击MicrosoftSQLServer程序组的服务管理器 2 在SQLServer服务管理器对话框的 服务器 下拉列表框中选择服务器 在 服务 下拉列表框中选择要启动的服务 注意 此时对话框底部的状态栏上的信息是 停止 3 单击 开始 继续 按钮 SQLServer服务管理器对话框底部的状态栏上的信息会从 停止 变为 开始 启动完成后变为正在运行 4 关闭SQLServer服务管理器对话框 关闭它以后SQLServer服务仍继续运行 另外 可以选中SQLServer服务管理器对话框中的 当启动OS时自动启动服务 复选框 这样使下一次操作系统启动时SQLServer服务自动启动 单击SQLServer服务管理器对话框中的 暂停 或 停止 按钮可以暂停或停止SQLServer服务 C 命令行方式启动 在 开始 菜单中单击 运行 命令 在运行程序对话框中 输入命令 netstartmssqlserver 同样 可以在运行程序对话框中输入netpausemssqlserver netstopmssqlserver或netcontinuemssqlserver命令 来暂停 停止或继续SQLServer服务 SQLServer2000新的图形化查询分析器用于输入和执行Transaction SQL语句 并且迅速查看这些语句的结果 以分析和处理数据库中的数据 2 1数据库的简介 数据库的创建过程实际就是数据库逻辑设计的物理实现过程 数据库文件 逻辑存储结构和物理存储结构一 数据库的逻辑存储结构指的是数据库是由哪些性质的信息所组成 SQLServer的数据库不仅仅只是数据的存储 所有与数据处理操作相关的信息都存储在数据库中 二 数据库的物理存储结构则是讨论数据库文件是如何在磁盘上存储的 数据库在磁盘上是以文件为单位存储的 由数据库文件和事务日志文件组成 一个数据库至少应该包含一个数据库文件和一个事务日志文件 创建数据库 系统要为数据库建立数据的存储结构 数据文件 事务日志文件 主数据文件 次数据文件 包含数据库的启动信息 用来存储数据 每个数据库有且只有一个主数据文件 保存所有主数据文件中容纳不下的数据 存储用来恢复数据库的日志文件 每个数据库必须至少有一个日志文件 可以有多个 最小为512K mdf ndf ldf 注意 默认状态下 数据库文件存放在 MSSQL data 目录下 主数据文件名为 数据库名 Data MDF 事务日志文件名为 数据库名 Log LDF 数据库的创建者可以在创建时指定其他的路径和文件名 也可以添加次要的数据文件和更多的事务日志文件 2 1 1数据库的存储结构 一个数据库是由文件组成的 文件是由盘区组成的 盘区是由页面组成的 SQLServer的数据存储基本单位是页 8K 页 盘区 8个连续的页面构成盘区 盘区是表和索引分配空间的基本单位 数据库的存储组织 数据库 数据文件 mdf或 ndf 日志文件 lfd 盘区 8个连续页面 数据 页 2 1 2事务日志 在对数据库的修改实现前 事务日志会自动记录对数据库对象所做的所有修改 有助于防止数据库损坏 事务日志的工作过程 发出事务 做标志 读入缓冲区 做记录 修改数据 写入标志 随着操作增多 事务日志增大 可以删除旧日志以为新的日志记录腾出空间 称为截断日志 事务日志初始大小为数据文件的10 25 2 2估算数据库的空间需求 为避免浪费磁盘空间资源或因估计不足造成空间不够 数据库管理员需要准确的估算数据库的容量 通常来说 经常被修改的数据库和很少被修改的数据库相比需要更大的日志空间 数据库文件组 为了便于分配和管理 SQLServer允许将多个文件归纳为同一组 并赋予此组一个名称 这就是文件组 与数据库文件一样 文件组也分为主文件组 PrimaryFileGroup 和次文件组 SecondaryFileGroup 2 3数据库的创建 创建数据库要指定数据库的名称 大小 文件存储空间和位置及一些特性信息等 建议 数据文件逻辑文件名命名时加入 data 后缀事务日志逻辑文件名命名时加入 log 后缀 创建数据库的方法 使用企业管理器 EnterpriseManager 创建数据库使用向导创建数据库使用Transact SQL语言创建数据库 1 使用企业管理器创建数据库 在企业管理器中 单击工具栏中的图标 或在数据库文件夹或其下属任一数据库图标上单击右键 选择新建数据库选项 就会出现如右图所示的对话框 在常规 General 页框中 要求用户输入数据库名称以及排序规则名称 点击数据文件 DataFiles 页框 该页框用来输入数据库文件的逻辑名称 存储位置 初始容量大小和所属文件组名称 点击事务日志 TransactionLog 页框 该页框用来设置事务日志文件信息 2 使用向导创建数据库 使用SQLServer向导可以创建一个最简单的数据库 其步骤如下 1 在企业管理器中 选中 控制台根目录 下的要创建数据库的服务器 然后单击 工具 菜单 单击 向导 命令 数据库的名称是Test 数据库文件将被放置在目录C ProgramFiles MicrosoftSQLServer MSSQL data中 事务日志文件将被放置在目录C ProgramFiles MicrosoftSQLServer MSSQL data中 数据库文件是 Test Data 大小为1数据库文件将按10 的比例自动增长 数据库文件可以无限增长 事务日志文件是 Test Log 大小为1事务日志文件将以10 的比例自动增加 事务日志文件可以无限增长 3 使用Transact SQL语言创建数据库 CREATEDATABASE数据库名 ON PRIMARY NAME 数据文件的逻辑名称 FILENAME 数据文件的物理名称 SIZE 数据文件的初始大小 MAXSIZE 数据文件的最大容量 FILEGROWTH 数据文件的增长量 n LOGON NAME 事务日志文件的逻辑名称 FILENAME 事务日志文件的物理名称 SIZE 事务日志文件的初始大小 MAXSIZE 事务日志文件的最大值 FILEGROWTH 事务日志文件的增长量 n database name 数据库的名称 最长为128个字符 PRIMARY 该选项是一个关键字 指定主文件组中的文件 LOGON 指明事务日志文件的明确定义 NAME 指定数据库的逻辑名称 这是在SQLServer系统中使用的名称 是数据库在SQLServer中的标识符 FILENAME 指定数据库所在文件的操作系统文件名称和路径 该操作系统文件名和NAME的逻辑名称一一对应 SIZE 指定数据库的初始容量大小 MAXSIZE 指定操作系统文件可以增长到的最大尺寸 FILEGROWTH 指定文件每次增加容量的大小 当指定数据为0时 表示文件不增长 说明 在Transact SQL语言的命令格式中 用 括起来的内容表示是可选的 n 表示重复前面的内容 用括起来表示在实际编写语句时 用相应的内容替代 用 括起来表示是必选的 类似A B的格式 表示A和B只能选择一个 不能同时都选 例1创建指定数据文件和事务日志文件的数据库USEmasterGOCREATEDATABASEtest 创建名称为test的数据库ON NAME test data 数据库主数据文件名称为test dataFILENAME I 数据库原理 data test data mdf 主数据文件存储位置SIZE 2 SIZE参数没有指定MB或KB 因此默认为MBMAXSIZE 5 最大增长空间为5MBFILEGROWTH 1 文件每次的增长大小为1MB LOGON 创建事务日志文件 NAME test log FILENAME I 数据库原理 data test log ldf SIZE 1MB MAXSIZE 2MB FILEGROWTH 1 GO 例2 创建了一个Company数据库 该数据库的主数据文件逻辑名称为Company data 物理文件名为Company mdf 初始大小为10MB 最大尺寸为无限大 增长速度为10 数据库的日志文件逻辑名称为Company log 物理文件名为Company ldf 初始大小为1MB 最大尺寸为5MB 增长速度为1MB USEmasterGOCREATEDATABASECompanyON NAME Company data FILENAME I 数据库原理 data Company mdf SIZE 10MB MAXSIZE unlimited FILEGROWTH 10 LOGON NAME Company log FILENAME I 数据库原理 data Company ldf SIZE 1MB MAXSIZE 10MB FILEGROWTH 1MB GO 例3 使用两个10MB的数据文件和两个1MB的事务日志文件创建了名为Work的数据库 主文件是列表中的第一个文件 并使用PRIMARY关键字显式指定 事务日志文件在LOGON关键字后指定 注意 FILENAME选项中所用的文件扩展名 主要数据文件使用 mdf 次要数据文件使用 ndf 事务日志文件使用 ldf USEmasterGOCREATEDATABASEWorkONPRIMARY NAME Work data FILENAME I data Work mdf SIZE 10MB MAXSIZE unlimited FILEGROWTH 10 NAME Work1 data FILENAME I data Work1 ndf SIZE 10MB MAXSIZE 5 FILEGROWTH 10 LOGON NAME Work log FILENAME I data Work ldf SIZE 1MB MAXSIZE 10MB FILEGROWTH 1MB NAME Work1 log FILENAME I data Work1 ldf SIZE 1MB MAXSIZE 10MB FILEGROWTH 1MB GO 例4 创建了名为Students的数据库 包含一个主文件组和一个次文件组 USEmasterGOCREATEDATABASEStudentONPRIMARY NAME Student data FILENAME I data Student mdf SIZE 10MB MAXSIZE unlimited FILEGROWTH 10 FileGroupGrp1 NAME Company1 data FILENAME I data Work1 ndf SIZE 10MB MAXSIZE 5 FILEGROWTH 10 LOGON NAME Company log FILENAME I data Work ldf SIZE 1MB MAXSIZE 10MB FILEGROWTH 1MB NAME Company1 log FILENAME I data Work1 ldf SIZE 1MB MAXSIZE 10MB FILEGROWTH 1MB GO 例5 创建名为Products的数据库 并指定单个文件 指定的文件成为主文件 并会自动创建一个2 5MB的事务日志文件 USEmasterGOCREATEDATABASEProductsON NAME prods data FILENAME I 数据库原理 data Products md SIZE 10 MAXSIZE 10 FILEGROWTH 1 GO 因为主文件的SIZE参数中没有指定MB或KB 所以主文件将以兆字节为单位进行分配 因为没有为事务日志文件指定 所以事务日志文件没有MAXSIZE 可以增长到填满所有可用的磁盘空间为止 例6CREATEDATABASEMarket因为该语句没有项 所以主数据库文件的大小为model数据库主文件的大小 事务日志文件的大小为model数据库事务日志文件的大小 因为没有指定MAXSIZE 文件可以增长到填满所有可用的磁盘空间为止 作业 创建一个指定多个数据文件和日志文件的数据库 该数据库名称为employees 有1个10MB和1个20MB的数据文件和2个10MB的事务日志文件 数据文件逻辑名称为employee1和employee2 物理文件名为employee1 mdf和employee2 mdf 主文件是employee1 由primary指定 两个数据文件的最大尺寸分别为无限大和100MB 增长速度分别为10 和1MB 事务日志文件的逻辑名为employeelog1和employeelog2 物理文件名为employeelog1 ldf和employeelog2 ldf 最大尺寸均为50MB 文件增长速度为1MB 2 3 5自动生成数据库的SQL脚本 所谓生成数据库的SQL脚本 Script 就是产生出能够创建现有数据库中各个数据库对象的SQL命令语句 2 4数据库的管理 2 4 1查看数据库的属性EXECsp helpdb数据库名 查看数据库信息 EXECsp spaceused 查看数据库所占用空间报表 EXECsp helpfilegroup 查看数据库文件组报表 EXECsp helpfile 查看数据库中文件的报表 2 4 2 使用ALTERDATABASE语句修改数据库 Alterdatabasedatabasename addfile n tofilegroupfilegroupname addlogfile n removefilelogical file name withdelete modifyfile modifyname new databasename addfilegroupfilegroup name removefilegroupfilegroup name modifyfilegroupfilegroup name filegroup property name new filegroup name 例2 4 1添加新数据文件添加一个5MB大小的新数据文件Company1 data到数据库Company中ALTERDATABASECompanyADDFILE name Compant1 data filename I 数据库原理 data Company1 data ndf size 5 例2 4 2添加新日志文件向数据库添加两个5MB的日志文件 ALTERDATABASECompanyADDLOGFILE name Compant1 log filename I 数据库原理 data Company1 log ldf size 5 name Compant2 log filename I 数据库原理 data Company2 log ldf size 5 例2 4 3添加文件组向数据库中添加文件组Company Group ALTERDATABASECompanyADDFILEGROUPCompany Group 例2 4 4向文件组中添加数据文件向数据库中添加文件Company2 Data到文件组Company Group ALTERDATABASECompanyADDFILE name Compant2 data filename I 数据库原理 data Company2 data ndf size 1maxsize 10filegrowth 10 TOFILEGROUPCompany Group 例2 4 5删除文件从数据库中删除数据文件Company1 data ALTERDATABASECompanyREMOVEFILECompany1 data 例2 4 5删除文件从数据库中删除日志文件Compant1 log ALTERDATABASECompanyREMOVEFILECompant1 log 例2 4 6更改数据库中文件的大小将数据库中的文件Company2 Data的大小更改为1