数据库表和数据库关系的实现

第五讲数据库表和数据库关系的实现

5．1数据类型

定义数据表的字段、声明程序中的变量时，都需要为他们设置一个数据类型。目的是指定该字段或变量所存放的数据类型，以及需要多少空间。

5．1．1整型：可以用来存放整数数据的字段或变量。有bigint、int、smallint、

两种类型，这两种类型完全相同，一般建议使用numeric。

使用numeric或decimal时，必须指明精确度（即全部有效位数）与小数点位数，例如：numeric（5，2）表示精度为5，总共位数为5位，其中3位整数及2位小数。若不指定，则默认值为numeric（18，0）。精确度可指定的范围为1~38，

取其“近似值”。例如：23456646677799变成 2.3E+13，此类数据类型有float 和real两种。注意：使用float和real类型，若数值的位数超过其有效位数的限

其中varchar及text的实际存储长度会依数据量而调整。如：varchar（10）表示最多可存储10字节，但若只填入5个字符，那么只会占用5字节。char与varchar 最多只能存储8000个字符，若数据超过此长度，请改用text类型。

在使用char及varchar时必须指定字符长度，例如char（50）、varchar（50）；

的数据与字符串类型相当类似，Unicode字符串的一个字符是用2个字节存储，而一般字符串是一个字符用1个字节存储。此类数据类型有nchar、nvarchar、ntext。

在使用nchar及nvarchar时必须指定字符长度，例如nchar（50）、nvarchar

据多用16进制表示，而且要加上0x字头）。此类数据类型有binary、varbinary 与image，其特性分别相当于字符串类型的char、varchar、text。image类型还可以用来存放word文件、excel电子表格、以及位图、GIF和JPEG文件。

使用binary及varbinary时须指定字符长度，例如binary（50）、varbinary（30）；若未指定，默认值为1。Image类型则不必指定长度。

两种。rowversion（又称timestamp）是记录数据更新的时间戳，当某条记录有变动时，该条记录的rowversion字段便会自动产生新值，此值会是整个数据的唯一值。Uniqueidentifier是全球唯一识别码，可以用来识别每一条记录唯一性。

可以用来存储前述的各种数据类型，但text、ntext，image、timestamp和sql_variant 类型的数据，若某一字段会存储不同数据类型时，即可将字段设为sql_variant 类型，此为SQL SERVER 新增的数据类型。

cursor可以用来存储查询结果的数据集，其内数据可供单条取出。table可用

数据表字段。

XML：使你可以在SQL Server 数据库中存储XML 文档和片段。XML 片段是缺少单个顶级元素的XML 实例。可以创建xml 类型的列和变量，并在其中存储XML 实例。xml 数据类型实例的存储表示形式不能超过 2 GB。

5．2数据表的建立

SQL Server 的每个数据库最多可存储20 亿个表，每个表可以有1024 列。表的行数及总大小仅受可用存储空间的限制。每行最多可以存储8,060 字节。如果创建具有varchar、nvarchar 或varbinary 列的表，并且列的字节总数超过8,060 字节，虽然仍可以创建此表，但会出现警告信息。如果试图插入超过8,060 字节的行或对行进行更新以至字节总数超过8,060，将出现错误信息并且语句执行失败。

5．2．1利用设计表窗口建立新数据表

在企业管理器中，可以用新表设计窗口设计表

（1）定义数据表的字段：

设计表的窗口分成上下两个窗口：上窗口定义字段的一般属性，下窗口定义字段的特别属性。一般属性是所有字段都具有的属性，特别属性需视字段类型而定。

（2）设置Primary key（操作示例）

注意设置复合Primary key。

（3）保存数据表

5．3自动计数和标识符的实现

标识符列中包含的值是由数据库系统自动创建的一些序号，每一个序号都唯一标识了表中的一个行。一个表中只能有一个标识符列和一个全球唯一的标识符列。

5．3．1 IDENTITY属性：

使用IDENTITY属性可以创建标识列，设置标识种子用来设置起始值。设置标识递增量，用来自动递增下个标识符。

