实验1+数据库的创建与管理

1、实验1 数据库的创建与管理目的和要求1巩固数据库的基础知识；2掌握创建数据库的两种方法；3掌握查看、修改数据库属性的方法；4掌握缩小、更名与删除数据库的方法。 背景知识：数据库实际上就是在硬盘中为诸如表、视图及存储结构等预先分配空间的系列文件。在SQL Server2000数据库中存在主要数据文件、次要数据文件和事务日志等三种数据文件。主要数据文件包含数据库的启动信息并用于存储数据，文件的扩展名为.MDF，每个数据库都包含一个主要数据文件，该文件存放二种对象，即用户对象和系统对象。用户对象包括表、存储过程、视图等，用于修改或保存用户输入的信息；系统对象有表名、数据库用户帐号、索引地址等保持数据

2、库工作所需要的信息。系统表应该保存在主要数据文件中，但用户信息可以移动到次要数据文件中。次要数据文件中主要存储数据文件，文件的扩展名为.NDF，如果主要数据文件包含了数据库中的所有数据，那么数据库就可以不要次要数据文件，但如果数据库非常大，就应该有多个辅助数据文件，用来存放用户数据，但不能存放系统对象。使用次要数据文件，可以让数据库文件不断得到扩充，并且可以通过将不同的文件存放到不同的磁盘空间中，以实现同时对多个磁盘进行访问，提高数据存储的并发性。事务日志文件主要用来做为数据库的联机备份，文件扩展名为.LDF。在SQL Server2000中，数据库必须至少包含一个事务日志文件，每个事务日志文

3、件仅能被一个数据库拥有，数据和事务日志信息不应该混合在一起，SQL Server2000主要通过事务日志文件来实现对事务进行恢复。拥有一定的权限的用户可以通过企业管理器或Transact-SQL二种方式来创建数据库，企业管理器简单易于使用，但Transact-SQL方便对任务进行规划，Transact-SQL是SQL Server2000功能的核心，不管应用程序的用户界面是什么形式，要和 数据库服务器进行交互，最终都要统一到Transact-SQL语言。该语言为了扩展标准SQL语言以方便用户直接完成应用程序的开发，在其中加入了程序流的控制结构、局部变量和其他一些功能，利用这些功能用户可以编写出

4、复杂的查询语句，也可以建立驻留于SQL Server2000服务器上数据库对象，在SQL Server2000服务器上自动生成大量的系统存储过程，熟练使用这些存储过程可以大大减少为实现某些功能而编写的代码数量。SQL Server2000提供的Transact-SQL语言还具有数据库管理的功能，SQL Server2000的企业管理器所能完成的大多数功能都可以利用Transact-SQL语言编写代码来实现。内容和步骤一、建数据库在SQL Server2000 下创建数据库有两种方法：1使用企业管理器直接创建数据库1) 选中要建立数据库的服务器节点，双击展开该节点。左键双击【数据库】节点，右键单

5、击空白区并选择【新建数据库】命令，会弹出窗体如图1-1所示。 图1-1 新建数据库2) 在Name文本框中输入正确的数据库名称，然后单击【数据文件】标签，将得到图1-2所示界面，在【文件名】表格可以输入文件名，SQL Server2000在默认情况下自动为用户输入的文件名后面增加上下划线和Data字样。 图1-2 数据文件对话框3) 在【文件组】列表中，输入文件所属于的文件组名称。4) 在【属性】选项组里，选择【文件自动增长】，当数据文件的空间不够用时，SQL Server2000可以自动增加容量。SQL Server2000提供了二种方式来实现数据文件的自动增加。一种是以【按兆字节】的方式递

6、增，一次增加1MB；另外一种方式是以【按百分比】的方式递增，一次递增原数据库文件容量的10%。5) 单击【位置】表格中的按钮，弹出图1-3所示对话框，可以选择存放数据文件的位置 图1-3 数据库位置对话框6) 单击【事务日志】标签，在此键入日志文件的名字、位置、大小，在这个标签中还提供了让日志文件在日志需要更多空间时自动扩展选项和日志文件大小的最大值，方法与设置数据文件的方法类似。如图1-4所示： 图1-4 事务日志对话框 2使用Transact-SQL语句创建数据库2.1使用Transact-SQL语句创建数据库的语法如下：CREATE DATABASE database_name ONPR

