数据库应用基础（SQL Server 2019）（AIGC赋能版） 课件 项目2 创建和管理数据库

项目2创建和管理数据库《数据库应用基础（SQLServer2019）》项目目标理解SQLServer数据库的组成掌握创建数据库的方法掌握修改数据库的方法掌握分离和附加数据库的方法掌握备份和还原数据库的方法本章目录01任务2.1认识SQLServer数据库02任务2.2创建数据库03任务2.3修改数据库04任务2.4分离和附加数据库05任务2.5备份和还原数据库认识SQLServer数据库2.1任务2.1认识SQLServer数据库任务目标理解SQLServer数据库的组成理解SQLServer数据库文件和文件组理解SQLServer系统数据库理解SQLServer数据库的各种状态理解SQLServer数据库文件的各种状态2.1.1SQLServer数据库概述数据库：存放数据库对象的容器，是表和其他对象（如视图、存储过程）的集合。表：存储结构化数据，由行（记录）和列（字段）组成。数据控制：通过约束、触发器、默认值等保证数据有效性。架构：数据库中的对象所有权组，包含表、视图等对象。权限管理：通过登录名、数据库用户和角色进行权限控制。管理工具：主要使用SQLServerManagementStudio(SSMS)。数据库分类：系统数据库：存储SQLServer系统级信息。用户数据库：用户创建，用于存储应用数据。2.1.2数据库文件与文件组（1/2）数据库的物理构成：至少包含两个操作系统文件。数据文件:存放数据和对象（表、索引等）。日志文件:存放用于恢复数据库的事务日志信息。数据文件类型:主要数据文件(.mdf):包含数据库启动信息，指向其他文件。每个数据库有且仅有一个主要数据文件。次要数据文件(.ndf):可选，用于存储用户数据。可将数据分散到不同磁盘。事务日志文件(.ldf):保存事务日志信息。每个数据库至少有一个。建议：将数据文件和日志文件放在不同的磁盘上。2.1.2数据库文件与文件组（2/2）文件名称逻辑文件名：在Transact-SQL语句中引用文件时使用的名称。操作系统文件名：包含路径的物理文件名称。文件大小文件可以自动增长。可以指定增量和最大大小。文件组主要文件组(PRIMARY)：包含主要数据文件和未放入其他文件组的次要文件。所有系统表都在此。用户定义文件组：用户创建，用于将数据文件集合起来，便于管理和分配。默认文件组：容纳未指定文件组的对象。PRIMARY是初始的默认文件组。2.1.3SQLServer系统数据库master数据库记录SQLServer实例的所有系统级信息（登录账户、配置、其他数据库位置等）。不可用则SQLServer无法启动。model数据库用作创建所有新数据库的模板。msdb数据库被SQLServer代理用于计划警报和作业，也用于备份还原历史等。tempdb数据库用于保存临时对象（临时表、表变量等）和中间结果。每次启动SQLServer时都会重新创建。Resource数据库只读数据库，包含所有系统对象。物理文件名为mssqlsystemresource.mdf/ldf。2.1.4数据库状态概述数据库总是处于一个特定状态中。主要状态ONLINE(在线)：数据库可用。OFFLINE(离线)：数据库不可用，通常由用户显式操作。RESTORING(还原)：正在还原文件，数据库不可用。RECOVERING(恢复)：正在恢复数据库，是临时状态。SUSPECT(可疑)：主文件组可能损坏，数据库不可用，需用户干预。EMERGENCY(紧急)：用于故障排除，单用户只读模式，仅sysadmin角色可访问。2.1.4数据库状态概述数据库总是处于一个特定状态中。主要状态ONLINE(在线)：数据库可用。OFFLINE(离线)：数据库不可用，通常由用户显式操作。RESTORING(还原)：正在还原文件，数据库不可用。RECOVERING(恢复)：正在恢复数据库，是临时状态。SUSPECT(可疑)：主文件组可能损坏，数据库不可用，需用户干预。EMERGENCY(紧急)：用于故障排除，单用户只读模式，仅sysadmin角色可访问。2.1.5数据库文件状态概述数据库文件的状态独立于数据库状态。主要状态ONLINE(在线)：文件可用于所有操作。OFFLINE(离线)：文件不可访问。RESTORING(还原)：正在还原文件。SUSPECT(可疑)：文件恢复失败。DEFUNCT(失效)：文件被删除。重要原则文件组中所有文件都必须联机，该文件组才可用。创建数据库2.2任务2.2创建数据库任务目标掌握使用SSMS创建数据库的方法掌握使用SQL语句创建数据库的方法创建前提需要拥有CREATEDATABASE权限。2.2.1使用SSMS创建数据库（1/2）SSMS优点图形化操作，简单直观。创建步骤在“对象资源管理器”中右键单击“数据库”。选择“新建数据库”。在对话框中输入数据库名称。配置文件和文件组（可选）：“常规”页：修改数据文件和日志文件的逻辑名称、初始大小、自动增长和路径。“文件组”页：添加新的文件组。“选项”页：修改排序规则、恢复模式等。单击“确定”完成创建。2.2.1使用SSMS创建数据库（2/2）例2.1：创建“人力资源”数据库，所有属性使用默认值。默认文件设置：主数据文件：逻辑名称：人力资源物理文件名：人力资源.mdf文件组：PRIMARY初始大小：8MB，增量：64MB，无限制增长日志文件：逻辑名称：人力资源_log物理文件名：人力资源_log.ldf初始大小：8MB，增量：64MB，无限制增长2.2.2使用SQL语句创建数据库（1/4）基本语法格式CREATEDATABASEdb_nameON[PRIMARY]<filespec>[,...n][LOGON<filespec>[,...n]][COLLATEcollation_name]其中，文件规范<filespec>::=(NAME=logical_file_name,FILENAME='os_file_name',SIZE=size[KB|MB|GB|TB],MAXSIZE={max_size|UNLIMITED},FILEGROWTH=growth_increment[KB|MB|GB|%])db_name要创建的数据库名称（必须唯一，且符合标识符规则）ON若显式定义数据文件时，则不可省略；若完全使用默认设置，则可省略PRIMARY指定主文件组（默认），可省略NAME文件的逻辑名称（在SQLServer内部使用）FILENAME操作系统中实际的物理文件路径（需有写权限）SIZE初始大小，默认单位是MBMAXSIZE文件可增长到的最大大小；UNLIMITED

