http://xiaorenwutest.blog.51cto.com 数据库和表的管理

前言：上次主要介绍的是sql server数据库的搭建以及通过SSMS工具进行连接数据库，本次内容主要介绍数据库的表进行管理。在工作中DBA通常对数据库进行创建和删除以及修改表的内容，扩展和收缩、分离及附加。

表是存放数据、查找数据以及更新数据的基本数据构建，在对数据的操作都是在表的基础上进行的，以及如何维护表。 注：实际上表是逻辑的存在，事实上数据存放在硬盘上面。

另外在创建数据库的时候回形成两个基本的库文件主数据库文件.mdf和事物日志文件.ldf

sql server 2008R2如何存储数据

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

数据库创建在物理介质（磁盘）上的一个或多个文件上，它预先分配了被数据和事物日志所要适用的物理存储空间。存储数据的文件叫做数据文件，数据文件包含数据和对象，如表和索引。存储事物日志的文件叫做事物日志文件（又称日志文件）。

sql server 2008 R2数据库有一下类型的文件

1. 主数据文件

2. 辅数据文件

3. 事物日志文件

4. 文件流

举例说明

--创建数据库

createdatabaseschoolDB

go

useschoolDB

go

这样创建完的数据库只有一个数据文件和一个事物日志文件，查看方式在创建的schoolDB上右键选择“属性”--“文件”

可以在界面中单击“添加”增加新的数据文件schooldb2，以后再schooldb数据库中创建新的表，表中的数据就会存放在schooldb和schoolDB2数据文件中。

数据文件在数据库中存放的时候都有标识，我们可以通过sp_helpdbschoolDB

数据文件结构

数据文件1和数据文件3的结构如果所示：

数据文件由64kb大小的区（extend)组成的,每个区由8个8KB的连续的页组成的。

sqlserver所能识别的最小存储单元被称为页，一个页的大小是8KB,是sql server 存储数据的单位。

在数据库中一页的大小是8KB，在计算机中1KB就是1024，所以1页可以存放1024*8=8192个字节

1.1.如何根据表的记录数量估算占用的磁盘空间

1.先算一个表中一行有多少个字节，例如学号字段6个字节，姓名字段8个字节，出生日期字段多少个字节

2.算一个页能够存多少行

3.数据库中的表每天增加多少行记录，就能够算出每天要增加多少硬盘空间，这样就可根据数据量估算规划多大的空间。

在数据库中创建一张Tstudent表

createTABLETStudent(

StudentIDvarchar(10)NOTNULL,

Snamevarchar(10)DEFAULTNULL,

sexchar(2)DEFAULTNULL,

cardIDvarchar(20)DEFAULTNULL,

BirthdaydatetimeDEFAULTNULL,

Emailvarchar(40)DEFAULTNULL,

Classvarchar(20)DEFAULTNULL,

enterTimedatetimeDEFAULTNULL

)

go

10个字节+10个字节+2个字节+20字节+8字节+40字节+20字节+8个字节=118字节

Tstudent表的一个行有118个字节

如果每天增加10000条记录，10000条记录占多少页？

1页可以存放1024*8=8192个字节，抛去标头96字节，真正存放的字节数就8192-96=8096字节。

一页能存8096字节，一页能够存8096/118=68条记录