7、IMARY( NAME = logical_file_name, FILENAME = os_file_name ,SIZE = size ,MAXSIZE = max_size|UNLIMITED ,FILEGROWTH = growth_increment),n<filegroup>:=FILEGROUP filegroup_name <filespec> ,nLOG ON(NAME = logical_file_name,FILENAME = os_file_nameSIZE = size,MAXSIZE = max_size | UNLIMITED,FILEGR

8、OWTH = growth_increment)注意：所有用括起来表示的是可以省略的选项，1n表示同样的选项可以重复1到n遍；<>括起来表示是对一组若干选项的代替，实际编写语句时，应该用响应的选项来代替；类似A|B的语句，表示可以选择A也可以选择B，但不能同时选择。说明：Ø database_name：表示为数据库起的名字，在同一个服务器中，数据库的命名必须唯一。Ø ON ：表示存放数据库的数据文件将在后边给出相关的定义。Ø PRIMARY：定义数据库的主数据文件，在PRIMARY filegroup中，第一个数据文件是主数据文件，如果没有给出PRIM

9、ARY关键字则默认文件序列中的第一个文件为主数据文件。Ø LOG ON：定义数据库的日志文件。Ø NAME：定义操作系统文件的逻辑文件名。该文件名只能在Transact-SQL语句中使用，是实际磁盘的文件名代号。Ø FILENAME：定义操作系统文件的实际名字，包括文件所在的路径。Ø SIZE：定义文件的初始长度。Ø MAXSIZE：定义文件能够增长到的最大长度，可以设置UNLIMITED关键字，使文件可以无限制增长，直到空间用完。Ø FILEGROWTH：定义操作系统文件长度不够时每次增长的速度。可以用MB、KB或使用%来设置增长速

10、度。2.2 使用Transact-SQL语句创建数据库的步骤： 打开查询分析器，连接服务器，选用“windows身份验证连接方式”得图1-5所示对话框： 图1-5 查询分析器对话框 在光标所在处输入创建数据库的Transact-SQL语句； 点击【分析查询】（CTRL+F5）按钮，完成数据库创建。例1 创建一个student数据库，其中主文件组包含主要数据文件student1_dat和次要数据文件student2_dat。有2个次要文件组：studentGroup1包含2个次要数据文件student1Fi1_dat 和 studentFi2_dat, studentGroup2包含studen

11、t2Fi1_dat和student2Fi2_dat两个次要数据文件。日志的逻辑文件名为student_log，此日志文件存储在c:mssqldatastudentlog.ldf中。CREATE DATABASE studentON PRIMARY(NAME = student1_dat, FILENAME = 'c:student1_dat.mdf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 15%), (NAME = student2_dat, FILENAME = 'c:student2_dat.ndf', SIZE =

12、10, MAXSIZE = 50, FILEGROWTH = 15%), FILEGROUP studentGroup1 ( NAME = student1Fi1_dat, FILENAME = 'c:student1Fildt.ndf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5) ,( NAME = student1Fi2_dat, FILENAME = 'c:student2Fildt.ndf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5), FILEGROUP studentGr

13、oup2(NAME = student2Fil_dat, FILENAME = 'c:SG2Fi1dt.ndf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5),(NAME = student2Fi2_dat, FILENAME = 'c:SG2Fi1dt.ndf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5)LOG ON (NAME = 'student_log', FILENAME = 'c:studentlog.ldf', SIZE = 5MB, MA

14、XSIZE = 25MB, FILEGROWTH = 5MB)执行结果如图1-6所示： 图1-6 使用Transact-SQL语句创建数据库二、查看、修改数据库属性在SQL Server2000 下查看和修改数据库有两种方法：1利用企业管理器查看、修改数据库属性（1）SQL Server2000 有许多选项可以改变数据库的行为。因此，在使用数据库之前，可以考虑其中一些选项的设置。 进入企业管理器，用鼠标右键单击数据库student，再单击“属性”，然后选择【选项】标签即可进入如图1-7所示student数据库的选项窗体。 图1-7 student数据库的选项窗体属性说明Ø 访问控制：