一个表中只能有一个列可以使用IDENTITY属性定义，并且这个列必须使用decimal、int、numeric、smallint、bigint和tinyint数据类型。

标识符列不允许使用空值，并不能包含DEFAULT定义或对象。

5．3．2 全球唯一标识：GUID

IDENTITY属性只能保证标识符值在定义的表中的唯一性，不能在所有互联网计算机中唯一。所以应该使用ROWGUIDCOL属性，全球唯一标识符（uniqueidentifier）数据类型和NEWID函数。

一个表中只能有一个列可以使用ROWGUIDCOL属性定义的列。

SQL SERVER 不能自动为使用ROWGUIDCOL属性定义的列产生值，必须创建一个DEFAULT定义，并使用NEWID()函数产生全球唯一ID。

5．4在设计表窗口修改数据表

（1）插入空白字段

（2）移动数据行（拖动列）

（3）删除数据行

（4）设置数据表属性

A、名称：显示目前所编辑的数据表名称。

B、架构：显示数据表的架构信息。一般是创建此表的人或架构信息。

C、服务器名：

D、数据库名称：显示数据库的名称。

E、标识列：显示数据表中设置标识属性的字段。亦可在此栏的列表框中选取欲设置标识属性的字段，只有可设置标识属性类型的字段，才会列在列表框中）。

F、行GUID列：显示数据表中设置为GOWGUID属性的字段。亦可从此栏的列表框中选取欲设置为ROWGUID属性的字段（只有uniqueidentifier类型的字段才会列在列表框中）。

G、常规数据空间规范：

H、说明：可在此列表框中输入对于整个数据表的一些说明文字。

5．5在设计表窗口建立数据表间的关联

（1）建立关联

（2）关联属性设置

A、在创建或重新启用时检查现有数据：建立关联就是设置Foreign key约束——限制字段的值必须是来自于其所参照的数据表对应字段。

B、强制用于复制：选择此项表示即使将外键数据表复制到不同的数据库时，亦会套用此关联（Foreign key约束）

C、对INSERT和UPDATE规范：选择此项表示当新建或更新外键数据表的记录时，会套用Foreign key约束来检查数据的正确性。此项还有两个附加项：ⅰ、级联更新相关的字段：选择此项，则当主键数据表中的参照字段的值更新时，外键数据表中参照到该值的字段亦会自动更新。

ⅱ、级联删除相关的记录：选择此项，则当主键数据表中的某条记录被删除时，外键数据表中参照到该记录字段值的记录亦会自动删除。

iii、无操作。

（3）删除关联：

5．6设置约束维护数据完整性

为了减少输入错误数据的概率以及维护数据的完整性，我们可以针对字段或数据表来设置约束（Constraint）。

5．6．1约束的种类分为6种：

（1）Primary key:限制字段的值必须是唯一的，而且不能够没有数据。Primary key 主要是用来标识数据表中的每一条记录。不能有NULL值，不能有重复值。

一个表只能包含一个PRIMARY KEY 约束。

由PRIMARY KEY 约束生成的索引不能使表中的非聚集索引超过249 个，聚集索引超过 1 个。

如果没有在PRIMARY KEY 约束中指定CLUSTERED 或NONCLUSTERED，并且没有为UNIQUE 约束指定聚集索引，则将对该PRIMARY KEY 约束使用CLUSTERED。

在PRIMARY KEY 约束中定义的所有列都必须定义为NOT NULL。如果没有指定为空，加入PRIMARY KEY 约束的所有列的为空性都将设置为NOT NULL。

（2）Foreign key：限制字段的值必须是来自于其所参考的数据表。当在Foreign key 的字段中输入NULL值，或该值不在其所参照数据表的记录中时，则该条记录输入则被拒绝。

FOREIGN KEY 可以引用同一表中的其它列（自引用）。

一个表最多可包含253 个FOREIGN KEY 约束。

FOREIGN KEY 约束只能引用被引用表的PRIMARY KEY 或UNIQUE 约束中的列或被引用表上UNIQUE INDEX 中的列。

（3）NULL和NOT NULL：当数据表的某字段一定要输入数据时，可将该字段限制为NOT NULL。若是允许不输入数据，则可将该字段设置为NULL。

