表

设计表时应考虑的因素

关系型数据库最大的特点就是数据以表的形式存储,设计关系型数据库其实就是设计表.因此表的设计尤为重要,下面会说几点考虑因素:

1. 考虑表中存储对象,绘制ER图(ER图不介绍了,请自行展开).
2. 考虑表中的列以及这些列的数据类型、精度等属性.
3. 考虑列的属性,如是否可以允许填充空值(NULL).
4. 一定要使用主键(唯一标识符),以及在何处使用主键.
5. 考虑是否使用约束、默认值、规则,以及在何处使用它们.
6. 考虑是否使用外键(来关联不同的表),以及在何处使用外键.
7. 考虑是否使用索引,在何处使用索引,以及使用什么样的索引.

表的基本特点和类型

表的特点

表具有的基本特点有: 代表实体、由行和列组成、行列顺序不重要等.下面来讨论这些特点:

• 表代表实体,有唯一的名称来确定实体.如可以使用order表来代替现实的订单实体,表是实体集合的抽象.
• 表由行和列组成,行又称为记录,列又称为字段/域.每一行都是该类实体的一个完整描述,每一列都是该类实体的一个属性.
• 行与行、列与列之间的顺序并不重要.一般而言行是按照插入的顺序存储的,但也经常在使用时进行排序,列之间的顺序是建表/增添列时确定的,但是对于同一组列,放置的位置并不影响表的结果.
• 列名必须唯一,同一个表不可出现相同的列名(就像一个事物不会有两个相同的属性一样).
• 行的唯一性通过主键来约束,常见的主键有ID,不同的行往往有不同的ID,两个完全一样的行没有存在意义.
• 在同一个架构下,表的名称也必须唯一,架构名可以成为表的唯一标识符.

表的类型

SQL server中,表可以分成以下四种类型:

1. 普通表: 又称标准表,是通常提到的作为数据库中存储数据的表,是最经常使用的表的对象,也是最重要、最基本的表.普通表又通常简称表.
2. 已分区表: 将数据水平划分为多个单元的表,这些单元可分散到数据库的多个文件组中,实现对单元中的数据并行访问.已分区表的优点在于可以可以有效管理大型表,提高对表数据的使用效率.
3. 临时表: 是临时创建的表,又分为本地临时表和全局临时表.临时表创建后可以一直存储到SQL server实例断开连接前.本地临时表仅对创建用户可见,全局临时表对所有用户和连接均可见.
4. 系统表: 和普通标的区别在于,系统表存储了SQL server服务器的配置、数据库设置、用户和表等对象的描述等系统信息.一般系统表只能由DBA来使用.

创建和修改表

创建(临时)表

我们已经说过,使用CREATE TABLE语句即可创建表,在创建表时应指定表列,注意数据类型放在列名后面.

如下是一个创建student表的示例:

USE Temp;
GO

CREATE TABLE Test.Student (					--在test架构下建一个Student表;
	studentNumber BIGINT NOT NULL,			--学号,不允许为空;
	studentName NVARCHAR(32) NOT NULL,		--姓名,不允许为空;
	gender NVARCHAR(6) NOT NULL,			--性别,不允许为空;
	birthday DATETIME NULL,					--生日,允许为空;
	email VARCHAR(32) NULL,					--电子邮箱,允许为空;
	phoneNumber VARCHAR(32) NOT NULL,		--电话号码,不允许为空;
	familyAddress NVARCHAR(128) NOT NULL,	--家庭住址,不允许为空;
	otherInfo NVARCHAR(256) NULL			--其他备注信息,允许为空;
)
GO

可以看出,建表时就可以指定其所属架构,可以指定列的数据类型和属性(是否允许为NULL)等数据.

以上的表时在建立时确定每一个列的类型和属性,此外还可以对某些列进行计算,使得该列的值不必通过输入得到,而是通过计算自动生成.具体脚本如下:

USE Temp;
GO

