青少年编程与数学 02-002 Sql Server 数据库应用 06课题、数据库操作

本课题介绍了SQL Server数据库的操作，包括数据库的文件组成、系统数据库、创建和配置数据库、删除数据库以及查看数据库信息。数据库由主数据文件、次要数据文件和事务日志文件组成，这些文件被组织成文件组。系统数据库如master、model、msdb和tempdb对SQL Server的运行至关重要。

课题摘要:

本课题介绍了SQL Server数据库的操作，包括数据库的文件组成、系统数据库、创建和配置数据库、删除数据库以及查看数据库信息。数据库由主数据文件、次要数据文件和事务日志文件组成，这些文件被组织成文件组。系统数据库如master、model、msdb和tempdb对SQL Server的运行至关重要。创建数据库可以通过T-SQL语句完成，而数据库配置可以通过修改文件大小、添加或移除数据文件、设置数据库选项等方法进行。删除数据库是一个谨慎的操作，需要确保数据已备份。查看数据库信息可以通过SSMS或使用系统视图和存储过程。这些操作对于数据库管理员来说至关重要，有助于优化性能、管理数据文件和维护数据库环境。

---

一、数据库的文件组成

SQL Server 数据库由一个或多个文件组成，这些文件存储在磁盘上，并被组织成文件组。以下是 SQL Server 数据库的文件组成：

1. 主数据文件（Primary Data File）：

   • 扩展名为 .mdf。
   • 包含数据库的启动信息，并指向数据库中的其他文件。
   • 每个数据库至少有一个主数据文件，通常存储系统表和用户数据。

2. 次要数据文件（Secondary Data File）：

   • 扩展名为 .ndf。
   • 用户定义的可选数据文件，用于存储用户数据。
   • 可以创建多个次要数据文件，并将它们分配给不同的文件组，以提高性能和数据管理的灵活性。

3. 事务日志文件（Log Data File）：

   • 扩展名为 .ldf。
   • 包含用于恢复数据库的事务日志信息。
   • 记录数据库的所有事务操作，如数据修改、事务开始和结束等。

4. 文件组（File Groups）：

   • 文件组是数据库中的逻辑分组，用于管理数据文件的集合。
   • 主文件组（PRIMARY）包含主数据文件和未分配给其他文件组的任何次要文件。
   • 用户可以创建自定义文件组，以将数据文件的集合分组，便于管理和优化性能。

文件组允许数据库管理员控制数据库对象的物理存储位置，例如，可以将表和索引分配到特定的文件组，从而影响它们的物理存储位置。这有助于优化数据库性能，特别是在处理大量数据和高并发访问时。

SQL Server 还支持内存优化的数据文件组，用于存储内存中的数据，以及 FILESTREAM 文件组，用于存储大型对象数据，如文件和图像。

数据库文件可以配置为自动增长，当空间不足时，SQL Server 会自动扩展文件大小，直到达到最大大小限制。

在创建数据库时，可以使用 CREATE DATABASE 语句定义文件和文件组，而在修改数据库结构时，可以使用 ALTER DATABASE 语句。

二、系统数据库

SQL Server 包含多个系统数据库，每个数据库都有其特定的用途和功能。以下是 SQL Server 中系统数据库的组成及其功能：

1. master 数据库：

   • 记录 SQL Server 实例的所有系统级信息，包括服务器配置、登录账号、系统设置、数据库文件位置等。如果 master 数据库损坏，SQL Server 将无法启动 。

2. model 数据库：

   • 用作 SQL Server 实例上创建的所有数据库的模板。当创建新数据库时，会复制 model 数据库的结构和配置。对 model 数据库的修改将影响以后创建的所有数据库 。

3. msdb 数据库：

   • 用于 SQL Server 代理计划警报和作业。它还用于存储数据库邮件、维护计划、日志_shipping、SQL Server 代理作业和警报的历史信息 。

4. tempdb 数据库：

   • 一个临时数据库，用于存储临时表、索引创建过程中的中间数据、事务日志等。每次 SQL Server 启动时，tempdb 都会被重新创建，因此它在系统启动时总是干净的 。

