数据库原理与应用第4章创建和管理数据表

第四章创建和管理数据表预习检查至少说出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）等创建表StudentUSEStudentInfo--将当前数据库设置为StudentInfoGOCREATETABLEStudent--创建学生信息表(StudentIDnvarchar(20)NOTNULL,--学号，非空（必填）passwordnvarchar(50)NOTNULL,--密码，非空（必填）Namenvarchar(50)NOTNULL,--姓名，非空（必填）genderbitNOTNULL,--性别，非空（必填）GradeIdintNOTNULL,--年级号（必填）Phonenvarchar(50)NOTNULL,--电话birthdaydatetimeNOTNULL,--出生日期（必填）Addressnvarchar(255),--地址Emailnvarchar(50),--邮件帐号stuIDENTITYvarchar(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）：要求该列数据唯一，允许为空，但只能出现