CREATE TABLE test.ShoppingList (									--在test架构下建一个ShoppingList表;
	itemName NVARCHAR(32) NOT NULL,									--物品名称;
	itemPrice DECIMAL(7,2) NOT NULL,								--物品单价;
	itemAmount INT NOT NULL,													--物品数量;
	partialTotalPrime AS itemPrice * itemAmount			--单品总价;
)
GO

以上列子很好地说明了刚刚提到的问题,显然总价(partialTotalPrime)这一列没有具体设定数据类型和属性,而是通过其他两列的乘积来确定具体的值.不过需要注意的是: 默认情况下,计算得来的列都是虚拟列,只能用于显示,而不具有任何物理结构.若要将此类的列物理化,可以使用PERSISTED关键字,如:

USE Temp;
GO

CREATE TABLE test.productSales (								--在test架构下建一个ShoppingList表;
	productName NVARCHAR(32) NOT NULL,							--产品名称;
	productPrice DECIMAL(7,2) NOT NULL,							--产品单价;
	amountSold INT NOT NULL,									--产品销量;
	partialTotalPrime AS productPrice * amountSold PERSISTED	--产品总价;
)
GO

如此一来,总价(partialTotalPrime)这一列就成为了表中实际存在的一部分,其值会跟着其依赖的其它列的变化而相应变化.

临时表和普通表的创建一样,只是在表名前面加’#’,一个’#‘表示本地临时表,两个’#'表示全局临时表,如:

USE Temp;
GO

CREATE TABLE ##Temp1 (			--在test架构下建一个全局临时表,所有用户可见;
	tempColumn1 INT NOT NULL 
)
GO

CREATE TABLE #Temp2 (			--在test架构下建一个本地临时表,仅创建者可见;
	tempColumn1 INT NOT NULL
)
GO

要注意: 临时表存在tempdb数据库里,这是一个系统数据库,专门用于存放临时对象.

临时表的操作和普通表完全一样,就不再多说了.

实际上,创建表时,可以为表列指定排列规则.若未指定则按照当前数据库默认排列规则.除此之外,还可以同时指定列的主键、外键以及其它约束、默认值、规则等属性,将会在后面的文章中逐渐说明这些内容.

增加/删除列

使用ALTER TABLE的以下两个子句来增加/删除列:

ALTER TABLE tb_name ADD column_name column_type [NULL/NOT NULL];
GO

ALTER TABLE tb_name DROP COLUMN column_name;
GO

其中,应注意的是:

• 添加列时,会向原有的所有行中新增列部分填充空值,此时应该向该列设置默认值(DEFAULT)属性.
• 如果未设置默认值(DEFAULT)属性,则应该设置该列允许空值(NULL).
• 如果以上条件未满足,应确保插入的列是标识符/时间戳列.
• 以上条件若不满足,且表不为空时,将会出现错误,原因是新列的初始化失败.

下面是正确示范:

USE tempdb;
GO

INSERT INTO ##Temp1 VALUES (10);	--先插入数据;
GO

SELECT * FROM ##Temp1;				--再查看表;
GO

ALTER TABLE ##Temp1 ADD tempcolumn2 INT NULL;		--设定允许空值;
GO

ALTER TABLE ##Temp1 ADD tempcolumn3 INT NULL;		--设定允许空值;
GO

SELECT * FROM ##Temp1;				--再查看表;
GO

运行结果如下:

此时若再向此表中插入不允许空值的列,就会出现错误:

USE tempdb;
GO

SELECT * FROM ##Temp1;				--先查看表;
GO

ALTER TABLE ##Temp1 ADD tempcolumn4 INT NOT NULL;		--不允许空值;
GO

SELECT * FROM ##Temp1;				--再查看表;
GO

错误信息如下:

然后我们再将一个列删除:

USE tempdb;
GO

SELECT * FROM ##Temp1;				--先查看表;
GO

ALTER TABLE ##Temp1 DROP COLUMN tempcolumn3;		--删除列;
GO

SELECT * FROM ##Temp1;				--再查看表;
GO