5. Resource 数据库：

   • 一个只读数据库，包含 SQL Server 包括的所有系统对象。系统对象在物理上保留在 Resource 数据库中，但在逻辑上显示在每个数据库的 sys 架构中 。

这些系统数据库对于 SQL Server 的运行至关重要，它们共同支持 SQL Server 实例的管理和操作。在对这些数据库进行操作时应该非常小心，因为不正确的更改可能会影响整个 SQL Server 实例的稳定性和性能。

三、创建数据库

在 SQL Server 中创建数据库可以通过 SQL 语句来完成。以下是创建数据库的基本步骤和示例：

1. 打开 SQL Server Management Studio (SSMS) 或者使用其他 SQL 客户端工具。

2. 连接到 SQL Server 实例。

3. 在查询编辑器中输入创建数据库的 SQL 语句。

创建数据库的基本 SQL 语句格式如下：

CREATE DATABASE [数据库名称]
ON
( NAME = [文件组名], FILENAME = '文件路径' , SIZE = [初始大小] , MAXSIZE = [最大大小] , FILEGROWTH = [增长大小] )
LOG ON
( NAME = [日志文件组名], FILENAME = '日志文件路径' , SIZE = [日志文件初始大小] , MAXSIZE = [日志文件最大大小] , FILEGROWTH = [日志文件增长大小] );

其中，[数据库名称] 是你想要创建的数据库的名字，[文件组名] 和 [日志文件组名] 是文件组的名称，文件路径 和 日志文件路径 是数据库文件和日志文件存储的路径，初始大小、最大大小、增长大小 和 日志文件初始大小、日志文件最大大小、日志文件增长大小 是数据库和日志文件的大小设置。

例如，创建一个名为 MyDatabase 的数据库，可以这样写：

CREATE DATABASE MyDatabase
ON
( NAME = MyDatabase_Data, FILENAME = 'C:\SQLData\MyDatabase.mdf' , SIZE = 10MB , MAXSIZE = 200MB , FILEGROWTH = 10% )
LOG ON
( NAME = MyDatabase_Log, FILENAME = 'C:\SQLLog\MyDatabase.ldf' , SIZE = 5MB , MAXSIZE = 100MB , FILEGROWTH = 5MB );

在这个例子中，数据库文件 (MyDatabase.mdf) 被创建在 C:\SQLData 目录下，日志文件 (MyDatabase.ldf) 被创建在 C:\SQLLog 目录下。数据库的初始大小设置为 10MB，最大大小为 200MB，并且每次增长 10%。日志文件的初始大小为 5MB，最大大小为 100MB，并且每次增长 5MB。

请注意，你需要确保 SQL Server 服务账户有权限访问指定的文件路径，并且路径是存在的。此外，根据你的 SQL Server 版本和配置，可能需要调整文件路径和大小设置。

也可以使用SSMS的功能菜单创建数据库。其方法请自行按功能提示完成操作，或参考一下网上的指导文件。本课程主要使用语句来完成。

四、数据库配置

在 SQL Server 中，已经创建的数据库可以通过多种方式进行配置，以优化性能、管理数据文件、设置数据库选项等。以下是一些常见的数据库配置方法：

1. 修改数据库文件大小和增长设置

如果你需要调整数据库文件的大小或增长设置，可以使用 ALTER DATABASE 语句。例如：

ALTER DATABASE [YourDatabaseName]
MODIFY FILE
( NAME = N'YourDatabaseName_Data',SIZE = 20MB,MAXSIZE = UNLIMITED,FILEGROWTH = 10%);

2. 添加或移除数据文件

如果需要添加更多的数据文件或移除现有的数据文件，也可以使用 ALTER DATABASE 语句：

-- 添加数据文件
ALTER DATABASE [YourDatabaseName]
ADD FILE
( NAME = N'YourDatabaseName_Data2', FILENAME = N'路径\YourDatabaseName_Data2.ndf' , SIZE = 10MB , FILEGROWTH = 10%);-- 移除数据文件
ALTER DATABASE [YourDatabaseName]
REMOVE FILE YourDatabaseName_Data2;

