SQLServer第2章数据库的创建与管理.ppt

1、,SQL Server数据库,1,使用企业管理器创建数据库,2,使用Transact-SQL语言创建数据库,3,数据库的修改和删除,4,数据库迁移,第2章 数据库的创建与管理,2.1 SQL Server数据库,SQL server 2000作为一个数据库管理系统（DBMS），它的主要功能就是管理数据库及其其他数据库对象，为了方便用户操作数据库和数据库对象，系统提供了两个实用工具，其中一个是以图形可视化界面操作数据库及其数据库对象的企业管理器，另一个是以SQL语句方法操作数据库及其数据库对象的查询分析器,一、SQL Server数据库结构,数据库的物理结构 数据库文件类型 数据文件 主要数据文

2、件和次要数据文件 日志文件 文件组 主文件组 用户定义文件组 数据库的逻辑结构,数据库的物理结构,在SQL server中任何一种数据库的内容都是以文件的形式存储在磁盘上的，具体来说就是以数据库文件和事务日志 文件的形式存储在磁盘上的，所以我们通常说数据库由数据库文件和事务日志 文件组成，一个数据库至少应包含一个数据库文件和一个事务日志文件。,数据库文件 类型,数据库文件（Database File）是存放数据库中的所有数据和数据库对象（表、视图等）的一种文件。分为两种类类型： 1. 主数据文件：是数据库的起点，用来存储数据库的启动信息和部分或全部数据。扩展名为“.mdf”。注意：一个数据库只

3、能有一个主数据文件。 2. 次数据文件：用来存储主数据库没有存储的其它数据。扩展名为“.ndf”。注意：一个数据库可以有0个或多个次数据文件。,日志文件,日志文件用来记录SQL Server的所有事务以及由这些事务引起的数据库数据的变化。所谓事务就是一个单元的工作，该单元的工作要么全部完成，要么全部取消。 SQL Server 2000是遵守先写日志再执行数据库修改的数据库系统，在数据库数据的任何变化写到磁盘之前，首先在日志文件中做记录，因此如果SQL Server系统发生出错，甚至出现数据库系统崩溃时，数据库管理员（DBA）可以通过日志文件完成数据库的修复与重建。,日志文件,每个数据库必须至

4、少有一个日志文件，但可以不止一个。日志文件的默认文件扩展名是 .ldf。建立数据库时，SQL Server会自动建立数据库的事务日志。 一般情况下，一个简单的数据库可以只有一个主数据文件和一个日志文件。如果数据库很大或很重要，则可以设置多个次要数据文件或更多的日志文件。,文件组,为了对数据库中的数据文件（主、次）进行有效管理， SQL Server 系统允许将多个数据库文件按照其类型进行分组，相同类型的归纳为同一组，并赋予此组一个名称，这就是文件组。,文件组,SQL Server 2000有两种类型的文件组：主文件组和用户定义文件组。 主文件组包含主要数据文件（.mdf）和任何没有明确指派给其

5、它文件组的其它文件。 用户定义文件组是在 CREATE DATABASE（创建数据库）或 ALTER DATABASE（修改数据库）语句中，使用 FILEGROUP 关键字另外指定的文件组。,使用文件组需要注意以下几点：,SQL Server2000中的文件或文件组不能由一个以上数据库使用； 每个数据库中的文件只能是一个文件组的成员； 日志文件不属于任何文件组。 如果文件组中的某个数据文件遭到破坏， 那么整个文件组中的数据都无法使用。,二、SQL Server数据库类型,SQL Server 2000数据库分为三种类型：系统数据库、示例数据库和用户自定义的数据库。 相同点：三种数据库都能够创建

6、数据库对象、存储数据以及管理数据 不同点： （1）只有系统数据库可以用来操作和管理数据库系统。 （2）系统数据库（4个）和样本数据库（2个）一般是在安装SQL Server 2000的同时，系统自动建立的。 而用户自定义的数据库是用户自己创建的。,系统数据库 master 数据库：是SQL Server 2000的总控数据库。master 数据库从整体上控制 SQL Server系统和用户数据库应用程序的运行。它记录了所有的登录标识系统配置 SQL Server初始化设置用户数据库及其初始化信息。由于master 数据库是非常重要的一个系统的数据库，所以一般禁止用户直接访问。 tempdb 数

7、据库 ：是SQL Server系统的一个临时数据库，所有连接到系统的用户的临时表和存储过程都存储在该数据库中。在连接断开时自动清除。,系统数据库 model 数据库 ：是建立所有数据库的模板库，所有在本系统中创建的新数据库的内容，刚开始都与这个模板数据库完全一样。 msdb 数据库 ：是SQL Server 2000 代理服务所使用的数据库，用来执行预定的任务，如数据库备份和数据转换、调度警报和作业等。,示例数据库 Pubs和Northwind数据库 这两个是样本数据库，SQL Server文档和联机丛书的大部分实例都是在这两个示例数据库基础上生成的，这是为了方便初学者使用。,用户数据库,用户

