创建和管理数据表

第四章

创建和管理数据表预习检查至少说出SQLServer中三种数据类型举例说明在哪些情况下要用到检查约束什么是标识列，其作用是什么？什么是数据完整性？创建数据表的T-SQL语句是什么？

本章目标掌握SQLServer常用数据类型。能够使用图形工具SSMS和T-SQL语句创建数据表。能够为表创建主键、外键、设置默认值、建立检查约束和非空约束、创建标识列等以确保数据完整性。SQLServer常用数据类型数据表用于存储各种数据。不同类型的数据又适合不同的存储方式，从而适合不同的数据类型。首先应该了解需要存储的数据在SQLServer数据库中的对应哪种数据类型，或者说这些数据适合以何种数据类型存储。二进制数据类型二进制数据类型

Binary[(n)]n位固定长度的二进制数据。n的取值范围可从

1到

8000当数据列内容长度基本相同时使用varbinary[(n)]可变长度的二进制数据。n的取值范围可从

1到

8000当数据列内容长度不同且差别较大时使用image可变长度的二进制数据。最多

2GB用于存储文件，图片或者视频等大文件文本数据类型

Char(n)n位固定长度的非Unicode字符串。n取值范围为1到

8000存储特定长度的数据，例如11位手机号码varchar(n)最大长度为n的可变长度的非Unicode字符串。n取值范围为1到

8000存储可变长度文本。根据实际存储数据长度分配空间text可变长度的字符串。最多

2GB字符数据存储大型的非统一编码型字符数据nchar(n)固定长度的

Unicode数据。最多

4,000个字符存储特定长度的统一编码字符型数据nvarchar(n)可变长度的

Unicode数据。最多

4,000个字符存储可变长度的统一编码字符型数据ntext可变长度的Unicode数据。最多

2GB字符数据存储大型的统一编码字符型数据文本数据类型Bit数据类型Bit数据类型bit允许

0、1或

NULL用于存储只有两种可能值的数据，如真假、男女

数字数据类型tinyint允许从0到255之间的整数smallint-2^15(-32768)到2^15(32767)之间的整数int从-2^31到2^31之间的整数decimal(p,s)固定精度和比例的数字。允许从

-10^38+1到10^38-1之间的数字。p参数指示可以存储的最大位数（小数点左侧和右侧之和）。p必须是

1到

38之间的值。默认是

18。s参数指示小数点右侧小数部分存储的最大位数。s必须是

0到

p之间的值。默认是

0numeric(p,s)decimal的同义词smallmoney介于-214,748.3648和

214,748.3647之间的货币数据money从-2^63到2^63-1的货币数据float[(n)]从

-1.79E+308到

1.79E+308的浮动精度数字，n介于1-53之间，如果

1<=n<=24，则将

n视为

24。如果

25<=n<=53，则将

n视为

53。real相当于float(24)数字数据类型时间和日期数据类型日期和时间数据类型datetime表示日期和时间。从

1753年

1月

1日到

9999年

12月

31日，精度为

3.33毫秒

smalldatetime表示日期和时间。从

1900年

1月

1日到

2079年

6月

6日，精度为

1分钟

数据类型示例身份证号：可能是18位或15位，可以选择 nvarchar(18)姓名：中国名字大多三个字，且可变长度，可选

择可以选择nvarchar(10)性别：只有男女，可选择bit或char(1)家庭地址：可变长度的字符串，nvarchar(200)电话：可变长度的字符串，nvarchar(20)生日：可选择日期数据类型，datatime存款余额：可选择money数据类型照片：可选择image数据类型数据完整性的问题大多是由于设计引起的。数据存放在表中，创建表的时候，就应当保证以后数据输入是正确的——错误的数据、不符合要求的数据不允许输入。数据完整性数据完整性+=准确性可靠性数据完整性(Integrity)是指数据的准确性（Accuracy）和可靠性（Reliability）。保证数据的完整性=实施完整性约束输入的类型是否正确？——年龄必须是数字输入的格式是否正确？——身份证号码必须是18位是否在允许的范围内？——性别只能是“男”或者“女”是否存在重复输入？——学员信息输入了两次是否符合其他特定要求？——学生的出生日期必须小于入学日期……列值要求（约束）整行要求（约束）完整性问题举例数据列表一数据行表二域完整性实体完整性引用完整性自定义完整性提供四种类型的约束保证完整性四种完整性约束实体完整性河南洛阳赵可10010016新疆和田张丽10010015上海徐汇区朱朱10010014湖南长沙吴兰10010013山东青岛刘山10010012….地址姓名学号上海徐汇区朱朱0010014×约束方法：唯一约束、主键约束、标识列实体完整性(EntityIntegrity)指表中数据行的完整性，主要用于保证每条记录非空、唯一且不重复。域完整性河北承德赵亮3488888999×约束方法：限制数据类型、检查约束、外键约束、默认值、非空约束学号要求8位字符河南洛阳赵可10010016新疆和田张丽10010015上海徐汇区朱朱10010014湖南长沙吴兰10010013山东青岛刘山10010012….地址姓名学号域完整性(DomainIntegrity)又称列完整性，保证列数据具有正确的数据类型、格式和有效的取值范围、精度等。9810010022C#×约束方法：外键约束科目学号分数高数1001001288外语1001001374C#1001001267java1001001381数据库1001001698参照(引用)完整性河南洛阳赵可10010016新疆和田张丽10010015上海徐汇区朱朱10010014湖南长沙吴兰10010013山东青岛刘山10010012地址姓名学号参照完整性