15、用于控制哪些用户能访问该数据库。Ø 只读：这个选项使数据库只能读取不能写入。Ø 模型：指定数据库的恢复模型类型。Ø ANSI NULL默认设置：指定将数据库列默认定义为NULL还是NOT NULL。Ø 递归触发器：启用触发器的递归触发。Ø 自动更新统计信息：指定在优化期间自动生成查询优化所需的过时统计信息。Ø 残缺页检测：指定可以检测不完整页。Ø 自动关闭：指定数据库资源被释放以及所有用户退出之后关闭数据库。Ø 自动收缩：指定数据库文件可以周期性自动收缩。Ø 自动创建统计信息：指定在优化期间自动生成优化查

16、询所需的任何缺少的统计信息。Ø 使用引用的标识符：指定SQL Server2000将强制执行关于引号的ANSI规则。Ø 级别：指定数据库兼容性级别。2 增加数据库大小如果在数据库中，用户需要不断的增加数据，就需要扩大数据库文件。点击【数据文件】或【事务日志】标签，可以得到如图1-8所示student数据库的窗体 图1-8 student数据库的数据文件窗体属性说明：Ø 文件自动增长：选中表示数据库自动，这时可以选择按兆增长还是按百分比增长。Ø 文件增长不受限制：意味着数据库没有最大限制，可以一直充满磁盘Ø 将文件增长限制为，为数据库选择一个最大

17、限制。Ø 分配空间：用户可以直接增加数据库文件大小Ø 扩大数据库的另外一个方法是增加辅助数据文件。3 加文件组生成一辅助文件后，可将其逻辑组织到文件组中，帮助管理磁盘空间分配。增加文件组的方法是：在文件组列中输入一个新的文件组名称，如“next”，单击“确定”，然后右击数据库，选择“属性”，选择【文件组】标签会看到增加的文件组。如图1-9所示：图1-9 student数据库的文件组窗体选中【选项】标签得到图1-7 所示界面：在Access选项组里，选择限制访问，原本保持灰白状态的两个选择按钮变为激活状态。单用户：表示当前只能有一个用户可以使用该数据库只读：表示该数据库在当前

18、只能读取数据而不能进行修改 缩小数据文件 SQL Server2000提供了缩小过于庞大的数据库的手段，以回收没有使用的数据空间，可以用手动的方法单独缩小某个数据文件，也可以缩小缩小整个文件组的长度。还可以设置数据库在达到一定大小之前自动执行缩小操作。 注意： 不能将数据库缩小到小于创建时的长度。如创建数据库文件时定义的长度是10MB，可以自动扩充到100MB。那么该数据库最多只能缩小到10MB。企业管理器完成缩小数据库的过程如下：² 展开服务器组，展开指定的服务器；² 从指定的服务器上展开数据库节点；² 选中要执行缩小操作的数据库；² 从快捷菜单中选择

19、 【所有任务】命令；² 从级联菜单中选择【收缩数据库】，会弹出图1-10所示窗体，选择执行数据库缩小操作的方式。利用该窗体可以输入数据库大小，并且可以定制任务，让SQL Server2000自动定期收缩数据库大小。 图1-10 收缩数据库窗体2使用Transact-SQL语句查看、修改数据库属性 查看数据库属性用户使用Transact-SQL语句查看数据库属性主要通过系统存储过程sp_dboption来完成操作，语法如下： sp_dboption dbname = database,optname = option_name,optname = option_name 说明：

20、6; dbname = database： 在其中设置指定选项的数据库名称Ø optname = option_name： 设置的选项名称，表示要查看或修改的选项，如果不指定该选项则返回该数据库所有当前值为on的选项Ø optname = option_name ：可以示ture、false、on或off 修改日志文件的最大值将student1_log日志文件的最大值更改为150M，SQL脚本程序如下：USE studentGOALTER DATABASE student MODIFY FILE(NAME=student1_log,MAXSIZE=150MB)GO修改日志文

21、件的初始值将student1_log日志文件的初始值由5M更改为20M，SQL脚本程序如下：USE studentGOALTER DATABASE studentMODIFY FILE(NAME=student1_log,SIZE=20MB)GO 查看数据库信息执行系统存储过程sp_helpdb,可以查看student数据库信息，并验证数据库是否已经修改成功。Sp_helpdb student 更改数据库SQL Server2000的数据文件可以自动扩充长度，所以数据库的大小也会自动增加。但是如果设置了最大文件长度，则数据库的扩充依然有必要进行的操作。修改数据库的大小实质上是修改数据文件和日志