3. 设置数据库选项

SQL Server 提供了许多数据库级别的选项，可以通过 ALTER DATABASE 语句进行设置。例如，设置数据库为只读模式：

ALTER DATABASE [YourDatabaseName]
SET READ_ONLY;

或者设置数据库的兼容性级别：

ALTER DATABASE [YourDatabaseName]
SET COMPATIBILITY_LEVEL = 130;

4. 配置数据库的恢复模型

数据库的恢复模型可以设置为 SIMPLE、FULL 或 BULK_LOGGED，这会影响事务日志的处理方式：

-- 设置为简单恢复模型
ALTER DATABASE [YourDatabaseName]
SET RECOVERY SIMPLE;-- 设置为完全恢复模型
ALTER DATABASE [YourDatabaseName]
SET RECOVERY FULL;

5. 管理数据库的访问权限

你可以使用 GRANT 或 REVOKE 语句来管理用户对数据库的访问权限：

-- 授予用户访问权限
GRANT SELECT, INSERT, UPDATE ON DATABASE::[YourDatabaseName] TO [YourUserName];-- 撤销用户访问权限
REVOKE SELECT, INSERT, UPDATE ON DATABASE::[YourDatabaseName] FROM [YourUserName];

6. 使用 SQL Server Management Studio (SSMS)

使用 SQL Server Management Studio (SSMS) 进行数据库配置是一个直观的过程，它提供了一个图形用户界面(GUI)来管理数据库的各种设置。以下是使用 SSMS 进行数据库配置的步骤：

（1）打开 SQL Server Management Studio (SSMS)

首先，启动 SSMS 并连接到你的 SQL Server 实例。

（2）访问数据库属性

• 在“对象资源管理器”中，找到并展开“数据库”节点。
• 右键点击你想要配置的数据库名称，然后选择“属性”。

（3）配置数据库选项

在“数据库属性”对话框中，你可以在不同的页面中设置各种数据库选项：

文件组页

• 数据文件和日志文件：在这里，你可以添加、删除或修改数据文件和日志文件的位置、大小和增长设置。

选项页

• 常规选项：设置数据库的兼容性级别、排序规则、恢复模型等。
• 状态：设置数据库的只读或只写状态，启用或禁用数据库等。
• 自动关闭：设置数据库在一定时间内无活动时自动关闭。
• 自动收缩：设置数据库在一定条件下自动收缩数据文件和日志文件。
• 安全性：设置数据库的访问权限和角色成员资格。

兼容性级别页

• 兼容性级别：设置数据库的兼容性级别，影响数据库的行为和功能。

排序规则和语言页

• 排序规则：设置数据库使用的排序规则。
• 默认语言：设置数据库的默认语言。

文件和文件组页

• 文件组：管理文件组和文件组中的文件。

触发器页

• DDL触发器：管理数据库级别的数据定义语言(DDL)触发器。

镜像页

• 数据库镜像：配置数据库镜像设置。

事务日志传送页

• 日志传送：设置数据库的事务日志传送选项。

（4）应用更改

在“数据库属性”对话框中，对数据库进行配置后，点击“确定”按钮应用更改。在某些情况下，更改可能需要重启数据库才能生效。

（5）使用查询编辑器进行配置

除了使用 GUI 界面外，你还可以在 SSMS 的查询编辑器中使用 T-SQL 语句来配置数据库。例如，你可以编写并执行 ALTER DATABASE 语句来修改数据库设置。

注意事项

• 在进行配置更改时，确保你有足够的权限。
• 在生产环境中，建议在进行重大更改之前进行备份和测试。
• 某些配置更改可能需要重启数据库，这可能会影响数据库的可用性。

通过这些步骤，你可以使用 SSMS 来配置 SQL Server 数据库的各种设置，以满足你的业务需求和性能要求。

五、删除数据库

在 SQL Server 中删除数据库是一个需要谨慎处理的操作，因为一旦执行，所有与该数据库相关的数据和对象都将被永久删除。以下是删除数据库的步骤：