运行结果如下:

更改列的数据类型

可以使用如下语法更改表中列的数据类型:

ALTER TABLE tb_name ALTER COLUMN column_name new column_type;

需要注意的是: 当一列没有数据时,更改数据类型是没有限制的,否则不能随意更改,应满足:

现有数据可以隐式转换为目标数据类型的数据,才可更改为目标数据类型.

如下面的例子:

USE tempdb;
GO

SELECT * FROM ##Temp1;				--先查看表;
GO

ALTER TABLE ##Temp1 ALTER COLUMN tempcolumn2 CHAR(10) NULL;		--更改列数据类型;
UPDATE ##Temp1 SET tempcolumn2 = 'OK' WHERE tempColumn1 = 10;
GO

SELECT * FROM ##Temp1;				--再查看表;
GO

可以看到,数据类型已经从INT变为了CHAR(10),因为列是空的,因此可以直接转换.运行结果如下:

此时如果再将数据类型改回INT,则会出现错误CHAR(10)无法隐式转换为INT:

创建和修改标识符列

为了唯一区分记录,可以为其设定一个标识符,在SQL server系统中,可以使用IDENTITY或ROWGUIDCOL创建.

IDENTITY列

IDENTITY的使用语法如下:

IDENTITY(seed,increment);

其中seed是初始值,又称种子值,increment是每次的增量.这两个值默认都是1.

指定了IDENTITY的列,其值是自动增长的,无需用户干预:

USE tempdb;
GO

CREATE TABLE ##PersonInfo (					--建表;
	ID INT IDENTITY(1,1),							--指定ID为标识符,使用IDENTITY(seed,increment);
	NAME NVARCHAR(32) NOT NULL,
	SEX VARCHAR(32) NOT NULL
)
GO

INSERT INTO ##PersonInfo(NAME,SEX) VALUES ('Aaron','male');
INSERT INTO ##PersonInfo(NAME,SEX) VALUES ('Aveline','female');
GO

SELECT * FROM ##PersonInfo;				--再查看表;
GO

可以看到插入数据是没管ID列,但ID还是自动增长了:

但是删除行后,IDENTITY指定的列将不会自动缩减:

USE tempdb;
GO

INSERT INTO ##PersonInfo(NAME,SEX) VALUES ('Alex','male');
GO

DELETE ##PersonInfo WHERE NAME = 'Aaron';
GO

SELECT * FROM ##PersonInfo;				--再查看表;
GO

可以看到,删除第一行后,后面的行ID并没有因此减1:

ROWGUIDCOL列

ROWGUIDCOL列是全局唯一标识符,每个表至多设定一个ROWGUIDCOL列,且从理论上来看,不同表的ROWGUIDCOL列的值出现相同的概率微乎其微,因此在将表合并的时候,设定ROWGUIDCOL可以避免标识符重复的问题.

需要注意的是:

• ROWGUIDCOL不自动生成数值,一般采用NEWID()函数或用户手动输入来生成值.
• 使用UNIQUEIDENTIFIER类型的列才可以设定ROWGUIDCOL属性.

下面是一个示例:

USE tempdb;
GO

CREATE TABLE ##StudentInfo(
	ID UNIQUEIDENTIFIER ROWGUIDCOL DEFAULT NEWID(),	--由NEWID()给ROWGUIDCOL默认值;
	NAME VARCHAR(32) NOT NULL,
	SEX CHAR(6) NOT NULL
);
GO

INSERT INTO ##StudentInfo(NAME,SEX) VALUES ('Alex','male');
INSERT INTO ##StudentInfo(NAME,SEX) VALUES ('Aaron','male');
GO

SELECT * FROM ##StudentInfo;				--再查看表;
GO

生成的ROWGUID值是经过计算的随机值,一般认为不存在两个完全一样的ROWGUID:

如果希望知道更多关于ROWGUIDCOL的信息,可以查看以下函数:

