表的建立与维护.ppt

第3章表的建立与维护主讲教师 卫琳 3 1 1什么是表 表类型 普通表 分区表 临时表 系统表 表需要包含的列 每列的数据类型 精度表中哪些列允许空值或不允许空值表中是否设置主键 在何处设置主键表中是否设置约束 默认值 规则表中是否设置外键以及在何处设置表中是否设置索引 在何处设置以及设置什么样的索引 一个单词一个字母一个数字一个日期一个NULL值 它表示该单元中没有数据 一个单元可以包含以下内容中的一个 实体与记录 设计数据库实际上就是设计数据库中的表 在设计表时 要使用尽可能少的表数量 在达到设计要求的前提下 使每个表中包含的列的数量尽可能地少 合理的表结构 可以大大提高整个数据库数据的查询效率 为了提高数据库的效率 设计出高质量的存储数据的表 在设计表时 应该从整体上考虑下面7个因素 在设计表时应该考虑的因素 1 因素一 考虑表将要存储哪些数据对象 绘制出ER图 ER图是描述数据库中所有实体以及实体之间关系的图形 是辅助设计关系模型的工具 表就是关系模型 也对应着模型中的实体 是存储数据的对象 在设计表时 应该综合考虑这些问题 数据库中将要包含哪些数据 数据库中应该包含多少表 每一个表将要包含哪些数据 表和表之间是否存在关系 如果存在关系 那么存在什么样的关系 2020年2月14日 第6页 2 因素二 考虑表中将要包含的列以及这些列的数据类型 精度等属性 确定了表之后 就要确定表的内容 每一个表包含多个列 每一个列都有一个数据类型 数字数据类型的列还需要确定列的精度和约度 这些都是设计表时必不可少的因素 数字列应该使用数字数据类型 字符列应该使用字符数据类型 日期列应该使用日期数据类型 对于数字列 需要认真考虑其精度和约度 对于字符列 应该考虑其是否使用定长字符列和字符长度 考虑这些因素的目标是 使得表中的列的数量尽可能地少 如果列的数量过多 应该考虑将该表分解成两个表或多个表 2020年2月14日 第7页 3 因素三 考虑列的属性 例如哪些列允许空值 哪些列不允许空值 列允许空值 表示该列可以不包含任何的数据 空值既不是数字0 也不是空字符 而是表示未知 如果允许列包含空值 表示可以不为该列输入具体的数据 如果不允许列包含空值 在输入数据时必须为该列提供数据 例如 在包含订单的orders表中 订单代码 产品代码 客户代码等列不允许空置 但是订单描述列则可以包含空值 考虑这个因素的目标是 尽可能地不使用允许空值的列 因为空值列有可能带来意想不到的查询效果 如果不得不允许某些列为空 那么应该使得这些列的数量最少 2020年2月14日 第8页 4 因素四 考虑表是否使用主键 如果使用则在何处使用主键 主键是唯一确定每一行数据的方式 是一种数据完整性对象 主键往往是一个列或多个列的组合 一个表中最多只能有一个主键 一般地 应该为每一个表都指定主键 借此可以确定行数据的唯一性 2020年2月14日 第9页 5 因素五 考虑是否使用约束 默认值 规则 以及在何处使用这些对象 约束 默认值 规则等都是数据完整性对象 用来确保表中的数据质量 对表中数据的查询操作 只能在满足定义的约束 默认值 规则等条件下 才能执行成功 这些因素的考虑往往与表中数据的商业特性相关 2020年2月14日 第10页 6 因素六 考虑是否使用外键 在何处使用外键 在ER图中 需要绘制出实体之间的关系 在表的设计时 实体之间的关系需要借助主键 外键对来实现 因此 该因素也是确保ER图完整实施的一个重要内容 只有通过这种关系 才能确保表和表之间强制的商业性关系 2020年2月14日 第11页 7 因素七 考虑是否使用索引 在何处使用索引 使用什么样的索引 索引也是一种数据库对象 是加快对表中数据检索的手段 是提高数据库使用效率的一种重要方法 在哪些列上使用索引 在哪些列上不使用索引 是使用聚集索引 还是使用非聚集索引 是否使用全文索引 等等 对这些因素的认真考虑和实现 也是对表质量的更高的要求 2020年2月14日 第12页 3 1 2表的数据类型 3 1 3创建表 1 使用SSMS创建表 使用SQLServerManagementStudio创建表 多媒体 创建表 在这个交互式多媒体中 你可以了解如何使用SQLServerManagementStudio创建表 请注意你需要为表指定一个自动增长的列作为ID 同时另外一列将拥有默认值 usexscjgocreatetablestudent 学号char 10 notnull 姓名varchar 8 notnull 性别char 2 notnull 专业varchar 30 出生日期smalldatetimenotnull 简历text 例 在xscj数据库下创建student表 2 使用T SQL命令创建表 创建students表 2020年2月14日 第18页 创建orders表 2020年2月14日 第19页 在创建表中使用计算列 2020年2月14日 第20页 计算列由可以使用同一表中的其他列的表达式计算得来 表达式可以是非计算列的列名 常量 函数 也可以是用一个或多个运算符连接的上述元素的任意组合 表达式不能为子查询 例如 在AdventureWorks示例数据库中 Sales SalesOrderHeader表的TotalDue列具有以下定义 TotalDueASSubtotal TaxAmt Freight 一般情况下 计算列是未实际存储在表中的虚拟列 每当在查询中引用计算列时 都将重新计算它们的值 数据库引擎在CREATETABLE和ALTERTABLE语句中使用PERSISTED关键字来将计算列实际存储在表中 如果在计算列的计算更改时涉及任何列 将更新计算列的值 使计算列中的数据物理化 2020年2月14日 第23页 PERSISTED 指定SQLServer数据库引擎将在表中物理存储计算值 而且 当计算列依赖的任何其他列发生更新时对这些计算值进行更新 创建全局临时表 2020年2月14日 第25页 临时表 临时表 在数据库 顾名思义就是起到建立一个临时性的存放某数据集的作中 临时表一般分为 事务临时表的管理 会话临时表事务临时表的话 当事务结束的时候 就会清空这个事务临时表 所以 当我们在数据库临时表中插入数据后 只要事务没有提交的话 该表中的数据就会存在 但是 当事务提交以后 该表中的数据就会被删除 而且 这个变化不会在重做日志中显示 会话临时表 顾名思义 是指数据只在当前会话内有效的临时表 关闭当前会话或者进行新的连接之后 数据表中的内容就会被清除 局部临时表和全局临时表 局部临时表只能被当前登录用户使用 全局临时表可以被不同登录用户使用 其实从局部和全局两个词就能知道 usexscjgoaltertablestudentadd少数民族否bitgo 例 在xscj数据库下修改student表 增加少数民族否一列 为bit类型 然后在此表中删除此列 3 6修改表 altertablestudentdropcolumn少数民族否go 3 6 1使用SSMS图形修改表 3 6 2使用T SQL命令修改表 usexscjgoaltertablestudentaltercolumn姓名varchar 10 go 例 在xscj数据库下修改student表 将姓名长度由原来的8修改为10 将出生日期由原来的smalldatetime修改为date altertablestudentaltercolumn出生日期datego Droptablestudentgo 例 在xscj数据库下删除student表 3 7删除表 3 7 1使用SSMS图形删除表 3 7 2使用T SQL命令删除表 3 2列的数据类型 3 2 1数据类型的分类 3 2 2数字数据类型 3 2 3字符数据类型 char类型存储字符数据时 每一个字符占一个字节大小 使用char类型时 应该给定数据的最大长度 定义格式为 char n 如果没有指定n的大小 默认值是1 最长可以容纳8000个字符 如果实际数据的字符长度大于给定的最大长度时 超过的字符将会被截断 若实际数据的字符长度小于给定的最大长度时 多余的字节被系统使用空格填充 varchar的使用方式与char基本相同 它们的区别在于 当列中字符长度基本一致时 如学号 姓名等 可以使用char类型 当数据长度相差较大时 如备注 使用varchar类型可以节省存储空间 当数据有可能涉及到英语外其他语言时 应该使用Unicode字符编码 每一个Unicode字符需要两个字节存储 因此 Unicode格式比ANSI格式有更大的字符集范围 ANSI字符集仅有256个字符 Unicode字符集有65536个不同的字符 使用Unicode字符可以在表的一个列中同时出现中文 英文 法文等 而不会出现编码冲突 通常使用以下方式来表示Unicode字符 N Unicode字符 nchar和nvarchar分别用于存储固定长度和可变长度的Unicode字符数据 text和ntext 分别对应存储长度很大的ANSI字符和Unicode字符数据 微软公司建议 用户应该避免使用text和ntext数据类型 而使用varchar和nvarchar存储大文本数据 3 2 4日期时间数据类型 datetime型数据长度为8字节 其中前4个字节用于存储日期 后4个字节用于存储时间 由于历史原因的限制 datetime支持的日期范围从1753年1月1日至9999年12月31日 时间精确度是3 33毫秒 由于存在各种各样的表示日期的习惯 输入datetime数据的值是一件比较棘手的事情 如 11 10 01 对于不同的人有不同的理解方式 当将其作为datetime型数据输入时 SQLServer将根据当前系统的语言设置进行转换 系统的语言由登录SQLServer时的默认语言决定 也可以使用SETDATEFORMAT语句 指定字符D M和Y的组合 设置格式为 SETDATEFORMAT format format var 参数format或 format var是指日期的顺序 有效的形式包括MDY DMY YMD YDM MYD和DYM 其中Y表示年 M表示月 D表示日 在默认情况下 日期格式为MDY 在SQLServer2008中 可以使用的时间格式是HH MI SS mmm 其中HH表示小时 MI表示分钟 SS表示秒 mmm表示千分之一秒 存在一种标准输入格式 不受DATEFORMAT设置的影响 不会产生歧义 它的中间部分没有分隔符 形式为 YY YYMMDD HH MI SS mmm 如 20111001 111001 和 2011100120 50 30 988 都是这种格式的应用 在输入datetime类型数据时 我们推荐使用这种格式 在SQLServer2008系统中 日期时间数据类型的最大转变就是在datetime和smalldatetime两种类型的基础上又引入了4种日期时间数据类型 分别为date time datetime2和datetimeoffset 下面分别介绍 date数据类型只存储日期型数据类型 不存储时间数据 取值范围从0001 01 01到9999 12 31 引入date类型 克服了datetime类型中既有日期又有时间的缺陷 使对日期的查询更加方便 time数据类型与date数据类型类似 如果只想存储时间数据而不需要存储日期部分就可以利用time数据类型 取值范围从00 00 00 0000000到23 59 59 9999999 datetime2数据类型是一种日期时间混合的数据类型 不过其时间部分秒数的小数部分可以保留不同位数的值 比datetime数据类型的取值范围要广 可以存储从公元元年1月1日到9999年12月31日的日期 用户可以根据自己的需要设置不同的参数来设定小数位数 最高可以设定到小数点后七位 参数为7 也可以不要小数部分 参数为0 以此类推 datetimeoffset数据类型用于存储与特定的日期和时区相关的日期和时间 这种数据类型的日期和时间存储为协调世界时 CoordinatedUniversalTime UTC 的值 然后 根据与该值有关的时区 定义要增加或减去的时间数 datetimeoffset类型是由年 月 日 小时 分钟 秒和小数秒组成的时间戳结构 小数秒的最大小数位数为7 3 2 5二进制数据类型 二进制数据类型用于表示位数据流 包括binary 固定长度 varbinary 可变长度 和image三种 binary用于存储固定长度的二进制数据 varbinary用于存储可变长度的二进制数据 若存储的二进制大于8000字节 就必须使用varbinary max 数据类型 image数据类型用于存储图像信息 在SQLServer2008中 只有在数据的字节数超过了8KB的情况下 才使用image数据类型 其他情况应使用varbinary max 代替 其中max最大可以达到231 1字节 当二进制数据存储到表中时 可以使用SELECT语句来检索 但是 检索结果以16进制数据格式来显示 3 2 6其他数据类型 timestamp时间戳数据类型与时间 日期无任何关系 timestamp值是二进制 表明数据库中的数据修改发生的相对顺序 实现timestamp数据类型最初是为了支持SQLServer恢复算法 每一个数据库都有一个时间戳计数器 当对该数据库中包含timestamp列的表执行插入