第5 章数据库的创建与管理 本章主要介绍SQLServer中数据库的概念、数据库文件的类型、系统数据库,以及创建、 打开、修改和删除数据库的方法。 5.evr数据库概述 1 SQLSre 1.数据库引擎 5.1 MicrosoftSQLServerDatabaseEngine(数据库引擎)是用于存储、处理和保护数据的核 心服务。利用数据库引擎可控制访问权限并快速处理事务,从而满足企业内要求极高且需要 处理大量数据的应用需要。 使用数据库引擎创建用于联机事务处理或联机分析处理数据的关系数据库,包括创建用于 存储数据的表以及用于查看、管理和保护数据安全的数据库对象(如索引、视图和存储过程)。 数据库引擎的主要任务如下。 ①设计并创建数据库,以保存系统所需的关系表或XML文档。 ②实现系统访问和更改数据库中存储的数据,包括实现网站或使用数据的应用程序,以 及生成利用SQLServer工具和实用工具使用数据的过程。 ③为单位或客户部署将要实现的系统。 ④提供日常管理支持,以优化数据库的性能。 SQLServer中的数据库由表集合组成。这些表包含数据以及为支持数据操作而定义的 其他对象,如视图、索引、存储过程、用户定义函数和触发器。存储在数据库中的数据通常与特 定的主题或过程相关,如生产仓库的库存信息。表上有几种类型的控制(如约束、触发器、默认 值和自定义用户数据类型),用于保证数据的有效性。可以向表上添加完整性约束,以确保不 同表中的相关数据保持一致。表上可以有索引,利用索引能够快速找到行。 数据库还可以包含使用Transact-SQL或.NETFramework编程代码的过程对数据库中 数据执行操作。这些操作包括创建用于提供对表数据的自定义访问的视图,或创建用于对部 分行执行复杂计算的用户定义函数。 一个SQLServer实例可以支持多个数据库。每个数据库可以存储来自其他数据库的相 关数据或不相关数据。例如,SQLServer实例可以有一个数据库用于存储职员数据,另一个 数据库用于存储与产品相关的数据。或者一个数据库可以存储当前客户订单数据,而另一个 相关数据库可以存储用于年度报告的历史客户订单。 1.文件和文件组 5.2 SQLServer将数据库映射为一组操作系统文件。数据和日志信息保存在不同的文件中, 第5章数据库的创建与管理 而且每个文件仅在一个数据库中使用。为了便于分配和管理,可以将数据文件集合起来放到 文件组中,用于帮助完成数据布局和管理任务,例如备份和还原操作。 每个SQLServer数据库至少具有两个操作系统文件:一个数据文件和一个日志文件。 数据文件包含数据和对象,例如表、索引、存储过程和视图;日志文件包含恢复数据库中的所有 事务所需的信息。 1. 数据库文件 SQLServer数据库具有如下三种类型的文件。 (1)主数据文件 主数据文件包含数据库的启动信息,并指向数据库中的其他文件。用户数据和对象可存 储在此文件中,也可以存储在次要数据文件中。每个数据库都有一个主数据文件。主数据文 件的建议文件扩展名是.mdf。 (2)辅助数据文件 除主数据文件以外的所有其他数据文件都是辅助数据文件,又称次要数据文件。辅助数 据文件是可选的,由用户定义并用来存储用户数据。通过将每个文件放在不同的磁盘驱动器 上,辅助数据文件可用于将数据分散到多个磁盘上。辅助数据文件可以有0到多个。如果数 据库超过了单个Windows文件的最大容量,可以使用辅助数据文件,这样数据库就能继续增 长。辅助数据文件的建议文件扩展名是.f。 (3)事务日志文件 nd 事务日志文件保存用于恢复数据库的日志信息。每个数据库必须至少有一个日志文件, 也可以有多个日志文件。事务日志文件的建议扩展名是l.df。 在SQLServer中,数据库中所有文件的位置都记录在数据库的主文件和master数据 库中。 默认情况下,数据和事务日志被放在同一个驱动器上的同一个路径下。这是为处理单磁 盘系统而采用的方法。但是,在生产环境中,这可能不是最佳的方法。建议将数据和日志文件 放在不同的磁盘上。 2. 数据库逻辑和物理文件名称 SQLServer数据库文件有两个名称。 (1)逻辑文件名 逻辑文件名(logicalfile_name)是在所有Transact-SQL 语句中引用物理文件时所使用的名称。逻辑文件名必须符(_) 合SQLServer标识符规则,而且在数据库中的逻辑文件名中必须是 唯一的。它是由ALTERDATABASE 的NAME 参数设置的。 (2)物理文件名 物理文件名(osfile_name)是包括目录路径的物理文件名。它必须符合操作系统文件命名规则。它是由ALT(_) ERDATABASE 的FILENAME 参数设置的。 在默认SQLServer实例上创建的数据库的逻辑文件名和物理文件名示例如图5. 1所示, 该数据库有一个主数据库文件、两个辅助数据库文件和两个日志文件。 SQLServer数据库文件的逻辑文件名与物理文件名的主文件名可以一致,也可以不一 致,在图5.下面列出的是物理文件名。 1中上面列出的是数据库的逻辑文件名, 3. 数据文件页 r中数据存储的基本单位是页。为数据库中的数据文件(f或.f)分配的 SQLServe.mdnd 数据库技术及应用(SQLServer2022 版) 图5.数据库逻辑文件名与物理文件名示例 1 磁盘空间可以从逻辑上划分成页(从0到 n 连续编号)。磁盘I/O操作在页级执行。也就是 说,SQLServer是以页为单位读写数据的。 区是8个物理上连续的页的集合,用来有效地管理页。所有页都存储在区中。 (1)页 在SQLServer中,页的大小为8KB 。这意味着SQLServer数据库中每MB 有128 页。 每页的开头是96B 的标头,用于存储有关页的系统信息。此信息包括页码、页类型、页的可用 空间以及拥有该页的对象的分配单元ID 。 注意:日志文件不包含页,而是包含一系列日志记录。 (2)区 区是管理空间的基本单位。一个区是8个物理上连续的页(即64KB )。这意味着SQL Server数据库中每MB 有16 个区。 为了使空间分配更有效,SQLServer不会将所有区分配给包含少量数据的表。SQL Server有两种类型的区: ①统一区。由单个对象所有。区中的所有8页只能由所属对象使用。 ②混合区。最多可由8个对象共享。区中8页的每页可由不同的对象所有。 (3)文件页 SQLServer数据文件中的页按顺序编号,文件的首页以0开始。数据库中的每个文件都 有唯一的文件ID 号。若要唯一标识数据库中的页,需要同时使用文件ID 和页码。图5. 2显 示了包含4MB 主数据文件和1MB 辅助数据文件的数据库中的页码。 4. 文件大小 SQLServer文件可以从最初指定的大小开始自动增长。在定义文件时,可以指定一个特 图5.2数据文件页示例 第5章数据库的创建与管理 定的增量。每次填充文件时,其大小均按此增量来 增长。如果文件组中有多个文件,则它们在所有文 件被填满之前不会自动增长。填满后,这些文件会 循环增长。 每个文件还可以指定一个最大容量。如果没 有指定最大容量,文件可以一直增长到用完磁盘上 的所有可用空间。 5. 数据库文件组 为便于分配和管理,可以将数据库对象和文件 一起分成文件组。有以下两种类型的文件组。 (1)主要文件组 每个数据库有一个主要文件组(PRIMARY 文件组)。此文件组包含主数据文件和未放 入其他文件组的所有次要文件。所有系统表都被分配到主要文件组中。 (2)用户定义文件组 用户定义文件组是用户首次创建数据库或以后修改数据库时明确创建的任何文件组,用 于将数据文件集合起来,以便于管理、分配和放置数据。用户定义文件组是通过在CREATE DATABASE 或ALTERDATABASE 语句中使用FILEGROUP 关键字指定的任何文件组。 日志文件不包括在文件组内。日志空间与数据空间分开管理。例如,分别在三个磁盘驱 动器上创建三个文件Daanf、aanf和Dt3.d将它们分配给文件组fru t1.dDt2.daanf, gop1。然后 在文件组fgroup1上创建一个表。对表中数据的查询将分散到三个磁盘上,从而提高了性能。 一个文件不可以是多个文件组的成员。表、索引和大型对象数据可以与指定的文件组相 关联。在这种情况下,它们的所有页将被分配到该文件组,或者对表和索引进行分区。已分区 表和索引的数据被分割为单元,每个单元可以放置在数据库中的单独文件组中。 每个数据库中均有一个文件组被指定为默认文件组。如果创建表或索引时未指定文件 组,则将假定所有页都从默认文件组分配。不管何时,只能将一个文件组指定为默认文件组。 默认文件组中的文件必须足够大,能够容纳未分配给其他文件组的所有新对象。db_owner固 定数据库角色成员可以将默认文件组从一个文件组切换到另一个。如果没有指定默认文件 组,则将PRIMARY 文件组作为默认文件组。 PRIMARY 文件组是默认文件组,除非使用ALTERDATABASE 语句进行了更改。但 系统对象和表仍然分配给PRIMARY 文件组,而不是新的默认文件组。 5.3 事务日志 1. 每个SQLServer数据库都有一个事务日志,用于记录所有事务,以及每个事务对数据库 所做的修改。事务日志是任何数据库的关键组成部分。如果系统出现故障,它将成为最新数 据的唯一源,因此慎做删除或移动事务日志操作。 1. 事务日志支持的操作 事务日志支持以下操作。 (1)恢复个别的事务 如果应用程序发出ROLLBACK(事务回滚)语句,或者数据库引擎检测到错误,系统使用 日志记录回滚未完成事务所做的修改。 数据库技术及应用(SQLServer2022版) (2)SQLServer启动时恢复所有未完成的事务 当运行SQLServer的服务器发生故障时,数据库可能处于以下不正确的状态:还没有将 某些修改从缓存写入数据文件,在数据文件内有未完成事务所做的修改。当启动SQLServer 实例时,它对每个数据库执行恢复操作。前滚日志中记录的、可能尚未写入数据文件的每个修 改,在事务日志中找到的每个未完成的事务都将回滚,以确保数据库的完整性。 (3)将还原的数据库、文件、文件组或页前滚到故障点 在硬件丢失或磁盘故障影响到数据库文件后,可以将数据库还原到故障点。首先还原上 一次的完整备份和差异备份,然后将事务日志备份后续序列还原到故障点。当还原每个日志 备份时,数据库引擎重新应用日志中记录的所有修改,以前滚(RolForward)所有事务。当最 后的日志备份还原后,数据库引擎将使用日志信息回滚到该点未完成的所有事务。 (4)支持事务复制 日志读取器代理程序监视已为事务复制配置的每个数据库的事务日志,并将已设复制标 记的事务从事务日志复制到分发数据库中。 (5)支持备用服务器解决方案 备用服务器解决方案、数据库镜像和日志传送高度依赖于事务日志。在日志传送方案中, 主服务器将主数据库的活动事务日志发送到一个或多个目标服务器。每个辅助服务器将该日 志还原为其本地的辅助数据库。 在数据库镜像方案中,数据库(主体数据库)的每次更新都在独立的、完整的数据库(镜像 数据库)副本中立即重新生成。主体服务器实例立即将每条日志记录发送到镜像服务器实例, 镜像服务器实例将传入的日志记录应用于镜像数据库,从而将其继续前滚。 2.事务日志的特征 ①事务日志是作为数据库中的单独的文件或一组文件实现的。日志缓存与数据页缓存 分开管理,从而使数据库引擎内的编码更简单、更快速和更可靠。 ②日志记录和页的格式不必遵守数据页的格式。 ③事务日志可以在几个文件上实现。通过设置日志的FILEGROWTH值,可以将这些 文件定义为自动扩展。这样可减少事务日志内空间不足的可能性,同时减少管理开销。 ④重用日志文件中空间机制,速度快且对事务吞吐量影响最小。 1.数据库快照 5.4 数据库快照是SQLServer数据库(源数据库)的只读静态视图。创建快照时,每个数据库快 照在事务上与源数据库一致。多个快照可以位于一个源数据库中,并且可作为数据库始终驻留 在同一服务器实例上。当源数据库更新时,数据库快照也将更新。因此数据库快照存在的时间 越长,就越有可能用完其可用磁盘空间。在被数据库所有者显式删除前,快照始终存在。 快照可用于报表。另外,如果源数据库出现用户错误,还可将源数据库恢复到创建快照时 的状态。丢失的数据仅限于创建快照后数据库更新的数据。 5.系统数据库 2 SQLServer2022安装成功后,系统自动安装了4个系统数据库和2个示例数据库,下面 分别介绍。 第5章数据库的创建与管理 1.mtr数据库 master(a) 数(e) 据库记录SQLServer系统的所有系统级信息,包括实例范围的元数据(如登录 账户)、端点、链接服务器和系统配置设置。master数据库还记录所有其他数据库是否存在, 以及这些数据库文件的位置。另外,master也记录SQLServer的初始化信息。因此,如果 master数据库不可用,则SQLServer无法启动。在SQLServer中,系统对象不再存储在 master数据库中,而是存储在Resource数据库中。 表5.1列出了master数据库的数据和日志文件的初始配置值。对于SQLServer的不同 版本,这些文件的大小可能略有不同。 表5.1 master数据库的初始配置 文件逻辑名称物理名称文件增长 主数据文件master master.mdf 按10% 自动增长,直到磁盘已满 日志文件mastlomastloldf 按10% 自动增长,直到达到最大值2TB g. g 不能在master数据库中执行下列操作: ①添加文件或文件组。 ②更改排序规则。默认排序规则为服务器排序规则。 ③更改数据库所有者。master的所有者是sa。 ④创建全文目录或全文索引。 ⑤在数据库的系统表上创建触发器。 ⑥删除数据库。 ⑦从数据库中删除guest用户。 ⑧启用变更数据捕获。 ⑨参与数据库镜像。 ⑩删除主文件组、主数据文件或日志文件 。 ....重命名数据库或主文件组 。 .. ....将数据库设置为OFFLINE 。 .. ....将数据库或主文件组设置为READ_ .. ONLY 。 使用master数据库时的建议 : ①始终有一个master数据库的当前备份可用。 ②执行下列操作后,尽快备份master数据库:创建、修改或删除数据库;更改服务器或数 据库的配置值;修改或添加登录账户。 ③不要在master中创建用户对象。否则,必须更频繁地备份master。 ④不要针对master数据库将TRUSTWORTHY 选项设置为ON 。 2.dl数据库 model(m) 数(e) 据库是在SQLS(s) erver实例上创建的所有数据库的模板。因为每次启动SQL Server时都会创建tempdb 数据库,所以model数据库必须始终存在于SQLServer系统中。 当发出CREATEDATABASE 语句时,将通过复制model数据库中的内容来创建数据库 的第一部分,然后用空页填充新数据库的剩余部分。 如果修改model数据库,之后创建的所有数据库都将继承这些修改。 表5.2列出了model数据库的数据和日志文件的初始配置值。对于SQLServer的不同 版本,这些文件的大小可能略有不同。 数据库技术及应用(SQLServer2022版) 表5.l数据库的初始配置 2 mode 文件逻辑名称物理名称文件增长 主数据文件modeldev model.mdf 按10%自动增长,直到磁盘充满为止 日志文件modelog modelog.ldf 按10%自动增长,直到达到最大值2TB 不能在model数据库中执行下列操作: ①添加文件或文件组。 ②更改排序规则。默认排序规则为服务器排序规则。 ③更改数据库所有者。model的所有者是sa。 ④删除数据库。 ⑤从数据库中删除guest用户。 ⑥启用变更数据捕获。 ⑦参与数据库镜像。 ⑧删除主文件组、主数据文件或日志文件。 ⑨重命名数据库或主文件组。 ⑩将数据库设置为OFFLINE 。 ....将数据库或主文件组设置为READ_ .. ONLY 。 ....使用WITHENCRYPTION选项创建过程、 .. 视图或触发器。加密密钥与在其中创建 对象的数据库绑定在一起。在model数据库中创建的加密对象只能用于model中。 3.sdb数据库 msdb(m) 数据库由SQLServer代理用来计划警报和作业。 表5.3列出了msdb数据库的数据和日志文件的初始配置值。对于SQLServer的不同版 本,这些文件的大小可能略有不同。 表5.mdb数据库的初始配置 3s msdb文件逻辑名称物理名称文件增长 主数据MSDBData MSDBData.mdf 按256KB自动增长,直到磁盘已满 LoMSDBLoMSDBLoldf 按256KB自动增长,直到达到最大值2TB g. 不能在msdb数据库中执行下列操作 。 g g ①更改排序规则。默认排序规则为服务器排序规则。 ②删除数据库。 ③从数据库中删除guest用户。 ④启用变更数据捕获。 ⑤参与数据库镜像。 ⑥删除主文件组、主数据文件或日志文件。 ⑦重命名数据库或主文件组。 ⑧将数据库设置为OFFLINE 。 ⑨将主文件组设置为READ_ONLY 。 4.tempdb数据库 tempdb系统数据库是连接到SQLServer实例的所有用户都可用的全局资源,它保存所 有临时表和临时存储过程。另外,它还用来满足所有其他临时存储要求,例如存储SQL Server生成的工作表。 第5章数据库的创建与管理 每次启动SQLServer时,都要重新创建tempdb,以便在系统启动时,该数据库总是空的。 在断开连接时会自动删除临时表和存储过程,并且在系统关闭后没有活动连接。 Tempdb 用于保存以下内容。 ①显式创建的临时对象,例如表、存储过程、表变量或游标。 ②所有版本的更新记录(如果启用了快照隔离) 。 ③SQLServerDatabaseEngine创建的内部工作表 。 ④创建或重新生成索引时,临时排序的结果(如果指定了SORT_IN_TEMPDB )。 tempdb 中的操作是最小日志记录操作,这样可以回滚事务,但tempdb 不能备份或还原。 表5.4列出了tempdb 数据库的数据和日志文件的初始配置值。对于SQLServer的不同 版本,这些文件的大小可能略有不同。 表5.mpdb 数据库的初始配置 4 te 文件逻辑名称物理名称文件增长 主数据文件tempdev tempdb.mdf 按10% 自动增长,直到磁盘已满 日志文件tlotloldf 按10% 自动增长,直到达到最大值2TB empg. empg tempdb 的大小可以影响系统性能。如果tempdb 的尺寸太小,则每次启动SQLServer 时,系统可能忙于处理数据库的自动增长,而不能满足工作负荷要求。可以通过增加tempdb 的尺寸来避免此开销。 不能对tempdb 数据库执行以下操作。 ①添加文件组。 ②备份或还原数据库。 ③更改排序规则。默认排序规则为服务器排序规则。 ④更改数据库所有者。tempdb 的所有者是sa。 ⑤删除数据库。 ⑥从数据库中删除guest用户。 ⑦启用变更数据捕获。 ⑧参与数据库镜像。 ⑨删除主文件组、主数据文件或日志文件。 ⑩重命名数据库或主文件组 。 ....运行DBCCCHECKALLOC 。 .. ....运行DBCCCHECKCATALOG 。 .. ....将数据库设置为OFFLINE 。 .. ....将数据库或主文件组设置为READ_ONLY 。 .. 5.创建数据库 3 若要创建数据库,必须确定数据库的名称、所有者、大小以及存储该数据库的文件和文件 组。其中,所有者是创建数据库的用户。 在创建数据库之前,用户必须至少拥有CREATEDATABASE 、CREATEANYDATABASE 或ALTERANYDATABASE 权限(第12 章介绍)。 一个SQLServer实例最多可以创建32767 个数据库。数据库名称必须遵循为标识符指 数据库技术及应用(SQLServer2022 版) 定的规则。 model数据库中的所有用户定义对象都将复制到所有新创建的数据库中。可以向model 数据库中添加任何对象(如表、视图、存储过程和数据类型), 以将这些对象包含到所有新创建 的数据库中。 在SQLServer中,可以通过SQLServerManagementStudio的图形工具或Transact- SQL 语句创建数据库,下面将分别介绍。 数据库案例背景: 大学生作为思想活跃的群体,如何有效地加强其思想政治教育,是广大教育工作者面 临的重要课题。红色影视作品作为思想政治教育的天然“教科书”,其蕴含的爱国主义精 神、民族精神和革命精神,为高校思政教育提供了丰富的素材和案例。通过深入挖掘红色 影视资源的理论内涵和时代价值,可以激励大学生立大志、明大德、成大才、担大任,努力成 为堪当民族复兴重任的时代新人。 5.1 使用图形工具创建数据库 3. 【例5.创建一个红色影视作品数据库, edMovie,该数据库贯穿本书的后 续 1】名称为R 章节。在 SQLServerManagementStudio中创建红色影视作品数据库过程如下。 ①启动MicrosoftSQLServerManagementStudio。 ②在“对象资源管理器”的树状结构中右击“数据库”,在出现的快捷菜单中选择“新建数 据库”菜单项,3所示。 如图5. 图5.“新建数据库”菜单项 3 第5章数据库的创建与管理 ③出现“新建数据库” 如图5. 窗口,4所示。 图5.“新建数据库”窗口 4 在图5.常规” 数据库名称” RedMovie”在“逻辑名称”下输入 4所示的“ 选项页中“ 处输入“, 主数据库文件的逻辑名称为“RedMovie_data”,在“初始大小”下可以设置主数据库文件的大 小,单击“ 后的【 . 按钮,5所示的“ 对话框。 自动增长”】出现图5.更改自动增长” 图5.“更改自动增长”对话框 5