（4）DEFAULT：如果字段设置了DEFAULT约束，那么当该字段未输入数据时，则

DEFAULT 定义。

（5）UNIQUE：某字段不允许出现重复的字段值，也就是每个字段必须是唯一的，则可以为该字段设置UNIQUE约束。设置UNIQUE的字段中允许输入NULL值，但为保持唯一性，最多只能出现一个NULL值。

如果UNIQUE 约束中没有指定CLUSTERED 或NONCLUSTERED，则默认为NONCLUSTERED。

每个UNIQUE 约束都生成一个索引。由UNIQUE 约束生成的索引不能使表中的非聚集索引超过249 个，聚集索引超过 1 个。

（6）CHECK：CHECK约束可以用来限制字段值是否在所允许的范围内。CHECK 的内容是一个逻辑表达式。一个字段可以设置多个CHECK约束，而一个CHECK 约束可以针对多个字段做约束。

注意：UNIQUE字段允许输入NULL值，但Primary key字段不允许；一个数据表中可以定义多个UNIQUE条件约束，但只能定义一个Primary key条件约束。

5．7在设计窗口中设置约束

5．7．1设置CHECK约束

（1）创建中检查现存数据

（2）对复制强制索引

5．6．2设置UNIQUE约束

5．7 在企业管理器中删除数据表

如果不想再使用某个表时，可以把它删除，在删除前，必须先删除该表的关联性，否则无法删除该表。

5．7．1查看数据表的相关性

5．7．2删除数据表

5．8数据库设计窗口与关系图对象

数据库设计窗口亦是企业管理器提供的一项可视化工具。它是建立数据库关系对象的场所。所谓关系图对象，是以“图形”方式来显示数据库内的数据表以及关联。

5．8．1用建立数据库关系图向导添加数据表

假若要添加的数据表已经与其他数据表建好关联，我们可以只选取关联中的一个数据表，然后选择自动添加相关的表选项，并设好级别数，当按“添加”按钮时，向导会自动挑出其他有关的数据表一并移入要添加到关系图的列表框中。5．8．2在数据库关系设计窗口添加数据表

（1）添加表

（2）按“排列表”调整数据设计窗口做适当的排列。

（3）调整比例

5．8．3将数据库表移出数据库关系设计窗口

5．8．4在数据库关系设计窗口中建立新数据表——定义字段，设置主键，保存。5．8．5数据库表及字段的属性设置

5．8．6改变显示模式

5．8．7建立关联

数据库关系设计窗口最大的优点是可以用“可视化”的方式来建立数据表间的关联。可以用鼠标指在关联线条上，会出现关联的工具提示。也可以执行“显

示关系标签”命令。

5．8．8删除关联

5．8．9设置CHECK与UNIQUE约束。

5．9用CREATE TABLE 语句建立数据表

5．9．1 CREATE TABLE语句语法：

CREATE TABLE

[ database_name.[ owner ] .| owner.] table_name