8、数据库是用户在开发具体应用程序时，因实际需要而在SQL Server 2000系统中建立的的数据库，它们都以model系统数据库为样板。用户数据库也可从其他数据库管理系统建立的数据库经转换而来。,2.2 创建数据库,若要创建数据库，必须确定数据库的名称、所有者、大小，以及用于存储数据库的文件和文件组。,创建数据库的方法 : 1、使用企业管理器（Enterprise Manager）创建数据库。 2、在查询分析器中利用Transact-SQL语言创建数据库。,1、使用企业管理器创建数据库,（）在企业管理器中，单击工具栏中的 图标，或在数据库文件夹或其下属任一数据库图标上单击右键，选择新建数据库选

9、项。 （）在常规（General）页框中，要求用户输入数据库名称以及排序规则名称。 （）点击数据文件（Data Files）页框，该页框用来输入数据库文件的逻辑名称、存储位置、初始容量大小和所属文件组名称，如图3-10所示。 （）点击事务日志（Transaction Log）页框，该页框用来设置事务日志文件信息，如图3-11所示。 （）单击图3-11中的“确定”按钮，则开始创建新的数据库。,2、使用Transact-SQL语言创建数据库,简单语法如下： CREATE DATABASE 数据库名 ON 子句 LOG ON 子句 说明： ON子句：用来定义数据文件(.mdf .ndf）属性。 LO

10、G ON句:用来定义日志文件（.ldf）属性,1. ON 子句格式 ON PRIMARY （name=数据文件逻辑文件名， filename=数据文件物理文件名 ，SIZE=size ，MAXSIZE=max_size|UNLIMITED ，FILEGROWTH=growth_increment ) ，n ， FILEGROUP 文件组名 ( 数据文件定义) ，n ,关于数据库文件以及 所属文件组的定义选项。（ ,n表示可以定义多个）,指数据库创建后在所有T-SQL语句中引用该文件时的名字。,存放到磁盘上时的具体路径和文件名。操作系统借助于该项去访问相应数据。,指定数据库文件的初始大小,指定数

11、据库文件的最大大小,表明文件大小不限,指定数据库文件每次的增量 ： 10% 2MB,2. LOG ON 子句格式 LOG ON （name=日志文件逻辑文件名， filename=日志文件物理文件名 ，SIZE=初始大小 ，MAXSIZE=max_size|UNLIMITED ，FILEGROWTH=growth_increment ) ，n ,【例2-1】创建一个BOOK数据库,CREATE DATABASE BOOK ON (NAME=BOOK1, FILENAME=F:SQL2008BOOK1.MDF, SIZE=5, MAXSIZE=15, FILEGROWTH=2), (NAME=B

12、OOK2, FILENAME=F:SQL2008BOOK2.NDF, SIZE=2,FILEGROUP yyy,【例2-1】创建一个BOOK数据库,MAXSIZE=10, FILEGROWTH=10%) LOG ON (NAME=BOOK_LOG, FILENAME=F:SQL2008BOOK_LOG.LDF, SIZE=1, MAXSIZE=10, FILEGROWTH=1),2.4 修改数据库,修改数据库的方法 : 1、使用企业管理器（Enterprise Manager）修改数据库。 2、在查询分析器中利用Transact-SQL语言修改数据库。,在企业管理器中修改数据库,方法： 选定某

13、个数据库 右键单击快捷菜单的属性。 扩大数据库：按至少1MB增加 收缩数据库：自动和手动,利用企业管理器修改数据库,设置中的各选项说明如下： ANSI NULL默认设置：允许在数据库表的列中输入空（NULL）值。 递归触发器：允许触发器递归调用。SQL Server设定的触发器递归调用的层数最多为32层。 自动更新统计信息：允许使用SELECT INTO或BCP、WRITETEXT、UPDATETEXT命令向表中大量插入数据。 残缺页检测：允许自动检测有损坏的页。 自动关闭：当数据库中无用户时，自动关闭该数据库，并将所占用的资源交还给操作系统。,自动收缩：允许定期对数据库进行检查，当数据库文件