表示无限制（日志文件默认为2TB）FILEGROWTH自动增长增量，可以是固定大小或百分比（如

10%）;默认为64MB，0表示不允许增加COLLATE指定数据库的排序规则（如

Chinese_PRC_CI_AS）LOGON显式定义事务日志文件（若省略，SQLServer自动生成）2.2.2使用SQL语句创建数据库（2/4）例2.2：创建“员工”数据库IFDB_ID('员工')ISNULLCREATEDATABASE员工ON(NAME=Employee_dat,FILENAME='D:\SQLServer2019\Data\empdat.mdf',SIZE=10MB,MAXSIZE=50MB,FILEGROWTH=5MB)LOGON(NAME=Employee_log,FILENAME='D:\SQLServer2019\Data\emplog.ldf',SIZE=5MB,MAXSIZE=25MB,FILEGROWTH=2MB);操作要点：在SSMS查询编辑器中执行SQL语句。使用IF语句判断指定数据库是否存在。员工数据库包含一个数据文件和一个日志文件。使用ON明确定义数据文件。使用LOGON明确定义日志文件。2.2.2使用SQL语句创建数据库（3/4）例2.3：创建“销售”数据库IFDB_ID('销售')ISNULLCREATEDATABASE销售ONPRIMARY(--主文件组包含两个文件

NAME=Sales_dat1,FILENAME='D:\SQLServer2019\Data\salesdat1.mdf',SIZE=50MB,MAXSIZE=200MB,FILEGROWTH=5MB),(NAME=Sales_dat2,--次要数据文件

FILENAME='D:\SQLServer2019\Data\salesdat2.ndf',SIZE=10MB,MAXSIZE=UNLIMITED,FILEGROWTH=10%)LOGON(NAME=Sales_log,FILENAME='D:\SQLServer2019\Data\saleslog.ldf',SIZE=5MB,MAXSIZE=50MB,FILEGROWTH=5MB);2.2.2使用SQL语句创建数据库（4/4）场景：创建包含多个文件组的数据库。例2.4：创建“货物”数据库CREATEDATABASE货物ONPRIMARY(...),--主文件组文件FILEGROUPGoodsGroup1(...),--用户定义文件组1及其文件FILEGROUPGoodsGroup2(...)--用户定义文件组2及其文件LOGON(...);核心语法使用FILEGROUP定义新的文件组，并在其后括号内定义属于该文件组的文件。优势便于数据管理、分配和提升性能（可将不同文件组置于不同磁盘）。修改数据库2.3任务2.3修改数据库任务目标掌握使用SSMS和SQL语句修改数据库掌握扩展和收缩数据库的方法掌握移动数据库文件的方法掌握设置数据库选项的方法掌握重命名和删除数据库的方法修改前提需要拥有ALTER权限。2.3.1使用SSMS修改数据库图形化修改步骤在“对象资源管理器”中，右键单击要修改的数据库。选择“属性”命令。在“数据库属性”对话框中，选择不同页面进行修改：“文件”页：添加、删除、修改数据文件和日志文件。“文件组”页：添加、删除文件组。“选项”页：设置恢复模式、排序规则等大量数据库选项。单击“确定”使修改生效。操作示例例2.5：使用SSMS修改例2.3中创建的销售数据库。要求在“销售”数据库中添加一个次要数据文件，其逻辑名称为Sales_dat3，初始容量为10MB，最大容量为50MB，文件增量为10%，物理文件名为salesdat3.ndf，存放路径为“D:\SQLServer2019\Data”。2.3.2使用SQL语句修改数据库核心语句ALTERDATABASE常用子句ADDFILE...：添加数据文件ADDLOGFILE...：添加日志文件REMOVEFILE...：删除文件MODIFYFILE...：修改文件属性（大小、路径等）MODIFYNAME=...：重命名数据库ADDFILEGROUP...：添加文件组REMOVEFILEGROUP...：删除文件组SET<选项>...：设置数据库选项2.3.3扩展数据库（1/2）目的：为数据库分配更多空间。方式1：修改现有文件大小ALTERDATABASE人力资源MODIFYFILE(NAME=人力资源,SIZE=20MB);方式2：添加新数据文件ALTERDATABASE人力资源ADDFILE(NAME=hr_dat2,FILENAME='D:\SQLServer2019\Data\hrdat2.ndf',...);方式3：添加新文件组并添加文件ALTERDATABASE人力资源ADDFILEGROUPFG1;GOALTERDATABASE人力资源ADDFILE(NAME=hr_dat3,...)TOFILEGROUPFG1;2.3.3扩展数据库（2/2）例2.5：使用ALTERDATABASE语句扩展例2.1中创建的人力资源数据库。将逻辑名称为“人力资源”的数据文件的大小改为10MB；增加一个次要数据文件，逻辑文件名为hr_dat2，物理文件名为hr_dat2.ndf，初始容量为10MB，最大容量为100MB，文件增量为10%；增加一个名为FG1的文件组，并向该文件组中添加两个数据文件：逻辑文件名为hr_dat3和hr_dat4，物理文件名为hr_dat3.ndf和hr_dat4.ndf，初始容量均为5MB，最大容量均为50MB，文件增量为5MB。2.3.4收缩数据库（1/2）目的：回收未使用的空间。方式1.设置自动收缩：ALTERDATABASE数据库名SETAUTO_SHRINKON;方式2.手动收缩整个数据库：DBCCSHRINKDATABASE(数据库名,目标可用空间百分比);--示例：使可用空间占10%DBCCSHRINKDATABASE(销售,10);方式3.手动收缩指定文件：DBCCSHRINKFILE(逻辑文件名,目标大小(MB));--示例：将文件收缩到5MBDBCCSHRINKFILE(Sales_dat1,5);方式4.删除文件和文件组：ALTERDATABASE数据库名REMOVEFILE逻辑文件名;ALTERDATABASE数据库名REMOVEFILEGROUP文件组名;2.3.4收缩数据库（2/2）例2.6：从例2.4中创建的货物数据库中删除逻辑名称为GoodsGrp1_data2的数据文件和名称为GoodsGroup2的文件组。ALTERDATABASE货物REMOVEFILEGoodsGrp1_dat2;GOALTERDATABASE货物REMOVEFILEGoodsGrp2_dat1;GOALTERDATABASE货物REMOVEFILEGoodsGrp2_dat2;GOALTERDATABASE货物REMOVEFILEGROUPGoodsGroup2;GO2.3.5设置数据库选项设置数据库选项方法在ALTERDATABASE使用SET子句设置指定选项常用选项分类自动选项:AUTO_CLOSE,AUTO_SHRINK,AUTO_CREATE_STATISTICS可用性选项:READ_ONLY/READ_WRITE,SINGLE_USER/MULTI_USER恢复选项:RECOVERY{FULL|BULK_LOGGED|SIMPLE}游标选项、SQL兼容性选项等注意事项修改model数据库的选项可改变后续新建数据库的默认设置。应用示例将恢复模式设为简单ALTERDATABASE销售SETRECOVERYSIMPLE;2.3.6移动数据库文件适用场景在同一SQLServer实例内移动文件（如更换磁盘）。核心步骤脱机数据库：