函数名	描述
$ROWGUID	返回当前表中ROWGUIDCOL列的值
OBJECTPROPERTY	判断指定的表是否包含ROWGUIDCOL列
COLUMNPROPERTY	获取指定表中列的信息

查看表的信息

除了使用COLUMNPROPERTY函数获取表中列的信息以外,还可以使用如下两个存储过程查看表的信息:

• sp_depends: 查看指定表的依赖对象,包括依赖于表的视图、存储过程等,详情请移步这里.
• sp_help: 查看有关表的结构信息.

注: COLUMNPROPERTY函数可以查看的列信息包括: 是否为空、是否为计算得到的列、是否具有IDENTITY列，是否具有ROWGUIDCOL列等,详情请移步这里.

使用sp_help查看表的信息如下:

USE tempdb;
GO

sp_help ##PersonInfo;	--查看临时表PersonInfo的信息;
GO

删除表

删除表就是将表从数据库结构中完全移除,该过程不可逆.可以使用DROP TABLE来删除表,应注意:

• 不能使用DROP TABLE删除具有外键约束的表,应该先删除外键约束,再删除表.
• 表的所有者可删除表,当删除时,绑定在表上的规则和默认值也失效了.
• 可以一次性删除多个表,表之间必须用逗号隔开.

以下是删除表的示例:

USE tempdb;
DROP TABLE ##PersonInfo,##StudentInfo,##Temp1,#Temp2;
GO

已分区表

分区表是按照水平方式分区,将数据分布于一个数据库的不同文件组中,在对数据进行查询或更新时,分区将被看成独立的逻辑单元.创建分区的主要步骤如下:

创建分区函数,指定如何分区

使用CREATE PARTITION FUNCTION来创建分区函数,如以下示例将INT类型的数据分成5个区:

CREATE PARTITION FUNCTION salesAmountPF(INT)
	AS RANGE LEFT FOR VALUES (10,100,1000,10000);
GO

按照INT类型的数据大小,分成五个区: $\le$ 10区,(10,100]区,(100,1000]区,(1000,10000]区和$>$ 10000分区.

其中RANGE指定数据范围,LEFT用于指定各个分区的大小下限值,RIGHT为上限制.

---

创建分区方案,指定分区函数的分区在文件组上的位置

使用CREATE PARTITION SCHEME来创建分区方案,分区方案必须根据分区函数来确定文件组的数量.

以下示例是基于方才建立的分区函数的分区方案:

CREATE PARTITION SCHEME	salesAmountPS
	AS PARTTION salesAmountPF
	TO (salesFG1,salesFG2,salesFG3,salesFG4,salesFG5);
GO

分区函数的五个分区分别存放在分区方案对应的五个文件组中,即:

对INT的分区范围	存放的文件组
$\le$ 10	salesFG1
(10,100]	salesFG2
(100,1000]	salesFG3
(1000,10000]	salesFG4
$>$ 10000	salesFG5

注:

• 不一定要一个范围内的数据映射到一个文件组,可以多个范围合起来的数据共用一个文件组.
• 文件组必须是真实存在于某个数据库的,不可使用不存在的文件组.

---

使用分区方案创建分区表

创建了分区函数和分区方案后,就可以用分区方案来创建分区表了,语法如下:

CREATE TABLE partition_table_name (args) ON partition_function_name(column_name);

即,创建表时对指定的列适用分区方案,如:

CREATE TABLE salePT (
	serialID INT,
	salesAmount INT,
	salesDate DATETIME,
	salesPerson VARCHAR(32)
)
ON salesAmountPF (salesAmount);

以上样例对salesAmount列使用分区,即根据其值大小不同存放在不同的文件组中.

注意:

• 已分区表中的分区列应在数据类型、长度、精度方面和分区方案中引用分区函数的对应指标一一对应.
• 一个列至多能适用一个分区函数,而一个分区函数可以对多个列同时适用.

---

以上就是表的基本内容,下篇会接着介绍数据操纵(语言).

上一篇:Transact-SQL语言

下一篇:操纵数据