14、或日志文件的未用空间超过其大小的25%时，系统将会自动缩减文件使其未用空间等于25%。 自动创建统计信息：在优化查询（Query Optimizer）时，根据需要自动创建统计信息。 使用被引用的标识符：标识符必须用双引号括起来，且可以不遵循Transact-SQL命名标准。,2. 利用企业管理器修改数据库,用Transact-SQL语句修改数据库,在查询分析器中，可以使用ALTER DATABASE语句来对数据库进行修改。 ALTER DATABASE功能: 在数据库中添加或删除文件和文件组。更改数据库名称、文件组名称以及数据文件和日志文件的逻辑名称及其各种属性。,简要语法,ALTER DAT

15、ABASE 数据库名 ADD FILE ,.n |ADD LOG FILE ,.n |REMOVE FILE 逻辑文件名 |ADD FILEGROUP 文件组名 | REMOVE FILEGROUP 文件组名 | MODIFY FILE | MODIFY NAME =新数据库名 ,内容: ( NAME =逻辑文件名 ,FILENAME = 物理文件名 , SIZE = 文件大小 , MAXSIZE = maxsize | UNLIMITED , FILEGROWTH = 文件的增长增量 ),【例子2-2】,ALTER DATABASE BOOK MODIFY FILE (NAME=book1,

16、SIZE=8) ALTER DATABASE BOOK REMOVE FILE book2,Name选项必须有； 文件创建后只能修改其size maxsize filegrowth属性，不能修改name filename; Size的值只能比原来 扩大,【例子2-3】,ALTER DATABASE TEACHDB ADD FILE (NAME=TEACHDB1, FILENAME=F:SQL2008TEACHDB1.NDF, SIZE=4, MAXSIZE=10, FILEGROWTH=10% ),用Transact-SQL语句收缩数据库,语法: DBCC SHRINKDATABASE (数据

17、库名,目标百分比) 说明:目标百分比指收缩后文件中所要剩余的可用空间的百分比.(语法上应是一个正数,分数学上的百分比数据) 【例子2-4】 dbcc shrinkdatabase(teachdb,10),删除数据库,删除数据库的方法 : 1、使用企业管理器（Enterprise Manager）删除数据库。 2、在查询分析器中利用Transact-SQL语言删除数据库。,其语法如下： DROP DATABASE 数据库名,n 【例3-3】 drop database book,2.5 数据库迁移,分离和附加SQL Server数据库 1分离和附加数据库的作用 当在SQL Server 2000

18、中新建一个数据库时，系统数据库master记载了此数据库的相应信息，从而将它附加到SQL Server中来。此时，服务器拥有对该数据库的一切管辖权，包括对它的所有访问和管理操作。,分离和附加数据库的作用,但出于以下原因，我们有时需要将一个数据库从SQL Server中分离出来，使其中的所有数据文件和日志文件脱离服务器独立存在，然后又将它附加到原SQL Server或另一台计算机的服务器中： 例如：将数据库移到其他计算机的SQL Server中使用。 例如：改变存放数据库数据文件和日志文件的物理位置、名称。,分离数据库,数据库分离以后，在企业管理器树状结构窗口中就找不到该数据库了。此时，可将该数

19、据库对应的数据文件和日志文件移动到其他磁盘或计算机去，再进行附加数据库操作。,分离数据库,通过数据库分离来改变数据库中数据文件和日志文件的存储位置 例如：创建了一个数据库BOOK,原数据文件和日志文件存放位置： D:SQL2008BOOK1.MDF D:SQL2008BOOK2.NDF (1)将主数据文件BOOK1.MDF改名、改存储位置。 D :bookshop.mdf (2)将次数据文件BOOK2.NDF改名。 D:SQL2008BOOKshop.NDF,附加数据库,启动企业管理器 在企业管理器树状结构窗口中，在数据库结点上单击鼠标右键，在弹出的快捷菜单上，选择所有任务中的“附加数据库”命

20、令，如图所示。 在“附加数据库”对话框中的“要附加数据库的MDF文件:M”文本框中输入主数据文件的存放路径及文件名，也可单击按钮查找选择对应的.mdf文件。这里找到已经WINDOWS资源管理器移到D盘根目录并改名bookshop.mdf的主数据文件。,附加数据库,选定主数据文件后，在中间框中将显示原数据库各文件名和当前文件位置。如果在当前主数据文件所在的位置找不到原来的文件，则在该文件的当前位置行会出现红色“”提示，如上页图所示。这时就应重新输入正确的路径和文件名。 用鼠标和键盘手工修正了错误的文件位置和文件名后，原来的“”将变成“”。 可在“附加为A：”右边的文本框中输入该数据库附加到SQL Server后的新文件名。在“指定数据库所有者：S”右边的列表框中选择一个用户帐户作为该数据库的所有者。 按“确定”按钮即显示消息框，提示附加数据库顺利完成。 提示：SQL Server 2000中，也可以使用系统存储过