数据表的创建和管理

数据表的创建和管理第一页，共三十九页，编辑于2023年，星期三学习要点：表的概念表的创建、修改和删除添加、更新与删除表中的数据数据完整性的概念及实施方法第二页，共三十九页，编辑于2023年，星期三3.1表的概念表是数据的集合，是用来存储数据和操作数据的逻辑结构。表是由行和列组成的，行被称为记录，是组织数据的单位；列被称为字段，每一列表示记录的一个属性。在特定表中，列名必须是唯一的，但相同的列名可以在数据库中的不同表中使用。SQLServer中的每个表中最多允许有1024列，每行最多允许有8060字节的用户数据。行和列的次序是任意的。第三页，共三十九页，编辑于2023年，星期三3.2数据类型第一大类:整数数据

bit:bit数据类型代表0,1或NULL,就是表true,false.

占用1byte.

int:以4个字节来存储正负数.可存储范围为:-

（-2147483648至2147483647）.

smallint:以2个字节来存储正负数.存储范围为:-

（-32768至32767）

tinyint:

是最小的整数类型,仅用1字节,范围:0至

255

bigint：以8个字节存储，是SQLServer2000引入的全新的数据类型，可以存储范围：（-263到263-1）第四页，共三十九页，编辑于2023年，星期三第二大类:精确数值数据

numeric:表示的数字可以达到38位,存储数据时所用的字节数目会随着使用权用位数的多少变化.格式写为numeric(p，s)

例如：numeric(10，4)，表示共有10位数，其中整数6位，小数4位。

decimal:和numeric类似。区别：numeric类型的列可以带有IDENTITY关键字（唯一标识符）。第五页，共三十九页，编辑于2023年，星期三第三大类:近似浮点数值数据

float:用8个字节来存储数据.最多可为53位.范围为:-1.79E+308至1.79E+308.

real:位数为24,用4个字节,数字范围:-3.04E+38至3.04E+38第四大类:日期时间数据

datatime:表示时间范围可以表示从1753/1/1至9999/12/31,时间可以表示到3.33/1000秒.使用8个字节.smalldatetime:表示时间范围可以表示从1900/1/1至2079/12/31.使用4个字节.第六页，共三十九页，编辑于2023年，星期三第五大类:字符串数据

char[（n）]:长度是设定的,最短为1字节,最长为8000个字节.不足的长度会用空白补上.

例如：char（10）

Varchar[（n）]:变长字符型数据,最短为1字节,最长为8000个字节,尾部的空白会去掉.

text:长专门用于存储数量庞大的变长字符数据，,最长可以存放2G（231-1个字符）的数据.第七页，共三十九页，编辑于2023年，星期三第六大类:Unincode字符串数据

nchar:长度是设定的,最短为1字节,最长为4000个字节.不足的长度会用空白补上.储存双字节字符,nchar(m)中的m代表的含义是用来定义字符数据的最大长度.

nvarchar:可变长度,最短为1字节,最长为4000个字节.尾部的空白会去掉.储存一个字符需要2个字节.

ntext:可变长度,最短为1字节,最长为230-1个unicode数据.尾部的空白会去掉,储存一个字符需要2个字节.第八页，共三十九页，编辑于2023年，星期三第七大类:货币数据类型

money:记录金额范围为:-92233720368577.5808至92233720368577.5807.需要8个字节.

smallmoney:记录金额范围为:-214748.3648至214748.36487.需要4个字节.第八大类:标记数据

timestamp:该数据类型在每一个表中是唯一的!当表中的一个记录更改时,该记录的timestamp字段会自动更新.

第九页，共三十九页，编辑于2023年，星期三第九大类:二进制码字符串数据

binary:固定长度的二进制码字符串字段,最短为1,最长为8000.

varbinary:与binary差异为数据尾部是00时,varbinary会将其去掉

image:为可变长度的二进制码字符串,最长2G.第十页，共三十九页，编辑于2023年，星期三3.3表结构的建立和删除

