TP2SQLServer数据库表管理.ppt

1 第二章 SQLServer数据库表管理 2 回顾 数据库的作用数据库的发展过程以及当前流行的数据库数据库基本概念 数据存储方式 实体和记录 数据库和数据库表 数据冗余和数据完整性使用企业管理器创建组 注册 用户在企业管理器中创建数据库收缩 移动和配置数据库的选项 3 本章目标 使用企业管理器创建数据库表设置表的主键 外键和建立表之间的关系为表增加约束 4 再论数据完整性2 1 数据完整性 5 再论数据完整性2 2 数据存放在表中 数据完整性的问题大多是由于设计引起的 创建表的时候 就应当保证以后数据输入是正确的 错误的数据 不符合要求的数据不允许输入 创建表 保证数据的完整性 实施完整性约束 6 完整性包括 2 1 输入的类型是否正确 年龄必须是数字输入的格式是否正确 身份证号码必须是18位是否在允许的范围内 性别只能是 男 或者 女 是否存在重复输入 学员信息输入了两次是否符合其他特定要求 信誉值大于5的用户才能够加入会员列表 7 完整性包括 2 2 8 实体完整性 约束方法 唯一约束 主键约束 标识列 9 域完整性 约束方法 限制数据类型 检查约束 外键约束 默认值 非空约束 10 参照完整性 约束方法 外键约束 11 自定义完整性 约束方法 规则 存储过程 触发器 12 表操作 在企业管理器中演示打开表 数据输入等操作 13 创建数据库表 1 14 SQLServer的数据类型 15 二种 datetime smalldatetime 说明方法 datetime用8个字节存储日期时间 日期范围1753 1 1 9999 12 31 精度到3 100秒 smalldatetime用4个字节存储 范围1900 1 1 2079 6 6 精度到分 常量用单引号引起 有多种格式 时间日期数据类型 08 10 2004或08 10 2004 分隔符数字方式08102004 数字方式A10 2004 英文数字 例 常量2004年8月10日下午5点30分 2004 8 1017 30 00 或者 8 10 20045 30 00PM 16 bigint 大整数 8个字节 Int或integer 整数 4个字节 231 231 1 smallint 短整数 2个字节 32768 32767 tinyint 微短整数 1个字节 0 255 Bit 只能取0 1或NULL bit类型的字段或变量输入0和NULL以外的数值都作为1处理 整数数据类型 17 二种 decimal numeric 等价 存储实数值 由数字0 9 正负号 小数点组成 最多38位 由整数部分和小数部分构成 说明方法 decimal p s 或numeric p s P为精度 S为小数位数 默认精度18位 默认小数位0位 精确数据类型 例 decimal 12 4 表示数值中共有12位 4位小数 8位整数 字段的精度为12位 18 二种 money smallmoney 说明方法 money两个4个字节整数存储 前面表示货币的整数部分 后面的4个字节表示小数部分 小数点后保留4位数 Smallmoney分别用两个字节表示货币的整数部分和小数部分 小数点后保留4位数 货币常量前加币值符号 例 120 35 货币数据类型 19 思考 电话号码一般使用什么数据类型存储 性别一般使用什么数据类型存储 年龄信息一般使用什么数据类型存储 照片信息一般使用什么数据类型存储 薪水一般使用什么数据类型存储 20 创建数据库表 2 21 思考 学员姓名允许为空吗 家庭地址允许为空吗 电子邮件信息允许为空吗 考试成绩允许为空吗 22 自定义数据类型 通过存储过程sp addtype创建新的类型 用sp droptype删除不再使用的自定义类型 sp addtypetype namesystem type NULL NOTNULL NONULL 自定义数据类型 23 自定义数据类型 例在BOOKS数据库中定义一个新的数据类型 类型名称为 图书编码类型 字符型 长度为12位 不允许为空 sp addtype图书编码类型 char 12 notnull 创建用户自定义数据类型 24 练习 使用系统存储过程定义一个新的数据类型 类型名称为 价格类型 货币型 允许为空 USEBOOKSGOEXECsp addtype价格类型 money null 25 自定义数据类型 例删除用户自定义数据类型 图书编码类型 USEBOOKSGOEXECUTEsp droptype图书编码类型 删除用户自定义数据类型 26 创建数据库表 3 27 思考 在主键列输入的数值 允许为空吗 一个表可以有多个主键吗 在一个学校数据库中 如果一个学校内允许重名的学员 但是一个班级内不允许学员重名 可以组合班级和姓名两个字段一起来作为主键吗 28 选择主键的原则 最少性尽量选择单个键作为主键稳定性尽量选择数值更新少的列作为主键 29 创建数据库表 4 表中没有合适的列作为主键怎么办 30 思考 标识列允许为字符数据类型吗 如果标识列A的初始值为1 增长量为2 则输入三行数据以后 再删除两行 下次再输入数据行的时候 标识值从多少开始 31 创建数据库表 5 32 创建数据库表 6 演示建立主 外键关系 33 关系图 演示关系图 34 主表和从表 1 当主表中没有对应的记录时 不能将记录添加到子表 成绩表中不能出现在学员信息表中不存在的学号 2 不能更改主表中的值而导致子表中的记录孤立 把学员信息表中的学号改变了 学员成绩表中的学号也应当随之改变 3 子表存在与主表对应的记录 不能从主表中删除该行 不能把有成绩的学员删除了4 删除主表前 先删子表 先删学员成绩表 后删除学员信息表 35 创建数据库表 7 演示建立检查约束 36 创建数据库表完毕 输入数据项 验证主键 主外键关系 检查约束 37 导入 导出数据 演示导出数据为EXCEL文件 38 回顾表的基础知识 建表的基本步骤 确定表中有哪些列确定每列的数据类型给表添加各种约束创建各表之间的关系SQLServer中的数据类型 整型数据 intsmallint浮点数据 floatnumeric 字符数据固定长度 char 6 可变长度 varchar 10 unicode编码 nchar 10 布尔数据 真 假 bit 1 0 货币数据money 日期数据datetime 39 创建表 建表的语法 CREATETABLE表名 字段1数据类型列的特征 字段2数据类型列的特征 列的特征 包括该列是是否为空 NULL 是否是标识列 自动编号 是否有默认值 是否为主键等 40 创建数据表 T SQL创建数据表的语法 CREATETABLE表名 字段1数据类型列的特征 字段2数据类型列的特征 数据类型 长度 小数 41 创建数据表 NOT NULL取空值约束DEFAULT缺省值IDENTITY 种子值 增量 自动增量标识CHECK检验表达式UNIQUE唯一值约束PRIMARYKEY主键FOREIGNKEY外键字段名 42 建表示例1 USEstuDB 将当前数据库设置为stuDBGOCREATETABLEstuInfo 创建学员信息表 stuNameVARCHAR 20 NOTNULL 姓名 非空 必填 stuNoCHAR 6 NOTNULL 学号 非空 必填 stuAgeINTNOTNULL 年龄 INT类型默认为4个字节stuIDNUMERIC 18 0 身份证号stuSeatSMALLINTIDENTITY 1 1 座位号 自动编号stuAddressTEXT 住址 允许为空 即可选输入 GO NUMERIC 18 0 代表18位数字 小数位数为0 演示 创建学员信息表stuInfo IDENTITY 起始值 递增量 43 建表示例2 CREATETABLEstuMarks ExamNoCHAR 7 NOTNULL 考号stuNoCHAR 6 NOTNULL 学号writtenExamINTNOTNULL 笔试成绩LabExamINTNOTNULL 上机成绩 GO 演示 创建学员成绩表stuMarks 44 删除表 如果当前数据库中已存在stuInfo表 再次创建时系统将提示出错 如何解决呢 45 删除表 删除表的语法 DROPTABLE表名 USEstuDB 将当前数据库设置为stuDB 以便在stuDB数据库中建表GOIFEXISTS SELECT FROMsysobjectsWHEREname stuInfo DROPTABLEstuInfoCREATETABLEstuInfo 创建学员信息表 GO 46 回顾SQLServer的约束 约束的目的 确保表中数据的完整型常用的约束类型 主键约束 PrimaryKeyConstraint 要求主键列数据唯一 并且不允许为空唯一约束 UniqueConstraint 要求该列唯一 允许为空 但只能出现一个空值 检查约束 CheckConstraint 某列取值范围限制 格式限制等 如有关年龄的约束默认约束 DefaultConstraint 某列的默认值 如我们的男性学员较多 性别默认为 男 外键约束 ForeignKeyConstraint 用于两表间建立关系 需要指定引用主表的那列 47 添加约束 添加约束的语法 ALTERTABLE表名ADDCONSTRAINT约束名约束类型具体的约束说明 约束名的取名规则推荐采用 约束类型 约束字段主键 PrimaryKey 约束 如PK stuNo唯一 UniqueKey 约束 如UQ stuID默认 DefaultKey 约束 如DF stuAddress检查 CheckKey 约束 如CK stuAge外键 ForeignKey 约束 如FK stuNo 48 添加约束示例 ALTERTABLEstuInfoADDCONSTRAINTPK stuNoPRIMARYKEY stuNo ALTERTABLEstuInfoADDCONSTRAINTUQ stuIDUNIQUE stuID ALTERTABLEstuInfoADDCONSTRAINTDF stuAddressDEFAULT 地址不详 FORstuAddressALTERTABLEstuInfoADDCONSTRAINTCK stuAgeCHECK stuAgeBETWEEN15AND40 ALTERTABLEstuMarksADDCONSTRAINTFK stuNoFOREIGNKEY stuNo REFERENCESstuInfo stuNo GO 演示 给学员信息表stuInfo添加约束 添加主键约束 stuNo作为主键 添加唯一约束 因为每人的身份证号全国唯一 添加默认约束 如果地址不填 默认为 地址不详 添加检查check约束 要求年龄只能在15 40岁之间 添加外键约束 主表stuInfo和从表stuMarks建立关系 关联字段为stuNo 49 添加约束示例 演示 查看约束结果 50 删除约束 如果错误地添加了约束 我们还可以删除约束删除约束的语法 ALTERTABLE表名DROPCONSTRAINT约束名 例如 删除stuInfo表中地址默认约束ALTERTABLEstuInfoDROPCONSTRAINTDF stuAddress 51 总结 SQLServer创建表的过程是规定数据列的属性的过程 同时也是实施数据完整性 包括实体完整性 引用完整性和域完整性等 保证的过程实体完整性数据行不能存在重复 引用完整性要求子表中的相关项必须在主表中存在域完整性实现了对输入到特定列的数值的限制SQLServer中存在五种约束 分别是 主键约束 外键约束 检查约束 默认约束和唯一性约束 唯一性约束将在后续课程中使用SQL语句实现 52 总结 创建数据库表需要 确定表的列名 数据类型 是否允许为空 还需要确定主键 必要的默认值 标识列和检查约束如果建立了主表和子表的关系 则 子表中的相关项目的数据 在主表中必须存在 主表中相关项的数据更改了 则子表对应的数据项也应当随之更改 在删除子表之前 不能够删除主表 53 上机实验 1 在BOOKS数据库中 创建一个新的数据表 名称为 图书借阅信息表 2 使用企业管理器向 读者基本信息表 中录入6条新记录 3 使用SQL语言中的INSERT语句向 图书借阅信息表 中加入5条新记录 54 上机实验 4 上机练习修改数据表中的记录信息 5 上机练习修改数据表的名称 6 上机练习使用两种方法删除数据库中的数据表 55 作业 1 两种方法创建数据库Test 在此数据库中建立一个表student 包含四个字段Student ID 整型 Student Name 字符型 Sex 字符型 要求默认值为 男 年龄 整型 要求设定年龄范围在13 25范围内 设置Studetn ID为主键 完成之后保存 2 练习使用导入 导出数据 把示范数据库NorthWind的表Publishers导出到Excel文件 下课之前交到156885931