正文

SQL Server数据库、表、数据类型基本概念

IP云
IP云V管理员/11 阅读
07/26
提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

一、SQL Server的数据存储结构

SQL Server是一个数据库管理系统，需要以有效方式存储高容量数据。要更好地理解SQL Server处理数据的方式，就需要了解数据的存储结构。

1、文件类型

数据库在磁盘上是以文件为单位存储的，由数据文件和事务日志文件组成。一个数据库至少应该包含一个数据文件和一个事务日志文件。

  • 主数据文件：主数据文件包含数据库的启动信息，指向数据库中的其他文件，每个数据库都有一个主数据文件（有且只有一个），推荐文件扩展名是.mdf；

  • 次要（辅助）数据文件：除主数据文件以外的所有其他数据文件都是次要数据文件，某些数据库可能不包含任何次要数据文件，而有些数据库则包含多个次要数据文件，次要数据文件的推荐文件扩展名是.ndf；

  • 事务日志文件：事务日志文件包含恢复数据库所有事务的信息，每个数据库中至少有一个事务日志文件，当然也可以有多个。事务日志文件的推荐文件扩展名是.idf；

  • 文件流数据文件：可以使基于SQL的应用程序能在文件系统中存储非结构化的数据，如：文档、图片、音频、视频等；

2、数据文件

从更微观的角度来看，数据文件由若干个64KB大小的区组成，每个区由8个8KB的连续页组成，如下图所示：

SQL Server所能识别的最小存储单位被称为页。一个页的大小是8KB，它是SQL Server实际存储数据的单位。磁盘I/O操作再页级执行。一个区由8个物理上连续的页组成，用来
有效地管理页。所有的页都存储在区中。当在SQL Server中创建了表以后，对象就被分配到区中。较小的表可以和其他数据库对象在一个区中。

3、事务日志

事务是一个或多个T-SQL语句的集合，相当于一个“原子”任务，要么执行成功，要不完全不执行。每个SQL Server数据库都具有事务日志，用来记录所有事务的SQL语句。当发生数据灾难时，通过事务日志记录的T-SQL语句可以恢复数据库。
事务日志文件的存储不是以页为单位的，而是以一条条的大小不等的日志记录为单位。

二、数据库管理

可以使用SSMS工具来设计和建立数据库，存储企业数据，以满足企业的需求。

1、创建数据库

创建数据库之前，必须要先确定数据库的名称、所有者（创建数据库的用户）、大小，以及存储该数据文件和事务日志文件的位置。所以在创建数据库之前，需要考虑如下事项：

  • 创建数据库的权限默认授予sysadmin和dbcreator服务器角色成员，服务器角色用于向用户授予服务器范围内的安全特权；

  • 创建数据库的用户将成为该数据库的所有者；

  • 考虑数据文件及日志文件的放置位置；

  • 合理估计数据库的大小合并增长值；

1）启动SSMS，使用sa登录数据库

2）右击“数据库”节点，在弹出的快捷菜单中选择“新建数据库”命令

3）打开“新建数据库”窗口，设置数据库名称为“class”，指定数据库的所有者，默认创建数据库的用户将成为该数据库的所有者

4）在“路径”列中设置文件的保存目录，单击该列中的按钮，打开“定位文件夹”对话框，可以选择保存文件的目录

5）在“初始大小”列中设置初始值，在“自动增长/最大大小”列中设置自动增长值

6）当设置完数据名称和其他选项后，单击“确定”按钮，一个新的数据库就建好了。它生成了两个文件，一个是包含数据的.mdf文件，另一个是包含日志信息的.ldf文件

7）作为一名数据库管理员，可能会处理一些对数据库的特殊要求，所有需要为数据库设置数据库级选项，而不采用创建时的默认值。这些选项的修改可以在“数据库属性-class”窗口的“选项”界面中进行

2、扩展数据库

当数据库中的数据文件或日志文件的空间被占满时，需要为数据文件和日志文件分配更多的空间。SQL Server可以根据在新建数据库时定义的增长参数自动扩展数据库，也可以通过在现有的数据库文件上分配更多文件空间，或者在另一个新文件上分配空间来手动扩展数据库。

扩展数据库时，必须使数据库的容量至少增加1MB，还可以指定允许文件增长到的最大值，这样可以防止文件无限制地增大，导致用尽整个磁盘空间。根据自己的需求，应适当设置增长值，过大或过小都是不恰当的。

1）在SSMS的对象资源管理器中，右击“class”数据库，在弹出的快捷菜单中选择“属性”命令，打开“数据库属性-class”窗口

2）在“数据库属性-class”窗口的“选择页”窗格中选择“文件”选项，打开“文件”选择页

3）选择class数据文件，设置“初始大小”为100MB，单击“自动增长/最大大小”列中的...按钮，打开“更改class的自动增长设置”对话框，取消选中“启用自动增长”复选框，然后单击“确定”