1. 备份数据库（如果需要）：
   在删除数据库之前，如果数据库中包含需要保留的数据，你应该先进行备份。可以使用 SQL Server Management Studio (SSMS) 或 T-SQL 命令来备份数据库。

2. 使用 SQL Server Management Studio (SSMS) 删除数据库：

   • 打开 SSMS 并连接到 SQL Server 实例。
   • 在对象资源管理器中，找到并展开“数据库”节点。
   • 右键点击你想要删除的数据库名称。
   • 选择“删除”选项。
   • 在弹出的对话框中，确认数据库名称，可以选择删除数据库的同时删除相关的数据库文件，然后点击“确定”按钮。
   • 系统会提示你确认删除操作，点击“确定”执行删除。

3. 使用 T-SQL 命令删除数据库：

   • 打开 SSMS 的查询编辑器。

   • 连接到目标 SQL Server 实例。

   • 执行以下 T-SQL 命令：

     USE master;
     GO
     DROP DATABASE [YourDatabaseName];
     GO
     

   • 将 [YourDatabaseName] 替换为你想要删除的数据库名称。

   • 执行该命令，数据库将被删除。

4. 处理依赖于数据库的对象：
   如果数据库中的对象（如登录名、作业、服务器级触发器等）被其他数据库或服务器对象所依赖，你可能需要先删除或修改这些依赖对象。

5. 确认数据库已被删除：
   在对象资源管理器中检查“数据库”节点，确认目标数据库已经不再列表中。

安全提示：

• 确保你有足够的权限来删除数据库。
• 删除数据库是一个破坏性操作，一旦执行，所有数据都将丢失，除非有备份可以恢复。
• 在生产环境中，建议在维护时间窗口内进行此类操作，并通知所有相关的利益相关者。

在执行删除操作前，请确保已经考虑了所有相关的因素，并且已经采取了适当的数据保护措施。

六、查看数据库信息

在 SQL Server 中，你可以通过多种方式查看数据库信息。以下是一些常用的方法：

1. 使用 SQL Server Management Studio (SSMS)

查看数据库列表

• 打开 SSMS 并连接到 SQL Server 实例。
• 在“对象资源管理器”中，展开“数据库”节点，你将看到一个数据库列表。

查看数据库属性

• 在“对象资源管理器”中，右键点击你想要查看的数据库。
• 选择“属性”以查看数据库的详细信息，如数据库大小、所有者、创建时间、兼容性级别等。

2. 使用系统视图查询数据库信息

查看所有数据库的基本信息

SELECT name, database_id, creation_date, compatibility_level, collation_name, recovery_model_desc
FROM sys.databases;

查看数据库的大小

SELECT DB_NAME() AS [DatabaseName], CAST(SUM(size) * 8 / 1024 AS DECIMAL(10, 2)) AS [SizeInMB]
FROM sys.master_files
WHERE database_id = DB_ID();

查看数据库的文件信息

SELECT name AS FileName,physical_name AS PhysicalPath,type_desc AS FileType,size AS FileSizeInPages,max_size AS MaxSizeInPages,growth AS FileGrowth
FROM sys.master_files
WHERE database_id = DB_ID();

查看数据库的扩展属性（如所有者、创建者等）

SELECT name, create_date, principal_id, type_desc, owner_sid
FROM sys.databases
WHERE name = N'YourDatabaseName';

3. 使用系统存储过程

查看数据库的详细信息

EXEC sp_helpdb 'YourDatabaseName';

这个存储过程提供了数据库的详细信息，包括数据库大小、所有者、创建日期等。

4. 使用动态管理视图 (DMVs)

查看数据库的 I/O 统计信息

SELECT DB_NAME(database_id) AS [DatabaseName], io_stall_read_ms AS [ReadLatency], io_stall_write_ms AS [WriteLatency], num_of_reads AS [NumberOfReads], num_of_writes AS [NumberOfWrites], (size/128.0) AS [SizeInMB]
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
CROSS JOIN sys.master_files AS mf
WHERE vfs.database_id = mf.database_id;

这些方法可以帮助你从不同的角度查看数据库的详细信息。在实际应用中，你可以根据需要选择合适的方法来获取所需的信息。