(ReferentialIntegrity)

又称引用完整性，保证主表中的数据与从表中数据的一致性参照完整性的操作要求参照完整性禁止以下几种操作：(1)禁止外键列中插入主键列中没有的值；即外键的取值必须是主表中主键的有效值，或者说向从表中插入数据时只能添加主表中已经存在的关联记录的相关信息。例如银行开户只能给储户信息表中已经存在的储户开户；学生成绩表中不能添加学生信息表中不存在的学号的成绩信息。(2)禁止只修改主表中主键列的值，而不修改从表中相应的外键列的值；即不能更改主表中的值而导致从表中的记录孤立无意义。例如学生信息表中一个学生学号变了，那么学生成绩表中该学生的学号也必须相应改变。(3)禁止先从主表中删除数据行；即主表中删除数据时，必须首先删除从表中的关联记录，否则从表中会遗留大量无意义的记录。自定义完整性约束方法：规则、存储过程、触发器编号姓名信用….00192张三700288李四612333王五893000马六-10×触发器：检查信用值用户自定义完整性(User-definedIntegrity)是对列数据的约束，允许用户定义不属于以上三种完整性分类的特定的规则，包括列的值域、列的类型和列的有效规则等约束，这些是由确定关系结构时所定义的字段的属性决定的。用户编号用户姓名会员证00288张三vip00112333李四vip00290111王五vip003示例：信用不良的客户“马六”不能办理会员证。创建数据表通过三大范式规范数据库设计并绘制数据库模型图后，就可以在数据库中创建数据表。创建表的基本步骤：第一步，确定表中有哪些列；第二步，确定每列的数据类型；第三步，给表添加各种约束；第四步，创建各表之间的关系。使用图形化向导创建数据库表操作演示：创建数据库表思考：空值NULL身份证允许为空吗？姓名允许为空吗?家庭地址允许为空吗?电子邮件信息允许为空吗?性别允许为空吗?保存并命名数据表操作演示：保存数据库表在主键列输入的数值，允许为空吗?一个表的主键可以有多个列组成吗?思考-主键最少性尽量选择单个键作为主键稳定性尽量选择数值更新少的列作为主键选择主键的原则为depositor表创建主键操作演示：设置主键为depositor表添加默认值示例：为gender列设置默认值1。操作演示：设置默认值操作演示：建立检查约束为depositor表建立检查约束示例：为depositorID建立检查约束，要求该列长度只能是15位或18位。标识列允许为字符数据类型吗？如果标识列A的初始值为1，增长量为2，则输入三行数据以后，再删除两行，下次再输入数据行的时候，标识值从多少开始？思考-标识列设置标识列操作演示：设置标识列建立表间(主外键)关系操作演示：建立主外键关系数据库关系图操作演示：建立数据库关系图利用主外键的对应、图形化表示表之间的关系当主表中没有对应的记录时，不能将记录添加到子表

——成绩表中不能出现在学员信息表中不存在的学号不能更改主表中的值而导致子表中的记录孤立

——把学员信息表中的学号改变了，学员成绩表中的学号也应当随之改变子表存在与主表对应的记录，不能从主表中删除该行

——不能把有成绩的学员删除了删除主表前，先删子表

——先删学员成绩表、后删除学员信息表主外键建立后注意事项使用T-SQL语句创建数据表建表的语法CREATETABLE

表名

(

列名1数据类型列的特征，列名2数据类型列的特征，

...)列的特征：包括该列是否为空（NULL）、是否是标识列（identity）、是否有默认值(default)、是否为主键（PrimaryKey）等创建表StudentUSE