4）在“文件”选择页单击“添加”按钮，添加一个数据库文件，在“逻辑名称”列中指定新建文件名为“db_class”，在“文件类型”列中选择“行数据”，“初始大小”设置为50MB，单击“自动增长/最大大小”列中的...按钮，在打开的对话框中选择“启用自动增长”复选框。设置“最大文件大小”为500MB，路径选择C:\db_class，然后单击“确定”

3、收缩数据库

数据库中的每个文件都可以通过删除未使用空间的方法来减小，SQL Server允许通过缩小数据库把不使用的空间释放出来，数据文件和日志文件都可以减小（收缩）。可以手动收缩数据库，也可以自动收缩数据库。

1）手动收缩数据库

如下图所示，选择“任务”——>“收缩”——>“数据库”命令，打开“收缩数据库-class”窗口。“当前分配的空间”选项显示了数据库占用空间的大小，“可用空间”选项显示了数据库可以收缩的空间及百分比，然后单击“确定”按钮，将执行收缩数据库操作


在收缩数据库时，无法将整个数据库收缩得比其初始大小更小。因此，如果数据库创建时的大小为10MB，后来增长到100MB，则该数据库最小只能收缩到10MB，即使以及删除了所有数据也是如此。但是，收缩文件时，可以将各个数据库文件收缩的比其初始大小更小。

2）手动收缩文件

如手动收缩数据库图一所示，选择“任务”——>“收缩”——>“文件”命令打开“收缩文件-class”窗口

在“文件类型”下拉列表中选择“数据”选项，也可以在这里选择收缩日志文件。“当前分配的空间”选项和“可用空间”选项显示了该文件的占用空间，剩余空间和可收缩的百分比。
在“收缩操作”选项 组中有三个选项，默认为“释放未使用的空间”，其作用分别如下：

  • 释放未使用的空间：将释放文件中所有未使用的空间，并将文件收缩到上次分配的大小。这将减小文件的大小，但不移动任何数据；
  • 在释放未使用的空间前重新组织页：将释放文件中所有未使用的空间，并尝试重新定位到未分配的空间，这里需要指定“将文件收缩到”选项的值；
  • 通过将数据迁移到同一个文件组中的其他我呢见来清空文件：将指定文件中的所有数据移至同一文件组中的其他文件中，然后就可以删除空文件；

3）自动收缩数据库

可以通过设置“数据库属性”窗口中的“选项”选择页中的“自动收缩”来实现自动收缩功能。将“自动收缩”选择设置为True后，将自动收缩有可用空间的数据库，如下图所示，该活动在后台进行，并且不影响数据内的用户活动

4、分离和附加数据库

如果需要将数据库更改到同一计算机或不同计算机的不同SQL Server实例中，或要移动数据库文件存储的位置，分离和附加数据会很有用。

1）分离数据库

①在SSMS对象资源管理器中右击“class”数据库，在弹出的快捷菜单中选择“任务”——>“分离”命令，打开“分离数据库”窗口

②检查要分离的数据库，无误后，单击“确定”按钮，完成分离操作

  • 数据库中有一个或多个活动连接时，“消息”列将显示活动连接的数量，如“一个活动连接”。必须选中“删除连接”复选框以断开所有活动连接；
  • 默认情况下，分离操作将在分离数据库是保留过期的优化统计信息；若要更新现有的优化统计信息，可用选中“更新统计信息”复选框；

2）附加数据库

附加数据库时，所有数据文件（主数据文件和次要数据文件）都必须可用。如果任何数据文件的路径不同于首次创建数据库或上次附加数据库时的路径，则必须指定文件的当前路径。

①将class数据库所有文件移动到其他位置，如D:\class_log文件夹

②在SSMS对象资源管理器中，连接到SQL Server数据库实例

③右击“数据库”，在弹出的快捷菜单中选中“附加”命令，打开“附加数据库”窗口


④单击“添加”按钮，然后在打开的“定位数据文件”对话框中选择要附加的数据库所在的磁盘驱动器，展开目录树以查找和选择class数据库的.mdf文件，然后单击“确定”按钮

⑤如果要改变附加数据库名称，可在“附加数据库”窗口的“附加为”列中输入名称，也可以在“所有者”列中更改数据库的所有者，确认无误后，单击“确定”按钮，完成附加操作

如果数据库的事务日志文件丢失，数据文件保存完好，也可以附加成功。在附加的时候，数据库会自动新建事务日志文件。

5、删除数据库

当用户不再需要自己的数据库，或者已将其移到其他数据库或服务器上时，即可删除该数据库，但是不能删除系统数据库。删除数据库之后，文件及其数据都从服务器上的磁盘中删除。一旦删除数据库，它即被永久删除。

