第3章 数据表的创建和管理

第三章基本表的创建与管理学习要点：表的概念表的创建、修改和删除添加、更新与删除表中的数据数据完整性的概念及实施方法3.1表的概念表是数据的集合，是用来存储数据和操作数据的逻辑结构。表是由行和列组成的，行被称为记录，是组织数据的单位；列被称为字段，每一列表示记录的一个属性。在特定表中，列名必须是唯一的，但相同的列名可以在数据库中的不同表中使用。SQLServer中的每个表中最多允许有1024列，每行最多允许有8060字节的用户数据。行和列的次序是任意的。3.2数据类型第一大类:整数数据bit:bit数据类型代表0,1或NULL,就是表true,false.占用1byte.int:以4个字节来存储正负数.可存储范围为:-（-2147483648至2147483647）.smallint:以2个字节来存储正负数.存储范围为:-（-32768至32767）tinyint:是最小的整数类型,仅用1字节,范围:0至255bigint：以8个字节存储，是SQLServer2000引入的全新的数据类型，可以存储范围：（-263到263-1）第二大类:精确数值数据numeric:表示的数字可以达到38位,存储数据时所用的字节数目会随着使用权用位数的多少变化.格式写为numeric(p，s)例如：numeric(10，4)，表示共有10位数，其中整数6位，小数4位。decimal:和numeric类似。区别：numeric类型的列可以带有IDENTITY关键字（唯一标识符）。第三大类:近似浮点数值数据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个字节.第五大类:字符串数据char[（n）]:长度是设定的,最短为1字节,最长为8000个字节.不足的长度会用空白补上.例如：char（10）Varchar[（n）]:变长字符型数据,最短为1字节,最长为8000个字节,尾部的空白会去掉.text:长专门用于存储数量庞大的变长字符数据，,最长可以存放2G（231-1个字符）的数据.第六大类:Unincode字符串数据nchar:长度是设定的,最短为1字节,最长为4000个字节.不足的长度会用空白补上.储存双字节字符,nchar(m)中的m代表的含义是用来定义字符数据的最大长度.nvarchar:可变长度,最短为1字节,最长为4000个字节.尾部的空白会去掉.储存一个字符需要2个字节.ntext:可变长度,最短为1字节,最长为230-1个unicode数据.尾部的空白会去掉,储存一个字符需要2个字节.第七大类:货币数据类型money:记录金额范围为:-92233720368577.5808至92233720368577.5807.需要8个字节.smallmoney:记录金额范围为:-214748.3648至214748.36487.需要4个字节.第八大类:标记数据timestamp:该数据类型在每一个表中是唯一的!当表中的一个记录更改时,该记录的timestamp字段会自动更新.第九大类:二进制码字符串数据binary:固定长度的二进制码字符串字段,最短为1,最长为8000.varbinary:与binary差异为数据尾部是00时,varbinary会将其去掉image:为可变长度的二进制码字符串,最长2G.3.3表结构的建立和删除1、表的建立表是包含数据库中所有数据的数据库对象，用来存储各种各样的信息。在SQLServer2005中，一个数据库中最多可以创建20亿个表，用户创建数据库表时，最多可以定义1024列。在同一数据库的不同表中，可以有相同的字段，但在同一个表中不允许有相同的字段，而且每个字段都要求数据类型相同。一般的SQL建表语句形式CREATETABLE表名[表约束](列名1数据类型[缺省值1，列约束1]列名2数据类型[缺省值2，列约束2]…列名n数据类型[缺省值n，列约束n]）例3-1创建三张表，表结构如下：列名描述数据类型约束默认值SnoSnameSsexSageSdept学号姓名性别年龄所在系字符型，长度为7字符型，长度为10字符型，长度为2微整数字符型，长度20主码非空男Student表结构Course表结构列名说明数据类型约束默认值CnoCnameCcreditCSemester课程号课程名学分学期字符型，长度为10字符型，长度为20微整数微整数主码非空3列名说明数据类型约束SnoCnoGrade学号课程号成绩字符型，长度为7字符型，长度为10浮点数主码，引用Student的外码主码，引用Course的外码Sc表结构CREATETABLEStudent(SnoCHAR(7)PRIMARYKEY，SnameCHAR(10)NOTNULL,SsexCHAR(2)DEFAULT‘男’,SageTINYINT,SdeptCHAR(20),)CREATETABLECourse(CnoCHAR(10)PRIMARYKEY，CnameCHAR(20)NOTNULL,CcreditTINYINTDEFAULT3,SemesterTINYINT,)CREATETABLESc(SnoCHAR(7)，CnoCHAR(10),GradeFLOAT，PRIMARYKEY（Sno，Cno），FOREIGNKEY（Sno）REFERENCESStudent（Sno），FOREIGNKEY（Cno）REFERENCESCourse（Cno））2、表的删除删除表的命令一般形式DROPTABLE表名例3-2删除教师表DROPTABLEteacher3.4表结构的扩充和修改1、修改表的命令一般形式ALTERTABLE表名{ADD列名数据类型[NULL/NOTNULL]|ALTERCOLUMN列名数据类型[NULL|NOTNULL]|DROPCOLUMN列名}例3-3在student中增加住址列ALTERTABLEstudentADDaddressCHAR（50）例3-4修改student表中的sdept字段，将数据类型长度为30ALTERTABLEstudentALTERCOLUMNsdeptchar(30)例3-5把Student表中的Sdept列删除。ALTERTABLEStudentDROPCOLUMNSdept2．添加主关键字ALTERTABLE表名ADD[CONSTRAINT约束名]PRIMARYKEY(主键列)3、删除主关键字ALTERTABLE＜表名＞DROP[CONSTRAINT]约束名3.补充定义外来关键字ALTERTABLE＜表名-1＞ADD[CONSTRAINT约束名]FOREIGNKEY[(＜外来关键字名＞)]REFERENCES(＜表名-2＞)5．删除外来关键字ALTERTABLE＜表名＞DROP＜外来关键字名＞例3-6补充定义student表的主关键字。ALTERTABLEStudentADDCONSTRAINTpk_studentPRIMARYKEY(Sno)例3-7、删除course表中的主关键字。ALTERTABLEcourseDROPpk_course例3-8、修改sc表，在sno字段上创建外键约束。ALTERTABLEscADDCONSTRAINTfk_sc_studentFOREIGNKEY(sno)REFERENCESstudent(sno)例3-9、删除sc表中sno字段上的外键约束ALTERTABLEscDROPfk_sc_student例3-10、向Student表增加“入学时间”列，其数据类型为日期型。ALTERTABLEStudentADDScomedateDATETIME例3-11、将年龄的数据类型改为半字长整数。ALTERTABLEStudentALTERCOLUMNSageSMALLINT3.5数据完整性数据完整性是指存储在数据库中的数据的一致性和准确性。数据完整性技术是衡量数据库质量好坏的重要标准。在MicrosoftSQLServer系统中，提供了一系列实现数据完整性的方法，这些方法主要包括主键约束、外键约束、非空约束、惟一约束、默认值约束、检查约束以及规则和默认对象。分类：实体完整性参照完整性域完整性实体完整性又称为行完整性。这里的实体是指表中的记录，一个实体就是表的一条记录。实体完整性要求在表中不能存在完全相同的记录，而且每条记录都要具有一个非空且不重复的主键值。参照完整性：一般指多个实体或关系表之间的关联关系，在关系数据库中用外码（Foreignkey）来实现参照完整性。域完整性也称用户定义的完整性或语义完整性。用户定义的完整性实际上就是指明关系中属性的取值范围，也就是属性的域，即限制关系中属性的取值类型及取值范围，防止属性的值与应用语义矛盾。约束定义了必须遵循的用于维护数据一致性和正确性的规则，是强制实现数据完整性的途径。约束分类：主键约束外键约束唯一值约束检查约束默认约束1、唯一性约束（UNIQUE）UNIQUE用来限制不受主键约束的列上的数据的唯一性。一个表可以放置多个UNIQUE约束语法：[CONSTRAINTconstraint_name]UNIQUE(列名)例3-12为student表添加身份证号列’id‘，并为’id’列添加唯一值约束。ALTERTABLEstudentADDidBIGINTCONSTRAINTunq_idUNIQUE注：UNIQUE约束列允许有NULL值，但最多只能有一个NULL值，否则认为重复。例3-13为course表的cname列添加唯一值约束。ALTERTABLEcourseADDCONSTRAINTunq_cnameUNIQUE(cname)2、检查约束（CHECK）CHECK约束用来指定某列的可取值范围。通过限制输入值强制控制域的完整性。语法：[CONSTRAINTconstraint_name]CHECK(检查条件)例3-14限制student表中的ssex列只能添入值为’男‘或’女’。ALTERTABLEstudentADDCONSTRAINTcheck_ssexCHECK(ssex=‘男’orssex=‘女’)3、默认约束（DEFAULT）DEFAULT约束用于给表中指定列赋予一个常量值（默认值）。语法：[CONSTRAINT约束名]DEFAULT默认值FOR列名例3-15为student中的sage列添加默认值“19”。ALTERTABLEstudentADDCONSTRAINTdefault_sageDEFAULT19FORsage上机实践（独立完成实验）实验目的：本章实验主要目的是学习正确创建表；掌握修改表结构的方法；正确删除表。实验内容：自定义数据类型。分别用企业管理器和查询分析器创建表。分别用企业管理器和查询分析器修改表。分别用企业管理器和查询分析器删除表。实验题目：题目一：在学生数据库student中创建学生个人信息数据表studentinfo题目要求分析：studentinfo数据表中包含以下信息：学号、姓名、性别、年龄、班级、地址以及联系方式等，如下表所示。字段名称字段说明数据类型长度属性student_id学号CHAR8主键student_name姓名CHAR10不允许空student_sex性别CHAR2允许空year_old年龄TINYINT允许空address地址CHAR80允许空student_region籍贯CHAR10允许空student_phone联系电话CHAR13允许空题目二：修改个人信息数据表studenti