数据库表和数据库关系的实现.docx
《数据库表和数据库关系的实现.docx》由会员分享，可在线阅读，更多相关《数据库表和数据库关系的实现.docx（30页珍藏版）》请在冰点文库上搜索。
数据库表和数据库关系的实现
第五讲数据库表和数据库关系的实现
5．1数据类型
定义数据表的字段、声明程序中的变量时，都需要为他们设置一个数据类型。
目的是指定该字段或变量所存放的数据类型，以及需要多少空间。
5．1．1整型：
可以用来存放整数数据的字段或变量。
有bigint、int、smallint、tinyint、bit五种类型。
其中bigint为SQLSERVER2000新增的类型。
数据类型
数据范围
长度
binint
-263~263-1
8字节
int
-231~231-1
4字节
smallint
-215~215-1
2字节
tinyint
0~28-1
1字节
bit
0、1、NULL
实际使用1bit，但会占用1字节。
例如有1~8个bit字段便占用1byte，9~16个bit字段便占2字节，以此类推。
5．1．2精确位数型：
可以用来定义带有小数部分的数值。
有numeric与decimal两种类型，这两种类型完全相同，一般建议使用numeric。
使用numeric或decimal时，必须指明精确度（即全部有效位数）与小数点位数，例如：
numeric（5，2）表示精度为5，总共位数为5位，其中3位整数及2位小数。
若不指定，则默认值为numeric（18，0）。
精确度可指定的范围为1~38，小数点位数可指定的范围最少为0，最多不可超过精确度。
数据类型
数据范围
长度
numeric
-1038+1~1038-1
识精确度（即全部有效位数）而定：
1~9位数使用5字节
10~19位数使用9字节
20~28位数使用13字节
29~38位数使用17字节
Decimal
-1038+1~1038-1
与numeric一样
5．1．3近似浮点数型：
当数值非常大或非常小时，就用近似浮点数数据类型来取其“近似值”。
例如：
23456646677799变成2.3E+13，此类数据类型有float和real两种。
注意：
使用float和real类型，若数值的位数超过其有效位数的限制时，则存储的数值会因四舍五入而产生误差。
数据类型
数据范围
长度
Float
-1.79E+308~1.79E+308
最多可表示17位数
8字节
Real
-3.40E+38~3.40+3
最多可表示7位
4字节
5．1．4日期时间型：
用来存储日期与时间数据，如“2004-2-2823:
20:
30”，其数据类型依可存储的范围与精确度分为datetime与smalldatetime两种。
数据类型
数据范围
长度
datetime
1753/1/1~9999/12/31
8字节
smalldatetime
1900/1/1~2079/6/6
4字节
5．1．5字符串型：
用来存放字符型数据，有三种数据类型：
char、varchar、text，其中varchar及text的实际存储长度会依数据量而调整。
如：
varchar（10）表示最多可存储10字节，但若只填入5个字符，那么只会占用5字节。
char与varchar最多只能存储8000个字符，若数据超过此长度，请改用text类型。
在使用char及varchar时必须指定字符长度，例如char（50）、varchar（50）；若未指定，默认值是1。
Text类型不必指定长度。
数据类型
数据范围
长度
Char
1~8000个字符
1个字符1字节，为固定长度，未填满数据的部分会自动补上空格字符。
varchar
1~8000个字符
1个字符1个字节，存储多少字符即占多少空间
Text
1~231-1个字符
1个字符1个字节，存储多少字符即占多少空间，最大可存储2GB。
varchar（max）
2^31-1个字节的数据
大值数据类型，可以使用大值数据类型来存储最大为2^31-1个字节的数据
5．1．6Unicode字符串型：
Unicode是双字节字符编码标准。
Unicode字符串类的数据与字符串类型相当类似，Unicode字符串的一个字符是用2个字节存储，而一般字符串是一个字符用1个字节存储。
此类数据类型有nchar、nvarchar、ntext。
在使用nchar及nvarchar时必须指定字符长度，例如nchar（50）、nvarchar（50）；若未指定，默认值是2。
ntext类型不必指定长度。
数据类型
数据范围
长度
nchar
1~4000个字符
1个字符2字节，为固定长度，未填满数据的部分会自动补上空格字符。
nvarchar
1~4000个字符
1个字符2个字节，存储多少字符即占多少空间
Ntext
1~230-1个字符
1个字符2个字节，存储多少字符即占多少空间，最大可存储2GB。
nvarchar（max）
2^31-1个字节的数据
大值数据类型，可以使用大值数据类型来存储最大为2^31-1个字节的数据
5．1．7二进制字符串型：
用来定义二进制（binary）数据，如0x5F（二进制数据多用16进制表示，而且要加上0x字头）。
此类数据类型有binary、varbinary与image，其特性分别相当于字符串类型的char、varchar、text。
image类型还可以用来存放word文件、excel电子表格、以及位图、GIF和JPEG文件。
使用binary及varbinary时须指定字符长度，例如binary（50）、varbinary（30）；若未指定，默认值为1。
Image类型则不必指定长度。
数据类型
数据范围
长度
binary
1~8000个字符
存储时，需另外增加4字节。
为固定长度，未填满数据的部分会自动补上0x00
varbinary
1~8000个字符
存储时，需另外增加4字节。
为变长，输入多少数据即占多少空间
Image
1~231-1个字符
同varbinary，最大可存储2GB。
varbinary（max）
2^31-1个字节的数据
大值数据类型，可以使用大值数据类型来存储最大为2^31-1个字节的数据
5．1．8货币型：
用来定义货币数据，如$123.0000，此类数据类型有money，smallmoney两种。
数据类型
数据范围
长度
money
-263~263-1
8字节
smallmoney
-231~231-1
4字节
5．1．9标记：
此类数据类型包括rowversion（又称timestamp）与uniqueidentifier两种。
rowversion（又称timestamp）是记录数据更新的时间戳，当某条记录有变动时，该条记录的rowversion字段便会自动产生新值，此值会是整个数据的唯一值。
Uniqueidentifier是全球唯一识别码，可以用来识别每一条记录唯一性。
GUID
数据类型
数据范围
长度
Timestamp
8字节的16进制数
如：
0x0000000000000130
8字节
uniqueidentifier
16字节的16进制数
如：
{9892E630-A2DF-4E92-8195-4E47EA74093A}
16字节
5．1．10其他：
被归类在其他的数据类型有sql_variant、cursor和table。
sql_variant可以用来存储前述的各种数据类型，但text、ntext，image、timestamp和sql_variant类型的数据，若某一字段会存储不同数据类型时，即可将字段设为sql_variant类型，此为SQLSERVER新增的数据类型。
cursor可以用来存储查询结果的数据集，其内数据可供单条取出。
table可用来存储临时的一组表格形式的数据。
数据类型
数据范围
长度
sql_variant
可以存放各种数据类型的数据，除text、ntex，image、timestamp和sql_variant类型
视存储的数据类型而定，最大长度为8016位（bit）
cursor
查询结果的数据集
Table
表格形式的数据集
注意：
cursor和table数据类型只能用来在程序中声明变量类型，不能用来定义数据表字段。
XML：
使你可以在SQLServer数据库中存储XML文档和片段。
XML片段是缺少单个顶级元素的XML实例。
可以创建xml类型的列和变量，并在其中存储XML实例。
xml数据类型实例的存储表示形式不能超过2GB。
5．2数据表的建立
SQLServer的每个数据库最多可存储20亿个表，每个表可以有1024列。
表的行数及总大小仅受可用存储空间的限制。
每行最多可以存储8,060字节。
如果创建具有varchar、nvarchar或varbinary列的表，并且列的字节总数超过8,060字节，虽然仍可以创建此表，但会出现警告信息。
如果试图插入超过8,060字节的行或对行进行更新以至字节总数超过8,060，将出现错误信息并且语句执行失败。
5．2．1利用设计表窗口建立新数据表
在企业管理器中，可以用新表设计窗口设计表
（1）定义数据表的字段：
设计表的窗口分成上下两个窗口：
上窗口定义字段的一般属性，下窗口定义字段的特别属性。
一般属性是所有字段都具有的属性，特别属性需视字段类型而定。
下表是各属性的说明：
属性
说明
列名
设置字段名称
数据类型
设置字段的数据类型。
将插入点移到此列时会出现下拉箭头按钮，按下该按钮即会显示数据类型列表框，让用户从中选择所需要的数据类型。
允许空
若字段允许输入NULL值，则不需要选择此项；若不允许，则取消此项。
描述
输入字段的补充说明。
每个字段皆可设置这个属性。
默认值
设置字段的默认值。
新建数据时，如果没有给予字段值，便填入默认值。
除了timestamp类型外，其他类型的字段皆可设置此属性。
精确度
设置字段的位数。
只有decimal和numeric类型可设置这项属性。
bigint、int、smallint、tinyint、money、smallmoney、float、real的精确度都是固定的。
小数位数
设置字段的小数位数。
只有decimal和numeric类型可设置这项属性。
标识
设置是否让字段自动编号。
有3个选择：
否表示不自动编号、“是”和“是（不用于复制）”都表示字段会根据标识种子和标识增量的设置自动编号产生字段值，但当由复制方式来输入数据时，后者不会自动编号。
只有bigint、int、smallint、tinyint、decimal和numeric可设置此项属性。
标识种子
设置自动编号的起始值，默认为1。
当标识属性为“是”或“是（不用于复制）”时才能设置此项。
标识递增量
设置自动编号的递增值，默认为1。
当标识属性为“是”或“是（不用于复制）”时才能设置此项。
RowGuid
设置是否由SQLSERVER自动产生全局唯一字段值。
若设置为是，则默认值属性会自动设置为newid（），由此函数产生全局唯一识别码。
只有uniqueidentifier类型的字段才可以设置这项属性。
公式
设置计算字段的表达式
排序规则
设置字段要套用的排序规则名称及排序选项，默认时使用数据库默认设置。
只有char、varchar、text、nchar、nvarchar、ntext类型可设置这项属性。
一个数据表中，最多只能有1个标识符属性字段及1个RowGuid属性字段。
（2）设置Primarykey（操作示例）
注意设置复合Primarykey。
（3）保存数据表
5．3自动计数和标识符的实现
标识符列中包含的值是由数据库系统自动创建的一些序号，每一个序号都唯一标识了表中的一个行。
一个表中只能有一个标识符列和一个全球唯一的标识符列。
5．3．1IDENTITY属性：
使用IDENTITY属性可以创建标识列，设置标识种子用来设置起始值。
设置标识递增量，用来自动递增下个标识符。
一个表中只能有一个列可以使用IDENTITY属性定义，并且这个列必须使用decimal、int、numeric、smallint、bigint和tinyint数据类型。
标识符列不允许使用空值，并不能包含DEFAULT定义或对象。
5．3．2全球唯一标识：
GUID
IDENTITY属性只能保证标识符值在定义的表中的唯一性，不能在所有互联网计算机中唯一。
所以应该使用ROWGUIDCOL属性，全球唯一标识符（uniqueidentifier）数据类型和NEWID函数。
一个表中只能有一个列可以使用ROWGUIDCOL属性定义的列。
SQLSERVER不能自动为使用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、在创建或重新启用时检查现有数据：
建立关联就是设置Foreignkey约束——限制字段的值必须是来自于其所参照的数据表对应字段。
B、强制用于复制：
选择此项表示即使将外键数据表复制到不同的数据库时，亦会套用此关联（Foreignkey约束）
C、对INSERT和UPDATE规范：
选择此项表示当新建或更新外键数据表的记录时，会套用Foreignkey约束来检查数据的正确性。
此项还有两个附加项：
ⅰ、级联更新相关的字段：
选择此项，则当主键数据表中的参照字段的值更新时，外键数据表中参照到该值的字段亦会自动更新。
ⅱ、级联删除相关的记录：
选择此项，则当主键数据表中的某条记录被删除时，外键数据表中参照到该记录字段值的记录亦会自动删除。
iii、无操作。
（3）删除关联：
5．6设置约束维护数据完整性
为了减少输入错误数据的概率以及维护数据的完整性，我们可以针对字段或数据表来设置约束（Constraint）。
5．6．1约束的种类分为6种：
（1）Primarykey:
限制字段的值必须是唯一的，而且不能够没有数据。
Primarykey主要是用来标识数据表中的每一条记录。
不能有NULL值，不能有重复值。
一个表只能包含一个PRIMARYKEY约束。
由PRIMARYKEY约束生成的索引不能使表中的非聚集索引超过249个，聚集索引超过1个。
如果没有在PRIMARYKEY约束中指定CLUSTERED或NONCLUSTERED，并且没有为UNIQUE约束指定聚集索引，则将对该PRIMARYKEY约束使用CLUSTERED。
在PRIMARYKEY约束中定义的所有列都必须定义为NOTNULL。
如果没有指定为空，加入PRIMARYKEY约束的所有列的为空性都将设置为NOTNULL。
（2）Foreignkey：
限制字段的值必须是来自于其所参考的数据表。
当在Foreignkey的字段中输入NULL值，或该值不在其所参照数据表的记录中时，则该条记录输入则被拒绝。
FOREIGNKEY可以引用同一表中的其它列（自引用）。
一个表最多可包含253个FOREIGNKEY约束。
FOREIGNKEY约束只能引用被引用表的PRIMARYKEY或UNIQUE约束中的列或被引用表上UNIQUEINDEX中的列。
（3）NULL和NOTNULL：
当数据表的某字段一定要输入数据时，可将该字段限制为NOTNULL。
若是允许不输入数据，则可将该字段设置为NULL。
（4）DEFAULT：
如果字段设置了DEFAULT约束，那么当该字段未输入数据时，则会自动将默认值填入字段中。
SQL-92niladic函数
返回的值
CURRENT_TIMESTAMP
当前日期和时间。
CURRENT_USER
执行插入操作的用户名。
SESSION_USER
执行插入操作的用户名。
SYSTEM_USER
执行插入操作的用户名。
USER
执行插入操作的用户名。
不能在数据类型为timestamp的列或具有IDENTITY属性的列上创建DEFAULT定义。
（5）UNIQUE：
某字段不允许出现重复的字段值，也就是每个字段必须是唯一的，则可以为该字段设置UNIQUE约束。
设置UNIQUE的字段中允许输入NULL值，但为保持唯一性，最多只能出现一个NULL值。
如果UNIQUE约束中没有指定CLUSTERED或NONCLUSTERED，则默认为NONCLUSTERED。
每个UNIQUE约束都生成一个索引。
由UNIQUE约束生成的索引不能使表中的非聚集索引超过249个，聚集索引超过1个。
（6）CHECK：
CHECK约束可以用来限制字段值是否在所允许的范围内。
CHECK的内容是一个逻辑表达式。
一个字段可以设置多个CHECK约束，而一个CHECK约束可以针对多个字段做约束。
注意：
UNIQUE字段允许输入NULL值，但Primarykey字段不允许；一个数据表中可以定义多个UNIQUE条件约束，但只能定义一个Primarykey条件约束。
5．6．2维护数据完整性的对策
完整性类型
可用的对策
实体完整性（EntityIntegrity），维持每条记录的唯一性
Primarykey
UNIQUE
GUID
IDENTITY（标识属性）
区域完整性（DomainIntegrity），维持字段维数据的正确性
DEFAULT
Foreignkey
CHECK
NOTNULL
参考完整性（ReferentialIntegrity），数据表间关联的完整性
Foreignkey
CHECK
用户自定义完整性（Userdefined）
所有约束
存储过程
触发器
5．7在设计窗口中设置约束
5．7．1设置CHECK约束
（1）创建中检查现存数据
（2）对复制强制索引
（3）对INSERT和UPDATE强制约束
通配符
描述
示例
%
包含零个或更多字符的任意字符串。
WHEREtitleLIKE'%computer%'将查找处于书名任意位置的包含单词computer的所有书名。
_（下划线）
任何单个字符。
WHEREau_fnameLIKE'_ean'将查找以ean结尾的所有4个字母的名字（Dean、Sean等）。
[]
指定范围（[a-f]）或集合（[abcdef]）中的任何单个字符。
WHEREau_lnameLIKE'[C-P]arsen'将查找以arsen结尾且以介于C与P之间的任何单个字符开始的作者姓氏，例如，Carsen、Larsen、Karsen等。
[^]
不属于指定范围（[a-f]）或集合（[abcdef]）的任何单个字符。
WHEREau_lnameLIKE'de[^l]%'将查找以de开始且其后的字母不为l的所有作者的姓氏。
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用CREATETABLE语句建立数据表
5．9．1CREATETABLE语句语法：
CREATETABLE
[database_name.[owner].|owner.]table_name
（{
|column_nameAScomputed_column_expression
|:
:
=[CONSTRAINTconstraint_name]}
|[{PRIMARYKEY|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：
新建数据表名称。
例：
CREATETALBENorthwind.dbo.mytable1
CREATETABLENorthwind..mytalbe1
CREATETABLEmytalbe1
5．9．3定义字段属性语法格式：
:
:
={column_namedata_type}
[COLLATE]
[[DEFAUL
升级会员 