1）右击“class”数据库，在弹出的快捷菜单中选择“删除”命令，打开“删除对象”窗口

2）确认要删除的数据库，如果有用户与数据库连接，还应选中“关闭现有连接”复选框，再单击“确定”按钮，完成数据库的删除操作

三、表的基本概念

表是包含数据库中所有数据的数据库对象。数据在表中的组织方式与在电子表格中相似，都是按行和列的格式组织的。每行代表一条唯一的记录，每行代表记录中的一个字段。如下图：

SQL Server中的表包括下列主要组件：

  • 列：每列代表由表建模的对象的某个属性。例如，员工表有编号列，姓名列和职务列等；

  • 行：每行代表由表建模的对象的一个单独的实例。例如，公司每名员工在表中均占一行；

1、数据完整性

1）实体完整性

实体完整性将行定义为特定表的唯一实体。实体完整性通过UNIQUE(唯一)索引，UNIQUE约束或PRIMARY KEY（主键）约束，强制表的标识列或主键的完整性；

2）域完整性

域完整性指特定列的项的有效性。可以强制域完整性限制类型（通过使用数据类型）、限制格式（通过使用CHECK约束和规则）或限制可能值的范围（通过使用FOREIGN KEY约束、CHECK约束、DEFAULT定义、NOT NULL定义和规则）；

3）引用完整性

输入或删除行时，引用完整性保留表之间定义的关系。强制引用完整性时，SQL Server将防止用户执行下列操作：

  • 在主表中没有关联行的情况下在相关表中添加或更改行；
  • 在主表中更改值（可导致相关表中出现孤立行）；
  • 在有匹配的相关行的情况下删除主表中的行；

4）用户定义完整性规则

用户定义完整性可以定义不属于其他任何完整性类别的特定业务规则。所有完整性类别都支持用户定义完整性，这包括CREATE TABLE中所有列级约束和表级约束、存储过程及触发器；

2、主键

主键唯一标识表中的行数据，一个主键值对应一行数据。主键由一个或多个字段组成，其值具有唯一性，而且不允许取空值（NULL），一个表只能有一个主键。

四、使用SSMS操作数据表

1、数据类型

数据类型是数据的一种属性，用于指定对象可保存的数据的类型，如整数数据、字符数据、货币数据、日期和时间数据、字符串等。

SQL Server中的数据类型可以归纳为下列类别：

1）精准数字

2）近似数字

3）日期和时间

4）字符串

5）Unicode字符串

6）二进制字符串

2、默认值

如果插入行时没有为列指定值，则该列使用默认值。默认值可以是计算结果为常量的任何值，如常量、内置函数或数学表达式。

对于表中的每个列，可以指定当用户将该列保留为空白时将在该列中输入的默认值。如果没有分配默认值，并将该列保留为空白，则：

  • 如果设置了允许空白值的选项，则将向该列中插入NULL；
  • 如果没有设置允许空白值的选项，则该列将保持空白，但在用户为该列提供值之前，它们将无法保持行；

3、标识列

对于每个表，均可创建一个包含系统生成的序号值的标识列，该序号值以唯一方式标识表中的每行。
可以使用标识列在表中创建自动递增标识号，所以标识列习惯上又叫自增列，只能为每个表创建一个标识列。

标识列具有以下三种特点：

  • 列的数据类型为不带小数的数值类型；

  • 在进行插入（Insert）操作时，该列的值由系统按一定规律生成，不允许空值；

  • 列值不重复，具有标识表中每行的作用，每个表只能有一个标识列；

创建一个标识列，通常要指定以下三个内容：

  • 类型（Type）：在SQL Server 2008中，标识列类型必须时数值类型，如decimal、int、numeric、smalint、bigint、tinyint。其中要注意的是，当选择decimal和numeric时，小数位数必须为零。另外还要注意每种数据类型所表示的数值范围；

  • 种子（Seed）：指派给表中第一行的值，默认为1；

  • 递增量（Increment）：相邻两个标识值之间的增量，默认为1；

4、检查约束

通过限制列可接受的值，CHECK结束可以强制域的完整性。此类约束类似于FOREIGN KEY约束，因为可以控制放入列中的值。但是，它们在确定有效值的方式上有所不同；FOREIGN KEY约束从其他表获得有效值列表，而CHECK约束通过不基于其他列中的数据的逻辑表达式确定有效值。

可以通过任何基于逻辑运算符返回True或Flase的逻辑（布尔）表达式创建CHECK约束。例如，可以通过创建CHECK约束将age列中值的范围限制为0~200之间的数据，以防止输入的年龄值超出正常的年龄范围。逻辑表达式为：

age >= 0 AND age <= 200

此博客就写到这里吧，下一篇博客将写如何在视图模式管理数据库和表以及使用T-SQL创建数据库及表。