SQL项目3-2数据库的创建与管理.ppt

项目3-2 数据库的创建和管理 3-2.1 数据库简介 3-2.2 估算数据库的空间需求 3-2.3 数据库的创建 3-2.4 数据库的管理 3-2.5 设置数据库选项 1 v目标： u1. 掌握创建数据库的两种方法，理解数据库 的逻辑文件名和物理文件名的含义； u2. 掌握数据库管理的常用操作：查看数据库 属性、修改数据库大小、收缩数据库、数据 库的更名和删除； u3. 掌握设置数据库选项的常用操作。 项目3-2 数据库的创建和管理 2 3-2.1 数据库简介 v数据库创建的过程就是数据库逻辑设计的物理实现过程。如果能根据 ER模型并结合单位的实际情况创建出一个性能优异、结构合理并具 有良好扩充性能的数据库，将会为后在数据库基础上开发应用程序奠 定坚实的基础，这是数据库管理最重要、最核心的问题。 v数据库的创建主要包括数据库及其表、索引、视图、存储过程等对象 的创建。 v创建数据库时，系统会建立数据库的存储结构。 v数据库的存储结构分为逻辑存储结构和物理存储结构两种。 v数据库的逻辑存储结构指的是数据库是由哪些性质的信息所组成 。实 际上，SQL Server的数据库是由诸如表、视图、索引等各种不同的数 据库对象所组成。 3 数据库的物理存储结构是讨论数据库文件是如何在磁盘上存储的，数 据库在磁盘上是以文件为单位存储的，由数据库文件和事务日志文件 组成，一个数据库至少应该包含一个数据文件和一个事务日志文件。 1主数据文件（Primary Database File） v一个数据库可以有一个或多个数据文件，一个数据文件只能属于一个 数据库。当有多个数据文件时，有一个文件被定义为主数据文件（简 称为主文件），其扩展名为mdf。 v主数据文件用来存储数据库的启动信息以及部分或者全部数据，是所 有数据库文件的起点，包含指向其它数据库文件的指针。一个数据库 只能有一个主数据文件。 2次数据文件（Secondary Database File） v用于存储主数据文件中未存储的剩余数据和数据库对象，一个数据库 可以没有次数据文件，但也可以同时拥有多个次数据文件。 v次数据文件的扩展名为ndf（简称为次文件）。 4 3-2.1.1 数据库的存储结构 1.盘区和页面 在创建数据库对象时,SQL Server会使用盘区和页面这种特殊的数据 结构给数据库对象分配空间。 v在SQL Server中，数据存储的基本单位是页。页的大小是8KB，这样数 据库上每MB是128页。每页的开始部分是96B的页首，用于存放系统信 息，如页的类型、页的可用空间、占据该页的数据库对象的对象标识 等。页有数据页、索引页、文本/图象页等8种。 盘区（区域）是连续8个页，可防止数据产生碎片，使SQL Server易于 寻找。每个盘区只能包含一个数据库对象。盘区是表和索引分配空间 的基本单位。盘区是表和索引分配空间的基本单位。 一个数据库是由文件组成的，文件是由盘区组成的，盘区是由页面组 成的。 5 2. 数据库的存储组织 数据库 数据文件 .mdf 或.ndf 日志文件 .ldf 表、索引盘区（8个连续页面） 数据页（8KB） 每行大小8060B 图5-1 数据库的存储组织 6 注意： v所有的数据库都有唯一的主数据文件和一个或多个事务日 志文件，可能还有次数据文件； v数据行存放在数据页中，但数据页只能存放除text、ntext 和image类型数据外的所有数据， text、ntext和image类 型数据存储在单独的页中，行不能跨页存储，页中每一行 最多包含的数据量是8060B； v数据存放在连续的页中； v日志文件不包含页，而是包含一系列日志记录； v所有新建的数据库都是model数据库的副本。 7 3.文件组 v文件组是文件的集合。对文件进行分组，便于进行数据的 管理和分配磁盘空间。例如有三个数据文件 data1.ndf,data2.ndf,data3.ndf,分别位于不同的磁盘上，将 这三个文件指派到文件组filegroup1中。假设在文件组 filegroup1上创建一个表，对表中数据的查询将会分散到三 个磁盘上，从而提高系统的查询性能。 v文件和文件组的设计规则: 文件和文件组不能由一个以上的数据库使用; 文件只能是一个文件组的成员; 事务日志文件不属于任何一个文件组。 数据和事务日志不能属于同一文件或文件组。数据文件 和日志文件总是分开存放的。 8 主文件组中包含了所有的系统表，当建立数据库时， 主文件组包括主数据库文件和未指定组的其他文件。 用户定义文件组中可以指定一个缺省文件组，那么在 创建数据库对象时如果没有指定将其放在哪一个文件 组中，就会将它放在缺省文件组中。如果没有指定缺 省文件组，则主文件组为缺省文件组。 3-2.1.2 事务日志文件 vSQL创建数据库的时候,会同时创建事务日志文件。 v事务日志文件存储数据库的更新情况等事务日志信息 ， 当数据库损坏时，管理员使用事务日志恢复数据库。 v每一个数据库至少必须拥有一个事务日志文件，而且允 许拥有多个日志文件。事务日志文件的扩展名为ldf。 9 1. 事务日志文件和数据文件必须分开存放，优点如下： 事务日志文件可以单独备份； 有可能从服务器失效的事件中将服务器恢复到最近的状态； 事务日志不会抢占数据库的空间； 可以很容易地监测到事务日志的空间； 在向数据文件和事务日志文件写入数据时会产生较少的冲突， 这有利于提高SQL Server的性能。 2. 事务日志的工作过程 在SQL Server中，事务是一次完成的操作的集合。虽然一个事 务中可能包含了很多SQL语句，但在处理上就象他们是同一个 操作似的。 为了维护数据的完整性，事务必须彻底完成或者根本不执行。 如果一个事务只是部分地被执行，并作用于数据库，那么数据 库将可能被损坏或数据的一致性遭到破坏。 10 vSQL Server使用数据库的事务日志来防止没有完成的事务破坏 数据。 v事务日志的工作过程： 应用程序发出一个修改数据库中的对象的事务； 当这个事务开始时，事务日志会记录一个事务开始标志，并将被 影响的数据页从磁盘读入缓冲区； 事务中每个数据更改语句都被记录在日志文件中，日志文件将记 录一个提交事务的标记。每一个事务都会以这种方式记录在事务 日志中并被立即写到硬盘上。 在缓冲区中修改相应的数据。这些数据一直在缓冲区中，在检查 点进程发生时，检查点进程把所有修改过的数据页写到数据库中 ，并在事务日志中写入一个检查点标志，这个标志用于在数据库 恢复过程中确定事务的起点和终点。以及哪些事务已经作用于数 据库 了。 11 随着数据库数据的不断变化，事务日志文件不断增大。因此，必 须把它们备份出来，为更多的事务提供空间。备份时，事务日志 文件会被截断。 事务日志文件包含有在系统故障下恢复数据库需要的所有信息。 一般来说，事务日志文件的初始大小是以数据文件大小的10%到 25%为起点的，根据数据增长的情况和修改的频率进行调整。 关于文件名： vSQL Server 2005的文件拥有两个名称，即逻辑文件名和 物理文件名。当使用Transact-SQL命令语句访问某一个 文件时，必须使用该文件的逻辑名 。 v物理文件名是文件实际存储在磁盘上的文件名，而且可包 含完整的磁盘目录路径。 回到首页回到首页 12 3-2.2 估算数据库的空间需求 v作为数据库管理员，主要任务之一就是创建数据库，并且 需要为每个文件指定容量。必须尽可能准确地估算数据库 容量，以免浪费磁盘空间资源或者因估计不足造成数据库 的空间不够。 v许多因素会影响数据库最终的大小，在估算数据库容量时 要考虑如下因素： 每行记录的大小； 记录数量； 表的数量； 索引的数量及索引大小； 数据库对象的数量和大小； 事务日志的大小； 数据库的计划增加量； 回到首页回到首页 13 3-2.3 创建数据库 创建数据库的两种方法： 1.使用SQL Server Management Studio创建数据库 2.使用Transact-SQL语言创建数据库 n创建数据库的过程实际上就是为数据库设计名称、设 计所占用的存储空间和存放文件位置以及相关特性的 过程。 n新建数据库的信息放在系统数据库master中，属于系 统级信息。 n建议：创建新数据库时，数据文件逻辑名加入 ”_data”后缀，形如：jwgl_data;事务日志文件逻 辑名加入”_log”后缀，形如：jwgl_log 。 14 3-2.3.1 创建数据库的注意事项: 创建数据库需要一定许可，在默认情况下，只有系统管 理员和被赋予权限的用户可以创建数据库。数据库被创 建后，创建数据库的用户自动成为该数据库的所有者。 给数据库指定的名字遵循SQL Server 命名规范。 所有的新数据库都是系统样本数据库model的副本。 单个数据库可以存储在单个文件上，也可以跨越多个文 件存储。 5. 数据库的大小可以被增大或者收缩。 6. 当新的数据库创建时，SQL Server自动更新master系 统数据库中的“sysdatabases”系统表。 7. 一台服务器上最多可能创建32767个数据库。 15 3-2.3.2 使用T-SQL语言创建数据库 v说明：在Transact-SQL语言的命令格式中， 用 括起来的内容表示是可选的； ，n表示重复前面的内容； 用括起来表示在实际编写语句时，用相应的内容替代 ； 用 括起来表示是必选的； 类似A|B的格式，表示A和B只能选择一个，不能同时都选 。 v1. 创建数据库的SQL语句的语法格式: CREATE DATABASE database_name ON PRIMARY ，n ， ，n LOG ON ，n 16 v其中： = NAME=logical_file_name， FILENAME=os_file_name ，SIZE=size ，MAXSIZE=max_size|UNLIMITED ，FILEGROWTH=growth_increment ，n = FILEGROUP filegroup_name ，n 各参数的含义（如下）： vdatabase_name：新数据库的名称。数据库名称在服务器中必须唯一， 最长为128个字符，并且要符合标识符的命名规则。 vON：指定存放数据库的数据文件信息。列表用于定义主文件 组的数据文件，列表用于定义用户文件组及其中的文件。 17 vPRIMARY：用于指定主文件组中的文件。主文件组的第 一个由指定的文件是主文件。如果不指定 PRIMARY关键字，则在命令中列出的第一个文件将被默 认为主文件。 vLOG ON：指明事务日志文件的明确定义。如果没有本选 项，则系统会自动产生一个文件名前缀与数据库名相同， 容量为所有数据库文件大小1/4的事务日志文件。 vNAME：指定数据库文件的逻辑名称。 vFILENAME：指定数据库文件的物理名称，即数据库所在 文件的操作系统文件名称和路径，该操作系统文件名和 NAME的逻辑名称一一对应。 18 vSIZE：指定数据库的初始容量大小。如果没有指定主文 件的大小，则SQL Server默认其与模板数据库中的主文 件大小一致，其它数据库文件和事务日志文件则默认为 1MB。指定大小的数字size可以使用KB、MB、GB和TB 后缀，默认的后缀为MB。Size最小值为1MB，默认值为 1MB。 vMAXSIZE：指定操作系统文件可以增长到的最大尺寸。 如果没有指定，则文件可以不断增长直到充满磁盘。 vUNLIMITED：指定文件可以增长到磁盘变满为止。 vFILEGROWTH：指定文件每次增加容量的大小，当指定 数据为0时，表示文件不增长。文件的增长有两种方式： 以指定的MB数量增长和按百分比增长。默认后缀为MB。 如果没有指定FILEGROWTH，则默认值为10%。 19 v2. 几点说明 1）创建用户数据库后，要备份master数据库。 2）所有数据库都至少包含一个主文件组。所有系统表都分 配在主文件组中。数据库还可以包含用户定义的文件组 。 3）每个数据库都有一个所有者，可在数据库中执行某些特 殊的活动。数据库所有者是创建数据库的用户，也可以 使用sp_changedbowner更改数据库所有者。 4）创建数据库的权限默认地授予sysadmin和dbcreator固 定服务器角色的成员。 Sysadmin和securityadmin固定 服务器角色的成员可以将创建数据库的权限授予其他登 录，且必须显示授予，不能通过GRANT ALL语句授予。 20 v例5-1：创建一个 只含一个数据文件 和一个事务日志文 件的数据库，数据 库名为JWGL1。 主数据库文件逻辑 名称为jwgl1_data ，数据文件的操作 系统名称 jwgl1.mdf，数据 文件大小为5MB， 最大值500MB，数 据文件大小以10% 的增量增加。 日志逻辑文件名称 jwgl1_log.ldf， 事务日志的操作系 统名称jwgl1.ldf ，日志文件初始大 小为5MB，最大值 100MB，日志文件 以2MB增量增加。 程序清单为： CREATE DATABASE jwgl1 ON PRIMARY (NAME=jwgl1_data, FILENAME=c:Program FilesMicrosoft SQL ServerMSSQLDATAjwgl1.m df, SIZE=5MB, MAXSIZE=500MB, FILEGROWTH=10%) LOG ON (name=jwgl1_log, FILENAME=c:Program FilesMicrosoft SQL ServerMSSQLDATAjwgl1.ld f, SIZE=5MB, MAXSIZE=100MB, FILEGROWTH=2MB) GO 21 v例5-2：创建一个指定多个数据文件和日志文件的数据库 。该数据库名称为jwgl2，有2个10MB的数据文件和2个 10MB的事务日志文件。主文件是列表中的第一个文件， 并使用PRIMARY关键字显式指定。事务日志文件在LOG ON关 键字后指定。注意FILE_NAME选项中所用的文件扩展名：主 数据文件使用.mdf，次数据文件使用.ndf，事务文件使用 .ldf。 v程序清单： CREATE DATABASE jwgl2 ON PRIMARY (NAME = jwgl20_data, FILENAME =c:program filesmicrosoft sql servermssqldatajwgl20.mdf, SIZE = 10MB, MAXSIZE = 200, FILEGROWTH = 20), 22 ( NAME = jwgl21_data, FILENAME = c:program filesmicrosoft sql servermssqldatajwgl21.ndf, SIZE = 10MB, MAXSIZE = 200, FILEGROWTH = 20) LOG ON ( NAME = jwgl20_log, FILENAME =c:program filesmicrosoft sql servermssqldatajwgl20.ldf, SIZE = 10MB, MAXSIZE = 200, FILEGROWTH = 20), ( NAME = jwgl21_log, FILENAME =c:program filesmicrosoft sql servermssqldatajwgl21.ldf, SIZE = 10MB, MAXSIZE = 200, FILEGROWTH = 20) GO 23 例5-3创建一个包含2个文件组的数据库。该数据库名为 jwgl3，主文件组包含文件 jwgl30_data和 jwgl31_data。文 件组jwgl3_group包含文件jwgl32_data和 jwgl33_data。两 个文件组数据文件的FILEGROWTH 增量为 15%，数据文 件的初始大小为10 MB。事务日志文件的文件名为 jwgl3_log，FILEGROWTH 增量为 15%，日志文件的初始 大小为5 MB。 程序清单： CREATE DATABASE jwgl3 ON PRIMARY ( NAME = jwgl30_data, FILENAME = c:program filesmicrosoft sql servermssqldatajwgl30.mdf, SIZE = 10MB, 24 MAXSIZE = 50MB, FILEGROWTH = 15% ), ( NAME = jwgl31_data, FILENAME = c:program filesmicrosoft sql servermssqldatajwgl31.ndf, SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 15% ), FILEGROUP jwgl3_Group ( NAME = jwgl32_data, FILENAME = c:program filesmicrosoft sql servermssqldatajwgl32.ndf, SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 15% ), 25 ( NAME = jwgl33_data, FILENAME =c:program filesmicrosoft sql servermssqldatajwgl33.ndf, SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 15% ) LOG ON ( NAME = jwgl3_log, FILENAME =c:program filesmicrosoft sql servermssqldatajwgl3.ldf, SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 15% ) GO 26 3-2.3.3 使用SQL Server Management Studio创建数据 库 v使用SQL Server Management Studio创建数据库比使用T-SQL语句更容 易。 v具体步骤如下: 1）进入SQL Server Management Studio; 2）右击对象资源管理器的”数据库”项，在快捷菜单中选择“新建数据库” ，将打开如图5-2所示的数据库属性窗口； 3）该窗口有三个选择页：常规、选项、文件组； 4）分别进行相关设置； 5）点击“确定”。 27 3-2.3.4 由已有库生成创建数据库的脚本 v由已建数据库JWGL生成该库的SQL脚本的步骤: （1）选择数据库JWGL，按右键，选择“编写数据库教本 为(S)” CREATE(C)； （2）选择数据库脚本文件生成的目的地：“新查询编辑窗 口(N)”、 “文件(F)”或“剪贴板(C)”； （3）单击“文件(F)”，便会产生一个“选择文件”的对话窗口 ，在其中输入文件名，单击“保存”按钮。 v如要验证脚本的正确性，可进入查询编辑器，调入该脚本 程序执行即可。 回到首页回到首页 28 3-2.4 数据库的管理 3-2.4.1 数据库属性的查看 1.用T-SQL语言查看数据库的属性 v使用系统存储过程可以查看数据库的属性。常用的存储过程有 sp_helpdb，sp_spaceused，sp_filehelp，sp_helpfilegroup。 v查看某个数据库或所有数据库的信息： EXEC sp_helpdb jwgl1 v查看有关数据库中所占用空间的信息： EXEC sp_spaceused v显示有关数据库中文件组的信息： EXEC sp_helpfilegroup v 显示数据库中文件的信息： EXEC sp_helpfile jwgl1_data 2. 用SQL Server Management Studio查看数据库的属性 v进入SQL Server Management Studio，右击数据库，选择“属性”选项即 可查看数据库相关信息。 29 3-2.4.2 修改数据库大小 用T-SQL语言进行数据库大小的设定 例5-4:修改数据库JWGL1日志文件的最大值，由 100MB改为80MB。 程序清单： USE jwgl1 GO ALTER DATABASE jwgl1 MODIFY FILE(NAME=jwgl1_log,MAXSIZE=80MB) 30 例5-5：修改jwgl1_data数据文件的初始值，由5MB改为 10MB。 程序清单： USE jwgl1 GO ALTER DATABASE jwgl1 MODIFY FILE(NAME= jwgl1_data, SIZE=10MB) v应该注意，对于数据库数据文件和事务日志文件初始空间大小 的修改，新指定的空间大小值不能小于当前文件空间大小值。 2. 用SQL Server Management Studio修改数据库属性 v进入SQL Server Management Studio，展开树形结构的“数据库 ”节点，选中要修改的数据库JWGL1，按右键，在系统弹出快捷 菜单上点击“属性”，进入JWGL1 数据库的属性窗口，可以根据 需要进入不同的选项卡修改数据库相应的信息。 31 3-2.4.3 数据库的收缩 vSQL允许收缩数据库中的每个文件以删除未使用的页。数据和事务日 志文件都可以收缩。 v数据库文件可以单独地进行手工收缩，也可以设置为按给定的时间间 隔进行自动收缩。 v注意：不能将整个数据库收缩到比原始大小还要小。 v缩小数据库有两种方法： 1使用SQL Server Management Studio可以缩小数据库(教材P91）。 2使用Transact-SQL语言缩小数据库。 （1）使用DBCC SHRINKDATABASE命令收缩指定数据库中的数据文 件。 DBCC SHRINKDATABASE (database_name,target_percent,NOTRUNCATE |TRUNCATEONLY) 32 database_name：要收缩的数据库名称。 Target_percent：当数据库收缩后，数据库文件中剩余可用空间的 百分比。 NOTRUNCATE：被释放的文件空间依然保持在数据库文件中。如 果未指定，将所释放的文件空间被操作系统回收。 TRUNCATEONLY：将数据文件中未使用的空间释放给操作系统 ，并将文件收缩到上一次所分配的大小。使用 TRUNCATEONLY 时，将忽略 target_percent的限制。 将数据库设为自动收缩 使用ALTER DATABASE语句可以将数据库设为自动收缩。设定数 据库是否自动收缩的SQL 语句的语法形式： ALTER DATABASE database_name SET AUTO_SHRINK on/off on ：数据库设为自动收缩，off ：数据库设为不自动收缩。 33 【例5-7】：将数据库JWGL1的收缩设为自动收缩，SQL脚本程序如下： ALTER DATABASE jwgl1 SET AUTO_SHRINK on 3-2.4.4 数据库的更名 v一般情况下不要做数据库的改名。如要更名，需将数据库属性“选项”中 的“限制访问”项设为“single_user”，更名后再将“限制访问”项改回来。 v两种给数据库更名的方法： （1）进入SQL Server Management Studio，展开对象资源管理器，右击 要更名的数据库，选择“重命名”。 （2）数据库的更名的SQL 语句的语法形式： sp_renamedb old_name , new_name 其中： old_name: 是数据库的当前名称; new_name: 是数据库的新名称。 【例5-8】：将数据库JWGL1更名为JWXT，SQL脚本程序如下： sp_renamedb jwgl1,jwxt 34 3-2.4.5 删除数据库 v1利用SQL Server Management Studio删除数据库(教材P92 ） v2利用Drop语句删除数据库 Drop database database_name,n v说明：当数据库处于以下状态时不能被删除：数据库正在使 用，数据库正在恢复，系统数据库master、tempdb、model、 msdb。 例5-9 使用DROP DATABASE 命令将数据库JWXT删除。 DROP database jwxt 回到首页回到首页 35 3-2.5 设置数据库的选项 v数据库选项就是数据库的属性。这些选项将决定数据库的不同部分如何 工作，可以使用SQLSQL Server Management Studio或者“sp_dboption” 存储过程来修改数据库选项