( { < column_definition >

| column_name AS computed_column_expression

| < table_constraint > ::= [ CONSTRAINT constraint_name ] }

| [ { PRIMARY KEY | UNIQUE } [ ,...n ]

)

[ ON { filegroup | DEFAULT } ]

[ TEXTIMAGE_ON { filegroup | DEFAULT } ]

5．9．2设置数据库表名称

一个完整的数据表名可以包含4个部分：

（1）Server_name：服务器名。

（1）database_name：数据表所在数据库名称。若省略，则为目前连接的数据库。（2）owner：数据表的所有者名称。若省略，则默认为数据表创建者所在目前数据库中的用户名称。如果是以db_owner或db_ddladmin、db_securityadmin这些角色的成员登陆，则可以指定自己以外的用户名称。

（3）table_name：新建数据表名称。

例：

CREATE TALBE Northwind . dbo . mytable1

CREATE TABLE Northwind . . mytalbe1

CREATE TABLE mytalbe1

5．9．3定义字段属性语法格式：

< column_definition > ::= { column_name data_type }

[ COLLATE < collation_name > ]

[ [ DEFAULT constant_expression ]

| [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ] ]

[ ROWGUIDCOL]

[ < column_constraint > ] [ ...n ]

（1）column_name data_type：定义字段名称以及数据类型（包括长度）

（2）COLLATE < collation_name >：设置字段要套用的排序规则名称，若省略，则会使用数据库的默认值。

（3）[ DEFAULT constant_expression：设置字段的默认值属性。

（4）IDENTITY（seed，increment）：设置标识属性，让字段值自动编号。

（5）ROWGUIDCOL：由SQL SERVER自动为字段产生全局唯一的字段值，只有uniqueidentifier字段类型可以使用这项属性。

（6）< column_constraint >：针对单一字段设置约束。详细语法稍后介绍。

例：

Create table cus

(

客户编号int IDENTITY（3,3）,

客户名称varchar(30),

优惠折扣numeric(3,2) DEFAULT 0.8,

Guid uniqueidentifer default newid())

)

5．9．4设置字段约束

< column_constraint > ::= [ CONSTRAINT constraint_name ]

{ [ NULL | NOT NULL ]

| [ { PRIMARY KEY | UNIQUE }

[ CLUSTERED | NONCLUSTERED ]

[ WITH FILLFACTOR = fillfactor ]

[ON {filegroup | DEFAULT} ] ]

]

| [ [ FOREIGN KEY ] ——设置FOREIGN KEY约束

REFERENCES ref_table [ ( ref_column ) ]

[ ON DELETE { CASCADE | NO ACTION } ]

[ ON UPDATE { CASCADE | NO ACTION } ]

[ NOT FOR REPLICATION ]

]

| CHECK [ NOT FOR REPLICATION ] ——设置CHECK约束

( logical_expression )

}

（1）CONSTRAINT constraint_name：参数可自行为约束取名，若省略，则SQL SERVER会自动为设置的约束取名。

（2）NULL和NOT NULL：定义好字段的属性后，直接把NULL或NOT NULL 加在后面就可以了。

（3）PRIMARY KEY和UNIQUE约束：要设置PRIMARY KEY和UNIQUE约束也是直接把参数加在字段属性后即可。

例：

(

客户编号int NOT NULL PRIMARY KEY,

联系人char(10) NOT NULL UNIQUE

)

（4）FOREIGN KEY约束：是设置数据表之间的关联。

例：

Create table 定单01

(定单编号int PRIMARY KEY，

下单日期datetime，

客户编号int CONSTRAINT FK_定单与客户01

FOREIGN KEY

REFERENCES 客户(客户编号))

（5）CHECK约束：

例：

(

价格smallmoney CONSTRAINT CK_价格

CHECK (价格>1 AND价格<3000 ) )

)

5．9．5设置数据表约束

要同时针对多个字段设置约束。

< table_constraint > ::= [ CONSTRAINT constraint_name ] { [ { PRIMARY KEY | UNIQUE }

[ CLUSTERED | NONCLUSTERED ]

{ ( column [ ASC | DESC ] [ ,...n ] ) }

[ WITH FILLFACTOR = fillfactor ]

[ ON { filegroup | DEFAULT } ]

]

| FOREIGN KEY

[ ( column [ ,...n ] ) ]

REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]

[ ON DELETE { CASCADE | NO ACTION } ]

[ ON UPDATE { CASCADE | NO ACTION } ]

[ NOT FOR REPLICATION ]

| CHECK [ NOT FOR REPLICATION ]

( search_conditions )

}

例：

CREATE TALBE 客户

(

客户编号int IDENTITY PRIMARY KEY,

身份证号char(10) NOT NULL UNIQUE,

年龄int CHECK(年龄> 0) DEFAULT 25,

地址varchar(50),

电话char(12),

杂志编号int

REFERENCES 杂志种类(杂志编号)，

定单编号int NOT NULL,

FOREIGN KEY (杂志编号，定单编号)

REFERENCES 杂志定户(杂志编号，订户编号)，CHECK (地址is not null or 电话is not null)

)

5．9．6指定文件组