StudentInfo

--将当前数据库设置为StudentInfoGOCREATETABLE

Student

--创建学生信息表(

StudentID

nvarchar(20)NOTNULL, --学号，非空（必填）

password

nvarchar(50)NOTNULL,

--密码，非空（必填）Namenvarchar(50)NOTNULL, --姓名，非空（必填）genderbitNOTNULL,--性别，非空（必填）

GradeIdintNOTNULL,

--年级号（必填）

Phonenvarchar(50)

NOTNULL,--电话

birthday

datetimeNOTNULL,

--出生日期（必填）Address

nvarchar(255),

--地址

Emailnvarchar(50),

--邮件帐号stuIDENTITY

varchar(18)

NOTNULL

--身份证号)GO布尔值TRUE和FALSE可以转换为以下bit值：TRUE转换为1，FALSE转换为0演示示例：创建学生信息表Student创建Student表使用T-SQL语句修改数据表1、ALTERTABLE[表名]ADDCOLUMN[列名]数据类型列特征 --增加一个新列2、ALTERTABLE[表名]addCONSTRAINT[约束名] DEFAULT默认值FOR[列名] --设置默认值3、ALTERTABLE[表名]RENAMEcolumn[列名]TO[新列名] --修改列名4、ALTERTABLE[表名]RENAMETO[新表名] --修改表名5、ALTERTABLE[表名]ADDCONSTRAINT[约束名称]约束类型(列) --添加约束常用的数据表修改语句：回顾数据完整性实体完整性能够唯一标识表中的每一条记录实现方式：主键、唯一键、IDENTITY属性域完整性表中特定列数据的有效性，确保不会输入无效的值实现方式：类型、缺省值、约束、空值引用完整性维护表间数据的有效性、完整性实现方式：建立外键联系另一表的主键自定义完整性根据业务处理流程定义的特定业务规则实现方式：存储过程、触发器、规则回顾SQLServer的约束使用约束的目的是确保表中数据完整性。常用的约束类型有哪些？主键约束（PrimaryKeyConstraint）：要求主键列数据唯一，并且不允许为空唯一约束（UniqueConstraint）：要求该列数据唯一，允许为空，但只能出现一个空值检查约束（CheckConstraint）：某列取值范围限制、格式限制等默认约束（DefaultConstraint）：某列的默认值外键约束（ForeignKeyConstraint）：用于两表间建立关系，需要指定引用主表的哪列主键约束与唯一约束的区别：1.主键约束所在的列不允许有空值，唯一约束所在的列允许空值2.每个表中可以有一个主键，多个唯一键使用T-SQL语句添加约束添加约束的语法ALTERTABLE

表名

ADDCONSTRAINT

约束名约束类型具体的约束说明约束名的取名规则推荐采用：约束类型_约束列主键（PrimaryKey）约束：如PK_stuNo唯一（UniqueKey）约束：如

UQ_stuID默认（DefaultKey）约束：如DF_stuAddress检查（CheckKey）约束：如

CK_stuBornDate外键（ForeignKey）约束：如FK_stuNo

为表添加约束ALTERTABLE

Student

ADDCONSTRAINTPK_StuIDPRIMARYKEY(StudentID)ALTERTABLE

Student

ADDCONSTRAINTUQ_IdentityUNIQUE(stuIdentity)ALTERTABLE

Student

ADDCONSTRAINTDF_AddressDEFAULT('地址不详')FORAddressALTERTABLE

Student

ADDCONSTRAINTCK_birthday

CHECK(birthday>‘1980-01-01’)

ALTERTABLE

Student

ADDCONSTRAINTFK_Student_Grade

FOREIGNKEY(GradeID)REFERENCESGrade(GradeID)ALTERTABLESubject

WITHNOCHECK

--检查约束（学时必须大于等于0）

ADDCONSTRAINTCK_ClassHourCHECK(ClassHour>=0)GO添加主键约束（StudentID作为主键）添加唯一约束（因为每人的身份证号全国唯一）添加默认约束（如果地址不填，默认为“地址不详”）添加检查check约束，要求出生日期在1980-1-1之后添加外键约束（主表Grade和从表Student建立关系，关联列为GradeID）WITHNOCHECK对表中已有数据不做约束检查，因此不能保证表中数据的正确性建议：创建表结构后应立即添加约束，不要马上插入数据，以避免插入的数据不符合约束要求，保证表中数据满足约束