数据库原理与应用第章创建和管理数据表PPT课件.ppt

第四章创建和管理数据表 1 预习检查 至少说出SQLServer中三种数据类型举例说明在哪些情况下要用到检查约束什么是标识列 其作用是什么 什么是数据完整性 创建数据表的T SQL语句是什么 2 本章目标 掌握SQLServer常用数据类型 能够使用图形工具SSMS和T SQL语句创建数据表 能够为表创建主键 外键 设置默认值 建立检查约束和非空约束 创建标识列等以确保数据完整性 3 SQLServer常用数据类型 数据表用于存储各种数据 不同类型的数据又适合不同的存储方式 从而适合不同的数据类型 首先应该了解需要存储的数据在SQLServer数据库中的对应哪种数据类型 或者说这些数据适合以何种数据类型存储 2020 3 18 4 二进制数据类型 2020 3 18 5 文本数据类型 2020 3 18 6 Bit数据类型 2020 3 18 7 数字数据类型 2020 3 18 8 时间和日期数据类型 2020 3 18 9 数据类型示例 身份证号 可能是18位或15位 可以选择nvarchar 18 姓名 中国名字大多三个字 且可变长度 可选择可以选择nvarchar 10 性别 只有男女 可选择bit或char 1 家庭地址 可变长度的字符串 nvarchar 200 电话 可变长度的字符串 nvarchar 20 生日 可选择日期数据类型 datatime存款余额 可选择money数据类型照片 可选择image数据类型 2020 3 18 10 数据完整性的问题大多是由于设计引起的 数据存放在表中 创建表的时候 就应当保证以后数据输入是正确的 错误的数据 不符合要求的数据不允许输入 数据完整性 数据完整性 Integrity 是指数据的准确性 Accuracy 和可靠性 Reliability 保证数据的完整性 实施完整性约束 2020 3 18 11 输入的类型是否正确 年龄必须是数字输入的格式是否正确 身份证号码必须是18位是否在允许的范围内 性别只能是 男 或者 女 是否存在重复输入 学员信息输入了两次是否符合其他特定要求 学生的出生日期必须小于入学日期 完整性问题举例 2020 3 18 12 提供四种类型的约束保证完整性 四种完整性约束 2020 3 18 13 实体完整性 约束方法 唯一约束 主键约束 标识列 实体完整性 EntityIntegrity 指表中数据行的完整性 主要用于保证每条记录非空 唯一且不重复 2020 3 18 14 域完整性 约束方法 限制数据类型 检查约束 外键约束 默认值 非空约束 学号要求8位字符 域完整性 DomainIntegrity 又称列完整性 保证列数据具有正确的数据类型 格式和有效的取值范围 精度等 2020 3 18 15 约束方法 外键约束 参照 引用 完整性 参照完整性 ReferentialIntegrity 又称引用完整性 保证主表中的数据与从表中数据的一致性 参照完整性的操作要求 参照完整性禁止以下几种操作 1 禁止外键列中插入主键列中没有的值 即外键的取值必须是主表中主键的有效值 或者说向从表中插入数据时只能添加主表中已经存在的关联记录的相关信息 例如银行开户只能给储户信息表中已经存在的储户开户 学生成绩表中不能添加学生信息表中不存在的学号的成绩信息 2 禁止只修改主表中主键列的值 而不修改从表中相应的外键列的值 即不能更改主表中的值而导致从表中的记录孤立无意义 例如学生信息表中一个学生学号变了 那么学生成绩表中该学生的学号也必须相应改变 3 禁止先从主表中删除数据行 即主表中删除数据时 必须首先删除从表中的关联记录 否则从表中会遗留大量无意义的记录 自定义完整性 约束方法 规则 存储过程 触发器 用户自定义完整性 User definedIntegrity 是对列数据的约束 允许用户定义不属于以上三种完整性分类的特定的规则 包括列的值域 列的类型和列的有效规则等约束 这些是由确定关系结构时所定义的字段的属性决定的 示例 信用不良的客户 马六 不能办理会员证 创建数据表 通过三大范式规范数据库设计并绘制数据库模型图后 就可以在数据库中创建数据表 创建表的基本步骤 第一步 确定表中有哪些列 第二步 确定每列的数据类型 第三步 给表添加各种约束 第四步 创建各表之间的关系 使用图形化向导创建数据库表 思考 空值NULL身份证允许为空吗 姓名允许为空吗 家庭地址允许为空吗 电子邮件信息允许为空吗 性别允许为空吗 2020 3 18 20 保存并命名数据表 2020 3 18 21 在主键列输入的数值 允许为空吗 一个表的主键可以有多个列组成吗 思考 主键 为depositor表创建主键 为depositor表添加默认值 示例 为gender列设置默认值1 2020 3 18 24 为depositor表建立检查约束 示例 为depositorID建立检查约束 要求该列长度只能是15位或18位 2020 3 18 25 标识列允许为字符数据类型吗 如果标识列A的初始值为1 增长量为2 则输入三行数据以后 再删除两行 下次再输入数据行的时候 标识值从多少开始 思考 标识列 设置标识列 2020 3 18 27 建立表间 主外键 关系 2020 3 18 28 数据库关系图 利用主外键的对应 图形化表示表之间的关系 2020 3 18 29 当主表中没有对应的记录时 不能将记录添加到子表 成绩表中不能出现在学员信息表中不存在的学号不能更改主表中的值而导致子表中的记录孤立 把学员信息表中的学号改变了 学员成绩表中的学号也应当随之改变子表存在与主表对应的记录 不能从主表中删除该行 不能把有成绩的学员删除了删除主表前 先删子表 先删学员成绩表 后删除学员信息表 主外键建立后注意事项 使用T SQL语句创建数据表 建表的语法 CREATETABLE表名 列名1数据类型列的特征 列名2数据类型列的特征 列的特征 包括该列是否为空 NULL 是否是标识列 identity 是否有默认值 default 是否为主键 PrimaryKey 等 31 创建表Student USEStudentInfo 将当前数据库设置为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表 32 使用T SQL语句修改数据表 1 ALTERTABLE 表名 ADDCOLUMN 列名 数据类型列特征 增加一个新列2 ALTERTABLE 表名 addCONSTRAINT 约束名 DEFAULT默认值FOR 列名 设置默认值3 ALTERTABLE 表名 RENAMEcolumn 列名 TO 新列名 修改列名4 ALTERTABLE 表名 RENAMETO 新表名 修改表名5 ALTERTABLE 表名 ADDCONSTRAINT 约束名称 约束类型 列 添加约束 常用的数据表修改语句 33 回顾数据完整性 实体完整性能够唯一标识表中的每一条记录实现方式 主键 唯一键 IDENTITY属性域完整性表中特定列数据的有效性 确保不会输入无效的值实现方式 类型 缺省值 约束 空值引用完整性维护表间数据的有效性 完整性实现方式 建立外键联系另一表的主键自定义完整性根据业务处理流程定义的特定业务规则实现方式 存储过程 触发器 规则 2020 3 18 34 回顾SQLServer的约束 使用约束的目的是确保表中数据完整性 常用的约束类型有哪些 主键约束 PrimaryKeyConstraint 要求主键列数据唯一 并且不允许为空唯一约束 UniqueConstraint 要求该列数据唯一 允许为空 但只能出现一个空值检查约束 CheckConstraint 某列取值范围限制 格式限制等默认约束 DefaultConstraint 某列的默认值外键约束 ForeignKeyConstraint 用于两表间建立关系 需要指定引用主表的哪列 主键约束与唯一约束的区别 1 主键约束所在的列不允许有空值 唯一约束所在的列允许空值2 每个表中可以有一个主键 多个唯一键 35 使用T SQL语句添加约束 添加约束的语法 ALTERTABLE表名ADDCONSTRAINT约束名约束类型具体的约束说明 约束名的取名规则推荐采用 约束类型 约束列主键 PrimaryKey 约束 如PK stuNo唯一 UniqueKey 约束 如UQ stuID默认 DefaultKey 约束 如DF stuAddress检查 CheckKey 约束 如CK stuBornDate外键 ForeignKey 约束 如FK stuNo 36 为表添加约束 ALTERTABLEStudentADDCONSTRAINTPK StuIDPRIMARYKEY StudentID ALTERTABLEStudentADDCONSTRAINTUQ IdentityUNIQUE stuIdentity ALTERTABLEStudentADDCONSTRAINTDF AddressDEFAULT 地址不详 FORAddressALTERTABLEStudentADDCONSTRAINTCK birthdayCHECK birthday 1980 01 01 ALTERTABLEStudentADDCONSTRAINTFK Student GradeFOREIGNKEY GradeID REFERENCESGrade GradeID ALTERTABLESubjectWITHNOCHECK 检查约束 学时必须大于等于0 ADDCONSTRAINTCK ClassHourCHECK ClassHour 0 GO 添加主键约束 StudentID作为主键 添加唯一约束 因为每人的身份证号全国唯一 添加默认约束 如果地址不填 默认为 地址不详 添加检查check约束 要求出生日期在1980 1 1之后 添加外键约束 主表Grade和从表Student建立关系 关联列为GradeID WITHNOCHECK对表中已有数据不做约束检查 因此不能保证表中数据的正确性 建议 创建表结构后应立即添加约束 不要马上插入数据 以避免插入的数据不符合约束要求 保证表中数据满足约束限制 2020 3 18 37 删除约束 如果错误地添加了约束 我们还可以删除约束删除约束的语法 ALTERTABLE表名DROPCONSTRAINT约束名 例如 Score表的结构比较简单 我们只在scoreID列建立主键就可以 现在删除上机练习4 11中的在 学号 科目号 上建的主键约束PK Score 然后在scoreID列新建主键约束 先删除现有主键 ALTERTABLEScoredropCONSTRAINTPK Score再新建主键 ALTERTABLEScoreADDCONSTRAINTPK ScorePRIMARYKEY scoreID 38 如果当前数据库中已存在Student表 再次创建时系统将提示出错 如何解决呢 39 DROPTABLE表名 USEStudentInfoGOIFEXISTS SELECT FROMsysobjectsWHEREname Student DROPTABLEStudentCREATETABLEStudent 创建学生信息表 具体创建内容省略 GO 使用T SQL语句删除数据表