（1）ON {filegroup | DEFAULT}：指定数据表的数据（Text及Image类型字段外）要存放的文件组。若设置或省略设置ON DEFAULT，则表示放在默认文件组中。（2）TEXTIMAGE_ON { filegroup | DEFAULT }：指定Text及Image类型字段数据要存放的文件组。若省略或设置为TEXTIMAGE_ON DEFAULT，那么也表示放在默认文件组中。

5．9．7建立计算列

计算列（Computed column）是一种虚拟字段，此字段的值是由同一数据表中的其他字段计算出来的，其字段内实际上并没有存放数据。

例：

CREATE TABLE 估价

(

编号int IDENTITY,

单价numeric(5,1),

数量int,

总价AS 单价*数量

)

计算字段不可以设置DEFAULT、FOREIGN KEY、NOT NULL及CHECK 约束。若计算字段的值具有唯一性，并且不会随意变动，则可以设置PRIMARY KEY或UNIQUE。

5．10用ALTER TABLE 语句修改数据表

5．10．1 语法格式如下：

ALTER TABLE table

{ [ ALTER COLUMN column_name

{ new_data_type [ ( precision [ , scale ] ) ]

[ COLLATE < collation_name > ]

[ NULL | NOT NULL ]

| {ADD | DROP } ROWGUIDCOL }

]

| ADD

{ [ < column_definition > ]

| column_name AS computed_column_expression

} [ ,...n ]

| [ WITH CHECK | WITH NOCHECK ] ADD

{ < table_constraint > } [ ,...n ]

| DROP

{ [ CONSTRAINT ] constraint_name

| COLUMN column } [ ,...n ]

| { CHECK | NOCHECK } CONSTRAINT

{ ALL | constraint_name [ ,...n ] }

| { ENABLE | DISABLE } TRIGGER

{ ALL | trigger_name [ ,...n ] }

}

5．10．2 修改字段属性

修改字段属性的语法如下：