22、文件的长度，或者增加、删除操作系统文件，这可以用下面的语法来实现。ALTER DATABASE database ADD FILE <filespec> 1nTO FILEGROUP filegroup_name |ADD LOG FILE <filespec> 1n |REMOVE FILE logical_file_name |ADD FILEGROUP filegroup_name |REMOVE FILEGROUP filegroup_name |MODIFY FILE <filespec> |MODIFY FILEGROUP filegroup_n

23、ame filegroup_property<filespec>:=(NAME = logical_file_name ,FILENAME = os_file_name ,SIZE = size ,MAXSIZE=max_size|UNLIMTED ,FILEGROWTH = grouwth_increment)说明： ADD FILE <filespec> 1nTO FILEGROUP filegroup_name：表示向指定的文件组里增加新的数据文件ADD LOG FILE <filespec> 1n：增加新的日志文件REMOVE FILE logica

24、l_file_name：删除某一个操作系统文件ADD FILEGROUP filegroup_name：增加一个文件组REMOVE FILEGROUP filegroup_name：删除某一个文件组MODIFY FILE <filespec>：修改某操作系统文件属性MODIFY FILEGROUP filegroup_name filegroup_property：修改某文件组的属性文件组的属性有三种：READONLY：只能读取该文件中的数据而不能修改READWRITE：既可以读取又可以修改该文件组中的数据DEFAULT：设置该文件组为默认文件组 注意： 如果操作系统文件中有任何数

25、据或数据对象存在，则该操作系统文件将无法被删除；一次只能修改一个文件属性，而且如果是修改文件的大小就只能增加文件长度；Primary文件组的属性不能修改为READINLY。例：更改数据库student时为student添加一个辅助文件，文件名为studentfz，存储在c: studentfz.ndf中，初始大小为3MB，最大值为50MB，增长量为3MB。USE studentALTER DATABASE studentADD FILE(NAME = studentfz,FILENAME='c:studentfz.ndf',SIZE=3MB,MAXSIZE=50,FILEGRO

26、WTH=3)向数据库student中添加一个含有2个文件的文件组USE studentGOALTER DATABASE studentADD FILEGROUP studentfg1GOALTER DATABASE studentADD FILE(NAME=studentfz1, FILENAME='C:studentfz1.ndf', SIZE =5MB, MAXSIZE=50MB, FILEGROWTH =5),(NAME=studentfz2, FILENAME='C:studentfz2.ndf', SIZE =5MB, MAXSIZE=50MB, FI

27、LEGROWTH =5) TO FILEGROUP studentfg1向student数据库中添加2个5MB的日志文件USE studentGOALTER DATABASE studentADD LOG FILE(NAME =studentlog2, FILENAME='c:studentlog2.ldf', SIZE =5MB, MAXSIZE = 10MB, FILEGROWTH =5),(NAME =studentlog3, FILENAME='c:studentlog3.ldf', SIZE =5MB, MAXSIZE = 10MB, FILEGROW

28、TH =5)删除数据库student中的一个文件studentfz.ndfUSE studentGOALTER DATABASE student REMOVE FILE studentfz删除student数据库USE studentDROP DATABASE student 缩小数据库可以使用Transact-SQL命令来完成数据库的缩小操作，语法如下：BDCC SHRINKDATABASE( database_name , target_percent ,NOTRUNCATE | TRUNCATEONLY) 说明：target_percent：表示当数据库被缩小后，还剩下的自由空间NOTR

29、UNCATE：被释放的文件空间依然保持在数据库文件的范围内。如果不设置该 选项，则被释放的空间将被操作系统回收TRUNCATEONLY：将所有未使用的数据空间释放并让操作系统回收，使用该关键字时，将忽略target_percent的限制。 例：使用DBCC SHRINGDATABASE缩小student数据库的大小，保留自由空间60MB，保留释放的文件空间 DBCC SHRINKDATABASE （student，60，NOTRUNCATE） GO使用DBCC命令来缩小某一个操作系统文件的长度，语法如下： DBCC SHRINKFILE （ file_name ,target_size | ,EMPTYFILE | NOTRUNCATE | TRUNCATEONLY ) 说明： target_size：将文件缩小到指定的长度，以MB为单位，如果不指定该选项，文件将尽可能进行缩小 EMPTYFILE：将指定文件上的数据全部转移到本文件组内其他文件上，以后的操作将不会再在该文件上增加数据下面的例子将student数据库的一个文件执行缩小操作。 USE student GO DBCC SHRINKFILE (studentfz1 , 15) 练习题练习1：创建数