数据库的创建与管理.ppt

第二章数据库的创建与管理,教学内容 2.1数据库简介 2.2估算数据库的空间需求 2.3数据库的创建 2.4数据库的管理 2.5设置数据库的选项,SQL Server2000简介,SQL Server 2000有6个系统数据库 1. Master数据库 2. Model数据库 3. Msdb数据库 4. Tempdb数据库 5. Pubs数据库 6. Northwind数据库,SQL Server系统最重要的数据库，它记录了SQL Server系统的所有系统信息。这些系统信息包括所有的登录信息、系统设置信息、SQL Server的初始化信息和其它系统数据库及用户数据库的相关信息。,是所有用户数据库和Tempdb数据库的模板数据库，它含有Master数据库所有系统表的子集，这些系统数据库是每个用户定义数据库需要的。,代理服务数据库，为其警报、任务调度和记录操作员的操作提供存储空间,一个临时数据库，它为所有的临时表、临时存储过程及其它临时操作提供存储空间。,实例数据库,实例数据库,SQL Server 工具和实用程序,2.1.1企业管理器 2.1.2服务管理器 2.1.3查询分析器 服务器网络实用工具 导入和导出数据 客户端网络实用工具 联机帮助文档,企业管理器是SQL Server中最重要的一个管理工具。,SQL Server 服务管理器是在服务器端实际工作时最有用的实用程序。服务管理器用来启动、暂停、继续和停止数据库服务器的实时服务,启动SQL Server服务,在系统中用户有几个启动SQL Server服务的办法。 可以配置Windows NT2000服务， 使每次Windows NT2000启动时都自动启动它。 也可以用服务管理器启动。 通过应用程序连接SQL Server时，也可以启动它。,A. 自动启动,Windows NT2000启动时，可以自动启动SQL Server服务。在安装SQL Server时，就可以选择使用此特性，方法是在设置服务账号的界面中的启动类别时，设置为【自动】。 安装SQL Server之后，也可以在Windows NT2000的控制面版中，将SQL Server服务设置为自动启动。,步骤如下： (1)打开Windows NT2000控制面版； (2)双击【服务】； (3)在服务对话框中，滚动服务列表框找到MS SQL Server，此时MS SQL Server的启动类别为手动； (4)右击MS SQL Server，在弹出的快捷菜单中有启动、暂停、停止或重新启动该服务等命令，如图4-9所示。可以使用这些命令启动、暂停、停止或重新启动SQL Server服务。 (5)在快捷菜单上选择【属性】，打开对话框，将启动类型设置为【自动】。 (6)单击【确定】，此时Services列表框中MS SQL Server的启动类别已经变为【自动】。,B.使用SQL Server服务管理器启动,用户可以使用SQL Server服务管理器启动SQL Server服务，步骤如下： (1)在【开始】菜单的【程序】项中，单击Microsoft SQL Server程序组的服务管理器。 (2)在SQL Server服务管理器对话框的【服务器】下拉列表框中选择服务器，在【服务】下拉列表框中选择要启动的服务。注意，此时对话框底部的状态栏上的信息是【停止】。 (3)单击【开始/继续】按钮。SQL Server服务管理器对话框底部的状态栏上的信息会从【停止】变为【开始】，启动完成后变为正在运行 (4)关闭SQL Server服务管理器对话框，关闭它以后SQL Server服务仍继续运行。,另外，可以选中SQL Server服务管理器对话框中的【当启动OS时自动启动服务】复选框，这样使下一次操作系统启动时SQL Server服务自动启动。 单击SQL Server服务管理器对话框中的【暂停】或【停止】按钮可以暂停或停止SQL Server服务。,C.命令行方式启动,在【开始】菜单中单击【运行】命令，在运行程序对话框中，输入命令:net start mssqlserver。 同样，可以在运行程序对话框中输入net pause mssqlserver、net stop mssqlserver或net continue mssqlserver命令，来暂停、停止或继续SQL Server服务。,SQL Server 2000新的图形化查询分析器用于输入和执行Transaction-SQL语句，并且迅速查看这些语句的结果，以分析和处理数据库中的数据。,2.1数据库的简介,数据库的创建过程实际就是数据库逻辑设计的物理实现过程。,数据库文件,逻辑存储结构和物理存储结构 一、数据库的逻辑存储结构指的是数据库是由哪些性质的信息所组成，SQL Server的数据库不仅仅只是数据的存储，所有与数据处理操作相关的信息都存储在数据库中。 二、数据库的物理存储结构则是讨论数据库文件是如何在磁盘上存储的，数据库在磁盘上是以文件为单位存储的，由数据库文件和事务日志文件组成，一个数据库至少应该包含一个数据库文件和一个事务日志文件,创建数据库，系统要为数据库建立数据的存储结构,数据文件,事务日志文件,主数据文件,次数据文件,包含数据库的启动信息、用来存储数据，每个数据库有且只有一个主数据文件。,保存所有主数据文件中容纳不下的数据,存储用来恢复数据库的日志文件。每个数据库必须至少有一个日志文件，可以有多个。最小为512K。,.mdf,.ndf,.ldf,注意：默认状态下，数据库文件存放在MSSQLdata目录下，主数据文件名为”数据库名_Data.MDF”,事务日志文件名为“数据库名_Log.LDF”。 数据库的创建者可以在创建时指定其他的路径和文件名，也可以添加次要的数据文件和更多的事务日志文件。,2.1.1 数据库的存储结构,一个数据库是由文件组成的，文件是由盘区组成的，盘区是由页面组成的。,SQL Server的数据存储基本单位是页。8K/页,盘区：8个连续的页面构成盘区。,盘区是表和索引分配空间的基本单位。,数据库的存储组织,数据库,数据文件.mdf或.ndf,日志文件.lfd,盘区（8个连续页面）,数据,页,2.1.2事务日志,在对数据库的修改实现前，事务日志会自动记录对数据库对象所做的所有修改。有助于防止数据库损坏。,事务日志的工作过程：,发出事务,做标志，读入缓冲区,做记录,修改数据,写入标志,随着操作增多，事务日志增大，可以删除旧日志以为新 的日志记录腾出空间，称为截断日志,事务日志初始大小为数据文件的1025%。,2.2 估算数据库的空间需求,为避免浪费磁盘空间资源或因估计不足造成空间不够，数据库管理员需要准确的估算数据库的容量。 通常来说，经常被修改的数据库和很少被修改的数据库相比需要更大的日志空间。,数据库文件组,为了便于分配和管理，SQL Server允许将多个文件归纳为同一组，并赋予此组一个名称，这就是文件组。 与数据库文件一样，文件组也分为主文件组（Primary File Group）和次文件组（Secondary File Group）。,2.3 数据库的创建,创建数据库要指定数据库的名称、大小。文件存储空间和位置及一些特性信息等。 建议： 数据文件逻辑文件名命名时加入“_data”后缀 事务日志逻辑文件名命名时加入“_log”后缀,创建数据库的方法,使用企业管理器（Enterprise Manager）创建数据库 使用向导创建数据库 使用Transact-SQL语言创建数据库。,1.使用企业管理器创建数据库,（）在企业管理器中，单击工具栏中的 图标，或在数据库文件夹或其下属任一数据库图标上单击右键，选择新建数据库选项，就会出现如右图所示的对话框。,在常规（General）页框中，要求用户输入数据库名称以及排序规则名称。,点击数据文件（Data Files）页框，该页框用来输入数据库文件的逻辑名称、存储位置、初始容量大小和所属文件组名称,点击事务日志（Transaction Log）页框，该页框用来设置事务日志文件信息,2使用向导创建数据库,使用SQL Server向导可以创建一个最简单的数据库，其步骤如下： (1) 在企业管理器中，选中“控制台根目录”下的要创建数据库的服务器，然后单击“工具”菜单，单击“向导”命令,数据库的名称是 Test。 数据库文件将被放置在目录 C:Program FilesMicrosoft SQL ServerMSSQLdata 中。 事务日志文件将被放置在 目录 C:Program FilesMicrosoft SQL ServerMSSQLdata 中。 数据库文件是: Test_Data，大小为 1 数据库文件将按 10 % 的比例自动增长。 数据库文件可以无限增长。 事务日志文件是: Test_Log，大小为 1 事务日志文件将以 10 % 的比例自动增加。 事务日志文件可以无限增长。,3.使用Transact-SQL语言创建数据库,CREATE DATABASE数据库名 ONPRIMARY （NAME=数据文件的逻辑名称， FILENAME=数据文件的物理名称 ，SIZE=数据文件的初始大小 ，MAXSIZE=数据文件的最大容量 ，FILEGROWTH=数据文件的 增长量），n,LOG ON （NAME=事务日志文件的逻辑名称， FILENAME=事务日志文件的物理名称 ，SIZE=事务日志文件的初始大小 ，MAXSIZE=事务日志文件的最大值 ，FILEGROWTH=事务日志文件的 增长量），n,database_name：数据库的名称，最长为128个字符。 PRIMARY：该选项是一个关键字，指定主文件组中的文件。 LOG ON：指明事务日志文件的明确定义。 NAME：指定数据库的逻辑名称，这是在SQL Server系统中使用的名称，是数据库在SQL Server中的标识符。,FILENAME：指定数据库所在文件的操作系统文件名称和路径，该操作系统文件名和NAME的逻辑名称一一对应。 SIZE：指定数据库的初始容量大小。 MAXSIZE：指定操作系统文件可以增长到的最大尺寸。 FILEGROWTH：指定文件每次增加容量的大小，当指定数据为0时，表示文件不增长。,说明：在Transact-SQL语言的命令格式中，用 括起来的内容表示是可选的； ，n表示重复前面的内容； 用括起来表示在实际编写语句时，用相应的内容替代； 用 括起来表示是必选的； 类似A|B的格式，表示A和B只能选择一个，不能同时都选。,例1创建指定数据文件和事务日志文件的数据库 USE master GO CREATE DATABASE test -创建名称为test的数据库 ON ( NAME = test_data, -数据库主数据文件名称为test_data FILENAME = I:数据库原理datatest_data.mdf, -主数据文件存储位置 SIZE = 2, -SIZE参数没有指定MB或KB，因此默认为MB MAXSIZE = 5, -最大增长空间为5MB FILEGROWTH = 1 -文件每次的增长大小为1MB ) LOG ON -创建事务日志文件 ( NAME = test_log, FILENAME = I:数据库原理datatest_log.ldf, SIZE = 1MB, MAXSIZE = 2MB, FILEGROWTH = 1 ) GO,例2：创建了一个Company数据库，该数据库的主数据文件逻辑名称为Company_data，物理文件名为Company.mdf，初始大小为10MB，最大尺寸为无限大，增长速度为10%；数据库的日志文件逻辑名称为Company_log，物理文件名为Company.ldf，初始大小为1MB，最大尺寸为5MB，增长速度为1MB。,USE master GO CREATE DATABASE Company ON ( NAME = Company_data, FILENAME = I:数据库原理data Company.mdf, SIZE = 10MB, MAXSIZE = unlimited, FILEGROWTH = 10% ) LOG ON ( NAME = Company_log, FILENAME = I:数据库原理data Company.ldf, SIZE = 1MB, MAXSIZE = 10MB, FILEGROWTH = 1MB ) GO,例3：使用两个10MB的数据文件和两个1 MB的事务日志文件创建了名为Work的数据库。主文件是列表中的第一个文件，并使用PRIMARY关键字显式指定。事务日志文件在LOG ON关键字后指定。 注意，FILENAME选项中所用的文件扩展名，主要数据文件使用 .mdf，次要数据文件使用 .ndf，事务日志文件使用 .ldf。,USE master GO CREATE DATABASE Work ON PRIMARY ( NAME = Work_data, FILENAME = I:dataWork.mdf, SIZE = 10MB, MAXSIZE = unlimited, FILEGROWTH = 10% ), ( NAME = Work1_data, FILENAME = I:dataWork1.ndf, SIZE = 10MB, MAXSIZE = 5, FILEGROWTH = 10% ),LOG ON ( NAME = Work_log, FILENAME = I:dataWork.ldf, SIZE = 1MB, MAXSIZE = 10MB, FILEGROWTH = 1MB ), ( NAME = Work1_log, FILENAME = I:dataWork1.ldf, SIZE = 1MB, MAXSIZE = 10MB, FILEGROWTH = 1MB ) GO,例4：创建了名为Students的数据库。包含一个主文件组和一个次文件组。,USE master GO CREATE DATABASE Student ON PRIMARY ( NAME = Student_data, FILENAME = I:dataStudent.mdf, SIZE = 10MB, MAXSIZE = unlimited, FILEGROWTH = 10% ), FileGroup Grp1 ( NAME = Company1_data, FILENAME = I:dataWork1.ndf, SIZE = 10MB, MAXSIZE = 5, FILEGROWTH = 10% ),LOG ON ( NAME = Company_log, FILENAME = I:dataWork.ldf, SIZE = 1MB, MAXSIZE = 10MB, FILEGROWTH = 1MB ), ( NAME = Company1_log, FILENAME = I:dataWork1.ldf, SIZE = 1MB, MAXSIZE = 10MB, FILEGROWTH = 1MB ) GO,例5：创建名为Products的数据库，并指定单个文件。指定的文件成为主文件，并会自动创建一个2.5 MB的事务日志文件。,USE master GO CREATE DATABASE Products ON ( NAME = prods_data, FILENAME = I:数据库原理dataProducts.md, SIZE = 10, MAXSIZE = 10, FILEGROWTH = 1 ) GO,因为主文件的SIZE参数中没有指定MB或KB，所以主文件将以兆字节为单位进行分配。 因为没有为事务日志文件指定，所以事务日志文件没有MAXSIZE，可以增长到填满所有可用的磁盘空间为止。,例6 CREATE DATABASE Market 因为该语句没有项，所以主数据库文件的大小为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 查看数据库的属性 EXEC sp_helpdb 数据库名 /* 查看数据库信息*/ EXEC sp_spaceused /* 查看数据库所占用空间报表*/ EXEC sp_helpfilegroup /* 查看数据库文件组报表*/ EXEC sp_helpfile /* 查看数据库中文件的报表*/,2.4.2. 使用ALTER DATABASE语句修改数据库,Alter database databasename add file,n to filegroup filegroupname |add log file ,n |remove file logical_file_name with delete |modify file |modify name=new_databasename |add filegroup filegroup_name |remove filegroup filegroup_name |modify filegroup filegroup_name filegroup_property|name=new_filegroup_name,例2.4.1添加新数据文件 添加一个5MB大小的新数据文件Company1_data到数据库Company中 ALTER DATABASE Company ADD FILE ( name = Compant1_data, filename = I:数据库原理dataCompany1_data.ndf, size = 5 ),例2.4.2添加新日志文件 向数据库添加两个5MB的日志文件,ALTER DATABASE Company ADD LOG FILE ( name = Compant1_log, filename = I:数据库原理dataCompany1_log.ldf, size = 5, ), ( name = Compant2_log, filename = I:数据库原理dataCompany2_log.ldf, size = 5, ),例2.4.3添加文件组 向数据库中添加文件组Company_Group,ALTER DATABASE Company ADD FILEGROUP Company_Group,例2.4.4向文件组中添加数据文件 向数据库中添加文件Company2_Data到文件组Company_Group,ALTER DATABASE Company ADD FILE ( name = Compant2_data, filename = I:数据库原理dataCompany2_data.ndf, size = 1 maxsize = 10 filegrowth = 10% ) TO FILEGROUP Company_Group,例2.4.5删除文件 从数据库中删除数据文件Company1_data,ALTER DATABASE Company REMOVE FILE Company1_data,例2.4.5删除文件 从数据库中删除日志文件Compant1_log,ALTER DATABASE Company REMOVE FILE Compant1_log,例2.4.6更改数据库中文件的大小 将数据库中的文件Company2_Data的大小