10000行有多少页？10000/68=148(页）

一页是8KB，148*8==1184KB

在Tstudent表中存放10000条记录需要预备出来1MB多的磁盘空间。这样就可以根据每天增加的记录数，合理规划好磁盘空间了。

现在Tstudent表中还没有插入任何记录，所以占用了0页的数据

提供查看数据文件页数的命令

selectOBJECT_NAME(i.object_id)as表名,data_pagesas数据页数

fromsys.indexesasi

joinsys.partitionsaspONp.object_id=i.object_idandp.index_id=i.index_id

joinsys.allocation_unitsasaONa.container_id=p.partition_id

wherei.object_id=object_id('dbo.TStudent')

执行教学环境中的存储过程，添加10000条记录

这个页数和我们刚才算出来的有些差距，这是正常的，每一页可能并没有插入68条记录。

1.2.事物日志

事物是一个或多个T-SQL语句的集合，事物有一个特性：要么执行成功，要么执行失败。每个sql server数据库都具有事物日志，用于记录所有事物的SQL语句。当发生数据灾难时候，通过事物日志记录的T-SQL语句可以恢复数据库。

如果系统出现故障，sql server将使用事物日志重做（前滚）所有已确认的事物，撤销（回滚）所有未完成的事物。

1.3.创建数据库（扩展/收缩）

使用图形界面创建数据库，在对象资源管理器中，右击“数据库”节点，在弹出的快捷菜单中选择“新建数据库”命令，打开“新建数据库”窗口，设置数据库名称为“class”，制定数据库的所有者，默认创建数据库的用户将成为该数据库的所有者。主数据文件初始值为3mb,自动增长值为1mb，当数据文件或日志文件空间使用完之后，会根据设定的自动增长值增大文件的容量。

在路径列中可以设置数据文件和日志文件的保存路径。单击“确定”之后一个新的数据库就建立成功了，随之会生成两个文件一个是包含数据文件的.mdf，一个是包含日志信息的.ldf文件。

创建数据库之前，必须先确定数据库的名称，所有者（创建数据库的用户），大小以及存储该数据文件和事物日志文件的位置。

如果要对数据库做数据库级别的设置

在数据库属性中选择“选项”，在右侧可以修改数据库相关的属性。

1.3.1.扩展数据库

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

扩展数据库时，必须使数据库的容量至少增加1mb，还可以指定允许文件增长到的最大值，这样可以防止文件无限制的增长，导致用尽整个磁盘空间。

举例说明：

将class数据库的class数据文件大小设置为100MB,不自动增长，然后添加一个新的数据文件，文件名为“class1”，初始文件大小设置为“50MB”，自动增长，最大文件大小设置为“500MB”

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

配置为如下图所示：最后单击“确定”

1.3.2.收缩数据库

数据库在使用一段时间后，时常会因为数据删除而造成数据库中空闲空间增多的情况，这时就需要减少分配给数据库文件和事物日志文件的磁盘空间，以免浪费磁盘空间。当数据库中没有数据时，可以修改数据库文件属性，直接改变其占用空间，但是当数据库中有数据时，这样做会破坏数据库中的数据，因此需要使用收缩的方式来缩减数据库空间。

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

手动收缩数据库的方法

从图中可以看出来即可以选择收缩数据库，也可以选择单独收缩某个数据文件。

收缩数据库：

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

收缩数据文件

选择--“任务”--“收缩”--“文件”命令，打开收缩文件对话框

在“文件类型”下拉列表框中选择“数据”选项，也可以在这里选择收缩日志文件。”当前分配的空间“选项和”可用空间“选项显示了该文件的占用空间，使用空间和收缩的百分比。

释放未使用的空间：将释放文件中所有未使用的空间，并将文件收缩到上次分配的大小，这样将减小文件大小，但不移动任何数据。

在释放未使用空间前重新组织页：将释放文件中所有未使用的空间，并尝试重新定位到未分配的空间。这里需要指定”将文件收缩到“选项的值。

通过将数据迁移到同一文件组中的其他文件来清空文件：将指定文件中所有数据迁移至同一文件组中的其他文件中，然后可以删除空文件。

根据需要确定选项，然后单击“确定”，执行收缩操作。

自动收缩数据库

可以通过设置“数据库属性--class”窗口中的“选项”选择页中的“自动收缩”选项参数来实现自动收缩功能。将“自动收缩”选项设置为“true”后，将自动收缩可用空间的数据库。

1.3.3.分离和附加数据库

如果要将数据库更改到同一计算机的不同sql server实例中或要移动数据库，就可以使用分离和附加数据库的功能了。可以分离数据库的数据文件和日志文件，然后将它们从新附加到同一其他SQL Server实例中。

分离数据库

分离数据库是指将数据库从SQL server实例中移除，但是要保证数据库中的数据文件和日志文件完整无损。这些分离的数据文件和日志文件可以附加到其他的数据库实例中。

举例说明：

将class数据库进行分离

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

数据库中有一个或多个活动连接时，“消息”列将显示活动连接的数量，如“一个活动连接”。必须选中“删除连接”复选框以断开所有活动连接。

默认情况下，分离操作将在分离数据库时保留过期的优化统计信息，若要更新现有的优化统计信息，可以选中“更新统计信息”复选框。

附加数据库

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

单击“添加”按钮，选择“class.mdf”数据文件

单击“确定”之后，附加文件成功。

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

1.3.4.删除数据库

当用户不在需要自己的数据库时就可以删除数据库了，但不能删除系统数据库。删除数据库后，文件及其数据都从服务器上的磁盘中删除，一旦删除数据库，就会被永久删除。

确认要删除的数据库

1.4.表的基本管理

数据完整性：

实体完整性：确保每一行有一个唯一的标识列：

域完整性：规定该列能够接受的数据取值（域完整性）

引用完整性：表内和表间的列参照

用户定义的完整性：列级约束和表级约束，存储过程及触发器

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

如果主键由多个列组成，则其中一个列将允许有重复值，但是主键中所有列的值得各种组合必须是唯一的。

定义主键可以对在不允许空值的指定列中输入的值强制其唯一性。如果为数据库中的某个表定义了主键，则可将该表与其他表相关连，从而减少对冗余数据的要求。

sql server 中的每个数据库最多可以存储20亿个表，每个表可以有1024列，表的行数及大小仅受可用存储空间的限制，每行最多可以存储8060B，创建表时必须指定表名，列名即数据类型等。

1.5.sql server的数据类型

1.数字型代表数字 int tinyint smallint bigint 十进制小数 money smallmoney decimal 浮点数和real

2.日期型 datetime 可以精确到0.333毫秒 small

3.字符型包括char和nchar 也包含变长字符类型varchar和nvarchar

4.定长字符 char(20)

5.变长字符 varchar(20)

6.Char 适合存放英文一个字符占用1个字节

7.Nchar 适合存放中文一个字符占用2个字节

8.二进制型 Binary和varbinary，bit代表一位的值0或1，rowversion代表数据库中唯一的8位二进制。

1.5.1.默认值

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

对于表的每个列，如果没有分配默认的值，并且保留为空白：则

1.如果设置了允许空值的选项，则将象该列中插入NULL

2.如果没有设置允许为空值，则该列将保持空白。但在用户为该列提供值之前，他们将无法保存行。

1.5.2.标识符列

对于每个表，均可创建一个博暗含系统生成的序号值得标识付列，该序号值以唯一的方式标识表中的每一行。当在表中插入行时，标识符列可自动为应用程序生成唯一的标号。

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

1.列的数据类型为不带小数的数值类型

2.在进行插入操作时，该列的值有系统按一定规律生成，不允许用空值

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

创建标识列，通常指定三个内容

类型：decimal ,int,numeric,smallint,bigint,tinyint。其中decimal和numeric，小数位数必须为零

种子：指派给表中第一行的值：默认为“1”

地增量：相邻两个标识值之间的增量，默认为1.

1.5.3.check约束

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

可以通过任何基于逻辑运算符返回TRUE或FALSE的逻辑表达式创建check约束。

举例说明：

可以通过创建check约束将age列中的值得范围限制在0-200之间的数据，以防止输入的年龄值超出正常的年龄范围，逻辑表达式为：

age >=0 and age <=200

1.6.管理表

在class 数据库中创建表，表名为“student”，其中包含列为：学生编号（int），姓名（nvarchar(50)），×××号（varchar(18)），所在班级（thinyint），成绩（tinyint）,备注（nvarchar(2000)），其中学生编号自动生成，从1开始，每增加1人则编号自动增加1，要求输入成绩的时候，其值必须为0-100，设置×××号列为主键：

为学生编号列设置标识列，标识增量和标识种子设置为“1”，如图所示：

分别新建“姓名”，“×××号”，“所在班级”，“成绩”，“备注”如图所示

由于要求输入成绩时，值必须是0-100,因此可以在成绩列设置“check约束”，

在打开的“check约束”对话框中，单击“添加”按钮，然后在“常规”文本框中输入“表达式”为“成绩>=0 and 成绩<=100”。单击确定即可

右击“×××号”，在弹出的快捷菜单中选择“设置主键”，可将该列设置为主键，主键列的左边会显示“×××钥匙”，图标

设置完成后，保存该表，

输入表的名称“student”

在对象资源管理器中查看刚刚创建的student表

编辑student表，右击表，在弹出的快捷菜单中选择“编辑前200行”命令，然后可以在表中插入、更新或删除数据了

修改表的结构

如果需要修改表结构，在表中新增或删除字段，则需要重新对表进行设计，在对象资源管理器中，右击student表，选择“设计”命令

删除表：如果需要删除表，可以右击表student，在弹出的快捷菜单中选择“删除”命令

使用T-SQL语句操作数据表

使用iNSERT语句将数据插入到表中

INSERT INTO <表名> [列名] VALUES <值列表>

INSERT INTO student (姓名，×××号，所在班级，成绩) VALUES ('百合'，'152323198201190045',2,90)

更新数据

使用UPDATE语句更新表中的数据

UPDATE <表名> set <列名 = 更新值> [WHERE <更新条件>]

update student set 成绩=‘88’ where 姓名=‘玉兰’

删除数据

使用delete语句删除表中的数据

DELETE FROM <表名> [WHERE <删除条件>]

DELETE FROM student WHERE 姓名=‘玉兰’

删除student表中所有记录

DELETE FROM student

使用truncate table 语句删除表中的数据

truncate table 语句用来删除表中的所有行，功能上类似于没有WHERE子句的DELETE语句。

TRUNCATE TABLE <表名>

要删除student表中的所有记录，可以使用如下语句

TRUNCATE TABLE student

TRUNCATE语句与DELETE语句的区别

1. TRUNCATE语句不带WHERE子句，只能将整个表数据清空，而DELETE语句可以带WHERE子句，允许按条件删除某些记录

2. TRUNCATE语句不记录事物日志，而DELETE语句无论删除多少条记录，都会记录事物日志，所以使用TRUNCATE语句删除数据后是无法通过事物日志恢复数据。

3. TRUNCATE语句删除表中所有行，标识符列会重置为0，而DELETE语句不会重置标识付列。

4. TRUNCATE语句不能用于有外键约束引用的表，这种情况下需要使用DELETE语句。