{ [ ALTER COLUMN column_name

{ new_data_type [ ( precision [ , scale ] ) ]

[ COLLATE < collation_name > ]

[ NULL | NOT NULL ]

| {ADD | DROP } ROWGUIDCOL }

（1）column_name：指定欲修改属性的字段名称。

注意下列字段不能在此处修改字段的属性：

A、text、image、ntext、timestamp类型字段。

B、已具备为GowGuid属性的字段。

C、计算字段或计算字段用到的字段。

D、用于PRIMARY KEY或FOREIGN KEY约束中的字段。

E、用户CHECK或UNIQUE约束中的字段，但可改变这些字段的长度(前提是该字段为可变长度的字段)。

F、用于DEFAULT中的字段，但若没有变更此字段的数据类型，则可更改字段的长度、精确度及小数点位数。

（2）new_data_type [ ( precision [ , scale ] )：为字段指定新的数据类型，若是decimal和numeric类型，则还需指定精度（precision）和小数点位数（scale）。注意事项：

A、新数据类型必须与原来数据类型兼容，即可以一进行隐含式转换。

B、不能改变timestamp类型。

C、若变更属性的字段原就具备标识属性，新数据类型也必须要能够支持标识属性。

（3）{ADD | DROP } ROWGUIDCOL：为字段加上或删除RowGuid属性；一个表中也只能有一个具有RowGuid属性的字段。

（4）在建立新的PRIMARY KEY、CHECK或FOREIGN KEY之前，必须先把当前的PRIMARY KEY、CHECK或FOREIGN KEY删除。

（5）在删除PRIMARY KEY之前，必须先把FOREIGN KEY删除。5．10．3新建字段/计算字段

ALTER TABLE table

ADD

{ [ < column_definition > ]

| column_name AS computed_column_expression

} [ ,...n ]

< column_definition > ::=

{ column_name data_type }

[ [ DEFAULT constant_expression ] [ WITH V ALUES ]

| [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ]

]

[ ROWGUIDCOL ]

[ COLLATE < collation_name > ]

[ < column_constraint > ] [ ...n ]

ALTER TABLE语句新建字段/计算字段的语法，除了多个ADD参数外，其他和建立数据表时定义字段属性几乎一模一样。唯一不同的地方在于字段定义的语法中多了个WITH V ALUES参数。新建字段若有设置DEFAULT（默认值属性），才可以使用WITH V ALUES参数，作用是在数据表旧

例：

ALTER TABLE 客户A

ADD

类别编号int

DEFAULT 1 WITH V ALUES

CONSTRAINT FK_类别编号

FOREIGN KEY

REFERENCES 客户类别(类别编号)

5．10．4新建数据表约束

ALTER TALBE table

[ WITH CHECK | WITH NOCHECK ] ADD

{ < table_constraint > } [ ,...n ]

< table_constraint > ::=

[ CONSTRAINT constraint_name ]

{ [ { PRIMARY KEY | UNIQUE }

[ CLUSTERED | NONCLUSTERED ]

{ ( column [ ,...n ] ) }

[ WITH FILLFACTOR = fillfactor ]

[ ON { filegroup | DEFAULT } ]

]

| FOREIGN KEY

[ ( column [ ,...n ] ) ]

REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]

[ ON DELETE { CASCADE | NO ACTION } ]

[ ON UPDATE { CASCADE | NO ACTION } ]

[ NOT FOR REPLICATION ]

| DEFAULT constant_expression

[ FOR column ] [ WITH V ALUES ]

| CHECK [ NOT FOR REPLICATION ]

( search_conditions )

}

（1）WITH CHECK：表示要套用新建的数据表约束去检查旧有的记录。

（2）WITH NOCHECK：表示不用新建的数据表约束去检查旧有的记录。例：

ALTER TABLE 订购项目A

WITH CHECK ADD

CONSTRAINT PK_订购项目A

PRIMARY KEY （订单编号,项目编号）

5．10．5删除约束/字段

ALTER TABLE table

DROP

{ [ CONSTRAINT ] constraint_name | COLUMN_column} [,…n]

例：

ALTER TABLE 订购项目A

DROP CONSTRAINT PK_订购项目A

ALTER TALBE 订购项目A

DROP COLUMN 订单编号，项目编号

5．10．6激活/关闭约束

ALTER TALBE语句有个特殊的功能，就是能暂停某约束的作用，然后再重新激活。这项功能只能用在FOREIGN KEY和CHECK约束上。

ALTER TALBE table

{ CHECK | NOCHECK } CONSTRAINT

{ ALL | constraint_name[,…n] }

（1）CHECK：表示激活约束。

（2）NOCHECK：表示关闭约束。

（3）ALL：指数据表中所有的FOREIGN KEY 和CHECK约束。

例：

ALTER TABLE 客户A

NOCHECK CONSTRAINT FK_类别编号

5．10．7更改字段与数据表的名称

ALTER TABLE语句没有更改字段字段名称及数据表名称的功能。若想要变更字段名称或数据表名称，则要使用sp_rename存储过程。Sp_rename可以用来改变数据库中所有“用户”建立的对象名。

Sp_rename ‘object_name’ , ‘new_name’ , [, ‘object_type’]

EXEC sp_rename '订单A','订购单A'

EXEC sp_rename ‘客户A.地址’,’通讯处’,’COLUMN’

5．11用DROP TABLE语句删除数据表

语法格式如下

DROP TABLE table_name

例：

DROP TABLE 书籍1

5．12临时数据表

临时数据表（Temporary table）是一种因暂时需求而产生的数据表，它和一般数据表（称Permanent table）的不同点在于：

（1）临时数据表存放在tempdb数据库中

（2）当我们用完临时数据表并关闭连接后，临时数据表会被自动删除。

临时数据表的种类

（1）局部临时数据表：局部临时数据表的名称须以#开头，只有建立它的人可以使用，当该用户断开连接后，SQL SERVER自动把它删除。

（2）全局临时数据表：全局临时数据表的名称必须以##开头，所有的用户都可以使用它。当建立他的用户断开连接后，其他用户即无法再打开此数据表，但仍在使用中的用户可继续使用，直到所有使用它的指令都结束后，SQL SERVER 会自动把它删除。

例：

CREATE TABLE #订单(编号int,数量int)

CREATE TABLE ##客户(编号int,姓名char(10))