ALTERDATABASE销售SETOFFLINE;在操作系统中将物理文件移动到新位置。告知SQLServer新路径：ALTERDATABASE销售MODIFYFILE(NAME=Sales_dat1,FILENAME='新路径\salesdat1.mdf');--为每个移动的文件重复此操作联机数据库：ALTERDATABASE销售SETONLINE;2.3.7重命名数据库前提条件确保无人使用，需设置为单用户模式。方法1：使用SSMS在对象资源管理器中右键单击数据库，选择“重命名”。方法2：使用SQL语句--设置为单用户ALTERDATABASE货物SETSINGLE_USER;GO--重命名ALTERDATABASE货物MODIFYNAME=Goods;GO--恢复为多用户ALTERDATABASEGoodsSETMULTI_USER;GO2.3.8删除数据库前提条件无人使用，无数据库快照。如果涉及复制，需先删除复制。方法1：使用SSMS右键单击数据库，选择“删除”。方法2：使用SQL语句DROPDATABASE数据库名;--示例：同时删除多个数据库DROPDATABASESales,NewSales;重要警告删除操作不可逆！文件将从磁盘物理删除。删除后应备份master数据库。分离和附加数据库2.4任务2.4分离和附加数据库如果要将数据库更改到同一计算机的不同SQLServer实例或者要移动数据库，分离和附加数据库会很有用。首先从SQLServer实例中分离数据库的数据和事务日志文件，然后通过指定这些文件的新位置将其重新附加到同一或其他SQLServer实例，这使数据库的使用状态与它分离时的状态完全相同。通过本任务将学习和掌握分离和附加数据库的方法步骤。项目目标掌握分离数据库的方法掌握附加数据库的方法2.4.1分离数据库（1/2）分离数据库概述将数据库从SQLServer实例中删除，但保留数据文件（.mdf）和日志文件（.ldf）目的是移动或备份数据库，之后可通过“附加”重新使用不能分离的情况数据库已复制并发布存在数据库快照；正在进行数据库镜像数据库处于可疑状态；数据库为系统数据库（如master）分离的关键分离需要独占访问权限（可先设为单用户模式）分离后需刷新对象资源管理器才能看到变化2.4.1分离数据库（2/2）分离数据库操作方法使用SSMS右键单击数据库→“任务”→“分离”；在对话框中勾选“删除连接”可以断开所有活动连接；勾选“更新统计信息”可分离前更新优化统计；单击“确定”，刷新视图（F5）。使用T-SQL--先设为单用户模式（独占）ALTERDATABASE数据库名SETSINGLE_USER;--执行分离EXECsp_detach_db'数据库名',[,'skipchecks']--'true'表示跳过更新统计信息