1、表的建立表是包含数据库中所有数据的数据库对象，用来存储各种各样的信息。在SQLServer2005中，一个数据库中最多可以创建20亿个表，用户创建数据库表时，最多可以定义1024列。在同一数据库的不同表中，可以有相同的字段，但在同一个表中不允许有相同的字段，而且每个字段都要求数据类型相同。第十一页，共三十九页，编辑于2023年，星期三一般的SQL建表语句形式CREATETABLE表名[表约束](列名1数据类型[缺省值1，列约束1]

列名2数据类型[缺省值2，列约束2]

…

列名n数据类型[缺省值n，列约束n]）第十二页，共三十九页，编辑于2023年，星期三例3-1创建三张表，表结构如下：列名描述数据类型约束默认值SnoSnameSsexSageSdept学号姓名性别年龄所在系字符型，长度为7字符型，长度为10字符型，长度为2微整数字符型，长度20主码非空男Student表结构第十三页，共三十九页，编辑于2023年，星期三Course表结构列名说明数据类型约束默认值CnoCnameCcreditCSemester课程号课程名学分学期字符型，长度为10字符型，长度为20微整数微整数主码非空

3第十四页，共三十九页，编辑于2023年，星期三

列名说明数据类型约束SnoCnoGrade学号课程号成绩字符型，长度为7字符型，长度为10浮点数主码，引用Student的外码主码，引用Course的外码Sc表结构第十五页，共三十九页，编辑于2023年，星期三CREATETABLEStudent(SnoCHAR(7)PRIMARYKEY，

SnameCHAR(10)NOTNULL,SsexCHAR(2)DEFAULT‘男’,SageTINYINT,SdeptCHAR(20),)第十六页，共三十九页，编辑于2023年，星期三CREATETABLECourse(CnoCHAR(10)PRIMARYKEY，

CnameCHAR(20)NOTNULL,CcreditTINYINTDEFAULT3,SemesterTINYINT,)第十七页，共三十九页，编辑于2023年，星期三CREATETABLESc(SnoCHAR(7)，

CnoCHAR(10),GradeFLOAT，

PRIMARYKEY（Sno，Cno），

FOREIGNKEY（Sno）REFERENCESStudent（Sno），

FOREIGNKEY（Cno）REFERENCESCourse（Cno））第十八页，共三十九页，编辑于2023年，星期三2、表的删除删除表的命令一般形式DROPTABLE表名例3-2删除教师表

DROPTABLEteacher第十九页，共三十九页，编辑于2023年，星期三3.4表结构的扩充和修改

1、修改表的命令一般形式

ALTERTABLE表名{ADD列名数据类型[NULL/NOTNULL]|ALTERCOLUMN列名数据类型[NULL|NOTNULL]|DROPCOLUMN列名}

第二十页，共三十九页，编辑于2023年，星期三例3-3在student中增加住址列

ALTERTABLEstudentADDaddressCHAR（50）例3-4修改student表中的sdept字段，将数据类型长度为30ALTERTABLEstudentALTERCOLUMNsdeptchar(30)例3-5把Student表中的Sdept列删除。

ALTERTABLEStudentDROPCOLUMNSdept第二十一页，共三十九页，编辑于2023年，星期三2．添加主关键字

ALTERTABLE表名

ADD[CONSTRAINT约束名]PRIMARYKEY(主键列)3、删除主关键字

ALTERTABLE＜表名＞

DROP[CONSTRAINT]约束名3.补充定义外来关键字

ALTERTABLE＜表名-1＞

ADD[CONSTRAINT约束名]FOREIGNKEY[(＜外来关键字名＞)]REFERENCES(＜表名-2＞)5．删除外来关键字

ALTERTABLE＜表名＞

DROP＜外来关键字名＞第二十二页，共三十九页，编辑于2023年，星期三例3-6补充定义student表的主关键字。

ALTERTABLEStudentADDCONSTRAINTpk_studentPRIMARYKEY(Sno)例3-7、删除course表中的主关键字。

ALTERTABLEcourseDROPpk_course例3-8、修改sc表，在sno字段上创建外键约束。

ALTERTABLEscADDCONSTRAINTfk_sc_studentFOREIGNKEY(sno)REFERENCESstudent(sno)

第二十三页，共三十九页，编辑于2023年，星期三例3-9、删除sc表中sno字段上的外键约束

ALTERTABLEscDROPfk_sc_student例3-10、向Student表增加“入学时间”列，其数据类型为日期型。

ALTERTABLEStudentADDScomedateDATETIME例3-11、将年龄的数据类型改为半字长整数。

ALTERTABLEStudentALTERCOLUMNSageSMALLINT第二十四页，共三十九页，编辑于2023年，星期三3.5数据完整性数据完整性是指存储在数据库中的数据的一致性和准确性。数据完整性技术是衡量数据库质量好坏的重要标准。在MicrosoftSQLServer系统中，提供了一系列实现数据完整性的方法，这些方法主要包括主键约束、外键约束、非空约束、惟一约束、默认值约束、检查约束以及规则和默认对象。第二十五页，共三十九页，编辑于2023年，星期三分类：实体完整性参照完整性域完整性

实体完整性又称为行完整性。这里的实体是指表中的记录，一个实体就是表的一条记录。实体完整性要求在表中不能存在完全相同的记录，而且每条记录都要具有一个非空且不重复的主键值。

第二十六页，共三十九页，编辑于2023年，星期三参照完整性：一般指多个实体或关系表之间的关联关系，在关系数据库中用外码（Foreignkey）来实现参照完整性。域完整性也称用户定义的完整性或语义完整性。用户定义的完整性实际上就是指明关系中属性的取值范围，也就是属性的域，即限制关系中属性的取值类型及取值范围，防止属性的值与应用语义矛盾。第二十七页，共三十九页，编辑于2023年，星期三约束定义了必须遵循的用于维护数据一致性和正确性的规则，是强制实现数据完整性的途径。约束分类：主键约束外键约束唯一值约束检查约束默认约束第二十八页，共三十九页，编辑于2023年，星期三1、唯一性约束（UNIQUE）UNIQUE用来限制不受主键约束的列上的数据的唯一性。一个表可以放置多个UNIQUE约束语法：[CONSTRAINTconstraint_name]UNIQUE(列名)

第二十九页，共三十九页，编辑于2023年，星期三例3-12为student表添加身份证号列’id‘，并为’id’列添加唯一值约束。

ALTERTABLEstudentADDidBIGINTCONSTRAINTunq_idUNIQUE注：UNIQUE约束列允许有NULL值，但最多只能有一个NULL值，否则认为重复。例3-13为course表的cname列添加唯一值约束。

ALTERTABLEcourseADDCONSTRAINTunq_cnameUNIQUE(cname)第三十页，共三十九页，编辑于2023年，星期三2、检查约束（CHECK）CHECK约束用来指定某列的可取值范围。通过限制输入值强制控制域的完整性。语法：[CONSTRAINTconstraint_name]CHECK(检查条件)

例3-14限制student表中的ssex列只能添入值为’男‘或’女’。ALTERTABLEstudentADDCONSTRAINTcheck_ssexCHECK(ssex=‘男’

orssex=‘女’)第三十一页，共三十九页，编辑于2023年，星期三3、默认约束（DEFAULT）DEFAULT约束用于给表中指定列赋予一个常量值（默认值）。语法：[CONSTRAINT约束名]DEFAULT默认值FOR列名例3-15为student中的sage列添加默认值“19”。

ALTERTABLEstudentADDCONSTRAINTdefault_sageDEFAULT19FORsage第三十二页，共三十九页，编辑于2023年，星期三上机实践（独立完成实验）实验目的：本章实验主要目的是学习正确创建表；掌握修改表结构的方法；正确删除表。实验内容：自定义数据类型。分别用企业管理器和查询分析器创建表。分别用企业管理器和查询分析器修改表。分别用企业管理器和查询分析器删除表。第三十三页，共三十九页，编辑于2023年，星期三实验题目：题目一：在学生数据库student中创建学生个人信息数据表studentinfo

题目要求分析：studentinfo数据表中包含以下信息：学号、姓名、性别、年龄、班级、地址以及联系方式等，如下表所示。第三十四页，共三十九页，编辑于2023年，星期三字段名称字段说明数据类型长度属性student_id学号CHAR8主键student_name姓名CHAR10不允许空student_sex性别CHAR2允许空year_old年龄TINYINT允许空address地址CHAR80允许空student_region籍贯CHAR10允许空student_phone联系电话CHAR13允许空第三十五页，共三十九页，编辑于2023年，星期三题目二：修改个人信息数据表studentinfo，完成如下操作：删除year_old字段。向该表中添加一列，列名为email，数据类型为char，长度4