[,'KeepFulltextIndexFile']--'true'保留全文索引文件【例2.10】分离AdventureWorks2019并跳过统计更新ALTERDATABASEAdventureWorks2019SETSINGLE_USER;EXECsp_detach_db'AdventureWorks2019','true';2.4.2附加数据库（1/2）什么是附加数据库将之前分离的数据库文件（.mdf和.ldf）重新附加到SQLServer实例中。可用于迁移数据库到其他计算机或磁盘，或恢复分离的数据库。附加数据库的必要条件所有数据文件（主文件.mdf、辅助文件.ndf）都必须可用。如果文件路径已更改，必须指定当前路径。使用SSMS附加数据库在对象资源管理器中右键单击“数据库”，选择“附加”。在“附加数据库”对话框中单击“添加”，找到并选择数据库的主文件（.mdf）。在“附加为”列可修改附加后的数据库名称。在“所有者”列可更改数据库所有者。如需添加更多文件（如日志文件），可再次单击“添加”。单击“确定”完成附加，刷新视图（F5）即可看到数据库。2.4.2附加数据库（2/2）使用T-SQL附加数据库-基本语法CREATEDATABASE数据库名称ON<文件选项>FORATTACH;必须指定主文件的当前路径（使用FILENAME选项）。其他文件如果路径与原来相同，可以省略。若需重新生成缺失的日志文件，可使用FORATTACH_REBUILD_LOG（仅限读/写数据库）。【例2.11】将分离的AdventureWorks2019数据库附加回实例。USEmaster;GOCREATEDATABASEAdventureWorks2019ON(FILENAME='D:\SQLServer2019\Data\AdventureWorks2019.mdf')FORATTACH;GO备份和还原数据库2.52.5备份和还原数据库SQLServer备份和还原组件提供了重要的保护手段，以保护存储在SQLServer数据库中的关键数据。实施计划妥善的备份和还原策略可以保护数据库，避免由于各种故障造成的损坏而丢失数据。通过还原一组备份并恢复数据库来测试备份和还原策略，为有效地应对灾难做好准备。通过本任务将学习和掌握备份和还原数据库的方法步骤。任务目标掌握备份数据库的方法掌握还原数据库的方法2.5.1备份数据库（1/3）什么是数据库备份数据库备份是数据的副本，用于在系统发生故障后还原和恢复数据。可以从多种故障中恢复：媒体故障、用户错误（如误删表）、硬件故障、自然灾害等。还可用于数据库复制、镜像、归档等日常操作。备份类型数据备份：包含一个或多个数据文件的完整映像，可备份全部/部分数据库或文件。差异备份：基于最近的数据备份（基准备份），仅包含基准备份之后更改的数据区。还原前必须先还原基准备份。事务日志备份：包含前一个日志备份之后的所有日志记录（仅完整/大容量日志恢复模式）。2.5.1备份数据库（2/3）使用SSMS备份数据库操作步骤在对象资源管理器中展开“数据库”节点，右键单击要备份的数据库→“任务”→“备份”，

打开备份对话框。在常规选项页：数据库：确认或选择要备份的数据库；备份类型：选择“完整”、“差异”或“事务日志”；备份组件：选择“数据库”；目标：通过“添加”按钮指定备份文件位置（磁盘或磁带），可添加多个目标。在介质选项页：覆盖介质：选择“追加到现有备份集”或“覆盖所有现有备份集（新建介质集）”；可靠性：可勾选“完成后验证备份”、“写入介质前检查校验和”、“出错时继续”等选项。单击“确定”开始备份。2.5.1备份数据库（3/3）使用T-SQL备份数据库-基本语法BACKUPDAT